package com.haha.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.haha.entity.DeviceInventory;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 设备商品库存Mapper
 */
@Mapper
public interface DeviceInventoryMapper extends BaseMapper<DeviceInventory> {

    /**
     * 根据设备ID查询库存列表（带商品详情）
     */
    @Select("SELECT di.*, p.barcode, p.pic as product_image, p.cost_price, p.retail_price " +
            "FROM t_device_inventory di " +
            "LEFT JOIN t_product p ON di.product_id = p.id " +
            "WHERE di.device_id = #{deviceId} " +
            "ORDER BY di.shelf_num, di.position")
    List<Map<String, Object>> selectInventoryWithProduct(@Param("deviceId") String deviceId);

    /**
     * 根据设备和商品查询库存
     */
    @Select("SELECT * FROM t_device_inventory WHERE device_id = #{deviceId} AND product_id = #{productId}")
    DeviceInventory selectByDeviceAndProduct(@Param("deviceId") String deviceId, @Param("productId") Long productId);

    /**
     * 查询低库存商品
     */
    @Select("SELECT di.*, d.name as device_name, p.barcode " +
            "FROM t_device_inventory di " +
            "LEFT JOIN t_device d ON di.device_id COLLATE utf8mb4_unicode_ci = d.device_id COLLATE utf8mb4_unicode_ci " +
            "LEFT JOIN t_product p ON di.product_id = p.id " +
            "WHERE di.stock <= di.warning_threshold " +
            "ORDER BY di.stock ASC")
    List<Map<String, Object>> selectLowStockInventories();

    /**
     * 分页查询设备库存统计列表
     * 按设备分组统计库存信息
     */
    @Select("<script>" +
            "SELECT " +
            "  d.id as device_id_primary, " +
            "  d.device_id, " +
            "  d.name as device_name, " +
            "  s.id as shop_id, " +
            "  s.name as shop_name, " +
            "  s.address as shop_address, " +
            "  COALESCE(SUM(di.stock), 0) as remaining_stock, " +
            "  COALESCE(SUM(di.standard_stock), 0) as standard_stock, " +
            "  COUNT(di.id) as product_count, " +
            "  MAX(di.last_restock_time) as last_restock_time, " +
            "  SUM(CASE WHEN di.stock = 0 THEN 1 ELSE 0 END) as zero_stock_count, " +
            "  SUM(CASE WHEN di.stock &lt; di.standard_stock AND di.standard_stock > 0 THEN 1 ELSE 0 END) as low_stock_count, " +
            "  SUM(CASE WHEN di.product_id IS NULL OR di.product_id = 0 THEN 1 ELSE 0 END) as unknown_product_count " +
            "FROM t_device d " +
            "LEFT JOIN t_shop s ON d.shop_id = s.id " +
            "LEFT JOIN t_device_inventory di ON d.device_id COLLATE utf8mb4_unicode_ci = di.device_id COLLATE utf8mb4_unicode_ci " +
            "WHERE 1=1 " +
            "<if test='shopName != null and shopName != \"\"'> " +
            "  AND s.name LIKE CONCAT('%', #{shopName}, '%') " +
            "</if> " +
            "<if test='deviceName != null and deviceName != \"\"'> " +
            "  AND d.name LIKE CONCAT('%', #{deviceName}, '%') " +
            "</if> " +
            "<if test='deviceId != null and deviceId != \"\"'> " +
            "  AND d.device_id LIKE CONCAT('%', #{deviceId}, '%') " +
            "</if> " +
            "<if test='hasUnknownProduct != null and hasUnknownProduct == true'> " +
            "  AND EXISTS (SELECT 1 FROM t_device_inventory di2 WHERE di2.device_id = d.device_id AND (di2.product_id IS NULL OR di2.product_id = 0)) " +
            "</if> " +
            "GROUP BY d.id, d.device_id, d.name, s.id, s.name, s.address " +
            "ORDER BY d.update_time DESC " +
            "</script>")
    IPage<Map<String, Object>> selectDeviceInventoryStats(
            Page<Map<String, Object>> page,
            @Param("shopName") String shopName,
            @Param("deviceName") String deviceName,
            @Param("deviceId") String deviceId,
            @Param("hasUnknownProduct") Boolean hasUnknownProduct,
            @Param("stockStatus") Integer stockStatus);

    /**
     * 查询设备的最后一次补货后总库存
     */
    @Select("SELECT COALESCE(SUM(stock), 0) as total_stock_after_restock " +
            "FROM t_inventory_log " +
            "WHERE device_id = #{deviceId} " +
            "AND change_type = 1 " +
            "AND create_time = (SELECT MAX(create_time) FROM t_inventory_log WHERE device_id = #{deviceId} AND change_type = 1)")
    Integer selectLastRestockTotalStock(@Param("deviceId") String deviceId);

    /**
     * 查询所有库存总数量（SQL聚合）
     */
    @Select("SELECT COALESCE(SUM(stock), 0) FROM t_device_inventory")
    Integer selectSumStock();
}
