package cn.quantgroup.customer.service.impl;

import cn.quantgroup.customer.enums.KdspOrderStatusEnum;
import cn.quantgroup.customer.enums.ProblemTypeEnum;
import cn.quantgroup.customer.rest.param.mail.SendMailParam;
import cn.quantgroup.customer.rest.param.thirdworkorder.WorkOrderParam;
import cn.quantgroup.customer.rest.vo.JsonResult;
import cn.quantgroup.customer.rest.vo.thirdworkorder.ThirdWorkOrderExportVo;
import cn.quantgroup.customer.rest.vo.thirdworkorder.ThirdWorkOrderVo;
import cn.quantgroup.customer.service.IKdspService;
import cn.quantgroup.customer.service.IThirdWorkOrderService;
import cn.quantgroup.customer.util.DateUtil;
import cn.quantgroup.customer.util.EasyExcelUtil;
import cn.quantgroup.customer.util.MailUtil;
import cn.quantgroup.customer.util.MaskingUtil;
import cn.quantgroup.third.customer.entity.LhpdsSysUser;
import cn.quantgroup.third.customer.entity.WorkflowProcessInstanceTaskAssignee;
import cn.quantgroup.third.customer.repo.LhpdsCustomer2Repo;
import cn.quantgroup.third.customer.repo.LhpdsSysUserRepo;
import cn.quantgroup.third.customer.repo.WorkflowProcessInstanceTaskAssigneeRepo;
import com.alibaba.fastjson.JSON;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.InputStreamResource;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;

/**
 * @description:
 * @author:tao
 * @create: 2020-08-14 15:24
 */
@Slf4j
@Service
public class ThirdWorkOrderServiceImpl implements IThirdWorkOrderService {

    @Autowired
    private LhpdsCustomer2Repo customer2Repo;
    @Autowired
    private LhpdsSysUserRepo lhpdsSysUserRepo;
    @Autowired
    private WorkflowProcessInstanceTaskAssigneeRepo taskAssigneeRepo;
    @Autowired
    @Qualifier("entityManagerSecondary")
    private EntityManager entityManager;

    @Autowired
    private IKdspService iKdspService;

    @Value("${customer.transaction.mail.from}")
    private String from;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    ExecutorService executorService = Executors.newFixedThreadPool(2);

    private static int pageSize = 500;
    @Override
    public JsonResult<Map<String, Object>> queryWorkOrder(WorkOrderParam param) {
        long offset = (param.getPageNo() - 1) * param.getPageSize();

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT "
            + "lc.id,lc.create_user createUser,assignee.assignee acceptUser ,lc.field10 orderId,lc.field17 problemDesc,"
            + "lwpi.process_instance_id processInstanceId,lwpi.priority priority,lwpi.global_state status,lwpi.process_definition_id processDefinitionId,lwpi.contact realContact,"
            + "lc.create_time "
            + "FROM lhpds_customer_2 lc  left join lhpds_workflow_process_instance lwpi on lc .id =lwpi.data_id "
            + "left join lhpds_workflow_process_instance_task_assignee assignee "
            + "on lwpi.process_instance_id = assignee.process_instance_id and assignee.task_name ='处理工单' and assignee.state =0 ");
        StringBuffer countSql = new StringBuffer();
        countSql.append("SELECT count(1)"
            + "FROM lhpds_customer_2 lc  left join lhpds_workflow_process_instance lwpi on lc .id =lwpi.data_id "
            + "left join lhpds_workflow_process_instance_task_assignee assignee "
            + "on lwpi.process_instance_id = assignee.process_instance_id and assignee.task_name ='处理工单' and assignee.state =0 ");

        StringBuffer conditionSql = new StringBuffer();
        if (param.getId() != null) {
            conditionSql.append("and lc.id=" + param.getId() + " ");
        }
        if (StringUtils.isNotBlank(param.getOrderId())) {
            conditionSql.append("and lc.field10='" + param.getOrderId() + "' ");
        }
        if (StringUtils.isNotBlank(param.getProcessInstanceId())) {
            conditionSql.append(
                "and lwpi.process_instance_id='" + param.getProcessInstanceId() + "' ");
        }
        if (StringUtils.isNotBlank(param.getPriority())) {
            conditionSql.append("and lwpi.priority='" + param.getPriority() + "' ");
        }
        if (CollectionUtils.isNotEmpty(param.getStatusList())) {
            StringBuffer statusSb = new StringBuffer();
            for (String s : param.getStatusList()) {
                statusSb.append(",'" + s + "'");
            }
            conditionSql.append("and lwpi.global_state in (" + statusSb.substring(1) + ") ");
        }
        if (StringUtils.isNotBlank(param.getCreateUser())) {
            conditionSql.append("and lc.create_user='" + param.getCreateUser() + "' ");
        }
        if (StringUtils.isNotBlank(param.getAcceptUser())) {
            conditionSql.append("and assignee.assignee='" + param.getAcceptUser() + "' ");
        }
        if (StringUtils.isNotBlank(param.getStartTime()) &&StringUtils.isNotBlank(param.getEndTime())  ) {
            conditionSql.append("and lc.create_time >='" + param.getStartTime() + "' and lc.create_time <= '"+param.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(conditionSql.toString())) {
            sql.append(" where " + conditionSql.toString().substring(3));
            countSql.append(" where " + conditionSql.toString().substring(3));
        }
        sql.append(" order by lc.id desc");
        sql.append(" limit " + offset + "," + param.getPageSize());
        Query nativeQuery = entityManager.createNativeQuery(sql.toString());
        List<Object[]> result = nativeQuery.getResultList();
        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        Object count = countQuery.getSingleResult();

        Set<String> userSet = new HashSet<>();
        Set<String> processInstanceIdSet = new HashSet<>();
        for (Object[] objects : result) {
            if (objects[1] != null) {
                userSet.add(objects[1].toString());
            }
            if (objects[2] != null) {
                userSet.add(objects[2].toString());
            }
            if (objects[5] != null) {
                processInstanceIdSet.add(objects[5].toString());
            }
        }

        //查询用户名称
        List<LhpdsSysUser> userList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(userSet)) {
            userList = lhpdsSysUserRepo.getUserByAccountIdList(new ArrayList<>(userSet));
        }
        Map<String, String> userNameMap = userList.stream().collect(
            Collectors.toMap(LhpdsSysUser::getAccountId, LhpdsSysUser::getNickname,
                (key1, key2) -> key2));

        //查询任务id
        List<WorkflowProcessInstanceTaskAssignee> taskAssigneeList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(processInstanceIdSet)) {
            taskAssigneeList = taskAssigneeRepo.getByInstanceIds(
                new ArrayList<>(processInstanceIdSet));
        }
        Map<String, String> taskAssigneeMap = taskAssigneeList.stream().collect(
            Collectors.toMap(WorkflowProcessInstanceTaskAssignee::getProcessInstanceId,
                WorkflowProcessInstanceTaskAssignee::getTaskId, (key1, key2) -> key2));

        List<ThirdWorkOrderVo> resultList = new ArrayList<>();
        for (Object[] objects : result) {
            ThirdWorkOrderVo vo = new ThirdWorkOrderVo();
            vo.setId(Long.parseLong(objects[0].toString()));
            if (objects[1] != null) {
                String name = userNameMap.get(objects[1].toString());
                name = StringUtils.isBlank(name) ? objects[1].toString()
                    : objects[1].toString() + "(" + userNameMap.get(objects[1].toString()) + ")";
                vo.setCreateUser(name);
            }
            if (objects[2] != null) {
                String name = userNameMap.get(objects[2].toString());
                name = StringUtils.isBlank(name) ? objects[2].toString()
                    : objects[2].toString() + "(" + userNameMap.get(objects[2].toString()) + ")";
                vo.setAcceptUser(name);
            }
            if (objects[3] != null) {
                vo.setOrderId(objects[3].toString());
            }
            if (objects[4] != null) {
                vo.setProblemDesc(objects[4].toString());
            }
            if (objects[5] != null) {
                vo.setProcessInstanceId(objects[5].toString());
            }
            if (objects[6] != null) {
                vo.setPriority(objects[6].toString());
            }
            if (objects[7] != null) {
                vo.setStatus(objects[7].toString());
            }
            if (objects[8] != null) {
                vo.setProcessDefinitionId(objects[8].toString());
            }
            if (objects[9] != null) {
                vo.setRealContact(MaskingUtil.maskPhoneNumber(objects[9].toString()));
            }
            if (objects[10] != null) {
                vo.setCreateTime(objects[10].toString());
            }
            vo.setTaskId(taskAssigneeMap.get(vo.getProcessInstanceId()));
            resultList.add(vo);
        }

        Map<String, Object> map = new HashMap<>();
        map.put("total", Integer.parseInt(count.toString()));
        map.put("records", resultList);
        return JsonResult.buildSuccessResult("查询成功", map);
    }

    @Override
    public JsonResult export(WorkOrderParam param,String account,Integer tenantId) {
        if (StringUtils.isBlank(account)){
            return JsonResult.buildErrorStateResult("账号名为空");
        }
        String key = "export_"+account ;
        String value = (String)redisTemplate.opsForValue().get(key);
        if (StringUtils.isNotBlank(value)){
            return JsonResult.buildSuccessResult("系统导出处理中,请勿重复下载","");
        }
        redisTemplate.opsForValue().set(key,"lock",1, TimeUnit.MINUTES);
        executorService.execute(()->{
            doExportBusiness(param,account,tenantId);
        });
        return JsonResult.buildSuccessResult("系统处理中,导出成功后将发送到您企业邮箱中","");
    }


    private void doExportBusiness(WorkOrderParam param,String account,Integer tenantId) {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT lc.create_time,lc.id,lc.field14,lc.field51,lc.create_user createUser,assignee.assignee acceptUser,"
                + "lc.field10 orderId,lc.field17 problemDesc,lwpi.process_instance_id processInstanceId,"
                + "lwpi.global_state status,lwpi.close_time "
                + "FROM lhpds_customer_2 lc  left join lhpds_workflow_process_instance lwpi on lc .id =lwpi.data_id "
                + "left join lhpds_workflow_process_instance_task_assignee assignee "
                + "on lwpi.process_instance_id = assignee.process_instance_id and assignee.task_name ='处理工单' and assignee.state =0 " );
            StringBuffer countSql = new StringBuffer();
            countSql.append("SELECT count(1)"
                + "FROM lhpds_customer_2 lc  left join lhpds_workflow_process_instance lwpi on lc .id =lwpi.data_id "
                + "left join lhpds_workflow_process_instance_task_assignee assignee "
                + "on lwpi.process_instance_id = assignee.process_instance_id and assignee.task_name ='处理工单' and assignee.state =0 " );

            StringBuffer conditionSql = new StringBuffer();
            if (param.getId()!=null){
                conditionSql.append("and lc.id="+param.getId()+" ");
            }
            if (StringUtils.isNotBlank(param.getOrderId())){
                conditionSql.append("and lc.field10='"+param.getOrderId()+"' ");
            }
            if (StringUtils.isNotBlank(param.getProcessInstanceId())){
                conditionSql.append("and lwpi.process_instance_id='"+param.getProcessInstanceId()+"' ");
            }
            if (StringUtils.isNotBlank(param.getPriority())){
                conditionSql.append("and lwpi.priority='"+param.getPriority()+"' ");
            }
            if (CollectionUtils.isNotEmpty(param.getStatusList())){
                StringBuffer statusSb  = new StringBuffer();
                for (String s : param.getStatusList()){
                    statusSb.append(",'"+s+"'");
                }
                conditionSql.append("and lwpi.global_state in ("+statusSb.substring(1)+") ");
            }
            if (StringUtils.isNotBlank(param.getCreateUser())){
                conditionSql.append("and lc.create_user='"+param.getCreateUser()+"' ");
            }
            if (StringUtils.isNotBlank(param.getAcceptUser())){
                conditionSql.append("and assignee.assignee='"+param.getAcceptUser()+"' ");
            }
            if (StringUtils.isNotBlank(param.getStartTime()) &&StringUtils.isNotBlank(param.getEndTime())  ) {
                conditionSql.append("and lc.create_time >='" + param.getStartTime() + "' and lc.create_time <= '"+param.getEndTime()+"'");
            }
            if (StringUtils.isNotBlank(conditionSql.toString())){
                sql.append(" where "+conditionSql.toString().substring(3));
                countSql.append(" where "+conditionSql.toString().substring(3));
            }
            sql.append(" order by lc.id desc");

            Query countQuery = entityManager.createNativeQuery(countSql.toString());
            Object countResult = countQuery.getSingleResult();
            int count = Integer.parseInt(countResult.toString());
            if (count == 0){
                return;
            }
            int pageNo = (count + pageSize - 1) / pageSize;

            List<ThirdWorkOrderExportVo> exportVos = new ArrayList<>();

            List<ThirdWorkOrderExportVo> dbAllData = new ArrayList<>();

            List<ThirdWorkOrderExportVo> kdspResult = new ArrayList<>();
            Set<String> orderNos = new HashSet<>();
            for (int i = 1;i <= pageNo;i++){
                long offset = (i - 1) * pageSize;
                String tempSql = sql.toString() + " limit "+offset+","+pageSize;
                Query nativeQuery = entityManager.createNativeQuery(tempSql);
                List<Object[]> result = nativeQuery.getResultList();
                for (Object[] objects : result) {
                    ThirdWorkOrderExportVo exportVo = new ThirdWorkOrderExportVo();
                    if (objects[0] != null) {
                        exportVo.setCreateTime(objects[0].toString());
                    }
                    if (objects[1] != null) {
                        exportVo.setId(objects[1].toString());
                    }
                    if (objects[2] != null) {
                        exportVo.setFeedbackChannel(objects[2].toString());
                    }
                    if (objects[3] != null) {
                        Integer questionLevel = null;
                        try {
                            questionLevel = Integer.parseInt(objects[3].toString());
                        }catch (Exception ex){}
                        ProblemTypeEnum problemTypeEnum = ProblemTypeEnum.getByCode(questionLevel);
                        if (problemTypeEnum != null){
                            exportVo.setQuestionLevel(problemTypeEnum.getDesc());
                        }else {
                            exportVo.setQuestionLevel(objects[3].toString());
                        }
                    }
                    if (objects[4] != null) {
                        exportVo.setCreateUser(objects[4].toString());
                    }
                    if (objects[5] != null) {
                        exportVo.setAcceptUser(objects[5].toString());
                    }
                    if (objects[6] != null) {
                        exportVo.setOrderNo(objects[6].toString().trim());
                        orderNos.add(exportVo.getOrderNo());
                    }
                    if (objects[7] != null) {
                        exportVo.setProblemDesc(objects[7].toString());
                    }
                    if (objects[8] != null) {
                        exportVo.setProcessInstanceId(objects[8].toString());
                    }
                    if (objects[9] != null) {
                        exportVo.setStatus(objects[9].toString());
                    }
                    if (objects[10] != null) {
                        exportVo.setCloseTime(objects[10].toString());
                    }
                    dbAllData.add(exportVo);
                }

            }

            int orderPageNo = (orderNos.size() + pageSize - 1) / pageSize;
            List<String> orderNoList = new ArrayList<>(orderNos);
            for (int i =0;i < orderPageNo;i++){
                List<String> subList = null;
                if (i != orderPageNo-1){
                    System.out.println(i*pageSize +"||"+ (i+1)*pageSize);
                    subList = orderNoList.subList(i*pageSize, (i+1)*pageSize);
                }else {
                    System.out.println(i*pageSize +"||"+ orderNos.size());
                    subList = orderNoList.subList(i*pageSize, orderNos.size());
                }
                JsonResult<List<ThirdWorkOrderExportVo>> orderResult = iKdspService.getOrderDetailForWorkOder(subList,tenantId);
                if (!orderResult.isSuccess()){
                    log.error("工单导出查询订单信息失败:{}",orderResult.getMsg());
                    return;
                }
                List<ThirdWorkOrderExportVo> data = orderResult.getData();
                if (CollectionUtils.isNotEmpty(data)){
                    kdspResult.addAll(data);
                }
            }

            Map<String, List<ThirdWorkOrderExportVo>> groupBy = kdspResult.stream().collect(Collectors.groupingBy(ThirdWorkOrderExportVo::getOrderNo));
            for (ThirdWorkOrderExportVo dbData : dbAllData){
                List<ThirdWorkOrderExportVo> kdspOrders = groupBy.get(dbData.getOrderNo());
                if (CollectionUtils.isEmpty(kdspOrders)){
                    exportVos.add(dbData);
                    continue;
                }
                for (ThirdWorkOrderExportVo kdspOrder : kdspOrders){
                    KdspOrderStatusEnum orderStatusEnum = KdspOrderStatusEnum.getByCode(kdspOrder.getOrderStatus());
                    if (orderStatusEnum != null){
                        kdspOrder.setOrderStatusText(orderStatusEnum.getDesc());
                    }else {
                        kdspOrder.setOrderStatusText(kdspOrder.getOrderStatus()+"");
                    }
                    kdspOrder.setId(dbData.getId());
                    kdspOrder.setProcessInstanceId(dbData.getProcessInstanceId());
                    kdspOrder.setCreateUser(dbData.getCreateUser());
                    kdspOrder.setAcceptUser(dbData.getAcceptUser());
                    kdspOrder.setStatus(dbData.getStatus());
                    kdspOrder.setProblemDesc(dbData.getProblemDesc());
                    kdspOrder.setCreateTime(dbData.getCreateTime());
                    kdspOrder.setFeedbackChannel(dbData.getFeedbackChannel());
                    kdspOrder.setQuestionLevel(dbData.getQuestionLevel());
                    kdspOrder.setCloseTime(dbData.getCloseTime());
                    exportVos.add(kdspOrder);
                }
            }

            String date = DateUtil.format(new Date(), DateUtil.YYYYMMDDHHMMSS);
            String path = System.getProperty("java.io.tmpdir") +File.separator + "工单导出"+DateUtil.format(new Date(),DateUtil.YYYYMMDDHHMMSS) +".xlsx";
            log.info("下载文件路径:{}",path);
            File file = new File(path);
            EasyExcelUtil.write(path,ThirdWorkOrderExportVo.class,exportVos);
            SendMailParam mailParam = new SendMailParam();
            mailParam.setSubject("工单导出"+date);
            mailParam.setReceiver(new String[]{account+"@quantgroup.com"});
            mailParam.setSender(from);
            mailParam.setContentText("工单导出");
            mailParam.setAttachmentFileName(file.getName());
            InputStreamResource resource = new InputStreamResource(new FileInputStream(file));
            mailParam.setInputStreamSource(resource);
            MailUtil.sendHtmlAndAttachmentMail(mailParam);
            if (file.exists()){
                file.delete();
            }
        }catch (Exception ex){
            log.error("导出excel异常",ex);
        }

    }
}
