-- 410_lignes_factures.sql
USE blitech_facturation_dev;

CREATE TABLE lignes_factures (
  id              CHAR(36)     NOT NULL,
  id_facture      CHAR(36)     NOT NULL,

  id_abonnement   CHAR(36)     DEFAULT NULL,
  id_article      CHAR(36)     DEFAULT NULL,

  libelle         VARCHAR(255) NOT NULL,
  description     TEXT         DEFAULT NULL,

  date_prestation DATE         NOT NULL,

  quantite        DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  prix_unitaire_ht DECIMAL(10,2) NOT NULL,
  tva_taux         DECIMAL(5,2)  NOT NULL DEFAULT 0.00,

  ordre           INT          NOT NULL DEFAULT 1,

  PRIMARY KEY (id),

  CONSTRAINT fk_lignes_factures_facture
    FOREIGN KEY (id_facture) REFERENCES factures(id)
      ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_lignes_factures_abonnement
    FOREIGN KEY (id_abonnement) REFERENCES abonnements(id)
      ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_lignes_factures_article
    FOREIGN KEY (id_article) REFERENCES articles(id)
      ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

TRUNCATE TABLE lignes_factures;

SET lc_time_names = 'fr_FR';

TRUNCATE TABLE import_lignes_factures;

LOAD DATA LOCAL INFILE '/var/www/html/facturation_dev/sql/lignes_factures.txt'
INTO TABLE import_lignes_factures
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
  lib_presta,
  date_txt,
  qte_txt,
  prix_txt,
  ttc_ht_txt,
  uht_txt,
  uttc_txt,
  tht_txt,
  tttc_txt,
  etat_txt,
  nfac_txt
);

select * from import_lignes_factures limit 5;
UPDATE import_lignes_factures SET nfac_txt = REPLACE(REPLACE(TRIM(nfac_txt), '\r', ''), '\n', '');

SELECT
  ilf.nfac_txt,
  HEX(ilf.nfac_txt) AS hex_import
FROM import_lignes_factures ilf
LIMIT 10;

SELECT
  f.no_facture,
  HEX(f.no_facture) AS hex_facture
FROM factures f
LIMIT 10;


SET lc_time_names = 'fr_FR';

INSERT INTO lignes_factures (
    id,
    id_facture,
    id_abonnement,
    id_article,
    libelle,
    description,
    date_prestation,
    quantite,
    prix_unitaire_ht,
    tva_taux,
    ordre
)
SELECT
    UUID() AS id,
    f.id   AS id_facture,
    NULL   AS id_abonnement,
    NULL   AS id_article,
    NULLIF(TRIM(ilf.lib_presta), '') AS libelle,
    NULL   AS description,

    -- JJ.MM.AAAA => DATE
    STR_TO_DATE(TRIM(ilf.date_txt), '%d.%m.%Y') AS date_prestation,

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

    -- UHT : ' 72,25 € ' -> 72.25
    CAST(
        REPLACE(
          REPLACE(
            REPLACE(TRIM(ilf.uht_txt), ' ', ''),
          '€',''),
        ',','.')
      AS DECIMAL(10,2)
    ) AS prix_unitaire_ht,

    20.00 AS tva_taux,
    1     AS ordre
FROM import_lignes_factures ilf
JOIN factures f
  ON f.no_facture = ilf.nfac_txt;

SELECT COUNT(*) AS nb_lignes
FROM lignes_factures;

SELECT
  f.no_facture,
  lf.libelle,
  lf.date_prestation,
  lf.quantite,
  lf.prix_unitaire_ht
FROM lignes_factures lf
JOIN factures f ON f.id = lf.id_facture
ORDER BY f.no_facture, lf.date_prestation, lf.libelle
LIMIT 20;
