# -*- coding: utf-8 -*-
"""
收藏表查询脚本
用于查看收藏表中的数据
"""
from src.flask_prompt_master import create_app, db
from src.flask_prompt_master.models.favorites import Favorite
from sqlalchemy import func

def query_favorites():
    """查询收藏表数据"""
    app = create_app()
    
    with app.app_context():
        print("=" * 60)
        print("收藏表数据查询")
        print("=" * 60)
        
        try:
            # 1. 基本统计
            total_count = Favorite.query.count()
            print(f"📊 总收藏数: {total_count}")
            
            if total_count == 0:
                print("📭 收藏表中暂无数据")
                return
            
            # 2. 按用户统计
            print(f"\n👥 用户统计:")
            user_stats = db.session.query(
                Favorite.user_id, 
                func.count(Favorite.id).label('count')
            ).group_by(Favorite.user_id).order_by(func.count(Favorite.id).desc()).all()
            
            for user_id, count in user_stats:
                print(f"   {user_id}: {count} 个收藏")
            
            # 3. 按分类统计
            print(f"\n📂 分类统计:")
            category_stats = db.session.query(
                Favorite.category, 
                func.count(Favorite.id).label('count')
            ).group_by(Favorite.category).order_by(func.count(Favorite.id).desc()).all()
            
            for category, count in category_stats:
                if category:
                    print(f"   {category}: {count} 个收藏")
                else:
                    print(f"   未分类: {count} 个收藏")
            
            # 4. 最近收藏
            print(f"\n🕒 最近收藏 (最新5个):")
            recent_favorites = Favorite.query.order_by(Favorite.created_time.desc()).limit(5).all()
            
            for i, fav in enumerate(recent_favorites, 1):
                print(f"   {i}. ID: {fav.id}")
                print(f"      用户: {fav.user_id}")
                print(f"      内容: {fav.original_text[:60]}...")
                print(f"      分类: {fav.category or '未分类'}")
                print(f"      时间: {fav.created_time}")
                if fav.notes:
                    print(f"      备注: {fav.notes}")
                print()
            
            # 5. 按行业统计
            print(f"\n🏢 行业统计:")
            industry_stats = db.session.query(
                Favorite.industry, 
                func.count(Favorite.id).label('count')
            ).group_by(Favorite.industry).order_by(func.count(Favorite.id).desc()).all()
            
            for industry, count in industry_stats:
                if industry:
                    print(f"   {industry}: {count} 个收藏")
                else:
                    print(f"   未指定行业: {count} 个收藏")
            
            # 6. 按职业统计
            print(f"\n💼 职业统计:")
            profession_stats = db.session.query(
                Favorite.profession, 
                func.count(Favorite.id).label('count')
            ).group_by(Favorite.profession).order_by(func.count(Favorite.id).desc()).all()
            
            for profession, count in profession_stats:
                if profession:
                    print(f"   {profession}: {count} 个收藏")
                else:
                    print(f"   未指定职业: {count} 个收藏")
            
            # 7. 有备注的收藏
            print(f"\n📝 有备注的收藏:")
            noted_favorites = Favorite.query.filter(Favorite.notes.isnot(None)).filter(Favorite.notes != '').all()
            print(f"   有备注的收藏数: {len(noted_favorites)}")
            
            for fav in noted_favorites[:3]:  # 只显示前3个
                print(f"   - ID {fav.id}: {fav.notes[:50]}...")
            
            if len(noted_favorites) > 3:
                print(f"   ... 还有 {len(noted_favorites) - 3} 个有备注的收藏")
            
            print("\n" + "=" * 60)
            print("✅ 查询完成！")
            print("=" * 60)
            
        except Exception as e:
            print(f"❌ 查询过程中出现错误: {str(e)}")

def search_favorites(keyword):
    """搜索收藏"""
    app = create_app()
    
    with app.app_context():
        print(f"\n🔍 搜索关键词: '{keyword}'")
        print("-" * 40)
        
        try:
            # 在原始文本和生成提示词中搜索
            search_results = Favorite.query.filter(
                db.or_(
                    Favorite.original_text.contains(keyword),
                    Favorite.generated_prompt.contains(keyword),
                    Favorite.notes.contains(keyword)
                )
            ).order_by(Favorite.created_time.desc()).all()
            
            if not search_results:
                print(f"❌ 未找到包含 '{keyword}' 的收藏")
                return
            
            print(f"✅ 找到 {len(search_results)} 个相关收藏:")
            
            for i, fav in enumerate(search_results, 1):
                print(f"\n{i}. ID: {fav.id}")
                print(f"   用户: {fav.user_id}")
                print(f"   原始文本: {fav.original_text[:80]}...")
                print(f"   分类: {fav.category or '未分类'}")
                print(f"   时间: {fav.created_time}")
                
        except Exception as e:
            print(f"❌ 搜索过程中出现错误: {str(e)}")

if __name__ == '__main__':
    # 基本查询
    query_favorites()
    
    # 搜索示例
    search_favorites('产品')
    search_favorites('设计')