-- ** TITULO      : Crear la vista materializada schedule_spaces_view_m.
-- ** OBJETIVO    : Crear la tabla que permite guardar los espacios de cada agenda.
-- ** DESCRIPCION : Se elimina la vista si existe

-- View: public.schedule_spaces_view_m

DROP MATERIALIZED VIEW IF EXISTS schedule_spaces_view_m;

CREATE MATERIALIZED VIEW public.schedule_spaces_view_m
TABLESPACE pg_default
AS
 SELECT schedule_rules.id_schedule_rules,
    schedule_rules.date_start AS date_start_schedule,
    schedule_rules.date_end AS date_end_schedule,
    generate_series(
		calendar.actual_date + schedule_rules.hours_start, 
		calendar.actual_date + schedule_rules.hours_end
		, (schedule_rules.duration || ' minute'::text)::interval
	) AS space_schedule,
    schedule_rules.hours_start,
    schedule_rules.hours_end,
    schedule_rules.duration,
    schedule_rules.days_week,
    calendar.day_name_spanish,
    calendar.id_date,
    calendar.working_day,
    schedule_rules.id_room
   FROM schedule_rules
     LEFT JOIN calendar ON schedule_rules.days_week ~~ (('%'::text || replace(replace(calendar.day_name_spanish::text, 'é'::text, 'e'::text), 'á'::text, 'a'::text)) || '%'::text)
  WHERE schedule_rules.status = 0 
  AND schedule_rules.days_week IS NOT NULL 
  AND calendar.actual_date >= schedule_rules.date_start::date 
  AND calendar.actual_date <= schedule_rules.date_end 
  AND schedule_rules.date_end > now()
  ORDER BY schedule_rules.id_schedule_rules, schedule_rules.date_start, schedule_rules.date_end
WITH DATA;

CREATE UNIQUE INDEX idx_schedule_spaces_view_m_  ON schedule_spaces_view_m (id_schedule_rules, space_schedule);

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

-- Refrescar la vista materializada
--REFRESH MATERIALIZED VIEW concurrently schedule_spaces_view_m;