-- 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$
DECLARE
--    store_code_value text; -- Variable to hold the current store code in the loop
    store_codes_array text[]; -- Variable to hold the parsed store codes
BEGIN
    -- Parse the input string to convert it into an array
    -- Split by comma
    store_codes_array := string_to_array(store_codes_input, ','); 

    -- 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_array) AS store_code_value
    LEFT JOIN 
        task t ON t.store_code = store_code_value
    GROUP BY 
        store_code_value;

END;
$function$
;