"""add photo_urls to dealer_request

Revision ID: 44625108aa55
Revises: 5126e3302755
Create Date: 2026-08-31 15:29:13.005120

"""
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 = '44625108aa55'
down_revision: Union[str, None] = '5126e3302755'
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.execute("ALTER TABLE dealer_request ADD COLUMN IF NOT EXISTS photo_urls JSONB")
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_foreign_key('fk_chassis_build_spec', 'chassis_master', 'build_spec', ['build_spec_id'], ['spec_id'])
    op.create_foreign_key('fk_bo_build_spec', 'build_order', 'build_spec', ['build_spec_id'], ['spec_id'])
    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'),
    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('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('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_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('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('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_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_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')
    )
    # ### end Alembic commands ###