"""add json_data fields and make all non-critical fields optional

Revision ID: b5edaa4713e3
Revises: a11c2df10797
Create Date: 2021-10-29 14:19:16.650312

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = 'b5edaa4713e3'
down_revision = 'a11c2df10797'
branch_labels = None
depends_on = None


def upgrade():
    for table in ['participants', 'samples', 'shipments']:
        op.add_column(
            table,
            sa.Column('json_data', postgresql.JSONB(astext_type=sa.Text()),
                nullable=False,
                server_default=sa.text("'{}'::jsonb"),
            )
        )
    op.add_column('samples', sa.Column('manifest_id', sa.String(), nullable=True))
    op.create_foreign_key(None, 'samples', 'shipments', ['trial_id', 'manifest_id'], ['trial_id', 'manifest_id'])
    op.execute("UPDATE samples set manifest_id=shipment_manifest_id")
    op.alter_column('samples', 'manifest_id', existing_type=sa.String(), nullable=False)
    op.drop_column('samples', 'shipment_manifest_id')

    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('aliquots', 'aliquot_replacement',
               existing_type=postgresql.ENUM('Replacement Not Requested', 'Replacement Requested', 'Replacement Tested', 'Not Reported', 'Other', name='replace_enum'),
               nullable=True)
    op.alter_column('aliquots', 'aliquot_status',
               existing_type=postgresql.ENUM('Aliquot Returned', 'Aliquot Exhausted', 'Remainder used for other Assay', 'Aliquot Leftover', 'Other', name='aliquot_status_enum'),
               nullable=True)
    op.alter_column('participants', 'trial_participant_id',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('samples', 'collection_event_name',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('samples', 'parent_sample_id',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('samples', 'sample_location',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('samples', 'type_of_sample',
               existing_type=postgresql.ENUM('Tumor Tissue', 'Normal Tissue', 'Skin Tissue', 'Blood', 'Bone Marrow', 'Cerebrospinal Fluid', 'Lymph Node', 'Stool', 'Cell Product', 'White Blood Cell Apheresis', 'Not Reported', 'Other', name='sample_types_enum'),
               nullable=True)
    op.alter_column('shipments', 'assay_priority',
               existing_type=postgresql.ENUM('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', 'Not Reported', 'Other', name='assay_priority_enum'),
               nullable=True)
    op.alter_column('shipments', 'assay_type',
               existing_type=postgresql.ENUM('ATACseq', 'CyTOF', 'ELISA', 'H&E', 'IHC', 'mIF', 'mIHC', 'Olink', 'RNAseq', 'TCRseq', 'WES', name='assay_enum'),
               nullable=True)
    op.alter_column('shipments', 'courier',
               existing_type=postgresql.ENUM('FEDEX', 'USPS', 'UPS', 'Inter-Site Delivery', name='courier_enum'),
               nullable=True)
    op.alter_column('shipments', 'tracking_number',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('shipments', 'account_number',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('shipments', 'shipping_condition',
               existing_type=postgresql.ENUM('Frozen_Dry_Ice', 'Frozen_Shipper', 'Ice_Pack', 'Ambient', 'Not Reported', 'Other', name='shipping_condition_enum'),
               nullable=True)
    op.alter_column('shipments', 'date_shipped',
               existing_type=sa.DATE(),
               nullable=True)
    op.alter_column('shipments', 'date_received',
               existing_type=sa.DATE(),
               nullable=True)
    op.alter_column('shipments', 'quality_of_shipment',
               existing_type=postgresql.ENUM('Specimen shipment received in good condition', 'Specimen shipment received in poor condition', 'Not Reported', 'Other', name='quality_of_shipment_enum'),
               nullable=True)
    op.alter_column('shipments', 'ship_from',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('shipments', 'ship_to',
               existing_type=sa.VARCHAR(),
               nullable=True)
    op.alter_column('shipments', 'receiving_party',
               existing_type=postgresql.ENUM('MDA_Wistuba', 'MDA_Bernatchez', 'MDA_Al-Atrash', 'MSSM_Gnjatic', 'MSSM_Rahman', 'MSSM_Kim-Schulze', 'MSSM_Bongers', 'DFCI_Wu', 'DFCI_Hodi', 'DFCI_Severgnini', 'DFCI_Livak', 'Broad_Cibulskis', 'Stanf_Maecker', 'Stanf_Bendall', 'NCH', 'Adaptive', 'FNLCR_MoCha', name='receiving_party_enum'),
               nullable=True)
    # ### end Alembic commands ###


def downgrade():
    op.add_column('samples', sa.Column('shipment_manifest_id', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.create_foreign_key(None, 'samples', 'shipments', ['trial_id', 'shipment_manifest_id'], ['trial_id', 'manifest_id'])
    op.execute("UPDATE samples set shipment_manifest_id=manifest_id")
    op.alter_column('samples', 'shipment_manifest_id', existing_type=sa.VARCHAR(), nullable=False)
    op.drop_column('samples', 'manifest_id')
    
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('shipments', 'receiving_party',
               existing_type=postgresql.ENUM('MDA_Wistuba', 'MDA_Bernatchez', 'MDA_Al-Atrash', 'MSSM_Gnjatic', 'MSSM_Rahman', 'MSSM_Kim-Schulze', 'MSSM_Bongers', 'DFCI_Wu', 'DFCI_Hodi', 'DFCI_Severgnini', 'DFCI_Livak', 'Broad_Cibulskis', 'Stanf_Maecker', 'Stanf_Bendall', 'NCH', 'Adaptive', 'FNLCR_MoCha', name='receiving_party_enum'),
               nullable=False)
    op.alter_column('shipments', 'ship_to',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('shipments', 'ship_from',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('shipments', 'quality_of_shipment',
               existing_type=postgresql.ENUM('Specimen shipment received in good condition', 'Specimen shipment received in poor condition', 'Not Reported', 'Other', name='quality_of_shipment_enum'),
               nullable=False)
    op.alter_column('shipments', 'date_received',
               existing_type=sa.DATE(),
               nullable=False)
    op.alter_column('shipments', 'date_shipped',
               existing_type=sa.DATE(),
               nullable=False)
    op.alter_column('shipments', 'shipping_condition',
               existing_type=postgresql.ENUM('Frozen_Dry_Ice', 'Frozen_Shipper', 'Ice_Pack', 'Ambient', 'Not Reported', 'Other', name='shipping_condition_enum'),
               nullable=False)
    op.alter_column('shipments', 'account_number',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('shipments', 'tracking_number',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('shipments', 'courier',
               existing_type=postgresql.ENUM('FEDEX', 'USPS', 'UPS', 'Inter-Site Delivery', name='courier_enum'),
               nullable=False)
    op.alter_column('shipments', 'assay_type',
               existing_type=postgresql.ENUM('ATACseq', 'CyTOF', 'ELISA', 'H&E', 'IHC', 'mIF', 'mIHC', 'Olink', 'RNAseq', 'TCRseq', 'WES', name='assay_enum'),
               nullable=False)
    op.alter_column('shipments', 'assay_priority',
               existing_type=postgresql.ENUM('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', 'Not Reported', 'Other', name='assay_priority_enum'),
               nullable=False)
    op.drop_column('shipments', 'json_data')
    op.alter_column('samples', 'type_of_sample',
               existing_type=postgresql.ENUM('Tumor Tissue', 'Normal Tissue', 'Skin Tissue', 'Blood', 'Bone Marrow', 'Cerebrospinal Fluid', 'Lymph Node', 'Stool', 'Cell Product', 'White Blood Cell Apheresis', 'Not Reported', 'Other', name='sample_types_enum'),
               nullable=False)
    op.alter_column('samples', 'sample_location',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('samples', 'parent_sample_id',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.alter_column('samples', 'collection_event_name',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.drop_column('samples', 'json_data')
    op.alter_column('participants', 'trial_participant_id',
               existing_type=sa.VARCHAR(),
               nullable=False)
    op.drop_column('participants', 'json_data')
    op.alter_column('aliquots', 'aliquot_status',
               existing_type=postgresql.ENUM('Aliquot Returned', 'Aliquot Exhausted', 'Remainder used for other Assay', 'Aliquot Leftover', 'Other', name='aliquot_status_enum'),
               nullable=False)
    op.alter_column('aliquots', 'aliquot_replacement',
               existing_type=postgresql.ENUM('Replacement Not Requested', 'Replacement Requested', 'Replacement Tested', 'Not Reported', 'Other', name='replace_enum'),
               nullable=False)
    # ### end Alembic commands ###
