//--------------------------- Drop ---------------------------------

DROP TABLE Hotel CASCADE CONSTRAINTS;
DROP TABLE Consommation CASCADE CONSTRAINTS;
DROP TABLE Sejour CASCADE CONSTRAINTS;
DROP TABLE Client CASCADE CONSTRAINTS;
DROP TABLE Propose_Conso;
DROP TABLE Consomme_Par;

//--------------------------- Create -------------------------------

CREATE TABLE Hotel(
	id INTEGER CONSTRAINT pk_hotel PRIMARY KEY,
	nom VARCHAR2 (30)
);

CREATE TABLE Consommation(
	id INTEGER CONSTRAINT pk_conso PRIMARY KEY,
	designation VARCHAR2 (30),
	volume INTEGER (6)
);

CREATE TABLE Sejour(
	id INTEGER CONSTRAINT pk_sejour PRIMARY KEY,
	duree INTEGER(4),
	date_debut DATE,
	id_client INTEGER,
	id_hotel INTEGER
);

CREATE TABLE Client(
	id INTEGER CONSTRAINT pk_client PRIMARY KEY,
	nom VARCHAR2 (30),
	prenom VARCHAR2 (30),
	adresse VARCHAR2 (100),
	cp NUMBER(5) CHECK(codepost BETWEEN 00001 AND 99999),
	ville VARCHAR2 (30)
);

CREATE TABLE Propose_Conso (
	id_hotel INTEGER,
	id_conso INTEGER,
	tarif NUMBER
);

CREATE TABLE Consomme_Par(
	id_conso INTEGER,
	id_sejour INTEGER,
	quantite NUMBER
);

//--------------------------- Alter --------------------------------

ALTER TABLE Sejour ADD CONSTRAINT fk_sejour1 FOREIGN KEY id_client REFERENCES Client(id);
ALTER TABLE Sejour ADD CONSTRAINT fk_sejour2 FOREIGN KEY id_hotel REFERENCES Hotel(id);

ALTER TABLE Propose_Conso ADD CONSTRAINT fk_propose1 FOREIGN KEY id_hotel REFERENCES Hotel(id);
ALTER TABLE Propose_Conso ADD CONSTRAINT fk_propose2 FOREIGN KEY id_conso REFERENCES Consomation(id);
ALTER TABLE Propose_Conso ADD CONSTRAINT pk_propose PRIMARY KEY (id_hotel, id_conso);

ALTER TABLE Consomme_Par ADD CONSTRAINT fk_consomme1 FOREIGN KEY id_conso REFERENCES Consommation(id);
ALTER TABLE Consomme_Par ADD CONSTRAINT fk_consomme2 FOREIGN KEY id_sejour REFERENCES Sejour(id);
ALTER TABLE Consomme_Par ADD CONSTRAINT pk_consomme PRIMARY KEY (id_conso, id_sejour);
