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

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

const tables = {
  cms_content: `
    CREATE TABLE IF NOT EXISTS cms_content (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      section VARCHAR(100) NOT NULL UNIQUE,
      title TEXT,
      subtitle TEXT,
      content TEXT,
      button_text VARCHAR(100),
      button_link VARCHAR(255),
      image_url VARCHAR(255),
      is_active BOOLEAN DEFAULT 1,
      display_order INTEGER DEFAULT 0,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_by INTEGER
    )
  `,
  
  cms_vehicles: `
    CREATE TABLE IF NOT EXISTS cms_vehicles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(100) NOT NULL,
      description TEXT,
      image_url VARCHAR(255),
      max_passengers INTEGER NOT NULL,
      max_luggage INTEGER NOT NULL,
      base_price DECIMAL(10,2) NOT NULL,
      price_per_mile DECIMAL(10,2) NOT NULL,
      vehicle_type VARCHAR(50),
      features TEXT,
      is_active BOOLEAN DEFAULT 1,
      display_order INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `,
  
  cms_pricing: `
    CREATE TABLE IF NOT EXISTS cms_pricing (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      rule_name VARCHAR(100) NOT NULL UNIQUE,
      rule_type VARCHAR(50) NOT NULL,
      value DECIMAL(10,2) NOT NULL,
      value_type VARCHAR(20) NOT NULL,
      description TEXT,
      applicable_to TEXT,
      is_active BOOLEAN DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `,
  
  cms_airports: `
    CREATE TABLE IF NOT EXISTS cms_airports (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(150) NOT NULL,
      code VARCHAR(10) UNIQUE,
      postcode VARCHAR(20),
      address TEXT,
      meet_greet_fee DECIMAL(10,2) DEFAULT 15.00,
      parking_fee DECIMAL(10,2) DEFAULT 0.00,
      is_active BOOLEAN DEFAULT 1,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `,
  
  cms_settings: `
    CREATE TABLE IF NOT EXISTS cms_settings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      setting_key VARCHAR(100) NOT NULL UNIQUE,
      setting_value TEXT,
      setting_type VARCHAR(50),
      category VARCHAR(50),
      description TEXT,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `,
  
  cms_images: `
    CREATE TABLE IF NOT EXISTS cms_images (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      filename VARCHAR(255) NOT NULL,
      original_name VARCHAR(255),
      file_path VARCHAR(500) NOT NULL,
      file_size INTEGER,
      mime_type VARCHAR(100),
      category VARCHAR(50),
      alt_text VARCHAR(255),
      uploaded_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `,
  
  bookings_enhanced: `
    CREATE TABLE IF NOT EXISTS bookings_enhanced (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      booking_reference VARCHAR(20) UNIQUE NOT NULL,
      journey_type VARCHAR(50) NOT NULL,
      pickup_type VARCHAR(50),
      pickup_location TEXT NOT NULL,
      pickup_postcode VARCHAR(20),
      pickup_airport_id INTEGER,
      dropoff_type VARCHAR(50),
      dropoff_location TEXT NOT NULL,
      dropoff_postcode VARCHAR(20),
      dropoff_airport_id INTEGER,
      pickup_date DATE NOT NULL,
      pickup_time TIME NOT NULL,
      flight_number VARCHAR(50),
      flight_arrival_time TIME,
      passengers INTEGER NOT NULL,
      luggage INTEGER NOT NULL,
      vehicle_id INTEGER NOT NULL,
      vehicle_name VARCHAR(100),
      user_id INTEGER,
      guest_email VARCHAR(255),
      guest_name VARCHAR(255),
      guest_phone VARCHAR(50),
      customer_notes TEXT,
      distance_miles REAL,
      base_fare DECIMAL(10,2) NOT NULL,
      distance_charge DECIMAL(10,2) DEFAULT 0,
      airport_fee DECIMAL(10,2) DEFAULT 0,
      meet_greet_fee DECIMAL(10,2) DEFAULT 0,
      extras_total DECIMAL(10,2) DEFAULT 0,
      discount_amount DECIMAL(10,2) DEFAULT 0,
      discount_type VARCHAR(50),
      total_price DECIMAL(10,2) NOT NULL,
      meet_greet BOOLEAN DEFAULT 0,
      child_seat BOOLEAN DEFAULT 0,
      is_return_journey BOOLEAN DEFAULT 0,
      return_pickup_date DATE,
      return_pickup_time TIME,
      return_flight_number VARCHAR(50),
      status VARCHAR(50) DEFAULT 'pending',
      payment_status VARCHAR(50) DEFAULT 'unpaid',
      assigned_driver_id INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `,
  
  quote_requests: `
    CREATE TABLE IF NOT EXISTS quote_requests (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      journey_type VARCHAR(50) NOT NULL,
      pickup_location TEXT NOT NULL,
      dropoff_location TEXT NOT NULL,
      pickup_date DATE,
      pickup_time TIME,
      passengers INTEGER,
      luggage INTEGER,
      customer_name VARCHAR(255),
      customer_email VARCHAR(255) NOT NULL,
      customer_phone VARCHAR(50),
      additional_info TEXT,
      status VARCHAR(50) DEFAULT 'pending',
      quoted_price DECIMAL(10,2),
      quoted_vehicle_id INTEGER,
      quoted_at DATETIME,
      quoted_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `
};

async function createTables() {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(DB_PATH, async (err) => {
      if (err) {
        reject(err);
        return;
      }

      console.log('✓ Connected to database');
      console.log('Creating CMS tables...\n');

      let created = 0;
      let existed = 0;

      for (const [tableName, createSQL] of Object.entries(tables)) {
        try {
          await new Promise((res, rej) => {
            db.run(createSQL, (err) => {
              if (err) rej(err);
              else res();
            });
          });
          console.log(`✓ Created/verified table: ${tableName}`);
          created++;
        } catch (error) {
          if (error.message.includes('already exists')) {
            console.log(`  - Table ${tableName} already exists`);
            existed++;
          } else {
            console.error(`✗ Error creating ${tableName}:`, error.message);
          }
        }
      }

      console.log(`\n✓ Tables created: ${created}`);
      console.log(`  - Already existed: ${existed}`);

      db.close((err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  });
}

createTables()
  .then(() => {
    console.log('\n🎉 Database schema ready!');
    process.exit(0);
  })
  .catch((error) => {
    console.error('\n❌ Error:', error);
    process.exit(1);
  });
