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

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;
$$;