require('dotenv').config();
const mysql = require('mysql2/promise');

// Database connection configuration
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'masm_app'
};

async function addDestinationColumns() {
    let connection;
    try {
        console.log('Connecting to database...');
        connection = await mysql.createConnection(dbConfig);
        console.log('Connected successfully!');

        // Check if columns already exist
        const [columns] = await connection.query(`
            SELECT COLUMN_NAME 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'ambulance_requests' 
            AND COLUMN_NAME IN ('destination_latitude', 'destination_longitude')
        `, [dbConfig.database]);

        if (columns.length === 2) {
            console.log('✓ Columns destination_latitude and destination_longitude already exist');
            return;
        }

        if (columns.length === 1) {
            console.log('⚠ One column exists, adding the missing one...');
        }

        // Add destination_latitude column
        try {
            await connection.query(`
                ALTER TABLE ambulance_requests 
                ADD COLUMN destination_latitude DECIMAL(10, 8) NULL AFTER longitude
            `);
            console.log('✓ Added column: destination_latitude');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('  Column destination_latitude already exists');
            } else {
                throw error;
            }
        }

        // Add destination_longitude column
        try {
            await connection.query(`
                ALTER TABLE ambulance_requests 
                ADD COLUMN destination_longitude DECIMAL(11, 8) NULL AFTER destination_latitude
            `);
            console.log('✓ Added column: destination_longitude');
        } catch (error) {
            if (error.code === 'ER_DUP_FIELDNAME') {
                console.log('  Column destination_longitude already exists');
            } else {
                throw error;
            }
        }

        console.log('\n✅ Migration completed successfully!');
        console.log('Columns destination_latitude and destination_longitude are now available in ambulance_requests table.');

    } catch (error) {
        console.error('❌ Migration failed:', error.message);
        if (error.code === 'ER_BAD_DB_ERROR') {
            console.error(`\nDatabase '${dbConfig.database}' does not exist.`);
        } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
            console.error('\nAccess denied. Please check your database credentials in .env file.');
        }
        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('\nDatabase connection closed.');
        }
    }
}

// Run the migration
addDestinationColumns();

