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;
