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

export interface ParsedMaterialRow {
  material_name: string;
  specification: string;
  quantity: number;
  unit: string;
  technical_standard: string;
  material_requirement: string;
  technical_requirement: string;
  applicable_conditions: string;
  test_requirement: string;
  acceptance_note: string;
  warranty_period: string;
  deadline: string;
  invoice_type: string;
  brand: string;
  remarks: string;
  delivery_address: string;
}

const FIELD_ALIASES: Record<keyof ParsedMaterialRow, string[]> = {
  material_name: ['材料名称', '物料名称', '物资名称', '品名', '名称', '货物名称', '商品名称'],
  specification: ['规格型号', '规格', '型号', '规格型号参数', '规格/型号', '型号规格', '规格参数'],
  quantity: ['数量', '需求数量', '采购数量', '数目', '需求数', '采购数'],
  unit: ['单位'],
  technical_standard: ['技术标准', '执行标准', '标准'],
  material_requirement: ['材质要求', '材质', '材质说明'],
  technical_requirement: ['技术要求', '要求', '技术参数'],
  applicable_conditions: ['适用条件', '适用场景', '使用场景'],
  test_requirement: ['试验测试要求', '测试要求', '试验要求'],
  acceptance_note: ['验收说明', '验收要求'],
  warranty_period: ['质保期', '质保要求'],
  deadline: ['交货期限', '交期', '交货期'],
  invoice_type: ['发票种类', '发票类型'],
  brand: ['品牌', '品牌要求'],
  remarks: ['备注', '备注说明'],
  delivery_address: ['收货地址', '交货地址', '送货地址'],
};

function normalizeHeader(value: string) {
  return value
    .trim()
    .toLowerCase()
    .replace(/[：:；;，,。.、*]/g, '')
    .replace(/[\s\r\n\t_（）()【】[\]/\\-]/g, '');
}

function headerMatchesAlias(normalizedHeader: string, alias: string) {
  const normalizedAlias = normalizeHeader(alias);
  if (normalizedHeader === normalizedAlias) {
    return true;
  }

  return normalizedAlias.length >= 3 && normalizedHeader.includes(normalizedAlias);
}

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

function parseQuantity(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 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;
    let hasMaterialName = false;

    Object.values(FIELD_ALIASES).forEach(aliases => {
      const matched = aliases.some(alias => {
        return normalizedCells.some(cell => headerMatchesAlias(cell, alias));
      });

      if (matched) {
        score += 1;
      }
    });

    hasMaterialName = FIELD_ALIASES.material_name.some(alias => {
      return normalizedCells.some(cell => headerMatchesAlias(cell, alias));
    });

    if (hasMaterialName && 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 Record<string, unknown>[];
  }

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

  return rows
    .filter(hasMeaningfulValue)
    .map(row => {
      const entries = headerRow.map((header, index) => [header || `column_${index}`, row[index] ?? ''] as const);
      return Object.fromEntries(entries);
    });
}

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: string[]) {
  for (const alias of aliases) {
    const normalizedAlias = normalizeHeader(alias);
    const value = normalizedRow.get(normalizedAlias);
    if (value != null && toText(value) !== '') {
      return value;
    }
  }

  for (const alias of aliases) {
    const normalizedAlias = normalizeHeader(alias);
    for (const [header, value] of normalizedRow.entries()) {
      if (
        value != null
        && toText(value) !== ''
        && normalizedAlias.length >= 3
        && header.includes(normalizedAlias)
      ) {
        return value;
      }
    }
  }

  return '';
}

function mapRowToMaterial(row: Record<string, unknown>): ParsedMaterialRow | null {
  const normalizedRow = buildNormalizedRow(row);
  const materialName = toText(pickValue(normalizedRow, FIELD_ALIASES.material_name));

  if (!materialName) {
    return null;
  }

  return {
    material_name: materialName,
    specification: toText(pickValue(normalizedRow, FIELD_ALIASES.specification)),
    quantity: parseQuantity(pickValue(normalizedRow, FIELD_ALIASES.quantity)),
    unit: toText(pickValue(normalizedRow, FIELD_ALIASES.unit)),
    technical_standard: toText(pickValue(normalizedRow, FIELD_ALIASES.technical_standard)),
    material_requirement: toText(pickValue(normalizedRow, FIELD_ALIASES.material_requirement)),
    technical_requirement: toText(pickValue(normalizedRow, FIELD_ALIASES.technical_requirement)),
    applicable_conditions: toText(pickValue(normalizedRow, FIELD_ALIASES.applicable_conditions)),
    test_requirement: toText(pickValue(normalizedRow, FIELD_ALIASES.test_requirement)),
    acceptance_note: toText(pickValue(normalizedRow, FIELD_ALIASES.acceptance_note)),
    warranty_period: toText(pickValue(normalizedRow, FIELD_ALIASES.warranty_period)),
    deadline: toText(pickValue(normalizedRow, FIELD_ALIASES.deadline)),
    invoice_type: toText(pickValue(normalizedRow, FIELD_ALIASES.invoice_type)),
    brand: toText(pickValue(normalizedRow, FIELD_ALIASES.brand)),
    remarks: toText(pickValue(normalizedRow, FIELD_ALIASES.remarks)),
    delivery_address: toText(pickValue(normalizedRow, FIELD_ALIASES.delivery_address)),
  };
}

export async function parseMaterialsFromExcel(file: File) {
  const workbook = XLSX.read(await file.arrayBuffer(), { type: 'array' });
  const materials: ParsedMaterialRow[] = [];

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

    const rows = buildRowsFromSheet(sheet);

    rows.forEach(row => {
      const material = mapRowToMaterial(row);
      if (material) {
        materials.push(material);
      }
    });
  });

  return materials;
}
