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

const migrateDb = async () => {
    console.log('--- Starting Database Auto-Migration ---');
    try {
        // 1. users table: alerts_this_month
        try {
            await db.query("ALTER TABLE users ADD COLUMN alerts_this_month int DEFAULT 0", [], { silent: true });
            console.log('✅ Added users.alerts_this_month');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.alerts_this_month check skipped/exists');
        }

        // 1.1 users table: is_student column (Manual Override)
        try {
            await db.query("ALTER TABLE users ADD COLUMN is_student BOOLEAN DEFAULT FALSE", [], { silent: true });
            console.log('✅ Added users.is_student');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.is_student check skipped/exists');
        }

        // 1.2 student_domains table (Whitelist)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS student_domains (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    domain VARCHAR(255) UNIQUE NOT NULL,
                    added_by INT DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            `, [], { silent: true });
            console.log('✅ Checked/Created student_domains table');
        } catch (e) {
            console.error('⚠️ Could not create student_domains table', e.message);
        }
        // 2. users table: last_usage_reset
        try {
            await db.query("ALTER TABLE users ADD COLUMN last_usage_reset datetime DEFAULT CURRENT_TIMESTAMP", [], { silent: true });
            console.log('✅ Added users.last_usage_reset');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.last_usage_reset check skipped/exists');
        }

        // 34. verification_reminder_sent and first_push_sent for unverified cleanup
        try {
            const hasSentCol = await db.query("SHOW COLUMNS FROM users LIKE 'verification_reminder_sent'");
            if (hasSentCol.rows.length === 0) {
                await db.query("ALTER TABLE users ADD COLUMN verification_reminder_sent TINYINT(1) DEFAULT 0");
                console.log('✅ Added verification_reminder_sent column to users table');
            }
            const hasFirstPushCol = await db.query("SHOW COLUMNS FROM users LIKE 'first_push_sent'");
            if (hasFirstPushCol.rows.length === 0) {
                await db.query("ALTER TABLE users ADD COLUMN first_push_sent TINYINT(1) DEFAULT 0");
                console.log('✅ Added first_push_sent column to users table');
            }
        } catch (e) {
            console.error('⚠️ Could not check/add reminder columns', e.message);
        }

        // 2.1 users table: username
        try {
            await db.query("ALTER TABLE users ADD COLUMN username VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.username');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.username check skipped/exists');
        }

        // 2.1.1 users table: Fix duplicate usernames (JS Logic for Safety)
        try {
            // Fetch all users with usernames to check duplicates reliably
            const allUsers = await db.query("SELECT id, username, created_at FROM users WHERE username IS NOT NULL ORDER BY created_at");
            
            const usernameMap = {};
            const duplicateUpdates = [];

            for (const user of allUsers.rows) {
                if (usernameMap[user.username]) {
                    // Duplicate found!
                    duplicateUpdates.push(user);
                } else {
                    usernameMap[user.username] = true;
                }
            }
            
            if (duplicateUpdates.length > 0) {
                console.log(`Found ${duplicateUpdates.length} duplicate usernames via JS check. Fixing...`);
                for (const dupUser of duplicateUpdates) {
                    const newUsername = `${dupUser.username}_${dupUser.id}`;
                    await db.query("UPDATE users SET username = ? WHERE id = ?", [newUsername, dupUser.id]);
                    console.log(`Updated duplicate username for ID ${dupUser.id}: ${dupUser.username} -> ${newUsername}`);
                }
                console.log('✅ Deduplicated usernames complete.');
            }
        } catch (err) {
            console.error('⚠️ Failed to deduplicate usernames:', err.message);
        }

        // 2.1.2 users table: username unique constraint
        try {
            await db.query("ALTER TABLE users ADD UNIQUE (username)", [], { silent: true });
            console.log('✅ Added UNIQUE constraint to users.username');
        } catch (err) {
             if (!err.message.includes('Duplicate key')) console.log('ℹ️ users.username UNIQUE check skipped/exists');
        }

        // 2.2 users table: profile_picture
        try {
            await db.query("ALTER TABLE users ADD COLUMN profile_picture TEXT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.profile_picture');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.profile_picture check skipped/exists');
        }

        // 2.3 users table: session_token (for single-device login)
        try {
            await db.query("ALTER TABLE users ADD COLUMN session_token VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.session_token');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.session_token check skipped/exists');
        }

        // 3. alerts table: assigned_to
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN assigned_to INT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added alerts.assigned_to');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.assigned_to check skipped/exists');
        }

        // 4. alerts table: raw_log
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN raw_log TEXT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added alerts.raw_log');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.raw_log check skipped/exists');
        }

        // 5. alerts table: status
        try {
            await db.query("ALTER TABLE alerts MODIFY COLUMN status VARCHAR(50) DEFAULT 'new'", [], { silent: true });
            console.log('✅ Converted alerts.status to VARCHAR(50)');
        } catch (e) {
            console.error('⚠️ Could not convert alerts.status to VARCHAR.', e.message);
        }

        // 5.1 alerts table: playbook_solution
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN playbook_solution JSON DEFAULT NULL", [], { silent: true });
            console.log('✅ Added alerts.playbook_solution');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.playbook_solution check skipped/exists');
        }

        // 5.1.1 alerts table: is_ai_generated
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN is_ai_generated BOOLEAN DEFAULT FALSE", [], { silent: true });
            console.log('✅ Added alerts.is_ai_generated');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.is_ai_generated check skipped/exists');
        }

        // 5.1.2 alerts table: min_level
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN min_level INT DEFAULT 1", [], { silent: true });
            console.log('✅ Added alerts.min_level');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.min_level check skipped/exists');
        }

        // 5.1.3 alerts table: path_code
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN path_code VARCHAR(20) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added alerts.path_code');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.path_code check skipped/exists');
        }

        // 5.1.4 alerts table: sector_code (Industry)
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN sector_code VARCHAR(50) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added alerts.sector_code');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.sector_code check skipped/exists');
        }

        // 5.1.4 alerts table: difficulty
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN difficulty VARCHAR(50) DEFAULT 'Intermediate'", [], { silent: true });
            console.log('✅ Added alerts.difficulty');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.difficulty check skipped/exists');
        }

        // 5.1.5 alerts table: created_by
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN created_by INT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added alerts.created_by');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.created_by check skipped/exists');
        }

        // 5.2 investigations table: ai_evaluation_scheduled_at
        try {
            await db.query("ALTER TABLE investigations ADD COLUMN ai_evaluation_scheduled_at DATETIME DEFAULT NULL", [], { silent: true });
            console.log('✅ Added investigations.ai_evaluation_scheduled_at');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ investigations.ai_evaluation_scheduled_at check skipped');
        }

        // 5.3 investigations table: is_reported
        try {
            await db.query("ALTER TABLE investigations ADD COLUMN is_reported BOOLEAN DEFAULT FALSE", [], { silent: true });
            console.log('✅ Added investigations.is_reported');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ investigations.is_reported check skipped');
        }

        // 5.4 investigations table: report_reason
        try {
            await db.query("ALTER TABLE investigations ADD COLUMN report_reason TEXT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added investigations.report_reason');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ investigations.report_reason check skipped');
        }

        // 5.4.1 investigations table: verdict and grade (STRIKE: Missing columns)
        try {
            await db.query("ALTER TABLE investigations ADD COLUMN verdict VARCHAR(50) DEFAULT 'true_positive'", [], { silent: true });
            console.log('✅ Added investigations.verdict');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ investigations.verdict check skipped');
        }
        try {
            await db.query("ALTER TABLE investigations ADD COLUMN grade INT DEFAULT 0", [], { silent: true });
            console.log('✅ Added investigations.grade');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ investigations.grade check skipped');
        }

        // 5.5 investigations table: user_id + alert_id UNIQUE (Deduplication first)
        try {
            // Check for duplicates
            const duplicates = await db.query(`
                SELECT user_id, alert_id, COUNT(*) as count 
                FROM investigations 
                GROUP BY user_id, alert_id 
                HAVING count > 1
            `);
            
            if (duplicates.rows.length > 0) {
                console.log(`🔄 Found ${duplicates.rows.length} duplicate investigations groups. Deduplicating...`);
                for (const group of duplicates.rows) {
                    // Keep the latest one (highest ID), delete others
                    const { user_id, alert_id } = group;
                    await db.query(`
                        DELETE FROM investigations 
                        WHERE user_id = ? AND alert_id = ? 
                        AND id NOT IN (
                            SELECT id FROM (
                                SELECT MAX(id) as id FROM investigations 
                                WHERE user_id = ? AND alert_id = ?
                            ) as tmp
                        )
                    `, [user_id, alert_id, user_id, alert_id]);
                }
                console.log('✅ Deduplication complete.');
            }

            // Apply UNIQUE constraint
            await db.query("ALTER TABLE investigations ADD UNIQUE INDEX unique_user_alert (user_id, alert_id)", [], { silent: true });
            console.log('✅ Added UNIQUE INDEX to investigations (user_id, alert_id)');
        } catch (err) {
             if (!err.message.includes('Duplicate key') && !err.message.includes('Duplicate entry')) {
                 console.log('ℹ️ investigations UNIQUE index check skipped/exists');
             }
        }

        // 6. users table: subscription_end_date
        try {
            await db.query("ALTER TABLE users ADD COLUMN subscription_end_date DATETIME DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.subscription_end_date');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.subscription_end_date check skipped');
        }

        // 6.1 users table: current_mission (default 1)
        try {
            await db.query("ALTER TABLE users ADD COLUMN current_mission INT DEFAULT 1", [], { silent: true });
            console.log('✅ Added users.current_mission');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.current_mission check skipped');
        }

        // 6.2 users table: training_completed (default 0)
        try {
            await db.query("ALTER TABLE users ADD COLUMN training_completed TINYINT(1) DEFAULT 0", [], { silent: true });
            console.log('✅ Added users.training_completed');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.training_completed check skipped');
        }

        // 6.3 users table: email_preferences (JSON for email opt-out settings)
        try {
            await db.query("ALTER TABLE users ADD COLUMN email_preferences JSON DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.email_preferences');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.email_preferences check skipped');
        }

        // 6.4 users table: customer_id (Stripe customer ID)
        try {
            await db.query("ALTER TABLE users ADD COLUMN customer_id VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.customer_id');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.customer_id check skipped');
        }

        // 6.5 users table: subscription_id (Stripe subscription ID)
        try {
            await db.query("ALTER TABLE users ADD COLUMN subscription_id VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.subscription_id');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.subscription_id check skipped');
        }

        // 6.6 users table: subscription_status
        try {
            await db.query("ALTER TABLE users ADD COLUMN subscription_status VARCHAR(50) DEFAULT 'inactive'", [], { silent: true });
            console.log('✅ Added users.subscription_status');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.subscription_status check skipped');
        }

        // 6.7 users table: subscription_source (e.g., 'stripe', 'complimentary')
        try {
            await db.query("ALTER TABLE users ADD COLUMN subscription_source VARCHAR(50) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.subscription_source');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.error('⚠️ Could not add users.subscription_source:', err.message);
        }

        // 7. alert_grades table (if missing)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS alert_grades (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    alert_id INT NOT NULL,
                    user_id INT NOT NULL,
                    grade INT,
                    feedback TEXT,
                    graded_by INT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            `);
            console.log('✅ Checked/Created alert_grades table');
        } catch (e) {
            console.error('⚠️ Could not create alert_grades table', e.message);
        }

        // 8. notifications table
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS notifications (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    message TEXT,
                    is_read BOOLEAN DEFAULT FALSE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            `);
            console.log('✅ Checked/Created notifications table');
        } catch (e) {
            console.error('⚠️ Could not create notifications table', e.message);
        }

        // 9. blog_posts table
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS blog_posts (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    slug VARCHAR(255) UNIQUE NOT NULL,
                    content LONGTEXT,
                    excerpt TEXT,
                    featured_image VARCHAR(1024),
                    author_id INT,
                    status ENUM('published', 'draft') DEFAULT 'draft',
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            `);
            console.log('✅ Checked/Created blog_posts table');
        } catch (e) {
            console.error('⚠️ Could not create blog_posts table', e.message);
        }

        // 9.1 blog_posts table: category
        try {
            await db.query("ALTER TABLE blog_posts ADD COLUMN category VARCHAR(100) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added blog_posts.category');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ blog_posts.category check skipped/exists');
        }

        // 9.2 blog_posts table: seo_title
        try {
            await db.query("ALTER TABLE blog_posts ADD COLUMN seo_title VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added blog_posts.seo_title');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ blog_posts.seo_title check skipped/exists');
        }

        // 9.3 blog_posts table: seo_description
        try {
            await db.query("ALTER TABLE blog_posts ADD COLUMN seo_description TEXT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added blog_posts.seo_description');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ blog_posts.seo_description check skipped/exists');
        }

        // 9.4 blog_posts table: focus_keyword
        try {
            await db.query("ALTER TABLE blog_posts ADD COLUMN focus_keyword VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added blog_posts.focus_keyword');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ blog_posts.focus_keyword check skipped/exists');
        }

        // 10. operations table
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS operations (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    apt_group VARCHAR(255),
                    description TEXT,
                    story_intro TEXT,
                    difficulty_level VARCHAR(50),
                    display_order INT DEFAULT 0,
                    is_active BOOLEAN DEFAULT TRUE,
                    is_premium BOOLEAN DEFAULT FALSE,
                    passing_grade INT DEFAULT 60,
                    time_limit_hours INT,
                    tags JSON,
                    scenario_prompt TEXT,
                    total_alerts INT DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            `);
            console.log('✅ Checked/Created operations table');
        } catch (e) {
            console.error('⚠️ Could not create operations table', e.message);
        }

        // 10.1 operations table: seo_title
        try {
            await db.query("ALTER TABLE operations ADD COLUMN seo_title VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added operations.seo_title');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ operations.seo_title check skipped/exists');
        }

        // 10.2 operations table: seo_description
        try {
            await db.query("ALTER TABLE operations ADD COLUMN seo_description TEXT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added operations.seo_description');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ operations.seo_description check skipped/exists');
        }


        // 10.3 operations table: slug
        try {
            await db.query("ALTER TABLE operations ADD COLUMN slug VARCHAR(255) DEFAULT NULL", [], { silent: true });
            console.log('✅ Added operations.slug');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ operations.slug check skipped/exists');
        }

        // 10.4 operations table: Data Migration for Null Slugs
        try {
            const { rows: opsWithoutSlug } = await db.query("SELECT id, title FROM operations WHERE slug IS NULL");
            if (opsWithoutSlug.length > 0) {
                console.log(`🔄 Generating slugs for ${opsWithoutSlug.length} operations...`);
                for (const op of opsWithoutSlug) {
                    const slug = op.title.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');
                    await db.query("UPDATE operations SET slug = ? WHERE id = ?", [slug, op.id]);
                }
                console.log('✅ Slugs generated for existing operations.');
            }
        } catch (err) {
            console.error('⚠️ Failed to migrate operation slugs:', err.message);
        }

        // 10.5 operations table: display_order
        try {
            await db.query("ALTER TABLE operations ADD COLUMN display_order INT DEFAULT 0", [], { silent: true });
            console.log('✅ Added operations.display_order');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ operations.display_order check skipped/exists');
        }

        // 11. operation_alerts table
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS operation_alerts (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    operation_id INT NOT NULL,
                    alert_id INT NOT NULL,
                    sequence_order INT NOT NULL,
                    intel_report_title VARCHAR(255),
                    intel_report_content TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX (operation_id),
                    INDEX (alert_id)
                )
            `);
            console.log('✅ Checked/Created operation_alerts table');
        } catch (e) {
            console.error('⚠️ Could not create operation_alerts table', e.message);
        }

        // 12. user_operation_progress table
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS user_operation_progress (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    operation_id INT NOT NULL,
                    current_alert_sequence INT DEFAULT 1,
                    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    completed_at TIMESTAMP NULL,
                    INDEX (user_id),
                    INDEX (operation_id)
                )
            `);
            console.log('✅ Checked/Created user_operation_progress table');
        } catch (e) {
            console.error('⚠️ Could not create user_operation_progress table', e.message);
        }

        // 13. Learning Paths Tables
        try {
            // learning_paths
            await db.query(`
                CREATE TABLE IF NOT EXISTS learning_paths (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    description TEXT,
                    difficulty_level ENUM('beginner', 'intermediate', 'advanced') NOT NULL,
                    estimated_hours INT DEFAULT 0,
                    icon_url VARCHAR(255),
                    display_order INT DEFAULT 0,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            `);
            console.log('✅ Checked/Created learning_paths table');

            // modules
            await db.query(`
                CREATE TABLE IF NOT EXISTS modules (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    learning_path_id INT NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    description TEXT,
                    estimated_hours INT DEFAULT 0,
                    display_order INT DEFAULT 0,
                    prerequisite_module_id INT DEFAULT NULL,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (learning_path_id) REFERENCES learning_paths(id) ON DELETE CASCADE,
                    FOREIGN KEY (prerequisite_module_id) REFERENCES modules(id) ON DELETE SET NULL,
                    INDEX (learning_path_id)
                )
            `);
            console.log('✅ Checked/Created modules table');

            // tasks
            await db.query(`
                CREATE TABLE IF NOT EXISTS tasks (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    module_id INT NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    description TEXT,
                    task_type ENUM('reading', 'challenge', 'quiz', 'alert_link', 'external_resource') DEFAULT 'reading',
                    task_data JSON,
                    xp_reward INT DEFAULT 50,
                    display_order INT DEFAULT 0,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
                    INDEX (module_id)
                )
            `);
            console.log('✅ Checked/Created tasks table');

            // user_task_progress
            await db.query(`
                CREATE TABLE IF NOT EXISTS user_task_progress (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    task_id INT NOT NULL,
                    completed BOOLEAN DEFAULT FALSE,
                    completed_at TIMESTAMP NULL,
                    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 (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                    UNIQUE KEY unique_user_task (user_id, task_id),
                    INDEX (user_id),
                    INDEX (task_id)
                )
            `);
            console.log('✅ Checked/Created user_task_progress table');

            // user_path_progress
            await db.query(`
                CREATE TABLE IF NOT EXISTS user_path_progress (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    learning_path_id INT NOT NULL,
                    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    completed_at TIMESTAMP NULL,
                    percentage_completed DECIMAL(5,2) DEFAULT 0.00,
                    current_module_id INT DEFAULT NULL,
                    certificate_id VARCHAR(255) DEFAULT NULL,
                    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 (learning_path_id) REFERENCES learning_paths(id) ON DELETE CASCADE,
                    FOREIGN KEY (current_module_id) REFERENCES modules(id) ON DELETE SET NULL,
                    UNIQUE KEY unique_user_path (user_id, learning_path_id),
                    INDEX (user_id)
                )
            `);
            console.log('✅ Checked/Created user_path_progress table');
            
            // Check for certificate_id column in user_path_progress in case table existed but column didn't
            try {
                await db.query("ALTER TABLE user_path_progress ADD COLUMN certificate_id VARCHAR(255) DEFAULT NULL", [], { silent: true });
                console.log('✅ Added user_path_progress.certificate_id');
            } catch (err) {
                 if (!err.message.includes('Duplicate column')) console.log('ℹ️ user_path_progress.certificate_id check skipped');
            }


            // badges
            await db.query(`
                CREATE TABLE IF NOT EXISTS badges (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    description TEXT,
                    icon_url VARCHAR(255),
                    badge_type ENUM('path_completion', 'milestone', 'streak', 'special') DEFAULT 'milestone',
                    criteria JSON,
                    xp_reward INT DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            `);
            console.log('✅ Checked/Created badges table');

            // user_badges
            await db.query(`
                CREATE TABLE IF NOT EXISTS user_badges (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    badge_id INT NOT NULL,
                    earned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE,
                    UNIQUE KEY unique_user_badge (user_id, badge_id),
                    INDEX (user_id)
                )
            `);
            console.log('✅ Checked/Created user_badges table');

            // 13.1 user_xp table fix (ensure it exists)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS user_xp (
                    user_id INT PRIMARY KEY,
                    total_xp INT DEFAULT 0,
                    current_level INT DEFAULT 1,
                    xp_to_next_level INT DEFAULT 100,
                    streak_days INT DEFAULT 0,
                    last_activity_date DATE,
                    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
                )
            `);
            console.log('✅ Checked/Created user_xp table');
        } catch (e) {
            console.error('⚠️ Could not create user_xp table', e.message);
        }

        // 15. users table: organization_id
        try {
            await db.query("ALTER TABLE users ADD COLUMN organization_id INT DEFAULT NULL", [], { silent: true });
            console.log('✅ Added users.organization_id');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.organization_id check skipped/exists');
        }

        // 17. users table: registration_source
        try {
            await db.query("ALTER TABLE users ADD COLUMN registration_source VARCHAR(50) DEFAULT 'web'", [], { silent: true });
            console.log('✅ Added users.registration_source');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.registration_source check skipped/exists');
        }

        } catch (e) {
            console.error('⚠️ Could not create Learning Paths tables', e.message);
        }

        // 13.2 Referral System Migration
        try {
            console.log('🔄 Checking Referral System columns...');
            // Add referral_code to users
            try {
                await db.query("ALTER TABLE users ADD COLUMN referral_code VARCHAR(50) DEFAULT NULL", [], { silent: true });
                console.log('✅ Added users.referral_code');
            } catch (err) {
                if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.referral_code check skipped/exists');
            }

            // Add referred_by to users
            try {
                await db.query("ALTER TABLE users ADD COLUMN referred_by INT DEFAULT NULL", [], { silent: true });
                console.log('✅ Added users.referred_by');
            } catch (err) {
                if (!err.message.includes('Duplicate column')) console.log('ℹ️ users.referred_by check skipped/exists');
            }

            // Create referrals table
            await db.query(`
                CREATE TABLE IF NOT EXISTS referrals (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    referrer_id INT NOT NULL,
                    referee_id INT NOT NULL,
                    status ENUM('pending', 'active', 'subscribed') DEFAULT 'pending',
                    reward_earned BOOLEAN DEFAULT FALSE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
                    FOREIGN KEY (referee_id) REFERENCES users(id) ON DELETE CASCADE
                )
            `);
            console.log('✅ Checked/Created referrals table');

            // Backfill referral codes for users who don't have one
            const { rows: usersWithoutCode } = await db.query("SELECT id, username, name FROM users WHERE referral_code IS NULL");
            if (usersWithoutCode.length > 0) {
                const crypto = require('crypto');
                console.log(`🔄 Backfilling referral codes for ${usersWithoutCode.length} users...`);
                for (const user of usersWithoutCode) {
                    const cleanName = (user.username || user.name || 'USER').replace(/[^A-Za-z0-9]/g, '').substring(0, 5).toUpperCase();
                    const refStr = crypto.randomBytes(2).toString('hex').toUpperCase();
                    const code = `${cleanName}-${user.id}-${refStr}`;
                    await db.query("UPDATE users SET referral_code = ? WHERE id = ?", [code, user.id]);
                }
                console.log('✅ Referral codes backfilled.');
            }
        } catch (e) {
            console.error('⚠️ Referral System migration failed:', e.message);
        }

        // 14. Retroactive Student Domain Fix
        try {
            await db.query(`
                UPDATE users u
                JOIN student_domains sd
                ON SUBSTRING_INDEX(u.email, '@', -1) = sd.domain
                SET u.is_student = 1
                WHERE u.is_student = 0
            `, [], { silent: true });
            console.log('✅ Retroactively updated existing students based on whitelist.');
        } catch (e) {
            console.error('⚠️ Could not run retroactive student update:', e.message);
        }

        // 16. alerts table: simulation_data (For Micro-Simulators)
        try {
            await db.query("ALTER TABLE alerts ADD COLUMN simulation_data JSON DEFAULT NULL");
            console.log('✅ Added alerts.simulation_data');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ alerts.simulation_data check skipped/exists');
        }

        // 18. simulated_logs table (Threat Hunting / SIEM)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS simulated_logs (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    source_ip VARCHAR(45) DEFAULT NULL,
                    dest_ip VARCHAR(45) DEFAULT NULL,
                    event_type VARCHAR(100) DEFAULT NULL,
                    action VARCHAR(50) DEFAULT NULL,
                    raw_data JSON DEFAULT NULL,
                    index_name VARCHAR(50) DEFAULT 'main',
                    alert_id INT DEFAULT NULL,
                    KEY idx_timestamp (timestamp),
                    KEY idx_source_ip (source_ip),
                    KEY idx_dest_ip (dest_ip),
                    KEY idx_index_name (index_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            `);
            console.log('✅ Checked/Created simulated_logs table');

            // Seed with 500 realistic SIEM logs if empty
            const seedSimulatedLogs = require('./utils/seedSimulatedLogs');
            await seedSimulatedLogs(db, 500);
        } catch (e) {
            console.error('⚠️ Could not create/seed simulated_logs table', e.message);
        }

        // 19. email_logs table (For Email Management)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS email_logs (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    recipient_email VARCHAR(255) NOT NULL,
                    subject VARCHAR(255),
                    status VARCHAR(50),
                    error_message TEXT,
                    user_id INT DEFAULT NULL,
                    body LONGTEXT,
                    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX (recipient_email),
                    INDEX (sent_at),
                    INDEX (user_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            `, [], { silent: true });
            console.log('✅ Checked/Created email_logs table');

            // Explicitly check for 'body' column if table existed but was old
            try {
                const hasBodyCol = await db.query("SHOW COLUMNS FROM email_logs LIKE 'body'");
                if (hasBodyCol.rows.length === 0) {
                    await db.query("ALTER TABLE email_logs ADD COLUMN body LONGTEXT DEFAULT NULL AFTER user_id");
                    console.log('✅ Added body column to email_logs table');
                }
            } catch (e) {
                console.error('⚠️ Could not check/add body column to email_logs', e.message);
            }
        } catch (e) {
            console.error('⚠️ Could not create email_logs table', e.message);
        }

        // 20. mobile_triage_results table (For Mobile App)
        try {
            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,
                    duration_seconds INT DEFAULT NULL,
                    xp_earned INT DEFAULT 0,
                    game_type VARCHAR(50) DEFAULT 'triage',
                    played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX (user_id),
                    INDEX (played_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            `);
            console.log('✅ Checked/Created mobile_triage_results table');
        } catch (e) {
            console.error('⚠️ Could not create mobile_triage_results table', e.message);
        }

        // 21. user_skills table (For Public Profile Radar Chart)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS user_skills (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    skill_name VARCHAR(100) NOT NULL,
                    level INT DEFAULT 0,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    UNIQUE KEY unique_user_skill (user_id, skill_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            `);
            console.log('✅ Checked/Created user_skills table');
        } catch (e) {
            console.error('⚠️ Could not create user_skills table', e.message);
        }

        // 22. user_badges table: is_pinned
        try {
            await db.query("ALTER TABLE user_badges ADD COLUMN is_pinned BOOLEAN DEFAULT FALSE", [], { silent: true });
            console.log('✅ Added user_badges.is_pinned');
        } catch (err) {
            if (!err.message.includes('Duplicate column')) console.log('ℹ️ user_badges.is_pinned check skipped');
        }

        // 23. seo_metadata table (For SEO Management)
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS seo_metadata (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    route VARCHAR(255) UNIQUE NOT NULL,
                    title VARCHAR(255),
                    description TEXT,
                    keywords TEXT,
                    og_image TEXT,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            `);
            console.log('✅ Checked/Created seo_metadata table');
            
            // Seed default SEO data if empty
            const seoCount = await db.query("SELECT COUNT(*) as count FROM seo_metadata");
            if (seoCount.rows[0].count === 0) {
                await db.query(`
                    INSERT IGNORE INTO seo_metadata (route, title, description) VALUES
                    ('/', 'InfoSecLabs | SOC Analyst Training & Cybersecurity Academy', 'Master cybersecurity with realistic SOC simulations, hand-on labs, and narrative-driven missions.'),
                    ('/missions', 'Cyber Missions | InfoSecLabs', 'Engage in narrative-driven cyber attack simulations and master incident response.'),
                    ('/blog', 'Cybersecurity Blog | InfoSecLabs', 'Stay updated with the latest in cybersecurity, threat intelligence, and training tips.')
                `);
                console.log('🌱 Seeded default SEO metadata.');
            }
        } catch (e) {
            console.error('⚠️ Could not create seo_metadata table', e.message);
        }

        // 24. analytics_events table
        try {
            await db.query(`
                CREATE TABLE IF NOT EXISTS analytics_events (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT DEFAULT NULL,
                    event_type VARCHAR(100) NOT NULL,
                    event_data JSON DEFAULT NULL,
                    ip_address VARCHAR(45) DEFAULT NULL,
                    user_agent TEXT DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX (user_id),
                    INDEX (event_type),
                    INDEX (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            `);
            console.log('✅ Checked/Created analytics_events table');
        } catch (e) {
            console.error('⚠️ Could not create analytics_events table', e.message);
        }

        console.log('--- Database Auto-Migration Complete ---');

    } catch (error) {
        console.error('❌ Database Auto-Migration Failed:', error);
    }
};

module.exports = migrateDb;

