-- Phase 1 migration: tracker integration + ratio history + torrent state.
-- Tables added match PROJECT.md Appendix A exactly. See CLAUDE-phase1.md for
-- the rationale on splitting these tables across Phase 1 vs later phases.

CREATE TABLE IF NOT EXISTS trackers (
    id                       TEXT PRIMARY KEY,
    name                     TEXT NOT NULL,
    type                     TEXT NOT NULL,
    base_url                 TEXT NOT NULL,
    config_json              TEXT NOT NULL,
    enabled                  INTEGER NOT NULL DEFAULT 1,
    scrape_interval_seconds  INTEGER NOT NULL DEFAULT 300,
    scrape_jitter_seconds    INTEGER NOT NULL DEFAULT 60,
    use_byparr               INTEGER NOT NULL DEFAULT 0,
    created_at               INTEGER NOT NULL,
    updated_at               INTEGER NOT NULL
);

-- ratio_snapshots is also created in DuckDB (see internal/db/duckdb.go). Both
-- copies have the same columns; the SQLite copy is for cheap recent-history
-- API reads, the DuckDB copy for analytical scans over long windows.
-- simulation_id is the Phase 8 escape hatch; it is created here so Phase 8
-- does not need to ALTER TABLE.
CREATE TABLE IF NOT EXISTS ratio_snapshots (
    id                          INTEGER PRIMARY KEY AUTOINCREMENT,
    tracker_id                  TEXT NOT NULL REFERENCES trackers(id),
    timestamp                   INTEGER NOT NULL,
    simulation_id               INTEGER,
    real_uploaded_bytes         INTEGER,
    real_downloaded_bytes       INTEGER,
    real_ratio                  REAL,
    displayed_uploaded_bytes    INTEGER,
    displayed_downloaded_bytes  INTEGER,
    displayed_ratio             REAL,
    bonus_points                INTEGER,
    unsat_count                 INTEGER,
    unsat_limit                 INTEGER,
    class_or_rank               TEXT,
    raw_json                    TEXT
);
CREATE INDEX IF NOT EXISTS idx_ratio_snapshots_tracker_time
    ON ratio_snapshots(tracker_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_ratio_snapshots_simulation
    ON ratio_snapshots(simulation_id) WHERE simulation_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS torrents (
    info_hash               TEXT PRIMARY KEY,
    name                    TEXT NOT NULL,
    size_bytes              INTEGER NOT NULL,
    category                TEXT,
    tags                    TEXT,
    first_seen_at           INTEGER NOT NULL,
    last_seen_at            INTEGER NOT NULL,
    source_filter_id        TEXT,
    source_tracker_id       TEXT REFERENCES trackers(id),
    cross_seed_origin_hash  TEXT,
    deleted_at              INTEGER
);

CREATE TABLE IF NOT EXISTS torrent_trackers (
    id                              INTEGER PRIMARY KEY AUTOINCREMENT,
    info_hash                       TEXT NOT NULL REFERENCES torrents(info_hash),
    tracker_id                      TEXT NOT NULL REFERENCES trackers(id),
    seed_time_required_seconds      INTEGER,
    seed_time_accumulated_seconds   INTEGER DEFAULT 0,
    h_and_r_risk_at                 INTEGER,
    UNIQUE(info_hash, tracker_id)
);
