"""alter_pay_rate_pk

Revision ID: 79711a8b68e7
Revises: d8168117f9ae
Create Date: 2018-03-27 17:55:57.742000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '79711a8b68e7'
down_revision = 'd8168117f9ae'
branch_labels = None
depends_on = None

first_agent_version = '18.4.13'


def upgrade():
    """
    We're altering primary keys here so we have to drop and recreate the table. And the payrate_level table.
    """
    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')

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

    op.create_table(
        'pay_rates',
        sa.Column('key', sa.Integer, autoincrement=True),
        sa.Column('id', sa.String(length=64), nullable=False),
        sa.Column('employee_id', sa.String(length=64), nullable=True),
        sa.Column('job_id', sa.String(length=64), nullable=True),
        sa.Column('rate', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['employee_id'],
            ['employees.id'],
            name='fk_pay_rates_employees',
            onupdate='cascade',
            ondelete='cascade',
        ),
        sa.PrimaryKeyConstraint('key'),
    )
    with op.batch_alter_table('pay_rates', schema=None) as batch_op:
        batch_op.create_index('idx_pay_rates_job_id', ['job_id'], unique=False)
        batch_op.create_index('idx_pay_rates_employee_id_id', ['employee_id', 'id'], unique=True)

    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_key', sa.Integer(), 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_key'],
            ['pay_rates.key'],
            name='fk_pay_rate_levels_pay_rate_key_pay_rates',
            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_key_id', ['pay_rate_key', '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')

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

    op.create_table(
        'pay_rates',
        sa.Column('id', sa.String(length=64), nullable=False),
        sa.Column('employee_id', sa.String(length=64), nullable=True),
        sa.Column('job_id', sa.String(length=64), nullable=True),
        sa.Column('rate', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(
            ['employee_id'],
            ['employees.id'],
            name='fk_pay_rates_employees',
            onupdate='cascade',
            ondelete='cascade',
        ),
        sa.PrimaryKeyConstraint('id'),
    )
    with op.batch_alter_table('pay_rates', schema=None) as batch_op:
        batch_op.create_index('idx_pay_rates_job_id', ['job_id'], unique=False)

    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)
