"""Add employee_id to FrozenSalaryComponent

Revision ID: 468c5ecb9196
Revises: 961c8806aab3
Create Date: 2025-07-26 19:23:31.481756

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = '468c5ecb9196'
down_revision = '961c8806aab3'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('frozen_salary_component',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('employee_id', sa.Integer(), nullable=False),
    sa.Column('month', sa.String(length=7), nullable=False),
    sa.Column('component_type', sa.String(length=20), nullable=False),
    sa.Column('head_name', sa.String(length=100), nullable=False),
    sa.Column('amount', sa.Float(), nullable=False),
    sa.ForeignKeyConstraint(['employee_id'], ['employee.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('payroll_record',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('employee_id', sa.Integer(), nullable=False),
    sa.Column('year', sa.Integer(), nullable=False),
    sa.Column('month', sa.Integer(), nullable=False),
    sa.Column('gross', sa.Float(), nullable=False),
    sa.Column('deductions', sa.Float(), nullable=False),
    sa.Column('net_pay', sa.Float(), nullable=False),
    sa.Column('ctc', sa.Float(), nullable=True),
    sa.Column('generated_on', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['employee_id'], ['employee.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('employee_id', 'year', 'month', name='unique_employee_month')
    )
    with op.batch_alter_table('employee', schema=None) as batch_op:
        batch_op.create_foreign_key(None, 'employee', ['created_by'], ['id'])
        batch_op.create_foreign_key(None, 'employee', ['updated_by'], ['id'])
        batch_op.drop_column('rh_allowed')
        batch_op.drop_column('base_salary')

    with op.batch_alter_table('employee_leave', schema=None) as batch_op:
        batch_op.alter_column('status',
               existing_type=mysql.VARCHAR(length=50),
               nullable=True)

    with op.batch_alter_table('leave_balance', schema=None) as batch_op:
        batch_op.alter_column('balance',
               existing_type=mysql.DECIMAL(precision=5, scale=2),
               type_=sa.Float(),
               existing_nullable=True,
               existing_server_default=sa.text("'0.00'"))

    with op.batch_alter_table('leave_request', schema=None) as batch_op:
        batch_op.create_foreign_key(None, 'employee', ['approved_by_hr_id'], ['id'])
        batch_op.drop_column('days')

    with op.batch_alter_table('leave_type', schema=None) as batch_op:
        batch_op.alter_column('annual_quota',
               existing_type=mysql.DECIMAL(precision=5, scale=2),
               type_=sa.Float(),
               existing_nullable=True,
               existing_server_default=sa.text("'0.00'"))

    with op.batch_alter_table('master_change_log', schema=None) as batch_op:
        batch_op.alter_column('table_name',
               existing_type=mysql.VARCHAR(length=255),
               type_=sa.String(length=50),
               existing_nullable=True)
        batch_op.alter_column('field_name',
               existing_type=mysql.VARCHAR(length=255),
               type_=sa.String(length=100),
               existing_nullable=True)
        batch_op.alter_column('old_value',
               existing_type=mysql.TEXT(),
               type_=sa.String(length=255),
               existing_nullable=True)
        batch_op.alter_column('new_value',
               existing_type=mysql.TEXT(),
               type_=sa.String(length=255),
               existing_nullable=True)

    with op.batch_alter_table('pending_approval_log', schema=None) as batch_op:
        batch_op.create_foreign_key(None, 'employee', ['approved_by'], ['id'])

    with op.batch_alter_table('pending_employee', schema=None) as batch_op:
        batch_op.alter_column('full_name',
               existing_type=mysql.VARCHAR(length=100),
               type_=sa.String(length=255),
               nullable=True)
        batch_op.alter_column('email',
               existing_type=mysql.VARCHAR(length=100),
               type_=sa.String(length=255),
               existing_nullable=True)
        batch_op.alter_column('phone',
               existing_type=mysql.VARCHAR(length=20),
               type_=sa.String(length=50),
               existing_nullable=True)
        batch_op.alter_column('qualification',
               existing_type=mysql.VARCHAR(length=100),
               type_=sa.String(length=255),
               existing_nullable=True)
        batch_op.alter_column('institute',
               existing_type=mysql.VARCHAR(length=100),
               type_=sa.String(length=255),
               existing_nullable=True)
        batch_op.drop_constraint(batch_op.f('pending_employee_ibfk_3'), type_='foreignkey')
        batch_op.drop_constraint(batch_op.f('pending_employee_ibfk_2'), type_='foreignkey')
        batch_op.drop_constraint(batch_op.f('pending_employee_ibfk_1'), type_='foreignkey')
        batch_op.drop_column('employee_code')
        batch_op.drop_column('reporting_manager_id')

    with op.batch_alter_table('public_holiday', schema=None) as batch_op:
        batch_op.alter_column('name',
               existing_type=mysql.VARCHAR(length=255),
               type_=sa.String(length=150),
               existing_nullable=False)
        batch_op.alter_column('location',
               existing_type=mysql.VARCHAR(length=255),
               type_=sa.String(length=100),
               existing_nullable=True)
        batch_op.drop_index(batch_op.f('date'))
        batch_op.drop_column('updated_on')
        batch_op.drop_column('created_on')
        batch_op.drop_column('description')

    with op.batch_alter_table('public_link', schema=None) as batch_op:
        batch_op.alter_column('token',
               existing_type=mysql.VARCHAR(length=100),
               type_=sa.String(length=255),
               nullable=True)

    with op.batch_alter_table('weekend_settings', schema=None) as batch_op:
        batch_op.alter_column('sunday',
               existing_type=mysql.ENUM('off', 'half', 'working'),
               type_=sa.String(length=20),
               existing_nullable=True,
               existing_server_default=sa.text("'off'"))
        batch_op.drop_column('saturday')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('weekend_settings', schema=None) as batch_op:
        batch_op.add_column(sa.Column('saturday', mysql.ENUM('off', 'half', 'working'), server_default=sa.text("'off'"), nullable=True))
        batch_op.alter_column('sunday',
               existing_type=sa.String(length=20),
               type_=mysql.ENUM('off', 'half', 'working'),
               existing_nullable=True,
               existing_server_default=sa.text("'off'"))

    with op.batch_alter_table('public_link', schema=None) as batch_op:
        batch_op.alter_column('token',
               existing_type=sa.String(length=255),
               type_=mysql.VARCHAR(length=100),
               nullable=False)

    with op.batch_alter_table('public_holiday', schema=None) as batch_op:
        batch_op.add_column(sa.Column('description', mysql.TEXT(), nullable=True))
        batch_op.add_column(sa.Column('created_on', mysql.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True))
        batch_op.add_column(sa.Column('updated_on', mysql.TIMESTAMP(), server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), nullable=True))
        batch_op.create_index(batch_op.f('date'), ['date'], unique=True)
        batch_op.alter_column('location',
               existing_type=sa.String(length=100),
               type_=mysql.VARCHAR(length=255),
               existing_nullable=True)
        batch_op.alter_column('name',
               existing_type=sa.String(length=150),
               type_=mysql.VARCHAR(length=255),
               existing_nullable=False)

    with op.batch_alter_table('pending_employee', schema=None) as batch_op:
        batch_op.add_column(sa.Column('reporting_manager_id', mysql.INTEGER(), autoincrement=False, nullable=True))
        batch_op.add_column(sa.Column('employee_code', mysql.VARCHAR(length=50), nullable=True))
        batch_op.create_foreign_key(batch_op.f('pending_employee_ibfk_1'), 'department', ['department_id'], ['id'])
        batch_op.create_foreign_key(batch_op.f('pending_employee_ibfk_2'), 'designation', ['designation_id'], ['id'])
        batch_op.create_foreign_key(batch_op.f('pending_employee_ibfk_3'), 'employee', ['reporting_manager_id'], ['id'])
        batch_op.alter_column('institute',
               existing_type=sa.String(length=255),
               type_=mysql.VARCHAR(length=100),
               existing_nullable=True)
        batch_op.alter_column('qualification',
               existing_type=sa.String(length=255),
               type_=mysql.VARCHAR(length=100),
               existing_nullable=True)
        batch_op.alter_column('phone',
               existing_type=sa.String(length=50),
               type_=mysql.VARCHAR(length=20),
               existing_nullable=True)
        batch_op.alter_column('email',
               existing_type=sa.String(length=255),
               type_=mysql.VARCHAR(length=100),
               existing_nullable=True)
        batch_op.alter_column('full_name',
               existing_type=sa.String(length=255),
               type_=mysql.VARCHAR(length=100),
               nullable=False)

    with op.batch_alter_table('pending_approval_log', schema=None) as batch_op:
        batch_op.drop_constraint(None, type_='foreignkey')

    with op.batch_alter_table('master_change_log', schema=None) as batch_op:
        batch_op.alter_column('new_value',
               existing_type=sa.String(length=255),
               type_=mysql.TEXT(),
               existing_nullable=True)
        batch_op.alter_column('old_value',
               existing_type=sa.String(length=255),
               type_=mysql.TEXT(),
               existing_nullable=True)
        batch_op.alter_column('field_name',
               existing_type=sa.String(length=100),
               type_=mysql.VARCHAR(length=255),
               existing_nullable=True)
        batch_op.alter_column('table_name',
               existing_type=sa.String(length=50),
               type_=mysql.VARCHAR(length=255),
               existing_nullable=True)

    with op.batch_alter_table('leave_type', schema=None) as batch_op:
        batch_op.alter_column('annual_quota',
               existing_type=sa.Float(),
               type_=mysql.DECIMAL(precision=5, scale=2),
               existing_nullable=True,
               existing_server_default=sa.text("'0.00'"))

    with op.batch_alter_table('leave_request', schema=None) as batch_op:
        batch_op.add_column(sa.Column('days', mysql.INTEGER(), server_default=sa.text("'0'"), autoincrement=False, nullable=True))
        batch_op.drop_constraint(None, type_='foreignkey')

    with op.batch_alter_table('leave_balance', schema=None) as batch_op:
        batch_op.alter_column('balance',
               existing_type=sa.Float(),
               type_=mysql.DECIMAL(precision=5, scale=2),
               existing_nullable=True,
               existing_server_default=sa.text("'0.00'"))

    with op.batch_alter_table('employee_leave', schema=None) as batch_op:
        batch_op.alter_column('status',
               existing_type=mysql.VARCHAR(length=50),
               nullable=False)

    with op.batch_alter_table('employee', schema=None) as batch_op:
        batch_op.add_column(sa.Column('base_salary', mysql.DECIMAL(precision=12, scale=2), server_default=sa.text("'0.00'"), nullable=True))
        batch_op.add_column(sa.Column('rh_allowed', mysql.INTEGER(), server_default=sa.text("'2'"), autoincrement=False, nullable=True))
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_constraint(None, type_='foreignkey')

    op.drop_table('payroll_record')
    op.drop_table('frozen_salary_component')
    # ### end Alembic commands ###