// SQLite persistence using Node's built-in node:sqlite (Node 22.13+). No native modules.
// Each tournament is stored as two JSON documents (tournament state + court engine state),
// rewritten on every mutation. Data is tiny (a few hundred KB for a big day), so this is
// simpler and more robust than a normalized schema, and a backup is one file copy.
import { DatabaseSync } from 'node:sqlite';
import { mkdirSync } from 'node:fs';
import { dirname } from 'node:path';

export class Store {
  constructor(path) {
    if (path !== ':memory:') mkdirSync(dirname(path), { recursive: true });
    this.db = new DatabaseSync(path);
    this.db.exec(`
      PRAGMA journal_mode = WAL;
      CREATE TABLE IF NOT EXISTS tournaments (
        slug TEXT PRIMARY KEY,
        tournament_json TEXT NOT NULL,
        engine_json TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      );
      CREATE TABLE IF NOT EXISTS events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        slug TEXT NOT NULL,
        ts INTEGER NOT NULL,
        actor TEXT,
        type TEXT NOT NULL,
        json TEXT NOT NULL
      );
      CREATE INDEX IF NOT EXISTS events_slug ON events(slug, id);
    `);
    this.stmts = {
      all: this.db.prepare('SELECT slug, tournament_json, engine_json FROM tournaments ORDER BY created_at DESC'),
      upsert: this.db.prepare(`INSERT INTO tournaments (slug, tournament_json, engine_json, created_at, updated_at) VALUES (?, ?, ?, ?, ?)
        ON CONFLICT(slug) DO UPDATE SET tournament_json = excluded.tournament_json, engine_json = excluded.engine_json, updated_at = excluded.updated_at`),
      del: this.db.prepare('DELETE FROM tournaments WHERE slug = ?'),
      event: this.db.prepare('INSERT INTO events (slug, ts, actor, type, json) VALUES (?, ?, ?, ?, ?)'),
      events: this.db.prepare('SELECT ts, actor, type, json FROM events WHERE slug = ? ORDER BY id DESC LIMIT ?'),
    };
  }

  loadAll() {
    return this.stmts.all.all().map(r => ({ slug: r.slug, tournament: JSON.parse(r.tournament_json), engine: r.engine_json ? JSON.parse(r.engine_json) : null }));
  }

  save(slug, tournamentJson, engineJson) {
    const now = Date.now();
    this.stmts.upsert.run(slug, JSON.stringify(tournamentJson), engineJson ? JSON.stringify(engineJson) : null, tournamentJson.createdAt ?? now, now);
  }

  remove(slug) { this.stmts.del.run(slug); }

  logEvent(slug, evt) { this.stmts.event.run(slug, Date.now(), evt.actor ?? null, evt.type, JSON.stringify(evt)); }

  events(slug, limit = 200) { return this.stmts.events.all(slug, limit).map(r => ({ ts: r.ts, ...JSON.parse(r.json) })); }

  close() { this.db.close(); }
}