--Permet de supprimer toutes les tables avant de les recreer (à mettre en commentaire à la première utilisation)

drop table LesTickets;
drop table LesDossiers;
drop table LesRepresentations;
drop table LesSpectacles;
drop table LesPlaces;
drop table LesZones;
drop table LesCategories;

--creation des tables
create table LesCategories(
	nomC varchar(10),
	prix number(2),
	constraint LesCategories_pk primary key (nomC),
	constraint LesCategories_ck check (prix > 0));

create table LesZones(
	numZ number(1),
	nomC varchar(10),
	constraint LesZones_pk primary key (numZ),
	constraint LesZones_fk foreign key (nomC) references LesCategories(nomC),
	constraint LesZones_ck check (numZ > 0));

create table LesPlaces(
	noPlace number(2),
	noRang number(2),
	numZ number(1),
	constraint LesPlaces_pk primary key (noPlace,noRang),
	constraint LesPlaces_fk foreign key (numZ) references LesZones(numZ),
	constraint LesPlaces_ck1 check (noPlace > 0),
	constraint LesPlaces_ck2 check (noRang > 0),
	constraint LesPlaces_ck3 check (numZ > 0));

create table LesSpectacles(
	numS number(3),
	nomS varchar(20),
	constraint LesSpectacles_pk primary key (numS),
	constraint LesSpectacles_ck check (numS > 0));


create table LesRepresentations(
	numS number(3),
	dateRep date,
	constraint LesRepresentations_pk primary key (numS,dateRep),
	constraint LesRepresentations_fk foreign key (numS) references LesSpectacles(numS),
	constraint LesRepresentations_ck check (numS > 0));

create table LesDossiers(
	noDossier number(3),
	montant number(4),
	constraint LesDossiers_pk primary key (noDossier),
	constraint LesDossiers_ck1 check (noDossier > 0),
	constraint LesDossiers_ck2 check (montant > 0));

create table LesTickets(
	noSerie number(4),
	numS number(3),
	dateRep date,
	noPlace number(2),
	noRang number(2),
	dateEmission date,
	noDossier number(3),
	constraint LesTickets_pk primary key (noSerie,numS,dateRep,noPlace,noRang),
	constraint LesTickets_fk1 foreign key (numS,dateRep) references LesRepresentations(numS,dateRep)
	on delete cascade,
	constraint LesTickets_fk2 foreign key (noPlace,noRang) references LesPlaces(noPlace,noRang)
	on delete cascade,
	constraint LesTickets_fk3 foreign key (noDossier) references LesDossiers(noDossier)
	on delete cascade,
	constraint LesTickets_ck1 check (dateEMission < dateRep),
	constraint LesTickets_ck2 check (noSerie > 0),
	constraint LesTickets_ck3 check (numS > 0),
	constraint LesTickets_ck4 check (noPlace > 0),
	constraint LesTickets_ck5 check (noRang > 0),
	constraint LesTickets_ck6 check (noDossier > 0));

--remplissage des tables
insert into LesCategories
	select NOMC,PRIX
	from theatre.LesCategories;

insert into LesZones
	select NOZONE,NOMC
	from theatre.LesZones;

insert into LesPlaces
	select NOPLACE,NORANG,NOZONE
	from theatre.LesSieges;

insert into LesSpectacles
	select NOSPEC,NOMS
	from theatre.LesSpectacles;

insert into LesRepresentations
	select NOSPEC,DATEREP
	from theatre.LesRepresentations;

insert into LesDossiers
	select NODOSSIER,sum(prix)as MONTANT
	from theatre.LesTickets natural join theatre.LesSieges natural join theatre.LesZones natural join theatre.LesCategories
	group by NODOSSIER;

insert into LesTickets
	select NOSERIE,R.NUMS,R.DATEREP,NOPLACE,NORANG,DATEEMISSION,NODOSSIER
	from theatre.LesTickets T join LesRepresentations R on (T.NOSPEC = R.NUMS and T.DATEREP = R.DATEREP);
--On exclut les 20 representations qui on ete annulees le 22-FEB-17 pour respecter la contrainte d'integrite