TRUNCATE TABLE factures;
SET lc_time_names = 'fr_FR';
LOAD DATA LOCAL INFILE '/var/www/html/facturation_dev/sql/factures.txt'
IGNORE
INTO TABLE factures
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
  @No_Client,
  @Onglet,
  @Titre,
  @Prenom,
  @Nom,
  @Raison_sociale,
  @Adresse,
  @CP,
  @Ville,
  @Telephone,
  @Email,
  @Date,
  @Echeance,
  @LibFac,
  @No,
  @HT_TTC,
  @No_Facture,
  @THT,
  @TTC,
  @Etat
)
SET
  id        = UUID(),
  id_entite = (SELECT id FROM entites WHERE code = 'BLI'),

  id_client = (
      SELECT c.id
      FROM clients c
      JOIN entites e ON e.id = c.id_entite
      WHERE e.code = 'BLI'
        AND CAST(c.no_client AS UNSIGNED) = CAST(TRIM(@No_Client) AS UNSIGNED)
      LIMIT 1
  ),

  no_facture = TRIM(@No_Facture),
  date_facture = STR_TO_DATE(SUBSTRING(TRIM(@No_Facture), 1, 6), '%y%m%d'),
  date_echeance = NULL,
  mode_generation = 'historique',
  commentaire   = NULLIF(TRIM(@LibFac), ''),
  periode_debut = NULL,
  periode_fin   = NULL,
  date_creation = NOW();
