"""Add rm_purchase_request tables

Revision ID: 2402d6b8c857
Revises: f737935c94b9
Create Date: 2026-09-19 09:48:27.692589

"""
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 = '2402d6b8c857'
down_revision: Union[str, None] = 'f737935c94b9'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Induction scheduling columns: present in databases built before this chain was
    # complete, created here for databases built from migrations alone.
    op.execute("ALTER TABLE chassis_master ADD COLUMN IF NOT EXISTS scheduled_induction_date DATE")
    op.execute("ALTER TABLE chassis_master ADD COLUMN IF NOT EXISTS scheduled_by UUID")
    op.execute("ALTER TABLE chassis_master ADD COLUMN IF NOT EXISTS scheduled_at TIMESTAMP WITH TIME ZONE")
    op.create_foreign_key('chassis_master_scheduled_by_fkey', 'chassis_master', 'login_users', ['scheduled_by'], ['user_id'])
    # Guard against column already existing (may have been added manually or by a prior migration)
    conn = op.get_bind()
    col_exists = conn.execute(sa.text(
        "SELECT 1 FROM information_schema.columns "
        "WHERE table_name='maintenance_record' AND column_name='photo_url'"
    )).fetchone()
    if not col_exists:
        op.add_column('maintenance_record', sa.Column('photo_url', sa.String(length=500), nullable=True))
    has_unique = conn.execute(sa.text(
        "SELECT 1 FROM pg_constraint WHERE conname = 'uq_feature_option_rm_context'"
    )).fetchone()
    if not has_unique:
        op.create_unique_constraint('uq_feature_option_rm_context', 'special_feature_bom', ['option_id', 'rm_id'])
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_unique_constraint('uq_feature_option_rm', 'special_feature_bom', ['option_id', 'rm_id'])
    op.create_foreign_key('service_order_order_id_fkey', 'service_order', 'build_order', ['order_id'], ['order_id'])
    op.create_foreign_key('payment_receipt_order_id_fkey', 'payment_receipt', 'build_order', ['order_id'], ['order_id'])
    op.create_foreign_key('dealer_order_build_order_id_fkey', 'dealer_order', 'build_order', ['build_order_id'], ['order_id'])
    op.create_foreign_key('da_lite_hold_log_order_id_fkey', 'da_lite_hold_log', 'build_order', ['order_id'], ['order_id'])
    op.create_foreign_key('customer_payment_log_order_id_fkey', 'customer_payment_log', 'build_order', ['order_id'], ['order_id'])
    op.create_foreign_key('chassis_master_build_order_id_fkey', 'chassis_master', 'build_order', ['build_order_id'], ['order_id'])
    op.create_table('dispatch_thermal_checksheet_item',
    sa.Column('item_result_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('checksheet_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('item_no', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('item_text', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('is_ok', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=False),
    sa.Column('photo_url', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('authority_name', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
    sa.Column('fault_type', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('remarks', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('mail_screenshot_url', sa.TEXT(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['checksheet_id'], ['dispatch_thermal_checksheet.checksheet_id'], name='dispatch_thermal_checksheet_item_checksheet_id_fkey'),
    sa.PrimaryKeyConstraint('item_result_id', name='dispatch_thermal_checksheet_item_pkey')
    )
    op.create_table('qa_severity_master',
    sa.Column('severity_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('code', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('name', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('sequence_no', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('severity_id', name='qa_severity_master_pkey'),
    sa.UniqueConstraint('code', name='qa_severity_master_code_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('build_order',
    sa.Column('order_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('order_number', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('quote_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('customer_id', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('chassis_no', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('chassis_model', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('body_type', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('bus_use', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('pre_production_status', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('da_lite_status', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('chassis_receive_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('induction_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('dispatch_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('contractor_group', sa.VARCHAR(length=10), autoincrement=False, nullable=True),
    sa.Column('ac_communication', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('driver_request_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('seat_fitting_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('document_pending', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('pdi_inspector', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('yard_location', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('assigned_to_name', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('remarks', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('balance_due', sa.DOUBLE_PRECISION(precision=53), server_default=sa.text("'0'::double precision"), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('receiving_transport_id', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('dispatch_transport_id', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('payment_status', sa.VARCHAR(length=20), server_default=sa.text("'PENDING'::character varying"), autoincrement=False, nullable=False),
    sa.Column('build_spec_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('procurement_status', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('procurement_reviewed_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('procurement_reviewed_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('procurement_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('production_status', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('production_reviewed_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('production_reviewed_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('production_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('target_start_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('estimated_completion_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('sales_released_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('sales_released_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('sales_release_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('total_bom_lines', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('total_shortage_lines', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('total_rm_value', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('bom_exploded_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.Column('priority', sa.VARCHAR(length=10), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['customer_id'], ['customer_master.customer_id'], name='build_order_customer_id_fkey'),
    sa.ForeignKeyConstraint(['dispatch_transport_id'], ['transport_master.transport_id'], name='build_order_dispatch_transport_id_fkey'),
    sa.ForeignKeyConstraint(['procurement_reviewed_by'], ['login_users.user_id'], name='build_order_procurement_reviewed_by_fkey'),
    sa.ForeignKeyConstraint(['production_reviewed_by'], ['login_users.user_id'], name='build_order_production_reviewed_by_fkey'),
    sa.ForeignKeyConstraint(['quote_id'], ['quote_header.quote_id'], name='build_order_quote_id_fkey'),
    sa.ForeignKeyConstraint(['receiving_transport_id'], ['transport_master.transport_id'], name='build_order_receiving_transport_id_fkey'),
    sa.ForeignKeyConstraint(['sales_released_by'], ['login_users.user_id'], name='build_order_sales_released_by_fkey'),
    sa.PrimaryKeyConstraint('order_id', name='build_order_pkey'),
    sa.UniqueConstraint('order_number', name='build_order_order_number_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('qa_checksheet_instance',
    sa.Column('instance_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('template_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('model_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('chassis_no', sa.VARCHAR(length=30), autoincrement=False, nullable=False),
    sa.Column('face', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('inspection_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('special_features_text', sa.TEXT(), autoincrement=False, nullable=True),
    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(['model_id'], ['qa_bus_model_master.model_id'], name='qa_checksheet_instance_model_id_fkey'),
    sa.ForeignKeyConstraint(['template_id'], ['qa_checksheet_template.template_id'], name='qa_checksheet_instance_template_id_fkey'),
    sa.PrimaryKeyConstraint('instance_id', name='qa_checksheet_instance_pkey'),
    sa.UniqueConstraint('template_id', 'chassis_no', name='uq_qa_instance_template_chassis'),
    postgresql_ignore_search_path=False
    )
    op.create_table('dispatch_gate_pass',
    sa.Column('gate_pass_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('checksheet_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('chassis_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('chassis_no', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('generated_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.Column('fuel_before_ltr', sa.NUMERIC(precision=10, scale=2), autoincrement=False, nullable=True),
    sa.Column('filling_fuel_qty_ltr', sa.NUMERIC(precision=10, scale=2), autoincrement=False, nullable=True),
    sa.Column('driver_name', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
    sa.Column('driver_photo_url', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('dispatch_authority', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
    sa.Column('created_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), server_default=sa.text('now()'), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['chassis_id'], ['chassis_master.chassis_id'], name='dispatch_gate_pass_chassis_id_fkey'),
    sa.ForeignKeyConstraint(['checksheet_id'], ['dispatch_thermal_checksheet.checksheet_id'], name='dispatch_gate_pass_checksheet_id_fkey'),
    sa.ForeignKeyConstraint(['created_by'], ['login_users.user_id'], name='dispatch_gate_pass_created_by_fkey'),
    sa.PrimaryKeyConstraint('gate_pass_id', name='dispatch_gate_pass_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('qa_checkpoint_spec_variant',
    sa.Column('variant_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('item_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('model_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('parameter_name', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('spec_value', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('nominal', sa.NUMERIC(precision=10, scale=2), autoincrement=False, nullable=True),
    sa.Column('tolerance_plus', sa.NUMERIC(precision=10, scale=2), autoincrement=False, nullable=True),
    sa.Column('tolerance_minus', sa.NUMERIC(precision=10, scale=2), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['item_id'], ['qa_checkpoint_item.item_id'], name='qa_checkpoint_spec_variant_item_id_fkey', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['model_id'], ['qa_bus_model_master.model_id'], name='qa_checkpoint_spec_variant_model_id_fkey'),
    sa.PrimaryKeyConstraint('variant_id', name='qa_checkpoint_spec_variant_pkey')
    )
    op.create_table('pre_production_hold',
    sa.Column('hold_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('order_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('blocker_type', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('description', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('resolved_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('resolved_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('created_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['order_id'], ['build_order.order_id'], name='pre_production_hold_order_id_fkey'),
    sa.PrimaryKeyConstraint('hold_id', name='pre_production_hold_pkey')
    )
    op.create_table('dispatch_thermal_checksheet',
    sa.Column('checksheet_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('chassis_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('chassis_no', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('model', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('engine_type', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('check_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('signature', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
    sa.Column('photo_front', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('photo_rear', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('photo_rh', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('photo_lh', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('all_ok', sa.BOOLEAN(), server_default=sa.text('false'), autoincrement=False, nullable=False),
    sa.Column('submitted_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('submitted_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(['chassis_id'], ['chassis_master.chassis_id'], name='dispatch_thermal_checksheet_chassis_id_fkey'),
    sa.ForeignKeyConstraint(['submitted_by'], ['login_users.user_id'], name='dispatch_thermal_checksheet_submitted_by_fkey'),
    sa.PrimaryKeyConstraint('checksheet_id', name='dispatch_thermal_checksheet_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('quote_line_item',
    sa.Column('line_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('quote_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('item_type', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('description', sa.VARCHAR(length=200), autoincrement=False, nullable=False),
    sa.Column('quantity', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('unit_price', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('line_total', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['quote_id'], ['quote_header.quote_id'], name='quote_line_item_quote_id_fkey'),
    sa.PrimaryKeyConstraint('line_id', name='quote_line_item_pkey')
    )
    op.create_table('quote_header',
    sa.Column('quote_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('enquiry_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('customer_id', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('valid_until', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('status', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('created_by', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('updated_at', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
    sa.Column('total_base_amount', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('total_taxes', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('final_amount', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('approved_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('revision_number', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('quote_number', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('quote_type', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('quote_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('chassis_model', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('body_type', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('bus_use', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('bom_class', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('ac_communication', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('driver_request_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('remarks', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('seat_fitting_notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('receiving_transport_id', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('dispatch_transport_id', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('base_price', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('feature_price', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('subtotal', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('discount_pct', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('discount_amount', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('discount_reason', sa.VARCHAR(length=200), autoincrement=False, nullable=True),
    sa.Column('gst_pct', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('gst_amount', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('total_amount', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('is_goodwill', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('notes', sa.TEXT(), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['approved_by'], ['login_users.user_id'], name='quote_header_approved_by_fkey'),
    sa.ForeignKeyConstraint(['created_by'], ['login_users.user_id'], name='quote_header_created_by_fkey'),
    sa.ForeignKeyConstraint(['customer_id'], ['customer_master.customer_id'], name='quote_header_customer_id_fkey'),
    sa.ForeignKeyConstraint(['dispatch_transport_id'], ['transport_master.transport_id'], name='quote_header_dispatch_transport_id_fkey'),
    sa.ForeignKeyConstraint(['enquiry_id'], ['sales_enquiry.enquiry_id'], name='quote_header_enquiry_id_fkey'),
    sa.ForeignKeyConstraint(['receiving_transport_id'], ['transport_master.transport_id'], name='quote_header_receiving_transport_id_fkey'),
    sa.PrimaryKeyConstraint('quote_id', name='quote_header_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('quote_configuration',
    sa.Column('config_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('quote_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('feature_category', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('feature_code', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('feature_value', sa.VARCHAR(length=200), autoincrement=False, nullable=True),
    sa.Column('feature_label', sa.VARCHAR(length=200), autoincrement=False, nullable=True),
    sa.Column('is_mandatory', sa.BOOLEAN(), autoincrement=False, nullable=True),
    sa.Column('price_impact', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['quote_id'], ['quote_header.quote_id'], name='quote_configuration_quote_id_fkey'),
    sa.PrimaryKeyConstraint('config_id', name='quote_configuration_pkey')
    )
    op.create_table('qa_checksheet_signoff',
    sa.Column('signoff_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('instance_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('signoff_type', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('signed_by_user_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('signed_by_name', sa.VARCHAR(length=255), autoincrement=False, nullable=False),
    sa.Column('remarks', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('signed_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['instance_id'], ['qa_checksheet_instance.instance_id'], name='qa_checksheet_signoff_instance_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('signoff_id', name='qa_checksheet_signoff_pkey')
    )
    op.create_table('qa_checksheet_section',
    sa.Column('section_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('template_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('phase_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('section_code', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('section_name', sa.VARCHAR(length=255), autoincrement=False, nullable=False),
    sa.Column('section_number', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['phase_id'], ['qa_sub_phase_master.phase_id'], name='qa_checksheet_section_phase_id_fkey'),
    sa.ForeignKeyConstraint(['template_id'], ['qa_checksheet_template.template_id'], name='qa_checksheet_section_template_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('section_id', name='qa_checksheet_section_pkey'),
    postgresql_ignore_search_path=False
    )
    op.create_table('qa_checksheet_template',
    sa.Column('template_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('template_code', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('template_name', sa.VARCHAR(length=255), autoincrement=False, nullable=False),
    sa.Column('station_code', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('format_no', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('revision', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('revision_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('has_paint_history', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('has_frequency_col', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), 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.Column('updated_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.PrimaryKeyConstraint('template_id', name='qa_checksheet_template_pkey'),
    sa.UniqueConstraint('template_code', name='qa_checksheet_template_template_code_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('dispatch_gate_pass_item',
    sa.Column('gate_pass_item_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('gate_pass_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('item_key', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('item_label', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('is_checked', sa.BOOLEAN(), server_default=sa.text('true'), autoincrement=False, nullable=False),
    sa.Column('qty', sa.INTEGER(), server_default=sa.text('1'), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['gate_pass_id'], ['dispatch_gate_pass.gate_pass_id'], name='dispatch_gate_pass_item_gate_pass_id_fkey'),
    sa.PrimaryKeyConstraint('gate_pass_item_id', name='dispatch_gate_pass_item_pkey')
    )
    op.create_table('qa_sub_phase_master',
    sa.Column('phase_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('code', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('name', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('phase_id', name='qa_sub_phase_master_pkey'),
    sa.UniqueConstraint('code', name='qa_sub_phase_master_code_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('qa_inspection_method_master',
    sa.Column('method_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('code', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('name', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('method_id', name='qa_inspection_method_master_pkey'),
    sa.UniqueConstraint('code', name='qa_inspection_method_master_code_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('quote_bom_snapshot',
    sa.Column('quote_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('rm_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('station_code', sa.VARCHAR(length=30), autoincrement=False, nullable=True),
    sa.Column('station_label', sa.VARCHAR(length=100), autoincrement=False, nullable=True),
    sa.Column('production_day', sa.INTEGER(), autoincrement=False, nullable=True),
    sa.Column('required_qty', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('unit_price', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('line_amount', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=True),
    sa.Column('line_source', sa.VARCHAR(length=10), autoincrement=False, nullable=False),
    sa.Column('feature_code', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('snapshot_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['quote_id'], ['quote_header.quote_id'], name='quote_bom_snapshot_quote_id_fkey'),
    sa.ForeignKeyConstraint(['rm_id'], ['rm_master.rm_id'], name='quote_bom_snapshot_rm_id_fkey')
    )
    op.create_table('qa_template_model_map',
    sa.Column('template_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('model_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['model_id'], ['qa_bus_model_master.model_id'], name='qa_template_model_map_model_id_fkey', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['template_id'], ['qa_checksheet_template.template_id'], name='qa_template_model_map_template_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('template_id', 'model_id', name='qa_template_model_map_pkey')
    )
    op.create_table('qa_checkpoint_response',
    sa.Column('response_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('instance_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('item_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('status', sa.VARCHAR(length=10), autoincrement=False, nullable=True),
    sa.Column('measured_value', sa.NUMERIC(precision=10, scale=2), autoincrement=False, nullable=True),
    sa.Column('observation', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('after_rework_status', sa.VARCHAR(length=10), autoincrement=False, nullable=True),
    sa.Column('after_rework_remarks', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('responded_by', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('responded_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['instance_id'], ['qa_checksheet_instance.instance_id'], name='qa_checkpoint_response_instance_id_fkey', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['item_id'], ['qa_checkpoint_item.item_id'], name='qa_checkpoint_response_item_id_fkey'),
    sa.PrimaryKeyConstraint('response_id', name='qa_checkpoint_response_pkey')
    )
    op.create_table('qa_bus_model_master',
    sa.Column('model_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('code', sa.VARCHAR(length=50), autoincrement=False, nullable=False),
    sa.Column('name', sa.VARCHAR(length=100), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.PrimaryKeyConstraint('model_id', name='qa_bus_model_master_pkey'),
    sa.UniqueConstraint('code', name='qa_bus_model_master_code_key'),
    postgresql_ignore_search_path=False
    )
    op.create_table('production_report_manual_entry',
    sa.Column('id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('chassis_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('contractor_group', sa.VARCHAR(length=10), autoincrement=False, nullable=True),
    sa.Column('ac_make', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('interior_work', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('driver_request', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('document_pending', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('seat_fitting_pending', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('remark', sa.TEXT(), autoincrement=False, nullable=True),
    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(['chassis_id'], ['chassis_master.chassis_id'], name='production_report_manual_entry_chassis_id_fkey'),
    sa.PrimaryKeyConstraint('id', name='production_report_manual_entry_pkey'),
    sa.UniqueConstraint('chassis_id', name='production_report_manual_entry_chassis_id_key')
    )
    op.create_table('qa_checkpoint_item',
    sa.Column('item_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('section_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('severity_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('method_id', sa.UUID(), autoincrement=False, nullable=True),
    sa.Column('item_number', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('description', sa.TEXT(), autoincrement=False, nullable=False),
    sa.Column('frequency', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('has_measurement', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('measurement_unit', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('is_special_feature_slot', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('sequence_no', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('is_active', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['method_id'], ['qa_inspection_method_master.method_id'], name='qa_checkpoint_item_method_id_fkey'),
    sa.ForeignKeyConstraint(['section_id'], ['qa_checksheet_section.section_id'], name='qa_checkpoint_item_section_id_fkey', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['severity_id'], ['qa_severity_master.severity_id'], name='qa_checkpoint_item_severity_id_fkey'),
    sa.PrimaryKeyConstraint('item_id', name='qa_checkpoint_item_pkey')
    )
    op.create_table('qa_paint_history_entry',
    sa.Column('entry_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('instance_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('history_type', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('painting_date', sa.DATE(), autoincrement=False, nullable=True),
    sa.Column('painting_shift', sa.VARCHAR(length=20), autoincrement=False, nullable=True),
    sa.Column('area_position', sa.VARCHAR(length=50), autoincrement=False, nullable=True),
    sa.Column('painter_name', sa.VARCHAR(length=255), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['instance_id'], ['qa_checksheet_instance.instance_id'], name='qa_paint_history_entry_instance_id_fkey', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('entry_id', name='qa_paint_history_entry_pkey')
    )
    # ### end Alembic commands ###