/**
 * Tournament SQLite persistence layer.
 * Manages rounds, strategies, agent records, and user picks.
 * @module openfinclaw/tournament/db
 */
import type { DatabaseSync } from "node:sqlite";

// ── Types ─────────────────────────────────────────────────────────────────

export interface TournamentRound {
  id: string;
  date: string;
  ticker: string;
  status: "pending" | "running" | "completed" | "skipped";
  started_at: string | null;
  completed_at: string | null;
  skip_reason: string | null;
}

export interface TournamentStrategy {
  round_id: string;
  agent_name: string;
  thesis: string;
  entry_price: number | null;
  exit_price: number | null;
  stop_loss: number | null;
  position_pct: number | null;
  confidence: number;
  sharpe: number | null;
  max_drawdown: number | null;
  total_return: number | null;
  raw_result: string | null;
  created_at: string;
}

export interface TournamentAgent {
  name: string;
  wins: number;
  losses: number;
  avg_sharpe: number;
  rounds_played: number;
}

export interface TournamentPick {
  round_id: string;
  user_id: string;
  session_key: string;
  agent_name: string;
  picked_at: string;
}

// ── Schema ────────────────────────────────────────────────────────────────

/** Create tournament tables. Safe to call multiple times. */
export function ensureTournamentSchema(db: DatabaseSync): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS tournament_rounds (
      id           TEXT PRIMARY KEY,
      date         TEXT NOT NULL,
      ticker       TEXT NOT NULL,
      status       TEXT NOT NULL DEFAULT 'pending',
      started_at   TEXT,
      completed_at TEXT,
      skip_reason  TEXT
    );
  `);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_tournament_rounds_date ON tournament_rounds(date);`);

  db.exec(`
    CREATE TABLE IF NOT EXISTS tournament_strategies (
      round_id     TEXT NOT NULL,
      agent_name   TEXT NOT NULL,
      thesis       TEXT NOT NULL,
      entry_price  REAL,
      exit_price   REAL,
      stop_loss    REAL,
      position_pct REAL,
      confidence   INTEGER NOT NULL DEFAULT 50,
      sharpe       REAL,
      max_drawdown REAL,
      total_return REAL,
      raw_result   TEXT,
      created_at   TEXT NOT NULL,
      PRIMARY KEY (round_id, agent_name)
    );
  `);

  db.exec(`
    CREATE TABLE IF NOT EXISTS tournament_agents (
      name          TEXT PRIMARY KEY,
      wins          INTEGER NOT NULL DEFAULT 0,
      losses        INTEGER NOT NULL DEFAULT 0,
      avg_sharpe    REAL NOT NULL DEFAULT 0,
      rounds_played INTEGER NOT NULL DEFAULT 0
    );
  `);

  db.exec(`
    CREATE TABLE IF NOT EXISTS tournament_picks (
      round_id     TEXT NOT NULL,
      user_id      TEXT NOT NULL,
      session_key  TEXT NOT NULL,
      agent_name   TEXT NOT NULL,
      picked_at    TEXT NOT NULL,
      PRIMARY KEY (round_id, user_id)
    );
  `);
}

// ── CRUD Operations ───────────────────────────────────────────────────────

export class TournamentDb {
  constructor(private db: DatabaseSync) {}

  // ── Rounds ──────────────────────────────────────────────────────────

  /** Create a new tournament round. Returns false if already exists. */
  createRound(round: { id: string; date: string; ticker: string }): boolean {
    const existing = this.getRound(round.id);
    if (existing) return false;

    const now = new Date().toISOString();
    this.db
      .prepare(
        "INSERT INTO tournament_rounds (id, date, ticker, status, started_at) VALUES (?, ?, ?, 'running', ?)",
      )
      .run(round.id, round.date, round.ticker, now);
    return true;
  }

  /** Get a round by ID. */
  getRound(id: string): TournamentRound | undefined {
    return this.db.prepare("SELECT * FROM tournament_rounds WHERE id = ?").get(id) as
      | TournamentRound
      | undefined;
  }

  /** Update round status. */
  updateRoundStatus(id: string, status: TournamentRound["status"], skipReason?: string): void {
    const now = new Date().toISOString();
    if (status === "completed" || status === "skipped") {
      this.db
        .prepare(
          "UPDATE tournament_rounds SET status = ?, completed_at = ?, skip_reason = ? WHERE id = ?",
        )
        .run(status, now, skipReason ?? null, id);
    } else {
      this.db.prepare("UPDATE tournament_rounds SET status = ? WHERE id = ?").run(status, id);
    }
  }

  /** Get the latest N rounds ordered by date desc. */
  getRecentRounds(limit: number): TournamentRound[] {
    return this.db
      .prepare("SELECT * FROM tournament_rounds ORDER BY date DESC LIMIT ?")
      .all(limit) as TournamentRound[];
  }

  /** Count consecutive skipped rounds from the most recent. */
  countConsecutiveSkips(): number {
    const recent = this.getRecentRounds(10);
    let count = 0;
    for (const r of recent) {
      if (r.status === "skipped") count++;
      else break;
    }
    return count;
  }

  // ── Strategies ──────────────────────────────────────────────────────

  /** Save a strategy result from a subagent. Upserts by (round_id, agent_name). */
  saveStrategy(strategy: Omit<TournamentStrategy, "created_at">): void {
    const now = new Date().toISOString();
    this.db
      .prepare(
        `INSERT OR REPLACE INTO tournament_strategies
         (round_id, agent_name, thesis, entry_price, exit_price, stop_loss,
          position_pct, confidence, sharpe, max_drawdown, total_return, raw_result, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(
        strategy.round_id,
        strategy.agent_name,
        strategy.thesis,
        strategy.entry_price ?? null,
        strategy.exit_price ?? null,
        strategy.stop_loss ?? null,
        strategy.position_pct ?? null,
        strategy.confidence,
        strategy.sharpe ?? null,
        strategy.max_drawdown ?? null,
        strategy.total_return ?? null,
        strategy.raw_result ?? null,
        now,
      );
  }

  /** Get all strategies for a round. */
  getStrategies(roundId: string): TournamentStrategy[] {
    return this.db
      .prepare("SELECT * FROM tournament_strategies WHERE round_id = ? ORDER BY agent_name")
      .all(roundId) as TournamentStrategy[];
  }

  // ── Agents ──────────────────────────────────────────────────────────

  /** Ensure an agent record exists. */
  ensureAgent(name: string): void {
    this.db.prepare("INSERT OR IGNORE INTO tournament_agents (name) VALUES (?)").run(name);
  }

  /** Record a win for an agent. Updates avg_sharpe from the strategy's backtest. */
  recordWin(name: string, sharpe: number | null): void {
    this.ensureAgent(name);
    const agent = this.getAgent(name)!;
    const newRounds = agent.rounds_played + 1;
    const newSharpe =
      sharpe != null
        ? (agent.avg_sharpe * agent.rounds_played + sharpe) / newRounds
        : agent.avg_sharpe;
    this.db
      .prepare(
        "UPDATE tournament_agents SET wins = wins + 1, rounds_played = ?, avg_sharpe = ? WHERE name = ?",
      )
      .run(newRounds, newSharpe, name);
  }

  /** Record a loss for an agent. */
  recordLoss(name: string, sharpe: number | null): void {
    this.ensureAgent(name);
    const agent = this.getAgent(name)!;
    const newRounds = agent.rounds_played + 1;
    const newSharpe =
      sharpe != null
        ? (agent.avg_sharpe * agent.rounds_played + sharpe) / newRounds
        : agent.avg_sharpe;
    this.db
      .prepare(
        "UPDATE tournament_agents SET losses = losses + 1, rounds_played = ?, avg_sharpe = ? WHERE name = ?",
      )
      .run(newRounds, newSharpe, name);
  }

  /** Get agent stats. */
  getAgent(name: string): TournamentAgent | undefined {
    return this.db.prepare("SELECT * FROM tournament_agents WHERE name = ?").get(name) as
      | TournamentAgent
      | undefined;
  }

  /** Get all agents sorted by avg_sharpe desc. */
  getLeaderboard(): TournamentAgent[] {
    return this.db
      .prepare("SELECT * FROM tournament_agents ORDER BY avg_sharpe DESC, wins DESC")
      .all() as TournamentAgent[];
  }

  // ── Picks ───────────────────────────────────────────────────────────

  /** Record a user's pick. Idempotent per (round_id, user_id). Returns true if new. */
  recordPick(pick: Omit<TournamentPick, "picked_at">): boolean {
    const existing = this.db
      .prepare("SELECT * FROM tournament_picks WHERE round_id = ? AND user_id = ?")
      .get(pick.round_id, pick.user_id) as TournamentPick | undefined;
    if (existing) return false;

    const now = new Date().toISOString();
    this.db
      .prepare(
        "INSERT INTO tournament_picks (round_id, user_id, session_key, agent_name, picked_at) VALUES (?, ?, ?, ?, ?)",
      )
      .run(pick.round_id, pick.user_id, pick.session_key, pick.agent_name, now);
    return true;
  }

  /** Get the most recent completed round ID. */
  getLatestCompletedRoundId(): string | undefined {
    const row = this.db
      .prepare(
        "SELECT id FROM tournament_rounds WHERE status = 'completed' ORDER BY date DESC LIMIT 1",
      )
      .get() as { id: string } | undefined;
    return row?.id;
  }
}