-- =============================================================================
-- cleanup_role_rules.sql
-- Diagnostic et nettoyage des incohérences SECU_ROLE_RULES / SECU_RULES
-- et des lignes inactives SECU_USER_RULES
--
-- USAGE : exécuter les SELECT (diagnostic) pour voir l'étendue des
-- problèmes, puis les corrections dans l'ordre indiqué.
--
-- La correction #7 (CEILING orphelin) est une migration ponctuelle à
-- exécuter UNE SEULE FOIS avant le déploiement du nouveau
-- role_rules_update.php (distinction architecte/pilote). Après ça,
-- le code gère la distinction et ce cas ne devrait plus se reproduire.
--
-- Les corrections #8 et #9 sont idempotentes et rejouables à volonté.
-- =============================================================================


-- ═════════════════════════════════════════════════════════════════════════════
-- SECU_ROLE_RULES — DIAGNOSTIC
-- ═════════════════════════════════════════════════════════════════════════════


-- ─────────────────────────────────────────────────────────────────────────────
-- 1. DIAGNOSTIC — CEILING orphelin : GRANT=0 mais CEILING=1 (la permission
--    reste visible alors qu'elle n'est plus octroyée — hérité de l'ancien
--    modèle qui ne distinguait pas architecte/pilote au décochage)
-- ─────────────────────────────────────────────────────────────────────────────
SELECT a.ID_ROLE, b.ROLE AS NOM_ROLE,
       a.ID_RULE, c.RULE, c.DESCRIPTION,
       'CEILING ORPHELIN' AS PROBLEME,
       a.GRANT_VIEW, a.CEILING_VIEW,
       a.GRANT_INSERT, a.CEILING_INSERT,
       a.GRANT_UPDATE, a.CEILING_UPDATE,
       a.GRANT_DELETE, a.CEILING_DELETE
FROM SECU_ROLE_RULES a
JOIN SECU_ROLES b ON b.ID_ROLE = a.ID_ROLE
JOIN SECU_RULES c ON c.ID_RULE = a.ID_RULE
WHERE a.IND_ACTIF = 1
  AND (   (a.GRANT_VIEW   = 0 AND a.CEILING_VIEW   = 1)
       OR (a.GRANT_INSERT = 0 AND a.CEILING_INSERT = 1)
       OR (a.GRANT_UPDATE = 0 AND a.CEILING_UPDATE = 1)
       OR (a.GRANT_DELETE = 0 AND a.CEILING_DELETE = 1))
ORDER BY b.ROLE, c.MODULE, c.RULE;


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. DIAGNOSTIC — GRANT dépasse CEILING (le droit actif dépasse le plafond
--    octroyé par le niveau supérieur)
-- ─────────────────────────────────────────────────────────────────────────────
SELECT a.ID_ROLE, b.ROLE AS NOM_ROLE,
       a.ID_RULE, c.RULE, c.DESCRIPTION,
       'GRANT > CEILING' AS PROBLEME,
       a.GRANT_VIEW, a.CEILING_VIEW,
       a.GRANT_INSERT, a.CEILING_INSERT,
       a.GRANT_UPDATE, a.CEILING_UPDATE,
       a.GRANT_DELETE, a.CEILING_DELETE
FROM SECU_ROLE_RULES a
JOIN SECU_ROLES b ON b.ID_ROLE = a.ID_ROLE
JOIN SECU_RULES c ON c.ID_RULE = a.ID_RULE
WHERE a.IND_ACTIF = 1
  AND (   a.GRANT_VIEW   > a.CEILING_VIEW
       OR a.GRANT_INSERT > a.CEILING_INSERT
       OR a.GRANT_UPDATE > a.CEILING_UPDATE
       OR a.GRANT_DELETE > a.CEILING_DELETE)
ORDER BY b.ROLE, c.MODULE, c.RULE;


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. DIAGNOSTIC — CEILING dépasse le plafond physique (SECU_RULES) — un rôle
--    prétend autoriser une action que la règle elle-même ne supporte pas
-- ─────────────────────────────────────────────────────────────────────────────
SELECT a.ID_ROLE, b.ROLE AS NOM_ROLE,
       a.ID_RULE, c.RULE, c.DESCRIPTION,
       'CEILING > PHYSIQUE' AS PROBLEME,
       a.CEILING_VIEW, c.GRANT_VIEW AS PHYS_VIEW,
       a.CEILING_INSERT, c.GRANT_INSERT AS PHYS_INSERT,
       a.CEILING_UPDATE, c.GRANT_UPDATE AS PHYS_UPDATE,
       a.CEILING_DELETE, c.GRANT_DELETE AS PHYS_DELETE
FROM SECU_ROLE_RULES a
JOIN SECU_ROLES b ON b.ID_ROLE = a.ID_ROLE
JOIN SECU_RULES c ON c.ID_RULE = a.ID_RULE
WHERE a.IND_ACTIF = 1
  AND (   a.CEILING_VIEW   > c.GRANT_VIEW
       OR a.CEILING_INSERT > c.GRANT_INSERT
       OR a.CEILING_UPDATE > c.GRANT_UPDATE
       OR a.CEILING_DELETE > c.GRANT_DELETE)
ORDER BY b.ROLE, c.MODULE, c.RULE;


-- ─────────────────────────────────────────────────────────────────────────────
-- 4. DIAGNOSTIC — GRANT dépasse le plafond physique (conséquence combinée
--    des deux cas ci-dessus, ou données de test/migration)
-- ─────────────────────────────────────────────────────────────────────────────
SELECT a.ID_ROLE, b.ROLE AS NOM_ROLE,
       a.ID_RULE, c.RULE, c.DESCRIPTION,
       'GRANT > PHYSIQUE' AS PROBLEME,
       a.GRANT_VIEW, c.GRANT_VIEW AS PHYS_VIEW,
       a.GRANT_INSERT, c.GRANT_INSERT AS PHYS_INSERT,
       a.GRANT_UPDATE, c.GRANT_UPDATE AS PHYS_UPDATE,
       a.GRANT_DELETE, c.GRANT_DELETE AS PHYS_DELETE
FROM SECU_ROLE_RULES a
JOIN SECU_ROLES b ON b.ID_ROLE = a.ID_ROLE
JOIN SECU_RULES c ON c.ID_RULE = a.ID_RULE
WHERE a.IND_ACTIF = 1
  AND (   a.GRANT_VIEW   > c.GRANT_VIEW
       OR a.GRANT_INSERT > c.GRANT_INSERT
       OR a.GRANT_UPDATE > c.GRANT_UPDATE
       OR a.GRANT_DELETE > c.GRANT_DELETE)
ORDER BY b.ROLE, c.MODULE, c.RULE;


-- ─────────────────────────────────────────────────────────────────────────────
-- 5. DIAGNOSTIC — Lignes orphelines SECU_ROLE_RULES : CEILING=0 et GRANT=0
--    sur les 4 actions (aucun effet fonctionnel)
-- ─────────────────────────────────────────────────────────────────────────────
SELECT a.ID_ROLE, b.ROLE AS NOM_ROLE,
       a.ID_RULE, c.RULE, c.DESCRIPTION,
       'ORPHELINE (tout à 0)' AS PROBLEME
FROM SECU_ROLE_RULES a
JOIN SECU_ROLES b ON b.ID_ROLE = a.ID_ROLE
JOIN SECU_RULES c ON c.ID_RULE = a.ID_RULE
WHERE a.IND_ACTIF = 1
  AND a.GRANT_VIEW = 0 AND a.GRANT_INSERT = 0 AND a.GRANT_UPDATE = 0 AND a.GRANT_DELETE = 0
  AND a.CEILING_VIEW = 0 AND a.CEILING_INSERT = 0 AND a.CEILING_UPDATE = 0 AND a.CEILING_DELETE = 0
ORDER BY b.ROLE, c.MODULE, c.RULE;


-- ═════════════════════════════════════════════════════════════════════════════
-- SECU_USER_RULES — DIAGNOSTIC
-- ═════════════════════════════════════════════════════════════════════════════


-- ─────────────────────────────────────────────────────────────────────────────
-- 6. DIAGNOSTIC — Lignes inactives SECU_USER_RULES (surcharges désactivées,
--    aucun effet fonctionnel — la vue filtre sur e.IND_ACTIF = 1)
-- ─────────────────────────────────────────────────────────────────────────────
SELECT a.ID_USER_RULE, a.ID_USER, b.USERNAME,
       a.ID_RULE, c.RULE, c.DESCRIPTION,
       a.GRANT_VIEW, a.GRANT_INSERT, a.GRANT_UPDATE, a.GRANT_DELETE,
       a.IND_ACTIF, a.DT_MODIFIED, a.MODIFIED_BY
FROM SECU_USER_RULES a
JOIN SECU_USERS b ON b.ID_USER = a.ID_USER
JOIN SECU_RULES c ON c.ID_RULE = a.ID_RULE
WHERE a.IND_ACTIF = 0
ORDER BY b.USERNAME, c.MODULE, c.RULE;


-- ═════════════════════════════════════════════════════════════════════════════
-- NETTOYAGE
-- ═════════════════════════════════════════════════════════════════════════════


-- ─────────────────────────────────────────────────────────────────────────────
-- 7. MIGRATION — Aligner CEILING sur GRANT dans SECU_ROLE_RULES (migration
--    vers le nouveau modèle architecte/pilote). Si GRANT=0, CEILING passe
--    à 0. Si GRANT=1, CEILING reste inchangé. À exécuter UNE SEULE FOIS
--    avant déploiement du nouveau role_rules_update.php.
-- ─────────────────────────────────────────────────────────────────────────────
UPDATE SECU_ROLE_RULES
SET CEILING_VIEW   = LEAST(CEILING_VIEW,   GRANT_VIEW),
    CEILING_INSERT = LEAST(CEILING_INSERT, GRANT_INSERT),
    CEILING_UPDATE = LEAST(CEILING_UPDATE, GRANT_UPDATE),
    CEILING_DELETE = LEAST(CEILING_DELETE, GRANT_DELETE)
WHERE IND_ACTIF = 1
  AND (   (GRANT_VIEW   = 0 AND CEILING_VIEW   = 1)
       OR (GRANT_INSERT = 0 AND CEILING_INSERT = 1)
       OR (GRANT_UPDATE = 0 AND CEILING_UPDATE = 1)
       OR (GRANT_DELETE = 0 AND CEILING_DELETE = 1));


-- ─────────────────────────────────────────────────────────────────────────────
-- 8. CORRECTION — Borne CEILING par le physique, puis GRANT par le CEILING
--    corrigé, en une seule opération. Idempotent, rejouable à volonté.
-- ─────────────────────────────────────────────────────────────────────────────
UPDATE SECU_ROLE_RULES a
JOIN SECU_RULES b ON b.ID_RULE = a.ID_RULE
SET a.CEILING_VIEW   = LEAST(a.CEILING_VIEW,   b.GRANT_VIEW),
    a.CEILING_INSERT = LEAST(a.CEILING_INSERT, b.GRANT_INSERT),
    a.CEILING_UPDATE = LEAST(a.CEILING_UPDATE, b.GRANT_UPDATE),
    a.CEILING_DELETE = LEAST(a.CEILING_DELETE, b.GRANT_DELETE),
    a.GRANT_VIEW     = LEAST(a.GRANT_VIEW,   LEAST(a.CEILING_VIEW,   b.GRANT_VIEW)),
    a.GRANT_INSERT   = LEAST(a.GRANT_INSERT, LEAST(a.CEILING_INSERT, b.GRANT_INSERT)),
    a.GRANT_UPDATE   = LEAST(a.GRANT_UPDATE, LEAST(a.CEILING_UPDATE, b.GRANT_UPDATE)),
    a.GRANT_DELETE   = LEAST(a.GRANT_DELETE, LEAST(a.CEILING_DELETE, b.GRANT_DELETE))
WHERE a.IND_ACTIF = 1
  AND (   a.CEILING_VIEW   > b.GRANT_VIEW
       OR a.CEILING_INSERT > b.GRANT_INSERT
       OR a.CEILING_UPDATE > b.GRANT_UPDATE
       OR a.CEILING_DELETE > b.GRANT_DELETE
       OR a.GRANT_VIEW     > LEAST(a.CEILING_VIEW,   b.GRANT_VIEW)
       OR a.GRANT_INSERT   > LEAST(a.CEILING_INSERT, b.GRANT_INSERT)
       OR a.GRANT_UPDATE   > LEAST(a.CEILING_UPDATE, b.GRANT_UPDATE)
       OR a.GRANT_DELETE   > LEAST(a.CEILING_DELETE, b.GRANT_DELETE));


-- ─────────────────────────────────────────────────────────────────────────────
-- 9. NETTOYAGE — Désactiver les lignes orphelines SECU_ROLE_RULES (tout à 0,
--    aucun effet fonctionnel). Décommenter si souhaité.
-- ─────────────────────────────────────────────────────────────────────────────
-- UPDATE SECU_ROLE_RULES
-- SET IND_ACTIF = 0
-- WHERE IND_ACTIF = 1
--   AND GRANT_VIEW = 0 AND GRANT_INSERT = 0 AND GRANT_UPDATE = 0 AND GRANT_DELETE = 0
--   AND CEILING_VIEW = 0 AND CEILING_INSERT = 0 AND CEILING_UPDATE = 0 AND CEILING_DELETE = 0;


-- ─────────────────────────────────────────────────────────────────────────────
-- 10. NETTOYAGE — Suppression physique des lignes inactives SECU_USER_RULES.
--     Pas de IND_DEL sur cette table — une ligne absente a le même effet
--     qu'une ligne IND_ACTIF=0 (la vue fait LEFT JOIN ... AND e.IND_ACTIF=1).
--     Si la surcharge est réattribuée plus tard, ON DUPLICATE KEY UPDATE
--     crée une ligne neuve (clé UNIQUE ID_USER, ID_RULE libérée par la purge).
--     Décommenter si souhaité.
-- ─────────────────────────────────────────────────────────────────────────────
-- DELETE FROM SECU_USER_RULES WHERE IND_ACTIF = 0;
