"""Add start_date to employee and pay_rate

Revision ID: e27e807c1ad0
Revises: 318e913edf0a
Create Date: 2018-09-26 11:01:51.046000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import Column, Date


# revision identifiers, used by Alembic.
revision = 'e27e807c1ad0'
down_revision = '318e913edf0a'
branch_labels = None
depends_on = None

first_agent_version = '18.11.14'


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

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

    with op.batch_alter_table('employees') as batch_op:
        batch_op.add_column(Column('start_date', Date, nullable=True))

    with op.batch_alter_table('pay_rates') as batch_op:
        batch_op.add_column(Column('start_date', Date, nullable=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')

    with op.batch_alter_table('employees') as employee_batch_op:
        employee_batch_op.drop_column('start_date')

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