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

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

        if (columns.length > 0) {
            console.log('✓ Column created_by already exists');
            
            // Check if foreign key exists
            const [fks] = await connection.query(`
                SELECT CONSTRAINT_NAME 
                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                WHERE TABLE_SCHEMA = ? 
                AND TABLE_NAME = 'ambulance_requests' 
                AND CONSTRAINT_NAME = 'fk_created_by'
            `, [dbConfig.database]);
            
            if (fks.length > 0) {
                console.log('✓ Foreign key fk_created_by already exists');
            } else {
                console.log('⚠ Column exists but foreign key is missing. Adding foreign key...');
                await connection.query(`
                    ALTER TABLE ambulance_requests 
                    ADD CONSTRAINT fk_created_by 
                    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
                `);
                console.log('✓ Added foreign key: fk_created_by');
            }
            
            // Check if index exists
            const [indexes] = await connection.query(`
                SELECT INDEX_NAME 
                FROM INFORMATION_SCHEMA.STATISTICS 
                WHERE TABLE_SCHEMA = ? 
                AND TABLE_NAME = 'ambulance_requests' 
                AND INDEX_NAME = 'idx_created_by'
            `, [dbConfig.database]);
            
            if (indexes.length > 0) {
                console.log('✓ Index idx_created_by already exists');
            } else {
                console.log('⚠ Column exists but index is missing. Adding index...');
                await connection.query(`
                    ALTER TABLE ambulance_requests 
                    ADD INDEX idx_created_by (created_by)
                `);
                console.log('✓ Added index: idx_created_by');
            }
            
            return;
        }

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

        // Add foreign key constraint
        try {
            await connection.query(`
                ALTER TABLE ambulance_requests 
                ADD CONSTRAINT fk_created_by 
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
            `);
            console.log('✓ Added foreign key: fk_created_by');
        } catch (error) {
            if (error.code === 'ER_DUP_KEY' || error.code === 'ER_DUP_KEY_NAME') {
                console.log('  Foreign key fk_created_by already exists');
            } else {
                throw error;
            }
        }

        // Add index
        try {
            await connection.query(`
                ALTER TABLE ambulance_requests 
                ADD INDEX idx_created_by (created_by)
            `);
            console.log('✓ Added index: idx_created_by');
        } catch (error) {
            if (error.code === 'ER_DUP_KEY' || error.code === 'ER_DUP_KEY_NAME') {
                console.log('  Index idx_created_by already exists');
            } else {
                throw error;
            }
        }

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

