"""map_to_V2_Taxonomy

Revision ID: 4a9c2107d1b8
Revises: 86a39d98138f
Create Date: 2019-11-29 16:23:21.683736

"""
from alembic import op
import sqlalchemy as sa
from migrations import utils
from sqlalchemy import Column

# revision identifiers, used by Alembic.
revision = '4a9c2107d1b8'
down_revision = '86a39d98138f'
branch_labels = None
depends_on = None

'''
Migrates v1 taxonomy values to v2 hierarchy.

    * old_value_id - old taxonomy value to map
    * transforms - the list of new taxonomy pairs like [category_id, value_id, text(optional)] according to v2 hierarchy

'''
def migrate_taxonomy(old_value_id, transforms):
    bind = op.get_bind()

    activity_types = bind.execute('''SELECT * FROM "ActivityType" WHERE value_id = {0}'''.format(old_value_id)).fetchall()
    if len(activity_types) == 0:
        return
    ids = [row[0] for row in activity_types]
    placeholders = ', '.join(['%s'] * len(ids))
    request = '''DELETE FROM "ActivityType" WHERE activity_id in ({0})'''.format(placeholders)
    bind.execute(request, tuple(ids))
    for row in activity_types:
        for transform in transforms:
            request = ''' INSERT INTO "ActivityType" (activity_id, category_id, value_id, value_text) VALUES (%s, %s, %s, %s) '''
            value_text = row[3]
            if len(transform) == 3:
                value_text = transform[2]
            bind.execute(request, tuple([row[0], transform[0], transform[1], value_text]))

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    bind = op.get_bind()

    op.create_table('ActivityTypeWidget',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ActivityTypeWidget'))
    )

    op.create_table('ActivityTypeCity',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_ActivityTypeCity'))
    )

    op.add_column('ActivityTypeCategory', sa.Column('category_name', sa.String(length=100), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('max_length', sa.SmallInteger(), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('min_length', sa.SmallInteger(), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('parent_bool_value', sa.Boolean(), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('parent_id', sa.SmallInteger(), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('required', sa.Boolean(), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('widget_id', sa.Integer(), nullable=True))
    op.add_column('ActivityTypeCategory', sa.Column('default_value', sa.String(length=100), nullable=True))

    op.alter_column('ActivityTypeCategory', 'name', existing_type=sa.VARCHAR(length=50), nullable=True)
    op.alter_column('ActivityType', 'value_text', existing_type=sa.String(length=50), type_=sa.String(length=100), nullable=False)

    op.drop_constraint("single_value", "ActivityType", type_="check")

    op.drop_constraint(op.f('fk_ActivityTypeValue_category_id_ActivityTypeCategory'), 'ActivityTypeValue',
                       type_='foreignkey')
    op.drop_constraint(op.f('fk_ActivityTypeCategory_parent_value_id_ActivityTypeValue'), 'ActivityTypeCategory',
                       type_='foreignkey')

    utils.import_data("ActivityTypeValue", "taxonomy_values_v2.csv")
    utils.import_data("ActivityTypeCategory", "taxonomy_categories_v2.csv")
    utils.import_data("ActivityTypeWidget", "taxonomy_widgets_v2.csv")

    op.create_foreign_key(op.f('fk_ActivityTypeValue_category_id_ActivityTypeCategory'), 'ActivityTypeValue',
                          'ActivityTypeCategory', ['category_id'], ['id'],
                           ondelete='CASCADE')
    op.create_foreign_key(op.f('fk_ActivityTypeCategory_parent_value_id_ActivityTypeValue'), 'ActivityTypeCategory',
                          'ActivityTypeValue', ['parent_value_id'], ['id'])

    op.create_foreign_key(op.f('fk_ActivityTypeCategory_widget_id_ActivityTypeWidget'), 'ActivityTypeCategory', 'ActivityTypeWidget', ['widget_id'], ['id'])

    migrate_taxonomy(39, [[17, 54]])
    migrate_taxonomy(38, [[17, 56]])

    migrate_taxonomy(49, [[17, 57], [47, 145]])
    migrate_taxonomy(50, [[17, 57], [47, 144]])
    migrate_taxonomy(51, [[17, 57], [47, 143]])
    migrate_taxonomy(52, [[17, 57], [47, 146]])

    migrate_taxonomy(48, [[17, 56], [34, 101]])
    migrate_taxonomy(47, [[17, 56], [34, 101], [45, 138, "Bing"]])
    migrate_taxonomy(46, [[17, 56], [34, 99]])

    migrate_taxonomy(45, [[17, 58]])
    migrate_taxonomy(41, [[17, 58], [63, 186]])
    migrate_taxonomy(42, [[17, 58], [63, 187]])
    migrate_taxonomy(43, [[17, 58], [63, 188]])
    migrate_taxonomy(44, [[17, 58], [63, 190]])

    migrate_taxonomy(40, [[17, 56], [34, 101], [45, 138, "Home Page Takeover"]])

    migrate_taxonomy(7, [[17, 67]])
    migrate_taxonomy(11, [[17, 67]])
    migrate_taxonomy(13, [[17, 65]])

    migrate_taxonomy(17, [[17, 60], [106, 317]])
    migrate_taxonomy(18, [[17, 60], [106, 315]])
    migrate_taxonomy(19, [[17, 60], [106, 316]])
    migrate_taxonomy(20, [[17, 60], [106, 313]])
    migrate_taxonomy(21, [[17, 60], [106, 318]])

    migrate_taxonomy(27, [[17, 58], [63, 187]])
    migrate_taxonomy(28, [[17, 58], [63, 186]])
    migrate_taxonomy(29, [[17, 58], [63, 190]])
    migrate_taxonomy(30, [[17, 58], [63, 188]])
    migrate_taxonomy(31, [[17, 58], [63, 191]])
    migrate_taxonomy(53, [[17, 58], [63, 189]])

    migrate_taxonomy(3, [[17, 61], [113, 326]])
    migrate_taxonomy(6, [[17, 62]])
    migrate_taxonomy(8, [])

    migrate_taxonomy(15, [[17, 65]])
    migrate_taxonomy(14, [])

    migrate_taxonomy(9, [[17, 65]])
    migrate_taxonomy(2, [[17, 59]])
    migrate_taxonomy(12, [])
    migrate_taxonomy(16, [[17, 55]])

    migrate_taxonomy(26, [[17, 68]])
    migrate_taxonomy(23, [[17, 69]])
    migrate_taxonomy(25, [[17, 72]])
    migrate_taxonomy(22, [[17, 70]])
    migrate_taxonomy(24, [[17, 71]])

    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    bind = op.get_bind()

    op.drop_constraint(op.f('fk_ActivityTypeValue_category_id_ActivityTypeCategory'), 'ActivityTypeValue',
                       type_='foreignkey')
    op.drop_constraint(op.f('fk_ActivityTypeCategory_parent_value_id_ActivityTypeValue'), 'ActivityTypeCategory',
                       type_='foreignkey')

    bind.execute('''DELETE FROM "ActivityTypeValue" WHERE id BETWEEN 54 AND 545''')
    bind.execute('''DELETE FROM "ActivityTypeCategory" WHERE id BETWEEN 17 AND 188''')

    op.create_foreign_key(op.f('fk_ActivityTypeValue_category_id_ActivityTypeCategory'), 'ActivityTypeValue',
                          'ActivityTypeCategory', ['category_id'], ['id'])
    op.create_foreign_key(op.f('fk_ActivityTypeCategory_parent_value_id_ActivityTypeValue'), 'ActivityTypeCategory',
                          'ActivityTypeValue', ['parent_value_id'], ['id'])

    op.drop_constraint(op.f('fk_ActivityTypeCategory_widget_id_ActivityTypeWidget'), 'ActivityTypeCategory', type_='foreignkey')
    op.drop_column('ActivityTypeCategory', 'widget_id')
    op.drop_column('ActivityTypeCategory', 'required')
    op.drop_column('ActivityTypeCategory', 'parent_id')
    op.drop_column('ActivityTypeCategory', 'parent_bool_value')
    op.drop_column('ActivityTypeCategory', 'min_length')
    op.drop_column('ActivityTypeCategory', 'max_length')
    op.drop_column('ActivityTypeCategory', 'category_name')
    op.drop_table('ActivityTypeWidget')
    op.drop_table('ActivityTypeCity')
    op.alter_column('ActivityTypeCategory', 'name',
               existing_type=sa.VARCHAR(length=50),
               nullable=False)
    # ### end Alembic commands ###