CREATE TABLE  "ABONNEMENTS"
   (	"ID_ABON" NUMBER,
	"NOM" VARCHAR2(255),
	"PRIX" VARCHAR2(255) NOT NULL ENABLE,
	"TYPE" VARCHAR2(255),
	 CONSTRAINT "PK_ABON" PRIMARY KEY ("ID_ABON") ENABLE
   );

CREATE SEQUENCE abon_sequence;




CREATE TABLE  "ASSURANCE"
   (	"ID_ASSURANCE" NUMBER,
	"NOM_COMP" VARCHAR2(255),
	"TYPE" NUMBER(*,0),
	"PRIX" NUMBER,
	 CONSTRAINT "PK_ASSURANCE" PRIMARY KEY ("ID_ASSURANCE") ENABLE
   );

CREATE SEQUENCE assurance_sequence;

CREATE TABLE  "CLIENTS"
   (	"ID_CLIENT" NUMBER,
	"NOM" VARCHAR2(255),
	"PRE" VARCHAR2(255),
	"DOB" VARCHAR2(255),
	"SEXE" VARCHAR2(255),
	"TEL" VARCHAR2(255),
	"EMAIL" VARCHAR2(255),
	"OCC" VARCHAR2(255),
	"ADRESSE" VARCHAR2(255),
	"CIN" VARCHAR2(255),
	"ID_SALLE" NUMBER,
	"RES_DATE" VARCHAR2(255),
	"ABONNEMENT" NUMBER,
	 CONSTRAINT "PK_CLIENTS" PRIMARY KEY ("ID_CLIENT") ENABLE,
	 CONSTRAINT "FK_CLIENTS_SALLES" FOREIGN KEY ("ID_SALLE")
	  REFERENCES  "SALLES" ("ID_SALLE") ENABLE,
	 CONSTRAINT "FK_CLIENT_ABONNEMENTS" FOREIGN KEY ("ABONNEMENT")
	  REFERENCES  "ABONNEMENTS" ("ID_ABON") ENABLE
   );

CREATE SEQUENCE clients_sequence;


CREATE TABLE  "CLIENT_OCCUPATIONS"
   (	"ID_OCC" NUMBER,
	"NOM_OCC" VARCHAR2(255),
	"AMOUNT" VARCHAR2(255),
	 CONSTRAINT "PK_CLIENT_OCCUPATIONS" PRIMARY KEY ("ID_OCC") ENABLE
   );

CREATE SEQUENCE occ_sequence;

CREATE TABLE  "FORMULAIRES"
   (	"ID_FORM" NUMBER,
	"NOM_C" VARCHAR2(255),
	"DATE_F" VARCHAR2(255),
	"QUEST1" NUMBER DEFAULT 0,
	"QUEST2" NUMBER DEFAULT 0,
	"QUEST3" NUMBER DEFAULT 0,
	"QUEST4" NUMBER DEFAULT 0,
	"QUEST5" NUMBER DEFAULT 0,
	"QUEST6" NUMBER DEFAULT 0,
	"COMMENTAIRE" VARCHAR2(255),
	 CONSTRAINT "PK_FORMULAIRES" PRIMARY KEY ("ID_FORM") ENABLE
   );

CREATE SEQUENCE formulaires_sequence;





CREATE TABLE  "HISTORIQUE"
   (	"ID_HISTORIQUE" NUMBER,
	"NOM" VARCHAR2(255),
	"PRENOM" VARCHAR2(255),
	"CIN" VARCHAR2(255),
	"QUANT" NUMBER,
	"FID" NUMBER,
	"ID_PROD" NUMBER,
	 CONSTRAINT "PK_HISTORIQUE" PRIMARY KEY ("ID_HISTORIQUE") ENABLE,
	 CONSTRAINT "FK_PRODUITS_HISTORIQUE" FOREIGN KEY ("ID_PROD")
	  REFERENCES  "PRODUITS" ("ID") ENABLE
   );

CREATE SEQUENCE hist_sequence;







CREATE TABLE  "NOTIFICATIONS"
   (	"ID_NOTIF" NUMBER,
	"TEXT" VARCHAR2(1000),
	"TABLECOMPTES" VARCHAR2(2000),
	 CONSTRAINT "PK_NOTIFICATIONS" PRIMARY KEY ("ID_NOTIF") ENABLE
   );

CREATE SEQUENCE notifications_sequence;

CREATE TABLE  "PARTENARIATS"
   (	"ID_PART" NUMBER,
	"NOM" VARCHAR2(255),
	"PROD" VARCHAR2(255),
	"PRIX" NUMBER,
	 CONSTRAINT "PK_PARTENARIATS" PRIMARY KEY ("ID_PART") ENABLE
   );

CREATE SEQUENCE part_sequence;

CREATE TABLE  "PERSONNELS"
   (	"ID_PERSONNELS" NUMBER,
	"PRENOM" VARCHAR2(255),
	"NOM" VARCHAR2(255),
	"DOB" VARCHAR2(255),
	"SEXE" VARCHAR2(255),
	"EMAIL" VARCHAR2(255),
	"SPEC" VARCHAR2(255),
	"TEL" VARCHAR2(255),
	"USERNAME" VARCHAR2(255),
	"MDP" VARCHAR2(255),
	"ID_SALLE" NUMBER,
	 CONSTRAINT "CHECK_PERSONNELS_EMAIL" CHECK (email LIKE '%@%.%') ENABLE,
	 CONSTRAINT "PK_PERSONNELS" PRIMARY KEY ("ID_PERSONNELS") ENABLE,
	 CONSTRAINT "FK_PERSONNELS_SALLES" FOREIGN KEY ("ID_SALLE")
	  REFERENCES  "SALLES" ("ID_SALLE") ENABLE
   );

CREATE SEQUENCE personnels_sequence;


CREATE TABLE  "PRODUITS"
   (	"ID" NUMBER,
	"NOM" VARCHAR2(255),
	"QUANT" NUMBER,
	"PRIX" NUMBER,
	 CONSTRAINT "PK_PRODUITS" PRIMARY KEY ("ID") ENABLE
   );

CREATE SEQUENCE produits_sequence;


CREATE TABLE  "PROD_STAT"
   (	"ID_PROD" NUMBER,
	"NOM_PROD" VARCHAR2(255),
	"AMOUNT" NUMBER,
	 CONSTRAINT "PK_PROD_STAT" PRIMARY KEY ("ID_PROD") ENABLE
   );

CREATE SEQUENCE prod_stat_sequence;


CREATE TABLE  "PROMOTIONS"
   (	"ID_PROMO" NUMBER,
	"NOM" VARCHAR2(255),
	"TYPE" NUMBER,
	 CONSTRAINT "PK_PROMOTIONS" PRIMARY KEY ("ID_PROMO") ENABLE
   );

CREATE SEQUENCE promotions_sequence;

CREATE TABLE  "RECLAMATIONS"
   (	"ID_RECLAMATION" NUMBER,
	"ID_SALLE" NUMBER DEFAULT '0',
	"ID_SERVICE" NUMBER DEFAULT '0',
	"ID_PRODUIT" NUMBER DEFAULT '0',
	"ID_STAFF" NUMBER DEFAULT '0',
	"COMMENTAIRE" VARCHAR2(255),
	"NOM_CLIENT" VARCHAR2(4000),
	"REC_DATE" VARCHAR2(255),
	 CONSTRAINT "PK_RECLAMATIONS" PRIMARY KEY ("ID_RECLAMATION") ENABLE,
	 CONSTRAINT "FK_RECLAMATIONS_SALLES" FOREIGN KEY ("ID_SALLE")
	  REFERENCES  "SALLES" ("ID_SALLE") ENABLE,
	 CONSTRAINT "FK_RECLAMATIONS_SERVICES" FOREIGN KEY ("ID_SERVICE")
	  REFERENCES  "SERVICES" ("ID_SERVICE") ENABLE,
	 CONSTRAINT "FK_RECLAMATIONS_PRODUITS" FOREIGN KEY ("ID_PRODUIT")
	  REFERENCES  "PRODUITS" ("ID") ENABLE,
	 CONSTRAINT "FK_RECLAMATIONS_PERSONNELS" FOREIGN KEY ("ID_STAFF")
	  REFERENCES  "PERSONNELS" ("ID_PERSONNELS") ENABLE
   );

CREATE SEQUENCE reclamations_sequence;

CREATE TABLE  "SALLES"
   (	"ID_SALLE" NUMBER,
	"NOM" VARCHAR2(255),
	"RUE" VARCHAR2(255),
	"VILLE" VARCHAR2(255),
	"CODE_POSTAL" NUMBER,
	"TELEPHONE" VARCHAR2(255),
	"EMAIL" VARCHAR2(255),
	"DEB_HR" VARCHAR2(255),
	"FIN_HR" VARCHAR2(255),
	"AMOUNT" NUMBER,
	 CONSTRAINT "CHECK_EMAIL" CHECK (email LIKE '%@%.%') ENABLE,
	 CONSTRAINT "PK_SALLES" PRIMARY KEY ("ID_SALLE") ENABLE
   );

CREATE SEQUENCE salles_sequence;

CREATE TABLE  "SERVICES"
   (	"ID_SERVICE" NUMBER,
	"NOM" VARCHAR2(255),
	"PRIX" NUMBER,
	"ID_SALLE" NUMBER,
	 CONSTRAINT "PK_SERVICES" PRIMARY KEY ("ID_SERVICE") ENABLE,
	 CONSTRAINT "FK_SERVICES_SALLES" FOREIGN KEY ("ID_SALLE")
	  REFERENCES  "SALLES" ("ID_SALLE") ENABLE
   );

CREATE SEQUENCE services_sequence;

CREATE TABLE  "SPECIALITES"
   (	"ID_SPEC" NUMBER(*,0),
	"NOM_SPEC" VARCHAR2(255),
	"AMOUNT" NUMBER(*,0),
	 CONSTRAINT "PK_SPECIALITES" PRIMARY KEY ("ID_SPEC") ENABLE
   );

CREATE SEQUENCE spec_sequence;


CREATE TABLE  "STAFF"
   (	"USERNAME" VARCHAR2(255),
	"PASSWORD" VARCHAR2(255),
	 CONSTRAINT "PK_USERNAME" PRIMARY KEY ("USERNAME") ENABLE
   );



CREATE OR REPLACE TRIGGER  "ABON_ON_INSERT"
  BEFORE INSERT ON abonnements
  FOR EACH ROW
BEGIN
  SELECT abon_sequence.nextval
  INTO :new.id_abon
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "ASSURANCE_ON_INSERT"
  BEFORE INSERT ON assurance
  FOR EACH ROW
BEGIN
  SELECT assurance_sequence.nextval
  INTO :new.id_assurance
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "CLIENTS_ON_INSERT"
  BEFORE INSERT ON clients
  FOR EACH ROW
BEGIN
  SELECT clients_sequence.nextval
  INTO :new.id_client
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "OCC_ON_INSERT"
  BEFORE INSERT ON client_occupations
  FOR EACH ROW
BEGIN
  SELECT occ_sequence.nextval
  INTO :new.id_occ
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "FORM_ON_INSERT"
  BEFORE INSERT ON formulaires
  FOR EACH ROW
BEGIN
  SELECT formulaires_sequence.nextval
  INTO :new.id_form
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "HIST_ON_INSERT"
  BEFORE INSERT ON historique
  FOR EACH ROW
BEGIN
  SELECT hist_sequence.nextval
  INTO :new.id_historique
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "NOTIF_ON_INSERT"
  BEFORE INSERT ON notifications
  FOR EACH ROW
BEGIN
  SELECT notifications_sequence.nextval
  INTO :new.id_notif
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "PART_ON_INSERT"
  BEFORE INSERT ON partenariats
  FOR EACH ROW
BEGIN
  SELECT part_sequence.nextval
  INTO :new.id_part
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "PERSONNELS_ON_INSERT"
  BEFORE INSERT ON personnels
  FOR EACH ROW
BEGIN
  SELECT personnels_sequence.nextval
  INTO :new.id_personnels
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "PRODUITS_ON_INSERT"
  BEFORE INSERT ON produits
  FOR EACH ROW
BEGIN
  SELECT produits_sequence.nextval
  INTO :new.id
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "PRODSTAT_ON_INSERT"
  BEFORE INSERT ON prod_stat
  FOR EACH ROW
BEGIN
  SELECT prod_stat_sequence.nextval
  INTO :new.id_prod
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "PROM_ON_INSERT"
  BEFORE INSERT ON promotions
  FOR EACH ROW
BEGIN
  SELECT promotions_sequence.nextval
  INTO :new.id_promo
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "BOOKS_ON_INSERT"
  BEFORE INSERT ON reclamations
  FOR EACH ROW
BEGIN
  SELECT reclamations_sequence.nextval
  INTO :new.id_reclamation
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "SALLES_ON_INSERT"
 BEFORE INSERT ON salles
 FOR EACH ROW
BEGIN
 SELECT salles_sequence.nextval
 INTO :new.id_salle
 FROM dual;
END;
CREATE OR REPLACE TRIGGER  "SERVICES_ON_INSERT"
  BEFORE INSERT ON services
  FOR EACH ROW
BEGIN
  SELECT services_sequence.nextval
  INTO :new.id_service
  FROM dual;
END;
CREATE OR REPLACE TRIGGER  "SPEC_ON_INSERT"
  BEFORE INSERT ON specialites
  FOR EACH ROW
BEGIN
  SELECT spec_sequence.nextval
  INTO :new.id_spec
  FROM dual;
END;