"""Pay rates and clockentry segments

Revision ID: f81ad9d98879
Revises: de9051142923
Create Date: 2017-08-04 17:40:12.613063

"""


import logging

from alembic import op
import sqlalchemy as sa

logger = logging.getLogger(__name__)

# revision identifiers, used by Alembic.
revision = 'f81ad9d98879'
down_revision = 'de9051142923'
branch_labels = None
depends_on = None

first_agent_version = '17.8.8'


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 'clock_entry_segments' in tables:
        logger.info("Table 'clock_entry_segments' already exists. Skipping its migration...")
    else:
        op.create_table(
            'clock_entry_segments',
            sa.Column('clock_entry_id', sa.String(length=64), nullable=False),
            sa.Column('id', sa.String(length=64), nullable=False),
            sa.Column('pay', sa.Integer(), nullable=False),
            sa.Column('minutes', sa.Integer(), nullable=True),
            sa.ForeignKeyConstraint(
                ['clock_entry_id'],
                ['clock_entries.id'],
                name='fk_clock_entry_segments_clock_entries',
                onupdate='cascade',
                ondelete='cascade'
            ),
            sa.PrimaryKeyConstraint('clock_entry_id', 'id')
        )

    if 'pay_rates' in tables:
        logger.info("Table 'pay_rates' already exists. Skipping its migration...")
    else:
        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)

    if 'pay_rate_levels' in tables:
        logger.info("Table 'pay_rate_levels' already exists. Skipping its migration...")
    else:
        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')
        )

    with op.batch_alter_table('clock_entries', schema=None) as batch_op:
        batch_op.add_column(sa.Column('pay_rate_id', sa.String(length=64), nullable=True))
        batch_op.create_index('idx_clock_entries_pay_rate_id', ['pay_rate_id'], unique=False)


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

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """
    with op.batch_alter_table('clock_entries', schema=None) as batch_op:
        batch_op.drop_index('idx_clock_entries_pay_rate_id')
        batch_op.drop_column('pay_rate_id')

    op.drop_table('pay_rate_levels')

    with op.batch_alter_table('pay_rates', schema=None) as batch_op:
        batch_op.drop_index('idx_pay_rates_job_id')

    op.drop_table('pay_rates')
    op.drop_table('clock_entry_segments')
