import Database from 'better-sqlite3';
import { mkdirSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_PATH = join(__dirname, 'newbury.sqlite');

mkdirSync(__dirname, { recursive: true });

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

db.exec(`
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'admin',
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS abilities (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  name      TEXT UNIQUE NOT NULL,
  kind      TEXT NOT NULL DEFAULT 'common',   -- common | boss
  charges   INTEGER,
  cooldown  TEXT,
  effect    TEXT
);

CREATE TABLE IF NOT EXISTS ghosts (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  name         TEXT NOT NULL,
  display_name TEXT,                           -- shown on the lock-on label; falls back to name
  type         TEXT NOT NULL,                  -- red | yellow | blue
  rarity       INTEGER NOT NULL,               -- 1..4 (stars)
  speed        INTEGER NOT NULL DEFAULT 0,
  range        INTEGER NOT NULL DEFAULT 0,
  charge_shot  INTEGER NOT NULL DEFAULT 0,
  health       INTEGER NOT NULL,
  damage       INTEGER NOT NULL,
  ability      TEXT,
  is_boss      INTEGER NOT NULL DEFAULT 0,
  set_number   TEXT,
  set_name     TEXT,
  image_path   TEXT,                           -- uploaded GIF/PNG billboard (nullable)
  enabled      INTEGER NOT NULL DEFAULT 1,
  created_at   TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_ghosts_type   ON ghosts(type);
CREATE INDEX IF NOT EXISTS idx_ghosts_rarity ON ghosts(rarity);
CREATE INDEX IF NOT EXISTS idx_ghosts_boss   ON ghosts(is_boss);

CREATE TABLE IF NOT EXISTS sets (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  code        TEXT UNIQUE NOT NULL,            -- QR payload / scan code
  set_number  TEXT,                            -- e.g. 70419 (reference only)
  set_name    TEXT NOT NULL,
  boss_ghost_id INTEGER REFERENCES ghosts(id) ON DELETE SET NULL,
  enabled     INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS set_ghosts (
  set_id   INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE,
  ghost_id INTEGER NOT NULL REFERENCES ghosts(id) ON DELETE CASCADE,
  PRIMARY KEY (set_id, ghost_id)
);
`);

/* ---- Migrations (idempotent) ----
 * Add transparent-video sprite columns to the existing ghosts table without
 * dropping data. webm_path is the VP9+alpha billboard; webp_path is the
 * animated-WebP fallback used where VP9 alpha isn't supported (e.g. iOS).
 */
function addColumnIfMissing(table, column, decl) {
  const cols = db.prepare(`PRAGMA table_info(${table})`).all();
  if (!cols.some((c) => c.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${decl}`);
  }
}
addColumnIfMissing('ghosts', 'webm_path', 'TEXT'); // VP9+alpha billboard (nullable)
addColumnIfMissing('ghosts', 'webp_path', 'TEXT'); // animated-WebP fallback (nullable)

export default db;
export { DB_PATH };