CREATE OR REPLACE FUNCTION get_events_grouped_by_date_in_date(cat_id UUID, day date)
RETURNS TABLE(
    start_date DATE,
    events JSON
) AS $$
BEGIN
  RETURN QUERY

WITH time_grouped AS (
    SELECT
        time.start_date::date AS group_start_date,
        events.id AS event_id,
        events.name AS event_name,
        events.description AS event_description,
        events.detail AS event_detail,
        events.subheader AS event_subheader,
        events.main_category_id AS event_main_cat,
        location.name AS location_title,
        time.start_date AS start_timestamp,
        time.end_date AS end_timestamp   
    FROM events
    INNER JOIN location ON events.location_id = location.id
    INNER JOIN time ON time.event_id = events.id
    INNER JOIN event_category ON event_category.event_id = events.id
    WHERE event_category.category_id = cat_id AND time.start_date::date >= now()
    GROUP BY time.start_date::date, events.id, location.name, time.start_date, time.end_date
)
SELECT
    group_start_date AS start_date,
    json_agg(json_build_object(
        'id', event_id,
        'name', event_name,
        'description', event_description,
        'detail', event_detail,
        'subheader', event_subheader,
        'main_category_id', event_main_cat,
        'location_name', location_title,
        'start', start_timestamp,
        'end', end_timestamp    
    )) AS events
FROM time_grouped
GROUP BY group_start_date
ORDER BY group_start_date DESC;
END;
$$ LANGUAGE plpgsql;


