-- ============================================
-- 修复产品平均单价计算
-- ============================================
-- 说明：重新计算所有产品的平均单价，基于当前所有有效库存
-- 执行时间：2025年
-- ============================================

-- 重新计算所有产品的平均单价（基于所有有效库存）
UPDATE `lq_product` p
SET p.`F_AveragePrice` = (
  SELECT 
    CASE 
      WHEN SUM(inv.`F_Quantity`) > 0 THEN
        SUM(
          CASE 
            WHEN inv.`F_FinalAmount` IS NOT NULL AND inv.`F_FinalAmount` > 0 THEN inv.`F_FinalAmount`
            WHEN inv.`F_PurchaseUnitPrice` IS NOT NULL AND inv.`F_PurchaseUnitPrice` > 0 THEN inv.`F_PurchaseUnitPrice` * inv.`F_Quantity`
            ELSE 0
          END
        ) / SUM(inv.`F_Quantity`)
      ELSE p.`F_Price`
    END
  FROM `lq_inventory` inv
  WHERE inv.`F_ProductId` = p.`F_Id`
    AND inv.`F_IsEffective` = 1
    AND inv.`F_Quantity` > 0
)
WHERE EXISTS (
  SELECT 1 
  FROM `lq_inventory` inv
  WHERE inv.`F_ProductId` = p.`F_Id`
    AND inv.`F_IsEffective` = 1
    AND inv.`F_Quantity` > 0
);

-- 对于没有库存的产品，将平均单价设置为产品价格
UPDATE `lq_product` p
SET p.`F_AveragePrice` = p.`F_Price`
WHERE p.`F_AveragePrice` = 0 OR p.`F_AveragePrice` IS NULL
   OR NOT EXISTS (
     SELECT 1 
     FROM `lq_inventory` inv
     WHERE inv.`F_ProductId` = p.`F_Id`
       AND inv.`F_IsEffective` = 1
       AND inv.`F_Quantity` > 0
   );

-- 验证特定产品的平均单价（产品ID: 770163009904444677）
-- 应该显示：平均单价 = 200.00 (100+200+300)/3 = 600/3 = 200
SELECT 
    p.`F_Id` as ProductId,
    p.`F_ProductName` as ProductName,
    p.`F_Price` as Price,
    p.`F_AveragePrice` as AveragePrice,
    SUM(inv.`F_Quantity`) as TotalQuantity,
    SUM(
        CASE 
            WHEN inv.`F_FinalAmount` IS NOT NULL AND inv.`F_FinalAmount` > 0 THEN inv.`F_FinalAmount`
            WHEN inv.`F_PurchaseUnitPrice` IS NOT NULL AND inv.`F_PurchaseUnitPrice` > 0 THEN inv.`F_PurchaseUnitPrice` * inv.`F_Quantity`
            ELSE 0
        END
    ) as TotalAmount,
    CASE 
        WHEN SUM(inv.`F_Quantity`) > 0 THEN
            SUM(
                CASE 
                    WHEN inv.`F_FinalAmount` IS NOT NULL AND inv.`F_FinalAmount` > 0 THEN inv.`F_FinalAmount`
                    WHEN inv.`F_PurchaseUnitPrice` IS NOT NULL AND inv.`F_PurchaseUnitPrice` > 0 THEN inv.`F_PurchaseUnitPrice` * inv.`F_Quantity`
                    ELSE 0
                END
            ) / SUM(inv.`F_Quantity`)
        ELSE p.`F_Price`
    END as CalculatedAveragePrice
FROM `lq_product` p
LEFT JOIN `lq_inventory` inv ON inv.`F_ProductId` = p.`F_Id` AND inv.`F_IsEffective` = 1 AND inv.`F_Quantity` > 0
WHERE p.`F_Id` = '770163009904444677'
GROUP BY p.`F_Id`, p.`F_ProductName`, p.`F_Price`, p.`F_AveragePrice`;