/**
 * SQLite schema for OpenFinClaw plugin (MVP 4 tables).
 * Based on ER diagram v0.3.
 */
import type { DatabaseSync } from "node:sqlite";
import { ensureTournamentSchema } from "../tournament/db.js";

/**
 * Create (or migrate) all MVP tables.
 * Safe to call multiple times — uses CREATE TABLE IF NOT EXISTS.
 */
export function ensureSchema(db: DatabaseSync): void {
  // ── strategies ────────────────────────────────────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS strategies (
      id                   TEXT PRIMARY KEY,
      name                 TEXT NOT NULL,
      template_id          TEXT,
      level                TEXT DEFAULT 'L0',
      status               TEXT NOT NULL DEFAULT 'draft',
      symbols              TEXT,
      timeframes           TEXT,
      markets              TEXT,
      exchange_id          TEXT,
      parameters           TEXT,
      definition           TEXT,
      version              INTEGER NOT NULL DEFAULT 1,
      created_at           TEXT NOT NULL,
      updated_at           TEXT NOT NULL,
      promoted_at          TEXT,
      last_backtest_id     TEXT,
      last_paper_session_id TEXT,
      tags                 TEXT
    );
  `);

  // ── backtest_results ──────────────────────────────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS backtest_results (
      id               TEXT PRIMARY KEY,
      strategy_id      TEXT NOT NULL,
      remote_task_id   TEXT,
      status           TEXT NOT NULL DEFAULT 'pending',
      total_return     REAL,
      sharpe           REAL,
      sortino          REAL,
      max_drawdown     REAL,
      win_rate         REAL,
      profit_factor    REAL,
      total_trades     INTEGER,
      final_equity     REAL,
      initial_capital  REAL,
      equity_curve     TEXT,
      trade_journal    TEXT,
      monthly_returns  TEXT,
      tearsheet_html   TEXT,
      submitted_at     TEXT,
      completed_at     TEXT,
      created_at       TEXT NOT NULL,
      FOREIGN KEY (strategy_id) REFERENCES strategies(id)
    );
  `);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_br_strategy_id ON backtest_results(strategy_id);`);

  // ── agent_activity_log ────────────────────────────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS agent_activity_log (
      id            TEXT PRIMARY KEY,
      timestamp     TEXT NOT NULL,
      category      TEXT NOT NULL,
      action        TEXT NOT NULL,
      strategy_id   TEXT,
      detail        TEXT,
      metadata_json TEXT
    );
  `);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_aal_timestamp ON agent_activity_log(timestamp);`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_aal_category ON agent_activity_log(category);`);

  // ── agent_events ──────────────────────────────────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS agent_events (
      id                TEXT PRIMARY KEY,
      type              TEXT NOT NULL,
      title             TEXT NOT NULL,
      detail            TEXT,
      timestamp         TEXT NOT NULL,
      status            TEXT,
      action_params_json TEXT,
      narration         TEXT,
      feed_type         TEXT,
      chips_json        TEXT,
      sparkline_json    TEXT,
      category          TEXT,
      severity          TEXT,
      strategy_id       TEXT,
      reasoning         TEXT
    );
  `);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_ae_timestamp ON agent_events(timestamp);`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_ae_strategy_id ON agent_events(strategy_id);`);

  // ── scan_history ─────────────────────────────────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS scan_history (
      id                  TEXT PRIMARY KEY,
      scan_type           TEXT NOT NULL,
      started_at          TEXT NOT NULL,
      completed_at        TEXT,
      status              TEXT NOT NULL DEFAULT 'running',
      strategies_scanned  INTEGER DEFAULT 0,
      news_found          INTEGER DEFAULT 0,
      actions_taken       INTEGER DEFAULT 0,
      summary             TEXT,
      detail_json         TEXT
    );
  `);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_scan_history_started ON scan_history(started_at);`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_scan_history_type ON scan_history(scan_type);`);

  // ── price_alerts ────────────────────────────────────────────────────────
  db.exec(`
    CREATE TABLE IF NOT EXISTS price_alerts (
      id              TEXT PRIMARY KEY,
      strategy_id     TEXT NOT NULL,
      symbol          TEXT NOT NULL,
      alert_type      TEXT NOT NULL,
      trigger_value   REAL,
      threshold       REAL,
      message         TEXT,
      created_at      TEXT NOT NULL,
      acknowledged    INTEGER DEFAULT 0
    );
  `);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_price_alerts_created ON price_alerts(created_at);`);
  db.exec(`CREATE INDEX IF NOT EXISTS idx_price_alerts_strategy ON price_alerts(strategy_id);`);

  // ── tournament tables ──────────────────────────────────────────────────
  ensureTournamentSchema(db);

  // ── migrations (add columns to existing databases) ──────────────────────
}

/**
 * Purge old rows from accumulating tables.
 * Safe to call periodically (e.g. on plugin register or via cron).
 * Keeps 90 days of activity logs, scan history, and acknowledged price alerts.
 */
export function cleanupOldRows(db: DatabaseSync): void {
  const cutoff = new Date(Date.now() - 90 * 24 * 60 * 60 * 1000).toISOString();
  try {
    db.exec(`DELETE FROM agent_activity_log WHERE timestamp < '${cutoff}'`);
    db.exec(`DELETE FROM scan_history WHERE started_at < '${cutoff}'`);
    db.exec(`DELETE FROM price_alerts WHERE acknowledged = 1 AND created_at < '${cutoff}'`);
    db.exec(`DELETE FROM agent_events WHERE timestamp < '${cutoff}'`);
  } catch {
    // Cleanup is best-effort — never crash the plugin
  }
}

/** Add a column if it doesn't already exist (safe for repeated calls). */
function ensureColumn(db: DatabaseSync, table: string, column: string, definition: string): void {
  const rows = db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>;
  if (rows.some((r) => r.name === column)) return;
  db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
}