package cn.iocoder.yudao.module.mes.dal.mysql.em.record;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.mes.controller.admin.em.record.vo.MesEmRecordPageReqVO;
import cn.iocoder.yudao.module.mes.dal.dataobject.em.record.MesEmRecordDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * MES 能耗抄表记录 Mapper
 *
 * @author 超级管理员
 */
@Mapper
public interface MesEmRecordMapper extends BaseMapperX<MesEmRecordDO> {

    default PageResult<MesEmRecordDO> selectPage(MesEmRecordPageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<MesEmRecordDO>()
                .eqIfPresent(MesEmRecordDO::getMeterId, reqVO.getMeterId())
                .likeIfPresent(MesEmRecordDO::getMeterName, reqVO.getMeterName())
                .eqIfPresent(MesEmRecordDO::getEnergyType, reqVO.getEnergyType())
                .eqIfPresent(MesEmRecordDO::getWorkshopId, reqVO.getWorkshopId())
                .eqIfPresent(MesEmRecordDO::getSource, reqVO.getSource())
                .betweenIfPresent(MesEmRecordDO::getRecordDate, reqVO.getRecordDate())
                .orderByDesc(MesEmRecordDO::getRecordDate)
                .orderByDesc(MesEmRecordDO::getId));
    }

    default MesEmRecordDO selectByMeterIdAndDate(Long meterId, LocalDate recordDate) {
        return selectOne(new LambdaQueryWrapperX<MesEmRecordDO>()
                .eq(MesEmRecordDO::getMeterId, meterId)
                .eq(MesEmRecordDO::getRecordDate, recordDate));
    }

    default MesEmRecordDO selectLastByMeterId(Long meterId, LocalDate beforeDate, Long excludeId) {
        return selectOne(new LambdaQueryWrapperX<MesEmRecordDO>()
                .eq(MesEmRecordDO::getMeterId, meterId)
                .lt(MesEmRecordDO::getRecordDate, beforeDate)
                .ne(excludeId != null, MesEmRecordDO::getId, excludeId)
                .orderByDesc(MesEmRecordDO::getRecordDate)
                .orderByDesc(MesEmRecordDO::getId)
                .last("LIMIT 1"));
    }

    default List<MesEmRecordDO> selectListByMeterIdAndDateRange(Long meterId, LocalDate begin, LocalDate end) {
        return selectList(new LambdaQueryWrapperX<MesEmRecordDO>()
                .eq(MesEmRecordDO::getMeterId, meterId)
                .between(MesEmRecordDO::getRecordDate, begin, end));
    }

    /**
     * 统计某个考核对象在指定区间内的用量
     *
     * @param objectType 0 全厂 / 1 车间 / 2 产线 / 3 设备
     */
    @Select("""
            <script>
            SELECT COALESCE(SUM(usage_amount), 0)
            FROM mes_em_record
            WHERE deleted = 0
              AND energy_type = #{energyType}
              AND record_date BETWEEN #{begin} AND #{end}
              <if test="objectType == 1">AND workshop_id = #{objectId}</if>
              <if test="objectType == 2">AND line_id = #{objectId}</if>
              <if test="objectType == 3">AND machinery_id = #{objectId}</if>
            </script>
            """)
    BigDecimal sumUsage(@Param("energyType") Integer energyType,
                        @Param("objectType") Integer objectType,
                        @Param("objectId") Long objectId,
                        @Param("begin") LocalDate begin,
                        @Param("end") LocalDate end);

    @Select("""
            SELECT COALESCE(SUM(usage_amount), 0)
            FROM mes_em_record
            WHERE deleted = 0 AND meter_id = #{meterId} AND record_date BETWEEN #{begin} AND #{end}
            """)
    BigDecimal sumMeterUsage(@Param("meterId") Long meterId,
                             @Param("begin") LocalDate begin,
                             @Param("end") LocalDate end);

    @Select("""
            SELECT COALESCE(SUM(usage_amount), 0)
            FROM mes_em_record
            WHERE deleted = 0 AND energy_type = #{energyType} AND record_date BETWEEN #{begin} AND #{end}
            """)
    BigDecimal sumTypeUsage(@Param("energyType") Integer energyType,
                            @Param("begin") LocalDate begin,
                            @Param("end") LocalDate end);

    default List<MesEmRecordDO> selectListByDateRange(LocalDate begin, LocalDate end) {
        return selectList(new LambdaQueryWrapperX<MesEmRecordDO>()
                .between(MesEmRecordDO::getRecordDate, begin, end)
                .orderByAsc(MesEmRecordDO::getRecordDate)
                .orderByAsc(MesEmRecordDO::getId));
    }

    // ==================== 统计查询 ====================

    default List<Map<String, Object>> selectTypeStats(LocalDate begin, LocalDate end) {
        return selectMaps(new QueryWrapper<MesEmRecordDO>()
                .select("energy_type AS energyType",
                        "COALESCE(SUM(usage_amount), 0) AS usageAmount",
                        "COALESCE(SUM(cost), 0) AS costAmount",
                        "COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce")
                .between("record_date", begin, end)
                .groupBy("energy_type")
                .orderByAsc("energy_type"));
    }

    default List<Map<String, Object>> selectWorkshopStats(LocalDate begin, LocalDate end) {
        return selectMaps(new QueryWrapper<MesEmRecordDO>()
                .select("workshop_id AS workshopId",
                        "workshop_name AS workshopName",
                        "COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce",
                        "COALESCE(SUM(cost), 0) AS costAmount")
                .between("record_date", begin, end)
                .isNotNull("workshop_id")
                .groupBy("workshop_id", "workshop_name")
                .orderByDesc("tce"));
    }

    default List<Map<String, Object>> selectTrendRows(LocalDate begin, LocalDate end) {
        return selectMaps(new QueryWrapper<MesEmRecordDO>()
                .select("record_date AS recordDate",
                        "energy_type AS energyType",
                        "COALESCE(SUM(usage_amount), 0) AS usageAmount",
                        "COALESCE(SUM(cost), 0) AS costAmount",
                        "COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce")
                .between("record_date", begin, end)
                .groupBy("record_date", "energy_type")
                .orderByAsc("record_date"));
    }

    default List<Map<String, Object>> selectTopMeterStats(LocalDate begin, LocalDate end, int limit) {
        return selectMaps(new QueryWrapper<MesEmRecordDO>()
                .select("meter_id AS meterId",
                        "meter_name AS meterName",
                        "energy_type AS energyType",
                        "unit AS unit",
                        "COALESCE(SUM(usage_amount), 0) AS usageAmount",
                        "COALESCE(SUM(cost), 0) AS costAmount",
                        "COALESCE(SUM(usage_amount * coal_factor) / 1000, 0) AS tce")
                .between("record_date", begin, end)
                .groupBy("meter_id", "meter_name", "energy_type", "unit")
                .orderByDesc("tce")
                .last("LIMIT " + limit));
    }

    default Long selectCountByMeterId(Long meterId) {
        return selectCount(new LambdaQueryWrapperX<MesEmRecordDO>()
                .eq(MesEmRecordDO::getMeterId, meterId));
    }

}