/**
 * 星环OPC中心 — 每日简报生成器
 *
 * 生成每日待办事项、经营数据分析、AI 员工汇报。
 */

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

type TodoItem = {
  type: string;
  title: string;
  priority: "urgent" | "high" | "normal";
  dueDate?: string;
  description: string;
};

type MetricData = {
  label: string;
  value: number | string;
  trend?: string;
  unit?: string;
};

type StaffReport = {
  role: string;
  roleName: string;
  observations: string[];
  suggestions: string[];
  tasks: { title: string; status: string }[];
};

/** 生成每日简报 */
export function generateDailyBrief(db: OpcDatabase, companyId: string): {
  todos: TodoItem[];
  metrics: MetricData[];
  staffReports: StaffReport[];
  summary: string;
} {
  const todos = collectTodoItems(db, companyId);
  const metrics = analyzeBusinessMetrics(db, companyId);
  const staffReports = generateStaffReports(db, companyId);

  // 生成摘要
  const urgentCount = todos.filter(t => t.priority === "urgent").length;
  const summary = urgentCount > 0
    ? `今日有 ${urgentCount} 项紧急待办，${todos.length} 项总待办。`
    : todos.length > 0
    ? `今日有 ${todos.length} 项待办事项。`
    : "今日暂无紧急待办事项。";

  return {
    todos,
    metrics,
    staffReports,
    summary,
  };
}

/** 收集待办事项 */
function collectTodoItems(db: OpcDatabase, companyId: string): TodoItem[] {
  const todos: TodoItem[] = [];
  const today = new Date().toISOString().slice(0, 10);

  // 1. 逾期款项催收
  const overduePayments = db.query(
    `SELECT id, direction, amount, due_date, counterparty
     FROM opc_payments
     WHERE company_id = ? AND status IN ('pending', 'partial')
       AND due_date != '' AND due_date < ?
     ORDER BY due_date ASC
     LIMIT 5`,
    companyId, today,
  ) as { id: string; direction: string; amount: number; due_date: string; counterparty: string }[] | undefined;

  for (const payment of overduePayments ?? []) {
    const daysOverdue = Math.floor(
      (Date.now() - new Date(payment.due_date).getTime()) / 86400000,
    );
    todos.push({
      type: "overdue_payment",
      title: `催收逾期款项：${payment.counterparty}`,
      priority: daysOverdue > 30 ? "urgent" : "high",
      dueDate: payment.due_date,
      description: `${payment.direction === "receivable" ? "应收" : "应付"}款项 ${payment.amount.toLocaleString()} 元，已逾期 ${daysOverdue} 天`,
    });
  }

  // 2. 合同到期提醒
  const expiringContracts = db.query(
    `SELECT id, title, end_date, counterparty
     FROM opc_contracts
     WHERE company_id = ? AND status = 'active'
       AND end_date != ''
       AND end_date >= ? AND end_date <= date(?, '+30 days')
     ORDER BY end_date ASC
     LIMIT 5`,
    companyId, today, today,
  ) as { id: string; title: string; end_date: string; counterparty: string }[] | undefined;

  for (const contract of expiringContracts ?? []) {
    const daysUntilExpiry = Math.floor(
      (new Date(contract.end_date).getTime() - Date.now()) / 86400000,
    );
    todos.push({
      type: "expiring_contract",
      title: `合同即将到期：${contract.title}`,
      priority: daysUntilExpiry <= 7 ? "urgent" : daysUntilExpiry <= 14 ? "high" : "normal",
      dueDate: contract.end_date,
      description: `与 ${contract.counterparty} 的合同将在 ${daysUntilExpiry} 天后到期，需要续约或重新协商`,
    });
  }

  // 2.1. 里程碑到期提醒（订单闭环）
  const dueMilestones = db.query(
    `SELECT m.id, m.title, m.due_date, m.amount, c.title as contract_title, c.counterparty
     FROM opc_contract_milestones m
     JOIN opc_contracts c ON m.contract_id = c.id
     WHERE m.company_id = ? AND m.status IN ('pending', 'in_progress')
       AND m.due_date != ''
       AND m.due_date >= ? AND m.due_date <= date(?, '+7 days')
     ORDER BY m.due_date ASC
     LIMIT 5`,
    companyId, today, today,
  ) as { id: string; title: string; due_date: string; amount: number; contract_title: string; counterparty: string }[] | undefined;

  for (const milestone of dueMilestones ?? []) {
    const daysUntilDue = Math.floor(
      (new Date(milestone.due_date).getTime() - Date.now()) / 86400000,
    );
    todos.push({
      type: "milestone_due",
      title: `里程碑即将到期：${milestone.title}`,
      priority: daysUntilDue <= 3 ? "urgent" : "high",
      dueDate: milestone.due_date,
      description: `合同「${milestone.contract_title}」的里程碑将在 ${daysUntilDue} 天后到期，应收款 ${milestone.amount.toLocaleString()} 元`,
    });
  }

  // 3. 税务申报提醒
  const pendingTaxFilings = db.query(
    `SELECT id, period, tax_type, due_date, tax_amount
     FROM opc_tax_filings
     WHERE company_id = ? AND status = 'pending'
       AND due_date != '' AND due_date >= ?
     ORDER BY due_date ASC
     LIMIT 3`,
    companyId, today,
  ) as { id: string; period: string; tax_type: string; due_date: string; tax_amount: number }[] | undefined;

  for (const filing of pendingTaxFilings ?? []) {
    const daysUntilDue = Math.floor(
      (new Date(filing.due_date).getTime() - Date.now()) / 86400000,
    );
    todos.push({
      type: "tax_filing",
      title: `税务申报：${filing.period} ${filing.tax_type}`,
      priority: daysUntilDue <= 3 ? "urgent" : daysUntilDue <= 7 ? "high" : "normal",
      dueDate: filing.due_date,
      description: `应纳税额 ${filing.tax_amount.toLocaleString()} 元，还有 ${daysUntilDue} 天截止`,
    });
  }

  // 4. 客户回访提醒
  const staleContacts = db.query(
    `SELECT id, name, last_contact_date, pipeline_stage
     FROM opc_contacts
     WHERE company_id = ?
       AND last_contact_date != ''
       AND last_contact_date < date('now', '-30 days')
       AND pipeline_stage NOT IN ('lost', 'won')
     ORDER BY last_contact_date ASC
     LIMIT 5`,
    companyId,
  ) as { id: string; name: string; last_contact_date: string; pipeline_stage: string }[] | undefined;

  for (const contact of staleContacts ?? []) {
    const daysSinceContact = Math.floor(
      (Date.now() - new Date(contact.last_contact_date).getTime()) / 86400000,
    );
    todos.push({
      type: "customer_follow_up",
      title: `客户回访：${contact.name}`,
      priority: daysSinceContact > 90 ? "high" : "normal",
      description: `已 ${daysSinceContact} 天未联系，当前阶段：${contact.pipeline_stage}`,
    });
  }

  // 5. 逾期任务
  const overdueTasks = db.query(
    `SELECT id, title, due_date, priority
     FROM opc_tasks
     WHERE company_id = ? AND status NOT IN ('done', 'completed', 'cancelled')
       AND due_date != '' AND due_date < ?
     ORDER BY due_date ASC
     LIMIT 5`,
    companyId, today,
  ) as { id: string; title: string; due_date: string; priority: string }[] | undefined;

  for (const task of overdueTasks ?? []) {
    const daysOverdue = Math.floor(
      (Date.now() - new Date(task.due_date).getTime()) / 86400000,
    );
    todos.push({
      type: "overdue_task",
      title: `逾期任务：${task.title}`,
      priority: task.priority === "urgent" || daysOverdue > 7 ? "urgent" : "high",
      dueDate: task.due_date,
      description: `已逾期 ${daysOverdue} 天`,
    });
  }

  // 按优先级排序
  const priorityOrder = { urgent: 1, high: 2, normal: 3 };
  todos.sort((a, b) => priorityOrder[a.priority] - priorityOrder[b.priority]);

  return todos;
}

/** 分析经营数据 */
function analyzeBusinessMetrics(db: OpcDatabase, companyId: string): MetricData[] {
  const metrics: MetricData[] = [];
  const today = new Date();
  const thisMonth = today.toISOString().slice(0, 7);
  const lastMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1).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 { total: number } | null)?.total ?? 0;

  const lastMonthIncome = (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, lastMonth,
  ) as { total: number } | null)?.total ?? 0;

  const incomeTrend = lastMonthIncome > 0
    ? `${((monthIncome - lastMonthIncome) / lastMonthIncome * 100).toFixed(1)}%`
    : undefined;

  metrics.push({
    label: "本月收入",
    value: monthIncome,
    unit: "元",
    trend: incomeTrend,
  });

  // 本月支出
  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 { total: number } | null)?.total ?? 0;

  metrics.push({
    label: "本月支出",
    value: monthExpense,
    unit: "元",
  });

  // 本月利润
  const monthProfit = monthIncome - monthExpense;
  metrics.push({
    label: "本月利润",
    value: monthProfit,
    unit: "元",
  });

  // 现金余额
  const totalIncome = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'",
    companyId,
  ) as { total: number } | null)?.total ?? 0;

  const totalExpense = (db.queryOne(
    "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'",
    companyId,
  ) as { total: number } | null)?.total ?? 0;

  const cashBalance = totalIncome - totalExpense;

  // 计算可撑月数
  const avgMonthlyBurn = monthExpense > 0 ? monthExpense : totalExpense / 12;
  const runway = avgMonthlyBurn > 0 ? Math.floor(cashBalance / avgMonthlyBurn) : 999;

  metrics.push({
    label: "现金余额",
    value: cashBalance,
    unit: "元",
    trend: runway < 3 ? `⚠️ 仅够 ${runway} 个月` : runway < 12 ? `可撑 ${runway} 个月` : undefined,
  });

  // 应收账款
  const receivables = (db.queryOne(
    "SELECT COALESCE(SUM(amount - paid_amount), 0) as total FROM opc_payments WHERE company_id = ? AND direction = 'receivable' AND status IN ('pending', 'partial')",
    companyId,
  ) as { total: number } | null)?.total ?? 0;

  if (receivables > 0) {
    const overdueReceivables = (db.queryOne(
      "SELECT COALESCE(SUM(amount - paid_amount), 0) as total FROM opc_payments WHERE company_id = ? AND direction = 'receivable' AND status IN ('pending', 'partial') AND due_date < date('now')",
      companyId,
    ) as { total: number } | null)?.total ?? 0;

    metrics.push({
      label: "应收账款",
      value: receivables,
      unit: "元",
      trend: overdueReceivables > 0 ? `⚠️ ${overdueReceivables.toLocaleString()} 元已逾期` : undefined,
    });
  }

  return metrics;
}

/** 生成 AI 员工汇报 */
function generateStaffReports(db: OpcDatabase, companyId: string): StaffReport[] {
  const reports: StaffReport[] = [];

  // 获取启用的 AI 员工
  const staffConfigs = db.query(
    "SELECT role, role_name FROM opc_staff_config WHERE company_id = ? AND enabled = 1",
    companyId,
  ) as { role: string; role_name: string }[] | undefined;

  for (const staff of staffConfigs ?? []) {
    const observations: string[] = [];
    const suggestions: string[] = [];
    const tasks: { title: string; status: string }[] = [];

    // 获取该员工的任务
    const staffTasks = db.query(
      "SELECT title, status FROM opc_staff_tasks WHERE company_id = ? AND staff_role = ? AND status IN ('pending', 'in_progress') ORDER BY assigned_at DESC LIMIT 3",
      companyId, staff.role,
    ) as { title: string; status: string }[] | undefined;

    tasks.push(...(staffTasks ?? []));

    // 根据角色生成观察和建议
    switch (staff.role) {
      case "finance": {
        // 财务顾问
        const cashBalance = (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 { total: number } | null)?.total ?? 0;

        if (cashBalance < 0) {
          observations.push("现金流为负，需要关注");
          suggestions.push("建议尽快增加收入或控制支出");
        }

        const overdueReceivables = (db.queryOne(
          "SELECT COUNT(*) as cnt FROM opc_payments WHERE company_id = ? AND direction = 'receivable' AND status IN ('pending', 'partial') AND due_date < date('now')",
          companyId,
        ) as { cnt: number } | null)?.cnt ?? 0;

        if (overdueReceivables > 0) {
          observations.push(`有 ${overdueReceivables} 笔应收款逾期`);
          suggestions.push("建议及时催收逾期款项");
        }

        // 应收风险分层统计（订单闭环新增）
        const warningReceivables = (db.queryOne(
          "SELECT COUNT(*) as cnt, COALESCE(SUM(amount - paid_amount), 0) as total FROM opc_payments WHERE company_id = ? AND direction = 'receivable' AND risk_level = 'warning'",
          companyId,
        ) as { cnt: number; total: number } | null);

        const criticalReceivables = (db.queryOne(
          "SELECT COUNT(*) as cnt, COALESCE(SUM(amount - paid_amount), 0) as total FROM opc_payments WHERE company_id = ? AND direction = 'receivable' AND risk_level = 'critical'",
          companyId,
        ) as { cnt: number; total: number } | null);

        if (criticalReceivables && criticalReceivables.cnt > 0) {
          observations.push(`有 ${criticalReceivables.cnt} 笔严重逾期应收（>30天），合计 ${criticalReceivables.total.toLocaleString()} 元`);
          suggestions.push("建议升级催收方式或考虑法律途径");
        } else if (warningReceivables && warningReceivables.cnt > 0) {
          observations.push(`有 ${warningReceivables.cnt} 笔预警应收（8-30天），合计 ${warningReceivables.total.toLocaleString()} 元`);
          suggestions.push("建议电话跟进，避免逾期恶化");
        }

        break;
      }

      case "legal": {
        // 法务助理
        const expiringContracts = (db.queryOne(
          "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ? AND status = 'active' AND end_date != '' AND end_date <= date('now', '+30 days')",
          companyId,
        ) as { cnt: number } | null)?.cnt ?? 0;

        if (expiringContracts > 0) {
          observations.push(`有 ${expiringContracts} 份合同即将到期`);
          suggestions.push("建议提前联系客户商讨续约事宜");
        }

        break;
      }

      case "ops": {
        // 运营经理
        const staleContacts = (db.queryOne(
          "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ? AND last_contact_date < date('now', '-30 days')",
          companyId,
        ) as { cnt: number } | null)?.cnt ?? 0;

        if (staleContacts > 0) {
          observations.push(`有 ${staleContacts} 个客户超过 30 天未联系`);
          suggestions.push("建议定期回访客户，维护客户关系");
        }

        break;
      }
    }

    if (observations.length > 0 || suggestions.length > 0 || tasks.length > 0) {
      reports.push({
        role: staff.role,
        roleName: staff.role_name,
        observations,
        suggestions,
        tasks,
      });
    }
  }

  return reports;
}

/** 格式化每日简报为 Markdown */
export function formatDailyBriefMarkdown(brief: ReturnType<typeof generateDailyBrief>): string {
  const lines: string[] = [];

  lines.push("# 📋 每日简报");
  lines.push("");
  lines.push(`**${new Date().toISOString().slice(0, 10)}** | ${brief.summary}`);
  lines.push("");

  // 待办事项
  if (brief.todos.length > 0) {
    lines.push("## 📌 今日待办");
    lines.push("");

    const urgent = brief.todos.filter(t => t.priority === "urgent");
    const high = brief.todos.filter(t => t.priority === "high");
    const normal = brief.todos.filter(t => t.priority === "normal");

    if (urgent.length > 0) {
      lines.push("### 🔴 紧急");
      for (const todo of urgent) {
        lines.push(`- **${todo.title}**`);
        lines.push(`  ${todo.description}`);
        if (todo.dueDate) lines.push(`  截止：${todo.dueDate}`);
      }
      lines.push("");
    }

    if (high.length > 0) {
      lines.push("### 🟡 重要");
      for (const todo of high) {
        lines.push(`- **${todo.title}**`);
        lines.push(`  ${todo.description}`);
        if (todo.dueDate) lines.push(`  截止：${todo.dueDate}`);
      }
      lines.push("");
    }

    if (normal.length > 0) {
      lines.push("### 🟢 一般");
      for (const todo of normal) {
        lines.push(`- ${todo.title}`);
      }
      lines.push("");
    }
  }

  // 经营数据
  if (brief.metrics.length > 0) {
    lines.push("## 📊 经营数据");
    lines.push("");
    for (const metric of brief.metrics) {
      const valueStr = typeof metric.value === "number"
        ? metric.value.toLocaleString()
        : metric.value;
      const trendStr = metric.trend ? ` (${metric.trend})` : "";
      lines.push(`- **${metric.label}**: ${valueStr} ${metric.unit ?? ""}${trendStr}`);
    }
    lines.push("");
  }

  // AI 员工汇报
  if (brief.staffReports.length > 0) {
    lines.push("## 🤖 AI 员工汇报");
    lines.push("");
    for (const report of brief.staffReports) {
      lines.push(`### ${report.roleName}`);
      lines.push("");

      if (report.observations.length > 0) {
        lines.push("**观察：**");
        for (const obs of report.observations) {
          lines.push(`- ${obs}`);
        }
        lines.push("");
      }

      if (report.suggestions.length > 0) {
        lines.push("**建议：**");
        for (const sug of report.suggestions) {
          lines.push(`- ${sug}`);
        }
        lines.push("");
      }

      if (report.tasks.length > 0) {
        lines.push("**进行中的任务：**");
        for (const task of report.tasks) {
          const statusIcon = task.status === "in_progress" ? "🔄" : "⏳";
          lines.push(`- ${statusIcon} ${task.title}`);
        }
        lines.push("");
      }
    }
  }

  lines.push("---");
  lines.push("*由星环 OPC 智能助手自动生成*");

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