
DROP TRIGGER IF EXISTS execute_approval_process_job_trigger ON public.approval_process_job_workflows;

DROP FUNCTION IF EXISTS public.execute_approval_process_job() CASCADE;


CREATE OR REPLACE FUNCTION PUBLIC.EXECUTE_APPROVAL_PROCESS_JOB()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $function$
DECLARE
    levels JSONB;
	email_template_data JSONB;
    whatsapp_template_data JSONB;
	modified_query JSONB;
	email_query_result JSONB;
    whatsapp_query_result JSONB;
    queries TEXT[];
    query_str TEXT;
    query_result JSONB;
    combined_result JSONB := '{}'::JSONB;
    -- i INTEGER := 1;
    next_level_id BIGINT;
    date_at TIMESTAMPTZ;
    sql TEXT;
	process_status public."ProcessStatus";
    reason_val TEXT;
    is_drafted_val BOOLEAN;
	record_details JSONB;
BEGIN
    date_at := NOW();
    RAISE NOTICE 'Trigger executed: % on ID: %', TG_OP, NEW.id;

    -- Fetch next approval levels
    SELECT get_pending_approval_levels(NEW, 'pending') INTO levels;

    -- If approval is completed or rejected
    IF NEW.review_status IN ('approval_completed'::public."ApprovalStatus", 'approval_rejected'::public."ApprovalStatus") THEN

		
        IF jsonb_array_length(levels) > 0 THEN
            -- Case: Rejected
            IF NEW.review_status = 'approval_rejected'::public."ApprovalStatus" THEN
                BEGIN
                    -- Dynamically build the SQL to update the primary table
                    sql := format('UPDATE %I SET process_status = $1, reason = $2, is_drafted = $3 WHERE uuid = $4', NEW.primary_table);

                    -- Execute the update
                    EXECUTE sql USING 'rejected'::public."ProcessStatus", NEW.reason, true, NEW.screen_id::UUID;

                    -- Optional debug message
                    RAISE NOTICE 'Updated % SET process_status = rejected, reason, is_drafted=true WHERE uuid = %', NEW.primary_table, NEW.screen_id;
					IF NEW.reject_mail_id IS NOT NULL THEN
					    SELECT get_email_template(NEW.reject_mail_id) INTO email_template_data;
						modified_query := REPLACE(email_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
						CALL get_multiple_records(modified_query::jsonb, notification_query_result);
						record_details := notification_query_result->'records'->0;
						record_details := jsonb_set(record_details, '{email_custom_reason}', to_jsonb(NEW.reason));
						INSERT INTO notification_process_jobs(
							notification_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'notification_template_process'->>'slug',
							NEW.unique_id::INT, -- unique_id (if that's user_id)
    						(record_details->>'user_id')::INT,
							record_details,
							1,
							1,
							NEW.user_id
						);
						
					    -- RAISE EXCEPTION 'Email Template Data: %', record_details;
					END IF;

                    -- IF NEW.reject_whatsapp_id IS NOT NULL THEN
					--     SELECT get_whatsapp_template(NEW.reject_whatsapp_id) INTO email_template_data;
					-- 	modified_query := REPLACE(email_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
					-- 	CALL get_multiple_records(modified_query::jsonb, whatsapp_query_result);
					-- 	record_details := whatsapp_query_result->'records'->0;
					-- 	record_details := jsonb_set(record_details, '{whatsapp_custom_reason}', to_jsonb(NEW.reason));
					-- 	INSERT INTO notification_process_jobs(
					-- 		notification_template_process_slug,
					-- 		unique_id,
					-- 		user_id,
					-- 		prefill_data,
					-- 		company_id,
					-- 		status_id,
					-- 		notification_type,
					-- 		created_by
					-- 	) VALUES(
					-- 		whatsapp_template_data->'notification_template_process'->>'slug',
					-- 		NEW.unique_id::INT, -- unique_id (if that's user_id)
    				-- 		(record_details->>'user_id')::INT,
					-- 		record_details,
					-- 		1,
					-- 		1,
					-- 		'whatsapp',
					-- 		NEW.user_id
					-- 	);
						
					--     -- RAISE EXCEPTION 'Email Template Data: %', record_details;
					-- END IF;

                EXCEPTION WHEN OTHERS THEN
                    RAISE EXCEPTION 'Error updating the process_status as rejected and draftmode as true in the primary table: %, %, %', NEW.primary_table, NEW.screen_id, SQLERRM;
                END;

                FOR next_level_id IN 
                    SELECT (elem->>'id')::BIGINT 
                    FROM jsonb_array_elements(levels) AS elem
                LOOP
                    UPDATE approval_process_job_workflows 
                    SET review_status = 'void'::public."ApprovalStatus", assigned_at = date_at, processed_at = date_at
                    WHERE id = next_level_id;

                    RAISE NOTICE 'Rejected status updated for ID: %', next_level_id;
                END LOOP;

            -- Case: Completed
            ELSIF NEW.review_status = 'approval_completed'::public."ApprovalStatus" THEN
                next_level_id := (levels->0->>'id')::BIGINT;

					IF NEW.approve_mail_id IS NOT NULL THEN
					    SELECT get_email_template(NEW.approve_mail_id) INTO email_template_data;
						modified_query := REPLACE(email_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
						CALL get_multiple_records(modified_query::jsonb, email_query_result);
						record_details := email_query_result->'records'->0;
						-- Add call_reason to the JSON
						record_details := jsonb_set(record_details, '{email_custom_reason}', to_jsonb(NEW.reason));
						INSERT INTO notification_process_jobs(
							notification_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'notification_template_process'->>'slug',
							NEW.unique_id::INT,  -- unique_id (if that's user_id)
    						(record_details->>'user_id')::INT,
							record_details,
							1,
							1,
							NEW.user_id
						);
						--RAISE EXCEPTION 'Email Template Data: %', email_template_data;
					END IF;


                    -- IF NEW.approve_whatsapp_id IS NOT NULL THEN
					--     SELECT get_whatsapp_template(NEW.approve_whatsapp_id) INTO whatsapp_template_data;
					-- 	--RAISE EXCEPTION 'Whatsapp Template Data: %', whatsapp_template_data;
					-- 	modified_query := REPLACE(whatsapp_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
					-- 	CALL get_multiple_records(modified_query::jsonb, whatsapp_query_result);
					-- 	record_details := whatsapp_query_result->'records'->0;
					-- 	-- Add call_reason to the JSON
					-- 	record_details := jsonb_set(record_details, '{whatsapp_custom_reason}', to_jsonb(NEW.reason));
					-- 	INSERT INTO notification_process_jobs(
					-- 		notification_template_process_slug,
					-- 		unique_id,
					-- 		user_id,
					-- 		prefill_data,
					-- 		company_id,
					-- 		status_id,
					-- 		notification_type,
					-- 		created_by
					-- 	) VALUES(
					-- 		whatsapp_template_data->'notification_template_process'->>'slug',
					-- 		NEW.unique_id::INT,  -- unique_id (if that's user_id)
    				-- 		(record_details->>'user_id')::INT,
					-- 		record_details,
					-- 		1,
					-- 		1,
					-- 		'whatsapp',
					-- 		NEW.user_id
					-- 	);
					-- 	--RAISE EXCEPTION 'Email Template Data: %', whatsapp_template_data;
					-- END IF;



                RAISE NOTICE 'Next level ID to approve: %', next_level_id;

                BEGIN
                    UPDATE approval_process_job_workflows 
                    SET review_status = 'approval_needed'::public."ApprovalStatus", assigned_at = date_at
                    WHERE id = next_level_id;
						
                EXCEPTION WHEN OTHERS THEN
                    RAISE EXCEPTION 'Error updating approval_needed for ID %: %', next_level_id, SQLERRM;
                END;
            END IF;
        ELSE
			BEGIN
			    -- Determine values based on review_status
			    IF NEW.review_status = 'approval_rejected'::public."ApprovalStatus" THEN
			        process_status := 'rejected'::public."ProcessStatus";
			        reason_val := NEW.reason;
			        is_drafted_val := true;

					IF NEW.reject_mail_id IS NOT NULL THEN
					    SELECT get_email_template(NEW.reject_mail_id) INTO email_template_data;
						modified_query := REPLACE(email_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
						CALL get_multiple_records(modified_query::jsonb, email_query_result);
						record_details := email_query_result->'records'->0;
						record_details := jsonb_set(record_details, '{email_custom_reason}', to_jsonb(NEW.reason));
						INSERT INTO notification_process_jobs(
							notification_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'notification_template_process'->>'slug',
							NEW.unique_id::INT,  -- unique_id (if that's user_id)
    						(record_details->>'user_id')::INT,
							record_details,
							1,
							1,
							NEW.user_id
						);
						
					    -- RAISE EXCEPTION 'Email Template Data: %', record_details;
					END IF;

                    -- IF NEW.reject_whatsapp_id IS NOT NULL THEN
					--     SELECT get_whatsapp_template(NEW.reject_whatsapp_id) INTO whatsapp_template_data;
					-- 	modified_query := REPLACE(whatsapp_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
					-- 	CALL get_multiple_records(modified_query::jsonb, whatsapp_query_result);
					-- 	record_details := whatsapp_query_result->'records'->0;
					-- 	record_details := jsonb_set(record_details, '{whatsapp_custom_reason}', to_jsonb(NEW.reason));
					-- 	INSERT INTO notification_process_jobs(
					-- 		notification_template_process_slug,
					-- 		unique_id,
					-- 		user_id,
					-- 		prefill_data,
					-- 		company_id,
					-- 		status_id,
					-- 		notification_type,
					-- 		created_by
					-- 	) VALUES(
					-- 		whatsapp_template_data->'notification_template_process'->>'slug',
					-- 		NEW.unique_id::INT,  -- unique_id (if that's user_id)
    				-- 		(record_details->>'user_id')::INT,
					-- 		record_details,
					-- 		1,
					-- 		1,
					-- 		'whatsapp',
					-- 		NEW.user_id
					-- 	);
						
					--     -- RAISE EXCEPTION 'Email Template Data: %', record_details;
					-- END IF;
			    ELSE
			        process_status := 'approved'::public."ProcessStatus";
			        reason_val := NULL;
			        is_drafted_val := false;
					
					IF NEW.approve_mail_id IS NOT NULL THEN
					    SELECT get_email_template(NEW.approve_mail_id) INTO email_template_data;
						modified_query := REPLACE(email_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
						CALL get_multiple_records(modified_query::jsonb, email_query_result);
						record_details := email_query_result->'records'->0;
						-- Add call_reason to the JSON
						record_details := jsonb_set(record_details, '{email_custom_reason}', to_jsonb(NEW.reason));
						INSERT INTO notification_process_jobs(
							notification_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'notification_template_process'->>'slug',
							NEW.unique_id::INT,  -- unique_id (if that's user_id)
    						(record_details->>'user_id')::INT,
							record_details,
							1,
							1,
							NEW.user_id
						);
						--RAISE EXCEPTION 'Email Template Data: %', email_template_data;
					END IF;


                    -- IF NEW.approve_whatsapp_id IS NOT NULL THEN
					--     SELECT get_whatsapp_template(NEW.approve_whatsapp_id) INTO whatsapp_template_data;
					-- 	--RAISE EXCEPTION 'Whatsapp Template Data: %', whatsapp_template_data;
					-- 	modified_query := REPLACE(whatsapp_template_data->'notification_template_process'->>'query_information'::TEXT, '@process.unique_id', NEW.unique_id::TEXT);
					-- 	CALL get_multiple_records(modified_query::jsonb, whatsapp_query_result);
					-- 	record_details := whatsapp_query_result->'records'->0;
					-- 	-- Add call_reason to the JSON
					-- 	record_details := jsonb_set(record_details, '{whatsapp_custom_reason}', to_jsonb(NEW.reason));
					-- 	INSERT INTO notification_process_jobs(
					-- 		notification_template_process_slug,
					-- 		unique_id,
					-- 		user_id,
					-- 		prefill_data,
					-- 		company_id,
					-- 		status_id,
					-- 		notification_type,
					-- 		created_by
					-- 	) VALUES(
					-- 		whatsapp_template_data->'notification_template_process'->>'slug',
					-- 		NEW.unique_id::INT,  -- unique_id (if that's user_id)
    				-- 		(record_details->>'user_id')::INT,
					-- 		record_details,
					-- 		1,
					-- 		1,
					-- 		'whatsapp',
					-- 		NEW.user_id
					-- 	);
					-- 	--RAISE EXCEPTION 'Email Template Data: %', whatsapp_template_data;
					-- END IF;
			    END IF;
			
			    -- Build dynamic SQL
			    sql := format(
			        'UPDATE %I SET process_status = $1, reason = $2, is_drafted = $3 WHERE uuid = $4',
			        NEW.primary_table
			    );
			
			    -- Execute dynamic SQL
			    EXECUTE sql USING process_status, reason_val, is_drafted_val, NEW.screen_id::UUID;
			
			    -- Debug message
			    RAISE NOTICE 'Updated % SET process_status = %, reason = %, is_drafted = % WHERE uuid = %',
			        NEW.primary_table, process_status, reason_val, is_drafted_val, NEW.screen_id;
			
			EXCEPTION WHEN OTHERS THEN
			    RAISE EXCEPTION 'Error updating process_status and is_drafted in % for uuid %: %',
			        NEW.primary_table, NEW.screen_id, SQLERRM;
			END;

        END IF;

        -- Execute approve/reject queries
        queries := CASE 
                     WHEN NEW.review_status = 'approval_completed' THEN NEW.approve_query_information 
                     ELSE NEW.reject_query_information 
                   END;

        RAISE NOTICE 'Processing % queries. Count: %', NEW.review_status, array_length(queries, 1);

        IF array_length(queries, 1) > 0 THEN
            FOREACH query_str IN ARRAY queries LOOP
                query_str := replace_placeholders(jsonb_build_object(
                    'query_text', query_str,
                    'param_values', NULL,
                    'new_row', NEW,
                    'old_row', OLD,
                    'query_results', combined_result
                ));

                RAISE NOTICE 'Executing query: %', query_str;

                BEGIN
					EXECUTE query_str;
                    -- EXECUTE format('SELECT jsonb_agg(t) FROM (%s) t', query_str) INTO query_result;

                    -- Optionally build a combined result object
                    -- combined_result := combined_result || jsonb_build_object(
                    --     format('query%s', i), COALESCE(query_result, '[]'::jsonb)
                    -- );

                    -- i := i + 1;
                EXCEPTION WHEN OTHERS THEN
                    RAISE EXCEPTION 'Error executing query [%]: %', query_str, SQLERRM;
                END;
            END LOOP;
        END IF;
    END IF;

    RETURN NEW;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unhandled error in execute_approval_process_job: %', SQLERRM;
END;
$function$;

CREATE TRIGGER execute_approval_process_job_trigger
AFTER UPDATE OF review_status
ON public.approval_process_job_workflows
FOR EACH ROW
WHEN (
  OLD.review_status IS DISTINCT FROM NEW.review_status
  AND NEW.review_status IN (
    'approval_completed'::public."ApprovalStatus",
    'approval_rejected'::public."ApprovalStatus"
  )
)
EXECUTE FUNCTION public.execute_approval_process_job();