-- Phase 3 migration: torrent client registry.
-- The `torrents` and `torrent_trackers` tables already exist (Phase 1) and
-- the DuckDB `torrent_snapshots` table already exists (Phase 0). Phase 3
-- only adds the SQLite torrent_clients table; the snapshot stream starts
-- being written by the new internal/snapshot collector.

CREATE TABLE IF NOT EXISTS torrent_clients (
    id                     TEXT PRIMARY KEY,
    name                   TEXT NOT NULL,
    type                   TEXT NOT NULL,            -- "qbit" | "qui"
    base_url               TEXT NOT NULL,
    proxy_via_qui          INTEGER NOT NULL DEFAULT 0,
    config_json            TEXT NOT NULL,
    enabled                INTEGER NOT NULL DEFAULT 1,
    poll_interval_seconds  INTEGER NOT NULL DEFAULT 30,
    created_at             INTEGER NOT NULL,
    updated_at             INTEGER NOT NULL
);

-- Also create the SQLite copy of torrent_snapshots so transactional reads
-- (the recent-history API endpoint) don't have to round-trip through DuckDB.
-- The simulation_id column is the Phase 8 escape hatch.
CREATE TABLE IF NOT EXISTS torrent_snapshots (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    info_hash           TEXT NOT NULL,
    client_id           TEXT NOT NULL REFERENCES torrent_clients(id),
    timestamp           INTEGER NOT NULL,
    simulation_id       INTEGER,
    uploaded_bytes      INTEGER,
    downloaded_bytes    INTEGER,
    state               TEXT,
    ratio               REAL,
    seeders             INTEGER,
    leechers            INTEGER,
    upload_speed_bps    INTEGER,
    download_speed_bps  INTEGER
);
CREATE INDEX IF NOT EXISTS idx_torrent_snapshots_hash_time
    ON torrent_snapshots(info_hash, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_torrent_snapshots_client_time
    ON torrent_snapshots(client_id, timestamp DESC);
