import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
import { readFile } from 'fs/promises';

dotenv.config();

// Read and parse data.ts
const dataFile = await readFile('./src/config/data.ts', 'utf-8');
const dataMatch = dataFile.match(/export const data = ({[\s\S]*?});?\s*$/m);
if (!dataMatch) {
  throw new Error('Could not parse data.ts');
}

// Simple eval to get the data object (safe in this context)
const data = eval(`(${dataMatch[1]})`);

const connection = await mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
});

console.log('=== Migrating Data to Database ===\n');

try {
  // 1. Migrate menus
  console.log(`Migrating ${data.menus.length} menus...`);
  
  for (const menu of data.menus) {
    await connection.execute(`
      INSERT INTO menus (
        id, title, description, courses, sides, tags, image, featured,
        base_ingredients_per_cover, ingredients_cost_index, base_labour_hours,
        incremental_labour_per_cover, min_covers, lead_time_days, base_price_per_cover
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        title = VALUES(title),
        description = VALUES(description),
        courses = VALUES(courses),
        sides = VALUES(sides),
        tags = VALUES(tags),
        image = VALUES(image),
        featured = VALUES(featured),
        base_ingredients_per_cover = VALUES(base_ingredients_per_cover),
        ingredients_cost_index = VALUES(ingredients_cost_index),
        base_labour_hours = VALUES(base_labour_hours),
        incremental_labour_per_cover = VALUES(incremental_labour_per_cover),
        min_covers = VALUES(min_covers),
        lead_time_days = VALUES(lead_time_days),
        base_price_per_cover = VALUES(base_price_per_cover)
    `, [
      menu.id,
      menu.title,
      menu.description,
      JSON.stringify(menu.courses),
      JSON.stringify(menu.sides || []),
      JSON.stringify(menu.tags || []),
      menu.image || null,
      menu.featured || false,
      menu.baseIngredientsPerCover || null,
      menu.ingredientsCostIndex || 1.0,
      menu.baseLabourHours || 4,
      menu.incrementalLabourPerCover || 0.05,
      menu.minCovers || 10,
      menu.leadTimeDays || 7,
      menu.basePricePerCover || 0
    ]);
    
    // Migrate ingredients for this menu
    if (menu.ingredients && menu.ingredients.length > 0) {
      // Delete existing ingredients first
      await connection.execute('DELETE FROM menu_ingredients WHERE menu_id = ?', [menu.id]);
      
      for (let i = 0; i < menu.ingredients.length; i++) {
        const ing = menu.ingredients[i];
        await connection.execute(`
          INSERT INTO menu_ingredients (menu_id, name, quantity, per_cover, sort_order)
          VALUES (?, ?, ?, ?, ?)
        `, [
          menu.id,
          ing.name,
          ing.quantity,
          ing.perCover !== false,
          i
        ]);
      }
      console.log(`  ✓ ${menu.title} (${menu.ingredients.length} ingredienser)`);
    } else {
      console.log(`  ✓ ${menu.title} (ingen ingredienser)`);
    }
  }
  
  console.log(`\n✅ ${data.menus.length} menus migreret!\n`);

  // 2. Migrate packages
  console.log(`Migrating ${data.packages.length} packages...`);
  
  for (const pkg of data.packages) {
    await connection.execute(`
      INSERT INTO packages (
        id, name, menu_id, price_per_cover, min_covers, max_covers,
        includes, upsells, notes
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        menu_id = VALUES(menu_id),
        price_per_cover = VALUES(price_per_cover),
        min_covers = VALUES(min_covers),
        max_covers = VALUES(max_covers),
        includes = VALUES(includes),
        upsells = VALUES(upsells),
        notes = VALUES(notes)
    `, [
      pkg.id,
      pkg.name,
      pkg.menuId,
      pkg.pricePerCover,
      pkg.minCovers || 10,
      pkg.maxCovers || null,
      JSON.stringify(pkg.includes || []),
      JSON.stringify(pkg.upsells || []),
      pkg.notes || null
    ]);
    console.log(`  ✓ ${pkg.name} (${pkg.menuId})`);
  }
  
  console.log(`\n✅ ${data.packages.length} packages migreret!\n`);

  // 3. Show summary
  const [menuCount] = await connection.execute('SELECT COUNT(*) as count FROM menus');
  const [ingredientCount] = await connection.execute('SELECT COUNT(*) as count FROM menu_ingredients');
  const [packageCount] = await connection.execute('SELECT COUNT(*) as count FROM packages');
  
  console.log('=== Database Summary ===');
  console.log(`Menus: ${menuCount[0].count}`);
  console.log(`Ingredients: ${ingredientCount[0].count}`);
  console.log(`Packages: ${packageCount[0].count}`);
  console.log('\n✅ Migration complete!');

} catch (error) {
  console.error('❌ Migration failed:', error);
  process.exit(1);
} finally {
  await connection.end();
}