package com.clx.performance.sqlProvider;

import cn.hutool.core.collection.CollectionUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.clx.performance.enums.OrderChildEnum;
import com.clx.performance.model.OrderChild;
import com.clx.performance.param.app.PageOrderChildOfDriverParam;
import com.clx.performance.param.app.PageOrderChildOfDriverSearchParam;
import com.clx.performance.param.pc.*;
import com.clx.performance.vo.app.OrderChildVO;
import com.clx.performance.vo.pc.PageCarrierOrderChildVO;
import com.clx.performance.vo.pc.PageOrderChildPoundAuditVO;
import com.msl.common.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.StringJoiner;

public class OrderChildSqlProvider {

    public String pageOrderChildOfDriver(@Param("page") Page<OrderChildVO> page, @Param("param") PageOrderChildOfDriverParam param) {
        return new SQL(){{
            SELECT("child_no,goods_name,freight_price," +
                    "send_address,receive_address," +
                    "driver_user_no, driver_name,driver_mobile," +
                    "truck_no," +
                    "unload_pound_no,weight," +
                    "cancel_remark," +
                    "pound_status,status," +
                    "date_format(load_deadline, '%Y-%m-%d %H:%i:%s') as loadDeadline," +
                    "date_format(unload_time, '%Y-%m-%d %H:%i:%s') as unloadTime "
            );
            FROM("order_child ");
            WHERE("(driver_user_no = #{param.userNo} or truck_own_user_no = #{param.userNo}) ");

            if (Objects.isNull(param.getStatus())) {

            }
            else if (param.getStatus() == 1){   //运输中
                WHERE("status in (" + StringUtils.join(OrderChildEnum.TRANSIT_lIST, ",")+")");
            }
            else if (param.getStatus() == 2){   //完成
                WHERE("status in (" + StringUtils.join(OrderChildEnum.FINISH_lIST, ",")+")");
            }
            else if (param.getStatus() == 3){   //已结算
                WHERE("status in (" + StringUtils.join(OrderChildEnum.SETTLE_lIST, ",")+")");
            }
            else if (param.getStatus() == 4){   //取消
                WHERE("status in (" + StringUtils.join(OrderChildEnum.CANCEL_lIST, ",")+")");
            }

            ORDER_BY("create_time desc");
        }}.toString();
    }
    public String pageSearchOrderChildOfDriver(@Param("page") Page<OrderChildVO> page, @Param("param") PageOrderChildOfDriverSearchParam param) {
        return new SQL(){{
            SELECT("child_no,goods_name,freight_price," +
                    "send_address,receive_address," +
                    "driver_user_no, driver_name,driver_mobile," +
                    "truck_no," +
                    "unload_pound_no,weight," +
                    "cancel_remark," +
                    "pound_status,status," +
                    "date_format(load_deadline, '%Y-%m-%d %H:%i:%s') as loadDeadline," +
                    "date_format(unload_time, '%Y-%m-%d %H:%i:%s') as unloadTime "
            );
            FROM("order_child ");
            WHERE("(user_no = #{param.userNo} or driver_user_no = #{param.userNo}) ");
            if (StringUtils.isNotBlank(param.getContent())) {WHERE("(" +
                    "    owner_name like CONCAT(#{param.content},'%') " +
                    " or truck_no like CONCAT(#{param.content},'%') " +
                    " or driver_name like CONCAT(#{param.content},'%') " +
                    " or send_address like CONCAT(#{param.content},'%') " +
                    " or receive_address like CONCAT(#{param.content},'%') " +
                    ")");}


            ORDER_BY("create_time desc");
        }}.toString();
    }

    public String pagePoundAuditList(@Param("page") Page<PageOrderChildPoundAuditVO> page, @Param("param") PagePoundAuditParam param) {
        return new SQL(){{
            SELECT("child_no,pound_status as status,goods_name,send_address,receive_address,driver_name," +
                    "driver_mobile,load_net,unload_net,load_rough,load_tare,unload_rough,unload_tare,truck_no," +
                    "date_format(pay_time, '%Y-%m-%d %H:%i:%s') as payTime," +
                    "date_format(load_time, '%Y-%m-%d %H:%i:%s') as loadTime," +
                    "date_format(unload_time, '%Y-%m-%d %H:%i:%s') as unloadTime ");
            FROM("order_child ");
            WHERE("pound_status >=0");
            WHERE("status !=113");
            if (StringUtils.isNotBlank(param.getBeginTime())) {WHERE("pay_time >= #{param.beginTime}");}
            if (StringUtils.isNotBlank(param.getEndTime())) {WHERE("pay_time <= #{param.endTime}");}

            if (StringUtils.isNotBlank(param.getChildNo())) {WHERE("child_no = #{param.childNo}");}
            if (StringUtils.isNotBlank(param.getDriverName())) {WHERE("driver_name= #{param.driverName}");}
            if (StringUtils.isNotBlank(param.getDriverMobile())) {WHERE("driver_mobile = #{param.driverMobile}");}

            if (StringUtils.isNotBlank(param.getSendAddress())) {WHERE("send_address = #{param.sendAddress}");}
            if (StringUtils.isNotBlank(param.getReceiveAddress())) {WHERE("receive_address= #{param.receiveAddress}");}
            if (Objects.nonNull(param.getStatus())) {WHERE("pound_status = #{param.status}");}

            ORDER_BY("pound_status,unload_time");
        }}.toString();
    }


    public String pageCarrierOrderChildList(@Param("page") Page<PageCarrierOrderChildVO> page, @Param("param") PageCarrierOrderChildParam param) {
        return new SQL(){{
            SELECT("truck_no,child_no,order_goods_no,status,goods_name," +
                    "freight_price,send_address,receive_address,load_rough,load_tare,unload_rough,unload_tare," +
                    "driver_name,driver_mobile,load_net,unload_net," +
                    "date_format(pay_time, '%Y-%m-%d %H:%i:%s') as payTime," +
                    "date_format(finish_time, '%Y-%m-%d %H:%i:%s') as finishTime," +
                    "date_format(pound_audit_time, '%Y-%m-%d %H:%i:%s') as poundAuditTime");
            FROM("order_child ");
            if (Objects.equals(param.getTimeType(),1)) {
                if (StringUtils.isNotBlank(param.getBeginTime())) {WHERE("pay_time >= #{param.beginTime}");}
                if (StringUtils.isNotBlank(param.getEndTime())) {WHERE("pay_time <= #{param.endTime}");}
            }else if (Objects.equals(param.getTimeType(),2)) {
                if (StringUtils.isNotBlank(param.getBeginTime())) {WHERE("pound_audit_time >= #{param.beginTime}");}
                if (StringUtils.isNotBlank(param.getEndTime())) {WHERE("pound_audit_time <= #{param.endTime}");}
            }else if (Objects.equals(param.getTimeType(),3)) {
                if (StringUtils.isNotBlank(param.getBeginTime())) {WHERE("finish_time >= #{param.beginTime}");}
                if (StringUtils.isNotBlank(param.getEndTime())) {WHERE("finish_time <= #{param.endTime}");}
            }

            if (StringUtils.isNotBlank(param.getOrderGoodsNo())) {WHERE("order_goods_no = #{param.orderGoodsNo}");}
            if (StringUtils.isNotBlank(param.getChildNo())) {WHERE("child_no = #{param.childNo}");}
            if (StringUtils.isNotBlank(param.getDriverName())) {WHERE("driver_name= #{param.driverName}");}
            if (StringUtils.isNotBlank(param.getDriverMobile())) {WHERE("driver_mobile = #{param.driverMobile}");}
            if(StringUtils.isNotBlank(param.getSendAddress())){WHERE("send_address like CONCAT(#{param.sendAddress},'%') ");}
            if(StringUtils.isNotBlank(param.getReceiveAddress())){WHERE("receive_address like CONCAT(#{param.receiveAddress},'%') ");}
            if (Objects.nonNull(param.getStatus())) {WHERE("status = #{param.status}");}
            if (StringUtils.isNotBlank(param.getTruckNo())) {WHERE("truck_no like '%"+ param.getTruckNo() +"%'");}
            if (CollectionUtil.isNotEmpty(param.getStatusList())) {
                WHERE("status in (" + StringUtils.join(param.getStatusList(), ",") +")");
            }


            ORDER_BY("id desc");
        }}.toString();
    }


    public String batchUpdateOrderChildStatus(@Param(value ="status") Integer status, @Param(value ="remark") String remark,
                                              @Param(value = "cancelTime") String cancelTime, @Param(value = "finishTime") String finishTime,
                                              @Param(value = "childNoList") List<String> childNoList ){
        StringBuffer sqlList = new StringBuffer();

        sqlList.append("update order_child set cancel_remark = '"+ remark +"' ,status = " + status + ",cancel_time = '"
                + cancelTime +"',finish_time = '" + finishTime + "'  where child_no in (");
        for (int i = 0; i < childNoList.size(); i++) {
            String childNo = childNoList.get(i);

            sqlList.append("'").append(childNo).append("'");

            if (i < childNoList.size() - 1) {
                sqlList.append(",");
            }
        }
        sqlList.append(")");
        return sqlList.toString();
    }


    public String listAfterArrayReceiveChild(String orderNo){
        String sql = new SQL() {{
            SELECT("sum(load_net)");
            FROM("order_child ");
            WHERE("order_no = #{orderNo}");
            WHERE("status >=" + OrderChildEnum.Status.ARRIVE_RECEIVE.getCode());
            WHERE("status <=" + OrderChildEnum.Status.COMPLETE.getCode());
        }}.toString();
        return sql;
    }

    public String findArtificialCancelOrder(@Param("param") ArtificialCancelOrderQueryParam param) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT child_no, goods_name, freight_price, order_no, ");
        sql.append("send_address, receive_address, ");
        sql.append("driver_user_no, driver_name, driver_mobile, ");
        sql.append("truck_no, unload_pound_no, weight, ");
        sql.append("cancel_remark, pound_status, status, ");
        sql.append("DATE_FORMAT(load_deadline, '%Y-%m-%d %H:%i:%s') AS loadDeadline, ");
        sql.append("DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') AS createTime, ");
        sql.append("DATE_FORMAT(unload_time, '%Y-%m-%d %H:%i:%s') AS unloadTime ");
        sql.append("FROM order_child ");
        sql.append("WHERE 1=1 ");

        List<String> conditions = new ArrayList<>();
        if (StringUtils.isNotBlank(param.getOrderChildNo())) {
            conditions.add("child_no = #{param.orderChildNo}");
        }
        if (StringUtils.isNotBlank(param.getTruckNo())) {
            conditions.add("truck_no = #{param.truckNo}");
        }
        List<Integer> statuses = Arrays.asList(
                OrderChildEnum.Status.CREATED.getCode(),
                OrderChildEnum.Status.GO_TO_SEND.getCode(),
                OrderChildEnum.Status.ARRIVE_SEND.getCode(),
                OrderChildEnum.Status.LOAD.getCode(),
                OrderChildEnum.Status.GO_TO_RECEIVE.getCode(),
                OrderChildEnum.Status.ARRIVE_RECEIVE.getCode()
        );
        conditions.add("status IN (" + StringUtils.join(statuses, ", ") + ")");
        sql.append("AND ");
        sql.append(StringUtils.join(conditions, " AND "));
        return sql.toString();
    }

    public String orderChildCancelRecord(@Param("page") Page<OrderChildVO> page, @Param("param") OrderChildCancelRecordParam param) {
        return new SQL(){{
            SELECT("child_no,goods_name,freight_price," +
                    "send_address,receive_address," +
                    "driver_user_no, driver_name,driver_mobile," +
                    "truck_no,cancel_type,cancel_remark," +
                    "unload_pound_no,weight," +
                    "cancel_remark,order_no," +
                    "pound_status,status," +
                    "date_format(load_deadline, '%Y-%m-%d %H:%i:%s') as loadDeadline," +
                    "date_format(load_deadline, '%Y-%m-%d %H:%i:%s') as loadDeadline," +
                    "date_format(cancel_time, '%Y-%m-%d %H:%i:%s') as cancelTime "
            );
            FROM("order_child ");

            WHERE("status in (" + StringUtils.join(OrderChildEnum.CANCEL_lIST, ",")+")");


            ORDER_BY("create_time desc");
        }}.toString();
    }



    public String getLossNet(@Param("param") OrderChildReportParam param) {
        return new SQL(){{
            SELECT("IF (( a.unload_net < a.load_net ),( a.load_net - a.unload_net ),   0    )  as lossNet  "
            );
            FROM("order_child a ");

            WHERE("a.status in (" + StringUtils.join(OrderChildEnum.FINISH_lIST, ",")+")");
            WHERE("a.receive_address_id = " + param.getReceiveAddressId());
            WHERE("a.send_address_id = " + param.getSendAddressId());

            WHERE("a.pay_time >= '" +param.getBeginTime()+"'");


        }}.toString();
    }


    public String statisticsTruckMonth(@Param("startTime") LocalDateTime startTime, @Param("endTime")LocalDateTime endTime,  @Param("truckNo")String truckNo) {
        return "select a.monthOrderNumber,if(a.total,a.total,0) monthIncome from (" +
                " SELECT count(*) AS monthOrderNumber,sum(freight) total FROM  order_child  WHERE truck_no = #{truckNo} and AND finish_time >= #{startTime} AND finish_time <=  #{endTime}" +
                ") a";
    }


    public String countValidByOrderGoodsNoList(@Param("orderGoodsList") List<String> orderGoodsList){
        return new SQL(){{
            SELECT("order_goods_no,count(1) count ");
            FROM("order_child ");

            StringJoiner sj = new StringJoiner(",","order_goods_no in (",")");
            orderGoodsList.forEach(item -> sj.add("'"+item+"'"));
            WHERE(sj.toString());
            WHERE("  status < " + OrderChildEnum.Status.DRIVER_CANCEL.getCode());
            GROUP_BY(" order_goods_no");
        }}.toString();
    }


    public String batchUpdateCancel(List<OrderChild> list){
        StringJoiner sj = new StringJoiner("; ");
        for(OrderChild child : list){
            sj.add("update order_child " +
                    "set " +
                    "cancel_remark = '" + child.getCancelRemark() + "',"  +
                    "cancel_time = '" + DateUtils.formatDateTime(child.getCancelTime()).get() + "',"  +
                    "finish_time = '" + DateUtils.formatDateTime(child.getFinishTime()).get() + "',"  +
                    "status = " + child.getStatus() + "  "  +
                    "where id = "+child.getId()
            );
        }
        return sj.toString();
    }

}
