-- MSIN-info — Restauration MASSIVE table info depuis backup (Navicat)
-- Contexte : après sync_static_db / import prod — clés brutes partout (info_texte = info_clef)
-- Prérequis :
--   1) Backup d'avant incident importée sur le MÊME serveur (ex. devinscription_bak)
--   2) Exécuter MSIN-info-restore-diag-compare.sql et valider les volumes
--   3) FAIRE UN DUMP de devinscription_preprod.info AVANT ce script
-- Idempotent : INSERT manquants + UPDATE poubelles ; ne touche pas les textes déjà OK
-- Notes prod client : attendre blocage sync info ; même procédure avec backup prod pré-sync

SET NAMES utf8mb4;

-- === CONFIG — modifier avant exécution ===
SET @db_cible  = 'devinscription_preprod';
SET @db_backup = 'devinscription_bak';

-- ---------------------------------------------------------------------------
-- Étape 1 — Insérer les lignes absentes en cible (clé = info_clef + info_langue + info_prg)
-- ---------------------------------------------------------------------------
INSERT INTO devinscription_preprod.info (
    info_clef, info_langue, info_texte, info_aide, info_prg,
    info_description, info_trie, info_actif,
    info_option1, info_option2, info_option3, info_creation
)
SELECT
    b.info_clef, b.info_langue, b.info_texte, b.info_aide, b.info_prg,
    b.info_description, b.info_trie, b.info_actif,
    b.info_option1, b.info_option2, b.info_option3, NOW()
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;

-- ---------------------------------------------------------------------------
-- Étape 2 — Réparer les poubelles (info_texte = info_clef) quand backup a un vrai libellé
-- ---------------------------------------------------------------------------
UPDATE 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
SET
    c.info_texte       = b.info_texte,
    c.info_aide        = b.info_aide,
    c.info_description = b.info_description,
    c.info_trie        = b.info_trie,
    c.info_actif       = b.info_actif,
    c.info_option1     = b.info_option1,
    c.info_option2     = b.info_option2,
    c.info_option3     = b.info_option3,
    c.info_maj         = NOW()
WHERE c.info_texte = c.info_clef
  AND b.info_texte <> b.info_clef
  AND TRIM(b.info_texte) <> '';

-- ---------------------------------------------------------------------------
-- Étape 3 — Réparer texte vide en cible (backup non vide)
-- ---------------------------------------------------------------------------
UPDATE 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
SET
    c.info_texte = b.info_texte,
    c.info_aide    = b.info_aide,
    c.info_maj     = NOW()
WHERE TRIM(c.info_texte) = ''
  AND TRIM(b.info_texte) <> ''
  AND b.info_texte <> b.info_clef;

-- ---------------------------------------------------------------------------
-- Étape 4 — Supprimer poubelles orphelines (absentes du backup, jamais traduites)
-- ATTENTION : exécuter seulement si section E du diag est cohérente
-- ---------------------------------------------------------------------------
DELETE c 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;

-- ---------------------------------------------------------------------------
-- Étape 5 — Vérification post-restore (doit tendre vers 0 poubelle)
-- ---------------------------------------------------------------------------
SELECT 'post_total' AS k, COUNT(*) AS v FROM devinscription_preprod.info
UNION ALL
SELECT 'post_poubelle', COUNT(*) FROM devinscription_preprod.info WHERE info_texte = info_clef
UNION ALL
SELECT 'post_manquants_vs_backup', COUNT(*)
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;