/**
 * 星环OPC中心 — 智能简报构建器
 *
 * 将预计算数据组装为上下文注入文本。
 * 两个入口：
 * - buildBriefingContext  — 单公司 Agent 上下文（Path A）
 * - buildPortfolioBriefing — 跨公司概览（Path C）
 *
 * 硬上限: 最多 5 条洞察 + 2 条庆祝 + 3 条告警
 */

import type { OpcDatabase } from "../db/index.js";
import { getActiveInsights } from "./intelligence-engine.js";

type CelebrationRow = {
  id: string;
  celebration_type: string;
  title: string;
  message: string;
};

type AlertRow = {
  id: string;
  title: string;
  message: string;
  severity: string;
};

type StageRow = {
  company_id: string;
  stage: string;
  stage_label: string;
};

type CompanyRow = {
  id: string;
  name: string;
  status: string;
};

type CountRow = { cnt: number };
type SumRow = { total: number };
type MonthlyTxRow = { month: string; income: number; expense: number };

/**
 * 计算公司健康评分（0-100）。
 * 维度：财务健康(30%) + 运营效率(20%) + 增长趋势(20%) + 数据完整度(15%) + 合规状态(15%)
 */
export function computeHealthScore(db: OpcDatabase, companyId: string): {
  total: number;
  dimensions: { name: string; score: number; weight: number; detail: string }[];
} {
  const dims: { name: string; score: number; weight: number; detail: string }[] = [];

  // ── 财务健康 (30%) ──
  const finance = (() => {
    const income = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", companyId,
    ) as SumRow).total;
    const expense = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'", companyId,
    ) as SumRow).total;
    let score = 30; // baseline
    if (income > 0) score += 25;
    if (income > expense) score += 25; // profitable
    const revenueMonths = (db.queryOne(
      "SELECT COUNT(DISTINCT strftime('%Y-%m', transaction_date)) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'income' AND amount > 0", companyId,
    ) as CountRow).cnt;
    if (revenueMonths >= 2) score += 10;
    if (revenueMonths >= 3) score += 10;
    const detail = income === 0 ? "无收入记录" : `收入 ${income.toLocaleString()} 元，${income > expense ? "盈利" : "亏损"}`;
    return { score: Math.min(score, 100), detail };
  })();
  dims.push({ name: "财务健康", score: finance.score, weight: 0.3, detail: finance.detail });

  // ── 运营效率 (20%) ──
  const ops = (() => {
    let score = 60; // baseline
    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;
    const totalTasks = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_tasks WHERE company_id = ?", companyId,
    ) as CountRow).cnt;
    if (totalTasks > 0 && overdueTasks === 0) score += 30;
    else if (overdueTasks > 0) score -= Math.min(overdueTasks * 10, 40);
    const overBudget = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ? AND budget > 0 AND spent > budget AND status NOT IN ('completed','cancelled')", companyId,
    ) as CountRow).cnt;
    if (overBudget > 0) score -= overBudget * 15;
    const detail = overdueTasks > 0 ? `${overdueTasks} 个逾期任务` : totalTasks > 0 ? "任务按时推进" : "暂无任务数据";
    return { score: Math.max(Math.min(score, 100), 0), detail };
  })();
  dims.push({ name: "运营效率", score: ops.score, weight: 0.2, detail: ops.detail });

  // ── 增长趋势 (20%) ──
  const growth = (() => {
    let score = 40;
    const months = db.query(
      "SELECT strftime('%Y-%m', transaction_date) as m, SUM(CASE WHEN type='income' THEN amount ELSE 0 END) as inc FROM opc_transactions WHERE company_id = ? GROUP BY m ORDER BY m DESC LIMIT 3", companyId,
    ) as { m: string; inc: number }[];
    if (months.length >= 2 && months[1].inc > 0) {
      const rate = (months[0].inc - months[1].inc) / months[1].inc;
      if (rate > 0.1) score += 40;
      else if (rate > 0) score += 20;
      else if (rate > -0.1) score += 10;
    }
    const contactCount = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", companyId,
    ) as CountRow).cnt;
    if (contactCount >= 5) score += 10;
    else if (contactCount >= 1) score += 5;
    const detail = months.length >= 2 && months[1].inc > 0
      ? `月收入环比 ${Math.round(((months[0].inc - months[1].inc) / months[1].inc) * 100)}%`
      : contactCount > 0 ? `${contactCount} 个客户` : "数据不足";
    return { score: Math.max(Math.min(score, 100), 0), detail };
  })();
  dims.push({ name: "增长趋势", score: growth.score, weight: 0.2, detail: growth.detail });

  // ── 数据完整度 (15%) ──
  const data = (() => {
    let score = 0;
    const checks = [
      { sql: "SELECT COUNT(*) as cnt FROM opc_transactions WHERE company_id = ?", pts: 15 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", pts: 15 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_opb_canvas WHERE company_id = ?", pts: 20 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_staff_config WHERE company_id = ? AND enabled = 1", pts: 15 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", pts: 10 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ?", pts: 10 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", pts: 10 },
      { sql: "SELECT COUNT(*) as cnt FROM opc_hr_records WHERE company_id = ?", pts: 5 },
    ];
    let filled = 0;
    for (const c of checks) {
      if ((db.queryOne(c.sql, companyId) as CountRow).cnt > 0) {
        score += c.pts;
        filled++;
      }
    }
    const detail = `${filled}/${checks.length} 个模块已使用`;
    return { score: Math.min(score, 100), detail };
  })();
  dims.push({ name: "数据完整度", score: data.score, weight: 0.15, detail: data.detail });

  // ── 合规状态 (15%) ──
  const compliance = (() => {
    let score = 70; // baseline
    const activeAlerts = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_alerts WHERE company_id = ? AND status = 'active'", companyId,
    ) as CountRow).cnt;
    const criticalAlerts = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_alerts WHERE company_id = ? AND status = 'active' AND severity = 'critical'", companyId,
    ) as CountRow).cnt;
    if (activeAlerts === 0) score += 30;
    else {
      score -= criticalAlerts * 20;
      score -= (activeAlerts - criticalAlerts) * 5;
    }
    const hasContract = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    if (hasContract) score += 10;
    const detail = activeAlerts > 0 ? `${activeAlerts} 条活跃告警` : "无告警";
    return { score: Math.max(Math.min(score, 100), 0), detail };
  })();
  dims.push({ name: "合规状态", score: compliance.score, weight: 0.15, detail: compliance.detail });

  const total = Math.round(dims.reduce((s, d) => s + d.score * d.weight, 0));
  return { total, dimensions: dims };
}

function healthScoreEmoji(score: number): string {
  if (score >= 80) return "🟢";
  if (score >= 60) return "🟡";
  if (score >= 40) return "🟠";
  return "🔴";
}

// ── 核心指标一览 ────────────────────────────────────────────────

function buildDashboardMetrics(db: OpcDatabase, companyId: string): string[] {
  const income = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
    companyId,
  ) as SumRow).total;
  const expense = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'",
    companyId,
  ) as SumRow).total;
  const thisMonth = new Date().toISOString().slice(0, 7);
  const monthIncome = (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 monthExpense = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense' AND strftime('%Y-%m', transaction_date) = ?",
    companyId, thisMonth,
  ) as SumRow).total;
  const contactCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts 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 activeProjects = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ? AND status IN ('active','planning')", companyId,
  ) as CountRow).cnt;
  const contentCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  // 运营时长
  const companyRow = db.queryOne(
    "SELECT created_at FROM opc_companies WHERE id = ?", companyId,
  ) as { created_at: string } | null;
  let ageText = "";
  if (companyRow?.created_at) {
    const created = new Date(companyRow.created_at);
    const now = new Date();
    const days = Math.floor((now.getTime() - created.getTime()) / 86400000);
    if (days < 30) ageText = `${days} 天`;
    else if (days < 365) ageText = `${Math.floor(days / 30)} 个月`;
    else ageText = `${(days / 365).toFixed(1)} 年`;
  }
  const stage = db.queryOne(
    "SELECT stage_label FROM opc_company_stage WHERE company_id = ?", companyId,
  ) as { stage_label: string } | null;

  const lines: string[] = ["### 📊 核心指标一览"];
  lines.push(`- 💰 累计收入: ${income.toLocaleString()} 元 | 累计支出: ${expense.toLocaleString()} 元 | 净利润: ${(income - expense).toLocaleString()} 元`);
  lines.push(`- 📈 本月收入: ${monthIncome.toLocaleString()} 元 | 本月支出: ${monthExpense.toLocaleString()} 元`);
  lines.push(`- 👥 客户: ${contactCount} | 活跃合同: ${activeContracts} | 活跃项目: ${activeProjects} | 内容: ${contentCount} 篇`);
  if (ageText) {
    lines.push(`- 📅 运营时长: ${ageText} | 发展阶段: ${stage?.stage_label ?? "未检测"}`);
  }
  lines.push("");
  return lines;
}

// ── 现金流预测 ──────────────────────────────────────────────────

function buildCashFlowForecast(db: OpcDatabase, companyId: string): string[] {
  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 6`,
    companyId,
  ) as MonthlyTxRow[];

  if (months.length === 0) return [];

  const avgIncome = months.reduce((s, m) => s + m.income, 0) / months.length;
  const avgExpense = months.reduce((s, m) => s + m.expense, 0) / months.length;
  const netMonthly = avgIncome - avgExpense;

  const lines: string[] = ["### 💰 现金流预测"];
  lines.push(`- 月均收入: ${Math.round(avgIncome).toLocaleString()} 元 | 月均支出: ${Math.round(avgExpense).toLocaleString()} 元`);

  if (netMonthly > 0) {
    lines.push(`- 月均净收入: +${Math.round(netMonthly).toLocaleString()} 元 ✅`);
  } else if (netMonthly < 0) {
    const balance = (db.queryOne(
      "SELECT COALESCE(SUM(CASE WHEN type='income' THEN amount ELSE -amount END), 0) as total FROM opc_transactions WHERE company_id = ?",
      companyId,
    ) as SumRow).total;
    if (balance > 0) {
      const runway = Math.floor(balance / Math.abs(netMonthly));
      lines.push(`- 月均净亏损: ${Math.round(Math.abs(netMonthly)).toLocaleString()} 元 ⚠️ | 预计跑道: ${runway} 个月`);
    } else {
      lines.push(`- 月均净亏损: ${Math.round(Math.abs(netMonthly)).toLocaleString()} 元 ⚠️`);
    }
  }

  // 收入里程碑预测
  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 (avgIncome > 0) {
    const milestones = [10_000, 50_000, 100_000, 500_000, 1_000_000];
    const next = milestones.find(m => m > totalIncome);
    if (next) {
      const monthsNeeded = Math.ceil((next - totalIncome) / avgIncome);
      const label = next >= 10_000 ? `${next / 10_000} 万` : `${next.toLocaleString()} 元`;
      lines.push(`- 🎯 下一里程碑: 收入 ${label}（预计 ${monthsNeeded} 个月内）`);
    }
  }

  // 环比增长
  if (months.length >= 2 && months[1].income > 0) {
    const rate = ((months[0].income - months[1].income) / months[1].income) * 100;
    const icon = rate > 0 ? "📈" : rate < 0 ? "📉" : "➡️";
    lines.push(`- ${icon} 收入趋势: 环比 ${rate > 0 ? "+" : ""}${Math.round(rate)}%`);
  }

  lines.push("");
  return lines;
}

// ── 员工晨会 ──────────────────────────────────────────────────────

const ROLE_EMOJI: Record<string, string> = {
  finance: "💰",
  legal: "⚖️",
  hr: "👤",
  marketing: "📣",
  ops: "⚙️",
  admin: "📋",
};

type InsightRow = {
  insight_type: string;
  staff_role?: string;
  title: string;
  message: string;
  action_hint?: string;
  priority: number;
};

type StaffTaskRow = {
  id: string;
  staff_role: string;
  title: string;
  status: string;
  priority: string;
  result_summary: string;
  assigned_at: string;
  completed_at: string;
};

type StaffConfigRow = {
  role: string;
  role_name: string;
  enabled: number;
};

function buildEmployeeStandup(
  db: OpcDatabase,
  companyId: string,
  staffObs: InsightRow[],
): string[] {
  // 查询启用的员工列表
  const staffConfigs = db.query(
    "SELECT role, role_name, enabled FROM opc_staff_config WHERE company_id = ? AND enabled = 1 ORDER BY created_at ASC",
    companyId,
  ) as StaffConfigRow[];

  if (staffConfigs.length === 0 && staffObs.length === 0) return [];

  // 查询进行中和待处理的任务
  const activeTasks = db.query(
    `SELECT id, staff_role, title, status, priority, result_summary, assigned_at, completed_at
     FROM opc_staff_tasks
     WHERE company_id = ? AND status IN ('pending', 'in_progress')
     ORDER BY CASE priority WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 WHEN 'normal' THEN 3 ELSE 4 END, assigned_at DESC`,
    companyId,
  ) as StaffTaskRow[];

  // 查询最近完成的任务（24h 内）
  const recentCompleted = db.query(
    `SELECT id, staff_role, title, status, priority, result_summary, assigned_at, completed_at
     FROM opc_staff_tasks
     WHERE company_id = ? AND status = 'completed' AND completed_at > datetime('now', '-24 hours')
     ORDER BY completed_at DESC LIMIT 5`,
    companyId,
  ) as StaffTaskRow[];

  // 按角色分组
  const roleNames = new Map<string, string>();
  for (const s of staffConfigs) {
    roleNames.set(s.role, s.role_name);
  }

  const obsByRole = new Map<string, InsightRow[]>();
  for (const obs of staffObs) {
    const role = obs.staff_role ?? "general";
    if (!obsByRole.has(role)) obsByRole.set(role, []);
    obsByRole.get(role)!.push(obs);
  }

  const tasksByRole = new Map<string, StaffTaskRow[]>();
  for (const t of activeTasks) {
    if (!tasksByRole.has(t.staff_role)) tasksByRole.set(t.staff_role, []);
    tasksByRole.get(t.staff_role)!.push(t);
  }

  const completedByRole = new Map<string, StaffTaskRow[]>();
  for (const t of recentCompleted) {
    if (!completedByRole.has(t.staff_role)) completedByRole.set(t.staff_role, []);
    completedByRole.get(t.staff_role)!.push(t);
  }

  // 收集所有有内容的角色
  const allRoles = new Set<string>();
  for (const s of staffConfigs) allRoles.add(s.role);
  for (const role of obsByRole.keys()) allRoles.add(role);
  for (const role of tasksByRole.keys()) allRoles.add(role);
  for (const role of completedByRole.keys()) allRoles.add(role);

  const lines: string[] = ["### 👥 员工晨会"];

  for (const role of allRoles) {
    const emoji = ROLE_EMOJI[role] ?? "🤖";
    const name = roleNames.get(role) ?? role;
    const obs = obsByRole.get(role) ?? [];
    const tasks = tasksByRole.get(role) ?? [];
    const completed = completedByRole.get(role) ?? [];

    // 跳过没有任何内容的角色
    if (obs.length === 0 && tasks.length === 0 && completed.length === 0) continue;

    // 员工发言：观察 + 建议
    if (obs.length > 0) {
      const mainObs = obs[0];
      const extraObs = obs.slice(1);
      lines.push(`${emoji} **${name}**: "${mainObs.message}"`);
      for (const extra of extraObs) {
        lines.push(`  - ${extra.title}: ${extra.message}`);
      }
      // 可安排的任务建议
      const hints = obs.filter(o => o.action_hint).map(o => o.action_hint);
      if (hints.length > 0) {
        lines.push(`  → 可安排: ${hints.join(" | ")}`);
      }
    } else {
      lines.push(`${emoji} **${name}**:`);
    }

    // 当前任务
    if (tasks.length > 0) {
      for (const t of tasks) {
        const statusIcon = t.status === "in_progress" ? "🔄" : "⏳";
        const priorityTag = t.priority === "urgent" ? " [紧急]" : t.priority === "high" ? " [重要]" : "";
        lines.push(`  ${statusIcon} 进行中: ${t.title}${priorityTag}`);
      }
    }

    // 刚完成的任务
    if (completed.length > 0) {
      for (const t of completed) {
        const summary = t.result_summary ? ` — ${t.result_summary}` : "";
        lines.push(`  ✅ 已完成: ${t.title}${summary}`);
      }
    }
  }

  // 汇总统计
  const totalActive = activeTasks.length;
  const totalCompleted24h = recentCompleted.length;
  if (totalActive > 0 || totalCompleted24h > 0) {
    const parts: string[] = [];
    if (totalActive > 0) parts.push(`${totalActive} 项进行中`);
    if (totalCompleted24h > 0) parts.push(`${totalCompleted24h} 项24h内完成`);
    lines.push(`📌 团队任务: ${parts.join("，")}`);
  }

  lines.push(`💡 说"安排[员工]做..."可一键派遣任务`);
  lines.push("");
  return lines;
}

// ── 本周聚焦 ────────────────────────────────────────────────────

function buildWeeklyFocus(db: OpcDatabase, companyId: string): string[] {
  const items: { priority: number; text: string }[] = [];

  // 逾期任务
  const overdueTasks = db.query(
    `SELECT title, due_date FROM opc_tasks
     WHERE company_id = ? AND status NOT IN ('done','completed','cancelled')
       AND due_date != '' AND due_date < date('now')
     ORDER BY due_date ASC LIMIT 2`,
    companyId,
  ) as { title: string; due_date: string }[];
  for (const t of overdueTasks) {
    items.push({ priority: 95, text: `完成逾期任务「${t.title}」（截止 ${t.due_date}）` });
  }

  // 即将到期合同
  const expiringContracts = db.query(
    `SELECT title, end_date FROM opc_contracts
     WHERE company_id = ? AND status = 'active' AND end_date != ''
       AND end_date <= date('now', '+14 days') AND end_date >= date('now')
     LIMIT 2`,
    companyId,
  ) as { title: string; end_date: string }[];
  for (const c of expiringContracts) {
    items.push({ priority: 90, text: `处理即将到期合同「${c.title}」（${c.end_date} 到期）` });
  }

  // 账龄发票催收
  const agingInvoices = db.query(
    `SELECT counterparty, total_amount 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 2`,
    companyId,
  ) as { counterparty: string; total_amount: number }[];
  for (const inv of agingInvoices) {
    items.push({ priority: 85, text: `催收应收款「${inv.counterparty}」${inv.total_amount.toLocaleString()} 元` });
  }

  // 久未联系客户
  const staleContacts = db.query(
    `SELECT name, last_contact_date FROM opc_contacts
     WHERE company_id = ? AND last_contact_date != '' AND last_contact_date < date('now', '-60 days')
     ORDER BY last_contact_date ASC LIMIT 2`,
    companyId,
  ) as { name: string; last_contact_date: string }[];
  for (const c of staleContacts) {
    const days = Math.floor((Date.now() - new Date(c.last_contact_date).getTime()) / 86400000);
    items.push({ priority: 80, text: `联系客户「${c.name}」（已 ${days} 天未联系）` });
  }

  // 本月未记录支出
  const thisMonth = new Date().toISOString().slice(0, 7);
  const monthExpenseCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'expense' AND strftime('%Y-%m', transaction_date) = ?",
    companyId, thisMonth,
  ) as CountRow).cnt;
  if (monthExpenseCount === 0) {
    const anyExpense = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'expense'", companyId,
    ) as CountRow).cnt;
    items.push({ priority: 60, text: anyExpense > 0 ? "补录本月支出记录" : "开始记录支出，掌握成本结构" });
  }

  // 无客户
  const contactCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (contactCount === 0) {
    items.push({ priority: 75, text: "添加第一个客户/联系人到客户池" });
  }

  // 增长阶段无项目
  const stageRow = db.queryOne(
    "SELECT stage FROM opc_company_stage WHERE company_id = ?", companyId,
  ) as { stage: string } | null;
  const stage = stageRow?.stage ?? "idea";

  const projectCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ? AND status IN ('active','planning')", companyId,
  ) as CountRow).cnt;
  if (projectCount === 0 && ["growth", "stable", "scaling"].includes(stage)) {
    items.push({ priority: 65, text: "创建项目来系统化管理当前业务" });
  }

  // 内容营销缺口
  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) {
    items.push({ priority: 50, text: "本月发布一篇内容，保持品牌曝光" });
  } else if (totalContent === 0 && ["early_revenue", "growth", "stable"].includes(stage)) {
    items.push({ priority: 55, text: "开始内容营销，建立个人品牌" });
  }

  // 无合同
  const contractCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  if (contractCount === 0 && ["early_revenue", "growth"].includes(stage)) {
    items.push({ priority: 68, text: "创建第一份服务合同，保护权益" });
  }

  if (items.length === 0) return [];

  items.sort((a, b) => b.priority - a.priority);
  const top = items.slice(0, 3);

  const lines: string[] = ["### 🎯 本周聚焦"];
  top.forEach((item, i) => {
    lines.push(`${i + 1}. ${item.text}`);
  });
  lines.push("");
  return lines;
}

// ── 阶段基准对标 ────────────────────────────────────────────────

type StageBenchmark = {
  revenueTarget: string;
  clientTarget: number;
  contractTarget: number;
  contentTarget: string;
  upgradeCondition: string;
};

const STAGE_BENCHMARKS: Record<string, StageBenchmark> = {
  idea: {
    revenueTarget: "获得第一笔收入",
    clientTarget: 1,
    contractTarget: 0,
    contentTarget: "开始规划",
    upgradeCondition: "有画布/客户/项目 → 验证阶段",
  },
  validation: {
    revenueTarget: "实现首次变现",
    clientTarget: 2,
    contractTarget: 1,
    contentTarget: "开始内容输出",
    upgradeCondition: "有收入 → 初始营收",
  },
  early_revenue: {
    revenueTarget: "月收入稳定 > 5,000 元",
    clientTarget: 3,
    contractTarget: 1,
    contentTarget: "保持月更",
    upgradeCondition: "累计收入 > 1万 + 2月以上有收入 → 增长阶段",
  },
  growth: {
    revenueTarget: "月均收入 > 1万",
    clientTarget: 5,
    contractTarget: 2,
    contentTarget: "多平台运营",
    upgradeCondition: "累计收入 > 10万 + 3月以上 + 2份活跃合同 → 稳定运营",
  },
  stable: {
    revenueTarget: "月均收入 > 5万",
    clientTarget: 10,
    contractTarget: 3,
    contentTarget: "品牌已建立",
    upgradeCondition: "累计收入 > 50万 + 6月以上 + 3名员工 → 规模化",
  },
  scaling: {
    revenueTarget: "月均收入 > 10万",
    clientTarget: 20,
    contractTarget: 5,
    contentTarget: "团队协作",
    upgradeCondition: "考虑退出或持续扩张",
  },
};

function buildStageBenchmark(db: OpcDatabase, companyId: string): string[] {
  const stageRow = db.queryOne(
    "SELECT stage FROM opc_company_stage WHERE company_id = ?", companyId,
  ) as { stage: string } | null;
  const stage = stageRow?.stage ?? "idea";
  const bench = STAGE_BENCHMARKS[stage];
  if (!bench) return [];

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

  const clientCheck = contactCount >= bench.clientTarget ? "✅" : `❌ (${contactCount}/${bench.clientTarget})`;
  const contractCheck = contractCount >= bench.contractTarget ? "✅" :
    bench.contractTarget === 0 ? "—" : `❌ (${contractCount}/${bench.contractTarget})`;

  const lines: string[] = [`### 🏁 阶段目标对标`];
  lines.push(`- 收入目标: ${bench.revenueTarget} | 客户: ${clientCheck} | 合同: ${contractCheck}`);
  lines.push(`- 📌 升级条件: ${bench.upgradeCondition}`);
  lines.push("");
  return lines;
}

// ── 自动 OKR 生成 ──────────────────────────────────────────────

type OKRItem = {
  objective: string;
  keyResults: { text: string; current: number; target: number }[];
};

function buildQuarterlyOKR(db: OpcDatabase, companyId: string): string[] {
  const stageRow = db.queryOne(
    "SELECT stage FROM opc_company_stage WHERE company_id = ?", companyId,
  ) as { stage: string } | null;
  const stage = stageRow?.stage ?? "idea";

  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 contactCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  const contractCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  const contentCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", companyId,
  ) as CountRow).cnt;

  let okr: OKRItem;

  switch (stage) {
    case "idea":
      okr = {
        objective: "完成商业模式验证，迈出第一步",
        keyResults: [
          { text: "完成 OPB 商业画布", current: (db.queryOne("SELECT COUNT(*) as cnt FROM opc_opb_canvas WHERE company_id = ?", companyId) as CountRow).cnt, target: 1 },
          { text: "添加潜在客户", current: contactCount, target: 2 },
          { text: "获得第一笔收入", current: totalIncome > 0 ? 1 : 0, target: 1 },
        ],
      };
      break;
    case "validation":
      okr = {
        objective: "证明有人愿意付费，实现首次变现",
        keyResults: [
          { text: "累计收入（元）", current: totalIncome, target: 5000 },
          { text: "正式客户数", current: contactCount, target: 3 },
          { text: "签约合同数", current: contractCount, target: 1 },
        ],
      };
      break;
    case "early_revenue":
      okr = {
        objective: "建立稳定收入来源，夯实业务基础",
        keyResults: [
          { text: "累计收入（元）", current: totalIncome, target: 10_000 },
          { text: "正式客户数", current: contactCount, target: 3 },
          { text: "签约合同数", current: contractCount, target: 1 },
        ],
      };
      break;
    case "growth":
      okr = {
        objective: "加速增长，实现收入多元化",
        keyResults: [
          { text: "累计收入（元）", current: totalIncome, target: 100_000 },
          { text: "客户数", current: contactCount, target: 5 },
          { text: "活跃合同数", current: contractCount, target: 2 },
        ],
      };
      break;
    case "stable":
      okr = {
        objective: "优化利润率，建立标准化体系",
        keyResults: [
          { text: "累计收入（元）", current: totalIncome, target: 500_000 },
          { text: "客户数", current: contactCount, target: 10 },
          { text: "内容发布篇数", current: contentCount, target: 20 },
        ],
      };
      break;
    case "scaling":
      okr = {
        objective: "规模化运营，考虑团队扩展",
        keyResults: [
          { text: "累计收入（元）", current: totalIncome, target: 1_000_000 },
          { text: "客户数", current: contactCount, target: 20 },
          { text: "活跃合同数", current: contractCount, target: 5 },
        ],
      };
      break;
    default:
      return [];
  }

  const lines: string[] = [`### 🎯 本季度 OKR（AI 生成）`];
  lines.push(`**O: ${okr.objective}**`);
  for (let i = 0; i < okr.keyResults.length; i++) {
    const kr = okr.keyResults[i];
    const pct = kr.target > 0 ? Math.min(Math.round((kr.current / kr.target) * 100), 100) : 0;
    const bar = pct >= 100 ? "✅" : `${pct}%`;
    lines.push(`- KR${i + 1}: ${kr.text} → ${kr.target.toLocaleString()} (当前: ${kr.current.toLocaleString()}, ${bar})`);
  }
  lines.push("");
  return lines;
}

/**
 * 构建单个公司的智能简报上下文（用于 Path A — 公司 Agent）。
 * 按优先级从 opc_insights / opc_celebrations / opc_alerts / opc_company_stage 组装。
 */
export function buildBriefingContext(db: OpcDatabase, companyId: string): string {
  const sections: string[] = [];

  // 0. 健康评分
  const health = computeHealthScore(db, companyId);
  sections.push(`### 公司健康评分: ${healthScoreEmoji(health.total)} ${health.total}/100`);
  const dimText = health.dimensions.map(d =>
    `${d.name} ${Math.round(d.score)}分(${Math.round(d.weight * 100)}%) — ${d.detail}`,
  );
  sections.push(dimText.map(t => `- ${t}`).join("\n"));
  sections.push("");

  // 0a2. 增长评分卡
  const scorecard = computeGrowthScorecard(db, companyId);
  sections.push(`### 📋 增长评分卡: ${gradeEmoji(scorecard.overall)} 总评 ${scorecard.overall}`);
  sections.push(scorecard.dimensions.map(d => `- ${gradeEmoji(d.grade)} ${d.name}: **${d.grade}** — ${d.detail}`).join("\n"));

  // 0a3. 历史对比
  const lastBriefing = getLastBriefing(db, companyId);
  if (lastBriefing) {
    const healthDiff = health.total - lastBriefing.healthScore;
    const healthArrow = healthDiff > 0 ? `↑${healthDiff}` : healthDiff < 0 ? `↓${Math.abs(healthDiff)}` : "→";
    sections.push("");
    sections.push(`### 📈 vs 上次 (${lastBriefing.date})`);
    sections.push(`- 健康评分: ${lastBriefing.healthScore} → ${health.total} (${healthArrow})`);
    const currentIncome = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", companyId,
    ) as SumRow).total;
    if (lastBriefing.totalIncome !== currentIncome) {
      const incomeDiff = currentIncome - lastBriefing.totalIncome;
      sections.push(`- 累计收入: ${lastBriefing.totalIncome.toLocaleString()} → ${currentIncome.toLocaleString()} (+${incomeDiff.toLocaleString()} 元)`);
    }
    if (lastBriefing.scorecardGrade !== scorecard.overall) {
      sections.push(`- 评级: ${lastBriefing.scorecardGrade} → ${scorecard.overall}`);
    }
  }
  sections.push("");

  // 0a4. 阶段基准对标
  sections.push(...buildStageBenchmark(db, companyId));

  // 0a5. 本季度 OKR
  sections.push(...buildQuarterlyOKR(db, companyId));

  // 0b. 核心指标一览
  sections.push(...buildDashboardMetrics(db, companyId));

  // 0c. 现金流预测
  sections.push(...buildCashFlowForecast(db, companyId));

  // 1. 未展示的庆祝（最多 2 条）
  const celebrations = db.query(
    `SELECT id, celebration_type, title, message FROM opc_celebrations
     WHERE company_id = ? AND shown = 0
     ORDER BY created_at DESC LIMIT 2`,
    companyId,
  ) as CelebrationRow[];

  if (celebrations.length > 0) {
    sections.push("### 成就达成");
    for (const c of celebrations) {
      sections.push(`- **${c.title}** — ${c.message}`);
    }
    // 标记为已展示
    for (const c of celebrations) {
      db.execute("UPDATE opc_celebrations SET shown = 1 WHERE id = ?", c.id);
    }
    sections.push("");
  }

  // 2. 待处理告警（最多 3 条，按严重程度排序）
  const alerts = db.query(
    `SELECT id, title, message, severity FROM opc_alerts
     WHERE company_id = ? AND status = 'active'
     ORDER BY
       CASE severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END,
       created_at DESC
     LIMIT 3`,
    companyId,
  ) as AlertRow[];

  if (alerts.length > 0) {
    sections.push("### 待处理告警");
    for (const a of alerts) {
      const icon = a.severity === "critical" ? "[紧急]" : a.severity === "warning" ? "[注意]" : "[提示]";
      sections.push(`- ${icon} **${a.title}** — ${a.message}`);
    }
    sections.push("");
  }

  // 2b. 本周聚焦
  sections.push(...buildWeeklyFocus(db, companyId));

  // 3. 智能洞察（最多 10 条，员工晨会需要更多观察数据）
  const insights = getActiveInsights(db, companyId, 10);

  // 分类整理
  const nextSteps = insights.filter(i => i.insight_type === "next_step");
  const dataGaps = insights.filter(i => i.insight_type === "data_gap");
  const trends = insights.filter(i => i.insight_type === "trend" || i.insight_type === "risk" || i.insight_type === "opportunity");
  const staffObs = insights.filter(i => i.insight_type === "staff_observation");

  if (trends.length > 0) {
    sections.push("### AI 分析观察");
    for (const t of trends) {
      sections.push(`- **${t.title}** — ${t.message}`);
    }
    sections.push("");
  }

  // 员工晨会（按角色分组，含任务状态 + 洞察）
  const standupLines = buildEmployeeStandup(db, companyId, staffObs);
  if (standupLines.length > 0) {
    sections.push(...standupLines);
  }

  if (nextSteps.length > 0) {
    sections.push("### 建议下一步行动");
    for (const n of nextSteps) {
      sections.push(`- **${n.title}** — ${n.message}`);
    }
    sections.push("");
  }

  if (dataGaps.length > 0) {
    sections.push("### 数据完善建议");
    for (const d of dataGaps) {
      sections.push(`- **${d.title}** — ${d.message}`);
    }
    sections.push("");
  }

  if (sections.length === 0) return "";

  const header = ["", "## 今日智能简报", ""];
  const footer = [
    "---",
    "**交互指令**：先简洁汇报（健康评分 + 紧急事项 + 关键变化），然后根据数据主动建议今天应优先处理什么。",
    "遵循「CEO幕僚长交互规范」和「数据闭环规范」：主动追问、主动建议、所有成果必须调用工具写入。",
  ];

  return [...header, ...sections, ...footer].join("\n");
}

/**
 * 构建跨公司组合概览（用于 Path C — 普通对话）。
 * 每家公司一行摘要 + top 3 洞察。
 */
export function buildPortfolioBriefing(db: OpcDatabase): string {
  const companies = db.query(
    "SELECT id, name, status FROM opc_companies ORDER BY created_at DESC LIMIT 10",
  ) as CompanyRow[];

  if (companies.length === 0) return "";

  // 组合级汇总
  let portfolioIncome = 0;
  let portfolioExpense = 0;
  for (const c of companies) {
    portfolioIncome += (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", c.id,
    ) as SumRow).total;
    portfolioExpense += (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'", c.id,
    ) as SumRow).total;
  }

  const lines: string[] = ["", "## 公司组合概览", ""];
  lines.push(`**组合汇总**: ${companies.length} 家公司 | 总收入: ${portfolioIncome.toLocaleString()} 元 | 总支出: ${portfolioExpense.toLocaleString()} 元 | 净利润: ${(portfolioIncome - portfolioExpense).toLocaleString()} 元`);
  lines.push("");

  for (const c of companies) {
    // 阶段
    const stage = db.queryOne(
      "SELECT stage, stage_label FROM opc_company_stage WHERE company_id = ?", c.id,
    ) as StageRow | null;
    const stageLabel = stage?.stage_label ?? "未检测";

    // 活跃告警数
    const alertCount = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_alerts WHERE company_id = ? AND status = 'active'",
      c.id,
    ) as { cnt: number }).cnt;

    // 新成就数（未展示的）
    const newCelebrations = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_celebrations WHERE company_id = ? AND shown = 0",
      c.id,
    ) as { cnt: number }).cnt;

    // 健康评分
    const health = computeHealthScore(db, c.id);

    let summary = `- ${healthScoreEmoji(health.total)} **${c.name}**（${stageLabel} | ${health.total}分）`;
    const badges: string[] = [];
    if (alertCount > 0) badges.push(`${alertCount} 条告警`);
    if (newCelebrations > 0) badges.push(`${newCelebrations} 个新成就`);
    if (badges.length > 0) summary += ` — ${badges.join("、")}`;

    lines.push(summary);
  }

  // Top 3 跨公司洞察
  const topInsights = db.query(
    `SELECT i.title, i.message, i.priority, c.name as company_name
     FROM opc_insights i
     LEFT JOIN opc_companies c ON i.company_id = c.id
     WHERE i.status = 'active' AND (i.expires_at = '' OR i.expires_at > datetime('now'))
     ORDER BY i.priority DESC, i.created_at DESC
     LIMIT 3`,
  ) as { title: string; message: string; priority: number; company_name: string }[];

  if (topInsights.length > 0) {
    lines.push("");
    lines.push("### 重点关注");
    for (const ins of topInsights) {
      lines.push(`- [${ins.company_name}] **${ins.title}** — ${ins.message}`);
    }
  }

  // 跨公司未展示庆祝
  const newCelebs = db.query(
    `SELECT cel.title, cel.message, c.name as company_name
     FROM opc_celebrations cel
     LEFT JOIN opc_companies c ON cel.company_id = c.id
     WHERE cel.shown = 0
     ORDER BY cel.created_at DESC
     LIMIT 3`,
  ) as { title: string; message: string; company_name: string }[];

  if (newCelebs.length > 0) {
    lines.push("");
    lines.push("### 最新成就");
    for (const cel of newCelebs) {
      lines.push(`- [${cel.company_name}] **${cel.title}** — ${cel.message}`);
    }
  }

  return lines.join("\n");
}

// ── 增长评分卡 ──────────────────────────────────────────────────

type GradeDimension = {
  name: string;
  grade: string;
  score: number;
  detail: string;
};

function letterGrade(score: number): string {
  if (score >= 90) return "A";
  if (score >= 75) return "B";
  if (score >= 55) return "C";
  if (score >= 35) return "D";
  return "F";
}

function gradeEmoji(g: string): string {
  if (g === "A") return "🟢";
  if (g === "B") return "🔵";
  if (g === "C") return "🟡";
  if (g === "D") return "🟠";
  return "🔴";
}

/**
 * 计算增长评分卡（5 维度 A–F 评级）。
 */
export function computeGrowthScorecard(db: OpcDatabase, companyId: string): {
  overall: string;
  overallScore: number;
  dimensions: GradeDimension[];
} {
  const dims: GradeDimension[] = [];

  // ── 财务成熟度 ──
  const fin = (() => {
    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 totalExpense = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'", companyId,
    ) as SumRow).total;
    const revenueMonths = (db.queryOne(
      "SELECT COUNT(DISTINCT strftime('%Y-%m', transaction_date)) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'income' AND amount > 0", companyId,
    ) as CountRow).cnt;
    let score = 0;
    if (totalIncome > 0) score += 20;
    if (totalIncome > 10_000) score += 15;
    if (totalIncome > 50_000) score += 15;
    if (totalExpense > 0) score += 10; // 记录支出
    if (revenueMonths >= 2) score += 15;
    if (revenueMonths >= 6) score += 10;
    if (totalIncome > totalExpense && totalExpense > 0) score += 15; // 盈利
    const profitRate = totalIncome > 0 ? ((totalIncome - totalExpense) / totalIncome * 100) : 0;
    const detail = totalIncome === 0 ? "尚无收入" :
      `收入 ${totalIncome.toLocaleString()} 元, ${revenueMonths} 个月, 利润率 ${Math.round(profitRate)}%`;
    return { score: Math.min(score, 100), detail };
  })();
  dims.push({ name: "财务成熟度", grade: letterGrade(fin.score), score: fin.score, detail: fin.detail });

  // ── 客户基础 ──
  const cust = (() => {
    const contactCount = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", companyId,
    ) as CountRow).cnt;
    const counterpartyCount = (db.queryOne(
      "SELECT COUNT(DISTINCT counterparty) as cnt FROM opc_transactions WHERE company_id = ? AND type = 'income' AND counterparty != ''", companyId,
    ) as CountRow).cnt;
    const activeContacts = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ? AND last_contact_date > date('now', '-90 days')", companyId,
    ) as CountRow).cnt;
    let score = 0;
    if (contactCount > 0) score += 25;
    if (contactCount >= 3) score += 15;
    if (contactCount >= 5) score += 10;
    if (counterpartyCount >= 2) score += 20; // 多元收入来源
    if (counterpartyCount >= 3) score += 10;
    if (activeContacts > 0) score += 20; // 定期联系
    const detail = contactCount === 0 ? "尚无客户" :
      `${contactCount} 个客户, ${counterpartyCount} 个收入来源, ${activeContacts} 个活跃联系`;
    return { score: Math.min(score, 100), detail };
  })();
  dims.push({ name: "客户基础", grade: letterGrade(cust.score), score: cust.score, detail: cust.detail });

  // ── 运营体系 ──
  const ops = (() => {
    const hasProject = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const hasContract = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const hasInvoice = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_invoices WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const hasStaff = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_staff_config WHERE company_id = ? AND enabled = 1", companyId,
    ) as CountRow).cnt > 0;
    const hasCanvas = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_opb_canvas WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const taskCount = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_tasks WHERE company_id = ?", companyId,
    ) as CountRow).cnt;
    let score = 0;
    if (hasCanvas) score += 20;
    if (hasProject) score += 20;
    if (hasContract) score += 20;
    if (hasInvoice) score += 15;
    if (hasStaff) score += 15;
    if (taskCount > 0) score += 10;
    const items = [hasCanvas && "画布", hasProject && "项目", hasContract && "合同", hasInvoice && "发票", hasStaff && "AI员工"].filter(Boolean);
    const detail = items.length === 0 ? "尚未建立" : `已建立: ${items.join("、")}`;
    return { score: Math.min(score, 100), detail };
  })();
  dims.push({ name: "运营体系", grade: letterGrade(ops.score), score: ops.score, detail: ops.detail });

  // ── 品牌建设 ──
  const brand = (() => {
    const contentTotal = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ?", companyId,
    ) as CountRow).cnt;
    const publishedCount = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_media_content WHERE company_id = ? AND status = 'published'", companyId,
    ) as CountRow).cnt;
    const platforms = (db.queryOne(
      "SELECT COUNT(DISTINCT platform) as cnt FROM opc_media_content WHERE company_id = ? AND platform != ''", companyId,
    ) as CountRow).cnt;
    const thisMonth = new Date().toISOString().slice(0, 7);
    const recentContent = (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;
    let score = 0;
    if (contentTotal > 0) score += 25;
    if (contentTotal >= 5) score += 15;
    if (contentTotal >= 10) score += 10;
    if (publishedCount > 0) score += 15;
    if (platforms >= 2) score += 15;
    if (recentContent > 0) score += 20; // 本月有发布
    const detail = contentTotal === 0 ? "尚无内容" :
      `${contentTotal} 篇内容, ${publishedCount} 篇已发布, ${platforms} 个平台`;
    return { score: Math.min(score, 100), detail };
  })();
  dims.push({ name: "品牌建设", grade: letterGrade(brand.score), score: brand.score, detail: brand.detail });

  // ── 合规完善度 ──
  const comp = (() => {
    const hasTax = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_tax_filings WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const hasContract = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const hasInvoice = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_invoices WHERE company_id = ?", companyId,
    ) as CountRow).cnt > 0;
    const activeAlerts = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_alerts WHERE company_id = ? AND status = 'active'", companyId,
    ) as CountRow).cnt;
    let score = 30; // baseline
    if (hasTax) score += 25;
    if (hasContract) score += 20;
    if (hasInvoice) score += 15;
    if (activeAlerts === 0) score += 10;
    else score -= activeAlerts * 5;
    const items = [hasTax && "税务", hasContract && "合同", hasInvoice && "发票"].filter(Boolean);
    const detail = items.length === 0 ? "尚无合规记录" :
      `已有: ${items.join("、")}${activeAlerts > 0 ? `, ${activeAlerts} 条告警` : ""}`;
    return { score: Math.max(Math.min(score, 100), 0), detail };
  })();
  dims.push({ name: "合规完善度", grade: letterGrade(comp.score), score: comp.score, detail: comp.detail });

  const overallScore = Math.round(dims.reduce((s, d) => s + d.score, 0) / dims.length);
  return { overall: letterGrade(overallScore), overallScore, dimensions: dims };
}

// ── 每日简报快照 ────────────────────────────────────────────────

/** 保存每日简报快照到 opc_briefings（每个公司每天仅一条） */
export function saveDailyBriefing(db: OpcDatabase, companyId: string): void {
  const today = new Date().toISOString().slice(0, 10);
  // 检查今日是否已有快照
  const existing = db.queryOne(
    "SELECT id FROM opc_briefings WHERE company_id = ? AND briefing_date = ?",
    companyId, today,
  );
  if (existing) return; // 今日已保存

  const stageRow = db.queryOne(
    "SELECT stage FROM opc_company_stage WHERE company_id = ?", companyId,
  ) as { stage: string } | null;

  const health = computeHealthScore(db, companyId);
  const scorecard = computeGrowthScorecard(db, companyId);

  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 totalExpense = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'", companyId,
  ) as SumRow).total;
  const contactCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", companyId,
  ) as CountRow).cnt;
  const contractCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ? AND status = 'active'", companyId,
  ) as CountRow).cnt;

  const summaryJson = JSON.stringify({
    health_score: health.total,
    health_dimensions: health.dimensions,
    scorecard_grade: scorecard.overall,
    scorecard_dimensions: scorecard.dimensions,
    total_income: totalIncome,
    total_expense: totalExpense,
    net_profit: totalIncome - totalExpense,
    contact_count: contactCount,
    contract_count: contractCount,
  });

  const insights = getActiveInsights(db, companyId, 10);
  const insightsJson = JSON.stringify(insights.map(i => ({
    type: i.insight_type, title: i.title, priority: i.priority,
  })));

  db.execute(
    `INSERT INTO opc_briefings (id, company_id, briefing_date, stage, health_score, summary_json, insights_json, next_steps_json, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, '[]', datetime('now'))`,
    db.genId(), companyId, today, stageRow?.stage ?? "idea", health.total, summaryJson, insightsJson,
  );
}

/** 获取上一次的简报快照（用于历史对比） */
export function getLastBriefing(db: OpcDatabase, companyId: string): {
  date: string;
  healthScore: number;
  totalIncome: number;
  totalExpense: number;
  contactCount: number;
  scorecardGrade: string;
} | null {
  const today = new Date().toISOString().slice(0, 10);
  const row = db.queryOne(
    `SELECT briefing_date, health_score, summary_json FROM opc_briefings
     WHERE company_id = ? AND briefing_date < ?
     ORDER BY briefing_date DESC LIMIT 1`,
    companyId, today,
  ) as { briefing_date: string; health_score: number; summary_json: string } | null;

  if (!row) return null;

  try {
    const summary = JSON.parse(row.summary_json) as Record<string, unknown>;
    return {
      date: row.briefing_date,
      healthScore: row.health_score,
      totalIncome: (summary.total_income as number) ?? 0,
      totalExpense: (summary.total_expense as number) ?? 0,
      contactCount: (summary.contact_count as number) ?? 0,
      scorecardGrade: (summary.scorecard_grade as string) ?? "?",
    };
  } catch {
    return null;
  }
}

/** 构建 Webhook 每日推送文本 */
export function buildWebhookBriefingText(db: OpcDatabase): string {
  const companies = db.query(
    "SELECT id, name FROM opc_companies ORDER BY created_at DESC LIMIT 10",
  ) as { id: string; name: string }[];

  if (companies.length === 0) return "";

  const lines: string[] = [`📋 星环OPC每日简报 (${new Date().toISOString().slice(0, 10)})`, ""];

  for (const c of companies) {
    const health = computeHealthScore(db, c.id);
    const scorecard = computeGrowthScorecard(db, c.id);
    const stageRow = db.queryOne(
      "SELECT stage_label FROM opc_company_stage WHERE company_id = ?", c.id,
    ) as { stage_label: string } | null;

    const totalIncome = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", c.id,
    ) as SumRow).total;

    lines.push(`【${c.name}】${stageRow?.stage_label ?? "未知"} | 健康 ${health.total}分 | 评级 ${scorecard.overall}`);
    lines.push(`  收入: ${totalIncome.toLocaleString()} 元 | ${scorecard.dimensions.map(d => `${d.name}:${d.grade}`).join(" ")}`);

    // 未展示庆祝
    const celebs = db.query(
      "SELECT title FROM opc_celebrations WHERE company_id = ? AND shown = 0 LIMIT 2", c.id,
    ) as { title: string }[];
    if (celebs.length > 0) {
      lines.push(`  🎉 ${celebs.map(x => x.title).join("、")}`);
    }

    // 紧急告警
    const alerts = db.query(
      "SELECT title FROM opc_alerts WHERE company_id = ? AND status = 'active' AND severity IN ('critical','warning') LIMIT 2", c.id,
    ) as { title: string }[];
    if (alerts.length > 0) {
      lines.push(`  ⚠️ ${alerts.map(x => x.title).join("、")}`);
    }

    lines.push("");
  }

  lines.push("— 星环OPC中心 AI 助手");
  return lines.join("\n");
}