"""账号租约 `account_leases` + `accounts.role_change_available_at` + 操作人索引

配套文档：`docs/设计-账号自动分配与回收.md`（§3.2 表结构、§4 换绑冷却、§3.14 流水）。

三件事：

1. **`account_leases`** —— 归属（`assigned_to`，管理员/运营的维护责任）与
   使用权（租约，客服临时占用）分离。核心是生成列 `active_account_id`
   加唯一索引 `uq_lease_active_account`：一个账号同时只能有一个**有效**租约，
   并发抢号由数据库裁决（撞唯一键即换候选），不需要应用层加锁。
2. **`accounts.role_change_available_at`** —— `change_role_wait_time` 是接口原文的
   「剩余秒数」，落库即过期；换绑判定需要的是**绝对时刻**。电竞手册复用既有的
   `esports_can_change_time`（本来就是绝对时刻），不另开副本。
3. **`ix_account_events_actor_occurred`** —— 池化后客服不再持有账号，
   「某客服这段时间做了什么」只能按 `actor_id` 查；此前没有可用索引。

**可重跑**：应用启动时会自动跑迁移，所以每个动作都带存在性守卫。

**回退**：drop 表、drop 列、drop 索引。租约是运行期状态，回退即丢失 ——
但账号与任务数据不受影响（`assigned_to`、`douyu_tasks` 一律没动）。
"""

from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision: str = "20261008_0053"
down_revision: str | None = "20261008_0052"
branch_labels: str | None = None
depends_on: str | None = None

TABLE = "account_leases"
ACCOUNT_COLUMN = "role_change_available_at"
ACTOR_INDEX = "ix_account_events_actor_occurred"

#: 「一个账号同时只能有一个有效租约」的数据库级保证。
ACTIVE_ACCOUNT_EXPR = "CASE WHEN status = 'active' THEN account_id ELSE NULL END"


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


def _column_exists(table: str, name: str) -> bool:
    conn = op.get_bind()
    count = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.columns "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :table AND column_name = :name"
        ),
        {"table": table, "name": name},
    ).scalar()
    return bool(count)


def _index_exists(table: str, name: str) -> bool:
    conn = op.get_bind()
    count = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.statistics "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :table AND index_name = :name"
        ),
        {"table": table, "name": name},
    ).scalar()
    return bool(count)


def upgrade() -> None:
    if not _table_exists(TABLE):
        op.create_table(
            TABLE,
            sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
            sa.Column("account_id", sa.Integer(), nullable=False),
            sa.Column("holder_user_id", sa.Integer(), nullable=False),
            sa.Column(
                "scope", sa.String(length=16), nullable=False, server_default="elite"
            ),
            sa.Column(
                "purpose",
                sa.String(length=24),
                nullable=False,
                server_default="exchange",
            ),
            sa.Column(
                "commodity_id", sa.String(length=64), nullable=False, server_default=""
            ),
            sa.Column(
                "batch_id", sa.String(length=64), nullable=False, server_default=""
            ),
            sa.Column(
                "status", sa.String(length=16), nullable=False, server_default="active"
            ),
            sa.Column("leased_at", sa.DateTime(), nullable=False),
            sa.Column("expires_at", sa.DateTime(), nullable=False),
            sa.Column("released_at", sa.DateTime(), nullable=True),
            sa.Column(
                "release_reason",
                sa.String(length=64),
                nullable=False,
                server_default="",
            ),
            # ⚠️ 生成列必须排在 account_id / status 之后：MySQL 只允许生成列
            # 引用它前面已定义的列。
            sa.Column(
                "active_account_id",
                sa.Integer(),
                sa.Computed(ACTIVE_ACCOUNT_EXPR, persisted=True),
                nullable=True,
            ),
            sa.Column("created_at", sa.DateTime(), nullable=False),
            sa.PrimaryKeyConstraint("id"),
            sa.ForeignKeyConstraint(
                ["account_id"], ["accounts.id"], name="fk_lease_account"
            ),
            sa.ForeignKeyConstraint(
                ["holder_user_id"], ["users.id"], name="fk_lease_holder"
            ),
        )

    if not _index_exists(TABLE, "uq_lease_active_account"):
        op.create_index(
            "uq_lease_active_account", TABLE, ["active_account_id"], unique=True
        )
    if not _index_exists(TABLE, "ix_leases_holder_status"):
        op.create_index("ix_leases_holder_status", TABLE, ["holder_user_id", "status"])
    if not _index_exists(TABLE, "ix_leases_status_expires"):
        op.create_index("ix_leases_status_expires", TABLE, ["status", "expires_at"])
    if not _index_exists(TABLE, "ix_account_leases_account_id"):
        op.create_index("ix_account_leases_account_id", TABLE, ["account_id"])

    if not _column_exists("accounts", ACCOUNT_COLUMN):
        op.add_column(
            "accounts",
            sa.Column(
                ACCOUNT_COLUMN,
                sa.DateTime(),
                nullable=True,
                comment="精英宝典可换绑时刻（绝对时间）；换绑冷却只记录不拦截",
            ),
        )

    if not _index_exists("account_events", ACTOR_INDEX):
        op.create_index(ACTOR_INDEX, "account_events", ["actor_id", "occurred_at"])


def downgrade() -> None:
    if _index_exists("account_events", ACTOR_INDEX):
        op.drop_index(ACTOR_INDEX, table_name="account_events")
    if _column_exists("accounts", ACCOUNT_COLUMN):
        op.drop_column("accounts", ACCOUNT_COLUMN)
    if _table_exists(TABLE):
        op.drop_table(TABLE)