# cSpell:ignore momo
import os
import sys
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker

# 設定路徑
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, BASE_DIR)

from database.models import Product
from database.edm_models import PromoProduct

# 資料庫路徑
DB_PATH = os.path.join(BASE_DIR, 'data', 'momo_database.db')

def check_missing_images():
    """檢查各個資料表中缺少圖片的商品數量"""
    print("🔍 正在檢查商品圖片狀態...\n")

    if not os.path.exists(DB_PATH):
        print(f"❌ 資料庫檔案不存在: {DB_PATH}")
        return

    try:
        engine = create_engine(f"sqlite:///{DB_PATH}")
        Session = sessionmaker(bind=engine)
        session = Session()

        # 1. 檢查一般商品 (Product)
        print("=" * 60)
        print("📦 一般商品 (Product)")
        print("=" * 60)

        total_products = session.query(func.count(Product.i_code)).scalar()
        missing_images = session.query(func.count(Product.i_code)).filter(
            (Product.image_url == None) | (Product.image_url == '')
        ).scalar()
        has_images = total_products - missing_images

        print(f"總商品數: {total_products:,}")
        print(f"有圖片: {has_images:,} ({has_images/total_products*100:.1f}%)")
        print(f"缺圖片: {missing_images:,} ({missing_images/total_products*100:.1f}%)")

        # 顯示幾個缺圖的範例
        if missing_images > 0:
            print("\n📋 缺少圖片的商品範例 (前5筆):")
            missing_products = session.query(Product).filter(
                (Product.image_url == None) | (Product.image_url == '')
            ).limit(5).all()

            for i, p in enumerate(missing_products, 1):
                print(f"{i}. [{p.i_code}] {p.name[:40]}...")

        # 2. 檢查促銷商品 (PromoProduct - EDM)
        print("\n" + "=" * 60)
        print("🎁 促銷商品 (EDM)")
        print("=" * 60)

        total_promo = session.query(func.count(PromoProduct.i_code)).filter(
            PromoProduct.page_type == 'edm'
        ).scalar()
        if total_promo > 0:
            missing_promo = session.query(func.count(PromoProduct.i_code)).filter(
                PromoProduct.page_type == 'edm',
                (PromoProduct.image_url == None) | (PromoProduct.image_url == '')
            ).scalar()
            has_promo = total_promo - missing_promo

            print(f"總商品數: {total_promo:,}")
            print(f"有圖片: {has_promo:,} ({has_promo/total_promo*100:.1f}%)")
            print(f"缺圖片: {missing_promo:,} ({missing_promo/total_promo*100:.1f}%)")
        else:
            print("目前沒有促銷商品資料")

        # 3. 檢查購物節商品 (從 PromoProduct 中篩選 page_type='festival')
        print("\n" + "=" * 60)
        print("🎉 購物節商品 (Festival)")
        print("=" * 60)

        total_festival = session.query(func.count(PromoProduct.i_code)).filter(
            PromoProduct.page_type == 'festival'
        ).scalar()
        if total_festival > 0:
            missing_festival = session.query(func.count(PromoProduct.i_code)).filter(
                PromoProduct.page_type == 'festival',
                (PromoProduct.image_url == None) | (PromoProduct.image_url == '')
            ).scalar()
            has_festival = total_festival - missing_festival

            print(f"總商品數: {total_festival:,}")
            print(f"有圖片: {has_festival:,} ({has_festival/total_festival*100:.1f}%)")
            print(f"缺圖片: {missing_festival:,} ({missing_festival/total_festival*100:.1f}%)")
        else:
            print("目前沒有購物節商品資料")

        print("\n" + "=" * 60)
        print("✅ 檢查完成")
        print("=" * 60)

        session.close()

    except Exception as e:
        print(f"❌ 檢查失敗: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    check_missing_images()