import fs from 'fs';
import XLSX from 'xlsx';

type RowObject = Record<string, any>;

export function normalizeHeader(h: string) {
  return (h || '').toString().trim().toLowerCase().replace(/\s+/g, ' ');
}

export function findHeaderIndex(headers: string[], patterns: string[]) {
  const p = patterns.map((t) => t.toLowerCase());
  for (let i = 0; i < headers.length; i++) {
    const h = normalizeHeader(headers[i]);
    for (const pat of p) if (h.includes(pat)) return i;
  }
  return -1;
}

export function getCellSafe(row: RowObject, key: string) {
  return row[key] !== undefined && row[key] !== null ? row[key] : '';
}

export function toNumberSafe(v: any): number | null {
  if (v === undefined || v === null || v === '') return null;
  const n = Number(String(v).replace(/[^\d.-]/g, ''));
  return Number.isFinite(n) ? n : null;
}


// ---------- Excel reading ----------
export function readExcelFile(filePath: string, sheetNumber = 2): RowObject[] {
  if (!fs.existsSync(filePath)) throw new Error(`File not found: ${filePath}`);
  const wb = XLSX.readFile(filePath);
  const firstSheetName = wb.SheetNames[sheetNumber];
  const ws = wb.Sheets[firstSheetName];
  // Convert to JSON with raw values
  const json = XLSX.utils.sheet_to_json(ws, { defval: '' }) as RowObject[];
  return json;
}