☣️ Produits Chimiques / SDS

// 🚨 REQUÊTE #1 - Stockage Incompatible / Incompatible Storage

MATCH (a:Chemical)-[:STORED_IN]->(z1:Zone)-[:PART_OF]->(p:Project{id:$projectId}),
      (b:Chemical)-[:STORED_IN]->(z2:Zone)

// ☣️ Groupes incompatibles (NFPA 704, ISO 14001)
WHERE a.group IN ['Acid', 'Oxidizer']
  AND b.group IN ['Base', 'Fuel']
  
  // 📏 Distance géospatiale < seuil minimum de séparation
  AND distance(point(z1), point(z2)) < $minSeparationM

RETURN 
  a.name AS chemical_A,
  a.group AS group_A,
  b.name AS chemical_B,
  b.group AS group_B,
  z1.name AS zone_A,
  z2.name AS zone_B,
  round(distance(point(z1), point(z2)), 2) AS distance_m,
  'CRITIQUE' AS severity

ORDER BY distance_m ASC

// ⏰ REQUÊTE #2 - Inventaire & Dates d'Expiration / Inventory & Expiration

MATCH (chem:Chemical)-[:PART_OF]->(p:Project{id:$projectId})

// 📅 Filtre: expirés OU expiration dans 30 jours
WHERE chem.expiryDate IS NOT NULL
  AND (
    chem.expiryDate < date()  // Déjà expirés
    OR chem.expiryDate < date() + duration({days: $warningDays})  // Expire bientôt
  )

// 🧮 Calcul jours restants
WITH chem,
     duration.inDays(chem.expiryDate, date()).days AS daysRemaining

RETURN
  chem.name AS chemical_name,
  chem.casNumber AS cas_number,
  chem.quantity AS quantity_L,
  toString(chem.expiryDate) AS expiry_date,
  daysRemaining,
  CASE
    WHEN daysRemaining < 0 THEN 'EXPIRÉ'
    WHEN daysRemaining <= 7 THEN 'CRITIQUE'
    WHEN daysRemaining <= 30 THEN 'AVERTISSEMENT'
    ELSE 'OK'
  END AS status

ORDER BY daysRemaining ASC

// 💨 REQUÊTE #3 - Dépassement VLE / OEL Exceedance

MATCH (r:Reading)-[:AT]->(z:Zone)-[:PART_OF]->(p:Project{id:$projectId})

// 🧪 Types de gaz surveillés
WHERE r.type IN ['VOC', 'H2S', 'CO']
  
  // ⚠️ Valeur mesurée > Valeur Limite d'Exposition (OEL)
  AND r.value > r.oel
  
  // ⏱️ Dernières 24h (optionnel)
  AND r.timestamp > datetime() - duration({hours: 24})

RETURN
  z.name AS zone,
  r.type AS gas_type,
  max(r.value) AS peak_concentration_ppm,
  r.oel AS oel_limit_ppm,
  round(max(r.value) / r.oel * 100, 1) AS percentage_of_oel,
  count(r) AS exceedance_count,
  max(r.timestamp) AS last_exceedance

ORDER BY percentage_of_oel DESC

// ⏳ REQUÊTE #4 - Contenants Ouverts > Délai / Containers Open Too Long

MATCH (cont:Container)-[:CONTAINS]->(chem:Chemical)-[:PART_OF]->(p:Project{id:$projectId})

// 📦 Filtre: contenants actuellement ouverts
WHERE cont.openedAt IS NOT NULL
  
  // ⏰ Ouvert depuis plus de X heures (paramétrable)
  AND cont.openedAt + duration({hours: $maxOpenHours}) < datetime()

// 🧮 Calcul durée d'ouverture
WITH cont, chem,
     duration.inHours(cont.openedAt, datetime()).hours AS hoursOpen

RETURN
  cont.id AS container_id,
  chem.name AS chemical_name,
  chem.group AS hazard_group,
  toString(cont.openedAt) AS opened_at,
  round(hoursOpen, 1) AS hours_open,
  CASE
    WHEN hoursOpen > 48 THEN 'ÉLEVÉ'
    WHEN hoursOpen > 24 THEN 'MOYEN'
    ELSE 'FAIBLE'
  END AS risk_level

ORDER BY hours_open DESC

// 🧰 REQUÊTE #5 - Kit Déversement Trop Loin / Spill Kit Too Far

MATCH (z:Zone)-[:PART_OF]->(p:Project{id:$projectId})

// ☣️ Zones avec stockage de produits chimiques
MATCH (chem:Chemical)-[:STORED_IN]->(z)

// 🧰 Calcul distance au kit de déversement le plus proche
WITH z, count(chem) AS chemicalCount,
     coalesce(
       min([
         d IN [
           (kit:Asset{class:'SpillKit'})-[:LOCATED_IN]->(z2:Zone) |
           distance(point(z), point(z2))
         ] | d
       ]),
       1e9  // Valeur infinie si aucun kit trouvé
     ) AS nearestKitDistance

// ⚠️ Filtre: distance > seuil maximum autorisé
WHERE nearestKitDistance > $maxDistanceM

RETURN DISTINCT
  z.name AS zone_name,
  chemicalCount,
  round(nearestKitDistance, 2) AS nearest_kit_distance_m,
  CASE
    WHEN nearestKitDistance > 50 THEN 'CRITIQUE'
    WHEN nearestKitDistance > 30 THEN 'ÉLEVÉ'
    ELSE 'MOYEN'
  END AS response_time_risk

ORDER BY nearest_kit_distance_m DESC