-- Audit trail for reviewer access to manuscripts (preview + download).
-- See docs/reviewer-paper-access/phase-2b-i-audit-watermark-design.md
CREATE TABLE paper_access_audit (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    review_id       UUID        NOT NULL REFERENCES reviews(id),
    reviewer_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')),
    accessed_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address      VARCHAR(45) NOT NULL,   -- IPv6 max length
    user_agent      VARCHAR(500)
);

CREATE INDEX idx_paper_access_audit_review    ON paper_access_audit(review_id);
CREATE INDEX idx_paper_access_audit_reviewer  ON paper_access_audit(reviewer_id);
CREATE INDEX idx_paper_access_audit_paper     ON paper_access_audit(paper_id);
CREATE INDEX idx_paper_access_audit_time      ON paper_access_audit(accessed_at DESC);

-- Composite for admin query patterns:
--   "show me every access a reviewer made to a paper, in order"
CREATE INDEX idx_paper_access_audit_rev_pap_t
    ON paper_access_audit(reviewer_id, paper_id, accessed_at DESC);