/**
 * 星环OPC中心 — 公司阶段检测器
 *
 * 根据数据信号自动判断公司所处发展阶段，写入 opc_company_stage 表。
 *
 * 阶段定义：
 * - idea       构想阶段：无画布、无交易、无客户
 * - validation 验证阶段：有画布或客户或项目，但无收入
 * - early_revenue 初始营收：有收入但 < 2 个月
 * - growth     增长阶段：收入 > 1万，有收入月份 ≥ 2
 * - stable     稳定运营：收入 > 10万，有收入月份 ≥ 3，活跃合同 ≥ 2
 * - scaling    规模化：收入 > 50万，有收入月份 ≥ 6，员工 ≥ 3
 * - exit       退出阶段：公司状态为 acquired/packaged
 */

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

export type CompanyStage =
  | "idea"
  | "validation"
  | "early_revenue"
  | "growth"
  | "stable"
  | "scaling"
  | "exit";

export interface StageResult {
  stage: CompanyStage;
  stageLabel: string;
  confidence: number;
  factors: Record<string, unknown>;
}

const STAGE_LABELS: Record<CompanyStage, string> = {
  idea: "构想阶段",
  validation: "验证阶段",
  early_revenue: "初始营收",
  growth: "增长阶段",
  stable: "稳定运营",
  scaling: "规模化",
  exit: "退出阶段",
};

type CountRow = { cnt: number };
type SumRow = { total: number };
type MonthRow = { month_count: number };

/** 检测单个公司的发展阶段 */
export function detectCompanyStage(db: OpcDatabase, companyId: string): StageResult {
  const factors: Record<string, unknown> = {};

  // 基础信号采集
  const company = db.getCompany(companyId);
  if (!company) {
    return { stage: "idea", stageLabel: STAGE_LABELS.idea, confidence: 0, factors: {} };
  }

  // 退出阶段判定（优先级最高）
  const companyStatus = company.status;
  if (companyStatus === "acquired" || companyStatus === "packaged") {
    factors.status = companyStatus;
    return { stage: "exit", stageLabel: STAGE_LABELS.exit, confidence: 1.0, factors };
  }

  // 数据信号
  const totalIncome = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
    companyId,
  ) as SumRow).total;

  const revenueMonths = (db.queryOne(
    `SELECT COUNT(DISTINCT strftime('%Y-%m', transaction_date)) as month_count
     FROM opc_transactions WHERE company_id = ? AND type = 'income' AND amount > 0`,
    companyId,
  ) as MonthRow).month_count;

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

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

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

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

  const hrCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_hr_records WHERE company_id = ? AND status = 'active'",
    companyId,
  ) as CountRow).cnt;

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

  factors.totalIncome = totalIncome;
  factors.revenueMonths = revenueMonths;
  factors.hasCanvas = hasCanvas;
  factors.contactCount = contactCount;
  factors.projectCount = projectCount;
  factors.activeContracts = activeContracts;
  factors.hrCount = hrCount;
  factors.transactionCount = transactionCount;

  // 阶段判定（从高到低）
  if (totalIncome > 500_000 && revenueMonths >= 6 && hrCount >= 3) {
    return { stage: "scaling", stageLabel: STAGE_LABELS.scaling, confidence: 0.9, factors };
  }

  if (totalIncome > 100_000 && revenueMonths >= 3 && activeContracts >= 2) {
    return { stage: "stable", stageLabel: STAGE_LABELS.stable, confidence: 0.85, factors };
  }

  if (totalIncome > 10_000 && revenueMonths >= 2) {
    return { stage: "growth", stageLabel: STAGE_LABELS.growth, confidence: 0.8, factors };
  }

  if (totalIncome > 0) {
    return { stage: "early_revenue", stageLabel: STAGE_LABELS.early_revenue, confidence: 0.8, factors };
  }

  if (hasCanvas || contactCount > 0 || projectCount > 0 || transactionCount > 0) {
    return { stage: "validation", stageLabel: STAGE_LABELS.validation, confidence: 0.7, factors };
  }

  return { stage: "idea", stageLabel: STAGE_LABELS.idea, confidence: 0.6, factors };
}

/** 更新单个公司的阶段缓存到 opc_company_stage */
export function updateCompanyStage(db: OpcDatabase, companyId: string): StageResult {
  const result = detectCompanyStage(db, companyId);
  const now = new Date().toISOString();

  db.execute(
    `INSERT INTO opc_company_stage (company_id, stage, stage_label, confidence, factors_json, updated_at)
     VALUES (?, ?, ?, ?, ?, ?)
     ON CONFLICT(company_id) DO UPDATE SET
       stage = excluded.stage,
       stage_label = excluded.stage_label,
       confidence = excluded.confidence,
       factors_json = excluded.factors_json,
       updated_at = excluded.updated_at`,
    companyId, result.stage, result.stageLabel, result.confidence,
    JSON.stringify(result.factors), now,
  );

  return result;
}

/** 重新计算所有公司的阶段 */
export function recalculateAllStages(db: OpcDatabase, log: (msg: string) => void): void {
  const companies = db.query("SELECT id FROM opc_companies") as { id: string }[];
  for (const c of companies) {
    const result = updateCompanyStage(db, c.id);
    log(`opc-stage: [${c.id}] → ${result.stageLabel}`);
  }
}