CREATE TABLE IF NOT EXISTS stocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL UNIQUE,
    quantite DECIMAL(12,2) NOT NULL DEFAULT 0,
    stock_minimum DECIMAL(12,2) NOT NULL DEFAULT 0,
    valeur_stock DECIMAL(12,2) NOT NULL DEFAULT 0,
    date_maj DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_stocks_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS mouvements_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    type_mouvement VARCHAR(10) NOT NULL,
    quantite DECIMAL(12,2) NOT NULL,
    prix_achat_ht DECIMAL(12,2) DEFAULT 0,
    origine VARCHAR(50),
    origine_id INT NULL,
    commentaire TEXT,
    utilisateur VARCHAR(100) DEFAULT 'Admin',
    date_mouvement DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_mouvements_stock_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE RESTRICT,
    INDEX idx_mouvements_stock_article (article_id),
    INDEX idx_mouvements_stock_date (date_mouvement),
    INDEX idx_mouvements_stock_type (type_mouvement)
);

INSERT INTO stocks (article_id, quantite, stock_minimum, valeur_stock)
SELECT a.id, 0, COALESCE(a.stock_minimum, 0), 0
FROM articles a
LEFT JOIN stocks s ON s.article_id = a.id
WHERE a.actif = 1 AND s.id IS NULL;