/* Drop Triggers ********************************************************************************************/
DROP TRIGGER IF EXISTS "checkPrimaryKeyTableLivreTraduit" ON TP01_LIVRETRADUIT;
DROP TRIGGER IF EXISTS "checkPrimarykeyTableLivreOriginal" ON TP01_LIVREORIGINAL;
DROP TRIGGER IF EXISTS "checkPrimaryKeyTableLivre" ON TP01_LIVRE;

DROP TRIGGER IF EXISTS "checkAbstractTableLivre" ON TP01_LIVRE;

DROP FUNCTION IF EXISTS "checkPrimaryKeyLivre"();




DROP TRIGGER IF EXISTS "checkPrimaryKeyTableAbonne" ON TP01_ABONNE;
DROP TRIGGER IF EXISTS "checkPrimarykeyTableHabitant" ON TP01_HABITANT;
DROP TRIGGER IF EXISTS "checkPrimaryKeyTablePersonnePhysique" ON TP01_PERSONNEPHYSIQUE;

DROP FUNCTION IF EXISTS "checkPrimaryKeyPersonnePhysique"();




DROP FUNCTION IF EXISTS "checkAbstractTable"();
/* Drop Triggers ********************************************************************************************/





/* Drop Tables **********************************************************************************************/
DROP TABLE If EXISTS TP01_ABONNEMENT;
DROP TABLE If EXISTS TP01_BIBLIOTHEQUE;
DROP TABLE If EXISTS TP01_EMPRUNT;
DROP TABLE If EXISTS TP01_EXEMPLAIRE;
DROP TABLE If EXISTS TP01_CARACTERISATION;
DROP TABLE If EXISTS TP01_MOT_CLEF;
DROP TABLE If EXISTS TP01_LIVRETRADUIT;
DROP TABLE If EXISTS TP01_LIVREORIGINAL;
DROP TABLE If EXISTS TP01_LIVRE;
DROP TABLE If EXISTS TP01_DOMICILIATION;
DROP TABLE If EXISTS TP01_VILLE;
DROP TABLE If EXISTS TP01_ABONNE;
DROP TABLE If EXISTS TP01_HABITANT;
DROP TABLE If EXISTS TP01_PERSONNEPHYSIQUE;
/* Drop Tables **********************************************************************************************/














/* Hiérarchie Personne Physique *****************************************************************************/
CREATE TABLE TP01_PERSONNEPHYSIQUE(
	IDPERSONNEPHYSIQUE INTEGER NOT NULL,
	NOM VARCHAR(20) NOT NULL,
	PRENOM VARCHAR(20),
	AGE INTEGER,
--	CONSTRAINT TP01_PERSONNEPHYSIQUE_PK PRIMARY KEY (IDPERSONNEPHYSIQUE),
	CONSTRAINT TP01_AGE_DK CHECK (AGE BETWEEN 0 AND 120)
);





CREATE TABLE TP01_HABITANT(
) INHERITS(TP01_PERSONNEPHYSIQUE);





CREATE TABLE TP01_ABONNE(
) INHERITS(TP01_HABITANT);
/* Hiérarchie Personne Physique *****************************************************************************/










/* Ville ****************************************************************************************************/
CREATE TABLE TP01_VILLE(
	IDVILLE SERIAL,
	NOM VARCHAR(20) NOT NULL,
	CONSTRAINT TP01_VILLE_PK PRIMARY KEY (IDVILLE)
);





CREATE TABLE TP01_DOMICILIATION(
	IDVILLE INTEGER,
	IDPERSONNEPHYSIQUE INTEGER,
	CONSTRAINT TP01_DOMICILIATION_PK PRIMARY KEY (IDVILLE, IDPERSONNEPHYSIQUE),
	CONSTRAINT TP01_DOMICILIATIONTOVILLE_FK FOREIGN KEY (IDVILLE) REFERENCES TP01_VILLE(IDVILLE)
--	Remplacer cette contrainte par un trigger
--	CONSTRAINT TP01_DOMICILIATIONTOHABITANT_FK FOREIGN KEY (IDPERSONNEPHYSIQUE) REFERENCES TP01_HABITANT(IDPERSONNEPHYSIQUE)
);
/* Ville ****************************************************************************************************/










/* Hiérarchie Livre *****************************************************************************************/
CREATE TABLE TP01_LIVRE(
	IDLIVRE INTEGER NOT NULL,
	ISBN VARCHAR(20),
	TITRE VARCHAR(50) NOT NULL,
	LANGUE VARCHAR(15) CHECK (LANGUE IN ('FR', 'BR', 'SP'))
--	CONSTRAINT TP01_LIVRE_PK PRIMARY KEY (IDLIVRE)
);





CREATE TABLE TP01_LIVREORIGINAL(
	SIECLE INTEGER CHECK (SIECLE BETWEEN 0 AND 21),
	CATEGORIE VARCHAR(20)
) INHERITS(TP01_LIVRE);





CREATE TABLE TP01_LIVRETRADUIT(
	IDLIVREORIGINAL INTEGER
) INHERITS(TP01_LIVRE);
/* Hiérarchie Livre *****************************************************************************************/










/* Mot clé **************************************************************************************************/
CREATE TABLE TP01_MOT_CLEF(
	IDMOT SERIAL,
	MOT VARCHAR(20),
	CONSTRAINT TP01_MOT_CLEF_PK PRIMARY KEY (IDMOT)
);





CREATE TABLE TP01_CARACTERISATION(
	IDLIVRE INTEGER,
	IDMOT INTEGER,
	CONSTRAINT TP01_CARACT_PK PRIMARY KEY (IDLIVRE, IDMOT),
--	Remplacer cette contrainte par un trigger
--	CONSTRAINT TP01_CARACTTOLIVRE_FK FOREIGN KEY (IDLIVRE) REFERENCES TP01_LIVRE(IDLIVRE),
	CONSTRAINT TP01_CARACTTOMOT_CLEF_FK FOREIGN KEY (IDMOT) REFERENCES TP01_MOT_CLEF(IDMOT)
);
/* Mot clé **************************************************************************************************/










/* Exemplaire ***********************************************************************************************/
CREATE TABLE TP01_EXEMPLAIRE(
	IDEXEMPLAIRE SERIAL,
	NUMERO INTEGER,
	DATE_ACHAT DATE,
	PRIX REAL,
	CODE_PRET VARCHAR(20),
	ETAT VARCHAR(15) CHECK (ETAT IN ('BON', 'ABIME', 'EN_REPARATION')),
	IDLIVRE INTEGER,
	CONSTRAINT TP01_EXEMPLAIRE_PK PRIMARY KEY (IDEXEMPLAIRE),
--	Remplacer cette contrainte par un trigger
--	CONSTRAINT TP01_EXEMPLAIRETOLIVRE_FK FOREIGN KEY (IDLIVRE) REFERENCES TP01_LIVRE(IDLIVRE),
	CONSTRAINT TP01_CODE_PRET_DK CHECK (CODE_PRET IN ('EXCLU', 'EMPRUNTABLE', 'CONSULTABLE')) 
);
/* Exemplaire ***********************************************************************************************/










/* Emprunt **************************************************************************************************/
CREATE TABLE TP01_EMPRUNT(
	IDPERSONNEPHYSIQUE INTEGER,
	IDEXEMPLAIRE INTEGER,
	DATE_EMPRUNT DATE,
	DATE_RETOUR DATE,
	DATE_RETOUR_REEL DATE,
	NB_RELANCE INTEGER CHECK (NB_RELANCE IN (1, 2, 3)),
	CONSTRAINT TP01_EMPRUNT_PK PRIMARY KEY (IDPERSONNEPHYSIQUE, IDEXEMPLAIRE, DATE_EMPRUNT),
--	Remplacer cette contrainte par un trigger
--	CONSTRAINT TP01_EMPRUNTTOABONNE_FK FOREIGN KEY (IDPERSONNEPHYSIQUE) REFERENCES TP01_ABONNE(IDPERSONNEPHYSIQUE),
	CONSTRAINT TP01_EMPRUNTTOEXEMPLAIRE_FK FOREIGN KEY (IDEXEMPLAIRE) REFERENCES TP01_EXEMPLAIRE(IDEXEMPLAIRE)
);
/* Emprunt **************************************************************************************************/










/* Bibliothéque *********************************************************************************************/
CREATE TABLE TP01_BIBLIOTHEQUE(
	IDBIBLIOTHEQUE SERIAL,
	NOM VARCHAR(20) NOT NULL,
	CONSTRAINT TP01_BIBLIOTHEQUE_PK PRIMARY KEY (IDBIBLIOTHEQUE)
);





CREATE TABLE TP01_ABONNEMENT(
	IDPERSONNEPHYSIQUE INTEGER,
	IDBIBLIOTHEQUE INTEGER,
	NUMERO_ABONNE INTEGER,
	TARIF REAL,
	REDUCTION REAL,
	CONSTRAINT TP01_ABONNEMENT_PK PRIMARY KEY (IDPERSONNEPHYSIQUE, IDBIBLIOTHEQUE),
--	Remplacer cette contrainte par un trigger
--	CONSTRAINT ABONNEMENTTOABONNE_FK FOREIGN KEY (IDPERSONNEPHYSIQUE) REFERENCES TP01_ABONNE(IDPERSONNEPHYSIQUE),
	CONSTRAINT TP01_ABONNEMENTTOBIBLIOTHEQUE_FK FOREIGN KEY (IDBIBLIOTHEQUE) REFERENCES TP01_BIBLIOTHEQUE(IDBIBLIOTHEQUE)
);
/* Bibliothéque *********************************************************************************************/

















/* Fonstion commune *****************************************************************************************/
CREATE OR REPLACE FUNCTION "checkAbstractTable"()
	RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
	RAISE EXCEPTION 'Insertion interdite dans la table abstraite %', TG_TABLE_NAME;
	RETURN NULL;
END $$;
/* Fonstion commune *****************************************************************************************/










/* Hiérarchie Personne Physique *****************************************************************************/
CREATE OR REPLACE FUNCTION "checkPrimaryKeyPersonnePhysique"()
	RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
	nbVal integer;
BEGIN
	SELECT COUNT(IDPERSONNEPHYSIQUE) FROM TP01_PERSONNEPHYSIQUE WHERE IDPERSONNEPHYSIQUE = NEW.IDPERSONNEPHYSIQUE INTO nbVal;
	IF nbVal > 0 THEN
		RAISE EXCEPTION 'L''identifiant (%) existe déjà dans la table %.', NEW.IDPERSONNEPHYSIQUE, TG_TABLE_NAME ;
		END IF;
	RETURN NEW;
END $$;





CREATE TRIGGER "checkPrimaryKeyTablePersonnePhysique"
BEFORE INSERT
ON TP01_PERSONNEPHYSIQUE
FOR EACH ROW
EXECUTE PROCEDURE "checkPrimaryKeyPersonnePhysique"();


CREATE TRIGGER "checkPrimarykeyTableHabitant"
BEFORE INSERT
ON TP01_HABITANT
FOR EACH ROW
EXECUTE PROCEDURE "checkPrimaryKeyPersonnePhysique"();


CREATE TRIGGER "checkPrimaryKeyTableAbonne"
BEFORE INSERT
ON TP01_ABONNE
FOR EACH ROW
EXECUTE PROCEDURE "checkPrimaryKeyPersonnePhysique"();
/* Hiérarchie Personne Physique *****************************************************************************/










/* Hiérarchie Livre *****************************************************************************************/
CREATE OR REPLACE FUNCTION "checkPrimaryKeyLivre"()
	RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
	nbVal integer;
BEGIN
	SELECT COUNT(IDLIVRE) FROM TP01_LIVRE WHERE IDLIVRE = NEW.IDLIVRE INTO nbVal;
	IF nbVal > 0 THEN
		RAISE EXCEPTION 'L''identifiant (%) existe déjà dans la table %.', NEW.IDLIVRE, TG_TABLE_NAME ;
		END IF;
	RETURN NEW;
END $$;





CREATE TRIGGER "checkAbstractTableLivre"
BEFORE INSERT
ON TP01_LIVRE
FOR EACH ROW
EXECUTE PROCEDURE "checkAbstractTable"();


CREATE TRIGGER "checkPrimaryKeyTableLivre"
BEFORE INSERT
ON TP01_LIVRE
FOR EACH ROW
EXECUTE PROCEDURE "checkPrimaryKeyLivre"();


CREATE TRIGGER "checkPrimarykeyTableLivreOriginal"
BEFORE INSERT
ON TP01_LIVREORIGINAL
FOR EACH ROW
EXECUTE PROCEDURE "checkPrimaryKeyLivre"();


CREATE TRIGGER "checkPrimaryKeyTableLivreTraduit"
BEFORE INSERT
ON TP01_LIVRETRADUIT
FOR EACH ROW
EXECUTE PROCEDURE "checkPrimaryKeyLivre"();
/* Hiérarchie Livre *****************************************************************************************/




