/**
 * ============================================================
 * db.ts — Supabase 客户端初始化 + 数据库操作函数
 * ============================================================
 * 功能：
 *   1. 初始化 Supabase 客户端（读取环境变量）
 *   2. standardized_results / keyword_process / 搜索运行时表相关 CRUD
 * 
 * 环境变量：
 *   SUPABASE_URL          — Supabase 项目 URL
 *   SUPABASE_SERVICE_ROLE_KEY — 服务端密钥（优先使用）
 *   SUPABASE_ANON_KEY     — 匿名密钥（回退使用）
 * ============================================================
 */

import { createClient } from "@supabase/supabase-js";
import { mergeSupabaseNodeClientOptions, resolveSupabaseKey, resolveSupabaseUrl } from "./supabase-env.ts";
import type {
  KeywordProcessRow,
  StandardizedResultRow,
  StandardizationExecutionMode,
  StandardizationTaskRow,
  StandardizationTaskItemRow,
  SearchTaskRow,
  SearchTaskItemRow,
  SearchRoundRow,
  BestMatchImageRow,
  ExtendedBestMatchesRow,
  ImageSearchResultRow,
  KeywordSelectionRow,
  ProcurementDictionaryTermType,
  ProcurementParameterInterpretation,
  FinalResultRow,
  FinalResultProductRow,
  ExtendedBestMatchRecordRow,
} from "../core/types.ts";

export type ProcurementAccount1688Row = {
  id: string;
  user_id: string;
  account_name: string;
  password: string;
  remark: string | null;
  login_status: string | null;
  login_error: string | null;
  login_checked_at: string | null;
  created_at: string;
  updated_at: string;
};

export type OrderedProcurementMaterialRow = {
  id: string;
  batch_id: string;
  material_name: string;
  specification: string;
  quantity: number;
  unit: string;
  invoice_type: string;
  brand: string;
  remarks: string;
  delivery_address: string;
  created_by: string | null;
};

// ── 初始化 Supabase 客户端 ────────────────────────────────────
// 优先使用 service_role key（服务端完整权限），回退到 anon key
const supabaseUrl = resolveSupabaseUrl(process.env);
const supabaseKey = resolveSupabaseKey(process.env);

if (!supabaseUrl || !supabaseKey) {
  throw new Error(
    "缺少 Supabase 环境变量。请在 .env 中填写 SUPABASE_URL 和真正的 service_role key。\n" +
      "参考模板：.env.example",
  );
}

export const supabase = createClient(
  supabaseUrl,
  supabaseKey,
  mergeSupabaseNodeClientOptions({}, process.env),
);

export async function getProcurementAccountById(
  id: string,
): Promise<ProcurementAccount1688Row | null> {
  const { data, error } = await supabase
    .from("procurement_accounts_1688")
    .select("*")
    .eq("id", id)
    .maybeSingle();

  if (error) throw new Error(`[procurement_accounts_1688] 查询失败: ${error.message}`);
  return data as ProcurementAccount1688Row | null;
}

export async function getProcurementAccountsByIds(
  ids: string[],
): Promise<ProcurementAccount1688Row[]> {
  const normalizedIds = [...new Set(ids.map((id) => id.trim()).filter(Boolean))];
  if (normalizedIds.length === 0) {
    return [];
  }

  const { data, error } = await supabase
    .from("procurement_accounts_1688")
    .select("*")
    .in("id", normalizedIds);

  if (error) throw new Error(`[procurement_accounts_1688] 批量查询失败: ${error.message}`);
  return (data ?? []) as ProcurementAccount1688Row[];
}

export async function getBatchCreatedBy(batchId: string): Promise<string | null> {
  const { data, error } = await supabase
    .from("procurement_batches")
    .select("created_by")
    .eq("batch_id", batchId)
    .maybeSingle();

  if (error) throw new Error(`[procurement_batches] 查询创建人失败: ${error.message}`);
  return (data as { created_by?: string | null } | null)?.created_by ?? null;
}

export async function getOrderedProcurementMaterials(
  batchId: string,
  materialIds?: string[],
): Promise<OrderedProcurementMaterialRow[]> {
  const { data, error } = await supabase
    .from("procurement_materials")
    .select("id, batch_id, material_name, specification, quantity, unit, invoice_type, brand, remarks, delivery_address, created_by, created_at")
    .eq("batch_id", batchId)
    .order("created_at", { ascending: true })
    .order("id", { ascending: true });

  if (error) {
    throw new Error(`[procurement_materials] 查询排序物料失败: ${error.message}`);
  }

  const scopedIds = materialIds && materialIds.length > 0 ? new Set(materialIds) : null;

  return ((data ?? []) as OrderedProcurementMaterialRow[])
    .filter((row) => !scopedIds || scopedIds.has(row.id));
}

export async function getStandardizedResultByMaterialId(
  batchId: string,
  materialId: string,
): Promise<StandardizedResultRow | null> {
  const { data, error } = await supabase
    .from("standardized_results")
    .select("*")
    .eq("batch_id", batchId)
    .eq("material_id", materialId)
    .maybeSingle();

  if (error) {
    throw new Error(`[standardized_results] 查询失败: ${error.message}`);
  }

  if (!data) {
    return null;
  }

  const row = data as Omit<StandardizedResultRow, "alt_search_terms"> & { alt_search_terms: unknown };
  return {
    ...row,
    alt_search_terms: Array.isArray(row.alt_search_terms) ? row.alt_search_terms.map((item) => String(item)) : [],
  };
}

export async function getStandardizedResultsByBatch(
  batchId: string,
  materialIds?: string[],
): Promise<StandardizedResultRow[]> {
  let query = supabase
    .from("standardized_results")
    .select("*")
    .eq("batch_id", batchId)
    .order("created_at", { ascending: true });

  if (materialIds && materialIds.length > 0) {
    query = query.in("material_id", materialIds);
  }

  const { data, error } = await query;
  if (error) {
    throw new Error(`[standardized_results] 查询批次结果失败: ${error.message}`);
  }

  return ((data ?? []) as Array<Omit<StandardizedResultRow, "alt_search_terms"> & { alt_search_terms: unknown }>).map((row) => ({
    ...row,
    alt_search_terms: Array.isArray(row.alt_search_terms) ? row.alt_search_terms.map((item) => String(item)) : [],
  }));
}

export async function upsertStandardizedResults(
  rows: Omit<StandardizedResultRow, "id" | "created_at" | "updated_at">[],
): Promise<StandardizedResultRow[]> {
  if (rows.length === 0) {
    return [];
  }

  const { data, error } = await supabase
    .from("standardized_results")
    .upsert(rows, { onConflict: "batch_id,material_id" })
    .select("*");

  if (error) {
    throw new Error(`[standardized_results] upsert 失败: ${error.message}`);
  }

  return ((data ?? []) as Array<Omit<StandardizedResultRow, "alt_search_terms"> & { alt_search_terms: unknown }>).map((row) => ({
    ...row,
    alt_search_terms: Array.isArray(row.alt_search_terms) ? row.alt_search_terms.map((item) => String(item)) : [],
  }));
}

export async function deleteStandardizedResultsByMaterialIds(
  batchId: string,
  materialIds: string[],
): Promise<void> {
  if (materialIds.length === 0) {
    return;
  }

  const { error } = await supabase
    .from("standardized_results")
    .delete()
    .eq("batch_id", batchId)
    .in("material_id", materialIds);

  if (error) {
    throw new Error(`[standardized_results] 删除失败: ${error.message}`);
  }
}

// ── keyword_process 操作 ──────────────────────────────────────

export async function upsertKeywordProcess(
  row: Omit<KeywordProcessRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("keyword_process")
    .upsert(row, { onConflict: "batch_id,material_id" });

  if (error) throw new Error(`[keyword_process] upsert 失败: ${error.message}`);
}

export async function getKeywordProcessByMaterialId(
  materialId: string,
  batchId?: string,
): Promise<KeywordProcessRow | null> {
  let query = supabase
    .from("keyword_process")
    .select("*")
    .eq("material_id", materialId);
  if (batchId) query = query.eq("batch_id", batchId);
  const { data, error } = await query.maybeSingle();

  if (error) throw new Error(`[keyword_process] 查询失败: ${error.message}`);
  return data as KeywordProcessRow | null;
}

export async function getLatestKeywordProcess(): Promise<KeywordProcessRow | null> {
  const { data, error } = await supabase
    .from("keyword_process")
    .select("*")
    .order("created_at", { ascending: false })
    .limit(1)
    .maybeSingle();

  if (error) throw new Error(`[keyword_process] 查询最新记录失败: ${error.message}`);
  return data as KeywordProcessRow | null;
}

export async function deleteKeywordProcessesByMaterialIds(
  materialIds: string[],
  batchId: string = "",
): Promise<void> {
  if (materialIds.length === 0) {
    return;
  }

  const { error } = await supabase
    .from("keyword_process")
    .delete()
    .eq("batch_id", batchId)
    .in("material_id", materialIds);

  if (error) throw new Error(`[keyword_process] 删除失败: ${error.message}`);
}

// ── standardization_tasks 操作 ─────────────────────────────

export async function createStandardizationTask(
  row: Omit<StandardizationTaskRow, "id" | "created_at" | "updated_at">,
): Promise<StandardizationTaskRow> {
  const now = new Date().toISOString();
  const { data, error } = await supabase
    .from("standardization_tasks")
    .insert({
      ...row,
      updated_at: now,
    })
    .select("*")
    .single();

  if (error) throw new Error(`[standardization_tasks] 创建失败: ${error.message}`);
  return data as StandardizationTaskRow;
}

export async function updateStandardizationTask(
  id: string,
  updates: Partial<Omit<StandardizationTaskRow, "id" | "created_at">>,
): Promise<StandardizationTaskRow> {
  const { data, error } = await supabase
    .from("standardization_tasks")
    .update({
      ...updates,
      updated_at: new Date().toISOString(),
    })
    .eq("id", id)
    .select("*")
    .single();

  if (error) throw new Error(`[standardization_tasks] 更新失败: ${error.message}`);
  return data as StandardizationTaskRow;
}

export async function getStandardizationTasksByStatuses(
  statuses: StandardizationTaskRow["status"][],
): Promise<StandardizationTaskRow[]> {
  if (statuses.length === 0) {
    return [];
  }

  const { data, error } = await supabase
    .from("standardization_tasks")
    .select("*")
    .in("status", statuses)
    .order("updated_at", { ascending: false });

  if (error) throw new Error(`[standardization_tasks] 按状态查询失败: ${error.message}`);
  return (data ?? []) as StandardizationTaskRow[];
}

export async function getStandardizationTasksByModeAndStatuses(
  executionMode: StandardizationExecutionMode,
  statuses: StandardizationTaskRow["status"][],
  options: {
    orderBy?: "created_at" | "updated_at";
    ascending?: boolean;
  } = {},
): Promise<StandardizationTaskRow[]> {
  if (statuses.length === 0) {
    return [];
  }

  const { data, error } = await supabase
    .from("standardization_tasks")
    .select("*")
    .eq("execution_mode", executionMode)
    .in("status", statuses)
    .order(options.orderBy ?? "updated_at", { ascending: options.ascending ?? false });

  if (error) {
    throw new Error(`[standardization_tasks] 按执行模式和状态查询失败: ${error.message}`);
  }

  return (data ?? []) as StandardizationTaskRow[];
}

// ── standardization_task_items 操作 ────────────────────────

export async function insertStandardizationTaskItems(
  rows: Omit<StandardizationTaskItemRow, "id" | "created_at" | "updated_at">[],
): Promise<StandardizationTaskItemRow[]> {
  if (rows.length === 0) {
    return [];
  }

  const now = new Date().toISOString();
  const { data, error } = await supabase
    .from("standardization_task_items")
    .insert(rows.map((row) => ({
      ...row,
      updated_at: now,
    })))
    .select("*");

  if (error) throw new Error(`[standardization_task_items] 批量插入失败: ${error.message}`);
  return (data ?? []) as StandardizationTaskItemRow[];
}

export async function updateStandardizationTaskItem(
  id: string,
  updates: Partial<Omit<StandardizationTaskItemRow, "id" | "task_id" | "batch_id" | "source_material_id" | "created_at">>,
): Promise<StandardizationTaskItemRow> {
  const { data, error } = await supabase
    .from("standardization_task_items")
    .update({
      ...updates,
      updated_at: new Date().toISOString(),
    })
    .eq("id", id)
    .select("*")
    .single();

  if (error) throw new Error(`[standardization_task_items] 更新失败: ${error.message}`);
  return data as StandardizationTaskItemRow;
}

export async function getStandardizationTaskItemById(
  id: string,
): Promise<StandardizationTaskItemRow | null> {
  const { data, error } = await supabase
    .from("standardization_task_items")
    .select("*")
    .eq("id", id)
    .maybeSingle();

  if (error) throw new Error(`[standardization_task_items] 查询失败: ${error.message}`);
  return data as StandardizationTaskItemRow | null;
}

export async function getStandardizationTaskItemsByTaskId(
  taskId: string,
): Promise<StandardizationTaskItemRow[]> {
  const { data, error } = await supabase
    .from("standardization_task_items")
    .select("*")
    .eq("task_id", taskId)
    .order("created_at", { ascending: true });

  if (error) throw new Error(`[standardization_task_items] 按任务查询失败: ${error.message}`);
  return (data ?? []) as StandardizationTaskItemRow[];
}

export async function resolveOutstandingStandardizationFailures(params: {
  batchId: string;
  sourceMaterialId?: string;
  resolvedByTaskId: string;
}): Promise<void> {
  if (!params.sourceMaterialId) {
    return;
  }

  let query = supabase
    .from("standardization_task_items")
    .update({
      resolved_at: new Date().toISOString(),
      resolved_by_task_id: params.resolvedByTaskId,
      updated_at: new Date().toISOString(),
    })
    .eq("batch_id", params.batchId)
    .eq("status", "failed")
    .is("resolved_at", null);

  query = query.eq("source_material_id", params.sourceMaterialId);

  const { error } = await query;
  if (error) throw new Error(`[standardization_task_items] 标记历史失败记录已解决失败: ${error.message}`);
}

export async function failPendingStandardizationTaskItems(
  taskId: string,
  message: string,
): Promise<void> {
  const { error } = await supabase
    .from("standardization_task_items")
    .update({
      status: "failed",
      failure_reason: message,
      updated_at: new Date().toISOString(),
    })
    .eq("task_id", taskId)
    .in("status", ["pending", "processing"]);

  if (error) throw new Error(`[standardization_task_items] 批量失败标记失败: ${error.message}`);
}

function mapSearchTaskRow(row: Record<string, unknown>): SearchTaskRow {
  return {
    ...(row as unknown as SearchTaskRow),
    pipeline_config: (row.pipeline_config ?? {}) as SearchTaskRow["pipeline_config"],
    errors: Array.isArray(row.errors) ? row.errors.map((item) => String(item)) : [],
    result: row.result && typeof row.result === "object" && !Array.isArray(row.result)
      ? { ...(row.result as Record<string, unknown>) }
      : {},
  };
}

function mapSearchTaskItemRow(row: Record<string, unknown>): SearchTaskItemRow {
  const snapshot = row.item_snapshot;
  return {
    ...(row as unknown as SearchTaskItemRow),
    item_snapshot: snapshot && typeof snapshot === "object" && !Array.isArray(snapshot)
      ? {
          material_id: String((snapshot as Record<string, unknown>).material_id ?? row.material_id ?? ""),
          name: String((snapshot as Record<string, unknown>).name ?? row.material_name ?? ""),
        }
      : {
          material_id: String(row.material_id ?? ""),
          name: String(row.material_name ?? ""),
        },
  };
}

// ── search_tasks 操作 ───────────────────────────────────────

export async function createSearchTask(
  row: Omit<SearchTaskRow, "id" | "created_at" | "updated_at">,
): Promise<SearchTaskRow> {
  const now = new Date().toISOString();
  const { data, error } = await supabase
    .from("search_tasks")
    .insert({
      ...row,
      updated_at: now,
    })
    .select("*")
    .single();

  if (error) throw new Error(`[search_tasks] 创建失败: ${error.message}`);
  return mapSearchTaskRow((data ?? {}) as Record<string, unknown>);
}

export async function updateSearchTask(
  id: string,
  updates: Partial<Omit<SearchTaskRow, "id" | "created_at">>,
): Promise<SearchTaskRow> {
  const { data, error } = await supabase
    .from("search_tasks")
    .update({
      ...updates,
      updated_at: new Date().toISOString(),
    })
    .eq("id", id)
    .select("*")
    .single();

  if (error) throw new Error(`[search_tasks] 更新失败: ${error.message}`);
  return mapSearchTaskRow((data ?? {}) as Record<string, unknown>);
}

export async function getSearchTaskById(
  id: string,
): Promise<SearchTaskRow | null> {
  const { data, error } = await supabase
    .from("search_tasks")
    .select("*")
    .eq("id", id)
    .maybeSingle();

  if (error) throw new Error(`[search_tasks] 查询失败: ${error.message}`);
  return data ? mapSearchTaskRow(data as Record<string, unknown>) : null;
}

export async function getSearchTasksByStatuses(
  statuses: SearchTaskRow["status"][],
  options: {
    orderBy?: "created_at" | "updated_at";
    ascending?: boolean;
  } = {},
): Promise<SearchTaskRow[]> {
  if (statuses.length === 0) {
    return [];
  }

  const { data, error } = await supabase
    .from("search_tasks")
    .select("*")
    .in("status", statuses)
    .order(options.orderBy ?? "updated_at", { ascending: options.ascending ?? false });

  if (error) throw new Error(`[search_tasks] 按状态查询失败: ${error.message}`);
  return (data ?? []).map((row) => mapSearchTaskRow(row as Record<string, unknown>));
}

// ── search_task_items 操作 ──────────────────────────────────

export async function insertSearchTaskItems(
  rows: Omit<SearchTaskItemRow, "id" | "created_at" | "updated_at">[],
): Promise<SearchTaskItemRow[]> {
  if (rows.length === 0) {
    return [];
  }

  const now = new Date().toISOString();
  const { data, error } = await supabase
    .from("search_task_items")
    .insert(rows.map((row) => ({
      ...row,
      updated_at: now,
    })))
    .select("*");

  if (error) throw new Error(`[search_task_items] 批量插入失败: ${error.message}`);
  return (data ?? []).map((row) => mapSearchTaskItemRow(row as Record<string, unknown>));
}

export async function updateSearchTaskItem(
  id: string,
  updates: Partial<Omit<SearchTaskItemRow, "id" | "task_id" | "batch_id" | "material_id" | "sort_order" | "created_at">>,
): Promise<SearchTaskItemRow> {
  const { data, error } = await supabase
    .from("search_task_items")
    .update({
      ...updates,
      updated_at: new Date().toISOString(),
    })
    .eq("id", id)
    .select("*")
    .single();

  if (error) throw new Error(`[search_task_items] 更新失败: ${error.message}`);
  return mapSearchTaskItemRow((data ?? {}) as Record<string, unknown>);
}

export async function getSearchTaskItemsByTaskId(
  taskId: string,
): Promise<SearchTaskItemRow[]> {
  const { data, error } = await supabase
    .from("search_task_items")
    .select("*")
    .eq("task_id", taskId)
    .order("sort_order", { ascending: true });

  if (error) throw new Error(`[search_task_items] 按任务查询失败: ${error.message}`);
  return (data ?? []).map((row) => mapSearchTaskItemRow(row as Record<string, unknown>));
}

export async function getSearchTaskItemsByTaskIds(
  taskIds: string[],
): Promise<SearchTaskItemRow[]> {
  const normalizedTaskIds = [...new Set(taskIds.map((taskId) => taskId.trim()).filter(Boolean))];
  if (normalizedTaskIds.length === 0) {
    return [];
  }

  const { data, error } = await supabase
    .from("search_task_items")
    .select("*")
    .in("task_id", normalizedTaskIds)
    .order("sort_order", { ascending: true });

  if (error) throw new Error(`[search_task_items] 批量查询失败: ${error.message}`);
  return (data ?? []).map((row) => mapSearchTaskItemRow(row as Record<string, unknown>));
}

export async function deleteSearchTaskItemsByTaskId(taskId: string): Promise<void> {
  const { error } = await supabase
    .from("search_task_items")
    .delete()
    .eq("task_id", taskId);

  if (error) throw new Error(`[search_task_items] 删除失败: ${error.message}`);
}

export async function deleteSearchTaskById(taskId: string): Promise<void> {
  const { error } = await supabase
    .from("search_tasks")
    .delete()
    .eq("id", taskId);

  if (error) throw new Error(`[search_tasks] 删除失败: ${error.message}`);
}

export async function markSearchTaskItemsStopped(
  taskId: string,
  message: string,
  statuses: SearchTaskItemRow["status"][] = ["pending", "processing"],
): Promise<void> {
  if (statuses.length === 0) {
    return;
  }

  const now = new Date().toISOString();
  const { error } = await supabase
    .from("search_task_items")
    .update({
      status: "stopped",
      error_message: message,
      finished_at: now,
      updated_at: now,
    })
    .eq("task_id", taskId)
    .in("status", statuses);

  if (error) throw new Error(`[search_task_items] 批量标记停止失败: ${error.message}`);
}

// ── search_rounds 操作 ───────────────────────────────────────

const SEARCH_ROUND_DIAGNOSTIC_COLUMNS = [
  "source_candidate_count",
  "qualified_result_count",
  "rejected_no_phone_count",
  "target_result_count",
  "shortage_reason",
  "require_phone_contact",
  "contact_enrichment_attempted",
  "contact_enrichment_skip_reason",
  "keyword_hit_rate_source",
  "keyword_hit_rate_reason",
  "keyword_hit_rate_sample_count",
];

let warnedSearchRoundLegacySchema = false;
const FINAL_RESULT_PRODUCT_DIAGNOSTIC_COLUMNS = [
  "score",
  "is_match",
  "reason",
  "has_phone_contact",
  "qualification_status",
  "contact_collection_reason",
];
let warnedFinalResultProductLegacySchema = false;

function stripSearchRoundDiagnosticColumns(row: Omit<SearchRoundRow, "id" | "created_at">) {
  const output = { ...row };
  for (const key of SEARCH_ROUND_DIAGNOSTIC_COLUMNS) {
    delete output[key as keyof typeof output];
  }
  return output;
}

function isMissingSearchRoundDiagnosticColumn(error: { message?: string } | null): boolean {
  const message = String(error?.message ?? "");
  return SEARCH_ROUND_DIAGNOSTIC_COLUMNS.some((column) => message.includes(column));
}

function stripFinalResultProductDiagnosticColumns(
  rows: Omit<FinalResultProductRow, "id" | "created_at">[],
) {
  return rows.map((row) => {
    const output = { ...row };
    for (const key of FINAL_RESULT_PRODUCT_DIAGNOSTIC_COLUMNS) {
      delete output[key as keyof typeof output];
    }
    return output;
  });
}

function isMissingFinalResultProductDiagnosticColumn(error: { message?: string } | null): boolean {
  const message = String(error?.message ?? "");
  return FINAL_RESULT_PRODUCT_DIAGNOSTIC_COLUMNS.some((column) => message.includes(column));
}

export async function upsertSearchRound(
  row: Omit<SearchRoundRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("search_rounds")
    .upsert(row, { onConflict: "batch_id,material_id,search_round,run_type" });

  if (!error) {
    return;
  }

  if (isMissingSearchRoundDiagnosticColumn(error)) {
    const { error: legacyError } = await supabase
      .from("search_rounds")
      .upsert(stripSearchRoundDiagnosticColumns(row), { onConflict: "batch_id,material_id,search_round,run_type" });

    if (!legacyError) {
      if (!warnedSearchRoundLegacySchema) {
        warnedSearchRoundLegacySchema = true;
        console.warn("[search_rounds] 当前数据库缺少新的诊断列，已回退为旧 schema 写入。请执行最新 migration 以启用命中率来源与联系方式跳过诊断。");
      }
      return;
    }

    throw new Error(`[search_rounds] upsert 失败: ${legacyError.message}`);
  }

  if (error) throw new Error(`[search_rounds] upsert 失败: ${error.message}`);
}

export async function getSearchRoundsByMaterialId(
  materialId: string,
  runType?: "initial" | "followup",
): Promise<SearchRoundRow[]> {
  let query = supabase
    .from("search_rounds")
    .select("*")
    .eq("material_id", materialId)
    .order("search_round", { ascending: true });

  if (runType) {
    query = query.eq("run_type", runType);
  }

  const { data, error } = await query;
  if (error) throw new Error(`[search_rounds] 查询失败: ${error.message}`);
  return data as SearchRoundRow[];
}

export async function updateSearchRoundStageDiagnostics(params: {
  batchId: string;
  materialId: string;
  stageOrder: number;
  taskId?: string | null;
  sourceCandidateCount?: number | null;
  keywordHitRate?: number | null;
  keywordHitRateSource?: string;
  keywordHitRateReason?: string;
  keywordHitRateSampleCount?: number | null;
  qualifiedResultCount?: number | null;
  rejectedNoPhoneCount?: number | null;
  targetResultCount?: number | null;
  shortageReason: string;
  contactEnrichmentAttempted: boolean;
  contactEnrichmentSkipReason: string;
}): Promise<void> {
  const update: Record<string, unknown> = {
    source_candidate_count: params.sourceCandidateCount ?? null,
    qualified_result_count: params.qualifiedResultCount ?? null,
    rejected_no_phone_count: params.rejectedNoPhoneCount ?? null,
    target_result_count: params.targetResultCount ?? null,
    shortage_reason: params.shortageReason,
    require_phone_contact: true,
    contact_enrichment_attempted: params.contactEnrichmentAttempted,
    contact_enrichment_skip_reason: params.contactEnrichmentSkipReason,
  };
  if (params.keywordHitRate !== undefined) {
    update.keyword_hit_rate = params.keywordHitRate;
  }
  if (params.keywordHitRateSource !== undefined) {
    update.keyword_hit_rate_source = params.keywordHitRateSource;
  }
  if (params.keywordHitRateReason !== undefined) {
    update.keyword_hit_rate_reason = params.keywordHitRateReason;
  }
  if (params.keywordHitRateSampleCount !== undefined) {
    update.keyword_hit_rate_sample_count = params.keywordHitRateSampleCount;
  }

  const applyUpdate = (withTaskId: boolean) => {
    let query = supabase
      .from("search_rounds")
      .update(update)
      .eq("batch_id", params.batchId)
      .eq("material_id", params.materialId)
      .eq("stage_order", params.stageOrder)
      .eq("run_type", "initial");

    if (withTaskId && params.taskId) {
      query = query.eq("task_id", params.taskId);
    }

    return query.select("id");
  };

  let { data, error } = await applyUpdate(Boolean(params.taskId));
  if (!error && (!params.taskId || (data?.length ?? 0) > 0)) {
    return;
  }
  if (!error && params.taskId && (data?.length ?? 0) === 0) {
    ({ data, error } = await applyUpdate(false));
    if (!error) {
      return;
    }
  }

  if (isMissingSearchRoundDiagnosticColumn(error)) {
    if (!warnedSearchRoundLegacySchema) {
      warnedSearchRoundLegacySchema = true;
      console.warn("[search_rounds] 当前数据库缺少新的诊断列，无法回写固定搜索后处理摘要。请执行最新 migration。");
    }
    return;
  }

  throw new Error(`[search_rounds] 阶段诊断更新失败: ${error.message}`);
}

// ── best_match_images 操作 ───────────────────────────────────

export async function upsertBestMatchImage(
  row: Omit<BestMatchImageRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("best_match_images")
    .upsert(row, { onConflict: "batch_id,material_id" });

  if (error) throw new Error(`[best_match_images] upsert 失败: ${error.message}`);
}

export async function getBestMatchImageByMaterialId(
  materialId: string,
): Promise<BestMatchImageRow | null> {
  const { data, error } = await supabase
    .from("best_match_images")
    .select("*")
    .eq("material_id", materialId)
    .maybeSingle();

  if (error) throw new Error(`[best_match_images] 查询失败: ${error.message}`);
  return data as BestMatchImageRow | null;
}

// ── extended_best_matches 操作 ───────────────────────────────

export async function upsertExtendedBestMatches(
  row: Omit<ExtendedBestMatchesRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("extended_best_matches")
    .upsert(row, { onConflict: "batch_id,material_id" });

  if (error) throw new Error(`[extended_best_matches] upsert 失败: ${error.message}`);
}

export async function getExtendedBestMatchesByMaterialId(
  materialId: string,
): Promise<ExtendedBestMatchesRow | null> {
  const { data, error } = await supabase
    .from("extended_best_matches")
    .select("*")
    .eq("material_id", materialId)
    .maybeSingle();

  if (error) throw new Error(`[extended_best_matches] 查询失败: ${error.message}`);
  return data as ExtendedBestMatchesRow | null;
}

// ── image_search_results 操作 ────────────────────────────────

export async function upsertImageSearchResult(
  row: Omit<ImageSearchResultRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("image_search_results")
    .upsert(row, { onConflict: "batch_id,material_id" });

  if (error) throw new Error(`[image_search_results] upsert 失败: ${error.message}`);
}

export async function getImageSearchResultByMaterialId(
  materialId: string,
): Promise<ImageSearchResultRow | null> {
  const { data, error } = await supabase
    .from("image_search_results")
    .select("*")
    .eq("material_id", materialId)
    .maybeSingle();

  if (error) throw new Error(`[image_search_results] 查询失败: ${error.message}`);
  return data as ImageSearchResultRow | null;
}

// ── keyword_selections 操作 ──────────────────────────────────

export async function upsertKeywordSelection(
  row: Omit<KeywordSelectionRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("keyword_selections")
    .upsert(row, { onConflict: "batch_id,material_id" });

  if (error) throw new Error(`[keyword_selections] upsert 失败: ${error.message}`);
}

export async function getKeywordSelectionByMaterialId(
  materialId: string,
): Promise<KeywordSelectionRow | null> {
  const { data, error } = await supabase
    .from("keyword_selections")
    .select("*")
    .eq("material_id", materialId)
    .maybeSingle();

  if (error) throw new Error(`[keyword_selections] 查询失败: ${error.message}`);
  return data as KeywordSelectionRow | null;
}

// ── final_results 操作 ──────────────────────────────────────

export async function upsertFinalResult(
  row: Omit<FinalResultRow, "id" | "created_at">,
): Promise<void> {
  const { error } = await supabase
    .from("final_results")
    .upsert(row, { onConflict: "batch_id,material_id" });

  if (error) throw new Error(`[final_results] upsert 失败: ${error.message}`);
}

export async function getFinalResultByMaterialId(
  materialId: string,
): Promise<FinalResultRow | null> {
  const { data, error } = await supabase
    .from("final_results")
    .select("*")
    .eq("material_id", materialId)
    .maybeSingle();

  if (error) throw new Error(`[final_results] 查询失败: ${error.message}`);
  return data as FinalResultRow | null;
}

// ── final_result_products 操作 ───────────────────────────────

export async function upsertFinalResultProducts(
  materialId: string,
  rows: Omit<FinalResultProductRow, "id" | "created_at">[],
  batchId: string = '',
): Promise<void> {
  const { error: delError } = await supabase
    .from("final_result_products")
    .delete()
    .eq("batch_id", batchId)
    .eq("material_id", materialId);
  if (delError) throw new Error(`[final_result_products] 删除失败: ${delError.message}`);

  if (rows.length === 0) return;

  const { error: insertError } = await supabase
    .from("final_result_products")
    .insert(rows);
  if (!insertError) {
    return;
  }

  if (isMissingFinalResultProductDiagnosticColumn(insertError)) {
    const { error: legacyError } = await supabase
      .from("final_result_products")
      .insert(stripFinalResultProductDiagnosticColumns(rows));

    if (!legacyError) {
      if (!warnedFinalResultProductLegacySchema) {
        warnedFinalResultProductLegacySchema = true;
        console.warn("[final_result_products] 当前数据库缺少匹配/资格审计列，已回退为旧 schema 写入。请执行最新 migration 以启用 score / is_match / reason / has_phone_contact / qualification_status 审计。");
      }
      return;
    }

    throw new Error(`[final_result_products] 插入失败: ${legacyError.message}`);
  }

  throw new Error(`[final_result_products] 插入失败: ${insertError.message}`);
}

// ── extended_best_match_records 操作 ─────────────────────────

export async function upsertExtendedBestMatchRecords(
  materialId: string,
  rows: Omit<ExtendedBestMatchRecordRow, "id" | "created_at">[],
  batchId: string = '',
): Promise<void> {
  const { error: delError } = await supabase
    .from("extended_best_match_records")
    .delete()
    .eq("batch_id", batchId)
    .eq("material_id", materialId);
  if (delError) throw new Error(`[extended_best_match_records] 删除失败: ${delError.message}`);

  if (rows.length === 0) return;

  const { error: insertError } = await supabase
    .from("extended_best_match_records")
    .insert(rows);
  if (insertError) throw new Error(`[extended_best_match_records] 插入失败: ${insertError.message}`);
}

// ── llm_config 操作 ──────────────────────────────────────────

export interface LlmConfigRecord {
  id: string;
  provider: string;
  model: string;
  api_key: string;
  base_url: string;
  is_active: boolean;
}

export interface LlmProviderConfigRow {
  id?: string;
  provider_key: string;
  api_key: string;
  base_url: string;
  is_active: boolean;
  configured_by?: string | null;
  created_at?: string;
  updated_at?: string;
}

export interface LlmStageRouteRow {
  id?: string;
  stage: string;
  provider_key: string;
  model: string;
  updated_by?: string | null;
  created_at?: string;
  updated_at?: string;
}

/**
 * 获取当前活跃的 LLM 配置（is_active = true 的最新一条）
 */
export async function getActiveLlmConfig(): Promise<LlmConfigRecord | null> {
  const { data, error } = await supabase
    .from("llm_config")
    .select("id, provider, model, api_key, base_url, is_active")
    .eq("is_active", true)
    .order("updated_at", { ascending: false })
    .limit(1)
    .maybeSingle();

  if (error) {
    console.warn(`[llm_config] 查询失败: ${error.message}`);
    return null;
  }

  return data;
}

export async function listActiveLlmProviderConfigs(): Promise<LlmProviderConfigRow[]> {
  const { data, error } = await supabase
    .from("llm_provider_configs")
    .select("id, provider_key, api_key, base_url, is_active, configured_by, created_at, updated_at")
    .eq("is_active", true);

  if (error) {
    console.warn(`[llm_provider_configs] 查询失败: ${error.message}`);
    return [];
  }

  return data ?? [];
}

export async function listLlmStageRoutes(): Promise<LlmStageRouteRow[]> {
  const { data, error } = await supabase
    .from("llm_stage_routes")
    .select("id, stage, provider_key, model, updated_by, created_at, updated_at");

  if (error) {
    console.warn(`[llm_stage_routes] 查询失败: ${error.message}`);
    return [];
  }

  return data ?? [];
}

// ── 型号关联字典 ────────────────────────────────────────────

export type ModelNumberOverrideRow = {
  id: string;
  model_number: string;
  suggested_keyword: string;
  note: string | null;
  created_by: string | null;
  created_at: string;
  updated_at: string;
};

export type ProcurementDictionaryEntryRow = {
  id: string;
  term: string;
  normalized_term: string;
  term_type: ProcurementDictionaryTermType;
  product_context: string;
  normalized_context: string;
  interpretation: string;
  suggested_keyword: string;
  evidence: string;
  source_batch_id: string;
  source_material_id: string | null;
  source_llm_model: string;
  occurrence_count: number;
  note: string | null;
  created_by: string | null;
  created_at: string;
  updated_at: string;
};

export type ProcurementDictionaryEntryUpsert = ProcurementParameterInterpretation & {
  suggested_keyword?: string;
  source_batch_id?: string;
  source_material_id?: string;
  source_llm_model?: string;
  note?: string;
  created_by?: string;
};

function normalizeDictionaryTerm(value: string): string {
  return value.trim().replace(/\s+/g, " ").toUpperCase();
}

function normalizeDictionaryContext(value: string): string {
  return value.trim().replace(/\s+/g, " ").toLowerCase();
}

/**
 * 从 Supabase 加载采购字典里的唯一型号，返回 upper-cased key 的映射。
 * 新表不可用时降级读取旧 model_number_overrides。
 * 失败时静默警告并返回空对象（降级到静态字典）
 */
export async function loadModelNumberOverridesFromDb(): Promise<Record<string, { suggested_keyword: string; note: string }>> {
  const dictionary = await supabase
    .from("procurement_dictionary_entries")
    .select("term, suggested_keyword, interpretation, note")
    .eq("term_type", "unique_model");

  if (!dictionary.error) {
    const result: Record<string, { suggested_keyword: string; note: string }> = {};
    for (const row of dictionary.data ?? []) {
      const term = typeof row.term === "string" ? row.term.trim() : "";
      const suggestedKeyword = typeof row.suggested_keyword === "string" ? row.suggested_keyword.trim() : "";
      if (!term || !suggestedKeyword) {
        continue;
      }

      result[term.toUpperCase()] = {
        suggested_keyword: suggestedKeyword,
        note: (typeof row.interpretation === "string" && row.interpretation.trim())
          || (typeof row.note === "string" ? row.note : "")
          || "",
      };
    }
    return result;
  }

  console.warn("[procurement_dictionary_entries] 从 DB 加载采购字典失败，尝试旧型号字典:", dictionary.error.message);

  const { data, error } = await supabase
    .from("model_number_overrides")
    .select("model_number, suggested_keyword, note");

  if (error) {
    console.warn("[model_number_overrides] 从 DB 加载字典失败，将使用静态字典:", error.message);
    return {};
  }

  const result: Record<string, { suggested_keyword: string; note: string }> = {};
  for (const row of data ?? []) {
    result[(row.model_number as string).toUpperCase()] = {
      suggested_keyword: row.suggested_keyword as string,
      note: (row.note as string | null) ?? "",
    };
  }
  return result;
}

/**
 * Upsert 采购字典条目。重复 term/type/context 时更新解释并累加出现次数。
 */
export async function upsertProcurementDictionaryEntries(
  entries: ProcurementDictionaryEntryUpsert[],
): Promise<void> {
  for (const entry of entries) {
    const term = entry.term.trim();
    const interpretation = entry.interpretation.trim();
    if (!term || !interpretation) {
      continue;
    }

    const productContext = entry.product_context.trim();
    const normalizedTerm = normalizeDictionaryTerm(term);
    const normalizedContext = normalizeDictionaryContext(productContext);
    const now = new Date().toISOString();
    const basePayload = {
      term,
      term_type: entry.term_type,
      product_context: productContext,
      interpretation,
      suggested_keyword: entry.suggested_keyword?.trim() ?? "",
      evidence: entry.evidence.trim(),
      source_batch_id: entry.source_batch_id?.trim() ?? "",
      source_material_id: entry.source_material_id?.trim() || null,
      source_llm_model: entry.source_llm_model?.trim() ?? "",
      note: entry.note?.trim() || null,
      created_by: entry.created_by?.trim() || null,
      updated_at: now,
    };

    const existing = await supabase
      .from("procurement_dictionary_entries")
      .select("id, occurrence_count")
      .eq("normalized_term", normalizedTerm)
      .eq("term_type", entry.term_type)
      .eq("normalized_context", normalizedContext)
      .maybeSingle();

    if (existing.error) {
      throw new Error(`[procurement_dictionary_entries] 查询失败: ${existing.error.message}`);
    }

    if (existing.data?.id) {
      const { error } = await supabase
        .from("procurement_dictionary_entries")
        .update({
          ...basePayload,
          occurrence_count: Math.max(1, Number(existing.data.occurrence_count ?? 0) + 1),
        })
        .eq("id", existing.data.id);

      if (error) throw new Error(`[procurement_dictionary_entries] update 失败: ${error.message}`);
      continue;
    }

    const { error } = await supabase
      .from("procurement_dictionary_entries")
      .insert({
        ...basePayload,
        occurrence_count: 1,
      });

    if (error) throw new Error(`[procurement_dictionary_entries] insert 失败: ${error.message}`);
  }
}

/**
 * Upsert 型号关联字典条目（型号已存在则更新 suggested_keyword 和 note）
 * note 字段用于记录自动录入原因，长度截断到 200 字符
 */
export async function upsertModelNumberOverride(
  modelNumber: string,
  suggestedKeyword: string,
  note: string,
): Promise<void> {
  // 统一存储大写，与 loadModelNumberOverridesFromDb 中的 key 规范保持一致
  const normalizedModel = modelNumber.trim().toUpperCase();
  await upsertProcurementDictionaryEntries([
    {
      term: normalizedModel,
      term_type: "unique_model",
      product_context: "",
      interpretation: note.trim() || suggestedKeyword.trim(),
      suggested_keyword: suggestedKeyword,
      evidence: "identified_unique_model",
      note,
      created_by: "auto",
    },
  ]);

  const { error } = await supabase
    .from("model_number_overrides")
    .upsert(
      {
        model_number: normalizedModel,
        suggested_keyword: suggestedKeyword,
        note: note.slice(0, 200),
        created_by: "auto",
        updated_at: new Date().toISOString(),
      },
      { onConflict: "model_number" },
    );

  if (error) {
    throw new Error(`[model_number_overrides] upsert 失败: ${error.message}`);
  }
}
