package cn.iocoder.foodnexus.module.order.dal.mysql.customerorder;

import java.util.*;

import cn.iocoder.foodnexus.framework.common.pojo.PageResult;
import cn.iocoder.foodnexus.framework.common.util.CommonUtil;
import cn.iocoder.foodnexus.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.foodnexus.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.foodnexus.framework.mybatis.core.query.MPJLambdaWrapperX;
import cn.iocoder.foodnexus.framework.mybatis.core.query.QueryWrapperX;
import cn.iocoder.foodnexus.module.erp.dal.dataobject.purchase.ErpPurchaseOrderDO;
import cn.iocoder.foodnexus.module.erp.dal.dataobject.purchase.ErpSupplierDO;
import cn.iocoder.foodnexus.module.erp.dal.dataobject.sale.ErpCustomerDO;
import cn.iocoder.foodnexus.module.erp.dal.dataobject.sale.ErpSaleOrderDO;
import cn.iocoder.foodnexus.module.order.controller.app.customerOrder.vo.AppCustomerMonthOrderRespVO;
import cn.iocoder.foodnexus.module.order.controller.app.customerOrder.vo.AppCustomerMonthOrderTotalRespVO;
import cn.iocoder.foodnexus.module.order.controller.app.customerOrder.vo.AppCustomerYearOrderPageReqVO;
import cn.iocoder.foodnexus.module.order.dal.dataobject.customerorder.CustomerOrderDO;
import cn.iocoder.foodnexus.module.order.enums.CustomerOrderStatus;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.github.yulichang.query.MPJQueryWrapper;
import org.apache.ibatis.annotations.Mapper;
import cn.iocoder.foodnexus.module.order.controller.admin.customerorder.vo.*;

/**
 * 客户总订单 Mapper
 *
 * @author 超级管理员
 */
@Mapper
public interface CustomerOrderMapper extends BaseMapperX<CustomerOrderDO> {

    default PageResult<CustomerOrderDO> selectPage(CustomerOrderPageReqVO reqVO) {
        MPJLambdaWrapperX<CustomerOrderDO> queryWrapperX = new MPJLambdaWrapperX<CustomerOrderDO>()
                .inIfPresent(CustomerOrderDO::getId, reqVO.getIds())
                .eqIfPresent(CustomerOrderDO::getCode, reqVO.getCode())
                .eqIfPresent(CustomerOrderDO::getOrderStatus, reqVO.getOrderStatus())
                .inIfPresent(CustomerOrderDO::getOrderStatus, reqVO.getOrderStatusList())
                .eqIfPresent(CustomerOrderDO::getCustomerId, reqVO.getCustomerId())
                .eqIfPresent(CustomerOrderDO::getWarehouseId, reqVO.getWarehouseId())
                .eqIfPresent(CustomerOrderDO::getWarehouseAreaId, reqVO.getWarehouseAreaId())
                .eqIfPresent(CustomerOrderDO::getWarehouseInfo, reqVO.getWarehouseInfo())
                .eqIfPresent(CustomerOrderDO::getDeliveryMode, reqVO.getDeliveryMode())
                .eqIfPresent(CustomerOrderDO::getProductCount, reqVO.getProductCount())
                .eqIfPresent(CustomerOrderDO::getSupplierCount, reqVO.getSupplierCount())
                .betweenIfPresent(CustomerOrderDO::getPlanDeliveryStartTime, reqVO.getPlanDeliveryStartTime())
                .betweenIfPresent(CustomerOrderDO::getPlanDeliveryEndTime, reqVO.getPlanDeliveryEndTime())
                .eqIfPresent(CustomerOrderDO::getOrderAmount, reqVO.getOrderAmount())
                .eqIfPresent(CustomerOrderDO::getActualAmount, reqVO.getActualAmount())
                .eqIfPresent(CustomerOrderDO::getOperCompany, reqVO.getOperCompany())
                .betweenIfPresent(CustomerOrderDO::getCreateTime, reqVO.getCreateTime())
                .eqIfPresent(CustomerOrderDO::getCreator, reqVO.getCreator())
                .eqIfPresent(CustomerOrderDO::getHasScore, reqVO.getHasScore())
                .likeIfPresent(CustomerOrderDO::getAddressInfo, reqVO.getContactPhone())
                .likeIfPresent(CustomerOrderDO::getAddressInfo, reqVO.getContactUser())
                .orderByDesc(CustomerOrderDO::getId);
        if (CommonUtil.isNotBlank(reqVO.getCreateYearMonth())) {
            String createMonth = reqVO.getCreateYearMonth().trim();
            String startOfMonth = createMonth + "-01 00:00:00";
            String[] parts = createMonth.split("-");
            int year = Integer.parseInt(parts[0]);
            int nextMonth = Integer.parseInt(parts[1]) + 1;
            String endOfMonth = nextMonth > 12 ?
                    (year + 1) + "-01-01 00:00:00" :
                    year + "-" + (nextMonth < 10 ? "0" + nextMonth : nextMonth) + "-01 00:00:00";

            queryWrapperX.ge(CustomerOrderDO::getCreateTime, startOfMonth)
                    .lt(CustomerOrderDO::getCreateTime, endOfMonth);
        }
        if (CommonUtil.isNotEmpty(reqVO.getFilterToBeScore()) && reqVO.getFilterToBeScore()) {
            queryWrapperX.eq(CustomerOrderDO::getHasScore, Boolean.FALSE);
            queryWrapperX.in(CustomerOrderDO::getOrderStatus, CommonUtil.asList(CustomerOrderStatus.SIGN_RECEIPT.getKey(),
                    CustomerOrderStatus.FINISH.getKey()));
        }
        if (CommonUtil.isNotEmpty(reqVO.getDeliveryTime())) {
            queryWrapperX.leftJoin(ErpSaleOrderDO.class, ErpSaleOrderDO::getCustomerOrderId, CustomerOrderDO::getId);
            queryWrapperX.betweenIfPresent(ErpSaleOrderDO::getDeliveryTime, reqVO.getDeliveryTime());
        }
        return selectPage(reqVO, queryWrapperX);
    }

    default List<Map<String, Object>> queryStatusCount(Long loginUserId) {
        QueryWrapperX<CustomerOrderDO> queryWrapper = new QueryWrapperX<>();
        queryWrapper.select("order_status, count(*) as 'status_count'");
        queryWrapper.eq("creator", loginUserId);
        queryWrapper.groupBy("order_status");
        return this.selectMaps(queryWrapper);
    }

    default AppCustomerMonthOrderRespVO reconciliationPageYearTotal(String year) {
        year = year.trim();
        String begin = year + "-01-01 00:00:00";
        String end = year + "-12-31 23:59:59";
        MPJQueryWrapper<CustomerOrderDO> queryWrapperX = new MPJQueryWrapper<>();
        queryWrapperX.select("count(*) as 'orderCount',SUM(order_amount) as 'orderAmount'");
        queryWrapperX.between("create_time", begin, end);
        queryWrapperX.in("order_status", CommonUtil.asList(CustomerOrderStatus.SIGN_RECEIPT.getKey(),
                CustomerOrderStatus.FINISH.getKey()));
        return this.selectJoinOne(AppCustomerMonthOrderRespVO.class, queryWrapperX);
    }

    default PageResult<AppCustomerMonthOrderRespVO.MonthItems> reconciliationPageYearPage(AppCustomerYearOrderPageReqVO reqVO) {
        String year = reqVO.getYear();
        String begin = year + "-01-01 00:00:00";
        String end = year + "-12-31 23:59:59";
        MPJQueryWrapper<CustomerOrderDO> queryWrapperX = new MPJQueryWrapper<>();
        queryWrapperX.select("DATE_FORMAT(create_time, '%Y年%m月') as 'yearMonth',count(*) as 'orderCount',SUM(order_amount) as 'orderAmount',SUM(actual_amount) as 'payableAmount'");
        queryWrapperX.select("CASE WHEN SUM(CASE WHEN order_status='SIGN_RECEIPT' THEN 1 ELSE 0 END)> 0 THEN 0 ELSE 1 END AS 'status'");
        queryWrapperX.between("create_time", begin, end);
        queryWrapperX.in("order_status", CommonUtil.asList(CustomerOrderStatus.SIGN_RECEIPT.getKey(),
                CustomerOrderStatus.FINISH.getKey()));
        queryWrapperX.groupBy("DATE_FORMAT(create_time, '%Y-%m') ");
        queryWrapperX.orderByDesc("id");

        return this.selectJoinPage(reqVO, AppCustomerMonthOrderRespVO.MonthItems.class, queryWrapperX);
    }

    default AppCustomerMonthOrderTotalRespVO reconciliationMonthTotal(String yearMonth) {
        String createMonth = yearMonth.trim();
        String startOfMonth = createMonth + "-01 00:00:00";
        String[] parts = createMonth.split("-");
        int year = Integer.parseInt(parts[0]);
        int nextMonth = Integer.parseInt(parts[1]) + 1;
        String endOfMonth = nextMonth > 12 ?
                (year + 1) + "-01-01 00:00:00" :
                year + "-" + (nextMonth < 10 ? "0" + nextMonth : nextMonth) + "-01 00:00:00";

        MPJQueryWrapper<CustomerOrderDO> queryWrapperX = new MPJQueryWrapper<>();
        queryWrapperX.select("count(*) as 'orderCount',SUM(order_amount) as 'orderAmount'");
        queryWrapperX.between("create_time", startOfMonth, endOfMonth);
        queryWrapperX.in("order_status", CommonUtil.asList(CustomerOrderStatus.SIGN_RECEIPT.getKey(),
                CustomerOrderStatus.FINISH.getKey()));
        queryWrapperX.orderByDesc("id");
        return this.selectJoinOne(AppCustomerMonthOrderTotalRespVO.class, queryWrapperX);
    }

    default PageResult<CustomerOrderReconPageRespVO> pageReconciliation(CustomerOrderReconPageReqVO pageReqVO) {
        MPJLambdaWrapperX<CustomerOrderDO> wrapperX = new MPJLambdaWrapperX<>();
        wrapperX.rightJoin(ErpCustomerDO.class, ErpCustomerDO::getId, CustomerOrderDO::getCustomerId);
        wrapperX.select("DATE_FORMAT( t.create_time, '%Y-%m' ) AS 'yearMonth'");
        wrapperX.select("t1.id AS 'customerId'");
        wrapperX.select("t1.NAME AS 'customerName'");
        wrapperX.select("count(t.id) as 'orderCount'");
        wrapperX.select("IFNULL(SUM(t.order_amount),0) as 'orderAmount'");
        wrapperX.select("IFNULL(sum(t.actual_amount),0) as 'actualAmount'");
        wrapperX.select("CASE WHEN SUM(CASE WHEN t.order_status='SIGN_RECEIPT' THEN 1 ELSE 0 END)> 0 THEN 0 ELSE 1 END AS 'isFinish'");
        wrapperX.in(CustomerOrderDO::getOrderStatus, CommonUtil.asList(CustomerOrderStatus.SIGN_RECEIPT.getKey(),
                CustomerOrderStatus.FINISH.getKey()));
        wrapperX.likeIfPresent(ErpCustomerDO::getName, pageReqVO.getCustomerName());
        if (CommonUtil.isNotBlank(pageReqVO.getYearMonth())) {
            String createMonth = pageReqVO.getYearMonth().trim();
            String startOfMonth = createMonth + "-01 00:00:00";
            String[] parts = createMonth.split("-");
            int year = Integer.parseInt(parts[0]);
            int nextMonth = Integer.parseInt(parts[1]) + 1;
            String endOfMonth = nextMonth > 12 ?
                    (year + 1) + "-01-01 00:00:00" :
                    year + "-" + (nextMonth < 10 ? "0" + nextMonth : nextMonth) + "-01 00:00:00";
            wrapperX.between(CustomerOrderDO::getCreateTime, startOfMonth, endOfMonth);
        }
        wrapperX.groupBy("DATE_FORMAT( t.create_time, '%Y-%m' )", "t1.id");
        wrapperX.orderByDesc("DATE_FORMAT( t.create_time, '%Y-%m' )", "t1.id");

        return this.selectJoinPage(pageReqVO, CustomerOrderReconPageRespVO.class, wrapperX);
    }
}