"""cmdb_dedicated_lines — 专线 (dedicated / leased line) register + seed.

Revision ID: 310
Revises: 309
Create Date: 2026-06-21

A manually-curated table of inter-office / VPN network circuits the IT team
contracts from carriers. No upstream provider sync — admins own CRUD via
``can_manage_cloud``. Initial 8 rows are seeded from the IT operations
cockpit "网络专线统计" spreadsheet (idempotent: only when the table is empty).

FK ``last_modified_by_person_id`` → ``identity_persons.id`` is declared
out-of-line with the parent's id type + the table created with the parent's
charset/collation so the FK doesn't trip MySQL/TiDB error 3780.
Idempotent + re-runnable.
"""
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 = "310"
down_revision = "309"
branch_labels = None
depends_on = None

_TABLE = "cmdb_dedicated_lines"

# (location, contracting_entity, carrier, payment_method, monthly, annual,
#  renewal_date, purpose)
# renewal_date is the recurring "MM-DD" the circuit renews on each year
# (normalised in migration 311; seeded directly here for fresh DBs). The
# contracting_entity names match rows in asset_legal_entities (公司主体信息).
_SEED = [
    ("北京", "北京平凯星辰科技发展有限公司", "中国电信", "年付【积分兑换】",
     "10000", "120000", "04-01", "北京office办公网"),
    ("上海", "上海楷浦科技发展有限公司", "中国联通", "年付",
     "2100", "25200", "09-01", "上海office办公网"),
    ("成都", "平凯星辰(北京)科技有限公司成都分公司", "中国联通", "年付",
     "1200", "14400", "09-01", "成都office办公网"),
    ("杭州", "平凯星辰(北京)科技有限公司", "中国电信", "年付",
     "3000", "36000", "10-01", "杭州office办公网"),
    ("广州", "北京平凯星辰科技发展有限公司广州分公司", "中国电信", "月付",
     "2800", "33600", None, "广州office办公网"),
    ("深圳", "北京平凯星辰科技发展有限公司深圳分公司", "中国电信", "月付",
     "2900", "34800", None, "深圳office办公网"),
    ("飞连VPN", "平凯星辰(北京)科技有限公司", "世纪互联", "月付",
     "3500", "42000", None, "US出口-10M，限定美国IP访问的网络资源，如：ChatGPT"),
    ("飞连VPN+金山云", "平凯星辰(北京)科技有限公司", "世纪互联", "月付",
     "28000", "336000", None, "HK出口-200M，产研拉取 Github 代码、飞连VPN办公等场景；"),
]


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"
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    pid = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    if not _table_exists(bind, _TABLE):
        op.create_table(
            _TABLE,
            sa.Column("id", id_type_for(_TABLE), primary_key=True),
            sa.Column("location", sa.String(length=128), nullable=False),
            sa.Column("contracting_entity", sa.String(length=255), nullable=True),
            sa.Column("carrier", sa.String(length=128), nullable=True),
            sa.Column("payment_method", sa.String(length=64), nullable=True),
            sa.Column("monthly_cost", sa.Numeric(14, 2), nullable=True),
            sa.Column("annual_cost", sa.Numeric(14, 2), nullable=True),
            sa.Column(
                "currency", sa.String(length=8),
                nullable=False, server_default="CNY",
            ),
            sa.Column("renewal_date", sa.String(length=128), nullable=True),
            sa.Column("purpose", sa.Text(), nullable=True),
            sa.Column("remark", sa.Text(), nullable=True),
            sa.Column("last_modified_by_person_id", pid, nullable=True),
            sa.Column("last_modified_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            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.ForeignKeyConstraint(
                ["last_modified_by_person_id"], ["identity_persons.id"],
                name="fk_dedicated_line_modifier", ondelete="SET NULL",
            ),
            sa.Index("ix_cmdb_dedicated_lines_carrier", "carrier"),
            sa.Index("ix_cmdb_dedicated_lines_deleted_at", "deleted_at"),
            **kw,
        )

    # Idempotent seed — only when the live (non-deleted) table is empty.
    existing = bind.execute(
        sa.text(f"SELECT COUNT(*) FROM {_TABLE}")
    ).scalar()
    if existing:
        return

    rows = [
        {
            "id": str(uuid.uuid4()),
            "location": loc,
            "contracting_entity": entity,
            "carrier": carrier,
            "payment_method": pay,
            "monthly_cost": monthly,
            "annual_cost": annual,
            "currency": "CNY",
            "renewal_date": renewal,
            "purpose": purpose,
        }
        for (loc, entity, carrier, pay, monthly, annual, renewal, purpose) in _SEED
    ]
    op.bulk_insert(
        sa.table(
            _TABLE,
            sa.column("id", sa.String),
            sa.column("location", sa.String),
            sa.column("contracting_entity", sa.String),
            sa.column("carrier", sa.String),
            sa.column("payment_method", sa.String),
            sa.column("monthly_cost", sa.Numeric),
            sa.column("annual_cost", sa.Numeric),
            sa.column("currency", sa.String),
            sa.column("renewal_date", sa.String),
            sa.column("purpose", sa.Text),
        ),
        rows,
    )


def downgrade() -> None:
    op.drop_table(_TABLE)