import * as XLSX from 'xlsx';

interface BiotoneProduct {
  supplier: string;
  product_name: string;
  category: string;
  code_article: string;
  price_ht: number;
  valid_from: string;
}

export function parseBiotonePriceList(buffer: ArrayBuffer): BiotoneProduct[] {
  const workbook = XLSX.read(buffer, { type: 'array' });
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(sheet, {
    header: 1,
    defval: null,
  }) as unknown as (unknown[] | null)[];

  const products: BiotoneProduct[] = [];

  for (let rowIdx = 1; rowIdx < rows.length; rowIdx++) {
    const row = rows[rowIdx];
    if (!row) continue;

    // Section gauche — "Matériel atelier" (cols A-E = indices 0-4)
    const leftCode = row[0];
    const leftName = row[1];
    const leftPriceNet = row[4]; // Prix net 2026 (col E)

    if (leftCode && leftName && leftPriceNet) {
      const code = String(leftCode).trim();
      const name = String(leftName).trim();
      if (name && !name.toLowerCase().startsWith('total') && !name.toLowerCase().includes('désignation')) {
        const price = typeof leftPriceNet === 'number'
          ? leftPriceNet
          : parseFloat(String(leftPriceNet).replace(',', '.'));
        if (!isNaN(price) && price > 0) {
          products.push({
            supplier: 'biotone',
            product_name: name,
            category: 'Matériel atelier',
            code_article: code,
            price_ht: Math.round(price * 100) / 100,
            valid_from: '2026-01-01',
          });
        }
      }
    }

    // Section droite — "AudioLine" (cols H-O = indices 7-14)
    const rightCode = row[7];
    const rightName = row[8];
    const rightPriceNetUnit = row[13]; // Prix net unitaire (col N)

    if (rightCode && rightName && rightPriceNetUnit) {
      const code = String(rightCode).trim();
      const name = String(rightName).trim();
      if (name && !name.toLowerCase().startsWith('total') && !name.toLowerCase().includes('désignation')) {
        const price = typeof rightPriceNetUnit === 'number'
          ? rightPriceNetUnit
          : parseFloat(String(rightPriceNetUnit).replace(',', '.'));
        if (!isNaN(price) && price > 0) {
          products.push({
            supplier: 'biotone',
            product_name: name,
            category: 'Entretien',
            code_article: code,
            price_ht: Math.round(price * 100) / 100,
            valid_from: '2026-01-01',
          });
        }
      }
    }
  }

  return products;
}