"""HR 年假到期提醒 —— 发送批次台账。

一次 HR 群发 = 一行。每张卡片的 outbox 行通过
``related_resource_type='hr_leave_reminder_batch'`` + ``related_resource_id``
挂回来,所以没有 per-recipient 表(投递状态由 outbox 唯一持有)。

Convention (mirrors migration 324 / 453): ``created_by_person_id`` 是普通 id
列(**无 FK** 到 identity_persons —— TiDB-FK-safe),用
``id_type_for('identity_persons')`` + ``table_kw_for`` 对齐父表字符集。

Revision ID: 506
Revises: 505
"""
from __future__ import annotations

import sqlalchemy as sa

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

revision = "506"
down_revision = "505"
branch_labels = None
depends_on = None

BATCHES = "hr_leave_reminder_batches"


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()
    person_id_type = id_type_for("identity_persons")
    tkw = table_kw_for("identity_persons")

    if not _table_exists(bind, BATCHES):
        op.create_table(
            BATCHES,
            sa.Column("id", person_id_type, nullable=False),
            sa.Column("created_by_person_id", person_id_type, nullable=True),
            sa.Column("trace_id", sa.String(64), nullable=True),
            sa.Column("status", sa.String(16), nullable=False,
                      server_default="sending"),
            sa.Column("test_mode", sa.Boolean, nullable=False,
                      server_default="0"),
            sa.Column("channels", sa.JSON, nullable=False),
            sa.Column("filters", sa.JSON, nullable=False),
            sa.Column("copy_snapshot", sa.JSON, nullable=True),
            sa.Column("recipients_snapshot", sa.JSON, nullable=False),
            sa.Column("recipient_count", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("account_count", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("at_risk_days", sa.Numeric(10, 2), nullable=True),
            sa.Column("enqueued", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("delivered", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("failed", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("error_summary", sa.Text, nullable=True),
            sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("finished_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.PrimaryKeyConstraint("id"),
            **tkw,
        )
        op.create_index(
            "ix_hr_leave_reminder_batches_created", BATCHES, ["created_at"],
        )
        op.create_index(
            "ix_hr_leave_reminder_batches_status", BATCHES, ["status"],
        )


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