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)

// Load environment variables from .env file in parent directory
dotenv.config({ path: join(__dirname, '..', '.env') })

async function addAddressColumn() {
  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('Checking if address column exists in bookings table...')
    
    // Check if column exists
    const [columns] = await connection.query(
      `SELECT COLUMN_NAME 
       FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA = ? 
       AND TABLE_NAME = 'bookings' 
       AND COLUMN_NAME = 'address'`,
      [process.env.DB_NAME]
    )

    if (columns.length > 0) {
      console.log('✓ Address column already exists')
    } else {
      console.log('Adding address column to bookings table...')
      await connection.query(
        `ALTER TABLE bookings ADD COLUMN address VARCHAR(255) DEFAULT NULL AFTER phone`
      )
      console.log('✓ Address column added successfully')
    }

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

addAddressColumn()