"""comparison_record 加 product_names 列(下单商品名派生串)+ 回填历史行

admin 比价记录页要把原「店/商品」一列拆成「店」+「商品」两列、并支持按商品名搜索。
items 是 JSON(SQLite 下 json.dumps ensure_ascii 会把中文转义存成 \\uXXXX,无法直接 CAST+LIKE
命中中文),故把商品名派生成普通文本列,跨库 LIKE 一致、可索引。

写路径(upsert_record / harvest_done)已同步派生;本迁移建列并从已存 items 回填历史行。
全新环境顺序应用即得空列 + 回填(表本为空,回填 no-op)。

Revision ID: comparison_product_names
Revises: comparison_record_trace_unique
Create Date: 2026-07-04 00:00:00.000000
"""

import json
from collections.abc import Sequence

import sqlalchemy as sa

from alembic import op

revision: str = "comparison_product_names"
down_revision: str | Sequence[str] | None = "comparison_record_trace_unique"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def _product_names(items) -> str | None:
    """items([{name,...}]) → 顿号分隔的商品名串(去重保序)。与 repositories.comparison
    ._product_names_from_items 同口径,迁移内联一份避免耦合 app 代码。"""
    if isinstance(items, str):  # SQLite: items 以 JSON 文本存,取回是 str
        try:
            items = json.loads(items)
        except (ValueError, TypeError):
            return None
    if not isinstance(items, list) or not items:
        return None
    names: list[str] = []
    for it in items:
        name = it.get("name") if isinstance(it, dict) else None
        if not name:
            continue
        s = str(name).strip()
        if s and s not in names:
            names.append(s)
    joined = "、".join(names)
    return joined[:500] or None


def upgrade() -> None:
    with op.batch_alter_table("comparison_record", schema=None) as batch_op:
        batch_op.add_column(sa.Column("product_names", sa.String(length=512), nullable=True))

    # 回填:从已存 items 派生商品名。items 小(菜名列表),一次取回即可;只更新有商品名的行。
    bind = op.get_bind()
    rows = bind.execute(
        sa.text("SELECT id, items FROM comparison_record")
    ).fetchall()
    for rid, items in rows:
        pn = _product_names(items)
        if pn:
            bind.execute(
                sa.text("UPDATE comparison_record SET product_names = :pn WHERE id = :id"),
                {"pn": pn, "id": rid},
            )


def downgrade() -> None:
    with op.batch_alter_table("comparison_record", schema=None) as batch_op:
        batch_op.drop_column("product_names")