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

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

        // Test 1: Get all users with their roles and permissions
        console.log('1️⃣ Testing User-Role-Permission Relationships:');
        const [users] = await connection.execute(`
            SELECT 
                u.id,
                u.name,
                u.email,
                u.role_id,
                r.name as role_name,
                r.level as role_level,
                COUNT(DISTINCT rp.permission_id) as permission_count
            FROM users u
            JOIN roles r ON u.role_id = r.id
            LEFT JOIN role_permissions rp ON r.id = rp.role_id
            WHERE u.is_active = 1
            GROUP BY u.id, u.name, u.email, u.role_id, r.name, r.level
            ORDER BY r.level DESC
        `);

        console.log(`   Found ${users.length} active users:`);
        users.forEach(user => {
            console.log(`   👤 ${user.name} (${user.email})`);
            console.log(`      Role: ${user.role_name} (Level: ${user.role_level})`);
            console.log(`      Permissions: ${user.permission_count}`);
        });

        // Test 2: Test specific user permissions
        console.log('\n2️⃣ Testing Specific User Permissions:');
        for (const user of users) {
            const [permissions] = await connection.execute(`
                SELECT DISTINCT p.name, p.description, p.resource, p.action
                FROM permissions p
                WHERE p.is_active = 1
                AND p.id IN (
                    SELECT rp.permission_id 
                    FROM role_permissions rp
                    WHERE rp.role_id = ?
                )
                ORDER BY p.resource, p.action
            `, [user.role_id]);

            console.log(`\n   👤 ${user.name} (${user.role_name}):`);
            console.log(`      Total Permissions: ${permissions.length}`);
            
            // 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}: ${permissionsByResource[resource].map(p => p.action).join(', ')}`);
            });

            // Test specific permissions that should be present
            const testPermissions = [
                'settings.access',
                'admin.access',
                'directory.access',
                'conversations.access',
                'profile_page.access'
            ];

            console.log(`      🔍 Permission Tests:`);
            testPermissions.forEach(perm => {
                const hasPermission = permissions.some(p => p.name === perm);
                console.log(`         ${hasPermission ? '✅' : '❌'} ${perm}`);
            });
        }

        // Test 3: Test Super Admin permissions
        console.log('\n3️⃣ Testing Super Admin Permissions:');
        const [superAdmin] = await connection.execute(`
            SELECT u.id, u.name, u.email, r.name as role_name
            FROM users u
            JOIN roles r ON u.role_id = r.id
            WHERE r.name = 'Super Admin' AND u.is_active = 1
            LIMIT 1
        `);

        if (superAdmin.length > 0) {
            const admin = superAdmin[0];
            const [adminPermissions] = await connection.execute(`
                SELECT DISTINCT p.name
                FROM permissions p
                WHERE p.is_active = 1
                AND p.id IN (
                    SELECT rp.permission_id 
                    FROM role_permissions rp
                    WHERE rp.role_id = ?
                )
                ORDER BY p.name
            `, [admin.role_id]);

            console.log(`   👤 ${admin.name} (${admin.role_name}):`);
            console.log(`      Total Permissions: ${adminPermissions.length}`);
            
            // Check for critical permissions
            const criticalPermissions = [
                'settings.access',
                'admin.access',
                'role.create',
                'role.update',
                'role.delete',
                'permission.create',
                'permission.update',
                'permission.delete',
                'user.create',
                'user.update',
                'user.delete'
            ];

            console.log(`      🔍 Critical Permission Tests:`);
            criticalPermissions.forEach(perm => {
                const hasPermission = adminPermissions.some(p => p.name === perm);
                console.log(`         ${hasPermission ? '✅' : '❌'} ${perm}`);
            });
        }

        // Test 4: Test User role permissions
        console.log('\n4️⃣ Testing User Role Permissions:');
        const [regularUsers] = await connection.execute(`
            SELECT u.id, u.name, u.email, r.name as role_name
            FROM users u
            JOIN roles r ON u.role_id = r.id
            WHERE r.name = 'User' AND u.is_active = 1
            LIMIT 1
        `);

        if (regularUsers.length > 0) {
            const user = regularUsers[0];
            const [userPermissions] = await connection.execute(`
                SELECT DISTINCT p.name
                FROM permissions p
                WHERE p.is_active = 1
                AND p.id IN (
                    SELECT rp.permission_id 
                    FROM role_permissions rp
                    WHERE rp.role_id = ?
                )
                ORDER BY p.name
            `, [user.role_id]);

            console.log(`   👤 ${user.name} (${user.role_name}):`);
            console.log(`      Total Permissions: ${userPermissions.length}`);
            
            // Check for basic permissions
            const basicPermissions = [
                'dashboard.view',
                'directory.access',
                'conversations.access',
                'profile_page.access',
                'settings.access'
            ];

            console.log(`      🔍 Basic Permission Tests:`);
            basicPermissions.forEach(perm => {
                const hasPermission = userPermissions.some(p => p.name === perm);
                console.log(`         ${hasPermission ? '✅' : '❌'} ${perm}`);
            });

            // Check for restricted permissions (should NOT have)
            const restrictedPermissions = [
                'admin.access',
                'role.create',
                'role.update',
                'role.delete',
                'permission.create',
                'user.create',
                'user.delete'
            ];

            console.log(`      🔍 Restricted Permission Tests (should be ❌):`);
            restrictedPermissions.forEach(perm => {
                const hasPermission = userPermissions.some(p => p.name === perm);
                console.log(`         ${hasPermission ? '❌' : '✅'} ${perm} (should not have)`);
            });
        }

        console.log('\n✅ All permission tests completed successfully!');

    } 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) {
    testUserPermissions()
        .then(() => {
            console.log('\n✨ Permission test completed successfully!');
            process.exit(0);
        })
        .catch((error) => {
            console.error('\n💥 Permission test failed:', error.message);
            process.exit(1);
        });
}

module.exports = { testUserPermissions };
