/* DDL en SQL standard de création des tables de eVentes */
create table CATEGORIE (
     id numeric(10) not null,
     libelle varchar(255) not null,
	 vers numeric(10, 0) default 0 not null,
     constraint IDCATEGORIE primary key (id),
     constraint CatLibelleUnique unique (libelle));

create table CLIENT (
     id numeric(10) not null,
     identifiant varchar(10) not null,
     password varchar(10) not null,
     mail varchar(100) not null,
     nom varchar(100) not null,
     prenom varchar(100) not null,
     actif char not null,
	 vers numeric(10, 0) default 0 not null,
     constraint IDCLIENT primary key (id),
     constraint CliIdentifiantUnique unique (identifiant),
     constraint CliMailUnique unique (mail));

create table COMMANDE (
     id numeric(10) not null,
     dat date not null,
     client numeric(10) not null,
     montantTotal numeric(12,2) not null,
     traitee char not null,
     constraint IDCOMMANDE_ID primary key (id));

create table LIGNECOMMANDE (
     commande numeric(10) not null,
     ligneNo numeric(3) not null,
     produit numeric(10) not null,
     prix numeric(8,2) not null,
     quantite numeric(3) not null,
     constraint IDLIGNECOMMANDE primary key (commande, ligneNo),
     constraint LigCmdCmdLigNoUnique unique (commande, produit));

create table MARQUE (
     id numeric(10) not null,
     libelle varchar(255) not null,
     site varchar(200),
	 vers numeric(10, 0) default 0 not null,
     constraint IDMARQUE primary key (id),
     constraint MqLibelleUnique unique (libelle));

create table PARAMETRES (
     PathPhotos varchar(255) not null);

create table PRODUIT (
     id numeric(10) not null,
     libelle varchar(255) not null,
     prix numeric(8,2) not null,
     disponible char not null,
     photoDisponible char not null,
     marque numeric(10) not null,
     rayon numeric(10) not null,
     prixPromotion numeric(8,2),
	 vers numeric(10, 0) default 0 not null,
     constraint IDPRODUIT primary key (id));

create table RAYON (
     id numeric(10) not null,
     libelle varchar(255) not null,
     categorie numeric(10) not null,
	 vers numeric(10, 0) default 0 not null,
     constraint IDRAYON primary key (id),
     constraint IDRAYON_1 unique (libelle));

create table SEQUENCES (
     id varchar(50) not null,
     valeur numeric(10) not null,
     constraint IDSEQUENCE primary key (id));


-- Constraints Section
-- ___________________ 

alter table COMMANDE add constraint ClientDeLaCommande
     foreign key (client)
     references CLIENT;

/*alter table COMMANDE add constraint IDCOMMANDE_CHK
     check(exists(select * from LIGNECOMMANDE
                  where LIGNECOMMANDE.commande = id)); */

alter table LIGNECOMMANDE add constraint CommandeDeLaLigne
     foreign key (commande)
     references COMMANDE;

alter table LIGNECOMMANDE add constraint QuantiteNonNulle
	Check (prix>0);
	
alter table LIGNECOMMANDE add constraint ProduitCommande
     foreign key (produit)
     references PRODUIT;

alter table PRODUIT add constraint MarqueDuProduit
     foreign key (marque)
     references MARQUE;

alter table PRODUIT add constraint RayonDuProduit
     foreign key (rayon)
     references RAYON;
	 
alter table PRODUIT add constraint PrixPromotionPositif
	check(prixPromotion is null OR prixPromotion>=0);

alter table PRODUIT add constraint PrixPositif
	check(Prix>=0);
	
alter table RAYON add constraint CategorieDuRayon
     foreign key (categorie)
     references CATEGORIE;

--Vues
--------

Create View CategorieAvecRayon as
SELECT Categorie.id, Categorie.libelle, Categorie.vers, Rayon.id AS rayId, Rayon.libelle AS rayLib, Rayon.vers AS RayVer
FROM Categorie LEFT JOIN Rayon ON Categorie.id = Rayon.categorie;

Create View produitVue as
SELECT Produit.id, Produit.Libelle, Produit.prix, Produit.rayon AS rayid, Produit.marque AS mqId, Produit.prixPromotion, 
	Produit.disponible, Produit.photoDisponible, Produit.vers, Marque.libelle AS mqLib, Marque.site AS mqSite, Marque.vers AS mqVersion, 
	Rayon.libelle AS rayLib, Rayon.vers AS rayVersion, Rayon.categorie AS catId, Categorie.libelle As catLib, Categorie.vers As catVer
FROM Rayon INNER JOIN (Marque INNER JOIN Produit ON Marque.id = Produit.marque) ON Rayon.id = Produit.rayon INNER JOIN Categorie on rayon.categorie=categorie.id;

Create view LigneCommandePrd as
SELECT LigneCommande.commande, LigneCommande.ligneNo, LigneCommande.produit, LigneCommande.prix, LigneCommande.quantite, Marque.libelle AS MqLib, Produit.Libelle AS PrdLib
FROM (Marque INNER JOIN Produit ON Marque.id = Produit.marque) INNER JOIN LigneCommande ON Produit.id = LigneCommande.produit;