-- creation de la vue matérialisée des salles
-- DROP MATERIALIZED VIEW public."tr_serenade_salle";

CREATE MATERIALIZED VIEW public."tr_serenade_salle" AS
	SELECT id_salle AS id, nom, code FROM "ERP_Salle";

ALTER TABLE "tr_serenade_salle" OWNER TO cri;
GRANT SELECT ON "tr_serenade_salle" TO edt;
REFRESH MATERIALIZED VIEW "tr_serenade_salle";


-- creation de la vue matérialisée des utilisateurs
-- DROP MATERIALIZED VIEW public."tr_serenade_salle";

CREATE MATERIALIZED VIEW public."t_serenade_user" AS
	SELECT 
		id_user AS id , 
		CASE
			WHEN prenomusuel IS NOT NULL AND length(prenomusuel::text) > 0 THEN initcap(prenomusuel::text)
			ELSE initcap(prenom::text)
		END AS prenom,
		CASE
			WHEN nomusuel IS NOT NULL AND length(nomusuel::text) > 0 THEN initcap(nomusuel::text)
			ELSE initcap(nom::text)
		END AS nom,
		logindomaine AS pseudo,
		mailgemgrh AS mail
	FROM "ERP_User" 
	ORDER BY id_user ;

ALTER TABLE "tr_serenade_salle" OWNER TO cri;
GRANT SELECT ON "tr_serenade_salle" TO edt;
REFRESH MATERIALIZED VIEW "tr_serenade_salle";


-- creation de la vue matérialisée des type de séance (enseignement)
-- DROP MATERIALIZED VIEW public."tr_serenade_type_seance";

CREATE MATERIALIZED VIEW public."tr_serenade_type_seance" AS
	SELECT id_typeseance AS id, nom, code FROM "ERP_TypeSeance" ORDER BY id_typeseance ;

ALTER TABLE "tr_serenade_type_seance" OWNER TO cri;
GRANT SELECT ON "tr_serenade_type_seance" TO edt;
REFRESH MATERIALIZED VIEW "tr_serenade_type_seance";

	
-- creation de la vue matérialisée des type d'évènements
-- DROP MATERIALIZED VIEW public."tr_serenade_type_evenement";

CREATE MATERIALIZED VIEW public."tr_serenade_type_evenement" AS
	SELECT id_typeevenement AS id, nom, code FROM "ERP_TypeEvenement" ORDER BY id_typeevenement ;

ALTER TABLE "tr_serenade_type_evenement" OWNER TO cri;
GRANT SELECT ON "tr_serenade_type_evenement" TO edt;
REFRESH MATERIALIZED VIEW "tr_serenade_type_evenement";


-- creation de la vue matérialisée des type d'évènements
-- DROP MATERIALIZED VIEW public."tr_serenade_semestre";

CREATE MATERIALIZED VIEW public."tr_serenade_semestre" AS
	SELECT id_semestre AS id, nom , '' AS code FROM "ERP_Semestre" ORDER BY id_semestre ;

ALTER TABLE "tr_serenade_semestre" OWNER TO cri;
GRANT SELECT ON "tr_serenade_semestre" TO edt;
REFRESH MATERIALIZED VIEW "tr_serenade_semestre";



-- creation de la vue matérialisée des type d'évènements
-- DROP MATERIALIZED VIEW public."t_serenade_module";

CREATE MATERIALIZED VIEW public."t_serenade_module" AS
	SELECT id_module AS id, nom, code FROM "ERP_Module" ORDER BY id_module;

ALTER TABLE "t_serenade_module" OWNER TO cri;
GRANT SELECT ON "t_serenade_module" TO edt;
REFRESH MATERIALIZED VIEW "t_serenade_module";

-- creation de la vue matérialisée des séances
-- séances avec les enseignants comme utilisateurs
-- DROP MATERIALIZED VIEW public."t_serenade_seance";

CREATE MATERIALIZED VIEW public."t_serenade_seance" AS
	SELECT 
		row_number() OVER () AS id,
		tab.utilisateur_id,
		tab.module_id,
		tab.semestre_id,
		tab.salle_id,
		tab.debut,
		tab.fin,
		tab.type_id,
		tab.number_type,
		tab.groupe_td,
		tab.enseignant
	FROM ( 
		SELECT DISTINCT 
			seance.id_seance AS "id",
			CASE
				WHEN usrprof.id_user IS NULL THEN 0::bigint
				ELSE usrprof.id_user
			END AS "utilisateur_id",
			salle.id_salle::text AS "salle_id",
			mo.id_module AS "module_id",
			es.id_semestre AS "semestre_id",
			((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
			((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
			CASE
				WHEN usrprof.nomusuel IS NOT NULL AND length(usrprof.nomusuel::text) > 0 THEN usrprof.nomusuel::text
				ELSE usrprof.nom::text || ' '::text
			END ||
			CASE
				WHEN usrprof.prenomusuel IS NOT NULL AND length(usrprof.prenomusuel::text) > 0 THEN initcap(usrprof.prenomusuel::text)
				ELSE initcap(usrprof.prenom::text)
			END AS "enseignant",
			typseance.id_typeseance AS "type_id",
			0 AS number_type,
			STRING_AGG (gpe_td.nom, '|') AS "groupe_td"
		   FROM "ERP_Seance" seance
			 LEFT JOIN "ERP_GroupeSeanceReelle" seance_relle ON seance.id_seance = seance_relle.id_seance
			 LEFT JOIN "ERP_InscriptionInstanceModule" iim ON seance_relle.id_instancemodule = iim.id_instancemodule
			 LEFT JOIN "ERP_InstanceModule" imo ON seance_relle.id_instancemodule = imo.id_instancemodule
			 LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
			 LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
			 LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
			 LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
			 LEFT JOIN "ERP_ProfAnime" profanim ON profanim.id_seance = seance.id_seance
			 LEFT JOIN "ERP_Professeur" prof ON prof.id_prof = profanim.id_prof
			 LEFT JOIN "ERP_User" usrprof ON prof.id_user = usrprof.id_user
			 LEFT JOIN "ERP_SalleSeance" salseance ON seance.id_seance = salseance.id_seance
			 LEFT JOIN "ERP_Salle" salle ON salseance.id_salle = salle.id_salle
			 LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
			 LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND iim.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
			 LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
			 LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
		  WHERE seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
		  GROUP BY seance.id_seance, usrprof.id_user, salle.id_salle, mo.id_module, es.id_semestre, seance.dateseance, seance.heuredeb, seance.heurefin, usrprof.nomusuel, usrprof.nom, usrprof.prenomusuel, usrprof.prenom, typseance.id_typeseance 
		--;
		UNION ALL
		-- séances avec les étudiants comme utilisateurs
		SELECT DISTINCT 
			seance.id_seance AS "id",
			CASE
				WHEN usr.id_user IS NULL THEN 0::bigint
				ELSE usr.id_user
			END AS "utilisateur_id",
			salle.id_salle::text AS "salle_id",
			mo.id_module AS "module_id",
			es.id_semestre AS "semestre_id",
			((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
			((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
			STRING_AGG (CASE
				WHEN usrprof.nomusuel IS NOT NULL AND length(usrprof.nomusuel::text) > 0 THEN initcap(usrprof.nomusuel::text)
				ELSE initcap(usrprof.nom::text) || ' '::text
			END ||
			CASE
				WHEN usrprof.prenomusuel IS NOT NULL AND length(usrprof.prenomusuel::text) > 0 THEN initcap(usrprof.prenomusuel::text)
				ELSE initcap(usrprof.prenom::text)
			END, '|') AS "enseignant",
			typseance.id_typeseance AS "type_id",
			0 AS number_type,
			gpe_td.nom AS "groupe_td"
		FROM "ERP_Seance" seance
			LEFT JOIN "ERP_GroupeSeanceReelle" seance_relle ON seance.id_seance = seance_relle.id_seance
			LEFT JOIN "ERP_InscriptionInstanceModule" iim ON seance_relle.id_instancemodule = iim.id_instancemodule
			LEFT JOIN "ERP_InstanceModule" imo ON iim.id_instancemodule = imo.id_instancemodule
			LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
			LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
			LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
			LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
			LEFT JOIN "ERP_ProfAnime" profanim ON profanim.id_seance = seance.id_seance
			LEFT JOIN "ERP_Professeur" prof ON prof.id_prof = profanim.id_prof
			LEFT JOIN "ERP_User" usrprof ON prof.id_user = usrprof.id_user
			LEFT JOIN "ERP_SalleSeance" salseance ON seance.id_seance = salseance.id_seance
			LEFT JOIN "ERP_Salle" salle ON salseance.id_salle = salle.id_salle
			LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
			LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND iim.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
			LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
			LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
		WHERE igpe_td.id_instancegroupetd = seance_relle.id_instancegroupetd AND seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
		GROUP BY seance.id_seance, usr.id_user, salle.id_salle, mo.id_module, es.id_semestre, seance.dateseance, seance.heuredeb, seance.heurefin, typseance.id_typeseance, gpe_td.nom 
		--;
		UNION ALL
		-- séances avec les étudiants comme utilisateurs
		-- table des modules bizarres
		SELECT DISTINCT 
			seance.id_seance AS "id",
			CASE
				WHEN usr.id_user IS NULL THEN 0::bigint
				ELSE usr.id_user
			END AS "utilisateur_id",
			salle.id_salle::text AS "salle_id",
			mo.id_module AS "module_id",
			es.id_semestre AS "semestre_id",
			((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
			((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
			STRING_AGG (CASE
				WHEN usrprof.nomusuel IS NOT NULL AND length(usrprof.nomusuel::text) > 0 THEN initcap(usrprof.nomusuel::text)
				ELSE initcap(usrprof.nom::text) || ' '::text
			END ||
			CASE
				WHEN usrprof.prenomusuel IS NOT NULL AND length(usrprof.prenomusuel::text) > 0 THEN initcap(usrprof.prenomusuel::text)
				ELSE initcap(usrprof.prenom::text)
			END, '|') AS "enseignant",
			typseance.id_typeseance AS "type_id",
			0 AS number_type,
			gpe_td.nom AS "groupe_td"
		FROM "ERP_Seance" seance
			LEFT JOIN "ERP_GroupeSeanceReelle" seance_relle ON seance.id_seance = seance_relle.id_seance
			LEFT JOIN "ERP_InscriptionInstanceModule" iim ON seance_relle.id_instancemodule = iim.id_instancemodule
			JOIN "ERP_GPCOM_IIM" gp_comp ON iim.id_inscriptioninstancemodule = gp_comp.id_inscriptioninstancemodule
			LEFT JOIN "ERP_InstanceModule" imo ON iim.id_instancemodule = imo.id_instancemodule
			LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
			LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
			LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
			LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
			LEFT JOIN "ERP_ProfAnime" profanim ON profanim.id_seance = seance.id_seance
			LEFT JOIN "ERP_Professeur" prof ON prof.id_prof = profanim.id_prof
			LEFT JOIN "ERP_User" usrprof ON prof.id_user = usrprof.id_user
			LEFT JOIN "ERP_SalleSeance" salseance ON seance.id_seance = salseance.id_seance
			LEFT JOIN "ERP_Salle" salle ON salseance.id_salle = salle.id_salle
			LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
			LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND gp_comp.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
			LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
			LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
		WHERE igpe_td.id_instancegroupetd = seance_relle.id_instancegroupetd AND seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
		GROUP BY seance.id_seance, usr.id_user, salle.id_salle, mo.id_module, es.id_semestre, seance.dateseance, seance.heuredeb, seance.heurefin, typseance.id_typeseance, gpe_td.nom
		--;
		UNION ALL
		-- seance de type evaluations
		SELECT DISTINCT 
			seance.id_seanceevaluation AS "id",
			CASE
				WHEN usr.id_user IS NULL THEN 0::bigint
				ELSE usr.id_user
			END AS "utilisateur_id",
			salle.id_salle::text AS "salle_id",
			mo.id_module AS "module_id",
			es.id_semestre AS "semestre_id",
			((seance.dateseance || ' '::text) || seance.heuredeb::text) || ':00'::text AS "debut",
			((seance.dateseance || ' '::text) || seance.heurefin::text) || ':00'::text AS "fin",
			''::text AS "enseignant",
			typseance.id_typeseance AS "type_id",
			0 AS number_type,
			gpe_td.nom AS "groupe_td"
		FROM "ERP_SeanceEvaluation" seance
			LEFT JOIN "ERP_SalleSeanceEvaluation" evasalle ON evasalle.id_seanceevaluation = seance.id_seanceevaluation
			LEFT JOIN "ERP_Salle" salle ON salle.id_salle = evasalle.id_salle
			LEFT JOIN "ERP_GroupeSeanceEvaluationReelle" s_grpe_eval_re ON seance.id_seanceevaluation = s_grpe_eval_re.id_seanceevaluation
			LEFT JOIN "ERP_InstanceModule" imo ON imo.id_instancemodule = s_grpe_eval_re.id_instancemodule
			LEFT JOIN "ERP_Semestre" es ON imo.id_semestre = es.id_semestre
			LEFT JOIN "ERP_Module" mo ON imo.id_module = mo.id_module
			LEFT JOIN "ERP_InscriptionInstanceModule" iim ON imo.id_instancemodule = iim.id_instancemodule
			LEFT JOIN "ERP_Etudiant" etu ON iim.id_etudiant = etu.id_etudiant
			LEFT JOIN "ERP_User" usr ON usr.id_user = etu.id_user
			LEFT JOIN "ERP_TypeSeance" typseance ON seance.id_typeseance = typseance.id_typeseance
			LEFT JOIN "ERP_InstanceGroupeTDModule" igpe_td_mo ON imo.id_instancemodule = igpe_td_mo.id_instancemodule AND iim.id_instancegroupetd = igpe_td_mo.id_instancegroupetd
			LEFT JOIN "ERP_InstanceGroupeTD" igpe_td ON igpe_td_mo.id_instancegroupetd = igpe_td.id_instancegroupetd
			LEFT JOIN "ERP_GroupeTd" gpe_td ON igpe_td.id_groupetd = gpe_td.id_groupetd
		WHERE s_grpe_eval_re.id_instancegroupetd = iim.id_instancegroupetd AND seance.dateseance <= (now() + '6 mons'::interval) AND seance.dateseance >= (now() - '6 mons'::interval)
		--;
		UNION ALL
		-- séance de type evenements
		SELECT DISTINCT
			evtsalle.id_evenement AS "id",
			CASE
				WHEN usr_inv.id_user IS NOT NULL THEN usr_inv.id_user
				ELSE 0::bigint
			END AS "utilisateur_id",
			STRING_AGG (salle.id_salle::text, '|') AS "salle_id",
			0 AS "module_id",
			0 AS "semestre_id",
			((evt.datedebutevenement || ' '::text) || evt.heuredebut::text) || ':00'::text AS "debut",
			((evt.datefinevenement || ' '::text) || evt.heurefin::text) || ':00'::text AS "fin",
			''::text AS "enseignant",
			1 AS number_type,
			evttype.id_typeevenement AS "type_id",
			''::text AS "groupe_td"
		FROM "ERP_Evenement" evt
			LEFT JOIN "ERP_SalleEvenement" evtsalle ON evtsalle.id_evenement = evt.id_evenement
			LEFT JOIN "ERP_TypeEvenement" evttype ON evttype.id_typeevenement = evt.id_typeevenement
			LEFT JOIN "ERP_Salle" salle ON evtsalle.id_salle = salle.id_salle
			LEFT JOIN "ERP_InviteEvenement" inv ON inv.id_evenement = evt.id_evenement
			LEFT JOIN "ERP_User" usr_inv ON inv.id_user = usr_inv.id_user
		WHERE evt.datedebutevenement <= (now() + '6 mons'::interval) AND evt.datedebutevenement >= (now() - '6 mons'::interval)
		GROUP BY evtsalle.id_evenement, usr_inv.id_user, evt.datedebutevenement, evt.heuredebut, evt.datefinevenement, evt.heurefin, evttype.id_typeevenement
	) AS tab;

ALTER TABLE "t_serenade_seance" OWNER TO cri;
GRANT SELECT ON "t_serenade_seance" TO edt;
REFRESH MATERIALIZED VIEW "t_serenade_seance";
