drop table tournois;
drop table evaluationGrimoire;
drop table lectures;
drop table eleves;
drop table groupes;
drop table grimoires;
drop table typegrimoire;

-----création des tables
-- Table  groupes
create table groupes(
idgroupe number(2,0) constraint pk_groupe primary key,
nomgroupe varchar2(50)not null,
couleur varchar2(30)
);

--- Table eleves
create table eleves (
ideleve number(4,0) GENERATED BY DEFAULT AS IDENTITY,
aliass varchar2(10) unique,
nom varchar2(20) not null,
prenom varchar2(20),
solde number(7,0) default 10000 not null,
nbpoints number(8,0) default 0 not null,
idgroupe number(2,0),
constraint pk_eleve primary key(ideleve),
constraint fkeleve_grp foreign key(idgroupe) references groupes(idgroupe));

-- table typeGrimoire
create table TypeGrimoire(
typeG char(1) constraint pk_type primary key,
description varchar2(50),
prix number(5,0),
points number(4,0)
);
-----
-- Table Grimoires
create table Grimoires (
idgrimoire number(5,0)GENERATED BY DEFAULT AS IDENTITY start with 100 increment by 1,
titre varchar2(50)not null,
CONSTRAINT pk_grimoire primary key(idgrimoire),
typeG char(1), constraint fk_type foreign key (typeG) references TypeGrimoire(typeG)
);
---
--Table lectures
create table Lectures (
idgrimoire number(5,0),
ideleve number(4,0),
constraint fk_eleve_gri foreign key(ideleve) references eleves(ideleve),
constraint fk_gri_eleve foreign key(idgrimoire) references grimoires(idgrimoire),
constraint pk_lectures primary key (idgrimoire,ideleve));

----------
--Table evaluationGrimoire
create table evaluationGrimoire (
idgrimoire number(5,0),
ideleve number(4,0),
commentaire varchar2(40),
nbetoiles number(1,0),
constraint fk_evaluation1 foreign key(ideleve,idgrimoire) references lectures(ideleve,idgrimoire),
constraint pk_Evaluation primary key (idgrimoire,ideleve),
constraint ck_nbetoile check (nbetoiles >=1 and nbetoiles<=5));
----
--Table Tournois
create table tournois(
idTournoi number(6,0) GENERATED BY DEFAULT AS IDENTITY constraint pk_tournois primary key,
ideleve1 number(4,0),
ideleve2 number(4,0),
nbPointsE1 number(2,0) constraint ck_nbpointsE1 check (nbPointsE1>=0 and nbPointsE1<=5),
nbPointsE2 number(2,0),constraint ck_nbpointsE2 check (nbPointsE2>=0 and nbPointsE2<=5),
constraint fk_tournoi foreign key(ideleve1) references eleves(ideleve),
constraint fk_tournoi2 foreign key(ideleve2) references eleves(ideleve));

-----------------------------------------------------------

------------------INSERTIONS

-----insertion dans groupes
insert into groupes values (1,'les poussins','jaune');
insert into groupes values (2,'les renards','rouge');
insert into groupes values (3,'les chevaux','vert');
insert into groupes values (4,'les tortues','orange');
insert into groupes values (5,'félins','bleu');


---------------Insertion dans eleves
insert into eleves (aliass,nom, prenom,idgroupe) values('Kiwi','Lépine','Fred',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Chubaka','Clément','Sylvain',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('TiGris','Courtey','Rosa',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Patoche','Loroy','Samuel',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Primogene','Lebeau','Yanick',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Panthère','Lavoie','Linna',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Fantomas','Fafar','Kévin',2);
insert into eleves (aliass,nom, prenom,idgroupe) values('Féroce','Bien','Raphaël',2);
insert into eleves (aliass,nom, prenom,idgroupe) values('R2D2','O''Brian','Mathieu',2);
insert into eleves (aliass,nom, prenom,idgroupe) values('PiwiPiwi','Deblanc','Racha',2);
insert into eleves (aliass,nom, prenom,idgroupe) values('Gribouille','Lapointe','Vanessa',3);
insert into eleves (aliass,nom, prenom,idgroupe) values('Barakuda','Viens','Louis',3);
insert into eleves (aliass,nom, prenom,idgroupe) values('Cacao','Laforet','Louise',3);
insert into eleves (aliass,nom, prenom,idgroupe) values('Karamba','Simpson','Bart',3);
insert into eleves (aliass,nom, prenom,idgroupe) values('Malin','Gagnon','Nicolas',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Lefou','Desjardins','Sebastien',1);
insert into eleves (aliass,nom, prenom,idgroupe) values('Zola','Valere','Nadine',null);
insert into eleves (aliass,nom, prenom,idgroupe) values('Spok','Jordache','Peter',null);


--------Insertion dans typegrimoire
insert into typegrimoire values ('E', 'Eau',70,50);
insert into typegrimoire values ('F', 'Feu',100,100);
insert into typegrimoire values ('T', 'Terre',80,80);
insert into typegrimoire values ('A', 'Air',50,50);
insert into typegrimoire values ('S', 'Sort',150,200);

-------------Insertion dans Grimoires
insert into grimoires (titre,typeG) values('Se déplacer sur l''eau','E');
insert into grimoires (titre,typeG) values('Contrôler les rivières','E');
insert into grimoires (titre,typeG) values('Marcher dans le feu','F');
insert into grimoires (titre,typeG) values('Parler aux arbres','T');
insert into grimoires (titre,typeG) values('Parler aux animaux','T');
insert into grimoires (titre,typeG) values('Contrôler la braise','F');
insert into grimoires (titre,typeG) values('Vite comme le vent','A');
insert into grimoires (titre,typeG) values('Apprivoiser des créatures','T');
insert into grimoires (titre,typeG) values('Contrôler les tornades','A');
insert into grimoires (titre,typeG) values('Voler avec les oiseaux','T');
insert into grimoires (titre,typeG) values('Le grimoire de Zéro','S');

--------- insertion dans Lectures
insert into lectures values(100,1);
insert into lectures values(100,2);
insert into lectures values(100,3);
insert into lectures values(100,4);
insert into lectures values(100,15);
insert into lectures values(101,1);
insert into lectures values(101,2);
insert into lectures values(101,3);
insert into lectures values(102,6);
insert into lectures values(102,15);
insert into lectures values(102,10);
insert into lectures values(102,13);
insert into lectures values(108,3);
insert into lectures values(100,11);
insert into lectures values(102,11);
insert into lectures values(108,5);
insert into lectures values(105,3);
insert into lectures values(102,3);
insert into lectures values (108,2);

----Insertion dans evaluationgrimoire
insert into evaluationgrimoire values (102,6,'Excellent grimoire. Je recommande',5);
insert into evaluationgrimoire values (102,13,'un peu difficile à lire, pas pour moi',3);
insert into evaluationgrimoire values (102,10,'Va droit au but. Très bien',4);
insert into evaluationgrimoire values (102,11,'Complet. Clair. Je recommande',4);
insert into evaluationgrimoire values (102,15,'Parfait. Je recommande',5);
insert into evaluationgrimoire values (100,1,'Difficile à comprendre. Je recommande',3);
insert into evaluationgrimoire values (100,2,'Pas facile, mais intéressant' ,4);
insert into evaluationgrimoire values (100,3,'Un peu difficile, mais wow',5);
insert into evaluationgrimoire values (102,3,'Vraiment pas évident.je me suis brulé',2);
insert into evaluationgrimoire values (105,3,'difficile, mais ça va ',3);
insert into evaluationgrimoire values (101,1,'Excellent livre',5);
insert into evaluationgrimoire values (101,2,'très moyen',3);
insert into evaluationgrimoire values (101,3,'Très bien expliqué',4);
insert into evaluationgrimoire values (108,2,'Pas facile à cpmprendre',2);
----
--Insertion dans tournois
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (1,7,3,2);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (2,8,4,1);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (3,11,3,2);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (4,12,3,2);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (7,1,4,1);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (8,2,4,1);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (11,3,1,4);
insert into tournois (ideleve1,ideleve2,nbPointsE1,nbPointsE2) values (12,4,2,3);

commit;