-- Unified append-only audit trail (see assets/docs/audit-log-implementation.md).
-- Application role should be granted INSERT, SELECT only (configure per deployment).

DO $$
BEGIN
    CREATE TYPE audit_action AS ENUM (
        'create',
        'read',
        'update',
        'delete',
        'login',
        'logout',
        'login_failed'
    );
EXCEPTION
    WHEN duplicate_object THEN NULL;
END
$$;

CREATE TABLE IF NOT EXISTS audit_events (
    id              BIGSERIAL PRIMARY KEY,
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    actor_user_id   UUID REFERENCES users(id) ON DELETE SET NULL,
    actor_email     TEXT NOT NULL,
    actor_role      TEXT NOT NULL,
    action          audit_action NOT NULL,
    entity_type     TEXT NOT NULL,
    entity_id       TEXT,
    before          JSONB,
    after           JSONB,
    metadata        JSONB NOT NULL DEFAULT '{}'::jsonb,
    request_id      UUID
);

CREATE INDEX IF NOT EXISTS idx_audit_actor_time   ON audit_events (actor_user_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_entity       ON audit_events (entity_type, entity_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_action_time  ON audit_events (action, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_metadata_gin ON audit_events USING gin (metadata);