"""essage=Updated pubmed schema with MeSH FKs

Revision ID: c55ccf39e932
Revises: c125ca8aa9be
Create Date: 2019-03-31 10:15:11.053787

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'c55ccf39e932'
down_revision = 'c125ca8aa9be'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_citation_descriptors_qualifiers_descriptor_id_descriptors', 'citation_descriptors_qualifiers', schema='pubmed', type_='foreignkey')
    op.drop_constraint('fk_citation_descriptors_qualifiers_qualifier_id_qualifiers', 'citation_descriptors_qualifiers', schema='pubmed', type_='foreignkey')
    op.drop_index('ix_pubmed_descriptors_descriptor', table_name='descriptors', schema='pubmed')
    op.drop_index('ix_pubmed_descriptors_uid', table_name='descriptors', schema='pubmed')
    op.drop_table('descriptors', schema='pubmed')
    op.drop_index('ix_pubmed_qualifiers_qualifier', table_name='qualifiers', schema='pubmed')
    op.drop_index('ix_pubmed_qualifiers_uid', table_name='qualifiers', schema='pubmed')
    op.drop_table('qualifiers', schema='pubmed')
    op.drop_constraint('fk_citation_chemicals_chemical_id_chemicals', 'citation_chemicals', schema='pubmed', type_='foreignkey')
    op.drop_index('ix_pubmed_chemicals_num_registry', table_name='chemicals', schema='pubmed')
    op.drop_index('ix_pubmed_chemicals_uid', table_name='chemicals', schema='pubmed')
    op.drop_table('chemicals', schema='pubmed')
    op.create_foreign_key(op.f('fk_citation_chemicals_chemical_id_descriptors'), 'citation_chemicals', 'descriptors', ['chemical_id'], ['descriptor_id'], source_schema='pubmed', referent_schema='mesh')
    op.create_foreign_key(op.f('fk_citation_descriptors_qualifiers_descriptor_id_descriptors'), 'citation_descriptors_qualifiers', 'descriptors', ['descriptor_id'], ['descriptor_id'], source_schema='pubmed', referent_schema='mesh')
    op.create_foreign_key(op.f('fk_citation_descriptors_qualifiers_qualifier_id_qualifiers'), 'citation_descriptors_qualifiers', 'qualifiers', ['qualifier_id'], ['qualifier_id'], source_schema='pubmed', referent_schema='mesh')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('chemicals',
    sa.Column('chemical_id', sa.BIGINT(), nullable=False),
    sa.Column('num_registry', sa.VARCHAR(), autoincrement=False, nullable=True),
    sa.Column('uid', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('chemical', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('chemical_id', name='pk_chemicals'),
    schema='pubmed'
    )
    op.create_index('ix_pubmed_chemicals_uid', 'chemicals', ['uid'], unique=True, schema='pubmed')
    op.create_index('ix_pubmed_chemicals_num_registry', 'chemicals', ['num_registry'], unique=False, schema='pubmed')
    op.create_table('qualifiers',
    sa.Column('qualifier_id', sa.BIGINT(), nullable=False),
    sa.Column('uid', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('qualifier', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('qualifier_id', name='pk_qualifiers'),
    schema='pubmed'
    )
    op.create_index('ix_pubmed_qualifiers_uid', 'qualifiers', ['uid'], unique=True, schema='pubmed')
    op.create_index('ix_pubmed_qualifiers_qualifier', 'qualifiers', ['qualifier'], unique=True, schema='pubmed')
    op.create_table('descriptors',
    sa.Column('descriptor_id', sa.BIGINT(), nullable=False),
    sa.Column('uid', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.Column('descriptor', sa.VARCHAR(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('descriptor_id', name='pk_descriptors'),
    schema='pubmed'
    )
    op.create_index('ix_pubmed_descriptors_uid', 'descriptors', ['uid'], unique=True, schema='pubmed')
    op.create_index('ix_pubmed_descriptors_descriptor', 'descriptors', ['descriptor'], unique=True, schema='pubmed')
    op.drop_constraint(op.f('fk_citation_descriptors_qualifiers_qualifier_id_qualifiers'), 'citation_descriptors_qualifiers', schema='pubmed', type_='foreignkey')
    op.drop_constraint(op.f('fk_citation_descriptors_qualifiers_descriptor_id_descriptors'), 'citation_descriptors_qualifiers', schema='pubmed', type_='foreignkey')
    op.create_foreign_key('fk_citation_descriptors_qualifiers_qualifier_id_qualifiers', 'citation_descriptors_qualifiers', 'qualifiers', ['qualifier_id'], ['qualifier_id'], source_schema='pubmed', referent_schema='pubmed')
    op.create_foreign_key('fk_citation_descriptors_qualifiers_descriptor_id_descriptors', 'citation_descriptors_qualifiers', 'descriptors', ['descriptor_id'], ['descriptor_id'], source_schema='pubmed', referent_schema='pubmed')
    op.drop_constraint(op.f('fk_citation_chemicals_chemical_id_descriptors'), 'citation_chemicals', schema='pubmed', type_='foreignkey')
    op.create_foreign_key('fk_citation_chemicals_chemical_id_chemicals', 'citation_chemicals', 'chemicals', ['chemical_id'], ['chemical_id'], source_schema='pubmed', referent_schema='pubmed')
    # ### end Alembic commands ###
