-- ============================================================================
-- FIX: Coherencia entre id_role y Roles Bouncer
-- ============================================================================
-- 
-- Problema: Usuarios web (root, admin, operative, viewer) fueron creados
-- con id_role=5 (client) por defecto, causando inconsistencia entre
-- sistema legacy (id_role) y sistema Bouncer (assigned_roles).
--
-- Solución: Actualizar id_role según el rol Bouncer asignado
-- ============================================================================

BEGIN;

-- Mostrar estado ANTES de la corrección
SELECT 
    '=== ANTES DE LA CORRECCIÓN ===' as status;

SELECT 
    u.id,
    u.name,
    u.email,
    u.id_role as current_id_role,
    r.name as current_role_name,
    br.slug as bouncer_role_slug
FROM users u
LEFT JOIN roles r ON u.id_role = r.id
LEFT JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
LEFT JOIN roles br ON ar.role_id = br.id
WHERE u.id IN (1, 3)  -- Alberto y Hugo (ajustar según producción)
ORDER BY u.id;

-- ============================================================================
-- CORRECCIONES
-- ============================================================================

-- 1. Actualizar usuarios con rol Bouncer 'root' → id_role=7 (root)
UPDATE users u
SET id_role = (SELECT id FROM roles WHERE slug = 'root' LIMIT 1)
FROM assigned_roles ar
JOIN roles r ON ar.role_id = r.id
WHERE u.id = ar.entity_id 
  AND ar.entity_type = 'App\User'
  AND r.slug = 'root'
  AND u.id_role != (SELECT id FROM roles WHERE slug = 'root' LIMIT 1);

SELECT format('✅ Actualizados %s usuarios con rol root', COUNT(*)) as resultado
FROM users u
JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
JOIN roles r ON ar.role_id = r.id
WHERE r.slug = 'root' AND u.id_role = (SELECT id FROM roles WHERE slug = 'root' LIMIT 1);

-- 2. Actualizar usuarios con rol Bouncer 'admin' → id_role=1 (admin)
UPDATE users u
SET id_role = (SELECT id FROM roles WHERE slug = 'admin' LIMIT 1)
FROM assigned_roles ar
JOIN roles r ON ar.role_id = r.id
WHERE u.id = ar.entity_id 
  AND ar.entity_type = 'App\User'
  AND r.slug = 'admin'
  AND u.id_role != (SELECT id FROM roles WHERE slug = 'admin' LIMIT 1);

SELECT format('✅ Actualizados %s usuarios con rol admin', COUNT(*)) as resultado
FROM users u
JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
JOIN roles r ON ar.role_id = r.id
WHERE r.slug = 'admin' AND u.id_role = (SELECT id FROM roles WHERE slug = 'admin' LIMIT 1);

-- 3. Actualizar usuarios con rol Bouncer 'operative' → id_role=3 (operative)
UPDATE users u
SET id_role = (SELECT id FROM roles WHERE slug = 'operative' LIMIT 1)
FROM assigned_roles ar
JOIN roles r ON ar.role_id = r.id
WHERE u.id = ar.entity_id 
  AND ar.entity_type = 'App\User'
  AND r.slug = 'operative'
  AND u.id_role != (SELECT id FROM roles WHERE slug = 'operative' LIMIT 1);

SELECT format('✅ Actualizados %s usuarios con rol operative', COUNT(*)) as resultado
FROM users u
JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
JOIN roles r ON ar.role_id = r.id
WHERE r.slug = 'operative' AND u.id_role = (SELECT id FROM roles WHERE slug = 'operative' LIMIT 1);

-- 4. Actualizar usuarios con rol Bouncer 'viewer' → id_role=8 (viewer)
UPDATE users u
SET id_role = (SELECT id FROM roles WHERE slug = 'viewer' LIMIT 1)
FROM assigned_roles ar
JOIN roles r ON ar.role_id = r.id
WHERE u.id = ar.entity_id 
  AND ar.entity_type = 'App\User'
  AND r.slug = 'viewer'
  AND u.id_role != (SELECT id FROM roles WHERE slug = 'viewer' LIMIT 1);

SELECT format('✅ Actualizados %s usuarios con rol viewer', COUNT(*)) as resultado
FROM users u
JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
JOIN roles r ON ar.role_id = r.id
WHERE r.slug = 'viewer' AND u.id_role = (SELECT id FROM roles WHERE slug = 'viewer' LIMIT 1);

-- ============================================================================
-- VERIFICACIÓN
-- ============================================================================

SELECT 
    '=== DESPUÉS DE LA CORRECCIÓN ===' as status;

SELECT 
    u.id,
    u.name,
    u.email,
    u.id_role,
    r.name as role_name,
    r.slug as role_slug,
    br.slug as bouncer_role_slug,
    CASE 
        WHEN r.slug = br.slug THEN '✅ COHERENTE'
        ELSE '❌ INCOHERENTE'
    END as coherencia
FROM users u
LEFT JOIN roles r ON u.id_role = r.id
LEFT JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
LEFT JOIN roles br ON ar.role_id = br.id
WHERE u.id_role IS NOT NULL
  AND br.slug IS NOT NULL
ORDER BY u.id;

-- ============================================================================
-- RESUMEN
-- ============================================================================

SELECT 
    '=== RESUMEN DE COHERENCIA ===' as titulo;

SELECT 
    COUNT(*) as total_usuarios_web,
    SUM(CASE WHEN r.slug = br.slug THEN 1 ELSE 0 END) as coherentes,
    SUM(CASE WHEN r.slug != br.slug THEN 1 ELSE 0 END) as incoherentes
FROM users u
JOIN roles r ON u.id_role = r.id
JOIN assigned_roles ar ON u.id = ar.entity_id AND ar.entity_type = 'App\User'
JOIN roles br ON ar.role_id = br.id
WHERE br.slug IN ('root', 'admin', 'operative', 'viewer');

-- Si todo está OK, ejecuta COMMIT. Si hay errores, ejecuta ROLLBACK.
-- COMMIT;
-- ROLLBACK;

