DROP TRIGGER IF EXISTS intiate_approval_process_job_trigger ON public.approval_process_jobs;

DROP FUNCTION IF EXISTS public.initiate_approval_process_job() CASCADE;

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, slug, 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.slug 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.slug;
    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.slug, 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_slug = approval_workflow.slug
                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$;


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