-- ============================================================================
-- Phase 5: prof_classes data-consistency checks (READ-ONLY)
--
-- Run these against a production replica/read-only connection. None of them
-- write or modify any row. Each is meant to return an EMPTY result set when
-- the data is healthy; any row returned is something to investigate before
-- (or after) treating prof_classes as the sole authorization source.
-- ============================================================================

-- 1. Teachers (type_utilisateur_id = 1, excluding the hardcoded directeur
--    login) with NO prof_classes row at all. Under the new
--    Utilisateurs::teachesClass(), these teachers cannot access any class
--    until they get at least one prof_classes row.
SELECT u.id, u.nom, u.prenom, u.login, u.classe_id AS legacy_classe_id
FROM utilisateurs u
WHERE u.type_utilisateur_id = 1
  AND NOT EXISTS (
      SELECT 1 FROM prof_classes pc WHERE pc.prof_id = u.id
  );

-- 2. prof_classes.prof_id values that don't correspond to a professeur
--    account (either the utilisateurs row doesn't exist, or it exists but
--    isn't type_utilisateur_id = 1).
SELECT pc.id AS prof_classes_id, pc.prof_id, pc.classe_id, u.id AS user_id,
       u.type_utilisateur_id
FROM prof_classes pc
LEFT JOIN utilisateurs u ON u.id = pc.prof_id
WHERE u.id IS NULL
   OR u.type_utilisateur_id <> 1;

-- 3. prof_classes.classe_id values that don't correspond to an existing
--    class.
SELECT pc.id AS prof_classes_id, pc.prof_id, pc.classe_id
FROM prof_classes pc
LEFT JOIN classes c ON c.id = pc.classe_id
WHERE c.id IS NULL;

-- 4. Teachers with more than one prof_classes.isdefault = 1 row (ambiguous
--    default; the app expects at most one per teacher).
SELECT pc.prof_id, COUNT(*) AS default_count
FROM prof_classes pc
WHERE pc.isdefault = 1
GROUP BY pc.prof_id
HAVING COUNT(*) > 1;

-- 5. Teachers who have classes assigned but none marked as default (their
--    frontend default-class selection will fall back to "first class" or
--    a legacy classId already in local storage, not the intended default).
SELECT pc.prof_id
FROM prof_classes pc
GROUP BY pc.prof_id
HAVING SUM(pc.isdefault = 1) = 0;

-- 6. Divergence between the legacy utilisateurs.classe_id and the
--    prof_classes row marked isdefault = 1 for the same teacher. Rows here
--    are teachers whose old "primary class" column disagrees with the new
--    authoritative default -- useful evidence for deciding whether
--    utilisateurs.classe_id can eventually be dropped.
SELECT
    u.id AS prof_id,
    u.nom,
    u.prenom,
    u.classe_id AS legacy_classe_id,
    pc.classe_id AS prof_classes_default_classe_id
FROM utilisateurs u
LEFT JOIN prof_classes pc
       ON pc.prof_id = u.id AND pc.isdefault = 1
WHERE u.type_utilisateur_id = 1
  AND (
        (u.classe_id IS NULL AND pc.classe_id IS NOT NULL)
     OR (u.classe_id IS NOT NULL AND pc.classe_id IS NULL)
     OR (u.classe_id <> pc.classe_id)
  );
