Quand les métadonnées SST d'AgenticX5 optimisent votre Supply Chain

// Corrélation entre type de lésion et EPI préventif
MATCH (l:LesionCNESST)
WHERE l.annee >= 2020
WITH l.siege_lesion AS partie_corps,
     l.genre_accident AS type_accident,
     count(*) AS nb_cas,
     sum(l.cout_total) AS cout_total

MATCH (epi:EPI)
WHERE epi.zone_protection = partie_corps

WITH partie_corps, type_accident, nb_cas, cout_total, epi,
     round(cout_total / nb_cas, 2) AS cout_moyen_lesion

RETURN partie_corps,
       type_accident,
       nb_cas,
       format(cout_total, '$#,###') AS cout_total_format,
       epi.type AS epi_recommande,
       epi.cout_unitaire AS cout_epi,
       round(cout_moyen_lesion / epi.cout_unitaire, 1) AS roi_potentiel
ORDER BY nb_cas DESC

// Analyse saisonnalité + prévision stock EPI
MATCH (l:LesionCNESST)
WHERE l.secteur IN ['Construction', 'Extraction minière']
WITH l.mois AS mois,
     l.genre_accident AS type_accident,
     count(*) AS nb_incidents,
     collect(DISTINCT l.siege_lesion) AS zones_touchees

WITH mois, type_accident, nb_incidents, zones_touchees,
     avg(nb_incidents) OVER () AS moyenne_annuelle

WITH *, 
     round((nb_incidents - moyenne_annuelle) / moyenne_annuelle * 100, 1) AS variation_pct

MATCH (epi:EPI)
WHERE ANY(zone IN zones_touchees WHERE zone = epi.zone_protection)

RETURN mois,
       type_accident,
       nb_incidents,
       variation_pct AS var_vs_moyenne,
       CASE 
         WHEN variation_pct > 20 THEN '⬆️ HAUTE SAISON'
         WHEN variation_pct < -20 THEN '⬇️ BASSE SAISON'
         ELSE '➡️ NORMAL'
       END AS periode,
       collect(DISTINCT epi.type) AS epi_a_stocker
ORDER BY mois

// Traçabilité: Incident → Pièce → Lot → Fournisseur
MATCH path = (i:Incident)-[:IMPLIQUE_PIECE]->(p:Piece)
              -[:PROVIENT_LOT]->(lot:Lot)
              -[:FOURNI_PAR]->(f:Fournisseur)
WHERE i.date > date() - duration({months: 6})
  AND i.type IN ['Near-Miss', 'Incident', 'Accident']

WITH f, lot, p,
     count(i) AS nb_incidents,
     collect(i.description) AS descriptions

WITH f,
     sum(nb_incidents) AS total_incidents,
     collect({lot: lot.numero, piece: p.type, incidents: nb_incidents}) AS details

RETURN f.nom AS fournisseur,
       f.score_qualite AS score_actuel,
       total_incidents,
       CASE
         WHEN total_incidents > 10 THEN '🔴 BLOCAGE'
         WHEN total_incidents > 5 THEN '🟠 SURVEILLANCE'
         ELSE '🟢 OK'
       END AS statut,
       details[0..3] AS problemes_recents
ORDER BY total_incidents DESC