-- Run this once in pgAdmin 4 after the main application tables have been created.
-- It records authenticated INSERT, UPDATE and DELETE activity across existing
-- application tables. Passwords and row contents are never copied to the log.

CREATE TABLE IF NOT EXISTS system_audit_log (
    id BIGSERIAL PRIMARY KEY,
    actor_id BIGINT NULL,
    actor_name VARCHAR(180) NOT NULL DEFAULT 'Unknown user',
    actor_role VARCHAR(40) NOT NULL DEFAULT '',
    campus VARCHAR(180) NULL,
    entity VARCHAR(120) NOT NULL,
    record_id TEXT NULL,
    action VARCHAR(80) NOT NULL,
    summary TEXT NOT NULL,
    ip_address VARCHAR(64) NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_system_audit_created
    ON system_audit_log (created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_system_audit_campus_created
    ON system_audit_log (campus, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_system_audit_actor_role
    ON system_audit_log (actor_role, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_system_audit_actor_created
    ON system_audit_log (actor_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_system_audit_entity_record
    ON system_audit_log (entity, record_id);

CREATE OR REPLACE FUNCTION rvu_capture_system_audit_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    actor_text TEXT;
    actor_id_value BIGINT;
    actor_name_value TEXT;
    actor_role_value TEXT;
    actor_campus_value TEXT;
    actor_ip_value TEXT;
    row_value JSONB;
    record_id_value TEXT;
    action_value TEXT;
BEGIN
    actor_text := NULLIF(current_setting('rvu.audit_actor_id', true), '');
    -- Ignore anonymous/database maintenance changes. Authenticated API requests
    -- set these values in session_auth.php before changing application data.
    IF actor_text IS NULL THEN
        IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
    END IF;

    IF TG_OP = 'UPDATE' AND to_jsonb(OLD) IS NOT DISTINCT FROM to_jsonb(NEW) THEN
        RETURN NEW;
    END IF;

    actor_id_value := actor_text::BIGINT;
    actor_name_value := COALESCE(NULLIF(current_setting('rvu.audit_actor_name', true), ''), 'Unknown user');
    actor_role_value := COALESCE(NULLIF(current_setting('rvu.audit_actor_role', true), ''), '');
    actor_campus_value := NULLIF(current_setting('rvu.audit_actor_campus', true), '');
    actor_ip_value := NULLIF(current_setting('rvu.audit_ip_address', true), '');

    IF TG_OP = 'DELETE' THEN
        row_value := to_jsonb(OLD);
        action_value := 'delete';
    ELSIF TG_OP = 'UPDATE' THEN
        row_value := to_jsonb(NEW);
        action_value := 'update';
    ELSE
        row_value := to_jsonb(NEW);
        action_value := 'create';
    END IF;

    record_id_value := COALESCE(row_value->>'id', row_value->>'uuid', row_value->>'code');

    INSERT INTO system_audit_log
        (actor_id, actor_name, actor_role, campus, entity, record_id, action, summary, ip_address)
    VALUES
        (actor_id_value, actor_name_value, actor_role_value, actor_campus_value,
         TG_TABLE_NAME, record_id_value, action_value,
         initcap(action_value) || ' record in ' || TG_TABLE_NAME ||
             CASE WHEN record_id_value IS NULL THEN '' ELSE ' #' || record_id_value END,
         actor_ip_value);

    IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
END;
$$;

-- Attach the audit trigger to all existing application tables. Run this
-- migration again if new application tables are added later.
DO $$
DECLARE
    table_row RECORD;
BEGIN
    FOR table_row IN
        SELECT c.relname AS table_name, n.nspname AS schema_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = current_schema()
          AND c.relkind IN ('r', 'p')
          AND c.relname NOT IN ('system_audit_log', 'user_sessions')
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS rvu_system_audit_change ON %I.%I', table_row.schema_name, table_row.table_name);
        EXECUTE format(
            'CREATE TRIGGER rvu_system_audit_change AFTER INSERT OR UPDATE OR DELETE ON %I.%I FOR EACH ROW EXECUTE FUNCTION rvu_capture_system_audit_change()',
            table_row.schema_name, table_row.table_name
        );
    END LOOP;
END;
$$;
