"""Dealer Portal Phase 1: production-configurable masters (body type, customer segment, RTO, special-feature checklist + options), the model<->body-type and model<->segment relationship maps, dealer clients, dealer orders (header + vehicle line + chassis + feature selections + activity log), and dealer portal login credentials/sessions. Revision ID: 142a4473844f Revises: 67b31ad215e2 Create Date: 2026-07-18 09: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 = '142a4473844f' down_revision: Union[str, None] = '67b31ad215e2' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def _uuid(): return str(uuid.uuid4()) # ── Seed data taken directly from the Model / Body Type / School-Staff sheet ── BODY_TYPES = [ 'LEGACY', 'STARLINE', 'CITY FLOW', 'MMU FACE', 'LEGACY GEN-4', 'SKYLINE', 'SKYLINE NEW FACE', 'PRO FACE', 'PRO AMTS', 'PRO WIDER', 'VOLVO FACE', 'HD BAGGAD FACE', ] SEGMENTS = ['School', 'Staff', 'RP'] MODEL_BODY_TYPE_GROUPS = [ (['2050 D', '2050 E'], ['LEGACY', 'STARLINE']), (['2065 E', '2070 E', '2070 E CNG', '2075 G', '2075 H', '2075 H CNG', '2090 K', '2090 L', '2090 L CNG'], ['LEGACY', 'STARLINE', 'CITY FLOW', 'MMU FACE', 'LEGACY GEN-4', 'SKYLINE', 'SKYLINE NEW FACE', 'PRO FACE']), (['3009 H', '3010 L', '3010 L CNG'], ['PRO AMTS', 'PRO WIDER', 'PRO FACE', 'VOLVO FACE']), (['3014 N', '3016 N', '3016 R', '6016 M', '6016 R', '6019 S'], ['HD BAGGAD FACE']), ] # Every model in the sheet is offered under all three segments today. ALL_MODELS = [m for models, _ in MODEL_BODY_TYPE_GROUPS for m in models] # ── Seed data taken from the dealer "Req spec" checklist, split into the three # sections it's grouped under. Dropdown option lists are a sensible starting # point — production can add/remove/edit both features and options at any time # from the Production Masters screen (Phase 5), with no code change needed. # Model Type / Chassis Modal / Body Types rows are intentionally excluded here # since they're already captured by the order wizard's model/body-type pickers. SPECIAL_FEATURES = [ ('Dealer / Customer', 'Bus Structure & Body Type', 'DROPDOWN', ['Monocoque', 'Ladder Frame Chassis']), ('Dealer / Customer', 'Luggage Compartments (RLC)', 'DROPDOWN', ['Rear Luggage Compartment (RLC)', 'Side Luggage Compartment', 'Both', 'None']), ('Dealer / Customer', 'Rear Dicky', 'BOOLEAN', None), ('Dealer / Customer', 'Side Dicky', 'BOOLEAN', None), ('Dealer / Customer', 'Sunvan and Gangway', 'BOOLEAN', None), ('Dealer / Customer', 'Structure - GP our HR', 'TEXT', None), ('Dealer / Customer', 'Plywood Checker', 'BOOLEAN', None), ('Dealer / Customer', 'AC / Non-AC Variant', 'DROPDOWN', ['AC - Roof-mounted Ducted', 'Non-AC - Ceiling Fans', 'Non-AC - Natural Ventilation Windows']), ('Dealer / Customer', 'Partition', 'DROPDOWN', ['With Partition', 'Without Partition']), ('Interior Features', 'Seat Width', 'DROPDOWN', ['16 inch', '18 inch', '20 inch']), ('Interior Features', 'Seating Capacity', 'TEXT', None), ('Interior Features', 'Seat Type', 'DROPDOWN', ['Push Back', 'HHR']), ('Interior Features', 'Seat Belt', 'DROPDOWN', ['2-Point', '3-Point']), ('Interior Features', 'Seat Material', 'DROPDOWN', ['Raxine', 'Cloth', 'Leather']), ('Interior Features', 'Seat Colour', 'TEXT', None), ('Interior Features', 'Vinyl / Chequer', 'DROPDOWN', ['Vinyl', 'Chequer']), ('Interior Features', 'LED Aisle & Seating Lights', 'BOOLEAN', None), ('Interior Features', 'Mobile Charging Points', 'BOOLEAN', None), ('Interior Features', 'Rear-View Camera Screen Size', 'DROPDOWN', ['6 inch', '9 inch']), ('Interior Features', 'Camera Package (Std. 4-Camera)', 'BOOLEAN', None), ('Interior Features', 'Window Glass Colour', 'DROPDOWN', ['Black', 'Grey', 'Blue', 'Clear/Tinted']), ('Interior Features', 'Window Guard Rail Qty', 'TEXT', None), ('Interior Features', 'LED Destination Board', 'BOOLEAN', None), ('Interior Features', 'Watch', 'BOOLEAN', None), ('Interior Features', 'FAPS', 'BOOLEAN', None), ('Interior Features', 'FDAS / FDSS', 'DROPDOWN', ['FDAS', 'FDSS', 'Both', 'None']), ('Exterior Specifications', 'Final Colour', 'DROPDOWN', ['White', 'Silver', 'Blue', 'Red', 'Yellow', 'Custom']), ('Exterior Specifications', 'Additional Specifications', 'TEXT', None), ('Exterior Specifications', 'RTO Compliance (AIS 153)', 'BOOLEAN', None), ] def upgrade() -> None: now = datetime.now(timezone.utc) conn = op.get_bind() # ── Production-configurable masters ── body_type_master = op.create_table( 'body_type_master', sa.Column('body_type_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('name', sa.String(100), nullable=False, unique=True), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) body_type_ids = {name: _uuid() for name in BODY_TYPES} op.bulk_insert(body_type_master, [ {'body_type_id': body_type_ids[name], 'name': name, 'is_active': True, 'created_at': now} for name in BODY_TYPES ]) op.create_table( 'model_body_type_map', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('model_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('vehicle_model_master.model_id'), nullable=False), sa.Column('body_type_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('body_type_master.body_type_id'), nullable=False), sa.UniqueConstraint('model_id', 'body_type_id', name='uq_model_body_type'), ) segment_master = op.create_table( 'customer_segment_master', sa.Column('segment_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('name', sa.String(100), nullable=False, unique=True), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) segment_ids = {name: _uuid() for name in SEGMENTS} op.bulk_insert(segment_master, [ {'segment_id': segment_ids[name], 'name': name, 'is_active': True, 'created_at': now} for name in SEGMENTS ]) op.create_table( 'model_segment_map', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('model_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('vehicle_model_master.model_id'), nullable=False), sa.Column('segment_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('customer_segment_master.segment_id'), nullable=False), sa.UniqueConstraint('model_id', 'segment_id', name='uq_model_segment'), ) op.create_table( 'rto_master', sa.Column('rto_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('name', sa.String(150), nullable=False), sa.Column('description', sa.Text), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) # No RTO list was supplied — seeded empty; added from the Production Masters screen. feature_master = op.create_table( 'special_feature_master', sa.Column('feature_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('section', sa.String(100), nullable=False), sa.Column('feature_name', sa.String(200), nullable=False), sa.Column('input_type', sa.String(20), nullable=False, server_default='BOOLEAN'), 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)), ) feature_option_master = op.create_table( 'special_feature_option_master', sa.Column('option_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('feature_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('special_feature_master.feature_id'), nullable=False), sa.Column('option_value', 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()), ) feature_rows = [] option_rows = [] for i, (section, feature_name, input_type, options) in enumerate(SPECIAL_FEATURES): feature_id = _uuid() feature_rows.append({ 'feature_id': feature_id, 'section': section, 'feature_name': feature_name, 'input_type': input_type, 'sequence_no': i, 'is_active': True, 'created_at': now, }) if options: for j, option_value in enumerate(options): option_rows.append({ 'option_id': _uuid(), 'feature_id': feature_id, 'option_value': option_value, 'sequence_no': j, 'is_active': True, }) op.bulk_insert(feature_master, feature_rows) op.bulk_insert(feature_option_master, option_rows) # ── model_id lookups (vehicle_model_master already exists, seeded in an earlier migration) ── model_ids = dict(conn.execute(text("SELECT model_number, model_id FROM vehicle_model_master")).fetchall()) for models, body_types in MODEL_BODY_TYPE_GROUPS: for model_number in models: if model_number not in model_ids: continue for body_type_name in body_types: conn.execute(text( "INSERT INTO model_body_type_map (id, model_id, body_type_id) VALUES (:id, :model_id, :body_type_id)" ), {"id": _uuid(), "model_id": str(model_ids[model_number]), "body_type_id": body_type_ids[body_type_name]}) for model_number in ALL_MODELS: if model_number not in model_ids: continue for segment_name in SEGMENTS: conn.execute(text( "INSERT INTO model_segment_map (id, model_id, segment_id) VALUES (:id, :model_id, :segment_id)" ), {"id": _uuid(), "model_id": str(model_ids[model_number]), "segment_id": segment_ids[segment_name]}) # ── Dealer credentials on the existing customer_master (mirrors vendor_master) ── op.add_column('customer_master', sa.Column('portal_enabled', sa.Boolean, nullable=False, server_default=sa.false())) op.add_column('customer_master', sa.Column('portal_username', sa.String(100), unique=True)) op.add_column('customer_master', sa.Column('portal_password_hash', sa.String(255))) op.create_table( 'dealer_portal_log', sa.Column('log_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('dealer_id', sa.String(50), sa.ForeignKey('customer_master.customer_id'), nullable=False), sa.Column('session_token', sa.String(128), nullable=False, unique=True), sa.Column('ip_address', sa.String(50)), sa.Column('user_agent', sa.Text), sa.Column('issued_at', sa.DateTime(timezone=True), nullable=False), sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False), sa.Column('invalidated_at', sa.DateTime(timezone=True)), ) # ── Dealer's clients ── op.create_table( 'dealer_client_master', sa.Column('client_id', sa.String(50), primary_key=True), sa.Column('dealer_id', sa.String(50), sa.ForeignKey('customer_master.customer_id'), nullable=False), sa.Column('name', sa.String(200), nullable=False), sa.Column('contact_person', sa.String(150)), sa.Column('phone', sa.String(20)), sa.Column('email', sa.String(150)), sa.Column('address', sa.Text), sa.Column('city', sa.String(100)), sa.Column('state', sa.String(100)), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_by_dealer', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('created_by_user_id', 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)), ) # ── Dealer orders ── op.create_table( 'dealer_order', sa.Column('order_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('order_no', sa.String(50), nullable=False, unique=True), sa.Column('dealer_id', sa.String(50), sa.ForeignKey('customer_master.customer_id'), nullable=False), sa.Column('client_id', sa.String(50), sa.ForeignKey('dealer_client_master.client_id'), nullable=False), sa.Column('status', sa.String(30), nullable=False, server_default='DRAFT'), sa.Column('remarks', sa.Text), sa.Column('submitted_at', sa.DateTime(timezone=True)), sa.Column('confirmed_at', sa.DateTime(timezone=True)), sa.Column('build_order_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('build_order.order_id'), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True)), sa.Column('updated_at', sa.DateTime(timezone=True)), ) op.create_table( 'dealer_order_vehicle', sa.Column('order_vehicle_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('order_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('dealer_order.order_id'), nullable=False), sa.Column('model_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('vehicle_model_master.model_id'), nullable=False), sa.Column('body_type_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('body_type_master.body_type_id'), nullable=False), sa.Column('segment_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('customer_segment_master.segment_id'), nullable=False), sa.Column('rto_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('rto_master.rto_id'), nullable=True), sa.Column('qty', sa.Integer, nullable=False, server_default='1'), sa.Column('sequence_no', sa.Integer, nullable=False, server_default='0'), sa.Column('notes', sa.Text), ) op.create_table( 'dealer_order_vehicle_chassis', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('order_vehicle_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('dealer_order_vehicle.order_vehicle_id'), nullable=False), sa.Column('seq_no', sa.Integer, nullable=False, server_default='1'), sa.Column('chassis_no', sa.String(100)), ) op.create_table( 'dealer_order_vehicle_feature', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('order_vehicle_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('dealer_order_vehicle.order_vehicle_id'), nullable=False), sa.Column('feature_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('special_feature_master.feature_id'), nullable=False), sa.Column('feature_option_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('special_feature_option_master.option_id'), nullable=True), sa.Column('bool_value', sa.Boolean), sa.Column('text_value', sa.Text), sa.UniqueConstraint('order_vehicle_id', 'feature_id', name='uq_order_vehicle_feature'), ) op.create_table( 'dealer_order_activity_log', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('order_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('dealer_order.order_id'), nullable=False), sa.Column('action', sa.String(30), nullable=False), sa.Column('performed_by_dealer', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('performed_by_user_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('login_users.user_id'), nullable=True), sa.Column('notes', sa.Text), sa.Column('created_at', sa.DateTime(timezone=True)), ) # ── RBAC: staff-side dealer/order-review administration ── 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': 'DEALER_PORTAL_ADMIN', 'module_name': 'Dealer Portal Admin', 'description': 'Dealer records, portal credentials, client lists, and dealer order review/confirmation', '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)), ) dealer_admin_matrix = [ ('ADMIN', True, True, True, True, True), ('SALES_MGR', True, True, True, True, True), ('SALES_EXEC', True, True, True, False, False), ('VIEWER', True, False, False, False, False), ] op.bulk_insert(role_module_permission, [ {'id': _uuid(), 'role_code': r, 'module_code': 'DEALER_PORTAL_ADMIN', '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 dealer_admin_matrix ]) def downgrade() -> None: op.execute("DELETE FROM role_module_permission WHERE module_code = 'DEALER_PORTAL_ADMIN'") op.execute("DELETE FROM module_master WHERE module_code = 'DEALER_PORTAL_ADMIN'") op.drop_table('dealer_order_activity_log') op.drop_table('dealer_order_vehicle_feature') op.drop_table('dealer_order_vehicle_chassis') op.drop_table('dealer_order_vehicle') op.drop_table('dealer_order') op.drop_table('dealer_client_master') op.drop_table('dealer_portal_log') op.drop_column('customer_master', 'portal_password_hash') op.drop_column('customer_master', 'portal_username') op.drop_column('customer_master', 'portal_enabled') op.drop_table('special_feature_option_master') op.drop_table('special_feature_master') op.drop_table('rto_master') op.drop_table('model_segment_map') op.drop_table('customer_segment_master') op.drop_table('model_body_type_map') op.drop_table('body_type_master')