-- MSIN-4433 — Impression PDF distribution des dossards
-- Prérequis : MSIN-4436-bib-sort-levels.sql (questions tri batch)
-- Idempotent. Exécuter avant déploiement PHP/JS.

SET @db := DATABASE();

-- Questions affichées sur le rapport (IDs séparés par virgule, par épreuve)
SET @col_dist_que := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'inscriptions_epreuves' AND COLUMN_NAME = 'ba_bib_dist_que_ids'
);
SET @sql_dist_que := IF(
    @col_dist_que = 0,
    'ALTER TABLE inscriptions_epreuves ADD COLUMN ba_bib_dist_que_ids VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''MSIN-4433 — que_id cochés pour PDF distribution'' AFTER ba_bib_sort_2',
    'SELECT ''ba_bib_dist_que_ids already exists'' AS note'
);
PREPARE stmt_dist_que FROM @sql_dist_que;
EXECUTE stmt_dist_que;
DEALLOCATE PREPARE stmt_dist_que;

-- Dernière impression (événement)
SET @col_print_at := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'inscriptions_evenements' AND COLUMN_NAME = 'eve_bib_dist_print_at'
);
SET @sql_print_at := IF(
    @col_print_at = 0,
    'ALTER TABLE inscriptions_evenements ADD COLUMN eve_bib_dist_print_at DATETIME NULL DEFAULT NULL COMMENT ''MSIN-4433 — dernière impression PDF distribution'' AFTER eve_bib_allow_inter_epr',
    'SELECT ''eve_bib_dist_print_at already exists'' AS note'
);
PREPARE stmt_print_at FROM @sql_print_at;
EXECUTE stmt_print_at;
DEALLOCATE PREPARE stmt_print_at;

SET @col_print_by := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'inscriptions_evenements' AND COLUMN_NAME = 'eve_bib_dist_print_by'
);
SET @sql_print_by := IF(
    @col_print_by = 0,
    'ALTER TABLE inscriptions_evenements ADD COLUMN eve_bib_dist_print_by VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''MSIN-4433 — nom affiché dernière impression'' AFTER eve_bib_dist_print_at',
    'SELECT ''eve_bib_dist_print_by already exists'' AS note'
);
PREPARE stmt_print_by FROM @sql_print_by;
EXECUTE stmt_print_by;
DEALLOCATE PREPARE stmt_print_by;

SET @col_print_com := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'inscriptions_evenements' AND COLUMN_NAME = 'eve_bib_dist_print_com_id'
);
SET @sql_print_com := IF(
    @col_print_com = 0,
    'ALTER TABLE inscriptions_evenements ADD COLUMN eve_bib_dist_print_com_id INT NOT NULL DEFAULT 0 COMMENT ''MSIN-4433 — com_id dernière impression'' AFTER eve_bib_dist_print_by',
    'SELECT ''eve_bib_dist_print_com_id already exists'' AS note'
);
PREPARE stmt_print_com FROM @sql_print_com;
EXECUTE stmt_print_com;
DEALLOCATE PREPARE stmt_print_com;

-- Traductions (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_dist_print_title', 'fr', 'Impression PDF — distribution des dossards', 'Liste imprimable pour le comptoir : une section par épreuve, une ligne par dossard assigné. Les colonnes de questions sont mémorisées à chaque génération.', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_title' 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_dist_print_title', 'en', 'PDF print — bib distribution', 'Printable list for the desk: one section per race, one row per assigned bib. Question columns are saved on each generation.', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_title' 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_dist_print_questions', 'fr', 'Colonnes optionnelles (questions)', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_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_dist_print_questions', 'en', 'Optional columns (questions)', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_questions' 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_dist_print_generate', 'fr', 'Générer le PDF', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_generate' 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_dist_print_generate', 'en', 'Generate PDF', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_generate' 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_dist_print_last', 'fr', 'Dernière impression : %s — %s', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_last' 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_dist_print_last', 'en', 'Last print: %s — %s', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_last' 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_dist_print_last_none', 'fr', 'Aucune impression enregistrée pour cet événement.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_last_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_dist_print_last_none', 'en', 'No print recorded for this event yet.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_last_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_dist_print_col_bib', 'fr', 'Dossard', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_bib' 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_dist_print_col_bib', 'en', 'Bib', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_bib' 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_dist_print_col_nom', 'fr', 'Nom', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_nom' 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_dist_print_col_nom', 'en', 'Last name', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_nom' 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_dist_print_col_prenom', 'fr', 'Prénom', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_prenom' 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_dist_print_col_prenom', 'en', 'First name', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_prenom' 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_dist_print_col_sexe', 'fr', 'Sexe', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_sexe' 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_dist_print_col_sexe', 'en', 'Gender', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_col_sexe' 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_dist_print_max_questions', 'fr', 'Maximum %d questions par épreuve pour le format lettre.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_max_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_dist_print_max_questions', 'en', 'Maximum %d questions per race for letter format.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_max_questions' 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_dist_print_empty_epr', 'fr', 'Aucun dossard assigné pour cette épreuve.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_empty_epr' 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_dist_print_empty_epr', 'en', 'No bib assigned for this race.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_empty_epr' 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_dist_print_no_questions', 'fr', 'Aucune question éligible pour cette épreuve.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_no_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_dist_print_no_questions', 'en', 'No eligible question for this race.', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_no_questions' 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_dist_print_footer', 'fr', 'Imprimé le %s — %s', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_footer' 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_dist_print_footer', 'en', 'Printed on %s — %s', '', 'compte.php', '', 0, 1, '', '', '', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM info WHERE info_clef = 'bib_v4_dist_print_footer' AND info_langue = 'en' AND info_prg = 'compte.php');