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

// 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'
};

// Table creation queries (in order of dependencies)
const createTables = {
    users: `CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(255) NOT NULL,
        last_name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        phone_number VARCHAR(20),
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50),
        is_verified BOOLEAN DEFAULT FALSE,
        OTP VARCHAR(10),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )`,
    
    masm_scheme: `CREATE TABLE IF NOT EXISTS masm_scheme (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        \`desc\` TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name)
    )`,
    
    clients: `CREATE TABLE IF NOT EXISTS clients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL UNIQUE,
        insurance_scheme_id INT,
        phone_number VARCHAR(20),
        is_cash_client BOOLEAN DEFAULT FALSE,
        membership_number VARCHAR(255),
        suffix VARCHAR(50),
        address TEXT,
        date_of_birth DATE,
        emergency_contact_name VARCHAR(255),
        emergency_contact_phone VARCHAR(20),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (insurance_scheme_id) REFERENCES masm_scheme(id) ON DELETE SET NULL,
        INDEX idx_user_id (user_id),
        INDEX idx_phone_number (phone_number),
        INDEX idx_insurance_scheme_id (insurance_scheme_id)
    )`,
    
    chat: `CREATE TABLE IF NOT EXISTS chat (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        message_to INT NOT NULL,
        message TEXT NOT NULL,
        status ENUM('pending', 'cancelled', 'dispatched', 'completed') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (message_to) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_message_to (message_to),
        INDEX idx_status (status)
    )`,
    
    ambulance: `CREATE TABLE IF NOT EXISTS ambulance (
        id INT AUTO_INCREMENT PRIMARY KEY,
        vehicle_number VARCHAR(50) NOT NULL UNIQUE,
        license_plate VARCHAR(50) NOT NULL UNIQUE,
        model VARCHAR(255),
        make VARCHAR(255),
        status ENUM('available', 'in-use', 'maintenance', 'out-of-service') DEFAULT 'available',
        latitude DECIMAL(10, 8),
        longitude DECIMAL(11, 8),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_vehicle_number (vehicle_number),
        INDEX idx_license_plate (license_plate),
        INDEX idx_status (status)
    )`,
    
    driver: `CREATE TABLE IF NOT EXISTS driver (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL UNIQUE,
        license_number VARCHAR(50) NOT NULL UNIQUE,
        ambulance_id INT,
        status ENUM('on-duty', 'off-duty', 'available') DEFAULT 'on-duty',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (ambulance_id) REFERENCES ambulance(id) ON DELETE SET NULL,
        INDEX idx_user_id (user_id),
        INDEX idx_license_number (license_number),
        INDEX idx_ambulance_id (ambulance_id),
        INDEX idx_status (status)
    )`,
    
    ambulance_requests: `CREATE TABLE IF NOT EXISTS ambulance_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        request_by INT NULL,
        created_by INT NULL,
        is_urgent BOOLEAN DEFAULT FALSE,
        location VARCHAR(255) NOT NULL,
        destination VARCHAR(255),
        latitude DECIMAL(10, 8) NOT NULL,
        longitude DECIMAL(11, 8) NOT NULL,
        destination_latitude DECIMAL(10, 8),
        destination_longitude DECIMAL(11, 8),
        status ENUM('pending', 'dispatched', 'ongoing', 'enroute', 'cancelled', 'completed') DEFAULT 'pending',
        comment TEXT,
        trip_started_at TIMESTAMP NULL,
        trip_completed_at TIMESTAMP NULL,
        pickup_confirmed_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (request_by) REFERENCES users(id) ON DELETE SET NULL,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
        INDEX idx_request_by (request_by),
        INDEX idx_created_by (created_by),
        INDEX idx_status (status)
    )`,

    notifications: `CREATE TABLE IF NOT EXISTS notifications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        request_id INT NULL,
        text TEXT NOT NULL,
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        is_read BOOLEAN DEFAULT FALSE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_request_id (request_id),
        INDEX idx_timestamp (timestamp)
    )`,
    
    dispatch: `CREATE TABLE IF NOT EXISTS dispatch (
        id INT AUTO_INCREMENT PRIMARY KEY,
        ambulance_request_id INT NOT NULL,
        ambulance_id INT NOT NULL,
        driver_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (ambulance_request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
        FOREIGN KEY (ambulance_id) REFERENCES ambulance(id) ON DELETE CASCADE,
        FOREIGN KEY (driver_id) REFERENCES driver(id) ON DELETE CASCADE,
        INDEX idx_ambulance_request_id (ambulance_request_id),
        INDEX idx_ambulance_id (ambulance_id),
        INDEX idx_driver_id (driver_id),
        UNIQUE KEY unique_request (ambulance_request_id)
    )`,

    device_tokens: `CREATE TABLE IF NOT EXISTS device_tokens (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        token VARCHAR(512) NOT NULL UNIQUE,
        platform ENUM('android', 'ios', 'web') DEFAULT 'android',
        is_active BOOLEAN DEFAULT TRUE,
        push_enabled BOOLEAN DEFAULT TRUE,
        sound_enabled BOOLEAN DEFAULT TRUE,
        vibration_enabled BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_platform (platform),
        INDEX idx_active (is_active)
    )`,

    token_blacklist: `CREATE TABLE IF NOT EXISTS token_blacklist (
        id INT AUTO_INCREMENT PRIMARY KEY,
        token TEXT NOT NULL,
        expires_at DATETIME,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_expires_at (expires_at)
    )`,
    
    incident_type: `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)
    )`,
    
    incident_details: `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)
    )`,
    
};

// Table names in reverse dependency order (for deletion)
const tablesInReverseOrder = ['incident_details', 'incident_type', 'dispatch', 'device_tokens', 'driver', 'ambulance', 'chat', 'clients', 'masm_scheme', 'notifications', 'ambulance_requests', 'token_blacklist', 'users'];

// Create database connection
async function getConnection() {
    try {
        // Try to connect directly to the database
        // If database doesn't exist, it will fail and user needs to create it in cPanel
        const connection = await mysql.createConnection(dbConfig);
        return connection;
    } catch (error) {
        // If database doesn't exist, provide helpful error message
        if (error.code === 'ER_BAD_DB_ERROR' || error.code === 'ER_DBACCESS_DENIED_ERROR') {
            console.error(`\n❌ Database '${dbConfig.database}' does not exist or access denied.`);
            console.error(`\n📋 Please create the database '${dbConfig.database}' in cPanel first, then run this migration again.`);
            console.error(`\n   Steps:`);
            console.error(`   1. Go to cPanel → MySQL Databases`);
            console.error(`   2. Create a new database named: ${dbConfig.database}`);
            console.error(`   3. Add user '${dbConfig.user}' to the database with ALL PRIVILEGES`);
            console.error(`   4. Run: node database.js migrate\n`);
        }
        console.error('Error creating database connection:', error.message);
        throw error;
    }
}

// Migrate function - creates tables if they don't exist
async function migrate() {
    let connection;
    try {
        console.log('Starting database migration...');
        connection = await getConnection();
        
        // Create tables in order
        for (const [tableName, query] of Object.entries(createTables)) {
            await connection.query(query);
            console.log(`✓ Table '${tableName}' migrated successfully`);
        }
        
        console.log('\n✅ Migration completed successfully!');
    } catch (error) {
        console.error('❌ Migration failed:', error.message);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// Reset function - drops all tables and recreates them
async function reset() {
    let connection;
    try {
        console.log('Starting database reset...');
        connection = await getConnection();
        
        // Disable foreign key checks temporarily
        await connection.query('SET FOREIGN_KEY_CHECKS = 0');
        
        // Drop tables in reverse dependency order
        console.log('\nDropping existing tables...');
        for (const tableName of tablesInReverseOrder) {
            try {
                await connection.query(`DROP TABLE IF EXISTS ${tableName}`);
                console.log(`✓ Dropped table '${tableName}'`);
            } catch (error) {
                // Table might not exist, continue
                console.log(`  Table '${tableName}' does not exist (skipping)`);
            }
        }
        
        // Re-enable foreign key checks
        await connection.query('SET FOREIGN_KEY_CHECKS = 1');
        
        // Create tables in order
        console.log('\nCreating tables...');
        for (const [tableName, query] of Object.entries(createTables)) {
            await connection.query(query);
            console.log(`✓ Created table '${tableName}'`);
        }
        
        console.log('\n✅ Reset completed successfully!');
    } catch (error) {
        console.error('❌ Reset failed:', error.message);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

// Main execution
async function main() {
    const command = process.argv[2];
    
    if (command === 'migrate') {
        await migrate();
    } else if (command === 'reset') {
        await reset();
    } else {
        console.log('Usage: node database.js [migrate|reset]');
        console.log('  migrate - Create tables if they don\'t exist');
        console.log('  reset  - Drop all tables and recreate them');
        process.exit(1);
    }
    
    process.exit(0);
}

// Run if executed directly
if (require.main === module) {
    main().catch((error) => {
        console.error('Fatal error:', error);
        process.exit(1);
    });
}

module.exports = { migrate, reset, getConnection };

