const db = require('../src/db');

async function comprehensiveFix() {
    try {
        console.log('🚀 Starting Comprehensive Database Schema Fix...');

        // 1. Mobile Triage Results (Ensure consistency)
        await db.query(`
            CREATE TABLE IF NOT EXISTS mobile_triage_results (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                score INT DEFAULT 0,
                correct_count INT DEFAULT 0,
                total_cards INT DEFAULT 0,
                xp_earned INT DEFAULT 0,
                game_type VARCHAR(50) DEFAULT 'triage',
                played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX (user_id),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ mobile_triage_results table ready');

        // 2. Mobile Device Tokens
        await db.query(`
            CREATE TABLE IF NOT EXISTS mobile_device_tokens (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                token VARCHAR(255) NOT NULL,
                platform ENUM('ios', 'android', 'web') DEFAULT 'ios',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, token),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ mobile_device_tokens table ready');

        // 3. Mobile User Settings
        await db.query(`
            CREATE TABLE IF NOT EXISTS mobile_user_settings (
                user_id INT PRIMARY KEY,
                theme_preference VARCHAR(20) DEFAULT 'dark',
                enable_haptics TINYINT(1) DEFAULT 1,
                daily_reminder_time TIME DEFAULT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ mobile_user_settings table ready');

        // 4. User Stats Snapshot (Daily snapshots)
        await db.query(`
            CREATE TABLE IF NOT EXISTS user_stats_snapshot (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                date DATE NOT NULL,
                total_alerts_solved INT DEFAULT 0,
                avg_score DECIMAL(5,2) DEFAULT 0.00,
                xp_earned INT DEFAULT 0,
                UNIQUE(user_id, date),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ user_stats_snapshot table ready');

        // 5. Assessments (B2B)
        await db.query(`
            CREATE TABLE IF NOT EXISTS assessments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                organization_id INT NOT NULL,
                title VARCHAR(255) NOT NULL,
                description TEXT,
                time_limit_minutes INT DEFAULT 60,
                passing_score INT DEFAULT 70,
                difficulty_filter VARCHAR(50),
                random_alert_count INT DEFAULT 5,
                is_active TINYINT(1) DEFAULT 1,
                created_by INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ assessments table ready');

        // 6. Assessment Invites (B2B)
        await db.query(`
            CREATE TABLE IF NOT EXISTS assessment_invites (
                id INT AUTO_INCREMENT PRIMARY KEY,
                assessment_id INT NOT NULL,
                email VARCHAR(255) NOT NULL,
                name VARCHAR(255),
                token VARCHAR(255) UNIQUE NOT NULL,
                expires_at DATETIME NOT NULL,
                status ENUM('pending', 'started', 'completed', 'expired') DEFAULT 'pending',
                started_at DATETIME,
                completed_at DATETIME,
                score INT,
                time_spent_seconds INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ assessment_invites table ready');

        // 7. Assessment Responses (B2B)
        await db.query(`
            CREATE TABLE IF NOT EXISTS assessment_responses (
                id INT AUTO_INCREMENT PRIMARY KEY,
                invite_id INT NOT NULL,
                alert_id INT NOT NULL,
                response JSON NOT NULL,
                is_correct TINYINT(1),
                time_spent_seconds INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (invite_id) REFERENCES assessment_invites(id) ON DELETE CASCADE,
                FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE
            )
        `);
        console.log('✅ assessment_responses table ready');

        // 8. Organization Path Assignments (B2B)
        await db.query(`
            CREATE TABLE IF NOT EXISTS organization_path_assignments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                organization_id INT NOT NULL,
                user_id INT NOT NULL,
                learning_path_id INT,
                operation_id INT,
                assigned_by INT NOT NULL,
                due_date DATE,
                is_mandatory TINYINT(1) DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (learning_path_id) REFERENCES learning_paths(id) ON DELETE SET NULL,
                FOREIGN KEY (operation_id) REFERENCES operations(id) ON DELETE SET NULL
            )
        `);
        console.log('✅ organization_path_assignments table ready');

        // 9. ALTER EXISTING TABLES for missing columns
        console.log('📝 Checking for missing columns in existing tables...');

        try {
            await db.query(`ALTER TABLE users ADD COLUMN focus_area ENUM('red', 'blue', 'purple') DEFAULT 'purple' AFTER organization_id`);
            console.log('✅ Added focus_area to users table');
        } catch (e) {
            if (e.message.includes('Duplicate column name')) {
                console.log('ℹ️ focus_area already exists in users table');
            } else {
                console.error('❌ Error adding focus_area to users:', e.message);
            }
        }

        try {
            await db.query(`ALTER TABLE operations ADD COLUMN icon_url VARCHAR(255) AFTER total_alerts`);
            console.log('✅ Added icon_url to operations table');
        } catch (e) {
            if (e.message.includes('Duplicate column name')) {
                console.log('ℹ️ icon_url already exists in operations table');
            } else {
                console.error('❌ Error adding icon_url to operations:', e.message);
            }
        }

        console.log('\n✨ Database Schema Fix Completed Successfully!');
    } catch (error) {
        console.error('\n❌ ERROR during schema fix:', error.message);
    } finally {
        process.exit();
    }
}

comprehensiveFix();
