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);
    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;
  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;
  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) VALUES (?, ?, ?, ?)").run(
      input.id, input.slug, input.name, input.area
    );
    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,
      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;
  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 = ? WHERE id = ?").run(
      input.slug, input.name, input.area, 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,
      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);
  })();
}