"""Add vendor_id to RmPurchaseRequest

Revision ID: 24a48d613277
Revises: 2402d6b8c857
Create Date: 2026-09-19 13:24:17.192852

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '24a48d613277'
down_revision: Union[str, None] = '2402d6b8c857'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()

    # Create rm_purchase_request if it doesn't exist yet
    # (migration 2402d6b8c857 may not have created it depending on DB state)
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS rm_purchase_request (
            request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            request_number VARCHAR(50) UNIQUE,
            status VARCHAR(30) DEFAULT 'PENDING',
            requested_by UUID REFERENCES login_users(user_id),
            created_at TIMESTAMPTZ DEFAULT now()
        )
    """))

    # Create rm_purchase_request_detail if it doesn't exist yet
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS rm_purchase_request_detail (
            request_detail_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            request_id UUID REFERENCES rm_purchase_request(request_id),
            rm_id UUID REFERENCES rm_master(rm_id),
            shortage_qty NUMERIC(14,3),
            affected_chassis JSONB DEFAULT '[]',
            reason VARCHAR(200)
        )
    """))

    # Add vendor_id column if it doesn't exist
    col_exists = conn.execute(sa.text(
        "SELECT 1 FROM information_schema.columns "
        "WHERE table_name='rm_purchase_request' AND column_name='vendor_id'"
    )).fetchone()
    if not col_exists:
        op.add_column('rm_purchase_request', sa.Column('vendor_id', sa.UUID(), nullable=True))
        op.create_foreign_key(None, 'rm_purchase_request', 'vendor_master', ['vendor_id'], ['vendor_id'])



def downgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    if 'rm_purchase_request' in inspector.get_table_names():
        op.drop_constraint(None, 'rm_purchase_request', type_='foreignkey')
        op.drop_column('rm_purchase_request', 'vendor_id')