"""empty message

Revision ID: 38a7ac82fd59
Revises: 7cb95304dbcf
Create Date: 2016-10-27 01:04:06.369846

"""

# revision identifiers, used by Alembic.
revision = '38a7ac82fd59'
down_revision = '7cb95304dbcf'

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

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('data_sources',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"), nullable=False),
    sa.Column('source_type', sa.Unicode(length=20), nullable=False),
    sa.Column('source_name', sa.Unicode(length=100), nullable=True),
    sa.Column('source_url', sa.Unicode(length=500), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('source_type', 'source_name', name='source_type_source_name_uc')
    )
    op.create_index(op.f('ix_data_sources_source_name'), 'data_sources', ['source_name'], unique=False)
    op.create_index(op.f('ix_data_sources_source_type'), 'data_sources', ['source_type'], unique=False)
    op.create_table('studies',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"), nullable=False),
    sa.Column('last_updated', sa.TIMESTAMP(), server_default=sa.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('review_id', sa.Integer(), nullable=False),
    sa.Column('data_source_id', sa.Integer(), nullable=False),
    sa.Column('dedupe_status', sa.Unicode(length=20), nullable=True),
    sa.Column('citation_status', sa.Unicode(length=20), nullable=True),
    sa.Column('fulltext_status', sa.Unicode(length=20), nullable=True),
    sa.Column('data_extraction_status', sa.Unicode(length=20), nullable=True),
    sa.Column('tags', postgresql.ARRAY(sa.Unicode(length=25)), server_default='{}', nullable=True),
    sa.ForeignKeyConstraint(['data_source_id'], ['data_sources.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['review_id'], ['reviews.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_studies_citation_status'), 'studies', ['citation_status'], unique=False)
    op.create_index(op.f('ix_studies_data_extraction_status'), 'studies', ['data_extraction_status'], unique=False)
    op.create_index(op.f('ix_studies_data_source_id'), 'studies', ['data_source_id'], unique=False)
    op.create_index(op.f('ix_studies_dedupe_status'), 'studies', ['dedupe_status'], unique=False)
    op.create_index(op.f('ix_studies_fulltext_status'), 'studies', ['fulltext_status'], unique=False)
    op.create_index(op.f('ix_studies_review_id'), 'studies', ['review_id'], unique=False)
    op.create_index(op.f('ix_studies_tags'), 'studies', ['tags'], unique=False)
    op.create_index(op.f('ix_studies_user_id'), 'studies', ['user_id'], unique=False)
    op.create_table('data_extractions',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"), nullable=False),
    sa.Column('last_updated', sa.TIMESTAMP(), server_default=sa.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"), nullable=False),
    sa.Column('review_id', sa.Integer(), nullable=False),
    sa.Column('extracted_items', postgresql.JSONB(none_as_null=True, astext_type=sa.Text()), server_default='{}', nullable=True),
    sa.ForeignKeyConstraint(['id'], ['studies.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['review_id'], ['reviews.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_data_extractions_review_id'), 'data_extractions', ['review_id'], unique=False)
    op.create_table('dedupes',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"), nullable=False),
    sa.Column('review_id', sa.Integer(), nullable=False),
    sa.Column('duplicate_of', sa.BigInteger(), nullable=False),
    sa.Column('duplicate_score', sa.Float(), nullable=False),
    sa.ForeignKeyConstraint(['id'], ['studies.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['review_id'], ['reviews.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_dedupes_review_id'), 'dedupes', ['review_id'], unique=False)
    op.drop_table('fulltexts_extracted_data')
    op.drop_index('ix_citations_status', table_name='citations')
    op.drop_index('ix_citations_tags', table_name='citations')
    op.create_foreign_key(None, 'citations', 'studies', ['id'], ['id'], ondelete='CASCADE')
    op.drop_column('citations', 'tags')
    op.drop_column('citations', 'status')
    op.drop_column('citations', 'deduplication')
    op.drop_column('citations', 'data_source')
    op.drop_index('ix_fulltexts_citation_id', table_name='fulltexts')
    op.drop_index('ix_fulltexts_status', table_name='fulltexts')
    op.drop_constraint('fulltexts_citation_id_fkey', 'fulltexts', type_='foreignkey')
    op.create_foreign_key(None, 'fulltexts', 'studies', ['id'], ['id'], ondelete='CASCADE')
    op.drop_column('fulltexts', 'status')
    op.drop_column('fulltexts', 'citation_id')
    op.add_column('imports', sa.Column('data_source_id', sa.BigInteger(), nullable=False))
    op.alter_column('imports', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False,
               existing_server_default=sa.text("timezone('UTC'::text, now())"))
    op.create_foreign_key(None, 'imports', 'data_sources', ['data_source_id'], ['id'], ondelete='SET NULL')
    op.drop_column('imports', 'data_source')
    op.drop_index('ix_review_plans_review_id', table_name='review_plans')
    op.drop_constraint('review_plans_review_id_fkey', 'review_plans', type_='foreignkey')
    op.create_foreign_key(None, 'review_plans', 'reviews', ['id'], ['id'], ondelete='CASCADE')
    op.drop_column('review_plans', 'review_id')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('review_plans', sa.Column('review_id', sa.INTEGER(), autoincrement=False, nullable=False))
    op.drop_constraint(None, 'review_plans', type_='foreignkey')
    op.create_foreign_key('review_plans_review_id_fkey', 'review_plans', 'reviews', ['review_id'], ['id'], ondelete='CASCADE')
    op.create_index('ix_review_plans_review_id', 'review_plans', ['review_id'], unique=True)
    op.add_column('imports', sa.Column('data_source', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'{}'::jsonb"), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'imports', type_='foreignkey')
    op.alter_column('imports', 'created_at',
               existing_type=postgresql.TIMESTAMP(),
               nullable=True,
               existing_server_default=sa.text("timezone('UTC'::text, now())"))
    op.drop_column('imports', 'data_source_id')
    op.add_column('fulltexts', sa.Column('citation_id', sa.BIGINT(), autoincrement=False, nullable=False))
    op.add_column('fulltexts', sa.Column('status', sa.VARCHAR(length=20), server_default=sa.text("'not_screened'::character varying"), autoincrement=False, nullable=False))
    op.drop_constraint(None, 'fulltexts', type_='foreignkey')
    op.create_foreign_key('fulltexts_citation_id_fkey', 'fulltexts', 'citations', ['citation_id'], ['id'], ondelete='CASCADE')
    op.create_index('ix_fulltexts_status', 'fulltexts', ['status'], unique=False)
    op.create_index('ix_fulltexts_citation_id', 'fulltexts', ['citation_id'], unique=True)
    op.add_column('citations', sa.Column('data_source', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'{}'::jsonb"), autoincrement=False, nullable=True))
    op.add_column('citations', sa.Column('deduplication', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'{}'::jsonb"), autoincrement=False, nullable=True))
    op.add_column('citations', sa.Column('status', sa.VARCHAR(length=20), server_default=sa.text("'not_screened'::character varying"), autoincrement=False, nullable=False))
    op.add_column('citations', sa.Column('tags', postgresql.ARRAY(sa.VARCHAR(length=25)), server_default=sa.text("'{}'::character varying[]"), autoincrement=False, nullable=True))
    op.drop_constraint(None, 'citations', type_='foreignkey')
    op.create_index('ix_citations_tags', 'citations', ['tags'], unique=False)
    op.create_index('ix_citations_status', 'citations', ['status'], unique=False)
    op.create_table('fulltexts_extracted_data',
    sa.Column('id', sa.BIGINT(), nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(), server_default=sa.text("timezone('UTC'::text, now())"), autoincrement=False, nullable=False),
    sa.Column('review_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('fulltext_id', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('extracted_data', postgresql.JSONB(astext_type=sa.Text()), server_default=sa.text("'{}'::jsonb"), autoincrement=False, nullable=True),
    sa.Column('last_updated', postgresql.TIMESTAMP(), server_default=sa.text("timezone('UTC'::text, now())"), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['fulltext_id'], ['fulltexts.id'], name='fulltexts_extracted_data_fulltext_id_fkey', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['review_id'], ['reviews.id'], name='fulltexts_extracted_data_review_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name='fulltexts_extracted_data_pkey')
    )
    op.drop_index(op.f('ix_dedupes_review_id'), table_name='dedupes')
    op.drop_table('dedupes')
    op.drop_index(op.f('ix_data_extractions_review_id'), table_name='data_extractions')
    op.drop_table('data_extractions')
    op.drop_index(op.f('ix_studies_user_id'), table_name='studies')
    op.drop_index(op.f('ix_studies_tags'), table_name='studies')
    op.drop_index(op.f('ix_studies_review_id'), table_name='studies')
    op.drop_index(op.f('ix_studies_fulltext_status'), table_name='studies')
    op.drop_index(op.f('ix_studies_dedupe_status'), table_name='studies')
    op.drop_index(op.f('ix_studies_data_source_id'), table_name='studies')
    op.drop_index(op.f('ix_studies_data_extraction_status'), table_name='studies')
    op.drop_index(op.f('ix_studies_citation_status'), table_name='studies')
    op.drop_table('studies')
    op.drop_index(op.f('ix_data_sources_source_type'), table_name='data_sources')
    op.drop_index(op.f('ix_data_sources_source_name'), table_name='data_sources')
    op.drop_table('data_sources')
    ### end Alembic commands ###
