"""alter metricsRaceCache table and create indexes

Revision ID: bf7f784daca9
Revises: 93d831aa6fb4
Create Date: 2019-01-31 16:53:34.008379

"""
from alembic import op
import sqlalchemy as sa
import model.utils
from sqlalchemy.dialects import mysql

from participant_enums import PhysicalMeasurementsStatus, QuestionnaireStatus, OrderStatus
from participant_enums import WithdrawalStatus, WithdrawalReason, SuspensionStatus, QuestionnaireDefinitionStatus
from participant_enums import EnrollmentStatus, Race, SampleStatus, OrganizationType, BiobankOrderStatus
from participant_enums import MetricSetType, MetricsKey
from model.site_enums import SiteStatus, EnrollingStatus, DigitalSchedulingStatus, ObsoleteStatus
from model.code import CodeType

# revision identifiers, used by Alembic.
revision = 'bf7f784daca9'
down_revision = '93d831aa6fb4'
branch_labels = None
depends_on = None


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()



def upgrade_rdr():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_index('calendar_idx', 'calendar', ['day'], unique=False)
    op.add_column('metrics_race_cache', sa.Column('american_indian_alaska_native', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('asian', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('black_african_american', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('hispanic_latino_spanish', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('middle_eastern_north_african', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('multi_ancestry', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('native_hawaiian_other_pacific_islander', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('no_ancestry_checked', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('none_of_these_fully_describe_me', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('prefer_not_to_answer', sa.Integer(), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('white', sa.Integer(), nullable=False))
    op.drop_column('metrics_race_cache', 'race_count')
    op.drop_column('metrics_race_cache', 'race_name')
    op.create_index('participant_withdrawl_sign_up_hpo', 'participant', ['participant_id', 'withdrawal_status', 'sign_up_time', 'hpo_id', 'is_ghost_id'], unique=False)
    op.drop_index('participant_sign_up_time', table_name='participant')
    op.drop_index('participant_summary_core_ordered_time', table_name='participant_summary')
    op.drop_index('participant_summary_core_stored_time', table_name='participant_summary')
    op.drop_index('participant_summary_member_time', table_name='participant_summary')
    op.drop_index('participant_summary_sign_up_time', table_name='participant_summary')
    # ### end Alembic commands ###


def downgrade_rdr():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_index('participant_summary_sign_up_time', 'participant_summary', ['sign_up_time'], unique=False)
    op.create_index('participant_summary_member_time', 'participant_summary', ['enrollment_status_member_time'], unique=False)
    op.create_index('participant_summary_core_stored_time', 'participant_summary', ['enrollment_status_core_stored_sample_time'], unique=False)
    op.create_index('participant_summary_core_ordered_time', 'participant_summary', ['enrollment_status_core_ordered_sample_time'], unique=False)
    op.create_index('participant_sign_up_time', 'participant', ['sign_up_time'], unique=False)
    op.drop_index('participant_withdrawl_sign_up_hpo', table_name='participant')
    op.add_column('metrics_race_cache', sa.Column('race_name', mysql.VARCHAR(length=255), nullable=False))
    op.add_column('metrics_race_cache', sa.Column('race_count', mysql.INTEGER(display_width=11), autoincrement=False, nullable=False))
    op.drop_column('metrics_race_cache', 'white')
    op.drop_column('metrics_race_cache', 'prefer_not_to_answer')
    op.drop_column('metrics_race_cache', 'none_of_these_fully_describe_me')
    op.drop_column('metrics_race_cache', 'no_ancestry_checked')
    op.drop_column('metrics_race_cache', 'native_hawaiian_other_pacific_islander')
    op.drop_column('metrics_race_cache', 'multi_ancestry')
    op.drop_column('metrics_race_cache', 'middle_eastern_north_african')
    op.drop_column('metrics_race_cache', 'hispanic_latino_spanish')
    op.drop_column('metrics_race_cache', 'black_african_american')
    op.drop_column('metrics_race_cache', 'asian')
    op.drop_column('metrics_race_cache', 'american_indian_alaska_native')
    op.drop_index('calendar_idx', table_name='calendar')
    # ### end Alembic commands ###


def upgrade_metrics():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade_metrics():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


@@ -1550,243 +1550,36 @@
 mn('
-hispanic_latino_spanish', sa.Integer(), nullable=False))%0A    op.add_column('metrics_race_cache', sa.Column('middle_eastern_north_african', sa.Integer(), nullable=False))%0A    op.add_column('metrics_race_cache', sa.Column('multi_ancestry
+middle_eastern_north_african
 ', s
@@ -1782,35 +1782,129 @@
 Column('
-no_ancestry_checked
+white', sa.Integer(), nullable=False))%0A    op.add_column('metrics_race_cache', sa.Column('hispanic_latino_spanish
 ', sa.In
@@ -2193,37 +2193,150 @@
 he', sa.Column('
-white
+multi_ancestry', sa.Integer(), nullable=False))%0A    op.add_column('metrics_race_cache', sa.Column('no_ancestry_checked
 ', sa.Integer(),

fix race column order (#914)