"""add_yard_slot_master_and_maintenance_tables

Revision ID: b8e9f0a1c2d3
Revises: a7f8e9d0c1b2
Create Date: 2026-07-29 16:30:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = 'b8e9f0a1c2d3'
down_revision: Union[str, None] = 'a7f8e9d0c1b2'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # 1. yard_slot_master
    op.create_table(
        'yard_slot_master',
        sa.Column('slot_id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('slot_code', sa.String(length=20), unique=True, nullable=False),
        sa.Column('zone', sa.String(length=5), nullable=True),
        sa.Column('slot_type', sa.String(length=20), server_default='STANDARD', nullable=True),
        sa.Column('is_occupied', sa.Boolean(), server_default='false', nullable=False),
        sa.Column('current_chassis', sa.String(length=30), nullable=True),
        sa.Column('notes', sa.Text(), nullable=True),
        sa.Column('is_active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    )

    # 2. maintenance_monthly_stock
    op.create_table(
        'maintenance_monthly_stock',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('stock_month', sa.Integer(), nullable=False),
        sa.Column('stock_year', sa.Integer(), nullable=False),
        sa.Column('category', sa.String(length=100), nullable=False),
        sa.Column('category_order', sa.Integer(), server_default='0', nullable=False),
        sa.Column('material_name', sa.String(length=300), nullable=False),
        sa.Column('uom', sa.String(length=50), nullable=False),
        sa.Column('cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('opening_qty', sa.Numeric(precision=12, scale=3), server_default='0', nullable=False),
        sa.Column('opening_amount', sa.Numeric(precision=14, scale=2), server_default='0', nullable=False),
        sa.Column('incoming_qty', sa.Numeric(precision=12, scale=3), server_default='0', nullable=False),
        sa.Column('incoming_amount', sa.Numeric(precision=14, scale=2), server_default='0', nullable=False),
        sa.Column('total_qty', sa.Numeric(precision=12, scale=3), server_default='0', nullable=False),
        sa.Column('total_amount', sa.Numeric(precision=14, scale=2), server_default='0', nullable=False),
        sa.Column('consumption_qty', sa.Numeric(precision=12, scale=3), server_default='0', nullable=False),
        sa.Column('consumption_amount', sa.Numeric(precision=14, scale=2), server_default='0', nullable=False),
        sa.Column('closing_qty', sa.Numeric(precision=12, scale=3), server_default='0', nullable=False),
        sa.Column('closing_amount', sa.Numeric(precision=14, scale=2), server_default='0', nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index('ix_maintenance_monthly_stock_stock_month', 'maintenance_monthly_stock', ['stock_month'])
    op.create_index('ix_maintenance_monthly_stock_stock_year', 'maintenance_monthly_stock', ['stock_year'])

    # 3. maintenance_equipment_purchase
    op.create_table(
        'maintenance_equipment_purchase',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('purchase_month', sa.Integer(), nullable=False),
        sa.Column('purchase_year', sa.Integer(), nullable=False),
        sa.Column('equipment_name', sa.String(length=300), nullable=False),
        sa.Column('quantity', sa.Numeric(precision=10, scale=2), nullable=False),
        sa.Column('unit_price', sa.Numeric(precision=12, scale=2), nullable=False),
        sa.Column('total_amount', sa.Numeric(precision=14, scale=2), nullable=False),
        sa.Column('remarks', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    )

    # 4. maintenance_equipment_rental
    op.create_table(
        'maintenance_equipment_rental',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('rental_date', sa.Date(), nullable=False),
        sa.Column('vendor', sa.String(length=200), nullable=False),
        sa.Column('equipment_type', sa.String(length=100), nullable=False),
        sa.Column('in_time', sa.String(length=20), nullable=True),
        sa.Column('out_time', sa.String(length=20), nullable=True),
        sa.Column('hours', sa.Numeric(precision=6, scale=2), nullable=True),
        sa.Column('work_description', sa.Text(), nullable=True),
        sa.Column('bill_no', sa.String(length=100), nullable=True),
        sa.Column('amount', sa.Numeric(precision=12, scale=2), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index('ix_maintenance_equipment_rental_rental_date', 'maintenance_equipment_rental', ['rental_date'])

    # 5. maintenance_machine_consumption_cost
    op.create_table(
        'maintenance_machine_consumption_cost',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column('machine_name', sa.String(length=300), nullable=False),
        sa.Column('display_order', sa.Integer(), server_default='0', nullable=False),
        sa.Column('year', sa.Integer(), nullable=False),
        sa.Column('jan_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('feb_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('mar_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('apr_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('may_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('jun_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('jul_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('aug_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('sep_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('oct_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('nov_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('dec_cost', sa.Numeric(precision=12, scale=2), server_default='0', nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index('ix_maintenance_machine_consumption_cost_year', 'maintenance_machine_consumption_cost', ['year'])


def downgrade() -> None:
    op.drop_table('maintenance_machine_consumption_cost')
    op.drop_table('maintenance_equipment_rental')
    op.drop_table('maintenance_equipment_purchase')
    op.drop_table('maintenance_monthly_stock')
    op.drop_table('yard_slot_master')