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
    );

    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 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);
  `);

  // 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 */ }

  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_artist_links_artist_id ON artist_links(artist_id);
    CREATE INDEX IF NOT EXISTS idx_band_artists_band_id ON band_artists(band_id);
    CREATE INDEX IF NOT EXISTS idx_band_artists_artist_id ON band_artists(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);
  `);
}

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 BandArtistRow {
  band_id: string;
  artist_id: string;
  role: string | null;
  order_index: number;
  artist_name: string;
  artist_slug: string;
}

export interface ArtistBandRow {
  band_id: string;
  artist_id: string;
  role: string | null;
  band_name: string;
  band_slug: 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 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 name").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 getBandArtists(bandId: string): BandArtistRow[] {
  return getDb()
    .prepare(
      `SELECT ba.*, a.name AS artist_name, a.slug AS artist_slug
       FROM band_artists ba
       JOIN artists a ON a.id = ba.artist_id
       WHERE ba.band_id = ?
       ORDER BY ba.order_index`
    )
    .all(bandId) as BandArtistRow[];
}

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 }[];
  artists: { id: string; role: string | null }[];
  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.artists.forEach((artist, i) => {
      db.prepare(
        "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
      ).run(input.id, artist.id, artist.role, i);
    });
    const band = getBandById(input.id)!;
    const links = getBandLinks(input.id);
    const artists = getBandArtists(input.id);
    const snapshot = JSON.stringify({
      name: band.name,
      area: band.area,
      description: band.description,
      status: band.status,
      links: links.map((l) => ({ label: l.label, url: l.url })),
      artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })),
    });
    db.prepare(
      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), input.id, snapshot, 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 }[];
  artists: { id: string; role: string | null }[];
  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 band_artists WHERE band_id = ?").run(id);
    input.artists.forEach((artist, i) => {
      db.prepare(
        "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
      ).run(id, artist.id, artist.role, i);
    });
    const band = getBandById(id)!;
    const links = getBandLinks(id);
    const artists = getBandArtists(id);
    const snapshot = JSON.stringify({
      name: band.name,
      area: band.area,
      description: band.description,
      status: band.status,
      links: links.map((l) => ({ label: l.label, url: l.url })),
      artists: artists.map((a) => ({ id: a.artist_id, name: a.artist_name, role: a.role })),
    });
    db.prepare(
      "INSERT INTO band_revisions (id, band_id, snapshot, message, ip_address) VALUES (?, ?, ?, ?, ?)"
    ).run(crypto.randomUUID(), id, snapshot, input.message, input.ip_address);
  })();
}

// ── 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 getArtistBands(artistId: string): ArtistBandRow[] {
  return getDb()
    .prepare(
      `SELECT ba.*, b.name AS band_name, b.slug AS band_slug
       FROM band_artists ba
       JOIN bands b ON b.id = ba.band_id
       WHERE ba.artist_id = ?
       ORDER BY b.name`
    )
    .all(artistId) as ArtistBandRow[];
}

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);
  })();
}

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

interface BandArtistRaw {
  band_id: string;
  artist_id: string;
  role: string | null;
  order_index: number;
}

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

export function exportDb(): DbExport {
  const db = getDb();
  return {
    version: 1,
    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[],
    band_artists: db.prepare("SELECT * FROM band_artists ORDER BY band_id, order_index").all() as BandArtistRaw[],
    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;
  band_artists: number;
  band_revisions: number;
  artist_revisions: number;
}

export function importDb(data: DbExport): ImportResult {
  if (data.version !== 1) throw new Error("Unsupported export version");
  const db = getDb();
  return db.transaction(() => {
    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 insertBandArtist = db.prepare(
      "INSERT INTO band_artists (band_id, artist_id, role, order_index) VALUES (?, ?, ?, ?)"
    );
    for (const ba of data.band_artists) {
      insertBandArtist.run(ba.band_id, ba.artist_id, ba.role, ba.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,
      band_artists: data.band_artists.length,
      band_revisions: data.band_revisions.length,
      artist_revisions: data.artist_revisions.length,
    };
  })() as ImportResult;
}