import * as XLSX from 'xlsx';
import { normalizeEmptyFieldText } from '@/lib/emptyFieldPlaceholders';

export type ParsedStandardizedImportField =
  | 'batch_id'
  | 'material_id'
  | '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';

export interface ParsedStandardizedImportRow {
  batch_id: string;
  material_id: string;
  invoice_type: string;
  original_name: string;
  standardized_name: string;
  original_spec: string;
  standardized_spec: string;
  brand: string;
  category: string;
  quantity: number;
  standardized_unit: string;
  notes: string;
  delivery_address: string;
  primary_search_term: string;
  alt_search_terms: string[];
  product_type: string;
  llm_model: string;
  ai_reasoning: string;
  provided_fields: ParsedStandardizedImportField[];
}

export interface ParsedStandardizedImportResult {
  rows: ParsedStandardizedImportRow[];
  skippedCount: number;
}

const FIELD_ALIASES = {
  batch_id: ['批次ID', 'batch_id', 'batchid'],
  material_id: ['物料ID', '物料ID(material_id)', 'material_id', 'source_material_id', '采购物料ID'],
  invoice_type: ['发票种类', '发票类型', 'invoice_type'],
  original_name: ['原始名称', '原名称', '名称', '材料名称', '物料名称', 'original_name'],
  standardized_name: ['标准化名称', '标准名称', 'standardized_name'],
  original_spec: ['原始规格', '原规格', '规格型号', '规格', 'original_spec'],
  standardized_spec: ['标准化规格', '标准规格', '简化规格', 'standardized_spec'],
  brand: ['品牌', 'brand'],
  category: ['分类(材质/用途)', '分类', '材质用途', 'category'],
  quantity: ['数量', 'quantity'],
  standardized_unit: ['单位', '标准化单位', 'unit', 'standardized_unit'],
  notes: ['备注/附件', '备注', 'notes'],
  delivery_address: ['收货地址', '交货地址', 'delivery_address'],
  primary_search_term: ['主搜索词', '搜索词', '建议关键词', 'primary_search_term'],
  alt_search_terms: ['备选搜索词', '备用搜索词', 'alt_search_terms'],
  product_type: ['产品类型', 'product_type'],
  llm_model: ['LLM模型', '模型', 'llm_model'],
  ai_reasoning: ['AI分析说明', 'AI推理说明', 'ai_reasoning'],
} as const;

type ParsedSheetRow = {
  record: Record<string, unknown>;
};

const IMPORT_FIELD_KEYS: ParsedStandardizedImportField[] = [
  'batch_id',
  'material_id',
  '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 normalizeHeader(value: string) {
  return value
    .trim()
    .toLowerCase()
    .replace(/[：:；;，,。.、*]/g, '')
    .replace(/[\s\r\n\t_（）()【】[\]/\\-]/g, '');
}

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

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

  const text = toText(value).replace(/,/g, '');
  const match = text.match(/-?\d+(?:\.\d+)?/);
  if (!match) {
    return 0;
  }

  const parsed = Number(match[0]);
  return Number.isFinite(parsed) ? parsed : 0;
}

function hasMeaningfulValue(row: unknown[]) {
  return row.some(cell => toText(cell) !== '');
}

function includesAlias(cells: string[], aliases: readonly string[]) {
  return aliases.some(alias => {
    const normalizedAlias = normalizeHeader(alias);
    return cells.some(cell => cell === normalizedAlias || cell.includes(normalizedAlias) || normalizedAlias.includes(cell));
  });
}

function detectHeaderRowIndex(rows: unknown[][]) {
  let bestIndex = -1;
  let bestScore = 0;

  rows.slice(0, 10).forEach((row, index) => {
    const normalizedCells = row.map(cell => normalizeHeader(toText(cell))).filter(Boolean);
    if (normalizedCells.length === 0) {
      return;
    }

    let score = 0;
    Object.values(FIELD_ALIASES).forEach(aliases => {
      if (includesAlias(normalizedCells, aliases)) {
        score += 1;
      }
    });

    const hasKeyField = includesAlias(normalizedCells, FIELD_ALIASES.material_id)
      || includesAlias(normalizedCells, FIELD_ALIASES.original_name)
      || includesAlias(normalizedCells, FIELD_ALIASES.standardized_name);

    if (hasKeyField && score > bestScore) {
      bestIndex = index;
      bestScore = score;
    }
  });

  return bestScore >= 2 ? bestIndex : -1;
}

function buildRowsFromSheet(sheet: XLSX.WorkSheet) {
  const rawRows = XLSX.utils.sheet_to_json<unknown[]>(sheet, {
    header: 1,
    defval: '',
    blankrows: false,
  });

  const headerRowIndex = detectHeaderRowIndex(rawRows);
  if (headerRowIndex < 0) {
    return [] as ParsedSheetRow[];
  }

  const headerRow = rawRows[headerRowIndex].map(cell => toText(cell));
  const rows = rawRows.slice(headerRowIndex + 1).filter(hasMeaningfulValue);

  return rows.map((row) => ({
    record: Object.fromEntries(headerRow.map((header, columnIndex) => [header || `column_${columnIndex}`, row[columnIndex] ?? ''])),
  }));
}

function buildNormalizedRow(row: Record<string, unknown>) {
  const normalized = new Map<string, unknown>();

  Object.entries(row).forEach(([key, value]) => {
    normalized.set(normalizeHeader(key), value);
  });

  return normalized;
}

function pickValue(normalizedRow: Map<string, unknown>, aliases: readonly string[]) {
  for (const alias of aliases) {
    const value = normalizedRow.get(normalizeHeader(alias));
    if (value != null && toText(value) !== '') {
      return value;
    }
  }

  return '';
}

function hasAlias(normalizedRow: Map<string, unknown>, aliases: readonly string[]) {
  return aliases.some(alias => normalizedRow.has(normalizeHeader(alias)));
}

function splitTerms(value: unknown) {
  return toText(value)
    .split(/[\n,，;；]+/)
    .map(item => item.trim())
    .filter(Boolean);
}

function mapRowToStandardizedImportRow(row: ParsedSheetRow, defaultBatchId?: string): ParsedStandardizedImportRow | null {
  const normalizedRow = buildNormalizedRow(row.record);
  const providedFields = IMPORT_FIELD_KEYS.filter(field => hasAlias(normalizedRow, FIELD_ALIASES[field]));
  const batchId = toText(pickValue(normalizedRow, FIELD_ALIASES.batch_id)) || defaultBatchId?.trim() || '';
  const materialId = toText(pickValue(normalizedRow, FIELD_ALIASES.material_id));
  const standardizedName = toText(pickValue(normalizedRow, FIELD_ALIASES.standardized_name));
  const originalName = toText(pickValue(normalizedRow, FIELD_ALIASES.original_name));

  if (!batchId || (!materialId && !standardizedName && !originalName)) {
    return null;
  }

  return {
    batch_id: batchId,
    material_id: materialId,
    invoice_type: toText(pickValue(normalizedRow, FIELD_ALIASES.invoice_type)),
    original_name: originalName,
    standardized_name: standardizedName,
    original_spec: toText(pickValue(normalizedRow, FIELD_ALIASES.original_spec)),
    standardized_spec: toText(pickValue(normalizedRow, FIELD_ALIASES.standardized_spec)),
    brand: toText(pickValue(normalizedRow, FIELD_ALIASES.brand)),
    category: toText(pickValue(normalizedRow, FIELD_ALIASES.category)),
    quantity: toNumber(pickValue(normalizedRow, FIELD_ALIASES.quantity)),
    standardized_unit: toText(pickValue(normalizedRow, FIELD_ALIASES.standardized_unit)),
    notes: toText(pickValue(normalizedRow, FIELD_ALIASES.notes)),
    delivery_address: toText(pickValue(normalizedRow, FIELD_ALIASES.delivery_address)),
    primary_search_term: toText(pickValue(normalizedRow, FIELD_ALIASES.primary_search_term)),
    alt_search_terms: splitTerms(pickValue(normalizedRow, FIELD_ALIASES.alt_search_terms)),
    product_type: toText(pickValue(normalizedRow, FIELD_ALIASES.product_type)),
    llm_model: toText(pickValue(normalizedRow, FIELD_ALIASES.llm_model)),
    ai_reasoning: toText(pickValue(normalizedRow, FIELD_ALIASES.ai_reasoning)),
    provided_fields: providedFields,
  };
}

export async function parseStandardizedImportFile(file: File, defaultBatchId?: string): Promise<ParsedStandardizedImportResult> {
  const workbook = XLSX.read(await file.arrayBuffer(), { type: 'array' });
  const rows: ParsedStandardizedImportRow[] = [];
  let skippedCount = 0;

  workbook.SheetNames.forEach(sheetName => {
    const sheet = workbook.Sheets[sheetName];
    if (!sheet) {
      return;
    }

    buildRowsFromSheet(sheet).forEach(row => {
      const mapped = mapRowToStandardizedImportRow(row, defaultBatchId);
      if (mapped) {
        rows.push(mapped);
      } else {
        skippedCount += 1;
      }
    });
  });

  return { rows, skippedCount };
}