"""alter_pay_rate_level_pk

Revision ID: d8168117f9ae
Revises: 735393a229d9
Create Date: 2018-03-19 14:32:12.118000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'd8168117f9ae'
down_revision = '735393a229d9'
branch_labels = None
depends_on = None

first_agent_version = '18.3.19'


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

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """
    connection = op.get_bind()
    inspector = sa.inspect(connection)
    tables = set(inspector.get_table_names())
    if 'pay_rate_levels' in tables:
        op.drop_table('pay_rate_levels')

    op.create_table(
        'pay_rate_levels',
        sa.Column('key', sa.Integer(), nullable=False, autoincrement=True),
        sa.Column('id', sa.String(length=64), nullable=False),
        sa.Column('pay_rate_id', sa.String(length=64), nullable=False),
        sa.Column('name', sa.String(length=64), nullable=True),
        sa.Column('rate', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['pay_rate_id'],
            ['pay_rates.id'],
            name='fk_pay_rate_levels_pay_rate',
            onupdate='cascade',
            ondelete='cascade'
        ),
        sa.PrimaryKeyConstraint('key'),
    )

    with op.batch_alter_table('pay_rate_levels', schema=None) as batch_op:
        batch_op.create_index('idx_pay_rate_levels_pay_rate_id_id', ['pay_rate_id', 'id'], unique=True)


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

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """
    connection = op.get_bind()
    inspector = sa.inspect(connection)
    tables = set(inspector.get_table_names())
    if 'pay_rate_levels' in tables:
        op.drop_table('pay_rate_levels')

    op.create_table(
        'pay_rate_levels',
        sa.Column('id', sa.String(length=64), nullable=False),
        sa.Column('pay_rate_id', sa.String(length=64), nullable=True),
        sa.Column('name', sa.String(length=64), nullable=True),
        sa.Column('rate', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['pay_rate_id'],
            ['pay_rates.id'],
            name='fk_pay_rate_levels_pay_rate',
            onupdate='cascade',
            ondelete='cascade'
        ),
        sa.PrimaryKeyConstraint('id'),
    )
