"""Create platform_security_tips + seed five default tips.

Revision ID: 100
Revises: 099
Create Date: 2026-05-02

Per user direction 2026-05-02: add a configurable rotating banner to
the app header so every signed-in user sees a steady drumbeat of
security & IT-awareness reminders. Admin-managed at
``/admin/security-tips`` (full CRUD + drag-reorder); displayed via
``GET /api/v1/security-tips`` consumed by the
``SecurityTipsBanner`` header component.

Seeds five defaults so the banner has content the moment the
migration lands. Admins can edit / disable / delete / add via the
management UI.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "100"
down_revision = "099"
branch_labels = None
depends_on = None


def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36):
    """Mirror the FK target's DATA_TYPE + COLLATION so TiDB / MySQL 8
    don't reject the constraint with error 3780. ``identity_persons.id``
    is ``CHAR(36) utf8mb4_unicode_ci`` on prod TiDB; declaring a child
    column as plain ``sa.CHAR(36)`` (default collation) trips the
    strict compatibility check. Same pattern as migration 098."""
    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()
    data_type = (row[0] or "char").lower() if row else "char"
    collation = row[1] if row else None
    type_cls = sa.CHAR if data_type == "char" else sa.String
    return type_cls(length, collation=collation) if collation else type_cls(length)


_DEFAULT_TIPS = [
    # (text_zh, text_en) — order is the seed display_order.
    ("离开座位请记得锁屏",
     "Lock your screen when stepping away from your desk"),
    ("信息安全人人有责",
     "Information security is everyone's responsibility"),
    ("不要把你的密码告诉任何人",
     "Never share your password with anyone"),
    ("警惕钓鱼邮件,陌生附件不要打开",
     "Beware of phishing emails — don't open attachments from unknown senders"),
    ("有任何问题请联系 IT 服务台",
     "Reach out to the IT helpdesk whenever you have questions"),
]


def upgrade() -> None:
    bind = op.get_bind()
    persons_id = _fk_source_col_type(bind, "identity_persons", "id")
    op.create_table(
        "platform_security_tips",
        sa.Column("id", sa.CHAR(36), primary_key=True),
        sa.Column("text_zh", sa.String(500), nullable=False),
        sa.Column("text_en", sa.String(500), nullable=False),
        sa.Column(
            "enabled", sa.Boolean(), nullable=False, server_default=sa.text("1"),
        ),
        sa.Column(
            "display_order", sa.Integer(), nullable=False,
            server_default=sa.text("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(),
        ),
        sa.Column("created_by_person_id", persons_id, nullable=True),
        sa.Column("updated_by_person_id", persons_id, nullable=True),
        sa.ForeignKeyConstraint(
            ["created_by_person_id"], ["identity_persons.id"],
            name="fk_platform_security_tips_created_by",
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["updated_by_person_id"], ["identity_persons.id"],
            name="fk_platform_security_tips_updated_by",
            ondelete="SET NULL",
        ),
        sa.Index("ix_platform_security_tips_order", "display_order"),
    )

    # Seed defaults — only when the table is empty (idempotent across
    # repeat runs in dev).
    bind = op.get_bind()
    existing = bind.execute(sa.text(
        "SELECT COUNT(*) FROM platform_security_tips"
    )).scalar()
    if existing == 0:
        for idx, (zh, en) in enumerate(_DEFAULT_TIPS):
            bind.execute(sa.text(
                "INSERT INTO platform_security_tips "
                "(id, text_zh, text_en, enabled, display_order, "
                "created_at, updated_at) "
                "VALUES (UUID(), :zh, :en, 1, :ord, NOW(), NOW())"
            ), {"zh": zh, "en": en, "ord": idx})


def downgrade() -> None:
    op.drop_index(
        "ix_platform_security_tips_order",
        table_name="platform_security_tips",
    )
    op.drop_table("platform_security_tips")