SELECT
    c.no_client,
    COALESCE(c.raison_sociale, CONCAT_WS(' ', c.prenom, c.nom)) AS nom_client,
    COUNT(DISTINCT f.id)    AS nb_factures,
    COALESCE(SUM(lf.quantite * lf.prix_unitaire_ht), 0) AS total_ht,
    COALESCE(SUM(lf.quantite * lf.prix_unitaire_ht * (1 + lf.tva_taux/100)), 0) AS total_ttc
FROM clients c
JOIN factures f
  ON f.id_client = c.id
LEFT JOIN lignes_factures lf
  ON lf.id_facture = f.id
WHERE f.id_entite = (SELECT id FROM entites WHERE code = 'BLITECH')
GROUP BY
    c.id,
    c.no_client,
    nom_client
ORDER BY
    total_ttc DESC;
