import { createHash } from "node:crypto";
import { readFile } from "node:fs/promises";
import path from "node:path";
import process from "node:process";
import { setTimeout as delay } from "node:timers/promises";
import { fileURLToPath } from "node:url";
import { createClient, type SupabaseClient } from "@supabase/supabase-js";

import {
  COMBINED_STANDARDIZATION_SYSTEM_PROMPT,
  DOCUMENT_EXTRACTION_SYSTEM_PROMPT,
  KEYWORD_SEMANTIC_SYSTEM_PROMPT,
} from "./prompt-seed-content.ts";

type ContentFormat = "text" | "text_template" | "chat_bundle" | "multimodal_bundle";

type PromptSeed = {
  promptKey: string;
  module: string;
  scene: string;
  contentFormat: ContentFormat;
  description: string;
  payload: Record<string, unknown>;
  variables: Array<Record<string, unknown>>;
  notes: string;
  version: number;
};

type WriteMode = "auto" | "management-api" | "service-role";

type CliOptions = {
  accessToken: string;
  projectRef: string;
  targetEnv: string;
  providerScope: string;
  modelScope: string;
  applySchema: boolean;
  promptStoreUrl: string;
  promptStoreServiceRoleKey: string;
  writeMode: WriteMode;
};

const SCRIPT_DIR = path.dirname(fileURLToPath(import.meta.url));
const REPO_ROOT = path.resolve(SCRIPT_DIR, "..");
const SCHEMA_PATH = path.join(REPO_ROOT, "supabase/sql/prompt-store-schema.sql");

const MANAGEMENT_API_BASE = "https://api.supabase.com/v1";
const MANAGEMENT_API_MAX_ATTEMPTS = 4;
const MANAGEMENT_API_RETRIABLE_STATUS_CODES = new Set([429, 502, 503, 504]);
const CREATED_BY = "scripts/seed-prompt-store.ts";

function joinLines(lines: string[]): string {
  return lines.join("\n");
}

function createChecksum(payload: Record<string, unknown>): string {
  return createHash("sha256").update(JSON.stringify(payload)).digest("hex");
}

function requireOption(value: string | undefined, name: string): string {
  if (!value || !value.trim()) {
    throw new Error(`缺少必填参数: ${name}`);
  }

  return value.trim();
}

function parseProjectRefFromUrl(rawUrl: string): string {
  const hostname = new URL(rawUrl).hostname;
  const [projectRef] = hostname.split(".");

  if (!projectRef) {
    throw new Error(`无法从 SUPABASE_URL 解析 project ref: ${rawUrl}`);
  }

  return projectRef;
}

function parseWriteMode(rawValue: string | undefined, patOnlyRequested: boolean): WriteMode {
  if (patOnlyRequested) {
    return "management-api";
  }

  const normalized = String(rawValue ?? "").trim().toLowerCase();
  if (!normalized) {
    return "management-api";
  }

  if (normalized === "auto") {
    return "auto";
  }

  if (normalized === "management-api" || normalized === "pat" || normalized === "pat-only") {
    return "management-api";
  }

  if (normalized === "service-role" || normalized === "service_role") {
    return "service-role";
  }

  throw new Error(`不支持的写入模式: ${rawValue}`);
}

function parseArgs(argv: string[]): CliOptions {
  const values = new Map<string, string>();
  let applySchema = true;
  let patOnlyRequested = false;

  for (const arg of argv) {
    if (!arg.startsWith("--")) {
      continue;
    }

    if (arg === "--skip-schema") {
      applySchema = false;
      continue;
    }

    if (arg === "--pat-only") {
      patOnlyRequested = true;
      continue;
    }

    const [flag, rawValue] = arg.slice(2).split("=", 2);
    values.set(flag, rawValue ?? "");
  }

  const accessToken = requireOption(
    values.get("access-token") || process.env.SUPABASE_ACCESS_TOKEN,
    "SUPABASE_ACCESS_TOKEN 或 --access-token",
  );

  const rawProjectRef = values.get("project-ref") || process.env.PROMPT_SUPABASE_PROJECT_REF || "";
  const rawSupabaseUrl = values.get("supabase-url") || process.env.SUPABASE_URL || process.env.PROMPT_SUPABASE_URL || "";
  const projectRef = rawProjectRef.trim() || (rawSupabaseUrl.trim() ? parseProjectRefFromUrl(rawSupabaseUrl.trim()) : "");
  const promptStoreUrl = values.get("prompt-store-url") || process.env.PROMPT_SUPABASE_URL || rawSupabaseUrl || "";
  const promptStoreServiceRoleKey = values.get("prompt-store-service-role-key")
    || process.env.PROMPT_SUPABASE_SERVICE_ROLE_KEY
    || process.env.PROMPT_SUPABASE_service_key
    || "";
  const writeMode = parseWriteMode(
    values.get("write-mode") || process.env.PROMPT_STORE_WRITE_MODE,
    patOnlyRequested,
  );

  return {
    accessToken,
    projectRef: requireOption(projectRef, "--project-ref 或 SUPABASE_URL/PROMPT_SUPABASE_URL"),
    targetEnv: values.get("target-env") || process.env.PROMPT_ENV || "prod",
    providerScope: values.get("provider-scope") || "*",
    modelScope: values.get("model-scope") || "*",
    applySchema,
    promptStoreUrl: promptStoreUrl.trim(),
    promptStoreServiceRoleKey: promptStoreServiceRoleKey.trim(),
    writeMode,
  };
}

function createPromptStoreClient(options: CliOptions): SupabaseClient | null {
  if (options.writeMode === "management-api") {
    return null;
  }

  if (!options.promptStoreUrl || !options.promptStoreServiceRoleKey) {
    if (options.writeMode === "service-role") {
      throw new Error("当前已强制 service_role 写入，但缺少 PROMPT_SUPABASE_URL 或 PROMPT_SUPABASE_SERVICE_ROLE_KEY");
    }

    return null;
  }

  return createClient(options.promptStoreUrl, options.promptStoreServiceRoleKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  });
}

function describeWriteChannel(options: CliOptions, promptStoreClient: SupabaseClient | null): string {
  if (promptStoreClient) {
    return options.writeMode === "service-role" ? "service_role (forced)" : "service_role";
  }

  return options.writeMode === "management-api" ? "management_api (PAT-only)" : "management_api";
}

function formatManagementApiError(status: number, rawText: string): string {
  if (status === 403 && rawText.includes("error code: 1010")) {
    return [
      "Supabase 管理 API 被 Cloudflare 1010 拦截。",
      "这一步尚未进入 Supabase 项目权限校验，通常不是 SQL 或 project_ref 写错。",
      "请在可直连 https://api.supabase.com/v1 的网络或本机环境中重试 PAT-only 写入。",
    ].join(" ");
  }

  return `Supabase 管理 API 失败 (${status}): ${rawText}`;
}

function getManagementApiRetryDelayMs(attempt: number): number {
  return attempt * 800;
}

async function runManagementQuery<T = unknown>(
  projectRef: string,
  accessToken: string,
  query: string,
  parameters: unknown[] = [],
): Promise<T> {
  for (let attempt = 1; attempt <= MANAGEMENT_API_MAX_ATTEMPTS; attempt += 1) {
    const response = await fetch(`${MANAGEMENT_API_BASE}/projects/${projectRef}/database/query`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${accessToken}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ query, parameters }),
    });

    const rawText = await response.text();
    if (response.ok) {
      if (!rawText.trim()) {
        return [] as T;
      }

      return JSON.parse(rawText) as T;
    }

    const isRetriable = MANAGEMENT_API_RETRIABLE_STATUS_CODES.has(response.status);
    if (!isRetriable || attempt >= MANAGEMENT_API_MAX_ATTEMPTS) {
      throw new Error(formatManagementApiError(response.status, rawText));
    }

    const delayMs = getManagementApiRetryDelayMs(attempt);
    console.warn(
      `[prompt-store] 管理 API 暂时不可用 (${response.status})，${delayMs}ms 后重试 (${attempt}/${MANAGEMENT_API_MAX_ATTEMPTS - 1})`,
    );
    await delay(delayMs);
  }

  throw new Error("Supabase 管理 API 重试后仍未返回结果");
}

function normalizeQueryRows(value: unknown): Array<Record<string, unknown>> {
  if (Array.isArray(value)) {
    return value as Array<Record<string, unknown>>;
  }

  if (value && typeof value === "object") {
    const candidate = value as { result?: unknown; data?: unknown };
    if (Array.isArray(candidate.result)) {
      return candidate.result as Array<Record<string, unknown>>;
    }
    if (Array.isArray(candidate.data)) {
      return candidate.data as Array<Record<string, unknown>>;
    }
  }

  return [];
}

function buildChatbotRuntimeConfigBlockTemplate(): string {
  return joinLines([
    "当前运行时漏斗配置：",
    "- 本轮名称：{{round_name}}",
    "- 本轮发送话术：{{template_text}}",
    "- 本轮通过条件：{{pass_condition}}",
  ]);
}

function buildChatbotRoundTemplate(basePrompt: string): string {
  return `${basePrompt}\n\n${buildChatbotRuntimeConfigBlockTemplate()}`;
}

async function buildPromptSeeds(): Promise<PromptSeed[]> {
  const semanticTitleValidationUserTemplate = joinLines([
    "你在做 1688 搜索结果语义校验。",
    "当前搜索词: {{primary_keyword}}",
    "搜索词中的关键词片段: {{keyword_tokens}}",
    "请根据以下商品标题，判断搜索词的各个关键词片段中，哪些出现在商品标题里（被市场认可），哪些几乎不出现（可能是客户内部叫法、地名、型号缩写等干扰项）。",
    "目标：找出导致搜索结果混乱、偏离目标商品的干扰词片段，建议停止后续包含这些干扰词的 progressive 搜索轮次。",
    "只有当某词或子词在所有标题中均未出现，而其他词有持续命中时，才判定为干扰项。",
    "标题样本: {{titles_inline}}",
    "只返回 JSON，不要解释。格式：",
    '{"found_terms":["出现在标题中的词"],"interference_terms":["未出现在标题中的干扰词"],"noise_terms_detected":true,"should_stop_progressive":true,"reason":"..."}',
  ]);

  const queryRegenerationUserTemplate = joinLines([
    "你在为 1688 搜索失败后的商品重新生成 3 个市场化搜索词。",
    "当前失败搜索词: {{primary_keyword}}",
    "要求：",
    "1. 只返回 3 个新词。",
    "2. 不能重复之前已经试过的词。",
    "3. 不能继续使用已判定为干扰项的词，例如 风亭。",
    "4. 要更偏向真实市场商品叫法、材质、形态叫法。",
    "最近失败历史:\n{{history_text}}",
    "只返回 JSON，不要解释。格式：",
    '{"blocked_terms":["风亭"],"regenerated_keywords":["不锈钢玻璃护栏","玻璃栏杆","不锈钢立柱玻璃围栏"],"reason":"..."}',
  ]);

  const keywordHitRateFallbackUserTemplate = joinLines([
    "你在做 1688 搜索结果命中率复核。",
    "目标搜索词: {{keyword}}",
    "抽样商品数: {{sample_count}}",
    "请基于每条商品标题、卡片全文和高亮词，判断有多少样本与目标搜索词语义明确匹配。",
    "要求：",
    "1. 只有商品本体、材质、规格、用途整体都明显指向目标搜索词时，才算命中。",
    "2. 只看给出的样本，不要臆测页面外内容。",
    "3. 如果信息不足或明显无关，按未命中处理。",
    "4. 请给出 0 到 1 之间的 estimated_hit_rate。",
    "只返回 JSON，不要解释。格式：",
    '{"estimated_hit_count":8,"sample_count":12,"estimated_hit_rate":0.6667,"confidence":0.84,"reasoning_summary":"..."}',
    "",
    "{{sample_blocks}}",
  ]);

  const pageReviewUserTemplate = joinLines([
    "你在判断 1688 搜索结果第一页是否已经形成有效搜索。",
    "主搜索词: {{primary_keyword}}",
    "备选词: {{alt_keywords}}",
    "当前参与判定的已加载商品数: {{result_count}}",
    "样本标题: {{sample_titles}}",
    "判断标准: 要以整页已加载商品为准，不是只看前10条。只有当页面里大部分商品在名称、材料、用途上都与主搜索词高度一致，且商品图与标题整体指向同一类商品时，才能判定为有效搜索。",
    "如果你认为有效，请在 confidence >= {{threshold}} 时返回 true。",
    "只返回 JSON，不要解释。格式：",
    '{"is_valid_search":true,"confidence":0.91,"reason":"..."}',
  ]);

  const candidateFilterUserTemplate = joinLines([
    "根据以下采购需求，从候选商品列表中筛选出符合要求的商品。",
    "最多选 10 个，没有符合的可以少于 10 个，不符合的不要选。",
    "",
    "【采购需求分析】",
    "- 商品类型: {{product_type}}",
    "- 结构摘要: {{structure_summary}}",
    "- 组件角色: {{component_roles}}",
    "- 推荐搜索词: {{suggested_keyword}}",
    "",
    "【筛选标准】",
    "- 必须是完整的整体系统，不能是单一零件、配件或附属件",
    "- 商品在结构上必须与「结构摘要」描述一致",
    "- 商品类型必须与「商品类型」字段一致",
    "",
    "【候选商品】",
    "{{candidate_titles_text}}",
    "",
    '只返回 JSON: {"selected": [1, 3, 5]}',
  ]);

  const bestImageSelectionIntroTemplate = joinLines([
    "请从下面候选商品里选出 1 个最适合作为后续图搜参考图的商品。",
    "请先参考 ai_keyword_analysis，对候选进行第一轮过滤：优先保留与商品本体、结构摘要、组件角色一致的候选，淘汰明显像局部件、零配件、附属件、错误类目的候选。",
    "然后在剩余候选之间进行第二轮比较：优先选择更像完整商品或完整组合体、结构更完整、与推荐搜索词和标题语义更一致、且更适合作为后续图搜参考图的候选。",
    "不要使用任何固定行业示例或预设具体商品名称；判断只能基于 ai_keyword_analysis 和候选本身。",
    '只返回 JSON：{"selected_index":1,"reason":"..."}',
  ]);

  const bestImageSelectionCandidateTemplate = joinLines([
    "候选{{candidate_index}}: 标题={{title}}",
    "轮次={{search_round}}",
    "关键词={{primary_keyword}}",
    "有效搜索={{is_valid_search}}",
    "视觉置信度={{visual_confidence}}",
    "ai_keyword_analysis={{ai_keyword_analysis_summary}}",
  ]);

  const keywordFusionStep1Template = joinLines([
    "【步骤1：解析商品图片（结构确认）】",
    "请先观察两张输入图片，判断商品是完整商品、组合体，还是局部组件。",
    "请明确识别图片中的结构特征，并判断是否为多个组件组合体。",
    "请尽量指出图片里可见的主要组成部分，但不要使用任何固定行业示例来套结论。",
  ]);

  const keywordFusionStep2Template = joinLines([
    "【步骤2：解析相似标题】",
    "标题高频材质词: {{material_terms_summary}}",
    "标题高频产品词: {{whole_product_terms_summary}}",
    "标题高频组件词: {{component_terms_summary}}",
    "标题高频结构词: {{structure_terms_summary}}",
    "请把更像完整商品名称的词归入产品主体候选，把更像局部部件或附件的词归入组件词，不得把组件词直接当作唯一主体。",
    "10条相似标题：",
    "{{titles_numbered}}",
  ]);

  const keywordFusionStep3Template = joinLines([
    "【步骤3：融合判断（必须遵守优先级）】",
    "ai_keyword_analysis.suggested_keyword: {{ai_suggested_keyword}}",
    "ai_keyword_analysis.product_type: {{ai_product_type}}",
    "ai_keyword_analysis.structure_summary: {{ai_structure_summary}}",
    "ai_keyword_analysis.component_roles: {{ai_component_roles}}",
    "你必须严格按 ai_keyword_analysis > 图片 > 标题 的顺序做决策。",
    "如果 ai_keyword_analysis 与图片都说明这是完整商品或组合体，主关键词不能退化成单独配件词。",
    "标题只能用于验证市场叫法，不能推翻 ai_keyword_analysis 与图片已经确认的结构。",
  ]);

  const chatbotRound1BasePrompt = joinLines([
    "你是一个采购助手 AI，负责分析 1688 商家客服的回复。",
    '当前是第一轮询问：我们问了"有没有现货"。',
    "请根据客服回复判断：该商家是否有现货。",
    "",
    "输出严格的 JSON 格式（不要输出其他内容）：",
    "{",
    '  "passed": true/false,',
    '  "reason": "简要说明判断依据"',
    "}",
    "",
    "判断规则：",
    "- 明确表示有货、有现货、可以发货 → passed: true",
    "- 明确表示无货、停产、下架、缺货、暂时没有 → passed: false",
    '- 模糊、不相关、问其他问题 → passed: false, reason 说明"回复不相关"',
  ]);

  const chatbotRound2BasePrompt = joinLines([
    "你是一个采购助手 AI，负责分析 1688 商家客服的回复。",
    '当前是第二轮询问：我们问了"是否支持指定规格和起订量"。',
    "请根据客服回复判断：该商家是否支持我们要求的规格和起订量。",
    "",
    "输出严格的 JSON 格式（不要输出其他内容）：",
    "{",
    '  "passed": true/false,',
    '  "reason": "简要说明判断依据"',
    "}",
    "",
    "判断规则：",
    "- 明确表示可以做、支持、没问题 → passed: true",
    "- 明确表示不做、量太少、无法定制 → passed: false",
    '- 模糊、需进一步确认但态度积极 → passed: true, reason 注明"态度积极但未完全确认"',
  ]);

  const chatbotRound3BasePrompt = joinLines([
    "你是一个采购助手 AI，负责分析 1688 商家客服的回复。",
    '当前是第三轮询问：我们问了"最低价格和交期"。',
    "请从客服回复中提取价格和交期信息。",
    "",
    "输出严格的 JSON 格式（不要输出其他内容）：",
    "{",
    '  "passed": true/false,',
    '  "status": "PASSED" | "PENDING",',
    '  "price": 数字或null,',
    '  "price_unit": "元/件" 或其他单位或null,',
    '  "delivery_days": 数字或null,',
    '  "reason": "简要说明"',
    "}",
    "",
    "判断规则：",
    '- 给出了明确价格（哪怕没给交期）→ passed: true, status: "PASSED"',
    ' - 说需要核算、让等一下、稍后报价 → passed: false, status: "PENDING"（含糊其辞，需人工跟进）'.trim(),
    ' - 完全不回答价格问题 → passed: false, status: "PENDING"'.trim(),
    ' - 价格字段请提取数字部分（如"10.5元/件"→ price: 10.5, price_unit: "元/件"）'.trim(),
    ' - 交期请提取天数（如"3-5个工作日"→ delivery_days: 5，取较大值）'.trim(),
  ]);

  const chatbotRound4BasePrompt = joinLines([
    "你是一个采购助手 AI，负责分析 1688 商家客服的回复。",
    '当前是第四轮询问：我们问了"是否符合国标、能否开增值税专用发票"。',
    "请判断合规性。",
    "",
    "输出严格的 JSON 格式（不要输出其他内容）：",
    "{",
    '  "passed": true/false,',
    '  "is_guobiao": true/false/null,',
    '  "can_invoice": true/false/null,',
    '  "reason": "简要说明"',
    "}",
    "",
    "判断规则：",
    "- 明确表示符合国标且可开专票 → passed: true",
    "- 明确表示不符合国标或不能开票 → passed: false",
    ' - 只确认了一项 → passed: true（部分确认，reason 注明哪项未确认）'.trim(),
    "- 完全回避问题 → passed: false",
  ]);

  const chatbotGenericRoundTemplate = joinLines([
    "你是一个采购助手 AI，负责分析 1688 商家客服的回复。",
    "当前是第 {{current_round}} 轮询问，这是一个自定义漏斗轮次。",
    "本轮名称：{{round_name}}",
    "本轮发送话术：{{template_text}}",
    "本轮通过条件：{{pass_condition}}",
    "",
    "请根据客服回复判断：该商家是否满足本轮推进条件。",
    "",
    "输出严格的 JSON 格式（不要输出其他内容）：",
    "{",
    '  "passed": true/false,',
    '  "status": "PASSED" | "REJECTED" | "PENDING" | "UNCLEAR",',
    '  "reason": "简要说明判断依据"',
    "}",
    "",
    "判断规则：",
    '- 明确满足本轮通过条件，或明确正面确认本轮诉求 → passed: true, status: "PASSED"',
    '- 明确拒绝、明确不满足、明确无法配合 → passed: false, status: "REJECTED"',
    '- 回复表示需要进一步确认、稍后答复、信息暂时不足 → passed: false, status: "PENDING"',
    '- 回复无关、语义混乱、无法判断 → passed: false, status: "UNCLEAR"',
  ]);

  const seeds: PromptSeed[] = [
    {
      promptKey: "automation.standardization.combined.system",
      module: "automation.standardization",
      scene: "combined_standardization",
      contentFormat: "text",
      description: "标准化与 1688 可搜索性联合判断 system prompt",
      payload: { content: COMBINED_STANDARDIZATION_SYSTEM_PROMPT },
      variables: [],
      notes: "source=scripts/prompt-seed-content.ts",
      version: 3,
    },
    {
      promptKey: "automation.standardization.keyword_semantic.system",
      module: "automation.standardization",
      scene: "keyword_semantic",
      contentFormat: "text",
      description: "关键词语义分析 system prompt",
      payload: { content: KEYWORD_SEMANTIC_SYSTEM_PROMPT },
      variables: [],
      notes: "source=scripts/prompt-seed-content.ts",
      version: 2,
    },
    {
      promptKey: "automation.document_extraction.materials.system",
      module: "automation.document_extraction",
      scene: "extract_materials",
      contentFormat: "text",
      description: "文档提取采购物料 system prompt",
      payload: { content: DOCUMENT_EXTRACTION_SYSTEM_PROMPT },
      variables: [],
      notes: "source=scripts/prompt-seed-content.ts",
      version: 2,
    },
    {
      promptKey: "automation.search.keyword_fusion.bundle",
      module: "automation.search",
      scene: "keyword_fusion",
      contentFormat: "multimodal_bundle",
      description: "图片、标题与 ai_keyword_analysis 融合判断",
      payload: {
        system: joinLines([
          "你是一个电商商品识别与命名专家。",
          "你必须严格遵守优先级：1.ai_keyword_analysis 2.图片 3.标题。",
          "标题只能用于验证市场叫法，不能主导结构判断。",
          "你必须输出 JSON 对象，字段只能是：main_keyword、image_basis、title_analysis、decision_logic。",
          "main_keyword 必须优先保留完整、自然、可搜索的市场叫法，尽量同时包含材质、核心组件和产品本体，不要为了缩短而主动删减关键信息。",
        ]),
        user_template: joinLines([
          "请根据以下步骤完成判断，并仅输出要求的 JSON：",
          "{{step1_image_analysis_prompt}}",
          "",
          "{{step2_title_analysis_prompt}}",
          "",
          "{{step3_fusion_prompt}}",
        ]),
        step_templates: {
          step1_image_analysis_prompt: keywordFusionStep1Template,
          step2_title_analysis_prompt: keywordFusionStep2Template,
          step3_fusion_prompt: keywordFusionStep3Template,
        },
        image_slots: ["source_image", "search_result_screenshot"],
      },
      variables: [
        { name: "material_terms_summary", required: true, description: "标题高频材质词汇总，如 不锈钢(6)，玻璃(4)" },
        { name: "whole_product_terms_summary", required: true, description: "标题高频产品主体词汇总" },
        { name: "component_terms_summary", required: true, description: "标题高频组件词汇总" },
        { name: "structure_terms_summary", required: true, description: "标题高频结构词汇总" },
        { name: "titles_numbered", required: true, description: "10 条相似标题的编号文本" },
        { name: "ai_suggested_keyword", required: true, description: "ai_keyword_analysis.suggested_keyword" },
        { name: "ai_product_type", required: true, description: "ai_keyword_analysis.product_type" },
        { name: "ai_structure_summary", required: true, description: "ai_keyword_analysis.structure_summary" },
        { name: "ai_component_roles", required: true, description: "ai_keyword_analysis.component_roles 的可读文本" },
      ],
      notes: "source=automation/keywords/keyword-selection.ts",
      version: 1,
    },
    {
      promptKey: "automation.search.semantic_title_validation.bundle",
      module: "automation.search",
      scene: "semantic_title_validation",
      contentFormat: "chat_bundle",
      description: "标题语义校验，识别干扰项",
      payload: {
        system: "你是商品标题语义校验器，只返回 JSON。",
        user_template: semanticTitleValidationUserTemplate,
      },
      variables: [
        { name: "primary_keyword", required: true, description: "当前搜索词" },
        { name: "keyword_tokens", required: true, description: "搜索词片段，使用 顿号 连接" },
        { name: "titles_inline", required: true, description: "编号后的标题样本，使用竖线分隔" },
      ],
      notes: "source=automation/playwright/search-plan.js:evaluateTopTitleSemantics",
      version: 1,
    },
    {
      promptKey: "automation.search.keyword_regeneration.bundle",
      module: "automation.search",
      scene: "keyword_regeneration",
      contentFormat: "chat_bundle",
      description: "搜索失败后重写关键词",
      payload: {
        system: "你是 1688 搜索词重写器，只返回 JSON。",
        user_template: queryRegenerationUserTemplate,
      },
      variables: [
        { name: "primary_keyword", required: true, description: "当前失败搜索词" },
        { name: "history_text", required: true, description: "最近失败历史摘要，按轮次换行" },
      ],
      notes: "source=automation/playwright/search-plan.js:regenerateKeywords",
      version: 1,
    },
    {
      promptKey: "automation.search.hit_rate_fallback.bundle",
      module: "automation.search",
      scene: "hit_rate_fallback",
      contentFormat: "chat_bundle",
      description: "搜索命中率语义复核",
      payload: {
        system: "你是 1688 搜索结果命中率审核器，只返回 JSON。",
        user_template: keywordHitRateFallbackUserTemplate,
      },
      variables: [
        { name: "keyword", required: true, description: "目标搜索词" },
        { name: "sample_count", required: true, description: "抽样商品数量" },
        { name: "sample_blocks", required: true, description: "样本文本块，逐条包含标题、卡片全文和高亮词" },
      ],
      notes: "source=automation/playwright/search-hit-rate-fallback.js + automation/playwright/search-plan.js:estimateKeywordHitRateWithLlm",
      version: 1,
    },
    {
      promptKey: "automation.search.page_review.bundle",
      module: "automation.search",
      scene: "page_review",
      contentFormat: "multimodal_bundle",
      description: "搜索结果页视觉有效性审查",
      payload: {
        system: "你是 1688 搜索结果审核器，只返回 JSON。",
        user_template: pageReviewUserTemplate,
        image_slots: ["search_result_screenshot"],
      },
      variables: [
        { name: "primary_keyword", required: true, description: "主搜索词" },
        { name: "alt_keywords", required: true, description: "备选搜索词，使用竖线分隔；无则写 无" },
        { name: "result_count", required: true, description: "当前已加载商品数" },
        { name: "sample_titles", required: true, description: "前 20 个标题样本，使用竖线分隔" },
        { name: "threshold", required: true, description: "有效搜索置信度阈值，如 0.72" },
      ],
      notes: "source=automation/playwright/search-plan.js:evaluateSearchPageWithVision",
      version: 1,
    },
    {
      promptKey: "automation.search.candidate_filter.bundle",
      module: "automation.search",
      scene: "candidate_filter",
      contentFormat: "chat_bundle",
      description: "候选商品与 ai_keyword_analysis 对齐过滤",
      payload: {
        system: "你是采购商品匹配专家，只返回 JSON。",
        user_template: candidateFilterUserTemplate,
      },
      variables: [
        { name: "product_type", required: true, description: "ai_keyword_analysis.product_type" },
        { name: "structure_summary", required: true, description: "ai_keyword_analysis.structure_summary" },
        { name: "component_roles", required: true, description: "ai_keyword_analysis.component_roles 的文本或 JSON 字符串" },
        { name: "suggested_keyword", required: true, description: "ai_keyword_analysis.suggested_keyword" },
        { name: "candidate_titles_text", required: true, description: "按序号拼接的候选商品标题列表" },
      ],
      notes: "source=automation/playwright/search-plan.js:filterCandidatesByAiRelevance",
      version: 1,
    },
    {
      promptKey: "automation.search.best_image_selection.bundle",
      module: "automation.search",
      scene: "best_image_selection",
      contentFormat: "multimodal_bundle",
      description: "候选图中挑选最佳图搜参考图",
      payload: {
        system: "你是商品参考图筛选器，只返回 JSON。",
        intro_template: bestImageSelectionIntroTemplate,
        candidate_text_template: bestImageSelectionCandidateTemplate,
        image_slots: ["candidate_images"],
      },
      variables: [
        { name: "candidate_index", required: true, description: "候选序号，从 1 开始" },
        { name: "title", required: true, description: "候选标题" },
        { name: "search_round", required: true, description: "来源轮次" },
        { name: "primary_keyword", required: true, description: "该候选对应的关键词" },
        { name: "is_valid_search", required: true, description: "是否来自有效搜索" },
        { name: "visual_confidence", required: true, description: "视觉置信度，保留两位小数" },
        { name: "ai_keyword_analysis_summary", required: true, description: "候选携带的 ai_keyword_analysis 摘要，无则写 无" },
      ],
      notes: "source=automation/playwright/search-plan.js:selectBestImageCandidateWithAi",
      version: 1,
    },
    {
      promptKey: "chatbot.intent.round_1.system",
      module: "chatbot.intent",
      scene: "round_1",
      contentFormat: "text_template",
      description: "第 1 轮现货判断 system prompt",
      payload: { template: buildChatbotRoundTemplate(chatbotRound1BasePrompt) },
      variables: [
        { name: "round_name", required: true, description: "当前轮次名称" },
        { name: "template_text", required: true, description: "当前轮次发送话术" },
        { name: "pass_condition", required: false, description: "当前轮次通过条件原文；无则留空" },
      ],
      notes: "source=chatbot/src/llm-intent.js:BASE_SYSTEM_PROMPTS[1] + buildSystemPrompt",
      version: 1,
    },
    {
      promptKey: "chatbot.intent.round_2.system",
      module: "chatbot.intent",
      scene: "round_2",
      contentFormat: "text_template",
      description: "第 2 轮规格与起订量判断 system prompt",
      payload: { template: buildChatbotRoundTemplate(chatbotRound2BasePrompt) },
      variables: [
        { name: "round_name", required: true, description: "当前轮次名称" },
        { name: "template_text", required: true, description: "当前轮次发送话术" },
        { name: "pass_condition", required: false, description: "当前轮次通过条件原文；无则留空" },
      ],
      notes: "source=chatbot/src/llm-intent.js:BASE_SYSTEM_PROMPTS[2] + buildSystemPrompt",
      version: 1,
    },
    {
      promptKey: "chatbot.intent.round_3.system",
      module: "chatbot.intent",
      scene: "round_3",
      contentFormat: "text_template",
      description: "第 3 轮价格与交期提取 system prompt",
      payload: { template: buildChatbotRoundTemplate(chatbotRound3BasePrompt) },
      variables: [
        { name: "round_name", required: true, description: "当前轮次名称" },
        { name: "template_text", required: true, description: "当前轮次发送话术" },
        { name: "pass_condition", required: false, description: "当前轮次通过条件原文；无则留空" },
      ],
      notes: "source=chatbot/src/llm-intent.js:BASE_SYSTEM_PROMPTS[3] + buildSystemPrompt",
      version: 1,
    },
    {
      promptKey: "chatbot.intent.round_4.system",
      module: "chatbot.intent",
      scene: "round_4",
      contentFormat: "text_template",
      description: "第 4 轮国标与专票判断 system prompt",
      payload: { template: buildChatbotRoundTemplate(chatbotRound4BasePrompt) },
      variables: [
        { name: "round_name", required: true, description: "当前轮次名称" },
        { name: "template_text", required: true, description: "当前轮次发送话术" },
        { name: "pass_condition", required: false, description: "当前轮次通过条件原文；无则留空" },
      ],
      notes: "source=chatbot/src/llm-intent.js:BASE_SYSTEM_PROMPTS[4] + buildSystemPrompt",
      version: 1,
    },
    {
      promptKey: "chatbot.intent.generic_round.system",
      module: "chatbot.intent",
      scene: "generic_round",
      contentFormat: "text_template",
      description: "自定义漏斗轮次通用 system prompt",
      payload: { template: chatbotGenericRoundTemplate },
      variables: [
        { name: "current_round", required: true, description: "当前轮次编号" },
        { name: "round_name", required: true, description: "当前轮次名称" },
        { name: "template_text", required: true, description: "当前轮次发送话术" },
        { name: "pass_condition", required: false, description: "当前轮次通过条件原文；无则留空" },
      ],
      notes: "source=chatbot/src/llm-intent.js:buildGenericRoundSystemPrompt",
      version: 1,
    },
  ];

  return seeds;
}

async function applySchema(projectRef: string, accessToken: string): Promise<void> {
  const schemaSql = await readFile(SCHEMA_PATH, "utf8");
  await runManagementQuery(projectRef, accessToken, schemaSql);
}

async function upsertPromptSeed(
  projectRef: string,
  accessToken: string,
  seed: PromptSeed,
  targetEnv: string,
  providerScope: string,
  modelScope: string,
  promptStoreClient: SupabaseClient | null,
): Promise<void> {
  const payloadJson = seed.payload;
  const variablesJson = seed.variables;
  const checksum = createChecksum(payloadJson);

  if (promptStoreClient) {
    const { error } = await promptStoreClient.rpc("prompt_store_upsert_active_version", {
      p_prompt_key: seed.promptKey,
      p_module: seed.module,
      p_scene: seed.scene,
      p_content_format: seed.contentFormat,
      p_description: seed.description,
      p_target_env: targetEnv,
      p_provider_scope: providerScope,
      p_model_scope: modelScope,
      p_version: seed.version,
      p_payload_json: payloadJson,
      p_variables_json: seed.variables,
      p_notes: seed.notes,
      p_checksum: checksum,
      p_created_by: CREATED_BY,
    });

    if (error) {
      throw new Error(`service_role upsert 失败 (${seed.promptKey}): ${error.message}`);
    }

    return;
  }

  await runManagementQuery(
    projectRef,
    accessToken,
    `select public.prompt_store_upsert_active_version(
      $1::text,
      $2::text,
      $3::text,
      $4::text,
      $5::text,
      $6::text,
      $7::text,
      $8::text,
      $9::integer,
      $10::jsonb,
      $11::jsonb,
      $12::text,
      $13::text,
      $14::text
    ) as version_id`,
    [
      seed.promptKey,
      seed.module,
      seed.scene,
      seed.contentFormat,
      seed.description,
      targetEnv,
      providerScope,
      modelScope,
      seed.version,
      JSON.stringify(payloadJson),
      JSON.stringify(variablesJson),
      seed.notes,
      checksum,
      CREATED_BY,
    ],
  );
}

async function verifyPromptSeeds(
  projectRef: string,
  accessToken: string,
  seeds: PromptSeed[],
  targetEnv: string,
  promptStoreClient: SupabaseClient | null,
): Promise<void> {
  const activeRows = promptStoreClient
    ? await (async () => {
      const { data, error } = await promptStoreClient
        .from("prompt_active_versions")
        .select("prompt_key, version, content_format")
        .order("prompt_key");

      if (error) {
        throw new Error(`service_role 读取 active prompts 失败: ${error.message}`);
      }

      return normalizeQueryRows(data ?? []);
    })()
    : normalizeQueryRows(await runManagementQuery(
      projectRef,
      accessToken,
      "select prompt_key, version, content_format from public.prompt_active_versions order by prompt_key",
    ));

  if (activeRows.length < seeds.length) {
    throw new Error(`active prompt 数量不足，期望至少 ${seeds.length} 条，实际 ${activeRows.length} 条`);
  }

  const activeKeys = new Set(activeRows.map((row) => String(row.prompt_key ?? "")));
  for (const seed of seeds) {
    if (!activeKeys.has(seed.promptKey)) {
      throw new Error(`未在 active 视图中找到 prompt_key=${seed.promptKey}`);
    }
  }

  for (const seed of seeds) {
    const resolvedRows = promptStoreClient
      ? await (async () => {
        const { data, error } = await promptStoreClient.rpc("prompt_store_resolve_active_version", {
          p_prompt_key: seed.promptKey,
          p_target_env: targetEnv,
          p_provider: "openai",
          p_model: "gpt-4o-mini",
        });

        if (error) {
          throw new Error(`service_role 解析 prompt 失败 (${seed.promptKey}): ${error.message}`);
        }

        return normalizeQueryRows(data ?? []);
      })()
      : normalizeQueryRows(await runManagementQuery(
        projectRef,
        accessToken,
        `select prompt_key, version, content_format, payload_json
         from public.prompt_store_resolve_active_version($1::text, $2::text, $3::text, $4::text)`,
        [seed.promptKey, targetEnv, "openai", "gpt-4o-mini"],
      ));

    if (resolvedRows.length !== 1) {
      throw new Error(`prompt_key=${seed.promptKey} 解析结果异常，返回 ${resolvedRows.length} 条`);
    }

    const payload = resolvedRows[0]?.payload_json;
    if (!payload || typeof payload !== "object") {
      throw new Error(`prompt_key=${seed.promptKey} 未返回有效 payload_json`);
    }
  }
}

async function main(): Promise<void> {
  const options = parseArgs(process.argv.slice(2));
  const seeds = await buildPromptSeeds();
  const promptStoreClient = createPromptStoreClient(options);

  console.log(`[prompt-store] 目标项目: ${options.projectRef}`);
  console.log(`[prompt-store] 目标环境: ${options.targetEnv}`);
  console.log(`[prompt-store] 准备写入 ${seeds.length} 条 prompt`);
  console.log(`[prompt-store] 写入通道: ${describeWriteChannel(options, promptStoreClient)}`);

  if (options.applySchema) {
    console.log("[prompt-store] 开始应用 schema SQL ...");
    await applySchema(options.projectRef, options.accessToken);
    console.log("[prompt-store] schema 应用完成");
  }

  for (const seed of seeds) {
    console.log(`[prompt-store] upsert ${seed.promptKey}`);
    await upsertPromptSeed(
      options.projectRef,
      options.accessToken,
      seed,
      options.targetEnv,
      options.providerScope,
      options.modelScope,
      promptStoreClient,
    );
  }

  console.log("[prompt-store] 开始验证 active prompt 与解析函数 ...");
  await verifyPromptSeeds(options.projectRef, options.accessToken, seeds, options.targetEnv, promptStoreClient);
  console.log(`[prompt-store] 完成，共验证 ${seeds.length} 条 prompt`);
}

main().catch((error) => {
  console.error("[prompt-store] 失败:", error instanceof Error ? error.message : error);
  process.exitCode = 1;
});
