import pool from './db';
import fs from 'fs';
import path from 'path';

export async function runMigrations() {
    console.log('--- Iniciando Migraciones ---');

    // 1. Crear tabla de control si no existe
    await pool.execute(`
    CREATE TABLE IF NOT EXISTS _migrations (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

    const migrationsDir = path.join(process.cwd(), 'migrations');
    if (!fs.existsSync(migrationsDir)) {
        fs.mkdirSync(migrationsDir);
    }

    const files = fs.readdirSync(migrationsDir).filter(f => f.endsWith('.sql')).sort();

    const [executed]: any = await pool.execute('SELECT name FROM _migrations');
    const executedNames = new Set(executed.map((m: any) => m.name));

    for (const file of files) {
        if (!executedNames.has(file)) {
            console.log(`Aplicando migración: ${file}`);
            const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf8');

            // Ejecutar cada comando por separado (si hay ;)
            const commands = sql.split(';').filter(cmd => cmd.trim());

            for (const cmd of commands) {
                await pool.execute(cmd);
            }

            await pool.execute('INSERT INTO _migrations (name) VALUES (?)', [file]);
            console.log(`Migración ${file} completada.`);
        }
    }

    console.log('--- Migraciones Finalizadas ---');
}