"""Add vehicle_model_master, point chassis_master.model_id at it, and drop AC/Non-AC from yard (not a relevant attribute at the yard stage). Also carries forward the handful of real chassis inward records that existed only in the old, unused chassis_inward table. Revision ID: f937820abea4 Revises: c7a2e9f4b1d3 Create Date: 2026-07-16 12: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 import text from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision: str = 'f937820abea4' down_revision: Union[str, None] = 'c7a2e9f4b1d3' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def _uuid(): return str(uuid.uuid4()) # The model numbers this yard actually receives, per the current production line-up. # Body type / variant naming stays a production-side concern — this is deliberately # just the plain model number list for the yard's "which model is this chassis" pick. MODEL_NUMBERS = [ '2050 D', '2050 E', '2065 E', '2070 E', '2070 E CNG', '2075 G', '2075 H', '2075 H CNG', '2090 K', '2090 L', '2090 L CNG', '3009 H', '3010 L', '3010 L CNG', '3014 N', '3016 N', '3016 R', '6016 M', '6016 R', '6019 S', ] # Real chassis recorded (via the earlier, now-unused chassis_inward table) that # are worth carrying forward into the live chassis_master table. LEGACY_CHASSIS = [ dict(chassis_id='c3ee1858-c408-495f-819f-1f252de7ca1f', chassis_no='58861', model_number='2075 H', fuel_type='DIESEL', voltage='12V', dealer_name=None, arrival_date='2026-07-16', da_lite_status='PENDING', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 07:56:51.852461+05:30'), dict(chassis_id='b407afae-8629-444e-b32e-17eb7870fa29', chassis_no='58862', model_number='2075 H', fuel_type='DIESEL', voltage='12V', dealer_name=None, arrival_date='2026-07-16', da_lite_status='OK', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 08:05:27.365165+05:30'), dict(chassis_id='0b365510-77ed-493c-85dc-9ab4ef0005ed', chassis_no='58863', model_number='2075 H', fuel_type='DIESEL', voltage='12V', dealer_name=None, arrival_date='2026-07-16', da_lite_status='OK', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 08:10:38.576822+05:30'), dict(chassis_id='b058457a-3a52-4382-9b2a-0a72bae57410', chassis_no='585521', model_number='2075 H', fuel_type='DIESEL', voltage='12V', dealer_name=None, arrival_date='2026-07-16', da_lite_status='HOLD', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 08:15:08.831333+05:30'), dict(chassis_id='b0502408-d5a4-4bd7-b5e0-9781c42dc57d', chassis_no='58552', model_number='2075 H', fuel_type='DIESEL', voltage='12V', dealer_name=None, arrival_date='2026-07-16', da_lite_status='OK', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 08:19:26.499102+05:30'), dict(chassis_id='edb788f9-e998-4225-bdaf-a805f144853d', chassis_no='58865', model_number='2075 H', fuel_type='DIESEL', voltage=None, dealer_name='VECV BHOPAL', arrival_date='2026-07-16', da_lite_status='PENDING', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 08:38:31.204714+05:30'), dict(chassis_id='c6bb6fb3-8a70-44f6-9e39-9014e65567b4', chassis_no='585524', model_number='2075 H', fuel_type='DIESEL', voltage=None, dealer_name='BAJARANG HALDWANI', arrival_date='2026-07-16', da_lite_status='PENDING', current_status='YARD_PENDING_CHECK', created_at='2026-07-16 09:12:32.404425+05:30'), ] def upgrade() -> None: now = datetime.now(timezone.utc) conn = op.get_bind() model_master = op.create_table( 'vehicle_model_master', sa.Column('model_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('model_number', sa.String(20), nullable=False, unique=True), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) op.bulk_insert(model_master, [ {'model_id': _uuid(), 'model_number': number, 'is_active': True, 'created_at': now} for number in MODEL_NUMBERS ]) # AC / Non-AC is not a yard-stage attribute (it only matters for school/staff # vehicle specs downstream) — drop it from the chassis intake record. op.drop_column('chassis_master', 'ac_available') # Re-point model_id at the new clean model master instead of BOM's product_master # (product_master mixes model + body type + fuel into one free-text product name, # which isn't what the yard's "pick a model" dropdown needs). op.drop_constraint('chassis_master_model_id_fkey', 'chassis_master', type_='foreignkey') op.create_foreign_key( 'chassis_master_model_id_fkey', 'chassis_master', 'vehicle_model_master', ['model_id'], ['model_id'], ) # Carry forward the handful of real chassis previously recorded in the old, # code-orphaned chassis_inward table so that data isn't lost on re-clone. for row in LEGACY_CHASSIS: model_id = conn.execute( text("SELECT model_id FROM vehicle_model_master WHERE model_number = :n"), {"n": row['model_number']}, ).scalar() dealer_id = None if row['dealer_name']: dealer_id = conn.execute( text("SELECT customer_id FROM customer_master WHERE name = :n LIMIT 1"), {"n": row['dealer_name']}, ).scalar() conn.execute(text(""" INSERT INTO chassis_master (chassis_id, chassis_no, model_id, fuel_type, voltage, transport_id, dealer_id, source_type, fuel_receiving_qty, arrival_date, da_lite_status, current_status, remarks, created_at, updated_at) VALUES (:chassis_id, :chassis_no, :model_id, :fuel_type, :voltage, NULL, :dealer_id, :source_type, NULL, :arrival_date, :da_lite_status, :current_status, NULL, :created_at, :created_at) ON CONFLICT (chassis_no) DO NOTHING """), { "chassis_id": row['chassis_id'], "chassis_no": row['chassis_no'], "model_id": model_id, "fuel_type": row['fuel_type'], "voltage": row['voltage'], "dealer_id": dealer_id, "source_type": 'SALES_ORDER' if dealer_id else 'SPARE_STOCK', "arrival_date": row['arrival_date'], "da_lite_status": row['da_lite_status'], "current_status": row['current_status'], "created_at": row['created_at'], }) def downgrade() -> None: conn = op.get_bind() conn.execute(text( "DELETE FROM chassis_master WHERE chassis_no = ANY(:nos)" ), {"nos": [row['chassis_no'] for row in LEGACY_CHASSIS]}) op.drop_constraint('chassis_master_model_id_fkey', 'chassis_master', type_='foreignkey') op.create_foreign_key( 'chassis_master_model_id_fkey', 'chassis_master', 'product_master', ['model_id'], ['product_id'], ) op.add_column('chassis_master', sa.Column('ac_available', sa.Boolean, nullable=False, server_default=sa.false())) op.drop_table('vehicle_model_master')