-- 400_factures.sql
USE blitech_facturation_dev;

CREATE TABLE factures (
  id            CHAR(36)     NOT NULL,
  id_entite     CHAR(36)     NOT NULL,
  id_client     CHAR(36)     NOT NULL,
  id_contact    CHAR(36)     DEFAULT NULL,

  no_facture    VARCHAR(50)  NOT NULL,      -- 'BLI_240731_001_JUL_QG' ou '250914_019'
  mode_generation VARCHAR(20) NOT NULL,     -- 'ponctuelle', 'abonnement', 'atelier'

  date_facture  DATE         NOT NULL,
  date_echeance DATE         DEFAULT NULL,

  periode_debut DATE         DEFAULT NULL,
  periode_fin   DATE         DEFAULT NULL,

  commentaire   TEXT         DEFAULT NULL,

  date_creation DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

  CONSTRAINT fk_factures_entite
    FOREIGN KEY (id_entite) REFERENCES entites(id)
      ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT fk_factures_client
    FOREIGN KEY (id_client) REFERENCES clients(id)
      ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT fk_factures_contact
    FOREIGN KEY (id_contact) REFERENCES contacts(id)
      ON DELETE SET NULL ON UPDATE CASCADE,

  UNIQUE KEY uniq_factures_entite_num (id_entite, no_facture)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


TRUNCATE TABLE factures;

DROP TABLE IF EXISTS import_factures;

CREATE TABLE import_factures (
    no_client_txt     VARCHAR(10),
    onglet_txt        VARCHAR(10),
    titre_txt         VARCHAR(50),
    prenom_txt        VARCHAR(100),
    nom_txt           VARCHAR(100),
    raison_sociale_txt VARCHAR(255),
    adresse_txt       VARCHAR(255),
    cp_txt            VARCHAR(10),
    ville_txt         VARCHAR(100),
    telephone_txt     VARCHAR(50),
    email_txt         VARCHAR(255),
    date_txt          VARCHAR(50),
    echeance_txt      VARCHAR(50),
    libfac_txt        VARCHAR(255),
    no_txt            VARCHAR(10),
    ht_ttc_txt        VARCHAR(10),
    no_facture_txt    VARCHAR(50),
    tht_txt           VARCHAR(50),
    ttc_txt           VARCHAR(50),
    etat_txt          VARCHAR(50),
    pdf_path          VARCHAR(255),
    pdf_filename          VARCHAR(255),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COLLATE utf8mb4_unicode_ci;


SET NAMES utf8mb4;

TRUNCATE TABLE import_factures;
LOAD DATA LOCAL INFILE '/var/www/html/facturation_dev/sql/factures.txt'
INTO TABLE import_factures
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
  no_client_txt,
  onglet_txt,
  titre_txt,
  prenom_txt,
  nom_txt,
  raison_sociale_txt,
  adresse_txt,
  cp_txt,
  ville_txt,
  telephone_txt,
  email_txt,
  date_txt,
  echeance_txt,
  libfac_txt,
  no_txt,
  ht_ttc_txt,
  no_facture_txt,
  tht_txt,
  ttc_txt,
  etat_txt
);


TRUNCATE TABLE factures;
SET lc_time_names = 'fr_FR';
INSERT INTO factures (
    id,
    id_entite,
    id_client,
    id_contact,
    id_devis_source,
    no_facture,
    mode_generation,
    date_facture,
    date_echeance,
    periode_debut,
    periode_fin,
    commentaire
)
SELECT
    UUID() AS id,
    e.id AS id_entite,
    c.id AS id_client,
    NULL AS id_contact,
    NULL AS id_devis_source,
    f.no_facture_clean AS no_facture,
    'historique' AS mode_generation,
    STR_TO_DATE(REPLACE(TRIM(f.date_txt),     '\r',''), '%d/%m/%Y') AS date_facture,
    STR_TO_DATE(REPLACE(TRIM(f.echeance_txt), '\r',''), '%d/%m/%Y') AS date_echeance,
    NULL AS periode_debut,
    NULL AS periode_fin,
    f.libfac_txt AS commentaire
FROM
(
    -- on regroupe par No Facture pour éviter les doublons
    SELECT
        TRIM(no_facture_txt) AS no_facture_clean,
        MIN(TRIM(date_txt)) AS date_txt,
        MIN(TRIM(echeance_txt)) AS echeance_txt,
        MIN(TRIM(libfac_txt)) AS libfac_txt,
        MIN(TRIM(no_client_txt)) AS no_client_txt,
        MIN(TRIM(etat_txt)) AS etat_txt
    FROM import_factures
    WHERE
        -- on ignore la ligne "Total / #N/A / #REF!"
        no_facture_txt IS NOT NULL
        AND TRIM(no_facture_txt) <> ''
        AND (etat_txt IS NULL OR etat_txt NOT LIKE '#REF!%')
    GROUP BY TRIM(no_facture_txt)
) AS f
JOIN entites e
    ON e.code = 'BLI'
JOIN clients c
    ON c.id_entite = e.id
   -- si tes no_client sont '0001', '0002', ... :
   AND c.no_client = LPAD(f.no_client_txt, 4, '0');
   -- si au contraire c.no_client vaut simplement '1','2','3', remplace la ligne ci-dessus par :
   -- AND c.no_client = f.no_client_txt;
