-- DROP FUNCTION public.get_store_categories_brand_totals(text, text[], text, int4);

CREATE OR REPLACE FUNCTION public.get_store_categories_brand_totals(p_store_code text, p_categories text[], p_week_start_date text, p_customer_id integer)
 RETURNS TABLE(store_code text, category 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_soh_no_sales bigINT := 0;
    v_oos bigINT := 0;
    v_negative_stock bigINT := 0;
    v_no_sales_100_days bigINT := 0;
    v_seven_day_cover bigINT := 0;
    v_total_soh bigINT := 0;
    v_fixed_count bigINT := 0;
    v_not_fixed_count bigINT := 0;
    v_customer_brands TEXT[];
    category_value text; -- Variable to hold the current category in the loop
BEGIN

    -- 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 category provided in the input array
    FOREACH category_value IN ARRAY p_categories LOOP
        
        -- Reset variables for each category
        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 category
        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 = p_store_code AND 
            t.category = category_value AND 
            t.week_start_date = p_week_start_date AND
            t.brand = ANY(v_customer_brands);

        -- Calculate total stock on hand (SOH) for the current category
        SELECT COUNT(*) INTO v_total_soh 
        FROM sjreport sj 
        WHERE sj.store_code = p_store_code AND sj.category = category_value AND sj.week_start_date = p_week_start_date AND
              sj.brand = ANY(v_customer_brands);

        -- Calculate active products and availability
        RETURN QUERY SELECT 
            p_store_code AS store_code,
            category_value AS category,
            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 > 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_store_categories_brand_totals('EC06', 'FRUIT,BEVERAGES', '2024-12-02', 1);