"""
調試 SQL 查詢 - 檢查實際執行的 SQL
"""

def debug_sql_query():
    """調試 SQL 查詢邏輯"""
    
    print("🔍 調試 SQL 查詢邏輯")
    print("=" * 50)
    
    # 模擬關鍵字
    keywords = ['貓砂', '礦砂', '豆腐砂', '水晶砂', '木屑砂', 'litter', '貓']
    
    print(f"關鍵字: {keywords}")
    
    # 生成 SQL 查詢條件
    search_conditions = []
    
    for keyword in keywords:
        search_conditions.extend([
            f"productName ILIKE '%{keyword}%'",
            f"productCode ILIKE '%{keyword}%'",
            f"barcode ILIKE '%{keyword}%'"
        ])
    
    print(f"\n生成的搜尋條件數量: {len(search_conditions)}")
    print("前10個條件:")
    for i, condition in enumerate(search_conditions[:10], 1):
        print(f"  {i}. {condition}")
    
    # 組合完整的 SQL
    where_clause = " OR ".join(search_conditions)
    full_sql = f"""
SELECT id, productCode, productName, stock, warehouse, category_id
FROM products 
WHERE is_deleted = false 
AND ({where_clause})
ORDER BY stock DESC
LIMIT 5;
"""
    
    print(f"\n完整 SQL 查詢:")
    print(full_sql)
    
    # 測試與實際商品的匹配
    print(f"\n🛍️ 測試與實際商品的匹配")
    print("=" * 50)
    
    # 實際商品資料
    products = [
        {
            "id": 1,
            "productCode": "OL1100-1",
            "productName": "毆力天然犬種300g 室內成犬無榖小顆粒",
            "stock": 100
        },
        {
            "id": 2,
            "productCode": "SW-06-01",
            "productName": "Shovel well豪好鏟 破碎型礦砂",
            "stock": 50
        },
        {
            "id": 3,
            "productCode": "TL-03",
            "productName": "美國極冠貓砂 薰衣草12kg",
            "stock": 48
        },
        {
            "id": 4,
            "productCode": "SL11002",
            "productName": "首領汪 膠原鴨舌 5入彭湃包",
            "stock": 100
        }
    ]
    
    matched_products = []
    
    for product in products:
        product_name = product["productName"].lower()
        product_code = product["productCode"].lower()
        
        # 檢查是否匹配任一關鍵字
        for keyword in keywords:
            keyword_lower = keyword.lower()
            
            if (keyword_lower in product_name or 
                keyword_lower in product_code):
                matched_products.append({
                    "product": product,
                    "matched_keyword": keyword
                })
                print(f"✅ 匹配: {product['productName']} (關鍵字: '{keyword}')")
                break
    
    print(f"\n總共匹配 {len(matched_products)} 個商品")
    
    if not matched_products:
        print("❌ 沒有匹配的商品")
        
        # 逐個測試關鍵字
        print("\n🔍 逐個測試關鍵字:")
        for keyword in keywords:
            print(f"\n關鍵字: '{keyword}'")
            found = False
            for product in products:
                if keyword.lower() in product["productName"].lower():
                    print(f"  ✅ 匹配商品: {product['productName']}")
                    found = True
            if not found:
                print(f"  ❌ 無匹配商品")

def debug_ilike_logic():
    """調試 ILIKE 邏輯"""
    
    print(f"\n🔍 調試 ILIKE 邏輯")
    print("=" * 50)
    
    # 測試字串
    test_strings = [
        "美國極冠貓砂 薰衣草12kg",
        "Shovel well豪好鏟 破碎型礦砂",
        "毆力天然犬種300g 室內成犬無榖小顆粒",
        "首領汪 膠原鴨舌 5入彭湃包"
    ]
    
    # 測試關鍵字
    test_keywords = ["貓砂", "礦砂", "貓", "狗", "犬"]
    
    print("ILIKE 匹配測試:")
    for keyword in test_keywords:
        print(f"\n關鍵字: '{keyword}'")
        for string in test_strings:
            # 模擬 ILIKE '%keyword%' 邏輯
            if keyword.lower() in string.lower():
                print(f"  ✅ '{string}' 包含 '{keyword}'")
            else:
                print(f"  ❌ '{string}' 不包含 '{keyword}'")

def debug_or_logic():
    """調試 OR 邏輯"""
    
    print(f"\n🔍 調試 OR 邏輯")
    print("=" * 50)
    
    # 模擬 SQLAlchemy OR 查詢
    products = [
        {"productName": "美國極冠貓砂 薰衣草12kg", "productCode": "TL-03"},
        {"productName": "Shovel well豪好鏟 破碎型礦砂", "productCode": "SW-06-01"},
        {"productName": "毆力天然犬種300g 室內成犬無榖小顆粒", "productCode": "OL1100-1"},
        {"productName": "首領汪 膠原鴨舌 5入彭湃包", "productCode": "SL11002"}
    ]
    
    keywords = ['貓砂', '礦砂']
    
    print(f"使用關鍵字: {keywords}")
    print("OR 邏輯測試:")
    
    for product in products:
        conditions_met = []
        
        for keyword in keywords:
            # 檢查 productName
            if keyword.lower() in product["productName"].lower():
                conditions_met.append(f"productName contains '{keyword}'")
            
            # 檢查 productCode  
            if keyword.lower() in product["productCode"].lower():
                conditions_met.append(f"productCode contains '{keyword}'")
        
        if conditions_met:
            print(f"  ✅ {product['productName']}")
            print(f"     滿足條件: {', '.join(conditions_met)}")
        else:
            print(f"  ❌ {product['productName']} (無匹配)")

def main():
    """主函數"""
    print("🚀 開始 SQL 查詢調試")
    print("=" * 60)
    
    debug_sql_query()
    debug_ilike_logic()
    debug_or_logic()
    
    print("\n" + "=" * 60)
    print("✅ SQL 調試完成！")
    print("\n💡 如果邏輯測試都正確，問題可能在於:")
    print("1. 資料庫連接問題")
    print("2. SQLAlchemy 查詢執行問題") 
    print("3. 事務或會話問題")
    print("4. 權限問題")

if __name__ == "__main__":
    main()