AiDOPWarehouse/doc/plan/sql/uat-data/run_wp_sd4_approval_baseline.py

#!/usr/bin/env python3
"""WP-SD4: publish formal SMART_OPS_IMPROVEMENT flow and verify UAT role members."""

from __future__ import annotations

import json
import re
from pathlib import Path

import pymysql
from pymysql.constants import CLIENT


ROOT = Path(__file__).resolve().parents[4]
CONFIG = ROOT / "server" / "Admin.NET.Application" / "Configuration" / "Database.json"
FLOW_JSON = Path(__file__).with_name("smart_ops_improvement_flow.json")
EVIDENCE = ROOT / "doc" / "plan" / "UAT留证" / "2026-08-17-智慧诊断正式落地"
TENANTS = {
    "A": 838257186181189,
    "B": 838257212780613,
    "DEMO": 838257237606469,
}
ROLES = (
    ("SMART_OPS_DEPT_HEAD", "责任部门负责人"),
    ("SMART_OPS_MODULE_OWNER", "模块负责人"),
    ("SMART_OPS_OPS_OWNER", "运营负责人"),
)


def connect() -> pymysql.Connection:
    raw = CONFIG.read_text(encoding="utf-8-sig")
    value = next(
        item
        for item in re.findall(r'(?m)^\s*"ConnectionString"\s*:\s*"([^"]+)"', raw)
        if "Database=aidopdev" in item
    )
    parts = {
        item.split("=", 1)[0].strip().lower(): item.split("=", 1)[1].strip()
        for item in value.split(";")
        if "=" in item
    }
    return pymysql.connect(
        host=parts["server"],
        port=int(parts["port"]),
        user=parts["uid"],
        password=parts["pwd"],
        database=parts["database"],
        charset="utf8mb4",
        autocommit=True,
        client_flag=CLIENT.MULTI_STATEMENTS,
        cursorclass=pymysql.cursors.DictCursor,
    )


def next_id(cur, table: str) -> int:
    cur.execute(f"SELECT IFNULL(MAX(Id), 0) + 1 AS n FROM {table}")
    return int(cur.fetchone()["n"])


def ensure_fallback_members(cur) -> None:
    for tenant_id in TENANTS.values():
        for code, _name in ROLES:
            cur.execute(
                """
                SELECT r.Id AS role_id
                FROM SysRole r
                WHERE r.TenantId=%s AND r.Code=%s AND r.Status=1
                LIMIT 1
                """,
                (tenant_id, code),
            )
            role = cur.fetchone()
            if not role:
                continue
            cur.execute(
                """
                SELECT COUNT(*) AS n
                FROM SysUserRole ur
                JOIN SysUser u ON u.Id=ur.UserId
                WHERE ur.RoleId=%s AND u.TenantId=%s AND u.Status=1
                """,
                (role["role_id"], tenant_id),
            )
            if cur.fetchone()["n"] > 0:
                continue
            cur.execute(
                """
                SELECT Id FROM SysUser
                WHERE TenantId=%s AND Status=1
                ORDER BY AccountType DESC, Id
                LIMIT 1
                """,
                (tenant_id,),
            )
            user = cur.fetchone()
            if not user:
                continue
            cur.execute(
                "INSERT INTO SysUserRole (Id, UserId, RoleId) VALUES (%s, %s, %s)",
                (next_id(cur, "SysUserRole"), user["Id"], role["role_id"]),
            )


def publish_formal_flow(cur) -> dict:
    flow_json = FLOW_JSON.read_text(encoding="utf-8")
    cur.execute(
        """
        SELECT Id, OrgId, Version, IsPublished, FlowJson, Remark
        FROM ApprovalFlow
        WHERE BizType='SMART_OPS_IMPROVEMENT' AND IFNULL(IsDelete,0)=0
        """
    )
    rows = cur.fetchall()
    updated = 0
    for row in rows:
        raw = row.get("FlowJson") or ""
        if "SMART_OPS_DEPT_HEAD" in raw and "exclusiveGateway" in raw:
            if not row.get("IsPublished"):
                cur.execute(
                    "UPDATE ApprovalFlow SET IsPublished=1, UpdateTime=NOW() WHERE Id=%s",
                    (row["Id"],),
                )
                updated += 1
            continue
        new_version = max(int(row.get("Version") or 1), 1) + 1
        cur.execute(
            """
            UPDATE ApprovalFlow
            SET FlowJson=%s, Version=%s, IsPublished=1, Status=1,
                Remark='正式流程：黄/红/跨部门网关，按角色解析审批人',
                UpdateTime=NOW()
            WHERE Id=%s
            """,
            (flow_json, new_version, row["Id"]),
        )
        cur.execute(
            """
            INSERT INTO ApprovalFlowVersion
              (Id, FlowId, Version, FlowJson, FormJson, PublishTime, PublisherName, CreateTime, UpdateTime)
            VALUES (%s, %s, %s, %s, '{}', NOW(), 'WP-SD4', NOW(), NOW())
            """,
            (next_id(cur, "ApprovalFlowVersion"), row["Id"], new_version, flow_json),
        )
        updated += 1
    return {"existing": len(rows), "updated": updated}


def inventory(cur) -> dict:
    result = {"tenants": {}, "legacyJsonPlans": 0, "actionRows": 0}
    for code, tenant_id in TENANTS.items():
        roles = {}
        for role_code, role_name in ROLES:
            cur.execute(
                """
                SELECT r.Id AS role_id,
                       (SELECT COUNT(*) FROM SysUserRole ur
                        JOIN SysUser u ON u.Id=ur.UserId
                        WHERE ur.RoleId=r.Id AND u.TenantId=%s AND u.Status=1) AS members
                FROM SysRole r
                WHERE r.TenantId=%s AND r.Code=%s
                """,
                (tenant_id, tenant_id, role_code),
            )
            row = cur.fetchone()
            roles[role_code] = {
                "name": role_name,
                "roleId": str(row["role_id"]) if row else None,
                "members": int(row["members"]) if row else 0,
            }
        result["tenants"][code] = {"tenantId": str(tenant_id), "roles": roles}

    cur.execute(
        """
        SELECT COUNT(*) AS n
        FROM ado_smart_ops_improvement_plan
        WHERE ActionItemsJson IS NOT NULL
          AND ActionItemsJson NOT IN ('', '[]', 'null')
        """
    )
    result["legacyJsonPlans"] = int(cur.fetchone()["n"])
    cur.execute("SELECT COUNT(*) AS n FROM ado_smart_ops_improvement_action")
    result["actionRows"] = int(cur.fetchone()["n"])
    cur.execute(
        """
        SELECT Id, OrgId, Version, IsPublished
        FROM ApprovalFlow
        WHERE BizType='SMART_OPS_IMPROVEMENT' AND IFNULL(IsDelete,0)=0
        ORDER BY Version DESC
        """
    )
    result["flows"] = [
        {
            "id": str(x["Id"]),
            "orgId": str(x["OrgId"]),
            "version": x["Version"],
            "published": bool(x["IsPublished"]),
        }
        for x in cur.fetchall()
    ]
    return result


def main() -> None:
    conn = connect()
    try:
        with conn.cursor() as cur:
            ensure_fallback_members(cur)
            flow = publish_formal_flow(cur)
            data = inventory(cur)
            data["flowUpdate"] = flow
    finally:
        conn.close()

    EVIDENCE.mkdir(parents=True, exist_ok=True)
    out = EVIDENCE / "04-approval-baseline.json"
    out.write_text(json.dumps(data, ensure_ascii=False, indent=2), encoding="utf-8")
    print(json.dumps(data, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()