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

const applyMigration = async () => {
    try {
        console.log('--- Applying Manual Migration ---');

        // 1. Add operation_id column
        try {
            await db.query(`ALTER TABLE organization_path_assignments ADD COLUMN operation_id INT NULL AFTER learning_path_id`);
            console.log('✅ Added operation_id column');
        } catch (err) {
            if (err.message.includes('Duplicate column')) console.log('ℹ️ operation_id column already exists');
            else console.error('❌ Error adding column:', err.message);
        }

        // 2. Make learning_path_id nullable
        try {
            await db.query(`ALTER TABLE organization_path_assignments MODIFY COLUMN learning_path_id INT NULL`);
            console.log('✅ Modified learning_path_id to be NULL');
        } catch (err) {
            console.error('❌ Error modifying column:', err.message);
        }

        // 3. Drop old index
        try {
            await db.query(`ALTER TABLE organization_path_assignments DROP INDEX unique_user_path`);
            console.log('✅ Dropped old unique index');
        } catch (err) {
             if (err.message.includes("check that column/key exists")) console.log('ℹ️ Old index not found (already dropped?)');
             else console.error('⚠️ Error dropping index:', err.message);
        }

        // 4. Add new unique key
        try {
            await db.query(`ALTER TABLE organization_path_assignments ADD UNIQUE KEY unique_user_assignment (user_id, learning_path_id, operation_id)`);
            console.log('✅ Added new unique key');
        } catch (err) {
            if (err.message.includes('Duplicate key')) console.log('ℹ️ Unique key already exists');
            else console.error('❌ Error adding key:', err.message);
        }

        console.log('--- Migration Complete ---');
        process.exit(0);
    } catch (err) {
        console.error('Script Error:', err);
        process.exit(1);
    }
};

applyMigration();
