CREATE TABLE IF NOT EXISTS audit_log (
  id TEXT PRIMARY KEY NOT NULL,
  event_id TEXT,
  source_app TEXT NOT NULL DEFAULT 'cfdm',
  action TEXT NOT NULL,
  severity TEXT NOT NULL DEFAULT 'info',
  actor_user_id TEXT,
  actor_email TEXT,
  actor_name TEXT,
  target_type TEXT,
  target_id TEXT,
  summary TEXT NOT NULL,
  details_json TEXT,
  ip TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_log_created ON audit_log(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action);
CREATE INDEX IF NOT EXISTS idx_audit_log_actor ON audit_log(actor_user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_log_target ON audit_log(target_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_log_source ON audit_log(source_app, created_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_audit_log_event_id ON audit_log(event_id) WHERE event_id IS NOT NULL;