"""Leave-health insights — daily trend snapshot table + cron.

Creates ``hr_leave_insight_snapshots`` (one aggregated leave-health row per
day per BU / department / lead / company) and seeds the daily
``hr.leave_insight_snapshot`` cron that fills it. The live insights
endpoints always recompute fresh from ``hr_leave_accounts``; this table is
only the forward-accumulating trend series (no historical backfill — SF
leave data is current-state).

Convention: ``id`` sized via ``id_type_for('identity_persons')`` and the
table charset aligned via ``table_kw_for`` (repo TiDB-FK-safe convention).
``group_key`` is a plain string (BU/department name or lead person-id), so
no FK to identity_persons.

Revision ID: 359
Revises: 358
"""
from __future__ import annotations

import json as _json
import uuid

import sqlalchemy as sa

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

revision = "359"
down_revision = "358"
branch_labels = None
depends_on = None

TABLE = "hr_leave_insight_snapshots"
HANDLER_KEY = "hr.leave_insight_snapshot"

_DAYS = sa.Numeric(10, 2)

_JOB_DESC = (
    "每日计算休假健康洞察的全部口径(公司基线 + 各 BU / 团队 / Lead 的年假使用率、"
    "对比公司、临期未休、调休堆积、健康度判级),写入 hr_leave_insight_snapshots,"
    "为「团队休假文化是否在改善」的趋势图逐日累积数据点。无历史回填(SF 假期为当下态),"
    "序列自上线起累积。排在 hr.leave_account_sync(06:30)之后(07:10)运行,幂等。"
)


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

    if not _table_exists(bind, TABLE):
        op.create_table(
            TABLE,
            sa.Column("id", id_type, nullable=False),
            sa.Column("snapshot_date", sa.Date, nullable=False),
            sa.Column("dimension", sa.String(16), nullable=False),
            sa.Column("group_key", sa.String(255), nullable=False),
            sa.Column("group_label", sa.String(255), nullable=True),
            sa.Column("fiscal_year", sa.Integer, nullable=True),
            sa.Column("member_count", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("covered_count", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("annual_entitled_total", _DAYS, nullable=True),
            sa.Column("annual_used_total", _DAYS, nullable=True),
            sa.Column("annual_balance_total", _DAYS, nullable=True),
            sa.Column("forfeit_at_risk_total", _DAYS, nullable=True),
            sa.Column("comp_banked_total", _DAYS, nullable=True),
            sa.Column("utilisation", sa.Float, nullable=True),
            sa.Column("median_utilisation", sa.Float, nullable=True),
            sa.Column("pace_index", sa.Float, nullable=True),
            sa.Column("vs_company", sa.Float, nullable=True),
            sa.Column("low_utiliser_count", sa.Integer, nullable=False,
                      server_default="0"),
            sa.Column("health", sa.String(24), nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), nullable=False,
                      server_default=sa.func.now()),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint(
                "snapshot_date", "dimension", "group_key",
                name="uq_hr_leave_insight_snapshot",
            ),
            **tkw,
        )
        op.create_index(
            "ix_hr_leave_insight_trend", TABLE,
            ["dimension", "group_key", "snapshot_date"],
        )
        op.create_index("ix_hr_leave_insight_date", TABLE, ["snapshot_date"])

    # Seed the daily snapshot cron (idempotent).
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text(
                "INSERT INTO scheduled_jobs "
                "(id, name, display_name, description, handler_key, "
                " trigger_type, cron_expr, timezone, enabled, config, "
                " run_count, failure_count, created_at, updated_at) "
                "SELECT :id, :name, :display, :desc, :handler, 'cron', "
                ":cron, 'Asia/Shanghai', 1, :config, 0, 0, NOW(), NOW() "
                "FROM DUAL WHERE NOT EXISTS "
                "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)"
            ),
            {
                "id": str(uuid.uuid4()),
                "name": HANDLER_KEY,
                "display": "休假健康洞察 · 每日趋势快照",
                "desc": _JOB_DESC,
                "handler": HANDLER_KEY,
                "cron": "10 7 * * *",  # 07:10 SH — after the 06:30 leave sync
                "config": _json.dumps({}),
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :k"),
            {"k": HANDLER_KEY},
        )
    if _table_exists(bind, TABLE):
        op.drop_table(TABLE)