CREATE OR REPLACE FUNCTION update_store_on_task_status_change()
RETURNS TRIGGER AS $$
DECLARE
    max_week_start_date text;
BEGIN
    -- Check if status has changed from 'Not Fixed' to 'Fixed'
    IF OLD.status = 'Not Fixed' AND NEW.status = 'Fixed' THEN
        
        -- Get the maximum week_start_date for the store associated with this task
        SELECT MAX(week_start_date) INTO max_week_start_date
        FROM sjreport
        WHERE store_code = NEW.store_code;

        -- Update counts in the store
        UPDATE store
        SET 
            soh_no_sales = COALESCE(subquery.soh_no_sales, 0),
            oos = COALESCE(subquery.oos, 0),
            negative_stock = COALESCE(subquery.negative_stock, 0),
            no_sales_100_days = COALESCE(subquery.no_sales_100_days, 0),
            seven_day_cover = COALESCE(subquery.seven_day_cover, 0)
        FROM (
            SELECT 
                store_code,
                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 
                task
            WHERE 
                week_start_date >= max_week_start_date
				AND store_code = NEW.store_code
            GROUP BY 
                store_code
        ) AS subquery
        WHERE 
            store.store_code = subquery.store_code;  -- Update only for the current store

	-- Step 2: Calculate availability after updating other fields
        UPDATE store
        SET 
			non_issues = non_issues+1,
            availability = CASE 
                WHEN total_soh > 0 THEN (COALESCE(non_issues+1, 0) / NULLIF(total_soh, 0)) * 100 
                ELSE 0 
            END
        WHERE 
            store_code = NEW.store_code; -- Ensure we are updating the 

    END IF;

    RETURN NEW; -- Return the new row for further processing if needed
END;
$$
 LANGUAGE plpgsql;
 


CREATE TRIGGER task_status_update_trigger
AFTER UPDATE ON task
FOR EACH ROW
WHEN (OLD.status = 'Not Fixed' AND NEW.status = 'Fixed')
EXECUTE FUNCTION update_store_on_task_status_change();