/*
 * Copyright 2021 Tianmian Tech. All Rights Reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.welab.wefe.board.service.service;

import com.welab.wefe.board.service.api.project.dataset.AddDataSetApi;
import com.welab.wefe.board.service.api.project.dataset.RemoveDataSetApi;
import com.welab.wefe.board.service.api.project.member.ExitProjectApi;
import com.welab.wefe.board.service.api.project.member.ListInProjectApi;
import com.welab.wefe.board.service.api.project.member.RemoveApi;
import com.welab.wefe.board.service.api.project.project.*;
import com.welab.wefe.board.service.database.entity.job.*;
import com.welab.wefe.board.service.database.repository.*;
import com.welab.wefe.board.service.dto.base.PagingOutput;
import com.welab.wefe.board.service.dto.entity.ProjectDataSetInput;
import com.welab.wefe.board.service.dto.entity.ProjectMemberInput;
import com.welab.wefe.board.service.dto.entity.project.ProjectDetailMemberOutputModel;
import com.welab.wefe.board.service.dto.entity.project.ProjectMemberOutputModel;
import com.welab.wefe.board.service.dto.entity.project.ProjectOutputModel;
import com.welab.wefe.board.service.dto.entity.project.ProjectQueryOutputModel;
import com.welab.wefe.board.service.dto.entity.project.data_set.ProjectDataResourceOutputModel;
import com.welab.wefe.board.service.dto.vo.AuditStatusCounts;
import com.welab.wefe.board.service.dto.vo.ProjectFlowStatisticsResult;
import com.welab.wefe.board.service.dto.vo.RoleCounts;
import com.welab.wefe.board.service.onlinedemo.OnlineDemoBranchStrategy;
import com.welab.wefe.board.service.service.account.AccountService;
import com.welab.wefe.board.service.service.data_resource.DataResourceService;
import com.welab.wefe.common.Convert;
import com.welab.wefe.common.StatusCode;
import com.welab.wefe.common.data.mysql.Where;
import com.welab.wefe.common.exception.StatusCodeWithException;
import com.welab.wefe.common.util.StringUtil;
import com.welab.wefe.common.util.ThreadUtil;
import com.welab.wefe.common.web.dto.AbstractApiInput;
import com.welab.wefe.common.web.util.CurrentAccountUtil;
import com.welab.wefe.common.web.util.ModelMapper;
import com.welab.wefe.common.wefe.enums.AuditStatus;
import com.welab.wefe.common.wefe.enums.FederatedLearningType;
import com.welab.wefe.common.wefe.enums.JobMemberRole;
import com.welab.wefe.common.wefe.enums.ProjectFlowStatus;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author zane.luo
 */
@Service
public class ProjectService extends AbstractService {

    @Autowired
    private ProjectMemberService projectMemberService;

    @Autowired
    private ProjectDataSetService projectDataSetService;

    @Autowired
    private ProjectFlowService projectFlowService;

    @Autowired
    private ProjectFlowNodeService projectFlowNodeService;

    @Autowired
    private JobService jobService;

    @Autowired
    private ProjectService projectService;

    @Autowired
    private ProjectRepository projectRepo;

    @Autowired
    private ProjectMemberRepository projectMemberRepo;

    @Autowired
    private ProjectDataSetRepository projectDataSetRepo;
    @Autowired
    private ProjectMemberAuditRepository projectMemberAuditRepository;
    @Autowired
    ProjectMemberAuditService projectMemberAuditService;

    @Autowired
    private ProjectFlowRepository projectFlowRepository;

    @Autowired
    private ProjectFlowNodeRepository projectFlowNodeRepository;
    @Autowired
    private DataResourceService dataResourceService;
    @Autowired
    private MessageService messageService;

    /**
     * New Project
     * <p>
     * promoter
     * project: agree
     * project_member: agree
     * <p>
     * provider
     * project: auditing
     * project_member: auditing
     */
    @Transactional(rollbackFor = Exception.class)
    public synchronized String addProject(AddApi.Input input) throws StatusCodeWithException {

        if (!input.fromGateway()) {
            // create projectId
            input.setProjectId(UUID.randomUUID().toString().replaceAll("-", ""));

            ProjectMemberInput promoter = new ProjectMemberInput();
            promoter.setMemberRole(JobMemberRole.promoter);
            promoter.setMemberId(CacheObjects.getMemberId());
            promoter.setDataSetList(input.getPromoterDataSetList());

            input.getProviderList().forEach(x -> x.setMemberRole(JobMemberRole.provider));
            List<ProjectMemberInput> members = new ArrayList<>();
            input.getProviderList().forEach(x -> members.add(x));
            input.setMembers(members);
            if (input.getPromoterList() != null && CollectionUtils.isNotEmpty(input.getPromoterList())) {
                input.getPromoterList().forEach(x -> x.setMemberRole(JobMemberRole.promoter));
                input.getMembers().addAll(0, input.getPromoterList().stream()
                        .filter(s -> !s.getMemberId().equals(promoter.getMemberId())).collect(Collectors.toList()));
            }
            input.getMembers().add(0, promoter);
        }


        ProjectMySqlModel project = new ProjectMySqlModel();
        project.setCreatedBy(input);
        project.setMemberId(input.fromGateway() ? input.callerMemberInfo.getMemberId() : CacheObjects.getMemberId());
        project.setMyRole(input.fromGateway() ? input.getRole() : JobMemberRole.promoter);
        project.setProjectId(input.getProjectId());
        project.setName(input.getName());
        project.setProjectDesc(input.getDesc());
        project.setAuditStatus(input.fromGateway() ? AuditStatus.auditing : AuditStatus.agree);
        project.setStatusUpdatedTime(new Date());
        project.setProgress(0);
        project.setProgressUpdatedTime(new Date());
        project.setUpdatedBy(input);
        project.setAuditStatusFromMyself(input.fromGateway() ? AuditStatus.auditing : AuditStatus.agree);
        project.setFlowStatusStatistics(new ProjectFlowStatisticsResult().toJsonString());
        project.setProjectType(input.getProjectType());
        projectRepo.save(project);

        if (input.fromGateway()) {
            messageService.addApplyJoinProjectMessage(
                    input.callerMemberInfo.getMemberId(),
                    project.getProjectId(),
                    project.getName()
            );
        }

        // create and save ProjectMember to database
        for (ProjectMemberInput item : input.getMembers()) {
            ProjectMemberMySqlModel member = new ProjectMemberMySqlModel();
            member.setProjectId(project.getProjectId());
            member.setCreatedBy(input);
            member.setMemberId(item.getMemberId());
            member.setMemberRole(item.getMemberRole());

            // I directly agree to a project initiated by myself
            AuditStatus auditStatus = item.getMemberId().equals(project.getMemberId()) ? AuditStatus.agree : AuditStatus.auditing;
            member.setAuditStatus(auditStatus);
            member.setAuditStatusFromMyself(auditStatus);
            member.setAuditComment(item.getMemberId().equals(project.getMemberId()) ? "项目创建者" : null);
            member.setInviterId(item.getMemberId().equals(project.getMemberId()) ? null
                    : input.fromGateway() ? input.callerMemberInfo.getMemberId() : CacheObjects.getMemberId());
            member.setFromCreateProject(true);
            projectMemberRepo.save(member);

            // The pause of 2ms here is to generate the createdTime of each member in order,
            // which is convenient for displaying in order on the interface.
            ThreadUtil.sleep(2);


            // save ProjectDataSet to database
            for (ProjectDataSetInput dataSetInput : item.getDataSetList()) {
                ProjectDataSetMySqlModel dataSet = new ProjectDataSetMySqlModel();
                dataSet.setProjectId(project.getProjectId());
                dataSet.setCreatedBy(input);
                dataSet.setMemberId(dataSetInput.getMemberId());
                dataSet.setMemberRole(dataSetInput.getMemberRole());
                dataSet.setDataSetId(dataSetInput.getDataSetId());
                dataSet.setStatusUpdatedTime(new Date());
                dataSet.setAuditStatus(auditStatus);
                dataSet.setSourceType(null);
                dataSet.setDataResourceType(dataSetInput.getDataResourceType());

                projectDataSetRepo.save(dataSet);

                // Update the usage count of the dataset in the project
                if (auditStatus == AuditStatus.agree && CacheObjects.isCurrentMember(dataSetInput.getMemberId())) {
                    dataResourceService.updateUsageCountInProject(dataSet.getDataSetId());
                }

                // 如果申请使用我方数据资源，添加一条消息予以提醒。
                if (input.fromGateway() && CacheObjects.isCurrentMember(dataSetInput.getMemberId())) {
                    messageService.addApplyDataResourceMessage(
                            input.callerMemberInfo.getMemberId(),
                            project,
                            dataSet
                    );
                }
            }

        }

        gatewayService.syncToNotExistedMembers(project.getProjectId(), input, AddApi.class);

        return project.getProjectId();

    }


    public DataInfoApi.Output getDataInfo(DataInfoApi.Input input) throws StatusCodeWithException {

        DataInfoApi.Output output = new DataInfoApi.Output();

        ProjectMySqlModel project = projectRepo.findOne("projectId", input.getProjectId(), ProjectMySqlModel.class);

        if (project == null) {
            throw new StatusCodeWithException(StatusCode.DATA_NOT_FOUND, "未找到相应的项目！");
        }

        List<ProjectMemberMySqlModel> projectMembers = projectMemberService.findListByProjectId(input.getProjectId());

        List<ProjectDataSetMySqlModel> projectDataSets = new ArrayList<>();
        for (ProjectMemberMySqlModel projectMember : projectMembers) {
            if (projectMember.getMemberRole() == JobMemberRole.provider) {
                ProjectMemberMySqlModel sameData = projectMembers
                        .stream()
                        .filter(x -> x.getMemberId().equals(projectMember.getMemberId()) && x.getMemberRole() == JobMemberRole.promoter)
                        .findFirst().orElse(null);
                if (sameData != null) {
                    continue;
                }
            }
            List<ProjectDataSetMySqlModel> projectMemberDataSets = projectDataSetService
                    .findDataSetList(input.getProjectId(), projectMember.getMemberId(), projectMember.getMemberRole());
            projectDataSets.addAll(projectMemberDataSets);
        }

        List<ProjectFlowMySqlModel> projectFlows = projectFlowService.findFlowsByProjectId(input.getProjectId());


        List<ProjectFlowNodeMySqlModel> projectFlowNodes = new ArrayList<>();
        for (ProjectFlowMySqlModel projectFlow : projectFlows) {

            List<ProjectFlowNodeMySqlModel> projectFlowNodeList = projectFlowNodeService.findNodesByFlowId(projectFlow.getFlowId());
            projectFlowNodes.addAll(projectFlowNodeList);
        }

        output.setProject(project);
        output.setProjectMembers(projectMembers);
        output.setProjectDataSets(projectDataSets);
        output.setProjectFlows(projectFlows);
        output.setProjectFlowNodes(projectFlowNodes);

        return output;

    }

    public ProjectOutputModel detail(String projectId) throws StatusCodeWithException {
        ProjectMySqlModel project = projectRepo.findOne("projectId", projectId, ProjectMySqlModel.class);
        if (project == null) {
            throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "未找到相应的项目！");
        }


        List<ProjectDetailMemberOutputModel> allMemberList = projectMemberService
                .findListByProjectId(projectId)
                .parallelStream()
                .map(x -> ModelMapper.map(x, ProjectDetailMemberOutputModel.class))
                .collect(Collectors.toList());

        List<ProjectDataResourceOutputModel> allDataSetList = projectDataSetService.listRawDataSet(projectId, null, null, null, null);


        // Populate the member's data set list
        allMemberList.forEach(member ->
                member.setDataResourceList(
                        allDataSetList
                                .stream()
                                .filter(dataSet ->
                                        dataSet != null
                                                && dataSet.getMemberId().equals(member.getMemberId())
                                                && dataSet.getMemberRole() == member.getMemberRole()
                                )
                                .collect(Collectors.toList())
                )
        );
        List<ProjectDetailMemberOutputModel> promoters = allMemberList.stream()
                .filter(x -> x.getMemberRole() == JobMemberRole.promoter).collect(Collectors.toList());
        ProjectDetailMemberOutputModel promoter = null;
        if (promoters.size() == 1) {
            promoter = allMemberList.stream().filter(x -> x.getMemberRole() == JobMemberRole.promoter).findFirst()
                    .orElse(null);
            promoters = null;
        } else if (promoters.size() > 1) {
            promoter = allMemberList.stream()
                    .filter(x -> x.getMemberRole() == JobMemberRole.promoter && StringUtils.isBlank(x.getInviterId()))
                    .findFirst().orElse(null);
            String creator = promoter.getMemberId();
            promoters = promoters.stream().filter(p -> !p.getMemberId().equals(creator)).collect(Collectors.toList());
        }

        List<ProjectDetailMemberOutputModel> providers = allMemberList.stream()
                .filter(x -> x.getMemberRole() == JobMemberRole.provider).collect(Collectors.toList());

        ProjectOutputModel output = ModelMapper.map(project, ProjectOutputModel.class);
//        ProjectDetailMemberOutputModel newPromoter = JSONObject.parseObject(JSONObject.toJSONString(promoter),
//                ProjectDetailMemberOutputModel.class);
        output.setPromoter(promoter);
        output.setProviderList(providers);
        output.setPromoterList(promoters);
        output.setIsCreator(
                promoter != null ? promoter.getMemberId().equalsIgnoreCase(CacheObjects.getMemberId()) : null);
        output.setIsExited(!(allMemberList.stream()
                .anyMatch(s -> s.getMemberId().equals(CacheObjects.getMemberId()) && !s.isExited())));
        return output;
    }


    /**
     * remove member
     */
    @Transactional(rollbackFor = Exception.class)
    public synchronized void removeMember(RemoveApi.Input input) throws StatusCodeWithException {
        ProjectMySqlModel project = findByProjectId(input.getProjectId());
        if (project == null) {
            throw new StatusCodeWithException(StatusCode.PARAMETER_VALUE_INVALID, "未找到相应的项目！");
        }
        if (project.getMyRole() != JobMemberRole.promoter && !input.fromGateway()) {
            throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "移除成员的操作只有 promoter 能发起");
        }
        ProjectMemberMySqlModel member = projectMemberService.findOneByMemberId(input.getProjectId(), input.getMemberId(), input.getMemberRole());
        if (member == null) {
            return;
        }

        OnlineDemoBranchStrategy.hackOnDelete(input, member, "只能删除自己加的成员。");

        // Mark all data sets belonging to the member as unavailable
        projectDataSetRepo.disableDataSetWhenMemberExist(input.getProjectId(), input.getMemberId(), input.getMemberRole().name());

        // Update the usage count of the dataset in the project
        projectDataSetService
                .listAllRawDataSet(project.getProjectId(), member.getMemberId())
                .stream()
                .forEach(x -> dataResourceService.updateUsageCountInProject(x.getDataSetId()));

        checkAuditingRecord(input.getProjectId(), input.getMemberId());

        gatewayService.syncToNotExistedMembers(input.getProjectId(), input, RemoveApi.class);

        projectMemberService.updateProjectMember(member, (x) -> {
            member.setExited(true);
            member.setFromCreateProject(false);
            member.setAuditComment("该成员已被 promoter 移除");
            return x;
        });

        // If you remove myself
        if (input.fromGateway() && input.getMemberId().equals(CacheObjects.getMemberId())) {
            project.setExited(true);
            project.setExitedBy(input.callerMemberInfo.getMemberId());
            project.setExitedTime(new Date());
            projectRepo.save(project);
        }

    }

    /**
     * Process and delete the audit information of a member
     * todo: winter
     */
    private void checkAuditingRecord(String projectId, String memberId) {
        // Find all audit information for this project
        Specification<ProjectMemberAuditMySqlModel> where = Where.create().equal("projectId", projectId)
                .build(ProjectMemberAuditMySqlModel.class);
        List<ProjectMemberAuditMySqlModel> allAuditList = projectMemberAuditRepository.findAll(where);
        if (CollectionUtils.isNotEmpty(allAuditList)) {

            // Auditee, audit list
            Map<String, Boolean> auditMap = new HashMap<>();
            for (ProjectMemberAuditMySqlModel audit : allAuditList) {
                // Information that needs to be reviewed by A
                if (audit.getAuditorId().equals(memberId) && audit.getAuditResult() == AuditStatus.auditing) {
                    // A was removed and temporarily set to agree
                    audit.setAuditComment("成员已被移除，自动同意");
                    audit.setAuditResult(AuditStatus.agree);
                }

                Boolean agree = auditMap.get(audit.getMemberId());
                if (agree == null) {
                    agree = audit.getAuditResult() == AuditStatus.agree;
                } else {
                    agree = audit.getAuditResult() == AuditStatus.agree && agree;
                }
                auditMap.put(audit.getMemberId(), agree);
            }

            for (Map.Entry<String, Boolean> auditEntry : auditMap.entrySet()) {
                // Person being audited
                String mId = auditEntry.getKey();
                boolean agree = auditEntry.getValue();
                if (!agree) {
                    continue;
                }
                // Person being audited
                List<ProjectMemberMySqlModel> needAuditMembers = projectMemberService.findListByMemberId(projectId,
                        mId);
                ProjectMemberMySqlModel needAuditMember = needAuditMembers.stream()
                        .filter(s -> s.getAuditStatus() == AuditStatus.auditing && !s.isExited()).findFirst().orElse(null);
                if (needAuditMember != null) {
                    projectMemberService.updateProjectMember(needAuditMember, (x) -> {
                        x.setAuditStatus(AuditStatus.agree);
                        x.setAuditStatusFromOthers(AuditStatus.agree);
                        x.setAuditComment("有成员被移除，自动审核通过");
                        x.setExited(false);
                        return x;
                    });
                    // If the person being reviewed is yourself,
                    // update the review status of the project
                    if (CacheObjects.getMemberId().equals(needAuditMember.getMemberId())) {
                        ProjectMySqlModel project = projectService.findByProjectId(projectId);
                        project.setAuditStatus(AuditStatus.agree);
                        project.setAuditStatusFromOthers(AuditStatus.agree);
                        project.setAuditComment(null);
                        project.setStatusUpdatedTime(new Date());
                        projectRepo.save(project);
                    }
                }
            }
        }
        // Delete all the members' pending audit records of this member,
        // and he no longer needs to review
        projectMemberAuditRepository.deleteAuditingRecord(projectId, memberId);
    }

    /**
     * Add a dataset to an existing project
     */
    @Transactional(rollbackFor = Exception.class)
    public synchronized ProjectMySqlModel addProjectDataSet(AddDataSetApi.Input input) throws StatusCodeWithException {
        ProjectMySqlModel project = findByProjectId(input.getProjectId());
        if (project == null) {
            throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "未找到相应的项目！");
        }

        if (!input.fromGateway()) {
            if (project.getAuditStatus() != AuditStatus.agree || project.isExited()) {
                throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "请在成为该项目的正式成员后再进行相关操作");
            }
        }

        if (CollectionUtils.isEmpty(input.getDataResourceList())) {
            throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "数据集不能为空");
        }

        for (ProjectDataSetInput item : input.getDataResourceList()) {
            // Determine whether the member exists
            ProjectMemberMySqlModel member = projectMemberService.findOneByMemberId(input.getProjectId(), item.getMemberId(), item.getMemberRole());
            if (member == null) {
                throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "该成员不存在");
            }

            // Add your own data set by yourself, the review status is agreed,
            // and add another person's data set, the status is under review.
            AuditStatus auditStatus;
            if (input.fromGateway()) {
                auditStatus = item.getMemberId().equals(input.callerMemberInfo.getMemberId())
                        ? AuditStatus.agree
                        : AuditStatus.auditing;
            } else {
                auditStatus = item.getMemberId().equals(CacheObjects.getMemberId())
                        ? AuditStatus.agree
                        : AuditStatus.auditing;
            }

            // Determine whether the data set exists
            ProjectDataSetMySqlModel projectDataSet = projectDataSetService.findOne(input.getProjectId(), item.getDataSetId(), item.getMemberRole());
            if (projectDataSet != null) {
                projectDataSet.setAuditStatus(auditStatus);
            } else {
                projectDataSet = ModelMapper.map(item, ProjectDataSetMySqlModel.class);
                projectDataSet.setProjectId(project.getProjectId());
                projectDataSet.setCreatedBy(input);
                projectDataSet.setAuditStatus(auditStatus);

                projectDataSet.setMemberRole(item.getMemberRole());
                projectDataSet.setStatusUpdatedTime(new Date());
                projectDataSet.setSourceType(null);
                projectDataSet.setDataResourceType(item.getDataResourceType());
            }

            projectDataSetRepo.save(projectDataSet);
            // Update the usage count of the dataset in the project
            if (projectDataSet.getAuditStatus() == AuditStatus.agree) {
                dataResourceService.updateUsageCountInProject(projectDataSet.getDataSetId());
            }

            // 如果申请使用我方数据资源，添加一条消息予以提醒。
            if (input.fromGateway() && CacheObjects.isCurrentMember(item.getMemberId())) {
                messageService.addApplyDataResourceMessage(
                        input.callerMemberInfo.getMemberId(),
                        project,
                        projectDataSet
                );
            }
        }

        gatewayService.syncToNotExistedMembers(input.getProjectId(), input, AddDataSetApi.class);

        return project;
    }


    /**
     * Remove the data set in the project
     */
    @Transactional(rollbackFor = Exception.class)
    public synchronized void removeDataSet(RemoveDataSetApi.Input input) throws Exception {

        ProjectMySqlModel project = findByProjectId(input.getProjectId());
        if (project == null) {
            throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "未找到相应的项目！");
        }

        if (!input.fromGateway()) {
            if (project.getAuditStatus() != AuditStatus.agree || project.isExited()) {
                throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "请在成为该项目的正式成员后再进行相关操作");
            }
        }

        // Determine whether the data set exists
        ProjectDataSetMySqlModel projectDataSet = projectDataSetService.findOne(input.getProjectId(), input.getDataSetId(), input.getMemberRole());
        if (projectDataSet == null) {
            return;
        }

        if (!input.fromGateway()) {

            // If it is the raw data set
            if (projectDataSet.getSourceType() == null) {
                // Not a promoter, you can't delete other people's data sets
                if (project.getMyRole() != JobMemberRole.promoter && !CacheObjects.getMemberId().equals(projectDataSet.getMemberId())) {
                    throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "不能删除别人的数据集");
                }

                OnlineDemoBranchStrategy.hackOnDelete(input, projectDataSet, "只能删除自己添加的数据集。");
            }
            // If it is the derived data set
            else {
                if (project.getMyRole() != JobMemberRole.promoter) {
                    throw new StatusCodeWithException(StatusCode.ILLEGAL_REQUEST, "只有 promoter 才能删除衍生数据集");
                }
                dataResourceService.delete(projectDataSet.getDataSetId(), projectDataSet.getDataResourceType());
            }

        }

        // delete ProjectDataSet
        projectDataSetRepo.deleteById(projectDataSet.getId());

        // Update the usage count of the dataset in the project
        dataResourceService.updateUsageCountInProject(projectDataSet.getDataSetId());

        gatewayService.syncToNotExistedMembers(input.getProjectId(), input, RemoveDataSetApi.class);

    }

    /**
     * Count the number of items from roles and audit status respectively
     */
    public CountStatisticsApi.Output statistics(QueryApi.Input input) {
        // Statistics by role
        StringBuffer sql = new StringBuffer()
                .append("select my_role,count(*) as 'count' from project p ")
                .append(buildQueryWhere(input))
                .append(" group by my_role");

        Map<JobMemberRole, Long> byRole = projectRepo
                .query(sql.toString())
                .stream()
                .map(x ->
                        new RoleCounts(
                                JobMemberRole.valueOf(String.valueOf(x[0])),
                                Long.parseLong(String.valueOf(x[1]))
                        )
                ).collect(Collectors.toMap(x -> x.getRole(), x -> x.getCount()));

        for (JobMemberRole value : JobMemberRole.values()) {
            if (!byRole.containsKey(value)) {
                byRole.put(value, 0L);
            }
        }

        long total = 0;

        for (Long value : byRole.values()) {
            total += value;
        }

        // Statistics by audit status
        sql = new StringBuffer()
                .append("select p.audit_status,count(*) as 'count' from project p ")
                .append(buildQueryWhere(input))
                .append(" group by p.audit_status");

        Map<AuditStatus, Long> byAuditStatus = projectRepo
                .query(sql.toString())
                .stream()
                .map(x ->
                        new AuditStatusCounts(
                                AuditStatus.valueOf(String.valueOf(x[0])),
                                Long.parseLong(String.valueOf(x[1]))
                        )
                ).collect(Collectors.toMap(x -> x.getAuditStatus(), x -> x.getCount()));

        for (AuditStatus value : AuditStatus.values()) {
            if (!byAuditStatus.containsKey(value)) {
                byAuditStatus.put(value, 0L);
            }
        }

        return new CountStatisticsApi.Output(total, byRole, byAuditStatus);

    }

    public PagingOutput<ProjectQueryOutputModel> query(QueryApi.Input input) {

        StringBuffer sql = new StringBuffer(
                "select distinct(p.id),p.top,p.sort_num,p.project_type,p.flow_status_statistics,p.deleted,p.name,p.project_desc,p.audit_status,p.status_updated_time"
                        + ",p.audit_status_from_myself,p.audit_status_from_others,p.audit_comment,p.exited,p.closed"
                        + ",p.closed_by,p.closed_time,p.exited_by,p.exited_time"
                        + ",p.project_id,p.member_id,p.my_role"
                        + ",p.start_time,p.finish_time,p.progress,p.progress_updated_time,p.message"
                        + ",p.created_by,p.created_time,p.updated_by,p.updated_time" + " from project p");

        int total = projectRepo.queryByClass(sql.append(buildQueryWhere(input)).toString(), ProjectMySqlModel.class).size();

        sql.append(" order by p.top desc,p.sort_num desc,p.created_time desc");
        sql.append(" limit " + input.getPageIndex() * input.getPageSize() + "," + input.getPageSize());

        List<ProjectMySqlModel> projectList = projectRepo.queryByClass(sql.toString(), ProjectMySqlModel.class);
        List<ProjectQueryOutputModel> list = projectList
                .parallelStream()
                .map(this::buildJobOutputModel)
                .collect(Collectors.toList());
        return PagingOutput.of(total, list);
    }

    private String buildQueryWhere(QueryApi.Input input) {
        StringBuffer where = new StringBuffer();

        if (StringUtil.isNotBlank(input.getMemberId())) {
            where.append(" inner join project_member pm on p.project_id=pm.project_id where 1=1");
            where.append(" and pm.member_id = '" + input.getMemberId() + "'");
            if (input.getMemberRole() != null) {
                where.append(" and pm.member_role = '" + input.getMemberRole() + "'");
            }
        } else {
            where.append(" where 1=1");
        }


        where.append(" and p.deleted != true ");

        if (input.getProjectType() != null) {
            where.append(" and p.project_type = '" + input.getProjectType() + "'");
        }

        if (StringUtil.isNotBlank(input.getName())) {
            where.append(" and p.name like '%" + input.getName() + "%'");
        }

        if (StringUtil.isNotBlank(input.getStartCreateTime())) {
            where.append(" and p.created_time >= '" + input.getStartCreateTime() + "'");
        }

        if (StringUtil.isNotBlank(input.getEndCreateTime())) {
            where.append(" and p.created_time <= '" + input.getEndCreateTime() + "'");
        }

        if (input.getMyRole() != null) {
            where.append(" and p.my_role = '" + input.getMyRole() + "'");
        }

        if (input.getClosed() != null) {
            where.append(" and p.closed = " + input.getClosed());
        }

        where.append(" and p.exited = 0");

        if (input.getAuditStatus() != null) {
            if (input.getAuditStatus() == AuditStatus.auditing) {
                where.append(""
                        + " and ("
                        + "p.audit_status= '" + input.getAuditStatus() + "'"
                        + " or "
                        + " p.project_id in ("
