"""Dedupe menu combo items.

Revision ID: 2f2346b8b7cb
Revises: c399658b0b3b
Create Date: 2020-02-20 14:01:26.810000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import Column, String, Index, ForeignKeyConstraint, UniqueConstraint

revision = '2f2346b8b7cb'
down_revision = 'c399658b0b3b'
branch_labels = None
depends_on = None

first_agent_version = '20.3.4'


def upgrade():
    """
    Use batch-operations to support sqlite. If you are modifying any tables with foreign keys, be sure to disable
    foreign keys while upgrading.

    > conn = op.get_bind()
    > try:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    >     with op.batch_alter_table('table_name') as batch_op:
    >         pass
    > finally:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    """
    connection = op.get_bind()
    try:
        connection.execute('PRAGMA foreign_keys = "0";')

        op.create_table(
            'menu_combo_item_groups_menu_combo_items',
            Column('menu_combo_item_group_id', String(64), nullable=False),
            Column('menu_combo_item_id', String(64), nullable=False),
            UniqueConstraint('menu_combo_item_group_id', 'menu_combo_item_id'),
            Index(
                'idx_menu_combo_items_to_menu_combo_groups',
                'menu_combo_item_group_id',
            ),
            ForeignKeyConstraint(
                ['menu_combo_item_group_id'],
                ['menu_combo_item_groups.id'],
                name='fk_menu_combo_items_to_menu_combo_groups',
                ondelete='cascade',
                onupdate='cascade',
            ),
            Index(
                'idx_menu_combo_groups_to_menu_combo_items',
                'menu_combo_item_id',
            ),
            ForeignKeyConstraint(
                ['menu_combo_item_id'],
                ['menu_combo_items.id'],
                name='fk_menu_combo_groups_to_menu_combo_items',
                ondelete='cascade',
                onupdate='cascade',
            ),
        )

        with connection.begin():
            connection.execute(
                """
                insert into menu_combo_item_groups_menu_combo_items (menu_combo_item_group_id, menu_combo_item_id)
                     select menu_combo_item_group_id as menu_combo_item_group_id, id as menu_combo_item_id
                     from menu_combo_items
                """
            )

        with op.batch_alter_table('menu_combo_items') as batch_op:
            # Can't explicitly drop fk due to alembic bug, but gets dropped when the column is dropped.
            # batch_op.drop_constraint('fk_menu_combo_items_menu_combo_item_groups', type_='foreignkey')
            batch_op.drop_index('idx_menu_combo_item_menu_combo_item_group_id')
            batch_op.drop_column('menu_combo_item_group_id')

            # Have to explicitly recreate fks. Otherwise they lose their names and cascades.
            batch_op.create_foreign_key(
                'fk_menu_combo_items_menu_items',
                'menu_items',
                ['menu_item_id'],
                ['id'],
                onupdate='cascade',
                ondelete='cascade',
            )

    finally:
        connection.execute('PRAGMA foreign_keys = "1";')


def downgrade():
    """
    Use batch-operations to support sqlite. If you are modifying any tables with foreign keys, be sure to disable
    foreign keys while upgrading.

    > conn = op.get_bind()
    > try:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    >     with op.batch_alter_table('table_name') as batch_op:
    >         pass
    > finally:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    """

    connection = op.get_bind()
    try:
        connection.execute('PRAGMA foreign_keys = "0";')

        with op.batch_alter_table('menu_combo_items') as batch_op:
            batch_op.add_column(Column('menu_combo_item_group_id', String(64), nullable=True))
            batch_op.create_index(
                'idx_menu_combo_item_menu_combo_item_group_id',
                ['menu_combo_item_group_id'],
                unique=False,
            )

        with connection.begin():
            connection.execute(
                """
                update menu_combo_items        
                   set menu_combo_item_group_id = (
                           select a.menu_combo_item_group_id
                             from menu_combo_item_groups_menu_combo_items a
                            where menu_combo_items.id = a.menu_combo_item_id
                       )
                 where exists (
                           select a.menu_combo_item_group_id
                             from menu_combo_item_groups_menu_combo_items a
                            where menu_combo_items.id = a.menu_combo_item_id
                       )                   
                """
            )

        with op.batch_alter_table('menu_combo_items') as batch_op:
            batch_op.alter_column('menu_combo_item_group_id', nullable=False)

            # Have to explicitly recreate fks. Otherwise they lose their names and cascades.
            batch_op.create_foreign_key(
                'fk_menu_combo_items_menu_items',
                'menu_items',
                ['menu_item_id'],
                ['id'],
                onupdate='cascade',
                ondelete='cascade',
            )
            batch_op.create_foreign_key(
                'fk_menu_combo_items_menu_combo_item_groups',
                'menu_combo_item_groups',
                ['menu_combo_item_group_id'],
                ['id'],
                onupdate='cascade',
                ondelete='cascade',
            )

        op.drop_table('menu_combo_item_groups_menu_combo_items')

    finally:
        connection.execute('PRAGMA foreign_keys = "1";')

