"""
簡化的資料庫測試 - 檢查是否有貓砂相關商品
"""

import sys
import os

# 添加專案根目錄到 Python 路徑
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

def test_database_products():
    """測試資料庫中的商品資料"""
    try:
        from backend.database.connection import get_database_session, close_database_session
        from backend.database.models import Product, Category
        from sqlalchemy.orm import joinedload
        from sqlalchemy import or_
        
        print("🗄️ 連接資料庫...")
        db = get_database_session()
        
        if not db:
            print("❌ 無法連接資料庫")
            return
        
        print("✅ 資料庫連接成功")
        
        # 1. 檢查總商品數量
        total_products = db.query(Product).filter(Product.is_deleted == False).count()
        print(f"📊 總商品數量: {total_products}")
        
        # 2. 檢查是否有分類
        categories = db.query(Category).all()
        print(f"📂 分類數量: {len(categories)}")
        if categories:
            print("分類列表:")
            for cat in categories[:5]:  # 只顯示前5個
                print(f"  - {cat.name}")
        
        # 3. 搜尋貓砂相關商品
        print("\n🔍 搜尋貓砂相關商品...")
        
        search_terms = ["貓砂", "貓", "寵物", "cat", "litter", "pet"]
        
        for term in search_terms:
            print(f"\n--- 搜尋關鍵字: '{term}' ---")
            
            # 搜尋商品名稱、編號、條碼
            products = db.query(Product).options(
                joinedload(Product.category)
            ).filter(
                Product.is_deleted == False
            ).filter(
                or_(
                    Product.productName.ilike(f"%{term}%"),
                    Product.productCode.ilike(f"%{term}%"),
                    Product.barcode.ilike(f"%{term}%")
                )
            ).limit(5).all()
            
            print(f"找到 {len(products)} 個商品:")
            for product in products:
                print(f"  - {product.productName} (編號: {product.productCode}, 庫存: {product.stock})")
        
        # 4. 檢查分類中是否有寵物相關
        print("\n🐾 檢查寵物相關分類...")
        pet_categories = db.query(Category).filter(
            or_(
                Category.name.ilike("%寵物%"),
                Category.name.ilike("%貓%"),
                Category.name.ilike("%狗%"),
                Category.name.ilike("%pet%"),
                Category.name.ilike("%cat%"),
                Category.name.ilike("%dog%")
            )
        ).all()
        
        print(f"找到 {len(pet_categories)} 個寵物相關分類:")
        for cat in pet_categories:
            print(f"  - {cat.name}")
            
            # 檢查該分類下的商品
            products_in_cat = db.query(Product).filter(
                Product.category_id == cat.id,
                Product.is_deleted == False
            ).limit(3).all()
            
            for product in products_in_cat:
                print(f"    * {product.productName} (庫存: {product.stock})")
        
        # 5. 隨機顯示一些商品名稱，看看資料庫中有什麼
        print("\n📋 隨機商品樣本 (前10個):")
        sample_products = db.query(Product).filter(
            Product.is_deleted == False
        ).limit(10).all()
        
        for i, product in enumerate(sample_products, 1):
            category_name = product.category.name if product.category else "無分類"
            print(f"  {i}. {product.productName} ({category_name}) - 庫存: {product.stock}")
        
        close_database_session(db)
        
    except Exception as e:
        print(f"❌ 測試錯誤: {str(e)}")
        import traceback
        traceback.print_exc()

def main():
    """主函數"""
    print("🚀 開始簡化資料庫測試\n")
    test_database_products()
    print("\n✅ 測試完成！")

if __name__ == "__main__":
    main()