-- MSIN-4436 — Tri batch dossards : genre, 2 niveaux, questions par épreuve
-- Idempotent. Exécuter avant déploiement PHP/JS.

SET @db := DATABASE();

-- Mémorisation tri 1 / tri 2 par épreuve (clés ba:X ou que:Y)
SET @col_sort1 := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'inscriptions_epreuves' AND COLUMN_NAME = 'ba_bib_sort_1'
);
SET @sql_sort1 := IF(
    @col_sort1 = 0,
    'ALTER TABLE inscriptions_epreuves ADD COLUMN ba_bib_sort_1 VARCHAR(32) NOT NULL DEFAULT '''' COMMENT ''MSIN-4436 — tri batch principal (ba:ID ou que:ID)'' AFTER ba_id',
    'SELECT ''ba_bib_sort_1 already exists'' AS note'
);
PREPARE stmt_sort1 FROM @sql_sort1;
EXECUTE stmt_sort1;
DEALLOCATE PREPARE stmt_sort1;

SET @col_sort2 := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'inscriptions_epreuves' AND COLUMN_NAME = 'ba_bib_sort_2'
);
SET @sql_sort2 := IF(
    @col_sort2 = 0,
    'ALTER TABLE inscriptions_epreuves ADD COLUMN ba_bib_sort_2 VARCHAR(32) NOT NULL DEFAULT '''' COMMENT ''MSIN-4436 — tri batch secondaire (optionnel)'' AFTER ba_bib_sort_1',
    'SELECT ''ba_bib_sort_2 already exists'' AS note'
);
PREPARE stmt_sort2 FROM @sql_sort2;
EXECUTE stmt_sort2;
DEALLOCATE PREPARE stmt_sort2;

-- Phase 0 — Tri par genre (catalogue bib_assignements)
INSERT INTO bib_assignements (ba_nom_fr, ba_nom_en, ba_sql_order, ba_equipe, ba_tri, ba_actif)
SELECT 'Genre', 'Gender', 'FIELD(p.par_sexe, ''f'', ''h'', ''n'', ''a''), p.par_nom, p.par_prenom', 0, 55, 1
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM bib_assignements
    WHERE ba_actif = 1 AND ba_sql_order LIKE '%par_sexe%'
);

INSERT INTO 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 'bib_v4_batch_order_2', 'fr', 'Tri secondaire', 'Critère optionnel pour départager les ex æquo du tri principal.', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_order_2' AND info_langue = 'fr' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_order_2', 'en', 'Secondary sort', 'Optional tie-breaker after the primary sort.', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_order_2' AND info_langue = 'en' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_order_none', 'fr', '— Aucun —', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_order_none' AND info_langue = 'fr' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_order_none', 'en', '— None —', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_order_none' AND info_langue = 'en' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_sort_group_fixed', 'fr', 'Critères fixes', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_sort_group_fixed' AND info_langue = 'fr' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_sort_group_fixed', 'en', 'Fixed criteria', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_sort_group_fixed' AND info_langue = 'en' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_sort_group_questions', 'fr', 'Questions', 'Réponses à une question d''inscription pour cette épreuve.', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_sort_group_questions' AND info_langue = 'fr' AND info_prg = 'compte.php');

INSERT INTO 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 'bib_v4_batch_sort_group_questions', 'en', 'Questions', 'Registration question answers for this race.', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_batch_sort_group_questions' AND info_langue = 'en' AND info_prg = 'compte.php');