"""add missing columns after prd restore

Revision ID: patch_missing_cols_prd
Revises: cc429124e9fc
Create Date: 2026-09-17

Adds 5 columns present in SQLAlchemy models but missing from the prd.sql dump.
Uses IF NOT EXISTS so it is safe to run on both:
  - local DB (columns already added manually)
  - server DB (columns don't exist yet)
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'patch_missing_cols_prd'
down_revision: Union[str, None] = 'cc429124e9fc'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


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

    # 1. maintenance_incoming_material → bill_photo_url
    conn.execute(sa.text(
        "ALTER TABLE maintenance_incoming_material "
        "ADD COLUMN IF NOT EXISTS bill_photo_url VARCHAR(500)"
    ))

    # 2. maintenance_record → photo_url
    conn.execute(sa.text(
        "ALTER TABLE maintenance_record "
        "ADD COLUMN IF NOT EXISTS photo_url VARCHAR(500)"
    ))

    # 3. special_feature_bom → body_type_id, model_id, segment_id
    conn.execute(sa.text(
        "ALTER TABLE special_feature_bom ADD COLUMN IF NOT EXISTS body_type_id UUID"
    ))
    conn.execute(sa.text(
        "ALTER TABLE special_feature_bom ADD COLUMN IF NOT EXISTS model_id UUID"
    ))
    conn.execute(sa.text(
        "ALTER TABLE special_feature_bom ADD COLUMN IF NOT EXISTS segment_id UUID"
    ))

    # Foreign keys — only add if not already present
    conn.execute(sa.text("""
        DO $$ BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_sfbom_body_type') THEN
                ALTER TABLE special_feature_bom
                    ADD CONSTRAINT fk_sfbom_body_type
                    FOREIGN KEY (body_type_id) REFERENCES body_type_master(body_type_id);
            END IF;
        END $$;
    """))
    conn.execute(sa.text("""
        DO $$ BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_sfbom_model') THEN
                ALTER TABLE special_feature_bom
                    ADD CONSTRAINT fk_sfbom_model
                    FOREIGN KEY (model_id) REFERENCES vehicle_model_master(model_id);
            END IF;
        END $$;
    """))
    conn.execute(sa.text("""
        DO $$ BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_sfbom_segment') THEN
                ALTER TABLE special_feature_bom
                    ADD CONSTRAINT fk_sfbom_segment
                    FOREIGN KEY (segment_id) REFERENCES customer_segment_master(segment_id);
            END IF;
        END $$;
    """))


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text(
        "ALTER TABLE special_feature_bom DROP CONSTRAINT IF EXISTS fk_sfbom_segment"
    ))
    conn.execute(sa.text(
        "ALTER TABLE special_feature_bom DROP CONSTRAINT IF EXISTS fk_sfbom_model"
    ))
    conn.execute(sa.text(
        "ALTER TABLE special_feature_bom DROP CONSTRAINT IF EXISTS fk_sfbom_body_type"
    ))
    op.drop_column('special_feature_bom', 'segment_id')
    op.drop_column('special_feature_bom', 'model_id')
    op.drop_column('special_feature_bom', 'body_type_id')
    op.drop_column('maintenance_record', 'photo_url')
    op.drop_column('maintenance_incoming_material', 'bill_photo_url')