-- Tier 2 cross-cutting: audit log.
--
-- Every meaningful host-facing write (collaborator change, branding
-- update, threshold tweak, allowlist edit, message send/cancel)
-- records a row here. The shape is deliberately generic so a future
-- Audit tab can render a per-event timeline without per-action joins.

CREATE TABLE audit_log (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID REFERENCES users(id)  ON DELETE SET NULL,
    event_id    UUID REFERENCES events(id) ON DELETE CASCADE,
    action      TEXT NOT NULL,                                -- e.g. "branding.update"
    entity_type TEXT,                                          -- "event" | "guest" | "message" …
    target_id   UUID,                                          -- what was acted on (nullable)
    metadata    JSONB NOT NULL DEFAULT '{}'::jsonb,            -- per-action context
    request_id  TEXT,                                          -- threading / correlation
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The two query shapes we'll need: "show me the trail on this event"
-- (Audit tab, host-side timeline) and "what did this user touch lately"
-- (support / compliance lookups). Partial index on event_id keeps the
-- footprint small since not every audit row carries one.
CREATE INDEX idx_audit_event ON audit_log (event_id, created_at DESC)
    WHERE event_id IS NOT NULL;
CREATE INDEX idx_audit_user  ON audit_log (user_id,  created_at DESC)
    WHERE user_id IS NOT NULL;