"""
系统管理服务层
从 system_view.py 提取的SQL查询逻辑
"""
import sys
import os

# 添加src目录到Python路径
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '../..'))
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '../../..'))

import logging
import uuid
import json
import secrets
import hashlib
from typing import Optional, List, Dict, Any, Tuple
from datetime import datetime, timezone
from app.base.async_mysql_connection import get_db_connection

logger = logging.getLogger(__name__)


class SystemService:
    """系统管理服务类 - 使用 SQL 查询方式"""
    
    def __init__(self):
        """初始化服务"""
        pass
    
    # ==================== 用户资料管理 ====================
    
    async def get_user_profile(self, user_id: str) -> Optional[Dict[str, Any]]:
        """获取用户资料"""
        conn = get_db_connection()
        if not conn:
            return None
        
        cursor = conn.cursor()
        
        try:
            # 查找用户详细信息
            cursor.execute("""
                SELECT u.id, u.username, u.email, u.phone, u.avatar_url, u.is_active, u.is_superuser,
                       u.last_login_at, u.created_time, u.updated_time,
                       p.real_name, p.company, p.department, p.position
                FROM t_sys_user u
                LEFT JOIN t_sys_user_profile p ON u.id = p.user_id
                WHERE u.id = %s
            """, (user_id,))
            
            user_data = cursor.fetchone()
            
            if not user_data:
                return None
            
            # 获取用户角色
            cursor.execute("""
                SELECT r.name
                FROM t_sys_user_role ur
                JOIN t_sys_role r ON ur.role_id = r.id
                WHERE ur.user_id = %s AND ur.is_active = 1
            """, (user_id,))
            
            roles = [row['name'] for row in cursor.fetchall()]
            
            # 构建用户信息
            user_info = {
                "id": user_data["id"],
                "username": user_data["username"],
                "email": user_data["email"],
                "phone": user_data["phone"],
                "avatar_url": user_data["avatar_url"],
                "is_active": user_data["is_active"],
                "is_superuser": user_data["is_superuser"],
                "last_login_at": user_data["last_login_at"].isoformat() if user_data["last_login_at"] else None,
                "created_time": user_data["created_time"].isoformat() if user_data["created_time"] else None,
                "updated_time": user_data["updated_time"].isoformat() if user_data["updated_time"] else None,
                "real_name": user_data["real_name"],
                "company": user_data["company"],
                "department": user_data["department"],
                "position": user_data["position"],
                "roles": roles
            }
            
            return user_info
        finally:
            cursor.close()
            conn.close()
    
    async def update_user_profile(self, user_id: str, profile_data: Dict[str, Any]) -> Tuple[bool, str]:
        """更新用户资料"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 更新用户基本信息
            update_fields = []
            update_values = []
            
            if 'email' in profile_data:
                update_fields.append('email = %s')
                update_values.append(profile_data['email'])
            
            if 'phone' in profile_data:
                update_fields.append('phone = %s')
                update_values.append(profile_data['phone'])
            
            if update_fields:
                update_values.append(user_id)
                cursor.execute(f"""
                    UPDATE t_sys_user 
                    SET {', '.join(update_fields)}, updated_time = NOW()
                    WHERE id = %s
                """, update_values)
            
            # 更新或插入用户详情
            profile_fields = ['real_name', 'company', 'department', 'position']
            profile_updates = {k: v for k, v in profile_data.items() if k in profile_fields}
            
            if profile_updates:
                # 检查是否已有记录
                cursor.execute("SELECT id FROM t_sys_user_profile WHERE user_id = %s", (user_id,))
                profile_exists = cursor.fetchone()
                
                if profile_exists:
                    # 更新现有记录
                    update_fields = []
                    update_values = []
                    for field, value in profile_updates.items():
                        update_fields.append(f'{field} = %s')
                        update_values.append(value)
                    
                    update_values.append(user_id)
                    cursor.execute(f"""
                        UPDATE t_sys_user_profile 
                        SET {', '.join(update_fields)}, updated_time = NOW()
                        WHERE user_id = %s
                    """, update_values)
                else:
                    # 插入新记录
                    fields = ['user_id'] + list(profile_updates.keys())
                    values = [user_id] + list(profile_updates.values())
                    placeholders = ', '.join(['%s'] * len(values))
                    
                    cursor.execute(f"""
                        INSERT INTO t_sys_user_profile ({', '.join(fields)}, created_time, updated_time)
                        VALUES ({placeholders}, NOW(), NOW())
                    """, values)
            
            conn.commit()
            return True, "用户资料更新成功"
        except Exception as e:
            logger.exception("更新用户资料错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    async def change_user_password(self, user_id: str, old_password: str, new_password: str) -> Tuple[bool, str]:
        """修改用户密码"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 验证当前密码
            cursor.execute("SELECT password_hash FROM t_sys_user WHERE id = %s", (user_id,))
            result = cursor.fetchone()
            
            if not result or not self._verify_password(old_password, result["password_hash"]):
                return False, "当前密码错误"
            
            # 生成新密码哈希
            new_password_hash = self._hash_password(new_password)
            
            # 更新密码
            cursor.execute("""
                UPDATE t_sys_user 
                SET password_hash = %s, updated_time = NOW()
                WHERE id = %s
            """, (new_password_hash, user_id))
            
            conn.commit()
            return True, "密码修改成功"
        except Exception as e:
            logger.exception("修改密码错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    # ==================== RBAC权限管理 ====================
    
    async def get_user_menus(self, user_id: str) -> List[Dict[str, Any]]:
        """获取用户菜单"""
        conn = get_db_connection()
        if not conn:
            return []
        
        cursor = conn.cursor()
        
        try:
            # 检查用户是否是超级管理员
            cursor.execute("""
                SELECT COUNT(*) as count FROM t_sys_user_role ur
                JOIN t_sys_role r ON ur.role_id = r.id
                WHERE ur.user_id = %s AND r.name = 'super_admin' AND ur.is_active = 1
            """, (user_id,))
            
            result = cursor.fetchone()
            is_super_admin = result['count'] > 0 if result else False
            
            if is_super_admin:
                # 超级管理员返回所有活跃菜单
                cursor.execute("""
                    SELECT m.id, m.parent_id, m.name, m.title, m.path, 
                           m.component, m.icon, m.sort_order, m.menu_type, 
                           m.is_hidden, m.is_active
                    FROM t_sys_menu m
                    WHERE m.is_active = 1
                    ORDER BY m.sort_order
                """)
            else:
                # 普通用户根据角色权限获取菜单
                cursor.execute("""
                    SELECT m.id, m.parent_id, m.name, m.title, m.path, 
                           m.component, m.icon, m.sort_order, m.menu_type, 
                           m.is_hidden, m.is_active
                    FROM t_sys_menu m
                    JOIN t_sys_role_menu rm ON m.id = rm.menu_id
                    JOIN t_sys_user_role ur ON rm.role_id = ur.role_id
                    WHERE ur.user_id = %s 
                    AND ur.is_active = 1
                    AND m.is_active = 1
                    GROUP BY m.id, m.parent_id, m.name, m.title, m.path, 
                             m.component, m.icon, m.sort_order, m.menu_type, 
                             m.is_hidden, m.is_active
                    ORDER BY m.sort_order
                """, (user_id,))
            
            menus = []
            for row in cursor.fetchall():
                # 过滤掉不想要的菜单
                if "文档处理中心" in str(row['title']):
                    continue
                    
                menu = {
                    "id": row['id'],
                    "parent_id": row['parent_id'],
                    "name": row['name'],
                    "title": row['title'],
                    "path": row['path'],
                    "component": row['component'],
                    "icon": row['icon'],
                    "sort_order": row['sort_order'],
                    "menu_type": row['menu_type'],
                    "is_hidden": bool(row['is_hidden']),
                    "is_active": bool(row['is_active']),
                    "children": []
                }
                menus.append(menu)
            
            # 构建菜单树前，过滤掉 button 类型的项，侧边栏只显示 menu 类型
            sidebar_menus = [m for m in menus if m.get("menu_type") == "menu"]
            menu_tree = self._build_menu_tree(sidebar_menus)
            
            return menu_tree
        finally:
            cursor.close()
            conn.close()
    
    async def get_all_menus(self, page: int, page_size: int, keyword: Optional[str] = None) -> Tuple[List[Dict[str, Any]], int]:
        """获取所有菜单（管理员）"""
        conn = get_db_connection()
        if not conn:
            return [], 0
        
        cursor = conn.cursor()
        
        try:
            # 构建查询条件
            where_conditions = []
            params = []
            
            if keyword:
                where_conditions.append("(m.title LIKE %s OR m.name LIKE %s)")
                params.extend([f"%{keyword}%", f"%{keyword}%"])
            
            where_clause = " AND ".join(where_conditions) if where_conditions else "1=1"
            
            # 查询总数
            cursor.execute(f"SELECT COUNT(*) as count FROM t_sys_menu m WHERE {where_clause}", params)
            total = cursor.fetchone()['count']
            
            # 查询菜单列表
            cursor.execute(f"""
                SELECT m.id, m.parent_id, m.name, m.title, m.path, m.component,
                       m.icon, m.sort_order, m.menu_type, m.is_hidden, m.is_active,
                       m.description, m.created_time, m.updated_time,
                       pm.title as parent_title
                FROM t_sys_menu m
                LEFT JOIN t_sys_menu pm ON m.parent_id = pm.id
                WHERE {where_clause}
                ORDER BY 
                    CASE WHEN m.parent_id IS NULL THEN 0 ELSE 1 END,
                    m.sort_order, 
                    CASE WHEN m.menu_type = 'menu' THEN 0 ELSE 1 END,
                    m.created_time
                LIMIT %s OFFSET %s
            """, params + [page_size, (page - 1) * page_size])
            
            menus = []
            for row in cursor.fetchall():
                menu = {
                    "id": row['id'],
                    "parent_id": row['parent_id'],
                    "name": row['name'],
                    "title": row['title'],
                    "path": row['path'],
                    "component": row['component'],
                    "icon": row['icon'],
                    "sort_order": row['sort_order'],
                    "menu_type": row['menu_type'],
                    "is_hidden": bool(row['is_hidden']),
                    "is_active": bool(row['is_active']),
                    "description": row['description'],
                    "created_time": row['created_time'].isoformat() if row['created_time'] else None,
                    "updated_time": row['updated_time'].isoformat() if row['updated_time'] else None,
                    "parent_title": row['parent_title']
                }
                menus.append(menu)
            
            return menus, total
        finally:
            cursor.close()
            conn.close()
    
    async def get_all_roles(self, page: int, page_size: int, keyword: Optional[str] = None) -> Tuple[List[Dict[str, Any]], int]:
        """获取所有角色"""
        conn = get_db_connection()
        if not conn:
            return [], 0
        
        cursor = conn.cursor()
        
        try:
            # 构建查询条件
            where_conditions = []
            params = []
            
            if keyword:
                where_conditions.append("(r.name LIKE %s OR r.code LIKE %s)")
                params.extend([f"%{keyword}%", f"%{keyword}%"])
            
            where_clause = " AND ".join(where_conditions) if where_conditions else "1=1"
            
            # 查询总数
            cursor.execute(f"SELECT COUNT(*) as count FROM t_sys_role r WHERE {where_clause}", params)
            total = cursor.fetchone()['count']
            
            # 查询角色列表
            offset = (page - 1) * page_size
            cursor.execute(f"""
                SELECT r.id, r.code, r.name, r.description, r.is_active,
                       r.is_system, r.created_time, r.updated_time,
                       COUNT(ur.user_id) as user_count
                FROM t_sys_role r
                LEFT JOIN t_sys_user_role ur ON r.id = ur.role_id AND ur.is_active = 1
                WHERE {where_clause}
                GROUP BY r.id
                ORDER BY r.is_system DESC, r.created_time
                LIMIT %s OFFSET %s
            """, params + [page_size, offset])
            
            roles = []
            for row in cursor.fetchall():
                role = {
                    "id": row['id'],
                    "name": row['code'],
                    "display_name": row['name'],
                    "description": row['description'],
                    "is_active": bool(row['is_active']),
                    "is_system": bool(row['is_system']),
                    "created_time": row['created_time'].isoformat() if row['created_time'] else None,
                    "updated_time": row['updated_time'].isoformat() if row['updated_time'] else None,
                    "user_count": row['user_count']
                }
                roles.append(role)
            
            return roles, total
        finally:
            cursor.close()
            conn.close()
    
    async def get_user_permissions(self, user_id: str) -> List[Dict[str, Any]]:
        """获取用户权限"""
        conn = get_db_connection()
        if not conn:
            return []
        
        cursor = conn.cursor()
        
        try:
            # 获取用户权限
            cursor.execute("""
                SELECT DISTINCT p.name, p.resource, p.action
                FROM t_sys_permission p
                JOIN t_sys_role_permission rp ON p.id = rp.permission_id
                JOIN t_sys_user_role ur ON rp.role_id = ur.role_id
                WHERE ur.user_id = %s 
                AND ur.is_active = 1
                AND p.is_active = 1
            """, (user_id,))
            
            permissions = []
            for row in cursor.fetchall():
                permissions.append({
                    "name": row['name'],
                    "resource": row['resource'],
                    "action": row['action']
                })
            
            return permissions
        finally:
            cursor.close()
            conn.close()
    
    async def get_all_roles_simple(self) -> List[Dict[str, Any]]:
        """获取所有角色（简化版，用于下拉选择）"""
        conn = get_db_connection()
        if not conn:
            return []
        
        cursor = conn.cursor()
        
        try:
            cursor.execute("""
                SELECT id, code, name, is_system, is_active
                FROM t_sys_role 
                WHERE is_active = 1
                ORDER BY is_system DESC, name
            """)
            
            roles = []
            for row in cursor.fetchall():
                roles.append({
                    "id": row['id'],
                    "code": row['code'],
                    "display_name": row['name'],
                    "is_system": bool(row['is_system']),
                    "is_active": bool(row['is_active'])
                })
            
            return roles
        finally:
            cursor.close()
            conn.close()
    
    # ==================== 角色管理 ====================
    
    async def create_role(self, role_data: Dict[str, Any], creator_id: str) -> Tuple[bool, str]:
        """创建角色"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查角色名是否已存在
            cursor.execute("SELECT id FROM t_sys_role WHERE name = %s", (role_data['name'],))
            if cursor.fetchone():
                return False, "角色名已存在"
            
            # 创建角色
            role_id = str(uuid.uuid4())
            cursor.execute("""
                INSERT INTO t_sys_role (id, code, name, description, is_active, is_system, created_by, created_time, updated_time)
                VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
            """, (role_id, role_data['code'], role_data['name'], role_data.get('description'), 
                  role_data.get('is_active', True), False, creator_id))
            
            conn.commit()
            return True, "角色创建成功"
        except Exception as e:
            logger.exception("创建角色错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    async def update_role(self, role_id: str, role_data: Dict[str, Any], updater_id: str) -> Tuple[bool, str]:
        """更新角色"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查是否为系统角色
            cursor.execute("SELECT is_system FROM t_sys_role WHERE id = %s", (role_id,))
            role = cursor.fetchone()
            if not role:
                return False, "角色不存在"
            
            if role["is_system"]:
                return False, "不能修改系统角色"
            
            # 更新角色
            update_fields = []
            update_values = []
            
            for field in ['name', 'description', 'is_active']:
                if field in role_data:
                    update_fields.append(f'{field} = %s')
                    update_values.append(role_data[field])
            
            if update_fields:
                update_values.extend([updater_id, role_id])
                cursor.execute(f"""
                    UPDATE t_sys_role 
                    SET {', '.join(update_fields)}, updated_by = %s, updated_time = NOW()
                    WHERE id = %s
                """, update_values)
            
            conn.commit()
            return True, "角色更新成功"
        except Exception as e:
            logger.exception("更新角色错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    async def delete_role(self, role_id: str) -> Tuple[bool, str]:
        """删除角色"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查是否为系统角色
            cursor.execute("SELECT is_system FROM t_sys_role WHERE id = %s", (role_id,))
            role = cursor.fetchone()
            if not role:
                return False, "角色不存在"
            
            if role["is_system"]:
                return False, "不能删除系统角色"
            
            # 检查是否有用户使用此角色
            cursor.execute("SELECT COUNT(*) as count FROM t_sys_user_role WHERE role_id = %s", (role_id,))
            if cursor.fetchone()['count'] > 0:
                return False, "该角色正在被使用，无法删除"
            
            # 删除角色相关数据
            cursor.execute("DELETE FROM t_sys_role_permission WHERE role_id = %s", (role_id,))
            cursor.execute("DELETE FROM t_sys_role_menu WHERE role_id = %s", (role_id,))
            cursor.execute("DELETE FROM t_sys_role WHERE id = %s", (role_id,))
            
            conn.commit()
            return True, "角色删除成功"
        except Exception as e:
            logger.exception("删除角色错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    # ==================== 角色菜单权限管理 ====================
    
    async def get_role_menus(self, role_id: str) -> Tuple[bool, Optional[Dict[str, Any]], str]:
        """获取角色的菜单权限"""
        conn = get_db_connection()
        if not conn:
            return False, None, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查角色是否存在
            cursor.execute("SELECT id, name FROM t_sys_role WHERE id = %s", (role_id,))
            role = cursor.fetchone()
            
            if not role:
                return False, None, "角色不存在"
            
            # 检查是否为超级管理员角色
            role_name = role["name"]
            is_super_admin_role = role_name == "super_admin"
            
            if is_super_admin_role:
                # 超级管理员默认拥有所有菜单权限
                cursor.execute("""
                    SELECT id, name, title, parent_id, menu_type
                    FROM t_sys_menu
                    WHERE is_active = 1
                    ORDER BY sort_order
                """)
                menu_permissions = cursor.fetchall()
            else:
                # 普通角色查询已分配的菜单权限
                cursor.execute("""
                    SELECT m.id, m.name, m.title, m.parent_id, m.menu_type
                    FROM t_sys_role_menu rm
                    JOIN t_sys_menu m ON rm.menu_id = m.id
                    WHERE rm.role_id = %s AND m.is_active = 1
                    ORDER BY m.sort_order
                """, (role_id,))
                menu_permissions = cursor.fetchall()
            
            # 构建返回数据
            menu_ids = [menu["id"] for menu in menu_permissions]
            menu_details = []
            
            for menu in menu_permissions:
                menu_details.append({
                    "id": menu["id"],
                    "name": menu["name"],
                    "title": menu["title"],
                    "parent_id": menu["parent_id"],
                    "menu_type": menu["menu_type"]
                })
            
            result = {
                "role_id": role_id,
                "role_name": role["name"],
                "menu_ids": menu_ids,
                "menu_details": menu_details,
                "total": len(menu_ids)
            }
            
            return True, result, "获取角色菜单权限成功"
        finally:
            cursor.close()
            conn.close()
    
    async def update_role_menus(self, role_id: str, menu_ids: List[str], updater_id: str) -> Tuple[bool, Optional[Dict[str, Any]], str]:
        """更新角色的菜单权限"""
        conn = get_db_connection()
        if not conn:
            return False, None, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查角色是否存在
            cursor.execute("SELECT id, name FROM t_sys_role WHERE id = %s", (role_id,))
            role = cursor.fetchone()
            
            if not role:
                return False, None, "角色不存在"
            
            # 检查是否为超级管理员角色
            role_name = role["name"]
            is_super_admin_role = role_name == "super_admin"
            
            if is_super_admin_role:
                return False, None, "超级管理员角色拥有全部权限，无需修改"
            
            # 验证菜单ID是否存在
            if menu_ids:
                placeholders = ','.join(['%s'] * len(menu_ids))
                cursor.execute(f"""
                    SELECT id FROM t_sys_menu 
                    WHERE id IN ({placeholders}) AND is_active = 1
                """, menu_ids)
                
                valid_menu_ids = [row['id'] for row in cursor.fetchall()]
                invalid_menu_ids = set(menu_ids) - set(valid_menu_ids)
                
                if invalid_menu_ids:
                    return False, None, f"无效的菜单ID: {', '.join(invalid_menu_ids)}"
            
            # 开始事务
            cursor.execute("START TRANSACTION")
            
            try:
                # 删除角色现有的菜单权限
                cursor.execute("DELETE FROM t_sys_role_menu WHERE role_id = %s", (role_id,))
                
                # 添加新的菜单权限
                if menu_ids:
                    values = [(role_id, menu_id) for menu_id in menu_ids]
                    cursor.executemany("""
                        INSERT INTO t_sys_role_menu (role_id, menu_id, created_by, created_time)
                        VALUES (%s, %s, %s, NOW())
                    """, [(role_id, menu_id, updater_id) for menu_id in menu_ids])
                
                # 提交事务
                conn.commit()
                
                result = {
                    "role_id": role_id,
                    "role_name": role["name"],
                    "menu_ids": menu_ids,
                    "updated_count": len(menu_ids)
                }
                
                return True, result, "角色菜单权限更新成功"
            except Exception as e:
                # 回滚事务
                conn.rollback()
                raise e
        except Exception as e:
            logger.exception("更新角色菜单权限错误")
            return False, None, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    # ==================== 菜单管理 ====================
    
    async def create_menu(self, menu_data: Dict[str, Any], creator_id: str) -> Tuple[bool, str]:
        """创建菜单"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查菜单名是否已存在
            cursor.execute("SELECT id FROM t_sys_menu WHERE name = %s", (menu_data['name'],))
            if cursor.fetchone():
                return False, "菜单标识已存在"
            
            # 创建菜单
            menu_id = str(uuid.uuid4())
            cursor.execute("""
                INSERT INTO t_sys_menu (id, parent_id, name, title, path, component, icon, 
                                 sort_order, menu_type, is_hidden, is_active, description, created_by, created_time, updated_time)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
            """, (
                menu_id, menu_data.get('parent_id'), menu_data['name'], menu_data['title'],
                menu_data.get('path'), menu_data.get('component'), menu_data.get('icon'),
                menu_data.get('sort_order', 0), menu_data.get('menu_type', 'menu'),
                menu_data.get('is_hidden', False), menu_data.get('is_active', True),
                menu_data.get('description'), creator_id
            ))
            
            conn.commit()
            return True, "菜单创建成功"
        except Exception as e:
            logger.exception("创建菜单错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    async def update_menu(self, menu_id: str, menu_data: Dict[str, Any], updater_id: str) -> Tuple[bool, str]:
        """更新菜单"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 更新菜单
            update_fields = []
            update_values = []
            
            for field in ['parent_id', 'title', 'path', 'component', 'icon', 'sort_order', 
                         'menu_type', 'is_hidden', 'is_active', 'description']:
                if field in menu_data:
                    update_fields.append(f'{field} = %s')
                    update_values.append(menu_data[field])
            
            if update_fields:
                update_values.extend([updater_id, menu_id])
                cursor.execute(f"""
                    UPDATE t_sys_menu 
                    SET {', '.join(update_fields)}, updated_by = %s, updated_time = NOW()
                    WHERE id = %s
                """, update_values)
            
            conn.commit()
            return True, "菜单更新成功"
        except Exception as e:
            logger.exception("更新菜单错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    async def delete_menu(self, menu_id: str) -> Tuple[bool, str]:
        """删除菜单"""
        conn = get_db_connection()
        if not conn:
            return False, "数据库连接失败"
        
        cursor = conn.cursor()
        
        try:
            # 检查是否有子菜单
            cursor.execute("SELECT COUNT(*) as count FROM t_sys_menu WHERE parent_id = %s", (menu_id,))
            if cursor.fetchone()['count'] > 0:
                return False, "该菜单下有子菜单，无法删除"
            
            # 删除菜单相关数据
            cursor.execute("DELETE FROM t_sys_role_menu WHERE menu_id = %s", (menu_id,))
            cursor.execute("DELETE FROM t_sys_menu WHERE id = %s", (menu_id,))
            
            conn.commit()
            return True, "菜单删除成功"
        except Exception as e:
            logger.exception("删除菜单错误")
            conn.rollback()
            return False, "服务器内部错误"
        finally:
            cursor.close()
            conn.close()
    
    # ==================== 辅助方法 ====================
    
    def _build_menu_tree(self, menus: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
        """构建菜单树结构"""
        menu_map = {menu["id"]: menu for menu in menus}
        tree = []
        
        for menu in menus:
            if menu["parent_id"] is None:
                tree.append(menu)
            else:
                parent = menu_map.get(menu["parent_id"])
                if parent:
                    parent["children"].append(menu)
        
        return tree
    
    def _hash_password(self, password: str) -> str:
        """简单的密码哈希"""
        # 生成盐值
        salt = secrets.token_hex(16)
        
        # 使用SHA256哈希
        password_hash = hashlib.sha256((password + salt).encode()).hexdigest()
        
        return f"sha256${salt}${password_hash}"
    
    def _verify_password(self, plain_password: str, hashed_password: str) -> bool:
        """
        验证密码
        支持两种格式：
        1. sha256$salt$hash - 自定义格式
        2. bcrypt格式 - 使用bcrypt验证
        """
        try:
            # 检查是否是自定义sha256格式
            if hashed_password.startswith("sha256$"):
                parts = hashed_password.split("$")
                if len(parts) == 3:
                    _, salt, stored_hash = parts
                    # 使用相同的盐值计算哈希
                    computed_hash = hashlib.sha256((plain_password + salt).encode()).hexdigest()
                    return computed_hash == stored_hash
            
            # 尝试bcrypt验证
            try:
                from passlib.context import CryptContext
                pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
                return pwd_context.verify(plain_password, hashed_password)
            except ImportError:
                # 如果没有passlib，尝试使用bcrypt
                try:
                    import bcrypt
                    return bcrypt.checkpw(plain_password.encode('utf-8'), hashed_password.encode('utf-8'))
                except ImportError:
                    pass
            
            return False
        except Exception as e:
            logger.exception("密码验证错误")
            return False
