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

import type { AuthorRow } from "@/interfaces";
import { nowMs } from "@/utils/dates";

interface AuthorRecord {
  name: string;
  display_name: string | null;
  added_at: number;
  last_synced_at: number | null;
  note: string | null;
}

const toRow = (r: AuthorRecord): AuthorRow => ({
  name: r.name,
  displayName: r.display_name,
  addedAt: r.added_at,
  lastSyncedAt: r.last_synced_at,
  note: r.note
});

export const addAuthor = (
  db: Database,
  name: string,
  note?: string | null
): { inserted: boolean; row: AuthorRow } => {
  const now = nowMs();
  const existing = db
    .query<AuthorRecord, [string]>("SELECT * FROM authors WHERE name = ?")
    .get(name);
  if (existing) {
    return { inserted: false, row: toRow(existing) };
  }
  db.query("INSERT INTO authors (name, added_at, note) VALUES (?, ?, ?)").run(
    name,
    now,
    note ?? null
  );
  const row = db.query<AuthorRecord, [string]>("SELECT * FROM authors WHERE name = ?").get(name);
  if (!row) throw new Error(`addAuthor: insert failed for ${name}`);
  return { inserted: true, row: toRow(row) };
};

export const removeAuthor = (
  db: Database,
  name: string,
  opts: { purge: boolean } = { purge: false }
): { removed: boolean; purgedWorks: number } => {
  const existing = db
    .query<{ name: string }, [string]>("SELECT name FROM authors WHERE name = ?")
    .get(name);
  if (!existing) return { removed: false, purgedWorks: 0 };

  let purgedWorks = 0;
  db.transaction(() => {
    if (opts.purge) {
      const ids = db
        .query<{ id: string }, [string]>("SELECT id FROM works WHERE author = ?")
        .all(name);
      for (const { id } of ids) {
        db.query("DELETE FROM price_history WHERE id = ?").run(id);
        db.query("DELETE FROM sukebei_magnets WHERE id = ?").run(id);
      }
      const res = db.query("DELETE FROM works WHERE author = ?").run(name);
      purgedWorks = Number(res.changes) || 0;
    }
    db.query("DELETE FROM authors WHERE name = ?").run(name);
  })();
  return { removed: true, purgedWorks };
};

export const listAuthors = (db: Database): AuthorRow[] => {
  const records = db.query<AuthorRecord, []>("SELECT * FROM authors ORDER BY added_at ASC").all();
  return records.map(toRow);
};

export const getAuthor = (db: Database, name: string): AuthorRow | null => {
  const r = db.query<AuthorRecord, [string]>("SELECT * FROM authors WHERE name = ?").get(name);
  return r ? toRow(r) : null;
};

export const touchAuthorLastSynced = (db: Database, name: string, atMs: number): void => {
  db.query("UPDATE authors SET last_synced_at = ? WHERE name = ?").run(atMs, name);
};