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

/**
 * Script to assign User role (ID: 5) to users in all organizations
 * This ensures all organization users have proper initial permissions
 */

// 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 || 'cms_db',
    port: process.env.DB_PORT || 3306
};

async function assignUserRoleToOrganizationUsers() {
    let connection;
    
    try {
        console.log('🔗 Connecting to database...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ Database connected successfully\n');

        // Get all organizations
        console.log('📊 Fetching organizations...');
        const [organizations] = await connection.execute(
            'SELECT id, name, code FROM organizations WHERE is_active = 1'
        );
        console.log(`✅ Found ${organizations.length} active organizations\n`);

        // Get User role information (ID: 5)
        const [userRole] = await connection.execute(
            'SELECT id, name, level FROM roles WHERE id = 5'
        );
        
        if (userRole.length === 0) {
            console.log('❌ User role (ID: 5) not found!');
            console.log('💡 Please run clean_and_rebuild_roles_permissions.js first');
            return;
        }

        console.log(`📋 User Role Details:`);
        console.log(`   ID: ${userRole[0].id}`);
        console.log(`   Name: ${userRole[0].name}`);
        console.log(`   Level: ${userRole[0].level}\n`);

        // Get User role permissions
        const [permissions] = await connection.execute(`
            SELECT p.id, p.name, p.description
            FROM permissions p
            JOIN role_permissions rp ON p.id = rp.permission_id
            WHERE rp.role_id = 5
            ORDER BY p.resource, p.action
        `);

        console.log(`✅ User role has ${permissions.length} permissions\n`);

        // Process each organization
        let totalUpdated = 0;
        let totalSkipped = 0;

        for (const org of organizations) {
            console.log(`\n📍 Processing: ${org.name} (${org.code})`);
            console.log('─'.repeat(60));

            // Get users in this organization with incorrect or no role
            const [usersToUpdate] = await connection.execute(`
                SELECT id, name, email, role_id, 
                       (SELECT name FROM roles WHERE id = u.role_id) as current_role_name
                FROM users u
                WHERE organization_id = ? 
                AND is_active = 1
                AND (role_id IS NULL OR role_id NOT IN (1, 2, 5))
            `, [org.id]);

            if (usersToUpdate.length === 0) {
                console.log('   ✓ All users already have proper roles');
                continue;
            }

            console.log(`   Found ${usersToUpdate.length} users to update:`);
            
            for (const user of usersToUpdate) {
                const currentRole = user.current_role_name || 'None';
                console.log(`   - ${user.name} (${user.email}): ${currentRole} → User`);
                
                // Update user role
                await connection.execute(
                    'UPDATE users SET role_id = 5, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                    [user.id]
                );
                
                totalUpdated++;
            }

            console.log(`   ✅ Updated ${usersToUpdate.length} users in ${org.name}`);

            // Get current user statistics for this organization
            const [stats] = await connection.execute(`
                SELECT 
                    COUNT(*) as total_users,
                    SUM(CASE WHEN role_id = 1 THEN 1 ELSE 0 END) as super_admins,
                    SUM(CASE WHEN role_id = 2 THEN 1 ELSE 0 END) as admins,
                    SUM(CASE WHEN role_id = 5 THEN 1 ELSE 0 END) as users
                FROM users
                WHERE organization_id = ? AND is_active = 1
            `, [org.id]);

            console.log(`\n   📊 Current Statistics for ${org.name}:`);
            console.log(`      Total Users: ${stats[0].total_users}`);
            console.log(`      Super Admins: ${stats[0].super_admins}`);
            console.log(`      Admins: ${stats[0].admins}`);
            console.log(`      Users: ${stats[0].users}`);
        }

        console.log('\n' + '='.repeat(60));
        console.log('🎉 User Role Assignment Completed!');
        console.log('='.repeat(60));
        console.log(`📊 Summary:`);
        console.log(`   Organizations Processed: ${organizations.length}`);
        console.log(`   Users Updated: ${totalUpdated}`);
        console.log(`   Users Skipped: ${totalSkipped}\n`);

        // Get overall statistics
        const [overallStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_users,
                SUM(CASE WHEN role_id = 1 THEN 1 ELSE 0 END) as super_admins,
                SUM(CASE WHEN role_id = 2 THEN 1 ELSE 0 END) as admins,
                SUM(CASE WHEN role_id = 3 THEN 1 ELSE 0 END) as managers,
                SUM(CASE WHEN role_id = 4 THEN 1 ELSE 0 END) as officers,
                SUM(CASE WHEN role_id = 5 THEN 1 ELSE 0 END) as users,
                COUNT(DISTINCT organization_id) as total_organizations
            FROM users
            WHERE is_active = 1
        `);

        console.log('📊 Overall System Statistics:');
        console.log(`   Total Organizations: ${overallStats[0].total_organizations}`);
        console.log(`   Total Active Users: ${overallStats[0].total_users}`);
        console.log(`   └─ Super Admins: ${overallStats[0].super_admins}`);
        console.log(`   └─ Admins: ${overallStats[0].admins}`);
        console.log(`   └─ Managers: ${overallStats[0].managers}`);
        console.log(`   └─ Officers: ${overallStats[0].officers}`);
        console.log(`   └─ Users: ${overallStats[0].users}\n`);

        console.log('✅ All organization users now have proper User role permissions!\n');

    } catch (error) {
        console.error('❌ Error assigning user roles:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 Database connection closed\n');
        }
    }
}

// Option to assign User role to specific organization
async function assignUserRoleToSpecificOrganization(organizationId) {
    let connection;
    
    try {
        console.log('🔗 Connecting to database...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ Database connected successfully\n');

        // Get organization details
        const [org] = await connection.execute(
            'SELECT id, name, code FROM organizations WHERE id = ? AND is_active = 1',
            [organizationId]
        );

        if (org.length === 0) {
            console.log(`❌ Organization with ID ${organizationId} not found or inactive`);
            return;
        }

        console.log(`📍 Processing Organization: ${org[0].name} (${org[0].code})\n`);

        // Get users to update
        const [usersToUpdate] = await connection.execute(`
            SELECT id, name, email, role_id,
                   (SELECT name FROM roles WHERE id = u.role_id) as current_role_name
            FROM users u
            WHERE organization_id = ? 
            AND is_active = 1
            AND (role_id IS NULL OR role_id NOT IN (1, 2, 5))
        `, [organizationId]);

        if (usersToUpdate.length === 0) {
            console.log('✓ All users already have proper roles');
            return;
        }

        console.log(`Found ${usersToUpdate.length} users to update:\n`);
        
        for (const user of usersToUpdate) {
            const currentRole = user.current_role_name || 'None';
            console.log(`   - ${user.name} (${user.email}): ${currentRole} → User`);
            
            // Update user role
            await connection.execute(
                'UPDATE users SET role_id = 5, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
                [user.id]
            );
        }

        console.log(`\n✅ Updated ${usersToUpdate.length} users in ${org[0].name}\n`);

    } catch (error) {
        console.error('❌ Error:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 Database connection closed\n');
        }
    }
}

// Run the script
if (require.main === module) {
    const args = process.argv.slice(2);
    
    if (args.length > 0 && args[0] === '--org') {
        const orgId = parseInt(args[1]);
        if (isNaN(orgId)) {
            console.log('❌ Invalid organization ID');
            console.log('Usage: node assign_user_role_to_organization_users.js --org <organization_id>');
            process.exit(1);
        }
        
        assignUserRoleToSpecificOrganization(orgId)
            .then(() => {
                console.log('✨ Script completed successfully!');
                process.exit(0);
            })
            .catch((error) => {
                console.error('💥 Script failed:', error.message);
                process.exit(1);
            });
    } else {
        assignUserRoleToOrganizationUsers()
            .then(() => {
                console.log('✨ Script completed successfully!');
                process.exit(0);
            })
            .catch((error) => {
                console.error('💥 Script failed:', error.message);
                process.exit(1);
            });
    }
}

module.exports = { 
    assignUserRoleToOrganizationUsers,
    assignUserRoleToSpecificOrganization
};

