<?php
/**
 * MSIN-4464 — Traçabilité fiches (config whitelist + rétention + writers).
 * UI fiche (« ? ») + permission promoteur : phase suivante.
 */

/**
 * Même allowlist que les kits ($arrEveAccesKitEditors dans inc_settings.php).
 */
if (!function_exists('fxAdminCanEditFicheAudit')) {
    function fxAdminCanEditFicheAudit()
    {
        return function_exists('fxAdminCanEditEveAccesKits') && fxAdminCanEditEveAccesKits();
    }
}

/** Tables MSIN-4464 présentes ? */
function fxFicheAuditTablesReady()
{
    global $objDatabase;
    static $blnReady = null;

    if ($blnReady !== null) {
        return $blnReady;
    }

    if (!isset($objDatabase)) {
        $blnReady = false;
        return $blnReady;
    }

    $tab = $objDatabase->fxGetResults("SHOW TABLES LIKE 'inscriptions_fiche_audit_settings'");
    $blnReady = ($tab != null && count($tab) > 0);

    return $blnReady;
}

/**
 * Catalogue connu (ordre d'affichage admin). Les clés absentes en BD sont
 * proposées à l'écran et créées au premier enregistrement.
 *
 * @return array[]
 */
function fxFicheAuditFieldCatalog()
{
    return array(
        array(
            'field_key' => 'par_statut_course',
            'field_label_fr' => 'Statut de course',
            'field_label_en' => 'Race status',
            'field_tri' => 10,
        ),
        array(
            'field_key' => 'no_bib',
            'field_label_fr' => 'Numéro de dossard',
            'field_label_en' => 'Bib number',
            'field_tri' => 20,
        ),
        array(
            'field_key' => 'no_bib_remis',
            'field_label_fr' => 'Dossard remis',
            'field_label_en' => 'Bib handed out',
            'field_tri' => 30,
        ),
        array(
            'field_key' => 'is_cancelled',
            'field_label_fr' => 'Annulation',
            'field_label_en' => 'Cancellation',
            'field_tri' => 40,
        ),
    );
}

/**
 * @return array{retention_days:int,retention_max_per_field:int,track_last_touch:int}
 */
function fxFicheAuditGetSettings()
{
    global $objDatabase;

    $arrDefault = array(
        'retention_days' => 90,
        'retention_max_per_field' => 10,
        'track_last_touch' => 1,
    );

    if (!isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return $arrDefault;
    }

    $sql = 'SELECT retention_days, retention_max_per_field, track_last_touch'
        . ' FROM inscriptions_fiche_audit_settings WHERE setting_id = 1 LIMIT 1';
    $row = $objDatabase->fxGetRow($sql);
    if ($row === null || !is_array($row)) {
        return $arrDefault;
    }

    return array(
        'retention_days' => max(1, intval($row['retention_days'])),
        'retention_max_per_field' => max(1, intval($row['retention_max_per_field'])),
        'track_last_touch' => (intval($row['track_last_touch']) === 1) ? 1 : 0,
    );
}

/**
 * Champs whitelist fusionnés avec le catalogue (actif = 0 si absent en BD).
 *
 * @return array[]
 */
function fxFicheAuditGetFieldsForAdmin()
{
    global $objDatabase;

    $arrByKey = array();
    if (isset($objDatabase) && fxFicheAuditTablesReady()) {
        $sql = 'SELECT field_key, field_label_fr, field_label_en, field_actif, field_tri'
            . ' FROM inscriptions_fiche_audit_fields ORDER BY field_tri, field_key';
        $arrRows = $objDatabase->fxGetResults($sql);
        if (is_array($arrRows)) {
            foreach ($arrRows as $row) {
                if (!is_array($row) || empty($row['field_key'])) {
                    continue;
                }
                $arrByKey[$row['field_key']] = array(
                    'field_key' => (string)$row['field_key'],
                    'field_label_fr' => (string)$row['field_label_fr'],
                    'field_label_en' => (string)$row['field_label_en'],
                    'field_actif' => (intval($row['field_actif']) === 1) ? 1 : 0,
                    'field_tri' => intval($row['field_tri']),
                );
            }
        }
    }

    $arrOut = array();
    foreach (fxFicheAuditFieldCatalog() as $arrCat) {
        $strKey = $arrCat['field_key'];
        if (isset($arrByKey[$strKey])) {
            $arrOut[] = $arrByKey[$strKey];
            unset($arrByKey[$strKey]);
        } else {
            $arrOut[] = array(
                'field_key' => $strKey,
                'field_label_fr' => $arrCat['field_label_fr'],
                'field_label_en' => $arrCat['field_label_en'],
                'field_actif' => 0,
                'field_tri' => intval($arrCat['field_tri']),
            );
        }
    }

    // Champs éventuels hors catalogue (déjà en BD)
    foreach ($arrByKey as $row) {
        $arrOut[] = $row;
    }

    return $arrOut;
}

/**
 * Clés actives (pour usage futur des writers).
 *
 * @return string[]
 */
function fxFicheAuditGetActiveFieldKeys()
{
    global $objDatabase;
    static $arrCache = null;

    if ($arrCache !== null) {
        return $arrCache;
    }

    $arrCache = array();
    if (!isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return $arrCache;
    }

    $sql = 'SELECT field_key FROM inscriptions_fiche_audit_fields'
        . ' WHERE field_actif = 1 ORDER BY field_tri, field_key';
    $arrRows = $objDatabase->fxGetResults($sql);
    if (is_array($arrRows)) {
        foreach ($arrRows as $row) {
            if (!empty($row['field_key'])) {
                $arrCache[] = (string)$row['field_key'];
            }
        }
    }

    return $arrCache;
}

/** @return bool */
function fxFicheAuditIsFieldActive($strFieldKey)
{
    $strFieldKey = (string)$strFieldKey;
    if ($strFieldKey === '') {
        return false;
    }

    return in_array($strFieldKey, fxFicheAuditGetActiveFieldKeys(), true);
}

/**
 * Acteur depuis la session (promoteur ou superadm lié).
 *
 * @return array{com_id:int,label:string}
 */
function fxFicheAuditResolveActor()
{
    $intComId = intval($_SESSION['com_info']['com_id'] ?? ($_SESSION['com_id'] ?? 0));
    $strLabel = trim(
        (string)($_SESSION['com_info']['com_prenom'] ?? '')
        . ' '
        . (string)($_SESSION['com_info']['com_nom'] ?? '')
    );

    if ($strLabel === '' && $intComId > 0) {
        $strLabel = 'compte#' . $intComId;
    }

    if ($intComId <= 0 && !empty($_SESSION['usa_id'])) {
        $intUsaId = intval($_SESSION['usa_id']);
        $intComId = intval($_SESSION['usa_info']['com_id'] ?? 0);
        $strLabel = trim(
            (string)($_SESSION['usa_info']['com_prenom'] ?? '')
            . ' '
            . (string)($_SESSION['usa_info']['com_nom'] ?? '')
        );
        if ($strLabel === '') {
            $strLabel = 'admin#' . $intUsaId;
        }
    }

    return array(
        'com_id' => $intComId,
        'label' => $strLabel,
    );
}

/**
 * MSIN-4464 — Point unique d'écriture audit après un UPDATE réussi.
 * - Si champ whitelisté et valeur changée → ligne détail
 * - Si track_last_touch → ligne last_touch (même hors whitelist)
 *
 * @param int $intParId
 * @param string $strFieldKey
 * @param string|null $strOld
 * @param string|null $strNew
 * @param string $strSource
 * @param int $intEveId
 */
function fxFicheAuditRecordChange(
    $intParId,
    $strFieldKey,
    $strOld,
    $strNew,
    $strSource = '',
    $intEveId = 0
) {
    global $objDatabase;

    $intParId = intval($intParId);
    $strFieldKey = preg_replace('/[^a-z0-9_]/i', '', (string)$strFieldKey);
    if ($intParId <= 0 || $strFieldKey === '' || !isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return;
    }

    $strOldNorm = ($strOld === null) ? '' : trim((string)$strOld);
    $strNewNorm = ($strNew === null) ? '' : trim((string)$strNew);
    if ($strOldNorm === $strNewNorm) {
        return;
    }

    $arrSettings = fxFicheAuditGetSettings();
    $blnDetail = fxFicheAuditIsFieldActive($strFieldKey);
    $blnLastTouch = (intval($arrSettings['track_last_touch']) === 1);

    if (!$blnDetail && !$blnLastTouch) {
        return;
    }

    if ($intEveId <= 0) {
        $intEveId = intval($objDatabase->fxGetVar(
            'SELECT eve_id FROM resultats_participants WHERE par_id = ' . $intParId . ' LIMIT 1'
        ));
    }

    $arrActor = fxFicheAuditResolveActor();

    if ($blnDetail) {
        fxFicheAuditInsertLogRow(
            $intParId,
            $intEveId,
            $strFieldKey,
            $strOldNorm,
            $strNewNorm,
            $arrActor,
            $strSource
        );
        fxFicheAuditTrimField($intParId, $strFieldKey, $arrSettings);
    }

    if ($blnLastTouch) {
        fxFicheAuditInsertLogRow(
            $intParId,
            $intEveId,
            'last_touch',
            '',
            $strFieldKey,
            $arrActor,
            $strSource
        );
        fxFicheAuditTrimField($intParId, 'last_touch', $arrSettings);
    }
}

/**
 * MSIN-4464 — Dernière intervention seule (édition fiche, etc.), sans old→new.
 */
function fxFicheAuditRecordLastTouchOnly($intParId, $strTriggerField, $strSource = '', $intEveId = 0)
{
    global $objDatabase;

    $intParId = intval($intParId);
    $strTriggerField = preg_replace('/[^a-z0-9_]/i', '', (string)$strTriggerField);
    if ($strTriggerField === '') {
        $strTriggerField = 'fiche';
    }
    if ($intParId <= 0 || !isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return;
    }

    $arrSettings = fxFicheAuditGetSettings();
    if (intval($arrSettings['track_last_touch']) !== 1) {
        return;
    }

    if ($intEveId <= 0) {
        $intEveId = intval($objDatabase->fxGetVar(
            'SELECT eve_id FROM resultats_participants WHERE par_id = ' . $intParId . ' LIMIT 1'
        ));
    }

    $arrActor = fxFicheAuditResolveActor();
    fxFicheAuditInsertLogRow(
        $intParId,
        $intEveId,
        'last_touch',
        '',
        $strTriggerField,
        $arrActor,
        $strSource
    );
    fxFicheAuditTrimField($intParId, 'last_touch', $arrSettings);
}

/**
 * Après annulation / rétablissement sur une commande (tous les par_id du pec).
 */
function fxFicheAuditRecordCancelRestoreForPec($intPecId, $strOld, $strNew, $strSource, $intEveId = 0)
{
    global $objDatabase;

    $intPecId = intval($intPecId);
    if ($intPecId <= 0 || !isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return;
    }

    $sql = 'SELECT par_id, eve_id FROM resultats_participants WHERE pec_id = ' . $intPecId;
    $arrRows = $objDatabase->fxGetResults($sql);
    if (!is_array($arrRows)) {
        return;
    }

    foreach ($arrRows as $row) {
        if (!is_array($row) || empty($row['par_id'])) {
            continue;
        }
        $intEveRow = intval($row['eve_id'] ?? 0);
        if ($intEveRow <= 0) {
            $intEveRow = intval($intEveId);
        }
        fxFicheAuditRecordChange(
            intval($row['par_id']),
            'is_cancelled',
            $strOld,
            $strNew,
            $strSource,
            $intEveRow
        );
    }
}

/**
 * Événements pour le sélecteur admin (actifs récents + ceux déjà dans les logs).
 *
 * @return array[]
 */
function fxFicheAuditAdminListEvents()
{
    global $objDatabase;

    if (!isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return array();
    }

    // Pas de LIMIT : le select admin a une recherche (bootstrap-select).
    $sql = 'SELECT e.eve_id, e.eve_nom_fr, e.eve_date_debut,'
        . ' (SELECT COUNT(*) FROM inscriptions_fiche_audit_log l WHERE l.eve_id = e.eve_id) AS fal_nb'
        . ' FROM inscriptions_evenements e'
        . ' WHERE e.eve_actif = 1'
        . ' OR EXISTS (SELECT 1 FROM inscriptions_fiche_audit_log l2 WHERE l2.eve_id = e.eve_id)'
        . ' ORDER BY'
        . ' CASE WHEN EXISTS (SELECT 1 FROM inscriptions_fiche_audit_log lx WHERE lx.eve_id = e.eve_id) THEN 0 ELSE 1 END,'
        . ' e.eve_date_debut DESC, e.eve_id DESC';
    $arrRows = $objDatabase->fxGetResults($sql);

    return is_array($arrRows) ? $arrRows : array();
}

/**
 * Champs (hors last_touch) qui ont au moins 1 log pour ce participant.
 *
 * @return array<string,true>
 */
function fxFicheAuditFieldsWithLogsForPar($intParId)
{
    global $objDatabase;

    $intParId = intval($intParId);
    $arrOut = array();
    if ($intParId <= 0 || !isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return $arrOut;
    }

    $sql = 'SELECT field_key FROM inscriptions_fiche_audit_log'
        . ' WHERE par_id = ' . $intParId
        . " AND field_key <> 'last_touch'"
        . ' GROUP BY field_key';
    $arrRows = $objDatabase->fxGetResults($sql);
    if (is_array($arrRows)) {
        foreach ($arrRows as $row) {
            if (!empty($row['field_key'])) {
                $arrOut[(string)$row['field_key']] = true;
            }
        }
    }

    return $arrOut;
}

/**
 * Historique détail d'un champ (fiche / AJAX), limité par la config.
 *
 * @return array[]
 */
function fxFicheAuditGetRecentForField($intParId, $strFieldKey, $intLimit = 0)
{
    global $objDatabase;

    $intParId = intval($intParId);
    $strFieldKey = preg_replace('/[^a-z0-9_]/i', '', (string)$strFieldKey);
    if ($intParId <= 0 || $strFieldKey === '' || $strFieldKey === 'last_touch'
        || !isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return array();
    }

    if ($intLimit <= 0) {
        $arrSettings = fxFicheAuditGetSettings();
        $intLimit = max(1, intval($arrSettings['retention_max_per_field']));
    }
    $intLimit = min(100, max(1, intval($intLimit)));

    $sql = 'SELECT fal_id, field_key, old_value, new_value, changed_by_label, source, created_at'
        . ' FROM inscriptions_fiche_audit_log'
        . ' WHERE par_id = ' . $intParId
        . " AND field_key = '" . $objDatabase->fxEscape($strFieldKey) . "'"
        . ' ORDER BY created_at DESC, fal_id DESC'
        . ' LIMIT ' . $intLimit;
    $arrRows = $objDatabase->fxGetResults($sql);

    return is_array($arrRows) ? $arrRows : array();
}

/**
 * Derniers logs d'un événement (contrôle admin).
 *
 * @return array[]
 */
function fxFicheAuditAdminListRecentByEve($intEveId, $intLimit = 100)
{
    global $objDatabase;

    $intEveId = intval($intEveId);
    $intLimit = intval($intLimit);
    if ($intLimit !== 200) {
        $intLimit = 100;
    }
    if ($intEveId <= 0 || !isset($objDatabase) || !fxFicheAuditTablesReady()) {
        return array();
    }

    $sql = 'SELECT l.fal_id, l.par_id, l.eve_id, l.field_key, l.old_value, l.new_value,'
        . ' l.changed_by_com_id, l.changed_by_label, l.source, l.created_at,'
        . ' p.par_prenom, p.par_nom, p.no_bib'
        . ' FROM inscriptions_fiche_audit_log l'
        . ' LEFT JOIN resultats_participants p ON p.par_id = l.par_id'
        . ' WHERE l.eve_id = ' . $intEveId
        . ' ORDER BY l.created_at DESC, l.fal_id DESC'
        . ' LIMIT ' . $intLimit;
    $arrRows = $objDatabase->fxGetResults($sql);

    return is_array($arrRows) ? $arrRows : array();
}

/**
 * @param array{com_id:int,label:string} $arrActor
 */
function fxFicheAuditInsertLogRow(
    $intParId,
    $intEveId,
    $strFieldKey,
    $strOld,
    $strNew,
    $arrActor,
    $strSource
) {
    global $objDatabase;

    $strSource = preg_replace('/[^a-z0-9_\-]/i', '', (string)$strSource);
    if (strlen($strSource) > 32) {
        $strSource = substr($strSource, 0, 32);
    }

    $sql = 'INSERT INTO inscriptions_fiche_audit_log'
        . ' (par_id, eve_id, field_key, old_value, new_value,'
        . ' changed_by_com_id, changed_by_label, source, created_at)'
        . ' VALUES ('
        . intval($intParId) . ', '
        . (intval($intEveId) > 0 ? intval($intEveId) : 'NULL') . ', '
        . "'" . $objDatabase->fxEscape($strFieldKey) . "', "
        . "'" . $objDatabase->fxEscape($strOld) . "', "
        . "'" . $objDatabase->fxEscape($strNew) . "', "
        . (intval($arrActor['com_id']) > 0 ? intval($arrActor['com_id']) : 'NULL') . ', '
        . "'" . $objDatabase->fxEscape((string)$arrActor['label']) . "', "
        . ($strSource !== '' ? "'" . $objDatabase->fxEscape($strSource) . "'" : 'NULL') . ', '
        . 'NOW()'
        . ')';
    $objDatabase->fxQuery($sql);
}

/**
 * Garde les N dernières entrées + purge par âge pour par_id × field_key.
 *
 * @param array{retention_days:int,retention_max_per_field:int,track_last_touch:int} $arrSettings
 */
function fxFicheAuditTrimField($intParId, $strFieldKey, $arrSettings)
{
    global $objDatabase;

    $intParId = intval($intParId);
    $strFieldKey = (string)$strFieldKey;
    if ($intParId <= 0 || $strFieldKey === '' || !isset($objDatabase)) {
        return;
    }

    $intDays = max(1, intval($arrSettings['retention_days']));
    $intMax = max(1, intval($arrSettings['retention_max_per_field']));
    $strEscKey = $objDatabase->fxEscape($strFieldKey);

    $sqlAge = 'DELETE FROM inscriptions_fiche_audit_log'
        . ' WHERE par_id = ' . $intParId
        . " AND field_key = '" . $strEscKey . "'"
        . ' AND created_at < DATE_SUB(NOW(), INTERVAL ' . $intDays . ' DAY)';
    $objDatabase->fxQuery($sqlAge);

    // MySQL : sous-requête dérivée obligatoire pour DELETE + LIMIT même table.
    $sqlMax = 'DELETE FROM inscriptions_fiche_audit_log'
        . ' WHERE par_id = ' . $intParId
        . " AND field_key = '" . $strEscKey . "'"
        . ' AND fal_id NOT IN ('
        . ' SELECT fal_id FROM ('
        . '   SELECT fal_id FROM inscriptions_fiche_audit_log'
        . '   WHERE par_id = ' . $intParId
        . "   AND field_key = '" . $strEscKey . "'"
        . '   ORDER BY created_at DESC, fal_id DESC'
        . '   LIMIT ' . $intMax
        . ' ) AS keep_rows'
        . ' )';
    $objDatabase->fxQuery($sqlMax);
}

/**
 * @param array $arrPost
 * @return array{state:string,message:string}
 */
function fxFicheAuditSaveConfig($arrPost)
{
    global $objDatabase;

    if (!isset($objDatabase)) {
        return array('state' => 'error', 'message' => 'Base de données indisponible.');
    }
    if (!fxFicheAuditTablesReady()) {
        return array(
            'state' => 'error',
            'message' => 'Tables absentes : exécuter sql/MSIN-4464-fiche-audit-config.sql',
        );
    }

    $intDays = isset($arrPost['retention_days']) ? intval($arrPost['retention_days']) : 90;
    $intMax = isset($arrPost['retention_max_per_field']) ? intval($arrPost['retention_max_per_field']) : 10;
    $intLastTouch = !empty($arrPost['track_last_touch']) ? 1 : 0;

    if ($intDays < 1 || $intDays > 3650) {
        return array('state' => 'error', 'message' => 'Rétention (jours) : entre 1 et 3650.');
    }
    if ($intMax < 1 || $intMax > 100) {
        return array('state' => 'error', 'message' => 'Max par champ : entre 1 et 100.');
    }

    $arrActif = array();
    if (!empty($arrPost['field_actif']) && is_array($arrPost['field_actif'])) {
        foreach ($arrPost['field_actif'] as $strKey) {
            $strKey = preg_replace('/[^a-z0-9_]/i', '', (string)$strKey);
            if ($strKey !== '') {
                $arrActif[$strKey] = true;
            }
        }
    }

    $sqlSettings = 'INSERT INTO inscriptions_fiche_audit_settings'
        . ' (setting_id, retention_days, retention_max_per_field, track_last_touch)'
        . ' VALUES (1, ' . $intDays . ', ' . $intMax . ', ' . $intLastTouch . ')'
        . ' ON DUPLICATE KEY UPDATE'
        . ' retention_days = VALUES(retention_days),'
        . ' retention_max_per_field = VALUES(retention_max_per_field),'
        . ' track_last_touch = VALUES(track_last_touch)';
    if (!$objDatabase->fxQuery($sqlSettings)) {
        return array('state' => 'error', 'message' => 'Erreur à l\'enregistrement des réglages.');
    }

    foreach (fxFicheAuditGetFieldsForAdmin() as $arrField) {
        $strKey = $arrField['field_key'];
        $intActif = isset($arrActif[$strKey]) ? 1 : 0;
        $strLabelFr = $objDatabase->fxEscape($arrField['field_label_fr']);
        $strLabelEn = $objDatabase->fxEscape($arrField['field_label_en']);
        $intTri = intval($arrField['field_tri']);

        $sqlField = 'INSERT INTO inscriptions_fiche_audit_fields'
            . ' (field_key, field_label_fr, field_label_en, field_actif, field_tri)'
            . " VALUES ('" . $objDatabase->fxEscape($strKey) . "', '" . $strLabelFr . "', '"
            . $strLabelEn . "', " . $intActif . ', ' . $intTri . ')'
            . ' ON DUPLICATE KEY UPDATE'
            . ' field_label_fr = VALUES(field_label_fr),'
            . ' field_label_en = VALUES(field_label_en),'
            . ' field_actif = VALUES(field_actif),'
            . ' field_tri = VALUES(field_tri)';
        if (!$objDatabase->fxQuery($sqlField)) {
            return array('state' => 'error', 'message' => 'Erreur à l\'enregistrement du champ ' . $strKey . '.');
        }
    }

    return array('state' => 'ok', 'message' => 'Configuration enregistrée.');
}