-- Sprint 3.1 : Module Articles + Import Excel

CREATE TABLE IF NOT EXISTS familles_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(100) NOT NULL UNIQUE,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS marques_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(100) NOT NULL UNIQUE,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    famille_id INT NULL,
    marque_id INT NULL,
    reference VARCHAR(50) NOT NULL UNIQUE,
    designation VARCHAR(255) NOT NULL,
    conditionnement VARCHAR(100),
    unite VARCHAR(30) DEFAULT 'unité',
    pua_ht DECIMAL(12,2) DEFAULT 0,
    coefficient DECIMAL(8,3) DEFAULT 1,
    puv_ht DECIMAL(12,2) DEFAULT 0,
    taux_tva DECIMAL(5,2) DEFAULT 20.00,
    prix_ttc DECIMAL(12,2) DEFAULT 0,
    marge_dh DECIMAL(12,2) DEFAULT 0,
    marge_pourcentage DECIMAL(5,2) DEFAULT 0,
    stock_minimum DECIMAL(12,2) DEFAULT 0,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_articles_reference (reference),
    INDEX idx_articles_designation (designation),
    INDEX idx_articles_famille (famille_id),
    INDEX idx_articles_marque (marque_id),
    FOREIGN KEY (famille_id) REFERENCES familles_articles(id),
    FOREIGN KEY (marque_id) REFERENCES marques_articles(id)
);

INSERT INTO familles_articles (libelle)
SELECT 'Détergents' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Détergents');
INSERT INTO familles_articles (libelle)
SELECT 'Désinfectants' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Désinfectants');
INSERT INTO familles_articles (libelle)
SELECT 'Papier' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Papier');
INSERT INTO familles_articles (libelle)
SELECT 'Sacs poubelles' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Sacs poubelles');
INSERT INTO familles_articles (libelle)
SELECT 'Cuisine' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Cuisine');
INSERT INTO familles_articles (libelle)
SELECT 'Sanitaires' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Sanitaires');
INSERT INTO familles_articles (libelle)
SELECT 'Matériel' WHERE NOT EXISTS (SELECT 1 FROM familles_articles WHERE libelle = 'Matériel');