Script to update source field of instance records

During the data saving phase in the MARC migration process for 7.9M records (tested on sprint-etesting environment), some of the records failed with optimistic locking exceptions (around 30 records) :

e70b2bb3-4764-4108-8fcd-9e5147123c05, ERROR: Cannot update record e70b2bb3-4764-4108-8fcd-9e5147123c05 because it has been changed (optimistic locking): Stored _version is 3, _version of request is <NULL> (23F09)

because some instances in mod-inventory-storage have source = FOLIO (or LINKED_DATA), while it should have been source = MARC, even if these instances have corresponding MARC-bib records in mod-source-record-storage.
To fix this issue, we need to update the source field of instances from FOLIO\LINKED_DATA → Marc where source = FOLIO\LINKED_DATA, and if they have a linked MARC bib.

SET search_path TO '%s_mod_marc_migrations';

-- Drop procedure if it already exists
DROP PROCEDURE IF EXISTS %s_mod_marc_migrations.update_instance_source();

-- Create new stored procedure
CREATE PROCEDURE %s_mod_marc_migrations.update_instance_source()
LANGUAGE plpgsql
AS $$
DECLARE
    matched_count INT;
    updated_count INT;
    failed_count INT;
BEGIN
    -- Step 1: Count instances that match criteria
    SELECT COUNT(*) INTO matched_count
    FROM %s_mod_inventory_storage.instance AS i
    JOIN %s_mod_source_record_storage.records_lb AS r
    ON r.external_id = i.id
    WHERE i.jsonb ->> 'source' = 'FOLIO';

    -- Step 2: Perform update and track affected rows
    WITH updated AS (
        UPDATE %s_mod_inventory_storage.instance AS i
        SET jsonb = jsonb_set(i.jsonb, '{source}', '"MARC"', false)  -- Update only "source" field
        FROM %s_mod_source_record_storage.records_lb AS r
        WHERE i.id = r.external_id
        AND i.jsonb ->> 'source' = 'FOLIO' -- or LINKED_DATA
        RETURNING i.id
    )
    SELECT COUNT(*) INTO updated_count FROM updated;

    -- Step 3: Verify failures (rows that should have been updated but weren't)
    SELECT COUNT(*) INTO failed_count
    FROM %s_mod_inventory_storage.instance AS i
    JOIN %s_mod_source_record_storage.records_lb AS r
    ON r.external_id = i.id
    WHERE i.jsonb ->> 'source' = 'FOLIO'; -- or LINKED_DATA

    -- Log output messages
    RAISE NOTICE 'Matched records: %', matched_count;
    RAISE NOTICE 'Updated records: %', updated_count;
    RAISE NOTICE 'Failed records: %', failed_count;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error occurred: %', SQLERRM;
END;
$$;