Clean up records with inconsistent matched id values

MODSOURCE-666 - Getting issue details... STATUS

if 999 ff s UUID is different from matched_id and status ACTUAL - get highest generation for matched_id and overwrite matched_id to be the same as 999 ff s with incremented generation and also If more than 1 ACTUAL record with the same matched_id - then to those records status will be set as 'OLD'.

Update unmatched matched_id and generation and update status

DO
$$
DECLARE
    rec record;
    updated_generation numeric := 0;
BEGIN
     
--  temporary table is designated to hold recorsd when matched_id and 999 ff s values are mismatched
CREATE TEMP TABLE value_matched_id_inconsistency ON COMMIT DROP
AS
    SELECT rl.id, rl.matched_id, rl.generation, mi.value, mi.version
    FROM fs09000000_mod_source_record_storage.records_lb rl,
         fs09000000_mod_source_record_storage.marc_indexers mi,
         (SELECT mi.value, max(mi.version) AS max_version
            FROM fs09000000_mod_source_record_storage.records_lb rl,
                fs09000000_mod_source_record_storage.marc_indexers mi
    WHERE rl.id = mi.marc_id AND rl.state='ACTUAL' AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value
    GROUP BY  mi.value) AS nested_query
    WHERE rl.id = mi.marc_id AND rl.state='ACTUAL' AND mi.value = nested_query.value AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value AND mi.version = nested_query.max_version;
     
--  temporary table (matched_id_generation_table) is for holding pair matched_id, generation
CREATE TEMP TABLE matched_id_generation_table ON COMMIT DROP AS
    SELECT rl.matched_id, MAX(rl.generation)+1 AS max_gen
    FROM fs09000000_mod_source_record_storage.records_lb rl,
        fs09000000_mod_source_record_storage.marc_indexers mi,
        value_matched_id_inconsistency  val_match
    WHERE rl.matched_id::text = val_match.value AND rl.id = mi.marc_id AND rl.state='ACTUAL' AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND  rl.matched_id::text = mi.value
    GROUP by rl.matched_id;
     
-- UPDATE ALL ACTUAL RECORDS TO -> 'OLD' state  
UPDATE fs09000000_mod_source_record_storage.records_lb rl
SET state='OLD'
FROM fs09000000_mod_source_record_storage.marc_indexers mi,
    value_matched_id_inconsistency val_match
WHERE rl.id = mi.marc_id AND rl.state='ACTUAL' AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.value = val_match.value;
     
   
     
--    iteration through mismatched matched_id and value
FOR rec IN SELECT * FROM value_matched_id_inconsistency matched_inc, matched_id_generation_table matched_gen 
where matched_inc.matched_id = matched_gen.matched_id
    LOOP
      updated_generation := (SELECT max_gen
        FROM matched_id_generation_table matched_gen
        WHERE matched_gen.matched_id = rec.value::uuid);
 
        if updated_generation is null then
            updated_generation := 0;
        end if;
 
      UPDATE fs09000000_mod_source_record_storage.records_lb rl
      SET generation = updated_generation,
            matched_id = rec.value::uuid,
            state = 'ACTUAL'
      WHERE rl.id = rec.id;
         
    END LOOP;
      
END;
$$
LANGUAGE plpgsql;