"""HR 洞察 AI diagnosis — per-(section, range) cache table.

Revision ID: 393
Revises: 392
Create Date: 2026-07-08

**``insights_hr_ai_summaries``** — cached bilingual LLM diagnosis for the
rebuilt HR 洞察 tabs (人员洞察 / 入离职洞察). Mirrors
``insights_impact_summaries`` (282) but keyed on ``(section, range_key)``
because the HR tabs are time-window-parameterised: one narrative per
``section`` × ``range``. Manual regenerate replaces the same-key row.
``generated_by_user_id`` mirrors ``identity_persons.id`` type AND
collation via ``_fk_source_col_type`` + out-of-line FK (TiDB 3780
discipline — see migrations 073/074/282).

No scheduled_jobs seed: generation is on-demand (global_admin / hr click
生成 on the AI panel), reusing the platform-wide ``ai_spending.llm.*``
credentials. Idempotent: table guarded by information_schema check.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "393"
down_revision = "392"
branch_labels = None
depends_on = None


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n LIMIT 1"
        ),
        {"n": name},
    ).scalar() is not None


def _fk_source_col_type(
    bind,
    target_table: str,
    target_column: str,
    *,
    fallback_type: str = "char",
    fallback_collation: str | None = "utf8mb4_unicode_ci",
    length: int = 36,
):
    """Mirror an FK target's data type AND collation on the source
    column (TiDB errno-3780 discipline; canonical pattern from
    migrations 073/074/282)."""
    row = bind.execute(
        sa.text(
            "SELECT DATA_TYPE, COLLATION_NAME FROM "
            "information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": target_table, "c": target_column},
    ).fetchone()
    if row:
        data_type = (row[0] or fallback_type).lower()
        collation = row[1] or fallback_collation
    else:
        data_type = fallback_type.lower()
        collation = fallback_collation
    type_cls = sa.CHAR if data_type == "char" else sa.String
    if collation:
        return type_cls(length, collation=collation)
    return type_cls(length)


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

    if not _table_exists(bind, "insights_hr_ai_summaries"):
        identity_persons_id_col = _fk_source_col_type(
            bind, "identity_persons", "id",
        )
        op.create_table(
            "insights_hr_ai_summaries",
            sa.Column("id", sa.CHAR(36), primary_key=True),
            sa.Column("section", sa.String(32), nullable=False),
            sa.Column("range_key", sa.String(8), nullable=False),
            sa.Column("summary_md_en", sa.Text, nullable=True),
            sa.Column("summary_md_zh", sa.Text, nullable=True),
            sa.Column("highlights", sa.JSON, nullable=True),
            sa.Column("metrics", sa.JSON, nullable=True),
            sa.Column("model_used", sa.String(128), nullable=True),
            sa.Column("input_token_count", sa.Integer, nullable=True),
            sa.Column("output_token_count", sa.Integer, nullable=True),
            sa.Column(
                "generated_by_user_id", identity_persons_id_col,
                nullable=True,
            ),
            sa.Column(
                "generated_at", sa.DateTime(timezone=True),
                server_default=sa.func.now(), nullable=False,
            ),
            sa.Column(
                "created_at", sa.DateTime(timezone=True),
                server_default=sa.func.now(), nullable=False,
            ),
            sa.Column(
                "updated_at", sa.DateTime(timezone=True),
                server_default=sa.func.now(), onupdate=sa.func.now(),
                nullable=False,
            ),
            sa.UniqueConstraint(
                "section", "range_key",
                name="uq_insights_hr_ai_summaries_section_range",
            ),
        )
        try:
            op.create_foreign_key(
                "fk_insights_hr_ai_summaries_user",
                "insights_hr_ai_summaries",
                "identity_persons",
                ["generated_by_user_id"],
                ["id"],
                ondelete="SET NULL",
            )
        except Exception:  # noqa: BLE001 — FK is best-effort hardening
            pass


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "insights_hr_ai_summaries"):
        op.drop_table("insights_hr_ai_summaries")