-- 查询今天入库的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;