CREATE OR REPLACE FUNCTION update_store_issue_counts_temp()
RETURNS VOID AS $$
DECLARE
    store_record RECORD;
	max_week_start_date text = '2024-11-11';
BEGIN
    -- Step 1: Get max week_start_date for each store
--    SELECT  
--            MAX(week_start_date) INTO  max_week_start_date
--        FROM 
--            sjreport;
      
    
        -- Step 2: Get counts based on issue_for_feedback for the current store
        UPDATE store_temp
        SET 
            non_issues = subquery.non_issues,
            soh_no_sales = subquery.soh_no_sales,
            oos = subquery.oos,
            negative_stock = subquery.negative_stock,
            no_sales_100_days = subquery.no_sales_100_days,
            seven_day_cover = subquery.seven_day_cover,
			total_soh = subquery.non_issues + subquery.soh_no_sales + subquery.oos + subquery.negative_stock + subquery.seven_day_cover + subquery.seven_day_cover,
			active_products = subquery.non_issues + subquery.soh_no_sales + subquery.oos + subquery.negative_stock + subquery.seven_day_cover + subquery.seven_day_cover
        FROM (
            SELECT 
                store_code,
                COUNT(CASE WHEN issue_for_feedback = 'NO ISSUE - ITEM SELLING' THEN 1 END) AS non_issues,
                COUNT(CASE WHEN issue_for_feedback = 'NO SALES - FIX BALANCE/SHOW STOCK ON SHELF' THEN 1 END) AS soh_no_sales,
                COUNT(CASE WHEN issue_for_feedback = 'OOS - PLACE ORDER' THEN 1 END) AS oos,
                COUNT(CASE WHEN issue_for_feedback = 'NEGATIVE STOCK - FIX STOCK BALANCE' THEN 1 END) AS negative_stock,
                COUNT(CASE WHEN issue_for_feedback = 'NO SALES 100 DAYS PLUS - FIX STOCK BALANCE' THEN 1 END) AS no_sales_100_days,
                COUNT(CASE WHEN issue_for_feedback = '<7 DAYS COVER - SEND FD CATEGORY PHOTO' THEN 1 END) AS seven_day_cover
            FROM 
                sjreport
            WHERE 
                week_start_date >= max_week_start_date
            GROUP BY 
                store_code
        ) AS subquery
        WHERE 
            store_temp.store_code = subquery.store_code;  -- Update only for the current store
    

		-- Step 3: Update availability for each store
	    FOR store_record IN SELECT store_code FROM store_temp LOOP
	        UPDATE store_temp
	        SET availability = CASE 
	            WHEN total_soh > 0 THEN (COALESCE(non_issues, 0)::float / total_soh) * 100 
	            ELSE 0 
	        END
	        WHERE store_code = store_record.store_code;
	    END LOOP;
END;
$$
 LANGUAGE plpgsql;

select update_store_issue_counts_temp()

