"""support_kb_articles embedding — semantic retrieval (求助域 AI-activation PR3).

Revision ID: 306
Revises: 305
Create Date: 2026-06-18

Adds a portable JSON ``embedding`` float array (NOT TiDB VECTOR — keeps local
MySQL tests runnable; ranking is Python cosine) + ``embedding_model`` (the
producing model id, so a model change can re-embed) + ``embedded_at``. NULL
until embedded; keyword search is always the graceful fallback. Idempotent +
re-runnable (guards on column presence).
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "306"
down_revision = "305"
branch_labels = None
depends_on = None

TABLE = "support_kb_articles"
COLUMNS = (
    ("embedding", lambda: sa.Column("embedding", sa.JSON(), nullable=True)),
    ("embedding_model", lambda: sa.Column("embedding_model", sa.String(length=64), nullable=True)),
    ("embedded_at", lambda: sa.Column("embedded_at", sa.DateTime(timezone=True), nullable=True)),
)


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


def upgrade() -> None:
    bind = op.get_bind()
    for name, factory in COLUMNS:
        if not _column_exists(bind, TABLE, name):
            op.add_column(TABLE, factory())


def downgrade() -> None:
    bind = op.get_bind()
    for name, _ in COLUMNS:
        if _column_exists(bind, TABLE, name):
            op.drop_column(TABLE, name)