-- 046_audit_column_redaction.sql
--
-- Keeps secrets out of the change log.
--
-- `audit.fn_log_change()` records to_jsonb(OLD/NEW) — the whole row — which
-- deliberately bypasses the application's AuditService.maskPayload, because the
-- trigger is meant to capture writes that never go through the application at
-- all. The cost of that is real: a login was already writing
-- `user_sessions.tokenHash` verbatim into audit.change_log, and the same path
-- would capture `users.passwordHash` / `resetToken` and the secrets on
-- `payment_provider_settings`.
--
-- So redaction moves into the database, next to the capture:
--
--   * `redactedColumns` is resolved ONCE at enrolment time, by matching the
--     table's real column names against a pattern. The trigger then does a
--     cheap key lookup per row instead of a regex per column per row.
--   * The pattern is the default rather than a hand-listed set, so a sensitive
--     column added to a table in six months is redacted the moment the table is
--     re-enrolled, instead of leaking until somebody notices.
--
-- Idempotent: safe to re-run.

BEGIN;

ALTER TABLE audit.audited_tables
  ADD COLUMN IF NOT EXISTS "redactedColumns" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[];

-- Anything whose NAME suggests a credential. Deliberately broad: a false
-- positive costs one unreadable field in an audit row, a false negative puts a
-- secret in a table that many people can read.
CREATE OR REPLACE FUNCTION audit.sensitive_column_pattern() RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
  SELECT '(password|passwd|secret|token|apikey|api_key|privatekey|private_key|accesskey|access_key|cvv|cardnumber|card_number|hash|salt|otp|credential|signature|authorization|cookie)'
$$;

-- Replaces the value of every named key with a marker, keeping the key itself
-- so the log still records THAT the field changed — which is usually the point
-- ("someone rotated the payment key at 03:14"), without disclosing the value.
CREATE OR REPLACE FUNCTION audit.redact_columns(p_row JSONB, p_columns TEXT[])
RETURNS JSONB
LANGUAGE plpgsql IMMUTABLE AS $fn$
DECLARE
  c TEXT;
BEGIN
  IF p_row IS NULL OR p_columns IS NULL OR array_length(p_columns, 1) IS NULL THEN
    RETURN p_row;
  END IF;

  FOREACH c IN ARRAY p_columns LOOP
    IF p_row ? c THEN
      p_row := jsonb_set(p_row, ARRAY[c], '"[REDACTED]"'::JSONB);
    END IF;
  END LOOP;

  RETURN p_row;
END;
$fn$;

-- ─────────────────────────────────────────────────────────────────────────────
-- Trigger function, now redacting
-- ─────────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION audit.fn_log_change() RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $fn$
DECLARE
  v_actor        JSONB;
  v_old          JSONB;
  v_new          JSONB;
  v_changed      TEXT[];
  v_excluded     TEXT[] := COALESCE(TG_ARGV[0], '')::TEXT[];
  v_capture_old  BOOLEAN := COALESCE(TG_ARGV[1], 'true')::BOOLEAN;
  v_redacted     TEXT[] := COALESCE(TG_ARGV[2], '{}')::TEXT[];
  v_record_id    TEXT;
  v_store_id     TEXT;
  v_org_id       TEXT;
  v_operation    TEXT;
BEGIN
  BEGIN
    v_actor := NULLIF(current_setting('audit.actor', TRUE), '')::JSONB;
  EXCEPTION WHEN OTHERS THEN
    v_actor := NULL;
  END;

  IF TG_OP = 'INSERT' THEN
    v_operation := 'create';
    v_new := to_jsonb(NEW);
    v_old := NULL;
  ELSIF TG_OP = 'UPDATE' THEN
    v_operation := 'update';
    v_new := to_jsonb(NEW);
    v_old := to_jsonb(OLD);
  ELSE
    v_operation := 'delete';
    v_new := NULL;
    v_old := to_jsonb(OLD);
  END IF;

  -- Diff BEFORE redaction, so a rotated secret is still detected as a change
  -- (both sides would read '[REDACTED]' afterwards and compare equal).
  IF TG_OP = 'UPDATE' THEN
    SELECT COALESCE(array_agg(key ORDER BY key), ARRAY[]::TEXT[])
      INTO v_changed
      FROM jsonb_each(v_new) AS n(key, value)
     WHERE NOT (key = ANY (v_excluded))
       AND n.value IS DISTINCT FROM (v_old -> n.key);

    IF array_length(v_changed, 1) IS NULL THEN
      RETURN NULL;
    END IF;
  ELSE
    SELECT COALESCE(array_agg(key ORDER BY key), ARRAY[]::TEXT[])
      INTO v_changed
      FROM jsonb_object_keys(COALESCE(v_new, v_old)) AS k(key)
     WHERE NOT (key = ANY (v_excluded));
  END IF;

  v_record_id := COALESCE(v_new ->> 'id', v_old ->> 'id', '');
  v_store_id  := COALESCE(v_new ->> 'storeId', v_old ->> 'storeId', v_actor ->> 'storeId');
  v_org_id    := COALESCE(v_new ->> 'organizationId', v_old ->> 'organizationId', v_actor ->> 'organizationId');

  v_new := audit.redact_columns(v_new, v_redacted);
  v_old := audit.redact_columns(v_old, v_redacted);

  INSERT INTO audit.change_log (
    "id", "organizationId", "storeId", "schemaName", "tableName", "recordId",
    "operation", "changedColumns", "oldValue", "newValue", "changedBy",
    "changedOn", "correlationId", "source", "actorRole", "actorName",
    "ipAddress", "batchId", "txId", "createdOn", "updatedOn"
  ) VALUES (
    gen_random_uuid()::TEXT,
    v_org_id, v_store_id, TG_TABLE_SCHEMA, TG_TABLE_NAME, v_record_id,
    v_operation,
    to_jsonb(v_changed),
    CASE WHEN v_capture_old THEN v_old ELSE NULL END,
    v_new,
    COALESCE(v_actor ->> 'actorId', 'system'),
    clock_timestamp(),
    v_actor ->> 'correlationId',
    COALESCE(v_actor ->> 'source', 'trigger'),
    v_actor ->> 'actorRole',
    v_actor ->> 'actorName',
    v_actor ->> 'ipAddress',
    v_actor ->> 'batchId',
    pg_current_xact_id()::TEXT::BIGINT,
    clock_timestamp(),
    clock_timestamp()
  );

  RETURN NULL;
END;
$fn$;

-- ─────────────────────────────────────────────────────────────────────────────
-- Enrolment now resolves the redact list from the table's real columns
-- ─────────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION audit.enroll_table(
  p_schema TEXT,
  p_table  TEXT,
  p_tier   SMALLINT DEFAULT 1
) RETURNS VOID
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_excluded    TEXT[];
  v_capture_old BOOLEAN;
  v_redacted    TEXT[];
  v_trigger     TEXT := format('trg_audit_%s', p_table);
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.tables
     WHERE table_schema = p_schema AND table_name = p_table
  ) THEN
    RAISE NOTICE 'audit.enroll_table: %.% does not exist, skipping', p_schema, p_table;
    RETURN;
  END IF;

  SELECT COALESCE(array_agg(column_name ORDER BY column_name), ARRAY[]::TEXT[])
    INTO v_redacted
    FROM information_schema.columns
   WHERE table_schema = p_schema
     AND table_name = p_table
     AND column_name ~* audit.sensitive_column_pattern();

  INSERT INTO audit.audited_tables ("schemaName", "tableName", "tier", "captureOldValue", "redactedColumns")
  VALUES (p_schema, p_table, p_tier, p_tier = 1, v_redacted)
  ON CONFLICT ("schemaName", "tableName") DO UPDATE
    SET "tier" = EXCLUDED."tier",
        "captureOldValue" = EXCLUDED."captureOldValue",
        -- Union, so a column added by hand to the list is never lost when the
        -- table is re-enrolled.
        "redactedColumns" = ARRAY(
          SELECT DISTINCT unnest(audit.audited_tables."redactedColumns" || EXCLUDED."redactedColumns")
        ),
        "enabled" = TRUE;

  SELECT "excludedColumns", "captureOldValue", "redactedColumns"
    INTO v_excluded, v_capture_old, v_redacted
    FROM audit.audited_tables
   WHERE "schemaName" = p_schema AND "tableName" = p_table;

  EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trigger, p_schema, p_table);
  EXECUTE format(
    'CREATE TRIGGER %I AFTER INSERT OR UPDATE OR DELETE ON %I.%I
       FOR EACH ROW EXECUTE FUNCTION audit.fn_log_change(%L, %L, %L)',
    v_trigger, p_schema, p_table, v_excluded::TEXT, v_capture_old::TEXT, v_redacted::TEXT
  );
END;
$fn$;

-- Re-apply every enrolled table so the redact lists take effect now.
SELECT audit.sync_triggers();

-- ─────────────────────────────────────────────────────────────────────────────
-- Scrub what was already captured before this migration existed
-- ─────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT "schemaName", "tableName", "redactedColumns"
             FROM audit.audited_tables
            WHERE array_length("redactedColumns", 1) > 0
  LOOP
    UPDATE audit.change_log
       SET "newValue" = audit.redact_columns("newValue", r."redactedColumns"),
           "oldValue" = audit.redact_columns("oldValue", r."redactedColumns")
     WHERE "schemaName" = r."schemaName"
       AND "tableName"  = r."tableName";
  END LOOP;
END $$;

COMMIT;