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

const dbPath = path.join(__dirname, './database/ashtransfer.db');
const migrationPath = path.join(__dirname, './database/migrations/0002_blocked_dates.sql');

const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('❌ Database connection error:', err.message);
    process.exit(1);
  }
  console.log('✅ Connected to database');
});

// Read migration file
const migrationSQL = fs.readFileSync(migrationPath, 'utf8');

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

console.log(`📝 Executing ${statements.length} SQL statements...`);

db.serialize(() => {
  statements.forEach((statement, index) => {
    db.run(statement, (err) => {
      if (err) {
        console.error(`❌ Error in statement ${index + 1}:`, err.message);
      } else {
        console.log(`✅ Statement ${index + 1} executed successfully`);
      }
    });
  });
});

// Close database connection
db.close((err) => {
  if (err) {
    console.error('❌ Error closing database:', err.message);
  } else {
    console.log('✅ Migration completed. Database closed.');
  }
});
