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

        // Check if incident_type table exists
        const [typeTables] = await connection.query(`
            SELECT TABLE_NAME 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'incident_type'
        `, [dbConfig.database]);

        if (typeTables.length === 0) {
            // Create incident_type table
            await connection.query(`
                CREATE TABLE IF NOT EXISTS incident_type (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL UNIQUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_name (name)
                )
            `);
            console.log('✓ Created table: incident_type');
        } else {
            console.log('✓ Table incident_type already exists');
        }

        // Check if incident_details table exists
        const [detailsTables] = await connection.query(`
            SELECT TABLE_NAME 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = ? 
            AND TABLE_NAME = 'incident_details'
        `, [dbConfig.database]);

        if (detailsTables.length === 0) {
            // Create incident_details table
            await connection.query(`
                CREATE TABLE IF NOT EXISTS incident_details (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    request_id INT NOT NULL UNIQUE,
                    number_of_casualties INT DEFAULT 0,
                    is_member BOOLEAN DEFAULT FALSE,
                    bill_accepted BOOLEAN DEFAULT FALSE,
                    incident_type_id INT,
                    incident_requirement_notes TEXT,
                    general_notes TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
                    FOREIGN KEY (incident_type_id) REFERENCES incident_type(id) ON DELETE SET NULL,
                    INDEX idx_request_id (request_id),
                    INDEX idx_incident_type_id (incident_type_id)
                )
            `);
            console.log('✓ Created table: incident_details');
        } else {
            console.log('✓ Table incident_details already exists');
        }

        console.log('\n✅ Migration completed successfully!');
        console.log('Tables incident_type and incident_details are now available.');

    } 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
addIncidentTables();

