"""add_qa_checkpoint_response_created_at_and_missing_cols

Revision ID: c89f1a2b3c4d
Revises: eb65482c06bf
Create Date: 2026-10-19 14:30:00

Ensures created_at and other required QA columns exist on qa_checkpoint_response
and related QA tables in production.
Uses IF NOT EXISTS so it is 100% idempotent and safe.
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = 'c89f1a2b3c4d'
down_revision: Union[str, None] = 'eb65482c06bf'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


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

    # 1. qa_checkpoint_response columns
    conn.execute(sa.text("""
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS sub_point_values JSONB DEFAULT '{}'::jsonb;
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS na_reason TEXT;
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS is_flagged BOOLEAN DEFAULT FALSE;
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS flagged_by UUID REFERENCES login_users(user_id);
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS flagged_at TIMESTAMP WITH TIME ZONE;
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS rework_status VARCHAR(20);
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS after_rework_observation TEXT;
        ALTER TABLE qa_checkpoint_response ADD COLUMN IF NOT EXISTS after_rework_verified_by UUID REFERENCES login_users(user_id);
    """))

    # 2. qa_checkpoint_item columns
    conn.execute(sa.text("""
        ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS area_desc VARCHAR(255);
        ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS model_specs JSONB DEFAULT '{}'::jsonb;
        ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS sub_points JSONB DEFAULT '[]'::jsonb;
        ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS reference_diagram_url VARCHAR(500);
        ALTER TABLE qa_checkpoint_item ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
    """))

    # 3. qa_station_template_map columns
    conn.execute(sa.text("""
        ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS model_id UUID REFERENCES vehicle_model_master(model_id) ON DELETE SET NULL;
        ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS body_type_id UUID REFERENCES body_type_master(body_type_id) ON DELETE SET NULL;
        ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS model_ids JSONB DEFAULT '[]'::jsonb;
        ALTER TABLE qa_station_template_map ADD COLUMN IF NOT EXISTS body_type_ids JSONB DEFAULT '[]'::jsonb;
    """))

    # 4. qa_checksheet_instance columns
    conn.execute(sa.text("""
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS station_qa_status VARCHAR(20) DEFAULT 'PENDING';
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS overall_result VARCHAR(20) DEFAULT 'PENDING';
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS shift VARCHAR(20);
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS remarks TEXT;
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS rework_notes TEXT;
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP WITH TIME ZONE;
        ALTER TABLE qa_checksheet_instance ADD COLUMN IF NOT EXISTS passed_by UUID REFERENCES login_users(user_id);
    """))


def downgrade() -> None:
    pass