#!/usr/bin/env python3
  """
  诊断脚本：检查MySQL数据源中分类和规格信息是否正确
  
  检查：
  1. category_path 字段是否有值
  2. category_path 格式是否正确（应该能被解析为 category1_name）
  3. shoplazza_product_option 表的 name 字段是否有值（应该是 "color", "size", "material"）
  4. shoplazza_product_sku 表的 option1/2/3 字段是否有值
  """
  
  import sys
  import argparse
  from pathlib import Path
  from sqlalchemy import create_engine, text
  
  # Add parent directory to path
  sys.path.insert(0, str(Path(__file__).parent.parent))
  
  from utils.db_connector import create_db_connection
  
  
  def check_category_path(db_engine, tenant_id: str):
      """检查 category_path 和 category 字段"""
      print("\n" + "="*60)
      print("1. 检查 category_path 和 category 字段")
      print("="*60)
      
      query = text("""
          SELECT 
              COUNT(*) as total,
              COUNT(category_path) as has_category_path,
              COUNT(*) - COUNT(category_path) as null_category_path,
              COUNT(category) as has_category,
              COUNT(*) - COUNT(category) as null_category
          FROM shoplazza_product_spu
          WHERE tenant_id = :tenant_id AND deleted = 0
      """)
      
      with db_engine.connect() as conn:
          result = conn.execute(query, {"tenant_id": tenant_id}).fetchone()
          total = result[0]
          has_category_path = result[1]
          null_category_path = result[2]
          has_category = result[3]
          null_category = result[4]
          
          print(f"总SPU数: {total}")
          print(f"有 category_path 的SPU: {has_category_path}")
          print(f"category_path 为空的SPU: {null_category_path}")
          print(f"有 category 的SPU: {has_category}")
          print(f"category 为空的SPU: {null_category}")
          
          # 查看category字段的示例
          if has_category > 0:
              sample_query = text("""
                  SELECT id, title, category_path, category, category_id, category_level
                  FROM shoplazza_product_spu
                  WHERE tenant_id = :tenant_id 
                    AND deleted = 0 
                    AND category IS NOT NULL
                  LIMIT 5
              """)
              samples = conn.execute(sample_query, {"tenant_id": tenant_id}).fetchall()
              print(f"\n示例数据（前5条有 category 的记录）:")
              for row in samples:
                  print(f"  SPU ID: {row[0]}, Title: {row[1][:50] if row[1] else ''}")
                  print(f"    category_path: {row[2]}")
                  print(f"    category: '{row[3]}'")
                  print(f"    category_id: {row[4]}, category_level: {row[5]}")
                  
                  # 解析 category 字段（用于生成 category1_name）
                  if row[3]:
                      category = str(row[3])
                      if '/' in category:
                          path_parts = category.split('/')
                          print(f"    解析后（按'/'分割）: {path_parts}")
                          if len(path_parts) > 0:
                              print(f"    → category1_name: '{path_parts[0].strip()}'")
                      else:
                          print(f"    → category1_name: '{category.strip()}'（直接作为category1_name）")
          else:
              print("\n⚠️ 警告: 没有SPU有 category 值！")
          
          # 查看category_path的示例（如果有）
          if has_category_path > 0:
              sample_query = text("""
                  SELECT id, title, category_path, category
                  FROM shoplazza_product_spu
                  WHERE tenant_id = :tenant_id 
                    AND deleted = 0 
                    AND category_path IS NOT NULL
                  LIMIT 3
              """)
              samples = conn.execute(sample_query, {"tenant_id": tenant_id}).fetchall()
              print(f"\n示例数据（有 category_path 的记录）:")
              for row in samples:
                  print(f"  SPU ID: {row[0]}, Title: {row[1][:50] if row[1] else ''}")
                  print(f"    category_path: '{row[2]}'")
                  print(f"    category: '{row[3]}'")
                  
                  # 检查是否是ID列表格式
                  if row[2] and ',' in str(row[2]) and not '/' in str(row[2]):
                      print(f"    ⚠️  注意: category_path是ID列表格式（逗号分隔），不是路径格式")
  
  
  def check_options(db_engine, tenant_id: str):
      """检查 option 表的 name 字段"""
      print("\n" + "="*60)
      print("2. 检查 shoplazza_product_option 表的 name 字段")
      print("="*60)
      
      query = text("""
          SELECT 
              COUNT(*) as total_options,
              COUNT(DISTINCT name) as distinct_names,
              COUNT(DISTINCT spu_id) as spus_with_options
          FROM shoplazza_product_option
          WHERE tenant_id = :tenant_id AND deleted = 0
      """)
      
      with db_engine.connect() as conn:
          result = conn.execute(query, {"tenant_id": tenant_id}).fetchone()
          total_options = result[0]
          distinct_names = result[1]
          spus_with_options = result[2]
          
          print(f"总 option 记录数: {total_options}")
          print(f"不同的 name 数量: {distinct_names}")
          print(f"有 option 定义的 SPU 数量: {spus_with_options}")
          
          if total_options > 0:
              # 查看不同的 name 值
              name_query = text("""
                  SELECT DISTINCT name, position, COUNT(*) as count
                  FROM shoplazza_product_option
                  WHERE tenant_id = :tenant_id AND deleted = 0
                  GROUP BY name, position
                  ORDER BY position, name
              """)
              names = conn.execute(name_query, {"tenant_id": tenant_id}).fetchall()
              print(f"\n不同的 name 值:")
              for row in names:
                  print(f"  position={row[1]}, name='{row[0]}', count={row[2]}")
                  
              # 查看一些示例
              sample_query = text("""
                  SELECT spu_id, position, name, `values`
                  FROM shoplazza_product_option
                  WHERE tenant_id = :tenant_id AND deleted = 0
                  ORDER BY spu_id, position
                  LIMIT 10
              """)
              samples = conn.execute(sample_query, {"tenant_id": tenant_id}).fetchall()
              print(f"\n示例数据（前10条 option 记录）:")
              for row in samples:
                  print(f"  SPU ID: {row[0]}, position: {row[1]}, name: '{row[2]}', values: {row[3]}")
          else:
              print("\n⚠️ 警告: 没有 option 记录！")
  
  
  def check_sku_options(db_engine, tenant_id: str):
      """检查 SKU 表的 option1/2/3 字段"""
      print("\n" + "="*60)
      print("3. 检查 shoplazza_product_sku 表的 option1/2/3 字段")
      print("="*60)
      
      query = text("""
          SELECT 
              COUNT(*) as total_skus,
              COUNT(option1) as has_option1,
              COUNT(option2) as has_option2,
              COUNT(option3) as has_option3,
              COUNT(DISTINCT spu_id) as distinct_spus
          FROM shoplazza_product_sku
          WHERE tenant_id = :tenant_id AND deleted = 0
      """)
      
      with db_engine.connect() as conn:
          result = conn.execute(query, {"tenant_id": tenant_id}).fetchone()
          total_skus = result[0]
          has_option1 = result[1]
          has_option2 = result[2]
          has_option3 = result[3]
          distinct_spus = result[4]
          
          print(f"总 SKU 数: {total_skus}")
          print(f"有 option1 的 SKU: {has_option1}")
          print(f"有 option2 的 SKU: {has_option2}")
          print(f"有 option3 的 SKU: {has_option3}")
          print(f"不同的 SPU 数量: {distinct_spus}")
          
          if total_skus > 0:
              # 查看一些示例
              sample_query = text("""
                  SELECT spu_id, id, option1, option2, option3
                  FROM shoplazza_product_sku
                  WHERE tenant_id = :tenant_id AND deleted = 0
                  ORDER BY spu_id, id
                  LIMIT 10
              """)
              samples = conn.execute(sample_query, {"tenant_id": tenant_id}).fetchall()
              print(f"\n示例数据（前10条 SKU 记录）:")
              for row in samples:
                  print(f"  SPU ID: {row[0]}, SKU ID: {row[1]}")
                  print(f"    option1: '{row[2]}', option2: '{row[3]}', option3: '{row[4]}'")
          else:
              print("\n⚠️ 警告: 没有 SKU 记录！")
  
  
  def check_spu_summary(db_engine, tenant_id: str):
      """检查 SPU 汇总信息"""
      print("\n" + "="*60)
      print("4. SPU 汇总信息")
      print("="*60)
      
      query = text("""
          SELECT 
              COUNT(DISTINCT spu.id) as total_spus,
              COUNT(DISTINCT sku.id) as total_skus,
              COUNT(DISTINCT opt.id) as total_options,
              COUNT(DISTINCT CASE WHEN spu.category_path IS NOT NULL THEN spu.id END) as spus_with_category_path,
              COUNT(DISTINCT opt.spu_id) as spus_with_options
          FROM shoplazza_product_spu spu
          LEFT JOIN shoplazza_product_sku sku ON spu.id = sku.spu_id AND sku.tenant_id = :tenant_id AND sku.deleted = 0
          LEFT JOIN shoplazza_product_option opt ON spu.id = opt.spu_id AND opt.tenant_id = :tenant_id AND opt.deleted = 0
          WHERE spu.tenant_id = :tenant_id AND spu.deleted = 0
      """)
      
      with db_engine.connect() as conn:
          result = conn.execute(query, {"tenant_id": tenant_id}).fetchone()
          total_spus = result[0]
          total_skus = result[1]
          total_options = result[2]
          spus_with_category_path = result[3]
          spus_with_options = result[4]
          
          print(f"总 SPU 数: {total_spus}")
          print(f"总 SKU 数: {total_skus}")
          print(f"总 option 记录数: {total_options}")
          print(f"有 category_path 的 SPU: {spus_with_category_path}")
          print(f"有 option 定义的 SPU: {spus_with_options}")
  
  
  def main():
      parser = argparse.ArgumentParser(description='检查MySQL数据源中的分类和规格信息')
      parser.add_argument('--tenant-id', required=True, help='Tenant ID')
      parser.add_argument('--db-host', help='MySQL host (或使用环境变量 DB_HOST)')
      parser.add_argument('--db-port', type=int, help='MySQL port (或使用环境变量 DB_PORT, 默认: 3306)')
      parser.add_argument('--db-database', help='MySQL database (或使用环境变量 DB_DATABASE)')
      parser.add_argument('--db-username', help='MySQL username (或使用环境变量 DB_USERNAME)')
      parser.add_argument('--db-password', help='MySQL password (或使用环境变量 DB_PASSWORD)')
      
      args = parser.parse_args()
      
      # 连接数据库
      import os
      db_host = args.db_host or os.environ.get('DB_HOST')
      db_port = args.db_port or int(os.environ.get('DB_PORT', 3306))
      db_database = args.db_database or os.environ.get('DB_DATABASE')
      db_username = args.db_username or os.environ.get('DB_USERNAME')
      db_password = args.db_password or os.environ.get('DB_PASSWORD')
      
      if not all([db_host, db_database, db_username, db_password]):
          print("错误: MySQL连接参数不完整")
          print("请提供 --db-host, --db-database, --db-username, --db-password")
          print("或设置环境变量: DB_HOST, DB_DATABASE, DB_USERNAME, DB_PASSWORD")
          return 1
      
      print(f"连接MySQL: {db_host}:{db_port}/{db_database}")
      print(f"Tenant ID: {args.tenant_id}")
      
      try:
          db_engine = create_db_connection(
              host=db_host,
              port=db_port,
              database=db_database,
              username=db_username,
              password=db_password
          )
          print("✓ MySQL连接成功\n")
      except Exception as e:
          print(f"✗ 连接MySQL失败: {e}")
          return 1
      
      # 执行检查
      check_spu_summary(db_engine, args.tenant_id)
      check_category_path(db_engine, args.tenant_id)
      check_options(db_engine, args.tenant_id)
      check_sku_options(db_engine, args.tenant_id)
      
      print("\n" + "="*60)
      print("检查完成")
      print("="*60)
      
      return 0
  
  
  if __name__ == '__main__':
      sys.exit(main())