"""Add ClientMaster and modify DealerOrder and ChassisMaster

Revision ID: 2fe975d2227b
Revises: f194f27d2c02
Create Date: 2026-08-09 17:17:20.513005

"""
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 = '2fe975d2227b'
down_revision: Union[str, None] = 'f194f27d2c02'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('client_master',
    sa.Column('client_id', sa.String(length=50), nullable=False),
    sa.Column('name', sa.String(length=200), nullable=False),
    sa.Column('contact_person', sa.String(length=150), nullable=True),
    sa.Column('phone', sa.String(length=20), nullable=True),
    sa.Column('email', sa.String(length=150), nullable=True),
    sa.Column('address', sa.Text(), nullable=True),
    sa.Column('city', sa.String(length=100), nullable=True),
    sa.Column('state', sa.String(length=100), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=False),
    sa.Column('created_by_user_id', sa.UUID(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['login_users.user_id'], ),
    sa.PrimaryKeyConstraint('client_id')
    )
    op.create_table('sales_vehicle_template',
    sa.Column('template_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=False),
    sa.Column('model_id', sa.UUID(), nullable=False),
    sa.Column('body_type_id', sa.UUID(), nullable=False),
    sa.Column('segment_id', sa.UUID(), nullable=False),
    sa.Column('created_by_user_id', sa.UUID(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['body_type_id'], ['body_type_master.body_type_id'], ),
    sa.ForeignKeyConstraint(['created_by_user_id'], ['login_users.user_id'], ),
    sa.ForeignKeyConstraint(['model_id'], ['vehicle_model_master.model_id'], ),
    sa.ForeignKeyConstraint(['segment_id'], ['customer_segment_master.segment_id'], ),
    sa.PrimaryKeyConstraint('template_id')
    )
    op.create_table('dealer_client_map',
    sa.Column('map_id', sa.UUID(), nullable=False),
    sa.Column('dealer_id', sa.String(length=50), nullable=False),
    sa.Column('client_id', sa.String(length=50), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['client_id'], ['client_master.client_id'], ),
    sa.ForeignKeyConstraint(['dealer_id'], ['customer_master.customer_id'], ),
    sa.PrimaryKeyConstraint('map_id')
    )
    
    # DATA MIGRATION: Move old dealer_client_master data into client_master and dealer_client_map
    op.execute("""
        INSERT INTO client_master (client_id, name, contact_person, phone, email, address, city, state, is_active, created_by_user_id, created_at, updated_at)
        SELECT client_id, name, contact_person, phone, email, address, city, state, is_active, created_by_user_id, created_at, updated_at
        FROM dealer_client_master;
    """)
    
    op.execute("""
        INSERT INTO dealer_client_map (map_id, dealer_id, client_id, created_at)
        SELECT gen_random_uuid(), dealer_id, client_id, created_at
        FROM dealer_client_master;
    """)

    op.create_table('sales_vehicle_template_feature',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('template_id', sa.UUID(), nullable=False),
    sa.Column('feature_id', sa.UUID(), nullable=False),
    sa.Column('feature_option_id', sa.UUID(), nullable=True),
    sa.Column('bool_value', sa.Boolean(), nullable=True),
    sa.Column('text_value', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['feature_id'], ['special_feature_master.feature_id'], ),
    sa.ForeignKeyConstraint(['feature_option_id'], ['special_feature_option_master.option_id'], ),
    sa.ForeignKeyConstraint(['template_id'], ['sales_vehicle_template.template_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.add_column('chassis_master', sa.Column('client_id', sa.String(length=50), nullable=True))
    op.create_foreign_key(None, 'chassis_master', 'client_master', ['client_id'], ['client_id'])
    op.drop_constraint('dealer_order_client_id_fkey', 'dealer_order', type_='foreignkey')
    op.create_foreign_key(None, 'dealer_order', 'client_master', ['client_id'], ['client_id'])
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'dealer_order', type_='foreignkey')
    op.create_foreign_key('dealer_order_client_id_fkey', 'dealer_order', 'dealer_client_master', ['client_id'], ['client_id'])
    op.drop_constraint(None, 'chassis_master', type_='foreignkey')
    op.create_foreign_key('fk_chassis_build_spec', 'chassis_master', 'build_spec', ['build_spec_id'], ['spec_id'])
    op.drop_column('chassis_master', 'client_id')
    op.create_foreign_key('fk_bo_build_spec', 'build_order', 'build_spec', ['build_spec_id'], ['spec_id'])
    op.create_table('production_capacity_config',
    sa.Column('config_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('station_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('max_wip', sa.INTEGER(), server_default=sa.text('3'), autoincrement=False, nullable=False),
    sa.Column('current_wip', sa.INTEGER(), server_default=sa.text('0'), autoincrement=False, nullable=False),
    sa.Column('avg_days_per_unit', sa.NUMERIC(precision=5, scale=1), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['station_id'], ['station_master.station_id'], name='production_capacity_config_station_id_fkey'),
    sa.PrimaryKeyConstraint('config_id', name='production_capacity_config_pkey'),
    sa.UniqueConstraint('station_id', name='production_capacity_config_station_id_key')
    )
    op.create_table('build_order_approval_log',
    sa.Column('log_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('order_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('gate', sa.VARCHAR(length=30), autoincrement=False, nullable=False),
    sa.Column('action', sa.VARCHAR(length=30), autoincrement=False, nullable=False),
    sa.Column('from_status', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('to_status', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('performed_by', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('performed_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=False),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('snapshot', postgresql.JSON(astext_type=sa.Text()), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['order_id'], ['build_order.order_id'], name='build_order_approval_log_order_id_fkey'),
    sa.ForeignKeyConstraint(['performed_by'], ['login_users.user_id'], name='build_order_approval_log_performed_by_fkey'),
    sa.PrimaryKeyConstraint('log_id', name='build_order_approval_log_pkey')
    )
    op.create_table('build_spec',
    sa.Column('spec_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('chassis_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('enquiry_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('chassis_model', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('body_type', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('bus_use', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('seating_capacity', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('seating_layout', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('ac_type', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('door_config', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('tail_lamp_type', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('faps_config', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('special_requirements', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('bom_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=20), server_default=sa.text("'DRAFT'::character varying"), autoincrement=False, nullable=False),
    sa.Column('procurement_all_confirmed', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=True),
    sa.Column('created_by', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('approved_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('approved_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('approval_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['approved_by'], ['login_users.user_id'], name='build_spec_approved_by_fkey'),
    sa.ForeignKeyConstraint(['bom_id'], ['bom_master.bom_id'], name='build_spec_bom_id_fkey'),
    sa.ForeignKeyConstraint(['chassis_id'], ['chassis_master.chassis_id'], name='build_spec_chassis_id_fkey'),
    sa.ForeignKeyConstraint(['created_by'], ['login_users.user_id'], name='build_spec_created_by_fkey'),
    sa.ForeignKeyConstraint(['enquiry_id'], ['sales_enquiry.enquiry_id'], name='build_spec_enquiry_id_fkey'),
    sa.PrimaryKeyConstraint('spec_id', name='build_spec_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('build_spec_bom_line',
    sa.Column('line_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('spec_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('rm_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('feature_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('quantity_required', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=False),
    sa.Column('uom', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('procurement_source', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('is_customer_supplied', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=True),
    sa.Column('source_type', sa.VARCHAR(length=20), server_default=sa.text("'BASE_BOM'::character varying"), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['feature_id'], ['build_spec_feature.feature_id'], name='build_spec_bom_line_feature_id_fkey'),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], name='build_spec_bom_line_rm_id_fkey'),
    sa.ForeignKeyConstraint(['spec_id'], ['build_spec.spec_id'], name='build_spec_bom_line_spec_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('line_id', name='build_spec_bom_line_pkey')
    )
    op.create_table('build_spec_feature',
    sa.Column('feature_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('spec_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('feature_category', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('feature_code', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('feature_name', sa.VARCHAR(length=150), autoincrement=False, nullable=False),
    sa.Column('feature_value', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('is_customer_supplied', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=True),
    sa.Column('csp_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['csp_id'], ['customer_supplied_part.csp_id'], name='build_spec_feature_csp_id_fkey'),
    sa.ForeignKeyConstraint(['spec_id'], ['build_spec.spec_id'], name='build_spec_feature_spec_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('feature_id', name='build_spec_feature_pkey')
    )
    op.create_table('procurement_category_approval',
    sa.Column('approval_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('spec_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('category', sa.VARCHAR(length=30), autoincrement=False, nullable=False),
    sa.Column('status', sa.VARCHAR(length=20), server_default=sa.text("'PENDING'::character varying"), autoincrement=False, nullable=False),
    sa.Column('expected_ready_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('blocked_reason', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('reviewed_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('reviewed_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['reviewed_by'], ['login_users.user_id'], name='procurement_category_approval_reviewed_by_fkey'),
    sa.ForeignKeyConstraint(['spec_id'], ['build_spec.spec_id'], name='procurement_category_approval_spec_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('approval_id', name='procurement_category_approval_pkey'),
    sa.UniqueConstraint('spec_id', 'category', name='uq_spec_category')
    )
    op.create_table('feature_bom_mapping',
    sa.Column('mapping_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('feature_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('feature_option_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('rm_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('quantity', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['feature_id'], ['special_feature_master.feature_id'], name='feature_bom_mapping_feature_id_fkey'),
    sa.ForeignKeyConstraint(['feature_option_id'], ['special_feature_option_master.option_id'], name='feature_bom_mapping_feature_option_id_fkey'),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], name='feature_bom_mapping_rm_id_fkey'),
    sa.PrimaryKeyConstraint('mapping_id', name='feature_bom_mapping_pkey')
    )
    op.create_table('build_order_bom',
    sa.Column('bom_line_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('order_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('rm_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('required_qty', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=False),
    sa.Column('available_qty', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=True),
    sa.Column('shortage_qty', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=True),
    sa.Column('reserved_qty', sa.NUMERIC(precision=14, scale=4), server_default=sa.text("'0'::numeric"), autoincrement=False, nullable=True),
    sa.Column('uom', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('procurement_source', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('is_customer_supplied', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=True),
    sa.Column('unit_cost', sa.NUMERIC(precision=14, scale=2), autoincrement=False, nullable=True),
    sa.Column('line_value', sa.NUMERIC(precision=14, scale=2), autoincrement=False, nullable=True),
    sa.Column('pr_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=20), server_default=sa.text("'PENDING'::character varying"), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['order_id'], ['build_order.order_id'], name='build_order_bom_order_id_fkey', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['pr_id'], ['purchase_request.pr_id'], name='fk_bo_bom_pr'),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], name='build_order_bom_rm_id_fkey'),
    sa.PrimaryKeyConstraint('bom_line_id', name='build_order_bom_pkey')
    )
    op.create_table('purchase_request',
    sa.Column('pr_id', sa.UUID(), server_default=sa.text('gen_random_uuid()'), autoincrement=False, nullable=False),
    sa.Column('pr_number', sa.VARCHAR(length=30), autoincrement=False, nullable=False),
    sa.Column('order_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('rm_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('required_qty', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=False),
    sa.Column('shortage_qty', sa.NUMERIC(precision=14, scale=4), autoincrement=False, nullable=False),
    sa.Column('uom', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('priority', sa.VARCHAR(length=10), server_default=sa.text("'NORMAL'::character varying"), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=20), server_default=sa.text("'OPEN'::character varying"), autoincrement=False, nullable=False),
    sa.Column('po_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('raised_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('raised_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['order_id'], ['build_order.order_id'], name='purchase_request_order_id_fkey'),
    sa.ForeignKeyConstraint(['po_id'], ['rm_purchase_order.po_id'], name='purchase_request_po_id_fkey'),
    sa.ForeignKeyConstraint(['raised_by'], ['login_users.user_id'], name='purchase_request_raised_by_fkey'),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], name='purchase_request_rm_id_fkey'),
    sa.PrimaryKeyConstraint('pr_id', name='purchase_request_pkey'),
    sa.UniqueConstraint('pr_number', name='purchase_request_pr_number_key')
    )
    op.drop_table('sales_vehicle_template_feature')
    op.drop_table('dealer_client_map')
    op.drop_table('sales_vehicle_template')
    op.drop_table('client_master')
    # ### end Alembic commands ###