"""add_dispatch_module_tables

Revision ID: d1a2b3c4e5f6
Revises: 5e34218e79a5
Create Date: 2026-09-10

Creates:
  - dispatch_thermal_checksheet
  - dispatch_thermal_checksheet_item
  - dispatch_gate_pass
  - dispatch_gate_pass_item
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID

# revision identifiers
revision = 'd1a2b3c4e5f6'
down_revision = '5e34218e79a5'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── dispatch_thermal_checksheet ────────────────────────────────────────────
    op.create_table(
        'dispatch_thermal_checksheet',
        sa.Column('checksheet_id', UUID(as_uuid=True), primary_key=True),
        sa.Column('chassis_id',    UUID(as_uuid=True),
                  sa.ForeignKey('chassis_master.chassis_id'), nullable=False),
        sa.Column('chassis_no',    sa.String(100), nullable=False),
        sa.Column('model',         sa.String(100), nullable=True),
        sa.Column('engine_type',   sa.String(50),  nullable=True),
        sa.Column('check_date',    sa.Date(),       nullable=True),
        sa.Column('signature',     sa.String(255),  nullable=True),
        sa.Column('photo_front',   sa.Text(),       nullable=True),
        sa.Column('photo_rear',    sa.Text(),       nullable=True),
        sa.Column('photo_rh',      sa.Text(),       nullable=True),
        sa.Column('photo_lh',      sa.Text(),       nullable=True),
        sa.Column('all_ok',        sa.Boolean(),    nullable=False, server_default='false'),
        sa.Column('submitted_by',  UUID(as_uuid=True),
                  sa.ForeignKey('login_users.user_id'), nullable=True),
        sa.Column('submitted_at',  sa.DateTime(timezone=True), nullable=True),
        sa.Column('created_at',    sa.DateTime(timezone=True),
                  server_default=sa.func.now()),
        sa.Column('updated_at',    sa.DateTime(timezone=True),
                  server_default=sa.func.now(), onupdate=sa.func.now()),
    )

    # ── dispatch_thermal_checksheet_item ──────────────────────────────────────
    op.create_table(
        'dispatch_thermal_checksheet_item',
        sa.Column('item_result_id',      UUID(as_uuid=True), primary_key=True),
        sa.Column('checksheet_id',       UUID(as_uuid=True),
                  sa.ForeignKey('dispatch_thermal_checksheet.checksheet_id'), nullable=False),
        sa.Column('item_no',             sa.Integer(), nullable=False),
        sa.Column('item_text',           sa.Text(),    nullable=True),
        sa.Column('is_ok',               sa.Boolean(), nullable=False, server_default='false'),
        sa.Column('photo_url',           sa.Text(),    nullable=True),
        sa.Column('authority_name',      sa.String(255), nullable=True),
        sa.Column('fault_type',          sa.String(30),  nullable=True),
        sa.Column('remarks',             sa.Text(),    nullable=True),
        sa.Column('mail_screenshot_url', sa.Text(),    nullable=True),
    )

    # ── dispatch_gate_pass ────────────────────────────────────────────────────
    op.create_table(
        'dispatch_gate_pass',
        sa.Column('gate_pass_id',          UUID(as_uuid=True), primary_key=True),
        sa.Column('checksheet_id',         UUID(as_uuid=True),
                  sa.ForeignKey('dispatch_thermal_checksheet.checksheet_id'), nullable=True),
        sa.Column('chassis_id',            UUID(as_uuid=True),
                  sa.ForeignKey('chassis_master.chassis_id'), nullable=False),
        sa.Column('chassis_no',            sa.String(100), nullable=False),
        sa.Column('generated_at',          sa.DateTime(timezone=True),
                  server_default=sa.func.now()),
        sa.Column('fuel_before_ltr',       sa.Numeric(10, 2), nullable=True),
        sa.Column('filling_fuel_qty_ltr',  sa.Numeric(10, 2), nullable=True),
        sa.Column('driver_name',           sa.String(255), nullable=True),
        sa.Column('driver_photo_url',      sa.Text(),      nullable=True),
        sa.Column('dispatch_authority',    sa.String(255), nullable=True),
        sa.Column('created_by',            UUID(as_uuid=True),
                  sa.ForeignKey('login_users.user_id'), nullable=True),
        sa.Column('created_at',            sa.DateTime(timezone=True),
                  server_default=sa.func.now()),
    )

    # ── dispatch_gate_pass_item ───────────────────────────────────────────────
    op.create_table(
        'dispatch_gate_pass_item',
        sa.Column('gate_pass_item_id', UUID(as_uuid=True), primary_key=True),
        sa.Column('gate_pass_id',      UUID(as_uuid=True),
                  sa.ForeignKey('dispatch_gate_pass.gate_pass_id'), nullable=False),
        sa.Column('item_key',          sa.String(50),  nullable=False),
        sa.Column('item_label',        sa.String(100), nullable=False),
        sa.Column('is_checked',        sa.Boolean(),   nullable=False, server_default='true'),
        sa.Column('qty',               sa.Integer(),   nullable=False, server_default='1'),
    )

    # ── upload directory ─────────────────────────────────────────────────────
    import os
    for subdir in ['bus_photos', 'item_photos', 'mail_screenshots', 'driver_photos']:
        os.makedirs(f'uploads/dispatch/{subdir}', exist_ok=True)


def downgrade() -> None:
    op.drop_table('dispatch_gate_pass_item')
    op.drop_table('dispatch_gate_pass')
    op.drop_table('dispatch_thermal_checksheet_item')
    op.drop_table('dispatch_thermal_checksheet')