koc-loop/app/api/resources-export/route.ts

import { isManagerRequest, managerForbidden } from "../../../lib/user-auth";
import { parseStoredDate } from "../../../lib/date-utils";
import { ensureSchema, getRawDb } from "../../../lib/mvp-db";
import {
  buildRecoveryWorkbook,
  type RecoveryWorkbookRow,
} from "../../../lib/recovery-workbook";
import { consumeMcpExportToken } from "../../../lib/mcp-export-token";

type AccountRow = {
  id: string;
  platform: string;
  public_account_id: string;
  nickname: string;
  profile_url: string;
  ip_location: string;
  followers: number;
  gender: string;
  bio: string;
  tags: string;
  post_count: number;
  cooperation_source: string;
  current_contact: string;
  first_seen_at: string;
  last_seen_at: string;
};

type CooperationRow = {
  account_id: string;
  partner_name: string;
  claimant_name: string | null;
  delegation_bundle_id: string | null;
};

function formatExportDate(value: string) {
  const date = parseStoredDate(value);
  if (Number.isNaN(date.getTime())) return value;
  return new Intl.DateTimeFormat("zh-CN", {
    timeZone: "Asia/Shanghai",
    year: "numeric",
    month: "2-digit",
    day: "2-digit",
    hour: "2-digit",
    minute: "2-digit",
    hour12: false,
  }).format(date);
}

function exactArrayBuffer(bytes: Uint8Array) {
  const copy = new Uint8Array(bytes.byteLength);
  copy.set(bytes);
  return copy.buffer;
}

function normalizeAccountIds(value: unknown) {
  if (!Array.isArray(value)) return [];
  return [
    ...new Set(
      value
        .filter((item): item is string => typeof item === "string")
        .map((item) => item.trim())
        .filter(Boolean),
    ),
  ].slice(0, 5000);
}

async function exportAccounts(accountIds: string[]) {
  try {
    if (accountIds.length === 0) {
      return Response.json({ error: "当前筛选结果为空" }, { status: 400 });
    }

    await ensureSchema();
    const db = getRawDb();
    const [accountResult, cooperationResult] = await Promise.all([
      db.prepare("SELECT * FROM accounts ORDER BY last_seen_at DESC").all<AccountRow>(),
      db
        .prepare(
          `SELECT
            d.account_id,
            p.name AS partner_name,
            cl.claimant_name,
            d.delegation_bundle_id
           FROM distributions d
           JOIN partners p ON p.id = d.partner_id
           LEFT JOIN claims cl ON cl.id = d.claim_id
           WHERE d.account_id IS NOT NULL`,
        )
        .all<CooperationRow>(),
    ]);
    const accountIdSet = new Set(accountIds);
    const order = new Map(accountIds.map((id, index) => [id, index]));
    const accounts = accountResult.results
      .filter((account) => accountIdSet.has(account.id))
      .sort(
        (left, right) =>
          (order.get(left.id) ?? Number.MAX_SAFE_INTEGER) -
          (order.get(right.id) ?? Number.MAX_SAFE_INTEGER),
      );
    if (accounts.length === 0) {
      return Response.json({ error: "没有找到可导出的账号" }, { status: 404 });
    }

    const cooperationByAccount = new Map<string, CooperationRow[]>();
    for (const cooperation of cooperationResult.results) {
      if (!accountIdSet.has(cooperation.account_id)) continue;
      const current = cooperationByAccount.get(cooperation.account_id) ?? [];
      current.push(cooperation);
      cooperationByAccount.set(cooperation.account_id, current);
    }
    const headers = [
      "序号",
      "平台",
      "账号名称",
      "小红书号/抖音号",
      "账号主页",
      "IP地",
      "粉丝数",
      "性别",
      "简介",
      "标签",
      "合作发布数",
      "历史合作来源",
      "当前联系人",
      "资源归属",
      "首次合作时间",
      "最近合作时间",
    ];
    const rows: RecoveryWorkbookRow[] = accounts.map((account, index) => {
      const cooperation = cooperationByAccount.get(account.id) ?? [];
      const sources = [
        ...new Set([
          ...cooperation
            .filter(
              (item) =>
                !item.claimant_name ||
                item.partner_name !== item.claimant_name,
            )
            .map((item) => item.partner_name),
          ...(account.cooperation_source || "")
            .split(/[、,，;；|]/)
            .map((item) => item.trim())
            .filter(Boolean),
        ]),
      ];
      const partnerManagedOnly =
        cooperation.some((item) => item.delegation_bundle_id) &&
        cooperation.every((item) => item.delegation_bundle_id);
      return {
        cells: [
          index + 1,
          account.platform,
          account.nickname,
          account.public_account_id || "待识别",
          account.profile_url || "",
          account.ip_location || "待识别",
          account.followers,
          account.gender || "",
          account.bio || "",
          account.tags || "",
          account.post_count,
          sources.join("、"),
          account.current_contact || "",
          partnerManagedOnly ? "合作社资源 · 不可直联" : "可直联",
          formatExportDate(account.first_seen_at),
          formatExportDate(account.last_seen_at),
        ],
        images: [],
        hyperlinks: account.profile_url
          ? [{ column: 4, url: account.profile_url }]
          : [],
      };
    });
    const workbook = buildRecoveryWorkbook({
      sheetName: "KOC资源库",
      headers,
      columnWidths: [
        9,
        12,
        22,
        22,
        44,
        14,
        14,
        10,
        36,
        32,
        14,
        32,
        22,
        22,
        21,
        21,
      ],
      rows,
    });
    const date = new Intl.DateTimeFormat("zh-CN", {
      timeZone: "Asia/Shanghai",
      year: "numeric",
      month: "2-digit",
      day: "2-digit",
    })
      .format(new Date())
      .replace(/\D/g, "");
    const fileName = `KOC资源库-${date}.xlsx`;
    return new Response(exactArrayBuffer(workbook), {
      headers: {
        "Cache-Control": "private, no-store",
        "Content-Type":
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "Content-Disposition": `attachment; filename="koc-resources.xlsx"; filename*=UTF-8''${encodeURIComponent(fileName)}`,
        "X-Content-Type-Options": "nosniff",
      },
    });
  } catch (error) {
    return Response.json(
      { error: error instanceof Error ? error.message : "导出失败" },
      { status: 500 },
    );
  }
}

export async function POST(request: Request) {
  if (!(await isManagerRequest(request))) return managerForbidden();
  try {
    const body = (await request.json()) as { accountIds?: unknown };
    if (!Array.isArray(body.accountIds)) {
      return Response.json({ error: "缺少需要导出的账号" }, { status: 400 });
    }
    return exportAccounts(normalizeAccountIds(body.accountIds));
  } catch (error) {
    return Response.json(
      { error: error instanceof Error ? error.message : "导出失败" },
      { status: 500 },
    );
  }
}

export async function GET(request: Request) {
  const token = new URL(request.url).searchParams.get("token")?.trim() || "";
  const payload = token
    ? await consumeMcpExportToken(token, "resources")
    : null;
  if (!payload) return managerForbidden();
  return exportAccounts(normalizeAccountIds(payload.accountIds));
}