-- Per-user API usage record: who called what, when, and how it went.
--
-- Answers "how many times did this user call the API", which until now was only
-- reachable by grepping application logs and only for as long as they were kept.
--
-- BIGSERIAL rather than the UUID default used by newer tables, matching audit_log
-- (V1), which is the closest analogue: an append-only, high-volume, insert-ordered
-- log. A sequential key keeps the primary index compact and in insertion order,
-- where random UUIDs would fragment it for no benefit — nothing references these
-- rows by id.
--
-- Deliberately NOT stored: query strings, request bodies, response bodies. Those
-- carry personal data (an email in a search parameter, an abstract in a POST) and
-- none of it is needed to count calls.
CREATE TABLE api_request_log (
    id           BIGSERIAL    PRIMARY KEY,
    -- Null for unauthenticated calls (login attempts, public archive browsing).
    -- SET NULL on delete so usage history survives a user being removed.
    user_id      UUID         REFERENCES users(id) ON DELETE SET NULL,
    method       VARCHAR(10)  NOT NULL,
    -- The matched route template ("/papers/{id}"), never the raw URI. Raw URIs
    -- would make this column unbounded in cardinality and every paper its own
    -- group, which would make the busiest-endpoint report meaningless.
    path         VARCHAR(200) NOT NULL,
    status       INT          NOT NULL,
    duration_ms  INT          NOT NULL,
    ip_address   VARCHAR(45),
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Per-user drill-down and the summary's GROUP BY.
CREATE INDEX idx_api_request_log_user_created ON api_request_log(user_id, created_at DESC);
-- Nightly retention delete, and every time-windowed report.
CREATE INDEX idx_api_request_log_created      ON api_request_log(created_at DESC);
-- Busiest-endpoint report.
CREATE INDEX idx_api_request_log_path_created ON api_request_log(path, created_at DESC);