"""combos
Revision ID: 2505d6f40bee
Revises: 281cea12023f
Create Date: 2019-06-13 17:59:36.620548
"""
import logging

from alembic import op, context
import sqlalchemy as sa


logger = logging.getLogger(__name__)


# revision identifiers, used by Alembic.
revision = '2505d6f40bee'
down_revision = '281cea12023f'
branch_labels = None
depends_on = None

first_agent_version = '19.6.18'


def upgrade():
    connection = op.get_bind()
    inspector = sa.inspect(connection)
    tables = set(inspector.get_table_names())

    op.execute("pragma foreign_keys = 0")

    try:
        if 'menu_combos' in tables:
            logger.info("Table 'menu_combos' already exists. Skipping its migration...")
        else:
            op.create_table(
                'menu_combos',
                sa.Column('id', sa.String(length=64), primary_key=True),
                sa.Column('pos_id', sa.String(length=64)),
                sa.Column('name', sa.String(length=128)),
                sa.Column('price_per_unit', sa.Integer()),
                sa.Column('_stub', sa.Boolean()),
                sa.Column('_registered_property_values', sa.BLOB()),
            )

        if 'menu_combo_item_groups' in tables:
            logger.info("Table 'menu_combo_item_groups' already exists. Skipping its migration...")
        else:
            op.create_table(
                'menu_combo_item_groups',
                sa.Column('id', sa.String(length=64), primary_key=True),
                sa.Column('menu_combo_id', sa.String(64)),
                sa.Column('pos_id', sa.String(length=64)),
                sa.Column('name', sa.String(length=128)),
                sa.Column('_stub', sa.Boolean()),
                sa.Column('_registered_property_values', sa.BLOB()),
                sa.ForeignKeyConstraint(
                    ['menu_combo_id'],
                    ['menu_combos.id'],
                    name='fk_menu_combo_item_groups_menu_combos',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
            )

            with op.batch_alter_table('menu_combo_item_groups', schema=None) as batch_op:
                batch_op.create_index('idx_menu_combo_item_group_menu_combo_id', ['menu_combo_id'], unique=False)

        if 'menu_combo_items' in tables:
            logger.info("Table 'menu_combo_items' already exists. Skipping its migration...")
        else:
            op.create_table(
                'menu_combo_items',
                sa.Column('id', sa.String(length=64), primary_key=True),
                sa.Column('menu_combo_item_group_id', sa.String(64)),
                sa.Column('menu_item_id', sa.String(64)),
                sa.Column('pos_id', sa.String(length=64)),
                sa.Column('default', sa.Boolean()),
                sa.Column('price_per_unit', sa.Integer()),
                sa.Column('name', sa.String(length=128)),
                sa.Column('_stub', sa.Boolean()),
                sa.Column('_registered_property_values', sa.BLOB()),
                sa.ForeignKeyConstraint(
                    ['menu_combo_item_group_id'],
                    ['menu_combo_item_groups.id'],
                    name='fk_menu_combo_items_menu_combo_item_groups',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
                sa.ForeignKeyConstraint(
                    ['menu_item_id'],
                    ['menu_items.id'],
                    name='fk_menu_combo_items_menu_items',
                    onupdate='cascade',
                    ondelete='cascade',
                ),
            )

            with op.batch_alter_table('menu_combo_items', schema=None) as batch_op:
                batch_op.create_index('idx_menu_combo_item_menu_combo_item_group_id', ['menu_combo_item_group_id'], unique=False)
                batch_op.create_index('idx_menu_combo_item_menu_item_id', ['menu_item_id'], unique=False)

        if 'ticket_combos' in tables:
            logger.info("Table 'ticket_combos' already exists. Skipping its migration...")
        else:
            op.create_table(
                'ticket_combos',
                sa.Column('key', sa.BigInteger(length=20), primary_key=True, autoincrement=True),
                sa.Column('ticket_id', sa.String(length=64)),
                sa.Column('id', sa.String(length=64)),
                sa.Column('name', sa.String(length=128)),
                sa.Column('quantity', sa.Integer(), nullable=False),
                sa.Column('split', sa.Integer()),
                sa.Column('price', sa.Integer(), nullable=False),
                sa.Column('comment', sa.Text()),
                sa.Column('sent', sa.Boolean()),
                sa.Column('sent_at', sa.DateTime()),
                sa.Column('void', sa.Boolean(), nullable=False),
                sa.Column('void_type_id', sa.String(length=64), nullable=True),
                sa.Column('approving_employee_id', sa.String(length=64), nullable=True),

                # Not a foreign key, b/c this is historical ticket data
                sa.Column('menu_combo_id', sa.String(length=64)),

                sa.ForeignKeyConstraint(
                    ['ticket_id'],
                    ['tickets.id'],
                    name='fk_ticket_combos_tickets',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
            )

            with op.batch_alter_table('ticket_combos', schema=None) as batch_op:
                batch_op.create_index('idx_ticket_combos_ticket_id_id', ['ticket_id', 'id'], unique=True)

        if 'ticket_combo_items' in tables:
            logger.info("Table 'ticket_combo_items' already exists. Skipping its migration...")
        else:
            op.create_table(
                'ticket_combo_items',
                sa.Column('key', sa.BigInteger(length=20), primary_key=True, autoincrement=True),
                sa.Column('ticket_id', sa.String(length=64)),
                sa.Column('ticket_combo_id', sa.String(length=64)),
                sa.Column('id', sa.String(length=64)),
                sa.Column('name', sa.String(length=128)),
                sa.Column('quantity', sa.Integer(), nullable=False),
                sa.Column('split', sa.Integer()),
                sa.Column('price', sa.Integer(), nullable=False),
                sa.Column('comment', sa.Text()),
                sa.Column('sent', sa.Boolean()),
                sa.Column('sent_at', sa.DateTime()),


                # Not a foreign key, b/c this is historical ticket data
                sa.Column('menu_combo_item_id', sa.String(length=64)),
                sa.Column('menu_combo_item_group_id', sa.String(length=64)),
                sa.Column('menu_item_id', sa.String(length=64)),

                sa.ForeignKeyConstraint(
                    ['ticket_id',],
                    ['tickets.id'],
                    name='fk_ticket_combo_items_tickets',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
                sa.ForeignKeyConstraint(
                    ['ticket_id', 'ticket_combo_id'],
                    ['ticket_combos.ticket_id', 'ticket_combos.id'],
                    name='fk_ticket_combo_items_ticket_combos',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
            )

            with op.batch_alter_table('ticket_combo_items', schema=None) as batch_op:
                batch_op.create_index('idx_ticket_combo_items_ticket_combo_id_id', ['ticket_combo_id', 'id'])
                batch_op.create_index(
                    'idx_ticket_combo_items_ticket_id_ticket_combo_id_id',
                    ['ticket_id', 'ticket_combo_id', 'id'],
                    unique=True,
                )

        if 'held_ticket_combos' in tables:
            logger.info("Table 'held_ticket_combos' already exists. Skipping its migration...")
        else:
            op.create_table(
                'held_ticket_combos',
                sa.Column('key', sa.BigInteger(length=20), primary_key=True, autoincrement=True),
                sa.Column('ticket_id', sa.String(length=64)),
                sa.Column('id', sa.String(length=64)),
                sa.Column('name', sa.String(length=128)),
                sa.Column('quantity', sa.Integer(), nullable=False),
                sa.Column('split', sa.Integer()),
                sa.Column('price', sa.Integer(), nullable=False),
                sa.Column('comment', sa.Text()),
                sa.Column('sent', sa.Boolean()),
                sa.Column('sent_at', sa.DateTime()),
                sa.Column('void', sa.Boolean(), nullable=False),
                sa.Column('void_type_id', sa.String(length=64), nullable=True),
                sa.Column('approving_employee_id', sa.String(length=64), nullable=True),
                sa.Column('price_per_unit', sa.Integer()),
                sa.Column('price_level_id', sa.String(length=32)),

                # Not a foreign key, b/c this is historical ticket data
                sa.Column('menu_combo_id', sa.String(length=64)),

                sa.ForeignKeyConstraint(
                    ['ticket_id'],
                    ['held_tickets.id'],
                    name='fk_held_ticket_combos_tickets',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
            )

            with op.batch_alter_table('held_ticket_combos', schema=None) as batch_op:
                batch_op.create_index('idx_held_ticket_combo_id_ticket_id', ['ticket_id', 'id'], unique=True)

        if 'held_ticket_combo_items' in tables:
            logger.info("Table 'held_ticket_combo_items' already exists. Skipping its migration...")
        else:
            op.create_table(
                'held_ticket_combo_items',
                sa.Column('key', sa.BigInteger(length=20), primary_key=True, autoincrement=True),
                sa.Column('ticket_id', sa.String(length=64)),
                sa.Column('ticket_combo_id', sa.String(length=64)),
                sa.Column('id', sa.String(length=64)),
                sa.Column('name', sa.String(length=128)),
                sa.Column('quantity', sa.Integer(), nullable=False),
                sa.Column('split', sa.Integer()),
                sa.Column('price', sa.Integer(), nullable=False),
                sa.Column('comment', sa.Text()),
                sa.Column('sent', sa.Boolean()),
                sa.Column('sent_at', sa.DateTime()),

                sa.Column('menu_combo_item_group_id', sa.String(length=64)),
                sa.Column('menu_combo_item_id', sa.String(length=64)),
                sa.Column('menu_item_id', sa.String(length=64)),

                sa.ForeignKeyConstraint(
                    ['ticket_id'],
                    ['held_tickets.id'],
                    name='fk_held_ticket_combo_items_held_tickets',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
                sa.ForeignKeyConstraint(
                    ['ticket_id', 'ticket_combo_id'],
                    ['held_ticket_combos.ticket_id', 'held_ticket_combos.id'],
                    name='fk_held_ticket_combo_items_held_ticket_combos',
                    onupdate='cascade',
                    ondelete='cascade'
                ),
            )

            with op.batch_alter_table('held_ticket_combo_items', schema=None) as batch_op:
                batch_op.create_index('idx_held_ticket_combo_items_ticket_id_id', ['ticket_id', 'id'], unique=True)

        with op.batch_alter_table('held_ticket_item_modifier_closures', schema=None) as batch_op:
            batch_op.add_column(sa.Column('target_combo_item_key', sa.BigInteger(length=20), nullable=True))
            batch_op.create_index('idx_held_target_combo_item_key', ['target_combo_item_key'], unique=False)
            batch_op.create_foreign_key(
                    'fk_held_ticket_item_modifier_closures_combo_item_targets',
                    'held_ticket_combo_items',
                    ['target_combo_item_key'],
                    ['key'],
                    onupdate='cascade',
                    ondelete='cascade',
                )

        # Cannot add a not-null column to a DB that already has data.
        # Add the column, migrate null data, then make it nullable
        with op.batch_alter_table('held_ticket_totals', schema=None) as batch_op:
            batch_op.add_column(sa.Column('combos', sa.Integer(), nullable=True, info={'after': 'due'}))

        connection.execute('UPDATE held_ticket_totals SET combos = 0 WHERE combos IS NULL')

        with op.batch_alter_table('held_ticket_totals', schema=None) as batch_op:
            batch_op.alter_column('combos', nullable=False)

        with op.batch_alter_table('ticket_item_modifier_closures', schema=None) as batch_op:
            batch_op.add_column(sa.Column('target_combo_item_key', sa.BigInteger(length=20), nullable=True))
            batch_op.create_index('idx_target_combo_item_key', ['target_combo_item_key'], unique=False)
            batch_op.create_foreign_key(
                'fk_ticket_item_modifier_closures_combo_item_targets',
                'ticket_combo_items',
                ['target_combo_item_key'],
                ['key'],
                onupdate='cascade',
                ondelete='cascade',
            )

        # Cannot add a not-null column to a DB that already has data.
        # Add the column, migrate null data, then make it nullable
        with op.batch_alter_table('ticket_totals', schema=None) as batch_op:
            batch_op.add_column(sa.Column('combos', sa.Integer(), nullable=True, info={'after': 'due'}))

        connection.execute('UPDATE ticket_totals SET combos = 0 WHERE combos IS NULL')

        with op.batch_alter_table('ticket_totals', schema=None) as batch_op:
            batch_op.alter_column('combos', nullable=False)

    finally:
        op.execute("pragma foreign_keys = 1")


def downgrade():
    current_context = context.get_context()
    inspector = sa.inspect(current_context.connection)
    tables = set(inspector.get_table_names())
    has_idx_held_target_combo_item_key = len([ind for ind in inspector.get_indexes('held_ticket_item_modifier_closures') if ind['name'] == 'idx_held_target_combo_item_key']) > 0
    op.execute("pragma foreign_keys = 0")

    try:
        with op.batch_alter_table('held_ticket_totals') as batch_op:
            batch_op.drop_column('combos')

        with op.batch_alter_table('ticket_totals') as batch_op:
            batch_op.drop_column('combos')

        with op.batch_alter_table('held_ticket_item_modifier_closures') as batch_op:
            batch_op.drop_column('target_combo_item_key')
            if has_idx_held_target_combo_item_key:
                # This was missed in the initial version, and was added in after release, it may not exist
                batch_op.drop_index('idx_held_target_combo_item_key')
            # Can't drop FK's like this.
            # batch_op.drop_constraint('fk_held_ticket_item_modifier_closures_combo_item_targets')

        with op.batch_alter_table('ticket_item_modifier_closures') as batch_op:
            batch_op.drop_column('target_combo_item_key')
            batch_op.drop_index('idx_target_combo_item_key')
            # batch_op.drop_constraint('fk_ticket_item_modifier_closures_combo_item_targets')

        if 'held_ticket_combo_items' in tables:
            op.drop_table('held_ticket_combo_items')

        if 'held_ticket_combos' in tables:
            op.drop_table('held_ticket_combos')

        if 'ticket_combo_items' in tables:
            op.drop_table('ticket_combo_items')

        if 'ticket_combos' in tables:
            op.drop_table('ticket_combos')

        if 'menu_combo_items' in tables:
            op.drop_table('menu_combo_items')

        if 'menu_combo_item_groups' in tables:
            op.drop_table('menu_combo_item_groups')

        if 'menu_combos' in tables:
            op.drop_table('menu_combos')

    finally:
        op.execute("pragma foreign_keys = 1")
