text: """reverse migration - remove 3 digit identifiers for IRONMAN
  
  Revision ID: 458e56eedb92
  Revises: ee3a3a61484f
  Create Date: 2019-05-15 15:27:05.531769
  
  """
  import logging
  import re
  
  from alembic import op
  from sqlalchemy.sql import text
  
  from portal.system_uri import TRUENTH_EXTERNAL_STUDY_SYSTEM
  
  # revision identifiers, used by Alembic.
  revision = '458e56eedb92'
  down_revision = 'ee3a3a61484f'
  
  logger = logging.getLogger("alembic")
  logger.setLevel(logging.INFO)
  
  
  def purge_3digit_org_ids(conn):
      two_dig_pat = re.compile(r'^146-(\d\d)$')
      three_dig_pat = re.compile(r'^146-(\d\d\d)$')
  
      two_dig_ids = []
      three_dig_ids = []
      query = (
          "SELECT id, value FROM identifiers WHERE"
          " system=:system AND value LIKE '146-%'")
      result = conn.execute(text(query), system="http://pcctc.org/")
  
      for id, value in result.fetchall():
          if two_dig_pat.match(value):
              two_dig_ids.append((id, value))
          elif three_dig_pat.match(value):
              three_dig_ids.append((id, value))
          else:
              logging.info("skipping pattern mismatch {}".format(value))
  
      kill_ids = []
      for id, value in three_dig_ids:
          segments = three_dig_pat.match(value).groups()
          if not segments[0].startswith('0'):
              continue
          lookfor = '146-{}'.format(segments[0][1:])
          match = [v2 for i2, v2 in two_dig_ids if v2 == lookfor]
          if not match:
              logging.info("only 3 dig version of {}; leave alone".format(
                  value))
              continue
          kill_ids.append(id)
  
      if not kill_ids:
          return
  
      stmt = (
          "DELETE FROM organization_identifiers WHERE"
          " identifier_id IN :kill_ids")
      conn.execute(text(stmt), kill_ids=tuple(kill_ids))
      logging.info("DELETED {} 3 digit organization_identifiers".format(
          len(kill_ids)))
  
      stmt = "DELETE FROM identifiers WHERE id IN :kill_ids"
      conn.execute(text(stmt), kill_ids=tuple(kill_ids))
      logging.info("DELETED {} 3 digit identifiers".format(len(kill_ids)))
  
  
  def purge_3digit_user_ids(conn):
      two_dig_pat = re.compile(r'^170-(\d\d)-(\d\d\d)$')
      three_dig_pat = re.compile(r'^170-(\d\d\d)-(\d\d\d)$')
  
      two_dig_ids = []
      three_dig_ids = []
      query = (
          "SELECT id, value FROM identifiers WHERE"
          " system=:system AND value LIKE '170-%'")
      result = conn.execute(text(query), system=TRUENTH_EXTERNAL_STUDY_SYSTEM)
  
      for id, value in result.fetchall():
          if two_dig_pat.match(value):
              two_dig_ids.append((id, value))
          elif three_dig_pat.match(value):
              three_dig_ids.append((id, value))
          else:
              logging.info("skipping pattern mismatch {}".format(value))
  
      kill_ids = []
      for id, value in three_dig_ids:
          segments = three_dig_pat.match(value).groups()
          if not segments[0].startswith('0'):
              continue
          lookfor = '170-{}-{}'.format(segments[0][1:], segments[1])
          match = [v2 for i2, v2 in two_dig_ids if v2 == lookfor]
          if not match:
              logging.info("only 3 dig version of {}; leave alone".format(
                  value))
              continue
          kill_ids.append(id)
  
      if not kill_ids:
          return
  
      stmt = "DELETE FROM user_identifiers WHERE identifier_id IN :kill_ids"
      conn.execute(text(stmt), kill_ids=tuple(kill_ids))
      logging.info("DELETED {} 3 digit user_identifiers".format(len(kill_ids)))
  
      stmt = "DELETE FROM identifiers WHERE id IN :kill_ids"
      conn.execute(text(stmt), kill_ids=tuple(kill_ids))
      logging.info("DELETED {} 3 digit identifiers".format(len(kill_ids)))
  
  
  def upgrade():
      conn = op.get_bind()
  
      # 3 digit org identifiers will be purged via site persistence,
      # but it leaves behind the identifiers themselves, so we must
      # purge here
  
      purge_3digit_org_ids(conn)
  
      # Now for users, simply remove all identifiers (and respective
      # user_identifiers) previously added, where a 2 digit version exists
  
      purge_3digit_user_ids(conn)
  
  
  def downgrade():
      # ### commands auto generated by Alembic - please adjust! ###
      pass
      # ### end Alembic commands ###
  