import mysql from 'mysql2/promise';
import { data } from '../src/config/data.js';

// Database pool configuration
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10,
  idleTimeout: 60000,
  queueLimit: 0,
});

function menuToRow(menu) {
  return {
    id: menu.id,
    title: menu.title,
    description: menu.description,
    courses: JSON.stringify(menu.courses),
    tags: JSON.stringify(menu.tags),
    image: menu.image,
    base_ingredients_per_cover: menu.baseIngredientsPerCover,
    ingredients_cost_index: menu.ingredientsCostIndex,
    base_labour_hours: menu.baseLabourHours,
    incremental_labour_per_cover: menu.incrementalLabourPerCover,
    min_covers: menu.minCovers,
    lead_time_days: menu.leadTimeDays,
    base_price_per_cover: menu.basePricePerCover,
  };
}

async function createTables() {
  const connection = await pool.getConnection();
  
  try {
    // Create menus table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS menus (
        id VARCHAR(255) PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        courses JSON NOT NULL,
        tags JSON NOT NULL,
        image VARCHAR(255),
        base_ingredients_per_cover DECIMAL(10,2) NOT NULL,
        ingredients_cost_index DECIMAL(10,2) NOT NULL,
        base_labour_hours DECIMAL(10,2) NOT NULL,
        incremental_labour_per_cover DECIMAL(10,2) NOT NULL,
        min_covers INT NOT NULL,
        lead_time_days INT NOT NULL,
        base_price_per_cover DECIMAL(10,2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);

    // Check if menus table is empty
    const [result] = await connection.query('SELECT COUNT(*) as count FROM menus');
    const rows = result;
    const count = rows[0].count;

    // If empty, insert sample data
    if (count === 0) {
      console.log('Inserting sample data...');
      const processedIds = new Set();
      
      for (const menu of data.menus) {
        // Skip if we've already processed this ID
        if (processedIds.has(menu.id)) continue;
        processedIds.add(menu.id);
        
        const row = menuToRow(menu);
        await connection.query(
          'INSERT INTO menus SET ?',
          row
        );
      }
      console.log('Sample data inserted successfully');
    }

  } finally {
    connection.release();
    await pool.end();
  }
}

// Kør migrationen
createTables().then(() => {
  console.log('Database migration completed');
  process.exit(0);
}).catch((error) => {
  console.error('Error during migration:', error);
  process.exit(1);
});