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

import type { SukebeiMagnet } from "@/crawlers/sukebei.db";
import type { MagnetRow } from "@/interfaces";
import { nowMs } from "@/utils/dates";

interface MagnetRecord {
  id: string;
  magnet_id: string;
  magnet_url: string;
  title: string | null;
  file_size: string | null;
  upload_time: string | null;
  first_seen_at: number;
  last_seen_at: number;
}

const toRow = (r: MagnetRecord): MagnetRow => ({
  id: r.id,
  magnetId: r.magnet_id,
  magnetUrl: r.magnet_url,
  title: r.title,
  fileSize: r.file_size,
  uploadTime: r.upload_time,
  firstSeenAt: r.first_seen_at,
  lastSeenAt: r.last_seen_at
});

export interface UpsertResult {
  newMagnetIds: string[];
  totalSeen: number;
}

export const upsertMagnets = (db: Database, id: string, magnets: SukebeiMagnet[]): UpsertResult => {
  const now = nowMs();
  const newMagnetIds: string[] = [];

  db.transaction(() => {
    for (const m of magnets) {
      const res = db
        .query(
          `INSERT INTO sukebei_magnets
             (id, magnet_id, magnet_url, title, file_size, upload_time, first_seen_at, last_seen_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)
           ON CONFLICT(id, magnet_id) DO UPDATE SET last_seen_at = excluded.last_seen_at`
        )
        .run(id, m.magnetId, m.magnetUrl, m.title, m.fileSize, m.uploadTime, now, now);
      // SQLite marks conflicting UPSERT rows as 1 change regardless; check for
      // existence by first_seen_at instead.
      if (res.changes > 0) {
        const row = db
          .query<
            { first_seen_at: number },
            [string, string]
          >("SELECT first_seen_at FROM sukebei_magnets WHERE id = ? AND magnet_id = ?")
          .get(id, m.magnetId);
        if (row && row.first_seen_at === now) newMagnetIds.push(m.magnetId);
      }
    }
  })();

  return { newMagnetIds, totalSeen: magnets.length };
};

export const listMagnetsForWork = (db: Database, id: string): MagnetRow[] => {
  const records = db
    .query<
      MagnetRecord,
      [string]
    >("SELECT * FROM sukebei_magnets WHERE id = ? ORDER BY first_seen_at DESC")
    .all(id);
  return records.map(toRow);
};