import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// Load environment variables
dotenv.config({ path: path.join(__dirname, '..', '.env') });

// Database connection
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('📦 Starting image embedding process...\n');

// Read all images from public/img
const imgDir = path.join(__dirname, '..', 'public', 'img');
const imageFiles = fs.readdirSync(imgDir).filter(file => file.endsWith('.png'));

console.log(`Found ${imageFiles.length} images to process:\n`);

for (const imageFile of imageFiles) {
  const imagePath = path.join(imgDir, imageFile);
  const imageBuffer = fs.readFileSync(imagePath);
  const base64Image = `data:image/png;base64,${imageBuffer.toString('base64')}`;
  
  // Extract base name (e.g., 'klassisk.png' -> 'klassisk')
  const baseName = imageFile.replace('.png', '');
  const pngUrl = `/img/${imageFile}`;
  const jpgUrl = `/img/${baseName}.jpg`;
  
  console.log(`Processing: ${imageFile}`);
  console.log(`  Size: ${(imageBuffer.length / 1024).toFixed(2)} KB`);
  console.log(`  Base64 size: ${(base64Image.length / 1024).toFixed(2)} KB`);
  
  // Update database - find menus with either .png or .jpg path and update to base64
  const [result] = await connection.execute(
    'UPDATE menus SET image = ? WHERE image IN (?, ?)',
    [base64Image, pngUrl, jpgUrl]
  );
  
  console.log(`  Updated ${result.affectedRows} menu(s) in database\n`);
}

// Show all menus with their image status
const [menus] = await connection.execute('SELECT id, title, LEFT(image, 50) as image_preview FROM menus');
console.log('\n📋 Current menu image status:');
console.log('================================');
for (const menu of menus) {
  const isBase64 = menu.image_preview?.startsWith('data:image');
  console.log(`${menu.title.padEnd(30)} ${isBase64 ? '✅ Embedded' : '❌ Path: ' + menu.image_preview}`);
}

await connection.end();
console.log('\n✅ Image embedding complete!');