-- pzsort schema
-- Run as: psql -U pzsort -d pzsort -f schema.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- -----------------------------------------------------------------------------
-- workshop_meta
-- Cheap, refreshed often via Steam ISteamRemoteStorage/GetPublishedFileDetails.
-- Keyed by Steam publishedfileid (text to avoid bigint surprises).
-- time_updated is the cache-invalidation key for mod_parsed.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS workshop_meta (
    workshop_id          TEXT PRIMARY KEY,
    title                TEXT,
    description          TEXT,
    tags                 TEXT[]      NOT NULL DEFAULT '{}',
    creator_steamid      TEXT,
    time_created         BIGINT,                 -- unix ts from Steam
    time_updated         BIGINT      NOT NULL,   -- unix ts; cache invalidation key
    file_size            BIGINT,
    preview_url          TEXT,
    consumer_app_id      INTEGER,                -- 108600 for PZ
    visibility           INTEGER,                -- 0=public, 1=friends, 2=private
    banned               BOOLEAN     NOT NULL DEFAULT FALSE,
    last_checked_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS workshop_meta_last_checked_idx
    ON workshop_meta (last_checked_at);

-- -----------------------------------------------------------------------------
-- mod_parsed
-- Expensive: requires DepotDownloader fetch. Only refreshed when
-- workshop_meta.time_updated changes vs parsed_at_time_updated.
-- One workshop item can yield N rows (multi-mod packages).
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mod_parsed (
    workshop_id              TEXT NOT NULL REFERENCES workshop_meta(workshop_id) ON DELETE CASCADE,
    mod_id                   TEXT NOT NULL,                -- mod.info `id=`
    name                     TEXT NOT NULL DEFAULT '',
    category                 TEXT NOT NULL DEFAULT 'undefined',
    requirements             TEXT[] NOT NULL DEFAULT '{}',
    load_after               TEXT[] NOT NULL DEFAULT '{}',
    load_before              TEXT[] NOT NULL DEFAULT '{}',
    incompatible_mods        TEXT[] NOT NULL DEFAULT '{}',
    load_first               TEXT NOT NULL DEFAULT 'off',
    load_last                TEXT NOT NULL DEFAULT 'off',
    tags                     TEXT[] NOT NULL DEFAULT '{}',
    maps                     TEXT[] NOT NULL DEFAULT '{}',  -- map folder names
    raw_mod_info             TEXT,                          -- original file for debugging
    version_min              TEXT,                          -- e.g. 41.55
    parsed_at_time_updated   BIGINT NOT NULL,               -- snapshot of workshop_meta.time_updated at parse
    parsed_at                TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (workshop_id, mod_id)
);

CREATE INDEX IF NOT EXISTS mod_parsed_mod_id_idx ON mod_parsed (mod_id);

-- -----------------------------------------------------------------------------
-- download_jobs
-- Work queue for the DepotDownloader worker. One job per workshop_id.
-- Worker dequeues (status='queued') ORDER BY priority DESC, created_at ASC.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS download_jobs (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workshop_id  TEXT NOT NULL,
    status       TEXT NOT NULL DEFAULT 'queued',  -- queued|downloading|parsing|done|failed
    priority     INTEGER NOT NULL DEFAULT 0,       -- higher first
    attempts     INTEGER NOT NULL DEFAULT 0,
    error        TEXT,
    requested_by TEXT,                              -- IP hash or user token; for rate limiting
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS download_jobs_dequeue_idx
    ON download_jobs (status, priority DESC, created_at ASC)
    WHERE status = 'queued';

CREATE INDEX IF NOT EXISTS download_jobs_workshop_idx
    ON download_jobs (workshop_id);

-- Trigger: keep updated_at fresh
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS download_jobs_touch ON download_jobs;
CREATE TRIGGER download_jobs_touch
    BEFORE UPDATE ON download_jobs
    FOR EACH ROW
    EXECUTE FUNCTION touch_updated_at();

-- -----------------------------------------------------------------------------
-- collections
-- Cache for ISteamRemoteStorage/GetCollectionDetails results.
-- Collections expand to N child workshop_ids; we cache that mapping.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS collections (
    collection_id        TEXT PRIMARY KEY,
    title                TEXT,
    child_workshop_ids   TEXT[] NOT NULL DEFAULT '{}',
    last_fetched_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- -----------------------------------------------------------------------------
-- sort_requests
-- Optional: log of submitted sort jobs for debugging + abuse triage.
-- Not required for sort to function. Keep TTL short via a cron.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sort_requests (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    input_workshop_ids  TEXT[] NOT NULL,
    input_collection_id TEXT,
    cache_hits      INTEGER NOT NULL DEFAULT 0,
    cache_misses    INTEGER NOT NULL DEFAULT 0,
    requested_by    TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS sort_requests_created_idx
    ON sort_requests (created_at);