-- Données synthétiques reproduisant la volumétrie ET les références pendouillantes
-- documentées de `e-learning-prod`. Issue #156 (livrable B14).
--
-- Sources — aucun chiffre n'est inventé :
--   docs/roadmap/14_RUNBOOK_BASCULE_PROD.md §1.4
--   docs/roadmap/19_DIFF_SCHEMA_PROD.md §5, §6
--   docs/context/CONSIGNATION_DEPLOIEMENT_2026-08-23.md §5.3
--   docs/roadmap/11_AUDIT_PROD_RESULTATS.md §4 (330 leçons validées, 1 184 réponses de quiz)
--
--   301 utilisateurs · 54 cours · 313 supports · 2 128 inscriptions · 135 complétions
--   orphelins : lessons→sections 89 (dont lessons→courses 16, INCLUS dans les 89)
--               subcategories→categories 1 · users→users(parent_id) 5
--               enrollments→courses et →users : 0 / 0
--   28 réponses de quiz de plus de 125 caractères (la plus longue : 295)
--
-- Aucune donnée personnelle : tout est engendré. Les identifiants « pendouillants »
-- valent 7000+ / 8000+ / 9000+ pour être reconnaissables d'un coup d'œil.

SET SESSION cte_max_recursion_depth = 20000;
SET SESSION sql_mode = '';

-- ------------------------------------------------------------------ taxonomie
INSERT INTO `categories` (`id`, `name`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 8)
SELECT n, CONCAT('Catégorie ', n), n, NOW(), NOW() FROM s;

INSERT INTO `subcategories` (`id`, `name`, `category_id`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 12)
SELECT n, CONCAT('Sous-catégorie ', n), ((n - 1) % 8) + 1, NOW(), NOW() FROM s;
-- 1 sous-catégorie pendouillante (runbook §1.4) — purgée par ea136_reliquat_orphelins.
INSERT INTO `subcategories` (`id`, `name`, `category_id`, `created_at`, `updated_at`)
VALUES (13, 'Sous-catégorie orpheline', 9001, NOW(), NOW());

INSERT INTO `course_types` (`id`, `name`, `created_at`, `updated_at`) VALUES
  (1, 'E-learning', NOW(), NOW()), (2, 'Présentiel', NOW(), NOW()), (3, 'Mixte', NOW(), NOW());

INSERT INTO `tags` (`id`, `name`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 10)
SELECT n, CONCAT('Étiquette ', n), NOW(), NOW() FROM s;

-- ------------------------------------------------------------------ 301 utilisateurs
INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `password`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 301)
SELECT n, CONCAT('Prenom', n), CONCAT('Nom', n), CONCAT('compte', n, '@fixture.invalid'),
       '$2y$10$fixturefixturefixturefixturefixturefixturefixturefixtu', NOW(), NOW()
FROM s;
-- Rattachements managériaux valides (les managers existent).
UPDATE `users` SET `parent_id` = 1 + (`id` % 6) WHERE `id` BETWEEN 20 AND 250;
-- 5 utilisateurs rattachés à un manager supprimé (runbook §1.4) — DÉTACHÉS, pas
-- supprimés, par ea136_reliquat_orphelins puis ea160.
UPDATE `users` SET `parent_id` = 9000 + `id` WHERE `id` IN (261, 262, 263, 264, 265);

-- ------------------------------------------------------------------ 54 cours
-- `subcategory_id` est NOT NULL en production (D4) et sans orphelin : le contrôle
-- bloquant d'avant-migration de bascule_base.sh doit passer au vert ici.
INSERT INTO `courses` (`id`, `name`, `status`, `course_type_id`, `teacher_id`, `manager_id`,
                       `subcategory_id`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 54)
SELECT n, CONCAT('Cours ', n), '1', ((n - 1) % 3) + 1, 1 + (n % 5), 1 + (n % 6),
       ((n - 1) % 12) + 1, n, NOW(), NOW()
FROM s;

INSERT INTO `course_tag` (`id`, `course_id`, `tag_id`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 40)
SELECT n, ((n - 1) % 54) + 1, ((n - 1) % 10) + 1 FROM s;
-- 3 rattachements d'étiquettes pendouillants — purgés par ea136_purge_course_tag_orphelins.
INSERT INTO `course_tag` (`id`, `course_id`, `tag_id`) VALUES
  (41, 7001, 1), (42, 3, 9002), (43, 7002, 9003);

-- ------------------------------------------------------------------ 120 sections
INSERT INTO `sections` (`id`, `course_id`, `name`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 120)
SELECT n, ((n - 1) % 54) + 1, CONCAT('Section ', n), ((n - 1) % 4) + 1, NOW(), NOW() FROM s;

-- ------------------------------------------------------------------ 313 supports
-- 224 sains + 89 orphelins de section, dont 16 également orphelins de cours.
-- ⚠️ C'est LE point du runbook §3 ter précision 3 : les 16 ne sont couverts que par
-- ricochet. Ils sont donc volontairement CONSTRUITS comme un sous-ensemble des 89.
INSERT INTO `lessons` (`id`, `name`, `status`, `section_id`, `course_id`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 224)
SELECT n, CONCAT('Support ', n), '1', ((n - 1) % 120) + 1, ((n - 1) % 54) + 1,
       ((n - 1) % 5) + 1, NOW(), NOW()
FROM s;
-- 73 orphelins de section seulement (le cours, lui, existe).
INSERT INTO `lessons` (`id`, `name`, `status`, `section_id`, `course_id`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 73)
SELECT 224 + n, CONCAT('Support orphelin de section ', n), '1', 8000 + n,
       ((n - 1) % 54) + 1, 1, NOW(), NOW()
FROM s;
-- 16 orphelins de section ET de cours — inclus dans les 89 par construction.
INSERT INTO `lessons` (`id`, `name`, `status`, `section_id`, `course_id`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 16)
SELECT 297 + n, CONCAT('Support orphelin de section et de cours ', n), '1', 8100 + n,
       7100 + n, 1, NOW(), NOW()
FROM s;

-- 330 leçons validées — AUCUNE sur un support orphelin : c'est la levée de doute du
-- 08/08 (« personne ne les a jamais ouverts ») et le garde-fou de la migration de purge.
INSERT INTO `learner_lessons` (`id`, `learner_id`, `lesson_id`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 330)
SELECT n, ((n - 1) % 301) + 1, ((n - 1) % 224) + 1, NOW(), NOW() FROM s;

-- ------------------------------------------------------------------ 2 128 inscriptions
-- 0 orphelin des deux côtés (runbook §1.4).
INSERT INTO `enrollments` (`id`, `learner_id`, `course_id`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 2128)
SELECT n, ((n - 1) % 301) + 1, ((n * 7 - 1) % 54) + 1, NOW(), NOW() FROM s;

-- 135 complétions, sans doublon sur (learner_id, course_id) : l'index unique de
-- `2026_08_05_000002` doit pouvoir se poser (vérifié en production, 0 doublon).
INSERT INTO `completed_courses` (`id`, `learner_id`, `course_id`, `completed_at`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 135)
SELECT n, n, ((n - 1) % 54) + 1, NOW(), NOW(), NOW() FROM s;

-- ------------------------------------------------------------------ quiz
INSERT INTO `quizzes` (`id`, `name`, `section_id`, `passing_percentage`, `is_required`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 60)
SELECT n, CONCAT('Quiz ', n), ((n - 1) % 120) + 1, 80, 'no', n, NOW(), NOW() FROM s;
-- 4 quiz pendouillants, porteurs de scores — purgés par ea136_purge_quiz_orphelins.
INSERT INTO `quizzes` (`id`, `name`, `section_id`, `passing_percentage`, `is_required`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 4)
SELECT 60 + n, CONCAT('Quiz orphelin ', n), 8200 + n, 80, 'no', 1, NOW(), NOW() FROM s;

INSERT INTO `questions` (`id`, `question`, `quiz_id`, `type`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 240)
SELECT n, CONCAT('Question ', n, ' ?'), ((n - 1) % 60) + 1, 'mcq_single',
       ((n - 1) % 4) + 1, NOW(), NOW()
FROM s;
INSERT INTO `questions` (`id`, `question`, `quiz_id`, `type`, `order_no`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 8)
SELECT 240 + n, CONCAT('Question de quiz orphelin ', n, ' ?'), 61 + ((n - 1) % 4),
       'mcq_single', 1, NOW(), NOW()
FROM s;

-- Réponses courtes.
INSERT INTO `options` (`id`, `question_id`, `option_text`, `is_correct`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 900)
SELECT n, ((n - 1) % 240) + 1, CONCAT('Réponse ', n), (n % 4 = 0), NOW(), NOW() FROM s;
-- D1 — 28 réponses de plus de 125 caractères, la plus longue à 295 (§5). Elles sont la
-- raison pour laquelle `options.option_text` ne peut PAS revenir à varchar(125) : c'est
-- la décision #142, et la fixture doit la rendre mesurable, pas seulement affirmée.
INSERT INTO `options` (`id`, `question_id`, `option_text`, `is_correct`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 28)
SELECT 900 + n, ((n - 1) % 240) + 1,
       RPAD(CONCAT('Réponse longue ', n, ' : '), 126 + ((n - 1) * 169 DIV 27), 'x'),
       0, NOW(), NOW()
FROM s;

-- 1 184 réponses de quiz (audit doc 11 §4).
INSERT INTO `quiz_scores` (`id`, `learner_id`, `quiz_id`, `question_id`, `is_correct`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 1184)
SELECT n, ((n - 1) % 301) + 1, ((n - 1) % 60) + 1, ((n - 1) % 240) + 1, (n % 3 <> 0), NOW(), NOW()
FROM s;
-- Scores rattachés aux quiz pendouillants : ils partent avec eux.
INSERT INTO `quiz_scores` (`id`, `learner_id`, `quiz_id`, `question_id`, `is_correct`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 12)
SELECT 1184 + n, ((n - 1) % 301) + 1, 61 + ((n - 1) % 4), 241 + ((n - 1) % 7), 1, NOW(), NOW()
FROM s;

-- ------------------------------------------------------------------ fils de discussion
INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `course_id`, `parent_message_id`, `message`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 60)
SELECT n, ((n - 1) % 301) + 1, ((n * 3 - 1) % 301) + 1, ((n - 1) % 54) + 1, NULL,
       CONCAT('Message ', n), NOW(), NOW()
FROM s;
-- 3 messages d'un expéditeur supprimé — purgés par ea136_purge_messages_orphelins.
INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `course_id`, `parent_message_id`, `message`, `created_at`, `updated_at`)
VALUES (61, 9101, 5, 1, NULL, 'Message sans expéditeur 1', NOW(), NOW()),
       (62, 9102, 6, 2, NULL, 'Message sans expéditeur 2', NOW(), NOW()),
       (63, 9103, 7, 3, NULL, 'Message sans expéditeur 3', NOW(), NOW());
-- 2 réponses dont le message parent n'existe plus — DÉTACHÉES, pas supprimées.
INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `course_id`, `parent_message_id`, `message`, `created_at`, `updated_at`)
VALUES (64, 8, 9, 4, 9201, 'Réponse à un message disparu 1', NOW(), NOW()),
       (65, 10, 11, 5, 9202, 'Réponse à un message disparu 2', NOW(), NOW());

-- ------------------------------------------------------------------ invitations
INSERT INTO `invitation_user` (`id`, `name`, `email`, `course_id`, `sent_by`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 20)
SELECT n, CONCAT('Invité ', n), CONCAT('invite', n, '@fixture.invalid'),
       ((n - 1) % 54) + 1, ((n - 1) % 6) + 1, NOW(), NOW()
FROM s;
-- 3 invitations d'un expéditeur inconnu (dont le `sent_by = 0` historique) —
-- ea136_invitations_expediteur_inconnu les DÉTACHE (sent_by = NULL).
INSERT INTO `invitation_user` (`id`, `name`, `email`, `course_id`, `sent_by`, `created_at`, `updated_at`)
VALUES (21, 'Invité expéditeur 0', 'inv21@fixture.invalid', 1, 0, NOW(), NOW()),
       (22, 'Invité expéditeur inconnu', 'inv22@fixture.invalid', 2, 9301, NOW(), NOW()),
       (23, 'Invité expéditeur supprimé', 'inv23@fixture.invalid', 3, 9302, NOW(), NOW());

-- ------------------------------------------------------------------ gamification
INSERT INTO `point_master` (`id`, `title`, `action_key`, `points`, `created_at`, `updated_at`)
VALUES (1, 'Connexion quotidienne', 'daily_login', 5, NOW(), NOW()),
       (2, 'Support terminé', 'lesson_completed', 10, NOW(), NOW());
-- `action_key` et `points` restent NULL : écartés en silence par `$fillable` (§4).
INSERT INTO `user_points` (`id`, `user_id`, `point_master_id`, `action_key`, `points`, `created_at`, `updated_at`)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 40)
SELECT n, ((n - 1) % 301) + 1, ((n - 1) % 2) + 1, NULL, NULL, NOW(), NOW() FROM s;
