-- Audit trail for admin access to manuscripts (preview + download).
-- See docs/admin-paper-review/phase-2-part-1-spec.md
CREATE TABLE admin_paper_access_audit (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    admin_user_id   UUID        NOT NULL REFERENCES users(id),
    paper_id        UUID        NOT NULL REFERENCES papers(id),
    access_type     VARCHAR(16) NOT NULL
                    CHECK (access_type IN ('PREVIEW', 'DOWNLOAD')),
    reason          TEXT        NOT NULL
                    CHECK (char_length(reason) >= 10),
    ip_address      VARCHAR(45) NOT NULL,   -- IPv6 max length
    user_agent      VARCHAR(500),
    accessed_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_admin_paper_access_audit_admin_user  ON admin_paper_access_audit(admin_user_id);
CREATE INDEX idx_admin_paper_access_audit_paper       ON admin_paper_access_audit(paper_id);
CREATE INDEX idx_admin_paper_access_audit_accessed_at ON admin_paper_access_audit(accessed_at DESC);