const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');
const path = require('path');

const DB_PATH = path.join(__dirname, 'database/ashtransfer.db');

async function runMigration() {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(DB_PATH, async (err) => {
      if (err) {
        console.error('Error opening database:', err);
        reject(err);
        return;
      }

      console.log('✓ Connected to SQLite database');
      console.log('Starting CMS and Enhanced Booking migration...\n');

      try {
        // Read the migration file
        const migrationSQL = fs.readFileSync(
          path.join(__dirname, 'database/migrations/0005_cms_and_enhanced_booking.sql'),
          'utf8'
        );

        // Split by semicolon and execute each statement
        const statements = migrationSQL
          .split(';')
          .map(s => s.trim())
          .filter(s => s.length > 0 && !s.startsWith('--'));

        let successCount = 0;
        let skipCount = 0;

        for (const statement of statements) {
          try {
            await new Promise((res, rej) => {
              db.run(statement, (err) => {
                if (err) rej(err);
                else res();
              });
            });
            successCount++;
            process.stdout.write('.');
          } catch (error) {
            // Ignore "already exists" errors
            if (error.message.includes('already exists')) {
              skipCount++;
            } else {
              console.error('\n❌ Error executing statement:', error.message);
            }
          }
        }

        console.log(`\n\n✓ CMS and Enhanced Booking migration completed!`);
        console.log(`  - ${successCount} statements executed successfully`);
        console.log(`  - ${skipCount} statements skipped (already exists)`);
        
        console.log('\n📦 New features added:');
        console.log('  ✓ CMS content management tables');
        console.log('  ✓ Vehicle management system');
        console.log('  ✓ Pricing rules management');
        console.log('  ✓ Airport information database');
        console.log('  ✓ Enhanced booking system');
        console.log('  ✓ Quote request system');
        console.log('  ✓ Driver management');
        console.log('  ✓ Image gallery system');
        console.log('  ✓ Website settings management');
        
        db.close((err) => {
          if (err) console.error('Error closing database:', err);
          else console.log('\n✓ Database connection closed');
          resolve();
        });

      } catch (error) {
        console.error('Migration failed:', error);
        db.close();
        reject(error);
      }
    });
  });
}

runMigration()
  .then(() => {
    console.log('\n🎉 Migration completed successfully!');
    process.exit(0);
  })
  .catch((error) => {
    console.error('\n❌ Migration failed:', error);
    process.exit(1);
  });
