
-- To check whether the trigger has been present or not
SELECT tgname, tgrelid::regclass, tgfoid::regprocedure, tgenabled
FROM pg_trigger
WHERE tgname = 'trigger_name';

-- To get the list of functions
SELECT proname FROM pg_proc WHERE proname LIKE 'function_name';

-- Use the below commands if the logs are not shown in the messages tab but shown only when error is encountered

SHOW log_statement;
    -- IF log statement is none use below command
    SET log_statement = 'all';

SHOW logging_collector;
SHOW client_min_messages;
    -- If client min messages is not LOG use below command to update it
    -- use LOG for Production
    -- use NOTICE for development and Testing
    SET client_min_messages = 'LOG';


==========***** 1. Title: Function to add entry in the approval_process_job_workflow and approval_process_job_workflow_users tabels for approval workflow *****=========

-- Function to replace placeholders dynamically
DROP FUNCTION IF EXISTS public.replace_placeholders(JSONB);
CREATE OR REPLACE FUNCTION public.replace_placeholders(input JSONB)
RETURNS TEXT 
LANGUAGE plpgsql 
AS $function$
DECLARE
    query_text TEXT;
    param_values JSONB;
    new_row JSONB;
	old_row JSONB;
	query_results JSONB;
    placeholder TEXT;
    placeholder_substr TEXT;
    extracted_value TEXT;
    regex_value1 TEXT[];
	regex_value2 TEXT[];
	query_key TEXT;
  	field_key TEXT;
BEGIN
    -- Extract query_text and param_values from the JSON input
    query_text := input ->> 'query_text';
    param_values := input -> 'param_values';
    new_row := input -> 'new_row'; -- optional

    -- RAISE NOTICE 'param_values: %, new_row: %, query_text: %', param_values, new_row, query_text;

    SELECT ARRAY(
        SELECT DISTINCT regexp_matches(query_text, '\$[a-zA-Z0-9_\.]+', 'g')::TEXT
    ) INTO regex_value1;

	-- SELECT ARRAY(
 --        SELECT DISTINCT regexp_matches(query_text, '\@[a-zA-Z0-9_\.]+', 'g')::TEXT
 --    ) INTO regex_value2;
	
	RAISE NOTICE 'regex_value1: %', regex_value1;
	
    IF array_length(regex_value1, 1) > 0 THEN
        FOREACH placeholder IN ARRAY regex_value1
        LOOP
            placeholder := replace(placeholder, '{', '');
            placeholder := replace(placeholder, '}', '');
            extracted_value := NULL;

            -- Handle $prefill_data.*
            IF placeholder LIKE '$prefill_data.%' THEN
                placeholder_substr := substring(placeholder FROM '\$prefill_data\.(.+)');
                extracted_value := param_values #>> string_to_array(placeholder_substr, '.');

                IF extracted_value IS NULL THEN
                    RAISE EXCEPTION 'Missing JSONB key: %', placeholder_substr;
                END IF;
            
            -- Handle $session_user_id.*
            ELSIF placeholder = '$session_user_id' THEN
                extracted_value := new_row #>> '{user_id}';

                IF extracted_value IS NULL THEN
                    RAISE EXCEPTION 'Missing JSONB key: %', '$session_user_id';
                END IF;


            -- Handle direct fields from new_row
            ELSIF placeholder LIKE '$%' AND new_row IS NOT NULL THEN
                placeholder_substr := substring(placeholder FROM '\$(.+)');
                IF new_row ? placeholder_substr THEN
                    extracted_value := new_row ->> placeholder_substr;
                ELSE
                    RAISE EXCEPTION 'Field % not found in new_row', placeholder_substr;
                END IF;
            END IF;

            RAISE NOTICE 'placeholder: %, replacing with: %', placeholder, extracted_value;
            query_text := replace(query_text, placeholder, quote_literal(extracted_value));
        END LOOP;
    END IF;
	
   --  IF array_length(regex_value2, 1) > 0 THEN
   --      FOREACH placeholder IN ARRAY regex_value2
   --      LOOP
   --          placeholder := replace(placeholder, '{', '');
   --          placeholder := replace(placeholder, '}', '');
   --          extracted_value := NULL;

			-- RAISE NOTICE 'regex_value2: %', placeholder;
   --          -- Handle @queryN
   --          IF placeholder LIKE '@query' THEN
   --              SELECT
			-- 	  substring(placeholder FROM '@([^[]+)') AS query_key,     -- query1
			-- 	  substring(placeholder FROM '\[([^\]]+)\]') AS field_key;  -- id
			-- 	RAISE EXCEPTION 'query: %', query_key, field_key;
   --              -- extracted_value := param_values #>> string_to_array(placeholder_substr, '.');

   --              -- IF extracted_value IS NULL THEN
   --              --     RAISE EXCEPTION 'Missing JSONB key: %', placeholder_substr;
   --              -- END IF;
   --          END IF;

   --          RAISE NOTICE 'placeholder: %, replacing with: %', placeholder, extracted_value;
   --          query_text := replace(query_text, placeholder, quote_literal(extracted_value));
   --      END LOOP;
   --  END IF;
    RETURN query_text;

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

-- Function to get email templates
DROP FUNCTION IF EXISTS public.get_email_template(INT);
CREATE OR REPLACE FUNCTION public.get_email_template(p_template_id INT)
RETURNS JSONB
LANGUAGE plpgsql
AS $function$
DECLARE
    result JSONB;
BEGIN
    SELECT 
	    jsonb_build_object(
	        'email_template', to_jsonb(et),
	        'email_template_process', to_jsonb(etp)
	    ) INTO result
	FROM email_templates et
	INNER JOIN email_template_process etp 
	    ON etp.id = et.email_template_process_id
	WHERE et.id = p_template_id;

    RETURN result;
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error in get_email_template: %', SQLERRM;
END;
$function$;


-- Function to get whatsapp template
DROP FUNCTION IF EXISTS public.get_whatsapp_template(INT);
CREATE OR REPLACE FUNCTION public.get_whatsapp_template(p_template_id INT)
RETURNS JSONB
LANGUAGE plpgsql
AS $function$
DECLARE
    result JSONB;
BEGIN
    SELECT 
	    jsonb_build_object(
	        'whatsapp_template', to_jsonb(et),
	        'whatsapp_template_process', to_jsonb(etp)
	    ) INTO result
	FROM whatsapp_templates et
	INNER JOIN whatsapp_template_process etp 
	    ON etp.id = et.whatsapp_template_process_id
	WHERE et.id = p_template_id;

    RETURN result;
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error in get_whatsapp_template: %', SQLERRM;
END;
$function$;

-- Function to retrieve pending approval levels
DROP FUNCTION IF EXISTS public.get_pending_approval_levels(approval_process_job_workflows, TEXT);
CREATE OR REPLACE FUNCTION public.get_pending_approval_levels(job approval_process_job_workflows, status TEXT)
RETURNS JSONB
LANGUAGE plpgsql
AS $function$
DECLARE
    result jsonb;
BEGIN
    SELECT jsonb_agg(row_to_json(t))
    INTO result
    FROM (
        SELECT * 
        FROM approval_process_job_workflows
        WHERE 
            approval_process_job_id = job.approval_process_job_id AND
            approver_order_no > job.approver_order_no AND
            review_status = status::public."ApprovalStatus"
        ORDER BY approver_order_no
    ) t;
    RETURN COALESCE(result, '[]'::jsonb);
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error in get_pending_approval_levels: %', SQLERRM;
END;
$function$;


-- Execute Approval process trigger function
DROP FUNCTION IF EXISTS public.execute_approval_process_job();
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->'email_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 email_process_jobs(
							email_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'email_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->'whatsapp_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 whatsapp_process_jobs(
							whatsapp_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							whatsapp_template_data->'whatsapp_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;

                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->'email_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 email_process_jobs(
							email_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'email_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->'whatsapp_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 whatsapp_process_jobs(
							whatsapp_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							whatsapp_template_data->'whatsapp_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: %', 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->'email_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 email_process_jobs(
							email_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'email_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->'whatsapp_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 whatsapp_process_jobs(
							whatsapp_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							whatsapp_template_data->'whatsapp_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;
			    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->'email_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 email_process_jobs(
							email_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							email_template_data->'email_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->'whatsapp_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 whatsapp_process_jobs(
							whatsapp_template_process_slug,
							unique_id,
							user_id,
							prefill_data,
							company_id,
							status_id,
							created_by
						) VALUES(
							whatsapp_template_data->'whatsapp_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: %', 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$;

-- Initate Approval process trigger function
DROP FUNCTION IF EXISTS public.initiate_approval_process_job();
CREATE OR REPLACE FUNCTION public.initiate_approval_process_job()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $function$
DECLARE
    tag_query_text TEXT;
    data_exists INT;
    param_values JSONB;
    inserted_job_workflow_id INT;
    approver_ids BIGINT[];
    assignments RECORD;
    user_data RECORD;
    approval_workflow RECORD;
    approver_tag TEXT;
    approver_list TEXT[];
    admin_id BIGINT[];
    sql TEXT;
    execute_trigger BOOLEAN := false;
    primary_record RECORD;
BEGIN
    -- Log trigger execution
    RAISE NOTICE 'Trigger executed: % on ID: %', TG_OP, NEW.id;
    
    -- Fetch workflow ID and query
    SELECT id, query_information, url, name, description
    INTO approval_workflow
    FROM approval_workflows
    WHERE slug = NEW.approval_workflow_slug 
      AND status_id = 1 
      AND company_id = NEW.company_id;

    -- Validate workflow existence
    IF approval_workflow.id IS NULL THEN
        RAISE EXCEPTION 'Approval workflow not found for slug: %, company_id: %', 
            NEW.approval_workflow_slug, NEW.company_id;
    END IF;

    -- Validate query existence
    IF approval_workflow.query_information IS NULL THEN
        RAISE EXCEPTION 'Query not found for approval workflow ID: %', approval_workflow.id;
    END IF;

    -- Fetch admin user IDs (ensure it's an array)
    SELECT ARRAY_AGG(id) 
    INTO admin_id 
    FROM users 
    WHERE role = 'company_admin' 
        AND status_id = 1 
        AND company_id = NEW.company_id;

    -- Replace placeholders and execute query
    param_values := NEW.prefill_data;
    approval_workflow.query_information := replace_placeholders(jsonb_build_object(
        'query_text', approval_workflow.query_information,
        'param_values', param_values,
        'new_row', NEW
    ));

    BEGIN
        EXECUTE approval_workflow.query_information INTO data_exists;
    EXCEPTION WHEN OTHERS THEN
        RAISE EXCEPTION 'Error executing query for approval workflow ID: % - %', approval_workflow.id, SQLERRM;
    END;

    -- Process assignments if data exists
    IF data_exists > 0 THEN
        BEGIN
            -- Dynamically build the SQL to update the primary table
            sql := format('SELECT * FROM %I WHERE uuid = $1', NEW.primary_table);

            -- Execute the update
            EXECUTE sql INTO primary_record USING NEW.screen_id::UUID;

            -- Optional debug message
            RAISE NOTICE 'Primary table record has been selected: %, %', NEW.primary_table, NEW.screen_id;
        EXCEPTION WHEN OTHERS THEN
            RAISE EXCEPTION 'Error fetching the record from the primary table: %, %, %', NEW.primary_table, NEW.screen_id, SQLERRM;
        END;

        IF primary_record.is_drafted = false THEN
            BEGIN
                -- Dynamically build the SQL to update the primary table
                sql := format('UPDATE %I SET process_status = $1 WHERE uuid = $2', NEW.primary_table);

                -- Execute the update
                EXECUTE sql USING 'under_approval'::public."ProcessStatus", NEW.screen_id::UUID;
       
                -- Optional debug message
                RAISE NOTICE 'Updated % SET process_status = under_approval WHERE uuid = %', NEW.primary_table, NEW.screen_id;
       
                execute_trigger := true;
            EXCEPTION
                WHEN OTHERS THEN
                    RAISE EXCEPTION 'Error updating the process_status as under_approval in the primary table: %, %, %',
                        NEW.primary_table, NEW.screen_id, SQLERRM;
            END;
        ELSE
            execute_trigger := false;
            BEGIN
                DELETE FROM approval_process_jobs WHERE id = NEW.id;
            EXCEPTION
                WHEN OTHERS THEN
                    RAISE EXCEPTION 'Error deleting the approval process job: %, %', NEW.id, SQLERRM;
            END;
        END IF;


        IF execute_trigger THEN
            FOR assignments IN 
                SELECT *
                FROM approval_workflow_assignments
                WHERE approval_workflow_id = approval_workflow.id
                AND status_id = 1 
                AND company_id = NEW.company_id
            LOOP
                -- Log assignment processing
                RAISE NOTICE 'Processing Assignment ID: %, Approver: %', assignments.id, assignments;

                -- Insert into approval_process_job_workflows
                INSERT INTO approval_process_job_workflows (
                    approval_workflow_assignment_id, 
                    approval_process_job_id, 
                    approval_workflow_slug,
                    approval_process_job_name,
                    approval_process_job_description,
                    primary_table,
                    sequence_no,
                    unique_id,
                    user_id,
                    screen_id,
                    prefill_data,
                    url,
                    approver_order_no,
                    approver_type,
                    approver,
                    review_status, 
                    company_id,
                    approve_query_information,
                    reject_query_information,
                    assigned_at,
					approve_mail_id,
					reject_mail_id,
                    approve_whatsapp_id,
                    reject_whatsapp_id,
                    details
                ) VALUES (
                    assignments.id,
                    NEW.uuid,
                    NEW.approval_workflow_slug,
                    approval_workflow.name,
                    approval_workflow.description,
                    NEW.primary_table,
                    NEW.sequence_no,
                    NEW.unique_id,
                    NEW.user_id,
                    NEW.screen_id,
                    NEW.prefill_data,
                    approval_workflow.url,
                    assignments.approver_order_no,
                    assignments.approver_type,
                    assignments.approver,
                    CASE 
                        WHEN assignments.approver_order_no = 1 THEN 'approval_needed'::public."ApprovalStatus"
                        ELSE 'pending'::public."ApprovalStatus"
                    END, 
                    NEW.company_id,
                    assignments.approve_query_information,
                    assignments.reject_query_information,
                    CASE 
                        WHEN assignments.approver_order_no = 1 THEN NOW()
                        ELSE NULL
                    END,
                    assignments.approve_mail_id,
					assignments.reject_mail_id, 
                    assignments.approve_whatsapp_id,
					assignments.reject_whatsapp_id,
                    NEW.details              
                ) RETURNING id INTO inserted_job_workflow_id;

                -- Reset approvers for each iteration
                approver_ids := NULL;

                -- Determine approvers
                IF assignments.approver_type = 'tag'::public."ApproverType" THEN
                    -- Split approver string into an array
                    approver_list := ARRAY(SELECT trim(value) FROM unnest(STRING_TO_ARRAY(assignments.approver, ',')) AS value);

                    -- Process each approver tag
                    FOREACH approver_tag IN ARRAY approver_list
                    LOOP
                        -- Fetch query for tag
                        SELECT query_information INTO tag_query_text
                        FROM public.approval_workflow_approver_tags
                        WHERE slug = approver_tag
                        AND status_id = 1 
                        AND company_id = NEW.company_id;

                        -- Execute query if found
                        IF tag_query_text IS NOT NULL THEN      
                            RAISE NOTICE 'Executing tag query: %', tag_query_text;
                            tag_query_text := replace_placeholders(jsonb_build_object(
                                'query_text', tag_query_text,
                                'param_values', param_values,
                                'new_row', NEW
                            ));

                            -- Fetch approver IDs
                            FOR user_data IN EXECUTE tag_query_text
                            LOOP
                                approver_ids := ARRAY_APPEND(approver_ids, user_data.user_id::BIGINT);
                            END LOOP;
                        END IF;
                    END LOOP;

                ELSIF assignments.approver_type = 'role_id'::public."ApproverType" THEN
                    -- Fetch approvers based on role_id
                    SELECT ARRAY_AGG(user_roles.user_id) INTO approver_ids
                    FROM user_roles LEFT JOIN users ON users.id = user_roles.user_id 
                    WHERE user_roles.role_id = ANY(STRING_TO_ARRAY(assignments.approver, ',')::int[])
                    AND user_roles.company_id = NEW.company_id AND users.status_id = 1;

                ELSIF assignments.approver_type = 'user_id'::public."ApproverType" THEN
                    -- Convert string to BIGINT array
                    approver_ids := STRING_TO_ARRAY(assignments.approver, ',')::BIGINT[];

                ELSE
                    RAISE EXCEPTION 'Unknown approver type: % for assignment ID: %', assignments.approver_type, assignments.id;
                END IF;

                -- Ensure admin IDs as fallback if no approvers found
                IF approver_ids IS NULL OR array_length(approver_ids, 1) = 0 THEN
                    approver_ids := admin_id;  -- Use admin_id directly since it's already an array
                END IF;

                -- Insert approvers
                IF approver_ids IS NOT NULL THEN
                    INSERT INTO approval_process_job_workflow_users (
                        approval_process_job_workflow_id, user_id, status_id, company_id
                    ) SELECT inserted_job_workflow_id, unnest(approver_ids), 1, NEW.company_id;
                END IF;

            END LOOP;
        END IF;


    END IF;

    RETURN NEW;

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


-- Drop and Create Trigger
DROP TRIGGER IF EXISTS intiate_approval_process_job_trigger ON public.approval_process_jobs;
DROP TRIGGER IF EXISTS execute_approval_process_job_trigger ON public.approval_process_job_workflows;


CREATE TRIGGER intiate_approval_process_job_trigger
AFTER INSERT
ON public.approval_process_jobs
FOR EACH ROW
EXECUTE FUNCTION public.initiate_approval_process_job();



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();
=======================================================================================================
