<?php

/**
 * MSIN-4469 — Production Excel des dossards.
 * MSIN-4515 — Colonnes Face (bleu) / Endos (rouge) / Info (blanc) par épreuve.
 *
 * Une commande active représente un instantané immuable des séquences. Le fichier
 * peut être téléchargé de nouveau, mais une seconde génération est refusée tant
 * que la commande active n'a pas été remplacée par un futur garde-fou.
 */

/** MSIN-4515 — Côtés Excel imprimeur. */
function fxBibProductionFieldSides() {
    return ['face', 'endos', 'info'];
}

/**
 * MSIN-4515 — Champs toujours proposés (pas des infos événement, pas des questions).
 *
 * @return array<string, array{label_key:string}>
 */
function fxBibProductionCoreFieldCatalog() {
    return [
        'no_bib' => ['label_key' => 'bib_v5_production_field_no_bib'],
        'epr_nom' => ['label_key' => 'bib_v5_production_field_epr_nom'],
        'par_prenom_nom' => ['label_key' => 'bib_v5_production_field_par_prenom_nom'],
    ];
}

/**
 * MSIN-4515 — Ancien nom conservé pour compat ; préférer fxBibProductionCollectSelectableFields.
 *
 * @return array<string, array{label_key:string}>
 */
function fxBibProductionStandardFieldCatalog() {
    return fxBibProductionCoreFieldCatalog();
}

/**
 * MSIN-4515 — Infos participant actives de l’événement (catalogue configurable).
 * SQL autonome : ne dépend PAS de fxGetInfosParticipants (absent en ajax_bib_range).
 *
 * @return array<int, array<string,mixed>>
 */
function fxBibProductionGetEventInfoFields($intEveId) {
    global $objDatabase;

    $intEveId = (int)$intEveId;
    if ($intEveId <= 0) {
        return [];
    }

    // Même jointure que fxGetInfosParticipants — obligatoire ici car l’AJAX
    // n’inclut pas toujours php/inc_fx_participants.php (via panier).
    $tabInfos = $objDatabase->fxGetResults(
        "SELECT i.inf_participant1, i.inf_required, c.*
         FROM inscriptions_infos_participants i
         INNER JOIN inscriptions_infos_participants_champs c ON i.cha_id = c.cha_id
         WHERE i.inf_actif = 1
           AND i.eve_id = $intEveId
         ORDER BY i.inf_tri, i.cha_id"
    );
    if (!is_array($tabInfos)) {
        return [];
    }

    $tabOut = [];
    $tabSkip = ['pro_id' => true, 'pay_id' => true];
    foreach ($tabInfos as $tabInfo) {
        if (!is_array($tabInfo)) {
            continue;
        }
        $strCha = trim((string)($tabInfo['cha_nom'] ?? ''));
        if ($strCha === '' || isset($tabSkip[$strCha])) {
            continue;
        }
        $tabOut[] = $tabInfo;
    }

    return $tabOut;
}

/**
 * MSIN-4515 — Une seule liste sélectionnable pour une épreuve.
 * Cœur (n° / épreuve) + infos participant activées sur l’événement + questions.
 *
 * @return array<int, array{key:string,label:string}>
 */
function fxBibProductionCollectSelectableFields($intEveId, $intEprId, $strLangue = 'fr') {
    $intEveId = (int)$intEveId;
    $intEprId = (int)$intEprId;
    $strLangue = ($strLangue === 'en') ? 'en' : 'fr';
    $tabOut = [];
    $tabSeen = [];

    $fnAdd = function ($strKey, $strLabel) use (&$tabOut, &$tabSeen) {
        $strKey = trim((string)$strKey);
        $strLabel = trim((string)$strLabel);
        if ($strKey === '' || isset($tabSeen[$strKey])) {
            return;
        }
        $tabSeen[$strKey] = true;
        $tabOut[] = [
            'key' => $strKey,
            'label' => $strLabel !== '' ? $strLabel : $strKey,
        ];
    };

    // 1) Toujours : n° dossard + épreuve
    $fnAdd('no_bib', fxBibTexte('bib_v5_production_field_no_bib', 0));
    $fnAdd('epr_nom', fxBibTexte('bib_v5_production_field_epr_nom', 0));

    // 2) Infos participant cochées pour cet événement (urgence, prénom, etc.)
    $blnHasPrenom = false;
    $blnHasNom = false;
    foreach (fxBibProductionGetEventInfoFields($intEveId) as $tabInfo) {
        $strCha = trim((string)($tabInfo['cha_nom'] ?? ''));
        if ($strCha === '') {
            continue;
        }
        if ($strCha === 'par_prenom') {
            $blnHasPrenom = true;
        }
        if ($strCha === 'par_nom') {
            $blnHasNom = true;
        }
        $strLabel = trim((string)($tabInfo['cha_label_' . $strLangue] ?? ''));
        if ($strLabel === '') {
            $strLabel = trim((string)($tabInfo['cha_label_fr'] ?? ''));
        }
        if ($strLabel === '') {
            $strLabel = $strCha;
        }
        $fnAdd($strCha, $strLabel);
    }

    // Prénom + nom seulement si les deux infos sont activées (évite le doublon confus).
    if ($blnHasPrenom && $blnHasNom) {
        $fnAdd('par_prenom_nom', fxBibTexte('bib_v5_production_field_par_prenom_nom', 0));
    }

    // 3) Questions de l’épreuve (clés que:id — pas de collision avec par_*)
    if ($intEprId > 0 && function_exists('fxBibGetQuestionSortOptions')) {
        foreach (fxBibGetQuestionSortOptions($intEprId, $intEveId, $strLangue) as $tabOpt) {
            $fnAdd((string)($tabOpt['key'] ?? ''), (string)($tabOpt['label'] ?? ''));
        }
    }

    return $tabOut;
}

/** MSIN-4515 — Défaut = n° dossard Face + épreuve Info (blanc, effaçable). */
function fxBibProductionDefaultFields() {
    return [
        ['key' => 'no_bib', 'side' => 'face'],
        ['key' => 'epr_nom', 'side' => 'info'],
    ];
}

/**
 * MSIN-4515 — Parse JSON ba_bib_prod_fields.
 *
 * @param mixed $mixJson
 * @return array<int, array{key:string,side:string}>
 */
function fxBibProductionParseFieldsJson($mixJson) {
    if (is_array($mixJson)) {
        $tabData = $mixJson;
    } else {
        $strJson = trim((string)$mixJson);
        if ($strJson === '') {
            return fxBibProductionDefaultFields();
        }
        $tabData = json_decode($strJson, true);
        if (!is_array($tabData)) {
            return fxBibProductionDefaultFields();
        }
    }

    $tabSides = fxBibProductionFieldSides();
    $tabOut = [];
    $tabSeen = [];
    foreach ($tabData as $tabItem) {
        if (!is_array($tabItem)) {
            continue;
        }
        $strKey = trim((string)($tabItem['key'] ?? ''));
        $strSide = strtolower(trim((string)($tabItem['side'] ?? '')));
        if ($strKey === '' || !in_array($strSide, $tabSides, true)) {
            continue;
        }
        if (isset($tabSeen[$strKey])) {
            continue;
        }
        $tabSeen[$strKey] = true;
        $tabOut[] = ['key' => $strKey, 'side' => $strSide];
    }

    return !empty($tabOut) ? $tabOut : fxBibProductionDefaultFields();
}

/** MSIN-4515 — Encode pour BD. */
function fxBibProductionFieldsToJson(array $tabFields) {
    $str = json_encode(array_values($tabFields), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    return $str === false ? '[]' : $str;
}

/**
 * MSIN-4515 — Clé valide pour une épreuve (liste unifiée).
 *
 * @param array<int, array{key:string,label?:string}>|null $tabSelectable
 */
function fxBibProductionFieldKeyAllowed($strKey, $tabSelectable = null, $intEveId = 0, $intEprId = 0, $strLangue = 'fr') {
    $strKey = trim((string)$strKey);
    if ($strKey === '') {
        return false;
    }
    if (!is_array($tabSelectable)) {
        $tabSelectable = fxBibProductionCollectSelectableFields($intEveId, $intEprId, $strLangue);
    }
    foreach ($tabSelectable as $tabOpt) {
        if ((string)($tabOpt['key'] ?? '') === $strKey) {
            return true;
        }
    }
    return false;
}

/**
 * MSIN-4515 — Libellé colonne Excel.
 *
 * @param array<int, array{key:string,label?:string}>|null $tabSelectable
 */
function fxBibProductionFieldLabel($strKey, $tabSelectable = null, $strLangue = 'fr', $intEveId = 0, $intEprId = 0) {
    $strKey = (string)$strKey;
    if (!is_array($tabSelectable)) {
        $tabSelectable = fxBibProductionCollectSelectableFields($intEveId, $intEprId, $strLangue);
    }
    foreach ($tabSelectable as $tabOpt) {
        if ((string)($tabOpt['key'] ?? '') === $strKey) {
            $strLabel = trim((string)($tabOpt['label'] ?? ''));
            return $strLabel !== '' ? $strLabel : $strKey;
        }
    }
    $tabCore = fxBibProductionCoreFieldCatalog();
    if (isset($tabCore[$strKey])) {
        $strLabel = fxBibTexte($tabCore[$strKey]['label_key'], 0);
        return $strLabel !== '' ? $strLabel : $strKey;
    }
    return $strKey;
}

/**
 * MSIN-4515 — Parse POST : fields_json (préféré) ou prod_fields[epr][key]=side.
 *
 * @return array<int, array<int, array{key:string,side:string}>>
 */
function fxBibProductionParseFieldsFromPost(array $tabPost) {
    $tabSides = fxBibProductionFieldSides();
    $tabOut = [];

    // Format fiable (évite les pièges de noms imbriqués / clés spéciales).
    $strJson = trim((string)($tabPost['fields_json'] ?? ''));
    if ($strJson !== '') {
        $tabDecoded = json_decode($strJson, true);
        if (is_array($tabDecoded)) {
            foreach ($tabDecoded as $mixEprId => $tabFields) {
                $intEprId = (int)$mixEprId;
                if ($intEprId <= 0) {
                    continue;
                }
                $tabParsed = [];
                $tabSeen = [];
                if (!is_array($tabFields)) {
                    $tabOut[$intEprId] = [];
                    continue;
                }
                foreach ($tabFields as $tabItem) {
                    if (!is_array($tabItem)) {
                        continue;
                    }
                    $strKey = trim((string)($tabItem['key'] ?? ''));
                    $strSide = strtolower(trim((string)($tabItem['side'] ?? '')));
                    if ($strKey === '' || $strKey === '_empty' || !in_array($strSide, $tabSides, true)) {
                        continue;
                    }
                    if (isset($tabSeen[$strKey])) {
                        continue;
                    }
                    $tabSeen[$strKey] = true;
                    $tabParsed[] = ['key' => $strKey, 'side' => $strSide];
                }
                $tabOut[$intEprId] = $tabParsed;
            }
            return $tabOut;
        }
    }

    $tabRaw = $tabPost['prod_fields'] ?? [];
    if (!is_array($tabRaw)) {
        return [];
    }

    foreach ($tabRaw as $mixEprId => $tabFields) {
        $intEprId = (int)$mixEprId;
        if ($intEprId <= 0 || !is_array($tabFields)) {
            continue;
        }
        $tabParsed = [];
        $tabSeen = [];
        foreach ($tabFields as $strKey => $strSide) {
            $strKey = trim((string)$strKey);
            $strSide = strtolower(trim((string)$strSide));
            if ($strKey === '' || $strKey === '_empty' || $strSide === '' || $strSide === 'none') {
                continue;
            }
            if (!in_array($strSide, $tabSides, true) || isset($tabSeen[$strKey])) {
                continue;
            }
            $tabSeen[$strKey] = true;
            $tabParsed[] = ['key' => $strKey, 'side' => $strSide];
        }
        $tabOut[$intEprId] = $tabParsed;
    }

    return $tabOut;
}

/** MSIN-4515 — Colonne ba_bib_prod_fields présente ? */
function fxBibProductionHasFieldsColumn() {
    global $objDatabase;
    static $blnCached = null;
    if ($blnCached !== null) {
        return $blnCached;
    }
    $tabRow = $objDatabase->fxGetRow(
        "SELECT COUNT(*) AS c
         FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = 'inscriptions_epreuves'
           AND COLUMN_NAME = 'ba_bib_prod_fields'"
    );
    $blnCached = ((int)($tabRow['c'] ?? 0) > 0);
    return $blnCached;
}

/**
 * MSIN-4515 — Enregistre la config champs par épreuve.
 *
 * @param array<int, array<int, array{key:string,side:string}>> $tabSelections
 * @return array{success:bool,message?:string,saved?:array}
 */
function fxBibProductionSaveFields($intEveId, array $tabSelections, $strLangue = 'fr') {
    global $objDatabase;

    $intEveId = (int)$intEveId;
    if ($intEveId <= 0) {
        return ['success' => false, 'message' => fxBibMsg('bib_v4_ajax_epr_invalid')];
    }
    if (fxBibProductionEventIsLocked($intEveId)) {
        return ['success' => false, 'message' => fxBibMsg('bib_v5_production_locked_error')];
    }
    if (!fxBibProductionHasFieldsColumn()) {
        return [
            'success' => false,
            'message' => 'Colonne ba_bib_prod_fields absente — exécuter sql/MSIN-4515-production-champs-imprimeur.sql en dev préprod.',
        ];
    }
    if (empty($tabSelections)) {
        return ['success' => false, 'message' => fxBibMsg('bib_v4_ajax_epr_invalid')];
    }

    $tabEpreuves = $objDatabase->fxGetResults(
        "SELECT epr_id, ba_bib_prod_fields
         FROM inscriptions_epreuves
         WHERE eve_id = $intEveId
           AND epr_actif = 1"
    );
    if (!is_array($tabEpreuves) || empty($tabEpreuves)) {
        return ['success' => false, 'message' => fxBibMsg('bib_v4_ajax_epr_invalid')];
    }

    $intSaved = 0;
    $tabSavedEcho = [];

    foreach ($tabEpreuves as $tabEpr) {
        $intEprId = (int)($tabEpr['epr_id'] ?? 0);
        if ($intEprId <= 0) {
            continue;
        }

        $tabSelectable = fxBibProductionCollectSelectableFields($intEveId, $intEprId, $strLangue);

        $tabChosen = $tabSelections[$intEprId] ?? null;
        if ($tabChosen === null) {
            // POST partiel : ne pas écraser les autres épreuves.
            continue;
        }

        $tabFiltered = [];
        $tabSeen = [];
        foreach ($tabChosen as $tabItem) {
            $strKey = trim((string)($tabItem['key'] ?? ''));
            $strSide = strtolower(trim((string)($tabItem['side'] ?? '')));
            if ($strKey === '' || isset($tabSeen[$strKey])) {
                continue;
            }
            if (!in_array($strSide, fxBibProductionFieldSides(), true)) {
                continue;
            }
            if (!fxBibProductionFieldKeyAllowed($strKey, $tabSelectable)) {
                continue;
            }
            $tabSeen[$strKey] = true;
            $tabFiltered[] = ['key' => $strKey, 'side' => $strSide];
        }

        if (empty($tabFiltered)) {
            $tabFiltered = fxBibProductionDefaultFields();
        } else {
            // MSIN-4515 — Toujours garder le n° dossard (sinon fichier imprimeur inutilisable).
            $blnHasBib = false;
            foreach ($tabFiltered as $tabItem) {
                if (($tabItem['key'] ?? '') === 'no_bib') {
                    $blnHasBib = true;
                    break;
                }
            }
            if (!$blnHasBib) {
                array_unshift($tabFiltered, ['key' => 'no_bib', 'side' => 'face']);
            }
        }

        $strJson = fxBibProductionFieldsToJson($tabFiltered);
        $blnOk = $objDatabase->fxQuery(
            "UPDATE inscriptions_epreuves
             SET ba_bib_prod_fields = '" . $objDatabase->fxEscape($strJson) . "'
             WHERE epr_id = $intEprId
               AND eve_id = $intEveId
             LIMIT 1"
        );
        if ($blnOk === false) {
            return [
                'success' => false,
                'message' => fxBibMsg('bib_v5_production_generation_error'),
            ];
        }

        $tabCheck = $objDatabase->fxGetRow(
            "SELECT ba_bib_prod_fields
             FROM inscriptions_epreuves
             WHERE epr_id = $intEprId
             LIMIT 1"
        );
        $strStored = trim((string)($tabCheck['ba_bib_prod_fields'] ?? ''));
        if ($strStored === '' || $strStored !== $strJson) {
            return [
                'success' => false,
                'message' => fxBibMsg('bib_v5_production_generation_error'),
            ];
        }

        $intSaved++;
        $tabSavedEcho[$intEprId] = $tabFiltered;
    }

    if ($intSaved < 1) {
        return ['success' => false, 'message' => fxBibMsg('bib_v4_ajax_epr_invalid')];
    }

    return [
        'success' => true,
        'message' => fxBibMsg('bib_v5_production_fields_saved'),
        'saved' => $tabSavedEcho,
    ];
}

/**
 * MSIN-4515 — Lettre de colonne Excel (0 = A).
 */
function fxBibProductionColumnLetter($intIndex) {
    $intIndex = (int)$intIndex;
    if ($intIndex < 0) {
        $intIndex = 0;
    }
    $str = '';
    $intN = $intIndex;
    do {
        $str = chr(65 + ($intN % 26)) . $str;
        $intN = intdiv($intN, 26) - 1;
    } while ($intN >= 0);
    return $str;
}

/**
 * MSIN-4515 — Union ordonnée des colonnes (face → endos → info).
 *
 * @param array<int, array<string,mixed>> $tabRaces
 * @return array<int, array{key:string,side:string,label:string}>
 */
function fxBibProductionBuildColumnPlan(array $tabRaces) {
    $tabSideRank = ['face' => 1, 'endos' => 2, 'info' => 3];
    $tabByKey = [];

    foreach ($tabRaces as $tabRace) {
        $tabFields = isset($tabRace['fields']) && is_array($tabRace['fields'])
            ? $tabRace['fields']
            : fxBibProductionDefaultFields();
        $tabLabels = isset($tabRace['field_labels']) && is_array($tabRace['field_labels'])
            ? $tabRace['field_labels']
            : [];
        foreach ($tabFields as $tabField) {
            $strKey = (string)($tabField['key'] ?? '');
            $strSide = (string)($tabField['side'] ?? 'info');
            if ($strKey === '') {
                continue;
            }
            $strLabel = (string)($tabLabels[$strKey] ?? $strKey);
            if (!isset($tabByKey[$strKey])) {
                $tabByKey[$strKey] = [
                    'key' => $strKey,
                    'side' => $strSide,
                    'label' => $strLabel,
                ];
                continue;
            }
            // Conflit de côté entre épreuves : privilégier face > endos > info.
            $intOld = $tabSideRank[$tabByKey[$strKey]['side']] ?? 99;
            $intNew = $tabSideRank[$strSide] ?? 99;
            if ($intNew < $intOld) {
                $tabByKey[$strKey]['side'] = $strSide;
            }
            if ($tabByKey[$strKey]['label'] === $strKey && $strLabel !== '') {
                $tabByKey[$strKey]['label'] = $strLabel;
            }
        }
    }

    if (empty($tabByKey)) {
        return [
            ['key' => 'no_bib', 'side' => 'face', 'label' => fxBibTexte('bib_v5_production_field_no_bib', 0)],
            ['key' => 'epr_nom', 'side' => 'info', 'label' => fxBibTexte('bib_v5_production_field_epr_nom', 0)],
        ];
    }

    $tabOut = array_values($tabByKey);
    usort($tabOut, function ($a, $b) use ($tabSideRank) {
        $intSa = $tabSideRank[$a['side']] ?? 99;
        $intSb = $tabSideRank[$b['side']] ?? 99;
        if ($intSa !== $intSb) {
            return $intSa - $intSb;
        }
        return strcmp($a['key'], $b['key']);
    });

    return $tabOut;
}

function fxBibProductionTemplatePath() {
    return __DIR__ . DIRECTORY_SEPARATOR . 'templates'
        . DIRECTORY_SEPARATOR . 'MSIN-4469-commande-dossards.xlsx';
}

function fxBibProductionStorageRoot() {
    return dirname(__DIR__) . DIRECTORY_SEPARATOR . 'data'
        . DIRECTORY_SEPARATOR . 'bib-production';
}

function fxBibProductionGetActiveOrder($intEveId) {
    global $objDatabase;

    $intEveId = (int)$intEveId;
    if ($intEveId <= 0) {
        return null;
    }

    return $objDatabase->fxGetRow(
        "SELECT *
         FROM inscriptions_bib_production_orders
         WHERE eve_id = $intEveId
           AND bpo_active_key = 1
         LIMIT 1"
    );
}

function fxBibProductionGetOrder($intOrderId, $intEveId = 0) {
    global $objDatabase;

    $intOrderId = (int)$intOrderId;
    $intEveId = (int)$intEveId;
    if ($intOrderId <= 0) {
        return null;
    }

    $strWhereEve = $intEveId > 0 ? " AND eve_id = $intEveId" : '';
    return $objDatabase->fxGetRow(
        "SELECT *
         FROM inscriptions_bib_production_orders
         WHERE bpo_id = $intOrderId
         $strWhereEve
         LIMIT 1"
    );
}

function fxBibProductionEventIsLocked($intEveId) {
    return fxBibProductionGetActiveOrder($intEveId) !== null;
}

/**
 * MSIN-4469 — Déverrouillage temporaire (dev) : même droit que la génération
 * (promoteur avec dossards.manage, ou superadmin).
 */
function fxBibProductionDevelopmentUnlock($intEveId) {
    global $objDatabase;

    $intEveId = (int)$intEveId;
    if ($intEveId <= 0 || !fxBibProductionUserCanManageEvent($intEveId)) {
        return ['success' => false, 'message' => fxBibMsg('bib_v4_ajax_unauthorized')];
    }

    $tabOrder = fxBibProductionGetActiveOrder($intEveId);
    if (!$tabOrder) {
        return ['success' => true];
    }

    $strBy = fxBibGetCurrentPromoteurDisplayName();
    $objDatabase->fxQuery(
        "UPDATE inscriptions_bib_production_orders
         SET bpo_status = 'development_unlocked',
             bpo_active_key = NULL,
             bpo_superseded_at = NOW(),
             bpo_superseded_by = '" . $objDatabase->fxEscape($strBy) . "',
             bpo_superseded_reason = 'MSIN-4469 development unlock'
         WHERE bpo_id = " . (int)$tabOrder['bpo_id'] . "
           AND bpo_active_key = 1"
    );

    return ['success' => true];
}

function fxBibProductionEpreuveIsLocked($intEprId) {
    global $objDatabase;

    $intEprId = (int)$intEprId;
    if ($intEprId <= 0) {
        return false;
    }

    $intEveId = (int)$objDatabase->fxGetVar(
        "SELECT eve_id FROM inscriptions_epreuves WHERE epr_id = $intEprId LIMIT 1"
    );
    return fxBibProductionEventIsLocked($intEveId);
}

/**
 * Vérifie l'accès réel à l'événement, en plus de la session et du jeton CSRF.
 * MSIN-4515 — Charge eve_acces si absent (AJAX ne l’inclut pas toujours).
 */
function fxBibProductionUserCanManageEvent($intEveId) {
    $intEveId = (int)$intEveId;
    if ($intEveId <= 0 || !fxBibRequirePromoteurSession()) {
        return false;
    }

    if (!empty($_SESSION['usa_id'])) {
        return true;
    }

    $intComId = (int)($_SESSION['com_id'] ?? 0);
    if ($intComId <= 0) {
        return false;
    }

    if (!function_exists('fxEveAccesHasPermission')) {
        $strEveAcces = __DIR__ . '/inc_fx_eve_acces.php';
        if (is_file($strEveAcces)) {
            require_once $strEveAcces;
        }
    }

    return function_exists('fxEveAccesHasPermission')
        && fxEveAccesHasPermission($intComId, $intEveId, 'dossards.manage');
}

/**
 * Charge les épreuves actives et toutes leurs séquences, dans l'ordre d'affichage.
 */
function fxBibProductionCollectSnapshot($intEveId, $strLangue = 'fr') {
    global $objDatabase;

    $intEveId = (int)$intEveId;
    $strLangue = in_array($strLangue, ['fr', 'en'], true) ? $strLangue : 'fr';

    $tabEvent = $objDatabase->fxGetRow(
        "SELECT eve_id, eve_nom_fr, eve_nom_en
         FROM inscriptions_evenements
         WHERE eve_id = $intEveId
         LIMIT 1"
    );
    if (!$tabEvent) {
        return ['success' => false, 'message' => fxBibMsg('bib_v5_production_event_invalid')];
    }

    $tabEpreuves = $objDatabase->fxGetResults(
        "SELECT *
         FROM inscriptions_epreuves
         WHERE eve_id = $intEveId
           AND epr_actif = 1
         ORDER BY epr_tri, epr_id"
    );
    if (!is_array($tabEpreuves)) {
        $tabEpreuves = [];
    }

    $tabRaces = [];
    $intBibCount = 0;
    $intRangeCount = 0;

    foreach ($tabEpreuves as $tabEpreuve) {
        $intEprId = (int)($tabEpreuve['epr_id'] ?? 0);
        if ($intEprId <= 0) {
            continue;
        }

        // MSIN-4469 — La commande porte sur toute la séquence à imprimer,
        // même lorsqu’aucun coureur ni aucun dossard assigné ne s’y trouve encore.
        $tabRangesRaw = $objDatabase->fxGetResults(
            "SELECT epr_bib_id, epr_bib_start, epr_bib_finish
             FROM inscriptions_epreuves_bib
             WHERE epr_id = $intEprId
             ORDER BY epr_bib_start, epr_bib_id"
        );
        if (!is_array($tabRangesRaw)) {
            continue;
        }

        $tabRanges = [];
        foreach ($tabRangesRaw as $tabRange) {
            $intStart = (int)($tabRange['epr_bib_start'] ?? 0);
            $intFinish = (int)($tabRange['epr_bib_finish'] ?? 0);
            if ($intStart <= 0 || $intFinish < $intStart) {
                return ['success' => false, 'message' => fxBibMsg('bib_v5_production_range_invalid')];
            }

            $intQuantity = $intFinish - $intStart + 1;
            $intBibCount += $intQuantity;
            $intRangeCount++;
            $tabRanges[] = [
                'epr_bib_id' => (int)$tabRange['epr_bib_id'],
                'start'      => $intStart,
                'finish'     => $intFinish,
                'quantity'   => $intQuantity,
            ];
        }

        if (!empty($tabRanges)) {
            // MSIN-4515 — Champs Face/Endos/Info + libellés pour l’onglet Imprimeur.
            $tabSelectable = fxBibProductionCollectSelectableFields($intEveId, $intEprId, $strLangue);
            $tabFields = fxBibProductionParseFieldsJson($tabEpreuve['ba_bib_prod_fields'] ?? '');
            // Ne garder que les clés encore sélectionnables (infos eve / questions à jour).
            $tabFieldsFiltered = [];
            foreach ($tabFields as $tabField) {
                $strKey = (string)($tabField['key'] ?? '');
                if (fxBibProductionFieldKeyAllowed($strKey, $tabSelectable)) {
                    $tabFieldsFiltered[] = $tabField;
                }
            }
            if (empty($tabFieldsFiltered)) {
                $tabFieldsFiltered = fxBibProductionDefaultFields();
            }
            $tabFieldLabels = [];
            foreach ($tabFieldsFiltered as $tabField) {
                $strKey = (string)($tabField['key'] ?? '');
                if ($strKey === '') {
                    continue;
                }
                $tabFieldLabels[$strKey] = fxBibProductionFieldLabel(
                    $strKey,
                    $tabSelectable,
                    $strLangue,
                    $intEveId,
                    $intEprId
                );
            }

            $tabRaces[] = [
                'epr_id' => $intEprId,
                // Le libellé type/catégorie est le nom court retenu pour la V1.
                'label'  => fxBibEpreuveDisplayName($tabEpreuve, $strLangue),
                'ranges' => $tabRanges,
                'fields' => $tabFieldsFiltered,
                'field_labels' => $tabFieldLabels,
            ];
        }
    }

    if ($intRangeCount === 0) {
        return ['success' => false, 'message' => fxBibMsg('bib_v5_production_no_ranges')];
    }

    return [
        'success' => true,
        'snapshot' => [
            'event' => [
                'eve_id' => $intEveId,
                'name'   => trim((string)($tabEvent['eve_nom_' . $strLangue] ?? '')),
            ],
            'language'    => $strLangue,
            'bib_count'   => $intBibCount,
            'range_count' => $intRangeCount,
            'races'       => $tabRaces,
        ],
    ];
}

function fxBibProductionSafeFilePart($strValue) {
    $strValue = trim((string)$strValue);
    if (function_exists('iconv')) {
        $strAscii = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $strValue);
        if ($strAscii !== false) {
            $strValue = $strAscii;
        }
    }
    $strValue = strtolower($strValue);
    $strValue = preg_replace('/[^a-z0-9]+/', '-', $strValue);
    $strValue = trim((string)$strValue, '-');
    return $strValue !== '' ? $strValue : 'evenement';
}

function fxBibProductionEnsureDirectory($strDirectory) {
    if (is_dir($strDirectory)) {
        return true;
    }
    return @mkdir($strDirectory, 0770, true) || is_dir($strDirectory);
}

function fxBibProductionXmlLoad($strXml) {
    $objDom = new DOMDocument('1.0', 'UTF-8');
    $objDom->preserveWhiteSpace = false;
    $objDom->formatOutput = false;
    if (!@$objDom->loadXML($strXml)) {
        throw new Exception('MSIN-4469 invalid OpenXML part');
    }
    return $objDom;
}

/** @return DOMElement|null */
function fxBibProductionXmlFindRow(DOMXPath $objXpath, $intRow) {
    $objRow = $objXpath->query('//m:sheetData/m:row[@r="' . (int)$intRow . '"]')->item(0);
    return $objRow instanceof DOMElement ? $objRow : null;
}

function fxBibProductionXmlClearCell(DOMElement $objCell) {
    while ($objCell->firstChild) {
        $objCell->removeChild($objCell->firstChild);
    }
    $objCell->removeAttribute('t');
}

function fxBibProductionXmlFindCell(DOMElement $objRow, $strColumn) {
    foreach ($objRow->childNodes as $objChild) {
        if ($objChild instanceof DOMElement && $objChild->localName === 'c') {
            $strRef = $objChild->getAttribute('r');
            if (preg_replace('/[0-9]+/', '', $strRef) === $strColumn) {
                return $objChild;
            }
        }
    }
    return null;
}

function fxBibProductionXmlEnsureCell(
    DOMDocument $objDom,
    DOMElement $objRow,
    $strColumn,
    $intRow,
    $strStyle = ''
) {
    $objCell = fxBibProductionXmlFindCell($objRow, $strColumn);
    if (!$objCell) {
        $objCell = $objDom->createElementNS(
            'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
            'c'
        );
        $objRow->appendChild($objCell);
    }
    $objCell->setAttribute('r', $strColumn . (int)$intRow);
    if ($strStyle !== '') {
        $objCell->setAttribute('s', $strStyle);
    }
    return $objCell;
}

function fxBibProductionXmlSetInlineString(
    DOMDocument $objDom,
    DOMElement $objRow,
    $strColumn,
    $intRow,
    $strValue,
    $strStyle = ''
) {
    $strNs = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
    $objCell = fxBibProductionXmlEnsureCell($objDom, $objRow, $strColumn, $intRow, $strStyle);
    fxBibProductionXmlClearCell($objCell);
    $objCell->setAttribute('t', 'inlineStr');
    $objInline = $objDom->createElementNS($strNs, 'is');
    $objText = $objDom->createElementNS($strNs, 't');
    $objText->appendChild($objDom->createTextNode((string)$strValue));
    $objInline->appendChild($objText);
    $objCell->appendChild($objInline);
}

function fxBibProductionXmlSetNumber(
    DOMDocument $objDom,
    DOMElement $objRow,
    $strColumn,
    $intRow,
    $mixValue,
    $strStyle = ''
) {
    $strNs = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
    $objCell = fxBibProductionXmlEnsureCell($objDom, $objRow, $strColumn, $intRow, $strStyle);
    fxBibProductionXmlClearCell($objCell);
    $objValue = $objDom->createElementNS($strNs, 'v');
    $objValue->appendChild($objDom->createTextNode((string)$mixValue));
    $objCell->appendChild($objValue);
}

function fxBibProductionXmlSetFormula(
    DOMDocument $objDom,
    DOMElement $objRow,
    $strColumn,
    $intRow,
    $strFormula,
    $strStyle = '',
    $mixCachedValue = null
) {
    $strNs = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
    $objCell = fxBibProductionXmlEnsureCell($objDom, $objRow, $strColumn, $intRow, $strStyle);
    fxBibProductionXmlClearCell($objCell);
    $objFormula = $objDom->createElementNS($strNs, 'f');
    $objFormula->appendChild($objDom->createTextNode($strFormula));
    $objCell->appendChild($objFormula);
    // MSIN-4469 — Valeur calculée en cache : sinon Excel/LibreOffice peut
    // afficher le TOTAL vide tant que le recalcul n'a pas tourné.
    if ($mixCachedValue !== null && $mixCachedValue !== '') {
        $objValue = $objDom->createElementNS($strNs, 'v');
        $objValue->appendChild($objDom->createTextNode((string)$mixCachedValue));
        $objCell->appendChild($objValue);
    }
}

function fxBibProductionXmlCloneRow(DOMElement $objTemplateRow, $intTargetRow) {
    $objRow = $objTemplateRow->cloneNode(true);
    $objRow->setAttribute('r', (string)$intTargetRow);
    foreach ($objRow->childNodes as $objChild) {
        if ($objChild instanceof DOMElement && $objChild->localName === 'c') {
            $strColumn = preg_replace('/[0-9]+/', '', $objChild->getAttribute('r'));
            $objChild->setAttribute('r', $strColumn . $intTargetRow);
            fxBibProductionXmlClearCell($objChild);
        }
    }
    return $objRow;
}

function fxBibProductionXmlShiftReference($strReference, $intFromRow, $intOffset) {
    return preg_replace_callback(
        '/([A-Z]+)([0-9]+)/',
        function ($tabMatch) use ($intFromRow, $intOffset) {
            $intRow = (int)$tabMatch[2];
            if ($intRow >= $intFromRow) {
                $intRow += $intOffset;
            }
            return $tabMatch[1] . $intRow;
        },
        $strReference
    );
}

/**
 * MSIN-4515 — Injecte styles Face (bleu) / Endos (rouge) / Info (gris) dans styles.xml.
 *
 * @return array{face:array{h:int,d:int},endos:array{h:int,d:int},info:array{h:int,d:int}}
 */
function fxBibProductionEnsureSideStyles(ZipArchive $objZip) {
    $strStyles = $objZip->getFromName('xl/styles.xml');
    if ($strStyles === false) {
        throw new Exception('MSIN-4515 styles.xml missing');
    }
    if (strpos($strStyles, 'MSIN-4515-side-styles') !== false) {
        // Déjà injecté : lire les indices depuis un commentaire XML.
        if (preg_match(
            '/MSIN-4515-side-styles face_h=(\d+) face_d=(\d+) endos_h=(\d+) endos_d=(\d+) info_h=(\d+) info_d=(\d+)/',
            $strStyles,
            $tabMatch
        )) {
            return [
                'face' => ['h' => (int)$tabMatch[1], 'd' => (int)$tabMatch[2]],
                'endos' => ['h' => (int)$tabMatch[3], 'd' => (int)$tabMatch[4]],
                'info' => ['h' => (int)$tabMatch[5], 'd' => (int)$tabMatch[6]],
            ];
        }
    }

    $objDom = fxBibProductionXmlLoad($strStyles);
    $objXpath = new DOMXPath($objDom);
    $objXpath->registerNamespace('m', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
    $strNs = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';

    $objFills = $objXpath->query('//m:fills')->item(0);
    $objCellXfs = $objXpath->query('//m:cellXfs')->item(0);
    if (!($objFills instanceof DOMElement) || !($objCellXfs instanceof DOMElement)) {
        throw new Exception('MSIN-4515 styles structure invalid');
    }

    $intFillCount = (int)$objFills->getAttribute('count');
    $tabFillRgb = [
        'face_h' => '5B9BD5',
        'face_d' => 'BDD7EE',
        'endos_h' => 'C00000',
        'endos_d' => 'F4CCCC',
        'info_h' => 'D9D9D9',
        'info_d' => 'FFFFFF',
    ];
    $tabFillIds = [];
    foreach ($tabFillRgb as $strKey => $strRgb) {
        $objFill = $objDom->createElementNS($strNs, 'fill');
        $objPattern = $objDom->createElementNS($strNs, 'patternFill');
        $objPattern->setAttribute('patternType', 'solid');
        $objFg = $objDom->createElementNS($strNs, 'fgColor');
        $objFg->setAttribute('rgb', 'FF' . $strRgb);
        $objPattern->appendChild($objFg);
        $objFill->appendChild($objPattern);
        $objFills->appendChild($objFill);
        $tabFillIds[$strKey] = $intFillCount;
        $intFillCount++;
    }
    $objFills->setAttribute('count', (string)$intFillCount);

    $intXfCount = (int)$objCellXfs->getAttribute('count');
    $tabStyleIds = [];
    $tabXfMap = [
        'face_h' => $tabFillIds['face_h'],
        'face_d' => $tabFillIds['face_d'],
        'endos_h' => $tabFillIds['endos_h'],
        'endos_d' => $tabFillIds['endos_d'],
        'info_h' => $tabFillIds['info_h'],
        'info_d' => $tabFillIds['info_d'],
    ];
    foreach ($tabXfMap as $strKey => $intFillId) {
        $objXf = $objDom->createElementNS($strNs, 'xf');
        $objXf->setAttribute('numFmtId', '0');
        $objXf->setAttribute('fontId', '0');
        $objXf->setAttribute('fillId', (string)$intFillId);
        $objXf->setAttribute('borderId', '0');
        $objXf->setAttribute('xfId', '0');
        $objXf->setAttribute('applyFill', '1');
        $objXf->setAttribute('applyAlignment', '1');
        $objAlign = $objDom->createElementNS($strNs, 'alignment');
        $objAlign->setAttribute('horizontal', 'center');
        $objAlign->setAttribute('vertical', 'center');
        $objAlign->setAttribute('wrapText', '1');
        $objXf->appendChild($objAlign);
        $objCellXfs->appendChild($objXf);
        $tabStyleIds[$strKey] = $intXfCount;
        $intXfCount++;
    }
    $objCellXfs->setAttribute('count', (string)$intXfCount);

    $strComment = sprintf(
        'MSIN-4515-side-styles face_h=%d face_d=%d endos_h=%d endos_d=%d info_h=%d info_d=%d',
        $tabStyleIds['face_h'],
        $tabStyleIds['face_d'],
        $tabStyleIds['endos_h'],
        $tabStyleIds['endos_d'],
        $tabStyleIds['info_h'],
        $tabStyleIds['info_d']
    );
    $objDom->insertBefore($objDom->createComment($strComment), $objDom->documentElement);

    $objZip->deleteName('xl/styles.xml');
    $objZip->addFromString('xl/styles.xml', $objDom->saveXML());

    return [
        'face' => ['h' => $tabStyleIds['face_h'], 'd' => $tabStyleIds['face_d']],
        'endos' => ['h' => $tabStyleIds['endos_h'], 'd' => $tabStyleIds['endos_d']],
        'info' => ['h' => $tabStyleIds['info_h'], 'd' => $tabStyleIds['info_d']],
    ];
}

/**
 * MSIN-4515 — Participants indexés par no_bib pour remplir les colonnes.
 *
 * @return array<string, array<string,mixed>>
 */
function fxBibProductionLoadParticipantsByBib($intEprId) {
    global $objDatabase;

    $intEprId = (int)$intEprId;
    if ($intEprId <= 0) {
        return [];
    }

    $sql = "
        SELECT p.*
        FROM resultats_participants p
        WHERE p.epr_id = $intEprId
          AND p.is_cancelled = 0
          AND TRIM(CAST(p.no_bib AS CHAR)) <> ''
          AND TRIM(CAST(p.no_bib AS CHAR)) <> '0'
    ";
    $tabRows = $objDatabase->fxGetResults($sql);
    if (!is_array($tabRows)) {
        return [];
    }

    $tabOut = [];
    foreach ($tabRows as $tabRow) {
        $strBib = trim((string)($tabRow['no_bib'] ?? ''));
        if ($strBib === '') {
            continue;
        }
        // Premier gagnant si doublon (anomalie déjà gérée ailleurs).
        if (!isset($tabOut[$strBib])) {
            $tabOut[$strBib] = $tabRow;
        }
    }

    return $tabOut;
}

/**
 * MSIN-4515 — Valeur d’une colonne pour une ligne dossard.
 *
 * @param array<string,mixed>|null $tabParticipant
 * @param array<int,string> $tabAnswers que_id => réponse
 */
function fxBibProductionResolveCellValue(
    $strKey,
    $intBib,
    $strRaceLabel,
    $tabParticipant,
    array $tabAnswers
) {
    $strKey = (string)$strKey;
    $fnUn = function ($str) {
        $str = (string)$str;
        return function_exists('fxUnescape') ? fxUnescape($str) : $str;
    };

    if ($strKey === 'no_bib') {
        return (string)(int)$intBib;
    }
    if ($strKey === 'epr_nom') {
        return (string)$strRaceLabel;
    }

    if (!is_array($tabParticipant)) {
        return '';
    }

    switch ($strKey) {
        case 'par_prenom':
            return trim($fnUn($tabParticipant['par_prenom'] ?? ''));
        case 'par_nom':
            return trim($fnUn($tabParticipant['par_nom'] ?? ''));
        case 'par_prenom_nom':
            return trim(
                $fnUn($tabParticipant['par_prenom'] ?? '')
                . ' '
                . $fnUn($tabParticipant['par_nom'] ?? '')
            );
        case 'par_sexe':
            return trim((string)($tabParticipant['par_sexe'] ?? ''));
        case 'par_age':
            $strAge = trim((string)($tabParticipant['par_age'] ?? ''));
            if ($strAge !== '' && $strAge !== '0') {
                return $strAge;
            }
            $strNaissance = trim((string)($tabParticipant['par_naissance'] ?? ''));
            if ($strNaissance === '' || $strNaissance === '0000-00-00') {
                return '';
            }
            try {
                $objBirth = new DateTime($strNaissance);
                $objNow = new DateTime('today');
                return (string)$objBirth->diff($objNow)->y;
            } catch (Exception $objEx) {
                return '';
            }
        default:
            if (preg_match('/^que:(\d+)$/', $strKey, $tabMatch)) {
                $intQueId = (int)$tabMatch[1];
                return trim((string)($tabAnswers[$intQueId] ?? ''));
            }
            // Infos participant (par_contact_urgence_*, par_telephone1, etc.)
            if (array_key_exists($strKey, $tabParticipant)) {
                return trim($fnUn($tabParticipant[$strKey] ?? ''));
            }
            return '';
    }
}

/**
 * MSIN-4469 / MSIN-4515 — Onglet Imprimeur : 1 ligne / dossard, colonnes colorées.
 *
 * @param array<string,array{h:int,d:int}> $tabSideStyles
 */
function fxBibProductionBuildSupplierXml($strXml, array $tabSnapshot, array $tabLabels, array $tabSideStyles = []) {
    $objDom = fxBibProductionXmlLoad($strXml);
    $objXpath = new DOMXPath($objDom);
    $objXpath->registerNamespace('m', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
    $strNs = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';

    $objSheetData = $objXpath->query('//m:sheetData')->item(0);
    $objHeaderTemplate = fxBibProductionXmlFindRow($objXpath, 1);
    $objDataTemplate = fxBibProductionXmlFindRow($objXpath, 2);
    if (!$objSheetData || !$objHeaderTemplate || !$objDataTemplate) {
        throw new Exception('MSIN-4469 invalid supplier template');
    }

    while ($objSheetData->firstChild) {
        $objSheetData->removeChild($objSheetData->firstChild);
    }

    $tabColumns = fxBibProductionBuildColumnPlan($tabSnapshot['races'] ?? []);
    $intColCount = count($tabColumns);
    if ($intColCount < 1) {
        throw new Exception('MSIN-4515 no printer columns');
    }

    if (empty($tabSideStyles)) {
        $tabSideStyles = [
            'face' => ['h' => 0, 'd' => 0],
            'endos' => ['h' => 0, 'd' => 0],
            'info' => ['h' => 0, 'd' => 0],
        ];
    }

    // Largeurs colonnes.
    $objCols = $objXpath->query('//m:cols')->item(0);
    if ($objCols instanceof DOMElement) {
        while ($objCols->firstChild) {
            $objCols->removeChild($objCols->firstChild);
        }
        for ($intC = 0; $intC < $intColCount; $intC++) {
            $objCol = $objDom->createElementNS($strNs, 'col');
            $objCol->setAttribute('min', (string)($intC + 1));
            $objCol->setAttribute('max', (string)($intC + 1));
            $strKey = $tabColumns[$intC]['key'];
            $fltWidth = ($strKey === 'no_bib') ? 14.0 : (($strKey === 'epr_nom') ? 28.0 : 22.0);
            $objCol->setAttribute('width', (string)$fltWidth);
            $objCol->setAttribute('customWidth', '1');
            $objCols->appendChild($objCol);
        }
    }

    $objHeader = fxBibProductionXmlCloneRow($objHeaderTemplate, 1);
    foreach ($tabColumns as $intC => $tabCol) {
        $strLetter = fxBibProductionColumnLetter($intC);
        $strSide = (string)$tabCol['side'];
        $strStyle = (string)($tabSideStyles[$strSide]['h'] ?? 0);
        $strLabel = (string)$tabCol['label'];
        if ($tabCol['key'] === 'no_bib' && isset($tabLabels['bib']) && $tabLabels['bib'] !== '') {
            $strLabel = $tabLabels['bib'];
        } elseif ($tabCol['key'] === 'epr_nom' && isset($tabLabels['race']) && $tabLabels['race'] !== '') {
            $strLabel = $tabLabels['race'];
        }
        fxBibProductionXmlSetInlineString($objDom, $objHeader, $strLetter, 1, $strLabel, $strStyle);
    }
    $objSheetData->appendChild($objHeader);

    $strLangue = (string)($tabSnapshot['language'] ?? 'fr');
    $intRow = 2;
    foreach ($tabSnapshot['races'] as $intRaceIndex => $tabRace) {
        if ($intRaceIndex > 0) {
            $intRow++;
        }

        $intEprId = (int)($tabRace['epr_id'] ?? 0);
        $tabRaceFields = isset($tabRace['fields']) && is_array($tabRace['fields'])
            ? $tabRace['fields']
            : fxBibProductionDefaultFields();
        $tabRaceKeys = [];
        foreach ($tabRaceFields as $tabField) {
            $tabRaceKeys[(string)$tabField['key']] = true;
        }

        $tabByBib = fxBibProductionLoadParticipantsByBib($intEprId);
        $tabQueIds = [];
        foreach ($tabRaceFields as $tabField) {
            if (preg_match('/^que:(\d+)$/', (string)$tabField['key'], $tabMatch)) {
                $tabQueIds[] = (int)$tabMatch[1];
            }
        }
        $tabAnswersByParticipant = [];
        if (!empty($tabQueIds) && !empty($tabByBib) && function_exists('fxBibGetDistPrintQuestionAnswers')) {
            $tabAnswersByParticipant = fxBibGetDistPrintQuestionAnswers(
                $intEprId,
                $tabQueIds,
                array_values($tabByBib),
                $strLangue
            );
        }

        foreach ($tabRace['ranges'] as $tabRange) {
            for ($intBib = (int)$tabRange['start']; $intBib <= (int)$tabRange['finish']; $intBib++) {
                $objRow = fxBibProductionXmlCloneRow($objDataTemplate, $intRow);
                $strBibKey = (string)$intBib;
                $tabParticipant = $tabByBib[$strBibKey] ?? null;
                $tabAnswers = [];
                if (is_array($tabParticipant) && function_exists('fxBibDistPrintAnswerKey')) {
                    $strAnsKey = fxBibDistPrintAnswerKey($tabParticipant);
                    $tabAnswers = $tabAnswersByParticipant[$strAnsKey] ?? [];
                }

                foreach ($tabColumns as $intC => $tabCol) {
                    $strLetter = fxBibProductionColumnLetter($intC);
                    $strSide = (string)$tabCol['side'];
                    $strStyle = (string)($tabSideStyles[$strSide]['d'] ?? 0);
                    $strKey = (string)$tabCol['key'];

                    // Colonne absente de la config de cette épreuve → cellule vide (style info).
                    if (!isset($tabRaceKeys[$strKey])) {
                        fxBibProductionXmlSetInlineString(
                            $objDom,
                            $objRow,
                            $strLetter,
                            $intRow,
                            '',
                            (string)($tabSideStyles['info']['d'] ?? 0)
                        );
                        continue;
                    }

                    $strValue = fxBibProductionResolveCellValue(
                        $strKey,
                        $intBib,
                        (string)($tabRace['label'] ?? ''),
                        $tabParticipant,
                        $tabAnswers
                    );

                    if ($strKey === 'no_bib' && $strValue !== '' && ctype_digit($strValue)) {
                        fxBibProductionXmlSetNumber(
                            $objDom,
                            $objRow,
                            $strLetter,
                            $intRow,
                            (int)$strValue,
                            $strStyle
                        );
                    } else {
                        fxBibProductionXmlSetInlineString(
                            $objDom,
                            $objRow,
                            $strLetter,
                            $intRow,
                            $strValue,
                            $strStyle
                        );
                    }
                }

                $objSheetData->appendChild($objRow);
                $intRow++;
            }
        }
    }

    $strLastCol = fxBibProductionColumnLetter($intColCount - 1);
    $objDimension = $objXpath->query('//m:dimension')->item(0);
    if ($objDimension instanceof DOMElement) {
        $objDimension->setAttribute('ref', 'A1:' . $strLastCol . max(1, $intRow - 1));
    }
    return $objDom->saveXML();
}

function fxBibProductionBuildOrderXml($strXml, array $tabSnapshot, array $tabLabels) {
    $objDom = fxBibProductionXmlLoad($strXml);
    $objXpath = new DOMXPath($objDom);
    $objXpath->registerNamespace('m', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');

    $objSheetData = $objXpath->query('//m:sheetData')->item(0);
    $objBaseEvent = fxBibProductionXmlFindRow($objXpath, 4);
    $objBaseSecond = fxBibProductionXmlFindRow($objXpath, 5);
    $objBaseBlank = fxBibProductionXmlFindRow($objXpath, 6);
    $objTotal = fxBibProductionXmlFindRow($objXpath, 31);
    if (!$objSheetData || !$objBaseEvent || !$objBaseSecond || !$objTotal) {
        throw new Exception('MSIN-4469 invalid order template');
    }
    if (!$objBaseBlank) {
        $objBaseBlank = $objBaseSecond;
    }

    $tabOrderRows = [];
    foreach ($tabSnapshot['races'] as $tabRace) {
        foreach ($tabRace['ranges'] as $intRangeIndex => $tabRange) {
            $tabOrderRows[] = [
                // MSIN-4470 — Une ligne reste réservée à chaque séquence,
                // mais le nom de l'épreuve n'est répété que sur la première.
                'label'    => $intRangeIndex === 0 ? $tabRace['label'] : '',
                'start'    => (int)$tabRange['start'],
                'finish'   => (int)$tabRange['finish'],
                'quantity' => (int)$tabRange['quantity'],
            ];
        }
    }

    // MSIN-4469 — Le gabarit a 9 emplacements ; on n'en garde que le nombre
    // réel de séquences (sinon lignes jaunes vides avant le TOTAL).
    $intTemplateSlots = 9;
    $intSlotCount = count($tabOrderRows);
    $intRowDelta = ($intSlotCount - $intTemplateSlots) * 3;
    $intTotalRow = 31 + $intRowDelta;

    $tabRemove = [];
    foreach ($objXpath->query('//m:sheetData/m:row') as $objRow) {
        if (!($objRow instanceof DOMElement)) {
            continue;
        }
        $intCurrentRow = (int)$objRow->getAttribute('r');
        if ($intCurrentRow >= 4 && $intCurrentRow <= 30) {
            $tabRemove[] = $objRow;
        }
    }
    foreach ($tabRemove as $objRow) {
        $objSheetData->removeChild($objRow);
    }

    if ($intRowDelta !== 0) {
        foreach ($objXpath->query('//m:sheetData/m:row') as $objRow) {
            if (!($objRow instanceof DOMElement)) {
                continue;
            }
            $intCurrentRow = (int)$objRow->getAttribute('r');
            if ($intCurrentRow < 31) {
                continue;
            }
            $intShiftedRow = $intCurrentRow + $intRowDelta;
            $objRow->setAttribute('r', (string)$intShiftedRow);
            foreach ($objRow->childNodes as $objCell) {
                if ($objCell instanceof DOMElement && $objCell->localName === 'c') {
                    $strColumn = preg_replace('/[0-9]+/', '', $objCell->getAttribute('r'));
                    $objCell->setAttribute('r', $strColumn . $intShiftedRow);
                }
            }
        }
        foreach ($objXpath->query('//m:mergeCell') as $objMerge) {
            if (!($objMerge instanceof DOMElement)) {
                continue;
            }
            $objMerge->setAttribute(
                'ref',
                fxBibProductionXmlShiftReference($objMerge->getAttribute('ref'), 31, $intRowDelta)
            );
        }
    }

    $objTotal = fxBibProductionXmlFindRow($objXpath, $intTotalRow);
    if (!$objTotal) {
        throw new Exception('MSIN-4469 total row missing');
    }

    for ($intSlot = 0; $intSlot < $intSlotCount; $intSlot++) {
        $intRow = 4 + ($intSlot * 3);
        $objEventRow = fxBibProductionXmlCloneRow($objBaseEvent, $intRow);
        $objSecondRow = fxBibProductionXmlCloneRow($objBaseSecond, $intRow + 1);
        $objBlankRow = fxBibProductionXmlCloneRow($objBaseBlank, $intRow + 2);

        $tabRow = $tabOrderRows[$intSlot];
        $objCellF = fxBibProductionXmlFindCell($objEventRow, 'F');
        $strStyleK = $objCellF ? (string)$objCellF->getAttribute('s') : '';
        fxBibProductionXmlSetInlineString($objDom, $objEventRow, 'A', $intRow, $tabRow['label']);
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'F', $intRow, $tabRow['quantity']);
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'G', $intRow, $tabRow['start']);
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'H', $intRow, $tabRow['finish']);
        fxBibProductionXmlSetFormula($objDom, $objEventRow, 'I', $intRow, 'H' . $intRow . '-G' . $intRow . '+1');
        fxBibProductionXmlSetFormula($objDom, $objEventRow, 'J', $intRow, 'I' . $intRow . '=F' . $intRow);
        // MSIN-4469 — À côté de la quantité : lignes Imprimeur pour cette séquence.
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'K', $intRow, $tabRow['quantity'], $strStyleK);
        // MSIN-4469 — V1 : une puce par dossard, avec le même numéro.
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'L', $intRow, $tabRow['quantity']);
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'M', $intRow, $tabRow['start']);
        fxBibProductionXmlSetNumber($objDom, $objEventRow, 'N', $intRow, $tabRow['finish']);
        fxBibProductionXmlSetFormula($objDom, $objEventRow, 'O', $intRow, 'G' . $intRow . '=M' . $intRow);
        fxBibProductionXmlSetFormula($objDom, $objEventRow, 'P', $intRow, 'H' . $intRow . '=N' . $intRow);

        $objSheetData->insertBefore($objEventRow, $objTotal);
        $objSheetData->insertBefore($objSecondRow, $objTotal);
        $objSheetData->insertBefore($objBlankRow, $objTotal);
    }

    $objTitleRow = fxBibProductionXmlFindRow($objXpath, 1);
    if ($objTitleRow) {
        fxBibProductionXmlSetInlineString(
            $objDom,
            $objTitleRow,
            'A',
            1,
            $tabSnapshot['event']['name']
        );
    }

    // MSIN-4469 — Colonne K : validation quantité vs lignes onglet Imprimeur (hors vides).
    $objHeaderRow = fxBibProductionXmlFindRow($objXpath, 3);
    if ($objHeaderRow) {
        $objHeaderCellF = fxBibProductionXmlFindCell($objHeaderRow, 'F');
        $strHeaderStyleK = $objHeaderCellF
            ? (string)$objHeaderCellF->getAttribute('s')
            : '';
        fxBibProductionXmlSetInlineString(
            $objDom,
            $objHeaderRow,
            'K',
            3,
            isset($tabLabels['printer_lines']) ? $tabLabels['printer_lines'] : 'Lignes Imprimeur',
            $strHeaderStyleK
        );
    }

    $intBibTotal = 0;
    foreach ($tabOrderRows as $tabRow) {
        $intBibTotal += (int)$tabRow['quantity'];
    }

    // Reprendre la ligne TOTAL après les insertions (référence DOM à jour).
    $objTotal = fxBibProductionXmlFindRow($objXpath, $intTotalRow);
    if (!$objTotal) {
        throw new Exception('MSIN-4469 total row missing');
    }

    fxBibProductionXmlSetInlineString($objDom, $objTotal, 'A', $intTotalRow, $tabLabels['total']);
    fxBibProductionXmlSetFormula(
        $objDom,
        $objTotal,
        'F',
        $intTotalRow,
        'SUM(F4:F' . ($intTotalRow - 1) . ')',
        '',
        $intBibTotal
    );
    fxBibProductionXmlSetFormula(
        $objDom,
        $objTotal,
        'I',
        $intTotalRow,
        'SUM(I4:I' . ($intTotalRow - 1) . ')',
        '',
        $intBibTotal
    );
    fxBibProductionXmlSetFormula(
        $objDom,
        $objTotal,
        'L',
        $intTotalRow,
        'SUM(L4:L' . ($intTotalRow - 1) . ')',
        '',
        $intBibTotal
    );
    // COUNT ignore l'en-tête texte et les lignes blanches entre épreuves.
    fxBibProductionXmlSetFormula(
        $objDom,
        $objTotal,
        'K',
        $intTotalRow,
        'COUNT(Imprimeur!A:A)',
        '',
        $intBibTotal
    );
    fxBibProductionXmlSetFormula(
        $objDom,
        $objTotal,
        'J',
        $intTotalRow,
        'F' . $intTotalRow . '=K' . $intTotalRow,
        '',
        '1'
    );

    $objDimension = $objXpath->query('//m:dimension')->item(0);
    if ($objDimension instanceof DOMElement) {
        $objDimension->setAttribute('ref', 'A1:Q' . (48 + $intRowDelta));
    }
    return $objDom->saveXML();
}

function fxBibProductionBuildWorkbook(array $tabSnapshot, $strDestination, array $tabLabels) {
    $strTemplate = fxBibProductionTemplatePath();
    if (!is_file($strTemplate)) {
        throw new Exception('MSIN-4469 template missing');
    }
    if (!class_exists('ZipArchive') || !class_exists('DOMDocument')) {
        throw new Exception('MSIN-4469 ZIP/XML extensions missing');
    }
    if (!copy($strTemplate, $strDestination)) {
        throw new Exception('MSIN-4469 template copy failed');
    }

    $objZip = new ZipArchive();
    if ($objZip->open($strDestination) !== true) {
        throw new Exception('MSIN-4469 workbook open failed');
    }

    try {
        $strSupplierXml = $objZip->getFromName('xl/worksheets/sheet1.xml');
        $strOrderXml = $objZip->getFromName('xl/worksheets/sheet2.xml');
        if ($strSupplierXml === false || $strOrderXml === false) {
            throw new Exception('MSIN-4469 worksheet missing');
        }

        $tabSideStyles = fxBibProductionEnsureSideStyles($objZip);
        $strSupplierXml = fxBibProductionBuildSupplierXml(
            $strSupplierXml,
            $tabSnapshot,
            $tabLabels,
            $tabSideStyles
        );
        $strOrderXml = fxBibProductionBuildOrderXml($strOrderXml, $tabSnapshot, $tabLabels);

        $objZip->deleteName('xl/worksheets/sheet1.xml');
        $objZip->addFromString('xl/worksheets/sheet1.xml', $strSupplierXml);
        $objZip->deleteName('xl/worksheets/sheet2.xml');
        $objZip->addFromString('xl/worksheets/sheet2.xml', $strOrderXml);

        $strWorkbookXml = $objZip->getFromName('xl/workbook.xml');
        if ($strWorkbookXml !== false) {
            $objWorkbookDom = fxBibProductionXmlLoad($strWorkbookXml);
            $objWorkbookXpath = new DOMXPath($objWorkbookDom);
            $objWorkbookXpath->registerNamespace('m', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
            $objCalcPr = $objWorkbookXpath->query('//m:calcPr')->item(0);
            if ($objCalcPr instanceof DOMElement) {
                $objCalcPr->setAttribute('fullCalcOnLoad', '1');
                $objCalcPr->setAttribute('forceFullCalc', '1');
            }
            $objZip->deleteName('xl/workbook.xml');
            $objZip->addFromString('xl/workbook.xml', $objWorkbookDom->saveXML());
        }
    } catch (Exception $objException) {
        $objZip->close();
        @unlink($strDestination);
        throw $objException;
    }

    $objZip->close();
}

/**
 * Crée la commande officielle et verrouille immédiatement les séquences via
 * bpo_active_key. Une requête concurrente retrouve la commande déjà active.
 */
function fxBibProductionCreateOfficialOrder($intEveId, $strLangue = 'fr') {
    global $objDatabase;

    $intEveId = (int)$intEveId;
    $tabExisting = fxBibProductionGetActiveOrder($intEveId);
    if ($tabExisting) {
        return ['success' => true, 'order' => $tabExisting, 'existing' => true];
    }

    $tabCollected = fxBibProductionCollectSnapshot($intEveId, $strLangue);
    if (empty($tabCollected['success'])) {
        return $tabCollected;
    }
    $tabSnapshot = $tabCollected['snapshot'];
    $strSnapshot = json_encode($tabSnapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($strSnapshot === false) {
        return ['success' => false, 'message' => fxBibMsg('bib_v5_production_generation_error')];
    }

    $strCreatedBy = fxBibGetCurrentPromoteurDisplayName();
    $intComId = (int)($_SESSION['com_id'] ?? 0);
    $intUsaId = (int)($_SESSION['usa_id'] ?? 0);
    $strEventPart = fxBibProductionSafeFilePart($tabSnapshot['event']['name']);

    $objDatabase->fxQuery('START TRANSACTION');
    $objDatabase->fxGetVar(
        "SELECT eve_id FROM inscriptions_evenements WHERE eve_id = $intEveId FOR UPDATE"
    );

    $tabExisting = fxBibProductionGetActiveOrder($intEveId);
    if ($tabExisting) {
        $objDatabase->fxQuery('COMMIT');
        return ['success' => true, 'order' => $tabExisting, 'existing' => true];
    }

    $intVersion = (int)$objDatabase->fxGetVar(
        "SELECT COALESCE(MAX(bpo_version), 0) + 1
         FROM inscriptions_bib_production_orders
         WHERE eve_id = $intEveId"
    );
    if ($intVersion <= 0) {
        $intVersion = 1;
    }

    $strFileName = 'commande-dossards-' . $strEventPart . '-v' . $intVersion . '.xlsx';
    $strInsert = "
        INSERT INTO inscriptions_bib_production_orders (
            eve_id,
            bpo_com_id,
            bpo_usa_id,
            bpo_status,
            bpo_active_key,
            bpo_version,
            bpo_file_name,
            bpo_file_path,
            bpo_sha256,
            bpo_snapshot,
            bpo_created_at,
            bpo_created_by
        ) VALUES (
            $intEveId,
            $intComId,
            $intUsaId,
            'generating',
            1,
            $intVersion,
            '" . $objDatabase->fxEscape($strFileName) . "',
            '',
            '',
            '" . $objDatabase->fxEscape($strSnapshot) . "',
            NOW(),
            '" . $objDatabase->fxEscape($strCreatedBy) . "'
        )
    ";
    $qryInsert = $objDatabase->fxQuery($strInsert);
    $intOrderId = $qryInsert ? (int)$objDatabase->fxGetLastId() : 0;
    if ($intOrderId <= 0) {
        $objDatabase->fxQuery('ROLLBACK');
        $tabExisting = fxBibProductionGetActiveOrder($intEveId);
        if ($tabExisting) {
            return ['success' => true, 'order' => $tabExisting, 'existing' => true];
        }
        return ['success' => false, 'message' => fxBibMsg('bib_v5_production_generation_error')];
    }

    foreach ($tabSnapshot['races'] as $tabRace) {
        foreach ($tabRace['ranges'] as $tabRange) {
            $qryRange = $objDatabase->fxQuery(
                "INSERT INTO inscriptions_bib_production_ranges (
                    bpo_id,
                    eve_id,
                    epr_id,
                    epr_bib_id,
                    bpr_epr_label,
                    bpr_start,
                    bpr_finish,
                    bpr_quantity
                ) VALUES (
                    $intOrderId,
                    $intEveId,
                    " . (int)$tabRace['epr_id'] . ",
                    " . (int)$tabRange['epr_bib_id'] . ",
                    '" . $objDatabase->fxEscape($tabRace['label']) . "',
                    " . (int)$tabRange['start'] . ",
                    " . (int)$tabRange['finish'] . ",
                    " . (int)$tabRange['quantity'] . "
                )"
            );
            if ($qryRange === false) {
                $objDatabase->fxQuery('ROLLBACK');
                return ['success' => false, 'message' => fxBibMsg('bib_v5_production_generation_error')];
            }
        }
    }
    $objDatabase->fxQuery('COMMIT');

    $strRelativeDir = 'eve-' . $intEveId . DIRECTORY_SEPARATOR . 'order-' . $intOrderId;
    $strAbsoluteDir = fxBibProductionStorageRoot() . DIRECTORY_SEPARATOR . $strRelativeDir;
    $strAbsoluteFile = $strAbsoluteDir . DIRECTORY_SEPARATOR . $strFileName;

    try {
        if (!fxBibProductionEnsureDirectory($strAbsoluteDir)) {
            throw new Exception('MSIN-4469 storage unavailable');
        }

        $strStorageRoot = fxBibProductionStorageRoot();
        if (fxBibProductionEnsureDirectory($strStorageRoot)) {
            $strDenyFile = $strStorageRoot . DIRECTORY_SEPARATOR . '.htaccess';
            if (!is_file($strDenyFile)) {
                @file_put_contents($strDenyFile, "Deny from all\n");
            }
        }

        fxBibProductionBuildWorkbook(
            $tabSnapshot,
            $strAbsoluteFile,
            [
                'bib'           => fxBibTexte('bib_v5_production_col_bib', 0),
                'race'          => fxBibTexte('bib_v5_production_col_race', 0),
                'total'         => fxBibTexte('bib_v5_production_total', 0),
                'title'         => fxBibTexte('bib_v5_production_excel_title', 0),
                'printer_lines' => fxBibTexte('bib_v5_production_col_printer_lines', 0),
            ]
        );

        if (!is_file($strAbsoluteFile)) {
            throw new Exception('MSIN-4469 workbook not written');
        }

        $strRelativeFile = str_replace('\\', '/', $strRelativeDir . DIRECTORY_SEPARATOR . $strFileName);
        $strSha256 = hash_file('sha256', $strAbsoluteFile);
        $objDatabase->fxQuery(
            "UPDATE inscriptions_bib_production_orders
             SET bpo_status = 'ready',
                 bpo_file_path = '" . $objDatabase->fxEscape($strRelativeFile) . "',
                 bpo_sha256 = '" . $objDatabase->fxEscape($strSha256) . "'
             WHERE bpo_id = $intOrderId
               AND bpo_active_key = 1"
        );
    } catch (Exception $objException) {
        if (is_file($strAbsoluteFile)) {
            @unlink($strAbsoluteFile);
        }
        $objDatabase->fxQuery(
            "UPDATE inscriptions_bib_production_orders
             SET bpo_status = 'failed',
                 bpo_active_key = NULL,
                 bpo_error = '" . $objDatabase->fxEscape($objException->getMessage()) . "'
             WHERE bpo_id = $intOrderId"
        );
        return ['success' => false, 'message' => fxBibMsg('bib_v5_production_generation_error')];
    }

    return [
        'success' => true,
        'order' => fxBibProductionGetOrder($intOrderId, $intEveId),
        'existing' => false,
    ];
}

function fxBibProductionOrderAbsolutePath(array $tabOrder) {
    $strRelative = str_replace('\\', '/', (string)($tabOrder['bpo_file_path'] ?? ''));
    if ($strRelative === '' || strpos($strRelative, '..') !== false || strpos($strRelative, "\0") !== false) {
        return '';
    }

    return fxBibProductionStorageRoot() . DIRECTORY_SEPARATOR
        . str_replace('/', DIRECTORY_SEPARATOR, $strRelative);
}

function fxBibProductionStreamOrder(array $tabOrder) {
    if (($tabOrder['bpo_status'] ?? '') !== 'ready') {
        return false;
    }

    $strFile = fxBibProductionOrderAbsolutePath($tabOrder);
    if ($strFile === '' || !is_file($strFile)) {
        return false;
    }

    while (ob_get_level() > 0) {
        ob_end_clean();
    }

    $strDownloadName = basename((string)$tabOrder['bpo_file_name']);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . str_replace('"', '', $strDownloadName) . '"');
    header('Content-Length: ' . filesize($strFile));
    header('Cache-Control: private, no-store, max-age=0');
    header('X-Content-Type-Options: nosniff');
    readfile($strFile);
    return true;
}