-- DROP FUNCTION public.update_total_counts(jsonb);

CREATE OR REPLACE FUNCTION public.update_total_counts(in_values jsonb)
 RETURNS void
 LANGUAGE plpgsql
AS $function$DECLARE
    store_record RECORD;
BEGIN
    -- Loop through each store record to calculate and update total_soh
    FOR store_record IN
        SELECT 
            s.id, 
            SUM(
                COALESCE(s.oos, 0) + 
                COALESCE(s.negative_stock, 0) + 
                COALESCE(s.soh_no_sales, 0) + 
                COALESCE(s.no_sales_100_days, 0) + 
                COALESCE(s."seven_day_cover", 0) --+ 
                -- COALESCE(s.non_issues, 0)
            ) AS total_issues,
            COALESCE(s.non_issues, 0) as non_issues
        FROM store AS s  -- Selecting from the store table
        JOIN jsonb_array_elements(in_values) AS elem ON 
            (elem->>'id')::int = s.id
        GROUP BY s.id
    LOOP
          -- Update the total_soh back into the store table
          UPDATE store 
          SET 
          active_products = store_record.total_issues + store_record.non_issues,
          availability = store_record.non_issues / NULLIF(store_record.total_issues + store_record.non_issues, 0) * 100,
          visibility = store_record.total_issues / NULLIF(store_record.total_issues + store_record.non_issues, 0) * 100,
          total_soh = store_record.total_issues + store_record.non_issues 
          WHERE id = store_record.id;

      END LOOP;

END;$function$
;