-- 查询今天入库的SPU和SKU商品数据
  -- 用于查询当天新增的商品信息
  
  -- ======================================
  -- 1. 查询今天入库的SPU商品
  -- ======================================
  
  -- 查询今天创建的SPU商品（SPU级别）
  SELECT
      spu.id AS spu_id,
      spu.tenant_id,
      spu.shop_id,
      spu.shoplazza_id AS shoplazza_product_id,
      spu.title AS product_title,
      spu.description AS product_description,
      spu.brief AS product_brief,
      spu.vendor AS brand_name,
      spu.category AS product_category,
      spu.category_path AS category_path,
      spu.handle AS product_handle,
      spu.tags AS product_tags,
      spu.published AS product_published,
      spu.published_at AS publish_time,
      spu.image_src AS main_image_url,
      spu.image_width AS main_image_width,
      spu.image_height AS main_image_height,
      spu.create_time AS spu_create_time,
      spu.update_time AS spu_update_time,
      CASE
          WHEN spu.deleted = 1 THEN '已删除'
          ELSE '正常'
      END AS spu_status
  FROM shoplazza_product_spu spu
  WHERE DATE(spu.create_time) = CURDATE()  -- 今天的日期
    AND spu.deleted = 0  -- 未删除的商品
  ORDER BY spu.create_time DESC;
  
  -- ======================================
  -- 2. 查询今天入库的SKU商品
  -- ======================================
  
  -- 查询今天创建的SKU商品（SKU级别）
  SELECT
      sku.id AS sku_id,
      sku.tenant_id,
      sku.shop_id,
      sku.spu_id,
      sku.shoplazza_id AS variant_id,
      sku.shoplazza_product_id AS shoplazza_product_id,
      sku.sku AS sku_code,
      sku.title AS sku_title,
      sku.price AS sku_price,
      sku.compare_at_price AS compare_price,
      sku.cost_price AS cost_price,
      sku.inventory_quantity AS stock_quantity,
      sku.weight AS product_weight,
      sku.weight_unit AS weight_unit,
      sku.option1 AS color_option,
      sku.option2 AS size_option,
      sku.option3 AS material_option,
      sku.image_src AS sku_image_url,
      sku.barcode AS barcode,
      sku.position AS variant_position,
      sku.create_time AS sku_create_time,
      sku.update_time AS sku_update_time,
      CASE
          WHEN sku.deleted = 1 THEN '已删除'
          ELSE '正常'
      END AS sku_status
  FROM shoplazza_product_sku sku
  WHERE DATE(sku.create_time) = CURDATE()  -- 今天的日期
    AND sku.deleted = 0  -- 未删除的商品
  ORDER BY sku.create_time DESC;
  
  -- ======================================
  -- 3. 关联查询今天入库的SPU及其对应的SKU
  -- ======================================
  
  -- 查询今天创建的SPU及其关联的SKU信息
  SELECT
      spu.id AS spu_id,
      spu.tenant_id,
      spu.shop_id,
      spu.shoplazza_id AS shoplazza_product_id,
      spu.title AS product_title,
      spu.vendor AS brand_name,
      spu.tags AS product_tags,
      spu.published AS product_published,
      spu.create_time AS spu_create_time,
  
      -- 聚合SKU信息
      COUNT(sku.id) AS sku_count,
      COALESCE(MIN(sku.price), 0) AS min_price,
      COALESCE(MAX(sku.price), 0) AS max_price,
      COALESCE(SUM(sku.inventory_quantity), 0) AS total_stock,
      GROUP_CONCAT(DISTINCT sku.option1 ORDER BY sku.option1 SEPARATOR ', ') AS available_colors,
      GROUP_CONCAT(DISTINCT sku.option2 ORDER BY sku.option2 SEPARATOR ', ') AS available_sizes,
      GROUP_CONCAT(DISTINCT sku.option3 ORDER BY sku.option3 SEPARATOR ', ') AS available_materials
  
  FROM shoplazza_product_spu spu
  LEFT JOIN shoplazza_product_sku sku ON spu.id = sku.spu_id
                                     AND spu.tenant_id = sku.tenant_id
                                     AND sku.deleted = 0
  WHERE DATE(spu.create_time) = CURDATE()  -- 今天创建的SPU
    AND spu.deleted = 0  -- 未删除的SPU
  GROUP BY spu.id, spu.tenant_id, spu.shop_id, spu.shoplazza_id,
           spu.title, spu.vendor, spu.tags, spu.published, spu.create_time
  ORDER BY spu.create_time DESC;
  
  -- ======================================
  -- 4. 查询今天入库商品的数量统计
  -- ======================================
  
  -- 统计今天入库的商品数量
  SELECT
      'SPU商品' AS data_type,
      COUNT(*) AS today_count,
      DATE(CURDATE()) AS statistics_date
  FROM shoplazza_product_spu
  WHERE DATE(create_time) = CURDATE()
    AND deleted = 0
  
  UNION ALL
  
  SELECT
      'SKU商品' AS data_type,
      COUNT(*) AS today_count,
      DATE(CURDATE()) AS statistics_date
  FROM shoplazza_product_sku
  WHERE DATE(create_time) = CURDATE()
    AND deleted = 0
  
  UNION ALL
  
  SELECT
      '活跃店铺' AS data_type,
      COUNT(DISTINCT shop_id) AS today_count,
      DATE(CURDATE()) AS statistics_date
  FROM shoplazza_product_spu
  WHERE DATE(create_time) = CURDATE()
    AND deleted = 0
  
  UNION ALL
  
  SELECT
      '活跃租户' AS data_type,
      COUNT(DISTINCT tenant_id) AS today_count,
      DATE(CURDATE()) AS statistics_date
  FROM shoplazza_product_spu
  WHERE DATE(create_time) = CURDATE()
    AND deleted = 0;
  
  -- ======================================
  -- 5. 按租户统计今天入库的商品
  -- ======================================
  
  -- 按租户统计今天入库的商品分布
  SELECT
      spu.tenant_id,
      COUNT(DISTINCT spu.id) AS spu_count,
      COUNT(DISTINCT sku.id) AS sku_count,
      COUNT(DISTINCT spu.shop_id) AS shop_count,
      COALESCE(SUM(sku.inventory_quantity), 0) AS total_inventory,
      COALESCE(AVG(sku.price), 0) AS avg_price
  FROM shoplazza_product_spu spu
  LEFT JOIN shoplazza_product_sku sku ON spu.id = sku.spu_id
                                     AND spu.tenant_id = sku.tenant_id
                                     AND sku.deleted = 0
  WHERE DATE(spu.create_time) = CURDATE()  -- 今天的日期
    AND spu.deleted = 0  -- 未删除的SPU
  GROUP BY spu.tenant_id
  ORDER BY spu_count DESC;
  
  -- ======================================
  -- 6. 查询今天入库商品的图片信息
  -- ======================================
  
  -- 查询今天入库商品的主图信息（从SPU表获取）
  SELECT
      spu.tenant_id,
      spu.shop_id,
      spu.shoplazza_id AS shoplazza_product_id,
      spu.image_src AS image_url,
      spu.image_width AS image_width,
      spu.image_height AS image_height,
      spu.image_path AS image_path,
      spu.image_alt AS image_alt,
      spu.create_time AS product_create_time,
      CASE
          WHEN spu.deleted = 1 THEN '已删除'
          ELSE '正常'
      END AS image_status
  FROM shoplazza_product_spu spu
  WHERE DATE(spu.create_time) = CURDATE()  -- 今天入库的商品
    AND spu.deleted = 0  -- 未删除的商品
    AND spu.image_src IS NOT NULL  -- 有图片的商品
  ORDER BY spu.tenant_id, spu.shop_id, spu.shoplazza_id;
  
  -- ======================================
  -- 7. 查询今天入库商品的详细信息（含图片）
  -- ======================================
  
  -- 完整的今天入库商品信息（包含图片）
  SELECT
      spu.id AS spu_id,
      spu.tenant_id,
      spu.shop_id,
      spu.shoplazza_id AS shoplazza_product_id,
      spu.title AS product_title,
      spu.description AS product_description,
      spu.brief AS product_brief,
      spu.vendor AS brand_name,
      spu.category AS product_category,
      spu.category_path AS category_path,
      spu.handle AS product_handle,
      spu.tags AS product_tags,
      spu.published AS product_published,
      spu.published_at AS publish_time,
      spu.create_time AS spu_create_time,
  
      -- SKU信息聚合
      COALESCE(sku_summary.sku_count, 0) AS variant_count,
      COALESCE(sku_summary.min_price, 0) AS min_price,
      COALESCE(sku_summary.max_price, 0) AS max_price,
      COALESCE(sku_summary.total_stock, 0) AS total_inventory,
  
      -- 主图信息（从SPU表直接获取）
      COALESCE(spu.image_src, '') AS main_image_url,
      COALESCE(spu.image_width, 0) AS main_image_width,
      COALESCE(spu.image_height, 0) AS main_image_height,
      COALESCE(spu.image_path, '') AS main_image_path,
      COALESCE(spu.image_alt, '') AS main_image_alt
  
  FROM shoplazza_product_spu spu
  
  -- 关联SKU统计信息
  LEFT JOIN (
      SELECT
          spu_id,
          tenant_id,
          COUNT(*) AS sku_count,
          MIN(price) AS min_price,
          MAX(price) AS max_price,
          SUM(inventory_quantity) AS total_stock
      FROM shoplazza_product_sku
      WHERE DATE(create_time) = CURDATE()  -- 今天的SKU
        AND deleted = 0
      GROUP BY spu_id, tenant_id
  ) sku_summary ON spu.id = sku_summary.spu_id
                AND spu.tenant_id = sku_summary.tenant_id
  
  WHERE DATE(spu.create_time) = CURDATE()  -- 今天的SPU
    AND spu.deleted = 0  -- 未删除的SPU

  ORDER BY spu.create_time DESC;
  
  -- ======================================
  -- 8. 分面数据诊断相关查询
  -- ======================================
  
  -- 8.1 检查category_path和category字段情况
  -- 用于诊断分类分面数据是否完整
  SELECT 
      COUNT(*) as total_spu,
      COUNT(category_path) as has_category_path,
      COUNT(category) as has_category,
      COUNT(*) - COUNT(category_path) as null_category_path,
      COUNT(*) - COUNT(category) as null_category
  FROM shoplazza_product_spu
  WHERE tenant_id = 162 AND deleted = 0;
  
  -- 8.2 查看category字段的数据示例
  -- 用于确认category字段的数据格式
  SELECT 
      id AS spu_id,
      title,
      category,
      category_path
  FROM shoplazza_product_spu
  WHERE tenant_id = 162 
    AND deleted = 0 
    AND category IS NOT NULL
  LIMIT 10;
  
  -- 8.3 检查option表的name字段值
  -- 用于诊断specifications分面是否有正确的选项名称
  SELECT 
      DISTINCT name, 
      position, 
      COUNT(*) as count
  FROM shoplazza_product_option
  WHERE tenant_id = 162 AND deleted = 0
  GROUP BY name, position
  ORDER BY position, name;
  
  -- 8.4 检查SKU的option1/2/3字段情况
  -- 用于诊断SKU是否有选项值数据
  SELECT 
      COUNT(*) as total_skus,
      COUNT(option1) as has_option1,
      COUNT(option2) as has_option2,
      COUNT(option3) as has_option3,
      COUNT(*) - COUNT(option1) as null_option1,
      COUNT(*) - COUNT(option2) as null_option2,
      COUNT(*) - COUNT(option3) as null_option3
  FROM shoplazza_product_sku
  WHERE tenant_id = 162 AND deleted = 0;
  
  -- 8.5 查看SKU的option值示例
  -- 用于确认option值的数据格式
  SELECT 
      id AS sku_id,
      spu_id,
      title,
      option1,
      option2,
      option3
  FROM shoplazza_product_sku
  WHERE tenant_id = 162 
    AND deleted = 0
    AND (option1 IS NOT NULL OR option2 IS NOT NULL OR option3 IS NOT NULL)
  LIMIT 10;
  
  -- 8.6 关联查询SPU、option和SKU数据
  -- 用于完整诊断分面数据流
  SELECT 
      spu.id AS spu_id,
      spu.title AS spu_title,
      spu.category,
      spu.category_path,
      opt.position AS opt_position,
      opt.name AS opt_name,
      sku.id AS sku_id,
      sku.option1,
      sku.option2,
      sku.option3
  FROM shoplazza_product_spu spu
  LEFT JOIN shoplazza_product_option opt ON spu.id = opt.spu_id 
      AND spu.tenant_id = opt.tenant_id 
      AND opt.deleted = 0
  LEFT JOIN shoplazza_product_sku sku ON spu.id = sku.spu_id 
      AND spu.tenant_id = sku.tenant_id 
      AND sku.deleted = 0
  WHERE spu.tenant_id = 162 
    AND spu.deleted = 0
  ORDER BY spu.id, opt.position, sku.id
  LIMIT 50;
  
  -- 8.7 统计有option定义的SPU数量
  -- 用于确认有多少商品定义了选项
  SELECT 
      COUNT(DISTINCT spu_id) as spu_with_options
  FROM shoplazza_product_option
  WHERE tenant_id = 162 AND deleted = 0;
  
  -- 8.8 按position统计option的name值分布
  -- 用于检查选项名称是否规范
  SELECT 
      position,
      name,
      COUNT(DISTINCT spu_id) as spu_count
  FROM shoplazza_product_option
  WHERE tenant_id = 162 AND deleted = 0
  GROUP BY position, name

  ORDER BY position, spu_count DESC;
  
  
  
  -- ======================================
  -- 10. SPU原始数据完整查询
  -- ======================================
  
  -- 10.1 查询SPU表所有字段的原始数据
  -- 用于全面检查数据质量
  SELECT 
      id,
      shop_id,
      shoplazza_id,
      handle,
      title,
      brief,
      description,
      spu,
      vendor,
      vendor_url,
      seo_title,
      seo_description,
      seo_keywords,
      image_src,
      image_width,
      image_height,
      image_path,
      image_alt,
      inventory_policy,
      inventory_quantity,
      inventory_tracking,
      published,
      published_at,
      requires_shipping,
      taxable,
      fake_sales,
      display_fake_sales,
      mixed_wholesale,
      need_variant_image,
      has_only_default_variant,
      tags,
      note,
      category,
      category_id,
      category_google_id,
      category_level,
      category_path,
      shoplazza_created_at,
      shoplazza_updated_at,
      tenant_id,
      creator,
      create_time,
      updater,
      update_time,
      deleted
  FROM shoplazza_product_spu
  WHERE tenant_id = 162 
    AND deleted = 0
  ORDER BY id DESC
  LIMIT 10;
  
  -- 10.2 查询指定SPU的完整关联数据
  -- 包含SPU、SKU、Option的完整信息
  SELECT 
      '=== SPU基本信息 ===' AS section,
      spu.id AS spu_id,
      spu.title,
      spu.category,
      spu.category_path,
      spu.category_id,
      spu.category_level,
      spu.vendor,
      spu.tags,
      NULL AS sku_id,
      NULL AS option1,
      NULL AS option2,
      NULL AS option3,
      NULL AS opt_position,
      NULL AS opt_name
  FROM shoplazza_product_spu spu
  WHERE spu.id = 64001  -- 替换为实际的spu_id
    AND spu.deleted = 0
  
  UNION ALL
  
  SELECT 
      '=== SKU信息 ===' AS section,
      sku.spu_id,
      NULL AS title,
      NULL AS category,
      NULL AS category_path,
      NULL AS category_id,
      NULL AS category_level,
      NULL AS vendor,
      NULL AS tags,
      sku.id AS sku_id,
      sku.option1,
      sku.option2,
      sku.option3,
      NULL AS opt_position,
      NULL AS opt_name
  FROM shoplazza_product_sku sku
  WHERE sku.spu_id = 64001  -- 替换为实际的spu_id
    AND sku.deleted = 0
  
  UNION ALL
  
  SELECT 
      '=== Option定义 ===' AS section,
      opt.spu_id,
      NULL AS title,
      NULL AS category,
      NULL AS category_path,
      NULL AS category_id,
      NULL AS category_level,
      NULL AS vendor,
      NULL AS tags,
      NULL AS sku_id,
      NULL AS option1,
      NULL AS option2,
      NULL AS option3,
      opt.position AS opt_position,
      opt.name AS opt_name
  FROM shoplazza_product_option opt
  WHERE opt.spu_id = 64001  -- 替换为实际的spu_id
    AND opt.deleted = 0;
  
  -- 10.3 导出SPU数据用于分析（简化版）
  -- 适合导出到Excel进行分析
  SELECT 
      id AS 'SPU_ID',
      tenant_id AS '租户ID',
      title AS '商品标题',
      category AS '类目字段',
      category_path AS '类目路径',
      category_id AS '类目ID',
      category_level AS '类目层级',
      vendor AS '品牌',
      tags AS '标签',
      published AS '是否发布',
      published_at AS '发布时间',
      create_time AS '创建时间',
      update_time AS '更新时间',
      CASE 
          WHEN category REGEXP '^[0-9]+$' THEN 'YES-需要修复'
          ELSE 'NO-正常'
      END AS 'Category是数字ID'
  FROM shoplazza_product_spu
  WHERE tenant_id = 162 
    AND deleted = 0
  ORDER BY id DESC
  LIMIT 100;
  
  -- ======================================
  -- 11. 租户数据概览
  -- ======================================
  
  -- 11.1 所有租户的数据统计
  SELECT 
      tenant_id,
      COUNT(*) AS total_spu,
      SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END) AS has_category,
      SUM(CASE WHEN category_path IS NOT NULL THEN 1 ELSE 0 END) AS has_category_path,
      SUM(CASE WHEN category REGEXP '^[0-9]+$' THEN 1 ELSE 0 END) AS category_is_numeric,
      MIN(create_time) AS earliest_data,
      MAX(create_time) AS latest_data
  FROM shoplazza_product_spu
  WHERE deleted = 0
  GROUP BY tenant_id
  ORDER BY tenant_id;
  
  -- 11.2 查询某个租户的所有SPU（分页查看）
  -- 用于逐条检查数据
  SELECT 
      id,
      title,
      category,
      category_path,
      vendor,
      tags,
      create_time
  FROM shoplazza_product_spu
  WHERE tenant_id = 162 
    AND deleted = 0
  ORDER BY id
  LIMIT 50 OFFSET 0;  -- 修改OFFSET查看不同页