-- 查询今天入库的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查看不同页