-- --------------------------------------------------------------------
-- Nettoyage préalable
-- --------------------------------------------------------------------
DROP DATABASE IF EXISTS fabricant_meubles;

-- --------------------------------------------------------------------
-- 1. CREATE DATABASE
-- --------------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS fabricant_meubles
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE fabricant_meubles;

-- --------------------------------------------------------------------
-- 2. TABLES
-- --------------------------------------------------------------------

-- TABLE Clients
CREATE TABLE clients (
    id_client INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    adresse VARCHAR(255) NOT NULL,
    telephone VARCHAR(20)
);

-- TABLE Catégories de produits
CREATE TABLE categories_produits (
    id_categorie INT AUTO_INCREMENT PRIMARY KEY,
    nom_categorie VARCHAR(100) NOT NULL UNIQUE
);

-- TABLE Statuts de commandes
CREATE TABLE statuts_commandes (
    id_statut INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(50) NOT NULL UNIQUE
);

-- TABLE Produits
CREATE TABLE produits (
    id_produit INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(150) NOT NULL,
    id_categorie INT NOT NULL,
    prix DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,

    FOREIGN KEY (id_categorie) REFERENCES categories_produits(id_categorie)
);

-- TABLE Commandes
CREATE TABLE commandes (
    id_commande INT AUTO_INCREMENT PRIMARY KEY,
    id_client INT NOT NULL,
    id_statut INT NOT NULL,
    date_commande DATE NOT NULL,

    FOREIGN KEY (id_client) REFERENCES clients(id_client),
    FOREIGN KEY (id_statut) REFERENCES statuts_commandes(id_statut)
);

-- TABLE Factures
CREATE TABLE factures (
    id_facture INT AUTO_INCREMENT PRIMARY KEY,
    id_commande INT NOT NULL,
    montant DECIMAL(10,2) NOT NULL,
    date_facture DATE NOT NULL,

    FOREIGN KEY (id_commande) REFERENCES commandes(id_commande)
);

-- TABLE Commande_Produit (relation n-n)
CREATE TABLE commande_produit (
    id_commande INT NOT NULL,
    id_produit INT NOT NULL,
    quantite INT NOT NULL,

    PRIMARY KEY (id_commande, id_produit),

    FOREIGN KEY (id_commande) REFERENCES commandes(id_commande)
      ON DELETE CASCADE,
    FOREIGN KEY (id_produit) REFERENCES produits(id_produit)
      ON DELETE CASCADE
);

-- --------------------------------------------------------------------
-- 3. INDEXES
-- --------------------------------------------------------------------
CREATE INDEX idx_client_email ON clients(email);
CREATE INDEX idx_categorie_nom ON categories_produits(nom_categorie);
CREATE INDEX idx_statut_libelle ON statuts_commandes(libelle);
CREATE INDEX idx_produit_categorie ON produits(id_categorie);
CREATE INDEX idx_commande_client ON commandes(id_client);
CREATE INDEX idx_commande_statut ON commandes(id_statut);
CREATE INDEX idx_facture_commande ON factures(id_commande);

-- --------------------------------------------------------------------
-- 4. DATASET – INSERTIONS
-- --------------------------------------------------------------------

-- 4.1 CATEGORIES (6 catégories)
INSERT INTO categories_produits (nom_categorie) VALUES
('Tables'),      -- id_categorie = 1
('Chaises'),     -- id_categorie = 2
('Rangements'),  -- id_categorie = 3
('Canapés'),     -- id_categorie = 4
('Bureaux'),     -- id_categorie = 5
('Sièges');      -- id_categorie = 6

-- 4.2 STATUTS DE COMMANDES
INSERT INTO statuts_commandes (libelle) VALUES
('en préparation'),  -- id_statut = 1
('en cours'),        -- id_statut = 2
('expédiée'),        -- id_statut = 3
('livrée'),          -- id_statut = 4
('annulée');         -- id_statut = 5

-- 4.3 PRODUITS (au moins 2 par catégorie, 12 produits au total)
INSERT INTO produits (nom, id_categorie, prix, stock) VALUES
-- Tables (1)
('Table en chêne massif', 1, 399.90, 10),
('Table basse industrielle', 1, 249.00, 8),

-- Chaises (2)
('Chaise scandinave', 2, 89.50, 50),
('Chaise rembourrée', 2, 119.00, 30),

-- Rangements (3)
('Buffet deux portes', 3, 549.00, 5),
('Armoire portes coulissantes', 3, 699.00, 4),

-- Canapés (4)
('Canapé 3 places tissu gris', 4, 799.00, 7),
('Fauteuil relax cuir', 4, 599.00, 6),

-- Bureaux (5)
('Bureau compact', 5, 199.90, 15),
('Bureau d’angle', 5, 349.00, 9),

-- Sièges (6)
('Tabouret bois', 6, 39.90, 40),
('Tabouret de bar métal', 6, 59.90, 35);

-- 4.4 CLIENTS (6 clients)
INSERT INTO clients (nom, email, adresse, telephone) VALUES
('Jean Martin', 'j.martin@example.com', '12 rue des Chênes, 69000 Lyon', '0601020304'),
('Sophie Duval', 's.duval@example.com', '8 avenue Victor Hugo, 75016 Paris', '0605060708'),
('Entreprise ABC', 'contact@abc.com', 'Zone Indus 4, 33000 Bordeaux', '0556778899'),
('Lucie Bernard', 'lucie.bernard@example.com', '5 rue des Lilas, 44000 Nantes', '0611223344'),
('Marc Petit', 'm.petit@example.com', '27 boulevard des Alpes, 38000 Grenoble', '0677889900'),
('DécoDesign SARL', 'contact@decodecor.fr', 'Parc d’activité Sud, 31000 Toulouse', '0533445566');

-- 4.5 COMMANDES (20 commandes)
-- (id_client, id_statut, date_commande)
INSERT INTO commandes (id_client, id_statut, date_commande) VALUES
(1, 4, '2025-11-01'), -- 1 livrée
(2, 3, '2025-11-02'), -- 2 expédiée
(3, 2, '2025-11-03'), -- 3 en cours
(1, 1, '2025-11-04'), -- 4 en préparation
(4, 4, '2025-11-05'), -- 5 livrée
(5, 2, '2025-11-06'), -- 6 en cours
(6, 3, '2025-11-07'), -- 7 expédiée
(2, 4, '2025-11-08'), -- 8 livrée
(3, 1, '2025-11-09'), -- 9 en préparation
(4, 2, '2025-11-10'), -- 10 en cours
(5, 4, '2025-11-11'), -- 11 livrée
(6, 3, '2025-11-12'), -- 12 expédiée
(1, 2, '2025-11-13'), -- 13 en cours
(2, 4, '2025-11-14'), -- 14 livrée
(3, 5, '2025-11-15'), -- 15 annulée
(4, 1, '2025-11-16'), -- 16 en préparation
(5, 2, '2025-11-17'), -- 17 en cours
(6, 3, '2025-11-18'), -- 18 expédiée
(1, 4, '2025-11-19'), -- 19 livrée
(2, 1, '2025-11-20'); -- 20 en préparation

-- 4.6 FACTURES (1 facture par commande)
-- NB : les montants sont indicatifs, ils ne reflètent pas forcément
-- la somme exacte des lignes de commande (pour garder le script lisible)
INSERT INTO factures (id_commande, montant, date_facture) VALUES
(1, 1058.90, '2025-11-02'),
(2, 799.00,  '2025-11-03'),
(3, 318.90,  '2025-11-04'),
(4, 1328.80, '2025-11-05'),
(5, 1398.00, '2025-11-06'),
(6, 438.90,  '2025-11-07'),
(7, 699.00,  '2025-11-08'),
(8, 399.90,  '2025-11-09'),
(9, 319.50,  '2025-11-10'),
(10, 1025.00,'2025-11-11'),
(11, 399.80,'2025-11-12'),
(12, 879.80,'2025-11-13'),
(13, 787.00,'2025-11-14'),
(14, 349.00,'2025-11-15'),
(15, 1198.00,'2025-11-16'),
(16, 1248.00,'2025-11-17'),
(17, 599.80,'2025-11-18'),
(18, 618.80,'2025-11-19'),
(19, 249.00,'2025-11-20'),
(20, 1647.90,'2025-11-21');

-- 4.7 COMMANDE_PRODUIT (20 commandes, 1 à 5 produits chacune)
-- id_commande, id_produit, quantite
INSERT INTO commande_produit (id_commande, id_produit, quantite) VALUES
-- Commande 1 (Jean Martin)
(1, 1, 1),   -- Table en chêne massif
(1, 3, 4),   -- 4 chaises scandinaves

-- Commande 2 (Sophie Duval)
(2, 7, 1),   -- Canapé 3 places

-- Commande 3 (Entreprise ABC)
(3, 9, 1),   -- Bureau compact
(3, 4, 1),   -- Chaise rembourrée

-- Commande 4 (Jean Martin)
(4, 5, 1),   -- Buffet deux portes
(4, 2, 1),   -- Table basse industrielle
(4, 11, 2),  -- 2 tabourets bois

-- Commande 5 (Lucie Bernard)
(5, 7, 1),   -- Canapé 3 places
(5, 8, 1),   -- Fauteuil relax

-- Commande 6 (Marc Petit)
(6, 10, 1),  -- Bureau d’angle
(6, 3, 2),   -- 2 chaises scandinaves

-- Commande 7 (DécoDesign SARL)
(7, 6, 1),   -- Armoire portes coulissantes

-- Commande 8 (Sophie Duval)
(8, 1, 1),   -- Table en chêne massif

-- Commande 9 (Entreprise ABC)
(9, 11, 4),  -- 4 tabourets bois
(9, 12, 2),  -- 2 tabourets métal

-- Commande 10 (Lucie Bernard)
(10, 5, 1),  -- Buffet deux portes
(10, 4, 4),  -- 4 chaises rembourrées

-- Commande 11 (Marc Petit)
(11, 9, 2),  -- 2 bureaux compacts

-- Commande 12 (DécoDesign SARL)
(12, 7, 1),  -- Canapé 3 places
(12, 11, 2), -- 2 tabourets bois

-- Commande 13 (Jean Martin)
(13, 2, 1),  -- Table basse industrielle
(13, 3, 6),  -- 6 chaises scandinaves

-- Commande 14 (Sophie Duval)
(14, 10, 1), -- Bureau d’angle

-- Commande 15 (Entreprise ABC) - annulée
(15, 8, 2),  -- 2 fauteuils relax

-- Commande 16 (Lucie Bernard)
(16, 6, 1),  -- Armoire portes coulissantes
(16, 5, 1),  -- Buffet deux portes

-- Commande 17 (Marc Petit)
(17, 1, 1),  -- Table en chêne
(17, 9, 1),  -- Bureau compact

-- Commande 18 (DécoDesign SARL)
(18, 4, 2),  -- 2 chaises rembourrées
(18, 12, 4), -- 4 tabourets métal

-- Commande 19 (Jean Martin)
(19, 2, 1),  -- Table basse industrielle

-- Commande 20 (Sophie Duval)
(20, 7, 1),  -- Canapé 3 places
(20, 8, 1),  -- Fauteuil relax
(20, 2, 1);  -- Table basse industrielle