import { StandardizedMaterial } from '@/types';
import { normalizeEmptyFieldText } from '@/lib/emptyFieldPlaceholders';
import { getSupabaseClient } from '@/lib/supabase';
import type {
  ParsedStandardizedImportField,
  ParsedStandardizedImportRow,
} from '@/lib/standardizedImportParser';
import { calculateStandardizationTaskProgressPercent } from '@/lib/standardizationTaskProgress';

type StandardizationExecutionMode = 'online' | 'offline';

interface StandardizationTaskRow {
  id: string;
  batch_id: string;
  execution_mode: StandardizationExecutionMode;
  status: 'pending' | 'processing' | 'stopping' | 'completed' | 'failed' | 'stopped';
  total_rows: number;
  processed_rows: number;
  success_rows: number;
  failed_rows: number;
  current_item: string;
  current_phase: string;
  error_message: string;
  created_at: string;
  updated_at: string;
  started_at: string | null;
  finished_at: string | null;
}

interface StandardizationTaskItemStatusRow {
  batch_id: string;
  source_material_id: string;
  status: 'pending' | 'processing' | 'success' | 'failed';
  created_at: string;
}

interface StandardizationSourceSnapshot {
  id: string;
  batch_id: string;
  material_name: string;
  specification: string;
  quantity: number;
  unit: string;
  invoice_type: string;
  brand: string;
  remarks: string;
  delivery_address: string;
  technical_standard: string;
  material_requirement: string;
  technical_requirement: string;
  applicable_conditions: string;
  test_requirement: string;
  acceptance_note: string;
  warranty_period: string;
  deadline: string;
  source_file: string;
  created_at: string;
  created_by: string;
}

interface StandardizationTaskItemFailureRow {
  id: string;
  task_id: string;
  batch_id: string;
  source_material_id: string;
  source_snapshot: unknown;
  standardized_name: string;
  can_search_1688: boolean | null;
  missing_required_fields: unknown;
  failure_reason: string;
  human_fix_hint: string;
  llm_model: string;
  created_at: string;
  updated_at: string;
}

interface StandardizedResultRow {
  id: string;
  batch_id: string;
  material_id: string;
  source_type: 'ai' | 'manual';
  invoice_type: string;
  original_name: string;
  original_spec: string;
  standardized_name: string;
  category: string;
  brand: string;
  notes: string;
  delivery_address: string;
  primary_search_term: string;
  alt_search_terms: unknown;
  standardized_spec: string;
  standardized_unit: string;
  quantity: number;
  product_type: string;
  llm_model: string;
  ai_reasoning: string;
  created_by: string | null;
  updated_by: string | null;
  created_at: string;
  updated_at: string;
}

interface ProcurementMaterialReferenceRow {
  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_at: string;
  created_by: string | null;
}

interface StandardizedResultUpsertRow {
  batch_id: string;
  material_id: string;
  source_type: 'ai' | 'manual';
  invoice_type: string;
  original_name: string;
  original_spec: string;
  standardized_name: string;
  category: string;
  brand: string;
  notes: string;
  delivery_address: string;
  primary_search_term: string;
  alt_search_terms: string[];
  standardized_spec: string;
  standardized_unit: string;
  quantity: number;
  product_type: string;
  llm_model: string;
  ai_reasoning: string;
  created_by: string | null;
  updated_by: string | null;
}

export type FailureSupplementFieldKey =
  | 'spec_model'
  | 'brand'
  | 'material'
  | 'usage_scene'
  | 'execution_standard'
  | 'supplement_notes';

export interface FailureSupplementFieldDefinition {
  key: FailureSupplementFieldKey;
  label: string;
}

export interface RequiredFailureSupplementField extends FailureSupplementFieldDefinition {
  required: boolean;
  displayLabel: string;
}

export type FailureSupplementFields = Partial<Record<FailureSupplementFieldKey, string>>;

export interface StandardizationFailureItem {
  id: string;
  task_id: string;
  batch_id: string;
  source_material_id: string;
  original_name: string;
  original_spec: string;
  quantity: number;
  unit: string;
  invoice_type: string;
  standardized_name: string;
  can_search_1688: boolean | null;
  missing_required_fields: string[];
  failure_reason: string;
  human_fix_hint: string;
  llm_model: string;
  created_at: string;
  updated_at: string;
  source_snapshot: StandardizationSourceSnapshot;
}

export interface StandardizationTaskRecord {
  id: string;
  batch_id: string;
  status: 'pending' | 'processing' | 'stopping' | 'completed' | 'failed' | 'stopped';
  total_rows: number;
  processed_rows: number;
  success_rows: number;
  failed_rows: number;
  current_item: string;
  current_phase: string;
  error_message: string;
  created_at: string;
  updated_at: string;
  started_at: string | null;
  finished_at: string | null;
  execution_mode: StandardizationExecutionMode;
  provider_status: string;
  progress_current: number;
  progress_total: number;
  progress_percent: number;
}

export interface StandardizationSuccessSummary {
  totalCount: number;
  countByBatchId: Record<string, number>;
}

export interface ImportStandardizedResult {
  importedCount: number;
  batchIds: string[];
  materialIds: string[];
}

export interface CreateManualStandardizedResultInput {
  failureId: string;
  supplement?: FailureSupplementFields;
}

export interface ManualStandardizedResultSuccess {
  failureId: string;
  materialId: string;
  batchId: string;
  updated_at: string;
}

export interface ManualStandardizedResultFailure {
  failureId: string;
  error: string;
}

export interface CreateManualStandardizedResultsResult {
  successCount: number;
  failedCount: number;
  successes: ManualStandardizedResultSuccess[];
  failures: ManualStandardizedResultFailure[];
}

export const FAILURE_SUPPLEMENT_FIELDS: FailureSupplementFieldDefinition[] = [
  { key: 'spec_model', label: '规格型号' },
  { key: 'brand', label: '品牌' },
  { key: 'material', label: '材质' },
  { key: 'usage_scene', label: '用途/场景' },
  { key: 'execution_standard', label: '执行标准' },
  { key: 'supplement_notes', label: '补充说明' },
];

const IMPORTABLE_STANDARDIZED_FIELDS: ParsedStandardizedImportField[] = [
  'invoice_type',
  'original_name',
  'standardized_name',
  'original_spec',
  'standardized_spec',
  'brand',
  'category',
  'quantity',
  'standardized_unit',
  'notes',
  'delivery_address',
  'primary_search_term',
  'alt_search_terms',
  'product_type',
  'llm_model',
  'ai_reasoning',
];

function toText(value: unknown): string {
  return normalizeEmptyFieldText(value);
}

function toNumber(value: unknown): number {
  if (typeof value === 'number' && Number.isFinite(value)) {
    return value;
  }

  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : 0;
}

function toStringArray(value: unknown): string[] {
  if (Array.isArray(value)) {
    return value
      .map((item) => normalizeEmptyFieldText(item))
      .filter(Boolean);
  }

  if (typeof value === 'string') {
    const text = value.trim();
    if (!text) {
      return [];
    }

    try {
      const parsed = JSON.parse(text);
      if (Array.isArray(parsed)) {
        return parsed
          .map((item) => normalizeEmptyFieldText(item))
          .filter(Boolean);
      }
    } catch {
      return text.split(/[,，]/).map((item) => normalizeEmptyFieldText(item)).filter(Boolean);
    }
  }

  return [];
}

function compactSearchKey(value: string) {
  return normalizeEmptyFieldText(value).replace(/\s+/g, '').toLowerCase();
}

function dedupeSearchTerms(values: string[]) {
  const seen = new Set<string>();
  const results: string[] = [];

  for (const value of values) {
    const normalized = normalizeEmptyFieldText(value);
    const key = compactSearchKey(normalized);
    if (!normalized || !key || seen.has(key)) {
      continue;
    }

    seen.add(key);
    results.push(normalized);
  }

  return results;
}

function combineSearchParts(parts: Array<string | undefined>) {
  return parts
    .map((part) => normalizeEmptyFieldText(part))
    .filter(Boolean)
    .join(' ')
    .trim();
}

function normalizeSupplementText(value: unknown): string {
  return normalizeEmptyFieldText(value).replace(/\s+/g, ' ').trim();
}

function normalizeSupplementMatcherText(value: unknown): string {
  return normalizeSupplementText(value).replace(/\s+/g, '').toLowerCase();
}

function hasAnyMatcher(text: string, matchers: RegExp[]) {
  return matchers.some((matcher) => matcher.test(text));
}

function getSupplementRequiredKeysFromText(value: string): Set<FailureSupplementFieldKey> {
  const text = normalizeSupplementMatcherText(value);
  const keys = new Set<FailureSupplementFieldKey>();

  if (!text) {
    return keys;
  }

  if (hasAnyMatcher(text, [/规格|型号|尺寸|尺码|厚度|口径|直径|长度|宽度|高度|容量|重量|管径|线径|目数|参数/])) {
    keys.add('spec_model');
  }

  if (hasAnyMatcher(text, [/品牌|厂家|厂牌|牌子|商标/])) {
    keys.add('brand');
  }

  if (hasAnyMatcher(text, [/材质|材料|成分|原料|基材|牌号|钢种|合金|涂层|镀层|材种|面料/])) {
    keys.add('material');
  }

  if (hasAnyMatcher(text, [/用途|场景|适用|适配|使用|工况|施工|介质|适配对象|部位|功能|性能/])) {
    keys.add('usage_scene');
  }

  if (hasAnyMatcher(text, [/执行标准|技术标准|标准号|国标|行业标准|标准|规范|等级|级别/])) {
    keys.add('execution_standard');
  }

  if (hasAnyMatcher(text, [/补充说明|备注|其他说明|其他要求/])) {
    keys.add('supplement_notes');
  }

  return keys;
}

export function getRequiredSupplementFields(params: {
  missing_required_fields?: unknown;
  failure_reason?: string;
  human_fix_hint?: string;
}): RequiredFailureSupplementField[] {
  const requiredKeys = new Set<FailureSupplementFieldKey>();
  const sourceTexts = [
    ...toStringArray(params.missing_required_fields),
    params.failure_reason ?? '',
    params.human_fix_hint ?? '',
  ];

  for (const text of sourceTexts) {
    for (const key of getSupplementRequiredKeysFromText(text)) {
      requiredKeys.add(key);
    }
  }

  if (requiredKeys.size === 0) {
    requiredKeys.add('spec_model');
    requiredKeys.add('supplement_notes');
  }

  return FAILURE_SUPPLEMENT_FIELDS.map((field) => {
    const required = requiredKeys.has(field.key);
    return {
      ...field,
      required,
      displayLabel: required ? `${field.label} *` : field.label,
    };
  });
}

function buildSupplementNotes(params: {
  supplement: FailureSupplementFields;
  sourceSnapshot: StandardizationSourceSnapshot;
}) {
  const { supplement, sourceSnapshot } = params;
  const supplementLines = [
    ['材质', supplement.material],
    ['用途/场景', supplement.usage_scene],
    ['执行标准', supplement.execution_standard],
    ['补充说明', supplement.supplement_notes],
  ]
    .map(([label, value]) => {
      const text = normalizeSupplementText(value);
      return text ? `${label}：${text}` : '';
    })
    .filter(Boolean);

  const sourceLines = [
    ['来源备注', sourceSnapshot.remarks],
    ['材质要求', sourceSnapshot.material_requirement],
    ['技术要求', sourceSnapshot.technical_requirement],
    ['适用条件', sourceSnapshot.applicable_conditions],
    ['检验要求', sourceSnapshot.test_requirement],
    ['验收说明', sourceSnapshot.acceptance_note],
  ]
    .map(([label, value]) => {
      const text = normalizeSupplementText(value);
      return text ? `${label}：${text}` : '';
    })
    .filter(Boolean);

  return [...supplementLines, ...sourceLines].join('\n');
}

export function mapFailureSupplementToStandardizedRow(params: {
  failure: Pick<StandardizationFailureItem, 'batch_id' | 'source_material_id' | 'standardized_name' | 'llm_model' | 'source_snapshot'>;
  supplement?: FailureSupplementFields;
  currentUserId?: string | null;
}): StandardizedResultUpsertRow {
  const { failure, currentUserId = null } = params;
  const supplement = params.supplement ?? {};
  const sourceSnapshot = failure.source_snapshot;
  const originalName = toText(sourceSnapshot.material_name);
  const originalSpec = toText(sourceSnapshot.specification);
  const standardizedName = normalizeSupplementText(failure.standardized_name) || originalName;
  const standardizedSpec = normalizeSupplementText(supplement.spec_model) || originalSpec;
  const brand = normalizeSupplementText(supplement.brand) || toText(sourceSnapshot.brand);
  const primarySearchTerm = combineSearchParts([standardizedName, standardizedSpec, brand])
    || standardizedName
    || originalName;
  const notes = buildSupplementNotes({ supplement, sourceSnapshot });
  const sourceStandard = normalizeSupplementText(supplement.execution_standard) || toText(sourceSnapshot.technical_standard);

  return {
    batch_id: failure.batch_id || sourceSnapshot.batch_id,
    material_id: failure.source_material_id || sourceSnapshot.id,
    source_type: 'manual',
    invoice_type: toText(sourceSnapshot.invoice_type),
    original_name: originalName,
    original_spec: originalSpec,
    standardized_name: standardizedName,
    category: '',
    brand,
    notes,
    delivery_address: toText(sourceSnapshot.delivery_address),
    primary_search_term: primarySearchTerm,
    alt_search_terms: buildFallbackAltSearchTerms({
      primarySearchTerm,
      originalName,
      standardizedName,
      originalSpec,
      standardizedSpec,
      brand,
      category: '',
    }),
    standardized_spec: standardizedSpec,
    standardized_unit: toText(sourceSnapshot.unit),
    quantity: toNumber(sourceSnapshot.quantity),
    product_type: normalizeSupplementText(supplement.usage_scene),
    llm_model: toText(failure.llm_model),
    ai_reasoning: sourceStandard ? `人工补充入成功池；执行标准：${sourceStandard}` : '人工补充入成功池',
    created_by: currentUserId,
    updated_by: currentUserId,
  };
}

function buildFallbackAltSearchTerms(params: {
  primarySearchTerm: string;
  originalName: string;
  standardizedName: string;
  originalSpec: string;
  standardizedSpec: string;
  brand: string;
  category: string;
}) {
  const candidates = dedupeSearchTerms([
    combineSearchParts([params.brand, params.standardizedName, params.standardizedSpec]),
    combineSearchParts([params.category, params.standardizedName, params.standardizedSpec]),
    combineSearchParts([params.standardizedName, params.standardizedSpec]),
    combineSearchParts([params.brand, params.standardizedName]),
    combineSearchParts([params.category, params.standardizedName]),
    combineSearchParts([params.originalName, params.originalSpec]),
    params.originalName,
    params.standardizedName,
  ]);

  const filtered = candidates.filter((term) => compactSearchKey(term) !== compactSearchKey(params.primarySearchTerm));
  if (filtered.length > 0) {
    return filtered.slice(0, 3);
  }

  if (candidates.length > 0) {
    return candidates.slice(0, 1);
  }

  return params.primarySearchTerm ? [params.primarySearchTerm] : [];
}

function mapSourceSnapshot(snapshotValue: unknown): StandardizationSourceSnapshot {
  const snapshot = (snapshotValue ?? {}) as Record<string, unknown>;

  return {
    id: toText(snapshot.id),
    batch_id: toText(snapshot.batch_id),
    material_name: toText(snapshot.material_name),
    specification: toText(snapshot.specification),
    quantity: toNumber(snapshot.quantity),
    unit: toText(snapshot.unit),
    invoice_type: toText(snapshot.invoice_type),
    brand: toText(snapshot.brand),
    remarks: toText(snapshot.remarks),
    delivery_address: toText(snapshot.delivery_address),
    technical_standard: toText(snapshot.technical_standard),
    material_requirement: toText(snapshot.material_requirement),
    technical_requirement: toText(snapshot.technical_requirement),
    applicable_conditions: toText(snapshot.applicable_conditions),
    test_requirement: toText(snapshot.test_requirement),
    acceptance_note: toText(snapshot.acceptance_note),
    warranty_period: toText(snapshot.warranty_period),
    deadline: toText(snapshot.deadline),
    source_file: toText(snapshot.source_file),
    created_at: toText(snapshot.created_at),
    created_by: toText(snapshot.created_by),
  };
}

function mapFailureItem(row: StandardizationTaskItemFailureRow): StandardizationFailureItem {
  const sourceSnapshot = mapSourceSnapshot(row.source_snapshot);

  return {
    id: row.id,
    task_id: row.task_id,
    batch_id: row.batch_id,
    source_material_id: row.source_material_id,
    original_name: sourceSnapshot.material_name,
    original_spec: sourceSnapshot.specification,
    quantity: sourceSnapshot.quantity,
    unit: sourceSnapshot.unit,
    invoice_type: sourceSnapshot.invoice_type,
    standardized_name: row.standardized_name,
    can_search_1688: row.can_search_1688,
    missing_required_fields: toStringArray(row.missing_required_fields),
    failure_reason: row.failure_reason,
    human_fix_hint: row.human_fix_hint,
    llm_model: row.llm_model,
    created_at: row.created_at,
    updated_at: row.updated_at,
    source_snapshot: sourceSnapshot,
  };
}

function inferExecutionMode(row: Pick<StandardizationTaskRow, 'execution_mode' | 'current_phase' | 'current_item'>): StandardizationExecutionMode {
  if (row.execution_mode === 'online' || row.execution_mode === 'offline') {
    return row.execution_mode;
  }

  if (row.current_phase?.startsWith('离线批处理')) {
    return 'offline';
  }

  if (/(batch_[A-Za-z0-9_-]+)/.test(row.current_item ?? '')) {
    return 'offline';
  }

  return 'online';
}

function extractProviderStatus(currentPhase: string) {
  const match = /^离线批处理\s*·\s*(.+)$/u.exec(currentPhase.trim());
  return match?.[1]?.trim() ?? '';
}

function getTaskProgressTotal(row: StandardizationTaskRow, executionMode: StandardizationExecutionMode): number {
  return executionMode === 'offline'
    ? Math.max(0, row.total_rows ?? 0)
    : Math.max(0, (row.total_rows ?? 0) * 2);
}

function getTaskProgressCurrent(row: StandardizationTaskRow, executionMode: StandardizationExecutionMode): number {
  const total = getTaskProgressTotal(row, executionMode);
  return Math.min(total, Math.max(0, row.processed_rows ?? 0));
}

function mapTaskRecord(row: StandardizationTaskRow): StandardizationTaskRecord {
  const executionMode = inferExecutionMode(row);
  const providerStatus = executionMode === 'offline' ? extractProviderStatus(row.current_phase) : '';
  const progressTotal = getTaskProgressTotal(row, executionMode);
  const progressCurrent = row.status === 'completed'
    ? progressTotal
    : getTaskProgressCurrent(row, executionMode);
  const progressPercent = calculateStandardizationTaskProgressPercent({
    execution_mode: executionMode,
    status: row.status,
    provider_status: providerStatus,
    progress_current: progressCurrent,
    progress_total: progressTotal,
    current_phase: row.current_phase,
  });

  return {
    ...row,
    execution_mode: executionMode,
    provider_status: providerStatus,
    progress_current: progressCurrent,
    progress_total: progressTotal,
    progress_percent: progressPercent,
  };
}

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

function toProvidedFieldSet(row: ParsedStandardizedImportRow) {
  return new Set(row.provided_fields);
}

function hasMeaningfulImportedField(row: ParsedStandardizedImportRow) {
  return IMPORTABLE_STANDARDIZED_FIELDS.some((field) => {
    if (!row.provided_fields.includes(field)) {
      return false;
    }

    const value = row[field];
    if (Array.isArray(value)) {
      return value.length > 0;
    }

    return typeof value === 'number' ? value !== 0 : Boolean(value);
  });
}

function resolveImportedMaterial(
  row: ParsedStandardizedImportRow,
  materialsByBatch: Map<string, ProcurementMaterialReferenceRow[]>,
) {
  const batchRows = materialsByBatch.get(row.batch_id) ?? [];

  if (batchRows.length === 0) {
    throw new Error(`批次 ${row.batch_id} 不存在可回写的采购物料，请先导入该批次原始数据。`);
  }

  if (row.material_id) {
    const exactMatch = batchRows.find((candidate) => candidate.id === row.material_id);
    if (exactMatch) {
      return exactMatch;
    }

    throw new Error(`批次 ${row.batch_id} 未找到物料ID ${row.material_id} 对应的采购物料，请确认导入模板中的物料ID是否来自最新导出。`);
  }

  const lookupName = normalizeImportLookupText(row.original_name || row.standardized_name);
  const lookupSpecs = [...new Set([
    normalizeImportLookupText(row.original_spec),
    normalizeImportLookupText(row.standardized_spec),
  ].filter(Boolean))];

  let matchedRows = lookupName
    ? batchRows.filter((candidate) => normalizeImportLookupText(candidate.material_name) === lookupName)
    : [];

  if (matchedRows.length > 1 && lookupSpecs.length > 0) {
    matchedRows = matchedRows.filter((candidate) => {
      const candidateSpecs = [normalizeImportLookupText(candidate.specification)].filter(Boolean);
      return lookupSpecs.some((spec) => candidateSpecs.includes(spec));
    });
  }

  if (matchedRows.length === 1) {
    return matchedRows[0];
  }

  const materialLabel = row.original_name || row.standardized_name || '未命名物料';
  if (matchedRows.length > 1) {
    throw new Error(`批次 ${row.batch_id} 中存在多条与“${materialLabel}”匹配的采购物料，无法确定回写目标，请使用最新导出模板并保留“物料ID(material_id)”列。`);
  }

  throw new Error(`批次 ${row.batch_id} 未找到与“${materialLabel}”匹配的采购物料，请使用最新导出模板并保留“物料ID(material_id)”列。`);
}

function mapStandardizedRow(
  row: StandardizedResultRow,
  material?: ProcurementMaterialReferenceRow,
): StandardizedMaterial {
  const originalName = toText(row.original_name) || toText(material?.material_name);
  const originalSpec = toText(row.original_spec) || toText(material?.specification);
  const standardizedName = toText(row.standardized_name) || originalName;
  const standardizedSpec = toText(row.standardized_spec) || originalSpec;
  const primarySearchTerm = toText(row.primary_search_term) || standardizedName || originalName;
  const altSearchTerms = dedupeSearchTerms(toStringArray(row.alt_search_terms));

  return {
    id: row.id,
    batch_id: row.batch_id,
    material_id: row.material_id,
    source_type: row.source_type,
    invoice_type: toText(row.invoice_type) || toText(material?.invoice_type),
    original_name: originalName,
    original_spec: originalSpec,
    standardized_name: standardizedName,
    category: toText(row.category),
    brand: toText(row.brand) || toText(material?.brand),
    notes: toText(row.notes) || toText(material?.remarks),
    delivery_address: toText(row.delivery_address) || toText(material?.delivery_address),
    primary_search_term: primarySearchTerm,
    alt_search_terms: altSearchTerms,
    standardized_spec: standardizedSpec,
    standardized_unit: toText(row.standardized_unit) || toText(material?.unit),
    quantity: Number.isFinite(row.quantity) ? row.quantity : toNumber(material?.quantity),
    product_type: toText(row.product_type),
    llm_model: toText(row.llm_model),
    ai_reasoning: toText(row.ai_reasoning),
    created_by: row.created_by,
    updated_at: row.updated_at,
    created_at: row.created_at,
  };
}

function buildImportedStandardizedRow(params: {
  importRow: ParsedStandardizedImportRow;
  material: ProcurementMaterialReferenceRow;
  existing?: StandardizedMaterial;
  currentUserId: string | null;
}): StandardizedResultUpsertRow {
  const { importRow, material, existing, currentUserId } = params;
  const providedFields = toProvidedFieldSet(importRow);
  const originalName = providedFields.has('original_name')
    ? importRow.original_name
    : (existing?.original_name || material.material_name);
  const standardizedName = providedFields.has('standardized_name')
    ? (importRow.standardized_name || (providedFields.has('original_name') ? importRow.original_name : existing?.standardized_name || originalName))
    : (existing?.standardized_name || originalName);
  const originalSpec = providedFields.has('original_spec')
    ? importRow.original_spec
    : (existing?.original_spec || material.specification);
  const standardizedSpec = providedFields.has('standardized_spec')
    ? importRow.standardized_spec
    : (existing?.standardized_spec || originalSpec);
  const brand = providedFields.has('brand')
    ? importRow.brand
    : (existing?.brand || material.brand);
  const category = providedFields.has('category')
    ? importRow.category
    : (existing?.category || '');
  const invoiceType = providedFields.has('invoice_type')
    ? importRow.invoice_type
    : (existing?.invoice_type || material.invoice_type);
  const notes = providedFields.has('notes')
    ? importRow.notes
    : (existing?.notes || material.remarks);
  const deliveryAddress = providedFields.has('delivery_address')
    ? importRow.delivery_address
    : (existing?.delivery_address || material.delivery_address);
  const quantity = providedFields.has('quantity')
    ? importRow.quantity
    : (existing?.quantity ?? material.quantity);
  const standardizedUnit = providedFields.has('standardized_unit')
    ? importRow.standardized_unit
    : (existing?.standardized_unit || material.unit);
  const primarySearchTerm = providedFields.has('primary_search_term')
    ? importRow.primary_search_term
    : (existing?.primary_search_term || standardizedName || originalName);
  const rawAltSearchTerms = providedFields.has('alt_search_terms')
    ? importRow.alt_search_terms
    : (existing?.alt_search_terms || []);
  const altSearchTerms = dedupeSearchTerms(rawAltSearchTerms);
  const fallbackAltSearchTerms = altSearchTerms.length > 0
    ? altSearchTerms
    : buildFallbackAltSearchTerms({
        primarySearchTerm: primarySearchTerm || standardizedName || originalName,
        originalName,
        standardizedName,
        originalSpec,
        standardizedSpec,
        brand,
        category,
      });

  return {
    batch_id: material.batch_id,
    material_id: material.id,
    source_type: 'manual',
    invoice_type: invoiceType,
    original_name: originalName,
    original_spec: originalSpec,
    standardized_name: standardizedName,
    category,
    brand,
    notes,
    delivery_address: deliveryAddress,
    primary_search_term: primarySearchTerm || standardizedName || originalName,
    alt_search_terms: fallbackAltSearchTerms,
    standardized_spec: standardizedSpec,
    standardized_unit: standardizedUnit,
    quantity,
    product_type: providedFields.has('product_type') ? importRow.product_type : (existing?.product_type || ''),
    llm_model: providedFields.has('llm_model') ? importRow.llm_model : (existing?.llm_model || ''),
    ai_reasoning: providedFields.has('ai_reasoning') ? importRow.ai_reasoning : (existing?.ai_reasoning || ''),
    created_by: existing?.created_by ?? currentUserId,
    updated_by: currentUserId,
  };
}

async function getCurrentUserId(supabase: ReturnType<typeof getSupabaseClient>) {
  const authApi = (supabase as unknown as {
    auth?: {
      getUser?: () => Promise<{ data?: { user?: { id?: string | null } | null }; error?: unknown }>;
    };
  }).auth;

  if (!authApi?.getUser) {
    return null;
  }

  try {
    const result = await authApi.getUser();
    return result.data?.user?.id ?? null;
  } catch {
    return null;
  }
}

async function deleteRelatedSearchArtifactsByMaterialIds(
  supabase: ReturnType<typeof getSupabaseClient>,
  materialIds: string[],
) {
  if (materialIds.length === 0) {
    return;
  }

  const { data: finalRows, error: finalRowsError } = await supabase
    .from('final_result_products')
    .select('id')
    .in('material_id', materialIds);

  if (finalRowsError) {
    throw new Error(finalRowsError.message);
  }

  const searchResultIds = (finalRows ?? []).map((row) => String((row as { id: string }).id));
  if (searchResultIds.length > 0) {
    const [contactLogsRes, customerChatsRes] = await Promise.all([
      supabase.from('contact_logs').delete().in('search_result_id', searchResultIds),
      supabase.from('customer_service_chats').delete().in('search_result_id', searchResultIds),
    ]);

    if (contactLogsRes.error) throw new Error(contactLogsRes.error.message);
    if (customerChatsRes.error) throw new Error(customerChatsRes.error.message);
  }

  const deletions = await Promise.all([
    supabase.from('search_rounds').delete().in('material_id', materialIds),
    supabase.from('best_match_images').delete().in('material_id', materialIds),
    supabase.from('extended_best_matches').delete().in('material_id', materialIds),
    supabase.from('image_search_results').delete().in('material_id', materialIds),
    supabase.from('keyword_selections').delete().in('material_id', materialIds),
    supabase.from('final_results').delete().in('material_id', materialIds),
    supabase.from('final_result_products').delete().in('material_id', materialIds),
    supabase.from('extended_best_match_records').delete().in('material_id', materialIds),
  ]);

  for (const result of deletions) {
    if (result.error) {
      throw new Error(result.error.message);
    }
  }
}

export function summarizeCurrentStandardizedRows(
  standardizedRows: Array<Pick<StandardizedMaterial, 'batch_id' | 'material_id'>>,
  taskRows: StandardizationTaskItemStatusRow[],
): StandardizationSuccessSummary {
  const latestTaskByMaterial = new Map<string, StandardizationTaskItemStatusRow>();

  for (const row of taskRows) {
    if (!row.source_material_id) {
      continue;
    }

    const key = `${row.batch_id}::${row.source_material_id}`;
    const existing = latestTaskByMaterial.get(key);
    if (!existing || row.created_at.localeCompare(existing.created_at) > 0) {
      latestTaskByMaterial.set(key, row);
    }
  }

  const seen = new Set<string>();
  const countByBatchId: Record<string, number> = {};
  let totalCount = 0;

  for (const row of standardizedRows) {
    const key = `${row.batch_id}::${row.material_id}`;
    if (seen.has(key)) {
      continue;
    }

    seen.add(key);
    const latestTask = latestTaskByMaterial.get(key);
    if (latestTask && latestTask.status !== 'success') {
      continue;
    }

    totalCount += 1;
    countByBatchId[row.batch_id] = (countByBatchId[row.batch_id] ?? 0) + 1;
  }

  return {
    totalCount,
    countByBatchId,
  };
}

export async function fetchStandardizationSuccessSummary(params?: {
  batchId?: string;
}): Promise<StandardizationSuccessSummary> {
  const supabase = getSupabaseClient();

  let query = supabase
    .from('standardized_results')
    .select('batch_id, material_id');

  if (params?.batchId) {
    query = query.eq('batch_id', params.batchId);
  }

  const { data, error } = await query;
  if (error) {
    throw new Error(error.message);
  }

  return summarizeCurrentStandardizedRows(
    ((data ?? []) as Array<Pick<StandardizedMaterial, 'batch_id' | 'material_id'>>),
    [],
  );
}

export async function fetchStandardized(params?: {
  batchId?: string;
  search?: string;
}): Promise<StandardizedMaterial[]> {
  const supabase = getSupabaseClient();

  let query = supabase
    .from('standardized_results')
    .select('*')
    .order('created_at', { ascending: false });

  if (params?.batchId) {
    query = query.eq('batch_id', params.batchId);
  }

  const { data, error } = await query;
  if (error) {
    throw new Error(error.message);
  }

  const standardizedRows = (data ?? []) as StandardizedResultRow[];
  if (standardizedRows.length === 0) {
    return [];
  }

  const materialIds = [...new Set(standardizedRows.map((row) => row.material_id).filter(Boolean))];
  const { data: materialData, error: materialError } = await supabase
    .from('procurement_materials')
    .select('id, batch_id, material_name, specification, quantity, unit, invoice_type, brand, remarks, delivery_address, created_at, created_by')
    .in('id', materialIds);

  if (materialError) {
    throw new Error(materialError.message);
  }

  const materialMap = new Map(
    ((materialData ?? []) as ProcurementMaterialReferenceRow[]).map((row) => [row.id, row]),
  );

  let results = standardizedRows.map((row) => mapStandardizedRow(row, materialMap.get(row.material_id)));

  if (params?.search) {
    const searchTerm = params.search.toLowerCase();
    results = results.filter((item) => [
      item.original_name,
      item.standardized_name,
      item.original_spec,
      item.standardized_spec,
      item.brand,
      item.category,
      item.primary_search_term,
      ...item.alt_search_terms,
    ].some((field) => field.toLowerCase().includes(searchTerm)));
  }

  return results;
}

export async function fetchStandardizationFailures(params?: {
  batchId?: string;
  search?: string;
}): Promise<StandardizationFailureItem[]> {
  const supabase = getSupabaseClient();

  let query = supabase
    .from('standardization_task_items')
    .select('id, task_id, batch_id, source_material_id, source_snapshot, standardized_name, can_search_1688, missing_required_fields, failure_reason, human_fix_hint, llm_model, created_at, updated_at')
    .eq('status', 'failed')
    .order('updated_at', { ascending: false });

  if (params?.batchId) {
    query = query.eq('batch_id', params.batchId);
  }

  const { data, error } = await query;
  if (error) throw new Error(error.message);

  let results = (data ?? []).map((row) => mapFailureItem(row as StandardizationTaskItemFailureRow));

  if (params?.search) {
    const searchTerm = params.search.toLowerCase();
    results = results.filter((item) => [
      item.batch_id,
      item.source_material_id,
      item.original_name,
      item.original_spec,
      item.standardized_name,
      ...item.missing_required_fields,
      item.failure_reason,
      item.human_fix_hint,
    ].some((field) => field.toLowerCase().includes(searchTerm)));
  }

  return results;
}

export async function createManualStandardizedResultsFromFailures(
  inputs: CreateManualStandardizedResultInput[],
): Promise<CreateManualStandardizedResultsResult> {
  const meaningfulInputs = inputs
    .map((input) => ({
      failureId: normalizeEmptyFieldText(input.failureId),
      supplement: input.supplement ?? {},
    }))
    .filter((input) => Boolean(input.failureId));

  if (meaningfulInputs.length === 0) {
    return { successCount: 0, failedCount: 0, successes: [], failures: [] };
  }

  const supabase = getSupabaseClient();
  const currentUserId = await getCurrentUserId(supabase);
  const requestedIds = [...new Set(meaningfulInputs.map((input) => input.failureId))];

  const { data, error } = await supabase
    .from('standardization_task_items')
    .select('id, task_id, batch_id, source_material_id, source_snapshot, standardized_name, can_search_1688, missing_required_fields, failure_reason, human_fix_hint, llm_model, created_at, updated_at')
    .eq('status', 'failed')
    .in('id', requestedIds);

  if (error) {
    throw new Error(`读取标准化失败记录失败：${error.message}`);
  }

  const failureMap = new Map(
    ((data ?? []) as StandardizationTaskItemFailureRow[])
      .map((row) => [row.id, mapFailureItem(row)]),
  );
  const standardizedMaterialIds = [...new Set([...failureMap.values()].map((failure) => failure.source_material_id || failure.source_snapshot.id).filter(Boolean))];
  const existingCreatedByMap = new Map<string, string | null>();

  if (standardizedMaterialIds.length > 0) {
    const { data: existingData, error: existingError } = await supabase
      .from('standardized_results')
      .select('batch_id, material_id, created_by')
      .in('material_id', standardizedMaterialIds);

    if (existingError) {
      throw new Error(`读取已有标准化成功数据失败：${existingError.message}`);
    }

    for (const row of (existingData ?? []) as Array<Pick<StandardizedResultRow, 'batch_id' | 'material_id' | 'created_by'>>) {
      existingCreatedByMap.set(`${row.batch_id}::${row.material_id}`, row.created_by);
    }
  }

  const successes: ManualStandardizedResultSuccess[] = [];
  const failures: ManualStandardizedResultFailure[] = [];

  for (const input of meaningfulInputs) {
    const failure = failureMap.get(input.failureId);
    if (!failure) {
      failures.push({ failureId: input.failureId, error: '未找到可处理的标准化失败记录' });
      continue;
    }

    try {
      const upsertRow = mapFailureSupplementToStandardizedRow({
        failure,
        supplement: input.supplement,
        currentUserId,
      });
      const existingCreatedByKey = `${upsertRow.batch_id}::${upsertRow.material_id}`;
      if (existingCreatedByMap.has(existingCreatedByKey)) {
        upsertRow.created_by = existingCreatedByMap.get(existingCreatedByKey) ?? null;
      }

      const { error: upsertError } = await supabase
        .from('standardized_results')
        .upsert([upsertRow], { onConflict: 'batch_id,material_id' })
        .select('id')
        .single();

      if (upsertError) {
        throw new Error(`写入标准化成功数据失败：${upsertError.message}`);
      }

      const { error: deleteFailureError } = await supabase
        .from('standardization_task_items')
        .delete()
        .eq('id', failure.id)
        .eq('status', 'failed');

      if (deleteFailureError) {
        throw new Error(`删除已入成功池失败记录失败：${deleteFailureError.message}`);
      }

      successes.push({
        failureId: failure.id,
        materialId: upsertRow.material_id,
        batchId: upsertRow.batch_id,
        updated_at: new Date().toISOString(),
      });
    } catch (err) {
      failures.push({
        failureId: input.failureId,
        error: err instanceof Error ? err.message : String(err),
      });
    }
  }

  return {
    successCount: successes.length,
    failedCount: failures.length,
    successes,
    failures,
  };
}

export async function fetchStandardizationTasks(params?: {
  batchId?: string;
}): Promise<StandardizationTaskRecord[]> {
  const supabase = getSupabaseClient();

  let query = supabase
    .from('standardization_tasks')
    .select('id, batch_id, execution_mode, status, total_rows, processed_rows, success_rows, failed_rows, current_item, current_phase, error_message, created_at, updated_at, started_at, finished_at')
    .order('updated_at', { ascending: false });

  if (params?.batchId) {
    query = query.eq('batch_id', params.batchId);
  }

  const { data, error } = await query;
  if (error) throw new Error(error.message);

  return (data ?? []).map((row) => mapTaskRecord(row as StandardizationTaskRow));
}

export async function deleteStandardizationTaskById(id: string): Promise<void> {
  const supabase = getSupabaseClient();

  const { data: task, error: taskError } = await supabase
    .from('standardization_tasks')
    .select('id, status')
    .eq('id', id)
    .maybeSingle();

  if (taskError) {
    throw new Error(taskError.message);
  }

  if (!task) {
    return;
  }

  const taskStatus = (task as Pick<StandardizationTaskRow, 'status'>).status;
  if (taskStatus === 'pending' || taskStatus === 'processing' || taskStatus === 'stopping') {
    throw new Error('仅支持删除已结束的标准化任务状态记录');
  }

  const { data: taskItems, error: taskItemsError } = await supabase
    .from('standardization_task_items')
    .select('id')
    .eq('task_id', id);

  if (taskItemsError) {
    throw new Error(taskItemsError.message);
  }

  const taskItemIds = ((taskItems ?? []) as Array<{ id: string }>).map((row) => String(row.id)).filter(Boolean);
  const updatedAt = new Date().toISOString();

  if (taskItemIds.length > 0) {
    const { error: unlinkRetryError } = await supabase
      .from('standardization_task_items')
      .update({ retry_of_item_id: null, updated_at: updatedAt })
      .in('retry_of_item_id', taskItemIds);

    if (unlinkRetryError) {
      throw new Error(unlinkRetryError.message);
    }
  }

  const { error: deleteError } = await supabase
    .from('standardization_tasks')
    .delete()
    .eq('id', id);

  if (deleteError) {
    throw new Error(deleteError.message);
  }
}

export async function updateStandardized(
  id: string,
  updates: Partial<Omit<StandardizedMaterial, 'id' | 'created_at'>>,
): Promise<void> {
  const supabase = getSupabaseClient();
  const currentUserId = await getCurrentUserId(supabase);
  const updatePayload: Partial<StandardizedResultUpsertRow> = {};

  if (updates.source_type !== undefined) updatePayload.source_type = updates.source_type;
  if (updates.invoice_type !== undefined) updatePayload.invoice_type = updates.invoice_type;
  if (updates.original_name !== undefined) updatePayload.original_name = updates.original_name;
  if (updates.original_spec !== undefined) updatePayload.original_spec = updates.original_spec;
  if (updates.standardized_name !== undefined) updatePayload.standardized_name = updates.standardized_name;
  if (updates.category !== undefined) updatePayload.category = updates.category;
  if (updates.brand !== undefined) updatePayload.brand = updates.brand;
  if (updates.notes !== undefined) updatePayload.notes = updates.notes;
  if (updates.delivery_address !== undefined) updatePayload.delivery_address = updates.delivery_address;
  if (updates.primary_search_term !== undefined) updatePayload.primary_search_term = updates.primary_search_term;
  if (updates.alt_search_terms !== undefined) updatePayload.alt_search_terms = dedupeSearchTerms(updates.alt_search_terms);
  if (updates.standardized_spec !== undefined) updatePayload.standardized_spec = updates.standardized_spec;
  if (updates.standardized_unit !== undefined) updatePayload.standardized_unit = updates.standardized_unit;
  if (updates.quantity !== undefined) updatePayload.quantity = updates.quantity;
  if (updates.product_type !== undefined) updatePayload.product_type = updates.product_type;
  if (updates.llm_model !== undefined) updatePayload.llm_model = updates.llm_model;
  if (updates.ai_reasoning !== undefined) updatePayload.ai_reasoning = updates.ai_reasoning;
  if (currentUserId !== undefined) updatePayload.updated_by = currentUserId;

  const { error } = await supabase
    .from('standardized_results')
    .update(updatePayload)
    .eq('id', id);

  if (error) {
    throw new Error(error.message);
  }
}

export async function deleteStandardizedByIds(ids: string[]): Promise<void> {
  if (ids.length === 0) {
    return;
  }

  const supabase = getSupabaseClient();
  const { data, error } = await supabase
    .from('standardized_results')
    .select('material_id')
    .in('id', ids);

  if (error) {
    throw new Error(error.message);
  }

  const materialIds = [...new Set((data ?? []).map((row) => String((row as { material_id: string }).material_id)).filter(Boolean))];
  await deleteRelatedSearchArtifactsByMaterialIds(supabase, materialIds);

  const { error: deleteError } = await supabase
    .from('standardized_results')
    .delete()
    .in('id', ids);

  if (deleteError) {
    throw new Error(deleteError.message);
  }
}

export async function deleteStandardizationFailuresByIds(ids: string[]): Promise<void> {
  if (ids.length === 0) {
    return;
  }

  const supabase = getSupabaseClient();
  const updatedAt = new Date().toISOString();

  const { error: unlinkError } = await supabase
    .from('standardization_task_items')
    .update({ retry_of_item_id: null, updated_at: updatedAt })
    .in('retry_of_item_id', ids);

  if (unlinkError) {
    throw new Error(unlinkError.message);
  }

  const { error } = await supabase
    .from('standardization_task_items')
    .delete()
    .eq('status', 'failed')
    .in('id', ids);

  if (error) {
    throw new Error(error.message);
  }
}

export async function importStandardizedRows(rows: ParsedStandardizedImportRow[]): Promise<ImportStandardizedResult> {
  if (rows.length === 0) {
    return { importedCount: 0, batchIds: [], materialIds: [] };
  }

  const meaningfulRows = rows.filter((row) => hasMeaningfulImportedField(row) || Boolean(row.material_id));
  if (meaningfulRows.length === 0) {
    return { importedCount: 0, batchIds: [], materialIds: [] };
  }

  const supabase = getSupabaseClient();
  const batchIds = [...new Set(meaningfulRows.map((row) => row.batch_id).filter(Boolean))];
  const currentUserId = await getCurrentUserId(supabase);

  const [{ data: materialData, error: materialError }, { data: standardizedData, error: standardizedError }] = await Promise.all([
    supabase
      .from('procurement_materials')
      .select('id, batch_id, material_name, specification, quantity, unit, invoice_type, brand, remarks, delivery_address, created_at, created_by')
      .in('batch_id', batchIds),
    supabase
      .from('standardized_results')
      .select('*')
      .in('batch_id', batchIds),
  ]);

  if (materialError) {
    throw new Error(`读取采购物料失败：${materialError.message}`);
  }

  if (standardizedError) {
    throw new Error(`读取标准化成功数据失败：${standardizedError.message}`);
  }

  const materialsByBatch = new Map<string, ProcurementMaterialReferenceRow[]>();
  for (const material of (materialData ?? []) as ProcurementMaterialReferenceRow[]) {
    const rowsInBatch = materialsByBatch.get(material.batch_id) ?? [];
    rowsInBatch.push(material);
    materialsByBatch.set(material.batch_id, rowsInBatch);
  }

  const materialMap = new Map(
    ((materialData ?? []) as ProcurementMaterialReferenceRow[]).map((material) => [material.id, material]),
  );
  const existingStandardizedMap = new Map(
    ((standardizedData ?? []) as StandardizedResultRow[]).map((row) => [row.material_id, mapStandardizedRow(row, materialMap.get(row.material_id))]),
  );

  const upsertRows = meaningfulRows.map((row) => {
    const material = resolveImportedMaterial(row, materialsByBatch);
    return buildImportedStandardizedRow({
      importRow: row,
      material,
      existing: existingStandardizedMap.get(material.id),
      currentUserId,
    });
  });

  const { data: upsertedData, error: upsertError } = await supabase
    .from('standardized_results')
    .upsert(upsertRows, { onConflict: 'batch_id,material_id' })
    .select('batch_id, material_id');

  if (upsertError) {
    throw new Error(`写入标准化成功数据失败：${upsertError.message}`);
  }

  return {
    importedCount: upsertRows.length,
    batchIds: [...new Set((upsertedData ?? []).map((row) => String((row as { batch_id: string }).batch_id)).filter(Boolean))],
    materialIds: [...new Set((upsertedData ?? []).map((row) => String((row as { material_id: string }).material_id)).filter(Boolean))],
  };
}
