"""Add user data to held tickets

Revision ID: 364536eb33d5
Revises: 8fed416518e1
Create Date: 2017-03-03 16:13:14.700386

"""
from alembic import op, context
from sqlalchemy import Column, String, inspect


# revision identifiers, used by Alembic.
revision = '364536eb33d5'
down_revision = '8fed416518e1'
branch_labels = None
depends_on = None

first_agent_version = '17.3.4'


def upgrade():
    """
    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";')

        tables = set()
        current_context = context.get_context()
        if current_context.connection:
            inspector = inspect(current_context.connection)
            tables = set(inspector.get_table_names())
        if 'user_data' not in tables:
            op.create_table(
                'user_data',
                Column('id', String(40), primary_key=True),
                Column('account_number', String(32), nullable=True),
                Column('first_name', String(32), nullable=True),
                Column('last_name', String(32), nullable=True),
                Column('address_street1', String(32), nullable=True),
                Column('address_street2', String(32), nullable=True),
                Column('address_street3', String(32), nullable=True),
                Column('address_zip', String(32), nullable=True),
                Column('address_city', String(32), nullable=True),
                Column('address_state', String(32), nullable=True),
                Column('phone', String(32), nullable=True),
                Column('phone_ext', String(10), nullable=True),
                mysql_charset='utf8',
                mysql_engine='InnoDB',
            )
        with op.batch_alter_table('held_tickets') as batch_op:
            batch_op.add_column(Column('user_info_hash', String(40), nullable=True))
            batch_op.create_foreign_key(
                'fk_user_info',
                'user_data',
                ['user_info_hash'],
                ['id'],
                onupdate='cascade',
                ondelete='cascade',
            )

    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_tickets') as batch_op:
            batch_op.drop_column('user_info_hash')
            batch_op.drop_constraint('fk_user_info')
        op.drop_table('user_data')

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