import mysql from 'mysql2/promise'
import dotenv from 'dotenv'
import { fileURLToPath } from 'url'
import { dirname, join } from 'path'

const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)

dotenv.config({ path: join(__dirname, '..', '.env') })

async function addBookingExtras() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  })

  try {
    console.log('Adding booking extras columns...')
    
    // Check and add include_service
    const [serviceCol] = await connection.query(
      `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'bookings' AND COLUMN_NAME = 'include_service'`,
      [process.env.DB_NAME]
    )
    
    if (serviceCol.length === 0) {
      await connection.query(`ALTER TABLE bookings ADD COLUMN include_service BOOLEAN DEFAULT FALSE`)
      console.log('✓ Added include_service column')
    } else {
      console.log('✓ include_service column already exists')
    }
    
    // Check and add include_cleanup
    const [cleanupCol] = await connection.query(
      `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'bookings' AND COLUMN_NAME = 'include_cleanup'`,
      [process.env.DB_NAME]
    )
    
    if (cleanupCol.length === 0) {
      await connection.query(`ALTER TABLE bookings ADD COLUMN include_cleanup BOOLEAN DEFAULT FALSE`)
      console.log('✓ Added include_cleanup column')
    } else {
      console.log('✓ include_cleanup column already exists')
    }
    
    // Check and add total_price
    const [priceCol] = await connection.query(
      `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'bookings' AND COLUMN_NAME = 'total_price'`,
      [process.env.DB_NAME]
    )
    
    if (priceCol.length === 0) {
      await connection.query(`ALTER TABLE bookings ADD COLUMN total_price DECIMAL(10,2) DEFAULT NULL`)
      console.log('✓ Added total_price column')
    } else {
      console.log('✓ total_price column already exists')
    }

  } catch (error) {
    console.error('Error:', error)
    process.exit(1)
  } finally {
    await connection.end()
  }
}

addBookingExtras()