============== **************** Functions ****************** =======================

======****** 1. Title: Function to Retrieve the Current Setting Value by Key ***** ========

CREATE OR REPLACE FUNCTION get_current_data(data_key text)
RETURNS VARCHAR AS $$
BEGIN
    -- Return the value of the setting, or NULL if it does not exist
    RETURN current_setting(data_key, TRUE);
END;
$$ LANGUAGE plpgsql;

=======================================================================================================

======****** 2. Title: Audit Trigger Function for Tracking Changes in Database Tables ***** ========

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    field RECORD;
    user_id INT;
    company_id INT;
    old_value TEXT;
    new_value TEXT;
    entity_id INT; -- To store the ID of the old data
BEGIN
    -- Fetch the user ID and company ID from session and convert empty strings to NULL
    user_id := NULLIF(get_current_data('app.lcp_user_id'), '');
    company_id := NULLIF(get_current_data('app.lcp_company_id'), '');

    -- Check if the `id` column exists
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = TG_TABLE_NAME
          AND column_name = 'id'
    ) THEN
        -- Extract the `id` value if it exists
        IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
            EXECUTE format('SELECT ($1).%I', 'id') INTO entity_id USING OLD;
        ELSE
            EXECUTE format('SELECT ($1).%I', 'id') INTO entity_id USING NEW;
        END IF;
    ELSE
        -- Set `entity_id` to 0 if the `id` column does not exist
        entity_id := 0;
    END IF;

    -- Loop through each column and check for changes
    IF TG_OP = 'UPDATE' THEN
        FOR field IN SELECT column_name FROM information_schema.columns 
            WHERE table_name = TG_TABLE_NAME LOOP

            -- Dynamically get the old and new values of the column
            EXECUTE format('SELECT ($1).%I', field.column_name) INTO old_value USING OLD;
            EXECUTE format('SELECT ($1).%I', field.column_name) INTO new_value USING NEW;

            -- Check if the values are different
            IF old_value IS DISTINCT FROM new_value THEN
                INSERT INTO audit_logs (table_name, entity_id, field_name, old_value, new_value, created_at, user_id, operation_type, company_id)
                VALUES (TG_TABLE_NAME, entity_id, field.column_name, 
                    old_value, new_value, 
                    now(), user_id, 'UPDATE', company_id);
            END IF;
        END LOOP;
    ELSIF TG_OP = 'DELETE' THEN
        FOR field IN SELECT column_name FROM information_schema.columns 
            WHERE table_name = TG_TABLE_NAME LOOP

            -- Dynamically get the old value of the column
            EXECUTE format('SELECT ($1).%I', field.column_name) INTO old_value USING OLD;

            -- Insert the old value into the audit_logs for a DELETE operation
            INSERT INTO audit_logs (table_name, entity_id, field_name, old_value, new_value, created_at, user_id, operation_type, company_id)
            VALUES (TG_TABLE_NAME, entity_id, field.column_name, 
                old_value, NULL,  -- Old value before deletion, no new value
                now(), user_id, 'DELETE', company_id);
        END LOOP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

=======================================================================================================

============== *********** Anonymus Block **************** ===================================

================ ******* 1. Title: Audit Trigger Management for Multiple Tables ******** ===============

DO $$
DECLARE
    trigger_name TEXT := 'trigger_audit_log';
    rec RECORD;
BEGIN
    -- Loop through all triggers with the name 'trigger_audit_log'
    FOR rec IN
        SELECT pg_class.relname AS table_name
        FROM pg_trigger
        JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
        WHERE pg_trigger.tgname = trigger_name
    LOOP
        -- Drop the trigger from the identified table
        EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I;', trigger_name, rec.table_name);
    END LOOP;
END $$;

DO $$ 
DECLARE
    table_name TEXT;
    tables TEXT[] := ARRAY['users', 'user_details', 'designations', 'departments', 'permissions', 'roles', 'role_permissions', 'user_roles', 'user_permissions', 'language_contents', 'menu', 'menu_items', 'master_entities', 'master_entity_line_items', 'email_templates', 'email_template_assignments']; -- List of tables for triggers
BEGIN
    -- Loop through each table in the list
    -- FOR table_name IN
    --     SELECT table_name
    --     FROM information_schema.tables
    --     WHERE table_schema = 'public'
    FOREACH table_name IN ARRAY tables
    LOOP
        -- Generate and execute the CREATE TRIGGER statement for each table
        EXECUTE format('
            CREATE TRIGGER trigger_audit_log
            AFTER UPDATE OR DELETE ON %I
            FOR EACH ROW EXECUTE FUNCTION audit_trigger();',
            table_name
        );
    END LOOP;
END $$;
-- CREATE TRIGGER trigger_audit_log
-- AFTER UPDATE OR DELETE ON roles
-- FOR EACH ROW EXECUTE FUNCTION audit_trigger();
SELECT tgname, relname
FROM pg_trigger
JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
WHERE tgname = 'trigger_audit_log';
