"""Department → platform-role auto-derivation.

Revision ID: 296
Revises: 295
Create Date: 2026-06-17

User direction: people in the Finance BU auto-hold ``finance``; HR
auto-holds ``hr``. Three things ship here:

1. ``auth_user_roles.grant_source`` (VARCHAR(16) NOT NULL DEFAULT
   'manual') — provenance so the reconciler can revoke ONLY the grants
   it created and never an admin's manual grant. Every pre-existing row
   defaults to 'manual'.
2. The ``identity.department_role_reconcile`` daily cron row (handler in
   ``app.scheduled_jobs.identity_handlers``).
3. A one-time backfill: grant ``derived`` finance/hr to every active
   person whose primary org_path already matches today, so existing
   staff who lack the role get it immediately on deploy (no waiting for
   the first cron tick / next HRIS sync).

Matching mirrors ``app.identity.department_role_sync``: any tenant-root-
stripped segment of the primary membership's ``org_path`` that equals a
known department name (case-insensitive). Defaults are inlined here (a
migration is a point-in-time snapshot; it must not import app code that
can drift). No FK is added — ``grant_source`` is a plain scalar.

Idempotent: column-add guarded on existence; cron seeded WHERE NOT
EXISTS; backfill uses INSERT IGNORE so re-runs and existing manual
grants are left untouched.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "296"
down_revision = "295"
branch_labels = None
depends_on = None

_HANDLER = "identity.department_role_reconcile"

# Inlined snapshot of DEFAULT_DEPARTMENT_ROLE_MAP — see
# app/identity/department_role_sync.py. Keys are lower-cased.
_DEPT_ROLE_MAP = {
    "finance": "finance",
    "财务": "finance",
    "财务部": "finance",
    "hr": "hr",
    "human resources": "hr",
    "人力资源": "hr",
    "人力资源部": "hr",
}
_TENANT_ROOTS = {"pingcap", "pingcap-ati"}


def _table_exists(bind, table: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": table},
    ).fetchone() is not None


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
            "AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).fetchone() is not None


def _segments(org_path: str | None, leaf: str | None) -> list[str]:
    raw = (org_path or "").strip()
    parts = [p.strip() for p in raw.split("/") if p.strip()]
    if not parts and leaf:
        parts = [leaf.strip()]
    if parts and parts[0].lower() in _TENANT_ROOTS:
        parts = parts[1:]
    return [p.lower() for p in parts if p]


def upgrade() -> None:
    bind = op.get_bind()

    # 1) grant_source column.
    if _table_exists(bind, "auth_user_roles") and not _column_exists(
        bind, "auth_user_roles", "grant_source"
    ):
        op.add_column(
            "auth_user_roles",
            sa.Column(
                "grant_source",
                sa.String(length=16),
                nullable=False,
                server_default="manual",
            ),
        )

    # 2) Seed the daily reconcile cron (03:05 Asia/Shanghai).
    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, '{}', 0, 0, NOW(), NOW() "
                "FROM DUAL WHERE NOT EXISTS "
                "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)"
            ),
            {
                "id": str(uuid.uuid4()),
                "name": _HANDLER,
                "display": "部门角色自动派生对账",
                "desc": (
                    "每日对账「部门 → 平台角色」自动派生:Finance 成员持有 "
                    "finance、HR 成员持有 hr;只增删 grant_source='derived' "
                    "的派生授权,手动授权不受影响。"
                ),
                "handler": _HANDLER,
                "cron": "5 3 * * *",
            },
        )

    # 3) One-time backfill of derived finance/hr grants for existing
    #    active staff. Skipped if the tables aren't present yet.
    if not (
        _table_exists(bind, "auth_user_roles")
        and _table_exists(bind, "auth_roles")
        and _table_exists(bind, "identity_org_memberships")
        and _table_exists(bind, "identity_persons")
    ):
        return

    role_ids = {
        name: rid
        for name, rid in bind.execute(
            sa.text(
                "SELECT name, id FROM auth_roles "
                "WHERE name IN ('finance', 'hr')"
            )
        ).fetchall()
    }
    if not role_ids:
        return

    rows = bind.execute(
        sa.text(
            "SELECT m.person_id, m.org_path, m.org_node_name "
            "FROM identity_org_memberships m "
            "JOIN identity_persons p ON p.id = m.person_id "
            "WHERE m.is_primary = 1 AND m.deleted_at IS NULL "
            "AND p.deleted_at IS NULL "
            "AND (p.employment_status = 'active' "
            "     OR p.employment_status IS NULL)"
        )
    ).fetchall()

    for person_id, org_path, leaf in rows:
        matched: set[str] = set()
        for seg in _segments(org_path, leaf):
            role = _DEPT_ROLE_MAP.get(seg)
            if role in ("finance", "hr"):
                matched.add(role)
        for role in matched:
            role_id = role_ids.get(role)
            if role_id is None:
                continue
            # INSERT IGNORE: leaves any existing (manual or derived) grant
            # untouched; only creates the missing derived rows.
            bind.execute(
                sa.text(
                    "INSERT IGNORE INTO auth_user_roles "
                    "(user_id, role_id, granted_by, granted_at, grant_source) "
                    "VALUES (:uid, :rid, NULL, NOW(), 'derived')"
                ),
                {"uid": person_id, "rid": role_id},
            )


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},
        )
    # Drop only the grants this feature created.
    if _table_exists(bind, "auth_user_roles") and _column_exists(
        bind, "auth_user_roles", "grant_source"
    ):
        bind.execute(
            sa.text(
                "DELETE FROM auth_user_roles WHERE grant_source = 'derived'"
            )
        )
        op.drop_column("auth_user_roles", "grant_source")