-- Community-reported broken mods. Each (workshop_id, version) is unique;
-- re-submitting the same pair upserts (refreshes updated_at) while
-- preserving accumulated votes. ORDER BY updated_at DESC drives the
-- list view, so a re-report bubbles the entry back to the top with
-- previous up/down counts intact.
CREATE TABLE IF NOT EXISTS broken_mod_reports (
    id           BIGSERIAL PRIMARY KEY,
    workshop_id  TEXT NOT NULL,
    mod_name     TEXT,
    version      TEXT NOT NULL,
    upvotes      INTEGER NOT NULL DEFAULT 0,
    downvotes    INTEGER NOT NULL DEFAULT 0,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (workshop_id, version)
);
CREATE INDEX IF NOT EXISTS broken_mod_reports_updated_idx
    ON broken_mod_reports (updated_at DESC);
CREATE INDEX IF NOT EXISTS broken_mod_reports_wsid_idx
    ON broken_mod_reports (workshop_id);