#!/usr/bin/env node
/**
 * seed-ghosts.js — builds a standalone ghost-roster SQLite DB for Newbury Nights.
 *
 * Separate from the live app DB (Free Hunt pool). Read-reference for the hunt
 * loop until you merge. Re-runnable: drops + rebuilds the roster tables only.
 *
 * Usage:
 *   node seed-ghosts.js                 # writes ./ghost-roster.db
 *   node seed-ghosts.js /path/to.db     # custom output path
 *
 * Requires: better-sqlite3, and ghosts.json / abilities.json / locations.json
 * sitting beside this script.
 */
const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

const here = __dirname;
const dbPath = process.argv[2] || path.join(here, 'ghost-roster.db');

const ghosts    = JSON.parse(fs.readFileSync(path.join(here, 'ghosts.json'), 'utf8'));
const abilities = JSON.parse(fs.readFileSync(path.join(here, 'abilities.json'), 'utf8'));
const locations = JSON.parse(fs.readFileSync(path.join(here, 'locations.json'), 'utf8'));

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

// --- schema (roster tables only; namespaced to avoid clashing with app tables) ---
db.exec(`
DROP TABLE IF EXISTS roster_ghost_locations;
DROP TABLE IF EXISTS roster_ghosts;
DROP TABLE IF EXISTS roster_abilities;
DROP TABLE IF EXISTS roster_locations;

CREATE TABLE roster_abilities (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL CHECK (type IN ('Common','Boss')),
  charges       INTEGER,
  cooldown_s    INTEGER,
  effect        TEXT
);

CREATE TABLE roster_locations (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL,          -- plane | image-target | qr
  backdrop      TEXT,
  qr_code       TEXT
);

CREATE TABLE roster_ghosts (
  id            TEXT PRIMARY KEY,       -- name slug, e.g. nagging-nathan
  name          TEXT NOT NULL,
  color         TEXT NOT NULL CHECK (color IN ('Red','Blue','Yellow')),
  rarity        TEXT NOT NULL CHECK (rarity IN ('Common','Rare','Epic','Legendary')),
  rarity_tier   INTEGER NOT NULL,       -- 1-4
  speed_pips    INTEGER,
  range_pips    INTEGER,
  charge_pips   INTEGER,
  health_max    INTEGER,
  damage_max    INTEGER,
  ability_id    TEXT REFERENCES roster_abilities(id),
  face_sprite   TEXT,
  fullbody_sprite TEXT,
  webm_path     TEXT,
  webp_path     TEXT,
  ar_health     REAL,
  haunt_value   REAL,
  is_boss       INTEGER,
  collection_order INTEGER
);

CREATE TABLE roster_ghost_locations (
  ghost_id      TEXT NOT NULL REFERENCES roster_ghosts(id) ON DELETE CASCADE,
  location_id   TEXT NOT NULL REFERENCES roster_locations(id) ON DELETE CASCADE,
  PRIMARY KEY (ghost_id, location_id)
);

CREATE INDEX idx_roster_ghosts_color  ON roster_ghosts(color);
CREATE INDEX idx_roster_ghosts_rarity ON roster_ghosts(rarity_tier);
`);

// --- inserts (transactional) ---
const insAbility = db.prepare(`
  INSERT INTO roster_abilities (id,name,type,charges,cooldown_s,effect)
  VALUES (@id,@name,@type,@charges,@cooldownSeconds,@effect)`);

const insLocation = db.prepare(`
  INSERT INTO roster_locations (id,name,type,backdrop,qr_code)
  VALUES (@id,@name,@type,@backdrop,@qrCode)`);

const insGhost = db.prepare(`
  INSERT INTO roster_ghosts
    (id,name,color,rarity,rarity_tier,speed_pips,range_pips,charge_pips,
     health_max,damage_max,ability_id,face_sprite,fullbody_sprite,
     webm_path,webp_path,ar_health,haunt_value,is_boss,collection_order)
  VALUES
    (@id,@name,@color,@rarity,@rarityTier,@speedPips,@rangePips,@chargePips,
     @healthMax,@damageMax,@abilityId,@faceSprite,@fullBodySprite,
     @webmPath,@webpPath,@arHealth,@hauntValue,@isBoss,@collectionOrder)`);

const insGhostLoc = db.prepare(`
  INSERT OR IGNORE INTO roster_ghost_locations (ghost_id,location_id)
  VALUES (?,?)`);

const seed = db.transaction(() => {
  for (const a of abilities) insAbility.run(a);
  for (const l of locations) insLocation.run({ backdrop:null, qrCode:null, ...l });
  let order = 0;
  for (const g of ghosts) {
    insGhost.run({
      isBoss: g.isBoss == null ? null : (g.isBoss ? 1 : 0),
      collectionOrder: order++,
      ...g
    });
    for (const locId of (g.locations || [])) insGhostLoc.run(g.id, locId);
  }
});
seed();

const n = id => db.prepare(`SELECT COUNT(*) c FROM ${id}`).get().c;
console.log(`Seeded ${dbPath}`);
console.log(`  abilities: ${n('roster_abilities')}`);
console.log(`  locations: ${n('roster_locations')}`);
console.log(`  ghosts:    ${n('roster_ghosts')}`);
console.log(`  by color:`, db.prepare(
  `SELECT color, COUNT(*) c FROM roster_ghosts GROUP BY color`).all());
console.log(`  by rarity:`, db.prepare(
  `SELECT rarity, COUNT(*) c FROM roster_ghosts GROUP BY rarity_tier ORDER BY rarity_tier`).all());
db.close();