USE blitech_facturation_dev;

DROP VIEW IF EXISTS vue_export_factures;

CREATE VIEW vue_export_factures AS
SELECT
    lf.lib_presta                   AS Lib_presta,
    lf.date_presta                  AS `DATE`,
    lf.qte                          AS QTE,
    lf.prix_unitaire                AS PRIX,
    lf.type_prix                    AS `TTC/HT`,

    -- UHT : prix unitaire hors taxe
    CASE
        WHEN lf.type_prix = 'HT'  THEN lf.prix_unitaire
        WHEN lf.type_prix = 'TTC' THEN
            ROUND(lf.prix_unitaire / (1 + lf.taux_tva / 100), 2)
        ELSE NULL
    END                             AS UHT,

    -- UTTC : prix unitaire TTC
    CASE
        WHEN lf.type_prix = 'TTC' THEN lf.prix_unitaire
        WHEN lf.type_prix = 'HT'  THEN
            ROUND(lf.prix_unitaire * (1 + lf.taux_tva / 100), 2)
        ELSE NULL
    END                             AS UTTC,

    f.total_ht                      AS THT,
    f.total_ttc                     AS TTTC,

    f.etat                          AS Etat,
    f.nfac                          AS NFac
FROM lignes_facture lf
JOIN factures f ON f.id = lf.id_facture;

