"""Add price_per_unit and price_level_id columns

Revision ID: de9051142923
Revises: d660db0ebc1b
Create Date: 2017-07-21 13:54:06.883000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import Column, String, Integer, inspect

revision = 'de9051142923'
down_revision = 'd660db0ebc1b'
branch_labels = None
depends_on = None

first_agent_version = '17.7.25'


def upgrade():
    """
    Use batch-operations to support sqlite

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """
    connection = op.get_bind()
    inspector = inspect(connection)

    def _add_columns_if_needed(table_name):
        cols = [col['name'] for col in inspector.get_columns(table_name)]
        needs_ppu = 'price_per_unit' not in cols
        needs_pl_id = 'price_level_id' not in cols
        if needs_ppu or needs_pl_id:
            with op.batch_alter_table(table_name) as batch_op:
                if needs_ppu:
                    batch_op.add_column(Column('price_per_unit', Integer))
                if needs_pl_id:
                    batch_op.add_column(Column('price_level_id', String(32)))

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

        _add_columns_if_needed('held_ticket_items')
        _add_columns_if_needed('held_ticket_item_modifiers')

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


def downgrade():
    """
    Use batch-operations to support sqlite

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

        with op.batch_alter_table('held_ticket_items') as batch_op:
            batch_op.drop_column('price_per_unit')
            batch_op.drop_column('price_level_id')

        with op.batch_alter_table('held_ticket_item_modifiers') as batch_op:
            batch_op.drop_column('price_per_unit')
            batch_op.drop_column('price_level_id')

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