/**
 * 星环OPC中心 — /opc 快捷命令
 *
 * 通过 registerCommand() 注册，用户输入 /opc 直接返回面板，
 * 不经 LLM，毫秒级响应。
 *
 * 子命令：
 *   /opc          总览仪表盘
 *   /opc tasks    任务板
 *   /opc brief    今日简报
 *   /opc staff    员工状态
 *   /opc alerts   活跃告警
 *   /opc company <id>  公司详情
 */

import type { OpenClawPluginApi } from "openclaw/plugin-sdk";
import type { OpcDatabase } from "../db/index.js";
import { computeHealthScore, computeGrowthScorecard } from "../opc/briefing-builder.js";

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

export function registerOpcCommand(api: OpenClawPluginApi, db: OpcDatabase): void {
  api.registerCommand({
    name: "opc",
    description: "OPC 一人公司快捷面板（/opc, /opc tasks, /opc brief, /opc staff, /opc alerts）",
    acceptsArgs: true,
    handler(ctx: Record<string, unknown>) {
      const args = ((ctx as { args?: string }).args ?? "").trim();

      try {
        if (!args || args === "status") return renderDashboard(db);
        if (args === "tasks") return renderTaskBoard(db);
        if (args === "brief") return renderBriefing(db);
        if (args === "staff") return renderStaffStatus(db);
        if (args === "alerts") return renderAlerts(db);
        if (args.startsWith("company ")) return renderCompanyDetail(db, args.slice(8).trim());

        return { text: "用法: /opc [tasks|brief|staff|alerts|company <id>]" };
      } catch (err) {
        return { text: `错误: ${err instanceof Error ? err.message : String(err)}` };
      }
    },
  });

  api.logger.info("opc: 已注册 /opc 快捷命令");
}

// ── 总览仪表盘 ──────────────────────────────────────────────

function renderDashboard(db: OpcDatabase): { text: string } {
  const companies = db.query(
    "SELECT id, name, status FROM opc_companies ORDER BY created_at DESC LIMIT 10",
  ) as { id: string; name: string; status: string }[];

  if (companies.length === 0) {
    return { text: "📊 **OPC 仪表盘**\n\n暂无公司。使用 opc_manage register_company 注册第一家公司。" };
  }

  const lines: string[] = ["📊 **OPC 仪表盘**", ""];

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

  lines.push(`**组合**: ${companies.length} 家公司 | 总收入: ${totalIncome.toLocaleString()} 元 | 总支出: ${totalExpense.toLocaleString()} 元 | 净利润: ${(totalIncome - totalExpense).toLocaleString()} 元`);
  lines.push("");

  // 每家公司一行
  for (const c of companies) {
    const health = computeHealthScore(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 alertCount = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_alerts WHERE company_id = ? AND status = 'active'", c.id,
    ) as CountRow).cnt;
    const pendingTasks = (db.queryOne(
      "SELECT COUNT(*) as cnt FROM opc_staff_tasks WHERE company_id = ? AND status IN ('pending', 'in_progress', 'pending_approval')", c.id,
    ) as CountRow).cnt;

    const emoji = health.total >= 80 ? "🟢" : health.total >= 60 ? "🟡" : health.total >= 40 ? "🟠" : "🔴";
    const badges: string[] = [];
    if (alertCount > 0) badges.push(`⚠️${alertCount}`);
    if (pendingTasks > 0) badges.push(`📋${pendingTasks}`);
    const badgeStr = badges.length > 0 ? ` ${badges.join(" ")}` : "";

    lines.push(`${emoji} **${c.name}** | ${stageRow?.stage_label ?? "未检测"} | ${health.total}分${badgeStr}`);
  }

  // 紧急事项
  const urgentAlerts = db.query(
    `SELECT a.title, c.name as company_name FROM opc_alerts a
     JOIN opc_companies c ON a.company_id = c.id
     WHERE a.status = 'active' AND a.severity IN ('critical', 'warning')
     ORDER BY CASE a.severity WHEN 'critical' THEN 1 ELSE 2 END
     LIMIT 3`,
  ) as { title: string; company_name: string }[];

  if (urgentAlerts.length > 0) {
    lines.push("");
    lines.push("**紧急事项**:");
    for (const a of urgentAlerts) {
      lines.push(`- [${a.company_name}] ${a.title}`);
    }
  }

  // 待决策事项
  const pendingApproval = db.query(
    `SELECT t.title, t.staff_role, c.name as company_name FROM opc_staff_tasks t
     JOIN opc_companies c ON t.company_id = c.id
     WHERE t.status = 'pending_approval'
     ORDER BY t.created_at DESC LIMIT 3`,
  ) as { title: string; staff_role: string; company_name: string }[];

  if (pendingApproval.length > 0) {
    lines.push("");
    lines.push("**待决策**:");
    for (const t of pendingApproval) {
      lines.push(`- [${t.company_name}/${t.staff_role}] ${t.title}`);
    }
  }

  lines.push("");
  lines.push("子命令: `/opc tasks` `/opc brief` `/opc staff` `/opc alerts` `/opc company <id>`");

  return { text: lines.join("\n") };
}

// ── 任务板 ──────────────────────────────────────────────────

function renderTaskBoard(db: OpcDatabase): { text: string } {
  const lines: string[] = ["📋 **任务板**", ""];

  // 待决策
  const approvalTasks = db.query(
    `SELECT t.id, t.title, t.staff_role, t.created_at, c.name as company_name,
            s.role_name FROM opc_staff_tasks t
     JOIN opc_companies c ON t.company_id = c.id
     LEFT JOIN opc_staff_config s ON t.company_id = s.company_id AND t.staff_role = s.role
     WHERE t.status = 'pending_approval'
     ORDER BY t.created_at DESC LIMIT 5`,
  ) as { id: string; title: string; staff_role: string; created_at: string; company_name: string; role_name: string }[];

  if (approvalTasks.length > 0) {
    lines.push("⚠️ **需要决策**:");
    for (const t of approvalTasks) {
      lines.push(`- [${t.company_name}/${t.role_name ?? t.staff_role}] ${t.title}`);
    }
    lines.push("");
  }

  // 进行中
  const inProgress = db.query(
    `SELECT t.title, t.staff_role, c.name as company_name,
            s.role_name FROM opc_staff_tasks t
     JOIN opc_companies c ON t.company_id = c.id
     LEFT JOIN opc_staff_config s ON t.company_id = s.company_id AND t.staff_role = s.role
     WHERE t.status = 'in_progress'
     ORDER BY t.started_at DESC LIMIT 5`,
  ) as { title: string; staff_role: string; company_name: string; role_name: string }[];

  if (inProgress.length > 0) {
    lines.push("🔄 **进行中**:");
    for (const t of inProgress) {
      lines.push(`- [${t.company_name}/${t.role_name ?? t.staff_role}] ${t.title}`);
    }
    lines.push("");
  }

  // 待执行
  const pending = db.query(
    `SELECT t.title, t.staff_role, t.priority, c.name as company_name,
            s.role_name FROM opc_staff_tasks t
     JOIN opc_companies c ON t.company_id = c.id
     LEFT JOIN opc_staff_config s ON t.company_id = s.company_id AND t.staff_role = s.role
     WHERE t.status = 'pending'
     ORDER BY CASE t.priority WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 ELSE 3 END, t.created_at DESC
     LIMIT 10`,
  ) as { title: string; staff_role: string; priority: string; company_name: string; role_name: string }[];

  if (pending.length > 0) {
    lines.push("⏳ **待执行**:");
    for (const t of pending) {
      const pri = t.priority === "urgent" ? " [紧急]" : t.priority === "high" ? " [重要]" : "";
      lines.push(`- [${t.company_name}/${t.role_name ?? t.staff_role}] ${t.title}${pri}`);
    }
    lines.push("");
  }

  // 最近完成
  const completed = db.query(
    `SELECT t.title, t.staff_role, t.completed_at, c.name as company_name,
            s.role_name FROM opc_staff_tasks t
     JOIN opc_companies c ON t.company_id = c.id
     LEFT JOIN opc_staff_config s ON t.company_id = s.company_id AND t.staff_role = s.role
     WHERE t.status = 'completed' AND t.completed_at > datetime('now', '-24 hours')
     ORDER BY t.completed_at DESC LIMIT 5`,
  ) as { title: string; staff_role: string; completed_at: string; company_name: string; role_name: string }[];

  if (completed.length > 0) {
    lines.push("✅ **最近完成** (24h):");
    for (const t of completed) {
      lines.push(`- [${t.company_name}/${t.role_name ?? t.staff_role}] ${t.title}`);
    }
  }

  if (approvalTasks.length === 0 && inProgress.length === 0 && pending.length === 0 && completed.length === 0) {
    lines.push("暂无任务。");
  }

  return { text: lines.join("\n") };
}

// ── 今日简报 ────────────────────────────────────────────────

function renderBriefing(db: OpcDatabase): { text: string } {
  const companies = db.query(
    "SELECT id, name FROM opc_companies ORDER BY created_at DESC LIMIT 5",
  ) as { id: string; name: string }[];

  if (companies.length === 0) {
    return { text: "📰 **今日简报**\n\n暂无公司数据。" };
  }

  const lines: string[] = [`📰 **今日简报** (${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 income = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'income'", c.id,
    ) as SumRow).total;
    const expense = (db.queryOne(
      "SELECT COALESCE(SUM(amount), 0) as total FROM opc_transactions WHERE company_id = ? AND type = 'expense'", c.id,
    ) as SumRow).total;

    lines.push(`### ${c.name}`);
    lines.push(`${stageRow?.stage_label ?? "未检测"} | 健康 ${health.total}分 | 评级 ${scorecard.overall}`);
    lines.push(`收入: ${income.toLocaleString()} 元 | 支出: ${expense.toLocaleString()} 元 | 净利润: ${(income - expense).toLocaleString()} 元`);

    // 告警
    const alerts = db.query(
      "SELECT title, 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 LIMIT 2",
      c.id,
    ) as { title: string; severity: string }[];
    if (alerts.length > 0) {
      lines.push(`告警: ${alerts.map(a => a.title).join(", ")}`);
    }

    // 洞察
    const insights = db.query(
      `SELECT title FROM opc_insights WHERE company_id = ? AND status = 'active'
       AND (expires_at = '' OR expires_at > datetime('now'))
       ORDER BY priority DESC LIMIT 2`,
      c.id,
    ) as { title: string }[];
    if (insights.length > 0) {
      lines.push(`洞察: ${insights.map(i => i.title).join(", ")}`);
    }

    lines.push("");
  }

  return { text: lines.join("\n") };
}

// ── 员工状态 ────────────────────────────────────────────────

function renderStaffStatus(db: OpcDatabase): { text: string } {
  const lines: string[] = ["👥 **员工状态**", ""];

  const companies = db.query(
    "SELECT id, name FROM opc_companies ORDER BY created_at DESC LIMIT 5",
  ) as { id: string; name: string }[];

  for (const c of companies) {
    const staffList = db.query(
      "SELECT role, role_name, enabled FROM opc_staff_config WHERE company_id = ? ORDER BY created_at ASC",
      c.id,
    ) as { role: string; role_name: string; enabled: number }[];

    if (staffList.length === 0) continue;

    lines.push(`### ${c.name}`);

    for (const s of staffList) {
      if (!s.enabled) {
        lines.push(`- ⏸️ ${s.role_name} (${s.role}) — 已停用`);
        continue;
      }

      const activeTasks = (db.queryOne(
        "SELECT COUNT(*) as cnt FROM opc_staff_tasks WHERE company_id = ? AND staff_role = ? AND status IN ('in_progress', 'pending')",
        c.id, s.role,
      ) as CountRow).cnt;

      const status = activeTasks > 0 ? `🔄 ${activeTasks} 个任务` : "💤 空闲";
      lines.push(`- ${s.role_name} (${s.role}) — ${status}`);
    }

    lines.push("");
  }

  if (companies.length === 0) {
    lines.push("暂无公司数据。");
  }

  return { text: lines.join("\n") };
}

// ── 活跃告警 ────────────────────────────────────────────────

function renderAlerts(db: OpcDatabase): { text: string } {
  const alerts = db.query(
    `SELECT a.id, a.title, a.message, a.severity, a.category, a.created_at, c.name as company_name
     FROM opc_alerts a
     JOIN opc_companies c ON a.company_id = c.id
     WHERE a.status = 'active'
     ORDER BY CASE a.severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END, a.created_at DESC
     LIMIT 20`,
  ) as { id: string; title: string; message: string; severity: string; category: string; created_at: string; company_name: string }[];

  if (alerts.length === 0) {
    return { text: "🔔 **告警列表**\n\n无活跃告警。" };
  }

  const lines: string[] = [`🔔 **告警列表** (${alerts.length} 条)`, ""];

  for (const a of alerts) {
    const icon = a.severity === "critical" ? "🔴" : a.severity === "warning" ? "🟡" : "🔵";
    lines.push(`${icon} [${a.company_name}] **${a.title}**`);
    if (a.message) lines.push(`  ${a.message}`);
  }

  return { text: lines.join("\n") };
}

// ── 公司详情 ────────────────────────────────────────────────

function renderCompanyDetail(db: OpcDatabase, companyId: string): { text: string } {
  // 尝试按 ID 查找，如果找不到尝试按名称模糊匹配
  let company = db.queryOne(
    "SELECT * FROM opc_companies WHERE id = ?", companyId,
  ) as Record<string, unknown> | null;

  if (!company) {
    company = db.queryOne(
      "SELECT * FROM opc_companies WHERE name LIKE ?", `%${companyId}%`,
    ) as Record<string, unknown> | null;
  }

  if (!company) {
    return { text: `公司 "${companyId}" 不存在。` };
  }

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

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

  const contactCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contacts WHERE company_id = ?", cid,
  ) as CountRow).cnt;
  const contractCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_contracts WHERE company_id = ? AND status = 'active'", cid,
  ) as CountRow).cnt;
  const projectCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_projects WHERE company_id = ? AND status IN ('active','planning')", cid,
  ) as CountRow).cnt;
  const alertCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_alerts WHERE company_id = ? AND status = 'active'", cid,
  ) as CountRow).cnt;
  const taskCount = (db.queryOne(
    "SELECT COUNT(*) as cnt FROM opc_staff_tasks WHERE company_id = ? AND status IN ('pending','in_progress','pending_approval')", cid,
  ) as CountRow).cnt;

  const lines: string[] = [
    `🏢 **${company.name}**`,
    "",
    `- 行业: ${company.industry} | 状态: ${company.status}`,
    `- 阶段: ${stageRow?.stage_label ?? "未检测"} | 健康: ${health.total}分 | 评级: ${scorecard.overall}`,
    `- 创办人: ${company.owner_name} | 注册资本: ${(company.registered_capital as number).toLocaleString()} 元`,
    "",
    "**财务**",
    `- 总收入: ${income.toLocaleString()} 元 | 总支出: ${expense.toLocaleString()} 元 | 净利润: ${(income - expense).toLocaleString()} 元`,
    "",
    "**运营**",
    `- 客户: ${contactCount} | 活跃合同: ${contractCount} | 活跃项目: ${projectCount}`,
    `- 活跃告警: ${alertCount} | 待办任务: ${taskCount}`,
  ];

  // 评分卡详情
  lines.push("");
  lines.push("**评分卡**");
  for (const d of scorecard.dimensions) {
    lines.push(`- ${d.name}: ${d.grade} — ${d.detail}`);
  }

  return { text: lines.join("\n") };
}