CREATE TABLE IF NOT EXISTS bons_livraison (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    devis_id INT NULL,
    numero VARCHAR(30) NOT NULL UNIQUE,
    date_bl DATE NOT NULL,
    date_livraison DATETIME NULL,
    receptionnaire VARCHAR(150) NULL,
    statut VARCHAR(30) DEFAULT 'préparé',
    total_ht DECIMAL(12,2) DEFAULT 0,
    total_tva DECIMAL(12,2) DEFAULT 0,
    total_ttc DECIMAL(12,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,
    INDEX idx_bl_client (client_id),
    INDEX idx_bl_devis (devis_id),
    INDEX idx_bl_numero (numero),
    CONSTRAINT fk_bl_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_bl_devis FOREIGN KEY (devis_id) REFERENCES devis(id)
);

CREATE TABLE IF NOT EXISTS bons_livraison_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bon_livraison_id INT NOT 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,
    ordre INT DEFAULT 1,
    INDEX idx_bl_lignes_bl (bon_livraison_id),
    INDEX idx_bl_lignes_article (article_id),
    CONSTRAINT fk_bl_lignes_bl FOREIGN KEY (bon_livraison_id) REFERENCES bons_livraison(id) ON DELETE CASCADE,
    CONSTRAINT fk_bl_lignes_article FOREIGN KEY (article_id) REFERENCES articles(id)
);