CREATE OR REPLACE FUNCTION process_sjreport_data()
RETURNS VOID AS $$
DECLARE
    offset INT := 0;
    limit INT := 1000;
    sjreport RECORD;
    sjreport_records RECORD[];
    date DATE;
    newRow JSONB;
BEGIN
    -- Get the maximum week start date
    SELECT get_max_week_start_date() INTO date;

    RAISE NOTICE 'Date: %', date;

    LOOP
        -- Fetch sjreport data
        SELECT * INTO sjreport_records 
        FROM get_sjreport_data_pages(date, offset, limit);

        IF NOT FOUND THEN
            RAISE NOTICE 'No data returned from sjreport.';
            EXIT; -- Exit if no data is returned
        END IF;

        RAISE NOTICE 'Number of records sj read: % + offset: %', array_length(sjreport_records, 1), offset;

        -- Exit if fewer records than limit are returned
        IF array_length(sjreport_records, 1) < limit THEN
            EXIT;
        END IF;

        -- Process each record
        FOREACH sjreport IN ARRAY sjreport_records LOOP
            newRow := jsonb_build_object(
                'chain', COALESCE(sjreport.channel, ''),
                'store_name', COALESCE(sjreport.store_name, ''),
                'status', COALESCE(sjreport.status, ''),
                'category', COALESCE(sjreport.category, ''),
                'brand', COALESCE(sjreport.brand, ''),
                'product', COALESCE(sjreport.product, ''),
                'variant', COALESCE(sjreport.variant, ''),
                'size', COALESCE(sjreport.size, ''),
                'sjreport_id', NULLIF(sjreport.id, 'NaN'), -- Assuming id can be NULL
                'store_code', COALESCE(sjreport.store_code, ''),
                'type', 'task',
                'name', COALESCE(sjreport.issue_for_feedback, ''),
                'store_soh', COALESCE(NULLIF(sjreport.store_soh, 'NaN'), 0),
                'system_dros', COALESCE(NULLIF(sjreport.system_dros, 'NaN'), 0),
                'scoring_system', COALESCE(sjreport.scoring_system, ''),
                'last_sold', NULLIF(sjreport.last_sold, 'NaN'),
                'week_start_date', NULLIF(sjreport.week_start_date, 'NaN'),
                'product_id', NULLIF(sjreport.product_id, 'NaN')
            );

            -- Upsert operation
            INSERT INTO task (chain, store_name, status, category, brand,
                               product, variant, size, sjreport_id,
                               store_code, type, name,
                               store_soh, system_dros,
                               scoring_system, last_sold,
                               week_start_date, product_id)
            VALUES (
                newRow->>'chain',
                newRow->>'store_name',
                newRow->>'status',
                newRow->>'category',
                newRow->>'brand',
                newRow->>'product',
                newRow->>'variant',
                newRow->>'size',
                (newRow->>'sjreport_id')::INTEGER,
                newRow->>'store_code',
                newRow->>'type',
                newRow->>'name',
                (newRow->>'store_soh')::INTEGER,
                (newRow->>'system_dros')::INTEGER,
                newRow->>'scoring_system',
                NULLIF(newRow->>'last_sold', '')::DATE,
                NULLIF(newRow->>'week_start_date', '')::DATE,
                (newRow->>'product_id')::INTEGER
            )
            ON CONFLICT (chain, store_name, product_id, week_start_date, name, sjreport_id)
            DO UPDATE SET 
                status = EXCLUDED.status,
                category = EXCLUDED.category,
                brand = EXCLUDED.brand,
                variant = EXCLUDED.variant,
                size = EXCLUDED.size,
                store_code = EXCLUDED.store_code,
                type = EXCLUDED.type,
                store_soh = EXCLUDED.store_soh,
                system_dros = EXCLUDED.system_dros,
                scoring_system = EXCLUDED.scoring_system,
                last_sold = EXCLUDED.last_sold;

            RAISE NOTICE 'Successfully inserted/upserted record for store: %', sjreport.store_name;
        END LOOP;

        offset := offset + limit; -- Increment offset for next batch
    END LOOP;

    -- Update issue counts on store table 
    PERFORM update_store_issue_counts();

    RAISE NOTICE 'Data processing completed successfully.';
END;
$$
 LANGUAGE plpgsql;