AiDOPWarehouse/ai-dop-platform/tools/insert_business_fact_menu.py

# -*- coding: utf-8 -*-
"""幂等插入「业务事实字典」菜单到 dev 库。

首次启动时 SysMenuSeedData 不会自动补新菜单（IgnoreUpdateSeed），
AidopMenuLinkSync 仅补租户/角色链接而不会建菜单本身。
故 dev 环境需一次性手工插。重跑无副作用。
"""
from __future__ import annotations
import sys
from datetime import datetime

import pymysql

sys.stdout.reconfigure(encoding='utf-8')

CONN = dict(
    host='123.60.180.165', port=3306,
    user='aidopremote', password='1234567890aiDOP#',
    database='aidopdev', charset='utf8mb4', autocommit=False,
)

MENU_ID = 1320990000313
PARENT_ID = 1320990000300  # Ai-DOP 智慧运营目录（从 kpi-master 菜单推得，同父级）
ROUTE_PATH = '/aidop/smart-ops/business-fact'
ROUTE_NAME = 'aidopSmartOpsBusinessFact'
COMPONENT = '/aidop/kanban/businessFact'
TITLE = '业务事实字典'


def main() -> None:
    conn = pymysql.connect(**CONN)
    cur = conn.cursor(pymysql.cursors.DictCursor)
    try:
        # 先找出 kpi-master 菜单的 Pid 作为父级
        cur.execute("SELECT Pid, OrderNo FROM SysMenu WHERE Id=%s", (1320990000312,))
        parent_row = cur.fetchone()
        if not parent_row:
            raise RuntimeError("未找到 kpi-master 菜单（1320990000312），无法推断父级目录")
        pid = parent_row['Pid']
        order_no = int(parent_row['OrderNo']) + 1
        print(f"父级目录 Pid={pid}, 排序={order_no}")

        # 探测 SysMenu 列集
        cur.execute("SHOW COLUMNS FROM SysMenu")
        cols = {r['Field'] for r in cur.fetchall()}
        print(f"SysMenu 列: {sorted(cols)[:12]}... (共 {len(cols)})")

        # 1) 菜单：从 kpi-master 克隆后覆盖关键字段
        cur.execute("SELECT COUNT(*) AS c FROM SysMenu WHERE Id=%s", (MENU_ID,))
        if cur.fetchone()['c'] > 0:
            print(f"[SKIP] SysMenu.Id={MENU_ID} 已存在")
        else:
            cur.execute("SELECT * FROM SysMenu WHERE Id=%s", (1320990000312,))
            tmpl = cur.fetchone()
            if not tmpl:
                raise RuntimeError("模板菜单 1320990000312 不存在")
            tmpl['Id'] = MENU_ID
            tmpl['Title'] = TITLE
            tmpl['Path'] = ROUTE_PATH
            tmpl['Name'] = ROUTE_NAME
            tmpl['Component'] = COMPONENT
            tmpl['Icon'] = 'ele-Collection'
            tmpl['OrderNo'] = order_no
            tmpl['Remark'] = TITLE
            tmpl['CreateTime'] = datetime.now()
            if 'UpdateTime' in tmpl:
                tmpl['UpdateTime'] = None
            ks = ','.join(f'`{k}`' for k in tmpl)
            ph = ','.join(['%s'] * len(tmpl))
            cur.execute(f"INSERT INTO SysMenu ({ks}) VALUES ({ph})", tuple(tmpl.values()))
            print(f"[OK]   新增 SysMenu.Id={MENU_ID}（按 kpi-master 菜单克隆）")

        # 2) 租户菜单 - 给所有租户都挂上
        cur.execute("SELECT Id FROM SysTenant")
        tenants = [r['Id'] for r in cur.fetchall()]
        cur.execute("SHOW COLUMNS FROM SysTenantMenu")
        tm_cols = {r['Field'] for r in cur.fetchall()}
        tm_added = 0
        for tid in tenants:
            cur.execute(
                "SELECT COUNT(*) AS c FROM SysTenantMenu WHERE TenantId=%s AND MenuId=%s",
                (tid, MENU_ID),
            )
            if cur.fetchone()['c'] > 0:
                continue
            cur.execute("SELECT IFNULL(MAX(Id),0)+1 AS nx FROM SysTenantMenu")
            link_id = cur.fetchone()['nx']
            tm_row = {'Id': link_id, 'TenantId': tid, 'MenuId': MENU_ID}
            tm_row = {k: v for k, v in tm_row.items() if k in tm_cols}
            ks = ','.join(f'`{k}`' for k in tm_row)
            ph = ','.join(['%s'] * len(tm_row))
            cur.execute(f"INSERT INTO SysTenantMenu ({ks}) VALUES ({ph})", tuple(tm_row.values()))
            tm_added += 1
        print(f"[OK]   补 SysTenantMenu x {tm_added} 条（共 {len(tenants)} 租户）")

        # 3) 角色菜单 - 给系统管理员 + 已授权 kpi-master 的角色
        cur.execute("SELECT DISTINCT RoleId FROM SysRoleMenu WHERE MenuId=%s", (1320990000312,))
        roles = {r['RoleId'] for r in cur.fetchall()}
        roles.add(1300000000101)  # SysAdmin
        cur.execute("SHOW COLUMNS FROM SysRoleMenu")
        rm_cols = {r['Field'] for r in cur.fetchall()}
        inserted = 0
        for rid in roles:
            cur.execute(
                "SELECT COUNT(*) AS c FROM SysRoleMenu WHERE RoleId=%s AND MenuId=%s",
                (rid, MENU_ID),
            )
            if cur.fetchone()['c'] > 0:
                continue
            # 避免 Id 撞号：用 MAX+1 策略
            cur.execute("SELECT IFNULL(MAX(Id),0)+1 AS nx FROM SysRoleMenu")
            nx = cur.fetchone()['nx']
            rm_row = {'Id': nx, 'RoleId': rid, 'MenuId': MENU_ID}
            rm_row = {k: v for k, v in rm_row.items() if k in rm_cols}
            ks = ','.join(f'`{k}`' for k in rm_row)
            ph = ','.join(['%s'] * len(rm_row))
            cur.execute(f"INSERT INTO SysRoleMenu ({ks}) VALUES ({ph})", tuple(rm_row.values()))
            inserted += 1
        print(f"[OK]   补 SysRoleMenu x {inserted} 角色（共候选 {len(roles)}）")

        conn.commit()
        print("\n[DONE] 菜单入库完成。请在前端 ctrl-shift-R 刷新或重新登录以加载。")

    except Exception as ex:
        conn.rollback()
        print(f"回滚：{ex}")
        raise
    finally:
        cur.close()
        conn.close()


if __name__ == "__main__":
    main()