/**
 * Data access helpers for OpenFinClaw plugin SQLite tables.
 */
import type { DatabaseSync, SQLInputValue } from "node:sqlite";

/** Cast unknown[] to SQLInputValue[] for db.prepare().run() spread. */
function sqlParams(values: unknown[]): SQLInputValue[] {
  return values as SQLInputValue[];
}

// ── Row types ─────────────────────────────────────────────────────────────

export interface ActivityLogEntry {
  id: string;
  timestamp: string;
  category: string;
  action: string;
  strategy_id?: string | null;
  detail?: string | null;
  metadata_json?: string | null;
}

export interface AgentEventEntry {
  id: string;
  type: string;
  title: string;
  detail?: string | null;
  timestamp: string;
  status?: string | null;
  action_params_json?: string | null;
  narration?: string | null;
  feed_type?: string | null;
  chips_json?: string | null;
  sparkline_json?: string | null;
  category?: string | null;
  severity?: string | null;
  strategy_id?: string | null;
  reasoning?: string | null;
}

/**
 * Strategy lifecycle levels:
 * - L0: 创建 / Fork（尚未回测）
 * - L1: 回测中 / 回测完成
 * - L2: 模拟盘运行中（预留）
 * - L3: 实盘运行中（预留）
 */
export type StrategyLevel = "L0" | "L1" | "L2" | "L3";

export interface StrategyRow {
  id: string;
  name: string;
  template_id?: string | null;
  /** 策略生命周期阶段：L0 创建 → L1 回测 → L2 模拟盘 → L3 实盘 */
  level?: StrategyLevel | null;
  status: string;
  symbols?: string | null;
  timeframes?: string | null;
  markets?: string | null;
  exchange_id?: string | null;
  parameters?: string | null;
  definition?: string | null;
  version: number;
  created_at: string;
  updated_at: string;
  promoted_at?: string | null;
  last_backtest_id?: string | null;
  last_paper_session_id?: string | null;
  tags?: string | null;
}

export interface BacktestRow {
  id: string;
  strategy_id: string;
  remote_task_id?: string | null;
  status: string;
  total_return?: number | null;
  sharpe?: number | null;
  sortino?: number | null;
  max_drawdown?: number | null;
  win_rate?: number | null;
  profit_factor?: number | null;
  total_trades?: number | null;
  final_equity?: number | null;
  initial_capital?: number | null;
  equity_curve?: string | null;
  trade_journal?: string | null;
  monthly_returns?: string | null;
  tearsheet_html?: string | null;
  submitted_at?: string | null;
  completed_at?: string | null;
  created_at: string;
}

// ── Writes ────────────────────────────────────────────────────────────────

/** Insert one row into agent_activity_log. Silently swallows errors to never interrupt tools. */
export function insertActivityLog(db: DatabaseSync, entry: ActivityLogEntry): void {
  try {
    db.prepare(`
      INSERT INTO agent_activity_log
        (id, timestamp, category, action, strategy_id, detail, metadata_json)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).run(
      entry.id,
      entry.timestamp,
      entry.category,
      entry.action,
      entry.strategy_id ?? null,
      entry.detail ?? null,
      entry.metadata_json ?? null,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Insert one row into agent_events. Silently swallows errors. */
export function insertAgentEvent(db: DatabaseSync, event: AgentEventEntry): void {
  try {
    db.prepare(`
      INSERT INTO agent_events
        (id, type, title, detail, timestamp, status, action_params_json,
         narration, feed_type, chips_json, sparkline_json, category, severity,
         strategy_id, reasoning)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      event.id,
      event.type,
      event.title,
      event.detail ?? null,
      event.timestamp,
      event.status ?? null,
      event.action_params_json ?? null,
      event.narration ?? null,
      event.feed_type ?? null,
      event.chips_json ?? null,
      event.sparkline_json ?? null,
      event.category ?? null,
      event.severity ?? null,
      event.strategy_id ?? null,
      event.reasoning ?? null,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Insert or replace a strategy row. */
export function upsertStrategy(db: DatabaseSync, row: StrategyRow): void {
  try {
    db.prepare(`
      INSERT INTO strategies
        (id, name, template_id, level, status, symbols, timeframes, markets,
         exchange_id, parameters, definition, version, created_at, updated_at,
         promoted_at, last_backtest_id, last_paper_session_id, tags)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(id) DO UPDATE SET
        name                  = excluded.name,
        template_id           = excluded.template_id,
        level                 = excluded.level,
        status                = excluded.status,
        symbols               = excluded.symbols,
        timeframes            = excluded.timeframes,
        markets               = excluded.markets,
        exchange_id           = excluded.exchange_id,
        parameters            = excluded.parameters,
        definition            = excluded.definition,
        version               = excluded.version,
        updated_at            = excluded.updated_at,
        promoted_at           = excluded.promoted_at,
        last_backtest_id      = excluded.last_backtest_id,
        last_paper_session_id = excluded.last_paper_session_id,
        tags                  = excluded.tags
    `).run(
      row.id,
      row.name,
      row.template_id ?? null,
      row.level ?? null,
      row.status,
      row.symbols ?? null,
      row.timeframes ?? null,
      row.markets ?? null,
      row.exchange_id ?? null,
      row.parameters ?? null,
      row.definition ?? null,
      row.version,
      row.created_at,
      row.updated_at,
      row.promoted_at ?? null,
      row.last_backtest_id ?? null,
      row.last_paper_session_id ?? null,
      row.tags ?? null,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Insert a backtest result row. */
export function insertBacktestResult(db: DatabaseSync, row: BacktestRow): void {
  try {
    db.prepare(`
      INSERT INTO backtest_results
        (id, strategy_id, remote_task_id, status, total_return, sharpe, sortino,
         max_drawdown, win_rate, profit_factor, total_trades, final_equity,
         initial_capital, equity_curve, trade_journal, monthly_returns,
         tearsheet_html, submitted_at, completed_at, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      row.id,
      row.strategy_id,
      row.remote_task_id ?? null,
      row.status,
      row.total_return ?? null,
      row.sharpe ?? null,
      row.sortino ?? null,
      row.max_drawdown ?? null,
      row.win_rate ?? null,
      row.profit_factor ?? null,
      row.total_trades ?? null,
      row.final_equity ?? null,
      row.initial_capital ?? null,
      row.equity_curve ?? null,
      row.trade_journal ?? null,
      row.monthly_returns ?? null,
      row.tearsheet_html ?? null,
      row.submitted_at ?? null,
      row.completed_at ?? null,
      row.created_at,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Update backtest_results metrics after publish_verify completes. */
export function updateBacktestResult(
  db: DatabaseSync,
  id: string,
  patch: Partial<
    Pick<
      BacktestRow,
      | "status"
      | "total_return"
      | "sharpe"
      | "sortino"
      | "max_drawdown"
      | "win_rate"
      | "profit_factor"
      | "total_trades"
      | "final_equity"
      | "equity_curve"
      | "trade_journal"
      | "monthly_returns"
      | "tearsheet_html"
      | "completed_at"
    >
  >,
): void {
  try {
    const sets: string[] = [];
    const values: unknown[] = [];
    for (const [k, v] of Object.entries(patch)) {
      sets.push(`${k} = ?`);
      values.push(v ?? null);
    }
    if (sets.length === 0) return;
    values.push(id);
    db.prepare(`UPDATE backtest_results SET ${sets.join(", ")} WHERE id = ?`).run(...sqlParams(values));
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Update strategy level by id. */
export function updateStrategyLevel(db: DatabaseSync, id: string, level: StrategyLevel): void {
  try {
    db.prepare(`UPDATE strategies SET level = ?, updated_at = ? WHERE id = ?`).run(
      level,
      new Date().toISOString(),
      id,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

// ── Scan History ──────────────────────────────────────────────────────────

export interface ScanHistoryEntry {
  id: string;
  scan_type: string;
  started_at: string;
  completed_at?: string | null;
  status: string;
  strategies_scanned: number;
  news_found: number;
  actions_taken: number;
  summary?: string | null;
  detail_json?: string | null;
}

/** Insert a scan history entry. */
export function insertScanHistory(db: DatabaseSync, entry: ScanHistoryEntry): void {
  try {
    db.prepare(`
      INSERT INTO scan_history
        (id, scan_type, started_at, completed_at, status,
         strategies_scanned, news_found, actions_taken, summary, detail_json)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      entry.id,
      entry.scan_type,
      entry.started_at,
      entry.completed_at ?? null,
      entry.status,
      entry.strategies_scanned,
      entry.news_found,
      entry.actions_taken,
      entry.summary ?? null,
      entry.detail_json ?? null,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Update scan history entry after completion. */
export function updateScanHistory(
  db: DatabaseSync,
  id: string,
  patch: Partial<
    Pick<
      ScanHistoryEntry,
      | "completed_at"
      | "status"
      | "strategies_scanned"
      | "news_found"
      | "actions_taken"
      | "summary"
      | "detail_json"
    >
  >,
): void {
  try {
    const sets: string[] = [];
    const values: unknown[] = [];
    for (const [k, v] of Object.entries(patch)) {
      sets.push(`${k} = ?`);
      values.push(v ?? null);
    }
    if (sets.length === 0) return;
    values.push(id);
    db.prepare(`UPDATE scan_history SET ${sets.join(", ")} WHERE id = ?`).run(...sqlParams(values));
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Query scan history, newest first. Optionally filter by scan_type and started_at lower bound (ISO 8601). */
export function queryScanHistory(
  db: DatabaseSync,
  opts: {
    scanType?: string;
    startedAfter?: string;
    limit?: number;
    offset?: number;
  } = {},
): ScanHistoryEntry[] {
  const { limit = 20, offset = 0, scanType, startedAfter } = opts;
  const conditions: string[] = [];
  const params: unknown[] = [];
  if (scanType) {
    conditions.push("scan_type = ?");
    params.push(scanType);
  }
  if (startedAfter) {
    conditions.push("started_at >= ?");
    params.push(startedAfter);
  }
  const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  params.push(limit, offset);
  return db
    .prepare(`SELECT * FROM scan_history ${where} ORDER BY started_at DESC LIMIT ? OFFSET ?`)
    .all(...sqlParams(params)) as unknown as ScanHistoryEntry[];
}

/**
 * Count scan_history rows grouped by scan_type since an inclusive time bound (ISO 8601 started_at).
 */
export function countScanHistoryByTypeSince(
  db: DatabaseSync,
  startedAfter: string,
): Map<string, number> {
  const rows = db
    .prepare(
      `SELECT scan_type, COUNT(*) AS cnt FROM scan_history WHERE started_at >= ? GROUP BY scan_type`,
    )
    .all(startedAfter) as Array<{ scan_type: string; cnt: number }>;
  const map = new Map<string, number>();
  for (const r of rows) {
    map.set(r.scan_type, Number(r.cnt));
  }
  return map;
}

// ── Price Alerts ──────────────────────────────────────────────────────────

export interface PriceAlertEntry {
  id: string;
  strategy_id: string;
  symbol: string;
  alert_type: string;
  trigger_value?: number | null;
  threshold?: number | null;
  message?: string | null;
  created_at: string;
  acknowledged: number;
}

/** Insert a price alert. */
export function insertPriceAlert(db: DatabaseSync, alert: PriceAlertEntry): void {
  try {
    db.prepare(`
      INSERT INTO price_alerts
        (id, strategy_id, symbol, alert_type, trigger_value, threshold, message, created_at, acknowledged)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      alert.id,
      alert.strategy_id,
      alert.symbol,
      alert.alert_type,
      alert.trigger_value ?? null,
      alert.threshold ?? null,
      alert.message ?? null,
      alert.created_at,
      alert.acknowledged,
    );
  } catch {
    // Logging must never crash the calling tool
  }
}

/** Query price alerts, newest first. Optionally filter by strategy and created_at lower bound (ISO 8601). */
export function queryPriceAlerts(
  db: DatabaseSync,
  opts: {
    strategyId?: string;
    createdAfter?: string;
    limit?: number;
    offset?: number;
  } = {},
): PriceAlertEntry[] {
  const { limit = 50, offset = 0, strategyId, createdAfter } = opts;
  const conditions: string[] = [];
  const params: unknown[] = [];
  if (strategyId) {
    conditions.push("strategy_id = ?");
    params.push(strategyId);
  }
  if (createdAfter) {
    conditions.push("created_at >= ?");
    params.push(createdAfter);
  }
  const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  params.push(limit, offset);
  return db
    .prepare(`SELECT * FROM price_alerts ${where} ORDER BY created_at DESC LIMIT ? OFFSET ?`)
    .all(...sqlParams(params)) as unknown as PriceAlertEntry[];
}

/** Count price_alerts rows since an inclusive time bound (ISO 8601 created_at). */
export function countPriceAlertsSince(db: DatabaseSync, createdAfter: string): number {
  const row = db
    .prepare(`SELECT COUNT(*) AS cnt FROM price_alerts WHERE created_at >= ?`)
    .get(createdAfter) as { cnt: number } | undefined;
  return Number(row?.cnt ?? 0);
}

/** Mark a price alert as acknowledged. */
export function acknowledgePriceAlert(db: DatabaseSync, id: string): void {
  try {
    db.prepare(`UPDATE price_alerts SET acknowledged = 1 WHERE id = ?`).run(id);
  } catch {
    // Logging must never crash the calling tool
  }
}

// ── Reads ─────────────────────────────────────────────────────────────────

/** Query agent_activity_log, newest first. */
export function queryActivityLog(db: DatabaseSync, limit = 50, offset = 0): ActivityLogEntry[] {
  return db
    .prepare(`SELECT * FROM agent_activity_log ORDER BY timestamp DESC LIMIT ? OFFSET ?`)
    .all(limit, offset) as unknown as ActivityLogEntry[];
}

/** Query agent_events, newest first. */
export function queryAgentEvents(db: DatabaseSync, limit = 50, offset = 0): AgentEventEntry[] {
  return db
    .prepare(`SELECT * FROM agent_events ORDER BY timestamp DESC LIMIT ? OFFSET ?`)
    .all(limit, offset) as unknown as AgentEventEntry[];
}

/** Query all strategies, newest first. */
export function queryStrategies(db: DatabaseSync): StrategyRow[] {
  return db.prepare(`SELECT * FROM strategies ORDER BY updated_at DESC`).all() as unknown as StrategyRow[];
}

/** Query backtest results, optionally filtered by strategy_id. */
export function queryBacktestResults(db: DatabaseSync, strategyId?: string): BacktestRow[] {
  if (strategyId) {
    return db
      .prepare(`SELECT * FROM backtest_results WHERE strategy_id = ? ORDER BY created_at DESC`)
      .all(strategyId) as unknown as BacktestRow[];
  }
  return db
    .prepare(`SELECT * FROM backtest_results ORDER BY created_at DESC`)
    .all() as unknown as BacktestRow[];
}