import type { Database } from "bun:sqlite";

interface Migration {
  version: number;
  up: (db: Database) => void;
}

const migrations: Migration[] = [
  {
    version: 1,
    up: (db) => {
      db.run(`
        CREATE TABLE authors (
          name             TEXT NOT NULL PRIMARY KEY,
          display_name     TEXT,
          added_at         INTEGER NOT NULL,
          last_synced_at   INTEGER,
          note             TEXT
        );

        CREATE TABLE works (
          fanhao             TEXT NOT NULL PRIMARY KEY,
          author             TEXT NOT NULL,
          title              TEXT NOT NULL,
          url                TEXT NOT NULL,
          duration           TEXT,
          published_at       INTEGER,
          first_seen_at      INTEGER NOT NULL,
          last_seen_at       INTEGER NOT NULL,
          latest_price_yen   INTEGER,
          latest_on_promo    INTEGER NOT NULL DEFAULT 0,
          latest_priced_at   INTEGER,
          detail_fetched_at  INTEGER
        );
        CREATE INDEX idx_works_author           ON works(author);
        CREATE INDEX idx_works_first_seen       ON works(first_seen_at DESC);
        CREATE INDEX idx_works_author_published ON works(author, published_at DESC);

        CREATE TABLE price_history (
          id           INTEGER PRIMARY KEY AUTOINCREMENT,
          fanhao       TEXT NOT NULL,
          price_yen    INTEGER NOT NULL,
          on_promo     INTEGER NOT NULL DEFAULT 0,
          observed_at  INTEGER NOT NULL,
          source       TEXT NOT NULL CHECK(source IN ('listing','detail'))
        );
        CREATE INDEX idx_price_history_fanhao_time ON price_history(fanhao, observed_at DESC);

        CREATE TABLE sukebei_magnets (
          fanhao        TEXT NOT NULL,
          magnet_id     TEXT NOT NULL,
          magnet_url    TEXT NOT NULL,
          title         TEXT,
          file_size     TEXT,
          upload_time   TEXT,
          first_seen_at INTEGER NOT NULL,
          last_seen_at  INTEGER NOT NULL,
          PRIMARY KEY (fanhao, magnet_id)
        );
        CREATE INDEX idx_magnets_first_seen ON sukebei_magnets(first_seen_at DESC);

        CREATE TABLE sync_runs (
          id              INTEGER PRIMARY KEY AUTOINCREMENT,
          author          TEXT NOT NULL,
          started_at      INTEGER NOT NULL,
          finished_at     INTEGER,
          status          TEXT NOT NULL CHECK(status IN ('running','ok','partial','error')),
          new_works       INTEGER NOT NULL DEFAULT 0,
          price_changes   INTEGER NOT NULL DEFAULT 0,
          new_magnets     INTEGER NOT NULL DEFAULT 0,
          with_detail     INTEGER NOT NULL DEFAULT 0,
          with_sukebei    INTEGER NOT NULL DEFAULT 0,
          error_message   TEXT
        );
        CREATE INDEX idx_sync_runs_author_time ON sync_runs(author, started_at DESC);
      `);
    }
  },
  {
    // url is derivable from the work id; drop the column so it can never drift.
    version: 2,
    up: (db) => {
      db.run(`ALTER TABLE works DROP COLUMN url;`);
    }
  },
  {
    // Rename `fanhao` → `id` across the schema to match the FC2-side naming
    // (crawlers already use `id`). price_history's autoincrement PK `id` is
    // renamed to `row_id` first to free up the `id` name for the work ref.
    version: 3,
    up: (db) => {
      db.run(`
        ALTER TABLE price_history RENAME COLUMN id TO row_id;
        ALTER TABLE works RENAME COLUMN fanhao TO id;
        ALTER TABLE price_history RENAME COLUMN fanhao TO id;
        ALTER TABLE sukebei_magnets RENAME COLUMN fanhao TO id;
      `);
    }
  }
];

export const runMigrations = (db: Database): void => {
  db.run(`CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL PRIMARY KEY);`);
  const row = db
    .query<{ version: number }, []>("SELECT MAX(version) AS version FROM schema_version")
    .get();
  const current = row?.version ?? 0;

  for (const m of migrations) {
    if (m.version <= current) continue;
    db.transaction(() => {
      m.up(db);
      db.query("INSERT INTO schema_version (version) VALUES (?)").run(m.version);
    })();
  }
};