--TYPES

CREATE TYPE exemplaires_etat AS ENUM ('neuf', 'bon', 'abime', 'perdu');
CREATE TYPE sanctions_etat AS ENUM ('perte', 'degradation', 'retard');
CREATE TYPE contribue_role AS ENUM ('auteur', 'compositeur', 'interprete', 'realisateur', 'acteur');

--TABLES

CREATE TABLE Editeurs(
	Nom VARCHAR NOT NULL,
	PRIMARY KEY (Nom)
);


CREATE TABLE Genres (
	Nom VARCHAR NOT NULL,
	PRIMARY KEY (Nom)
);


CREATE TABLE Ressources(
	Code SERIAL NOT NULL,
	Titre VARCHAR NOT NULL,
	Date_apparition DATE NOT NULL,
	Editeur VARCHAR NOT NULL,
	Genre VARCHAR NOT NULL,
	Code_classification VARCHAR NOT NULL,
	ISBN VARCHAR,
	Resume TEXT,
	Langue VARCHAR,
	Duree INTEGER,
	Synopsis TEXT,
	PRIMARY KEY (Code),
	UNIQUE (ISBN, Editeur),
	FOREIGN KEY (Editeur) REFERENCES Editeurs(Nom),
	FOREIGN KEY (Genre) REFERENCES Genres(Nom),
	CHECK ((ISBN IS NOT NULL AND Resume IS NOT NULL AND Langue IS NOT NULL AND Duree IS NULL AND Synopsis IS NULL) OR (Langue IS NOT NULL AND Duree IS NOT NULL AND Synopsis IS NOT NULL AND ISBN IS NULL AND RESUME IS NULL) OR (Duree IS NOT NULL AND ISBN IS NULL AND Resume IS NULL AND Langue IS NULL AND Synopsis IS NULL))
);

CREATE VIEW vueRessource AS
SELECT Code, Titre, Date_apparition, Editeur, Genre, Code_classification, ISBN, Resume, Langue, Duree, Synopsis,
CASE 
WHEN ISBN IS NOT NULL THEN 'Livres'
WHEN Synopsis IS NOT NULL THEN 'Films'
ELSE 'Musiques'
END AS type_Ressources
FROM Ressources;


CREATE TABLE Adherents(
	Login VARCHAR UNIQUE NOT NULL,
	Mot_de_passe VARCHAR NOT NULL,
	Nom VARCHAR NOT NULL,
	Prenom VARCHAR NOT NULL,
	Adresse VARCHAR NOT NULL,
	Email VARCHAR UNIQUE NOT NULL,
	Date_naissance DATE NOT NULL,
	Numero_telephone VARCHAR NOT NULL,
	Date_fin_adhesion DATE NOT NULL,
	Blackliste BOOLEAN NOT NULL,
	PRIMARY KEY (Login)
);


CREATE TABLE Personnels (
	Login VARCHAR UNIQUE NOT NULL,
	Mot_de_passe VARCHAR NOT NULL,
	Nom VARCHAR NOT NULL,
	Prenom VARCHAR NOT NULL,
	Adresse VARCHAR NOT NULL,
	Email VARCHAR UNIQUE NOT NULL,
PRIMARY KEY (Login)
);


CREATE TABLE Contributeurs (
	id SERIAL NOT NULL,
	Nom VARCHAR NOT NULL,
	Prenom VARCHAR NOT NULL,
	Date_naissance DATE NOT NULL,
	Nationalite VARCHAR NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE Contribue (
	id_contrib SERIAL NOT NULL,
	id_ressource SERIAL NOT NULL,
	Role contribue_role NOT NULL,
	PRIMARY KEY (id_contrib, id_ressource, Role),
	FOREIGN KEY (id_contrib) REFERENCES Contributeurs(id),
	FOREIGN KEY (id_ressource) REFERENCES Ressources(Code)
);


CREATE TABLE Sanctions (
	id_sanction VARCHAR NOT NULL,
	Type sanctions_etat NOT NULL,
	Duree_sanction INTEGER,
	Date DATE,
	Rembourse BOOLEAN,
	PRIMARY KEY (id_sanction),
	CHECK((Duree_sanction IS NOT NULL AND Date IS NOT NULL AND Rembourse IS NULL) OR (Duree_sanction IS NULL AND Date IS NULL AND Rembourse IS NOT NULL))
);

CREATE VIEW vueSanctions AS
SELECT id_sanction, Type, Duree_sanction, Date, Rembourse,
CASE 
WHEN Duree_sanction IS NOT NULL THEN 'Temporaire'
ELSE 'A_rembourser'
END AS type_Sanction
FROM Sanctions;


CREATE TABLE Exemplaires (
	id_ressource SERIAL NOT NULL,
	id INTEGER NOT NULL,
	Etat exemplaires_etat NOT NULL,
	PRIMARY KEY (id_ressource, id),
	FOREIGN KEY (id_ressource) REFERENCES Ressources(Code)
);

CREATE TABLE Prets(
	id_pret SERIAL NOT NULL,
	id_exemplaire INTEGER NOT NULL,
	id_ressource SERIAL NOT NULL,
	Login_adherent VARCHAR NOT NULL,
	Date_pret DATE NOT NULL,
	Duree_pret INTEGER NOT NULL,
	Date_rendu DATE,
	PRIMARY KEY (id_pret),
	FOREIGN KEY (id_exemplaire, id_ressource) REFERENCES Exemplaires(id, id_ressource),
	CHECK(Date_rendu>Date_pret)
);

--CONTRAINTES