"""Dedup related entities

Revision ID: 0361dbb4d96f
Revises: a5d97318b751
Create Date: 2016-12-18 22:00:05.397609

"""

# revision identifiers, used by Alembic.
revision = '0361dbb4d96f'
down_revision = 'a5d97318b751'

from alembic import op
import sqlalchemy as sa


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('entity',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('type', sa.String(length=256), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_entity', 'entity', ['name', 'type'], unique=True)
    op.create_table('session_entity',
    sa.Column('session_id', sa.Integer(), nullable=True),
    sa.Column('entity_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['entity_id'], ['entity.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['session_id'], ['session.id'], ondelete='CASCADE')
    )
    op.create_index('ix_session_entity_session_id_entity_id', 'session_entity', ['session_id', 'entity_id'], unique=False)
    op.create_table('test_entity',
    sa.Column('test_id', sa.Integer(), nullable=True),
    sa.Column('entity_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['entity_id'], ['entity.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['test_id'], ['test.id'], ondelete='CASCADE')
    )
    op.create_index('ix_test_entity_test_id_entity_id', 'test_entity', ['test_id', 'entity_id'], unique=False)

    op.execute('INSERT INTO entity (type, name) SELECT DISTINCT type, name FROM related_entity')
    op.execute('INSERT INTO session_entity (session_id, entity_id) SELECT session.id, entity.id FROM session JOIN related_entity ON related_entity.session_id = session.id JOIN entity ON related_entity.name = entity.name and related_entity.type = entity.type')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_test_entity_test_id_entity_id', table_name='test_entity')
    op.drop_table('test_entity')
    op.drop_index('ix_session_entity_session_id_entity_id', table_name='session_entity')
    op.drop_table('session_entity')
    op.drop_index('ix_entity', table_name='entity')
    op.drop_table('entity')
    # ### end Alembic commands ###
