-- MSIN-info — Diagnostic comparatif table info (cible vs backup)
-- Contexte : sync_static_db a écrasé / corrompu info ; clés brutes dans l'UI (info_texte = info_clef)
-- Prérequis : importer la backup sur le MÊME serveur MySQL sous un nom dédié (ex. devinscription_bak)
-- Notes prod : lecture seule — ne pas exécuter les DELETE/UPDATE du script restore sans validation
--
-- 1) Remplacer les deux noms ci-dessous
-- 2) Exécuter section par section dans Navicat
-- 3) Exporter les SELECT « manquants / corrompus » en CSV si besoin

SET NAMES utf8mb4;

-- === CONFIG — modifier avant exécution ===
SET @db_cible  = 'devinscription_preprod';
SET @db_backup = 'devinscription_bak';   -- backup d'avant incident sync / import prod

-- === A) Volumes ===
SELECT 'cible_total' AS metrique, COUNT(*) AS v FROM devinscription_preprod.info
UNION ALL SELECT 'backup_total', COUNT(*) FROM devinscription_bak.info
UNION ALL SELECT 'cible_poubelle', COUNT(*) FROM devinscription_preprod.info WHERE info_texte = info_clef
UNION ALL SELECT 'backup_poubelle', COUNT(*) FROM devinscription_bak.info WHERE info_texte = info_clef;

-- === B) Lignes manquantes dans la cible (présentes en backup, absentes en cible) ===
SELECT b.info_clef, b.info_langue, b.info_prg,
       LEFT(b.info_texte, 100) AS texte_backup
FROM devinscription_bak.info b
LEFT JOIN devinscription_preprod.info c
  ON c.info_clef = b.info_clef AND c.info_langue = b.info_langue AND c.info_prg = b.info_prg
WHERE c.pk_info IS NULL
ORDER BY b.info_prg, b.info_clef, b.info_langue;

-- === C) Lignes corrompues en cible (poubelle auto-créée par afficheTexte) réparables depuis backup ===
SELECT c.pk_info, c.info_clef, c.info_langue, c.info_prg,
       c.info_texte AS texte_cible,
       LEFT(b.info_texte, 100) AS texte_backup
FROM devinscription_preprod.info c
INNER JOIN devinscription_bak.info b
  ON b.info_clef = c.info_clef AND b.info_langue = c.info_langue AND b.info_prg = c.info_prg
WHERE c.info_texte = c.info_clef
  AND b.info_texte <> b.info_clef
  AND TRIM(b.info_texte) <> ''
ORDER BY c.info_prg, c.info_clef, c.info_langue;

-- === D) Texte différent (backup OK, cible pas poubelle) — revue manuelle avant UPDATE massif ===
SELECT c.pk_info, c.info_clef, c.info_langue, c.info_prg,
       LEFT(c.info_texte, 80) AS texte_cible,
       LEFT(b.info_texte, 80) AS texte_backup
FROM devinscription_preprod.info c
INNER JOIN devinscription_bak.info b
  ON b.info_clef = c.info_clef AND b.info_langue = c.info_langue AND b.info_prg = c.info_prg
WHERE c.info_texte <> b.info_texte
  AND c.info_texte <> c.info_clef
  AND b.info_texte <> b.info_clef
ORDER BY c.info_prg, c.info_clef, c.info_langue
LIMIT 500;

-- === E) Poubelle en cible SANS équivalent backup (créées après incident — souvent supprimables) ===
SELECT c.pk_info, c.info_clef, c.info_langue, c.info_prg, c.info_texte
FROM devinscription_preprod.info c
LEFT JOIN devinscription_bak.info b
  ON b.info_clef = c.info_clef AND b.info_langue = c.info_langue AND b.info_prg = c.info_prg
WHERE c.info_texte = c.info_clef
  AND b.pk_info IS NULL
ORDER BY c.info_creation DESC, c.info_prg, c.info_clef
LIMIT 500;

-- === F) Modules les plus touchés (prioriser la relecture UI) ===
SELECT c.info_prg,
       SUM(c.info_texte = c.info_clef) AS nb_poubelle,
       COUNT(*) AS nb_total
FROM devinscription_preprod.info c
GROUP BY c.info_prg
HAVING nb_poubelle > 0
ORDER BY nb_poubelle DESC;

-- === G) Clés inscr_gestion_* / promoteur_hub_* (capture écran typique) ===
SELECT c.info_clef, c.info_langue, c.info_prg,
       CASE WHEN c.info_texte = c.info_clef THEN 'POUBELLE' ELSE 'OK' END AS etat_cible,
       CASE WHEN b.pk_info IS NULL THEN 'ABSENT_BACKUP' ELSE 'OK_BACKUP' END AS etat_backup,
       LEFT(COALESCE(b.info_texte, c.info_texte), 80) AS extrait
FROM devinscription_preprod.info c
LEFT JOIN devinscription_bak.info b
  ON b.info_clef = c.info_clef AND b.info_langue = c.info_langue AND b.info_prg = c.info_prg
WHERE c.info_clef LIKE 'inscr_gestion_%'
   OR c.info_clef LIKE 'promoteur_hub_%'
ORDER BY c.info_clef, c.info_langue, c.info_prg;