DROP FUNCTION IF EXISTS get_store_category_totals(text, text, text);

CREATE OR REPLACE FUNCTION get_store_category_totals(
    p_store_code text,
    p_category text,
    p_week_start_date text
) 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
) AS $$
DECLARE
    v_non_issues bigINT := 0;  -- You can add this if you want to count non-issues as well
    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;
BEGIN
    -- Retrieve values from the task table and calculate totals
    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 = p_category AND 
        t.week_start_date = p_week_start_date;

    -- Calculate total stock on hand (SOH)
    SELECT COUNT(*) INTO v_total_soh 
    FROM sjreport sj 
    WHERE sj.store_code = p_store_code AND sj.category = p_category AND sj.week_start_date = p_week_start_date;

    -- Calculate active products and availability
    RETURN QUERY SELECT 
        p_store_code AS store_code,  -- Use the input 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 > 0 
			THEN ((100/v_total_soh)*COALESCE(v_total_soh-v_not_fixed_count, 0)::float)::BIGINT
			--(COALESCE(v_not_fixed_count, 0)::float / v_total_soh * 100)::BIGINT
            ELSE 0::BIGINT 
        END AS availability,
        v_fixed_count AS fixed_count,
        v_not_fixed_count AS not_fixed_count;

END;
$$
 LANGUAGE plpgsql;
 

select * from get_store_category_totals('EC06','BEVERAGES','2024-11-11')