/**
 * Import the Biotone 2026 plaquette into reference_prices.
 *
 * Reads data/samples/biotone-plaquette-2026.xlsx, parses it with
 * parseBiotonePriceList, expires any currently-active biotone prices
 * (valid_to → 2025-12-31), and inserts the new 222 products with
 * valid_from = 2026-01-01.
 *
 * Usage: npx tsx src/scripts/import-biotone.ts
 */

import * as dotenv from 'dotenv';
dotenv.config({ path: '.env.local' });
dotenv.config();

import * as fs from 'fs';
import * as path from 'path';
import { createClient } from '@supabase/supabase-js';
import { parseBiotonePriceList } from '../lib/parsers/biotone-prices';

const FILE_PATH = path.join(process.cwd(), 'data/samples/biotone-plaquette-2026.xlsx');
const VALID_FROM = '2026-01-01';
const EXPIRE_DATE = '2025-12-31';

async function main() {
  if (!fs.existsSync(FILE_PATH)) {
    console.error(`[import-biotone] File not found: ${FILE_PATH}`);
    process.exit(1);
  }

  const url = process.env.NEXT_PUBLIC_SUPABASE_URL;
  const serviceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
  if (!url || !serviceKey) {
    console.error('[import-biotone] Missing NEXT_PUBLIC_SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY');
    process.exit(1);
  }
  const supabase = createClient(url, serviceKey, { auth: { persistSession: false } });

  const buffer = fs.readFileSync(FILE_PATH);
  const arrayBuffer = buffer.buffer.slice(buffer.byteOffset, buffer.byteOffset + buffer.byteLength);
  const products = parseBiotonePriceList(arrayBuffer);

  const matelier = products.filter((p) => p.category === 'Matériel atelier').length;
  const entretien = products.filter((p) => p.category === 'Entretien').length;
  console.log(`[import-biotone] Parsed ${products.length} products (${matelier} Matériel atelier + ${entretien} Entretien)`);

  if (products.length === 0) {
    console.error('[import-biotone] No products extracted. Aborting.');
    process.exit(1);
  }

  const { data: existingActive, error: fetchErr } = await supabase
    .from('reference_prices')
    .select('id, product_name')
    .eq('supplier', 'biotone')
    .is('valid_to', null);

  if (fetchErr) {
    console.error('[import-biotone] Error fetching existing biotone prices:', fetchErr.message);
    process.exit(1);
  }

  if (existingActive && existingActive.length > 0) {
    console.log(`[import-biotone] Expiring ${existingActive.length} existing active biotone rows (valid_to = ${EXPIRE_DATE})`);
    const { error: expErr } = await supabase
      .from('reference_prices')
      .update({ valid_to: EXPIRE_DATE })
      .eq('supplier', 'biotone')
      .is('valid_to', null);
    if (expErr) {
      console.error('[import-biotone] Error expiring existing rows:', expErr.message);
      process.exit(1);
    }
  }

  const rows = products.map((p) => ({
    supplier: 'biotone',
    product_name: p.product_name,
    category: p.category,
    price_ht: p.price_ht,
    valid_from: VALID_FROM,
    valid_to: null,
    is_manual: false,
  }));

  let inserted = 0;
  for (let i = 0; i < rows.length; i += 100) {
    const batch = rows.slice(i, i + 100);
    const { error } = await supabase.from('reference_prices').insert(batch);
    if (error) {
      console.error(`[import-biotone] Insert batch ${i / 100} failed:`, error.message);
      process.exit(1);
    }
    inserted += batch.length;
  }

  console.log(`[import-biotone] Inserted ${inserted} rows (supplier=biotone, valid_from=${VALID_FROM})`);
}

main().catch((e) => {
  console.error('[import-biotone] Fatal:', e);
  process.exit(1);
});