#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
企业经营AI智能体问答数据库查询脚本
为AI智能体提供数据库查询接口
"""

import sqlite3
import json
from typing import List, Dict, Optional

class QADatabase:
    """问答数据库查询类"""
    
    def __init__(self, db_path: str = '/root/老业务网站8.30/ai_agent_qa.db'):
        """初始化数据库连接"""
        self.db_path = db_path
        self.conn = None
    
    def connect(self):
        """连接数据库"""
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.conn.row_factory = sqlite3.Row  # 使结果可以通过列名访问
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return False
    
    def disconnect(self):
        """断开数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
    
    def search_by_keyword(self, keyword: str, language: str = 'zh', limit: int = 10) -> List[Dict]:
        """根据关键词搜索问答"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        query = '''
        SELECT id, question, answer, category, keywords, language
        FROM qa_pairs
        WHERE (keywords LIKE ? OR question LIKE ? OR answer LIKE ?)
        AND language = ?
        ORDER BY id
        LIMIT ?
        '''
        
        keyword_pattern = f'%{keyword}%'
        cursor.execute(query, (keyword_pattern, keyword_pattern, keyword_pattern, language, limit))
        results = cursor.fetchall()
        
        return [dict(row) for row in results]
    
    def get_by_category(self, category: str, language: str = 'zh', limit: int = 10) -> List[Dict]:
        """根据分类获取问答"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        query = '''
        SELECT id, question, answer, category, keywords, language
        FROM qa_pairs
        WHERE category = ? AND language = ?
        ORDER BY id
        LIMIT ?
        '''
        
        cursor.execute(query, (category, language, limit))
        results = cursor.fetchall()
        
        return [dict(row) for row in results]
    
    def get_all_categories(self, language: str = 'zh') -> List[str]:
        """获取所有分类"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        query = '''
        SELECT DISTINCT category
        FROM qa_pairs
        WHERE language = ?
        ORDER BY category
        '''
        
        cursor.execute(query, (language,))
        results = cursor.fetchall()
        
        return [row[0] for row in results]
    
    def get_random_qa(self, language: str = 'zh', limit: int = 5) -> List[Dict]:
        """获取随机问答"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        query = '''
        SELECT id, question, answer, category, keywords, language
        FROM qa_pairs
        WHERE language = ?
        ORDER BY RANDOM()
        LIMIT ?
        '''
        
        cursor.execute(query, (language, limit))
        results = cursor.fetchall()
        
        return [dict(row) for row in results]
    
    def get_qa_by_id(self, qa_id: int) -> Optional[Dict]:
        """根据ID获取问答"""
        if not self.conn:
            return None
        
        cursor = self.conn.cursor()
        query = '''
        SELECT id, question, answer, category, keywords, language
        FROM qa_pairs
        WHERE id = ?
        '''
        
        cursor.execute(query, (qa_id,))
        result = cursor.fetchone()
        
        return dict(result) if result else None
    
    def search_similar_questions(self, question: str, language: str = 'zh', limit: int = 5) -> List[Dict]:
        """搜索相似问题"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        # 使用LIKE模糊匹配
        query = '''
        SELECT id, question, answer, category, keywords, language
        FROM qa_pairs
        WHERE question LIKE ? AND language = ?
        ORDER BY id
        LIMIT ?
        '''
        
        question_pattern = f'%{question}%'
        cursor.execute(query, (question_pattern, language, limit))
        results = cursor.fetchall()
        
        return [dict(row) for row in results]
    
    def get_statistics(self) -> Dict:
        """获取数据库统计信息"""
        if not self.conn:
            return {}
        
        cursor = self.conn.cursor()
        
        # 总记录数
        cursor.execute('SELECT COUNT(*) FROM qa_pairs')
        total_count = cursor.fetchone()[0]
        
        # 按语言统计
        cursor.execute('SELECT language, COUNT(*) FROM qa_pairs GROUP BY language')
        language_stats = dict(cursor.fetchall())
        
        # 按分类统计
        cursor.execute('SELECT category, COUNT(*) FROM qa_pairs GROUP BY category ORDER BY COUNT(*) DESC')
        category_stats = dict(cursor.fetchall())
        
        return {
            'total_count': total_count,
            'language_stats': language_stats,
            'category_stats': category_stats
        }

def main():
    """主函数 - 演示查询功能"""
    print("企业经营AI智能体问答数据库查询演示")
    print("=" * 50)
    
    # 创建数据库查询实例
    qa_db = QADatabase()
    
    # 连接数据库
    if not qa_db.connect():
        print("数据库连接失败")
        return
    
    # 获取统计信息
    stats = qa_db.get_statistics()
    print(f"数据库统计信息:")
    print(f"  总记录数: {stats['total_count']}")
    print(f"  语言分布: {stats['language_stats']}")
    print()
    
    # 演示各种查询功能
    print("1. 根据关键词搜索 (关键词: 'AI')")
    results = qa_db.search_by_keyword('AI', 'zh', 3)
    for i, result in enumerate(results, 1):
        print(f"  {i}. {result['question']}")
        print(f"     答案: {result['answer'][:100]}...")
        print()
    
    print("2. 根据分类获取 (分类: '产品功能')")
    results = qa_db.get_by_category('产品功能', 'zh', 3)
    for i, result in enumerate(results, 1):
        print(f"  {i}. {result['question']}")
        print(f"     答案: {result['answer'][:100]}...")
        print()
    
    print("3. 获取所有分类")
    categories = qa_db.get_all_categories('zh')
    print(f"  中文分类: {', '.join(categories[:10])}...")
    print()
    
    print("4. 随机问答")
    results = qa_db.get_random_qa('zh', 2)
    for i, result in enumerate(results, 1):
        print(f"  {i}. {result['question']}")
        print(f"     答案: {result['answer'][:100]}...")
        print()
    
    print("5. 相似问题搜索 (问题: '网站')")
    results = qa_db.search_similar_questions('网站', 'zh', 3)
    for i, result in enumerate(results, 1):
        print(f"  {i}. {result['question']}")
        print(f"     答案: {result['answer'][:100]}...")
        print()
    
    # 断开连接
    qa_db.disconnect()
    print("查询演示完成")

# AI智能体调用接口
def get_ai_response(user_question: str, language: str = 'zh') -> str:
    """
    AI智能体调用接口
    根据用户问题返回最相关的答案
    """
    qa_db = QADatabase()
    
    if not qa_db.connect():
        return "抱歉，数据库连接失败，无法获取答案。"
    
    try:
        # 首先尝试精确匹配
        results = qa_db.search_similar_questions(user_question, language, 1)
        
        if results:
            return results[0]['answer']
        
        # 如果没有精确匹配，尝试关键词搜索
        # 提取关键词进行搜索
        keywords = user_question.split()
        for keyword in keywords:
            if len(keyword) > 2:  # 只搜索长度大于2的关键词
                results = qa_db.search_by_keyword(keyword, language, 1)
                if results:
                    return results[0]['answer']
        
        # 如果还是没有找到，返回通用回答
        return "抱歉，我没有找到相关问题的答案。您可以尝试重新表述问题，或者通过网站联系表单获取更详细的帮助。"
        
    except Exception as e:
        return f"查询过程中出现错误: {str(e)}"
    finally:
        qa_db.disconnect()

if __name__ == "__main__":
    main()