-- ** TITULO      : Crear la vista schedule_reserve_view.
-- ** OBJETIVO    : Crear la vista que permite ver los espacios y disponibilidad de cada agenda.


-- View: public.schedule_reserve_view

DROP  VIEW IF EXISTS schedule_reserve_view;

CREATE OR REPLACE VIEW schedule_reserve_view AS
SELECT schedule_spaces_view_m.id_schedule_rules AS id_schedule_rules,
	schedule_spaces_view_m.space_schedule AS space_schedule,
	schedule_spaces_view_m.space_schedule::DATE AS space_schedule_date,
	schedule.date_start::DATE AS date_start,
	schedule_rules.name AS schedule_name,
	schedule_spaces_view_m.date_start_schedule::DATE AS date_start_schedule,
	schedule_spaces_view_m.date_end_schedule::DATE AS date_end_schedule,
	schedule.hours_start AS hours_start,
	schedule.hours_end AS hours_end,	
	schedule.type_schedule AS type_schedule,
	schedule_spaces_view_m.duration AS duration,
	schedule_spaces_view_m.day_name_spanish as day_name_spanish,
	CASE
		WHEN schedule.type_schedule = 'A'	THEN	'ASIGNADO'
		WHEN schedule.type_schedule = 'B'	THEN	'BLOQUEO'
		WHEN schedule.type_schedule = 'BE'	THEN	'BLOQUEO ESPECIAL'
		WHEN schedule.type_schedule = 'C'	THEN	'CANCELADO'
		WHEN schedule.type_schedule = 'CF'	THEN	'CONFIRMADO'
		WHEN holidays_by_room.holiday_room 	IS NOT NULL AND holidays_by_room.id_room = schedule_spaces_view_m.id_room 	THEN	'FESTIVO SALA' 
		WHEN calendar.working_day = false	THEN 	'FESTIVO'		
		WHEN schedule.type_schedule IS NULL	THEN 'DISPONIBLE'
		ELSE schedule.type_schedule 
	END AS type_schedule_description,
	CASE 
		WHEN calendar.working_day IS TRUE	THEN 'Sí' 
		ELSE 'No'
	END AS working_day,
	room.room_name AS room_name,
	room.id_modality AS id_modality,
	headquarter.headquarter_name AS headquarter_name,
	schedule.appointment_code AS assesion_number,
	room_appointment_study_receipt.id_room_appointment_study_receipt AS id_entry,
	appointment.appointment_status AS appointment_status
FROM schedule_spaces_view_m
LEFT JOIN schedule ON schedule.id_schedule_rules= schedule_spaces_view_m.id_schedule_rules 
			AND schedule_spaces_view_m.space_schedule = schedule.date_start::date + schedule.hours_start
LEFT JOIN room ON schedule_spaces_view_m.id_room = room.room_code	
LEFT JOIN headquarter ON headquarter.id_headquarter = room.id_headquarter
LEFT JOIN appointment ON appointment.appointment_code = schedule.appointment_code
LEFT JOIN room_appointment_study_receipt ON room_appointment_study_receipt.appointment_code = appointment.appointment_code
LEFT JOIN calendar ON calendar.actual_date = schedule_spaces_view_m.space_schedule::date
LEFT JOIN (
			select date_start::DATE AS holiday_room, id_room
			from schedule
			where type_schedule = 'DF'
			) holidays_by_room	ON	holidays_by_room.holiday_room = schedule_spaces_view_m.space_schedule::date
LEFT JOIN	schedule_rules ON  schedule_rules.id_schedule_rules=	schedule_spaces_view_m.id_schedule_rules;

DO 
$do$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'metabase_user')     
   THEN
		GRANT SELECT ON TABLE public.schedule_reserve_view TO metabase_user;
   END IF;
END
$do$;