


UPDATE fabrications f
LEFT JOIN (
    -- 1️⃣ Devis unique par client (tous statuts)
    SELECT client_id, MIN(id) AS devis_general
    FROM devis
    GROUP BY client_id
    HAVING COUNT(*) = 1
) d_general ON f.client_id = d_general.client_id
LEFT JOIN (
    -- 2️⃣ Devis unique par client avec statut = 5
    SELECT client_id, MIN(id) AS devis_statut5
    FROM devis
    WHERE statut = 5
    GROUP BY client_id
    HAVING COUNT(*) = 1
) d_statut ON f.client_id = d_statut.client_id
LEFT JOIN (
    -- 3️⃣ Devis unique correspondant à modele_nom LIKE nature et statut = 5
    SELECT d.client_id, f.modele_nom, MIN(d.id) AS devis_modele
    FROM devis d
    JOIN fabrications f ON d.client_id = f.client_id
    WHERE d.statut = 5
      AND d.nature LIKE CONCAT('%', f.modele_nom, '%')
    GROUP BY d.client_id, f.modele_nom
    HAVING COUNT(*) = 1
) d_modele ON f.client_id = d_modele.client_id
           AND f.modele_nom = d_modele.modele_nom
SET f.devis_id = COALESCE(f.devis_id, d_general.devis_general, d_statut.devis_statut5, d_modele.devis_modele)
WHERE f.devis_id IS NULL;
