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
};

// Fresh roles data
const freshRoles = [
    {
        id: 1,
        name: 'Super Admin',
        description: 'Super Administrator with full system access and control',
        level: 100,
        is_active: 1
    },
    {
        id: 2,
        name: 'Admin',
        description: 'Administrator with elevated privileges and management access',
        level: 50,
        is_active: 1
    },
    {
        id: 3,
        name: 'Manager',
        description: 'Manager with departmental oversight and user management',
        level: 30,
        is_active: 1
    },
    {
        id: 4,
        name: 'Officer',
        description: 'Officer with operational access and reporting capabilities',
        level: 20,
        is_active: 1
    },
    {
        id: 5,
        name: 'User',
        description: 'Regular user with standard access and basic permissions',
        level: 10,
        is_active: 1
    }
];

// Fresh permissions data
const freshPermissions = [
    // User Management Permissions
    { name: 'user.create', description: 'Create new users', resource: 'user', action: 'create' },
    { name: 'user.read', description: 'View user information', resource: 'user', action: 'read' },
    { name: 'user.update', description: 'Update user information', resource: 'user', action: 'update' },
    { name: 'user.delete', description: 'Delete users', resource: 'user', action: 'delete' },
    { name: 'user.manage_roles', description: 'Manage user roles and permissions', resource: 'user', action: 'manage_roles' },
    { name: 'user.export', description: 'Export user data', resource: 'user', action: 'export' },
    { name: 'user.import', description: 'Import user data', resource: 'user', action: 'import' },

    // Unit Management Permissions
    { name: 'unit.create', description: 'Create new units', resource: 'unit', action: 'create' },
    { name: 'unit.read', description: 'View unit information', resource: 'unit', action: 'read' },
    { name: 'unit.update', description: 'Update unit information', resource: 'unit', action: 'update' },
    { name: 'unit.delete', description: 'Delete units', resource: 'unit', action: 'delete' },

    // Department Management Permissions
    { name: 'department.create', description: 'Create new departments', resource: 'department', action: 'create' },
    { name: 'department.read', description: 'View department information', resource: 'department', action: 'read' },
    { name: 'department.update', description: 'Update department information', resource: 'department', action: 'update' },
    { name: 'department.delete', description: 'Delete departments', resource: 'department', action: 'delete' },

    // Designation Management Permissions
    { name: 'designation.create', description: 'Create new designations', resource: 'designation', action: 'create' },
    { name: 'designation.read', description: 'View designation information', resource: 'designation', action: 'read' },
    { name: 'designation.update', description: 'Update designation information', resource: 'designation', action: 'update' },
    { name: 'designation.delete', description: 'Delete designations', resource: 'designation', action: 'delete' },

    // Rank Management Permissions
    { name: 'rank.create', description: 'Create new ranks', resource: 'rank', action: 'create' },
    { name: 'rank.read', description: 'View rank information', resource: 'rank', action: 'read' },
    { name: 'rank.update', description: 'Update rank information', resource: 'rank', action: 'update' },
    { name: 'rank.delete', description: 'Delete ranks', resource: 'rank', action: 'delete' },

    // Contact Directory Permissions
    { name: 'contact.create', description: 'Create new contacts', resource: 'contact', action: 'create' },
    { name: 'contact.read', description: 'View contact information', resource: 'contact', action: 'read' },
    { name: 'contact.update', description: 'Update contact information', resource: 'contact', action: 'update' },
    { name: 'contact.delete', description: 'Delete contacts', resource: 'contact', action: 'delete' },
    { name: 'contact.export', description: 'Export contact data', resource: 'contact', action: 'export' },
    { name: 'contact.import', description: 'Import contact data', resource: 'contact', action: 'import' },

    // Messaging Permissions
    { name: 'message.create', description: 'Send messages', resource: 'message', action: 'create' },
    { name: 'message.read', description: 'Read messages', resource: 'message', action: 'read' },
    { name: 'message.update', description: 'Update messages', resource: 'message', action: 'update' },
    { name: 'message.delete', description: 'Delete messages', resource: 'message', action: 'delete' },
    { name: 'message.broadcast', description: 'Send broadcast messages', resource: 'message', action: 'broadcast' },

    // Conversation Permissions
    { name: 'conversation.create', description: 'Create conversations', resource: 'conversation', action: 'create' },
    { name: 'conversation.read', description: 'View conversations', resource: 'conversation', action: 'read' },
    { name: 'conversation.update', description: 'Update conversations', resource: 'conversation', action: 'update' },
    { name: 'conversation.delete', description: 'Delete conversations', resource: 'conversation', action: 'delete' },
    { name: 'conversation.manage', description: 'Manage conversation participants', resource: 'conversation', action: 'manage' },

    // Notification Permissions
    { name: 'notification.create', description: 'Create notifications', resource: 'notification', action: 'create' },
    { name: 'notification.read', description: 'View notifications', resource: 'notification', action: 'read' },
    { name: 'notification.update', description: 'Update notifications', resource: 'notification', action: 'update' },
    { name: 'notification.delete', description: 'Delete notifications', resource: 'notification', action: 'delete' },
    { name: 'notification.broadcast', description: 'Send broadcast notifications', resource: 'notification', action: 'broadcast' },

    // Feedback Permissions
    { name: 'feedback.create', description: 'Submit feedback', resource: 'feedback', action: 'create' },
    { name: 'feedback.read', description: 'View feedback', resource: 'feedback', action: 'read' },
    { name: 'feedback.update', description: 'Update feedback status', resource: 'feedback', action: 'update' },
    { name: 'feedback.delete', description: 'Delete feedback', resource: 'feedback', action: 'delete' },
    { name: 'feedback.respond', description: 'Respond to feedback', resource: 'feedback', action: 'respond' },

    // Audit Log Permissions
    { name: 'audit_log.read', description: 'View audit logs', resource: 'audit_log', action: 'read' },
    { name: 'audit_log.delete', description: 'Delete audit logs', resource: 'audit_log', action: 'delete' },
    { name: 'audit_log.export', description: 'Export audit logs', resource: 'audit_log', action: 'export' },

    // System Settings Permissions
    { name: 'system_settings.read', description: 'View system settings', resource: 'system_settings', action: 'read' },
    { name: 'system_settings.update', description: 'Update system settings', resource: 'system_settings', action: 'update' },
    { name: 'system_settings.backup', description: 'Create system backups', resource: 'system_settings', action: 'backup' },
    { name: 'system_settings.restore', description: 'Restore system from backup', resource: 'system_settings', action: 'restore' },

    // Dashboard Permissions
    { name: 'dashboard.view', description: 'Access dashboard', resource: 'dashboard', action: 'view' },
    { name: 'dashboard.analytics', description: 'View analytics and statistics', resource: 'dashboard', action: 'analytics' },
    { name: 'dashboard.reports', description: 'Generate and view reports', resource: 'dashboard', action: 'reports' },
    { name: 'dashboard.customize', description: 'Customize dashboard layout', resource: 'dashboard', action: 'customize' },

    // File Management Permissions
    { name: 'file.upload', description: 'Upload files', resource: 'file', action: 'upload' },
    { name: 'file.download', description: 'Download files', resource: 'file', action: 'download' },
    { name: 'file.delete', description: 'Delete files', resource: 'file', action: 'delete' },
    { name: 'file.manage', description: 'Manage file permissions', resource: 'file', action: 'manage' },

    // Profile Management Permissions
    { name: 'profile.read', description: 'View own profile', resource: 'profile', action: 'read' },
    { name: 'profile.update', description: 'Update own profile', resource: 'profile', action: 'update' },
    { name: 'profile.change_password', description: 'Change own password', resource: 'profile', action: 'change_password' },

    // Settings Page Permissions
    { name: 'settings.access', description: 'Access settings page', resource: 'settings', action: 'access' },
    { name: 'settings.profile', description: 'Manage profile settings', resource: 'settings', action: 'profile' },
    { name: 'settings.security', description: 'Manage security settings', resource: 'settings', action: 'security' },
    { name: 'settings.notifications', description: 'Manage notification settings', resource: 'settings', action: 'notifications' },
    { name: 'settings.privacy', description: 'Manage privacy settings', resource: 'settings', action: 'privacy' },
    { name: 'settings.preferences', description: 'Manage user preferences', resource: 'settings', action: 'preferences' },

    // Role Management Permissions (for Settings page)
    { name: 'role.create', description: 'Create new roles', resource: 'role', action: 'create' },
    { name: 'role.read', description: 'View roles', resource: 'role', action: 'read' },
    { name: 'role.update', description: 'Update roles', resource: 'role', action: 'update' },
    { name: 'role.delete', description: 'Delete roles', resource: 'role', action: 'delete' },
    { name: 'role.manage_permissions', description: 'Manage role permissions', resource: 'role', action: 'manage_permissions' },

    // Permission Management Permissions (for Settings page)
    { name: 'permission.create', description: 'Create new permissions', resource: 'permission', action: 'create' },
    { name: 'permission.read', description: 'View permissions', resource: 'permission', action: 'read' },
    { name: 'permission.update', description: 'Update permissions', resource: 'permission', action: 'update' },
    { name: 'permission.delete', description: 'Delete permissions', resource: 'permission', action: 'delete' },

    // Admin Page Permissions
    { name: 'admin.access', description: 'Access admin panel', resource: 'admin', action: 'access' },
    { name: 'admin.dashboard', description: 'View admin dashboard', resource: 'admin', action: 'dashboard' },
    { name: 'admin.analytics', description: 'View admin analytics', resource: 'admin', action: 'analytics' },

    // Directory Page Permissions
    { name: 'directory.access', description: 'Access contact directory', resource: 'directory', action: 'access' },
    { name: 'directory.search', description: 'Search in directory', resource: 'directory', action: 'search' },
    { name: 'directory.filter', description: 'Filter directory results', resource: 'directory', action: 'filter' },
    { name: 'directory.export', description: 'Export directory data', resource: 'directory', action: 'export' },
    { name: 'directory.unit_filter', description: 'Filter directory by unit', resource: 'directory', action: 'unit_filter' },
    { name: 'directory.department_filter', description: 'Filter directory by department', resource: 'directory', action: 'department_filter' },
    { name: 'directory.grid_view', description: 'View directory in grid mode', resource: 'directory', action: 'grid_view' },
    { name: 'directory.tree_view', description: 'View directory in tree mode', resource: 'directory', action: 'tree_view' },

    // Conversations Page Permissions
    { name: 'conversations.access', description: 'Access conversations page', resource: 'conversations', action: 'access' },
    { name: 'conversations.create_group', description: 'Create group conversations', resource: 'conversations', action: 'create_group' },
    { name: 'conversations.manage_groups', description: 'Manage group conversations', resource: 'conversations', action: 'manage_groups' },

    // Individual Messaging Permissions
    { name: 'individual_messaging.access', description: 'Access individual messaging', resource: 'individual_messaging', action: 'access' },
    { name: 'individual_messaging.send', description: 'Send individual messages', resource: 'individual_messaging', action: 'send' },
    { name: 'individual_messaging.broadcast', description: 'Send broadcast messages', resource: 'individual_messaging', action: 'broadcast' },

    // Feedback Form Permissions
    { name: 'feedback_form.access', description: 'Access feedback form', resource: 'feedback_form', action: 'access' },
    { name: 'feedback_form.submit', description: 'Submit feedback', resource: 'feedback_form', action: 'submit' },
    { name: 'feedback_form.view_responses', description: 'View feedback responses', resource: 'feedback_form', action: 'view_responses' },

    // Profile Page Permissions
    { name: 'profile_page.access', description: 'Access profile page', resource: 'profile_page', action: 'access' },
    { name: 'profile_page.edit', description: 'Edit profile information', resource: 'profile_page', action: 'edit' },
    { name: 'profile_page.change_avatar', description: 'Change profile avatar', resource: 'profile_page', action: 'change_avatar' },

    // Role Management Page Permissions
    { name: 'role_management.access', description: 'Access role management page', resource: 'role_management', action: 'access' },
    { name: 'role_management.assign_roles', description: 'Assign roles to users', resource: 'role_management', action: 'assign_roles' },
    { name: 'role_management.view_hierarchy', description: 'View role hierarchy', resource: 'role_management', action: 'view_hierarchy' }
];

// Role-Permission mappings
const rolePermissions = {
    1: [ // Super Admin - All permissions
        'user.create', 'user.read', 'user.update', 'user.delete', 'user.manage_roles', 'user.export', 'user.import',
        'unit.create', 'unit.read', 'unit.update', 'unit.delete',
        'department.create', 'department.read', 'department.update', 'department.delete',
        'designation.create', 'designation.read', 'designation.update', 'designation.delete',
        'rank.create', 'rank.read', 'rank.update', 'rank.delete',
        'contact.create', 'contact.read', 'contact.update', 'contact.delete', 'contact.export', 'contact.import',
        'message.create', 'message.read', 'message.update', 'message.delete', 'message.broadcast',
        'conversation.create', 'conversation.read', 'conversation.update', 'conversation.delete', 'conversation.manage',
        'notification.create', 'notification.read', 'notification.update', 'notification.delete', 'notification.broadcast',
        'feedback.create', 'feedback.read', 'feedback.update', 'feedback.delete', 'feedback.respond',
        'audit_log.read', 'audit_log.delete', 'audit_log.export',
        'system_settings.read', 'system_settings.update', 'system_settings.backup', 'system_settings.restore',
        'dashboard.view', 'dashboard.analytics', 'dashboard.reports', 'dashboard.customize',
        'file.upload', 'file.download', 'file.delete', 'file.manage',
        'profile.read', 'profile.update', 'profile.change_password',
        'settings.access', 'settings.profile', 'settings.security', 'settings.notifications', 'settings.privacy', 'settings.preferences',
        'role.create', 'role.read', 'role.update', 'role.delete', 'role.manage_permissions',
        'permission.create', 'permission.read', 'permission.update', 'permission.delete',
        'admin.access', 'admin.dashboard', 'admin.analytics',
        'directory.access', 'directory.search', 'directory.filter', 'directory.export',
        'conversations.access', 'conversations.create_group', 'conversations.manage_groups',
        'individual_messaging.access', 'individual_messaging.send', 'individual_messaging.broadcast',
        'feedback_form.access', 'feedback_form.submit', 'feedback_form.view_responses',
        'profile_page.access', 'profile_page.edit', 'profile_page.change_avatar',
        'role_management.access', 'role_management.assign_roles', 'role_management.view_hierarchy'
    ],
    2: [ // Admin - Most permissions except critical system ones
        'user.create', 'user.read', 'user.update', 'user.export', 'user.import',
        'unit.create', 'unit.read', 'unit.update', 'unit.delete',
        'department.create', 'department.read', 'department.update', 'department.delete',
        'designation.create', 'designation.read', 'designation.update', 'designation.delete',
        'rank.create', 'rank.read', 'rank.update', 'rank.delete',
        'contact.create', 'contact.read', 'contact.update', 'contact.delete', 'contact.export', 'contact.import',
        'message.create', 'message.read', 'message.update', 'message.delete', 'message.broadcast',
        'conversation.create', 'conversation.read', 'conversation.update', 'conversation.delete', 'conversation.manage',
        'notification.create', 'notification.read', 'notification.update', 'notification.delete', 'notification.broadcast',
        'feedback.create', 'feedback.read', 'feedback.update', 'feedback.delete', 'feedback.respond',
        'audit_log.read', 'audit_log.export',
        'system_settings.read',
        'dashboard.view', 'dashboard.analytics', 'dashboard.reports', 'dashboard.customize',
        'file.upload', 'file.download', 'file.delete', 'file.manage',
        'profile.read', 'profile.update', 'profile.change_password',
        'settings.access', 'settings.profile', 'settings.security', 'settings.notifications', 'settings.privacy', 'settings.preferences',
        'role.read', 'role.update', 'role.manage_permissions',
        'permission.read', 'permission.update',
        'admin.access', 'admin.dashboard', 'admin.analytics',
        'directory.access', 'directory.search', 'directory.filter', 'directory.export',
        'conversations.access', 'conversations.create_group', 'conversations.manage_groups',
        'individual_messaging.access', 'individual_messaging.send', 'individual_messaging.broadcast',
        'feedback_form.access', 'feedback_form.submit', 'feedback_form.view_responses',
        'profile_page.access', 'profile_page.edit', 'profile_page.change_avatar',
        'role_management.access', 'role_management.assign_roles', 'role_management.view_hierarchy'
    ],
    3: [ // Manager - Departmental management permissions
        'user.create', 'user.read', 'user.update',
        'unit.read',
        'department.read', 'department.update',
        'designation.read', 'designation.update',
        'rank.read',
        'contact.create', 'contact.read', 'contact.update', 'contact.export',
        'message.create', 'message.read', 'message.update', 'message.delete', 'message.broadcast',
        'conversation.create', 'conversation.read', 'conversation.update', 'conversation.manage',
        'notification.create', 'notification.read', 'notification.update', 'notification.broadcast',
        'feedback.create', 'feedback.read', 'feedback.update', 'feedback.respond',
        'audit_log.read',
        'system_settings.read',
        'dashboard.view', 'dashboard.analytics', 'dashboard.reports',
        'file.upload', 'file.download', 'file.delete',
        'profile.read', 'profile.update', 'profile.change_password',
        'settings.access', 'settings.profile', 'settings.security', 'settings.notifications', 'settings.privacy', 'settings.preferences',
        'role.read',
        'permission.read',
        'admin.access', 'admin.dashboard',
        'directory.access', 'directory.search', 'directory.filter', 'directory.export',
        'conversations.access', 'conversations.create_group', 'conversations.manage_groups',
        'individual_messaging.access', 'individual_messaging.send', 'individual_messaging.broadcast',
        'feedback_form.access', 'feedback_form.submit', 'feedback_form.view_responses',
        'profile_page.access', 'profile_page.edit', 'profile_page.change_avatar',
        'role_management.access', 'role_management.view_hierarchy'
    ],
    4: [ // Officer - Operational permissions
        'user.read',
        'unit.read',
        'department.read',
        'designation.read',
        'rank.read',
        'contact.read', 'contact.update',
        'message.create', 'message.read', 'message.update', 'message.delete',
        'conversation.create', 'conversation.read', 'conversation.update',
        'notification.read', 'notification.update',
        'feedback.create', 'feedback.read', 'feedback.update',
        'dashboard.view', 'dashboard.analytics',
        'file.upload', 'file.download',
        'profile.read', 'profile.update', 'profile.change_password',
        'settings.access', 'settings.profile', 'settings.security', 'settings.notifications', 'settings.privacy', 'settings.preferences',
        'directory.access', 'directory.search', 'directory.filter',
        'conversations.access', 'conversations.create_group',
        'individual_messaging.access', 'individual_messaging.send',
        'feedback_form.access', 'feedback_form.submit',
        'profile_page.access', 'profile_page.edit', 'profile_page.change_avatar'
    ],
    5: [ // User - Basic permissions
        'user.read',
        'unit.read',
        'department.read',
        'designation.read',
        'rank.read',
        'contact.read',
        'message.create', 'message.read', 'message.update', 'message.delete',
        'conversation.create', 'conversation.read', 'conversation.update',
        'notification.read', 'notification.update',
        'feedback.create', 'feedback.read',
        'dashboard.view',
        'file.upload', 'file.download',
        'profile.read', 'profile.update', 'profile.change_password',
        'settings.access', 'settings.profile', 'settings.security', 'settings.notifications', 'settings.privacy', 'settings.preferences',
        'directory.access', 'directory.search', 'directory.filter', 'directory.unit_filter', 'directory.department_filter', 'directory.grid_view', 'directory.tree_view',
        'conversations.access',
        'individual_messaging.access', 'individual_messaging.send',
        'feedback_form.access', 'feedback_form.submit',
        'profile_page.access', 'profile_page.edit', 'profile_page.change_avatar'
    ]
};

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

        console.log('\n🧹 Starting cleanup process...');
        
        // Disable foreign key checks temporarily
        await connection.execute('SET FOREIGN_KEY_CHECKS = 0');
        
        // Clean up existing data in correct order (respecting foreign key constraints)
        console.log('   - Clearing user_permissions...');
        await connection.execute('DELETE FROM user_permissions');
        
        console.log('   - Clearing role_permissions...');
        await connection.execute('DELETE FROM role_permissions');
        
        console.log('   - Clearing permissions...');
        await connection.execute('DELETE FROM permissions');
        
        console.log('   - Clearing roles...');
        await connection.execute('DELETE FROM roles');
        
        // Reset auto increment counters
        console.log('   - Resetting auto increment counters...');
        await connection.execute('ALTER TABLE roles AUTO_INCREMENT = 1');
        await connection.execute('ALTER TABLE permissions AUTO_INCREMENT = 1');
        await connection.execute('ALTER TABLE role_permissions AUTO_INCREMENT = 1');
        await connection.execute('ALTER TABLE user_permissions AUTO_INCREMENT = 1');
        
        // Re-enable foreign key checks
        await connection.execute('SET FOREIGN_KEY_CHECKS = 1');
        
        console.log('✅ Cleanup completed successfully');

        console.log('\n🔄 Starting rebuild process...');
        
        // Insert fresh roles
        console.log('   - Inserting fresh roles...');
        for (const role of freshRoles) {
            await connection.execute(
                'INSERT INTO roles (id, name, description, level, is_active) VALUES (?, ?, ?, ?, ?)',
                [role.id, role.name, role.description, role.level, role.is_active]
            );
        }
        console.log(`   ✅ Inserted ${freshRoles.length} roles`);

        // Insert fresh permissions
        console.log('   - Inserting fresh permissions...');
        for (const permission of freshPermissions) {
            await connection.execute(
                'INSERT INTO permissions (name, description, resource, action, is_active) VALUES (?, ?, ?, ?, ?)',
                [permission.name, permission.description, permission.resource, permission.action, 1]
            );
        }
        console.log(`   ✅ Inserted ${freshPermissions.length} permissions`);

        // Get permission IDs for role-permission mapping
        console.log('   - Mapping roles to permissions...');
        const [permissionRows] = await connection.execute('SELECT id, name FROM permissions');
        const permissionMap = {};
        permissionRows.forEach(row => {
            permissionMap[row.name] = row.id;
        });

        // Insert role-permission mappings
        let totalRolePermissions = 0;
        for (const [roleId, permissionNames] of Object.entries(rolePermissions)) {
            for (const permissionName of permissionNames) {
                if (permissionMap[permissionName]) {
                    await connection.execute(
                        'INSERT INTO role_permissions (role_id, permission_id) VALUES (?, ?)',
                        [roleId, permissionMap[permissionName]]
                    );
                    totalRolePermissions++;
                }
            }
        }
        console.log(`   ✅ Mapped ${totalRolePermissions} role-permission relationships`);

        console.log('\n🎉 Roles and permissions rebuild completed successfully!');
        
        // Display summary
        console.log('\n📊 Summary:');
        console.log(`   - Roles: ${freshRoles.length}`);
        console.log(`   - Permissions: ${freshPermissions.length}`);
        console.log(`   - Role-Permission Mappings: ${totalRolePermissions}`);
        
        // Display role hierarchy
        console.log('\n👥 Role Hierarchy:');
        freshRoles.forEach(role => {
            const permissionCount = rolePermissions[role.id]?.length || 0;
            console.log(`   ${role.level}. ${role.name} (${permissionCount} permissions)`);
        });

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

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

module.exports = { cleanAndRebuildRolesPermissions, freshRoles, freshPermissions, rolePermissions };
