path: root/app/lib/db.server.ts

import Database from "better-sqlite3";
import path from "path";

let db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (!db) {
    const dbPath = process.env.DB_PATH ?? path.resolve("whois.db");
    db = new Database(dbPath);
    db.pragma("journal_mode = WAL");
    db.pragma("foreign_keys = ON");
    initSchema(db);
  }
  return db;
}

function initSchema(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS bands (
      id         TEXT PRIMARY KEY,
      slug       TEXT UNIQUE NOT NULL,
      name       TEXT NOT NULL,
      area       TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS band_links (
      id          TEXT PRIMARY KEY,
      band_id     TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
      label       TEXT NOT NULL,
      url         TEXT NOT NULL,
      order_index INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS artists (
      id         TEXT PRIMARY KEY,
      slug       TEXT UNIQUE NOT NULL,
      name       TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS artist_links (
      id          TEXT PRIMARY KEY,
      artist_id   TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
      label       TEXT NOT NULL,
      url         TEXT NOT NULL,
      order_index INTEGER NOT NULL DEFAULT 0
    );

    -- legacy table kept for migration only
    CREATE TABLE IF NOT EXISTS band_artists (
      band_id     TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
      artist_id   TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
      role        TEXT,
      order_index INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (band_id, artist_id)
    );

    CREATE TABLE IF NOT EXISTS members (
      id          TEXT PRIMARY KEY,
      band_id     TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
      artist_id   TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
      role        TEXT,
      since       TEXT NOT NULL DEFAULT '',
      until       TEXT NOT NULL DEFAULT '',
      note        TEXT NOT NULL DEFAULT '',
      order_index INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS band_revisions (
      id         TEXT PRIMARY KEY,
      band_id    TEXT NOT NULL REFERENCES bands(id) ON DELETE CASCADE,
      snapshot   TEXT NOT NULL,
      message    TEXT NOT NULL,
      ip_address TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS artist_revisions (
      id         TEXT PRIMARY KEY,
      artist_id  TEXT NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
      snapshot   TEXT NOT NULL,
      message    TEXT NOT NULL,
      ip_address TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_band_links_band_id ON band_links(band_id);

    CREATE TABLE IF NOT EXISTS lists (
      id          TEXT PRIMARY KEY,
      slug        TEXT UNIQUE NOT NULL,
      title       TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      created_at  TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS list_entries (
      id          TEXT PRIMARY KEY,
      list_id     TEXT NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
      band_name   TEXT NOT NULL,
      note        TEXT NOT NULL DEFAULT '',
      order_index INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS list_revisions (
      id         TEXT PRIMARY KEY,
      list_id    TEXT NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
      snapshot   TEXT NOT NULL,
      message    TEXT NOT NULL,
      ip_address TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  // migrations
  try { db.exec("ALTER TABLE bands ADD COLUMN description TEXT"); } catch { /* already exists */ }
  try { db.exec("ALTER TABLE bands ADD COLUMN status TEXT NOT NULL DEFAULT 'active'"); } catch { /* already exists */ }
  try { db.exec("ALTER TABLE band_artists ADD COLUMN periods TEXT"); } catch { /* already exists */ }

  // one-time data migration: band_artists → members
  try {
    const mc = (db.prepare("SELECT COUNT(*) as c FROM members").get() as { c: number }).c;
    if (mc === 0) {
      const rows = db.prepare("SELECT * FROM band_artists").all() as {
        band_id: string; artist_id: string; role: string | null; order_index: number;
      }[];
      const insert = db.prepare(
        "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, '', '', '', ?)"
      );
      for (const r of rows) {
        insert.run(crypto.randomUUID(), r.band_id, r.artist_id, r.role, r.order_index);
      }
    }
  } catch { /* band_artists might not have data */ }

  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_artist_links_artist_id ON artist_links(artist_id);
    CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id);
    CREATE INDEX IF NOT EXISTS idx_members_artist_id ON members(artist_id);
    CREATE INDEX IF NOT EXISTS idx_band_revisions_band_id ON band_revisions(band_id);
    CREATE INDEX IF NOT EXISTS idx_artist_revisions_artist_id ON artist_revisions(artist_id);
    CREATE INDEX IF NOT EXISTS idx_list_entries_list_id ON list_entries(list_id);
    CREATE INDEX IF NOT EXISTS idx_list_revisions_list_id ON list_revisions(list_id);
  `);
}

// ── Interfaces ────────────────────────────────────────────────────────────────

export interface Band {
  id: string;
  slug: string;
  name: string;
  area: string | null;
  description: string | null;
  status: string;
  created_at: string;
}

export interface BandLink {
  id: string;
  band_id: string;
  label: string;
  url: string;
  order_index: number;
}

export interface Artist {
  id: string;
  slug: string;
  name: string;
  created_at: string;
}

export interface ArtistLink {
  id: string;
  artist_id: string;
  label: string;
  url: string;
  order_index: number;
}

export interface MemberRaw {
  id: string;
  band_id: string;
  artist_id: string;
  role: string | null;
  since: string;
  until: string;
  note: string;
  order_index: number;
}

export interface BandMemberRow extends MemberRaw {
  artist_name: string;
  artist_slug: string;
}

export interface ArtistMemberRow extends MemberRaw {
  band_name: string;
  band_slug: string;
}

export interface MemberInput {
  artist_id: string;
  role: string | null;
  since: string;
  until: string;
  note: string;
}

export interface BandRevision {
  id: string;
  band_id: string;
  snapshot: string;
  message: string;
  ip_address: string;
  created_at: string;
}

export interface ArtistRevision {
  id: string;
  artist_id: string;
  snapshot: string;
  message: string;
  ip_address: string;
  created_at: string;
}

export interface MemberGroup {
  artist_id: string;
  artist_name: string;
  artist_slug: string;
  periods: BandMemberRow[];
  is_current: boolean;
  duration_months: number | null;
}

export interface BandGroup {
  band_id: string;
  band_name: string;
  band_slug: string;
  periods: ArtistMemberRow[];
  is_current: boolean;
  duration_months: number | null;
}

function parseYearMonth(s: string): { year: number; month: number } | null {
  const m = s.match(/^(\d{4})-(\d{2})$/);
  if (!m) return null;
  return { year: parseInt(m[1]), month: parseInt(m[2]) };
}

function calcDurationMonths(since: string, until: string): number | null {
  const from = parseYearMonth(since);
  if (!from) return null;
  const today = new Date();
  const to = parseYearMonth(until) ?? { year: today.getFullYear(), month: today.getMonth() + 1 };
  return Math.max(0, (to.year * 12 + to.month) - (from.year * 12 + from.month));
}

export function groupBandMembers(members: BandMemberRow[]): {
  current: MemberGroup[];
  former: MemberGroup[];
  all: MemberGroup[];
} {
  const byArtist = new Map<string, BandMemberRow[]>();
  for (const m of members) {
    const list = byArtist.get(m.artist_id) ?? [];
    list.push(m);
    byArtist.set(m.artist_id, list);
  }
  const all: MemberGroup[] = [];
  for (const [artistId, periods] of byArtist) {
    const first = periods[0];
    const is_current = periods.some((p) => !p.until);
    const duration_months = periods.reduce<number | null>((acc, p) => {
      const d = calcDurationMonths(p.since, p.until);
      return d === null ? acc : (acc ?? 0) + d;
    }, null);
    all.push({ artist_id: artistId, artist_name: first.artist_name, artist_slug: first.artist_slug, periods, is_current, duration_months });
  }
  return { current: all.filter((g) => g.is_current), former: all.filter((g) => !g.is_current), all };
}

export function groupArtistMembers(members: ArtistMemberRow[]): {
  current: BandGroup[];
  former: BandGroup[];
  all: BandGroup[];
} {
  const byBand = new Map<string, ArtistMemberRow[]>();
  for (const m of members) {
    const list = byBand.get(m.band_id) ?? [];
    list.push(m);
    byBand.set(m.band_id, list);
  }
  const all: BandGroup[] = [];
  for (const [bandId, periods] of byBand) {
    const first = periods[0];
    const is_current = periods.some((p) => !p.until);
    const duration_months = periods.reduce<number | null>((acc, p) => {
      const d = calcDurationMonths(p.since, p.until);
      return d === null ? acc : (acc ?? 0) + d;
    }, null);
    all.push({ band_id: bandId, band_name: first.band_name, band_slug: first.band_slug, periods, is_current, duration_months });
  }
  return { current: all.filter((g) => g.is_current), former: all.filter((g) => !g.is_current), all };
}

export function getIpAddress(request: Request): string {
  return (
    request.headers.get("x-forwarded-for")?.split(",")[0].trim() ??
    request.headers.get("x-real-ip") ??
    "unknown"
  );
}

export function toSlug(name: string): string {
  return name
    .trim()
    .toLowerCase()
    .replace(/\s+/g, "-")
    .replace(/[^\w぀-ヿ一-鿿＀-￯-]/g, "")
    .replace(/^-+|-+$/g, "");
}

// ── Band queries ──────────────────────────────────────────────────────────────

export function listBands(): Band[] {
  return getDb().prepare("SELECT * FROM bands ORDER BY slug").all() as Band[];
}

export function getBandById(id: string): Band | null {
  return getDb().prepare("SELECT * FROM bands WHERE id = ?").get(id) as Band | null;
}

export function getBandBySlug(slug: string): Band | null {
  return getDb().prepare("SELECT * FROM bands WHERE slug = ?").get(slug) as Band | null;
}

export function getBandLinks(bandId: string): BandLink[] {
  return getDb()
    .prepare("SELECT * FROM band_links WHERE band_id = ? ORDER BY order_index")
    .all(bandId) as BandLink[];
}

export function getBandMembers(bandId: string): BandMemberRow[] {
  return getDb()
    .prepare(
      `SELECT m.*, a.name AS artist_name, a.slug AS artist_slug
       FROM members m
       JOIN artists a ON a.id = m.artist_id
       WHERE m.band_id = ?
       ORDER BY m.order_index`
    )
    .all(bandId) as BandMemberRow[];
}

export function getBandRevisions(bandId: string): BandRevision[] {
  return getDb()
    .prepare("SELECT * FROM band_revisions WHERE band_id = ? ORDER BY created_at DESC")
    .all(bandId) as BandRevision[];
}

export interface CreateBandInput {
  id: string;
  slug: string;
  name: string;
  area: string | null;
  description: string | null;
  status: string;
  links: { label: string; url: string }[];
  members: MemberInput[];
  message: string;
  ip_address: string;
}

export function createBand(input: CreateBandInput): Band {
  const db = getDb();
  return db.transaction(() => {
    db.prepare("INSERT INTO bands (id, slug, name, area, description, status) VALUES (?, ?, ?, ?, ?, ?)").run(
      input.id, input.slug, input.name, input.area, input.description, input.status
    );
    input.links.forEach((link, i) => {
      db.prepare(
        "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), input.id, link.label, link.url, i);
    });
    input.members.forEach((m, i) => {
      db.prepare(
        "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), input.id, m.artist_id, m.role, m.since, m.until, m.note, i);
    });
    const band = getBandById(input.id)!;
    const links = getBandLinks(input.id);
    const members = getBandMembers(input.id);
    db.prepare(
      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), input.id, buildSnapshot(band, links, members), input.message, input.ip_address);
    return band;
  })() as Band;
}

export interface UpdateBandInput {
  slug: string;
  name: string;
  area: string | null;
  description: string | null;
  status: string;
  links: { label: string; url: string }[];
  members: MemberInput[];
  message: string;
  ip_address: string;
}

export function updateBand(id: string, input: UpdateBandInput): void {
  const db = getDb();
  db.transaction(() => {
    db.prepare("UPDATE bands SET slug = ?, name = ?, area = ?, description = ?, status = ? WHERE id = ?").run(
      input.slug, input.name, input.area, input.description, input.status, id
    );
    db.prepare("DELETE FROM band_links WHERE band_id = ?").run(id);
    input.links.forEach((link, i) => {
      db.prepare(
        "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), id, link.label, link.url, i);
    });
    db.prepare("DELETE FROM members WHERE band_id = ?").run(id);
    input.members.forEach((m, i) => {
      db.prepare(
        "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), id, m.artist_id, m.role, m.since, m.until, m.note, i);
    });
    const band = getBandById(id)!;
    const links = getBandLinks(id);
    const members = getBandMembers(id);
    db.prepare(
      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), id, buildSnapshot(band, links, members), input.message, input.ip_address);
  })();
}

function buildSnapshot(band: Band, links: BandLink[], members: BandMemberRow[]): string {
  return JSON.stringify({
    name: band.name,
    area: band.area,
    description: band.description,
    status: band.status,
    links: links.map((l) => ({ label: l.label, url: l.url })),
    members: members.map((m) => ({
      artist_id: m.artist_id,
      artist_name: m.artist_name,
      role: m.role,
      since: m.since,
      until: m.until,
      note: m.note,
    })),
  });
}

// ── Artist queries ────────────────────────────────────────────────────────────

export function listArtists(): Artist[] {
  return getDb().prepare("SELECT * FROM artists ORDER BY name").all() as Artist[];
}

export function getArtistById(id: string): Artist | null {
  return getDb().prepare("SELECT * FROM artists WHERE id = ?").get(id) as Artist | null;
}

export function getArtistBySlug(slug: string): Artist | null {
  return getDb().prepare("SELECT * FROM artists WHERE slug = ?").get(slug) as Artist | null;
}

export function getArtistLinks(artistId: string): ArtistLink[] {
  return getDb()
    .prepare("SELECT * FROM artist_links WHERE artist_id = ? ORDER BY order_index")
    .all(artistId) as ArtistLink[];
}

export function getArtistMembers(artistId: string): ArtistMemberRow[] {
  return getDb()
    .prepare(
      `SELECT m.*, b.name AS band_name, b.slug AS band_slug
       FROM members m
       JOIN bands b ON b.id = m.band_id
       WHERE m.artist_id = ?
       ORDER BY b.name, m.order_index`
    )
    .all(artistId) as ArtistMemberRow[];
}

export function getArtistRevisions(artistId: string): ArtistRevision[] {
  return getDb()
    .prepare("SELECT * FROM artist_revisions WHERE artist_id = ? ORDER BY created_at DESC")
    .all(artistId) as ArtistRevision[];
}

export interface CreateArtistInput {
  id: string;
  slug: string;
  name: string;
  links: { label: string; url: string }[];
  message: string;
  ip_address: string;
}

export function createArtist(input: CreateArtistInput): Artist {
  const db = getDb();
  return db.transaction(() => {
    db.prepare("INSERT INTO artists (id, slug, name) VALUES (?, ?, ?)").run(
      input.id, input.slug, input.name
    );
    input.links.forEach((link, i) => {
      db.prepare(
        "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), input.id, link.label, link.url, i);
    });
    const artist = getArtistById(input.id)!;
    const links = getArtistLinks(input.id);
    const snapshot = JSON.stringify({
      name: artist.name,
      links: links.map((l) => ({ label: l.label, url: l.url })),
    });
    db.prepare(
      "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), input.id, snapshot, input.message, input.ip_address);
    return artist;
  })() as Artist;
}

export interface UpdateArtistInput {
  slug: string;
  name: string;
  links: { label: string; url: string }[];
  message: string;
  ip_address: string;
}

export function updateArtist(id: string, input: UpdateArtistInput): void {
  const db = getDb();
  db.transaction(() => {
    db.prepare("UPDATE artists SET slug = ?, name = ? WHERE id = ?").run(
      input.slug, input.name, id
    );
    db.prepare("DELETE FROM artist_links WHERE artist_id = ?").run(id);
    input.links.forEach((link, i) => {
      db.prepare(
        "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), id, link.label, link.url, i);
    });
    const artist = getArtistById(id)!;
    const links = getArtistLinks(id);
    const snapshot = JSON.stringify({
      name: artist.name,
      links: links.map((l) => ({ label: l.label, url: l.url })),
    });
    db.prepare(
      "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address);
  })();
}

// ── List queries ──────────────────────────────────────────────────────────────

export interface BandList {
  id: string;
  slug: string;
  title: string;
  description: string;
  created_at: string;
}

export interface ListEntry {
  id: string;
  list_id: string;
  band_name: string;
  note: string;
  order_index: number;
}

export interface ListRevision {
  id: string;
  list_id: string;
  snapshot: string;
  message: string;
  ip_address: string;
  created_at: string;
}

export interface ListEntryInput {
  band_name: string;
  note: string;
}

export interface CreateListInput {
  id: string;
  slug: string;
  title: string;
  description: string;
  entries: ListEntryInput[];
  message: string;
  ip_address: string;
}

export interface UpdateListInput {
  slug: string;
  title: string;
  description: string;
  entries: ListEntryInput[];
  message: string;
  ip_address: string;
}

export function listBandLists(): BandList[] {
  return getDb().prepare("SELECT * FROM lists ORDER BY created_at DESC").all() as BandList[];
}

export function getBandListById(id: string): BandList | null {
  return getDb().prepare("SELECT * FROM lists WHERE id = ?").get(id) as BandList | null;
}

export function getBandListBySlug(slug: string): BandList | null {
  return getDb().prepare("SELECT * FROM lists WHERE slug = ?").get(slug) as BandList | null;
}

export function getListEntries(listId: string): ListEntry[] {
  return getDb()
    .prepare("SELECT * FROM list_entries WHERE list_id = ? ORDER BY order_index")
    .all(listId) as ListEntry[];
}

export function getListRevisions(listId: string): ListRevision[] {
  return getDb()
    .prepare("SELECT * FROM list_revisions WHERE list_id = ? ORDER BY created_at DESC")
    .all(listId) as ListRevision[];
}

export function createBandList(input: CreateListInput): BandList {
  const db = getDb();
  return db.transaction(() => {
    db.prepare("INSERT INTO lists (id, slug, title, description) VALUES (?, ?, ?, ?)").run(
      input.id, input.slug, input.title, input.description
    );
    input.entries.forEach((e, i) => {
      db.prepare(
        "INSERT INTO list_entries (id, list_id, band_name, note, order_index) VALUES (?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), input.id, e.band_name, e.note, i);
    });
    const list = getBandListById(input.id)!;
    const entries = getListEntries(input.id);
    db.prepare(
      "INSERT INTO list_revisions (id, list_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), input.id, buildListSnapshot(list, entries), input.message, input.ip_address);
    return list;
  })() as BandList;
}

export function updateBandList(id: string, input: UpdateListInput): void {
  const db = getDb();
  db.transaction(() => {
    db.prepare("UPDATE lists SET slug = ?, title = ?, description = ? WHERE id = ?").run(
      input.slug, input.title, input.description, id
    );
    db.prepare("DELETE FROM list_entries WHERE list_id = ?").run(id);
    input.entries.forEach((e, i) => {
      db.prepare(
        "INSERT INTO list_entries (id, list_id, band_name, note, order_index) VALUES (?, ?, ?, ?, ?)"
      ).run(crypto.randomUUID(), id, e.band_name, e.note, i);
    });
    const list = getBandListById(id)!;
    const entries = getListEntries(id);
    db.prepare(
      "INSERT INTO list_revisions (id, list_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), id, buildListSnapshot(list, entries), input.message, input.ip_address);
  })();
}

function buildListSnapshot(list: BandList, entries: ListEntry[]): string {
  return JSON.stringify({
    title: list.title,
    description: list.description,
    entries: entries.map((e) => ({ band_name: e.band_name, note: e.note })),
  });
}

// ── Export / Import ───────────────────────────────────────────────────────────

export interface DbExport {
  version: 3;
  exported_at: string;
  bands: Band[];
  band_links: BandLink[];
  artists: Artist[];
  artist_links: ArtistLink[];
  members: MemberRaw[];
  band_revisions: BandRevision[];
  artist_revisions: ArtistRevision[];
}

export function exportDb(): DbExport {
  const db = getDb();
  return {
    version: 3,
    exported_at: new Date().toISOString(),
    bands: db.prepare("SELECT * FROM bands").all() as Band[],
    band_links: db.prepare("SELECT * FROM band_links ORDER BY band_id, order_index").all() as BandLink[],
    artists: db.prepare("SELECT * FROM artists").all() as Artist[],
    artist_links: db.prepare("SELECT * FROM artist_links ORDER BY artist_id, order_index").all() as ArtistLink[],
    members: db.prepare("SELECT * FROM members ORDER BY band_id, order_index").all() as MemberRaw[],
    band_revisions: db.prepare("SELECT * FROM band_revisions ORDER BY created_at").all() as BandRevision[],
    artist_revisions: db.prepare("SELECT * FROM artist_revisions ORDER BY created_at").all() as ArtistRevision[],
  };
}

export interface ImportResult {
  bands: number;
  artists: number;
  band_links: number;
  artist_links: number;
  members: number;
  band_revisions: number;
  artist_revisions: number;
}

export function importDb(data: DbExport): ImportResult {
  if (data.version !== 3) throw new Error("Unsupported export version");
  const db = getDb();
  return db.transaction(() => {
    db.prepare("DELETE FROM members").run();
    db.prepare("DELETE FROM band_artists").run();
    db.prepare("DELETE FROM band_revisions").run();
    db.prepare("DELETE FROM artist_revisions").run();
    db.prepare("DELETE FROM band_links").run();
    db.prepare("DELETE FROM artist_links").run();
    db.prepare("DELETE FROM bands").run();
    db.prepare("DELETE FROM artists").run();

    const insertBand = db.prepare(
      "INSERT INTO bands (id, slug, name, area, description, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
    );
    for (const b of data.bands) {
      insertBand.run(b.id, b.slug, b.name, b.area, b.description, b.status, b.created_at);
    }

    const insertArtist = db.prepare(
      "INSERT INTO artists (id, slug, name, created_at) VALUES (?, ?, ?, ?)"
    );
    for (const a of data.artists) {
      insertArtist.run(a.id, a.slug, a.name, a.created_at);
    }

    const insertBandLink = db.prepare(
      "INSERT INTO band_links (id, band_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
    );
    for (const l of data.band_links) {
      insertBandLink.run(l.id, l.band_id, l.label, l.url, l.order_index);
    }

    const insertArtistLink = db.prepare(
      "INSERT INTO artist_links (id, artist_id, label, url, order_index) VALUES (?, ?, ?, ?, ?)"
    );
    for (const l of data.artist_links) {
      insertArtistLink.run(l.id, l.artist_id, l.label, l.url, l.order_index);
    }

    const insertMember = db.prepare(
      "INSERT INTO members (id, band_id, artist_id, role, since, until, note, order_index) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    );
    for (const m of data.members) {
      insertMember.run(m.id, m.band_id, m.artist_id, m.role, m.since, m.until, m.note, m.order_index);
    }

    const insertBandRev = db.prepare(
      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address, created_at) VALUES (?, ?, ?, ?, ?, ?)"
    );
    for (const r of data.band_revisions) {
      insertBandRev.run(r.id, r.band_id, r.snapshot, r.message, r.ip_address, r.created_at);
    }

    const insertArtistRev = db.prepare(
      "INSERT INTO artist_revisions (id, artist_id, snapshot, message, ip_address, created_at) VALUES (?, ?, ?, ?, ?, ?)"
    );
    for (const r of data.artist_revisions) {
      insertArtistRev.run(r.id, r.artist_id, r.snapshot, r.message, r.ip_address, r.created_at);
    }

    return {
      bands: data.bands.length,
      artists: data.artists.length,
      band_links: data.band_links.length,
      artist_links: data.artist_links.length,
      members: data.members.length,
      band_revisions: data.band_revisions.length,
      artist_revisions: data.artist_revisions.length,
    };
  })() as ImportResult;
}