-- t-paliad-182 / Fristen Phase 3 Slice 1 — audit log for the rule editor
-- (design §2.8, §3.1 Step A.079).
--
-- The audit log lands BEFORE the rule editor (Slice 11) so every future
-- write to paliad.deadline_rules is captured forever, including the
-- Slice 2 backfill UPDATEs. Defence-in-depth: the rule-editor service
-- writes Go-authored audit rows with semantic actions ('publish',
-- 'archive', 'restore'); this trigger is the backstop for raw SQL.
--
-- Field-naming mirrors design §2.8 (`changed_by` / `changed_at` /
-- `before_json` / `after_json` / `migration_exported`), not the
-- audit_log shorthand used elsewhere in Paliad.
--
-- Schema deviations from design §2.8, documented for the head review:
--
--   1. `changed_by` is nullable, not NOT NULL. Reason: the trigger reads
--      auth.uid() which is NULL when the writer is `service_role`
--      (migrations, server-side Go using the service key, direct DB
--      maintenance). NOT NULL would block every Slice-2 backfill UPDATE
--      and every migration-applied seed. The Go rule-editor service
--      enforces non-NULL changed_by at the application layer when it
--      writes its own audit rows.
--
--   2. `action` values stored by the trigger are 'create' / 'update' /
--      'delete' (the raw TG_OP semantics). Go-authored audit rows can
--      additionally store 'publish' / 'archive' / 'restore' — those are
--      lifecycle_state flips at the SQL level and appear as 'update' in
--      the trigger's view of the world. The Go layer writes the
--      higher-level action *before* the UPDATE, so the human-readable
--      action is captured even though the trigger fires a paired
--      'update' row. The audit UI in Slice 11 collapses paired rows.
--
-- Audit-reason enforcement: the trigger reads
-- `current_setting('paliad.audit_reason', true)` (the `true` flag
-- returns NULL when unset rather than raising). On UPDATE and DELETE
-- the trigger requires a non-empty reason and raises EXCEPTION 'audit
-- reason required' if missing. On INSERT the reason is optional
-- (defaults to 'create' so seed migrations don't need to set it).
--
-- Idempotent: re-applying is a no-op. Tracker advances 78 → 79.

-- =============================================================================
-- 1. paliad.deadline_rule_audit
-- =============================================================================

CREATE TABLE IF NOT EXISTS paliad.deadline_rule_audit (
    id                   uuid          PRIMARY KEY DEFAULT gen_random_uuid(),

    -- The rule this delta concerns. ON DELETE CASCADE: when a rule row
    -- gets hard-deleted (rare; lifecycle_state='archived' is the normal
    -- path), drop its audit chain too — the trail otherwise survives in
    -- the migration history of the table itself.
    rule_id              uuid          NOT NULL
                          REFERENCES paliad.deadline_rules(id) ON DELETE CASCADE,

    -- See header comment §1: nullable so trigger writes from service_role
    -- contexts (migrations, backfills) don't fail.
    changed_by           uuid          REFERENCES auth.users(id) ON DELETE SET NULL,

    changed_at           timestamptz   NOT NULL DEFAULT now(),

    -- See header comment §2 for the trigger vs Go-layer split.
    action               text          NOT NULL
                          CHECK (action IN (
                              'create', 'update', 'delete',
                              'publish', 'archive', 'restore'
                          )),

    -- Row state pre/post change. NULL on create / delete respectively.
    before_json          jsonb,
    after_json           jsonb,

    -- Justification required by the trigger on UPDATE / DELETE; optional
    -- on INSERT (defaults to 'create' when paliad.audit_reason is unset
    -- so seed migrations don't need to bother).
    reason               text          NOT NULL,

    -- Flips to true when the migration-export endpoint (Slice 11b) folds
    -- this delta into a checked-in .up.sql. Lets the export endpoint
    -- skip already-exported rows.
    migration_exported   boolean       NOT NULL DEFAULT false
);

CREATE INDEX IF NOT EXISTS deadline_rule_audit_rule_id_idx
    ON paliad.deadline_rule_audit (rule_id, changed_at DESC);

CREATE INDEX IF NOT EXISTS deadline_rule_audit_changed_at_idx
    ON paliad.deadline_rule_audit (changed_at DESC);

CREATE INDEX IF NOT EXISTS deadline_rule_audit_changed_by_idx
    ON paliad.deadline_rule_audit (changed_by)
    WHERE changed_by IS NOT NULL;

CREATE INDEX IF NOT EXISTS deadline_rule_audit_pending_export_idx
    ON paliad.deadline_rule_audit (changed_at DESC)
    WHERE migration_exported = false;

COMMENT ON TABLE paliad.deadline_rule_audit IS
    'Append-only audit log for paliad.deadline_rules. Written by the '
    'AFTER-trigger on the rules table (raw create/update/delete) and '
    'by the Go rule-editor service (semantic publish/archive/restore). '
    'Required reason field is the compliance hook for the rule-editor '
    'design (Q5, §4.7).';

-- =============================================================================
-- 2. Audit trigger
-- =============================================================================
--
-- SECURITY DEFINER so the trigger function runs with the table-owner's
-- privileges and bypasses RLS on the audit table. Otherwise an
-- authenticated user's UPDATE on a rule would fail when the trigger
-- tried to INSERT under their RLS context.

CREATE OR REPLACE FUNCTION paliad.deadline_rule_audit_trigger()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = paliad, public
AS $$
DECLARE
    v_reason text;
    v_action text;
    v_before jsonb;
    v_after  jsonb;
    v_rule_id uuid;
BEGIN
    v_reason := current_setting('paliad.audit_reason', true);

    IF TG_OP = 'INSERT' THEN
        v_action  := 'create';
        v_before  := NULL;
        v_after   := to_jsonb(NEW);
        v_rule_id := NEW.id;
        -- INSERT is allowed without an explicit reason; seed migrations
        -- and net-new drafts default to a synthetic reason.
        IF v_reason IS NULL OR v_reason = '' THEN
            v_reason := 'create';
        END IF;

    ELSIF TG_OP = 'UPDATE' THEN
        v_action  := 'update';
        v_before  := to_jsonb(OLD);
        v_after   := to_jsonb(NEW);
        v_rule_id := NEW.id;
        IF v_reason IS NULL OR v_reason = '' THEN
            RAISE EXCEPTION 'paliad.deadline_rules: audit reason required for UPDATE — '
                            'set paliad.audit_reason via SET LOCAL or set_config()';
        END IF;

    ELSIF TG_OP = 'DELETE' THEN
        v_action  := 'delete';
        v_before  := to_jsonb(OLD);
        v_after   := NULL;
        v_rule_id := OLD.id;
        IF v_reason IS NULL OR v_reason = '' THEN
            RAISE EXCEPTION 'paliad.deadline_rules: audit reason required for DELETE — '
                            'set paliad.audit_reason via SET LOCAL or set_config()';
        END IF;
    END IF;

    INSERT INTO paliad.deadline_rule_audit
        (rule_id, changed_by, action, before_json, after_json, reason)
    VALUES
        (v_rule_id, auth.uid(), v_action, v_before, v_after, v_reason);

    RETURN COALESCE(NEW, OLD);
END;
$$;

COMMENT ON FUNCTION paliad.deadline_rule_audit_trigger() IS
    'AFTER-trigger backstop that writes paliad.deadline_rule_audit rows '
    'for every raw INSERT / UPDATE / DELETE on paliad.deadline_rules. '
    'UPDATE / DELETE require paliad.audit_reason to be set in the '
    'session (via SET LOCAL paliad.audit_reason = ...); INSERT defaults '
    'to ''create'' so seed migrations remain ergonomic.';

DROP TRIGGER IF EXISTS deadline_rules_audit_aiud ON paliad.deadline_rules;

CREATE TRIGGER deadline_rules_audit_aiud
    AFTER INSERT OR UPDATE OR DELETE ON paliad.deadline_rules
    FOR EACH ROW
    EXECUTE FUNCTION paliad.deadline_rule_audit_trigger();

-- =============================================================================
-- 3. RLS on the audit table
-- =============================================================================
--
-- Read: global_admin only (mirrors mig 057 pattern). Service-layer code
-- gates `/admin/rules/{id}/audit` separately; this RLS is defence-in-
-- depth for any future auth-context query path.
--
-- Write: nobody via row-level paths. The trigger function is
-- SECURITY DEFINER so it bypasses RLS entirely. Direct INSERTs by
-- authenticated users are denied (no INSERT policy). service_role
-- bypasses RLS as usual.

ALTER TABLE paliad.deadline_rule_audit ENABLE ROW LEVEL SECURITY;

CREATE POLICY deadline_rule_audit_select
    ON paliad.deadline_rule_audit FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM paliad.users u
             WHERE u.id = auth.uid()
               AND u.global_role = 'global_admin'
        )
    );
