# Developer:Joshua
# Time:2025/4/14 16:53
from flask import Flask, request, jsonify, make_response, render_template, redirect,send_file
from flask_sqlalchemy import SQLAlchemy
import json
import pymysql
from werkzeug.security import generate_password_hash, check_password_hash
import jwt
import datetime
from functools import wraps
from flask_cors import CORS
import mysql.connector
from cryptography.fernet import Fernet
import pandas as pd
import io

pymysql.install_as_MySQLdb()
app = Flask(__name__)
# 允许跨域且携带凭证
CORS(app, resources={
    r"/api/*": {
        "origins": ["http://localhost:*"],
        "supports_credentials": True
    }
})
# 数据库配置
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "ws001206",
    "database": "KC_database"
}

def get_db_connection():
    return mysql.connector.connect(**DB_CONFIG)

def get_client_ip(request):
    return request.headers.get('X-Forwarded-For', request.remote_addr)

def log_operation(user_id, operation_type, target_type, target_id, operation_status, old_value=None, new_value=None, ip_address=None):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO operation_log (user_id, operation_type, target_type, target_id, operation_status, old_value, new_value, ip_address, created_time)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            user_id,
            operation_type,
            target_type,
            target_id,
            operation_status,
            json.dumps(old_value) if old_value is not None else None,
            json.dumps(new_value) if new_value is not None else None,
            ip_address
        ))
        conn.commit()
    finally:
        cursor.close()
        conn.close()
'''
----------------------------------------------------------------------------------------------
'''
# 配置数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:ws001206@localhost/KC_database'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'you_will_never_guess'  # 生产环境中应该使用更安全的密钥

db = SQLAlchemy(app)


# 数据库模型
class Role(db.Model):
    __tablename__ = 'role'
    role_id = db.Column(db.Integer, primary_key=True)
    role_name = db.Column(db.String(20), nullable=False)
    permissions = db.Column(db.Text, nullable=False)


class User(db.Model):
    __tablename__ = 'user'
    user_id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(100), nullable=False)
    role_id = db.Column(db.Integer, db.ForeignKey('role.role_id'), nullable=False)
    managed_code_id = db.Column(db.Integer)
    managed_user_id = db.Column(db.Integer)

    role = db.relationship('Role', backref='users')


class KcCode(db.Model):
    __tablename__ = 'kc_code'
    code_id = db.Column(db.Integer, primary_key=True)
    node_id = db.Column(db.String(10))
    node_type = db.Column(db.Enum('root', 'level', 'leaf'), nullable=False)
    parent_id = db.Column(db.Integer, db.ForeignKey('kc_code.code_id'))
    zh_name = db.Column(db.String(100), nullable=False)
    en_name = db.Column(db.String(100), nullable=False)
    kc_code = db.Column(db.String(50), unique=True, nullable=False)
    version = db.Column(db.String(10), nullable=True)
    status = db.Column(db.Enum('pending', 'published', 'deprecated'), nullable=False, default='pending')
    created_time = db.Column(db.DateTime, server_default=db.func.now())
    updated_time = db.Column(db.DateTime, server_onupdate=db.func.now())

    parent = db.relationship('KcCode', remote_side=[code_id], backref='children')


# 装饰器用于验证token
def token_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        token = None

        # 从cookies中获取token
        if 'token' in request.cookies:
            token = request.cookies['token']

        if not token:
            return jsonify({'message': 'Token is missing!'}), 401

        try:
            data = jwt.decode(token, app.config['SECRET_KEY'], algorithms=["HS256"])
            current_user = User.query.filter_by(user_id=data['user_id']).first()
        except:
            return jsonify({'message': 'Token is invalid!'}), 401

        return f(current_user, *args, **kwargs)

    return decorated


def role_required(required_role):
    """
    角色权限验证装饰器
    用法：@role_required('超级管理员')
    """

    def decorator(f):
        @wraps(f)
        def decorated_function(current_user, *args, **kwargs):
            # 确保current_user是通过token_required获取的
            if not hasattr(current_user, 'role'):
                return jsonify({'success': False, 'message': '用户信息异常'}), 401

            # 验证角色权限
            if current_user.role.role_name != required_role:
                return jsonify({
                    'success': False,
                    'message': f'需要{required_role}权限'
                }), 403

            return f(current_user, *args, **kwargs)

        return decorated_function

    return decorator


# 登录路由
@app.route('/api/login', methods=['POST'])
def login():
    data = request.get_json()

    if not data or not data.get('username') or not data.get('password'):
        return jsonify({'success': False, 'message': '用户名和密码不能为空'}), 400

    user = User.query.filter_by(username=data['username']).first()
    if not user or not check_password_hash(user.password, data['password']):
        return jsonify({'success': False, 'message': '用户名或密码错误'}), 401

    # 生成JWT token
    token = jwt.encode({
        'user_id': user.user_id,
        'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=24)
    }, app.config['SECRET_KEY'])

    # 根据角色确定重定向URL
    role_name = user.role.role_name.lower()
    if role_name == 'super_admin':
        redirect_url = '/super_admin_dashboard'
    elif role_name == 'pole_admin':
        redirect_url = '/pole_admin_dashboard'
    elif role_name == 'operator':
        redirect_url = '/operator_dashboard'
    else:
        redirect_url = '/dashboard'

    # 创建响应对象
    response = make_response(jsonify({
        'success': True,
        'message': '登录成功',
        'user': {
            'user_id': user.user_id,
            'username': user.username,
            'role_id': user.role_id,
            'role_name': user.role.role_name,
            'managed_code_id': user.managed_code_id,
            'managed_user_id': user.managed_user_id
        },
        'redirect_to': redirect_url
    }))

    # 设置cookie
    response.set_cookie(
        'token',
        token,
        httponly=True,
        secure=False,  # 开发环境设置为False
        samesite='Lax',
        max_age=86400  # 24小时
    )

    return response

# 超级管理员仪表盘
@app.route('/super_admin_dashboard')
@token_required
def superadmin_dashboard(current_user):
    if current_user.role.role_name != 'super_admin':
        return jsonify({'message': '无权访问'}), 403
    return render_template('super_admin.html', user=current_user)

# 极点管理员仪表盘
@app.route('/pole_admin_dashboard')
@token_required
def poleadmin_dashboard(current_user):
    try:
        if not current_user:
            return jsonify({'message': '未找到用户信息'}), 401
            
        if not hasattr(current_user, 'role'):
            return jsonify({'message': '用户角色信息异常'}), 401
            
        if current_user.role.role_name != 'pole_admin':
            return jsonify({'message': '无权访问极点管理员页面'}), 403
            
        return render_template('pole_admin.html', user=current_user)
    except Exception as e:
        return jsonify({'message': f'访问极点管理员页面时发生错误: {str(e)}'}), 500

# 数据录入员仪表盘
@app.route('/operator_dashboard')
@token_required
def operator_dashboard(current_user):
    if current_user.role.role_name != 'operator':
        return jsonify({'message': '无权访问'}), 403
    return render_template('operator.html', user=current_user)


# 查询KC码路由
@app.route('/api/approve_kc_code', methods=['GET'])
@token_required
def get_kc_codes(current_user):
    """KC码审批接口
    
    功能：
    1. 查询KC码（支持分页和状态筛选）
    2. 返回KC码状态统计信息
    """
    # 获取查询参数
    page = request.args.get('page', 1, type=int)
    per_page = 10  # 每页显示10条记录
    status = request.args.get('status', 'all')  # 状态筛选：all, pending, published, deprecated
    search = request.args.get('search', '').strip()  # 搜索关键字
    
    try:
        # 构建基础查询
        query = KcCode.query
        
        # 应用状态筛选
        if status != 'all':
            query = query.filter_by(status=status)
        
        # 应用搜索条件
        if search:
            query = query.filter(
                (KcCode.zh_name.like(f'%{search}%')) |
                (KcCode.en_name.like(f'%{search}%')) |
                (KcCode.kc_code.like(f'%{search}%'))
            )
        
        # 获取状态统计
        stats = {
            'pending': KcCode.query.filter_by(status='pending').count(),
            'published': KcCode.query.filter_by(status='published').count(),
            'deprecated': KcCode.query.filter_by(status='deprecated').count()
        }
        
        # 分页查询
        pagination = query.paginate(page=page, per_page=per_page)
        
        # 构建返回数据
        kc_codes = []
        for code in pagination.items:
            kc_codes.append({
                'code_id': code.code_id,
                'node_id': code.node_id,
                'node_type': code.node_type,
                'zh_name': code.zh_name,
                'en_name': code.en_name,
                'kc_code': code.kc_code,
                'version': code.version,
                'status': code.status,
                'created_time': code.created_time.strftime('%Y-%m-%d %H:%M:%S'),
                'updated_time': code.updated_time.strftime('%Y-%m-%d %H:%M:%S') if code.updated_time else None
            })
        
        return jsonify({
            'success': True,
            'data': {
                'kc_codes': kc_codes,
                'stats': stats,
                'pagination': {
                    'total': pagination.total,
                    'pages': pagination.pages,
                    'current': pagination.page,
                    'per_page': pagination.per_page
                }
            }
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'查询失败: {str(e)}'
        }), 500

@app.route('/api/approve_kc_code', methods=['POST'])
@token_required
def update_kc_status(current_user):
    """更新KC码状态接口
    
    功能：
    1. 发布KC码（pending -> published）
    2. 废止KC码（published -> deprecated）
    3. 递归处理子节点
    """
    data = request.get_json()
    if not data or not data.get('code_id') or not data.get('action'):
        return jsonify({
            'success': False,
            'message': 'KC码ID和操作类型不能为空'
        }), 400
    
    code_id = data['code_id']
    action = data['action']  # 'publish' 或 'deprecate'
    version = data.get('version')  # 获取版本号（可选）
    
    try:
        # 查找KC码
        kc_code = KcCode.query.get(code_id)
        if not kc_code:
            return jsonify({
                'success': False,
                'message': 'KC码不存在'
            }), 404
        
        # 验证状态转换的合法性
        if action == 'publish' and kc_code.status != 'pending':
            return jsonify({
                'success': False,
                'message': '只能发布待发布状态的KC码'
            }), 400
        elif action == 'deprecate' and kc_code.status != 'published':
            return jsonify({
                'success': False,
                'message': '只能废止已发布状态的KC码'
            }), 400
        
        # 递归更新节点及其子节点的状态
        def update_node_and_children(node, new_status, new_version=None):
            # 更新当前节点状态
            node.status = new_status
            if new_version and node.node_type == 'root':
                node.version = new_version
            
            # 递归更新所有子节点
            for child in node.children:
                update_node_and_children(child, new_status, new_version)
        
        # 执行更新
        if action == 'publish':
            if kc_code.node_type == 'root' and not version:
                return jsonify({
                    'success': False,
                    'message': '根节点发布时必须提供版本号'
                }), 400
            update_node_and_children(kc_code, 'published', version)
        else:  # deprecate
            update_node_and_children(kc_code, 'deprecated')
        
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': '状态更新成功',
            'data': {
                'code_id': kc_code.code_id,
                'new_status': kc_code.status
            }
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': f'更新失败: {str(e)}'
        }), 500

@app.route('/api/update_version', methods=['POST'])
@token_required
@role_required('super_admin')
def update_version(current_user):
    """更新根节点版本号接口
    
    功能：
    1. 更新根节点的版本号
    2. 不影响已发布子节点的版本号
    """
    data = request.get_json()
    if not data or not data.get('code_id') or not data.get('version'):
        return jsonify({
            'success': False,
            'message': 'KC码ID和版本号不能为空'
        }), 400
    
    code_id = data['code_id']
    new_version = data['version']
    
    try:
        # 查找KC码
        kc_code = KcCode.query.get(code_id)
        if not kc_code:
            return jsonify({
                'success': False,
                'message': 'KC码不存在'
            }), 404
        
        # 验证是否为根节点
        if kc_code.node_type != 'root':
            return jsonify({
                'success': False,
                'message': '只能修改根节点的版本号'
            }), 400
        
        # 更新版本号
        kc_code.version = new_version
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': '版本号更新成功',
            'data': {
                'code_id': kc_code.code_id,
                'new_version': kc_code.version
            }
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': f'更新失败: {str(e)}'
        }), 500

# 登录页面
@app.route('/login')
def login_page():
    return render_template('login.html')

# 根路径重定向到登录页面
@app.route('/')
def index():
    return redirect('/login')

# 仪表盘页面
@app.route('/dashboard')
@token_required
def dashboard(current_user):
    return f"欢迎, {current_user.username}! 这是仪表盘页面。"


# 查询极点管理员
@app.route('/api/manage_pole_admin', methods=['GET'])
@token_required
def get_pole_admin(current_user):
    """管理极点管理员接口
    
    功能：
    1. 查询极点管理员（支持模糊搜索和分页）
    2. 返回管理员信息及其管理的极点KC码
    """
    if current_user.role.role_name != 'super_admin':
        return jsonify({'success': False, 'message': '无权访问此功能'}), 403
    
    username = request.args.get('username', '').strip()
    page = request.args.get('page', 1, type=int)
    per_page = 10  # 每页显示10条记录
    
    try:
        # 构建查询
        query = db.session.query(
            User.user_id,
            User.username,
            User.password,
            User.managed_code_id,
            KcCode.kc_code
        ).join(
            KcCode,
            User.managed_code_id == KcCode.code_id,
            isouter=True
        ).filter(
            User.role_id == 2  # 极点管理员角色ID为2
        )
        
        # 如果提供了用户名，添加模糊搜索条件
        if username:
            query = query.filter(User.username.like(f'%{username}%'))
        
        # 执行分页查询
        pagination = query.paginate(page=page, per_page=per_page)
        
        # 构建返回数据
        admins = []
        for result in pagination.items:
            # 解密密码
            # try:
            #     key = b'your-secret-key-here'  # 使用与加密时相同的密钥
            #     f = Fernet(key)
            #     decrypted_password = f.decrypt(result.password.encode()).decode()
            # except:
            #     decrypted_password = result.password  # 如果解密失败，返回原始密码
            
            admin = {
                'user_id': result.user_id,
                'username': result.username,
                'password': result.password,  # 返回解密后的密码
                'managed_code_id': result.managed_code_id,
                'managed_kc_code': result.kc_code if result.kc_code else '未分配'
            }
            admins.append(admin)
        
        return jsonify({
            'success': True,
            'data': admins,
            'pagination': {
                'page': page,
                'pages': pagination.pages,
                'total': pagination.total
            }
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'查询失败: {str(e)}'
        }), 500


@app.route('/api/root_nodes', methods=['GET'])
@token_required
def get_root_nodes(current_user):
    """获取所有根节点列表"""
    try:
        # 查询所有根节点
        root_nodes = KcCode.query.filter_by(node_type='root').all()
        
        # 构建返回数据
        nodes = []
        for node in root_nodes:
            nodes.append({
                'code_id': node.code_id,
                'node_id': node.node_id,
                'zh_name': node.zh_name,
                'en_name': node.en_name,
                'kc_code': node.kc_code,
                'version': node.version,
                'status': node.status
            })
        
        return jsonify({
            'success': True,
            'data': nodes
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取根节点列表失败: {str(e)}'
        }), 500

@app.route('/api/add_node', methods=['POST'])
@token_required
@role_required('super_admin')
def add_node(current_user):
    """添加节点接口
    
    功能：
    1. 添加根节点
    2. 添加极点节点
    3. 为极点节点创建管理员
    """
    data = request.get_json()
    
    # 验证必填字段
    required_fields = ['node_type', 'zh_name', 'en_name', 'kc_code']
    for field in required_fields:
        if field not in data:
            return jsonify({
                'success': False,
                'message': f'缺少必填字段: {field}'
            }), 400
    
    # 验证节点类型
    if data['node_type'] not in ['root', 'level']:
        return jsonify({
            'success': False,
            'message': '无效的节点类型'
        }), 400
    
    # 检查KC码是否已存在
    existing_code = KcCode.query.filter_by(kc_code=data['kc_code']).first()
    if existing_code:
        return jsonify({
            'success': False,
            'message': 'KC码已存在'
        }), 400
    
    try:
        # 创建新节点
        new_node = KcCode(
            node_type=data['node_type'],
            zh_name=data['zh_name'],
            en_name=data['en_name'],
            kc_code=data['kc_code'],
            version=data.get('version'),  # 使用get方法获取version，如果不存在则返回None
            status='pending'
        )
        
        # 如果是极点节点，需要设置父节点ID
        if data['node_type'] == 'level':
            if 'parent_id' not in data:
                return jsonify({
                    'success': False,
                    'message': '极点节点必须指定父节点ID'
                }), 400
            
            # 验证父节点是否存在且为根节点
            parent = KcCode.query.get(data['parent_id'])
            if not parent:
                return jsonify({
                    'success': False,
                    'message': '父节点不存在'
                }), 404
            if parent.node_type != 'root':
                return jsonify({
                    'success': False,
                    'message': '父节点必须是根节点'
                }), 400
            
            new_node.parent_id = data['parent_id']
            
            # 验证管理员信息
            if 'admin_username' not in data or 'admin_password' not in data:
                return jsonify({
                    'success': False,
                    'message': '极点节点必须提供管理员信息'
                }), 400
            
            # 检查管理员用户名是否已存在
            existing_admin = User.query.filter_by(username=data['admin_username']).first()
            if existing_admin:
                return jsonify({
                    'success': False,
                    'message': f'极点管理员用户名 {data["admin_username"]} 已存在'
                }), 400
        
        # 添加节点
        db.session.add(new_node)
        db.session.flush()  # 获取新节点的ID
        
        # 如果是极点节点，创建对应的极点管理员
        if data['node_type'] == 'level':
            # 创建极点管理员
            new_admin = User(
                username=data['admin_username'],
                password=generate_password_hash(data['admin_password']),
                role_id=2,  # 极点管理员角色ID
                managed_code_id=new_node.code_id,
                managed_user_id=1  # 超级管理员ID
            )
            db.session.add(new_admin)
        
        # 提交事务
        db.session.commit()
        
        # 构建返回消息
        message = f'{"根" if data["node_type"] == "root" else "极点"}节点添加成功'
        if data['node_type'] == 'level':
            message += f'，极点管理员创建成功'
        
        return jsonify({
            'success': True,
            'message': message,
            'data': {
                'code_id': new_node.code_id,
                'node_type': new_node.node_type,
                'zh_name': new_node.zh_name,
                'en_name': new_node.en_name,
                'kc_code': new_node.kc_code,
                'version': new_node.version,
                'status': new_node.status
            }
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': f'添加节点失败: {str(e)}'
        }), 500

# 删除极点管理员及其管理的极点
@app.route('/api/manage_pole_admin/<int:user_id>', methods=['DELETE'])
@token_required
def delete_pole_admin(current_user, user_id):
    if current_user.role.role_name != 'super_admin':
        return jsonify({'success': False, 'message': '无权访问此功能'}), 403
    
    try:
        # 查询极点管理员
        admin = User.query.filter_by(user_id=user_id, role_id=2).first()
        if not admin:
            return jsonify({'success': False, 'message': '极点管理员不存在'}), 404
        
        # 查询管理的极点
        pole = KcCode.query.get(admin.managed_code_id)
        if not pole:
            return jsonify({'success': False, 'message': '管理的极点不存在'}), 404
        
        # 删除极点管理员
        db.session.delete(admin)
        # 删除管理的极点
        db.session.delete(pole)
        
        db.session.commit()
        
        return jsonify({
            'success': True,
            'message': '极点管理员及其管理的极点已删除'
        })
        
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': f'删除失败: {str(e)}'
        }), 500

# 获取节点结构
@app.route('/api/node_structure', methods=['GET'])
@token_required
def get_node_structure(current_user):
    """获取节点结构接口
    
    功能：
    1. 获取所有节点的树状结构
    2. 返回包含节点层级关系的完整结构
    """
    if current_user.role.role_name != 'super_admin':
        return jsonify({'success': False, 'message': '无权访问此功能'}), 403
    
    try:
        # 获取所有节点
        nodes = KcCode.query.all()
        
        # 构建节点字典，用于快速查找
        node_dict = {}
        for node in nodes:
            node_dict[node.code_id] = {
                'id': node.code_id,
                'node_id': node.node_id,
                'node_type': node.node_type,
                'zh_name': node.zh_name,
                'en_name': node.en_name,
                'kc_code': node.kc_code,
                'version': node.version,
                'status': node.status,
                'children': []
            }
        
        # 构建树状结构
        root_nodes = []
        for node in nodes:
            node_data = node_dict[node.code_id]
            if node.parent_id is None:
                # 根节点
                root_nodes.append(node_data)
            else:
                # 将节点添加到父节点的children中
                parent = node_dict.get(node.parent_id)
                if parent:
                    parent['children'].append(node_data)
        
        return jsonify({
            'success': True,
            'data': root_nodes
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取节点结构失败: {str(e)}'
        }), 500
        
'''
----------------------------------------------------------------------------------------------
'''
# 极点管理员KC码查询接口
@app.route('/api/poleadmin/kc_codes', methods=['GET'])
@token_required
@role_required('pole_admin')
def get_poleadmin_kc_codes(current_user):
    # 获取查询参数
    page = request.args.get('page', 1, type=int) # 从请求的URL中获取当前请求的页码，默认为1
    print(request.args)
    per_page = 3 # 定义每页显示的记录数为3
    search = request.args.get('search', '').strip() # 从请求的URL中获取搜索关键字，并去除首尾空格，用于过滤KC码的名称或代码
    status_filter = request.args.get('status', 'all') # 从请求的URL中获取KC码状态过滤条件，默认为'all'

    # 构建递归查询
    managed_nodes = db.session.query(KcCode.code_id, KcCode.status).filter(
        (KcCode.parent_id == current_user.managed_code_id)
    ).cte(name='managed_nodes', recursive=True)

    managed_nodes = managed_nodes.union_all(
        db.session.query(KcCode.code_id, KcCode.status).filter(
            KcCode.parent_id == managed_nodes.c.code_id
        )
    )

    # 主查询
    query = KcCode.query.join(
        managed_nodes,
        KcCode.code_id == managed_nodes.c.code_id
    )

    # 应用状态筛选
    if status_filter != 'all':
        query = query.filter_by(status=status_filter)
        print(status_filter)

    # 应用搜索条件
    if search:
        query = query.filter(
            (KcCode.zh_name.contains(search)) |
            (KcCode.en_name.contains(search)) |
            (KcCode.kc_code.contains(search))
        )

    # 分页查询
    pagination = query.paginate(page=page, per_page=per_page)

    # 构建响应数据
    kc_codes = []
    for code in pagination.items:
        kc_codes.append({
            'id': code.code_id,
            'zh_name': code.zh_name,
            'en_name': code.en_name,
            'kc_code': code.kc_code,
            'status': code.status,
            'can_approve': code.status == 'pending'
        })

    return jsonify({
        'success': True,
        'data': kc_codes,
        'pagination': {
            'total': pagination.total, # 总记录数
            'pages': pagination.pages, # 总页数
            'current': pagination.page, # 当前页码
            'per_page': pagination.per_page # 每页记录数
        }
    })


# 极点管理员KC码审核接口
@app.route('/api/kc_codes/<int:code_id>/approve', methods=['POST'])
@token_required
@role_required('pole_admin')
def approve_kc_code(current_user,code_id):
    try:
        code = KcCode.query.get_or_404(code_id)

        if code.status != 'pending':
            return jsonify({
                'success': False,
                'message': '只能审核待审批状态的KC码',
                'code': 'invalid_status'
            }), 400

        # 更新状态
        code.status = 'published'
        db.session.commit()

        return jsonify({
            'success': True,
            'message': '审核成功',
            'data': {
                'code_id': code.code_id,
                'new_status': 'published'
            }
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"审核失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': '服务器内部错误',
            'code': 'server_error'
        }), 500

# 极点管理员KC码废止接口
@app.route('/api/kc_codes/<int:code_id>/deprecate', methods=['POST'])
@token_required
@role_required('pole_admin')
def deprecate_kc_code(current_user, code_id):
    try:
        code = KcCode.query.get_or_404(code_id)

        if code.status != 'published':
            return jsonify({
                'success': False,
                'message': '只能废止已发布状态的KC码',
                'code': 'invalid_status'
            }), 400

        # 更新状态并记录操作信息
        code.status = 'deprecated'
        # code.updated_by = current_user.user_id
        # code.updated_at = datetime.utcnow()

        db.session.commit()

        return jsonify({
            'success': True,
            'message': '废止成功',
            'data': {
                'code_id': code.code_id,
                'new_status': 'deprecated'
            }
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"废止失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': '服务器内部错误'
        }), 500




# 查询数据录入员（带分页）
@app.route('/api/poleadmin/data_operators', methods=['GET'])
@token_required
@role_required('pole_admin')
def get_data_operators(current_user):
    page = request.args.get('page', 1, type=int)
    per_page = 3  # 每页3条
    search = request.args.get('search', '').strip()

    query = User.query.filter_by(
        role_id=3,  # 数据录入员
        managed_user_id=current_user.user_id
    )


    if search:
        query = query.filter(User.username.contains(search))

    pagination = query.paginate(page=page, per_page=per_page)

    return jsonify({
        'success': True,
        'data': [{
            'user_id': op.user_id,
            'username': op.username,
            'role_id': op.role_id,
            'role_name': op.role.role_name,
        } for op in pagination.items],
        'pagination': {
            'total': pagination.total,
            'pages': pagination.pages,
            'current': pagination.page,
            'per_page': pagination.per_page
        }
    })


# 新增数据录入员
@app.route('/api/poleadmin/data_operators', methods=['POST'])
@token_required
@role_required('pole_admin')
def add_data_operator(current_user):
    data = request.get_json()
    try:
        if User.query.filter_by(username=data['username']).first():
            return jsonify({
                'success': False,
                'message': '用户名已存在'
            }), 400

        new_operator = User(
            username=data['username'],
            password=generate_password_hash(data['password']),
            role_id=3,
            managed_user_id=current_user.user_id
        )
        db.session.add(new_operator)
        db.session.commit()

        return jsonify({
            'success': True,
            'message': '新增成功',
            'data': {
                'user_id': new_operator.user_id
            }
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': '创建失败: ' + str(e)
        }), 400


@app.route('/api/poleadmin/data_operators/<int:user_id>', methods=['PUT'])
@token_required
@role_required('pole_admin')
def update_data_operator(current_user, user_id):
    data = request.get_json()

    # 验证请求数据
    if 'password' not in data or not data['password']:
        return jsonify({
            'success': False,
            'message': '必须提供新密码'
        }), 400

    # 查询要修改的用户（确保属于当前管理员管理）
    operator = User.query.filter_by(
        user_id=user_id,
        managed_user_id=current_user.user_id,  # 确保属于当前管理员管理的节点
        role_id=3  # 确保是数据录入员角色
    ).first_or_404()

    try:
        # 只更新密码字段
        operator.password = generate_password_hash(data['password'])
        # operator.updated_at = datetime.utcnow()  # 记录更新时间

        db.session.commit()
        return jsonify({
            'success': True,
            'message': '密码修改成功'
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': '密码更新失败: ' + str(e)
        }), 500


@app.route('/api/superadmin/data_operators/<int:user_id>', methods=['PUT'])
@token_required
@role_required('super_admin')
def update_data_admin(current_user, user_id):
    data = request.get_json()

    # 验证请求数据
    if 'password' not in data or not data['password']:
        return jsonify({
            'success': False,
            'message': '必须提供新密码'
        }), 400

    # 查询要修改的用户（确保属于当前管理员管理）
    pole = User.query.filter_by(
        user_id=user_id,
        managed_user_id=current_user.user_id,  # 确保属于当前管理员管理的节点
        role_id=2  # 确保是数据录入员角色
    ).first_or_404()

    try:
        # 只更新密码字段
        pole.password = generate_password_hash(data['password'])
        # operator.updated_at = datetime.utcnow()  # 记录更新时间

        db.session.commit()
        return jsonify({
            'success': True,
            'message': '密码修改成功'
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': '密码更新失败: ' + str(e)
        }), 500

# 删除数据录入员
@app.route('/api/poleadmin/data_operators/<int:user_id>', methods=['DELETE'])
@token_required
@role_required('pole_admin')
def delete_data_operator(current_user, user_id):
    operator = User.query.filter_by(
        user_id=user_id,
        managed_user_id=current_user.user_id
    ).first_or_404()

    try:
        db.session.delete(operator)
        db.session.commit()
        return jsonify({'success': True, 'message': '删除成功'})
    except Exception as e:
        db.session.rollback()
        return jsonify({
            'success': False,
            'message': '删除失败: ' + str(e)
        }), 400


@app.route('/api/poleadmin/root_nodes', methods=['GET'])
@token_required
def get_nodes(current_user):
    """获取所有根节点列表"""
    try:
        # 查询所有根节点
        root_nodes = KcCode.query.filter_by(code_id=current_user.managed_code_id).all()
        
        # 构建返回数据
        nodes = []
        for node in root_nodes:
            nodes.append({
                'code_id': node.code_id,
                'node_id': node.node_id,
                'zh_name': node.zh_name,
                'en_name': node.en_name,
                'kc_code': node.kc_code,
                'version': node.version,
                'status': node.status
            })
        
        return jsonify({
            'success': True,
            'data': nodes
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取根节点列表失败: {str(e)}'
        }), 500
        
        
        
@app.route('/api/poleadmin/node_structure', methods=['GET'])
@token_required
def get_node_structure_pole(current_user):
    """获取节点结构接口
    
    功能：
    1. 获取所有节点的树状结构
    2. 返回包含节点层级关系的完整结构
    """
    if current_user.role.role_name != 'pole_admin':
        return jsonify({'success': False, 'message': '无权访问此功能'}), 403
    
    try:
        # 递归获取所有节点
        managed_nodes = db.session.query(KcCode.code_id, KcCode.status).filter(
            ((KcCode.parent_id == current_user.managed_code_id)|(KcCode.code_id == current_user.managed_code_id))
        ).cte(name='managed_nodes', recursive=True)

        managed_nodes = managed_nodes.union_all(
            db.session.query(KcCode.code_id, KcCode.status).filter(
                KcCode.parent_id == managed_nodes.c.code_id
            )
        )

        # 主查询
        query = KcCode.query.join(
            managed_nodes,
            KcCode.code_id == managed_nodes.c.code_id
        )
        
        nodes = query.all()
        
        # 构建节点字典，用于快速查找
        node_dict = {}
        for node in nodes:
            print(node)
            node_dict[node.code_id] = {
                'id': node.code_id,
                'node_id': node.node_id,
                'node_type': node.node_type,
                'zh_name': node.zh_name,
                'en_name': node.en_name,
                'kc_code': node.kc_code,
                'version': node.version,
                'status': node.status,
                'children': []
            }
        
        # 构建树状结构
        root_nodes = []
        
        for node in nodes:
            node_data = node_dict[node.code_id]
            if node.node_type == 'level':
                # 根节点
                root_nodes.append(node_data)
            else:
                # 将节点添加到父节点的children中
                parent = node_dict.get(node.parent_id)
                if parent:
                    parent['children'].append(node_data)
        
        return jsonify({
            'success': True,
            'data': root_nodes
        })
        
    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取节点结构失败: {str(e)}'
        }), 500
        
        
'''
----------------------------------------------------------------------------------------------
'''
# 选择父节点
@app.route('/kc_code/list_for_user', methods=['GET'])
@token_required
def list_for_user(current_user):
    user_id = current_user.user_id
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 第一步：获取当前用户隶属的极点管理员 user_id
        cursor.execute("SELECT managed_user_id FROM user WHERE user_id = %s", (user_id,))
        row = cursor.fetchone()
        if not row or not row['managed_user_id']:
            return jsonify({'success': False, 'message': '未绑定极点管理员'}), 404

        managed_user_id = row['managed_user_id']

        # 第二步：用该极点管理员查找其 managed_code_id
        cursor.execute("SELECT managed_code_id FROM user WHERE user_id = %s", (managed_user_id,))
        row2 = cursor.fetchone()
        if not row2 or not row2['managed_code_id']:
            return jsonify({'success': False, 'message': '极点管理员未绑定管理节点'}), 404

        managed_code_id = row2['managed_code_id']

        # 第三步：从该 code_id 开始做递归查询
        cursor.execute("""
            WITH RECURSIVE node_tree AS (
                SELECT code_id, kc_code, zh_name, node_type, parent_id
                FROM kc_code
                WHERE code_id = %s
                UNION ALL
                SELECT kc.code_id, kc.kc_code, kc.zh_name, kc.node_type, kc.parent_id
                FROM kc_code kc
                INNER JOIN node_tree nt ON kc.parent_id = nt.code_id
            )
            SELECT * FROM node_tree;
        """, (managed_code_id,))

        nodes = cursor.fetchall()

        return jsonify({'success': True, 'nodes': nodes})

    except Exception as e:
        print("错误：", str(e))
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()



#添加节点
@app.route('/kc_code/add', methods=['POST'])
@token_required  # 添加 token_required 装饰器，确保用户已登录
def add_kc_code(current_user):
    data = request.json
    parent_id = data.get('parent_id')
    zh_name = data.get('zh_name')
    en_name = data.get('en_name')
    node_type = data.get('node_type')
    kc_code = data.get('kc_code')

    # 从 current_user 获取登录用户的 ID 和管理 ID
    user_id = current_user.user_id
    managed_code_id  = current_user.managed_code_id   # 获取管理节点 ID

    print(f"Received data: {data}")  # 添加调试日志，查看收到的数据

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 获取用户管理节点 managed_code_id  对应的 node_id (隶属极点的KC)
        cursor.execute("SELECT node_id FROM kc_code WHERE code_id = %s", (managed_code_id,))
        managed_node = cursor.fetchone()
        if not managed_node:
            return jsonify({'success': False, 'message': '隶属极点不存在！'}), 400

        print(f"Managed node: {managed_node}")  # 打印隶属极点的信息

        level_kc_code = managed_node['node_id']
        version = None

        # 插入新节点，使用用户隶属极点的 kc_code
        cursor.execute("""
            INSERT INTO kc_code (node_id, node_type, parent_id, zh_name, en_name, kc_code, version, status, created_time)
            VALUES (%s, %s, %s, %s, %s, %s, %s, 'pending', NOW())
        """, (level_kc_code, node_type, parent_id, zh_name, en_name, kc_code, version))

        conn.commit()

        # 记录操作日志
        log_operation(
            user_id=user_id,
            operation_type='add_node',
            target_type='kc_code',
            target_id=cursor.lastrowid,
            operation_status='success',
            new_value={
                'parent_id': parent_id,
                'node_type': node_type,
                'zh_name': zh_name,
                'en_name': en_name,
                'kc_code': kc_code
            },
            ip_address=get_client_ip(request)
        )

        return jsonify({'success': True, 'message': '节点创建成功！', 'kc_code': kc_code})

    except Exception as e:
        print(f"发生错误：{str(e)}")  # 打印错误信息
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        cursor.close()
        conn.close()


# 设置文件上传路径和允许的文件格式
UPLOAD_FOLDER = './uploads'
ALLOWED_EXTENSIONS = {'xls', 'xlsx'}

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# 判断文件是否为有效的 Excel 格式
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@app.route('/kc_code/upload_excel', methods=['POST'])
@token_required
def upload_excel(current_user):
    file = request.files.get('file')
    if not file:
        return jsonify({'success': False, 'message': '未上传文件'}), 400

    try:
        import pandas as pd
        df = pd.read_excel(file)

        success_count = 0
        error_rows = []

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 获取当前用户管理节点
        cursor.execute("SELECT node_id FROM kc_code WHERE code_id = %s", (current_user.managed_code_id,))
        managed_node = cursor.fetchone()

        if not managed_node:
            return jsonify({'success': False, 'message': '未找到当前用户隶属极点'}), 400

        level_kc_code = managed_node['node_id']
        version = None

        for index, row in df.iterrows():
            try:
                row_number = index + 2  # Excel中从第2行开始是数据（+2）
                parent_kc_code = row['上级节点']
                node_type = row['节点类型']
                zh_name = row['中文名称']
                en_name = row['英文名称']
                kc_code = row['KC码']

                # 查询父节点
                cursor.execute("SELECT node_id, code_id, kc_code FROM kc_code WHERE kc_code = %s", (parent_kc_code,))
                parent_node = cursor.fetchone()
                if not parent_node:
                    error_rows.append(f"第{row_number}行：父节点 {parent_kc_code} 不存在")
                    continue
                #不属于同一极点下
                if parent_node['node_id'] != level_kc_code:
                    error_rows.append(f"第{row_number}行：无权限在 {parent_kc_code} 下插入节点")
                    continue

                # 检查 KC码 唯一性
                cursor.execute("SELECT kc_code FROM kc_code WHERE kc_code = %s", (kc_code,))
                if cursor.fetchone():
                    error_rows.append(f"第{row_number}行：KC码 {kc_code} 已存在")
                    continue

                # 插入节点
                cursor.execute("""
                    INSERT INTO kc_code (node_id, node_type, parent_id, zh_name, en_name, kc_code,version, status, created_time)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, 'pending', NOW())
                """, (level_kc_code, node_type, parent_node['code_id'], zh_name, en_name, kc_code,version))
                conn.commit()

                log_operation(
                    user_id=current_user.user_id,
                    operation_type='add_node',
                    target_type='kc_code',
                    target_id=cursor.lastrowid,
                    operation_status='success',
                    new_value={
                        'parent_kc_code': parent_kc_code,
                        'zh_name': zh_name,
                        'en_name': en_name,
                        'kc_code': kc_code,
                        'node_type': node_type
                    },
                    ip_address=get_client_ip(request)
                )

                success_count += 1

            except Exception as row_error:
                error_rows.append(f"第{row_number}行：发生错误 {str(row_error)}")
                continue

        cursor.close()
        conn.close()

        # 最终返回
        return jsonify({
            'success': True,
            'message': f'上传完成：成功录入 {success_count} 条数据',
            'errors': error_rows
        }), 200

    except Exception as e:
        print(f"上传处理失败：{str(e)}")
        return jsonify({'success': False, 'message': f'处理失败：{str(e)}'}), 500

# @app.route('/kc_code/upload_excel', methods=['POST'])
# @token_required
# def upload_excel(current_user):
#     file = request.files.get('file')
#     if not file:
#         return jsonify({'success': False, 'message': '未上传文件'}), 400
#
#     try:
#         import pandas as pd
#         df = pd.read_excel(file)
#
#         success_count = 0
#         error_rows = []
#
#         conn = get_db_connection()
#         cursor = conn.cursor(dictionary=True)
#
#         # ✅ 获取当前用户管理的节点 ID
#         user_root_id = current_user.managed_code_id
#         if not user_root_id:
#             return jsonify({'success': False, 'message': '当前用户未绑定管理节点'}), 400
#
#         # ✅ 获取该节点及其所有子节点的 kc_code（权限控制白名单）
#         cursor.execute("""
#             WITH RECURSIVE sub_tree AS (
#                 SELECT code_id, kc_code FROM kc_code WHERE code_id = %s
#                 UNION ALL
#                 SELECT kc.code_id, kc.kc_code FROM kc_code kc
#                 INNER JOIN sub_tree st ON kc.parent_id = st.code_id
#             )
#             SELECT kc_code FROM sub_tree
#         """, (user_root_id,))
#         allowed_kc_codes = {row['kc_code'] for row in cursor.fetchall()}
#
#         version = None  # KC 版本默认空
#         level_kc_code = None  # 当前节点 node_id（用于存入）
#
#         # 获取该节点 node_id（用于设置插入节点的 node_id 字段）
#         cursor.execute("SELECT node_id FROM kc_code WHERE code_id = %s", (user_root_id,))
#         node_info = cursor.fetchone()
#         if node_info:
#             level_kc_code = node_info['node_id']
#         else:
#             return jsonify({'success': False, 'message': '未找到当前用户管理节点的 node_id'}), 400
#
#         # ✅ 遍历 Excel 数据
#         for index, row in df.iterrows():
#             try:
#                 row_number = index + 2  # Excel 中从第2行是数据
#                 parent_kc_code = str(row['上级节点']).strip()
#                 node_type = str(row['节点类型']).strip()
#                 zh_name = str(row['中文名称']).strip()
#                 en_name = str(row['英文名称']).strip()
#                 kc_code = str(row['KC码']).strip()
#
#                 # ✅ 校验权限：parent_kc_code 是否在允许的子树中
#                 if parent_kc_code not in allowed_kc_codes:
#                     error_rows.append(f"第{row_number}行：无权限在 {parent_kc_code} 下插入节点")
#                     continue
#
#                 # ✅ 获取父节点信息
#                 cursor.execute("SELECT code_id FROM kc_code WHERE kc_code = %s", (parent_kc_code,))
#                 parent_node = cursor.fetchone()
#                 if not parent_node:
#                     error_rows.append(f"第{row_number}行：父节点 {parent_kc_code} 不存在")
#                     continue
#
#                 # ✅ 检查 KC 码唯一性
#                 cursor.execute("SELECT kc_code FROM kc_code WHERE kc_code = %s", (kc_code,))
#                 if cursor.fetchone():
#                     error_rows.append(f"第{row_number}行：KC码 {kc_code} 已存在")
#                     continue
#
#                 # ✅ 插入数据
#                 cursor.execute("""
#                     INSERT INTO kc_code (
#                         node_id, node_type, parent_id,
#                         zh_name, en_name, kc_code, version,
#                         status, created_time
#                     ) VALUES (%s, %s, %s, %s, %s, %s, %s, 'pending', NOW())
#                 """, (
#                     level_kc_code, node_type, parent_node['code_id'],
#                     zh_name, en_name, kc_code, version
#                 ))
#                 conn.commit()
#
#                 # ✅ 操作日志
#                 log_operation(
#                     user_id=current_user.user_id,
#                     operation_type='add_node',
#                     target_type='kc_code',
#                     target_id=cursor.lastrowid,
#                     operation_status='success',
#                     new_value={
#                         'parent_kc_code': parent_kc_code,
#                         'zh_name': zh_name,
#                         'en_name': en_name,
#                         'kc_code': kc_code,
#                         'node_type': node_type
#                     },
#                     ip_address=get_client_ip(request)
#                 )
#
#                 success_count += 1
#
#             except Exception as row_error:
#                 error_rows.append(f"第{row_number}行：发生错误：{str(row_error)}")
#                 continue
#
#         cursor.close()
#         conn.close()
#
#         return jsonify({
#             'success': True,
#             'message': f'上传完成：成功录入 {success_count} 条数据',
#             'errors': error_rows
#         })
#
#     except Exception as e:
#         print(f"上传处理失败：{str(e)}")
#         return jsonify({'success': False, 'message': f'处理失败：{str(e)}'}), 500


#查看kc码结构
@app.route('/api/operator/root_nodes', methods=['GET'])
@token_required
def get_nodes_operator(current_user):
    try:
        # ✅ 用 managed_code_id 获取该节点自身信息
        root_node = KcCode.query.filter_by(code_id=current_user.managed_code_id).first()
        if not root_node:
            return jsonify({'success': False, 'message': '未找到管理节点'}), 404

        node_data = {
            'code_id': root_node.code_id,
            'node_id': root_node.node_id,
            'zh_name': root_node.zh_name,
            'en_name': root_node.en_name,
            'kc_code': root_node.kc_code,
            'version': root_node.version,
            'status': root_node.status
        }

        return jsonify({'success': True, 'data': [node_data]})

    except Exception as e:
        return jsonify({
            'success': False,
            'message': f'获取根节点失败: {str(e)}'
        }), 500


# @app.route('/api/operator/node_structure', methods=['GET'])
# @token_required
# def get_node_structure_operator(current_user):
#     if current_user.role.role_name != 'operator':
#         return jsonify({'success': False, 'message': '无权访问此功能'}), 403
#
#     try:
#         # ✅ 从 managed_code_id 开始递归
#         managed_nodes = db.session.query(KcCode.code_id, KcCode.status).filter(
#             (KcCode.code_id == current_user.managed_code_id)
#         ).cte(name='managed_nodes', recursive=True)
#
#         managed_nodes = managed_nodes.union_all(
#             db.session.query(KcCode.code_id, KcCode.status).filter(
#                 KcCode.parent_id == managed_nodes.c.code_id
#             )
#         )
#
#         # 查询所有相关节点
#         query = KcCode.query.join(
#             managed_nodes,
#             KcCode.code_id == managed_nodes.c.code_id
#         )
#         nodes = query.all()
#
#         # 构建 code_id → node 数据字典
#         node_dict = {}
#         for node in nodes:
#             node_dict[node.code_id] = {
#                 'id': node.code_id,
#                 'node_id': node.node_id,
#                 'node_type': node.node_type,
#                 'zh_name': node.zh_name,
#                 'en_name': node.en_name,
#                 'kc_code': node.kc_code,
#                 'version': node.version,
#                 'status': node.status,
#                 'children': []
#             }
#
#         # 构建树结构：子节点挂到父节点
#         root_nodes = []
#         for node in nodes:
#             node_data = node_dict[node.code_id]
#             if node.code_id == current_user.managed_code_id:
#                 root_nodes.append(node_data)
#             else:
#                 parent = node_dict.get(node.parent_id)
#                 if parent:
#                     parent['children'].append(node_data)
#
#         return jsonify({'success': True, 'data': root_nodes})
#
#     except Exception as e:
#         return jsonify({'success': False, 'message': f'获取节点结构失败: {str(e)}'}), 500

@app.route('/api/operator/node_structure', methods=['GET'])
@token_required
def get_node_structure_from_admin(current_user):
    if current_user.role.role_name != 'operator':
        return jsonify({'success': False, 'message': '无权访问此功能'}), 403

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Step 1: 获取隶属的极点管理员 user_id
        cursor.execute("SELECT managed_user_id FROM user WHERE user_id = %s", (current_user.user_id,))
        row = cursor.fetchone()
        if not row or not row['managed_user_id']:
            return jsonify({'success': False, 'message': '未绑定极点管理员'}), 404

        pole_admin_id = row['managed_user_id']

        # Step 2: 获取极点管理员的 managed_code_id
        cursor.execute("SELECT managed_code_id FROM user WHERE user_id = %s", (pole_admin_id,))
        row2 = cursor.fetchone()
        if not row2 or not row2['managed_code_id']:
            return jsonify({'success': False, 'message': '极点管理员未绑定管理节点'}), 404

        root_code_id = row2['managed_code_id']

        # Step 3: 从 root_code_id 开始递归查询所有子节点（使用 CTE）
        cursor.execute(f"""
            WITH RECURSIVE node_tree AS (
                SELECT code_id, node_id, zh_name, en_name, kc_code, node_type, version, status, parent_id
                FROM kc_code
                WHERE code_id = %s
                UNION ALL
                SELECT kc.code_id, kc.node_id, kc.zh_name, kc.en_name, kc.kc_code, kc.node_type, kc.version, kc.status, kc.parent_id
                FROM kc_code kc
                INNER JOIN node_tree nt ON kc.parent_id = nt.code_id
            )
            SELECT * FROM node_tree
        """, (root_code_id,))
        nodes = cursor.fetchall()

        # 构建树结构
        node_dict = {}
        for node in nodes:
            node_dict[node['code_id']] = {
                'id': node['code_id'],
                'node_id': node['node_id'],
                'zh_name': node['zh_name'],
                'en_name': node['en_name'],
                'kc_code': node['kc_code'],
                'node_type': node['node_type'],
                'version': node['version'],
                'status': node['status'],
                'children': []
            }

        root_nodes = []
        for node in nodes:
            node_data = node_dict[node['code_id']]
            if node['code_id'] == root_code_id:
                root_nodes.append(node_data)
            else:
                parent = node_dict.get(node['parent_id'])
                if parent:
                    parent['children'].append(node_data)

        return jsonify({'success': True, 'data': root_nodes})

    except Exception as e:
        return jsonify({'success': False, 'message': f'获取节点结构失败: {str(e)}'}), 500

    finally:
        cursor.close()
        conn.close()

@app.route('/kc_code/download_template', methods=['GET'])
@token_required
def download_excel_template(current_user):
    # 定义模板结构
    df = pd.DataFrame([{
        '上级节点': 'G.1',
        '节点类型': 'leaf',
        '中文名称': '示例中文名称',
        '英文名称': 'Example Name',
        'KC码': 'G.1.1'
    }])

    # 写入内存中而非磁盘
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
        df.to_excel(writer, index=False, sheet_name='模板')

    output.seek(0)  # 回到起始位置
    return send_file(
        output,
        download_name='KC码导入模板.xlsx',
        as_attachment=True,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


        
def insert_user(user_id,username, password, role_name, managed_code_id=None, managed_user_id=None):
    # 检查用户名是否已经存在
    user_exists = User.query.filter_by(username=username).first()
    if user_exists:
        print(f"用户名 {username} 已存在")
        return

    # 获取角色
    role = Role.query.filter_by(role_name=role_name).first()
    if not role:
        print(f"角色 {role_name} 不存在")
        return

    # 生成密码哈希值
    hashed_password = generate_password_hash(password)

    # 创建新用户
    new_user = User(
        user_id=user_id,
        username=username,
        password=hashed_password,
        role_id=role.role_id,
        managed_code_id=managed_code_id,
        managed_user_id=managed_user_id
    )

    db.session.add(new_user)
    db.session.commit()

    print(f"用户 {username} 插入成功")

if __name__ == '__main__':

    # 直接调用 insert_user 函数插入用户
    # insert_user('super_admin', '123456', 'super_admin', 1, None)
    # insert_user('pole_admin1', '123456', 'pole_admin',2,1)
    # insert_user('pole_admin2', '123456', 'pole_admin',3,1)
    # insert_user('operator1', '123456', 'operator',None,2)
        # insert_user(1,'admin', 'admin', 'super_admin')
        # insert_user(2,'pole_admin', 'pole_admin', 'pole_admin',2,1)
        # insert_user(3,'pole_admin1', 'pole_admin1', 'pole_admin',3,1)
        # insert_user(4,'operator', 'operator', 'operator',7,2)
    app.run(debug=True)