CREATE TABLE IF NOT EXISTS devis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    numero VARCHAR(30) NOT NULL UNIQUE,
    date_devis DATE NOT NULL,
    validite DATE NULL,
    statut VARCHAR(30) DEFAULT 'brouillon',
    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,
    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_devis_client (client_id),
    INDEX idx_devis_numero (numero),
    CONSTRAINT fk_devis_client FOREIGN KEY (client_id) REFERENCES clients(id)
);

CREATE TABLE IF NOT EXISTS devis_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    devis_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_devis_lignes_devis (devis_id),
    INDEX idx_devis_lignes_article (article_id),
    CONSTRAINT fk_devis_lignes_devis FOREIGN KEY (devis_id) REFERENCES devis(id) ON DELETE CASCADE,
    CONSTRAINT fk_devis_lignes_article FOREIGN KEY (article_id) REFERENCES articles(id)
);
