CREATE TABLE IF NOT EXISTS inventaires_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_inventaire DATETIME DEFAULT CURRENT_TIMESTAMP,
    commentaire TEXT NULL,
    nb_articles INT NOT NULL DEFAULT 0,
    nb_ecarts INT NOT NULL DEFAULT 0,
    total_ecart_quantite DECIMAL(12,3) NOT NULL DEFAULT 0,
    valeur_ecart_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
    utilisateur VARCHAR(100) DEFAULT 'Admin',
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_inventaires_stock_date (date_inventaire)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventaires_stock_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventaire_id INT NOT NULL,
    article_id INT NOT NULL,
    stock_theorique DECIMAL(12,3) NOT NULL DEFAULT 0,
    stock_reel DECIMAL(12,3) NOT NULL DEFAULT 0,
    ecart DECIMAL(12,3) NOT NULL DEFAULT 0,
    prix_achat_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
    valeur_ecart_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_inv_lignes_inventaire (inventaire_id),
    INDEX idx_inv_lignes_article (article_id),
    CONSTRAINT fk_inv_lignes_inventaire FOREIGN KEY (inventaire_id) REFERENCES inventaires_stock(id) ON DELETE CASCADE,
    CONSTRAINT fk_inv_lignes_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
