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

let db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (!db) {
    const dbPath = 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,
      name      TEXT NOT NULL,
      name_kana TEXT,
      formed_at TEXT,
      area      TEXT,
      genre     TEXT,
      url       TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS members (
      id         TEXT PRIMARY KEY,
      band_id    TEXT NOT NULL REFERENCES bands(id),
      name       TEXT NOT NULL,
      name_kana  TEXT,
      role       TEXT,
      joined_at  TEXT,
      left_at    TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id);
  `);
}