/**
 * 星环OPC中心 — 里程碑自动检测器
 *
 * 自动检测公司成就事件，写入 opc_celebrations 表。
 * 检测逻辑确保同一成就只庆祝一次。
 *
 * 成就类型：
 * - first_revenue        第一笔收入
 * - revenue_10k          累计收入破万
 * - revenue_50k          累计收入破5万
 * - revenue_100k         累计收入破10万
 * - revenue_500k         累计收入破50万
 * - first_contract       第一份合同
 * - first_customer       第一个客户
 * - first_monthly_profit 首次月度盈利
 * - project_completed    项目完成
 * - canvas_complete      画布 100%
 * - funding_closed       融资完成
 */

import type { OpcDatabase } from "../db/index.js";

type CelebrationRow = { id: string };
type CountRow = { cnt: number };
type SumRow = { total: number };

const OPB_CANVAS_FIELDS = [
  "track", "target_customer", "pain_point", "solution",
  "unique_value", "channels", "revenue_model", "cost_structure",
  "key_resources", "key_activities", "key_partners", "unfair_advantage",
  "metrics", "non_compete", "scaling_strategy", "notes",
] as const;

/** 检查某种庆祝是否已存在（防重复） */
function celebrationExists(db: OpcDatabase, companyId: string, celebrationType: string): boolean {
  const row = db.queryOne(
    "SELECT id FROM opc_celebrations WHERE company_id = ? AND celebration_type = ?",
    companyId, celebrationType,
  ) as CelebrationRow | null;
  return row !== null;
}

/** 写入庆祝记录 */
function createCelebration(
  db: OpcDatabase,
  companyId: string,
  celebrationType: string,
  title: string,
  message: string,
  metricValue: number,
): void {
  db.execute(
    `INSERT INTO opc_celebrations (id, company_id, celebration_type, title, message, metric_value, shown, created_at)
     VALUES (?, ?, ?, ?, ?, ?, 0, datetime('now'))`,
    db.genId(), companyId, celebrationType, title, message, metricValue,
  );
}

/** 检测收入相关里程碑 */
function checkRevenueMilestones(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  const totalIncome = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
    companyId,
  ) as SumRow).total;

  if (totalIncome <= 0) return;

  // 第一笔收入
  if (!celebrationExists(db, companyId, "first_revenue")) {
    createCelebration(db, companyId, "first_revenue",
      "第一笔收入！",
      `恭喜「${companyName}」获得了第一笔收入 ${totalIncome.toLocaleString()} 元！万事开头难，这是最重要的一步。`,
      totalIncome,
    );
    log(`opc-milestone: [${companyName}] 🎉 第一笔收入 ${totalIncome.toLocaleString()} 元`);
  }

  // 收入阈值里程碑
  const thresholds: [number, string, string][] = [
    [10_000, "revenue_10k", "累计收入突破 1 万元！"],
    [50_000, "revenue_50k", "累计收入突破 5 万元！"],
    [100_000, "revenue_100k", "累计收入突破 10 万元！"],
    [500_000, "revenue_500k", "累计收入突破 50 万元！"],
  ];

  for (const [threshold, type, title] of thresholds) {
    if (totalIncome >= threshold && !celebrationExists(db, companyId, type)) {
      createCelebration(db, companyId, type, title,
        `「${companyName}」累计收入达到 ${totalIncome.toLocaleString()} 元，突破 ${(threshold / 10000).toFixed(0)} 万元大关！`,
        totalIncome,
      );
      log(`opc-milestone: [${companyName}] 🎉 ${title}`);
    }
  }
}

/** 检测首次月度盈利 */
function checkMonthlyProfit(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  if (celebrationExists(db, companyId, "first_monthly_profit")) return;

  const row = db.queryOne(
    `SELECT strftime('%Y-%m', transaction_date) as month,
       SUM(CASE WHEN type='income' THEN amount ELSE 0 END) as income,
       SUM(CASE WHEN type='expense' THEN amount ELSE 0 END) as expense
     FROM opc_transactions
     WHERE company_id = ?
     GROUP BY month
     HAVING income > 0 AND income > expense
     LIMIT 1`,
    companyId,
  ) as { month: string; income: number; expense: number } | null;

  if (row) {
    const profit = row.income - row.expense;
    createCelebration(db, companyId, "first_monthly_profit",
      "首次月度盈利！",
      `「${companyName}」在 ${row.month} 首次实现月度盈利，净利润 ${profit.toLocaleString()} 元！`,
      profit,
    );
    log(`opc-milestone: [${companyName}] 🎉 首次月度盈利 ${profit.toLocaleString()} 元`);
  }
}

/** 检测合同里程碑 */
function checkContractMilestones(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  if (celebrationExists(db, companyId, "first_contract")) return;

  const cnt = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?",
    companyId,
  ) as CountRow).cnt;

  if (cnt > 0) {
    createCelebration(db, companyId, "first_contract",
      "第一份合同！",
      `「${companyName}」签订了第一份正式合同！业务正在走上正轨。`,
      cnt,
    );
    log(`opc-milestone: [${companyName}] 🎉 第一份合同`);
  }
}

/** 检测客户里程碑 */
function checkCustomerMilestones(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  if (celebrationExists(db, companyId, "first_customer")) return;

  const cnt = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?",
    companyId,
  ) as CountRow).cnt;

  if (cnt > 0) {
    createCelebration(db, companyId, "first_customer",
      "客户池开始建立！",
      `「${companyName}」添加了第一个客户/联系人，客户池正在建立中。`,
      cnt,
    );
    log(`opc-milestone: [${companyName}] 🎉 第一个客户`);
  }
}

/** 检测项目完成里程碑 */
function checkProjectCompleted(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  // 找到近期完成的项目且尚未庆祝的
  const completedProjects = db.query(
    `SELECT id, name FROM opc_projects
     WHERE company_id = ? AND status = 'completed'`,
    companyId,
  ) as { id: string; name: string }[];

  for (const proj of completedProjects) {
    const cType = `project_completed_${proj.id}`;
    if (celebrationExists(db, companyId, cType)) continue;

    createCelebration(db, companyId, cType,
      `项目完成：${proj.name}`,
      `「${companyName}」的项目「${proj.name}」顺利完成！`,
      1,
    );
    log(`opc-milestone: [${companyName}] 🎉 项目完成: ${proj.name}`);
  }
}

/** 检测画布完成度 */
function checkCanvasComplete(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  if (celebrationExists(db, companyId, "canvas_complete")) return;

  const canvas = db.queryOne(
    "SELECT * FROM opc_opb_canvas WHERE company_id = ?",
    companyId,
  ) as Record<string, string> | null;

  if (!canvas) return;

  const filled = OPB_CANVAS_FIELDS.filter(f => canvas[f] && canvas[f].trim() !== "").length;
  if (filled >= OPB_CANVAS_FIELDS.length) {
    createCelebration(db, companyId, "canvas_complete",
      "商业画布规划完成！",
      `「${companyName}」的 OPB 商业画布 ${OPB_CANVAS_FIELDS.length} 个字段已全部填写完成！商业模式规划圆满。`,
      OPB_CANVAS_FIELDS.length,
    );
    log(`opc-milestone: [${companyName}] 🎉 OPB 画布完成`);
  }
}

/** 检测融资完成 */
function checkFundingClosed(db: OpcDatabase, companyId: string, companyName: string, log: (msg: string) => void): void {
  const closedRounds = db.query(
    "SELECT id, round_name, amount FROM opc_investment_rounds WHERE company_id = ? AND status = 'closed'",
    companyId,
  ) as { id: string; round_name: string; amount: number }[];

  for (const round of closedRounds) {
    const cType = `funding_closed_${round.id}`;
    if (celebrationExists(db, companyId, cType)) continue;

    createCelebration(db, companyId, cType,
      `融资完成：${round.round_name}`,
      `「${companyName}」的${round.round_name}成功关闭，融资金额 ${round.amount.toLocaleString()} 元！`,
      round.amount,
    );
    log(`opc-milestone: [${companyName}] 🎉 融资完成: ${round.round_name}`);
  }
}

/** 检测单个公司的所有里程碑 */
export function detectMilestones(db: OpcDatabase, companyId: string, log: (msg: string) => void): void {
  try {
    const company = db.getCompany(companyId);
    if (!company) return;
    const name = company.name;

    checkRevenueMilestones(db, companyId, name, log);
    checkMonthlyProfit(db, companyId, name, log);
    checkContractMilestones(db, companyId, name, log);
    checkCustomerMilestones(db, companyId, name, log);
    checkProjectCompleted(db, companyId, name, log);
    checkCanvasComplete(db, companyId, name, log);
    checkFundingClosed(db, companyId, name, log);
  } catch (err) {
    log(`opc-milestone: 检测异常 [${companyId}]: ${err instanceof Error ? err.message : String(err)}`);
  }
}

/** 检测所有公司的里程碑 */
export function detectMilestonesForAll(db: OpcDatabase, log: (msg: string) => void): void {
  const companies = db.query("SELECT id FROM opc_companies") as { id: string }[];
  for (const c of companies) {
    detectMilestones(db, c.id, log);
  }
}