Rollback scripts to revert DB schema changes prior to v5.5.3 (Nolana) and v5.6.6 (Orchid)

MODSOURCE-670 - Getting issue details... STATUS

These scripts are relevant only for the environment where mod-source-record-storage v5.5.3 (Nolana) or v5.6.6 (Orchid) have been deployed. At the time of the scripts preparation, it is known that these environments are Orchid and Nolana Bugfest envs and test envs of the PTF team.
Previous mod-source-record-storage CSP versions (v5.5.3 (Nolana) and v5.6.6 (Orchid)) contain 
MODSOURCE-601, MODSOURCE-636 changes that introduce modifications to the database schema. 
However, in the scope of the v5.5.4 (Nolana) or v5.6.7 (Orchid) CSP releases MODSOURCE-601, MODSOURCE-636 changes have been reverted so it is necessary to run these rollback scripts to revert the module database schema to the state prior to v5.5.3 and v5.6.6 versions deployment correspondingly.

DROP TABLE IF EXISTS {tenant_id}_mod_source_record_storage.async_migration_jobs CASCADE;
DROP TYPE IF EXISTS {tenant_id}_mod_source_record_storage.migration_job_status CASCADE;
DROP INDEX IF EXISTS {tenant_id}_mod_source_record_storage.idx_records_state;

DROP TABLE IF EXISTS {tenant_id}_mod_source_record_storage.marc_records_tracking CASCADE;
ALTER TABLE {tenant_id}_mod_source_record_storage.marc_indexers DROP COLUMN IF EXISTS version CASCADE;

DROP FUNCTION IF EXISTS {tenant_id}_mod_source_record_storage.fill_in_marc_indexers(p_marc_id uuid, p_marc_content jsonb, p_version integer);

CREATE OR REPLACE FUNCTION {tenant_id}_mod_source_record_storage.delete_marc_indexers()
    RETURNS trigger as $delete_marc_indexers$
      begin
          if (TG_OP = 'DELETE') then
              delete from {tenant_id}_mod_source_record_storage.marc_indexers where marc_id = OLD.id;
              delete from {tenant_id}_mod_source_record_storage.marc_indexers_leader where marc_id = OLD.id;
          end if;
          return OLD;
      end;
$delete_marc_indexers$ language plpgsql;

CREATE OR REPLACE FUNCTION {tenant_id}_mod_source_record_storage.insert_marc_indexers()
    RETURNS trigger as $insert_marc_indexers$
  begin
      if (TG_OP = 'UPDATE') then
          delete from {tenant_id}_mod_source_record_storage.marc_indexers where marc_id = NEW.id;
          delete from {tenant_id}_mod_source_record_storage.marc_indexers_leader where marc_id = NEW.id;
      end if;
      perform {tenant_id}_mod_source_record_storage.fill_in_marc_indexers(NEW.id, NEW.content);
      return NEW;
  end;
$insert_marc_indexers$ language plpgsql;

drop trigger if exists process_marc_records_lb_delete_trigger on {tenant_id}_mod_source_record_storage.marc_records_lb;
create trigger process_marc_records_lb_delete_trigger before delete on {tenant_id}_mod_source_record_storage.marc_records_lb for each row execute procedure {tenant_id}_mod_source_record_storage.delete_marc_indexers();
drop trigger if exists process_marc_records_lb_insert_update_trigger on {tenant_id}_mod_source_record_storage.marc_records_lb;
create trigger process_marc_records_lb_insert_update_trigger after insert or update on {tenant_id}_mod_source_record_storage.marc_records_lb for each row execute procedure {tenant_id}_mod_source_record_storage.insert_marc_indexers();


DELETE FROM {tenant_id}_mod_source_record_storage.databasechangelog
WHERE filename IN (
 'liquibase/tenant/scripts/v-5.7.0/2023-04-13--16-00-update-fill-in-trigger.xml',
 'liquibase/tenant/scripts/v-5.7.0/2023-05-22--16-00-create-records-state-index.xml',
 'liquibase/tenant/scripts/v-5.7.0/2023-05-31--16-00-create-async-migration-jobs-table.xml',
 'liquibase/tenant/scripts/v-5.7.0/2023-05-31--16-00-create-async-migration-jobs-table.xml'
);

do $$
    declare
        index integer;
        suffix text;
    begin
        for index in 0 .. 999 loop
            suffix = lpad(index::text, 3, '0');
            execute 'drop index if exists {tenant_id}_mod_source_record_storage.idx_marc_indexers_marc_id_version_' || suffix || ';';
            execute 'create index if not exists idx_marc_indexers_marc_id_' || suffix || ' on {tenant_id}_mod_source_record_storage.marc_indexers_' || suffix || '(marc_id);';
        end loop;
    end;
$$;

Where {tenant_id} - the owner (tenant) id for which the script should be run.