"""Add yard inward tables (chassis tracking, checklists, movement/fuel log) Revision ID: c7a2e9f4b1d3 Revises: b4e7f1c9a6d2 Create Date: 2026-07-16 16:00:00.000000 """ import uuid from typing import Sequence, Union from datetime import datetime, timezone from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision: str = 'c7a2e9f4b1d3' down_revision: Union[str, None] = 'b4e7f1c9a6d2' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def _uuid(): return str(uuid.uuid4()) def upgrade() -> None: now = datetime.now(timezone.utc) checkpoint_master = op.create_table( 'yard_checkpoint_master', sa.Column('checkpoint_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('checkpoint_code', sa.String(50), nullable=False, unique=True), sa.Column('checkpoint_name', sa.String(150), nullable=False), sa.Column('sequence_no', sa.Integer, nullable=False, server_default='0'), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) movement_item_master = op.create_table( 'movement_checklist_item_master', sa.Column('item_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('item_code', sa.String(50), nullable=False, unique=True), sa.Column('item_label', sa.String(150), nullable=False), sa.Column('sequence_no', sa.Integer, nullable=False, server_default='0'), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), ) qa_item_master = op.create_table( 'yard_qa_checklist_item_master', sa.Column('item_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('zone_name', sa.String(150), nullable=False), sa.Column('checkpoint_text', sa.Text, nullable=False), sa.Column('sequence_no', sa.Integer, nullable=False, server_default='0'), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), ) op.create_table( 'chassis_master', sa.Column('chassis_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('chassis_no', sa.String(100), nullable=False, unique=True), sa.Column('model_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('product_master.product_id'), nullable=True), sa.Column('fuel_type', sa.String(20), nullable=False, server_default='DIESEL'), sa.Column('ac_available', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('voltage', sa.String(20)), sa.Column('transport_id', sa.String(50), sa.ForeignKey('transport_master.transport_id'), nullable=True), sa.Column('dealer_id', sa.String(50), sa.ForeignKey('customer_master.customer_id'), nullable=True), sa.Column('source_type', sa.String(20), nullable=False, server_default='SALES_ORDER'), sa.Column('build_order_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('build_order.order_id'), nullable=True), sa.Column('fuel_receiving_qty', sa.Numeric(10, 2)), sa.Column('arrival_date', sa.Date, nullable=False), sa.Column('da_lite_status', sa.String(20), nullable=False, server_default='PENDING'), sa.Column('da_lite_checked_at', sa.DateTime(timezone=True)), sa.Column('da_lite_checked_by', postgresql.UUID(as_uuid=True), sa.ForeignKey('login_users.user_id'), nullable=True), sa.Column('current_status', sa.String(30), nullable=False, server_default='YARD_PENDING_CHECK'), sa.Column('handed_to_production_at', sa.DateTime(timezone=True)), sa.Column('handed_to_production_by', postgresql.UUID(as_uuid=True), sa.ForeignKey('login_users.user_id'), nullable=True), sa.Column('remarks', sa.Text), sa.Column('created_by', postgresql.UUID(as_uuid=True), sa.ForeignKey('login_users.user_id'), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True)), sa.Column('updated_at', sa.DateTime(timezone=True)), ) op.create_table( 'chassis_movement_log', sa.Column('movement_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('chassis_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('chassis_master.chassis_id'), nullable=False), sa.Column('checkpoint_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('yard_checkpoint_master.checkpoint_id'), nullable=False), sa.Column('responsible_person', sa.String(150)), sa.Column('movement_date', sa.Date), sa.Column('in_time', sa.Time), sa.Column('out_time', sa.Time), sa.Column('fuel_qty', sa.Numeric(10, 2)), sa.Column('remark', sa.Text), sa.Column('signature_name', sa.String(150)), sa.Column('created_at', sa.DateTime(timezone=True)), ) op.create_table( 'chassis_movement_checklist_result', sa.Column('result_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('movement_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('chassis_movement_log.movement_id'), nullable=False), sa.Column('item_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('movement_checklist_item_master.item_id'), nullable=False), sa.Column('is_ok', sa.Boolean), sa.Column('value_text', sa.String(100)), ) op.create_table( 'chassis_qa_checklist_result', sa.Column('result_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('chassis_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('chassis_master.chassis_id'), nullable=False), sa.Column('item_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('yard_qa_checklist_item_master.item_id'), nullable=False), sa.Column('yard_remark', sa.String(255)), sa.Column('bhagirath_remark', sa.String(255)), sa.Column('fvi_remark', sa.String(255)), sa.Column('fvi_rework_remark', sa.String(255)), sa.Column('fvi_rework_pending_remark', sa.String(255)), sa.Column('updated_at', sa.DateTime(timezone=True)), sa.UniqueConstraint('chassis_id', 'item_id', name='uq_chassis_qa_item'), ) op.create_table( 'chassis_parts_removed', sa.Column('record_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('chassis_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('chassis_master.chassis_id'), nullable=False, unique=True), sa.Column('toolkit_tag', sa.Boolean, server_default=sa.false()), sa.Column('main_key_tag', sa.Boolean, server_default=sa.false()), sa.Column('urea_tank_key_tag', sa.Boolean, server_default=sa.false()), sa.Column('urea_plat', sa.Boolean, server_default=sa.false()), sa.Column('wiper_motor_kit', sa.Boolean, server_default=sa.false()), sa.Column('jack', sa.Boolean, server_default=sa.false()), sa.Column('meter_tag', sa.Boolean, server_default=sa.false()), sa.Column('tail_light_tag', sa.Boolean, server_default=sa.false()), sa.Column('head_light_tag', sa.Boolean, server_default=sa.false()), sa.Column('cng_plat', sa.Boolean, server_default=sa.false()), sa.Column('sensor_kit_envelope', sa.Boolean, server_default=sa.false()), sa.Column('responsible_person', sa.String(150)), sa.Column('signature_name', sa.String(150)), sa.Column('created_at', sa.DateTime(timezone=True)), ) # ── Seed: yard checkpoints, in the order chassis actually move through them ── checkpoints = [ ('ACRE_34_YARD', '34 Acre Yard'), ('UNIT2_GATE_IN', 'Unit-2 Gate Receiving'), ('UNIT2_PRODUCTION', 'Unit-2 Production'), ('UNIT2_GATE_OUT', 'Unit-2 Gate Out Receipt'), ('AUDI_YARD_RECEIPT', 'Audi Yard Receipt'), ('AUDI_MAIN_GATE', 'Audi Main Gate Receipt'), ('AC_VENDOR_OUT', 'AC Vendor Out Receipt'), ('AUDI_PRODUCTION_RECEIPT', 'Audi Production Receipt'), ('AUDI_STORE_RECEIPT', 'Audi Store Receipt'), ('DA_LITE_PROCESS', 'DA-Lite Process'), ('BUS_DISPATCHED', 'Bus Dispatched'), ('SPECIAL_WORK_1', 'Special Work 1'), ('SPECIAL_WORK_2', 'Special Work 2'), ('SPECIAL_WORK_3', 'Special Work 3'), ] op.bulk_insert(checkpoint_master, [ {'checkpoint_id': _uuid(), 'checkpoint_code': code, 'checkpoint_name': name, 'sequence_no': i, 'is_active': True, 'created_at': now} for i, (code, name) in enumerate(checkpoints) ]) # ── Seed: movement checklist items (Chassis Movement Check Sheet No. 03) ── movement_items = [ 'INVOICE', 'SERVICE_COUPON', 'HORN', 'ECCO_PLUS_SWITCH', 'ECCO_POWER_SWITCH', 'UREA_SWITCH', 'ABS_SWITCH', 'CUT_OFF_SWITCH', 'BATTERY_BOTH_TERMINAL', 'STEPNEY', 'JACK', ] movement_labels = { 'INVOICE': 'Invoice', 'SERVICE_COUPON': 'Service Coupon', 'HORN': 'Horn', 'ECCO_PLUS_SWITCH': 'ECCO+ Switch', 'ECCO_POWER_SWITCH': 'ECCO Power Switch', 'UREA_SWITCH': 'Urea Switch', 'ABS_SWITCH': 'ABS Switch', 'CUT_OFF_SWITCH': 'Cut-off Switch', 'BATTERY_BOTH_TERMINAL': 'Battery with Both Terminal', 'STEPNEY': 'Stepney', 'JACK': 'Jack', } op.bulk_insert(movement_item_master, [ {'item_id': _uuid(), 'item_code': code, 'item_label': movement_labels[code], 'sequence_no': i, 'is_active': True} for i, code in enumerate(movement_items) ]) # ── Seed: Yard QA / FVI checklist (Inspection Zone -> Check Points) ── qa_zones = [ ('Bus Interior Cluster Meter', [ 'Cluster meter all symbol check', 'Urea level check', 'Battery symbol check & Starting time battery voltage increase till 13.5V', 'Parking symbol check', 'Cluster meter push switch check', 'Cluster meter light visual & Damage check', 'No plastic stick to cluster meter', 'Switch board fitment', ]), ('Gauges & Level', ['Engine oil, P/stg oil, clutch oil & Coolant']), ('Cap & Gauges', ['Fuel tank, DAT, Adblue tank, Engine oil, and Thermostat cap locking']), ('Steering Function', [ 'No hard rotation', 'No abnormal noise', 'No oil leakage from vane pump', 'No oil leakage from stg gear box', 'No oil leakage from reservoir tank', 'No damage hose pipe & Metal pipe', 'Steering wheel cut & damage check', ]), ('Clutch Functionality', [ 'Clutch pedal free play', 'No clutch pedal abnormal noise', 'No clutch pedal hard operation', 'No clutch master cyl oil leakage', 'No clutch master cyl damage & broken', 'No clutch booster leakage', ]), ('Brake Functionality', [ 'Brake pedal free play', 'No brake pedal abnormal noise', 'No brake pedal hard operation', 'Brake pressed time no air leakage', '100% check ABS (Modulator) sound', ]), ('Gear Engagement', ['All gear smooth function', 'Reverse buzzer working', 'GSL knob fitment & damage']), ('Coolant Leakage', ['Radiator leakage & damage check', 'DAT tank leakage & damage check', 'Cooling system all hose check']), ('Battery Functionality', [ 'Battery terminal loose & damage check', 'Battery cut off terminal loose & damage', 'Battery inline fuse & harness cut check', 'Maxi fuse & cap check & harness cut', 'Battery cut of timer working & damage check', ]), ('Sensor Check', [ 'Vehicle speed sensor check', 'No speed meter fluctuation on cluster meter', 'Parking lever off & On time symbol check on cluster meter', 'Sure 100% ABS, MIL, Stop, ESC, ECT symbol not show on cluster meter', ]), ('EATS & EATS Sensor', [ 'EATS external dent', 'No coolant leakage & damage EATS hoses', 'No urea leakage & dosing injector damage', 'No harness, air pipe, coolant pipe, urea pipe fouls with EATS', 'T4, T5, T6, Post nox sensor, & DP sensor harness & coupler damage check', ]), ('DA-Lite 100%', ['Complete DA-Lite with Regeneration']), ('Visual Check', ['Horn working, Stg wheel noise, Starter relay damage & Fuse box all fuse and relay check']), ] qa_rows = [] seq = 0 for zone_name, checkpoints_list in qa_zones: for checkpoint_text in checkpoints_list: qa_rows.append({ 'item_id': _uuid(), 'zone_name': zone_name, 'checkpoint_text': checkpoint_text, 'sequence_no': seq, 'is_active': True, }) seq += 1 op.bulk_insert(qa_item_master, qa_rows) # ── Register YARD as a real module in the RBAC system (tables added in the prior migration) ── module_master = sa.table( 'module_master', sa.column('module_id', postgresql.UUID(as_uuid=True)), sa.column('module_code', sa.String), sa.column('module_name', sa.String), sa.column('description', sa.String), sa.column('is_active', sa.Boolean), sa.column('created_at', sa.DateTime(timezone=True)), ) op.bulk_insert(module_master, [{ 'module_id': _uuid(), 'module_code': 'YARD', 'module_name': 'Yard Inward', 'description': 'Chassis inward, yard/FVI QA checklist, movement & fuel log, handover to production', 'is_active': True, 'created_at': now, }]) role_module_permission = sa.table( 'role_module_permission', sa.column('id', postgresql.UUID(as_uuid=True)), sa.column('role_code', sa.String), sa.column('module_code', sa.String), sa.column('can_view', sa.Boolean), sa.column('can_create', sa.Boolean), sa.column('can_edit', sa.Boolean), sa.column('can_delete', sa.Boolean), sa.column('can_approve', sa.Boolean), sa.column('updated_at', sa.DateTime(timezone=True)), ) yard_matrix = [ ('ADMIN', True, True, True, True, True), ('PRODUCTION_MGR', True, True, True, False, True), ('STORE_MGR', True, True, True, False, False), ('STORE_KEEPER', True, True, True, False, False), ('VIEWER', True, False, False, False, False), ] op.bulk_insert(role_module_permission, [ {'id': _uuid(), 'role_code': r, 'module_code': 'YARD', 'can_view': v, 'can_create': c, 'can_edit': e, 'can_delete': d, 'can_approve': a, 'updated_at': now} for (r, v, c, e, d, a) in yard_matrix ]) def downgrade() -> None: op.execute("DELETE FROM role_module_permission WHERE module_code = 'YARD'") op.execute("DELETE FROM module_master WHERE module_code = 'YARD'") op.drop_table('chassis_parts_removed') op.drop_table('chassis_qa_checklist_result') op.drop_table('chassis_movement_checklist_result') op.drop_table('chassis_movement_log') op.drop_table('chassis_master') op.drop_table('yard_qa_checklist_item_master') op.drop_table('movement_checklist_item_master') op.drop_table('yard_checkpoint_master')