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

// 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 testRolesPermissions() {
    let connection;
    
    try {
        console.log('🔗 Connecting to database...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ Database connected successfully');

        console.log('\n📊 Testing Roles and Permissions System...\n');

        // Test 1: Check roles
        console.log('1️⃣ Testing Roles:');
        const [roles] = await connection.execute('SELECT * FROM roles ORDER BY level DESC');
        console.log(`   Found ${roles.length} roles:`);
        roles.forEach(role => {
            console.log(`   - ${role.name} (Level: ${role.level}) - ${role.description}`);
        });

        // Test 2: Check permissions
        console.log('\n2️⃣ Testing Permissions:');
        const [permissions] = await connection.execute('SELECT * FROM permissions ORDER BY resource, action');
        console.log(`   Found ${permissions.length} permissions:`);
        
        // Group permissions by resource
        const permissionsByResource = {};
        permissions.forEach(perm => {
            if (!permissionsByResource[perm.resource]) {
                permissionsByResource[perm.resource] = [];
            }
            permissionsByResource[perm.resource].push(perm);
        });

        Object.keys(permissionsByResource).forEach(resource => {
            console.log(`   📁 ${resource.toUpperCase()}:`);
            permissionsByResource[resource].forEach(perm => {
                console.log(`      - ${perm.action}: ${perm.description}`);
            });
        });

        // Test 3: Check role-permission mappings
        console.log('\n3️⃣ Testing Role-Permission Mappings:');
        const [rolePermissions] = await connection.execute(`
            SELECT r.name as role_name, r.level, COUNT(rp.permission_id) as permission_count
            FROM roles r
            LEFT JOIN role_permissions rp ON r.id = rp.role_id
            GROUP BY r.id, r.name, r.level
            ORDER BY r.level DESC
        `);

        rolePermissions.forEach(rp => {
            console.log(`   ${rp.role_name} (Level ${rp.level}): ${rp.permission_count} permissions`);
        });

        // Test 4: Detailed permission breakdown for each role
        console.log('\n4️⃣ Detailed Permission Breakdown:');
        for (const role of roles) {
            const [rolePerms] = await connection.execute(`
                SELECT p.name, p.resource, p.action, p.description
                FROM role_permissions rp
                JOIN permissions p ON rp.permission_id = p.id
                WHERE rp.role_id = ?
                ORDER BY p.resource, p.action
            `, [role.id]);

            console.log(`\n   👤 ${role.name} (${rolePerms.length} permissions):`);
            
            const groupedPerms = {};
            rolePerms.forEach(perm => {
                if (!groupedPerms[perm.resource]) {
                    groupedPerms[perm.resource] = [];
                }
                groupedPerms[perm.resource].push(perm);
            });

            Object.keys(groupedPerms).forEach(resource => {
                console.log(`      📁 ${resource}: ${groupedPerms[resource].map(p => p.action).join(', ')}`);
            });
        }

        // Test 5: Check for any orphaned data
        console.log('\n5️⃣ Checking for Data Integrity:');
        
        // Check for orphaned role_permissions
        const [orphanedRolePerms] = await connection.execute(`
            SELECT COUNT(*) as count FROM role_permissions rp
            LEFT JOIN roles r ON rp.role_id = r.id
            WHERE r.id IS NULL
        `);
        console.log(`   Orphaned role_permissions: ${orphanedRolePerms[0].count}`);

        // Check for orphaned permissions in role_permissions
        const [orphanedPerms] = await connection.execute(`
            SELECT COUNT(*) as count FROM role_permissions rp
            LEFT JOIN permissions p ON rp.permission_id = p.id
            WHERE p.id IS NULL
        `);
        console.log(`   Orphaned permissions in role_permissions: ${orphanedPerms[0].count}`);

        // Check for orphaned user_permissions
        const [orphanedUserPerms] = await connection.execute(`
            SELECT COUNT(*) as count FROM user_permissions up
            LEFT JOIN users u ON up.user_id = u.id
            WHERE u.id IS NULL
        `);
        console.log(`   Orphaned user_permissions: ${orphanedUserPerms[0].count}`);

        // Test 6: Verify specific permission patterns
        console.log('\n6️⃣ Verifying Permission Patterns:');
        
        // Check if Super Admin has all permissions
        const [superAdminPerms] = await connection.execute(`
            SELECT COUNT(*) as count FROM role_permissions rp
            JOIN roles r ON rp.role_id = r.id
            WHERE r.name = 'Super Admin'
        `);
        console.log(`   Super Admin permissions: ${superAdminPerms[0].count}/${permissions.length} (${((superAdminPerms[0].count/permissions.length)*100).toFixed(1)}%)`);

        // Check if User role has basic permissions only
        const [userRolePerms] = await connection.execute(`
            SELECT COUNT(*) as count FROM role_permissions rp
            JOIN roles r ON rp.role_id = r.id
            WHERE r.name = 'User'
        `);
        console.log(`   User role permissions: ${userRolePerms[0].count}`);

        console.log('\n✅ All tests completed successfully!');
        console.log('\n📋 Summary:');
        console.log(`   - Total Roles: ${roles.length}`);
        console.log(`   - Total Permissions: ${permissions.length}`);
        console.log(`   - Total Role-Permission Mappings: ${rolePermissions.reduce((sum, rp) => sum + rp.permission_count, 0)}`);
        console.log(`   - Data Integrity: ${orphanedRolePerms[0].count + orphanedPerms[0].count + orphanedUserPerms[0].count === 0 ? '✅ Clean' : '❌ Issues Found'}`);

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

// Run the test
if (require.main === module) {
    testRolesPermissions()
        .then(() => {
            console.log('\n✨ Test completed successfully!');
            process.exit(0);
        })
        .catch((error) => {
            console.error('\n💥 Test failed:', error.message);
            process.exit(1);
        });
}

module.exports = { testRolesPermissions };
