"""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
    """
    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',
        )


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

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """

    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')
