-- add column carrera_activa to table carrera
ALTER TABLE CARRERA
ADD CARRERA_ACTIVA BOOLEAN DEFAULT TRUE NOT NULL;
-- add column fp_activo to facultad_profesor
ALTER TABLE FACULTAD_PROFESOR
ADD FP_ACTIVO BOOLEAN DEFAULT TRUE NOT NULL;
-- CREATE A VIEW FROM USUARIO
CREATE VIEW public.fs_usuario AS
SELECT USUARIO_ID AS id,
    USUARIO_CLAVE AS clave,
    U.ROL_ID AS rol,
    ROL_TITULO AS titulo,
    USUARIO_NOMBRE AS username,
    USUARIO_CORREO AS email,
    USUARIO_ADMIN AS admin,
    facultad_id AS facultad
FROM USUARIO U
    JOIN ROL R ON U.rol_id = R.rol_id;
ALTER TABLE public.fs_usuario OWNER TO postgres;



	-- FUNCTION: public.fs_asistencia_reporte(integer, integer, character varying, character varying, date, date)

	DROP FUNCTION IF EXISTS public.fs_asistencia_reporte(integer, integer, character varying, character varying, date, date);

	CREATE OR REPLACE FUNCTION public.fs_asistencia_reporte(
	p_carrera int,
	p_periodo int,
	p_clave varchar,
	p_nombre varchar,
	p_facultad int,
	p_fecha_ini date DEFAULT now(),
	p_fecha_fin date DEFAULT now())
    RETURNS TABLE(profesor_id int, profesor_nombre varchar, profesor_clave varchar, total int, asistencias int, justificaciones int, retardos int) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
	WITH dias AS (
			SELECT DIA.DATE AS FECHA, (SELECT EXTRACT(DOW FROM DIA)::SMALLINT) AS DIA_ID
			FROM GENERATE_SERIES(P_FECHA_INI, P_FECHA_FIN, '1 DAY'::INTERVAL) AS DIA
			WHERE DIA.DATE NOT IN(SELECT DIASFESTIVOS_DIA FROM DIASFESTIVOS WHERE PERIODO_ID = P_PERIODO)
		)

		SELECT DISTINCT	PROF.PROFESOR_ID, PROF.PROFESOR_NOMBRE, PROF.PROFESOR_CLAVE,
			COALESCE (TOT.TOTAL, 0) , COALESCE (TMP.TOTAL, 0), COALESCE (TMPJ.TOTAL, 0) ,  COALESCE (RET.TOTAL, 0)
		FROM HORARIO HOR
			INNER JOIN PROFESOR PROF ON HOR.PROFESOR_ID = PROF.PROFESOR_ID
			INNER JOIN FACULTAD_PROFESOR FP ON FP.profesor_id = PROF.profesor_id and fp.facultad_id = p_facultad
			INNER JOIN MATERIA MAT ON MAT.MATERIA_ID = HOR.MATERIA_ID
			LEFT JOIN (

				SELECT -- TOTAL
				HOR.PROFESOR_ID, COUNT(1) AS TOTAL FROM HORARIO HOR
					INNER JOIN PROFESOR PROF ON HOR.PROFESOR_ID = PROF.PROFESOR_ID
					INNER JOIN MATERIA MAT ON MAT.MATERIA_ID = HOR.MATERIA_ID
					INNER JOIN DIAS AS D ON DIA_ID = HORARIO_DIA
					LEFT JOIN CARRERA C ON C.CARRERA_ID = MAT.CARRERA_ID
					WHERE MAT.CARRERA_ID = COALESCE(P_CARRERA, MAT.CARRERA_ID) AND PERIODO_ID = P_PERIODO AND P_FACULTAD = C.FACULTAD_ID
					GROUP BY HOR.PROFESOR_ID
			) AS TOT ON TOT.PROFESOR_ID = PROF.PROFESOR_ID
			LEFT JOIN(
				SELECT -- ASISTENCIAS
					HOR.PROFESOR_ID,
					COUNT(1) AS TOTAL
				FROM REGISTRO R
					INNER JOIN HORARIO HOR ON HOR.HORARIO_ID = r.HORARIO_ID
					INNER JOIN PROFESOR PROF ON HOR.PROFESOR_ID = PROF.PROFESOR_ID
					INNER JOIN MATERIA MAT ON MAT.MATERIA_ID = HOR.MATERIA_ID
					LEFT JOIN CARRERA C ON C.CARRERA_ID = MAT.CARRERA_ID
					WHERE MAT.CARRERA_ID = COALESCE(P_CARRERA, MAT.CARRERA_ID) AND PERIODO_ID = P_PERIODO AND NOT REGISTRO_RETARDO AND NOT REGISTRO_JUSTIFICADA AND R.REGISTRO_FECHA BETWEEN P_FECHA_INI AND P_FECHA_FIN AND P_FACULTAD = C.FACULTAD_ID
					GROUP BY HOR.PROFESOR_ID
			) AS TMP ON TMP.PROFESOR_ID = PROF.PROFESOR_ID
			LEFT JOIN(
				SELECT -- JUSTIFICADAS
					HOR.PROFESOR_ID,
					COUNT(1) AS TOTAL
				FROM REGISTRO R
					INNER JOIN HORARIO HOR ON HOR.HORARIO_ID = r.HORARIO_ID
					INNER JOIN PROFESOR PROF ON HOR.PROFESOR_ID = PROF.PROFESOR_ID
					INNER JOIN MATERIA MAT ON MAT.MATERIA_ID = HOR.MATERIA_ID
					LEFT JOIN CARRERA C ON C.CARRERA_ID = MAT.CARRERA_ID
					INNER JOIN FACULTAD F ON F.FACULTAD_ID = C.FACULTAD_ID
					WHERE MAT.CARRERA_ID = COALESCE(P_CARRERA, MAT.CARRERA_ID) AND PERIODO_ID = P_PERIODO AND NOT REGISTRO_RETARDO AND REGISTRO_JUSTIFICADA AND R.REGISTRO_FECHA BETWEEN P_FECHA_INI AND P_FECHA_FIN AND P_FACULTAD = C.FACULTAD_ID
					GROUP BY HOR.PROFESOR_ID
			) AS TMPJ ON TMPJ.PROFESOR_ID = PROF.PROFESOR_ID
			LEFT JOIN (
				SELECT -- RETARDOS
					HOR.PROFESOR_ID,
					COUNT(1) AS TOTAL
				FROM REGISTRO R
					INNER JOIN HORARIO HOR ON HOR.HORARIO_ID = r.HORARIO_ID
					INNER JOIN PROFESOR PROF ON HOR.PROFESOR_ID = PROF.PROFESOR_ID
					INNER JOIN MATERIA MAT ON MAT.MATERIA_ID = HOR.MATERIA_ID
					LEFT JOIN CARRERA C ON C.CARRERA_ID = MAT.CARRERA_ID
					INNER JOIN FACULTAD F ON F.FACULTAD_ID = C.FACULTAD_ID
					WHERE MAT.CARRERA_ID = COALESCE(P_CARRERA, MAT.CARRERA_ID) AND PERIODO_ID = P_PERIODO AND REGISTRO_RETARDO  AND R.REGISTRO_FECHA BETWEEN P_FECHA_INI AND P_FECHA_FIN AND P_FACULTAD = C.FACULTAD_ID
					GROUP BY HOR.PROFESOR_ID
			) AS RET ON RET.PROFESOR_ID = PROF.PROFESOR_ID
		WHERE PROFESOR_NOMBRE ILIKE '%' || COALESCE(P_NOMBRE,'') || '%' AND PROFESOR_CLAVE = COALESCE(P_CLAVE, PROFESOR_CLAVE)
		
		ORDER BY PROFESOR_NOMBRE
	
$BODY$;

DROP FUNCTION IF EXISTS public.fs_asistencia_profesorreporte(integer, integer, integer, date, date);

CREATE OR REPLACE FUNCTION public.fs_asistencia_profesorreporte(
	p_carrera integer,
	p_periodo integer,
	p_idprof integer,
	p_fecha_ini date DEFAULT now(),
	p_fecha_fin date DEFAULT now())
    RETURNS TABLE(profesor_id integer, profesor character varying, profesor_clave character varying, horario_fecha date, horario_hora_inicio time without time zone, registro_hora time without time zone, horario_salon character varying, horario_grupo character varying, materia_id int, materia_nombre character varying, carrera integer, checado boolean, retardo boolean, justificada boolean) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

	WITH dias AS (
		SELECT dia.date AS fecha, (SELECT EXTRACT(DOW FROM dia)::smallint) AS dia_id
		FROM generate_series(p_fecha_ini, p_fecha_fin, '1 day'::interval) AS dia
		WHERE dia.date NOT IN(SELECT diasfestivos_dia FROM diasfestivos WHERE periodo_id = COALESCE(p_periodo, PERIODO_ID))
	)
    SELECT profesor_id, profesor_nombre, profesor_clave, fe.fecha, fe.horario_hora_inicio, reg.registro_fecha::time, fe.horario_salon,fe.horario_grupo, fe.materia_id, fe.materia_nombre, fe.carrera_id,
		CASE WHEN reg.registro_fecha IS NULL THEN false ELSE true END AS checado,
		reg.registro_retardo, reg.registro_justificada
	FROM (
		SELECT prof.profesor_id, profesor_nombre, profesor_clave, hor.horario_id, d.fecha, hor.horario_hora_inicio,  hor.horario_salon, hor.horario_grupo, mat.materia_id, mat.materia_nombre, mat.carrera_id
		FROM dias as d
		INNER JOIN horario hor ON d.dia_id = hor.horario_dia
		INNER JOIN materia mat ON mat.materia_id = hor.materia_id
		INNER JOIN profesor prof ON prof.profesor_id = hor.profesor_id
		WHERE hor.profesor_id = COALESCE(p_idprof, hor.profesor_id) and mat.carrera_id = COALESCE(p_carrera, mat.carrera_id) AND periodo_id = COALESCE(p_periodo, PERIODO_ID)
	) AS fe
	LEFT JOIN registro reg ON fe.horario_id = reg.horario_id AND fe.fecha = reg.registro_fecha_ideal::date
	ORDER BY fe.fecha
$BODY$;

ALTER FUNCTION public.fs_asistencia_profesorreporte(integer, integer, integer, date, date)
    OWNER TO postgres;


DELETE FROM REGISTRO WHERE REGISTRO_ID IN (SELECT REGISTRO_ID FROM REGISTRO R
JOIN HORARIO H ON H.HORARIO_ID = R.HORARIO_ID AND FACULTAD_ID = 1);

DELETE FROM HORARIO WHERE FACULTAD_ID = 1;