-- 1) Créer une BD vide
DROP DATABASE projetJava;
CREATE DATABASE projetJava;
USE projetJava;

-- 2) Définir ses tables
CREATE TABLE t_clients
(
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 nom VARCHAR (30) NOT NULL,
 prenom VARCHAR(30) NOT NULL,
 adresse VARCHAR(30) NOT NULL,
 postal VARCHAR(6) NOT NULL,
 ville VARCHAR(30) NOT NULL,
 email VARCHAR(50)
)ENGINE=INNODB;

CREATE TABLE t_produits
(
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 nom VARCHAR (30) NOT NULL,
 poids NUMERIC (6,2) NOT NULL,
 prix NUMERIC (6,2) NOT NULL
)ENGINE=INNODB;

CREATE TABLE t_commandes
(
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 passation DATETIME NOT NULL,
 client INT UNSIGNED NOT NULL,
 produit INT UNSIGNED NOT NULL,
 quantite INT UNSIGNED NOT NULL,
 prix NUMERIC (6,2) NOT NULL,
 CONSTRAINT commCli FOREIGN KEY (client) REFERENCES t_clients(id),
 CONSTRAINT commProd FOREIGN KEY (produit) REFERENCES t_produits (id)
)ENGINE=INNODB;

-- 3) Concevoir les vues sur les tables
CREATE VIEW v_commandes AS 
 SELECT passation, t_clients.nom AS cliNom, prenom, quantite, t_produits.nom AS produit, t_commandes.prix AS prix, t_commandes.prix * quantite AS total
 FROM t_commandes
 LEFT JOIN t_clients ON t_commandes.client=t_clients.id
 LEFT JOIN t_produits ON t_commandes.produit=t_produits.id
 ORDER BY t_commandes.passation;
 
CREATE VIEW v_totCli AS 
 SELECT t_clients.Id as idClient, t_clients.nom AS cliNom, prenom, sum(t_commandes.prix * quantite) AS total
 FROM t_commandes
 LEFT JOIN t_clients ON t_commandes.client=t_clients.id
 GROUP BY t_clients.id; 

SHOW TABLES;

-- 4) Remplir les tables avec des données de test.
INSERT INTO t_clients VALUES
(NULL, 'Dupont', 'Louisa', 'Rue des Muguets, 120', '6543', 'Montignies le Tilleul', NULL),
(NULL, 'Lalieux', 'Béatrice', 'Rue Trieu Kaisin, 134', '6061', 'Montignies sur Sambre', 'bea.lalieux@hotmail.com'),
(NULL, 'Lefèbvre', 'Etienne', 'Rue des Prairies, 71', '6041', 'Gosselies', 'etienne.lefebvre@gmail.com'),
(NULL, 'Moucheron', 'Roger', 'Rue des Lilas, 36', '6238', 'Liberchies', 'roger.lamouche@monmail.be')
;

INSERT INTO t_produits VALUES
(NULL, 'Terreau', 25, 9.50),
(NULL, 'Sable', 50, 5.75),
(NULL, 'Ciment', 40, 12.25),
(NULL, 'Gravier', 15, 5.50)
;

INSERT INTO t_commandes VALUES
(NULL, '20130215103520', 1, 3, 2, 12.15),
(NULL, '20130310103545', 1, 2, 3, 5.52),
(NULL, '20130304', 1, 3, 1, 12.15),
(NULL, '20130402102745', 3, 4, 7, 5.08)
;

-- 5) Eventuellement, les afficher en fin de script pour les contrôler.
SELECT * FROM t_clients;
SELECT * FROM t_produits;
SELECT * FROM t_commandes;

-- 6) Puis, voir si les vues fonctionnent bien.
SELECT * FROM v_commandes;
SELECT * FROM v_totCli;

