-- ================================================================================
-- Script de vérification : Isolation complète des opérations
-- ================================================================================
--
-- Ce script vérifie que l'isolation par opération fonctionne correctement
--
-- ================================================================================

USE dva_geo;

-- ================================================================================
-- 1. Vérifier les contraintes FK
-- ================================================================================

SELECT '=== VÉRIFICATION DES CONTRAINTES FK ===' AS '';

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'dva_geo'
AND TABLE_NAME IN ('ope_users_sectors', 'ope_pass')
AND COLUMN_NAME = 'fk_user'
ORDER BY TABLE_NAME;

-- Résultat attendu :
-- ope_pass          | fk_user | ope_pass_ibfk_3           | ope_users | id
-- ope_users_sectors | fk_user | ope_users_sectors_ibfk_2  | ope_users | id

-- ================================================================================
-- 2. Vérifier l'intégrité des données (pas d'orphelins)
-- ================================================================================

SELECT '=== VÉRIFICATION INTÉGRITÉ DES DONNÉES ===' AS '';

-- Vérifier que tous les fk_user de ope_pass existent dans ope_users
SELECT
    'ope_pass → ope_users' AS verification,
    COUNT(*) as orphelins
FROM ope_pass op
LEFT JOIN ope_users ou ON op.fk_user = ou.id
WHERE ou.id IS NULL;
-- Résultat attendu : 0

-- Vérifier que tous les fk_user de ope_users_sectors existent dans ope_users
SELECT
    'ope_users_sectors → ope_users' AS verification,
    COUNT(*) as orphelins
FROM ope_users_sectors ous
LEFT JOIN ope_users ou ON ous.fk_user = ou.id
WHERE ou.id IS NULL;
-- Résultat attendu : 0

-- ================================================================================
-- 3. Statistiques de migration
-- ================================================================================

SELECT '=== STATISTIQUES DE MIGRATION ===' AS '';

-- Nombre d'entités
SELECT 'Entités' AS table_name, COUNT(*) AS count FROM entites
UNION ALL
-- Nombre d'opérations
SELECT 'Opérations' AS table_name, COUNT(*) AS count FROM operations
UNION ALL
-- Nombre d'utilisateurs dans la table centrale
SELECT 'Users (centrale)' AS table_name, COUNT(*) AS count FROM users
UNION ALL
-- Nombre d'utilisateurs dans les opérations
SELECT 'ope_users' AS table_name, COUNT(*) AS count FROM ope_users
UNION ALL
-- Nombre de secteurs
SELECT 'ope_sectors' AS table_name, COUNT(*) AS count FROM ope_sectors
UNION ALL
-- Nombre d'associations user-secteur
SELECT 'ope_users_sectors' AS table_name, COUNT(*) AS count FROM ope_users_sectors
UNION ALL
-- Nombre de passages
SELECT 'ope_pass' AS table_name, COUNT(*) AS count FROM ope_pass
UNION ALL
-- Nombre d'historiques de passage
SELECT 'ope_pass_histo' AS table_name, COUNT(*) AS count FROM ope_pass_histo;

-- ================================================================================
-- 4. Détail par opération
-- ================================================================================

SELECT '=== DÉTAIL PAR OPÉRATION ===' AS '';

SELECT
    o.id AS operation_id,
    o.libelle AS operation_name,
    (SELECT COUNT(*) FROM ope_users WHERE fk_operation = o.id) AS nb_users,
    (SELECT COUNT(*) FROM ope_sectors WHERE fk_operation = o.id) AS nb_sectors,
    (SELECT COUNT(*) FROM ope_users_sectors WHERE fk_operation = o.id) AS nb_user_sector_links,
    (SELECT COUNT(*) FROM ope_pass WHERE fk_operation = o.id) AS nb_passages
FROM operations o
ORDER BY o.id;

-- ================================================================================
-- 5. Vérifier la relation users → ope_users
-- ================================================================================

SELECT '=== RELATION users → ope_users ===' AS '';

SELECT
    u.id AS user_id,
    u.first_name,
    u.sect_name,
    COUNT(DISTINCT ou.fk_operation) AS nb_operations,
    GROUP_CONCAT(DISTINCT ou.fk_operation ORDER BY ou.fk_operation) AS operations_ids
FROM users u
LEFT JOIN ope_users ou ON u.id = ou.fk_user
GROUP BY u.id, u.first_name, u.sect_name
ORDER BY u.id;

-- ================================================================================
-- 6. TEST DE SUPPRESSION (commenté pour sécurité)
-- ================================================================================

SELECT '=== INSTRUCTIONS POUR TEST DE SUPPRESSION ===' AS '';
SELECT 'Pour tester la suppression en CASCADE, décommentez la section ci-dessous' AS instruction;

-- Compter avant suppression (remplacer [ID_OPERATION] par un ID réel)
/*
SET @operation_id = [ID_OPERATION];

SELECT
    CONCAT('Opération ID: ', @operation_id) AS info,
    (SELECT COUNT(*) FROM ope_users WHERE fk_operation = @operation_id) as ope_users_count,
    (SELECT COUNT(*) FROM ope_users_sectors WHERE fk_operation = @operation_id) as ope_users_sectors_count,
    (SELECT COUNT(*) FROM ope_pass WHERE fk_operation = @operation_id) as ope_pass_count,
    (SELECT COUNT(*) FROM ope_sectors WHERE fk_operation = @operation_id) as ope_sectors_count;

-- Supprimer l'opération
DELETE FROM operations WHERE id = @operation_id;

-- Vérifier que tout a été supprimé (doit retourner 0 partout)
SELECT
    CONCAT('Après suppression de l''opération ID: ', @operation_id) AS info,
    (SELECT COUNT(*) FROM ope_users WHERE fk_operation = @operation_id) as ope_users_count,
    (SELECT COUNT(*) FROM ope_users_sectors WHERE fk_operation = @operation_id) as ope_users_sectors_count,
    (SELECT COUNT(*) FROM ope_pass WHERE fk_operation = @operation_id) as ope_pass_count,
    (SELECT COUNT(*) FROM ope_sectors WHERE fk_operation = @operation_id) as ope_sectors_count;
*/

SELECT '✓ Vérifications terminées avec succès !' AS status;