/*
TRUNCATE TABLE lignes_factures;

SET lc_time_names = 'fr_FR';

LOAD DATA LOCAL INFILE '/var/www/html/facturation_dev/sql/lignes_factures.txt'
INTO TABLE lignes_factures
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
  @Lib_presta,
  @DATE_presta,
  @QTE,
  @PRIX,
  @TTC_HT,
  @UHT,
  @UTTC,
  @THT,
  @TTTC,
  @Etat,
  @NFac
)
SET
  id = UUID(),

  -- 🔗 lien vers la facture
  id_facture = (
      SELECT f.id
      FROM factures f
      JOIN entites e ON e.id = f.id_entite
      WHERE e.code = 'BLITECH'
        AND f.no_facture = TRIM(@NFac)
      LIMIT 1
  ),

  id_abonnement = NULL,
  id_article    = NULL,

  libelle = NULLIF(TRIM(@Lib_presta), ''),

  -- QTE : '4,00' -> 4.00
  quantite = CAST(
      REPLACE(
        REPLACE(
          REPLACE(TRIM(@QTE), ' ', ''),
        '€',''),
      ',','.')
    AS DECIMAL(10,2)
  ),

  -- UHT : vrai prix unitaire HT : ' 72,25 € ' -> 72.25
  prix_unitaire_ht = CAST(
      REPLACE(
        REPLACE(
          REPLACE(TRIM(@UHT), ' ', ''),
        '€',''),
      ',','.')
    AS DECIMAL(10,2)
  ),

  -- TVA : tu as mis 20% partout, je laisse comme tu l’as spécifié
  tva_taux = 20.00,

  -- 📅 Date : JJ.MM.AAAA (ex: 28.02.2020)
  -- si la colonne est vide, on peut, si tu veux, retomber sur la date de facture
  date_prestation = CASE
      WHEN TRIM(@DATE_presta) = '' THEN
          (
              SELECT f.date_facture
              FROM factures f
              JOIN entites e ON e.id = f.id_entite
              WHERE e.code = 'BLITECH'
                AND f.no_facture = TRIM(@NFac)
              LIMIT 1
          )
      ELSE
          STR_TO_DATE(TRIM(@DATE_presta), '%d.%m.%Y')
  END,
  ordre = 1;
*/


TRUNCATE TABLE lignes_factures;

SET lc_time_names = 'fr_FR';

LOAD DATA LOCAL INFILE '/var/www/html/facturation_dev/sql/lignes_factures.txt'
INTO TABLE lignes_factures
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
  @Lib_presta,
  @DATE_presta,
  @QTE,
  @PRIX,
  @TTC_HT,
  @UHT,
  @UTTC,
  @THT,
  @TTTC,
  @Etat,
  @NFac
)
SET
  id          = UUID(),
  nfac_import = TRIM(@NFac),

 id_facture = (
      SELECT f.id
      FROM factures f
      JOIN entites e ON e.id = f.id_entite
      WHERE e.code = 'BLI'
        AND f.no_facture = TRIM(@NFac)
      LIMIT 1
  ),

  id_abonnement = NULL,
  id_article    = NULL,

  libelle = NULLIF(TRIM(@Lib_presta), ''),

  quantite = CAST(
      REPLACE(
        REPLACE(
          REPLACE(TRIM(@QTE), ' ', ''),
        '€',''),
      ',','.')
    AS DECIMAL(10,2)
  ),

  prix_unitaire_ht = CAST(
      REPLACE(
        REPLACE(
          REPLACE(TRIM(@UHT), ' ', ''),
        '€',''),
      ',','.')
    AS DECIMAL(10,2)
  ),

  tva_taux = 20.00,

  date_prestation = CASE
      WHEN TRIM(@DATE_presta) = '' THEN
          (
              SELECT f.date_facture
              FROM factures f
              JOIN entites e ON e.id = f.id_entite
              WHERE e.code = 'BLI'
                AND f.no_facture = TRIM(@NFac)
              LIMIT 1
          )
      ELSE
          STR_TO_DATE(TRIM(@DATE_presta), '%d.%m.%Y')
  END,

  ordre = 1;
