-- ============================================
-- 卸妆水库存、入库、领用数据核对
-- ============================================
-- 说明：检查卸妆水的当前库存、入库总量、领用总量，验证数据是否对齐
-- 平衡关系：入库总量 = 当前库存 + 已领取数量
-- ============================================

-- 一、查找卸妆水产品
SELECT 
    F_Id AS 产品ID,
    F_ProductName AS 产品名称,
    F_Price AS 价格,
    F_AveragePrice AS 平均单价
FROM lq_product
WHERE F_ProductName LIKE '%卸妆水%';

-- 二、卸妆水库存/入库/领用汇总（按产品）
-- 说明：领用数量只统计「已领取」(F_IsReceived=1) 的申请批次
SELECT 
    p.F_Id AS 产品ID,
    p.F_ProductName AS 产品名称,
    IFNULL(inv.入库总量, 0) AS 入库总量,
    IFNULL(recv.已领取数量, 0) AS 已领取数量,
    IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0) AS 当前可用库存,
    CASE 
        WHEN IFNULL(inv.入库总量, 0) = IFNULL(recv.已领取数量, 0) + (IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0))
        THEN '✅ 对齐'
        ELSE '❌ 异常'
    END AS 数据是否对齐
FROM lq_product p
LEFT JOIN (
    -- 入库总量（有效库存记录）
    SELECT 
        F_ProductId,
        SUM(F_Quantity) AS 入库总量
    FROM lq_inventory
    WHERE F_IsEffective = 1
    GROUP BY F_ProductId
) inv ON inv.F_ProductId = p.F_Id
LEFT JOIN (
    -- 已领取数量（仅统计 F_IsReceived=1 的申请批次下的使用记录）
    SELECT 
        u.F_ProductId,
        SUM(u.F_UsageQuantity) AS 已领取数量
    FROM lq_inventory_usage u
    INNER JOIN lq_inventory_usage_application a 
        ON a.F_UsageBatchId = u.F_UsageBatchId 
        AND a.F_IsEffective = 1 
        AND a.F_IsReceived = 1
    WHERE u.F_IsEffective = 1
    GROUP BY u.F_ProductId
) recv ON recv.F_ProductId = p.F_Id
WHERE p.F_ProductName LIKE '%卸妆水%';

-- 三、卸妆水入库明细（按批次）
SELECT 
    p.F_ProductName AS 产品名称,
    i.F_Id AS 库存记录ID,
    i.F_BatchNumber AS 批次号,
    i.F_Quantity AS 入库数量,
    i.F_StockInTime AS 入库时间,
    i.F_StockInType AS 入库类型,
    i.F_IsEffective AS 是否有效
FROM lq_inventory i
INNER JOIN lq_product p ON p.F_Id = i.F_ProductId
WHERE p.F_ProductName LIKE '%卸妆水%'
  AND i.F_IsEffective = 1
ORDER BY i.F_StockInTime DESC;

-- 四、卸妆水领用明细（仅已领取）
SELECT 
    p.F_ProductName AS 产品名称,
    u.F_UsageBatchId AS 批次ID,
    u.F_UsageQuantity AS 领用数量,
    u.F_UsageTime AS 使用时间,
    a.F_ReceiveTime AS 领取时间,
    a.F_ApprovalStatus AS 审批状态,
    a.F_IsReceived AS 是否已领取
FROM lq_inventory_usage u
INNER JOIN lq_product p ON p.F_Id = u.F_ProductId
INNER JOIN lq_inventory_usage_application a 
    ON a.F_UsageBatchId = u.F_UsageBatchId 
    AND a.F_IsEffective = 1 
    AND a.F_IsReceived = 1
WHERE p.F_ProductName LIKE '%卸妆水%'
  AND u.F_IsEffective = 1
ORDER BY u.F_UsageTime DESC;

-- 五、数据平衡校验（简化版）
-- 期望：入库总量 = 当前可用库存 + 已领取数量
SELECT 
    p.F_ProductName AS 产品名称,
    IFNULL(inv.入库总量, 0) AS 入库总量,
    IFNULL(recv.已领取数量, 0) AS 已领取数量,
    IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0) AS 当前可用库存_计算值,
    IFNULL(inv.入库总量, 0) - (IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0)) - IFNULL(recv.已领取数量, 0) AS 平衡差_应为0
FROM lq_product p
LEFT JOIN (
    SELECT F_ProductId, SUM(F_Quantity) AS 入库总量
    FROM lq_inventory WHERE F_IsEffective = 1 GROUP BY F_ProductId
) inv ON inv.F_ProductId = p.F_Id
LEFT JOIN (
    SELECT u.F_ProductId, SUM(u.F_UsageQuantity) AS 已领取数量
    FROM lq_inventory_usage u
    INNER JOIN lq_inventory_usage_application a 
        ON a.F_UsageBatchId = u.F_UsageBatchId AND a.F_IsEffective = 1 AND a.F_IsReceived = 1
    WHERE u.F_IsEffective = 1
    GROUP BY u.F_ProductId
) recv ON recv.F_ProductId = p.F_Id
WHERE p.F_ProductName LIKE '%卸妆水%';