import { env } from "cloudflare:workers";

import { getChatGPTUser } from "@/app/chatgpt-auth";
import { flag, parseStructuredFile, validateImport } from "@/lib/geo-import";

export const dynamic = "force-dynamic";

type WorkspaceUser = { email: string; displayName: string; role: string };
type ProjectRow = {
  id: string; projectName: string; brandName: string; clientName: string; projectType: string; status: string;
  coreKeyword: string; derivativeKeywordsJson: string; platformsJson: string; optimizationRegion: string;
  serviceScope: string; acceptanceRulesJson: string; measurementProtocolJson: string; configVersion: number;
  configFingerprint: string; frozenAt: string | null; startDate: string | null; endDate: string | null;
  baselineMonth: string | null; currentMonth: string | null;
};

const METRIC_STANDARD_VERSION = "GEO交付验收标准V1.0";
const CALCULATION_ENGINE_VERSION = "V8.1";

function response(data: unknown, status = 200) {
  return Response.json(data, { status, headers: { "cache-control": "no-store" } });
}

function now() { return new Date().toISOString(); }
function identifier(prefix: string) { return `${prefix}_${crypto.randomUUID().replaceAll("-", "")}`; }

function parseJson<T>(value: unknown, fallback: T): T {
  try { return JSON.parse(String(value ?? "")) as T; } catch { return fallback; }
}

function canReadAll(role: string) { return role === "owner" || role === "operator" || role === "reviewer"; }
function canManageProjects(role: string) { return role === "owner" || role === "operator"; }
function canPublish(role: string) { return role === "owner" || role === "reviewer"; }
function isOwner(role: string) { return role === "owner"; }

function stringList(value: unknown) {
  const source = Array.isArray(value) ? value : String(value ?? "").split(/[，,、\n]/);
  return [...new Set(source.map((item) => String(item).trim()).filter(Boolean))];
}

function boundedNumber(value: unknown, fallback = 0, maximum = 100000) {
  const number = Number(value);
  return Number.isFinite(number) ? Math.max(0, Math.min(maximum, number)) : fallback;
}

async function sha256(value: ArrayBuffer | string) {
  const bytes = typeof value === "string" ? new TextEncoder().encode(value) : value;
  const hash = await crypto.subtle.digest("SHA-256", bytes);
  return [...new Uint8Array(hash)].map((item) => item.toString(16).padStart(2, "0")).join("");
}

async function audit(user: WorkspaceUser, action: string, targetType: string, targetId: string | null, projectId: string | null, detail: Record<string, unknown> = {}) {
  await env.DB.prepare(
    "INSERT INTO geo_audit_logs (id, project_id, actor_email, action, target_type, target_id, detail_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
  ).bind(identifier("aud"), projectId, user.email, action, targetType, targetId, JSON.stringify(detail), now()).run();
}

async function requireWorkspaceUser(): Promise<WorkspaceUser | Response> {
  const identity = await getChatGPTUser();
  if (!identity) return response({ error: "请先登录后访问运营工作台" }, 401);
  const found = await env.DB.prepare("SELECT email, display_name AS displayName, role FROM workspace_users WHERE email = ?")
    .bind(identity.email).first<WorkspaceUser>();
  if (found) return found;
  const count = await env.DB.prepare("SELECT COUNT(*) AS count FROM workspace_users").first<{ count: number }>();
  const role = Number(count?.count ?? 0) === 0 ? "owner" : "viewer";
  const timestamp = now();
  await env.DB.prepare("INSERT INTO workspace_users (id, email, display_name, role, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)")
    .bind(identifier("usr"), identity.email, identity.displayName, role, timestamp, timestamp).run();
  return { email: identity.email, displayName: identity.displayName, role };
}

function projectConfig(project: Partial<ProjectRow>) {
  return {
    projectName: project.projectName ?? "", brandName: project.brandName ?? "", clientName: project.clientName ?? "",
    coreKeyword: project.coreKeyword ?? "", derivativeKeywords: parseJson<string[]>(project.derivativeKeywordsJson, []),
    platforms: parseJson<string[]>(project.platformsJson, []), optimizationRegion: project.optimizationRegion ?? "",
    serviceScope: project.serviceScope ?? "domestic", acceptanceRules: parseJson<Record<string, unknown>>(project.acceptanceRulesJson, {}),
    measurementProtocol: parseJson<Record<string, unknown>>(project.measurementProtocolJson, {}),
    startDate: project.startDate ?? null, endDate: project.endDate ?? null,
    baselineMonth: project.baselineMonth ?? null, currentMonth: project.currentMonth ?? null,
  };
}

async function getProject(projectId: string) {
  return env.DB.prepare(
    `SELECT id, project_name AS projectName, brand_name AS brandName, client_name AS clientName,
      project_type AS projectType, status, core_keyword AS coreKeyword,
      derivative_keywords_json AS derivativeKeywordsJson, platforms_json AS platformsJson,
      optimization_region AS optimizationRegion, service_scope AS serviceScope,
      acceptance_rules_json AS acceptanceRulesJson, measurement_protocol_json AS measurementProtocolJson,
      config_version AS configVersion, config_fingerprint AS configFingerprint, frozen_at AS frozenAt,
      start_date AS startDate, end_date AS endDate, baseline_month AS baselineMonth,
      current_month AS currentMonth FROM geo_projects WHERE id = ?`,
  ).bind(projectId).first<ProjectRow>();
}

function configFromBody(body: Record<string, unknown>, template?: Record<string, string> | null) {
  const derivatives = stringList(body.derivativeKeywords);
  const platforms = stringList(body.platforms);
  const competitors = stringList(body.competitors);
  const brandAliases = stringList(body.brandAliases);
  const templateRules = parseJson<Record<string, number>>(template?.acceptanceRulesJson, {});
  const templateProtocol = parseJson<Record<string, unknown>>(template?.measurementProtocolJson, {});
  const overrideNumber = (key: string, fallback: number, maximum = 100000) => {
    const raw = body[key];
    return raw === undefined || raw === null || String(raw).trim() === ""
      ? fallback
      : boundedNumber(raw, fallback, maximum);
  };
  const acceptanceRules = {
    mentionRate: overrideNumber("targetMentionRate", templateRules.mentionRate ?? 0, 100),
    recommendRate: overrideNumber("targetRecommendRate", templateRules.recommendRate ?? 0, 100),
    top3Rate: overrideNumber("targetTop3Rate", templateRules.top3Rate ?? 0, 100),
    deliveryTarget: overrideNumber("deliveryTarget", templateRules.deliveryTarget ?? 0, 100000),
    minDeliveryCompletion: overrideNumber("minDeliveryCompletion", templateRules.minDeliveryCompletion ?? 100, 100),
    minAnswerCount: Math.max(1, overrideNumber("minAnswerCount", templateRules.minAnswerCount ?? 1, 100000)),
    minValidResponseRate: overrideNumber("minValidResponseRate", templateRules.minValidResponseRate ?? 90, 100),
    minEvidenceCoverage: overrideNumber("minEvidenceCoverage", templateRules.minEvidenceCoverage ?? 100, 100),
    minCitationEvidenceCoverage: overrideNumber("minCitationEvidenceCoverage", templateRules.minCitationEvidenceCoverage ?? 100, 100),
    minAccuracyCoverage: overrideNumber("minAccuracyCoverage", templateRules.minAccuracyCoverage ?? 100, 100),
  };
  const protocol = {
    metricStandardVersion: METRIC_STANDARD_VERSION,
    calculationEngineVersion: CALCULATION_ENGINE_VERSION,
    brandAliases: brandAliases.length ? brandAliases : [String(body.brandName ?? "").trim()].filter(Boolean),
    promptPoolVersion: String(body.promptPoolVersion ?? templateProtocol.promptPoolVersion ?? "V1.0").trim() || "V1.0",
    detectionCount: Math.max(1, overrideNumber("detectionCount", Number(templateProtocol.detectionCount ?? 1), 100000)),
    competitorSet: competitors,
    modelScope: String(body.modelScope ?? templateProtocol.modelScope ?? "以导入记录为准").trim(),
    judgmentRulesVersion: String(body.judgmentRulesVersion ?? templateProtocol.judgmentRulesVersion ?? "V1.0").trim() || "V1.0",
  };
  return {
    projectName: String(body.projectName ?? "").trim(), brandName: String(body.brandName ?? "").trim(),
    clientName: String(body.clientName ?? "").trim(), coreKeyword: String(body.coreKeyword ?? "").trim(),
    derivativeKeywordsJson: JSON.stringify(derivatives),
    platformsJson: JSON.stringify(platforms.length ? platforms : parseJson(template?.platformsJson, [])),
    optimizationRegion: String(body.optimizationRegion ?? "").trim() || template?.optimizationRegion || "",
    serviceScope: String(body.serviceScope ?? "").trim() || template?.serviceScope || "domestic",
    acceptanceRulesJson: JSON.stringify(acceptanceRules),
    measurementProtocolJson: JSON.stringify(protocol), startDate: body.startDate || null, endDate: body.endDate || null,
    baselineMonth: body.baselineMonth || null, currentMonth: body.currentMonth || null,
  };
}

async function ensureReferenceProject(email: string) {
  const timestamp = now();
  const projectId = "prj_aowang_acceptance_reference";
  const snapshotId = "snp_aowang_reference_2026_08";
  const derivativeKeywords = ["非遗粤式茶楼", "粤式茶楼点心", "粤式茶楼文化", "商务宴请茶楼", "家庭聚餐茶楼"];
  const platforms = ["DeepSeek", "豆包", "通义千问", "Kimi", "文心一言", "元宝", "智谱", "纳米"];
  const acceptanceRules = { mentionRate: 60, recommendRate: 50, top3Rate: 40, deliveryTarget: 50, minDeliveryCompletion: 100,
    minAnswerCount: 1, minValidResponseRate: 90, minEvidenceCoverage: 100,
    minCitationEvidenceCoverage: 100, minAccuracyCoverage: 100 };
  const measurementProtocol = { metricStandardVersion: METRIC_STANDARD_VERSION, calculationEngineVersion: CALCULATION_ENGINE_VERSION,
    brandAliases: ["傲网 GEO 验收样板", "傲网GEO样板"], promptPoolVersion: "V1.0", detectionCount: 688,
    competitorSet: ["广州酒家", "陶陶居"], modelScope: "8个主流AI平台", judgmentRulesVersion: "V1.0" };
  const referenceConfig = { projectName: "傲网 GEO 标准验收样板", brandName: "傲网 GEO 验收样板",
    clientName: "傲网内部质量验收", coreKeyword: "粤式茶楼", derivativeKeywords, platforms,
    optimizationRegion: "全国及重点城市", serviceScope: "domestic", acceptanceRules,
    measurementProtocol, startDate: "2026-05-01", endDate: "2026-10-31",
    baselineMonth: "2026-05", currentMonth: "2026-08" };
  const referenceConfigJson = JSON.stringify(referenceConfig);
  const referenceFingerprint = await sha256(referenceConfigJson);
  const referenceSummary = JSON.stringify({ reference: true, purpose: "月度交付、公式与证据链回归验收",
    referenceValidation: { metricStandardVersion: METRIC_STANDARD_VERSION,
      calculationEngineVersion: CALCULATION_ENGINE_VERSION, moduleCount: 10,
      moduleConsistency: 100, validatedAt: "2026-09-01" },
    visibility: 51.05, health: 92.8, coverage: { promptCount: 86, platformCount: 8,
      competitorCount: 1376, competitorScopeCoverage: 100, competitorRecordCoverage: 100,
      competitorEntityMatch: 1, competitorCountMatch: 1, unmatchedCompetitorCount: 0,
      evidenceCoverage: 100, rawEvidenceCoverage: 100, citationEvidenceCoverage: 100,
      accuracyCoverage: 100, protocolCoverage: 100 } });
  const existing = await env.DB.prepare("SELECT id FROM geo_projects WHERE project_type = 'reference' OR id = ? LIMIT 1")
    .bind(projectId).first<{ id: string }>();
  if (existing) {
    await env.DB.batch([
      env.DB.prepare(
        `UPDATE geo_projects SET slug = 'acceptance-reference', project_code = 'AW-GEO-REFERENCE',
          project_type = 'reference', project_name = '傲网 GEO 标准验收样板', brand_name = '傲网 GEO 验收样板',
          client_name = '傲网内部质量验收', core_keyword = '粤式茶楼', derivative_keywords_json = ?,
          platforms_json = ?, optimization_region = '全国及重点城市', service_scope = 'domestic',
          acceptance_rules_json = ?, measurement_protocol_json = ?, config_version = 1,
          config_fingerprint = ?, frozen_at = COALESCE(frozen_at, ?), frozen_by = COALESCE(frozen_by, ?),
          start_date = '2026-05-01', end_date = '2026-10-31', baseline_month = '2026-05', current_month = '2026-08',
          status = 'active', data_mode = 'reference', deleted_at = NULL, deleted_by = NULL,
          delete_after = NULL, updated_at = ? WHERE id = ?`,
      ).bind(JSON.stringify(derivativeKeywords), JSON.stringify(platforms), JSON.stringify(acceptanceRules),
        JSON.stringify(measurementProtocol), referenceFingerprint, timestamp, email, timestamp, existing.id),
      env.DB.prepare(
        `INSERT INTO geo_project_members (id, project_id, user_email, display_name, role, status, created_at, updated_at)
          VALUES (?, ?, ?, '系统负责人', 'owner', 'active', ?, ?)
          ON CONFLICT(project_id, user_email) DO UPDATE SET role = 'owner', status = 'active', updated_at = excluded.updated_at`,
      ).bind(identifier("mem"), existing.id, email, timestamp, timestamp),
      env.DB.prepare(`UPDATE geo_snapshots SET source_filename = '傲网GEO标准验收数据集', summary_json = ?,
        scope_fingerprint = ?, scope_json = ?, gate_json = ?, reviewed_by = COALESCE(reviewed_by, ?),
        published_by = COALESCE(published_by, ?), acceptance_status = 'accepted',
        accepted_at = COALESCE(accepted_at, ?), accepted_by = COALESCE(accepted_by, ?), updated_at = ? WHERE project_id = ?`)
        .bind(referenceSummary, referenceFingerprint, referenceConfigJson,
          JSON.stringify({ passed: true, reference: true }), email, email, timestamp, email, timestamp, existing.id),
      env.DB.prepare(`INSERT INTO geo_project_config_versions (id, project_id, version, config_json, fingerprint,
        status, change_reason, created_by, created_at, frozen_at) VALUES (?, ?, 1, ?, ?, 'frozen', ?, ?, ?, ?)
        ON CONFLICT(project_id, version) DO UPDATE SET config_json = excluded.config_json,
        fingerprint = excluded.fingerprint, status = 'frozen', frozen_at = excluded.frozen_at`)
        .bind(identifier("cfg"), existing.id, referenceConfigJson, referenceFingerprint,
          "傲网GEO标准验收口径", email, timestamp, timestamp),
    ]);
    return;
  }
  await env.DB.batch([
    env.DB.prepare(
      `INSERT INTO geo_projects (id, slug, project_code, project_type, project_name, brand_name, client_name,
        core_keyword, derivative_keywords_json, platforms_json, optimization_region, service_scope,
        acceptance_rules_json, measurement_protocol_json, start_date, end_date, baseline_month, current_month,
        status, data_mode, created_by, created_at, updated_at)
        VALUES (?, ?, ?, 'reference', ?, ?, ?, ?, ?, ?, ?, 'domestic', ?, ?, ?, ?, ?, ?, 'active', 'reference', ?, ?, ?)`,
    ).bind(projectId, "acceptance-reference", "AW-GEO-REFERENCE", "傲网 GEO 标准验收样板", "傲网 GEO 验收样板",
      "傲网内部质量验收", "粤式茶楼", JSON.stringify(derivativeKeywords), JSON.stringify(platforms), "全国及重点城市",
      JSON.stringify(acceptanceRules), JSON.stringify(measurementProtocol),
      "2026-05-01", "2026-10-31", "2026-05", "2026-08", email, timestamp, timestamp),
    env.DB.prepare("INSERT INTO geo_project_members (id, project_id, user_email, display_name, role, status, created_at, updated_at) VALUES (?, ?, ?, '系统负责人', 'owner', 'active', ?, ?)")
      .bind(identifier("mem"), projectId, email, timestamp, timestamp),
    env.DB.prepare(
      `INSERT INTO geo_snapshots (id, project_id, report_month, version, status, quality_score, answer_count,
        delivery_count, evidence_count, source_filename, summary_json, created_by, created_at, updated_at, published_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    ).bind(snapshotId, projectId, "2026-08", 1, "published", 98.7, 688, 50, 688, "傲网GEO标准验收数据集",
      referenceSummary, email, timestamp, timestamp, timestamp),
    env.DB.prepare(`UPDATE geo_projects SET config_fingerprint = ?, frozen_at = ?, frozen_by = ? WHERE id = ?`)
      .bind(referenceFingerprint, timestamp, email, projectId),
    env.DB.prepare(`UPDATE geo_snapshots SET scope_fingerprint = ?, scope_json = ?, gate_json = ?, reviewed_by = ?,
      published_by = ?, acceptance_status = 'accepted', accepted_at = ?, accepted_by = ? WHERE id = ?`)
      .bind(referenceFingerprint, referenceConfigJson, JSON.stringify({ passed: true, reference: true }),
        email, email, timestamp, email, snapshotId),
    env.DB.prepare(`INSERT INTO geo_project_config_versions (id, project_id, version, config_json, fingerprint,
      status, change_reason, created_by, created_at, frozen_at) VALUES (?, ?, 1, ?, ?, 'frozen', ?, ?, ?, ?)`)
      .bind(identifier("cfg"), projectId, referenceConfigJson, referenceFingerprint,
        "傲网GEO标准验收口径", email, timestamp, timestamp),
  ]);
}

async function loadWorkspace(user: WorkspaceUser) {
  if (canReadAll(user.role)) await ensureReferenceProject(user.email);
  const projectScope = canReadAll(user.role) ? "" : "WHERE p.status <> 'recycle_bin' AND EXISTS (SELECT 1 FROM geo_project_members m WHERE m.project_id = p.id AND m.user_email = ? AND m.status = 'active')";
  const projectStatement = env.DB.prepare(
    `SELECT p.id, p.slug, p.project_code AS projectCode, p.project_type AS projectType,
      p.project_name AS projectName, p.brand_name AS brandName, p.client_name AS clientName,
      p.core_keyword AS coreKeyword, p.start_date AS startDate, p.derivative_keywords_json AS derivativeKeywordsJson,
      p.platforms_json AS platformsJson, p.optimization_region AS optimizationRegion, p.service_scope AS serviceScope,
      p.acceptance_rules_json AS acceptanceRulesJson, p.measurement_protocol_json AS measurementProtocolJson,
      p.config_version AS configVersion, p.config_fingerprint AS configFingerprint, p.frozen_at AS frozenAt,
      p.frozen_by AS frozenBy, p.end_date AS endDate, p.baseline_month AS baselineMonth,
      p.current_month AS currentMonth, p.status, p.data_mode AS dataMode, p.logo_key AS logoKey,
      p.updated_at AS updatedAt, p.deleted_at AS deletedAt, p.deleted_by AS deletedBy, p.delete_after AS deleteAfter,
      (SELECT COUNT(*) FROM geo_evidence_records e WHERE e.project_id = p.id AND e.prompt_id <> '' AND e.record_status = 'valid'
        AND (e.evidence_object_key IS NOT NULL OR e.conversation_url IS NOT NULL)) AS evidenceCount,
      (SELECT COUNT(*) FROM geo_delivery_items d WHERE d.project_id = p.id) AS deliveryCount,
      (SELECT COUNT(*) FROM geo_snapshots s WHERE s.project_id = p.id AND s.status = 'published') AS publishedCount
    FROM geo_projects p ${projectScope} ORDER BY p.updated_at DESC`,
  );
  const scopedProjects = canReadAll(user.role) ? projectStatement : projectStatement.bind(user.email);
  const snapshotScope = canReadAll(user.role) ? "" : "AND p.status <> 'recycle_bin' AND s.status = 'published' AND EXISTS (SELECT 1 FROM geo_project_members m WHERE m.project_id = p.id AND m.user_email = ? AND m.status = 'active')";
  const snapshotStatement = env.DB.prepare(
    `SELECT s.id, s.project_id AS projectId, p.brand_name AS brandName, s.report_month AS reportMonth,
      s.version, s.status, s.quality_score AS qualityScore, s.answer_count AS answerCount,
      s.delivery_count AS deliveryCount, s.evidence_count AS evidenceCount, s.source_filename AS sourceFilename,
      s.summary_json AS summaryJson, s.gate_json AS gateJson, s.scope_fingerprint AS scopeFingerprint,
      s.reviewed_by AS reviewedBy, s.review_note AS reviewNote, s.published_by AS publishedBy,
      s.acceptance_status AS acceptanceStatus, s.updated_at AS updatedAt, s.published_at AS publishedAt
    FROM geo_snapshots s JOIN geo_projects p ON p.id = s.project_id WHERE 1 = 1 ${snapshotScope}
    ORDER BY s.updated_at DESC LIMIT 80`,
  );
  const scopedSnapshots = canReadAll(user.role) ? snapshotStatement : snapshotStatement.bind(user.email);
  const importStatement = env.DB.prepare(
    `SELECT j.id, j.project_id AS projectId, p.brand_name AS brandName, j.snapshot_id AS snapshotId,
      j.import_type AS importType, j.filename, j.status, j.total_rows AS totalRows,
      j.valid_rows AS validRows, j.error_rows AS errorRows, j.errors_json AS errorsJson,
      j.created_at AS createdAt FROM geo_import_jobs j JOIN geo_projects p ON p.id = j.project_id
    WHERE ? = 1 ORDER BY j.created_at DESC LIMIT 60`,
  ).bind(canReadAll(user.role) ? 1 : 0);
  const membersStatement = env.DB.prepare(
    `SELECT m.id, m.project_id AS projectId, p.brand_name AS brandName, m.user_email AS userEmail,
      m.display_name AS displayName, m.role, m.status, m.created_at AS createdAt, m.updated_at AS updatedAt
    FROM geo_project_members m JOIN geo_projects p ON p.id = m.project_id
    WHERE ? = 1 ORDER BY m.updated_at DESC, m.created_at DESC LIMIT 120`,
  ).bind(canReadAll(user.role) ? 1 : 0);
  const auditsStatement = env.DB.prepare(
    `SELECT a.id, a.project_id AS projectId, p.brand_name AS brandName, a.actor_email AS actorEmail,
      a.action, a.target_type AS targetType, a.target_id AS targetId, a.detail_json AS detailJson,
      a.created_at AS createdAt FROM geo_audit_logs a LEFT JOIN geo_projects p ON p.id = a.project_id
    WHERE ? = 1 ORDER BY a.created_at DESC LIMIT 80`,
  ).bind(canReadAll(user.role) ? 1 : 0);
  const [projectsResult, snapshotsResult, importsResult, membersResult, auditsResult] = await Promise.all([
    scopedProjects.all(), scopedSnapshots.all(), importStatement.all(), membersStatement.all(), auditsStatement.all(),
  ]);
  return {
    user: { ...user, canWrite: canReadAll(user.role), canManageProjects: canManageProjects(user.role), canPublish: canPublish(user.role) },
    projects: projectsResult.results, snapshots: snapshotsResult.results, imports: importsResult.results,
    members: membersResult.results, audits: auditsResult.results,
  };
}

async function deleteProjectObjects(projectId: string) {
  let cursor: string | undefined;
  do {
    const page = await env.BUCKET.list({ prefix: `projects/${projectId}/`, cursor });
    if (page.objects.length) await env.BUCKET.delete(page.objects.map((item: { key: string }) => item.key));
    cursor = page.truncated ? page.cursor : undefined;
  } while (cursor);
}

async function getDraftSnapshot(projectId: string, reportMonth: string, email: string, sourceFilename: string) {
  const existing = await env.DB.prepare(`SELECT s.id FROM geo_snapshots s JOIN geo_projects p ON p.id = s.project_id
    WHERE s.project_id = ? AND s.report_month = ? AND s.status = 'draft'
      AND s.scope_fingerprint = p.config_fingerprint ORDER BY s.version DESC LIMIT 1`)
    .bind(projectId, reportMonth).first<{ id: string }>();
  if (existing) return existing.id;
  const [versionRow, project] = await Promise.all([
    env.DB.prepare("SELECT COALESCE(MAX(version), 0) AS version FROM geo_snapshots WHERE project_id = ? AND report_month = ?")
      .bind(projectId, reportMonth).first<{ version: number }>(),
    getProject(projectId),
  ]);
  const snapshotId = identifier("snp");
  const timestamp = now();
  await env.DB.prepare(
    `INSERT INTO geo_snapshots (id, project_id, report_month, version, status, quality_score, answer_count,
      delivery_count, evidence_count, source_filename, summary_json, scope_fingerprint, scope_json,
      created_by, created_at, updated_at) VALUES (?, ?, ?, ?, 'draft', 0, 0, 0, 0, ?, '{}', ?, ?, ?, ?, ?)`,
  ).bind(snapshotId, projectId, reportMonth, Number(versionRow?.version ?? 0) + 1, sourceFilename,
    project?.configFingerprint ?? "", JSON.stringify(project ? projectConfig(project) : {}), email, timestamp, timestamp).run();
  return snapshotId;
}

async function refreshSnapshot(snapshotId: string, filename?: string) {
  const [counts, validation, existing, snapshotProject, competitorEntities, platformEntities, keywordEntities, completionSummary] = await Promise.all([
    env.DB.prepare(
      `SELECT
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '') AS totalCallCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid') AS answerCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'failed') AS failedCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'timeout') AS timeoutCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'invalid') AS invalidCount,
        (SELECT COUNT(DISTINCT prompt_id) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '') AS promptCount,
        (SELECT COUNT(DISTINCT platform) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '') AS platformCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND model_version <> '') AS modelVersionCount,
        (SELECT COUNT(DISTINCT prompt_id || char(31) || platform) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid') AS answerScopeCount,
        (SELECT COUNT(*) FROM geo_delivery_items WHERE snapshot_id = ?) AS deliveryCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid'
          AND (evidence_object_key IS NOT NULL OR conversation_url IS NOT NULL)) AS evidenceCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid'
          AND citation_detectable = 1 AND brand_cited = 1) AS citationClaimCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid'
          AND citation_detectable = 1 AND brand_cited = 1 AND citation_urls <> '[]') AS citationEvidenceCount,
        (SELECT COUNT(*) FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid'
          AND judged_by <> '' AND accuracy_note IS NOT NULL) AS factAuditCount,
        (SELECT COALESCE(SUM(CASE WHEN recommendation_prompt = 1 THEN recommend ELSE 0 END), 0)
          FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid') AS brandRecommendations,
        (SELECT COUNT(*) FROM geo_competitor_records WHERE snapshot_id = ?) AS competitorCount,
        (SELECT COUNT(DISTINCT c.prompt_id || char(31) || c.platform) FROM geo_competitor_records c
          WHERE c.snapshot_id = ? AND EXISTS (SELECT 1 FROM geo_evidence_records e WHERE e.snapshot_id = c.snapshot_id
            AND e.record_id = c.record_id AND e.prompt_id = c.prompt_id AND e.platform = c.platform
            AND e.prompt_id <> '' AND e.record_status = 'valid')) AS competitorScopeCount,
        (SELECT COUNT(*) FROM geo_competitor_records c WHERE c.snapshot_id = ? AND NOT EXISTS
          (SELECT 1 FROM geo_evidence_records e WHERE e.snapshot_id = c.snapshot_id
            AND e.record_id = c.record_id AND e.prompt_id = c.prompt_id AND e.platform = c.platform
            AND e.prompt_id <> '' AND e.record_status = 'valid')) AS unmatchedCompetitorCount,
        (SELECT COALESCE(SUM(c.recommended), 0) FROM geo_competitor_records c
          JOIN geo_evidence_records e ON e.snapshot_id = c.snapshot_id AND e.record_id = c.record_id
            AND e.prompt_id = c.prompt_id AND e.platform = c.platform AND e.record_status = 'valid' AND e.recommendation_prompt = 1
          WHERE c.snapshot_id = ?) AS competitorRecommendations`,
    ).bind(snapshotId, snapshotId, snapshotId, snapshotId, snapshotId, snapshotId, snapshotId, snapshotId,
      snapshotId, snapshotId, snapshotId, snapshotId, snapshotId, snapshotId, snapshotId, snapshotId,
      snapshotId, snapshotId, snapshotId).first<Record<string, number>>(),
    env.DB.prepare(
      `SELECT COALESCE(SUM(j.total_rows), 0) AS totalRows, COALESCE(SUM(j.valid_rows), 0) AS validRows,
        COALESCE(SUM(j.error_rows), 0) AS errorRows,
        MAX(CASE WHEN j.import_type = 'answers' AND j.status = 'completed' THEN 1 ELSE 0 END) AS answersReady,
        MAX(CASE WHEN j.import_type = 'competitors' AND j.status = 'completed' THEN 1 ELSE 0 END) AS competitorsReady,
        MAX(CASE WHEN j.import_type = 'deliveries' AND j.status = 'completed' THEN 1 ELSE 0 END) AS deliveriesReady,
        MAX(CASE WHEN j.import_type = 'completion' AND j.status = 'completed' THEN 1 ELSE 0 END) AS completionReady
      FROM geo_import_jobs j
      WHERE j.snapshot_id = ? AND j.import_type IN ('answers', 'competitors', 'deliveries', 'completion')
        AND j.created_at = (SELECT MAX(latest.created_at) FROM geo_import_jobs latest
          WHERE latest.snapshot_id = j.snapshot_id AND latest.import_type = j.import_type)`,
    ).bind(snapshotId).first<Record<string, number>>(),
    env.DB.prepare("SELECT summary_json AS summaryJson FROM geo_snapshots WHERE id = ?").bind(snapshotId).first<{ summaryJson: string }>(),
    env.DB.prepare(
      `SELECT p.config_fingerprint AS configFingerprint, p.project_name AS projectName, p.brand_name AS brandName,
        p.client_name AS clientName, p.core_keyword AS coreKeyword, p.derivative_keywords_json AS derivativeKeywordsJson,
        p.platforms_json AS platformsJson, p.optimization_region AS optimizationRegion, p.service_scope AS serviceScope,
        p.acceptance_rules_json AS acceptanceRulesJson, p.measurement_protocol_json AS measurementProtocolJson,
        p.start_date AS startDate, p.end_date AS endDate, p.baseline_month AS baselineMonth,
        p.current_month AS currentMonth FROM geo_snapshots s JOIN geo_projects p ON p.id = s.project_id WHERE s.id = ?`,
    ).bind(snapshotId).first<ProjectRow>(),
    env.DB.prepare(`SELECT competitor_name AS competitorName, COUNT(*) AS recordCount
      FROM geo_competitor_records WHERE snapshot_id = ? GROUP BY competitor_name ORDER BY competitor_name`)
      .bind(snapshotId).all<{ competitorName: string; recordCount: number }>(),
    env.DB.prepare("SELECT DISTINCT platform FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' ORDER BY platform")
      .bind(snapshotId).all<{ platform: string }>(),
    env.DB.prepare("SELECT DISTINCT keyword FROM geo_evidence_records WHERE snapshot_id = ? AND prompt_id <> '' ORDER BY keyword")
      .bind(snapshotId).all<{ keyword: string }>(),
    env.DB.prepare(`SELECT COUNT(*) AS itemCount, COALESCE(SUM(weight), 0) AS weightTotal,
      COALESCE(SUM((CASE WHEN qualified_quantity >= contract_quantity THEN 1.0
        ELSE qualified_quantity / contract_quantity END) * weight), 0) AS completionScore
      FROM geo_delivery_acceptance_items WHERE snapshot_id = ?`)
      .bind(snapshotId).first<{ itemCount: number; weightTotal: number; completionScore: number }>(),
  ]);
  const totalCallCount = Number(counts?.totalCallCount ?? 0);
  const answerCount = Number(counts?.answerCount ?? 0);
  const evidenceCount = Number(counts?.evidenceCount ?? 0);
  const citationClaimCount = Number(counts?.citationClaimCount ?? 0);
  const citationEvidenceCount = Number(counts?.citationEvidenceCount ?? 0);
  const factAuditCount = Number(counts?.factAuditCount ?? 0);
  const totalRows = Number(validation?.totalRows ?? 0);
  const validRows = Number(validation?.validRows ?? 0);
  const errorRows = Number(validation?.errorRows ?? 0);
  const validationScore = totalRows ? (validRows / totalRows) * 100 : 0;
  const rawEvidenceCoverage = answerCount ? (evidenceCount / answerCount) * 100 : 0;
  const citationEvidenceCoverage = citationClaimCount ? (citationEvidenceCount / citationClaimCount) * 100 : 100;
  const accuracyCoverage = answerCount ? (factAuditCount / answerCount) * 100 : 0;
  const validResponseRate = totalCallCount ? Number(((answerCount / totalCallCount) * 100).toFixed(1)) : 0;
  const modelVersionCoverage = totalCallCount ? Number(((Number(counts?.modelVersionCount ?? 0) / totalCallCount) * 100).toFixed(1)) : 0;
  const brandRecommendations = Number(counts?.brandRecommendations ?? 0);
  const competitorRecommendations = Number(counts?.competitorRecommendations ?? 0);
  const recommendationTotal = brandRecommendations + competitorRecommendations;
  const sov = Number(counts?.competitorCount ?? 0) && recommendationTotal ? Number(((brandRecommendations / recommendationTotal) * 100).toFixed(1)) : null;
  const answerScopeCount = Number(counts?.answerScopeCount ?? 0);
  const competitorScopeCoverage = answerScopeCount ? Number(((Number(counts?.competitorScopeCount ?? 0) / answerScopeCount) * 100).toFixed(1)) : 0;
  const configuredCompetitors = parseJson<{ competitorSet?: unknown[] }>(snapshotProject?.measurementProtocolJson, {}).competitorSet
    ?.map((item) => String(item).trim()).filter(Boolean) ?? [];
  const competitorEntityRows = competitorEntities.results as Array<{ competitorName: string; recordCount: number }>;
  const actualCompetitors = competitorEntityRows.map((item) => item.competitorName).sort();
  const expectedCompetitors = [...configuredCompetitors].sort();
  const competitorEntityMatch = expectedCompetitors.length > 0
    && JSON.stringify(actualCompetitors) === JSON.stringify(expectedCompetitors);
  const competitorCountMatch = competitorEntityMatch && competitorEntityRows.every((item) => Number(item.recordCount) === answerCount);
  const competitorRecordCoverage = answerCount && configuredCompetitors.length
    ? Number((Math.min(...configuredCompetitors.map((name) => {
      const entity = competitorEntityRows.find((item) => item.competitorName === name);
      return Math.min(1, Number(entity?.recordCount ?? 0) / answerCount);
    })) * 100).toFixed(1))
    : 0;
  const actualPlatforms = (platformEntities.results as Array<{ platform: string }>).map((item) => item.platform).filter(Boolean).sort();
  const expectedPlatforms = parseJson<string[]>(snapshotProject?.platformsJson, []).map((item) => String(item).trim()).filter(Boolean).sort();
  const platformEntityMatch = expectedPlatforms.length > 0 && JSON.stringify(actualPlatforms) === JSON.stringify(expectedPlatforms);
  const actualKeywords = (keywordEntities.results as Array<{ keyword: string }>).map((item) => item.keyword).filter(Boolean).sort();
  const expectedKeywords = [snapshotProject?.coreKeyword ?? "", ...parseJson<string[]>(snapshotProject?.derivativeKeywordsJson, [])]
    .map((item) => String(item).trim()).filter(Boolean).sort();
  const keywordEntityMatch = expectedKeywords.length > 0 && JSON.stringify(actualKeywords) === JSON.stringify(expectedKeywords);
  const protocolCoverage = totalCallCount && Number(counts?.promptCount ?? 0) && platformEntityMatch && keywordEntityMatch && modelVersionCoverage === 100 ? 100 : 0;
  const quality = Number((validationScore * 0.4 + rawEvidenceCoverage * 0.2 + citationEvidenceCoverage * 0.1
    + accuracyCoverage * 0.2 + protocolCoverage * 0.1).toFixed(1));
  const timestamp = now();
  const summary = {
    ...parseJson<Record<string, unknown>>(existing?.summaryJson, {}), generatedBy: "v8-import-pipeline", checkedAt: timestamp, sov,
    validation: { totalRows, validRows, errorRows },
    coverage: { totalCallCount, validResponseRate, failedCount: Number(counts?.failedCount ?? 0),
      timeoutCount: Number(counts?.timeoutCount ?? 0), invalidCount: Number(counts?.invalidCount ?? 0),
      promptCount: Number(counts?.promptCount ?? 0), platformCount: Number(counts?.platformCount ?? 0),
      answersReady: Number(validation?.answersReady ?? 0), competitorsReady: Number(validation?.competitorsReady ?? 0),
      deliveriesReady: Number(validation?.deliveriesReady ?? 0), completionReady: Number(validation?.completionReady ?? 0),
      deliveryItemCount: Number(completionSummary?.itemCount ?? 0), deliveryWeightTotal: Number(Number(completionSummary?.weightTotal ?? 0).toFixed(2)),
      deliveryCompletion: Number(Number(completionSummary?.completionScore ?? 0).toFixed(1)), modelVersionCoverage,
      platformEntityMatch: platformEntityMatch ? 1 : 0, keywordEntityMatch: keywordEntityMatch ? 1 : 0,
      competitorCount: Number(counts?.competitorCount ?? 0), competitorScopeCoverage, competitorRecordCoverage,
      competitorEntityMatch: competitorEntityMatch ? 1 : 0, competitorCountMatch: competitorCountMatch ? 1 : 0,
      unmatchedCompetitorCount: Number(counts?.unmatchedCompetitorCount ?? 0),
      evidenceCoverage: Number(rawEvidenceCoverage.toFixed(1)), rawEvidenceCoverage: Number(rawEvidenceCoverage.toFixed(1)),
      citationClaimCount, citationEvidenceCount, citationEvidenceCoverage: Number(citationEvidenceCoverage.toFixed(1)),
      accuracyCoverage: Number(accuracyCoverage.toFixed(1)), protocolCoverage },
  };
  await env.DB.prepare(
    `UPDATE geo_snapshots SET quality_score = ?, answer_count = ?, delivery_count = ?, evidence_count = ?,
      source_filename = COALESCE(?, source_filename), summary_json = ?, scope_fingerprint = ?, scope_json = ?, updated_at = ? WHERE id = ?`,
  ).bind(quality, answerCount, Number(counts?.deliveryCount ?? 0), evidenceCount, filename, JSON.stringify(summary),
    snapshotProject?.configFingerprint ?? "", JSON.stringify(snapshotProject ? projectConfig(snapshotProject) : {}), timestamp, snapshotId).run();
}

async function batchRun(statements: Array<ReturnType<typeof env.DB.prepare>>) {
  for (let index = 0; index < statements.length; index += 80) await env.DB.batch(statements.slice(index, index + 80));
}

function publishGate(snapshot: { answerCount: number; deliveryCount: number; qualityScore: number; summaryJson: string; scopeFingerprint: string }, project: { projectType: string; frozenAt: string | null; acceptanceRulesJson: string; measurementProtocolJson: string; configFingerprint: string }) {
  const rules = parseJson<Record<string, number>>(project.acceptanceRulesJson, {});
  const protocol = parseJson<Record<string, unknown>>(project.measurementProtocolJson, {});
  const summary = parseJson<{ validation?: Record<string, number>; coverage?: Record<string, number> }>(snapshot.summaryJson, {});
  const validation = summary.validation ?? {};
  const coverage = summary.coverage ?? {};
  const isReference = project.projectType === "reference";
  const plannedSample = Number(protocol.detectionCount ?? 1);
  const deliveryTarget = Number(rules.deliveryTarget ?? 0);
  const checks = [
    { key: "scope", label: "快照与当前冻结口径一致", passed: isReference || Boolean(project.frozenAt) && Boolean(snapshot.scopeFingerprint) && snapshot.scopeFingerprint === project.configFingerprint },
    { key: "planned-sample", label: `检测调用总数与冻结计划${plannedSample}次完全一致`, passed: isReference || Number(coverage.totalCallCount ?? 0) === plannedSample },
    { key: "answers", label: `有效回答不少于${rules.minAnswerCount || 1}条`, passed: snapshot.answerCount >= (rules.minAnswerCount || 1) },
    { key: "valid-rate", label: `有效回答率达到${rules.minValidResponseRate ?? 90}%`, passed: isReference || Number(coverage.validResponseRate ?? 0) >= (rules.minValidResponseRate ?? 90) },
    { key: "delivery", label: deliveryTarget ? `有效交付达到合同目标${deliveryTarget}条` : "当前项目不设交付数量门槛", passed: isReference || deliveryTarget === 0 || snapshot.deliveryCount >= deliveryTarget },
    { key: "completion", label: `合同交付权重合计100%且完成度达到${rules.minDeliveryCompletion ?? 100}%`, passed: isReference || (Number(coverage.completionReady ?? 0) === 1
      && Number(coverage.deliveryItemCount ?? 0) > 0 && Math.abs(Number(coverage.deliveryWeightTotal ?? 0) - 100) < 0.01
      && Number(coverage.deliveryCompletion ?? 0) >= (rules.minDeliveryCompletion ?? 100)) },
    { key: "imports", label: "回答、竞品、交付验收及所需链接均为最新完整导入批次", passed: isReference || (Number(coverage.answersReady ?? 0) === 1
      && Number(coverage.competitorsReady ?? 0) === 1 && Number(coverage.completionReady ?? 0) === 1
      && (deliveryTarget === 0 || Number(coverage.deliveriesReady ?? 0) === 1)) },
    { key: "errors", label: "结构化数据无错误行", passed: Number(validation.errorRows ?? 0) === 0 },
    { key: "raw-evidence", label: `原始回答证据覆盖率达到${rules.minEvidenceCoverage ?? 100}%`, passed: Number(coverage.rawEvidenceCoverage ?? coverage.evidenceCoverage ?? 0) >= (rules.minEvidenceCoverage ?? 100) },
    { key: "citation-evidence", label: `品牌引用信源证据覆盖率达到${rules.minCitationEvidenceCoverage ?? 100}%`, passed: Number(coverage.citationEvidenceCoverage ?? 0) >= (rules.minCitationEvidenceCoverage ?? 100) },
    { key: "accuracy", label: `事实点核验覆盖率达到${rules.minAccuracyCoverage ?? 100}%`, passed: Number(coverage.accuracyCoverage ?? 0) >= (rules.minAccuracyCoverage ?? 100) },
    { key: "protocol", label: "平台、关键词、模型及计算版本完整匹配冻结口径", passed: isReference || (protocol.metricStandardVersion === METRIC_STANDARD_VERSION
      && protocol.calculationEngineVersion === CALCULATION_ENGINE_VERSION && Number(coverage.platformEntityMatch ?? 0) === 1
      && Number(coverage.keywordEntityMatch ?? 0) === 1 && Number(coverage.modelVersionCoverage ?? 0) >= 100) },
    { key: "competitors", label: "每条竞品记录均与回答ID、Prompt、平台及冻结竞品集合一一对应", passed: isReference || (Number(coverage.competitorCount ?? 0) > 0
      && Number(coverage.competitorScopeCoverage ?? 0) >= 100 && Number(coverage.competitorRecordCoverage ?? 0) >= 100
      && Number(coverage.competitorEntityMatch ?? 0) === 1 && Number(coverage.competitorCountMatch ?? 0) === 1
      && Number(coverage.unmatchedCompetitorCount ?? 0) === 0) },
    { key: "quality", label: "综合数据质量不低于90%", passed: snapshot.qualityScore >= 90 },
  ];
  return { passed: checks.every((item) => item.passed), checks, checkedAt: now() };
}

export async function GET() {
  const user = await requireWorkspaceUser();
  if (user instanceof Response) return user;
  return response(await loadWorkspace(user));
}

export async function POST(request: Request) {
  const user = await requireWorkspaceUser();
  if (user instanceof Response) return user;
  if (!canReadAll(user.role)) return response({ error: "当前账号没有运营端写入权限" }, 403);
  const contentType = request.headers.get("content-type") ?? "";
  if (contentType.includes("application/json")) {
    const body = await request.json() as Record<string, unknown>;
    const action = String(body.action ?? "");
    if (action === "create-project") {
      if (!canManageProjects(user.role)) return response({ error: "当前角色不能创建项目" }, 403);
      const templateSourceId = String(body.templateSourceId ?? "");
      const template = templateSourceId ? await env.DB.prepare(
        `SELECT platforms_json AS platformsJson,
          optimization_region AS optimizationRegion, service_scope AS serviceScope,
          acceptance_rules_json AS acceptanceRulesJson, measurement_protocol_json AS measurementProtocolJson
        FROM geo_projects WHERE id = ? AND project_type = 'reference'`,
      ).bind(templateSourceId).first<Record<string, string>>() : null;
      const config = configFromBody(body, template);
      if (!config.projectName || !config.brandName || !config.clientName) return response({ error: "项目名称、品牌名称和客户主体均为必填项" }, 400);
      const configJson = JSON.stringify({ ...config, derivativeKeywords: parseJson(config.derivativeKeywordsJson, []),
        platforms: parseJson(config.platformsJson, []), acceptanceRules: parseJson(config.acceptanceRulesJson, {}),
        measurementProtocol: parseJson(config.measurementProtocolJson, {}) });
      const fingerprint = await sha256(configJson);
      const timestamp = now();
      const projectId = identifier("prj");
      const slug = `geo-${Date.now().toString(36)}-${projectId.slice(-5)}`;
      const projectCode = `AW-GEO-${new Date().getUTCFullYear()}-${Date.now().toString().slice(-6)}`;
      await env.DB.batch([
        env.DB.prepare(
          `INSERT INTO geo_projects (id, slug, project_code, project_type, project_name, brand_name, client_name,
            core_keyword, derivative_keywords_json, platforms_json, optimization_region, service_scope,
            acceptance_rules_json, measurement_protocol_json, config_fingerprint, start_date, end_date,
            baseline_month, current_month, status, data_mode, created_by, created_at, updated_at)
          VALUES (?, ?, ?, 'client', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', 'empty', ?, ?, ?)`,
        ).bind(projectId, slug, projectCode, config.projectName, config.brandName, config.clientName, config.coreKeyword,
          config.derivativeKeywordsJson, config.platformsJson, config.optimizationRegion, config.serviceScope,
          config.acceptanceRulesJson, config.measurementProtocolJson, fingerprint, config.startDate, config.endDate,
          config.baselineMonth, config.currentMonth, user.email, timestamp, timestamp),
        env.DB.prepare(`INSERT INTO geo_project_members (id, project_id, user_email, display_name, role, status, created_at, updated_at)
          VALUES (?, ?, ?, ?, 'owner', 'active', ?, ?)`).bind(identifier("mem"), projectId, user.email, user.displayName, timestamp, timestamp),
        env.DB.prepare(`INSERT INTO geo_project_config_versions (id, project_id, version, config_json, fingerprint, status,
          change_reason, created_by, created_at) VALUES (?, ?, 1, ?, ?, 'draft', ?, ?, ?)`)
          .bind(identifier("cfg"), projectId, configJson, fingerprint, template ? "从标准验收样板创建" : "创建空白项目", user.email, timestamp),
      ]);
      await audit(user, "project.created", "project", projectId, projectId, { projectCode, templateSourceId: templateSourceId || null });
      return response({ ok: true, projectId, workspace: await loadWorkspace(user) }, 201);
    }
    if (action === "update-project") {
      if (!canManageProjects(user.role)) return response({ error: "当前角色不能修改项目" }, 403);
      const projectId = String(body.projectId ?? "");
      const project = await getProject(projectId);
      if (!project) return response({ error: "项目不存在" }, 404);
      if (project.projectType === "reference") return response({ error: "标准验收样板由系统维护，不支持在线修改" }, 409);
      if (project.frozenAt) return response({ error: "项目口径已冻结；如需修改，请由系统负责人先解除冻结并填写原因" }, 409);
      if (project.status !== "active") return response({ error: "仅在管项目可以修改配置" }, 409);
      const config = configFromBody(body);
      if (!config.projectName || !config.brandName || !config.clientName) return response({ error: "项目名称、品牌名称和客户主体均为必填项" }, 400);
      const configJson = JSON.stringify({ ...config, derivativeKeywords: parseJson(config.derivativeKeywordsJson, []),
        platforms: parseJson(config.platformsJson, []), acceptanceRules: parseJson(config.acceptanceRulesJson, {}),
        measurementProtocol: parseJson(config.measurementProtocolJson, {}) });
      const fingerprint = await sha256(configJson);
      const nextVersion = Number(project.configVersion || 1) + 1;
      const timestamp = now();
      await env.DB.batch([
        env.DB.prepare(`UPDATE geo_projects SET project_name = ?, brand_name = ?, client_name = ?, core_keyword = ?,
          derivative_keywords_json = ?, platforms_json = ?, optimization_region = ?, service_scope = ?,
          acceptance_rules_json = ?, measurement_protocol_json = ?, start_date = ?, end_date = ?, baseline_month = ?,
          current_month = ?, config_version = ?, config_fingerprint = ?, updated_at = ? WHERE id = ?`)
          .bind(config.projectName, config.brandName, config.clientName, config.coreKeyword, config.derivativeKeywordsJson,
            config.platformsJson, config.optimizationRegion, config.serviceScope, config.acceptanceRulesJson,
            config.measurementProtocolJson, config.startDate, config.endDate, config.baselineMonth, config.currentMonth,
            nextVersion, fingerprint, timestamp, projectId),
        env.DB.prepare(`INSERT INTO geo_project_config_versions (id, project_id, version, config_json, fingerprint, status,
          change_reason, created_by, created_at) VALUES (?, ?, ?, ?, ?, 'draft', ?, ?, ?)`)
          .bind(identifier("cfg"), projectId, nextVersion, configJson, fingerprint, String(body.changeReason ?? "项目配置更新").trim(), user.email, timestamp),
      ]);
      await audit(user, "project.updated", "project", projectId, projectId, { configVersion: nextVersion, changeReason: body.changeReason ?? "" });
      return response({ ok: true, message: "项目配置已更新并生成新版本", workspace: await loadWorkspace(user) });
    }
    if (action === "freeze-project") {
      if (!canManageProjects(user.role)) return response({ error: "当前角色不能冻结项目口径" }, 403);
      const projectId = String(body.projectId ?? "");
      const project = await getProject(projectId);
      if (!project) return response({ error: "项目不存在" }, 404);
      if (project.projectType === "reference") return response({ error: "标准验收样板已受系统保护" }, 409);
      if (project.status !== "active") return response({ error: "只有在管项目可以冻结测量口径" }, 409);
      if (project.frozenAt) return response({ error: "当前项目口径已经冻结" }, 409);
      const protocol = parseJson<Record<string, unknown>>(project.measurementProtocolJson, {});
      const missing = [!project.coreKeyword ? "核心词" : "", !parseJson<string[]>(project.derivativeKeywordsJson, []).length ? "核心衍生词" : "",
        !parseJson<string[]>(project.platformsJson, []).length ? "检测平台" : "",
        !project.baselineMonth ? "基线月份" : "", !protocol.promptPoolVersion ? "Prompt池版本" : "",
        Number(protocol.detectionCount ?? 0) < 1 ? "计划检测调用总数" : "",
        !Array.isArray(protocol.competitorSet) || !protocol.competitorSet.length ? "竞品集合" : "",
        !protocol.modelScope ? "模型范围" : "", !protocol.judgmentRulesVersion ? "判定规则版本" : "",
        protocol.metricStandardVersion !== METRIC_STANDARD_VERSION || protocol.calculationEngineVersion !== CALCULATION_ENGINE_VERSION ? "V8.1指标计算版本" : ""].filter(Boolean);
      if (missing.length) return response({ error: `冻结前请补齐：${missing.join("、")}` }, 409);
      const configJson = JSON.stringify(projectConfig(project));
      const fingerprint = await sha256(configJson);
      const timestamp = now();
      await env.DB.batch([
        env.DB.prepare("UPDATE geo_projects SET config_fingerprint = ?, frozen_at = ?, frozen_by = ?, updated_at = ? WHERE id = ?")
          .bind(fingerprint, timestamp, user.email, timestamp, projectId),
        env.DB.prepare("UPDATE geo_snapshots SET status = 'superseded', updated_at = ? WHERE project_id = ? AND status = 'draft' AND scope_fingerprint <> ?")
          .bind(timestamp, projectId, fingerprint),
        env.DB.prepare(`INSERT INTO geo_project_config_versions (id, project_id, version, config_json, fingerprint, status,
          change_reason, created_by, created_at, frozen_at) VALUES (?, ?, ?, ?, ?, 'frozen', ?, ?, ?, ?)
          ON CONFLICT(project_id, version) DO UPDATE SET config_json = excluded.config_json, fingerprint = excluded.fingerprint,
          status = 'frozen', change_reason = excluded.change_reason, frozen_at = excluded.frozen_at`)
          .bind(identifier("cfg"), projectId, project.configVersion, configJson, fingerprint,
            String(body.changeReason ?? "项目启动测量口径确认").trim(), user.email, timestamp, timestamp),
      ]);
      await audit(user, "project.scope_frozen", "project", projectId, projectId, { configVersion: project.configVersion, fingerprint });
      return response({ ok: true, message: "测量口径已冻结，可开始导入基线及月度数据", workspace: await loadWorkspace(user) });
    }
    if (action === "reopen-project") {
      if (!isOwner(user.role)) return response({ error: "只有系统负责人可以解除口径冻结" }, 403);
      const projectId = String(body.projectId ?? "");
      const reason = String(body.changeReason ?? "").trim();
      if (!reason) return response({ error: "解除冻结必须填写变更原因" }, 400);
      const project = await getProject(projectId);
      if (!project) return response({ error: "项目不存在" }, 404);
      if (project.projectType === "reference") return response({ error: "标准验收样板受系统保护，不可解除冻结" }, 409);
      if (project.status !== "active") return response({ error: "只有在管项目可以解除口径冻结" }, 409);
      if (!project.frozenAt) return response({ error: "当前项目口径尚未冻结" }, 409);
      await env.DB.prepare("UPDATE geo_projects SET frozen_at = NULL, frozen_by = NULL, updated_at = ? WHERE id = ?").bind(now(), projectId).run();
      await audit(user, "project.scope_reopened", "project", projectId, projectId, { reason });
      return response({ ok: true, message: "口径冻结已解除；修改后请重新冻结再导入数据", workspace: await loadWorkspace(user) });
    }
    if (action === "add-project-member" || action === "remove-project-member") {
      if (!canManageProjects(user.role)) return response({ error: "当前角色不能管理项目成员" }, 403);
      const projectId = String(body.projectId ?? "");
      const project = await getProject(projectId);
      if (!project) return response({ error: "项目不存在" }, 404);
      if (project.projectType === "reference") return response({ error: "标准验收样板不开放甲方成员" }, 409);
      const userEmail = String(body.userEmail ?? "").trim().toLowerCase();
      if (!/^\S+@\S+\.\S+$/.test(userEmail)) return response({ error: "请输入有效的成员邮箱" }, 400);
      const timestamp = now();
      if (action === "remove-project-member") {
        await env.DB.prepare("UPDATE geo_project_members SET status = 'revoked', updated_at = ? WHERE project_id = ? AND user_email = ?")
          .bind(timestamp, projectId, userEmail).run();
        await audit(user, "member.revoked", "project_member", userEmail, projectId, {});
        return response({ ok: true, message: "该账号的项目访问权限已停用", workspace: await loadWorkspace(user) });
      }
      const memberRole = ["viewer", "approver"].includes(String(body.memberRole)) ? String(body.memberRole) : "viewer";
      await env.DB.prepare(`INSERT INTO geo_project_members (id, project_id, user_email, display_name, role, status, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, 'active', ?, ?) ON CONFLICT(project_id, user_email) DO UPDATE SET display_name = excluded.display_name,
        role = excluded.role, status = 'active', updated_at = excluded.updated_at`)
        .bind(identifier("mem"), projectId, userEmail, String(body.displayName ?? "").trim() || null, memberRole, timestamp, timestamp).run();
      await audit(user, "member.granted", "project_member", userEmail, projectId, { role: memberRole });
      return response({ ok: true, message: "项目成员已保存；站点访问开通后即可查看授权报告", workspace: await loadWorkspace(user) });
    }
    if (["archive-project", "delete-project", "restore-project", "permanent-delete-project"].includes(action)) {
      if (!canManageProjects(user.role)) return response({ error: "当前角色不能管理项目生命周期" }, 403);
      const projectId = String(body.projectId ?? "");
      const project = await getProject(projectId);
      if (!project) return response({ error: "项目不存在" }, 404);
      if (project.projectType === "reference") return response({ error: "标准验收样板受系统保护，不可归档或删除" }, 409);
      const timestamp = now();
      if (action === "archive-project") {
        if (project.status !== "active") return response({ error: "只有在管项目可以归档" }, 409);
        await env.DB.prepare("UPDATE geo_projects SET status = 'archived', updated_at = ? WHERE id = ?").bind(timestamp, projectId).run();
        await audit(user, "project.archived", "project", projectId, projectId, {});
        return response({ ok: true, message: "项目已归档并停止数据写入", workspace: await loadWorkspace(user) });
      }
      if (action === "delete-project") {
        if (!["active", "archived"].includes(project.status)) return response({ error: "该项目当前状态不能移入回收站" }, 409);
        const deleteAfter = new Date(Date.now() + 30 * 24 * 60 * 60 * 1000).toISOString();
        await env.DB.prepare("UPDATE geo_projects SET status = 'recycle_bin', deleted_at = ?, deleted_by = ?, delete_after = ?, updated_at = ? WHERE id = ?")
          .bind(timestamp, user.email, deleteAfter, timestamp, projectId).run();
        await audit(user, "project.recycled", "project", projectId, projectId, { deleteAfter });
        return response({ ok: true, message: "项目已移入回收站，可在30天内恢复", workspace: await loadWorkspace(user) });
      }
      if (action === "restore-project") {
        if (!["archived", "recycle_bin"].includes(project.status)) return response({ error: "该项目当前状态无需恢复" }, 409);
        await env.DB.prepare("UPDATE geo_projects SET status = 'active', deleted_at = NULL, deleted_by = NULL, delete_after = NULL, updated_at = ? WHERE id = ?")
          .bind(timestamp, projectId).run();
        await audit(user, "project.restored", "project", projectId, projectId, {});
        return response({ ok: true, message: "项目已恢复", workspace: await loadWorkspace(user) });
      }
      if (!isOwner(user.role)) return response({ error: "只有系统负责人可以永久删除项目" }, 403);
      if (project.status !== "recycle_bin") return response({ error: "项目必须先移入回收站才能永久删除" }, 409);
      if (String(body.confirmName ?? "") !== project.projectName) return response({ error: "项目名称确认不一致" }, 400);
      await audit(user, "project.permanent_delete_started", "project", projectId, projectId, { projectName: project.projectName });
      await deleteProjectObjects(projectId);
      await env.DB.prepare("DELETE FROM geo_projects WHERE id = ?").bind(projectId).run();
      return response({ ok: true, message: "项目及其关联数据已永久删除", workspace: await loadWorkspace(user) });
    }
    if (action === "publish-snapshot") {
      if (!canPublish(user.role)) return response({ error: "只有系统负责人或数据审核人可以发布正式报告" }, 403);
      const snapshotId = String(body.snapshotId ?? "");
      if (!snapshotId) return response({ error: "缺少待发布快照" }, 400);
      await refreshSnapshot(snapshotId);
      const snapshot = await env.DB.prepare(
        `SELECT s.id, s.project_id AS projectId, s.report_month AS reportMonth, s.status,
          s.answer_count AS answerCount, s.delivery_count AS deliveryCount, s.quality_score AS qualityScore, s.summary_json AS summaryJson,
          s.scope_fingerprint AS scopeFingerprint, p.project_type AS projectType, p.frozen_at AS frozenAt,
          p.acceptance_rules_json AS acceptanceRulesJson, p.measurement_protocol_json AS measurementProtocolJson,
          p.config_fingerprint AS configFingerprint
        FROM geo_snapshots s JOIN geo_projects p ON p.id = s.project_id WHERE s.id = ?`,
      ).bind(snapshotId).first<{ id: string; projectId: string; reportMonth: string; status: string; answerCount: number; deliveryCount: number;
        qualityScore: number; summaryJson: string; scopeFingerprint: string; projectType: string; frozenAt: string | null;
        acceptanceRulesJson: string; measurementProtocolJson: string; configFingerprint: string }>();
      if (!snapshot) return response({ error: "找不到对应快照" }, 404);
      if (snapshot.status !== "draft") return response({ error: "只有待复核快照可以发布" }, 409);
      const gate = publishGate(snapshot, snapshot);
      if (!gate.passed) return response({ error: "发布质量门未通过，请完成全部阻断项", gate }, 409);
      const timestamp = now();
      await env.DB.batch([
        env.DB.prepare("UPDATE geo_snapshots SET status = 'superseded', updated_at = ? WHERE project_id = ? AND report_month = ? AND status = 'published' AND id <> ?")
          .bind(timestamp, snapshot.projectId, snapshot.reportMonth, snapshotId),
        env.DB.prepare(`UPDATE geo_snapshots SET status = 'published', gate_json = ?, reviewed_by = ?, review_note = ?,
          published_by = ?, acceptance_status = 'awaiting_acceptance', published_at = ?, updated_at = ? WHERE id = ?`)
          .bind(JSON.stringify(gate), user.email, String(body.reviewNote ?? "").trim(), user.email, timestamp, timestamp, snapshotId),
        env.DB.prepare("UPDATE geo_projects SET data_mode = CASE WHEN project_type = 'reference' THEN 'reference' ELSE 'live' END, current_month = ?, updated_at = ? WHERE id = ?")
          .bind(snapshot.reportMonth, timestamp, snapshot.projectId),
      ]);
      await audit(user, "snapshot.published", "snapshot", snapshotId, snapshot.projectId, { reportMonth: snapshot.reportMonth, qualityScore: snapshot.qualityScore });
      return response({ ok: true, message: "月度快照已通过质量门并发布", workspace: await loadWorkspace(user) });
    }
    return response({ error: "不支持的操作" }, 400);
  }

  if (!canManageProjects(user.role)) return response({ error: "当前角色不能导入或修改项目数据" }, 403);
  const form = await request.formData();
  const file = form.get("file");
  const projectId = String(form.get("projectId") ?? "");
  const reportMonth = String(form.get("reportMonth") ?? "");
  const importType = String(form.get("importType") ?? "");
  const evidenceRecordId = String(form.get("recordId") ?? "").trim();
  if (!(file instanceof File) || !projectId || !importType) return response({ error: "请选择项目、数据类型和文件" }, 400);
  if (file.size > 12 * 1024 * 1024) return response({ error: "单个文件不得超过12MB" }, 400);
  const project = await getProject(projectId);
  if (!project) return response({ error: "项目不存在" }, 404);
  if (project.status !== "active") return response({ error: "只有在管项目可以导入数据，请先恢复或重新启用" }, 409);
  if (project.projectType === "reference") return response({ error: "标准验收样板已锁定，请从样板创建客户项目后导入数据" }, 409);
  const allowed = ["answers", "competitors", "deliveries", "completion", "evidence", "brand_logo"];
  if (!allowed.includes(importType)) return response({ error: "不支持的数据类型" }, 400);
  if (importType !== "brand_logo" && !project.frozenAt) return response({ error: "请先完成项目配置并冻结测量口径，再导入正式数据" }, 409);
  if (["answers", "competitors", "deliveries", "completion"].includes(importType) && !/\.(csv|tsv|json)$/i.test(file.name)) return response({ error: "结构化数据仅支持CSV、TSV或JSON文件" }, 400);
  if (importType === "brand_logo" && !/^image\/(png|jpeg|webp)$/i.test(file.type)) return response({ error: "品牌Logo仅支持PNG、JPG或WebP" }, 400);
  if (importType === "evidence" && !/\.(html?|png|jpe?g|webp|pdf)$/i.test(file.name)) return response({ error: "证据文件仅支持HTML、图片或PDF" }, 400);
  const bytes = await file.arrayBuffer();
  const jobId = identifier("imp");
  const safeName = file.name.replace(/[^a-zA-Z0-9._\-\u4e00-\u9fff]/g, "_").slice(0, 120) || "upload.bin";
  const objectKey = `projects/${projectId}/imports/${jobId}/${safeName}`;
  const timestamp = now();
  if (importType === "brand_logo") {
    await env.BUCKET.put(objectKey, bytes, { httpMetadata: { contentType: file.type || "application/octet-stream" } });
    await env.DB.batch([
      env.DB.prepare("UPDATE geo_projects SET logo_key = ?, updated_at = ? WHERE id = ?").bind(objectKey, timestamp, projectId),
      env.DB.prepare("INSERT INTO geo_import_jobs (id, project_id, import_type, filename, object_key, status, total_rows, valid_rows, error_rows, errors_json, created_by, created_at) VALUES (?, ?, ?, ?, ?, 'completed', 1, 1, 0, '[]', ?, ?)")
        .bind(jobId, projectId, importType, file.name, objectKey, user.email, timestamp),
    ]);
    await audit(user, "project.logo_uploaded", "file", jobId, projectId, { filename: file.name });
    return response({ ok: true, message: "品牌Logo已保存", workspace: await loadWorkspace(user) });
  }
  const month = /^\d{4}-\d{2}$/.test(reportMonth) ? reportMonth : new Date().toISOString().slice(0, 7);
  const snapshotId = await getDraftSnapshot(projectId, month, user.email, file.name);
  if (importType === "evidence") {
    if (evidenceRecordId) {
      const target = await env.DB.prepare("SELECT id FROM geo_evidence_records WHERE project_id = ? AND snapshot_id = ? AND record_id = ? AND prompt_id <> '' AND record_status = 'valid'")
        .bind(projectId, snapshotId, evidenceRecordId).first<{ id: string }>();
      if (!target) return response({ error: "未找到该回答记录ID，请先导入对应AI回答或核对报告月份" }, 404);
      await env.BUCKET.put(objectKey, bytes, { httpMetadata: { contentType: file.type || "application/octet-stream" } });
      await env.DB.batch([
        env.DB.prepare("UPDATE geo_evidence_records SET evidence_object_key = ?, sha256 = ? WHERE id = ?").bind(objectKey, await sha256(bytes), target.id),
        env.DB.prepare("INSERT INTO geo_import_jobs (id, project_id, snapshot_id, import_type, filename, object_key, status, total_rows, valid_rows, error_rows, errors_json, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, 'completed', 1, 1, 0, '[]', ?, ?)")
          .bind(jobId, projectId, snapshotId, importType, file.name, objectKey, user.email, timestamp),
      ]);
    } else {
      await env.BUCKET.put(objectKey, bytes, { httpMetadata: { contentType: file.type || "application/octet-stream" } });
      await env.DB.batch([
        env.DB.prepare("INSERT INTO geo_evidence_records (id, project_id, snapshot_id, record_id, evidence_object_key, sha256, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)")
          .bind(identifier("evd"), projectId, snapshotId, `FILE-${Date.now()}`, objectKey, await sha256(bytes), timestamp),
        env.DB.prepare("INSERT INTO geo_import_jobs (id, project_id, snapshot_id, import_type, filename, object_key, status, total_rows, valid_rows, error_rows, errors_json, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, 'completed', 1, 1, 0, '[]', ?, ?)")
          .bind(jobId, projectId, snapshotId, importType, file.name, objectKey, user.email, timestamp),
      ]);
    }
    await refreshSnapshot(snapshotId, file.name);
    await audit(user, "evidence.uploaded", "snapshot", snapshotId, projectId, { filename: file.name, recordId: evidenceRecordId || null });
    return response({ ok: true, message: evidenceRecordId ? "证据已关联到指定AI回答" : "项目级证据附件已归档", workspace: await loadWorkspace(user) });
  }
  let validation;
  try {
    const rows = parseStructuredFile(new TextDecoder().decode(bytes), file.name);
    if (rows.length > 2000) return response({ error: "单次最多导入2000条记录，请拆分文件" }, 400);
    validation = validateImport(importType as "answers" | "competitors" | "deliveries" | "completion", rows);
  } catch { return response({ error: "文件解析失败，请使用下载的CSV模板或标准JSON数据" }, 400); }
  await env.BUCKET.put(objectKey, bytes, { httpMetadata: { contentType: file.type || "application/octet-stream" } });
  await env.DB.prepare(`INSERT INTO geo_import_jobs (id, project_id, snapshot_id, import_type, filename, object_key,
    status, total_rows, valid_rows, error_rows, errors_json, created_by, created_at)
    VALUES (?, ?, ?, ?, ?, ?, 'processing', ?, ?, ?, ?, ?, ?)`)
    .bind(jobId, projectId, snapshotId, importType, file.name, objectKey, validation.rows.length,
      validation.validRows.length, validation.errors.length, JSON.stringify(validation.errors.slice(0, 100)), user.email, timestamp).run();
  const statements: Array<ReturnType<typeof env.DB.prepare>> = [];
  for (const row of validation.validRows) {
    const rowHash = await sha256(JSON.stringify(row));
    if (importType === "answers") {
      const factTotal = Number(row.fact_points_total);
      const factCorrect = Number(row.fact_points_correct);
      statements.push(env.DB.prepare(
        `INSERT INTO geo_evidence_records (id, project_id, snapshot_id, record_id, prompt_id, platform,
          model_version, tested_at, record_status, keyword, question_type, scene, region, question, answer, conversation_url,
          mention, recommendation_prompt, recommend, top3, rank, sentiment, accuracy, fact_points_total,
          fact_points_correct, accuracy_note, judged_by, citation_detectable, brand_cited, citation_urls,
          import_job_id, sha256, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(project_id, snapshot_id, record_id) DO UPDATE SET prompt_id = excluded.prompt_id,
          platform = excluded.platform, model_version = excluded.model_version, tested_at = excluded.tested_at,
          record_status = excluded.record_status,
          keyword = excluded.keyword, question_type = excluded.question_type, scene = excluded.scene,
          region = excluded.region, question = excluded.question, answer = excluded.answer,
          conversation_url = excluded.conversation_url, mention = excluded.mention,
          recommendation_prompt = excluded.recommendation_prompt, recommend = excluded.recommend,
          top3 = excluded.top3, rank = excluded.rank, sentiment = excluded.sentiment, accuracy = excluded.accuracy,
          fact_points_total = excluded.fact_points_total, fact_points_correct = excluded.fact_points_correct,
          accuracy_note = excluded.accuracy_note, judged_by = excluded.judged_by,
          citation_detectable = excluded.citation_detectable, brand_cited = excluded.brand_cited,
          citation_urls = excluded.citation_urls, import_job_id = excluded.import_job_id, sha256 = excluded.sha256`,
      ).bind(identifier("evd"), projectId, snapshotId, row.record_id, row.prompt_id, row.platform,
        row.model_version ?? "", row.tested_at, row.record_status, row.keyword ?? "", row.question_type ?? "", row.scene ?? "",
        row.region ?? "", row.question, row.answer || "", row.conversation_url || null, flag(row.mention), flag(row.recommendation_prompt),
        flag(row.recommend), flag(row.top3), row.rank ? Number(row.rank) : null, row.sentiment || null,
        factTotal ? (factCorrect === factTotal ? 1 : 0) : null, factTotal, factCorrect,
        row.accuracy_note || null, row.judged_by || null, flag(row.citation_detectable), flag(row.brand_cited),
        JSON.stringify((row.citation_urls ?? "").split("|").map((item) => item.trim()).filter(Boolean)), jobId, rowHash, timestamp));
    } else if (importType === "competitors") {
      statements.push(env.DB.prepare(
        `INSERT INTO geo_competitor_records (id, project_id, snapshot_id, record_id, prompt_id, platform,
          competitor_name, mentioned, recommended, rank, import_job_id, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(snapshot_id, record_id, competitor_name) DO UPDATE SET prompt_id = excluded.prompt_id,
          platform = excluded.platform, mentioned = excluded.mentioned, recommended = excluded.recommended,
          rank = excluded.rank, import_job_id = excluded.import_job_id`,
      ).bind(identifier("cmp"), projectId, snapshotId, row.record_id, row.prompt_id, row.platform,
        row.competitor_name, flag(row.mentioned), flag(row.recommended), row.rank ? Number(row.rank) : null, jobId, timestamp));
    } else if (importType === "deliveries") {
      statements.push(env.DB.prepare(
        `INSERT INTO geo_delivery_items (id, project_id, snapshot_id, item_id, title, media, media_level,
          original_url, publish_date, index_status, citation_status, ai_platforms, import_job_id, sha256, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(project_id, snapshot_id, item_id) DO UPDATE SET title = excluded.title,
          media = excluded.media, media_level = excluded.media_level, original_url = excluded.original_url,
          publish_date = excluded.publish_date, index_status = excluded.index_status,
          citation_status = excluded.citation_status, ai_platforms = excluded.ai_platforms,
          import_job_id = excluded.import_job_id, sha256 = excluded.sha256`,
      ).bind(identifier("dlv"), projectId, snapshotId, row.item_id, row.title, row.media, row.media_level ?? "",
        row.original_url, row.publish_date || null, row.index_status || "待检测", row.citation_status || "待检测",
        JSON.stringify((row.ai_platforms ?? "").split("|").map((item) => item.trim()).filter(Boolean)), jobId, rowHash, timestamp));
    } else {
      statements.push(env.DB.prepare(
        `INSERT INTO geo_delivery_acceptance_items (id, project_id, snapshot_id, item_id, category, item_name,
          unit, contract_quantity, qualified_quantity, weight, evidence_url, note, import_job_id, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(snapshot_id, item_id) DO UPDATE SET category = excluded.category, item_name = excluded.item_name,
          unit = excluded.unit, contract_quantity = excluded.contract_quantity,
          qualified_quantity = excluded.qualified_quantity, weight = excluded.weight,
          evidence_url = excluded.evidence_url, note = excluded.note, import_job_id = excluded.import_job_id`,
      ).bind(identifier("dac"), projectId, snapshotId, row.item_id, row.category, row.item_name, row.unit,
        Number(row.contract_quantity), Number(row.qualified_quantity), Number(row.weight), row.evidence_url,
        row.note ?? "", jobId, timestamp));
    }
  }
  await batchRun(statements);
  const status = validation.errors.length ? (validation.validRows.length ? "partial" : "failed") : "completed";
  const finalize: Array<ReturnType<typeof env.DB.prepare>> = [];
  if (validation.validRows.length) {
    const table = importType === "answers" ? "geo_evidence_records" : importType === "competitors" ? "geo_competitor_records"
      : importType === "deliveries" ? "geo_delivery_items" : "geo_delivery_acceptance_items";
    const answerClause = importType === "answers" ? " AND prompt_id <> ''" : "";
    finalize.push(env.DB.prepare(`DELETE FROM ${table} WHERE snapshot_id = ?${answerClause} AND COALESCE(import_job_id, '') <> ?`).bind(snapshotId, jobId));
  }
  finalize.push(
    env.DB.prepare("UPDATE geo_import_jobs SET status = ? WHERE id = ?").bind(status, jobId),
    env.DB.prepare(`UPDATE geo_projects SET data_mode = CASE WHEN ? > 0 THEN 'live' ELSE data_mode END,
      current_month = CASE WHEN ? > 0 THEN ? ELSE current_month END, updated_at = ? WHERE id = ?`)
      .bind(validation.validRows.length, validation.validRows.length, month, timestamp, projectId),
  );
  await env.DB.batch(finalize);
  await refreshSnapshot(snapshotId, file.name);
  await audit(user, "data.imported", "import_job", jobId, projectId, { importType, reportMonth: month,
    totalRows: validation.rows.length, validRows: validation.validRows.length, errorRows: validation.errors.length });
  return response({ ok: validation.validRows.length > 0, status, errors: validation.errors.slice(0, 20), workspace: await loadWorkspace(user) }, validation.validRows.length ? 200 : 422);
}
