"""add_qa_checksheet_tables

Revision ID: 89ef666cb452
Revises: 89b741b581e2
Create Date: 2026-08-19 14:31:25.301382

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '89ef666cb452'
down_revision: Union[str, None] = '89b741b581e2'
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! ###
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    existing_tables = set(inspector.get_table_names())

    if 'qa_bus_model_master' not in existing_tables:
        op.create_table('qa_bus_model_master',
        sa.Column('model_id', sa.UUID(), nullable=False),
        sa.Column('code', sa.String(length=50), nullable=False),
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('model_id'),
        sa.UniqueConstraint('code')
        )
    if 'qa_checksheet_template' not in existing_tables:
        op.create_table('qa_checksheet_template',
        sa.Column('template_id', sa.UUID(), nullable=False),
        sa.Column('template_code', sa.String(length=50), nullable=False),
        sa.Column('template_name', sa.String(length=255), nullable=False),
        sa.Column('station_code', sa.String(length=50), nullable=True),
        sa.Column('format_no', sa.String(length=20), nullable=True),
        sa.Column('revision', sa.String(length=20), nullable=True),
        sa.Column('revision_date', sa.Date(), nullable=True),
        sa.Column('has_paint_history', sa.Boolean(), nullable=False),
        sa.Column('has_frequency_col', sa.Boolean(), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_by', sa.UUID(), nullable=True),
        sa.PrimaryKeyConstraint('template_id'),
        sa.UniqueConstraint('template_code')
        )
    if 'qa_inspection_method_master' not in existing_tables:
        op.create_table('qa_inspection_method_master',
        sa.Column('method_id', sa.UUID(), nullable=False),
        sa.Column('code', sa.String(length=50), nullable=False),
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('method_id'),
        sa.UniqueConstraint('code')
        )
    if 'qa_severity_master' not in existing_tables:
        op.create_table('qa_severity_master',
        sa.Column('severity_id', sa.UUID(), nullable=False),
        sa.Column('code', sa.String(length=20), nullable=False),
        sa.Column('name', sa.String(length=50), nullable=False),
        sa.Column('sequence_no', sa.Integer(), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('severity_id'),
        sa.UniqueConstraint('code')
        )
    if 'qa_sub_phase_master' not in existing_tables:
        op.create_table('qa_sub_phase_master',
        sa.Column('phase_id', sa.UUID(), nullable=False),
        sa.Column('code', sa.String(length=50), nullable=False),
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.PrimaryKeyConstraint('phase_id'),
        sa.UniqueConstraint('code')
        )
    if 'qa_checksheet_instance' not in existing_tables:
        op.create_table('qa_checksheet_instance',
        sa.Column('instance_id', sa.UUID(), nullable=False),
        sa.Column('template_id', sa.UUID(), nullable=False),
        sa.Column('model_id', sa.UUID(), nullable=True),
        sa.Column('chassis_no', sa.String(length=30), nullable=False),
        sa.Column('face', sa.String(length=50), nullable=True),
        sa.Column('inspection_date', sa.Date(), nullable=True),
        sa.Column('status', sa.String(length=20), nullable=False),
        sa.Column('special_features_text', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(['model_id'], ['qa_bus_model_master.model_id'], ),
        sa.ForeignKeyConstraint(['template_id'], ['qa_checksheet_template.template_id'], ),
        sa.PrimaryKeyConstraint('instance_id'),
        sa.UniqueConstraint('template_id', 'chassis_no', name='uq_qa_instance_template_chassis')
        )
    if 'qa_checksheet_section' not in existing_tables:
        op.create_table('qa_checksheet_section',
        sa.Column('section_id', sa.UUID(), nullable=False),
        sa.Column('template_id', sa.UUID(), nullable=False),
        sa.Column('phase_id', sa.UUID(), nullable=True),
        sa.Column('section_code', sa.String(length=50), nullable=True),
        sa.Column('section_name', sa.String(length=255), nullable=False),
        sa.Column('section_number', sa.Integer(), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(['phase_id'], ['qa_sub_phase_master.phase_id'], ),
        sa.ForeignKeyConstraint(['template_id'], ['qa_checksheet_template.template_id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('section_id')
        )
    if 'qa_template_model_map' not in existing_tables:
        op.create_table('qa_template_model_map',
        sa.Column('template_id', sa.UUID(), nullable=False),
        sa.Column('model_id', sa.UUID(), nullable=False),
        sa.ForeignKeyConstraint(['model_id'], ['qa_bus_model_master.model_id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['template_id'], ['qa_checksheet_template.template_id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('template_id', 'model_id')
        )
    if 'qa_checkpoint_item' not in existing_tables:
        op.create_table('qa_checkpoint_item',
        sa.Column('item_id', sa.UUID(), nullable=False),
        sa.Column('section_id', sa.UUID(), nullable=False),
        sa.Column('severity_id', sa.UUID(), nullable=True),
        sa.Column('method_id', sa.UUID(), nullable=True),
        sa.Column('item_number', sa.Integer(), nullable=False),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('frequency', sa.String(length=50), nullable=True),
        sa.Column('has_measurement', sa.Boolean(), nullable=False),
        sa.Column('measurement_unit', sa.String(length=50), nullable=True),
        sa.Column('is_special_feature_slot', sa.Boolean(), nullable=False),
        sa.Column('sequence_no', sa.Integer(), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(['method_id'], ['qa_inspection_method_master.method_id'], ),
        sa.ForeignKeyConstraint(['section_id'], ['qa_checksheet_section.section_id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['severity_id'], ['qa_severity_master.severity_id'], ),
        sa.PrimaryKeyConstraint('item_id')
        )
    if 'qa_checksheet_signoff' not in existing_tables:
        op.create_table('qa_checksheet_signoff',
        sa.Column('signoff_id', sa.UUID(), nullable=False),
        sa.Column('instance_id', sa.UUID(), nullable=False),
        sa.Column('signoff_type', sa.String(length=20), nullable=False),
        sa.Column('signed_by_user_id', sa.UUID(), nullable=True),
        sa.Column('signed_by_name', sa.String(length=255), nullable=False),
        sa.Column('remarks', sa.Text(), nullable=True),
        sa.Column('signed_at', sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(['instance_id'], ['qa_checksheet_instance.instance_id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('signoff_id')
        )
    if 'qa_paint_history_entry' not in existing_tables:
        op.create_table('qa_paint_history_entry',
        sa.Column('entry_id', sa.UUID(), nullable=False),
        sa.Column('instance_id', sa.UUID(), nullable=False),
        sa.Column('history_type', sa.String(length=20), nullable=False),
        sa.Column('painting_date', sa.Date(), nullable=True),
        sa.Column('painting_shift', sa.String(length=20), nullable=True),
        sa.Column('area_position', sa.String(length=50), nullable=True),
        sa.Column('painter_name', sa.String(length=255), nullable=True),
        sa.ForeignKeyConstraint(['instance_id'], ['qa_checksheet_instance.instance_id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('entry_id')
        )
    if 'qa_checkpoint_response' not in existing_tables:
        op.create_table('qa_checkpoint_response',
        sa.Column('response_id', sa.UUID(), nullable=False),
        sa.Column('instance_id', sa.UUID(), nullable=False),
        sa.Column('item_id', sa.UUID(), nullable=False),
        sa.Column('status', sa.String(length=10), nullable=True),
        sa.Column('measured_value', sa.Numeric(precision=10, scale=2), nullable=True),
        sa.Column('observation', sa.Text(), nullable=True),
        sa.Column('after_rework_status', sa.String(length=10), nullable=True),
        sa.Column('after_rework_remarks', sa.Text(), nullable=True),
        sa.Column('responded_by', sa.UUID(), nullable=True),
        sa.Column('responded_at', sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(['instance_id'], ['qa_checksheet_instance.instance_id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['item_id'], ['qa_checkpoint_item.item_id'], ),
        sa.PrimaryKeyConstraint('response_id')
        )
    if 'qa_checkpoint_spec_variant' not in existing_tables:
        op.create_table('qa_checkpoint_spec_variant',
        sa.Column('variant_id', sa.UUID(), nullable=False),
        sa.Column('item_id', sa.UUID(), nullable=False),
        sa.Column('model_id', sa.UUID(), nullable=False),
        sa.Column('parameter_name', sa.String(length=100), nullable=False),
        sa.Column('spec_value', sa.String(length=100), nullable=False),
        sa.Column('nominal', sa.Numeric(precision=10, scale=2), nullable=True),
        sa.Column('tolerance_plus', sa.Numeric(precision=10, scale=2), nullable=True),
        sa.Column('tolerance_minus', sa.Numeric(precision=10, scale=2), nullable=True),
        sa.ForeignKeyConstraint(['item_id'], ['qa_checkpoint_item.item_id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['model_id'], ['qa_bus_model_master.model_id'], ),
        sa.PrimaryKeyConstraint('variant_id')
        )
    # op.add_column('seating_layout_master', sa.Column('layout_no', sa.String(length=100), nullable=False))
    # op.add_column('seating_layout_master', sa.Column('chassis_model', sa.String(length=100), nullable=False))
    # op.add_column('seating_layout_master', sa.Column('body_type', sa.String(length=100), nullable=False))
    # op.add_column('seating_layout_master', sa.Column('segment', sa.String(length=100), nullable=False))
    # op.add_column('seating_layout_master', sa.Column('seating_capacity', sa.String(length=100), nullable=False))
    # op.add_column('seating_layout_master', sa.Column('image_url', sa.String(length=500), nullable=True))
    # op.add_column('seating_layout_master', sa.Column('pdf_url', sa.String(length=500), nullable=True))
# #     op.add_column('seating_layout_master', sa.Column('layout_no', sa.String(length=100), nullable=False))
#     op.add_column('seating_layout_master', sa.Column('chassis_model', sa.String(length=100), nullable=False))
#     op.add_column('seating_layout_master', sa.Column('body_type', sa.String(length=100), nullable=False))
#     op.add_column('seating_layout_master', sa.Column('segment', sa.String(length=100), nullable=False))
#     op.add_column('seating_layout_master', sa.Column('seating_capacity', sa.String(length=100), nullable=False))
#     op.add_column('seating_layout_master', sa.Column('image_url', sa.String(length=500), nullable=True))
#     op.add_column('seating_layout_master', sa.Column('pdf_url', sa.String(length=500), nullable=True))
    op.alter_column('seating_layout_master', 'door_type',
               existing_type=sa.VARCHAR(length=50),
               type_=sa.String(length=100),
               nullable=False)
    op.alter_column('seating_layout_master', 'seat_type',
               existing_type=sa.VARCHAR(length=50),
               type_=sa.String(length=100),
               nullable=False)
    op.alter_column('seating_layout_master', 'seating_type',
               existing_type=sa.VARCHAR(length=20),
               type_=sa.String(length=100),
               nullable=False)
    # op.drop_index(op.f('ix_seating_layout_lookup'), table_name='seating_layout_master')
    op.create_unique_constraint(None, 'seating_layout_master', ['layout_no'])
    op.execute("ALTER TABLE seating_layout_master DROP CONSTRAINT IF EXISTS seating_layout_master_segment_id_fkey")
    op.execute("ALTER TABLE seating_layout_master DROP CONSTRAINT IF EXISTS seating_layout_master_model_id_fkey")
    op.execute("ALTER TABLE seating_layout_master DROP CONSTRAINT IF EXISTS seating_layout_master_body_type_id_fkey")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS body_type_id")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS seat_capacity")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS layout_number")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS segment_id")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS is_active")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS model_id")
    op.execute("ALTER TABLE seating_layout_master DROP COLUMN IF EXISTS pdf_path")
    # op.drop_constraint(op.f('seating_layout_master_segment_id_fkey'), 'seating_layout_master', type_='foreignkey')
    # op.drop_constraint(op.f('seating_layout_master_model_id_fkey'), 'seating_layout_master', type_='foreignkey')
    # op.drop_constraint(op.f('seating_layout_master_body_type_id_fkey'), 'seating_layout_master', type_='foreignkey')
#     # op.drop_column('seating_layout_master', 'body_type_id')
#     op.drop_column('seating_layout_master', 'seat_capacity')
#     op.drop_column('seating_layout_master', 'layout_number')
#     op.drop_column('seating_layout_master', 'segment_id')
#     op.drop_column('seating_layout_master', 'is_active')
#     op.drop_column('seating_layout_master', 'model_id')
#     op.drop_column('seating_layout_master', 'pdf_path')
    # op.alter_column('vehicle_model_master', 'model_number',
    #            existing_type=sa.VARCHAR(length=100),
    #            type_=sa.String(length=20),
    #            existing_nullable=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('vehicle_model_master', 'model_number',
               existing_type=sa.String(length=20),
               type_=sa.VARCHAR(length=100),
               existing_nullable=False)
#     op.add_column('seating_layout_master', sa.Column('pdf_path', sa.VARCHAR(length=500), autoincrement=False, nullable=True))
#     op.add_column('seating_layout_master', sa.Column('model_id', sa.UUID(), autoincrement=False, nullable=False))
#     op.add_column('seating_layout_master', sa.Column('is_active', sa.BOOLEAN(), server_default=sa.text('true'), autoincrement=False, nullable=False))
#     op.add_column('seating_layout_master', sa.Column('segment_id', sa.UUID(), autoincrement=False, nullable=False))
#     op.add_column('seating_layout_master', sa.Column('layout_number', sa.VARCHAR(length=50), autoincrement=False, nullable=False))
#     op.add_column('seating_layout_master', sa.Column('seat_capacity', sa.VARCHAR(length=50), autoincrement=False, nullable=True))
#     op.add_column('seating_layout_master', sa.Column('body_type_id', sa.UUID(), autoincrement=False, nullable=False))
    op.create_foreign_key(op.f('seating_layout_master_body_type_id_fkey'), 'seating_layout_master', 'body_type_master', ['body_type_id'], ['body_type_id'])
    op.create_foreign_key(op.f('seating_layout_master_model_id_fkey'), 'seating_layout_master', 'vehicle_model_master', ['model_id'], ['model_id'])
    op.create_foreign_key(op.f('seating_layout_master_segment_id_fkey'), 'seating_layout_master', 'customer_segment_master', ['segment_id'], ['segment_id'])
    op.drop_constraint(None, 'seating_layout_master', type_='unique')
    op.create_index(op.f('ix_seating_layout_lookup'), 'seating_layout_master', ['model_id', 'body_type_id', 'segment_id', 'door_type'], unique=False)
    op.alter_column('seating_layout_master', 'seating_type',
               existing_type=sa.String(length=100),
               type_=sa.VARCHAR(length=20),
               nullable=True)
    op.alter_column('seating_layout_master', 'seat_type',
               existing_type=sa.String(length=100),
               type_=sa.VARCHAR(length=50),
               nullable=True)
    op.alter_column('seating_layout_master', 'door_type',
               existing_type=sa.String(length=100),
               type_=sa.VARCHAR(length=50),
               nullable=True)
#     op.drop_column('seating_layout_master', 'pdf_url')
#     op.drop_column('seating_layout_master', 'image_url')
#     op.drop_column('seating_layout_master', 'seating_capacity')
#     op.drop_column('seating_layout_master', 'segment')
#     op.drop_column('seating_layout_master', 'body_type')
#     op.drop_column('seating_layout_master', 'chassis_model')
#     op.drop_column('seating_layout_master', 'layout_no')
    op.drop_table('qa_checkpoint_spec_variant')
    op.drop_table('qa_checkpoint_response')
    op.drop_table('qa_paint_history_entry')
    op.drop_table('qa_checksheet_signoff')
    op.drop_table('qa_checkpoint_item')
    op.drop_table('qa_template_model_map')
    op.drop_table('qa_checksheet_section')
    op.drop_table('qa_checksheet_instance')
    op.drop_table('qa_sub_phase_master')
    op.drop_table('qa_severity_master')
    op.drop_table('qa_inspection_method_master')
    op.drop_table('qa_checksheet_template')
    op.drop_table('qa_bus_model_master')
    # ### end Alembic commands ###