package com.ruoyi.web.sales.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.web.sales.domain.SalesOrderGoods;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

public interface SalesOrderGoodsMapper extends BaseMapper<SalesOrderGoods> {

    /**
     * 白条分配订单货品明细（销售日期、物流线/市场/客户、产品类型）
     * productTypeList 非空时按物料产品类型 {@code bm.product_type} 过滤；为空或 null 不按产品类型限制。
     */
    @Select({
            "<script>",
            "select sog.*, so.line_num, so.market_num, so.customer_num, so.customer_name, bm.variety, bm.variety_name, bm.goods_level ",
            "from sales_order_goods sog, sales_order so, base_material bm ",
            "where sog.order_num = so.order_num ",
            "  and sog.goods_num = bm.goods_num ",
            "  and sog.del_flag = '0' ",
            "  and so.del_flag = '0' ",
            "  and bm.del_flag = '0' ",
            "  and so.org_id = #{orgId} ",
            "  and sog.org_id = #{orgId} ",
            "  and bm.org_id = #{orgId} ",
            "  and so.sale_date = #{saleDate} ",
            "  <if test='lineNum != null and lineNum != \"\"'> and so.line_num = #{lineNum} </if>",
            "  <if test='marketNum != null and marketNum != \"\"'> and so.market_num = #{marketNum} </if>",
            "  <if test='customerNum != null and customerNum != \"\"'> and so.customer_num = #{customerNum} </if>",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "order by so.id desc, sog.id asc",
            "</script>"
    })
    List<Map<String, Object>> listGoodsDetailByConditions(@Param("orgId") String orgId,
                                                          @Param("saleDate") String saleDate,
                                                          @Param("lineNum") String lineNum,
                                                          @Param("marketNum") String marketNum,
                                                          @Param("customerNum") String customerNum,
                                                          @Param("productTypeList") List<String> productTypeList);

    /**
     * 按订单明细（sale_id 对应 sales_order_goods.id）统计白条已分配条数。
     * 关联 {@code base_material} 与明细列表一致；productTypeList 非空时追加 {@code bm.product_type in (...)}。
     */
    @Select({
            "<script>",
            "select pwsa.sale_id as sale_id, MAX(pwsa.variety) as variety, MAX(pwsa.grade) as grade, count(*) as alloc_num ",
            "from production_white_strip_allocation pwsa, sales_order_goods sog, sales_order so, base_material bm ",
            "where pwsa.sale_id = sog.id ",
            "  and pwsa.sale_num = so.order_num ",
            "  and sog.order_num = so.order_num ",
            "  and sog.goods_num = bm.goods_num ",
            "  and bm.org_id = #{orgId} ",
            "  and bm.del_flag = '0' ",
            "  and pwsa.del_flag = '0' ",
            "  and sog.del_flag = '0' ",
            "  and so.del_flag = '0' ",
            "  and sog.org_id = #{orgId} ",
            "  and so.org_id = #{orgId} ",
            "  and pwsa.org_id = #{orgId} ",
            "  and so.sale_date = #{saleDate} ",
            "  <if test='lineNum != null and lineNum != \"\"'> and so.line_num = #{lineNum} </if>",
            "  <if test='marketNum != null and marketNum != \"\"'> and so.market_num = #{marketNum} </if>",
            "  <if test='customerNum != null and customerNum != \"\"'> and so.customer_num = #{customerNum} </if>",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "group by pwsa.sale_id",
            "</script>"
    })
    List<Map<String, Object>> listAllocNumByOrderGoodsLine(@Param("orgId") String orgId,
                                                           @Param("saleDate") String saleDate,
                                                           @Param("lineNum") String lineNum,
                                                           @Param("marketNum") String marketNum,
                                                           @Param("customerNum") String customerNum,
                                                           @Param("productTypeList") List<String> productTypeList);

    /**
     * 按品种、级别汇总辅助计量数量。
     * productTypeList 非空时按物料产品类型 {@code bm.product_type} 过滤。
     * variety_name 使用 MAX，避免与 group by bm.variety,bm.goods_level 在 ONLY_FULL_GROUP_BY 下冲突。
     */
    @Select({
            "<script>",
            "select bm.variety, MAX(bm.variety_name) as variety_name, bm.goods_level as grade, sum(sog.ass_num) as ass_num ",
            "from sales_order_goods sog, sales_order so, base_material bm ",
            "where sog.order_num = so.order_num ",
            "  and sog.goods_num = bm.goods_num ",
            "  and sog.del_flag = '0' ",
            "  and so.del_flag = '0' ",
            "  and bm.del_flag = '0' ",
            "  and so.org_id = #{orgId} ",
            "  and sog.org_id = #{orgId} ",
            "  and bm.org_id = #{orgId} ",
            "  and so.sale_date = #{saleDate} ",
            "  <if test='lineNum != null and lineNum != \"\"'> and so.line_num = #{lineNum} </if>",
            "  <if test='marketNum != null and marketNum != \"\"'> and so.market_num = #{marketNum} </if>",
            "  <if test='customerNum != null and customerNum != \"\"'> and so.customer_num = #{customerNum} </if>",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "group by bm.variety, bm.goods_level",
            "</script>"
    })
    List<Map<String, Object>> summarizeAssNumByVarietyAndGrade(@Param("orgId") String orgId,
                                                               @Param("saleDate") String saleDate,
                                                               @Param("lineNum") String lineNum,
                                                               @Param("marketNum") String marketNum,
                                                               @Param("customerNum") String customerNum,
                                                               @Param("productTypeList") List<String> productTypeList);

    /**
     * 统计可用白条数量（按品种、级别）。
     * 与物料表关联：货品、品种、级别一致；productTypeList 非空时按 {@code bm.product_type} 过滤。
     * variety_name 使用 MAX，避免与 group by bm.variety,bm.goods_level 在 ONLY_FULL_GROUP_BY 下冲突。
     */
    @Select({
            "<script>",
            "select bm.variety, MAX(bm.variety_name) as variety_name, pr.grade, count(*) as avail_ws_num ",
            "from production_regular pr, base_material bm ",
            "where pr.goods_num = bm.goods_num ",
            "  and pr.variety = bm.variety ",
            "  and pr.grade = bm.goods_level ",
            "  and pr.production_date = #{productionDate} ",
            "  and pr.is_fenpei = 0 ",
            "  and pr.del_flag = '0' ",
            "  and bm.del_flag = '0' ",
            "  and pr.org_id = #{orgId} ",
            "  and bm.org_id = #{orgId} ",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "group by bm.variety, bm.goods_level",
            "</script>"
    })
    List<Map<String, Object>> summarizeAvailWhiteStripByVarietyGrade(@Param("orgId") String orgId,
                                                                   @Param("productionDate") String productionDate,
                                                                   @Param("productTypeList") List<String> productTypeList);

    /**
     * 统计已分配白条数量（按品种、级别）。
     * 与物料表关联：material_code 与货品、品种、级别一致；productTypeList 非空时按 {@code bm.product_type} 过滤。
     * variety_name 使用 MAX，避免与 group by pwsa.variety,pwsa.grade 在 ONLY_FULL_GROUP_BY 下冲突。
     */
    @Select({
            "<script>",
            "select pwsa.variety, MAX(bm.variety_name) as variety_name, pwsa.grade, count(*) as alloc_ws_num ",
            "from production_white_strip_allocation pwsa, base_material bm ",
            "where pwsa.material_code = bm.goods_num ",
            "  and pwsa.variety = bm.variety ",
            "  and pwsa.grade = bm.goods_level ",
            "  and pwsa.production_date = #{productionDate} ",
            "  and pwsa.del_flag = '0' ",
            "  and bm.del_flag = '0' ",
            "  and pwsa.org_id = #{orgId} ",
            "  and bm.org_id = #{orgId} ",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "group by pwsa.variety, pwsa.grade",
            "</script>"
    })
    List<Map<String, Object>> summarizeAllocWhiteStripByVarietyGrade(@Param("orgId") String orgId,
                                                                     @Param("productionDate") String productionDate,
                                                                     @Param("productTypeList") List<String> productTypeList);

    /**
     * 查询销售订单的客户信息（物流线、市场、客户，distinct）。仅订单与物料，不关联生产表。
     * {@code productionDate} 用于筛选订单 {@code so.sale_date}；productTypeList 非空时按 {@code bm.product_type} 过滤。
     */
    @Select({
            "<script>",
            "select so.line_num, so.market_num, so.customer_num, sum(sog.ass_num) as ass_num ",
            "from sales_order_goods sog, sales_order so, base_material bm ",
            "where sog.order_num = so.order_num ",
            "  and sog.goods_num = bm.goods_num ",
            "  and sog.del_flag = '0' ",
            "  and so.del_flag = '0' ",
            "  and bm.del_flag = '0' ",
            "  and so.org_id = #{orgId} ",
            "  and sog.org_id = #{orgId} ",
            "  and bm.org_id = #{orgId} ",
            "  and so.sale_date = #{productionDate} ",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "GROUP BY so.line_num, so.market_num, so.customer_num ",
            "order by so.line_num, so.market_num, so.customer_num ",

            "</script>"
    })
    List<Map<String, Object>> listLineMarketCustomerByOrders(@Param("orgId") String orgId,
                                                                           @Param("productionDate") String productionDate,
                                                                           @Param("productTypeList") List<String> productTypeList);

    /**
     * 分割品副产品：订单货品明细。
     * {@code productionDate} 用于筛选订单 {@code so.sale_date}；productTypeList 非空时按 {@code bm.product_type} 过滤。
     */
    @Select({
            "<script>",
            "select sog.*, so.line_num, so.market_num, so.customer_num, so.customer_name, ",
            "       so.document_date as order_document_date, so.sale_date as order_sale_date, ",
            "       bm.variety, bm.variety_name, bm.goods_level, bm.product_type ",
            "from sales_order_goods sog, sales_order so, base_material bm ",
            "where sog.order_num = so.order_num ",
            "  and sog.goods_num = bm.goods_num ",
            "  and sog.del_flag = '0' ",
            "  and so.del_flag = '0' ",
            "  and bm.del_flag = '0' ",
            "  and so.org_id = #{orgId} ",
            "  and sog.org_id = #{orgId} ",
            "  and bm.org_id = #{orgId} ",
            "  and so.sale_date = #{productionDate} ",
            "  <if test='lineNum != null and lineNum != \"\"'> and so.line_num = #{lineNum} </if>",
            "  <if test='marketNum != null and marketNum != \"\"'> and so.market_num = #{marketNum} </if>",
            "  <if test='customerNum != null and customerNum != \"\"'> and so.customer_num = #{customerNum} </if>",
            "  <if test='productTypeList != null and productTypeList.size() &gt; 0'>",
            "    and bm.product_type in ",
            "    <foreach collection='productTypeList' item='pt' open='(' separator=',' close=')'>",
            "      #{pt}",
            "    </foreach>",
            "  </if>",
            "order by so.id desc, sog.id asc",
            "</script>"
    })
    List<Map<String, Object>> listSegmentedProductOrderGoodsDetail(@Param("orgId") String orgId,
                                                                   @Param("productionDate") String productionDate,
                                                                   @Param("lineNum") String lineNum,
                                                                   @Param("marketNum") String marketNum,
                                                                   @Param("customerNum") String customerNum,
                                                                   @Param("productTypeList") List<String> productTypeList);

    /**
     * 按订单明细主键（{@code pwsa.sale_id} = {@code sales_order_goods.id}）查询白条分配的最大发货日期。
     * saleIds 为空时不命中任何行。
     */
    @Select({
            "<script>",
            "select pwsa.sale_id as sale_id, MAX(pwsa.delivery_date) as delivery_date ",
            "from production_white_strip_allocation pwsa ",
            "where pwsa.del_flag = '0' ",
            "  and pwsa.org_id = #{orgId} ",
            "  <if test='saleIds != null and saleIds.size() &gt; 0'>",
            "    and pwsa.sale_id in ",
            "    <foreach collection='saleIds' item='sid' open='(' separator=',' close=')'>",
            "      #{sid}",
            "    </foreach>",
            "  </if>",
            "  <if test='saleIds == null or saleIds.size() == 0'>",
            "    and 1 = 0 ",
            "  </if>",
            "group by pwsa.sale_id",
            "</script>"
    })
    List<Map<String, Object>> listMaxDeliveryDateBySaleIds(@Param("orgId") String orgId,
                                                          @Param("saleIds") List<Long> saleIds);

}
