-- DROP FUNCTION public.get_max_week_start_date_by_store_codes(_text);

CREATE OR REPLACE FUNCTION public.get_max_week_start_date_by_store_codes(store_codes_input text[])
 RETURNS TABLE(store_code text, max_week_start_date text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    -- Return the maximum week_start_date for each store code,
    -- returning NULL if no records exist for that store code
    RETURN QUERY 
    SELECT 
        store_code_value AS store_code,
        MAX(t.week_start_date) AS max_week_start_date
    FROM 
        unnest(store_codes_input) AS store_code_value
    LEFT JOIN 
        task t ON t.store_code = store_code_value
    GROUP BY 
        store_code_value;

END;
$function$
;
