-- ============================================
-- Role Permission Update Script
-- Date: 2024
-- Description: Update IT Manager, Maintenance Manager, and Incident Manager permissions
--
-- Changes:
-- 1. Remove system.users.view from Maintenance Manager
-- 2. Remove system.users.view from Incident Manager
-- 3. Add system.users.manage to IT Manager
-- 4. Add system.users.delete to IT Manager
--
-- IMPORTANT: This script must be run on EACH tenant database
-- ============================================

-- Verify we're in the correct database context
SELECT DATABASE() as current_database;

-- ============================================
-- STEP 1: Remove system.users.view from Maintenance Manager
-- ============================================
DELETE FROM role_permissions
WHERE role_id = (SELECT id FROM roles WHERE slug = 'maintenance_manager')
AND permission_id = (SELECT id FROM permissions WHERE slug = 'system.users.view')
AND granted = 1;

SELECT 'Removed system.users.view from Maintenance Manager' as step_1;

-- ============================================
-- STEP 2: Remove system.users.view from Incident Manager
-- ============================================
DELETE FROM role_permissions
WHERE role_id = (SELECT id FROM roles WHERE slug = 'incident_manager')
AND permission_id = (SELECT id FROM permissions WHERE slug = 'system.users.view')
AND granted = 1;

SELECT 'Removed system.users.view from Incident Manager' as step_2;

-- ============================================
-- STEP 3: Add system.users.manage to IT Manager
-- ============================================
INSERT IGNORE INTO role_permissions (role_id, permission_id, granted)
SELECT
    (SELECT id FROM roles WHERE slug = 'it_manager'),
    (SELECT id FROM permissions WHERE slug = 'system.users.manage'),
    1
WHERE NOT EXISTS (
    SELECT 1 FROM role_permissions
    WHERE role_id = (SELECT id FROM roles WHERE slug = 'it_manager')
    AND permission_id = (SELECT id FROM permissions WHERE slug = 'system.users.manage')
);

SELECT 'Added system.users.manage to IT Manager' as step_3;

-- ============================================
-- STEP 4: Add system.users.delete to IT Manager
-- ============================================
INSERT IGNORE INTO role_permissions (role_id, permission_id, granted)
SELECT
    (SELECT id FROM roles WHERE slug = 'it_manager'),
    (SELECT id FROM permissions WHERE slug = 'system.users.delete'),
    1
WHERE NOT EXISTS (
    SELECT 1 FROM role_permissions
    WHERE role_id = (SELECT id FROM roles WHERE slug = 'it_manager')
    AND permission_id = (SELECT id FROM permissions WHERE slug = 'system.users.delete')
);

SELECT 'Added system.users.delete to IT Manager' as step_4;

-- ============================================
-- VERIFICATION: Show updated permissions
-- ============================================
SELECT
    r.name as role_name,
    p.name as permission_name,
    p.slug as permission_slug,
    rp.granted
FROM roles r
INNER JOIN role_permissions rp ON r.id = rp.role_id
INNER JOIN permissions p ON rp.permission_id = p.id
WHERE r.slug IN ('it_manager', 'maintenance_manager', 'incident_manager')
AND p.slug IN ('system.users.view', 'system.users.invite', 'system.users.manage', 'system.users.delete')
ORDER BY r.name, p.slug;

SELECT 'Permission update completed successfully!' as final_result;
