# -*- coding: utf-8 -*-
"""
工单管理蓝图
处理工单相关的API路由
"""

import os
import pandas as pd
import logging
import uuid
import time
from datetime import datetime
from typing import Optional
from flask import Blueprint, request, jsonify, send_file
from werkzeug.utils import secure_filename
from sqlalchemy import text

logger = logging.getLogger(__name__)

# 简化的AI准确率配置类
class SimpleAIAccuracyConfig:
    """简化的AI准确率配置"""
    def __init__(self):
        self.auto_approve_threshold = 0.95
        self.use_human_resolution_threshold = 0.90
        self.manual_review_threshold = 0.80
        self.ai_suggestion_confidence = 0.95
        self.human_resolution_confidence = 0.90

    def should_auto_approve(self, similarity: float) -> bool:
        return similarity >= self.auto_approve_threshold

    def should_use_human_resolution(self, similarity: float) -> bool:
        return similarity < self.use_human_resolution_threshold

    def get_confidence_score(self, similarity: float, use_human: bool = False) -> float:
        if use_human:
            return self.human_resolution_confidence
        else:
            return max(similarity, self.ai_suggestion_confidence)

from src.main import TSPAssistant
from src.core.database import db_manager
from src.core.models import WorkOrder, Conversation, WorkOrderSuggestion, KnowledgeEntry, WorkOrderProcessHistory
from src.core.query_optimizer import query_optimizer
from src.web.service_manager import service_manager
from src.core.workorder_permissions import (
    WorkOrderPermissionManager, WorkOrderDispatchManager,
    UserRole, WorkOrderModule
)

workorders_bp = Blueprint('workorders', __name__, url_prefix='/api/workorders')

def get_current_user_role() -> UserRole:
    """获取当前用户角色（临时实现，实际需要集成认证系统）"""
    # TODO: 从session或token中获取用户信息
    # 在没有认证系统之前，默认返回ADMIN以便可以查看所有工单
    # 实际实现时需要从认证系统获取真实角色
    role_str = request.headers.get('X-User-Role', 'admin')  # 临时改为admin，避免VIEWER无法查看数据
    try:
        return UserRole(role_str)
    except ValueError:
        return UserRole.ADMIN  # 临时返回ADMIN，避免VIEWER无法查看数据

def get_current_user_name() -> str:
    """获取当前用户名（临时实现，实际需要集成认证系统）"""
    # TODO: 从session或token中获取用户信息
    return request.headers.get('X-User-Name', 'anonymous')

def add_process_history(
    workorder_id: int,
    processor_name: str,
    process_content: str,
    action_type: str,
    processor_role: Optional[str] = None,
    processor_region: Optional[str] = None,
    previous_status: Optional[str] = None,
    new_status: Optional[str] = None,
    assigned_module: Optional[str] = None
) -> WorkOrderProcessHistory:
    """
    添加工单处理过程记录

    Args:
        workorder_id: 工单ID
        processor_name: 处理人员姓名
        process_content: 处理内容
        action_type: 操作类型（dispatch、process、close、reassign等）
        processor_role: 处理人员角色
        processor_region: 处理人员区域
        previous_status: 处理前的状态
        new_status: 处理后的状态
        assigned_module: 分配的模块

    Returns:
        创建的处理记录对象
    """
    try:
        with db_manager.get_session() as session:
            history = WorkOrderProcessHistory(
                work_order_id=workorder_id,
                processor_name=processor_name,
                processor_role=processor_role,
                processor_region=processor_region,
                process_content=process_content,
                action_type=action_type,
                previous_status=previous_status,
                new_status=new_status,
                assigned_module=assigned_module,
                process_time=datetime.now()
            )
            session.add(history)
            session.commit()
            session.refresh(history)
            logger.info(f"工单 {workorder_id} 添加处理记录: {action_type} by {processor_name}")
            return history
    except Exception as e:
        logger.error(f"添加处理记录失败: {e}")
        raise

# 移除get_assistant函数，使用service_manager

def _ensure_workorder_template_file() -> str:
    """返回已有的模板xlsx路径；不做动态生成，避免运行时依赖问题"""
    # 获取项目根目录
    current_dir = os.path.dirname(os.path.abspath(__file__))
    project_root = os.path.abspath(os.path.join(current_dir, '..', '..', '..'))

    # 模板文件路径（项目根目录下的uploads）
    template_path = os.path.join(project_root, 'uploads', 'workorder_template.xlsx')

    # 确保目录存在
    uploads_dir = os.path.join(project_root, 'uploads')
    os.makedirs(uploads_dir, exist_ok=True)

    if not os.path.exists(template_path):
        # 尝试从其他可能的位置复制模板
        possible_locations = [
            os.path.join(project_root, 'uploads', 'workorder_template.xlsx'),
            os.path.join(current_dir, 'uploads', 'workorder_template.xlsx'),
            os.path.join(os.getcwd(), 'uploads', 'workorder_template.xlsx')
        ]

        source_found = False
        for source_path in possible_locations:
            if os.path.exists(source_path):
                try:
                    import shutil
                    shutil.copyfile(source_path, template_path)
                    source_found = True
                    break
                except Exception as e:
                    logger.warning(f"复制模板文件失败: {e}")

        if not source_found:
            # 自动生成一个最小可用模板
            try:
                import pandas as pd
                from pandas import DataFrame
                columns = ['标题', '描述', '分类', '优先级', '状态', '解决方案', '满意度']
                df: DataFrame = pd.DataFrame(columns=columns)
                df.to_excel(template_path, index=False)
                logger.info(f"自动生成模板文件: {template_path}")
            except Exception as gen_err:
                raise FileNotFoundError('模板文件缺失且自动生成失败，请检查依赖：openpyxl/pandas') from gen_err

    return template_path

@workorders_bp.route('')
def get_workorders():
    """获取工单列表（分页，带权限过滤）"""
    try:
        # 获取当前用户角色和权限
        current_role = get_current_user_role()

        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        status_filter = request.args.get('status', '')
        priority_filter = request.args.get('priority', '')
        module_filter = request.args.get('module', '')  # 模块过滤

        # 从数据库获取分页数据
        from src.core.database import db_manager
        from src.core.models import WorkOrder

        with db_manager.get_session() as session:
            # 构建查询
            query = session.query(WorkOrder)

            # 权限过滤：业务方只能看到自己模块的工单
            if not WorkOrderPermissionManager.can_view_all_workorders(current_role):
                # 获取用户可访问的模块
                accessible_modules = WorkOrderPermissionManager.get_accessible_modules(current_role)
                if accessible_modules:
                    # 构建模块列表过滤条件
                    module_names = [m.value for m in accessible_modules]
                    query = query.filter(WorkOrder.assigned_module.in_(module_names))
                else:
                    # 如果没有可访问的模块，返回空列表
                    return jsonify({
                        "workorders": [],
                        "total": 0,
                        "page": page,
                        "per_page": per_page,
                        "total_pages": 0  # 统一使用total_pages字段
                    })

            # 应用过滤器
            if status_filter:
                query = query.filter(WorkOrder.status == status_filter)
            if priority_filter:
                query = query.filter(WorkOrder.priority == priority_filter)
            if module_filter:
                query = query.filter(WorkOrder.assigned_module == module_filter)

            # 按创建时间倒序排列
            query = query.order_by(WorkOrder.created_at.desc())

            # 计算总数
            total = query.count()

            # 分页查询
            workorders = query.offset((page - 1) * per_page).limit(per_page).all()

            # 转换为字典
            workorders_data = []
            for workorder in workorders:
                workorders_data.append({
                    'id': workorder.id,
                    'order_id': workorder.order_id,
                    'title': workorder.title,
                    'description': workorder.description,
                    'assigned_module': workorder.assigned_module,
                    'module_owner': workorder.module_owner,
                    'dispatcher': workorder.dispatcher,
                    'dispatch_time': workorder.dispatch_time.isoformat() if workorder.dispatch_time else None,
                    'region': workorder.region,
                    'category': workorder.category,
                    'priority': workorder.priority,
                    'status': workorder.status,
                    'assignee': workorder.assignee,
                    'source': workorder.source,
                    'module': workorder.module,
                    'created_by': workorder.created_by,
                    'created_at': workorder.created_at.isoformat() if workorder.created_at else None,
                    'updated_at': workorder.updated_at.isoformat() if workorder.updated_at else None,
                    'date_of_close': workorder.date_of_close.isoformat() if workorder.date_of_close else None
                })

            # 计算分页信息
            total_pages = (total + per_page - 1) // per_page

            return jsonify({
                'workorders': workorders_data,
                'page': page,
                'per_page': per_page,
                'total': total,
                'total_pages': total_pages
            })

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('', methods=['POST'])
def create_workorder():
    """创建工单（初始状态为待分发）"""
    try:
        data = request.get_json()
        result = service_manager.get_assistant().create_work_order(
            title=data['title'],
            description=data['description'],
            category=data['category'],
            priority=data['priority']
        )

        # 获取当前用户信息（用于记录创建人）
        current_user = get_current_user_name()
        current_role = get_current_user_role()

        # 创建工单后，设置为待分发状态（未分配模块）
        if result and 'id' in result:
            workorder_id = result.get('id')
            with db_manager.get_session() as session:
                workorder = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
                if workorder:
                    # 初始状态为待分发
                    workorder.assigned_module = WorkOrderModule.UNASSIGNED.value
                    workorder.status = "pending"  # 待处理/待分发
                    workorder.created_by = current_user  # 记录创建人
                    session.commit()

                    # 记录创建工单的处理历史
                    processor_region = "overseas" if current_role == UserRole.OVERSEAS_OPS else "domestic"
                    add_process_history(
                        workorder_id=workorder_id,
                        processor_name=current_user,
                        process_content=f"工单已创建：{data.get('title', '')[:50]}",
                        action_type="create",
                        processor_role=current_role.value,
                        processor_region=processor_region,
                        previous_status=None,
                        new_status="pending"
                    )

        # 清除工单相关缓存
        from src.core.cache_manager import cache_manager
        cache_manager.clear()  # 清除所有缓存

        return jsonify({"success": True, "workorder": result})
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>')
def get_workorder_details(workorder_id):
    """获取工单详情（含数据库对话记录，带权限检查）"""
    try:
        # 获取当前用户角色和权限
        current_role = get_current_user_role()

        with db_manager.get_session() as session:
            w = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not w:
                return jsonify({"error": "工单不存在"}), 404

            # 权限检查：业务方只能访问自己模块的工单
            if not WorkOrderPermissionManager.can_view_all_workorders(current_role):
                # 检查是否有权限访问该工单
                assigned_module_str = w.assigned_module
                if not assigned_module_str or assigned_module_str == WorkOrderModule.UNASSIGNED.value:
                    # 未分配的工单，业务方不能访问
                    return jsonify({"error": "无权访问该工单"}), 403

                try:
                    assigned_module = WorkOrderModule(assigned_module_str)
                    accessible_modules = WorkOrderPermissionManager.get_accessible_modules(current_role)
                    if assigned_module not in accessible_modules:
                        return jsonify({"error": "无权访问该工单"}), 403
                except ValueError:
                    # 如果模块值无效，业务方不能访问
                    return jsonify({"error": "无权访问该工单"}), 403
            convs = session.query(Conversation).filter(Conversation.work_order_id == w.id).order_by(Conversation.timestamp.asc()).all()
            conv_list = []
            for c in convs:
                conv_list.append({
                    "id": c.id,
                    "user_message": c.user_message,
                    "assistant_response": c.assistant_response,
                    "timestamp": c.timestamp.isoformat() if c.timestamp else None
                })

            # 获取处理过程记录
            process_history_list = session.query(WorkOrderProcessHistory).filter(
                WorkOrderProcessHistory.work_order_id == w.id
            ).order_by(WorkOrderProcessHistory.process_time.asc()).all()

            process_history_data = []
            for ph in process_history_list:
                process_history_data.append({
                    "id": ph.id,
                    "processor_name": ph.processor_name,
                    "processor_role": ph.processor_role,
                    "processor_region": ph.processor_region,
                    "process_content": ph.process_content,
                    "action_type": ph.action_type,
                    "previous_status": ph.previous_status,
                    "new_status": ph.new_status,
                    "assigned_module": ph.assigned_module,
                    "process_time": ph.process_time.isoformat() if ph.process_time else None
                })

            # 在会话内构建工单数据
            workorder = {
                "id": w.id,
                "order_id": w.order_id,
                "title": w.title,
                "description": w.description,
                "category": w.category,
                "priority": w.priority,
                "status": w.status,
                "created_at": w.created_at.isoformat() if w.created_at else None,
                "updated_at": w.updated_at.isoformat() if w.updated_at else None,
                "resolution": w.resolution,
                "satisfaction_score": w.satisfaction_score,
                "assigned_module": w.assigned_module,
                "module_owner": w.module_owner,
                "dispatcher": w.dispatcher,
                "dispatch_time": w.dispatch_time.isoformat() if w.dispatch_time else None,
                "region": w.region,
                "conversations": conv_list,
                "process_history": process_history_data  # 处理过程记录
            }
            return jsonify(workorder)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>', methods=['PUT'])
def update_workorder(workorder_id):
    """更新工单（写入数据库，自动记录处理历史）"""
    try:
        # 获取当前用户信息
        current_user = get_current_user_name()
        current_role = get_current_user_role()

        data = request.get_json()
        if not data.get('title') or not data.get('description'):
            return jsonify({"error": "标题和描述不能为空"}), 400

        with db_manager.get_session() as session:
            w = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not w:
                return jsonify({"error": "工单不存在"}), 404

            # 记录更新前的状态
            previous_status = w.status
            previous_priority = w.priority

            # 更新工单信息
            w.title = data.get('title', w.title)
            w.description = data.get('description', w.description)
            w.category = data.get('category', w.category)
            w.priority = data.get('priority', w.priority)
            new_status = data.get('status', w.status)
            w.status = new_status
            w.resolution = data.get('resolution', w.resolution)
            w.satisfaction_score = data.get('satisfaction_score', w.satisfaction_score)
            w.updated_at = datetime.now()

            session.commit()

            # 如果状态或优先级发生变化，记录处理历史
            has_status_change = previous_status != new_status
            has_priority_change = previous_priority != data.get('priority', w.priority)

            if has_status_change or has_priority_change:
                # 构建处理内容
                change_items = []
                if has_status_change:
                    change_items.append(f"状态变更：{previous_status} → {new_status}")
                if has_priority_change:
                    change_items.append(f"优先级变更：{previous_priority} → {data.get('priority', w.priority)}")

                process_content = "；".join(change_items)
                if data.get('resolution'):
                    process_content += f"；解决方案：{data.get('resolution', '')[:100]}"

                # 判断区域
                processor_region = "overseas" if current_role == UserRole.OVERSEAS_OPS else "domestic"

                add_process_history(
                    workorder_id=workorder_id,
                    processor_name=current_user,
                    process_content=process_content or "更新工单信息",
                    action_type="update",
                    processor_role=current_role.value,
                    processor_region=processor_region,
                    previous_status=previous_status,
                    new_status=new_status
                )

            # 清除工单相关缓存
            from src.core.cache_manager import cache_manager
            cache_manager.clear()  # 清除所有缓存

            updated = {
                "id": w.id,
                "title": w.title,
                "description": w.description,
                "category": w.category,
                "priority": w.priority,
                "status": w.status,
                "resolution": w.resolution,
                "satisfaction_score": w.satisfaction_score,
                "updated_at": w.updated_at.isoformat() if w.updated_at else None
            }
            return jsonify({"success": True, "message": "工单更新成功", "workorder": updated})
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>', methods=['DELETE'])
def delete_workorder(workorder_id):
    """删除工单"""
    try:
        with db_manager.get_session() as session:
            workorder = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not workorder:
                return jsonify({"error": "工单不存在"}), 404

            # 先删除所有相关的子记录（按外键依赖顺序）
            # 1. 删除工单建议记录
            try:
                session.execute(text("DELETE FROM work_order_suggestions WHERE work_order_id = :id"), {"id": workorder_id})
            except Exception as e:
                print(f"删除工单建议记录失败: {e}")

            # 2. 删除对话记录
            session.query(Conversation).filter(Conversation.work_order_id == workorder_id).delete()

            # 3. 删除工单
            session.delete(workorder)
            session.commit()

            # 清除工单相关缓存
            from src.core.cache_manager import cache_manager
            cache_manager.clear()  # 清除所有缓存

            return jsonify({
                "success": True,
                "message": "工单删除成功"
            })
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>/ai-suggestion', methods=['POST'])
def generate_workorder_ai_suggestion(workorder_id):
    """根据工单描述与知识库生成AI建议草稿"""
    try:
        with db_manager.get_session() as session:
            w = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not w:
                return jsonify({"error": "工单不存在"}), 404
            # 调用知识库搜索与LLM生成
            # 使用问题描述（title）而不是处理过程（description）作为主要查询依据
            query = f"{w.title}"
            kb_results = service_manager.get_assistant().search_knowledge(query, top_k=3)
            kb_list = kb_results.get('results', []) if isinstance(kb_results, dict) else []
            # 组装提示词
            context = "\n".join([f"Q: {k.get('question','')}\nA: {k.get('answer','')}" for k in kb_list])
            from src.core.llm_client import QwenClient
            llm = QwenClient()
            prompt = f"请基于以下工单问题描述与知识库片段，给出简洁、可执行的处理建议。\n\n问题描述:\n{w.title}\n\n处理过程（仅供参考）:\n{w.description}\n\n知识库片段:\n{context}\n\n请直接输出建议文本："
            llm_resp = llm.chat_completion(messages=[{"role":"user","content":prompt}], temperature=0.3, max_tokens=800)
            suggestion = ""
            if llm_resp and 'choices' in llm_resp:
                suggestion = llm_resp['choices'][0]['message']['content']
            # 保存/更新草稿记录
            rec = session.query(WorkOrderSuggestion).filter(WorkOrderSuggestion.work_order_id == w.id).first()
            if not rec:
                rec = WorkOrderSuggestion(work_order_id=w.id, ai_suggestion=suggestion)
                session.add(rec)
            else:
                rec.ai_suggestion = suggestion
                rec.updated_at = datetime.now()
            session.commit()
            return jsonify({"success": True, "suggestion": suggestion})
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>/human-resolution', methods=['POST'])
def save_workorder_human_resolution(workorder_id):
    """保存人工描述，并计算与AI建议相似度；若≥95%可自动审批入库"""
    try:
        data = request.get_json() or {}
        human_text = data.get('human_resolution','').strip()
        if not human_text:
            return jsonify({"error":"人工描述不能为空"}), 400
        with db_manager.get_session() as session:
            w = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not w:
                return jsonify({"error": "工单不存在"}), 404
            rec = session.query(WorkOrderSuggestion).filter(WorkOrderSuggestion.work_order_id == w.id).first()
            if not rec:
                rec = WorkOrderSuggestion(work_order_id=w.id)
                session.add(rec)
            rec.human_resolution = human_text
            # 计算语义相似度（使用sentence-transformers进行更准确的语义比较）
            try:
                from src.utils.semantic_similarity import calculate_semantic_similarity
                ai_text = rec.ai_suggestion or ""
                sim = calculate_semantic_similarity(ai_text, human_text)
                logger.info(f"AI建议与人工描述语义相似度: {sim:.4f}")
            except Exception as e:
                logger.error(f"计算语义相似度失败: {e}")
                # 回退到传统方法
                try:
                    from sklearn.feature_extraction.text import TfidfVectorizer
                    from sklearn.metrics.pairwise import cosine_similarity
                    texts = [rec.ai_suggestion or "", human_text]
                    vec = TfidfVectorizer(max_features=1000)
                    mat = vec.fit_transform(texts)
                    sim = float(cosine_similarity(mat[0:1], mat[1:2])[0][0])
                except Exception:
                    sim = 0.0
            rec.ai_similarity = sim

            # 使用简化的配置
            config = SimpleAIAccuracyConfig()

            # 自动审批条件
            approved = config.should_auto_approve(sim)
            rec.approved = approved

            # 记录使用人工描述入库的标记（当AI准确率低于阈值时）
            use_human_resolution = config.should_use_human_resolution(sim)
            rec.use_human_resolution = use_human_resolution

            session.commit()
            return jsonify({
                "success": True,
                "similarity": sim,
                "approved": approved,
                "use_human_resolution": use_human_resolution
            })
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>/approve-to-knowledge', methods=['POST'])
def approve_workorder_to_knowledge(workorder_id):
    """将已审批的AI建议或人工描述入库为知识条目"""
    try:
        with db_manager.get_session() as session:
            w = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not w:
                return jsonify({"error": "工单不存在"}), 404

            rec = session.query(WorkOrderSuggestion).filter(WorkOrderSuggestion.work_order_id == w.id).first()
            if not rec:
                return jsonify({"error": "未找到工单建议记录"}), 400

            # 使用简化的配置
            config = SimpleAIAccuracyConfig()

            # 确定使用哪个内容入库
            if rec.use_human_resolution and rec.human_resolution:
                # AI准确率低于阈值，使用人工描述入库
                answer_content = rec.human_resolution
                confidence_score = config.get_confidence_score(rec.ai_similarity or 0, use_human=True)
                verified_by = 'human_resolution'
                logger.info(f"工单 {workorder_id} 使用人工描述入库，AI相似度: {rec.ai_similarity:.4f}")
            elif rec.approved and rec.ai_suggestion:
                # AI准确率≥阈值，使用AI建议入库
                answer_content = rec.ai_suggestion
                confidence_score = config.get_confidence_score(rec.ai_similarity or 0, use_human=False)
                verified_by = 'auto_approve'
                logger.info(f"工单 {workorder_id} 使用AI建议入库，相似度: {rec.ai_similarity:.4f}")
            else:
                return jsonify({"error": "未找到可入库的内容"}), 400

            # 入库为知识条目
            entry = KnowledgeEntry(
                question=w.title or (w.description[:20] if w.description else '工单问题'),
                answer=answer_content,
                category=w.category or '其他',
                confidence_score=confidence_score,
                is_active=True,
                is_verified=True,
                verified_by=verified_by,
                verified_at=datetime.now()
            )
            session.add(entry)
            session.commit()

            return jsonify({
                "success": True,
                "knowledge_id": entry.id,
                "used_content": "human_resolution" if rec.use_human_resolution else "ai_suggestion",
                "confidence_score": confidence_score
            })
    except Exception as e:
        logger.error(f"入库知识库失败: {e}")
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/import', methods=['POST'])
def import_workorders():
    """导入Excel工单文件"""
    try:
        # 检查是否有文件上传
        if 'file' not in request.files:
            return jsonify({"error": "没有上传文件"}), 400

        file = request.files['file']
        if file.filename == '':
            return jsonify({"error": "没有选择文件"}), 400

        if not file.filename.endswith(('.xlsx', '.xls')):
            return jsonify({"error": "只支持Excel文件(.xlsx, .xls)"}), 400

        # 保存上传的文件
        filename = secure_filename(file.filename)
        upload_path = os.path.join('uploads', filename)
        os.makedirs('uploads', exist_ok=True)
        file.save(upload_path)

        # 解析Excel文件
        try:
            df = pd.read_excel(upload_path)
            imported_workorders = []

            # 处理每一行数据
            for index, row in df.iterrows():
                # 根据Excel列名映射到工单字段
                title = str(row.get('标题', row.get('title', f'导入工单 {index + 1}')))
                description = str(row.get('描述', row.get('description', '')))
                category = str(row.get('分类', row.get('category', '技术问题')))
                priority = str(row.get('优先级', row.get('priority', 'medium')))
                status = str(row.get('状态', row.get('status', 'open')))

                # 验证必填字段
                if not title or title.strip() == '':
                    continue

                # 生成唯一的工单ID
                timestamp = int(time.time())
                unique_id = str(uuid.uuid4())[:8]
                order_id = f"IMP_{timestamp}_{unique_id}"

                # 创建工单到数据库
                try:
                    with db_manager.get_session() as session:
                        workorder = WorkOrder(
                            order_id=order_id,
                            title=title,
                            description=description,
                            category=category,
                            priority=priority,
                            status=status,
                            created_at=datetime.now(),
                            updated_at=datetime.now()
                        )

                        # 处理可选字段
                        if pd.notna(row.get('解决方案', row.get('resolution'))):
                            workorder.resolution = str(row.get('解决方案', row.get('resolution')))

                        if pd.notna(row.get('满意度', row.get('satisfaction_score'))):
                            try:
                                workorder.satisfaction_score = int(row.get('满意度', row.get('satisfaction_score')))
                            except (ValueError, TypeError):
                                workorder.satisfaction_score = None

                        session.add(workorder)
                        session.commit()

                        logger.info(f"成功导入工单: {order_id} - {title}")

                except Exception as db_error:
                    logger.error(f"导入工单到数据库失败: {db_error}")
                    continue

                # 添加到返回列表
                imported_workorders.append({
                    "id": workorder.id,
                    "order_id": workorder.order_id,
                    "title": workorder.title,
                    "description": workorder.description,
                    "category": workorder.category,
                    "priority": workorder.priority,
                    "status": workorder.status,
                    "created_at": workorder.created_at.isoformat() if workorder.created_at else None,
                    "updated_at": workorder.updated_at.isoformat() if workorder.updated_at else None,
                    "resolution": workorder.resolution,
                    "satisfaction_score": workorder.satisfaction_score
                })

            # 清理上传的文件
            os.remove(upload_path)

            return jsonify({
                "success": True,
                "message": f"成功导入 {len(imported_workorders)} 个工单",
                "imported_count": len(imported_workorders),
                "workorders": imported_workorders
            })

        except Exception as e:
            # 清理上传的文件
            if os.path.exists(upload_path):
                os.remove(upload_path)
            return jsonify({"error": f"解析Excel文件失败: {str(e)}"}), 400

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/import/template')
def download_import_template():
    """下载工单导入模板"""
    try:
        template_path = _ensure_workorder_template_file()
        return jsonify({
            "success": True,
            "template_url": f"/uploads/workorder_template.xlsx"
        })

    except Exception as e:
        return jsonify({"error": str(e)}), 500

@workorders_bp.route('/import/template/file')
def download_import_template_file():
    """直接返回工单导入模板文件（下载）"""
    try:
        template_path = _ensure_workorder_template_file()

        # 检查文件是否存在
        if not os.path.exists(template_path):
            logger.error(f"模板文件不存在: {template_path}")
            return jsonify({"error": "模板文件不存在"}), 404

        # 检查文件大小
        file_size = os.path.getsize(template_path)
        if file_size == 0:
            logger.error(f"模板文件为空: {template_path}")
            return jsonify({"error": "模板文件为空"}), 500

        logger.info(f"准备下载模板文件: {template_path}, 大小: {file_size} bytes")

        try:
            # Flask>=2 使用 download_name
            return send_file(template_path, as_attachment=True, download_name='工单导入模板.xlsx', mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        except TypeError:
            # 兼容 Flask<2 的 attachment_filename
            return send_file(template_path, as_attachment=True, attachment_filename='工单导入模板.xlsx', mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

    except Exception as e:
        logger.error(f"下载模板文件失败: {e}")
        return jsonify({"error": f"下载失败: {str(e)}"}), 500

@workorders_bp.route('/<int:workorder_id>/dispatch', methods=['POST'])
def dispatch_workorder(workorder_id):
    """工单分发：运维将工单分配给业务模块"""
    try:
        # 获取当前用户角色和权限
        current_role = get_current_user_role()
        current_user = get_current_user_name()

        # 检查分发权限
        if not WorkOrderPermissionManager.can_dispatch_workorder(current_role):
            return jsonify({
                "success": False,
                "error": "无权进行工单分发，只有属地运维和管理员可以分发工单"
            }), 403

        # 获取请求数据
        data = request.get_json() or {}
        target_module_str = data.get('target_module', '')

        if not target_module_str:
            return jsonify({"success": False, "error": "请指定目标模块"}), 400

        # 验证模块
        try:
            target_module = WorkOrderModule(target_module_str)
        except ValueError:
            return jsonify({"success": False, "error": f"无效的模块: {target_module_str}"}), 400

        # 获取工单
        with db_manager.get_session() as session:
            workorder = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not workorder:
                return jsonify({"success": False, "error": "工单不存在"}), 404

            # 执行分发
            module_owner = WorkOrderDispatchManager.get_module_owner(target_module)

            # 记录分发前的状态
            previous_status = workorder.status

            # 更新工单信息
            workorder.assigned_module = target_module.value
            workorder.module_owner = module_owner
            workorder.dispatcher = current_user
            workorder.dispatch_time = datetime.now()
            workorder.status = "assigned"  # 更新状态为已分配

            # 根据区域自动设置（可以从工单source或其他字段判断）
            # 这里简化处理，可以根据实际需求调整
            if not workorder.region:
                # 如果source包含特定关键词，可以判断区域
                source = workorder.source or ""
                if any(keyword in source.lower() for keyword in ["overseas", "abroad", "海外"]):
                    workorder.region = "overseas"
                else:
                    workorder.region = "domestic"

            session.commit()

            # 记录处理历史：工单分发
            processor_region = "overseas" if current_role == UserRole.OVERSEAS_OPS else "domestic"
            add_process_history(
                workorder_id=workorder_id,
                processor_name=current_user,
                process_content=f"工单已分发到{target_module.value}模块，业务接口人：{module_owner}",
                action_type="dispatch",
                processor_role=current_role.value,
                processor_region=processor_region,
                previous_status=previous_status,
                new_status="assigned",
                assigned_module=target_module.value
            )

            logger.info(f"工单 {workorder_id} 已分发到 {target_module.value} 模块，分发人: {current_user}")

            return jsonify({
                "success": True,
                "message": f"工单已成功分发到{target_module.value}模块",
                "workorder": {
                    "id": workorder.id,
                    "assigned_module": workorder.assigned_module,
                    "module_owner": workorder.module_owner,
                    "dispatcher": workorder.dispatcher,
                    "dispatch_time": workorder.dispatch_time.isoformat() if workorder.dispatch_time else None,
                    "status": workorder.status
                }
            })

    except Exception as e:
        logger.error(f"工单分发失败: {e}")
        return jsonify({"success": False, "error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>/suggest-module', methods=['POST'])
def suggest_workorder_module(workorder_id):
    """AI建议工单应该分配的模块"""
    try:
        with db_manager.get_session() as session:
            workorder = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not workorder:
                return jsonify({"success": False, "error": "工单不存在"}), 404

            # 使用AI分析建议模块
            suggested_module = WorkOrderDispatchManager.suggest_module(
                description=workorder.description or "",
                title=workorder.title or ""
            )

            return jsonify({
                "success": True,
                "suggested_module": suggested_module.value if suggested_module else None,
                "module_owner": WorkOrderDispatchManager.get_module_owner(suggested_module) if suggested_module else None
            })

    except Exception as e:
        logger.error(f"模块建议失败: {e}")
        return jsonify({"success": False, "error": str(e)}), 500

@workorders_bp.route('/modules', methods=['GET'])
def get_available_modules():
    """获取所有可用的模块列表"""
    try:
        modules = [
            {"value": m.value, "name": m.name, "owner": WorkOrderDispatchManager.get_module_owner(m)}
            for m in WorkOrderModule
            if m != WorkOrderModule.UNASSIGNED
        ]
        return jsonify({"success": True, "modules": modules})
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>/process-history', methods=['GET'])
def get_workorder_process_history(workorder_id):
    """获取工单处理过程记录"""
    try:
        with db_manager.get_session() as session:
            workorder = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not workorder:
                return jsonify({"error": "工单不存在"}), 404

            # 获取处理历史
            history_list = session.query(WorkOrderProcessHistory).filter(
                WorkOrderProcessHistory.work_order_id == workorder_id
            ).order_by(WorkOrderProcessHistory.process_time.asc()).all()

            history_data = []
            for ph in history_list:
                history_data.append({
                    "id": ph.id,
                    "processor_name": ph.processor_name,
                    "processor_role": ph.processor_role,
                    "processor_region": ph.processor_region,
                    "process_content": ph.process_content,
                    "action_type": ph.action_type,
                    "previous_status": ph.previous_status,
                    "new_status": ph.new_status,
                    "assigned_module": ph.assigned_module,
                    "process_time": ph.process_time.isoformat() if ph.process_time else None
                })

            return jsonify({
                "success": True,
                "workorder_id": workorder_id,
                "process_history": history_data,
                "total": len(history_data)
            })
    except Exception as e:
        logger.error(f"获取处理历史失败: {e}")
        return jsonify({"success": False, "error": str(e)}), 500

@workorders_bp.route('/<int:workorder_id>/process-history', methods=['POST'])
def add_workorder_process_history(workorder_id):
    """手动添加工单处理过程记录"""
    try:
        # 获取当前用户信息
        current_user = get_current_user_name()
        current_role = get_current_user_role()

        data = request.get_json() or {}
        process_content = data.get('process_content', '').strip()

        if not process_content:
            return jsonify({"success": False, "error": "处理内容不能为空"}), 400

        with db_manager.get_session() as session:
            workorder = session.query(WorkOrder).filter(WorkOrder.id == workorder_id).first()
            if not workorder:
                return jsonify({"success": False, "error": "工单不存在"}), 404

            # 获取可选参数
            action_type = data.get('action_type', 'process')  # 默认操作类型为process
            processor_role = data.get('processor_role', current_role.value)
            processor_region = data.get('processor_region')
            if not processor_region:
                # 根据角色自动判断区域
                processor_region = "overseas" if current_role == UserRole.OVERSEAS_OPS else "domestic"

            previous_status = data.get('previous_status', workorder.status)
            new_status = data.get('new_status', workorder.status)
            assigned_module = data.get('assigned_module', workorder.assigned_module)

            # 添加处理记录
            history = add_process_history(
                workorder_id=workorder_id,
                processor_name=data.get('processor_name', current_user),
                process_content=process_content,
                action_type=action_type,
                processor_role=processor_role,
                processor_region=processor_region,
                previous_status=previous_status,
                new_status=new_status,
                assigned_module=assigned_module
            )

            return jsonify({
                "success": True,
                "message": "处理记录已添加",
                "history": {
                    "id": history.id,
                    "processor_name": history.processor_name,
                    "processor_role": history.processor_role,
                    "process_content": history.process_content,
                    "action_type": history.action_type,
                    "process_time": history.process_time.isoformat() if history.process_time else None
                }
            })
    except Exception as e:
        logger.error(f"添加处理记录失败: {e}")
        return jsonify({"success": False, "error": str(e)}), 500
