-- ---------- CHAPITRE 3 ----------
-- ---------- CRÉATION DES TABLES ----------
CREATE TABLE CATEGORIE (
  idCategorie  INT         PRIMARY KEY,
  nomCategorie VARCHAR(50) NOT NULL
);

CREATE TABLE CLIENT (
  idClient  INT          PRIMARY KEY,
  nom       VARCHAR(50)  NOT NULL,
  prenom    VARCHAR(50)  NOT NULL,
  email     VARCHAR(100) NOT NULL UNIQUE,
  ville     VARCHAR(50),
  adresse   VARCHAR(120)
);

CREATE TABLE PRODUIT (
  idProduit   VARCHAR(10)   PRIMARY KEY,
  libelle     VARCHAR(80)   NOT NULL,
  prix        DECIMAL(10,2) NOT NULL CHECK (prix > 0),
  stock       INT           NOT NULL DEFAULT 0
              CHECK (stock >= 0),
  idCategorie INT           NOT NULL,
  FOREIGN KEY (idCategorie) REFERENCES CATEGORIE(idCategorie)
);

CREATE TABLE COMMANDE (
  idCommande   INT         PRIMARY KEY,
  dateCommande DATE        NOT NULL,
  statut       VARCHAR(20) NOT NULL
    CHECK (statut IN ('en_attente','expediee',
                      'livree','annulee')),
  idClient     INT         NOT NULL,
  FOREIGN KEY (idClient) REFERENCES CLIENT(idClient)
);

CREATE TABLE LIGNE_COMMANDE (
  idCommande        INT          NOT NULL,
  idProduit         VARCHAR(10)  NOT NULL,
  quantite          INT          NOT NULL
                    CHECK (quantite > 0),
  prixVenteApplique DECIMAL(10,2) NOT NULL
                    CHECK (prixVenteApplique > 0),
  PRIMARY KEY (idCommande, idProduit),
  FOREIGN KEY (idCommande) REFERENCES COMMANDE(idCommande),
  FOREIGN KEY (idProduit)  REFERENCES PRODUIT(idProduit)
);

-- ---------- Chapitre 3 ----------
-- ---------- CHARGEMENT DES DONNÉES ----------
INSERT INTO CATEGORIE
  (idCategorie, nomCategorie) VALUES
 (1, 'Téléphonie'), (2, 'Audio'),
 (3, 'Accessoires'), (4, 'Informatique'),
 (5, 'Maison connectée');

INSERT INTO CLIENT (idClient, nom, prenom, email, ville, adresse) VALUES
 (3,  'Boudiaf', 'Lina',   'boudiaf@mail.dz', 'Constantine', '12 rue Larbi Ben M''hidi'),
 (7,  'Benali',  'Amine',  'benali@mail.dz',  'Alger',       '5 boulevard Didouche Mourad'),
 (9,  'Cherif',  'Yacine', 'cherif@mail.dz',  'Annaba',      '8 cours de la Révolution'),
 (12, 'Haddad',  'Sarah',  'haddad@mail.dz',  'Oran',        '23 rue de Tlemcen'),
 (15, 'Meziane', 'Karim',  'meziane@mail.dz', 'Blida',       '4 avenue de l''ALN'),
 (18, 'Hamdi',   'Nour',   'hamdi@mail.dz',   'Sétif',       '17 rue du 8 Mai 1945'),
 (21, 'Khelifi', 'Imane',  'khelifi@mail.dz', 'Béjaïa',      NULL);

INSERT INTO PRODUIT (idProduit, libelle, prix, stock, idCategorie) VALUES
 ('P03', 'Coque de protection', 900.00,   200, 3),
 ('P05', 'Souris sans fil',     2500.00,  70,  4),
 ('P06', 'Câble HDMI',          1200.00,  150, 3),
 ('P09', 'Clavier mécanique',   7500.00,  30,  4),
 ('P11', 'Smartphone Lite',     29900.00, 40,  1),
 ('P12', 'Webcam HD',           6900.00,  35,  4),
 ('P17', 'Smartphone X',        49900.00, 25,  1),
 ('P18', 'Casque Bluetooth',    5900.00,  60,  2),
 ('P20', 'Écouteurs sans fil',  3900.00,  80,  2),
 ('P21', 'Chargeur USB-C',      1900.00,  120, 3),
 ('P25', 'Support ordinateur',  4500.00,  15,  4);

INSERT INTO COMMANDE
  (idCommande, dateCommande, statut, idClient) VALUES
 (1001, '2026-10-02', 'livree',     12),
 (1002, '2026-10-03', 'livree',     12),
 (1003, '2026-10-03', 'expediee',   7),
 (1004, '2026-10-04', 'expediee',   3),
 (1005, '2026-10-05', 'en_attente', 9),
 (1006, '2026-10-06', 'livree',     15),
 (1007, '2026-10-08', 'en_attente', 18),
 (1008, '2026-10-09', 'en_attente', 7),
 (1009, '2026-10-10', 'annulee',    12),
 (1010, '2026-10-12', 'en_attente', 3);

INSERT INTO LIGNE_COMMANDE
  (idCommande, idProduit, quantite, prixVenteApplique) VALUES
 (1001, 'P17', 1, 49900.00), 
 (1001, 'P21', 1, 1900.00),
 (1002, 'P18', 1, 5900.00),
 (1003, 'P09', 1, 7500.00),
 (1003, 'P05', 1, 2500.00),
 (1004, 'P11', 1, 29900.00), 
 (1004, 'P03', 2, 900.00),
 (1005, 'P17', 1, 47900.00),
 (1006, 'P20', 2, 3900.00),  
 (1006, 'P06', 1, 1200.00),
 (1007, 'P12', 1, 6900.00),  
 (1007, 'P05', 2, 2500.00),
 (1008, 'P18', 2, 5900.00),
 (1009, 'P11', 1, 29900.00),
 (1010, 'P21', 3, 1900.00),  
 (1010, 'P03', 1, 900.00);

-- ---------- Chapitre 3 ----------
-- ---------- Mini activité 1 ----------
-- 1. Afficher toutes les colonnes de CLIENT.
/* SELECT * 
FROM CLIENT;

-- 2. Afficher libelle et prix de PRODUIT.
SELECT libelle, prix 
FROM PRODUIT;

-- 3. Afficher chaque ville une seule fois.
SELECT DISTINCT ville 
FROM CLIENT;

-- 4. Afficher nom avec l’alias client.
SELECT nom AS client 
FROM CLIENT;*/

-- ---------- Chapitre 3 ----------
-- ---------- Mini activité 2 ----------
-- 1. Produits dont le prix est supérieur ou égal à 3 000.
/* SELECT * 
FROM PRODUIT 
WHERE prix >= 3000;

-- 2. Produits dont le stock est inférieur à 40.
SELECT * 
FROM PRODUIT 
WHERE stock < 40;

-- 3. Clients de Blida ou d’Oran.
SELECT * 
FROM CLIENT 
WHERE ville IN ('Blida', 'Oran');

-- 4. Clients dont l’adresse n’est pas renseignée.
SELECT * 
FROM CLIENT 
WHERE adresse IS NULL;*/

-- ---------- Chapitre 3 ----------
-- ---------- Mini activité 3 ----------
-- 1. Les trois produits les moins chers.
/*SELECT libelle, prix 
FROM PRODUIT
ORDER BY prix ASC LIMIT 3;

-- 2. Les commandes de la plus récente à la plus ancienne.
SELECT idCommande, dateCommande 
FROM COMMANDE
ORDER BY dateCommande DESC;

-- 3. Pourquoi LIMIT seul ne suffit-il pas pour un « top 3 » ? */

-- ---------- Chapitre 3 ----------
-- ---------- Mini activité 4 ----------
-- 1. Le nombre total de produits.
SELECT COUNT(*) AS nb_produits 
FROM PRODUIT;

-- 2. Le stock total et la valeur du stock (prix × stock).
SELECT SUM(stock) AS stock_total, SUM(prix * stock) AS valeur_stock
FROM PRODUIT;

-- 3. Le nombre de commandes par statut.
SELECT statut, COUNT(*) AS nb
FROM COMMANDE 
GROUP BY statut;

-- 4. Les catégories (idCategorie) qui regroupent au moins 3 produits.
SELECT idCategorie, COUNT(*) AS nb
FROM PRODUIT
GROUP BY idCategorie
HAVING COUNT(*) >= 3;

-- 5. Le nombre de villes différentes où habitent les clients.
SELECT COUNT(DISTINCT ville) AS nb_villes 
FROM CLIENT;