/**
 * 星环OPC中心 — 智能分析引擎
 *
 * P0 阶段实现 3 个分析器：
 * 1. analyzeDataGaps   — 检查缺失数据并建议补全
 * 2. analyzeFinanceTrends — 财务趋势分析
 * 3. generateNextSteps — 根据阶段生成建议
 *
 * 所有洞察写入 opc_insights 表，带防重复和自动过期逻辑。
 */

import type { OpcDatabase } from "../db/index.js";
import { detectCompanyStage, type CompanyStage } from "./stage-detector.js";

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

// ── 过期天数配置 ──────────────────────────────────────────────────

const EXPIRY_DAYS: Record<string, number> = {
  data_gap: 7,
  trend: 3,
  next_step: 14,
  staff_observation: 7,
  risk: 3,
  opportunity: 7,
};

// ── 防重复 ────────────────────────────────────────────────────────

/** 同 company_id + insight_type + title 前 20 字符在 7 天内不重复写入 */
function insightExists(db: OpcDatabase, companyId: string, insightType: string, title: string): boolean {
  const titleKey = title.slice(0, 20);
  const cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - 7);
  const cutoffStr = cutoff.toISOString();

  const row = db.queryOne(
    `SELECT id FROM opc_insights
     WHERE company_id = ? AND insight_type = ? AND SUBSTR(title, 1, 20) = ? AND created_at >= ? AND status = 'active'`,
    companyId, insightType, titleKey, cutoffStr,
  ) as InsightRow | null;
  return row !== null;
}

/** 写入洞察 */
function createInsight(
  db: OpcDatabase,
  companyId: string,
  params: {
    insightType: string;
    category: string;
    priority: number;
    title: string;
    message: string;
    actionHint?: string;
    staffRole?: string;
  },
): void {
  if (insightExists(db, companyId, params.insightType, params.title)) return;

  const expiryDays = EXPIRY_DAYS[params.insightType] ?? 7;
  const expiresAt = new Date();
  expiresAt.setDate(expiresAt.getDate() + expiryDays);
  const now = new Date().toISOString();

  db.execute(
    `INSERT INTO opc_insights
     (id, company_id, insight_type, category, priority, title, message, action_hint, staff_role, status, expires_at, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, ?, ?)`,
    db.genId(), companyId, params.insightType, params.category, params.priority,
    params.title, params.message, params.actionHint ?? "", params.staffRole ?? "",
    expiresAt.toISOString(), now, now,
  );
}

// ── 分析器 1: 数据缺口分析 ───────────────────────────────────────

function analyzeDataGaps(db: OpcDatabase, companyId: string): void {
  // 无交易
  const txCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_transactions WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (txCount === 0) {
    createInsight(db, companyId, {
      insightType: "data_gap", category: "finance", priority: 80,
      title: "还没有记录收支",
      message: "公司目前没有任何收支记录。建议记录第一笔交易，无论是收入还是支出，这是财务管理的第一步。",
      actionHint: "使用 opc_finance 工具的 record_transaction 记录第一笔收支",
    });
  }

  // 无客户
  const contactCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (contactCount === 0) {
    createInsight(db, companyId, {
      insightType: "data_gap", category: "marketing", priority: 70,
      title: "客户池为空",
      message: "还没有添加任何客户/联系人。客户是一人企业的核心资产，建议尽早开始建立客户池。",
      actionHint: "使用 opc_manage 工具的 add_contact 添加第一个客户",
    });
  }

  // 无画布
  const canvasCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_opb_canvas WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (canvasCount === 0) {
    createInsight(db, companyId, {
      insightType: "data_gap", category: "growth", priority: 90,
      title: "建议用 OPB 画布梳理商业模式",
      message: "OPB 画布是一人企业方法论的核心规划工具，可以帮你系统梳理目标客户、价值主张、收入模式等关键要素。",
      actionHint: "使用 opc_opb 工具创建画布，或让 AI 助手帮你填写",
    });
  } else {
    // 画布不完整
    const canvas = db.queryOne(
      "SELECT * FROM opc_opb_canvas WHERE company_id = ?", companyId,
    ) as Record<string, string> | null;
    if (canvas) {
      const 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",
      ];
      const filled = fields.filter(f => canvas[f] && canvas[f].trim() !== "").length;
      const pct = Math.round((filled / fields.length) * 100);
      if (pct < 100) {
        const missing = fields.filter(f => !canvas[f] || canvas[f].trim() === "");
        createInsight(db, companyId, {
          insightType: "data_gap", category: "growth", priority: 60,
          title: `OPB 画布已完成 ${pct}%`,
          message: `画布还缺少以下字段：${missing.join("、")}。完善画布有助于更清晰地规划业务方向。`,
          actionHint: "使用 opc_opb 工具更新画布字段",
        });
      }
    }
  }

  // 无 AI 员工
  const staffCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_staff_config WHERE company_id = ? AND enabled = 1",
    companyId,
  ) as CountRow).cnt;
  if (staffCount === 0) {
    createInsight(db, companyId, {
      insightType: "data_gap", category: "ops", priority: 65,
      title: "建议初始化 AI 员工团队",
      message: "AI 员工可以帮你处理财务、法务、HR、市场等专业工作。初始化后，你可以像管理真实团队一样分派任务。",
      actionHint: "使用 opc_staff 工具的 init_default 一键初始化默认岗位",
    });
  }

  // 无内容
  const contentCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (contentCount === 0 && txCount > 0) {
    // 只有在已经有交易活动时才建议内容营销
    createInsight(db, companyId, {
      insightType: "data_gap", category: "marketing", priority: 40,
      title: "建议开始内容营销",
      message: "一人企业的增长引擎之一是内容营销。建议开始创作和发布内容，建立个人品牌和获客渠道。",
      actionHint: "使用 opc_media 工具创建内容",
      staffRole: "marketing",
    });
  }
}

// ── 分析器 2: 财务趋势分析 ───────────────────────────────────────

type MonthlyRow = { month: string; income: number; expense: number };

function analyzeFinanceTrends(db: OpcDatabase, companyId: string): void {
  // 获取近 3 个月的月度数据
  const months = db.query(
    `SELECT strftime('%Y-%m', transaction_date) as month,
       COALESCE(SUM(CASE WHEN type='income' THEN amount ELSE 0 END), 0) as income,
       COALESCE(SUM(CASE WHEN type='expense' THEN amount ELSE 0 END), 0) as expense
     FROM opc_transactions
     WHERE company_id = ?
     GROUP BY month
     ORDER BY month DESC
     LIMIT 3`,
    companyId,
  ) as MonthlyRow[];

  if (months.length < 2) return;

  const current = months[0];
  const previous = months[1];

  // 月环比收入变化（> 20% 触发）
  if (previous.income > 0) {
    const changeRate = (current.income - previous.income) / previous.income;
    if (changeRate > 0.2) {
      createInsight(db, companyId, {
        insightType: "trend", category: "finance", priority: 70,
        title: `收入增长 ${Math.round(changeRate * 100)}%`,
        message: `本月收入 ${current.income.toLocaleString()} 元，较上月增长 ${Math.round(changeRate * 100)}%。增长势头良好，建议关注可持续性。`,
        staffRole: "finance",
      });
    } else if (changeRate < -0.2) {
      createInsight(db, companyId, {
        insightType: "risk", category: "finance", priority: 85,
        title: `收入下降 ${Math.round(Math.abs(changeRate) * 100)}%`,
        message: `本月收入 ${current.income.toLocaleString()} 元，较上月下降 ${Math.round(Math.abs(changeRate) * 100)}%。建议分析下降原因并制定应对措施。`,
        staffRole: "finance",
      });
    }
  }

  // 收入来源单一检测
  const counterparties = db.query(
    `SELECT counterparty, SUM(amount) as total
     FROM opc_transactions
     WHERE company_id = ? AND type = 'income' AND counterparty != ''
     GROUP BY counterparty
     ORDER BY total DESC`,
    companyId,
  ) as { counterparty: string; total: number }[];

  if (counterparties.length > 0) {
    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 && counterparties[0].total / totalIncome > 0.8 && counterparties.length >= 1) {
      createInsight(db, companyId, {
        insightType: "risk", category: "finance", priority: 75,
        title: "收入来源过于集中",
        message: `${counterparties[0].counterparty} 贡献了超过 80% 的收入。单一客户依赖是一人企业的常见风险，建议拓展更多客户来源。`,
        staffRole: "finance",
      });
    }
  }

  // 连续支出无收入
  if (current.income === 0 && current.expense > 0 && previous.income === 0 && previous.expense > 0) {
    createInsight(db, companyId, {
      insightType: "risk", category: "finance", priority: 80,
      title: "连续两月无收入但有支出",
      message: `已连续两个月只有支出没有收入。总支出 ${(current.expense + previous.expense).toLocaleString()} 元。建议关注现金流状况。`,
      staffRole: "finance",
    });
  }

  // 本月尚无收入（月中提醒，15号之后）
  const today = new Date();
  if (today.getDate() >= 15) {
    const thisMonth = today.toISOString().slice(0, 7);
    const thisMonthIncome = (db.queryOne(
      `SELECT COALESCE(SUM(amount), 0) as total
       FROM opc_transactions
       WHERE company_id = ? AND type = 'income' AND strftime('%Y-%m', transaction_date) = ?`,
      companyId, thisMonth,
    ) as SumRow).total;

    // 只在之前有过收入的公司才提醒
    const hasHistoricalIncome = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
      companyId,
    ) as SumRow).total > 0;

    if (thisMonthIncome === 0 && hasHistoricalIncome) {
      createInsight(db, companyId, {
        insightType: "trend", category: "finance", priority: 60,
        title: "本月尚无收入记录",
        message: `已过月中，本月尚未记录任何收入。如果有收入发生但未记录，建议及时补录。`,
        staffRole: "finance",
      });
    }
  }
}

// ── 分析器 3: 阶段性建议 ─────────────────────────────────────────

type PlaybookItem = {
  title: string;
  message: string;
  category: string;
  priority: number;
  /** 返回 true 表示用户尚未完成此项，需要推送 */
  check: (db: OpcDatabase, companyId: string) => boolean;
};

const STAGE_PLAYBOOKS: Record<CompanyStage, PlaybookItem[]> = {
  idea: [
    {
      title: "明确你的赛道方向",
      message: "用 OPB 画布梳理你的赛道选择，聚焦「小众强需求」比「大众弱需求」更适合一人企业。",
      category: "growth", priority: 90,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_opb_canvas WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
    {
      title: "找到你的第一个潜在客户",
      message: "在正式开始之前，至少找到 1 个可能愿意付费的潜在客户，验证需求是否真实存在。",
      category: "marketing", priority: 80,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
    {
      title: "初始化 AI 员工团队",
      message: "配置 AI 员工可以帮你处理专业工作，让你聚焦核心业务。",
      category: "ops", priority: 70,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_staff_config WHERE company_id = ? AND enabled = 1", cid) as CountRow).cnt === 0,
    },
  ],
  validation: [
    {
      title: "获得第一笔收入",
      message: "验证阶段最重要的目标是证明有人愿意为你的产品/服务付费。哪怕是很小的金额，也是最关键的验证。",
      category: "finance", priority: 95,
      check: (db, cid) => (db.queryOne("SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", cid) as SumRow).total === 0,
    },
    {
      title: "完善 OPB 商业画布",
      message: "通过验证过程中的发现，更新和完善你的商业画布，让商业模式更清晰。",
      category: "growth", priority: 75,
      check: (db, cid) => {
        const canvas = db.queryOne("SELECT * FROM opc_opb_canvas WHERE company_id = ?", cid) as Record<string, string> | null;
        if (!canvas) return true;
        const fields = ["track", "target_customer", "pain_point", "solution", "unique_value", "revenue_model"];
        return fields.some(f => !canvas[f] || canvas[f].trim() === "");
      },
    },
    {
      title: "签订第一份合同",
      message: "正式的合同关系有助于保护你的权益，也标志着业务正规化。",
      category: "legal", priority: 70,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
  ],
  early_revenue: [
    {
      title: "建立稳定的收入来源",
      message: "有了第一笔收入后，下一个目标是让收入可持续。思考如何让客户持续付费或带来新客户。",
      category: "finance", priority: 90,
      check: (db, cid) => (db.queryOne("SELECT COUNT(DISTINCT strftime('%Y-%m', transaction_date)) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'income' AND amount > 0", cid) as CountRow).cnt < 2,
    },
    {
      title: "开始记录支出以掌握成本",
      message: "了解你的成本结构是实现盈利的前提。建议开始系统记录各项支出。",
      category: "finance", priority: 75,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'expense'", cid) as CountRow).cnt === 0,
    },
    {
      title: "开始内容营销建立品牌",
      message: "一人企业的增长引擎是内容。开始在你擅长的平台上分享专业内容，吸引潜在客户。",
      category: "marketing", priority: 65,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
  ],
  growth: [
    {
      title: "拓展客户来源，避免单一依赖",
      message: "增长阶段要注意分散风险。确保不超过 80% 的收入来自单一客户。",
      category: "marketing", priority: 85,
      check: (db, cid) => {
        const rows = db.query("SELECT counterparty, SUM(amount) as total FROM opc_transactions WHERE company_id = ? AND type = 'income' AND counterparty != '' GROUP BY counterparty ORDER BY total DESC", cid) as { total: number }[];
        const totalIncome = (db.queryOne("SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", cid) as SumRow).total;
        return rows.length > 0 && totalIncome > 0 && rows[0].total / totalIncome > 0.8;
      },
    },
    {
      title: "建立项目管理流程",
      message: "随着业务增长，需要系统化管理项目和任务，避免遗漏和延期。",
      category: "ops", priority: 70,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
    {
      title: "考虑合规和税务筹划",
      message: "收入增长后，税务筹划变得重要。建议开始系统管理税务申报。",
      category: "finance", priority: 65,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_tax_filings WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
  ],
  stable: [
    {
      title: "优化利润率",
      message: "稳定运营阶段应关注利润率而非单纯收入增长。审视成本结构，找到优化空间。",
      category: "finance", priority: 80,
      check: () => true, // 通用建议，始终显示
    },
    {
      title: "建立标准化服务流程",
      message: "将你的服务标准化和产品化，降低对个人时间的依赖，为规模化做准备。",
      category: "ops", priority: 75,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_services WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
  ],
  scaling: [
    {
      title: "考虑团队扩展",
      message: "规模化阶段可能需要引入外包或兼职人员。一人企业不意味着所有事都自己做。",
      category: "hr", priority: 80,
      check: () => true,
    },
    {
      title: "评估融资需求",
      message: "如果增长受限于资金，可以考虑天使投资或银行信贷来加速扩张。",
      category: "finance", priority: 70,
      check: (db, cid) => (db.queryOne("SELECT COUNT(*) as cnt FROM opc_investment_rounds WHERE company_id = ?", cid) as CountRow).cnt === 0,
    },
  ],
  exit: [
    {
      title: "整理公司资产和数据",
      message: "退出阶段需要整理好所有资产、合同、知识产权等，为交割做准备。",
      category: "ops", priority: 90,
      check: () => true,
    },
  ],
};

function generateNextSteps(db: OpcDatabase, companyId: string): void {
  const stageResult = detectCompanyStage(db, companyId);
  const playbook = STAGE_PLAYBOOKS[stageResult.stage] ?? [];

  for (const item of playbook) {
    if (item.check(db, companyId)) {
      createInsight(db, companyId, {
        insightType: "next_step",
        category: item.category,
        priority: item.priority,
        title: item.title,
        message: item.message,
      });
    }
  }
}

// ── 分析器 4: AI 员工观察 ─────────────────────────────────────────

function analyzeStaffObservations(db: OpcDatabase, companyId: string): void {
  // ── 财务顾问观察 ──
  // 本季度无税务申报
  const quarter = Math.floor(new Date().getMonth() / 3) + 1;
  const year = new Date().getFullYear();
  const quarterStr = `${year}Q${quarter}`;
  const taxFiled = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_tax_filings WHERE company_id = ? AND period LIKE ?",
    companyId, `${year}%`,
  ) as CountRow).cnt;
  const hasIncome = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
    companyId,
  ) as SumRow).total > 0;
  if (taxFiled === 0 && hasIncome) {
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "finance", priority: 72,
      title: `${quarterStr} 尚未申报税务`,
      message: `公司已有收入但本年度尚未创建任何税务申报记录。建议及时了解纳税义务，避免逾期罚款。`,
      staffRole: "finance",
    });
  }

  // 未开票收入检测
  const invoicedTotal = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_invoices WHERE company_id = ? AND type = 'sales'",
    companyId,
  ) as SumRow).total;
  const incomeTotal = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
    companyId,
  ) as SumRow).total;
  if (incomeTotal > 0 && invoicedTotal < incomeTotal * 0.5) {
    const gap = incomeTotal - invoicedTotal;
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "finance", priority: 68,
      title: `约 ${gap.toLocaleString()} 元收入未开票`,
      message: `累计收入 ${incomeTotal.toLocaleString()} 元中，仅开票 ${invoicedTotal.toLocaleString()} 元。建议核实是否有遗漏开票。`,
      staffRole: "finance",
    });
  }

  // ── 法务助手观察 ──
  // 合同缺少风险备注
  const riskyContracts = db.query(
    "SELECT id, title FROM opc_contracts WHERE company_id = ? AND risk_notes = '' AND status = 'active'",
    companyId,
  ) as { id: string; title: string }[];
  if (riskyContracts.length > 0) {
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "legal", priority: 65,
      title: `${riskyContracts.length} 份活跃合同缺少风险备注`,
      message: `合同「${riskyContracts[0].title}」等 ${riskyContracts.length} 份活跃合同未填写风险备注。建议评估合同风险点并记录。`,
      staffRole: "legal",
    });
  }

  // 即将到期合同（30天内）
  const soonExpiring = db.query(
    `SELECT title, end_date FROM opc_contracts
     WHERE company_id = ? AND status = 'active' AND end_date != ''
       AND end_date <= date('now', '+30 days') AND end_date >= date('now')
     ORDER BY end_date LIMIT 3`,
    companyId,
  ) as { title: string; end_date: string }[];
  if (soonExpiring.length > 0) {
    const list = soonExpiring.map(c => `「${c.title}」(${c.end_date})`).join("、");
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "legal", priority: 78,
      title: `${soonExpiring.length} 份合同即将到期`,
      message: `以下合同将在 30 天内到期：${list}。请提前安排续签或终止事宜。`,
      staffRole: "legal",
    });
  }

  // ── 市场专员观察 ──
  // 本月无内容发布
  const thisMonth = new Date().toISOString().slice(0, 7);
  const contentThisMonth = (db.queryOne(
    `SELECT COUNT(*) as cnt FROM opc_media_content
     WHERE company_id = ? AND strftime('%Y-%m', COALESCE(NULLIF(published_date,''), created_at)) = ?`,
    companyId, thisMonth,
  ) as CountRow).cnt;
  const totalContent = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (totalContent > 0 && contentThisMonth === 0) {
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "marketing", priority: 55,
      title: "本月尚未发布任何内容",
      message: "上月有内容发布但本月还没有。持续的内容输出是个人品牌建设的关键，建议保持发布节奏。",
      staffRole: "marketing",
    });
  }

  // ── HR 专员观察 ──
  // 薪酬异常检测
  const hrRecords = db.query(
    "SELECT employee_name, salary FROM opc_hr_records WHERE company_id = ? AND status = 'active' AND salary > 0",
    companyId,
  ) as { employee_name: string; salary: number }[];
  if (hrRecords.length >= 2) {
    const avg = hrRecords.reduce((s, r) => s + r.salary, 0) / hrRecords.length;
    const outliers = hrRecords.filter(r => r.salary > avg * 2 || r.salary < avg * 0.3);
    if (outliers.length > 0) {
      createInsight(db, companyId, {
        insightType: "staff_observation", category: "hr", priority: 60,
        title: `${outliers.length} 名员工薪酬偏离均值`,
        message: `团队平均薪酬 ${Math.round(avg).toLocaleString()} 元，${outliers.map(o => o.employee_name).join("、")} 的薪酬显著偏离。建议核实是否合理。`,
        staffRole: "hr",
      });
    }
  }

  // ── 运营总监观察 ──
  // 逾期任务
  const overdueTasks = (db.queryOne(
    `SELECT COUNT(*) as cnt FROM opc_tasks
     WHERE company_id = ? AND status NOT IN ('done','completed','cancelled')
       AND due_date != '' AND due_date < date('now')`,
    companyId,
  ) as CountRow).cnt;
  if (overdueTasks > 0) {
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "ops", priority: 82,
      title: `${overdueTasks} 个任务已逾期`,
      message: `有 ${overdueTasks} 个任务已超过截止日期尚未完成。逾期任务会拖慢整体进度，建议尽快处理或调整计划。`,
      staffRole: "ops",
    });
  }

  // 停滞项目（状态 planning/active 但超过30天无更新）
  const staleProjects = db.query(
    `SELECT name FROM opc_projects
     WHERE company_id = ? AND status IN ('planning','active')
       AND updated_at < datetime('now', '-30 days')`,
    companyId,
  ) as { name: string }[];
  if (staleProjects.length > 0) {
    createInsight(db, companyId, {
      insightType: "staff_observation", category: "ops", priority: 70,
      title: `${staleProjects.length} 个项目超过 30 天无更新`,
      message: `项目「${staleProjects[0].name}」等已超过 30 天无任何更新。建议评估是否继续推进或归档。`,
      staffRole: "ops",
    });
  }
}

// ── 分析器 5: 运营健康分析 ───────────────────────────────────────

function analyzeOperationalHealth(db: OpcDatabase, companyId: string): void {
  // 超预算项目
  const overBudgetProjects = db.query(
    `SELECT name, budget, spent FROM opc_projects
     WHERE company_id = ? AND budget > 0 AND spent > budget AND status NOT IN ('completed','cancelled')`,
    companyId,
  ) as { name: string; budget: number; spent: number }[];
  for (const p of overBudgetProjects) {
    const overPct = Math.round(((p.spent - p.budget) / p.budget) * 100);
    createInsight(db, companyId, {
      insightType: "risk", category: "ops", priority: 80,
      title: `项目「${p.name}」已超预算 ${overPct}%`,
      message: `预算 ${p.budget.toLocaleString()} 元，已支出 ${p.spent.toLocaleString()} 元，超出 ${(p.spent - p.budget).toLocaleString()} 元。建议审视后续支出计划。`,
    });
  }

  // 90天以上未联系客户
  const staleContacts = (db.queryOne(
    `SELECT COUNT(*) as cnt FROM opc_contacts
     WHERE company_id = ? AND last_contact_date != '' AND last_contact_date < date('now', '-90 days')`,
    companyId,
  ) as CountRow).cnt;
  if (staleContacts > 0) {
    createInsight(db, companyId, {
      insightType: "risk", category: "marketing", priority: 58,
      title: `${staleContacts} 个客户超过 90 天未联系`,
      message: `有 ${staleContacts} 个客户/联系人上次沟通已超过 3 个月。定期维护客户关系有助于续单和转介绍。`,
    });
  }

  // 账龄超30天的未付发票
  const agingInvoices = db.query(
    `SELECT invoice_number, counterparty, total_amount, issue_date FROM opc_invoices
     WHERE company_id = ? AND status IN ('sent','pending') AND type = 'sales'
       AND issue_date != '' AND issue_date < date('now', '-30 days')
     LIMIT 5`,
    companyId,
  ) as { invoice_number: string; counterparty: string; total_amount: number; issue_date: string }[];
  if (agingInvoices.length > 0) {
    const totalAging = agingInvoices.reduce((s, i) => s + i.total_amount, 0);
    createInsight(db, companyId, {
      insightType: "risk", category: "finance", priority: 76,
      title: `${agingInvoices.length} 张发票账龄超过 30 天`,
      message: `共 ${agingInvoices.length} 张销售发票已开出超过 30 天未收款，总金额 ${totalAging.toLocaleString()} 元。建议及时催款。`,
      staffRole: "finance",
    });
  }
}

// ── 公开 API ─────────────────────────────────────────────────────

/** 对单个公司运行完整智能扫描 */
export function runIntelligenceScanForCompany(db: OpcDatabase, companyId: string, log: (msg: string) => void): void {
  try {
    analyzeDataGaps(db, companyId);
    analyzeFinanceTrends(db, companyId);
    generateNextSteps(db, companyId);
    analyzeStaffObservations(db, companyId);
    analyzeOperationalHealth(db, companyId);
    log(`opc-intel: 公司 [${companyId}] 智能扫描完成`);
  } catch (err) {
    log(`opc-intel: 公司 [${companyId}] 扫描异常: ${err instanceof Error ? err.message : String(err)}`);
  }
}

/** 对所有公司运行智能扫描 */
export function runIntelligenceScan(db: OpcDatabase, log: (msg: string) => void): void {
  const companies = db.query("SELECT id FROM opc_companies") as { id: string }[];
  for (const c of companies) {
    runIntelligenceScanForCompany(db, c.id, log);
  }
  log(`opc-intel: 全量智能扫描完成，共 ${companies.length} 家公司`);
}

/** 获取公司的活跃洞察（供上下文注入用） */
export function getActiveInsights(db: OpcDatabase, companyId: string, limit = 10): {
  id: string;
  insight_type: string;
  category: string;
  priority: number;
  title: string;
  message: string;
  action_hint: string;
  staff_role: string;
}[] {
  return db.query(
    `SELECT id, insight_type, category, priority, title, message, action_hint, staff_role
     FROM opc_insights
     WHERE company_id = ? AND status = 'active' AND (expires_at = '' OR expires_at > datetime('now'))
     ORDER BY priority DESC, created_at DESC
     LIMIT ?`,
    companyId, limit,
  ) as {
    id: string;
    insight_type: string;
    category: string;
    priority: number;
    title: string;
    message: string;
    action_hint: string;
    staff_role: string;
  }[];
}

/** 清理过期洞察 */
export function expireStaleInsights(db: OpcDatabase, log: (msg: string) => void): void {
  const result = db.execute(
    `UPDATE opc_insights SET status = 'expired', updated_at = datetime('now')
     WHERE status = 'active' AND expires_at != '' AND expires_at <= datetime('now')`,
  );
  if (result.changes > 0) {
    log(`opc-intel: 清理了 ${result.changes} 条过期洞察`);
  }
}