import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

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('=== Database Menus Migration ===\n');

try {
  // 1. Create menus table
  console.log('Creating menus table...');
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS menus (
      id VARCHAR(100) PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      description TEXT,
      courses JSON,
      sides JSON,
      tags JSON,
      image VARCHAR(255),
      featured BOOLEAN DEFAULT FALSE,
      base_ingredients_per_cover DECIMAL(10,2),
      ingredients_cost_index DECIMAL(4,2) DEFAULT 1.0,
      base_labour_hours DECIMAL(4,1),
      incremental_labour_per_cover DECIMAL(4,2),
      min_covers INT DEFAULT 10,
      lead_time_days INT DEFAULT 7,
      base_price_per_cover DECIMAL(10,2),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `);
  console.log('✓ menus table created\n');

  // 2. Create menu_ingredients table
  console.log('Creating menu_ingredients table...');
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS menu_ingredients (
      id INT AUTO_INCREMENT PRIMARY KEY,
      menu_id VARCHAR(100) NOT NULL,
      name VARCHAR(255) NOT NULL,
      quantity VARCHAR(50) NOT NULL,
      per_cover BOOLEAN DEFAULT TRUE,
      sort_order INT DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
      INDEX idx_menu_id (menu_id)
    )
  `);
  console.log('✓ menu_ingredients table created\n');

  // 3. Create packages table
  console.log('Creating packages table...');
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS packages (
      id VARCHAR(100) PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      menu_id VARCHAR(100) NOT NULL,
      price_per_cover DECIMAL(10,2),
      min_covers INT DEFAULT 10,
      max_covers INT,
      includes JSON,
      upsells JSON,
      notes TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE,
      INDEX idx_menu_id (menu_id)
    )
  `);
  console.log('✓ packages table created\n');

  console.log('✅ All tables created successfully!');
  console.log('\nNext step: Run node scripts/migrate-data-to-db.mjs to migrate existing data');

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