-- Tear out the hand-rolled `audit_log` domain-history table and realign the
-- framework `audit` sink to the canonical pikku AuditEvent shape (audit_id PK,
-- actor_user_id, data JSON). Domain row-change history now flows through the
-- pikku audit service — KyselyAuditService locally, a platform queue sink in
-- prod — persisted here and read back by get-booking-audit-log via metadata →
-- data. The old `audit` table had a drifted shape and was never written to.
DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS audit;

CREATE TABLE audit (
  audit_id       TEXT NOT NULL PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
  occurred_at    TEXT NOT NULL DEFAULT (datetime('now')),
  type           TEXT NOT NULL,
  source         TEXT NOT NULL DEFAULT 'auto',
  outcome        TEXT,
  function_id    TEXT,
  wire_type      TEXT,
  trace_id       TEXT,
  transaction_id TEXT,
  query_id       TEXT,
  actor_user_id  TEXT,
  actor_org_id   TEXT,
  tables         TEXT,  -- JSON array of table names touched
  changed_cols   TEXT,  -- JSON array of changed column names
  event          TEXT,  -- custom event label
  old            TEXT,  -- JSON: previous values
  data           TEXT   -- JSON: new values / event payload
);

CREATE INDEX IF NOT EXISTS idx_audit_occurred_at ON audit (occurred_at);
CREATE INDEX IF NOT EXISTS idx_audit_actor        ON audit (actor_user_id) WHERE actor_user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_audit_function     ON audit (function_id)   WHERE function_id  IS NOT NULL;