"""制度公告 — platform_policy_notices table.

Revision ID: 254
Revises: 253
Create Date: 2026-06-05

Adds the 制度公告 (policy / manual notice) feature backing the header
``PolicyNoticesBanner`` ribbon:

* ``platform_policy_notices`` — bilingual title (``title_zh`` /
  ``title_en``, both NOT NULL) + ``feishu_url`` (the click target) +
  per-row ``enabled`` + ``display_order``. Standard audit columns
  (``created_at`` / ``updated_at`` + ``created_by_person_id`` /
  ``updated_by_person_id`` FKs to ``identity_persons.id``).

Mirrors the ``platform_security_tips`` shape (PR for the rotating
security ribbon) — same admin-CRUD + public-read + reorder model, just
with a doc URL instead of free text.

Idempotent: table creation is guarded with information_schema; an
earlier VARCHAR-id cut of the table is dropped so the corrected CHAR(36)
CREATE converges on TiDB (FK-strictness, error 3780). Downgrade drops
the table.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "254"
down_revision = "253"
branch_labels = None
depends_on = None

# Three starter notices (zh title / en title / Feishu doc URL), seeded in
# display order. Ids are hyphenated uuid4 strings (Python ``str(uuid4())``)
# so they fit the CHAR(36) GUID column exactly — never the no-dash 32-char
# form, which strands CHAR(36)/GUID lookups. Seed runs only when the table
# is empty so re-running the migration (or later admin edits) never
# double-inserts.
_SEED = [
    (
        "AI使用手册",
        "AI Usage Guide",
        "https://pingcap.feishu.cn/wiki/PIdrwuYcsiPmygki68KcPTr6nmd",
    ),
    (
        "AI帐号&API申请指引",
        "AI Account & API Application Guide",
        "https://pingcap.feishu.cn/wiki/YIyxwGBNIib8MIk3uxVcbfqinbb",
    ),
    (
        "全球IT硬件资产管理系统",
        "Global IT Hardware Asset Management System",
        "https://pingcap.feishu.cn/wiki/TxN5wasHyiAlyqkIChkcXOR9nWr",
    ),
]


def _table_exists(table: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :t"
        ),
        {"t": table},
    )
    return (result.scalar() or 0) > 0


def _has_varchar_id(table: str) -> bool:
    """Detect a prior-bad cut where id was declared VARCHAR(36). Local CI
    MySQL accepts FK VARCHAR(36)→CHAR(36); prod TiDB rejects it (3780).
    On a clean deploy the table doesn't exist → returns False."""
    conn = op.get_bind()
    row = conn.execute(
        sa.text(
            "SELECT DATA_TYPE FROM information_schema.columns "
            "WHERE table_schema = DATABASE() AND table_name = :t "
            "AND column_name = 'id' LIMIT 1"
        ),
        {"t": table},
    ).scalar()
    return bool(row) and str(row).lower() == "varchar"


def upgrade() -> None:
    # ── 0. TiDB-FK-strictness cleanup ─────────────────────────────
    # Table is empty on first deploy + DB is the only data source, so a
    # drop loses nothing. The CREATE below matches identity_persons.id.
    if _has_varchar_id("platform_policy_notices"):
        op.execute("DROP TABLE IF EXISTS `platform_policy_notices`")

    if not _table_exists("platform_policy_notices"):
        id_type = id_type_for("identity_persons")
        op.create_table(
            "platform_policy_notices",
            sa.Column("id", id_type, nullable=False),
            sa.Column("title_zh", sa.String(length=200), nullable=False),
            sa.Column("title_en", sa.String(length=200), nullable=False),
            sa.Column("feishu_url", sa.String(length=1000), nullable=False),
            sa.Column(
                "enabled",
                sa.Boolean(),
                nullable=False,
                server_default="1",
            ),
            sa.Column(
                "display_order",
                sa.Integer(),
                nullable=False,
                server_default="0",
            ),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                nullable=False,
                server_default=sa.func.now(),
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                nullable=False,
                server_default=sa.func.now(),
                server_onupdate=sa.func.now(),
            ),
            sa.Column(
                "created_by_person_id",
                id_type,
                sa.ForeignKey("identity_persons.id"),
                nullable=True,
            ),
            sa.Column(
                "updated_by_person_id",
                id_type,
                sa.ForeignKey("identity_persons.id"),
                nullable=True,
            ),
            sa.PrimaryKeyConstraint("id"),
            **table_kw_for("identity_persons"),
        )
        op.create_index(
            "ix_platform_policy_notices_enabled_order",
            "platform_policy_notices",
            ["enabled", "display_order"],
        )

    # ── Seed the three starter notices (idempotent: empty-table only) ──
    conn = op.get_bind()
    already = conn.execute(
        sa.text("SELECT COUNT(*) FROM platform_policy_notices")
    ).scalar() or 0
    if already == 0:
        for idx, (title_zh, title_en, feishu_url) in enumerate(_SEED):
            conn.execute(
                sa.text(
                    "INSERT INTO platform_policy_notices "
                    "(id, title_zh, title_en, feishu_url, enabled, "
                    "display_order, created_at, updated_at) VALUES "
                    "(:id, :zh, :en, :url, 1, :ord, NOW(), NOW())"
                ),
                {
                    "id": str(uuid.uuid4()),
                    "zh": title_zh,
                    "en": title_en,
                    "url": feishu_url,
                    "ord": idx,
                },
            )


def downgrade() -> None:
    op.drop_table("platform_policy_notices")