
DELETE FROM TP01_ABONNEMENT;
DELETE FROM TP01_BIBLIOTHEQUE;
DELETE FROM TP01_EMPRUNT;
DELETE FROM TP01_EXEMPLAIRE;
DELETE FROM TP01_CARACTERISATION;
DELETE FROM TP01_MOT_CLEF;
DELETE FROM TP01_LIVRETRADUIT;
DELETE FROM TP01_LIVREORIGINAL;
DELETE FROM TP01_LIVRE;
DELETE FROM TP01_DOMICILIATION;
DELETE FROM TP01_VILLE;
DELETE FROM TP01_ABONNE;
DELETE FROM TP01_HABITANT;
DELETE FROM TP01_PERSONNEPHYSIQUE;









/* Réinitialisation de toutes les séquences *****************************************************************/
DO $$
DECLARE
	_schemaName text;
	_sequenceName text;
BEGIN
	FOR _schemaName, _sequenceName IN (SELECT schemaname, sequencename FROM pg_sequences)
		LOOP
			EXECUTE format('ALTER SEQUENCE %I.%I RESTART;', _schemaName, _sequenceName);
		END LOOP;
END
$$;
/* Réinitialisation de toutes les séquences *****************************************************************/











/* Hiérarchie Personne Physique *****************************************************************************/
INSERT INTO TP01_PERSONNEPHYSIQUE VALUES(1, 'LORCA', 'BERNARD', 15);
INSERT INTO TP01_PERSONNEPHYSIQUE VALUES(2, 'CLANET', 'LUCIEN', 40);





INSERT INTO TP01_HABITANT VALUES(3, 'NABILA', 'ZOE', 78);
INSERT INTO TP01_HABITANT VALUES(4, 'HUGO', 'EMILE', 53);





CREATE TABLE "_newOld_IDAbonne" AS 
	SELECT ROW_NUMBER() OVER() + (SELECT MAX (IDPERSONNEPHYSIQUE)
										FROM TP01_PERSONNEPHYSIQUE) AS IDNEW, NUMERO_ABONNE AS IDOLD
		FROM Fg01_ABONNE;





INSERT INTO TP01_ABONNE
	SELECT IDNEW, NOM, PRENOM, AGE FROM "_newOld_IDAbonne" i, Fg01_ABONNE A
		WHERE i.IDOLD = A.NUMERO_ABONNE;





/* Ville ****************************************************************************************************/
INSERT INTO TP01_VILLE(NOM)
	SELECT DISTINCT VILLE FROM Fg01_ABONNE;





INSERT INTO TP01_DOMICILIATION 
	SELECT (SELECT IDVILLE FROM TP01_VILLE 
					WHERE NOM = 'MONTPELLIER'), (SELECT IDPERSONNEPHYSIQUE FROM TP01_HABITANT
															WHERE NOM = 'NABILA' AND PRENOM = 'ZOE' AND AGE = 78);
INSERT INTO TP01_DOMICILIATION 
	SELECT (SELECT IDVILLE FROM TP01_VILLE 
					WHERE NOM = 'BEZIER'), (SELECT IDPERSONNEPHYSIQUE FROM TP01_HABITANT
														WHERE NOM = 'HUGO' AND PRENOM = 'EMILE' AND AGE = 53);





INSERT INTO TP01_DOMICILIATION 
	SELECT V.IDVILLE, I.IDNEW FROM TP01_VILLE V, Fg01_ABONNE A, "_newOld_IDAbonne" I
		WHERE V.NOM = A.VILLE AND A.NUMERO_ABONNE = I.IDOLD;
/* Ville ****************************************************************************************************/










/* Hiérarchie Livre *****************************************************************************************/
INSERT INTO TP01_LIVREORIGINAL
	SELECT row_number() over(), ISBN, TITRE, 'FR', SIECLE, CATEGORIE FROM Fg01_LIVRE;





UPDATE TP01_LIVREORIGINAL
	SET LANGUE = 'BR'
		WHERE TITRE IN ('AN INTRODUCTION TO DATABASE SYSTEMS','NEW APPLICATIONS OF DATABASES');





INSERT INTO TP01_LIVRETRADUIT
	SELECT (SELECT max(IDLIVRE)+1 FROM TP01_LIVRE), '014017737X', 'THE PEARL', 'BR', (SELECT IDLIVRE FROM TP01_LIVREORIGINAL
																								WHERE TITRE = 'LA PERLE');
INSERT INTO TP01_LIVRETRADUIT
	SELECT (SELECT max(IDLIVRE)+1 FROM TP01_LIVRE), '0465026567', 'GÖDEL ESCHER BACH: AN ETERNAL GOLDEN BRAID', 'BR', (SELECT IDLIVRE FROM TP01_LIVREORIGINAL
																																	WHERE TITRE = 'GODEL ESCHER BACH : LES BRINS D''UNE GUIRLANDE');
INSERT INTO TP01_LIVRETRADUIT
	SELECT (SELECT max(IDLIVRE)+1 FROM TP01_LIVRE), '2220080234', 'NOUVELLES APPLICATIONS DES BASES DE DONNÉES', 'FR', (SELECT IDLIVRE FROM TP01_LIVREORIGINAL
																																	WHERE TITRE = 'NEW APPLICATIONS OF DATABASES');
/* Hiérarchie Livre *****************************************************************************************/










/* Mot clé **************************************************************************************************/
INSERT INTO TP01_MOT_CLEF(MOT)
	SELECT * FROM Fg01_MOT_CLEF;





INSERT INTO TP01_CARACTERISATION
	SELECT L.IDLIVRE, M.IDMOT FROM TP01_LIVREORIGINAL L, Fg01_CARACTERISATION C, TP01_MOT_CLEF M
		WHERE L.ISBN = C.ISBN AND C.MOT = M.MOT;
/* Mot clé **************************************************************************************************/










/* Exemplaire ***********************************************************************************************/
INSERT INTO TP01_EXEMPLAIRE(NUMERO,DATE_ACHAT,PRIX,CODE_PRET,ETAT,IDLIVRE)
	SELECT NUMERO, DATE_ACHAT, PRIX, CODE_PRET, ETAT, L.IDLIVRE FROM Fg01_EXEMPLAIRE E, TP01_LIVRE L
			WHERE E.ISBN = L.ISBN;





INSERT INTO TP01_EXEMPLAIRE(NUMERO,DATE_ACHAT,PRIX,CODE_PRET,ETAT,IDLIVRE)
	SELECT 10000, '10-04-1985', 1000, 'EMPRUNTABLE', 'BON', (SELECT IDLIVRE FROM TP01_LIVRE
																	WHERE TITRE = 'THE PEARL');
INSERT INTO TP01_EXEMPLAIRE(NUMERO,DATE_ACHAT,PRIX,CODE_PRET,ETAT,IDLIVRE)
	SELECT 12000, '11-05-1999', 1500, 'EMPRUNTABLE', 'BON', (SELECT IDLIVRE FROM TP01_LIVRE
																	WHERE TITRE = 'GÖDEL ESCHER BACH: AN ETERNAL GOLDEN BRAID');
INSERT INTO TP01_EXEMPLAIRE(NUMERO,DATE_ACHAT,PRIX,CODE_PRET,ETAT,IDLIVRE)
	SELECT 13000, '27-10-2014', 2000, 'EMPRUNTABLE', 'BON', (SELECT IDLIVRE FROM TP01_LIVRE
																	WHERE TITRE = 'NOUVELLES APPLICATIONS DES BASES DE DONNÉES');





INSERT INTO TP01_EMPRUNT
	SELECT IDNEW, IDEXEMPLAIRE, DATE_EMPRUNT, DATE_RETOUR, DATE_RETOUR_REEL, NB_RELANCE FROM "_newOld_IDAbonne" N, TP01_EXEMPLAIRE EX, Fg01_EMPRUNT E
			WHERE N.IDOLD = E.NUMERO_ABONNE AND EX.NUMERO = E.NUMERO;
/* Exemplaire ***********************************************************************************************/










/* Bibliothéque *********************************************************************************************/
INSERT INTO TP01_BIBLIOTHEQUE(NOM) VALUES('EMILE ZOLA');
INSERT INTO TP01_BIBLIOTHEQUE(NOM) VALUES('VICTOR HUGO');





INSERT INTO TP01_ABONNEMENT
	SELECT IDNEW, (SELECT IDBIBLIOTHEQUE FROM TP01_BIBLIOTHEQUE WHERE NOM = 'EMILE ZOLA'), NUMERO_ABONNE, TARIF, REDUCTION
		FROM "_newOld_IDAbonne" N, Fg01_ABONNE A
			WHERE NOM = 'DUPONT' AND N.IDOLD = A.NUMERO_ABONNE;
INSERT INTO TP01_ABONNEMENT
	SELECT IDNEW, (SELECT IDBIBLIOTHEQUE FROM TP01_BIBLIOTHEQUE WHERE NOM = 'VICTOR HUGO'), NUMERO_ABONNE, TARIF, REDUCTION
		FROM "_newOld_IDAbonne" N, Fg01_ABONNE A
			WHERE NOM != 'DUPONT' AND N.IDOLD = A.NUMERO_ABONNE;
/* Bibliothéque *********************************************************************************************/











