DataOps-platform/app/core/data_parse/parse.py

from typing import Dict, Any
from app import db
from datetime import datetime
import os
import boto3
from botocore.config import Config
import logging
import requests
import json
import re
import uuid
from PIL import Image
from io import BytesIO
import pytesseract
import base64
from openai import OpenAI
from app.config.config import DevelopmentConfig, ProductionConfig

# 名片解析数据模型
class BusinessCard(db.Model):
    __tablename__ = 'business_cards'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name_zh = db.Column(db.String(100), nullable=False)
    name_en = db.Column(db.String(100))
    title_zh = db.Column(db.String(100))
    title_en = db.Column(db.String(100))
    mobile = db.Column(db.String(50))
    phone = db.Column(db.String(50))
    email = db.Column(db.String(100))
    hotel_zh = db.Column(db.String(200))
    hotel_en = db.Column(db.String(200))
    address_zh = db.Column(db.Text)
    address_en = db.Column(db.Text)
    postal_code_zh = db.Column(db.String(20))
    postal_code_en = db.Column(db.String(20))
    brand_zh = db.Column(db.String(100))
    brand_en = db.Column(db.String(100))
    affiliation_zh = db.Column(db.String(200))
    affiliation_en = db.Column(db.String(200))
    birthday = db.Column(db.Date)  # 生日，存储年月日
    residence = db.Column(db.Text)  # 居住地
    image_path = db.Column(db.String(255))  # MinIO中存储的路径
    career_path = db.Column(db.JSON)  # 职业轨迹，JSON格式
    brand_group = db.Column(db.String(200))  # 品牌组合
    created_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
    updated_at = db.Column(db.DateTime, onupdate=datetime.now)
    updated_by = db.Column(db.String(50))
    status = db.Column(db.String(20), default='active')
    
    def to_dict(self):
        return {
            'id': self.id,
            'name_zh': self.name_zh,
            'name_en': self.name_en,
            'title_zh': self.title_zh,
            'title_en': self.title_en,
            'mobile': self.mobile,
            'phone': self.phone,
            'email': self.email,
            'hotel_zh': self.hotel_zh,
            'hotel_en': self.hotel_en,
            'address_zh': self.address_zh,
            'address_en': self.address_en,
            'postal_code_zh': self.postal_code_zh,
            'postal_code_en': self.postal_code_en,
            'brand_zh': self.brand_zh,
            'brand_en': self.brand_en,
            'affiliation_zh': self.affiliation_zh,
            'affiliation_en': self.affiliation_en,
            'birthday': self.birthday.strftime('%Y-%m-%d') if self.birthday else None,
            'residence': self.residence,
            'image_path': self.image_path,
            'career_path': self.career_path,
            'brand_group': self.brand_group,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S') if self.created_at else None,
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S') if self.updated_at else None,
            'updated_by': self.updated_by,
            'status': self.status
        }


# 重复名片处理数据模型
class DuplicateBusinessCard(db.Model):
    __tablename__ = 'duplicate_business_cards'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    main_card_id = db.Column(db.Integer, db.ForeignKey('business_cards.id'), nullable=False)  # 新创建的主记录ID
    suspected_duplicates = db.Column(db.JSON, nullable=False)  # 疑似重复记录列表，JSON格式
    duplicate_reason = db.Column(db.String(200), nullable=False)  # 重复原因
    processing_status = db.Column(db.String(20), default='pending')  # 处理状态：pending/processed/ignored
    created_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
    processed_at = db.Column(db.DateTime)  # 处理时间
    processed_by = db.Column(db.String(50))  # 处理人
    processing_notes = db.Column(db.Text)  # 处理备注
    
    # 关联主记录
    main_card = db.relationship('BusinessCard', backref=db.backref('as_main_duplicate_records', lazy=True))
    
    def to_dict(self):
        return {
            'id': self.id,
            'main_card_id': self.main_card_id,
            'suspected_duplicates': self.suspected_duplicates,
            'duplicate_reason': self.duplicate_reason,
            'processing_status': self.processing_status,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S') if self.created_at else None,
            'processed_at': self.processed_at.strftime('%Y-%m-%d %H:%M:%S') if self.processed_at else None,
            'processed_by': self.processed_by,
            'processing_notes': self.processing_notes
        }


# 名片解析功能模块

def check_duplicate_business_card(extracted_data):
    """
    检查是否存在重复的名片记录
    
    Args:
        extracted_data (dict): 提取的名片信息
        
    Returns:
        dict: 包含检查结果的字典，格式为：
            {
                'is_duplicate': bool,
                'action': str,  # 'update', 'create_with_duplicates' 或 'create_new'
                'existing_card': BusinessCard 或 None,
                'suspected_duplicates': list,  # 疑似重复记录列表
                'reason': str
            }
    """
    try:
        # 获取提取的中文姓名和手机号码
        name_zh = extracted_data.get('name_zh', '').strip()
        mobile = extracted_data.get('mobile', '').strip()
        
        if not name_zh:
            return {
                'is_duplicate': False,
                'action': 'create_new',
                'existing_card': None,
                'suspected_duplicates': [],
                'reason': '无中文姓名，创建新记录'
            }
        
        # 查找具有相同中文姓名的记录
        existing_cards = BusinessCard.query.filter_by(name_zh=name_zh).all()
        
        if not existing_cards:
            return {
                'is_duplicate': False,
                'action': 'create_new',
                'existing_card': None,
                'suspected_duplicates': [],
                'reason': '未找到同名记录，创建新记录'
            }
        
        # 如果找到同名记录，进一步检查手机号码
        if mobile:
            # 有手机号码的情况
            for existing_card in existing_cards:
                existing_mobile = existing_card.mobile.strip() if existing_card.mobile else ''
                
                if existing_mobile == mobile:
                    # 手机号码相同，更新现有记录
                    return {
                        'is_duplicate': True,
                        'action': 'update',
                        'existing_card': existing_card,
                        'suspected_duplicates': [],
                        'reason': f'姓名和手机号码均相同：{name_zh} - {mobile}'
                    }
            
            # 有手机号码但与现有记录不匹配，创建新记录并标记疑似重复
            suspected_list = []
            for card in existing_cards:
                suspected_list.append({
                    'id': card.id,
                    'name_zh': card.name_zh,
                    'name_en': card.name_en,
                    'mobile': card.mobile,
                    'hotel_zh': card.hotel_zh,
                    'hotel_en': card.hotel_en,
                    'title_zh': card.title_zh,
                    'title_en': card.title_en,
                    'created_at': card.created_at.strftime('%Y-%m-%d %H:%M:%S') if card.created_at else None
                })
            
            return {
                'is_duplicate': True,
                'action': 'create_with_duplicates',
                'existing_card': None,
                'suspected_duplicates': suspected_list,
                'reason': f'姓名相同但手机号码不同：{name_zh}，新手机号：{mobile}，发现{len(suspected_list)}条疑似重复记录'
            }
        else:
            # 无手机号码的情况，创建新记录并标记疑似重复
            suspected_list = []
            for card in existing_cards:
                suspected_list.append({
                    'id': card.id,
                    'name_zh': card.name_zh,
                    'name_en': card.name_en,
                    'mobile': card.mobile,
                    'hotel_zh': card.hotel_zh,
                    'hotel_en': card.hotel_en,
                    'title_zh': card.title_zh,
                    'title_en': card.title_en,
                    'created_at': card.created_at.strftime('%Y-%m-%d %H:%M:%S') if card.created_at else None
                })
            
            return {
                'is_duplicate': True,
                'action': 'create_with_duplicates',
                'existing_card': None,
                'suspected_duplicates': suspected_list,
                'reason': f'姓名相同但新记录无手机号码可比较：{name_zh}，发现{len(suspected_list)}条疑似重复记录'
            }
            
    except Exception as e:
        logging.error(f"检查重复记录时发生错误: {str(e)}", exc_info=True)
        return {
            'is_duplicate': False,
            'action': 'create_new',
            'existing_card': None,
            'suspected_duplicates': [],
            'reason': f'检查过程出错，创建新记录: {str(e)}'
        }


def update_career_path(existing_card, new_data, image_path=None):
    """
    更新职业轨迹信息
    
    Args:
        existing_card (BusinessCard): 现有名片记录
        new_data (dict): 新的名片信息
        image_path (str, optional): 对应的图片路径
        
    Returns:
        list: 更新后的职业轨迹
    """
    try:
        # 获取现有的职业轨迹
        career_path = existing_card.career_path if existing_card.career_path else []
        
        # 准备新的职业轨迹条目
        new_entry = {
            'date': datetime.now().strftime('%Y-%m-%d'),
            'hotel_zh': new_data.get('hotel_zh', ''),
            'hotel_en': new_data.get('hotel_en', ''),
            'title_zh': new_data.get('title_zh', ''),
            'title_en': new_data.get('title_en', ''),
            'image_path': image_path or '',  # 添加图片路径
            'source': 'business_card_update'
        }
        
        # 检查是否已存在相似的条目（避免重复添加）
        is_duplicate_entry = False
        for entry in career_path:
            if (entry.get('hotel_zh') == new_entry['hotel_zh'] and 
                entry.get('title_zh') == new_entry['title_zh'] and
                entry.get('date') == new_entry['date']):
                is_duplicate_entry = True
                break
        
        if not is_duplicate_entry:
            career_path.append(new_entry)
            logging.info(f"为名片ID {existing_card.id} 添加了新的职业轨迹条目，包含图片路径: {image_path}")
        else:
            logging.info(f"名片ID {existing_card.id} 的职业轨迹条目已存在，跳过添加")
        
        return career_path
        
    except Exception as e:
        logging.error(f"更新职业轨迹时发生错误: {str(e)}", exc_info=True)
        return existing_card.career_path if existing_card.career_path else []


def create_main_card_with_duplicates(extracted_data, minio_path, suspected_duplicates, reason):
    """
    创建新的主记录并保存疑似重复记录信息
    
    Args:
        extracted_data (dict): 提取的新名片信息
        minio_path (str): 新图片的MinIO路径
        suspected_duplicates (list): 疑似重复记录列表
        reason (str): 重复原因
        
    Returns:
        tuple: (main_card, duplicate_record) 主记录和重复记录信息
    """
    try:
        # 1. 先创建主记录
        # 准备初始职业轨迹，包含当前名片信息和图片路径
        initial_career_path = extracted_data.get('career_path', [])
        if extracted_data.get('hotel_zh') or extracted_data.get('hotel_en') or extracted_data.get('title_zh') or extracted_data.get('title_en'):
            initial_entry = {
                'date': datetime.now().strftime('%Y-%m-%d'),
                'hotel_zh': extracted_data.get('hotel_zh', ''),
                'hotel_en': extracted_data.get('hotel_en', ''),
                'title_zh': extracted_data.get('title_zh', ''),
                'title_en': extracted_data.get('title_en', ''),
                'image_path': minio_path or '',  # 当前名片的图片路径
                'source': 'business_card_creation'
            }
            initial_career_path.append(initial_entry)
        
        main_card = BusinessCard(
            name_zh=extracted_data.get('name_zh', ''),
            name_en=extracted_data.get('name_en', ''),
            title_zh=extracted_data.get('title_zh', ''),
            title_en=extracted_data.get('title_en', ''),
            mobile=extracted_data.get('mobile', ''),
            phone=extracted_data.get('phone', ''),
            email=extracted_data.get('email', ''),
            hotel_zh=extracted_data.get('hotel_zh', ''),
            hotel_en=extracted_data.get('hotel_en', ''),
            address_zh=extracted_data.get('address_zh', ''),
            address_en=extracted_data.get('address_en', ''),
            postal_code_zh=extracted_data.get('postal_code_zh', ''),
            postal_code_en=extracted_data.get('postal_code_en', ''),
            brand_zh=extracted_data.get('brand_zh', ''),
            brand_en=extracted_data.get('brand_en', ''),
            affiliation_zh=extracted_data.get('affiliation_zh', ''),
            affiliation_en=extracted_data.get('affiliation_en', ''),
            birthday=datetime.strptime(extracted_data.get('birthday'), '%Y-%m-%d').date() if extracted_data.get('birthday') else None,
            residence=extracted_data.get('residence', ''),
            image_path=minio_path,  # 最新的图片路径
            career_path=initial_career_path,  # 包含图片路径的职业轨迹
            brand_group=extracted_data.get('brand_group', ''),
            status='active',
            updated_by='system'
        )
        
        db.session.add(main_card)
        db.session.flush()  # 获取主记录的ID
        
        # 2. 创建重复记录信息
        duplicate_record = DuplicateBusinessCard(
            main_card_id=main_card.id,
            suspected_duplicates=suspected_duplicates,
            duplicate_reason=reason,
            processing_status='pending'
        )
        
        db.session.add(duplicate_record)
        db.session.commit()
        
        logging.info(f"已创建主记录(ID: {main_card.id})并保存{len(suspected_duplicates)}条疑似重复记录信息(重复记录ID: {duplicate_record.id})")
        return main_card, duplicate_record
        
    except Exception as e:
        db.session.rollback()
        logging.error(f"创建主记录和重复记录信息失败: {str(e)}", exc_info=True)
        raise e


# DeepSeek API配置
DEEPSEEK_API_KEY = os.environ.get('DEEPSEEK_API_KEY', 'sk-2aea6e8b159b448aa3c1e29acd6f4349')
DEEPSEEK_API_URL = os.environ.get('DEEPSEEK_API_URL', 'https://api.deepseek.com/v1/chat/completions')
# 备用API端点
DEEPSEEK_API_URL_BACKUP = 'https://api.deepseek.com/v1/completions'

# OCR配置
# 设置pytesseract路径（如果需要）
# pytesseract.pytesseract.tesseract_cmd = r'/path/to/tesseract'
# OCR语言设置，支持多语言
OCR_LANG = os.environ.get('OCR_LANG', 'chi_sim+eng')


# 根据环境选择配置
""" 
if os.environ.get('FLASK_ENV') == 'production':
    config = ProductionConfig()
else:
    config = DevelopmentConfig() 
"""

# 使用配置变量,缺省认为在生产环境运行
config = ProductionConfig()
# 使用配置变量
minio_url = f"{'https' if config.MINIO_SECURE else 'http'}://{config.MINIO_HOST}"
minio_access_key = config.MINIO_USER
minio_secret_key = config.MINIO_PASSWORD
minio_bucket = config.MINIO_BUCKET
use_ssl = config.MINIO_SECURE

def get_minio_client():
    """获取MinIO客户端连接"""
    try:
        # 使用全局配置变量
        global minio_url, minio_access_key, minio_secret_key, minio_bucket, use_ssl
        
        logging.info(f"尝试连接MinIO服务器: {minio_url}")
        
        minio_client = boto3.client(
            's3',
            endpoint_url=minio_url,
            aws_access_key_id=minio_access_key,
            aws_secret_access_key=minio_secret_key,
            config=Config(
                signature_version='s3v4',
                retries={'max_attempts': 3, 'mode': 'standard'},
                connect_timeout=10,
                read_timeout=30
            )
        )
        
        # 确保存储桶存在
        buckets = minio_client.list_buckets()
        bucket_names = [bucket['Name'] for bucket in buckets.get('Buckets', [])]
        logging.info(f"成功连接到MinIO服务器，现有存储桶: {bucket_names}")
        
        if minio_bucket not in bucket_names:
            logging.info(f"创建存储桶: {minio_bucket}")
            minio_client.create_bucket(Bucket=minio_bucket)
            
        return minio_client
    except Exception as e:
        logging.error(f"MinIO连接错误: {str(e)}")
        return None

def extract_text_from_image(image_data):
    """
    使用OCR从图像中提取文本，然后通过DeepSeek API解析名片信息
    
    Args:
        image_data (bytes): 图像的二进制数据
        
    Returns:
        dict: 提取的信息（姓名、职位、公司等）
    
    Raises:
        Exception: 当OCR或API调用失败或配置错误时抛出异常
    """
    try:
        # 步骤1: 使用OCR从图像中提取文本
        ocr_text = ocr_extract_text(image_data)
        if not ocr_text or ocr_text.strip() == "":
            error_msg = "OCR无法从图像中提取文本"
            logging.error(error_msg)
            raise Exception(error_msg)
        
        logging.info(f"OCR提取的文本: {ocr_text[:200]}..." if len(ocr_text) > 200 else ocr_text)
        
        # 步骤2: 使用DeepSeek API解析文本中的信息
        return parse_text_with_deepseek(ocr_text)
    
    except Exception as e:
        error_msg = f"从图像中提取和解析文本失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        raise Exception(error_msg)


def ocr_extract_text(image_data):
    """
    使用OCR从图像中提取文本
    
    Args:
        image_data (bytes): 图像的二进制数据
        
    Returns:
        str: 提取的文本
    """
    try:
        # 将二进制数据转换为PIL图像
        image = Image.open(BytesIO(image_data))
        
        # 使用pytesseract进行OCR文本提取
        text = pytesseract.image_to_string(image, lang=OCR_LANG)
        
        # 清理提取的文本
        text = text.strip()
        logging.info(f"OCR成功从图像中提取文本，长度: {len(text)}")
        print(text)
        
        return text
    except Exception as e:
        error_msg = f"OCR提取文本失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        raise Exception(error_msg)


def parse_text_with_deepseek(text):
    """
    使用DeepSeek API解析文本中的名片信息
    
    Args:
        text (str): 要解析的文本
        
    Returns:
        dict: 解析的名片信息
    """
    # 准备请求DeepSeek API
    if not DEEPSEEK_API_KEY:
        error_msg = "未配置DeepSeek API密钥"
        logging.error(error_msg)
        raise Exception(error_msg)
    
    # 构建API请求的基本信息
    headers = {
        "Authorization": f"Bearer {DEEPSEEK_API_KEY}",
        "Content-Type": "application/json"
    }
    
    # 构建提示语，包含OCR提取的文本
    prompt = f"""请从以下名片文本中提取详细信息，需分别识别中英文内容。
以JSON格式返回，包含以下字段：
- name_zh: 中文姓名
- name_en: 英文姓名
- title_zh: 中文职位/头衔
- title_en: 英文职位/头衔
- hotel_zh: 中文酒店/公司名称
- hotel_en: 英文酒店/公司名称
- mobile: 手机号码
- phone: 固定电话
- email: 电子邮箱
- address_zh: 中文地址
- address_en: 英文地址
- brand_group: 品牌组合（如有多个品牌，以逗号分隔）
- career_path: 职业轨迹（如果能从文本中推断出，以JSON数组格式返回，包含公司名称和职位）

名片文本：
{text}
"""
    
    # 使用模型名称
    model_name = 'deepseek-chat'
    
    try:
        # 尝试调用DeepSeek API
        logging.info(f"尝试通过DeepSeek API解析文本")
        payload = {
            "model": model_name,
            "messages": [
                {"role": "system", "content": "你是一个专业的名片信息提取助手。请用JSON格式返回结果，不要有多余的文字说明。"},
                {"role": "user", "content": prompt}
            ],
            "temperature": 0.1
        }
        
        logging.info(f"向DeepSeek API发送请求")
        response = requests.post(DEEPSEEK_API_URL, headers=headers, json=payload, timeout=30)
        
        # 检查响应状态
        response.raise_for_status()
        
        # 解析API响应
        result = response.json()
        content = result.get("choices", [{}])[0].get("message", {}).get("content", "{}")
        
        # 尝试解析JSON内容
        try:
            # 找到内容中的JSON部分（有时模型会在JSON前后添加额外文本）
            json_content = extract_json_from_text(content)
            extracted_data = json.loads(json_content)
            logging.info(f"成功解析DeepSeek API返回的JSON")
        except json.JSONDecodeError:
            logging.warning(f"无法解析JSON，尝试直接从文本提取信息")
            # 如果无法解析JSON，尝试直接从文本中提取关键信息
            extracted_data = extract_fields_from_text(content)
        
        # 确保所有必要的字段都存在
        required_fields = ['name', 'title', 'company', 'phone', 'email', 'address', 'brand_group', 'career_path']
        for field in required_fields:
            if field not in extracted_data:
                extracted_data[field] = "" if field != 'career_path' else []
        
        logging.info(f"成功从DeepSeek API获取解析结果")
        return extracted_data
        
    except requests.exceptions.HTTPError as e:
        error_msg = f"DeepSeek API调用失败: {str(e)}"
        logging.error(error_msg)
        
        if hasattr(e, 'response') and e.response:
            logging.error(f"错误状态码: {e.response.status_code}")
            logging.error(f"错误内容: {e.response.text}")
        
        raise Exception(error_msg)
    except Exception as e:
        error_msg = f"解析文本过程中发生错误: {str(e)}"
        logging.error(error_msg, exc_info=True)
        raise Exception(error_msg)


def extract_json_from_text(text):
    """
    从文本中提取JSON部分
    
    Args:
        text (str): 包含JSON的文本
        
    Returns:
        str: 提取的JSON字符串
    """
    # 尝试找到最外层的花括号对
    start_idx = text.find('{')
    if start_idx == -1:
        return "{}"
    
    # 使用简单的括号匹配算法找到对应的闭合括号
    count = 0
    for i in range(start_idx, len(text)):
        if text[i] == '{':
            count += 1
        elif text[i] == '}':
            count -= 1
            if count == 0:
                return text[start_idx:i+1]
    
    # 如果没有找到闭合括号，返回从开始位置到文本结尾
    return text[start_idx:]


def extract_fields_from_text(text):
    """
    从文本中直接提取名片字段信息
    
    Args:
        text (str): 要分析的文本
        
    Returns:
        dict: 提取的字段
    """
    # 初始化结果字典
    result = {
        'name_zh': '',
        'name_en': '',
        'title_zh': '',
        'title_en': '',
        'mobile': '',
        'phone': '',
        'email': '',
        'hotel_zh': '',
        'hotel_en': '',
        'address_zh': '',
        'address_en': '',
        'postal_code_zh': '',
        'postal_code_en': '',
        'brand_zh': '',
        'brand_en': '',
        'affiliation_zh': '',
        'affiliation_en': '',
        'birthday': '',
        'residence': ''
    }
    
    # 提取中文姓名
    name_zh_match = re.search(r'["\'](姓名)["\'][\s\{:]*["\']?(中文)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if name_zh_match:
        result['name_zh'] = name_zh_match.group(3)
    
    # 提取英文姓名
    name_en_match = re.search(r'["\'](姓名)["\'][\s\{:]*["\']?(英文)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if name_en_match:
        result['name_en'] = name_en_match.group(3)
    
    # 提取中文头衔
    title_zh_match = re.search(r'["\'](头衔|职位)["\'][\s\{:]*["\']?(中文)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if title_zh_match:
        result['title_zh'] = title_zh_match.group(3)
    
    # 提取英文头衔
    title_en_match = re.search(r'["\'](头衔|职位)["\'][\s\{:]*["\']?(英文)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if title_en_match:
        result['title_en'] = title_en_match.group(3)
    
    # 提取手机
    mobile_match = re.search(r'["\'](手机)["\'][\s:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if mobile_match:
        result['mobile'] = mobile_match.group(2)
    
    # 提取电话
    phone_match = re.search(r'["\'](电话)["\'][\s:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if phone_match:
        result['phone'] = phone_match.group(2)
    
    # 提取邮箱
    email_match = re.search(r'["\'](邮箱)["\'][\s:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if email_match:
        result['email'] = email_match.group(2)
    
    # 提取中文酒店名称
    hotel_zh_match = re.search(r'["\'](地址)["\'][\s\{:]*["\']?(中文)["\']?[\s\{:]*["\']?(酒店名称)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if hotel_zh_match:
        result['hotel_zh'] = hotel_zh_match.group(4)
    
    # 提取英文酒店名称
    hotel_en_match = re.search(r'["\'](地址)["\'][\s\{:]*["\']?(英文)["\']?[\s\{:]*["\']?(酒店名称)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if hotel_en_match:
        result['hotel_en'] = hotel_en_match.group(4)
    
    # 提取中文详细地址
    address_zh_match = re.search(r'["\'](地址)["\'][\s\{:]*["\']?(中文)["\']?[\s\{:]*["\']?(详细地址)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if address_zh_match:
        result['address_zh'] = address_zh_match.group(4)
    
    # 提取英文详细地址
    address_en_match = re.search(r'["\'](地址)["\'][\s\{:]*["\']?(英文)["\']?[\s\{:]*["\']?(详细地址)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if address_en_match:
        result['address_en'] = address_en_match.group(4)
    
    # 提取中文邮政编码
    postal_code_zh_match = re.search(r'["\'](地址)["\'][\s\{:]*["\']?(中文)["\']?[\s\{:]*["\']?(邮政编码)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if postal_code_zh_match:
        result['postal_code_zh'] = postal_code_zh_match.group(4)
    
    # 提取英文邮政编码
    postal_code_en_match = re.search(r'["\'](地址)["\'][\s\{:]*["\']?(英文)["\']?[\s\{:]*["\']?(邮政编码)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if postal_code_en_match:
        result['postal_code_en'] = postal_code_en_match.group(4)
    
    # 提取中文品牌名称
    brand_zh_match = re.search(r'["\'](公司)["\'][\s\{:]*["\']?(中文)["\']?[\s\{:]*["\']?(品牌名称)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if brand_zh_match:
        result['brand_zh'] = brand_zh_match.group(4)
    
    # 提取英文品牌名称
    brand_en_match = re.search(r'["\'](公司)["\'][\s\{:]*["\']?(英文)["\']?[\s\{:]*["\']?(品牌名称)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if brand_en_match:
        result['brand_en'] = brand_en_match.group(4)
    
    # 提取中文隶属关系
    affiliation_zh_match = re.search(r'["\'](公司)["\'][\s\{:]*["\']?(中文)["\']?[\s\{:]*["\']?(隶属关系)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if affiliation_zh_match:
        result['affiliation_zh'] = affiliation_zh_match.group(4)
    
    # 提取英文隶属关系
    affiliation_en_match = re.search(r'["\'](公司)["\'][\s\{:]*["\']?(英文)["\']?[\s\{:]*["\']?(隶属关系)["\']?[\s\}:]*["\']([^"\']+)["\']', text, re.IGNORECASE)
    if affiliation_en_match:
        result['affiliation_en'] = affiliation_en_match.group(4)
    
    return result

def parse_text_with_qwen25VLplus(image_data):
    """
    使用阿里云的 Qwen VL Max 模型解析图像中的名片信息
    
    Args:
        image_data (bytes): 图像的二进制数据
        
    Returns:
        dict: 解析的名片信息
    """
    # 阿里云 Qwen API 配置
    QWEN_API_KEY = os.environ.get('QWEN_API_KEY', 'sk-8f2320dafc9e4076968accdd8eebd8e9')
    
    try:
        # 将图片数据转为 base64 编码
        base64_image = base64.b64encode(image_data).decode('utf-8')
        
        # 初始化 OpenAI 客户端，配置为阿里云 API
        client = OpenAI(
            api_key=QWEN_API_KEY,
            base_url="https://dashscope.aliyuncs.com/compatible-mode/v1",
        )
        
        # 构建优化后的提示语
        prompt = """你是企业名片的信息提取专家。请仔细分析提供的图片，精确提取名片信息。

## 提取要求
- 区分中英文内容，分别提取
- 保持提取信息的原始格式（如大小写、标点）
- 对于无法识别或名片中不存在的信息，返回空字符串
- 名片中没有的信息，请不要猜测
## 需提取的字段
1. 中文姓名 (name_zh)
2. 英文姓名 (name_en)
3. 中文职位/头衔 (title_zh)
4. 英文职位/头衔 (title_en)
5. 中文酒店/公司名称 (hotel_zh)
6. 英文酒店/公司名称 (hotel_en)
7. 手机号码 (mobile) - 如有多个，使用逗号分隔
8. 固定电话 (phone) - 如有多个，使用逗号分隔
9. 电子邮箱 (email)
10. 中文地址 (address_zh)
11. 英文地址 (address_en)
12. 中文邮政编码 (postal_code_zh)
13. 英文邮政编码 (postal_code_en)
14. 生日 (birthday) - 格式为YYYY-MM-DD，如1990-01-01
15. 居住地 (residence) - 个人居住地址信息
16. 品牌组合 (brand_group) - 如有多个品牌，使用逗号分隔
17. 职业轨迹 (career_path) - 如能从名片中推断，以JSON数组格式返回，包含当前日期，公司名称和职位。自动生成当前日期。
18. 隶属关系 (affiliation) - 如能从名片中推断，以JSON数组格式返回，包含公司名称和隶属集团名称
## 输出格式
请以严格的JSON格式返回结果，不要添加任何额外解释文字。JSON格式如下：
```json
{
  "name_zh": "",
  "name_en": "",
  "title_zh": "",
  "title_en": "",
  "hotel_zh": "",
  "hotel_en": "",
  "mobile": "",
  "phone": "",
  "email": "",
  "address_zh": "",
  "address_en": "",
  "postal_code_zh": "",
  "postal_code_en": "",
  "birthday": "",
  "residence": "",
  "brand_group": "",
  "career_path": [],
  "affiliation": []
}
```"""
        
        # 调用 Qwen VL Max  API
        logging.info("发送请求到 Qwen VL Max 模型")
        completion = client.chat.completions.create(
            # model="qwen-vl-plus",
            model="qwen-vl-max-latest",
            messages=[
                {
                    "role": "user",
                    "content": [
                        {"type": "text", "text": prompt},
                        {"type": "image_url", "image_url": {"url": f"data:image/jpeg;base64,{base64_image}"}}
                    ]
                }
            ],
            temperature=0.1,  # 降低温度增加精确性
            response_format={"type": "json_object"}  # 要求输出JSON格式
        )
        
        # 解析响应
        response_content = completion.choices[0].message.content
        logging.info(f"成功从 Qwen 模型获取响应: {response_content}")
        
        # 尝试从响应中提取 JSON
        try:
            json_content = extract_json_from_text(response_content)
            extracted_data = json.loads(json_content)
            logging.info("成功解析 Qwen 响应中的 JSON")
        except json.JSONDecodeError:
            logging.warning("无法解析 JSON，尝试从文本中提取信息")
            extracted_data = extract_fields_from_text(response_content)
        
        # 确保所有必要字段存在
        required_fields = [
            'name_zh', 'name_en', 'title_zh', 'title_en', 
            'hotel_zh', 'hotel_en', 'mobile', 'phone', 
            'email', 'address_zh', 'address_en',
            'postal_code_zh', 'postal_code_en', 'birthday', 'residence',
            'brand_group', 'career_path'
        ]
        
        for field in required_fields:
            if field not in extracted_data:
                extracted_data[field] = [] if field == 'career_path' else ""
        
        # 为career_path增加一条记录
        if extracted_data.get('hotel_zh') or extracted_data.get('hotel_en') or extracted_data.get('title_zh') or extracted_data.get('title_en'):
            career_entry = {
                'date': datetime.now().strftime('%Y-%m-%d'),
                'hotel_en': extracted_data.get('hotel_en', ''),
                'hotel_zh': extracted_data.get('hotel_zh', ''),
                'image_path': '',
                'source': 'business_card_creation',
                'title_en': extracted_data.get('title_en', ''),
                'title_zh': extracted_data.get('title_zh', '')
            }
            
            # 直接清空原有的career_path内容，用career_entry写入
            extracted_data['career_path'] = [career_entry]
            logging.info(f"为解析结果设置了career_path记录: {career_entry}")
        
        return extracted_data
        
    except Exception as e:
        error_msg = f"Qwen VL Max 模型解析失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        raise Exception(error_msg)

def update_business_card(card_id, data):
    """
    更新名片信息
    
    Args:
        card_id (int): 名片记录ID
        data (dict): 包含要更新的字段的字典
        
    Returns:
        dict: 包含操作结果和更新后的名片信息
    """
    try:
        # 查找要更新的名片记录
        card = BusinessCard.query.get(card_id)
        
        if not card:
            return {
                'code': 500,
                'success': False,
                'message': f'未找到ID为{card_id}的名片记录',
                'data': None
            }
        
        # 更新名片信息
        card.name_zh = data.get('name_zh', card.name_zh)
        card.name_en = data.get('name_en', card.name_en)
        card.title_zh = data.get('title_zh', card.title_zh)
        card.title_en = data.get('title_en', card.title_en)
        card.mobile = data.get('mobile', card.mobile)
        card.phone = data.get('phone', card.phone)
        card.email = data.get('email', card.email)
        card.hotel_zh = data.get('hotel_zh', card.hotel_zh)
        card.hotel_en = data.get('hotel_en', card.hotel_en)
        card.address_zh = data.get('address_zh', card.address_zh)
        card.address_en = data.get('address_en', card.address_en)
        card.postal_code_zh = data.get('postal_code_zh', card.postal_code_zh)
        card.postal_code_en = data.get('postal_code_en', card.postal_code_en)
        card.brand_zh = data.get('brand_zh', card.brand_zh)
        card.brand_en = data.get('brand_en', card.brand_en)
        card.affiliation_zh = data.get('affiliation_zh', card.affiliation_zh)
        card.affiliation_en = data.get('affiliation_en', card.affiliation_en)
        # 处理生日字段，支持字符串转日期
        if 'birthday' in data:
            if data['birthday']:
                try:
                    card.birthday = datetime.strptime(data['birthday'], '%Y-%m-%d').date()
                except ValueError:
                    # 如果日期格式不正确，设置为None
                    card.birthday = None
            else:
                card.birthday = None
        card.residence = data.get('residence', card.residence)
        card.career_path = data.get('career_path', card.career_path)  # 更新职业轨迹
        card.brand_group = data.get('brand_group', card.brand_group)  # 更新品牌组合
        card.updated_by = data.get('updated_by', 'user')  # 可以根据实际情况修改为当前用户
        
        # 保存更新
        db.session.commit()
        
        # 更新成功后，更新Neo4j图数据库中的人才-酒店关系
        try:
            from app.services.neo4j_driver import neo4j_driver
            from app.core.graph.graph_operations import create_or_get_node
            
            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 创建或更新人才节点
            talent_properties = {
                'pg_id': card_id,              # PostgreSQL数据库中的ID
                'name_zh': card.name_zh,       # 中文姓名
                'name_en': card.name_en,       # 英文姓名
                'mobile': card.mobile,         # 手机号码
                'email': card.email,           # 电子邮箱
                'updated_at': current_time     # 更新时间
            }
            
            talent_node_id = create_or_get_node('talent', **talent_properties)
            
            # 如果有酒店信息，创建或更新酒店节点
            if card.hotel_zh or card.hotel_en:
                hotel_properties = {
                    'hotel_zh': card.hotel_zh,     # 酒店中文名称
                    'hotel_en': card.hotel_en,     # 酒店英文名称
                    'updated_at': current_time     # 更新时间
                }
                
                hotel_node_id = create_or_get_node('hotel', **hotel_properties)
                
                # 创建或更新人才与酒店之间的WORK_FOR关系
                if talent_node_id and hotel_node_id:
                    # 构建Cypher查询以创建或更新关系
                    cypher_query = """
                    MATCH (t:talent), (h:hotel)
                    WHERE id(t) = $talent_id AND id(h) = $hotel_id
                    MERGE (t)-[r:WORKS_FOR]->(h)
                    SET r.title_zh = $title_zh,
                        r.title_en = $title_en,
                        r.updated_at = $updated_at
                    RETURN r
                    """
                    
                    with neo4j_driver.get_session() as session:
                        session.run(
                            cypher_query,
                            talent_id=talent_node_id,
                            hotel_id=hotel_node_id,
                            title_zh=card.title_zh,
                            title_en=card.title_en,
                            updated_at=current_time
                        )
                        
                    logging.info(f"已成功更新人才(ID:{talent_node_id})与酒店(ID:{hotel_node_id})的WORK_FOR关系")
            
            logging.info(f"Neo4j图数据库关系更新成功")
        except Exception as e:
            logging.error(f"更新Neo4j图数据库关系失败: {str(e)}", exc_info=True)
            # 不因为图数据库更新失败而影响PostgreSQL数据库的更新结果
        
        return {
            'code': 200,
            'success': True,
            'message': '名片信息已更新',
            'data': card.to_dict()
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"更新名片信息失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def get_business_cards():
    """
    获取所有名片记录列表
    
    Returns:
        dict: 包含操作结果和名片列表
    """
    try:
        # 查询所有名片记录
        cards = BusinessCard.query.all()
        
        # 将所有记录转换为字典格式
        cards_data = [card.to_dict() for card in cards]
        
        return {
            'code': 200,
            'success': True,
            'message': '获取名片列表成功',
            'data': cards_data
        }
    
    except Exception as e:
        error_msg = f"获取名片列表失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': []
        }

def update_business_card_status(card_id, status):
    """
    更新名片状态（激活/禁用）
    
    Args:
        card_id (int): 名片记录ID
        status (str): 新状态，'active'或'inactive'
        
    Returns:
        dict: 包含操作结果和更新后的名片信息
    """
    try:
        # 查找要更新的名片记录
        card = BusinessCard.query.get(card_id)
        
        if not card:
            return {
                'code': 500,
                'success': False,
                'message': f'未找到ID为{card_id}的名片记录',
                'data': None
            }
        
        # 验证状态值
        if status not in ['active', 'inactive']:
            return {
                'code': 500,
                'success': False,
                'message': f'无效的状态值: {status}，必须为 active 或 inactive',
                'data': None
            }
        
        # 更新状态
        card.status = status
        card.updated_at = datetime.now()
        card.updated_by = 'system'  # 可以根据实际情况修改为当前用户
        
        # 保存更新
        db.session.commit()
        
        return {
            'code': 200,
            'success': True,
            'message': f'名片状态已更新为: {status}',
            'data': card.to_dict()
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"更新名片状态失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def create_talent_tag(tag_data):
    """
    创建人才标签节点
    
    Args:
        tag_data: 包含标签信息的字典，包括:
            - name: 标签名称
            - category: 标签分类
            - description: 标签描述
            - status: 启用状态
    
    Returns:
        dict: 操作结果字典
    """
    try:
        from app.services.neo4j_driver import neo4j_driver
        
        # 验证必要参数存在
        if not tag_data or 'name' not in tag_data or not tag_data['name']:
            return {
                'code': 400,
                'success': False,
                'message': '标签名称为必填项',
                'data': None
            }
        
        # 准备节点属性
        tag_properties = {
            'name': tag_data.get('name'),
            'category': tag_data.get('category', '未分类'),
            'describe': tag_data.get('description', ''),  # 使用describe与现有系统保持一致
            'status': tag_data.get('status', 'active'),
            'time': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        }
        
        # 生成标签的英文名（可选）
        from app.core.graph.graph_operations import create_or_get_node
        
        # 如果提供了名称，尝试获取英文翻译
        if 'name' in tag_data and tag_data['name']:
            try:
                from app.api.data_interface.routes import translate_and_parse
                en_name = translate_and_parse(tag_data['name'])
                tag_properties['en_name'] = en_name[0] if en_name and isinstance(en_name, list) else ''
            except Exception as e:
                logging.warning(f"获取标签英文名失败: {str(e)}")
                tag_properties['en_name'] = ''
                
        # 创建节点
        node_id = create_or_get_node('data_label', **tag_properties)
        
        if node_id:
            return {
                'code': 200,
                'success': True,
                'message': '人才标签创建成功',
                'data': {
                    'id': node_id,
                    **tag_properties
                }
            }
        else:
            return {
                'code': 500,
                'success': False,
                'message': '人才标签创建失败',
                'data': None
            }
            
    except Exception as e:
        logging.error(f"创建人才标签失败: {str(e)}", exc_info=True)
        return {
            'code': 500,
            'success': False,
            'message': f'创建人才标签失败: {str(e)}',
            'data': None
        }

def get_talent_tag_list():
    """
    从Neo4j图数据库获取人才标签列表
    
    Returns:
        dict: 包含操作结果和标签列表的字典
    """
    try:
        from app.services.neo4j_driver import neo4j_driver
        
        # 构建Cypher查询语句，获取分类为talent的标签
        query = """
        MATCH (n:data_label)
        WHERE n.category CONTAINS 'talent' OR n.category CONTAINS '人才'
        RETURN id(n) as id, n.name as name, n.en_name as en_name, 
               n.category as category, n.describe as description, 
               n.status as status, n.time as time
        ORDER BY n.time DESC
        """
        
        # 执行查询
        tags = []
        with neo4j_driver.get_session() as session:
            result = session.run(query)
            
            # 处理查询结果
            for record in result:
                tag = {
                    'id': record['id'],
                    'name': record['name'],
                    'en_name': record['en_name'],
                    'category': record['category'],
                    'description': record['description'],
                    'status': record['status'],
                    'time': record['time']
                }
                tags.append(tag)
        
        return {
            'code': 200,
            'success': True,
            'message': '获取人才标签列表成功',
            'data': tags
        }
        
    except Exception as e:
        error_msg = f"获取人才标签列表失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': []
        }

def update_talent_tag(tag_id, tag_data):
    """
    更新人才标签节点属性
    
    Args:
        tag_id: 标签节点ID
        tag_data: 包含更新信息的字典，可能包括:
            - name: 标签名称
            - category: 标签分类
            - description: 标签描述
            - status: 启用状态
    
    Returns:
        dict: 操作结果字典
    """
    try:
        from app.services.neo4j_driver import neo4j_driver
        
        # 准备要更新的属性
        update_properties = {}
        
        # 检查并添加需要更新的属性
        if 'name' in tag_data and tag_data['name']:
            update_properties['name'] = tag_data['name']
            
            # 如果名称更新了，尝试更新英文名称
            try:
                from app.api.data_interface.routes import translate_and_parse
                en_name = translate_and_parse(tag_data['name'])
                update_properties['en_name'] = en_name[0] if en_name and isinstance(en_name, list) else ''
            except Exception as e:
                logging.warning(f"更新标签英文名失败: {str(e)}")
        
        if 'category' in tag_data and tag_data['category']:
            update_properties['category'] = tag_data['category']
            
        if 'description' in tag_data:
            update_properties['describe'] = tag_data['description']
            
        if 'status' in tag_data:
            update_properties['status'] = tag_data['status']
            
        # 添加更新时间
        update_properties['time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        # 如果没有可更新的属性，返回错误
        if not update_properties:
            return {
                'code': 400,
                'success': False,
                'message': '未提供任何可更新的属性',
                'data': None
            }
        
        # 构建更新的Cypher查询
        set_clauses = []
        params = {'nodeId': tag_id}
        
        for key, value in update_properties.items():
            param_name = f"param_{key}"
            set_clauses.append(f"n.{key} = ${param_name}")
            params[param_name] = value
            
        set_clause = ", ".join(set_clauses)
        
        query = f"""
        MATCH (n:data_label)
        WHERE id(n) = $nodeId
        SET {set_clause}
        RETURN id(n) as id, n.name as name, n.en_name as en_name, 
               n.category as category, n.describe as description, 
               n.status as status, n.time as time
        """
        
        # 执行更新查询
        with neo4j_driver.get_session() as session:
            result = session.run(query, **params)
            record = result.single()
            
            if not record:
                return {
                    'code': 404,
                    'success': False,
                    'message': f'未找到ID为{tag_id}的标签',
                    'data': None
                }
                
            # 提取更新后的标签信息
            updated_tag = {
                'id': record['id'],
                'name': record['name'],
                'en_name': record['en_name'],
                'category': record['category'],
                'description': record['description'],
                'status': record['status'],
                'time': record['time']
            }
            
            return {
                'code': 200,
                'success': True,
                'message': '人才标签更新成功',
                'data': updated_tag
            }
            
    except Exception as e:
        error_msg = f"更新人才标签失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def delete_talent_tag(tag_id):
    """
    删除人才标签节点及其相关关系
    
    Args:
        tag_id: 标签节点ID
    
    Returns:
        dict: 操作结果字典
    """
    try:
        from app.services.neo4j_driver import neo4j_driver
        
        # 首先获取要删除的标签信息，以便在成功后返回
        get_query = """
        MATCH (n:data_label)
        WHERE id(n) = $nodeId
        RETURN id(n) as id, n.name as name, n.en_name as en_name, 
               n.category as category, n.describe as description, 
               n.status as status, n.time as time
        """
        
        # 构建删除节点和关系的Cypher查询
        delete_query = """
        MATCH (n:data_label)
        WHERE id(n) = $nodeId
        OPTIONAL MATCH (n)-[r]-()
        DELETE r, n
        RETURN count(n) AS deleted
        """
        
        # 执行查询
        tag_info = None
        with neo4j_driver.get_session() as session:
            # 先获取标签信息
            result = session.run(get_query, nodeId=tag_id)
            record = result.single()
            
            if not record:
                return {
                    'code': 404,
                    'success': False,
                    'message': f'未找到ID为{tag_id}的标签',
                    'data': None
                }
                
            # 保存标签信息用于返回
            tag_info = {
                'id': record['id'],
                'name': record['name'],
                'en_name': record['en_name'],
                'category': record['category'],
                'description': record['description'],
                'status': record['status'],
                'time': record['time']
            }
            
            # 执行删除操作
            delete_result = session.run(delete_query, nodeId=tag_id)
            deleted = delete_result.single()['deleted']
            
            if deleted > 0:
                return {
                    'code': 200,
                    'success': True,
                    'message': '人才标签删除成功',
                    'data': tag_info
                }
            else:
                return {
                    'code': 404,
                    'success': False,
                    'message': f'未能删除ID为{tag_id}的标签',
                    'data': None
                }
            
    except Exception as e:
        error_msg = f"删除人才标签失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def query_neo4j_graph(query_requirement):
    """
    查询Neo4j图数据库，通过Deepseek API生成Cypher脚本
    
    Args:
        query_requirement (str): 查询需求描述
        
    Returns:
        dict: 包含查询结果的字典，JSON格式
    """
    try:
        # 导入必要的模块
        from app.services.neo4j_driver import neo4j_driver
        import requests
        import json
        
        # Deepseek API配置
        api_key = DEEPSEEK_API_KEY
        api_url = DEEPSEEK_API_URL
        
        # 步骤1: 从Neo4j获取所有标签列表
        logging.info("第一步：从Neo4j获取人才类别的标签列表")
        all_labels_query = """
        MATCH (dl:data_label)
        WHERE dl.category CONTAINS '人才' OR dl.category CONTAINS 'talent'
        RETURN dl.name as name
        """
        
        all_labels = []
        with neo4j_driver.get_session() as session:
            result = session.run(all_labels_query)
            for record in result:
                all_labels.append(record['name'])
        
        logging.info(f"获取到{len(all_labels)}个人才标签: {all_labels}")
        
        # 步骤2: 使用Deepseek判断查询需求中的关键信息与标签的对应关系
        logging.info("第二步：调用Deepseek API匹配查询需求与标签")
        
        # 构建所有标签的JSON字符串
        labels_json = json.dumps(all_labels, ensure_ascii=False)
        
        # 构建匹配标签的提示语
        matching_prompt = f"""
        请分析以下查询需求，并从标签列表中找出与查询需求相关的标签。
        
        ## 查询需求
        {query_requirement}
        
        ## 可用标签列表
        {labels_json}
        
        ## 输出要求
        1. 请以JSON数组格式返回匹配的标签名称列表，格式如: ["标签1", "标签2", "标签3"]
        2. 只返回标签名称数组，不要包含任何解释或其他文本
        3. 如果没有找到匹配的标签，请返回空数组 []
        """
        
        # 调用Deepseek API匹配标签
        headers = {
            "Authorization": f"Bearer {api_key}",
            "Content-Type": "application/json"
        }
        
        payload = {
            "model": "deepseek-chat",
            "messages": [
                {"role": "system", "content": "你是一个专业的文本分析和匹配专家。"},
                {"role": "user", "content": matching_prompt}
            ],
            "temperature": 0.1,
            "response_format": {"type": "json_object"}
        }
        
        logging.info("发送请求到Deepseek API匹配标签："+matching_prompt)
        response = requests.post(api_url, headers=headers, json=payload, timeout=30)
        response.raise_for_status()
        
        # 解析API响应
        result = response.json()
        matching_content = result.get("choices", [{}])[0].get("message", {}).get("content", "[]")
        
        # 提取JSON数组
        try:
            # 尝试直接解析返回结果，预期格式为 ["新开酒店经验", "五星级酒店", "总经理"]
            logging.info(f"Deepseek返回的匹配内容: {matching_content}")
            
            # 如果返回的是JSON字符串，先去除可能的前后缀文本
            if isinstance(matching_content, str):
                # 查找JSON数组的开始和结束位置
                start_idx = matching_content.find('[')
                end_idx = matching_content.rfind(']') + 1
                
                if start_idx >= 0 and end_idx > start_idx:
                    json_str = matching_content[start_idx:end_idx]
                    matched_labels = json.loads(json_str)
                else:
                    matched_labels = []
            else:
                matched_labels = []
                
            # 确保结果是字符串列表
            if matched_labels and all(isinstance(item, str) for item in matched_labels):
                logging.info(f"成功解析到标签列表: {matched_labels}")
            else:
                logging.warning("解析结果不是预期的字符串列表格式，将使用空列表")
                matched_labels = []
        except json.JSONDecodeError as e:
            logging.error(f"JSON解析错误: {str(e)}")
            matched_labels = []
        except Exception as e:
            logging.error(f"解析匹配标签时出错: {str(e)}")
            matched_labels = []
        
        logging.info(f"匹配到的标签: {matched_labels}")
        
        # 如果没有匹配到标签，返回空结果
        if not matched_labels:
            return {
                'code': 200,
                'success': True,
                'message': '未找到与查询需求匹配的标签',
                'query': '',
                'data': []
            }
        
        # 步骤3: 构建Cypher生成提示文本
        logging.info("第三步：构建提示文本生成Cypher查询语句")
        
        # 将匹配的标签转换为字符串
        matched_labels_str = ", ".join([f"'{label}'" for label in matched_labels])
        
        # 构建生成Cypher的提示语
        cypher_prompt = f"""
        请根据以下Neo4j图数据库结构和已匹配的标签，生成一个Cypher查询脚本。
        
        ## 图数据库结构
        
        ### 节点
        1. talent - 人才节点
           属性: pg_id(PostgreSQL数据库ID), name_zh(中文姓名), name_en(英文姓名), 
                mobile(手机号码), email(电子邮箱), updated_at(更新时间)
        
        2. data_label - 人才标签节点
                      
        ### 关系
        BELONGS_TO - 从属关系
           (talent)-[BELONGS_TO]->(data_label) - 人才属于某标签
        
        ## 匹配的标签列表
        [{matched_labels_str}]
        
        ## 查询需求
        {query_requirement}
        
        ## 输出要求
        1. 只输出有效的Cypher查询语句，不要包含任何解释或注释
        2. 确保return语句中包含talent节点属性
        3. 尽量利用图数据库的特性来优化查询效率
        4. 使用WITH子句和COLLECT函数收集标签，确保查询到同时拥有所有标签的人才
        
        注意：请直接返回Cypher查询语句，无需任何其他文本。
        
        以下是一个示例：
        假设匹配的标签是 ['五星级酒店', '新开酒店经验', '总经理']
        
        生成的Cypher查询语句应该是：
        MATCH (t:talent)-[:BELONGS_TO]->(dl:data_label)  
        WHERE dl.name IN ['五星级酒店', '新开酒店经验', '总经理']  
        WITH t, COLLECT(DISTINCT dl.name) AS labels  
        WHERE size(labels) = 3  
        RETURN t.pg_id as pg_id, t.name_zh as name_zh, t.name_en as name_en, t.mobile as mobile, t.email as email, t.updated_at as updated_at
        """
        
        # 调用Deepseek API生成Cypher脚本
        payload = {
            "model": "deepseek-chat",
            "messages": [
                {"role": "system", "content": "你是一个专业的Neo4j Cypher查询专家。"},
                {"role": "user", "content": cypher_prompt}
            ],
            "temperature": 0.1
        }
        
        logging.info("发送请求到Deepseek API生成Cypher脚本")
        response = requests.post(api_url, headers=headers, json=payload, timeout=30)
        response.raise_for_status()
        
        # 解析API响应
        result = response.json()
        cypher_script = result.get("choices", [{}])[0].get("message", {}).get("content", "")
        
        # 清理Cypher脚本，移除不必要的markdown格式或注释
        cypher_script = cypher_script.strip()
        if cypher_script.startswith("```cypher"):
            cypher_script = cypher_script[9:]
        elif cypher_script.startswith("```"):
            cypher_script = cypher_script[3:]
        if cypher_script.endswith("```"):
            cypher_script = cypher_script[:-3]
        cypher_script = cypher_script.strip()
        
        logging.info(f"生成的Cypher脚本: {cypher_script}")
        
        # 步骤4: 执行Cypher脚本
        logging.info("第四步：执行Cypher脚本并返回结果")
        with neo4j_driver.get_session() as session:
            result = session.run(cypher_script)
            records = [record.data() for record in result]
            
        # 构建查询结果
        response_data = {
            'code': 200,
            'success': True,
            'message': '查询成功执行',
            'query': cypher_script,
            'matched_labels': matched_labels,
            'data': records
        }
        
        return response_data
        
    except requests.exceptions.HTTPError as e:
        error_msg = f"调用Deepseek API失败: {str(e)}"
        logging.error(error_msg)
        if hasattr(e, 'response') and e.response:
            logging.error(f"错误状态码: {e.response.status_code}")
            logging.error(f"错误内容: {e.response.text}")
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': []
        }
    except Exception as e:
        error_msg = f"查询Neo4j图数据库失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': []
        }

def talent_get_tags(talent_id):
    """
    根据talent ID获取人才节点关联的标签
    
    Args:
        talent_id (int): 人才节点pg_id
        
    Returns:
        dict: 包含人才ID和关联标签的字典，JSON格式
    """
    try:
        # 导入必要的模块
        from app.services.neo4j_driver import neo4j_driver
        
        # 准备查询返回数据
        response_data = {
            'code': 200,
            'success': True,
            'message': '获取人才标签成功',
            'data': []
        }
        
        # 构建Cypher查询语句，获取人才节点关联的标签
        cypher_query = """
        MATCH (t:talent)-[r:BELONGS_TO]->(tag:data_label)
        WHERE t.pg_id = $talent_id
        RETURN t.pg_id as talent_id, tag.name as tag_name
        """
        
        # 执行查询
        with neo4j_driver.get_session() as session:
            result = session.run(cypher_query, talent_id=int(talent_id))
            records = list(result)
            
            # 如果没有查询到标签，返回空数组
            if not records:
                response_data['message'] = f'人才pg_id {talent_id} 没有关联的标签'
                return response_data
            
            # 处理查询结果
            for record in records:
                talent_tag = {
                    'talent': record['talent_id'],
                    'tag': record['tag_name']
                }
                response_data['data'].append(talent_tag)
            
        return response_data
    
    except Exception as e:
        error_msg = f"获取人才标签失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': []
        }

def talent_update_tags(data):
    """
    根据传入的JSON数据为人才节点创建与标签的BELONGS_TO关系
    
    Args:
        data (list): 包含talent和tag字段的对象列表
            例如: [
                {"talent": 12345, "tag": "市场营销"},
                {"talent": 12345, "tag": "酒店管理"}
            ]
        
    Returns:
        dict: 操作结果和状态信息
    """
    try:
        # 导入必要的模块
        from app.services.neo4j_driver import neo4j_driver
        
        # 验证输入参数
        if not isinstance(data, list):
            return {
                'code': 400,
                'success': False,
                'message': '参数格式错误，需要JSON数组',
                'data': None
            }
        
        if len(data) == 0:
            return {
                'code': 400,
                'success': False,
                'message': '数据列表为空',
                'data': None
            }
        
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        # 成功和失败计数
        success_count = 0
        failed_items = []
        
        # 按talent分组处理数据
        talent_tags = {}
        for item in data:
            # 验证每个项目的格式
            if not isinstance(item, dict) or 'talent' not in item or 'tag' not in item:
                failed_items.append(item)
                continue
                
            talent_id = item.get('talent')
            tag_name = item.get('tag')
            
            # 验证talent_id和tag_name的值
            if not talent_id or not tag_name or not isinstance(tag_name, str):
                failed_items.append(item)
                continue
                
            # 按talent_id分组
            if talent_id not in talent_tags:
                talent_tags[talent_id] = []
                
            talent_tags[talent_id].append(tag_name)
        
        with neo4j_driver.get_session() as session:
            # 处理每个talent及其标签
            for talent_id, tags in talent_tags.items():
                # 首先验证talent节点是否存在
                check_talent_query = """
                MATCH (t:talent) 
                WHERE t.pg_id = $talent_id
                RETURN t
                """
                talent_result = session.run(check_talent_query, talent_id=int(talent_id))
                if not talent_result.single():
                    # 该talent不存在，记录失败项并继续下一个talent
                    for tag in tags:
                        failed_items.append({'talent_pg_id': talent_id, 'tag': tag})
                    continue
                
                # 首先清除所有现有的BELONGS_TO关系
                clear_relations_query = """
                MATCH (t:talent)-[r:BELONGS_TO]->(:data_label)
                WHERE t.pg_id = $talent_id
                DELETE r
                RETURN count(r) as deleted_count
                """
                clear_result = session.run(clear_relations_query, talent_id=int(talent_id))
                deleted_count = clear_result.single()['deleted_count']
                logging.info(f"已删除talent_id={talent_id}的{deleted_count}个已有标签关系")
                
                # 处理每个标签
                for tag_name in tags:
                    try:
                        # 1. 查找或创建标签节点
                        # 先查找是否存在该标签
                        find_tag_query = """
                        MATCH (tag:data_label)
                        WHERE tag.name = $tag_name
                        RETURN id(tag) as tag_id
                        """
                        tag_result = session.run(find_tag_query, tag_name=tag_name)
                        tag_record = tag_result.single()
                        
                        if tag_record:
                            tag_id = tag_record['tag_id']
                        else:
                            # 创建新标签
                            create_tag_query = """
                            CREATE (tag:data_label {name: $name, category: $category, updated_at: $updated_at})
                            RETURN id(tag) as tag_id
                            """
                            tag_result = session.run(
                                create_tag_query, 
                                name=tag_name,
                                category='talent',
                                updated_at=current_time
                            )
                            tag_record = tag_result.single()
                            tag_id = tag_record['tag_id']
                        
                        # 2. 创建人才与标签的BELONGS_TO关系
                        create_relation_query = """
                        MATCH (t:talent), (tag:data_label)
                        WHERE t.pg_id = $talent_id AND tag.name = $tag_name
                        CREATE (t)-[r:BELONGS_TO]->(tag)
                        SET r.created_at = $current_time
                        RETURN r
                        """
                        
                        relation_result = session.run(
                            create_relation_query,
                            talent_id=int(talent_id),
                            tag_name=tag_name,
                            current_time=current_time
                        )
                        
                        if relation_result.single():
                            success_count += 1
                        else:
                            failed_items.append({'talent_pg_id': talent_id, 'tag': tag_name})
                            
                    except Exception as tag_error:
                        logging.error(f"为标签 {tag_name} 创建关系时出错: {str(tag_error)}")
                        failed_items.append({'talent_pg_id': talent_id, 'tag': tag_name})
        
        # 返回结果
        total_items = len(data)
        if success_count == total_items:
            return {
                'code': 200,
                'success': True,
                'message': f'成功创建或更新了 {success_count} 个标签关系',
                'data': {
                    'success_count': success_count,
                    'total_count': total_items,
                    'failed_items': []
                }
            }
        elif success_count > 0:
            return {
                'code': 206, # Partial Content
                'success': True,
                'message': f'部分成功: 创建或更新了 {success_count}/{total_items} 个标签关系',
                'data': {
                    'success_count': success_count,
                    'total_count': total_items,
                    'failed_items': failed_items
                }
            }
        else:
            return {
                'code': 500,
                'success': False,
                'message': '无法创建任何标签关系',
                'data': {
                    'success_count': 0,
                    'total_count': total_items,
                    'failed_items': failed_items
                }
            }
            
    except Exception as e:
        error_msg = f"更新人才标签关系失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def get_business_card(card_id):
    """
    根据ID从PostgreSQL数据库中获取名片记录
    
    Args:
        card_id (int): 名片记录ID
        
    Returns:
        dict: 包含操作结果和名片信息的字典
    """
    try:
        # 查询指定ID的名片记录
        card = BusinessCard.query.get(card_id)
        
        if not card:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{card_id}的名片记录',
                'data': None
            }
        
        # 将记录转换为字典格式返回
        return {
            'code': 200,
            'success': True,
            'message': '获取名片记录成功',
            'data': card.to_dict()
        }
    
    except Exception as e:
        error_msg = f"获取名片记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

# 酒店职位数据模型
class HotelPosition(db.Model):
    __tablename__ = 'hotel_positions'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    department_zh = db.Column(db.String(10), nullable=False)
    department_en = db.Column(db.String(50), nullable=False)
    position_zh = db.Column(db.String(20), nullable=False)
    position_en = db.Column(db.String(100), nullable=False)
    position_abbr = db.Column(db.String(20), nullable=True)
    level_zh = db.Column(db.String(10), nullable=False)
    level_en = db.Column(db.String(30), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
    created_by = db.Column(db.String(50), default='system')
    updated_by = db.Column(db.String(50), default='system')
    status = db.Column(db.String(20), default='active')
    
    def to_dict(self):
        return {
            'id': self.id,
            'department_zh': self.department_zh,
            'department_en': self.department_en,
            'position_zh': self.position_zh,
            'position_en': self.position_en,
            'position_abbr': self.position_abbr,
            'level_zh': self.level_zh,
            'level_en': self.level_en,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S') if self.created_at else None,
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S') if self.updated_at else None,
            'created_by': self.created_by,
            'updated_by': self.updated_by,
            'status': self.status
        }

def get_hotel_positions_list():
    """
    获取酒店职位数据表的全部记录
    
    Returns:
        dict: 包含操作结果和酒店职位列表的字典
    """
    try:
        # 查询所有酒店职位记录，按部门和职位排序
        positions = HotelPosition.query.order_by(
            HotelPosition.department_zh, 
            HotelPosition.position_zh
        ).all()
        
        # 将所有记录转换为字典格式
        positions_data = [position.to_dict() for position in positions]
        
        return {
            'code': 200,
            'success': True,
            'message': '获取酒店职位列表成功',
            'data': positions_data,
            'count': len(positions_data)
        }
    
    except Exception as e:
        error_msg = f"获取酒店职位列表失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': [],
            'count': 0
        }

def add_hotel_positions(position_data):
    """
    新增酒店职位数据表记录
    
    Args:
        position_data (dict): 包含职位信息的字典，包括:
            - department_zh: 部门中文名称 (必填)
            - department_en: 部门英文名称 (必填)
            - position_zh: 职位中文名称 (必填)
            - position_en: 职位英文名称 (必填)
            - position_abbr: 职位英文缩写 (可选)
            - level_zh: 职级中文名称 (必填)
            - level_en: 职级英文名称 (必填)
            - created_by: 创建者 (可选，默认为'system')
            - updated_by: 更新者 (可选，默认为'system')
            - status: 状态 (可选，默认为'active')
    
    Returns:
        dict: 包含操作结果和创建的职位信息的字典
    """
    try:
        # 验证必填字段
        required_fields = ['department_zh', 'department_en', 'position_zh', 'position_en', 'level_zh', 'level_en']
        missing_fields = []
        
        for field in required_fields:
            if field not in position_data or not position_data[field] or not position_data[field].strip():
                missing_fields.append(field)
        
        if missing_fields:
            return {
                'code': 400,
                'success': False,
                'message': f'缺少必填字段: {", ".join(missing_fields)}',
                'data': None
            }
        
        # 检查是否已存在相同的职位记录（基于部门和职位的中文名称）
        existing_position = HotelPosition.query.filter_by(
            department_zh=position_data['department_zh'].strip(),
            position_zh=position_data['position_zh'].strip()
        ).first()
        
        if existing_position:
            return {
                'code': 409,
                'success': False,
                'message': f'职位记录已存在：{position_data["department_zh"]} - {position_data["position_zh"]}',
                'data': existing_position.to_dict()
            }
        
        # 创建新的职位记录
        new_position = HotelPosition(
            department_zh=position_data['department_zh'].strip(),
            department_en=position_data['department_en'].strip(),
            position_zh=position_data['position_zh'].strip(),
            position_en=position_data['position_en'].strip(),
            position_abbr=position_data.get('position_abbr', '').strip() if position_data.get('position_abbr') else None,
            level_zh=position_data['level_zh'].strip(),
            level_en=position_data['level_en'].strip(),
            created_by=position_data.get('created_by', 'system'),
            updated_by=position_data.get('updated_by', 'system'),
            status=position_data.get('status', 'active')
        )
        
        # 保存到数据库
        db.session.add(new_position)
        db.session.commit()
        
        logging.info(f"成功创建酒店职位记录，ID: {new_position.id}")
        
        return {
            'code': 200,
            'success': True,
            'message': '酒店职位记录创建成功',
            'data': new_position.to_dict()
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"创建酒店职位记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def update_hotel_positions(position_id, position_data):
    """
    修改酒店职位数据表记录
    
    Args:
        position_id (int): 职位记录ID
        position_data (dict): 包含要更新的职位信息的字典，可能包括:
            - department_zh: 部门中文名称
            - department_en: 部门英文名称
            - position_zh: 职位中文名称
            - position_en: 职位英文名称
            - position_abbr: 职位英文缩写
            - level_zh: 职级中文名称
            - level_en: 职级英文名称
            - updated_by: 更新者
            - status: 状态
    
    Returns:
        dict: 包含操作结果和更新后的职位信息的字典
    """
    try:
        # 查找要更新的职位记录
        position = HotelPosition.query.get(position_id)
        
        if not position:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{position_id}的职位记录',
                'data': None
            }
        
        # 检查是否有数据需要更新
        if not position_data:
            return {
                'code': 400,
                'success': False,
                'message': '请求数据为空',
                'data': None
            }
        
        # 如果要更新部门和职位名称，检查是否会与其他记录冲突
        new_department_zh = position_data.get('department_zh', position.department_zh).strip() if position_data.get('department_zh') else position.department_zh
        new_position_zh = position_data.get('position_zh', position.position_zh).strip() if position_data.get('position_zh') else position.position_zh
        
        # 查找是否存在相同的职位记录（排除当前记录）
        existing_position = HotelPosition.query.filter(
            HotelPosition.id != position_id,
            HotelPosition.department_zh == new_department_zh,
            HotelPosition.position_zh == new_position_zh
        ).first()
        
        if existing_position:
            return {
                'code': 409,
                'success': False,
                'message': f'职位记录已存在：{new_department_zh} - {new_position_zh}',
                'data': existing_position.to_dict()
            }
        
        # 更新职位信息
        if 'department_zh' in position_data and position_data['department_zh']:
            position.department_zh = position_data['department_zh'].strip()
        
        if 'department_en' in position_data and position_data['department_en']:
            position.department_en = position_data['department_en'].strip()
        
        if 'position_zh' in position_data and position_data['position_zh']:
            position.position_zh = position_data['position_zh'].strip()
        
        if 'position_en' in position_data and position_data['position_en']:
            position.position_en = position_data['position_en'].strip()
        
        if 'position_abbr' in position_data:
            # 处理position_abbr，可能为空字符串或None
            if position_data['position_abbr'] and position_data['position_abbr'].strip():
                position.position_abbr = position_data['position_abbr'].strip()
            else:
                position.position_abbr = None
        
        if 'level_zh' in position_data and position_data['level_zh']:
            position.level_zh = position_data['level_zh'].strip()
        
        if 'level_en' in position_data and position_data['level_en']:
            position.level_en = position_data['level_en'].strip()
        
        if 'updated_by' in position_data:
            position.updated_by = position_data['updated_by'] or 'system'
        
        if 'status' in position_data:
            position.status = position_data['status'] or 'active'
        
        # 更新时间会自动设置（onupdate=datetime.now）
        
        # 保存更新
        db.session.commit()
        
        logging.info(f"成功更新酒店职位记录，ID: {position.id}")
        
        return {
            'code': 200,
            'success': True,
            'message': '酒店职位记录更新成功',
            'data': position.to_dict()
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"更新酒店职位记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def query_hotel_positions(position_id):
    """
    查找指定ID的酒店职位数据表记录
    
    Args:
        position_id (int): 职位记录ID
    
    Returns:
        dict: 包含操作结果和职位信息的字典
    """
    try:
        # 根据ID查找职位记录
        position = HotelPosition.query.get(position_id)
        
        if not position:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{position_id}的职位记录',
                'data': None
            }
        
        # 返回找到的记录
        return {
            'code': 200,
            'success': True,
            'message': '查找职位记录成功',
            'data': position.to_dict()
        }
    
    except Exception as e:
        error_msg = f"查找职位记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def delete_hotel_positions(position_id):
    """
    删除指定ID的酒店职位数据表记录
    
    Args:
        position_id (int): 职位记录ID
    
    Returns:
        dict: 包含操作结果的字典
    """
    try:
        # 根据ID查找要删除的职位记录
        position = HotelPosition.query.get(position_id)
        
        if not position:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{position_id}的职位记录',
                'data': None
            }
        
        # 保存被删除记录的信息，用于返回
        deleted_position_info = position.to_dict()
        
        # 执行删除操作
        db.session.delete(position)
        db.session.commit()
        
        logging.info(f"成功删除酒店职位记录，ID: {position_id}")
        
        return {
            'code': 200,
            'success': True,
            'message': '职位记录删除成功',
            'data': deleted_position_info
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"删除职位记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

# 酒店集团子品牌数据模型
class HotelGroupBrands(db.Model):
    __tablename__ = 'hotel_group_brands'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    group_name_en = db.Column(db.String(60), nullable=False)
    group_name_zh = db.Column(db.String(20), nullable=False)
    brand_name_en = db.Column(db.String(40), nullable=False)
    brand_name_zh = db.Column(db.String(40), nullable=False)
    positioning_level_en = db.Column(db.String(20), nullable=False)
    positioning_level_zh = db.Column(db.String(5), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.now, nullable=False)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
    created_by = db.Column(db.String(50), default='system')
    updated_by = db.Column(db.String(50), default='system')
    status = db.Column(db.String(20), default='active')
    
    def to_dict(self):
        return {
            'id': self.id,
            'group_name_en': self.group_name_en,
            'group_name_zh': self.group_name_zh,
            'brand_name_en': self.brand_name_en,
            'brand_name_zh': self.brand_name_zh,
            'positioning_level_en': self.positioning_level_en,
            'positioning_level_zh': self.positioning_level_zh,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S') if self.created_at else None,
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S') if self.updated_at else None,
            'created_by': self.created_by,
            'updated_by': self.updated_by,
            'status': self.status
        }

def get_hotel_group_brands_list():
    """
    获取酒店集团子品牌数据表的全部记录
    
    Returns:
        dict: 包含操作结果和酒店集团品牌列表的字典
    """
    try:
        # 查询所有酒店集团品牌记录，按集团和品牌排序
        brands = HotelGroupBrands.query.order_by(
            HotelGroupBrands.group_name_zh, 
            HotelGroupBrands.brand_name_zh
        ).all()
        
        # 将所有记录转换为字典格式
        brands_data = [brand.to_dict() for brand in brands]
        
        return {
            'code': 200,
            'success': True,
            'message': '获取酒店集团品牌列表成功',
            'data': brands_data,
            'count': len(brands_data)
        }
    
    except Exception as e:
        error_msg = f"获取酒店集团品牌列表失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': [],
            'count': 0
        }

def add_hotel_group_brands(brand_data):
    """
    新增酒店集团子品牌数据表记录
    
    Args:
        brand_data (dict): 包含品牌信息的字典，包括:
            - group_name_en: 集团英文名称 (必填)
            - group_name_zh: 集团中文名称 (必填)
            - brand_name_en: 品牌英文名称 (必填)
            - brand_name_zh: 品牌中文名称 (必填)
            - positioning_level_en: 定位级别英文名称 (必填)
            - positioning_level_zh: 定位级别中文名称 (必填)
            - created_by: 创建者 (可选，默认为'system')
            - updated_by: 更新者 (可选，默认为'system')
            - status: 状态 (可选，默认为'active')
    
    Returns:
        dict: 包含操作结果和创建的品牌信息的字典
    """
    try:
        # 验证必填字段
        required_fields = ['group_name_en', 'group_name_zh', 'brand_name_en', 'brand_name_zh', 'positioning_level_en', 'positioning_level_zh']
        missing_fields = []
        
        for field in required_fields:
            if field not in brand_data or not brand_data[field] or not brand_data[field].strip():
                missing_fields.append(field)
        
        if missing_fields:
            return {
                'code': 400,
                'success': False,
                'message': f'缺少必填字段: {", ".join(missing_fields)}',
                'data': None
            }
        
        # 检查是否已存在相同的品牌记录（基于集团和品牌的中文名称）
        existing_brand = HotelGroupBrands.query.filter_by(
            group_name_zh=brand_data['group_name_zh'].strip(),
            brand_name_zh=brand_data['brand_name_zh'].strip()
        ).first()
        
        
        if existing_brand:
            return {
                'code': 409,
                'success': False,
                'message': f'品牌记录已存在：{brand_data["group_name_zh"]} - {brand_data["brand_name_zh"]}',
                'data': existing_brand.to_dict()
            }
        
        # 创建新的品牌记录
        new_brand = HotelGroupBrands(
            group_name_en=brand_data['group_name_en'].strip(),
            group_name_zh=brand_data['group_name_zh'].strip(),
            brand_name_en=brand_data['brand_name_en'].strip(),
            brand_name_zh=brand_data['brand_name_zh'].strip(),
            positioning_level_en=brand_data['positioning_level_en'].strip(),
            positioning_level_zh=brand_data['positioning_level_zh'].strip(),
            created_by=brand_data.get('created_by', 'system'),
            updated_by=brand_data.get('updated_by', 'system'),
            status=brand_data.get('status', 'active')
        )
        
        # 保存到数据库
        db.session.add(new_brand)
        db.session.commit()
        
        logging.info(f"成功创建酒店集团品牌记录，ID: {new_brand.id}")
        
        return {
            'code': 200,
            'success': True,
            'message': '酒店集团品牌记录创建成功',
            'data': new_brand.to_dict()
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"创建酒店集团品牌记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def update_hotel_group_brands(brand_id, brand_data):
    """
    修改酒店集团子品牌数据表记录
    
    Args:
        brand_id (int): 品牌记录ID
        brand_data (dict): 包含要更新的品牌信息的字典，可能包括:
            - group_name_en: 集团英文名称
            - group_name_zh: 集团中文名称
            - brand_name_en: 品牌英文名称
            - brand_name_zh: 品牌中文名称
            - positioning_level_en: 定位级别英文名称
            - positioning_level_zh: 定位级别中文名称
            - updated_by: 更新者
            - status: 状态
    
    Returns:
        dict: 包含操作结果和更新后的品牌信息的字典
    """
    try:
        # 查找要更新的品牌记录
        brand = HotelGroupBrands.query.get(brand_id)
        
        if not brand:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{brand_id}的品牌记录',
                'data': None
            }
        
        # 检查是否有数据需要更新
        if not brand_data:
            return {
                'code': 400,
                'success': False,
                'message': '请求数据为空',
                'data': None
            }
        
        # 如果要更新集团和品牌名称，检查是否会与其他记录冲突
        new_group_name_zh = brand_data.get('group_name_zh', brand.group_name_zh).strip() if brand_data.get('group_name_zh') else brand.group_name_zh
        new_brand_name_zh = brand_data.get('brand_name_zh', brand.brand_name_zh).strip() if brand_data.get('brand_name_zh') else brand.brand_name_zh
        
        # 查找是否存在相同的品牌记录（排除当前记录）
        existing_brand = HotelGroupBrands.query.filter(
            HotelGroupBrands.id != brand_id,
            HotelGroupBrands.group_name_zh == new_group_name_zh,
            HotelGroupBrands.brand_name_zh == new_brand_name_zh
        ).first()
        
        if existing_brand:
            return {
                'code': 409,
                'success': False,
                'message': f'品牌记录已存在：{new_group_name_zh} - {new_brand_name_zh}',
                'data': existing_brand.to_dict()
            }
        
        # 更新品牌信息
        if 'group_name_en' in brand_data and brand_data['group_name_en']:
            brand.group_name_en = brand_data['group_name_en'].strip()
        
        if 'group_name_zh' in brand_data and brand_data['group_name_zh']:
            brand.group_name_zh = brand_data['group_name_zh'].strip()
        
        if 'brand_name_en' in brand_data and brand_data['brand_name_en']:
            brand.brand_name_en = brand_data['brand_name_en'].strip()
        
        if 'brand_name_zh' in brand_data and brand_data['brand_name_zh']:
            brand.brand_name_zh = brand_data['brand_name_zh'].strip()
        
        if 'positioning_level_en' in brand_data and brand_data['positioning_level_en']:
            brand.positioning_level_en = brand_data['positioning_level_en'].strip()
        
        if 'positioning_level_zh' in brand_data and brand_data['positioning_level_zh']:
            brand.positioning_level_zh = brand_data['positioning_level_zh'].strip()
        
        if 'updated_by' in brand_data:
            brand.updated_by = brand_data['updated_by'] or 'system'
        
        if 'status' in brand_data:
            brand.status = brand_data['status'] or 'active'
        
        # 更新时间会自动设置（onupdate=datetime.now）
        
        # 保存更新
        db.session.commit()
        
        logging.info(f"成功更新酒店集团品牌记录，ID: {brand.id}")
        
        return {
            'code': 200,
            'success': True,
            'message': '酒店集团品牌记录更新成功',
            'data': brand.to_dict()
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"更新酒店集团品牌记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def query_hotel_group_brands(brand_id):
    """
    查找指定ID的酒店集团子品牌数据表记录
    
    Args:
        brand_id (int): 品牌记录ID
    
    Returns:
        dict: 包含操作结果和品牌信息的字典
    """
    try:
        # 根据ID查找品牌记录
        brand = HotelGroupBrands.query.get(brand_id)
        
        if not brand:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{brand_id}的品牌记录',
                'data': None
            }
        
        # 返回找到的记录
        return {
            'code': 200,
            'success': True,
            'message': '查找品牌记录成功',
            'data': brand.to_dict()
        }
    
    except Exception as e:
        error_msg = f"查找品牌记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def delete_hotel_group_brands(brand_id):
    """
    删除指定ID的酒店集团子品牌数据表记录
    
    Args:
        brand_id (int): 品牌记录ID
    
    Returns:
        dict: 包含操作结果的字典
    """
    try:
        # 根据ID查找要删除的品牌记录
        brand = HotelGroupBrands.query.get(brand_id)
        
        if not brand:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到ID为{brand_id}的品牌记录',
                'data': None
            }
        
        # 保存被删除记录的信息，用于返回
        deleted_brand_info = brand.to_dict()
        
        # 执行删除操作
        db.session.delete(brand)
        db.session.commit()
        
        logging.info(f"成功删除酒店集团品牌记录，ID: {brand_id}")
        
        return {
            'code': 200,
            'success': True,
            'message': '品牌记录删除成功',
            'data': deleted_brand_info
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"删除品牌记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def get_duplicate_records(status=None):
    """
    获取重复记录列表
    
    Args:
        status (str, optional): 筛选特定状态的记录 ('pending', 'processed', 'ignored')
        
    Returns:
        dict: 包含操作结果和重复记录列表
    """
    try:
        # 构建查询
        query = DuplicateBusinessCard.query
        if status:
            query = query.filter_by(processing_status=status)
        
        # 按创建时间倒序排列
        duplicate_records = query.order_by(DuplicateBusinessCard.created_at.desc()).all()
        
        # 获取详细信息，包括主记录
        records_data = []
        for record in duplicate_records:
            record_dict = record.to_dict()
            # 添加主记录信息
            if record.main_card:
                record_dict['main_card'] = record.main_card.to_dict()
            records_data.append(record_dict)
        
        return {
            'code': 200,
            'success': True,
            'message': '获取重复记录列表成功',
            'data': records_data,
            'count': len(records_data)
        }
    
    except Exception as e:
        error_msg = f"获取重复记录列表失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': [],
            'count': 0
        }


def process_duplicate_record(duplicate_id, action, selected_duplicate_id=None, processed_by=None, notes=None):
    """
    处理重复记录
    
    Args:
        duplicate_id (int): 名片记录ID（对应DuplicateBusinessCard表中的main_card_id字段）
        action (str): 处理动作 ('merge_to_suspected', 'keep_main', 'ignore')
        selected_duplicate_id (int, optional): 当action为'merge_to_suspected'时，选择的疑似重复记录ID
        processed_by (str, optional): 处理人
        notes (str, optional): 处理备注
        
    Returns:
        dict: 包含操作结果
    """
    try:
        # 查找重复记录 - 使用main_card_id字段匹配
        duplicate_record = DuplicateBusinessCard.query.filter_by(main_card_id=duplicate_id).first()
        if not duplicate_record:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到main_card_id为{duplicate_id}的重复记录',
                'data': None
            }
        
        if duplicate_record.processing_status != 'pending':
            return {
                'code': 400,
                'success': False,
                'message': f'重复记录状态为{duplicate_record.processing_status}，无法处理',
                'data': None
            }
        
        main_card = duplicate_record.main_card
        if not main_card:
            return {
                'code': 404,
                'success': False,
                'message': '未找到对应的主记录',
                'data': None
            }
        
        result_data = None
        
        if action == 'merge_to_suspected':
            # 合并到选中的疑似重复记录
            if not selected_duplicate_id:
                return {
                    'code': 400,
                    'success': False,
                    'message': '执行合并操作时必须提供selected_duplicate_id',
                    'data': None
                }
            
            # 查找选中的疑似重复记录
            target_card = BusinessCard.query.get(selected_duplicate_id)
            if not target_card:
                return {
                    'code': 404,
                    'success': False,
                    'message': f'未找到ID为{selected_duplicate_id}的目标记录',
                    'data': None
                }
            
            # 将主记录的信息合并到目标记录，并更新职业轨迹
            target_card.name_en = main_card.name_en or target_card.name_en
            target_card.title_zh = main_card.title_zh or target_card.title_zh
            target_card.title_en = main_card.title_en or target_card.title_en
            target_card.mobile = main_card.mobile or target_card.mobile
            target_card.phone = main_card.phone or target_card.phone
            target_card.email = main_card.email or target_card.email
            target_card.hotel_zh = main_card.hotel_zh or target_card.hotel_zh
            target_card.hotel_en = main_card.hotel_en or target_card.hotel_en
            target_card.address_zh = main_card.address_zh or target_card.address_zh
            target_card.address_en = main_card.address_en or target_card.address_en
            target_card.postal_code_zh = main_card.postal_code_zh or target_card.postal_code_zh
            target_card.postal_code_en = main_card.postal_code_en or target_card.postal_code_en
            target_card.brand_zh = main_card.brand_zh or target_card.brand_zh
            target_card.brand_en = main_card.brand_en or target_card.brand_en
            target_card.affiliation_zh = main_card.affiliation_zh or target_card.affiliation_zh
            target_card.affiliation_en = main_card.affiliation_en or target_card.affiliation_en
            target_card.birthday = main_card.birthday or target_card.birthday
            target_card.residence = main_card.residence or target_card.residence
            target_card.brand_group = main_card.brand_group or target_card.brand_group
            target_card.image_path = main_card.image_path  # 更新为最新的MinIO图片路径
            target_card.updated_by = processed_by or 'system'
            
            # 更新职业轨迹，使用主记录的图片路径
            new_data = {
                'hotel_zh': main_card.hotel_zh,
                'hotel_en': main_card.hotel_en,
                'title_zh': main_card.title_zh,
                'title_en': main_card.title_en
            }
            target_card.career_path = update_career_path(target_card, new_data, main_card.image_path)
            
            # 先删除重复记录表中的记录，避免外键约束冲突
            db.session.delete(duplicate_record)
            
            # 然后删除主记录
            db.session.delete(main_card)
            
            result_data = target_card.to_dict()
            
        elif action == 'keep_main':
            # 保留主记录，不做任何合并
            result_data = main_card.to_dict()
            
        elif action == 'ignore':
            # 忽略，不做任何操作
            result_data = main_card.to_dict()
        
        # 更新重复记录状态（只有在非merge_to_suspected操作时才更新，因为merge_to_suspected已经删除了记录）
        if action != 'merge_to_suspected':
            duplicate_record.processing_status = 'processed'
            duplicate_record.processed_at = datetime.now()
            duplicate_record.processed_by = processed_by or 'system'
            duplicate_record.processing_notes = notes or f'执行操作: {action}'
        
        db.session.commit()
        
        logging.info(f"成功处理重复记录，main_card_id: {duplicate_id}，操作: {action}")
        
        return {
            'code': 200,
            'success': True,
            'message': f'重复记录处理成功，操作: {action}',
            'data': {
                'duplicate_record': duplicate_record.to_dict(),
                'result': result_data
            }
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"处理重复记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def get_duplicate_record_detail(duplicate_id):
    """
    获取指定重复记录的详细信息
    
    Args:
        duplicate_id (int): 名片记录ID（对应DuplicateBusinessCard表中的main_card_id字段）
        
    Returns:
        dict: 包含重复记录详细信息
    """
    try:
        # 查找重复记录 - 使用main_card_id字段匹配
        duplicate_record = DuplicateBusinessCard.query.filter_by(main_card_id=duplicate_id).first()
        if not duplicate_record:
            return {
                'code': 404,
                'success': False,
                'message': f'未找到main_card_id为{duplicate_id}的重复记录',
                'data': None
            }
        
        # 构建详细信息
        record_dict = duplicate_record.to_dict()
        
        # 添加主记录信息
        if duplicate_record.main_card:
            record_dict['main_card'] = duplicate_record.main_card.to_dict()
        else:
            record_dict['main_card'] = None
        
        # 解析suspected_duplicates字段中的JSON信息，并获取详细的名片信息
        suspected_duplicates_details = []
        if duplicate_record.suspected_duplicates:
            try:
                # 确保suspected_duplicates是列表格式
                suspected_list = duplicate_record.suspected_duplicates
                if not isinstance(suspected_list, list):
                    logging.warning(f"suspected_duplicates不是列表格式: {type(suspected_list)}")
                    suspected_list = []
                
                # 遍历每个疑似重复记录ID
                for suspected_item in suspected_list:
                    try:
                        # 支持两种格式：直接ID或包含ID的字典
                        if isinstance(suspected_item, dict):
                            card_id = suspected_item.get('id')
                        else:
                            card_id = suspected_item
                        
                        if card_id:
                            # 调用get_business_card函数获取详细信息
                            card_result = get_business_card(card_id)
                            if card_result['success'] and card_result['data']:
                                suspected_duplicates_details.append(card_result['data'])
                                logging.info(f"成功获取疑似重复记录详情，ID: {card_id}")
                            else:
                                logging.warning(f"无法获取疑似重复记录详情，ID: {card_id}, 原因: {card_result['message']}")
                                # 添加错误信息记录
                                suspected_duplicates_details.append({
                                    'id': card_id,
                                    'error': card_result['message'],
                                    'success': False
                                })
                        else:
                            logging.warning(f"疑似重复记录项缺少ID信息: {suspected_item}")
                    
                    except Exception as item_error:
                        logging.error(f"处理疑似重复记录项时出错: {suspected_item}, 错误: {str(item_error)}")
                        suspected_duplicates_details.append({
                            'original_item': suspected_item,
                            'error': f"处理出错: {str(item_error)}",
                            'success': False
                        })
                
            except Exception as parse_error:
                logging.error(f"解析suspected_duplicates JSON时出错: {str(parse_error)}")
                suspected_duplicates_details = [{
                    'error': f"解析JSON出错: {str(parse_error)}",
                    'original_data': duplicate_record.suspected_duplicates,
                    'success': False
                }]
        
        # 将详细的疑似重复记录信息添加到返回数据中
        record_dict['suspected_duplicates_details'] = suspected_duplicates_details
        record_dict['suspected_duplicates_count'] = len(suspected_duplicates_details)
        
        return {
            'code': 200,
            'success': True,
            'message': '获取重复记录详情成功',
            'data': record_dict
        }
    
    except Exception as e:
        error_msg = f"获取重复记录详情失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }

def fix_broken_duplicate_records():
    """
    修复duplicate_business_cards表中main_card_id为null的损坏记录
    
    Returns:
        dict: 修复操作的结果
    """
    try:
        # 查找所有main_card_id为null的记录
        broken_records = DuplicateBusinessCard.query.filter(
            DuplicateBusinessCard.main_card_id.is_(None)
        ).all()
        
        if not broken_records:
            return {
                'code': 200,
                'success': True,
                'message': '没有发现需要修复的损坏记录',
                'data': {
                    'fixed_count': 0,
                    'total_broken': 0
                }
            }
        
        # 记录要删除的记录信息
        broken_info = []
        for record in broken_records:
            broken_info.append({
                'id': record.id,
                'duplicate_reason': record.duplicate_reason,
                'processing_status': record.processing_status,
                'created_at': record.created_at.strftime('%Y-%m-%d %H:%M:%S') if record.created_at else None,
                'processed_at': record.processed_at.strftime('%Y-%m-%d %H:%M:%S') if record.processed_at else None
            })
        
        # 删除所有损坏的记录
        for record in broken_records:
            db.session.delete(record)
        
        # 提交事务
        db.session.commit()
        
        logging.info(f"成功修复并删除了{len(broken_records)}条损坏的重复记录")
        
        return {
            'code': 200,
            'success': True,
            'message': f'成功修复并删除了{len(broken_records)}条损坏的重复记录',
            'data': {
                'fixed_count': len(broken_records),
                'total_broken': len(broken_records),
                'deleted_records': broken_info
            }
        }
    
    except Exception as e:
        db.session.rollback()
        error_msg = f"修复损坏记录失败: {str(e)}"
        logging.error(error_msg, exc_info=True)
        
        return {
            'code': 500,
            'success': False,
            'message': error_msg,
            'data': None
        }