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

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

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

db.serialize(() => {
  console.log('📝 Creating blocked_dates table...');
  
  db.run(`
    CREATE TABLE IF NOT EXISTS blocked_dates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      blocked_date DATE NOT NULL UNIQUE,
      reason TEXT,
      created_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (created_by) REFERENCES users(id)
    )
  `, (err) => {
    if (err) {
      console.error('❌ Error creating blocked_dates table:', err);
    } else {
      console.log('✅ blocked_dates table created');
    }
  });
  
  db.run(`
    CREATE INDEX IF NOT EXISTS idx_blocked_dates_date ON blocked_dates(blocked_date)
  `, (err) => {
    if (err) {
      console.error('❌ Error creating index:', err);
    } else {
      console.log('✅ Index created on blocked_dates');
    }
  });
  
  console.log('📝 Creating booking_config table...');
  
  db.run(`
    CREATE TABLE IF NOT EXISTS booking_config (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      config_key TEXT UNIQUE NOT NULL,
      config_value TEXT NOT NULL,
      description TEXT,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `, (err) => {
    if (err) {
      console.error('❌ Error creating booking_config table:', err);
    } else {
      console.log('✅ booking_config table created');
    }
  });
  
  db.run(`
    INSERT OR IGNORE INTO booking_config (config_key, config_value, description) 
    VALUES ('min_advance_hours', '24', 'Minimum hours in advance required for bookings')
  `, (err) => {
    if (err) {
      console.error('❌ Error inserting min_advance_hours:', err);
    } else {
      console.log('✅ min_advance_hours config inserted');
    }
  });
  
  db.run(`
    INSERT OR IGNORE INTO booking_config (config_key, config_value, description) 
    VALUES ('allow_same_day_booking', '0', 'Allow same-day bookings (0 = No, 1 = Yes)')
  `, (err) => {
    if (err) {
      console.error('❌ Error inserting allow_same_day_booking:', err);
    } else {
      console.log('✅ allow_same_day_booking config inserted');
    }
  });
});

db.close((err) => {
  if (err) {
    console.error('❌ Error closing database:', err);
  } else {
    console.log('\n🎉 Migration completed successfully!');
  }
});
