package com.clx.performance.sqlProvider;

import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.clx.performance.enums.OrderGoodsStatusEnum;
import com.clx.performance.enums.OrderGoodsStopEnum;
import com.clx.performance.enums.PendingOrderWayStatusEnum;
import com.clx.performance.model.OrderGoods;
import com.clx.performance.param.app.AppGoodsOrderSearchParam;
import com.clx.performance.param.pc.OrderGoodsEditParams;
import com.clx.performance.param.pc.PageOrderGoodsListParam;
import com.clx.performance.vo.app.OrderGoodsAPPVO;
import com.clx.performance.vo.pc.OrderGoodsVO;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.math.BigDecimal;
import java.util.List;
import java.util.Objects;
import java.util.StringJoiner;

public class OrderGoodsSqlProvider {

    public String getMaxOrderGoodsId(String type) {
        return "SELECT s.order_goods_no FROM  `order_goods` s WHERE s. id = ( SELECT max(id) FROM order_goods )";
    }

    public String pageOrderGoodsList(@Param("page") Page<OrderGoodsVO> page, @Param("param") PageOrderGoodsListParam param) {
        String sql = new SQL() {{
            SELECT("a.id, a.order_no," +
                    " a.order_goods_no, a.order_goods_status, a.extract_weight, " +
                    " if(a.residue_transport_weight < 0, '0', a.residue_transport_weight) as residue_transport_weight ,a.already_transport_weight, a.send_address_id, a.send_address_shorter,a.send_longitude," +
                    " a.send_latitude,a.receive_address_id, a.receive_address_shorter,a.receive_longitude,a.receive_latitude," +
                    "a.goods_id, a.goods_name, a.pending_order_way," +
                    " date_format(a.directional_expire_time, '%Y-%m-%d %H:%i:%s') as directional_expire_time ," +
                    " date_format(a.last_arrive_send_time, '%Y-%m-%d %H:%i:%s') as last_arrive_send_time ," +
                    " date_format(a.pending_order_time, '%Y-%m-%d %H:%i:%s') as pending_order_time," +
                    " date_format(a.create_time, '%Y-%m-%d %H:%i:%s') as create_time," +
                    " date_format(a.modified_time, '%Y-%m-%d %H:%i:%s') as modified_time," +
                    "a.extract_way,a.need_truck_num,a.senior_logistics_manager_id,a.senior_logistics_manager_name,a.pending_order_freight," +
                    "a.user_no,a.user_name,a.stop_flag,a.batch_number,a.last_arrive_receive_time,a.last_load_time,a.last_order_time,a.vehicle_usage," +
                    " a.platform_freight_quotation_tax_type, a.platform_service_fee_rate, " +
                    " a.deposit, a.invoice_service_fee_rate, a.settlement_way, a.support_loan ");
            FROM("order_goods a");

            if (StringUtils.isNotBlank(param.getBeginTime()) && StringUtils.isNotBlank(param.getEndTime())) {
                WHERE("a.create_time >= #{param.beginTime}");
                WHERE("a.create_time <= #{param.endTime}");
            }

            if (Objects.nonNull(param.getTruckDemand())) {
                if (1 == param.getTruckDemand()) {
                    WHERE("a.truck_demand = #{param.truckDemand}");
                } else if (2 == param.getTruckDemand()) {
                    WHERE("a.truck_demand = #{param.truckDemand}");
                } else if (3 == param.getTruckDemand()) {
                    WHERE("a.truck_demand = #{param.truckDemand}");
                }
            }
            if (Objects.nonNull(param.getOrderGoodsStatus())) {
                if (OrderGoodsStatusEnum.Status.CREATED.getCode().equals(param.getOrderGoodsStatus())) {
                    WHERE("a.order_goods_status = #{param.orderGoodsStatus}");
                } else if (OrderGoodsStatusEnum.Status.PAYING.getCode().equals(param.getOrderGoodsStatus())) {
                    WHERE("a.order_goods_status = #{param.orderGoodsStatus}");
                } else if (OrderGoodsStatusEnum.Status.GO_TO_SEND.getCode().equals(param.getOrderGoodsStatus())) {
                    WHERE("a.order_goods_status = #{param.orderGoodsStatus}");
                } else if (OrderGoodsStatusEnum.Status.SUCCESS.getCode().equals(param.getOrderGoodsStatus())) {
                    WHERE("a.order_goods_status = #{param.orderGoodsStatus}");
                } else if (OrderGoodsStatusEnum.Status.CANCEL.getCode().equals(param.getOrderGoodsStatus())) {
                    WHERE("a.order_goods_status = #{param.orderGoodsStatus}");
                }else if (OrderGoodsStatusEnum.Status.COMPLETED.getCode().equals(param.getOrderGoodsStatus())) {
                    WHERE("a.order_goods_status = #{param.orderGoodsStatus}");
                }
            }

            if (StringUtils.isNotBlank(param.getOrderNo())) {
                WHERE("a.order_no = #{param.orderNo}");
            }
            if (StringUtils.isNotBlank(param.getOrderGoodsNo())) {
                WHERE("a.order_goods_no = #{param.orderGoodsNo}");
            }
            ORDER_BY("a.create_time desc");
        }}.toString();
        return sql;
    }


    public String openOrderPageGoodsList(@Param("page") Page<OrderGoodsAPPVO> page, @Param("orderGoodsNoList") List<String> orderGoodsNoList) {
        String sql = new SQL() {{
            SELECT("a.id, a.order_no," +
                    " a.order_goods_no, a.order_goods_status, a.extract_weight, " +
                    " a.residue_transport_weight,a.already_transport_weight, a.send_address_id, a.send_address_shorter,a.send_longitude," +
                    " a.send_latitude,a.receive_address_id, a.receive_address_shorter,a.receive_longitude,a.receive_latitude," +
                    "a.goods_id, a.goods_name, a.pending_order_way," +
                    " date_format(a.directional_expire_time, '%Y-%m-%d %H:%i:%s') as directional_expire_time ," +
                    " date_format(a.last_arrive_send_time, '%Y-%m-%d %H:%i:%s') as last_arrive_send_time ," +
                    " date_format(a.pending_order_time, '%Y-%m-%d %H:%i:%s') as pending_order_time," +
                    " date_format(a.create_time, '%Y-%m-%d %H:%i:%s') as create_time," +
                    " date_format(a.modified_time, '%Y-%m-%d %H:%i:%s') as modified_time," +
                    "a.extract_way,a.need_truck_num,a.senior_logistics_manager_id,a.senior_logistics_manager_name,a.pending_order_freight," +
                    "a.user_no,a.user_name");
            FROM("order_goods a");
            WHERE("a.stop_flag = "+ OrderGoodsStopEnum.Status.NO.getCode());
            if(CollectionUtils.isEmpty(orderGoodsNoList)){
                WHERE(" a.pending_order_way = " + PendingOrderWayStatusEnum.Status.GK.getCode());
            }else{
                StringJoiner sj = new StringJoiner(",","a.order_goods_no in (",")");
                orderGoodsNoList.forEach(item -> sj.add("'"+item+"'"));
                WHERE(" (a.pending_order_way = " + PendingOrderWayStatusEnum.Status.GK.getCode() + " OR (a.pending_order_way = " +
                        PendingOrderWayStatusEnum.Status.FLEET_GK.getCode() + " and "  + sj + "))");
            }
            WHERE("a.last_arrive_send_time >= now()");
            WHERE("a.order_goods_status IN ( "+OrderGoodsStatusEnum.Status.PAYING.getCode()+","+OrderGoodsStatusEnum.Status.GO_TO_SEND.getCode()+")");
            WHERE("a.residue_transport_weight >=35");
            ORDER_BY("a.pending_order_time desc");
        }}.toString();
        return sql;
    }


    public String queryProcessFleetOpenTruckList() {
        String sql = new SQL() {{
            SELECT("b.*");
            FROM("order_goods a");
            LEFT_OUTER_JOIN("order_goods_fleet_open_truck b on a.order_goods_no = b.order_goods_no");
            WHERE("a.stop_flag = "+ OrderGoodsStopEnum.Status.NO.getCode());
            WHERE(" a.pending_order_way = " + PendingOrderWayStatusEnum.Status.FLEET_GK.getCode());
            WHERE("a.last_arrive_send_time >= now()");
            WHERE("a.order_goods_status IN ( "+OrderGoodsStatusEnum.Status.PAYING.getCode()+","+OrderGoodsStatusEnum.Status.GO_TO_SEND.getCode()+")");
            WHERE("a.residue_transport_weight >=35");
            WHERE("b.id is not null");
        }}.toString();
        return sql;
    }




    public String exclusiveOrderPageGoodsList(@Param("page") Page<OrderGoodsAPPVO> page, @Param("param") List<String> param) {
        StringBuffer sqlList = new StringBuffer();
        sqlList.append("select a.id, a.order_no," +
                " a.order_goods_no, a.order_goods_status," +
                " a.extract_weight,a.residue_transport_weight," +
                "a.already_transport_weight, a.send_address_id, " +
                "a.send_address_shorter,a.send_longitude,a.send_latitude," +
                "a.receive_address_id, a.receive_address_shorter,a.receive_longitude," +
                "a.receive_latitude,a.goods_id, a.goods_name, a.pending_order_way," +
                "date_format(a.directional_expire_time, '%Y-%m-%d %H:%i:%s') as directional_expire_time," +
                "date_format(a.last_arrive_send_time, '%Y-%m-%d %H:%i:%s') as last_arrive_send_time," +
                "date_format(a.pending_order_time, '%Y-%m-%d %H:%i:%s') as pending_order_time," +
                "date_format(a.create_time, '%Y-%m-%d %H:%i:%s') as create_time," +
                " date_format(a.modified_time, '%Y-%m-%d %H:%i:%s') as modified_time," +
                "a.extract_way,a.need_truck_num,a.senior_logistics_manager_id,a.senior_logistics_manager_name," +
                "a.pending_order_freight,a.user_no,a.user_name from order_goods a " +
                "where a.stop_flag = "+ OrderGoodsStopEnum.Status.NO.getCode()+" and a.last_arrive_send_time >= now() and (a.order_goods_status = "+OrderGoodsStatusEnum.Status.PAYING.getCode()+
                " or a.order_goods_status = "+OrderGoodsStatusEnum.Status.GO_TO_SEND.getCode()+")" +
                "and a.residue_transport_weight > 0" +
                " and a.pending_order_way in ( "+ PendingOrderWayStatusEnum.Status.DX.getCode()+","+
                PendingOrderWayStatusEnum.Status.FLEET_DX.getCode()+") and a.order_goods_no in (");

        if (!param.isEmpty()) {
            for (int i = 0; i < param.size(); i++) {
                sqlList.append("'" + param.get(i) + "'");
                if (i < param.size() -1) {
                    sqlList.append(",");
                }
            }


        }
        sqlList.append(") order by a.pending_order_time desc");
        return sqlList.toString();
    }

    public String saveBatchEntity( List<OrderGoods> orderGoodsList) {
        StringBuffer sqlList = new StringBuffer();

        sqlList.append("INSERT INTO order_goods(extract_weight,residue_transport_weight,already_transport_weight,loss_price,truck_demand,extract_way," +
                "need_truck_num,vehicle_usage,pending_order_way,pending_order_time,pending_order_freight,last_arrive_send_time,senior_logistics_manager_id," +
                "senior_logistics_manager_name,order_no,order_goods_no,order_goods_status,send_address_id,send_address_shorter,send_system_address_id,send_longitude," +
                "send_latitude,receive_latitude,receive_longitude,receive_address_id,receive_address_shorter,receive_system_address_id,goods_name,goods_id,create_time,modified_time," +
                "user_name,user_no,batch_number,last_arrive_receive_time,last_load_time,last_order_time,settlement_account_period,settlement_way," +
                "platform_freight_quotation_tax_type, platform_service_fee_rate, deposit, invoice_service_fee_rate) VALUES ");

        for (int i = 0; i < orderGoodsList.size(); i++) {
            OrderGoods o = orderGoodsList.get(i);

            sqlList.append(" ('").append(o.getExtractWeight()).append("','").append(o.getResidueTransportWeight()).append("','").append(o.getAlreadyTransportWeight()).append("','")
                    .append(o.getLossPrice()).append("','").append(o.getTruckDemand()).append("','").append(o.getExtractWay()).append("','")
                    .append(o.getNeedTruckNum()).append("','").append(o.getVehicleUsage()).append("','").append(o.getPendingOrderWay()).append("','").append(o.getPendingOrderTime()).append("','")
                    .append(o.getPendingOrderFreight()).append("','").append(o.getLastArriveSendTime()).append("','").append(o.getSeniorLogisticsManagerId()).append("','")
                    .append(o.getSeniorLogisticsManagerName()).append("','").append(o.getOrderNo()).append("','").append(o.getOrderGoodsNo()).append("','")
                    .append(o.getOrderGoodsStatus()).append("','").append(o.getSendAddressId()).append("','").append(o.getSendAddressShorter()).append("',").append(o.getSendSystemAddressId()).append(",'")
                    .append(o.getSendLongitude()).append("','").append(o.getSendLatitude()).append("','").append(o.getReceiveLatitude()).append("','").append(o.getReceiveLongitude()).append("','")
                    .append(o.getReceiveAddressId()).append("','").append(o.getReceiveAddressShorter()).append("',").append(o.getReceiveSystemAddressId()).append(",'").append(o.getGoodsName()).append("','")
                    .append(o.getGoodsId()).append("','").append(o.getCreateTime()).append("','").append(o.getModifiedTime()).append("','").append(o.getUserName()).append("','")
                    .append(o.getUserNo()).append("','").append(o.getBatchNumber()).append("','").append(o.getLastArriveReceiveTime()).append("','")
                    .append(o.getLastLoadTime()).append("','").append(o.getLastOrderTime()).append("','").append(o.getSettlementAccountPeriod()).append("','").append(o.getSettlementWay()).append("','")
                    .append(o.getPlatformFreightQuotationTaxType()).append("',").append(o.getPlatformServiceFeeRate()).append(",").append(o.getDeposit()).append(",").append(o.getInvoiceServiceFeeRate())
                    .append(")");

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

    public String updateOrderGoodsSetAlreadyWeight(@Param(value = "alreadyWeight") BigDecimal alreadyWeight,@Param(value ="id") Integer id){
        String sql = new SQL() {{
            UPDATE("order_goods")
                    .SET("already_transport_weight = already_transport_weight - #{alreadyWeight}")
                    .WHERE("id = #{id}");

        }}.toString();
        return sql;
    }

    public String updateOrderGoodsSetResidueWeightAndAlreadyWeight(@Param(value ="residueWeight") BigDecimal residueWeight, @Param(value = "alreadyWeight") BigDecimal alreadyWeight,@Param(value ="id") Integer id){
        String sql = new SQL() {{
            UPDATE("order_goods")
                    .SET("residue_transport_weight = residue_transport_weight + #{residueWeight}")
                    .SET("already_transport_weight = already_transport_weight - #{alreadyWeight}")
                    .WHERE("id = #{id}");

        }}.toString();
        return sql;
    }

    public String findOrderGoodsById(@Param("id") Integer id) {
        String sql = new SQL() {{
            SELECT("a.id, a.order_no," +
                    " a.order_goods_no, a.order_goods_status, a.extract_weight, " +
                    " if(a.residue_transport_weight < 0, '0', a.residue_transport_weight) as residue_transport_weight ,a.already_transport_weight, a.send_address_id, a.send_address_shorter,a.send_longitude," +
                    " a.send_latitude,a.receive_address_id, a.receive_address_shorter,a.receive_longitude,a.receive_latitude," +
                    "a.goods_id, a.goods_name, a.pending_order_way," +
                    " date_format(a.directional_expire_time, '%Y-%m-%d %H:%i:%s') as directional_expire_time ," +
                    " date_format(a.last_arrive_send_time, '%Y-%m-%d %H:%i:%s') as last_arrive_send_time ," +
                    " date_format(a.pending_order_time, '%Y-%m-%d %H:%i:%s') as pending_order_time," +
                    " date_format(a.create_time, '%Y-%m-%d %H:%i:%s') as create_time," +
                    " date_format(a.modified_time, '%Y-%m-%d %H:%i:%s') as modified_time," +
                    "a.extract_way,a.need_truck_num,a.senior_logistics_manager_id,a.senior_logistics_manager_name,a.pending_order_freight," +
                    "a.user_no,a.user_name,a.batch_number,a.last_arrive_receive_time,a.last_load_time,a.last_order_time,a.vehicle_usage");
            FROM("order_goods a");

            if (Objects.nonNull(id)) {
                WHERE("a.id = #{id}");
            }

            ORDER_BY("a.create_time desc");
        }}.toString();
        return sql;
    }

    public String updateOrderGoods(@Param(value ="params") OrderGoodsEditParams params){
        String sql = new SQL() {{
            UPDATE("order_goods")
                    .SET("vehicle_usage = #{params.vehicleUsage}")
                    .SET("extract_weight = #{params.extractWeight}")
                    .SET("residue_transport_weight = #{params.residueTransportWeight}")
                    .SET("pending_order_way = #{params.pendingOrderWay}")
                    .SET("last_order_time = #{params.lastOrderTime}")
                    .SET("last_arrive_send_time = #{params.lastArriveSendTime}")
                    .SET("last_load_time = #{params.lastLoadTime}")
                    .SET("last_arrive_receive_time = #{params.lastArriveReceiveTime}")
                    .SET("pending_order_time = #{params.pendingOrderTime}")
                    .SET("extract_way = #{params.extractWay}")
                    .SET("need_truck_num = #{params.needTruckNum}")
                    .SET("pending_order_freight = #{params.pendingOrderFreight}")
                    .SET("user_no = #{params.userNo}")
                    .SET("user_name = #{params.userName}")
                    .WHERE("id = #{params.id}");

        }}.toString();
        return sql;
    }

    public String updateOrderGoodsSetResidueWeightAndAlreadyWeightAndStatus(@Param(value ="residueWeight") BigDecimal residueWeight, @Param(value = "alreadyWeight") BigDecimal alreadyWeight,@Param(value ="status") Integer status,@Param(value ="id") Integer id){
        String sql = new SQL() {{
            UPDATE("order_goods")
                    .SET("residue_transport_weight = residue_transport_weight + #{residueWeight}")
                    .SET("already_transport_weight = already_transport_weight - #{alreadyWeight}")
                    .SET("order_goods_status = #{status}")

                    .WHERE("id = #{id}");

        }}.toString();
        return sql;
    }

    public String getCancelOrderGoods(@Param("param") List<String> param) {
        StringBuffer sqlList = new StringBuffer();

        sqlList.append(" select a.id, a.order_no," +
                    " a.order_goods_no, a.order_goods_status, a.extract_weight, " +
                    " if(a.residue_transport_weight < 0, '0', a.residue_transport_weight) as residue_transport_weight ,a.already_transport_weight, a.send_address_id, a.send_address_shorter,a.send_longitude," +
                    " a.send_latitude,a.receive_address_id, a.receive_address_shorter,a.receive_longitude,a.receive_latitude," +
                    "a.goods_id, a.goods_name, a.pending_order_way," +
                    " date_format(a.directional_expire_time, '%Y-%m-%d %H:%i:%s') as directional_expire_time ," +
                    " date_format(a.last_arrive_send_time, '%Y-%m-%d %H:%i:%s') as last_arrive_send_time ," +
                    " date_format(a.pending_order_time, '%Y-%m-%d %H:%i:%s') as pending_order_time," +
                    " date_format(a.create_time, '%Y-%m-%d %H:%i:%s') as create_time," +
                    " date_format(a.modified_time, '%Y-%m-%d %H:%i:%s') as modified_time," +
                    "a.extract_way,a.need_truck_num,a.senior_logistics_manager_id,a.senior_logistics_manager_name,a.pending_order_freight," +
                    "a.user_no,a.user_name,a.stop_flag,a.batch_number,a.last_arrive_receive_time,a.last_load_time,a.last_order_time,a.vehicle_usage,ab.truck_no as truckNo"
                + " from order_goods a right join order_goods_truck_bind ab on ab.order_goods_no = a.order_goods_no where ab.status = '1' and ab.truck_no in (");

        if (!param.isEmpty()) {
            for (int i = 0; i < param.size(); i++) {
                sqlList.append("'" + param.get(i) + "'");
                if (i < param.size() -1) {
                    sqlList.append(",");
                }
            }
        }
        sqlList.append(") order by ab.create_time desc");
        return sqlList.toString();
    }


    public String getAlreadyTransportWeight(String orderNo){
        String sql = new SQL() {{
            SELECT("sum(already_transport_weight)");
            FROM("order_goods ");
            WHERE("order_no = #{orderNo}");
        }}.toString();
        return sql;
    }

    public String indexOrderGoodsListSearch(@Param("page") Page<OrderGoodsAPPVO> page, @Param("param") AppGoodsOrderSearchParam param) {
        String sql = new SQL() {{
            SELECT("a.id, a.order_no," +
                    " a.order_goods_no, a.order_goods_status, a.extract_weight, " +
                    " a.residue_transport_weight,a.already_transport_weight, a.send_address_id, a.send_address_shorter,a.send_longitude," +
                    " a.send_latitude,a.receive_address_id, a.receive_address_shorter,a.receive_longitude,a.receive_latitude," +
                    "a.goods_id, a.goods_name, a.pending_order_way," +
                    " date_format(a.directional_expire_time, '%Y-%m-%d %H:%i:%s') as directional_expire_time ," +
                    " date_format(a.last_arrive_send_time, '%Y-%m-%d %H:%i:%s') as last_arrive_send_time ," +
                    " date_format(a.pending_order_time, '%Y-%m-%d %H:%i:%s') as pending_order_time," +
                    " date_format(a.create_time, '%Y-%m-%d %H:%i:%s') as create_time," +
                    " date_format(a.modified_time, '%Y-%m-%d %H:%i:%s') as modified_time," +
                    "a.extract_way,a.need_truck_num,a.senior_logistics_manager_id,a.senior_logistics_manager_name,a.pending_order_freight," +
                    "a.user_no,a.user_name");
            FROM("order_goods a");
            WHERE("a.stop_flag = "+ OrderGoodsStopEnum.Status.NO.getCode());
            WHERE("a.last_arrive_send_time >= now()");
            WHERE("(a.order_goods_status = "+OrderGoodsStatusEnum.Status.PAYING.getCode()+" or a.order_goods_status = "+OrderGoodsStatusEnum.Status.GO_TO_SEND.getCode()+")");
            WHERE("a.residue_transport_weight >=35");
            if (ObjectUtil.isNotNull(param.getSearchType())) {
                if (1 == param.getSearchType()) {
                    WHERE("a.send_address_shorter like '%"+ param.getSearchName() +"%'");
                }else if (2 == param.getSearchType()) {
                    WHERE("a.receive_address_shorter like '%"+ param.getSearchName()+"%'");
                }
            }
            ORDER_BY("a.pending_order_time desc");
        }}.toString();
        return sql;
    }

}
