CREATE TABLE IF NOT EXISTS factures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    bon_livraison_id INT NULL,
    numero VARCHAR(30) NOT NULL UNIQUE,
    date_facture DATE NOT NULL,
    date_echeance DATE NULL,
    statut VARCHAR(30) DEFAULT 'émise',
    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,
    INDEX idx_factures_client (client_id),
    INDEX idx_factures_bl (bon_livraison_id),
    INDEX idx_factures_numero (numero),
    INDEX idx_factures_statut (statut),
    CONSTRAINT fk_factures_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_factures_bl FOREIGN KEY (bon_livraison_id) REFERENCES bons_livraison(id)
);

CREATE TABLE IF NOT EXISTS factures_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    facture_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,
    marge_ht DECIMAL(12,2) DEFAULT 0,
    marge_pourcentage DECIMAL(6,2) DEFAULT 0,
    ordre INT DEFAULT 1,
    INDEX idx_factures_lignes_facture (facture_id),
    INDEX idx_factures_lignes_article (article_id),
    CONSTRAINT fk_factures_lignes_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
    CONSTRAINT fk_factures_lignes_article FOREIGN KEY (article_id) REFERENCES articles(id)
);
