/**
 * Database Migration Runner
 * Executes all migration files to set up database schema
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

const config = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  multipleStatements: true
};

async function runMigrations() {
  let connection;

  try {
    console.log('\n═══════════════════════════════════════════');
    console.log('🚀 WakaTraffic Database Migration Runner');
    console.log('═══════════════════════════════════════════\n');

    console.log(`📝 Connecting to database: ${process.env.DB_NAME}`);
    console.log(`🔗 Host: ${process.env.DB_HOST}\n`);

    // Create connection
    connection = await mysql.createConnection(config);
    console.log('✅ Connected to MySQL\n');

    // Get migration files
    const migrationsPath = path.join(__dirname, 'migrations');
    const migrationFiles = fs.readdirSync(migrationsPath)
      .filter(file => file.endsWith('.sql'))
      .sort();

    if (migrationFiles.length === 0) {
      console.log('⚠️  No migration files found');
      return;
    }

    console.log(`📁 Found ${migrationFiles.length} migration files\n`);

    let successCount = 0;
    let errorCount = 0;

    // Execute each migration
    for (const file of migrationFiles) {
      const filePath = path.join(migrationsPath, file);
      const fileContent = fs.readFileSync(filePath, 'utf8');

      console.log(`⏳ Running: ${file}`);

      try {
        // Execute migration
        const statements = fileContent
          .split(';')
          .map(s => s.trim())
          .filter(s => s.length > 0 && !s.startsWith('--'));

        for (const statement of statements) {
          if (statement.length > 0) {
            await connection.execute(statement);
          }
        }

        console.log(`   ✅ Success\n`);
        successCount++;
      } catch (error) {
        console.log(`   ❌ Error: ${error.message}\n`);
        errorCount++;
      }
    }

    console.log('═══════════════════════════════════════════');
    console.log('📊 Migration Results');
    console.log('═══════════════════════════════════════════');
    console.log(`✅ Successful: ${successCount}`);
    console.log(`❌ Failed: ${errorCount}`);
    console.log('═══════════════════════════════════════════\n');

    // Show table summary
    console.log('📋 Tables Created:\n');
    const [tables] = await connection.execute('SHOW TABLES');

    if (tables && tables.length > 0) {
      tables.forEach((row, index) => {
        const tableName = Object.values(row)[0];
        console.log(`   ${index + 1}. ${tableName}`);
      });
    }

    console.log('\n✅ Database setup complete!\n');

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

// Run migrations
runMigrations();
