"""asset_inventory_items — add ``evidence_photos`` JSON column.

Revision ID: 195
Revises: 194
Create Date: 2026-05-20

When a holder picks ``response_type='not_mine'`` ("记录有误，不属于我")
we now require them to upload one or more photos of the actual hardware
SN — without proof the IT desk can't reconcile the inventory mismatch.

Storage: each photo is uploaded to S3 via the presigned PUT endpoint in
``app.storage_router``; this column stores the resulting list of
S3 object keys (full key, including prefix). Reading back issues a
short-lived presigned GET per key.

JSON column (NOT TEXT/VARCHAR) so MySQL validates the value as a JSON
array on write — catches malformed driver bindings early.

Idempotent — re-run is a no-op via INFORMATION_SCHEMA check.
"""
# ruff: noqa: I001
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "195"
down_revision = "194"
branch_labels = None
depends_on = None


def _column_exists(bind, table: str, column: str) -> bool:
    rows = bind.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).scalar()
    return bool(rows)


def upgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, "asset_inventory_items", "evidence_photos"):
        return
    op.add_column(
        "asset_inventory_items",
        sa.Column("evidence_photos", sa.JSON(), nullable=True),
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "asset_inventory_items", "evidence_photos"):
        return
    op.drop_column("asset_inventory_items", "evidence_photos")