CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_document VARCHAR(20) NOT NULL,
    source_table VARCHAR(60) NULL,
    source_id INT NULL,
    client_id INT NOT NULL,
    numero VARCHAR(30) NOT NULL,
    date_document DATE NOT NULL,
    date_echeance DATE NULL,
    statut VARCHAR(40) DEFAULT 'brouillon',
    total_ht DECIMAL(12,2) DEFAULT 0,
    total_tva DECIMAL(12,2) DEFAULT 0,
    total_ttc DECIMAL(12,2) DEFAULT 0,
    montant_regle DECIMAL(12,2) DEFAULT 0,
    reste_du DECIMAL(12,2) DEFAULT 0,
    marge_ht DECIMAL(12,2) DEFAULT 0,
    marge_pourcentage DECIMAL(6,2) DEFAULT 0,
    observations TEXT,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_document_source (source_table, source_id),
    INDEX idx_documents_type (type_document),
    INDEX idx_documents_numero (numero),
    INDEX idx_documents_client (client_id),
    INDEX idx_documents_date (date_document),
    INDEX idx_documents_statut (statut),
    CONSTRAINT fk_documents_client FOREIGN KEY (client_id) REFERENCES clients(id)
);

CREATE TABLE IF NOT EXISTS documents_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    source_table VARCHAR(60) NULL,
    source_id INT NULL,
    article_id INT NULL,
    reference VARCHAR(50),
    designation VARCHAR(255) NOT NULL,
    unite VARCHAR(30) DEFAULT 'unité',
    quantite DECIMAL(12,2) DEFAULT 1,
    prix_achat_ht DECIMAL(12,2) DEFAULT 0,
    prix_vente_ht DECIMAL(12,2) DEFAULT 0,
    remise DECIMAL(6,2) DEFAULT 0,
    taux_tva DECIMAL(5,2) DEFAULT 20,
    total_ht DECIMAL(12,2) DEFAULT 0,
    total_tva DECIMAL(12,2) DEFAULT 0,
    total_ttc DECIMAL(12,2) DEFAULT 0,
    marge_ht DECIMAL(12,2) DEFAULT 0,
    marge_pourcentage DECIMAL(6,2) DEFAULT 0,
    ordre INT DEFAULT 1,
    INDEX idx_documents_lignes_document (document_id),
    INDEX idx_documents_lignes_article (article_id),
    CONSTRAINT fk_documents_lignes_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    CONSTRAINT fk_documents_lignes_article FOREIGN KEY (article_id) REFERENCES articles(id)
);

INSERT IGNORE INTO documents (
    type_document, source_table, source_id, client_id, numero, date_document, date_echeance, statut,
    total_ht, total_tva, total_ttc, montant_regle, reste_du, marge_ht, marge_pourcentage, observations,
    actif, date_creation, date_modification
)
SELECT 'DEVIS', 'devis', d.id, d.client_id, d.numero, d.date_devis, d.validite, d.statut,
       d.total_ht, d.total_tva, d.total_ttc, 0, 0, d.marge_ht, d.marge_pourcentage, d.observations,
       d.actif, d.date_creation, d.date_modification
FROM devis d
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'devis');

INSERT IGNORE INTO documents (
    type_document, source_table, source_id, client_id, numero, date_document, date_echeance, statut,
    total_ht, total_tva, total_ttc, montant_regle, reste_du, marge_ht, marge_pourcentage, observations,
    actif, date_creation, date_modification
)
SELECT 'BL', 'bons_livraison', bl.id, bl.client_id, bl.numero, bl.date_bl, NULL, bl.statut,
       bl.total_ht, bl.total_tva, bl.total_ttc, 0, 0, 0, 0, bl.observations,
       bl.actif, bl.date_creation, bl.date_modification
FROM bons_livraison bl
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'bons_livraison');

INSERT IGNORE INTO documents (
    type_document, source_table, source_id, client_id, numero, date_document, date_echeance, statut,
    total_ht, total_tva, total_ttc, montant_regle, reste_du, marge_ht, marge_pourcentage, observations,
    actif, date_creation, date_modification
)
SELECT 'FACTURE', 'factures', f.id, f.client_id, f.numero, f.date_facture, f.date_echeance, f.statut,
       f.total_ht, f.total_tva, f.total_ttc, f.montant_regle, f.reste_du, f.marge_ht, f.marge_pourcentage, f.observations,
       f.actif, f.date_creation, f.date_modification
FROM factures f
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'factures');

INSERT INTO documents_lignes (
    document_id, source_table, source_id, article_id, reference, designation, unite, quantite,
    prix_achat_ht, prix_vente_ht, remise, taux_tva, total_ht, total_tva, total_ttc,
    marge_ht, marge_pourcentage, ordre
)
SELECT doc.id, 'devis_lignes', dl.id, dl.article_id, dl.reference, dl.designation, dl.unite, dl.quantite,
       dl.prix_achat_ht, dl.prix_vente_ht, dl.remise, dl.taux_tva, dl.total_ht, dl.total_tva, dl.total_ttc,
       dl.marge_ht, dl.marge_pourcentage, dl.ordre
FROM devis_lignes dl
INNER JOIN documents doc ON doc.source_table = 'devis' AND doc.source_id = dl.devis_id
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'devis_lignes')
AND NOT EXISTS (SELECT 1 FROM documents_lignes x WHERE x.source_table = 'devis_lignes' AND x.source_id = dl.id);

INSERT INTO documents_lignes (
    document_id, source_table, source_id, article_id, reference, designation, unite, quantite,
    prix_achat_ht, prix_vente_ht, remise, taux_tva, total_ht, total_tva, total_ttc,
    marge_ht, marge_pourcentage, ordre
)
SELECT doc.id, 'bons_livraison_lignes', bll.id, bll.article_id, bll.reference, bll.designation, bll.unite, bll.quantite,
       bll.prix_achat_ht, bll.prix_vente_ht, bll.remise, bll.taux_tva, bll.total_ht, bll.total_tva, bll.total_ttc,
       0, 0, bll.ordre
FROM bons_livraison_lignes bll
INNER JOIN documents doc ON doc.source_table = 'bons_livraison' AND doc.source_id = bll.bon_livraison_id
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'bons_livraison_lignes')
AND NOT EXISTS (SELECT 1 FROM documents_lignes x WHERE x.source_table = 'bons_livraison_lignes' AND x.source_id = bll.id);

INSERT INTO documents_lignes (
    document_id, source_table, source_id, article_id, reference, designation, unite, quantite,
    prix_achat_ht, prix_vente_ht, remise, taux_tva, total_ht, total_tva, total_ttc,
    marge_ht, marge_pourcentage, ordre
)
SELECT doc.id, 'factures_lignes', fl.id, fl.article_id, fl.reference, fl.designation, fl.unite, fl.quantite,
       fl.prix_achat_ht, fl.prix_vente_ht, fl.remise, fl.taux_tva, fl.total_ht, fl.total_tva, fl.total_ttc,
       fl.marge_ht, fl.marge_pourcentage, fl.ordre
FROM factures_lignes fl
INNER JOIN documents doc ON doc.source_table = 'factures' AND doc.source_id = fl.facture_id
WHERE EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'factures_lignes')
AND NOT EXISTS (SELECT 1 FROM documents_lignes x WHERE x.source_table = 'factures_lignes' AND x.source_id = fl.id);
