import { env } from "cloudflare:workers";

import { getChatGPTUser } from "@/app/chatgpt-auth";
import { allocatePercentages } from "@/lib/geo-metrics";

export const dynamic = "force-dynamic";

type MetricAggregate = {
  answerCount: number; recommendationPromptCount: number; mentions: number; recommendations: number; top3: number; positive: number;
  neutralPositive: number; mentionedRows: number; citationDetectableRows: number; brandCited: number;
  rankedRows: number; rankTotal: number; factPointsTotal: number; factPointsCorrect: number;
};

function json(value: unknown, status = 200) {
  return Response.json(value, { status, headers: { "cache-control": "private, no-store", "x-robots-tag": "noindex, nofollow" } });
}

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

function rate(value: number, total: number) {
  return total ? Number(((value / total) * 100).toFixed(1)) : null;
}

function calculateMetrics(row: Partial<MetricAggregate>, shareOfVoice?: number | null) {
  const total = Number(row.answerCount ?? 0);
  const recommendationTotal = Number(row.recommendationPromptCount ?? 0);
  const mentionRate = rate(Number(row.mentions ?? 0), total);
  const recommendRate = rate(Number(row.recommendations ?? 0), recommendationTotal);
  const top3Rate = rate(Number(row.top3 ?? 0), recommendationTotal);
  const positiveRate = rate(Number(row.positive ?? 0), Number(row.mentionedRows ?? 0));
  const neutralPositiveRate = rate(Number(row.neutralPositive ?? 0), Number(row.mentionedRows ?? 0));
  const citationRate = rate(Number(row.brandCited ?? 0), Number(row.citationDetectableRows ?? 0));
  const accuracyRate = rate(Number(row.factPointsCorrect ?? 0), Number(row.factPointsTotal ?? 0));
  const averageRank = Number(row.rankedRows ?? 0)
    ? Number((Number(row.rankTotal ?? 0) / Number(row.rankedRows)).toFixed(2))
    : null;
  const rankScore = averageRank === null ? (total ? 0 : null) : Math.max(0, Math.min(100, ((11 - averageRank) / 10) * 100));
  const visibilityInputs = [mentionRate, recommendRate, top3Rate, rankScore, citationRate, shareOfVoice];
  const visibility = visibilityInputs.every((item) => typeof item === "number")
    ? Number(((mentionRate ?? 0) * 0.25 + (recommendRate ?? 0) * 0.25 + (top3Rate ?? 0) * 0.15
      + (rankScore ?? 0) * 0.1 + (citationRate ?? 0) * 0.1 + (shareOfVoice ?? 0) * 0.15).toFixed(1))
    : null;
  const health = typeof accuracyRate === "number" && typeof neutralPositiveRate === "number"
    ? Number((accuracyRate * 0.6 + neutralPositiveRate * 0.4).toFixed(1))
    : null;
  return { answerCount: total, mentionRate, recommendRate, top3Rate, positiveRate, neutralPositiveRate,
    citationRate, accuracyRate, averageRank, visibility, health };
}

async function resolveAccess(projectId: string, email: string) {
  const [workspaceUser, membership] = await Promise.all([
    env.DB.prepare("SELECT role FROM workspace_users WHERE email = ?").bind(email).first<{ role: string }>(),
    env.DB.prepare("SELECT role, status FROM geo_project_members WHERE project_id = ? AND user_email = ?")
      .bind(projectId, email).first<{ role: string; status: string }>(),
  ]);
  const internal = ["owner", "operator", "reviewer"].includes(workspaceUser?.role ?? "");
  return { allowed: internal || membership?.status === "active", internal, memberRole: internal ? workspaceUser?.role ?? "viewer" : membership?.role ?? "viewer" };
}

function aggregateSql(where: string) {
  return `SELECT COUNT(*) AS answerCount, COALESCE(SUM(mention), 0) AS mentions,
    COALESCE(SUM(recommendation_prompt), 0) AS recommendationPromptCount,
    COALESCE(SUM(CASE WHEN recommendation_prompt = 1 THEN recommend ELSE 0 END), 0) AS recommendations,
    COALESCE(SUM(CASE WHEN recommendation_prompt = 1 THEN top3 ELSE 0 END), 0) AS top3,
    COALESCE(SUM(CASE WHEN mention = 1 AND sentiment IN ('positive', '正面') THEN 1 ELSE 0 END), 0) AS positive,
    COALESCE(SUM(CASE WHEN mention = 1 AND sentiment IN ('positive', '正面', 'neutral', '中性') THEN 1 ELSE 0 END), 0) AS neutralPositive,
    COALESCE(SUM(CASE WHEN mention = 1 THEN 1 ELSE 0 END), 0) AS mentionedRows,
    COALESCE(SUM(citation_detectable), 0) AS citationDetectableRows,
    COALESCE(SUM(CASE WHEN citation_detectable = 1 THEN brand_cited ELSE 0 END), 0) AS brandCited,
    COALESCE(SUM(CASE WHEN recommendation_prompt = 1 AND recommend = 1 AND rank IS NOT NULL THEN 1 ELSE 0 END), 0) AS rankedRows,
    COALESCE(SUM(CASE WHEN recommendation_prompt = 1 AND recommend = 1 AND rank IS NOT NULL THEN rank ELSE 0 END), 0) AS rankTotal,
    COALESCE(SUM(fact_points_total), 0) AS factPointsTotal,
    COALESCE(SUM(fact_points_correct), 0) AS factPointsCorrect
    FROM geo_evidence_records WHERE ${where}`;
}

export async function GET(request: Request) {
  const identity = await getChatGPTUser();
  if (!identity) return json({ error: "请登录后查看项目报告" }, 401);
  const url = new URL(request.url);
  const slug = url.searchParams.get("slug")?.trim();
  if (!slug) return json({ error: "缺少项目标识" }, 400);
  const project = await env.DB.prepare(
    `SELECT id, slug, project_code AS projectCode, project_type AS projectType,
      project_name AS projectName, brand_name AS brandName, client_name AS clientName,
      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, status, data_mode AS dataMode, logo_key AS logoKey, updated_at AS updatedAt
    FROM geo_projects WHERE slug = ? AND status NOT IN ('recycle_bin')`,
  ).bind(slug).first<Record<string, unknown>>();
  if (!project) return json({ error: "项目不存在或尚未开放" }, 404);
  const access = await resolveAccess(String(project.id), identity.email);
  if (!access.allowed) return json({ error: "当前账号没有该项目的查看权限" }, 403);

  const snapshotsResult = await env.DB.prepare(
    `SELECT id, report_month AS reportMonth, version, status, quality_score AS qualityScore,
      answer_count AS answerCount, delivery_count AS deliveryCount, evidence_count AS evidenceCount,
      summary_json AS summaryJson, scope_fingerprint AS scopeFingerprint, scope_json AS scopeJson,
      gate_json AS gateJson, reviewed_by AS reviewedBy, review_note AS reviewNote,
      published_by AS publishedBy, acceptance_status AS acceptanceStatus, accepted_at AS acceptedAt,
      accepted_by AS acceptedBy, published_at AS publishedAt, updated_at AS updatedAt
    FROM geo_snapshots WHERE project_id = ? AND status = 'published'
    ORDER BY report_month DESC, version DESC LIMIT 12`,
  ).bind(project.id).all<Record<string, unknown>>();
  const snapshots = snapshotsResult.results as Record<string, unknown>[];
  const requestedMonth = url.searchParams.get("month")?.trim();
  const selectedSnapshot = snapshots.find((item) => item.reportMonth === requestedMonth) ?? snapshots[0] ?? null;
  const normalizedProject = { ...project, derivativeKeywords: parseJson(project.derivativeKeywordsJson, []),
    platforms: parseJson(project.platformsJson, []), acceptanceRules: parseJson(project.acceptanceRulesJson, {}),
    measurementProtocol: parseJson(project.measurementProtocolJson, {}) };
  if (!selectedSnapshot) return json({ project: normalizedProject, access, snapshot: null, snapshots: [], metrics: null,
    platforms: [], answers: [], answerTotal: 0, deliveries: [], completion: [], deliveryCompletion: null,
    competitors: [], demand: [], trend: [], filters: {}, acceptanceHistory: [] });

  const filterValues = {
    platform: url.searchParams.get("platform")?.trim() ?? "",
    keyword: url.searchParams.get("keyword")?.trim() ?? "",
    scene: url.searchParams.get("scene")?.trim() ?? "",
    region: url.searchParams.get("region")?.trim() ?? "",
  };
  const clauses = ["project_id = ?", "snapshot_id = ?", "prompt_id <> ''", "record_status = 'valid'"];
  const bindings: Array<string | number> = [String(project.id), String(selectedSnapshot.id)];
  for (const [field, column] of [["platform", "platform"], ["keyword", "keyword"], ["scene", "scene"], ["region", "region"]] as const) {
    if (filterValues[field]) { clauses.push(`${column} = ?`); bindings.push(filterValues[field]); }
  }
  const where = clauses.join(" AND ");
  const page = Math.max(1, Number(url.searchParams.get("page") || 1));
  const pageSize = 80;
  const offset = (page - 1) * pageSize;
  const snapshotSummary = parseJson<Record<string, unknown>>(selectedSnapshot.summaryJson, {});
  const competitorClauses = ["c.project_id = ?", "c.snapshot_id = ?"];
  const competitorBindings: Array<string | number> = [String(project.id), String(selectedSnapshot.id)];
  if (filterValues.platform) { competitorClauses.push("c.platform = ?"); competitorBindings.push(filterValues.platform); }
  for (const [field, column] of [["keyword", "keyword"], ["scene", "scene"], ["region", "region"]] as const) {
    if (filterValues[field]) {
      competitorClauses.push(`base.${column} = ?`);
      competitorBindings.push(filterValues[field]);
    }
  }

  const [aggregate, answersResult, deliveryResult, completionResult, platformResult, competitorResult, demandResult,
    trendResult, filterResult, acceptanceResult] = await Promise.all([
    env.DB.prepare(aggregateSql(where)).bind(...bindings).first<MetricAggregate>(),
    env.DB.prepare(
      `SELECT record_id AS recordId, prompt_id AS promptId, platform, model_version AS modelVersion,
        tested_at AS testedAt, record_status AS recordStatus, keyword, question_type AS questionType, scene, region, question, answer,
        conversation_url AS conversationUrl, evidence_object_key AS evidenceObjectKey,
        mention, recommendation_prompt AS recommendationPrompt, recommend, top3, rank, sentiment, accuracy,
        fact_points_total AS factPointsTotal, fact_points_correct AS factPointsCorrect,
        accuracy_note AS accuracyNote, judged_by AS judgedBy, citation_detectable AS citationDetectable,
        brand_cited AS brandCited, citation_urls AS citationUrls
      FROM geo_evidence_records WHERE ${where} ORDER BY tested_at DESC, record_id ASC LIMIT ? OFFSET ?`,
    ).bind(...bindings, pageSize, offset).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT item_id AS itemId, title, media, media_level AS mediaLevel, original_url AS originalUrl,
        publish_date AS publishDate, index_status AS indexStatus, citation_status AS citationStatus,
        ai_platforms AS aiPlatforms FROM geo_delivery_items WHERE project_id = ? AND snapshot_id = ?
      ORDER BY publish_date DESC, item_id ASC LIMIT 1000`,
    ).bind(project.id, selectedSnapshot.id).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT item_id AS itemId, category, item_name AS itemName, unit,
        contract_quantity AS contractQuantity, qualified_quantity AS qualifiedQuantity,
        weight, evidence_url AS evidenceUrl, note
      FROM geo_delivery_acceptance_items WHERE project_id = ? AND snapshot_id = ?
      ORDER BY item_id ASC LIMIT 200`,
    ).bind(project.id, selectedSnapshot.id).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT platform, COUNT(*) AS answerCount, COALESCE(SUM(mention), 0) AS mentions,
        COALESCE(SUM(recommendation_prompt), 0) AS recommendationPromptCount,
        COALESCE(SUM(CASE WHEN recommendation_prompt = 1 THEN recommend ELSE 0 END), 0) AS recommendations,
        COALESCE(SUM(CASE WHEN recommendation_prompt = 1 THEN top3 ELSE 0 END), 0) AS top3,
        COALESCE(SUM(CASE WHEN mention = 1 AND sentiment IN ('positive','正面') THEN 1 ELSE 0 END), 0) AS positive,
        COALESCE(SUM(CASE WHEN mention = 1 AND sentiment IN ('positive','正面','neutral','中性') THEN 1 ELSE 0 END), 0) AS neutralPositive,
        COALESCE(SUM(CASE WHEN mention = 1 THEN 1 ELSE 0 END), 0) AS mentionedRows,
        COALESCE(SUM(citation_detectable), 0) AS citationDetectableRows,
        COALESCE(SUM(CASE WHEN citation_detectable = 1 THEN brand_cited ELSE 0 END), 0) AS brandCited,
        COALESCE(SUM(CASE WHEN recommendation_prompt = 1 AND recommend = 1 AND rank IS NOT NULL THEN 1 ELSE 0 END), 0) AS rankedRows,
        COALESCE(SUM(CASE WHEN recommendation_prompt = 1 AND recommend = 1 AND rank IS NOT NULL THEN rank ELSE 0 END), 0) AS rankTotal,
        COALESCE(SUM(fact_points_total), 0) AS factPointsTotal,
        COALESCE(SUM(fact_points_correct), 0) AS factPointsCorrect
      FROM geo_evidence_records WHERE ${where} GROUP BY platform ORDER BY answerCount DESC`,
    ).bind(...bindings).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT c.competitor_name AS competitorName, COUNT(*) AS sampleCount,
        COALESCE(SUM(c.mentioned), 0) AS mentions, COALESCE(SUM(c.recommended), 0) AS recommendations,
        AVG(CASE WHEN c.rank IS NOT NULL THEN c.rank END) AS averageRank
      FROM geo_competitor_records c JOIN geo_evidence_records base
        ON base.snapshot_id = c.snapshot_id AND base.record_id = c.record_id
        AND base.prompt_id = c.prompt_id AND base.platform = c.platform
        AND base.record_status = 'valid' AND base.recommendation_prompt = 1
      WHERE ${competitorClauses.join(" AND ")}
      GROUP BY competitor_name ORDER BY recommendations DESC, mentions DESC`,
    ).bind(...competitorBindings).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT keyword, question_type AS questionType, scene, region, COUNT(*) AS sampleCount,
        COALESCE(SUM(mention), 0) AS mentions,
        COALESCE(SUM(CASE WHEN recommendation_prompt = 1 THEN recommend ELSE 0 END), 0) AS recommendations
      FROM geo_evidence_records WHERE ${where}
      GROUP BY keyword, question_type, scene, region ORDER BY sampleCount DESC LIMIT 100`,
    ).bind(...bindings).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT s.id AS snapshotId, s.report_month AS reportMonth, s.version, s.scope_fingerprint AS scopeFingerprint,
        COUNT(e.id) AS answerCount, COALESCE(SUM(e.mention), 0) AS mentions,
        COALESCE(SUM(e.recommendation_prompt), 0) AS recommendationPromptCount,
        COALESCE(SUM(CASE WHEN e.recommendation_prompt = 1 THEN e.recommend ELSE 0 END), 0) AS recommendations,
        COALESCE(SUM(CASE WHEN e.recommendation_prompt = 1 THEN e.top3 ELSE 0 END), 0) AS top3
      FROM geo_snapshots s LEFT JOIN geo_evidence_records e ON e.snapshot_id = s.id AND e.prompt_id <> '' AND e.record_status = 'valid'
      WHERE s.project_id = ? AND s.status = 'published' GROUP BY s.id, s.report_month, s.version, s.scope_fingerprint
      ORDER BY s.report_month ASC, s.version ASC LIMIT 12`,
    ).bind(project.id).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT platform, keyword, scene, region FROM geo_evidence_records
      WHERE project_id = ? AND snapshot_id = ? AND prompt_id <> '' AND record_status = 'valid' LIMIT 2000`,
    ).bind(project.id, selectedSnapshot.id).all<Record<string, unknown>>(),
    env.DB.prepare(
      `SELECT id, status, comment, submitted_by AS submittedBy, submitted_at AS submittedAt
      FROM geo_acceptance_records WHERE snapshot_id = ? ORDER BY submitted_at DESC LIMIT 20`,
    ).bind(selectedSnapshot.id).all<Record<string, unknown>>(),
  ]);

  const brandRecommendations = Number(aggregate?.recommendations ?? 0);
  const completion = (completionResult.results as Record<string, unknown>[]).map((item) => ({ ...item,
    contractQuantity: Number(item.contractQuantity ?? 0), qualifiedQuantity: Number(item.qualifiedQuantity ?? 0),
    weight: Number(item.weight ?? 0) }));
  const deliveryCompletion = completion.length ? Number(completion.reduce((sum, item) => sum
    + Math.min(1, item.qualifiedQuantity / item.contractQuantity) * item.weight, 0).toFixed(1)) : null;
  const competitorRows = competitorResult.results as Record<string, unknown>[];
  const competitorRecommendations = competitorRows.reduce((sum: number, item: Record<string, unknown>) => sum + Number(item.recommendations ?? 0), 0);
  const recommendationTotal = brandRecommendations + competitorRecommendations;
  const sovValues = allocatePercentages([
    brandRecommendations,
    ...competitorRows.map((item) => Number(item.recommendations ?? 0)),
  ]);
  const shareOfVoice = recommendationTotal ? sovValues[0] : null;
  const metrics = calculateMetrics(aggregate ?? {}, shareOfVoice);
  const competitors = [
    { competitorName: String(project.brandName), sampleCount: Number(aggregate?.answerCount ?? 0),
      mentions: Number(aggregate?.mentions ?? 0), recommendations: brandRecommendations,
      averageRank: metrics.averageRank, sov: recommendationTotal ? sovValues[0] : null,
      isTarget: true },
    ...competitorRows.map((item: Record<string, unknown>, index: number) => ({ ...item, sampleCount: Number(item.sampleCount ?? 0),
      mentions: Number(item.mentions ?? 0), recommendations: Number(item.recommendations ?? 0),
      averageRank: item.averageRank === null ? null : Number(Number(item.averageRank).toFixed(2)),
      sov: recommendationTotal ? sovValues[index + 1] : null, isTarget: false })),
  ];
  const selectedFingerprint = String(selectedSnapshot.scopeFingerprint ?? "");
  const trend = (trendResult.results as Record<string, unknown>[]).map((item: Record<string, unknown>) => {
    const total = Number(item.answerCount ?? 0);
    const recommendationTotal = Number(item.recommendationPromptCount ?? 0);
    return { reportMonth: item.reportMonth, version: item.version, answerCount: total,
      mentionRate: rate(Number(item.mentions ?? 0), total), recommendRate: rate(Number(item.recommendations ?? 0), recommendationTotal),
      top3Rate: rate(Number(item.top3 ?? 0), recommendationTotal), comparable: Boolean(selectedFingerprint) && item.scopeFingerprint === selectedFingerprint };
  });
  const filters = (filterResult.results as Record<string, unknown>[]).reduce<Record<string, Set<string>>>((acc, item) => {
    for (const key of ["platform", "keyword", "scene", "region"]) {
      const entry = String(item[key] ?? "").trim();
      if (entry) acc[key].add(entry);
    }
    return acc;
  }, { platform: new Set(), keyword: new Set(), scene: new Set(), region: new Set() });
  return json({
    project: normalizedProject, access, snapshot: { ...selectedSnapshot, summary: snapshotSummary,
      scope: parseJson(selectedSnapshot.scopeJson, {}), gate: parseJson(selectedSnapshot.gateJson, {}) }, snapshots,
    metrics, platforms: (platformResult.results as Record<string, unknown>[]).map((item: Record<string, unknown>) => ({ name: item.platform, ...calculateMetrics(item as Partial<MetricAggregate>, null) })),
    answers: (answersResult.results as Record<string, unknown>[]).map((item: Record<string, unknown>) => ({ ...item, citationUrls: parseJson<string[]>(item.citationUrls, []),
      evidenceUrl: item.evidenceObjectKey ? `/api/ops/files?key=${encodeURIComponent(String(item.evidenceObjectKey))}` : null })),
    answerTotal: Number(aggregate?.answerCount ?? 0), page, pageSize,
    deliveries: (deliveryResult.results as Record<string, unknown>[]).map((item: Record<string, unknown>) => ({ ...item, aiPlatforms: parseJson<string[]>(item.aiPlatforms, []) })),
    completion, deliveryCompletion, competitors, demand: demandResult.results, trend,
    filters: Object.fromEntries(Object.entries(filters).map(([key, values]: [string, Set<string>]) => [key, [...values].sort()])),
    activeFilters: filterValues, acceptanceHistory: acceptanceResult.results,
  });
}

export async function POST(request: Request) {
  const identity = await getChatGPTUser();
  if (!identity) return json({ error: "请登录后提交验收" }, 401);
  const body = await request.json() as Record<string, unknown>;
  if (body.action !== "submit-acceptance") return json({ error: "不支持的操作" }, 400);
  const snapshotId = String(body.snapshotId ?? "");
  const status = String(body.status ?? "");
  if (!["accepted", "disputed"].includes(status)) return json({ error: "验收状态无效" }, 400);
  const snapshot = await env.DB.prepare(
    `SELECT s.id, s.project_id AS projectId, s.status, p.slug FROM geo_snapshots s
    JOIN geo_projects p ON p.id = s.project_id WHERE s.id = ?`,
  ).bind(snapshotId).first<{ id: string; projectId: string; status: string; slug: string }>();
  if (!snapshot || snapshot.status !== "published") return json({ error: "只能验收已发布快照" }, 409);
  const access = await resolveAccess(snapshot.projectId, identity.email);
  if (!access.allowed) return json({ error: "无权验收该项目" }, 403);
  if (!["owner", "reviewer", "approver"].includes(access.memberRole)) return json({ error: "当前账号只有查看权限，不能提交验收结论" }, 403);
  const comment = String(body.comment ?? "").trim();
  if (status === "disputed" && !comment) return json({ error: "提出异议时必须填写具体问题" }, 400);
  const timestamp = new Date().toISOString();
  await env.DB.batch([
    env.DB.prepare("INSERT INTO geo_acceptance_records (id, project_id, snapshot_id, status, comment, submitted_by, submitted_at) VALUES (?, ?, ?, ?, ?, ?, ?)")
      .bind(`acc_${crypto.randomUUID().replaceAll("-", "")}`, snapshot.projectId, snapshotId, status, comment, identity.email, timestamp),
    env.DB.prepare("UPDATE geo_snapshots SET acceptance_status = ?, accepted_at = ?, accepted_by = ?, updated_at = ? WHERE id = ?")
      .bind(status, status === "accepted" ? timestamp : null, status === "accepted" ? identity.email : null, timestamp, snapshotId),
    env.DB.prepare("INSERT INTO geo_audit_logs (id, project_id, actor_email, action, target_type, target_id, detail_json, created_at) VALUES (?, ?, ?, ?, 'snapshot', ?, ?, ?)")
      .bind(`aud_${crypto.randomUUID().replaceAll("-", "")}`, snapshot.projectId, identity.email,
        status === "accepted" ? "acceptance.accepted" : "acceptance.disputed", snapshotId, JSON.stringify({ comment }), timestamp),
  ]);
  return json({ ok: true, message: status === "accepted" ? "本期报告已确认验收" : "验收异议已提交，乙方将据此复核" });
}
