import { NextRequest, NextResponse } from 'next/server';
import { getSupabaseAdmin } from '@/lib/supabase';
import * as XLSX from 'xlsx';

interface ExtractedPrice {
  supplier: string;
  product_name: string;
  category: string;
  price_ht: number;
}

interface ChangeDetection {
  unchanged: ExtractedPrice[];
  modified: { old: { product_name: string; price_ht: number; id: string }; new: ExtractedPrice }[];
  added: ExtractedPrice[];
  removed: { product_name: string; price_ht: number; id: string }[];
}

// Category sections in the "Valo inventaire" sheet
const SECTIONS = [
  { supplierCol: 0, nameCol: 1, priceCol: 3, category: 'RIC' },
  { supplierCol: 6, nameCol: 7, priceCol: 9, category: 'Intra' },
  { supplierCol: 12, nameCol: 13, priceCol: 15, category: 'BTE' },
  { supplierCol: 18, nameCol: 19, priceCol: 21, category: 'Accessoire' },
  { supplierCol: 24, nameCol: 25, priceCol: 27, category: 'Entretien' },
];

const SUPPLIER_ALIASES: Record<string, string> = {
  signia: 'signia',
  resound: 'resound',
  starkey: 'starkey',
  biotone: 'biotone',
  'gn hearing': 'resound',
  'gn resound': 'resound',
  'ws audiology': 'signia',
};

function normalizeSupplier(raw: string): string | null {
  const lower = raw.toLowerCase().trim();
  for (const [key, val] of Object.entries(SUPPLIER_ALIASES)) {
    if (lower.includes(key)) return val;
  }
  return null;
}

function parseValoInventaire(buffer: ArrayBuffer): ExtractedPrice[] {
  const workbook = XLSX.read(buffer, { type: 'array' });

  // Find the "Valo inventaire" sheet (or similar name)
  const sheetName = workbook.SheetNames.find(
    (s) => s.toLowerCase().includes('valo') || s.toLowerCase().includes('inventaire')
  );
  if (!sheetName) {
    throw new Error('Onglet "Valo inventaire" introuvable. Feuilles disponibles : ' + workbook.SheetNames.join(', '));
  }

  const sheet = workbook.Sheets[sheetName];
  const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(sheet, {
    header: 1,
    defval: null,
  }) as unknown as (unknown[] | null)[];

  const extracted: ExtractedPrice[] = [];

  // Data starts at row index 4 (0-based), header is row 3
  for (let rowIdx = 4; rowIdx < rows.length; rowIdx++) {
    const row = rows[rowIdx];
    if (!row) continue;

    for (const section of SECTIONS) {
      const supplierRaw = row[section.supplierCol];
      const productName = row[section.nameCol];
      const priceRaw = row[section.priceCol];

      if (!productName || !priceRaw) continue;

      const name = String(productName).trim();
      if (!name || name.toLowerCase().startsWith('total')) continue;

      const price = typeof priceRaw === 'number' ? priceRaw : parseFloat(String(priceRaw).replace(',', '.'));
      if (isNaN(price) || price < 0) continue;

      const supplier = normalizeSupplier(String(supplierRaw ?? ''));
      if (!supplier) continue;

      extracted.push({
        supplier,
        product_name: name,
        category: section.category,
        price_ht: Math.round(price * 100) / 100,
      });
    }
  }

  return extracted;
}

// POST: Upload tariff file, parse directly, detect changes
export async function POST(req: NextRequest) {
  try {
    const formData = await req.formData();
    const file = formData.get('file') as File | null;
    const action = formData.get('action') as string | null;
    const effectiveDate = formData.get('effectiveDate') as string | null;

    if (action === 'validate') {
      const changesJson = formData.get('changes') as string;
      if (!changesJson) {
        return NextResponse.json({ error: 'changes requis' }, { status: 400 });
      }

      const changes: ChangeDetection = JSON.parse(changesJson);
      const supabase = getSupabaseAdmin();
      const validFrom = effectiveDate || new Date().toISOString().split('T')[0];

      // Calculate valid_to for old prices: day before effective date
      const effectiveDateObj = new Date(validFrom);
      effectiveDateObj.setDate(effectiveDateObj.getDate() - 1);
      const validTo = effectiveDateObj.toISOString().split('T')[0];

      // Process modified prices: close old, create new
      for (const { old: oldPrice, new: newPrice } of changes.modified) {
        await supabase
          .from('reference_prices')
          .update({ valid_to: validTo })
          .eq('id', oldPrice.id);

        await supabase.from('reference_prices').insert({
          supplier: newPrice.supplier.toLowerCase(),
          product_name: newPrice.product_name,
          category: newPrice.category,
          price_ht: newPrice.price_ht,
          valid_from: validFrom,
          valid_to: null,
        });
      }

      // Process new products
      if (changes.added.length > 0) {
        await supabase.from('reference_prices').insert(
          changes.added.map((p) => ({
            supplier: p.supplier.toLowerCase(),
            product_name: p.product_name,
            category: p.category,
            price_ht: p.price_ht,
            valid_from: validFrom,
            valid_to: null,
          }))
        );
      }

      // Removed products: do nothing — they stay in the DB for historical analyses

      return NextResponse.json({
        success: true,
        modified: changes.modified.length,
        added: changes.added.length,
      });
    }

    // Default action: extract — parse Excel directly (no Claude API needed)
    if (!file) {
      return NextResponse.json({ error: 'Fichier requis' }, { status: 400 });
    }

    const arrayBuffer = await file.arrayBuffer();
    const extracted = parseValoInventaire(arrayBuffer);

    if (extracted.length === 0) {
      return NextResponse.json({ error: 'Aucun produit extrait du fichier' }, { status: 400 });
    }

    // Compare with existing active prices
    const supabase = getSupabaseAdmin();
    const { data: existing } = await supabase
      .from('reference_prices')
      .select('*')
      .is('valid_to', null);

    const changes: ChangeDetection = {
      unchanged: [],
      modified: [],
      added: [],
      removed: [],
    };

    const matchedExistingIds = new Set<string>();

    for (const newPrice of extracted) {
      const match = (existing ?? []).find(
        (e) =>
          e.supplier === newPrice.supplier &&
          e.product_name.toLowerCase() === newPrice.product_name.toLowerCase()
      );

      if (match) {
        matchedExistingIds.add(match.id);
        if (Math.abs(match.price_ht - newPrice.price_ht) > 0.01) {
          changes.modified.push({
            old: { product_name: match.product_name, price_ht: match.price_ht, id: match.id },
            new: newPrice,
          });
        } else {
          changes.unchanged.push(newPrice);
        }
      } else {
        changes.added.push(newPrice);
      }
    }

    // Products in DB but not in new file — "conserved" (old models still in use)
    for (const e of existing ?? []) {
      if (!matchedExistingIds.has(e.id)) {
        changes.removed.push({
          product_name: e.product_name,
          price_ht: e.price_ht,
          id: e.id,
        });
      }
    }

    return NextResponse.json({
      extracted: extracted.length,
      changes,
    });
  } catch (error) {
    const message = error instanceof Error ? error.message : 'Erreur inconnue';
    return NextResponse.json({ error: message }, { status: 500 });
  }
}