-- DROP FUNCTION public.get_stores_brand_totals(text, text, int4);

CREATE OR REPLACE FUNCTION public.get_stores_brand_totals(
    p_store_codes_text text,
    p_week_start_dates_text text,
    p_customer_id integer
)
RETURNS TABLE(
    store_code text,
    soh_no_sales bigint,
    oos bigint,
    negative_stock bigint,
    no_sales_100_days bigint,
    seven_day_cover bigint,
    total_soh bigint,
    availability bigint,
    fixed_count bigint,
    not_fixed_count bigint
)
LANGUAGE plpgsql
AS $function$
DECLARE
    v_non_issues bigINT := 0;  
    v_soh_no_sales bigINT;
    v_oos bigINT;
    v_negative_stock bigINT;
    v_no_sales_100_days bigINT;
    v_seven_day_cover bigINT;
    v_total_soh bigINT;
    v_fixed_count bigINT;
    v_not_fixed_count bigINT;
    v_customer_brands TEXT[];
    store_code_value text;  
    week_start_date_value text;  
    p_store_codes text[];   
    p_week_start_dates text[];  
BEGIN
    -- Parse the input string to convert it into an array
    p_store_codes := string_to_array(p_store_codes_text, ','); 
    p_week_start_dates := string_to_array(p_week_start_dates_text, ','); 

    -- Retrieve brand names associated with the customer and store them in a variable
    SELECT ARRAY(SELECT b.name FROM brand b
                 JOIN customer_brand cb ON b.id = cb.brand_id
                 WHERE cb.customer_id = p_customer_id) INTO v_customer_brands;

    -- Loop through each store code and corresponding week start date
    FOR i IN 1 .. array_length(p_store_codes, 1) LOOP
        store_code_value := p_store_codes[i];
        week_start_date_value := p_week_start_dates[i];  -- Get corresponding week start date

        -- Reset variables for each store code
        v_soh_no_sales := 0;
        v_oos := 0;
        v_negative_stock := 0;
        v_no_sales_100_days := 0;
        v_seven_day_cover := 0;
        v_fixed_count := 0;
        v_not_fixed_count := 0;

        -- Retrieve values from the task table and calculate totals for the current store code and week start date
        SELECT 
            COUNT(CASE WHEN name = 'NO SALES - FIX BALANCE/SHOW STOCK ON SHELF' THEN 1 END),
            COUNT(CASE WHEN name = 'OOS - PLACE ORDER' THEN 1 END),
            COUNT(CASE WHEN name = 'NEGATIVE STOCK - FIX STOCK BALANCE' THEN 1 END),
            COUNT(CASE WHEN name = 'NO SALES 100 DAYS PLUS - FIX STOCK BALANCE' THEN 1 END),
            COUNT(CASE WHEN name = '<7 DAYS COVER - SEND FD CATEGORY PHOTO' THEN 1 END),
            COUNT(CASE WHEN status = 'Fixed' THEN 1 END),
            COUNT(CASE WHEN status = 'Not Fixed' THEN 1 END)
        INTO 
            v_soh_no_sales, 
            v_oos, 
            v_negative_stock, 
            v_no_sales_100_days, 
            v_seven_day_cover,
            v_fixed_count,
            v_not_fixed_count
        FROM 
            task t
        WHERE 
            t.store_code = store_code_value AND
            t.week_start_date = week_start_date_value AND
            t.brand = ANY(v_customer_brands);

        -- Calculate total stock on hand (SOH) for the current store code and week start date
        SELECT COUNT(*) INTO v_total_soh 
        FROM sjreport sj 
        WHERE sj.store_code = store_code_value AND sj.week_start_date = week_start_date_value AND
              sj.brand = ANY(v_customer_brands);

        -- Calculate active products and availability
        RETURN QUERY SELECT 
            store_code_value AS store_code,        
            v_soh_no_sales,
            v_oos,
            v_negative_stock,
            v_no_sales_100_days,
            v_seven_day_cover,
            v_total_soh AS total_soh,        
            CASE 
                WHEN v_total_soh::int > 0 
                THEN ((100/v_total_soh::float)*COALESCE(v_total_soh-v_not_fixed_count, 0)::float)::BIGINT
                ELSE 0::BIGINT 
            END AS availability,
            v_fixed_count AS fixed_count,
            v_not_fixed_count AS not_fixed_count;

    END LOOP;

END;
$function$;

SELECT * FROM public.get_stores_brand_totals('57073', '2024-12-16', 1);