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 makeRequestByNullable() {
    let connection;
    try {
        console.log('Connecting to database...');
        connection = await mysql.createConnection(dbConfig);
        console.log('Connected successfully!');

        // Check current column definition
        const [columns] = await connection.query(`
            SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'ambulance_requests' 
            AND COLUMN_NAME = 'request_by'
        `, [dbConfig.database]);

        if (columns.length === 0) {
            console.error('❌ Column request_by not found in ambulance_requests table');
            process.exit(1);
        }

        const column = columns[0];
        if (column.IS_NULLABLE === 'YES') {
            console.log('✓ Column request_by is already nullable');
        } else {
            console.log('⚠ Column request_by is currently NOT NULL, making it nullable...');
            
            // First, drop the foreign key constraint if it exists
            try {
                await connection.query(`
                    ALTER TABLE ambulance_requests 
                    DROP FOREIGN KEY ambulance_requests_ibfk_1
                `);
                console.log('✓ Dropped existing foreign key constraint');
            } catch (error) {
                if (error.code === 'ER_CANT_DROP_FIELD_OR_KEY') {
                    // Try alternative constraint name
                    try {
                        await connection.query(`
                            ALTER TABLE ambulance_requests 
                            DROP FOREIGN KEY fk_request_by
                        `);
                        console.log('✓ Dropped existing foreign key constraint (fk_request_by)');
                    } catch (err2) {
                        // Check if constraint exists with a different name
                        const [constraints] = await connection.query(`
                            SELECT CONSTRAINT_NAME 
                            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                            WHERE TABLE_SCHEMA = ? 
                            AND TABLE_NAME = 'ambulance_requests' 
                            AND COLUMN_NAME = 'request_by'
                            AND REFERENCED_TABLE_NAME IS NOT NULL
                        `, [dbConfig.database]);
                        
                        if (constraints.length > 0) {
                            const constraintName = constraints[0].CONSTRAINT_NAME;
                            await connection.query(`
                                ALTER TABLE ambulance_requests 
                                DROP FOREIGN KEY ${constraintName}
                            `);
                            console.log(`✓ Dropped existing foreign key constraint (${constraintName})`);
                        } else {
                            console.log('  No foreign key constraint found to drop');
                        }
                    }
                } else {
                    console.log('  No foreign key constraint found to drop');
                }
            }

            // Modify column to allow NULL
            await connection.query(`
                ALTER TABLE ambulance_requests 
                MODIFY COLUMN request_by INT NULL
            `);
            console.log('✓ Modified column request_by to allow NULL');

            // Re-add foreign key constraint with ON DELETE SET NULL
            await connection.query(`
                ALTER TABLE ambulance_requests 
                ADD CONSTRAINT fk_request_by 
                FOREIGN KEY (request_by) REFERENCES users(id) ON DELETE SET NULL
            `);
            console.log('✓ Re-added foreign key constraint with ON DELETE SET NULL');
        }

        console.log('\n✅ Migration completed successfully!');
        console.log('Column request_by is now nullable 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
makeRequestByNullable();

