text: """Add NGSBarcodePair table and seq.
  
  Revision ID: 5a4d81becc55
  Revises: 2508b1c64724
  Create Date: 2015-11-16 16:45:50.744702
  
  """
  
  # revision identifiers, used by Alembic.
  revision = '5a4d81becc55'
  down_revision = '2508b1c64724'
  
  import csv
  import logging
  from datetime import datetime
  
  from alembic import op
  import sqlalchemy as db
  from sqlalchemy.sql import table, column
  from sqlalchemy import String, Integer, DateTime, Enum
  
  from app.dbmodels import create_unique_object_id
  from app.dbmodels import barcode_sequence_to_barcode_sample
  from app.dbmodels import NGS_BARCODE_PLATE, NGS_BARCODE_PLATE_TYPE
  from app.plate_to_plate_maps import maps_json
  
  
  logging.basicConfig(level=logging.INFO)
  
  def create_barcode_table():
      #
      # First the barcode database, which becomes a regular table.
      #
  
      method = 'USE_EXISTING'
      if method == 'CREATE_NEW':
          create_or_define_fn = op.create_table
      elif method == 'USE_EXISTING':
          create_or_define_fn = table
  
      ngs_barcode_pair_table = create_or_define_fn(
          'ngs_barcode_pair',
          db.Column('pk',
                    db.Integer,
                    primary_key=True),
          db.Column('i7_sequence_id',
                    db.String(40),
                    db.ForeignKey('backend.barcode_sequence.sequence_id'),
                    nullable=False),
          db.Column('i5_sequence_id',
                    db.String(40),
                    db.ForeignKey('backend.barcode_sequence.sequence_id'),
                    nullable=False),
          db.Column('reverse_primer_i7_well_row',
                    db.String(10),
                    nullable=False),
          db.Column('reverse_primer_i7_well_column',
                    db.Integer,
                    nullable=False),
          db.Column('forward_primer_i5_well_row',
                    db.String(10),
                    nullable=False),
          db.Column('forward_primer_i5_well_column',
                    db.Integer,
                    nullable=False)
      )
      ngs_barcode_pair_table.schema = 'ngs'
      return ngs_barcode_pair_table
  
  
  def populate_values(ngs_barcode_pair_table):
      #
      # Populate its values from the file Austin provided.
      #
      csvfilename = 'database/ngs_barcode_database.csv'
      csvfile = open(csvfilename, 'rU')
      if not csvfile:
          raise "Cannot open %s" % csvfilename
      fieldnames = ("pk",
                    "i7_sequence_id",
                    "i5_sequence_id",
                    "reverse_primer_i7_well_row",
                    "reverse_primer_i7_well_column",
                    "forward_primer_i5_well_row",
                    "forward_primer_i5_well_column")
      csvreader = csv.DictReader(csvfile, fieldnames)
      ignore_header = next(csvreader)
      ngs_barcode_pairs = [row for row in csvreader]
      csvfile.close()
      for ix, pair in enumerate(ngs_barcode_pairs):
          assert int(pair["pk"]) == ix
      max_value = ix
      print "Inserting %d pairs..." % len(ngs_barcode_pairs)
      op.bulk_insert(ngs_barcode_pair_table, ngs_barcode_pairs)
      print ("Created table 'ngs_barcode_pair': "
             "%d ngs_barcode_pairs added to DB" % len(ngs_barcode_pairs))
      return ngs_barcode_pairs, max_value
  
  
  def create_cycle_sequence(max_value):
      #
      # Now create a (postgres-specific) sequence which cycles
      # through the barcodes.  For non-postgres, a modulo operation
      # could be used instead.
      #
      sql = '''
          CREATE SEQUENCE "ngs_barcode_pair_index_seq"
          MINVALUE 0
          INCREMENT BY 1
          MAXVALUE %d
          START WITH 2618
          CYCLE''' % max_value
      print "Created sequence 'ngs_barcode_pair_index_seq': %s" % sql
      op.execute(sql)
  
  
  def build_plate_wells(ngs_barcode_pairs):
      # Build the barcode plate wells, making sure there is
      # no inconsistent data.
      # Return barcode sequences too.
  
      json_maps = maps_json()
      plate_map = json_maps["row_column_maps"][NGS_BARCODE_PLATE_TYPE]
      """
                  "SPTT_0006": {
                  # 384 well plate
                   1: {"row":"A", "column": 1}
                  ,2: {"row":"A", "column": 2}
      """
      reverse_map = {(dct["row"], int(dct["column"])): well_id
                     for well_id, dct in plate_map.items()}
      """
                   ("A", 1): 1,
                   ("A", 2): 2
      """
      """
      modulo_index
      i7_sequence_id,  i5_sequence_id
      reverse_primer_i7_well_row, reverse_primer_i7_well_column
      forward_primer_i5_well_row,  forward_primer_i5_well_column
        0 BC_00029  BC_00125  A 1 A 2
        1 BC_00030  BC_00126  C 1 C 2
        2 BC_00031  BC_00127  E 1 E 2
      """
      wells = {}
      barcode_sequences = set()
      for pair in ngs_barcode_pairs:
          for (row_key, col_key, seq_key) in (
              ("reverse_primer_i7_well_row",
               "reverse_primer_i7_well_column",
               "i7_sequence_id"),
              ("forward_primer_i5_well_row",
               "forward_primer_i5_well_column",
               "i5_sequence_id"),
          ):
              rowcol = (pair[row_key], int(pair[col_key]))
              well_id = reverse_map[rowcol]
              barcode_seq = pair[seq_key]
              # print("Adding barcode seq %s" % barcode_seq)
              barcode_sequences.add(barcode_seq)
              if well_id in wells:
                  assert wells[well_id] == barcode_seq
              else:
                  wells[well_id] = barcode_seq
      return wells, barcode_sequences
  
  
  def insert_barcode_sample_records(barcode_sequences):
      #
      # Now populate samples (of some type TBD) for the barcode sequences
      # so we can put them into a plate layout.
      #
  
      unq_srtd_barcode_seqs = sorted(list(set(barcode_sequences)))
      sample_migration_table = table('sample',
          column('sample_id', String),
          column('type_id', Integer),
          column('order_item_id', String),
          column('date_created', DateTime),
          column('name', String),
          column('description', String),
          column('external_barcode', String),
          column('operator_id', String),
      )
      sample_migration_table.schema = 'backend'
  
      rows = [{'sample_id': barcode_sequence_to_barcode_sample(seq_name),
               'type_id': 'blended_sample',
               'order_item_id': 'WOI_566f649100bc152ceec0c357',
               'date_created': datetime.now(),
               'name': seq_name,
               'description': 'Barcode sample for %s' % seq_name,
               'external_barcode': None,
               'operator_id': 'CL',
               }
              for seq_name in unq_srtd_barcode_seqs
              ]
      op.bulk_insert(sample_migration_table, rows)
  
  
  def insert_barcode_plate_record():
      #
      # Now insert a parentless NGS_BARCODE_PLATE_TEST1.
      #
      bc_plate_id = create_unique_object_id('SPLT_')
      sp_migration_table = table(
          'sample_plate',
          column('plate_id', String),
          column('type_id', Integer),
          column('operator_id', String),
          column('storage_location', String),
          column('date_created', DateTime),
          column('description', String),
          column('external_barcode', String),
          column('name', String),
          column('status', Enum('disposed', 'in_use', 'new'))
      )
      sp_migration_table.schema = 'sampletrack'
      barcode_plate = {
          'plate_id': bc_plate_id,
          'type_id': NGS_BARCODE_PLATE_TYPE,
          'operator_id': 'AH',
          'storage_location': 'LOC_0064',  # FAKE STORAGE LOCATION
          'date_created': datetime.now(),
          'description': 'Barcoding Plate Test 1',
          'external_barcode': NGS_BARCODE_PLATE,
          'name': 'NGS Barcode Plate Test 1',
          'status': 'in_use'
      }
      op.bulk_insert(sp_migration_table, [barcode_plate])
      return bc_plate_id
  
  
  def insert_barcode_well_records(bc_plate_id, wells):
      #
      # Now populate that plate NGS_BARCODE_PLATE_TEST1 with samples.
      #
  
      json_maps = maps_json()
      plate_map = json_maps["row_column_maps"][NGS_BARCODE_PLATE_TYPE]
  
      splt_migration_table = table(
          'well_sample',
          column('plate_id', String),
          column('sample_id', String),
          column('well_id', Integer),
          column('row', Integer),
          column('column', Integer),
          column('date_created', DateTime),
          column('operator_id', String),
          column('status', Enum('active',))
      )
      splt_migration_table.schema = 'sampletrack'
      rows = [{'plate_id': bc_plate_id,
               'sample_id': barcode_sequence_to_barcode_sample(wells[well_id]),
               'well_id': well_id,
               'row': row_col_dict["row"],
               'column': row_col_dict["column"],
               'date_created': datetime.now(),
               'operator_id': 'AH',
               'status': 'active'
               }
              for (well_id, row_col_dict) in plate_map.items()
              if well_id in wells]
      op.bulk_insert(splt_migration_table, rows)
  
      '''
      for well_id, row_col_dict in plate_map:
          if well_id in wells:
              # i7_wells[i7_seq_id] = reverse_map[i7_rowcol]
              barcode_id = 'BC_233'
              barcode_well = {
                  'plate_id': bc_plate_id,
                  'sample_id': barcode_id,
                  'well_id': well_id,
                  'row': row_col_dict["row"],
                  'column': row_col_dict["column"],
                  'date_created': datetime.now(),
                  'operator_id': 'AH',
                  'status': 'active'
              }
      '''
  
  
  def upgrade():
      """spammy since monitoring high latency connection"""
      # print("1. create_barcode_table")
      # ngs_barcode_pair_table = create_barcode_table()
      # print("2. populate_values")
      # ngs_barcode_pairs, max_value = populate_values(ngs_barcode_pair_table)
      print("3. create_cycle_sequence")
      create_cycle_sequence(8741)  # max_value)
      # print("4. build_plate_wells")
      # wells, barcode_sequences = build_plate_wells(ngs_barcode_pairs)
      # print("5. insert_barcode_sample_records")
      # insert_barcode_sample_records(barcode_sequences)
      # print("6. insert_barcode_plate_record")
      # bc_plate_id = insert_barcode_plate_record()
      # print("7. insert_barcode_well_records")
      # insert_barcode_well_records(bc_plate_id, wells)
  
  
  def downgrade():
      delete_sql = 'delete from sample_plate where external_barcode = :bc'
      args = {"bc": NGS_BARCODE_PLATE}
      op.execute(db.text(delete_sql).bindparams(**args))
      op.execute('drop table if exists ngs_barcode_pair')
      op.execute('drop sequence if exists ngs_barcode_pair_index_seq')
  