/**
 * Train product mappings from historical EDI files.
 *
 * Processes all EDI files in data/samples/, extracts unique article codes,
 * and runs the matching pipeline (L1 exact → L2 keyword → L3 Claude API)
 * to populate product_mappings for future instant lookups.
 *
 * Usage: npx tsx src/scripts/train-mappings.ts
 */

// Load env vars BEFORE any other imports (supabase.ts reads env at module load)
import * as dotenv from 'dotenv';
dotenv.config({ path: '.env.local' });
dotenv.config(); // fallback to .env

import * as fs from 'fs';
import * as path from 'path';
import * as XLSX from 'xlsx';
import { loadCaches, matchProduct } from '../lib/matching';
import { Supplier, ParsedEDILine } from '../types';

// ===== PARSERS (inline to avoid Next.js import issues) =====

function parseSigniaEDI(buffer: ArrayBuffer): ParsedEDILine[] {
  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, { defval: '' });
  const lines: ParsedEDILine[] = [];
  for (const row of rows) {
    const famille = String(row['Famille'] ?? '').trim();
    if (famille !== 'PR') continue;
    const sign = String(row['Signe'] ?? '+').trim();
    if (sign === '-') continue;
    const priceRaw = row['PrixUnitaireNet'];
    const unitPrice = typeof priceRaw === 'number' ? priceRaw : parseFloat(String(priceRaw).replace(',', '.'));
    if (isNaN(unitPrice) || unitPrice <= 0) continue;
    const quantity = Number(row['Quantité']) || 1;
    lines.push({
      centre_code: String(row['CompteClient'] ?? '').trim(),
      centre_name: String(row['NomCentre'] ?? '').trim(),
      code_article: String(row['CodeArticle'] ?? '').trim(),
      description: String(row['DescriptionArticle'] ?? '').trim(),
      quantity,
      unit_price: Math.round(unitPrice * 100) / 100,
      discount_pct: 0,
      invoice_number: String(row['NuméroFacture'] ?? '').trim(),
      invoice_date: String(row['DateFacture'] ?? '').trim(),
      sign,
      famille,
    });
  }
  return lines;
}

function parseResoundEDI(buffer: ArrayBuffer): ParsedEDILine[] {
  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, { defval: '' });
  const lines: ParsedEDILine[] = [];
  for (const row of rows) {
    const famille = String(row['Famille'] ?? '').trim();
    if (famille !== 'CONTOUR') continue;
    const sign = String(row['Signe'] ?? '+').trim();
    if (sign === '-') continue;
    const priceRaw = row['PrixUnitaireBrut'];
    const unitPrice = typeof priceRaw === 'number' ? priceRaw : parseFloat(String(priceRaw).replace(',', '.'));
    if (isNaN(unitPrice) || unitPrice <= 0) continue;
    const discount = parseFloat(String(row['Remise%'] ?? '0').replace(',', '.')) || 0;
    const actualPrice = discount > 0 ? Math.round(unitPrice * (1 - discount / 100) * 100) / 100 : Math.round(unitPrice * 100) / 100;
    if (actualPrice <= 0) continue;
    const quantity = Number(row['Quantité']) || 1;
    lines.push({
      centre_code: String(row['CompteClient'] ?? '').trim(),
      centre_name: String(row['NomCentre'] ?? '').trim(),
      code_article: String(row['CodeArticle'] ?? '').trim(),
      description: String(row['DescriptionArticle'] ?? '').trim(),
      quantity,
      unit_price: actualPrice,
      discount_pct: discount,
      invoice_number: String(row['NuméroFacture'] ?? '').trim(),
      invoice_date: String(row['DateFacture'] ?? '').trim(),
      sign,
      famille,
    });
  }
  return lines;
}

function parseStarkeyEDI(csvText: string): ParsedEDILine[] {
  const lines: ParsedEDILine[] = [];
  const rawLines = csvText.split('\n');
  let currentCentreCode = '';
  for (const rawLine of rawLines) {
    const trimmed = rawLine.trim();
    if (!trimmed) continue;
    const cols = trimmed.split(';');
    if (cols.length < 14) continue;
    const famille = (cols[6] ?? '').trim();
    if (famille !== 'PR') continue;
    const typeFacture = (cols[2] ?? '').trim();
    if (typeFacture === 'AV') continue;
    const centreCode = (cols[0] ?? '').trim();
    if (centreCode) currentCentreCode = centreCode;
    const discountRaw = parseFloat((cols[10] ?? '0').trim()) || 0;
    const discountPct = discountRaw / 100;
    const netPrice = parseFloat((cols[13] ?? '0').trim().replace(',', '.')) || 0;
    if (netPrice <= 0) continue;
    const quantity = parseInt((cols[9] ?? '1').trim()) || 1;
    const unitPrice = quantity > 0 ? Math.round((netPrice / quantity) * 100) / 100 : netPrice;
    lines.push({
      centre_code: currentCentreCode,
      centre_name: '',
      code_article: (cols[7] ?? '').trim(),
      description: (cols[8] ?? '').trim(),
      quantity,
      unit_price: unitPrice,
      discount_pct: discountPct,
      invoice_number: (cols[3] ?? '').trim(),
      invoice_date: (cols[4] ?? '').trim(),
      sign: '+',
      famille,
    });
  }
  return lines;
}

// ===== EDI FILE DISCOVERY =====

interface EDIFile {
  supplier: Supplier;
  month: string;
  filePath: string;
}

function discoverEDIFiles(): EDIFile[] {
  const basePath = path.join(process.cwd(), 'data/samples/Factures fournisseurs');
  const files: EDIFile[] = [];

  for (const yearDir of fs.readdirSync(basePath)) {
    const yearPath = path.join(basePath, yearDir);
    if (!fs.statSync(yearPath).isDirectory()) continue;

    for (const monthDir of fs.readdirSync(yearPath)) {
      const monthPath = path.join(yearPath, monthDir);
      if (!fs.statSync(monthPath).isDirectory()) continue;

      for (const file of fs.readdirSync(monthPath)) {
        const filePath = path.join(monthPath, file);
        let supplier: Supplier | null = null;
        if (/signia/i.test(file)) supplier = 'signia';
        else if (/resound/i.test(file)) supplier = 'resound';
        else if (/starkey/i.test(file)) supplier = 'starkey';
        if (supplier) {
          files.push({ supplier, month: monthDir, filePath });
        }
      }
    }
  }

  return files.sort((a, b) => a.month.localeCompare(b.month));
}

// ===== MAIN =====

async function main() {
  console.log('=== TRAIN MAPPINGS — Starting ===\n');

  // Verify Supabase connectivity by loading caches for one supplier
  console.log('Checking Supabase connection...');
  try {
    await loadCaches('signia');
  } catch (e) {
    console.error('FATAL: Cannot connect to Supabase:', e instanceof Error ? e.message : e);
    console.error('Make sure .env.local has valid NEXT_PUBLIC_SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY');
    process.exit(1);
  }
  // loadCaches logs the count — if it shows "0 mappings, 0 prices", connection failed silently
  console.log('(If above shows 0 mappings and 0 prices, the connection may have failed)\n');

  const ediFiles = discoverEDIFiles();
  console.log(`Found ${ediFiles.length} EDI files:\n`);
  for (const f of ediFiles) {
    console.log(`  ${f.month} ${f.supplier} — ${path.basename(f.filePath)}`);
  }
  console.log('');

  // Collect unique codes per supplier
  const uniqueCodes: Map<Supplier, Map<string, { description: string; unitPrice: number }>> = new Map();
  uniqueCodes.set('signia', new Map());
  uniqueCodes.set('resound', new Map());
  uniqueCodes.set('starkey', new Map());

  let totalLines = 0;

  for (const ediFile of ediFiles) {
    let lines: ParsedEDILine[];

    if (ediFile.supplier === 'starkey') {
      const csvText = fs.readFileSync(ediFile.filePath, 'latin1');
      lines = parseStarkeyEDI(csvText);
    } else {
      const buffer = fs.readFileSync(ediFile.filePath);
      const arrayBuffer = buffer.buffer.slice(buffer.byteOffset, buffer.byteOffset + buffer.byteLength);
      if (ediFile.supplier === 'signia') {
        lines = parseSigniaEDI(arrayBuffer);
      } else {
        lines = parseResoundEDI(arrayBuffer);
      }
    }

    totalLines += lines.length;
    const supplierMap = uniqueCodes.get(ediFile.supplier)!;
    for (const line of lines) {
      if (!supplierMap.has(line.code_article)) {
        supplierMap.set(line.code_article, {
          description: line.description,
          unitPrice: line.unit_price,
        });
      }
    }
  }

  console.log(`Total EDI lines parsed: ${totalLines}\n`);

  // Stats
  const stats: Record<Supplier, { total: 0; exact: 0; keyword: 0; claude: 0; unmatched: 0 }> = {
    signia: { total: 0, exact: 0, keyword: 0, claude: 0, unmatched: 0 },
    resound: { total: 0, exact: 0, keyword: 0, claude: 0, unmatched: 0 },
    starkey: { total: 0, exact: 0, keyword: 0, claude: 0, unmatched: 0 },
  };

  const unmatchedList: { supplier: Supplier; code: string; description: string }[] = [];
  let claudeApiCalls = 0;

  // Process each supplier
  for (const supplier of ['signia', 'resound', 'starkey'] as Supplier[]) {
    const codes = uniqueCodes.get(supplier)!;
    if (codes.size === 0) continue;

    console.log(`\n--- ${supplier.toUpperCase()} — ${codes.size} unique codes ---\n`);

    // Load caches (no date filter — use latest prices)
    await loadCaches(supplier);

    // Process codes: L1+L2 first, collect L3 candidates
    for (const [code, { description, unitPrice }] of Array.from(codes.entries())) {
      stats[supplier].total++;

      // Try L1 + L2 (matchProduct without Claude API would need internal access)
      // Instead, call matchProduct which already handles L1→L2→L3
      // But to batch L3, we first try L1+L2 by calling matchProduct with a
      // modified approach: just call it — it will do L1→L2→L3 sequentially
      // The first run for each code will populate product_mappings
      const result = await matchProduct(supplier, code, description, unitPrice);

      stats[supplier][result.match_method]++;

      if (result.match_method === 'claude') {
        claudeApiCalls++;
      }

      if (result.match_method === 'unmatched') {
        unmatchedList.push({ supplier, code, description });
      }
    }
  }

  // ===== REPORT =====
  console.log('\n\n========================================');
  console.log('       TRAINING REPORT');
  console.log('========================================\n');

  console.log(`Files processed: ${ediFiles.length}`);
  console.log(`Total EDI lines: ${totalLines}`);
  console.log('');

  let grandTotal = 0;
  let grandExact = 0;
  let grandKeyword = 0;
  let grandClaude = 0;
  let grandUnmatched = 0;

  for (const supplier of ['signia', 'resound', 'starkey'] as Supplier[]) {
    const s = stats[supplier];
    grandTotal += s.total;
    grandExact += s.exact;
    grandKeyword += s.keyword;
    grandClaude += s.claude;
    grandUnmatched += s.unmatched;

    console.log(`${supplier.toUpperCase()}:`);
    console.log(`  Codes uniques: ${s.total}`);
    console.log(`  L1 exact:      ${s.exact} (${s.total > 0 ? ((s.exact / s.total) * 100).toFixed(1) : 0}%)`);
    console.log(`  L2 keyword:    ${s.keyword} (${s.total > 0 ? ((s.keyword / s.total) * 100).toFixed(1) : 0}%)`);
    console.log(`  L3 Claude API: ${s.claude} (${s.total > 0 ? ((s.claude / s.total) * 100).toFixed(1) : 0}%)`);
    console.log(`  Unmatched:     ${s.unmatched} (${s.total > 0 ? ((s.unmatched / s.total) * 100).toFixed(1) : 0}%)`);
    console.log('');
  }

  console.log('TOTAL:');
  console.log(`  Codes uniques: ${grandTotal}`);
  console.log(`  L1 exact:      ${grandExact} (${((grandExact / grandTotal) * 100).toFixed(1)}%)`);
  console.log(`  L2 keyword:    ${grandKeyword} (${((grandKeyword / grandTotal) * 100).toFixed(1)}%)`);
  console.log(`  L3 Claude API: ${grandClaude} (${((grandClaude / grandTotal) * 100).toFixed(1)}%)`);
  console.log(`  Unmatched:     ${grandUnmatched} (${((grandUnmatched / grandTotal) * 100).toFixed(1)}%)`);
  console.log('');

  console.log(`Claude API calls: ${claudeApiCalls}`);
  console.log(`Estimated cost:   ~${(claudeApiCalls * 0.003).toFixed(2)}€`);
  console.log('');

  if (unmatchedList.length > 0) {
    console.log(`UNMATCHED PRODUCTS (${unmatchedList.length}):`);
    for (const u of unmatchedList) {
      console.log(`  [${u.supplier}] ${u.code} — ${u.description}`);
    }
  }

  console.log('\n=== DONE ===');
}

main().catch((err) => {
  console.error('Fatal error:', err);
  process.exit(1);
});