package cn.com.victorysoft.business.analyze.service;

import cn.com.victorysoft.business.analyze.constant.CstDataTypeEnum;
import cn.com.victorysoft.business.analyze.constant.CstGroupType;
import cn.com.victorysoft.business.analyze.dao.CustomSearchDao;
import cn.com.victorysoft.business.analyze.entity.*;
import cn.com.victorysoft.business.analyze.param.CstDataSourceParam;
import cn.com.victorysoft.business.analyze.param.CstSearchExportParam;
import cn.com.victorysoft.business.analyze.param.CstSearchParam;
import cn.com.victorysoft.business.analyze.service.condition.Condition;
import cn.com.victorysoft.business.analyze.service.condition.ConditionFactory;
import cn.com.victorysoft.business.constant.UnitLevel;
import cn.com.victorysoft.business.sys.entity.SysUnitinfo;
import cn.com.victorysoft.business.sys.service.SysBaseService;
import cn.com.victorysoft.business.util.BeanUtils;
import cn.com.victorysoft.business.util.PageUtils;
import cn.com.victorysoft.business.util.StringUtils;
import cn.com.victorysoft.vseaf.core.util.IDUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.DigestUtils;

import javax.annotation.Resource;
import java.lang.reflect.InvocationTargetException;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class CustomSearchService {

    @Resource
    private CustomSearchDao customSearchDao;

    @Resource
    private SysBaseService sysBaseService;

    /**
     * 查询附录数据
     */
    public List<CstDictData> selectDictData(CstDataSourceParam param) {
        CstDataSource dataSource = customSearchDao.selectDataSourceById(param.getDataSourceId());
        if (dataSource == null) return null;
        if (StringUtils.isNotEmpty(dataSource.getFilterSql())) {
            dataSource.setFilterSql(dataSource.getFilterSql().replaceAll("#\\{unitcode}", "'" + param.getUnitcode() + "'"));
        }
        PageHelper.startPage(1, 50);
        return customSearchDao.selectDictData(dataSource, param.getSearchText());
    }

    /**
     * 查询单位的模板列表
     */
    public List<CstTemplate> selectTemplates(String unitcode){
        return customSearchDao.selectTemplates(unitcode);
    }

    /**
     * 查询模板对象
     */
    public CstTemplate selectTemplate(String id){
        CstTemplate template = customSearchDao.selectTemplate(id);
        List<CstTemplateTable> tables = customSearchDao.selectTemplateTables(id);
        List<CstTemplateIndicator> indicators = customSearchDao.selectTemplateIndicators(id);
        List<CstTemplateCondition> conditions = customSearchDao.selectTemplateConditions(id);
        template.setTables(tables);
        template.setIndicators(indicators);
        template.setConditions(conditions);
        return template;
    }

    /**
     * 新增模板对象
     */
    @Transactional
    public void insertTemplate(CstTemplate template){
        template.setId(IDUtils.getUUID32());
        template.setUpdateTime(new Date());
        customSearchDao.insertTemplate(template);
        this.insertTemplateData(template);
    }

    /**
     * 更新模板对象
     */
    @Transactional
    public void updateTemplate(CstTemplate template){
        customSearchDao.updateTemplate(template);
        customSearchDao.deleteTemplateTables(template.getId());
        customSearchDao.deleteTemplateIndicators(template.getId());
        customSearchDao.deleteTemplateConditions(template.getId());
        this.insertTemplateData(template);
    }

    /**
     * 插入模板数据
     */
    @Transactional
    public void insertTemplateData(CstTemplate template) {
        template.getTables().forEach(item -> item.setTemplateId(template.getId()));
        customSearchDao.insertTemplateTables(template.getTables());
        template.getIndicators().forEach(item -> {
            item.setTemplateId(template.getId());
            item.setId(this.generateId());
        });
        customSearchDao.insertTemplateIndicators(template.getIndicators());
        if (template.getConditions().size() > 0) {
            template.getConditions().forEach(item -> item.setTemplateId(template.getId()));
            customSearchDao.insertTemplateConditions(template.getConditions());
        }
    }

    /**
     * 生成模板指标的ID，查询时作为查询字段名，因为Oracle限制字段名最大长度为30，所以将16进制转为32进制，较短
     */
    private String generateId() {
        BigInteger i = new BigInteger(IDUtils.getUUID32(), 16);
        return "A" + i.toString(32).toUpperCase();
    }

    /**
     * 删除模板对象
     */
    @Transactional
    public void deleteTemplate(String templateId){
        customSearchDao.deleteTemplateTables(templateId);
        customSearchDao.deleteTemplateIndicators(templateId);
        customSearchDao.deleteTemplateConditions(templateId);
        customSearchDao.deleteTemplateShares(templateId);
        customSearchDao.deleteTemplate(templateId);
    }

    /**
     * 共享模板对象
     */
    @Transactional
    public void shareTemplate(String templateId, String[] unitcodes){
        customSearchDao.deleteTemplateShares(templateId);
        List<CstTemplateShare> shares = Arrays.stream(unitcodes).map(item -> new CstTemplateShare(templateId, item))
                .collect(Collectors.toList());
        customSearchDao.insertTemplateShares(shares);
    }

    /**
     * 查询共享单位
     */
    public CstTemplateShareUnit selectShareUnits(String templateId){
        CstTemplate template = customSearchDao.selectTemplate(templateId);
        if (template == null) return null;
        String unitcode = template.getUnitcode();
        SysUnitinfo unitinfo = sysBaseService.selectUnitByCode(unitcode);
        int level = unitinfo.getUnitlevel().intValue();
        String rootUnitCode = unitcode;
        // 管理区用户可共享到厂
        if (UnitLevel.AREA_LEVEL == level) {
            rootUnitCode = unitinfo.getPUnitcode();
        }
        List<SysUnitinfo> unitList = null;
        if (UnitLevel.COMPANY_LEVEL == level || UnitLevel.AREA_LEVEL == level) {
            unitList = sysBaseService.selectUnitNodeListLevel3(rootUnitCode);
        }else if (UnitLevel.GROUP_LEVEL == level) {
            unitList = sysBaseService.selectUnitNodeListLevel2(rootUnitCode);
        }
        if (unitList == null) return null;
        // 查询模板共享单位
        List<CstTemplateShare> shares = customSearchDao.selectTemplateShares(templateId);
        Set<String> unitCodeSet = shares.stream().map(CstTemplateShare::getUnitcode).collect(Collectors.toSet());
        List<CstTemplateShareUnit> shareUnits = unitList.stream().map(item -> {
            CstTemplateShareUnit shareUnit = new CstTemplateShareUnit();
            try {
                BeanUtils.copyProperties(shareUnit, item);
                shareUnit.setShared(unitCodeSet.contains(item.getUnitcode()));
            } catch (IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
            }
            return shareUnit;
        }).collect(Collectors.toList());
        return (CstTemplateShareUnit) sysBaseService.buildUnitTree(shareUnits);
    }

    /**
     * 查询数据表
     */
    public List<CstTable> selectTables() {
        return customSearchDao.selectTables();
    }

    /**
     * 查询关联表
     */
    public List<CstTable> selectRelateTables(String tableId) {
        return customSearchDao.selectRelateTables(tableId);
    }

    /**
     * 查询数据表指标
     */
    public List<CstTableIndicator> selectTableIndicators(String tableId) {
        // 查询查询类型
        List<CstQueryType> queryTypes = customSearchDao.selectQueryTypes();
        Map<String, List<CstQueryType>> queryTypeMap = queryTypes.stream().collect(Collectors.groupingBy(CstQueryType::getDataTypeId));
        // 查询指标
        List<CstTableIndicator> indicators = customSearchDao.selectTableIndicators(tableId);
        // 设置指标的查询方式列表
        indicators.forEach(item -> {
            item.setQueryTypes(queryTypeMap.get(item.getDataTypeId()));
            if (StringUtils.isEmpty(item.getQueryTypeId()) && item.getQueryTypes() != null) {
                item.setQueryTypeId(item.getQueryTypes().get(0).getDataTypeId());
            }
        });
        return indicators;
    }

    /**
     * 根据报表类型查询指标
     */
    public PageInfo<CstIndicator> selectIndicators(String tableType, Integer pageNum, Integer pageSize, String searchField) {
        PageHelper.startPage(pageNum, pageSize);
        // 根据tableType查询CstIndicator
        List<CstIndicator> result = customSearchDao.selectIndicators(tableType, searchField);
        // 有对应数据表时才去查询指标
        if (result.size() > 0) {
            // 根据result查询CstTableIndicator
            List<CstTableIndicator> tableIndicators = customSearchDao.selectByIndicators(tableType, result);

            for (CstTableIndicator tableIndicator : tableIndicators) {
                CstIndicator indicator = null;
                for (CstIndicator ci : result) {
                    if (ci.getId().equals(tableIndicator.getIndicatorId())) {
                        indicator = ci;
                        break;
                    }
                }
                if (indicator == null) {
                    indicator = new CstIndicator();
                    indicator.setId(tableIndicator.getIndicatorId());
                    indicator.setName(tableIndicator.getName());
                    indicator.setUnit(tableIndicator.getUnit());
                    indicator.setTableIndicatorId(tableIndicator.getId());
                    indicator.setTableIndicators(new ArrayList<>());
                    result.add(indicator);
                }else if (indicator.getTableIndicators() == null) {
                    indicator.setTableIndicatorId(tableIndicator.getId());
                    indicator.setTableIndicators(new ArrayList<>());
                }
                indicator.getTableIndicators().add(tableIndicator);
            }
        }

        return new PageInfo<>(result);
    }

    /**
     * 查询数据
     */
    public List<Map<String, Object>> searchList(CstSearchParam param, boolean page) {
        StringBuilder sb = new StringBuilder();
        sb.append("with unitcodes as (select unitcode from sys_unitinfo start with unitcode = '");
        sb.append(param.getUnitcode());
        sb.append("' connect by prior unitcode = p_unitcode) ");
        sb.append("select ");
        String templateId = param.getTemplateId();
        // 查询模板数据表
        List<CstTemplateTable> templateTables = customSearchDao.selectTemplateTables(templateId);
        // 查询模板字段
        List<CstTemplateIndicator> templateIndicators = customSearchDao.selectTemplateIndicators(templateId);
        // 拼接查询字段
        for (int i = 0; i < templateIndicators.size(); i++) {
            CstTemplateIndicator templateIndicator = templateIndicators.get(i);
            if (i != 0) {
                sb.append(",");
            }
            // 自定义参数
            if (StringUtils.isEmpty(templateIndicator.getIndicatorId())) {
                sb.append(templateIndicator.getFormula());
            }
            // 数据表参数
            else {
                CstTableIndicator indicator = templateIndicator.getIndicator();
                if (StringUtils.isNotEmpty(indicator.getFormula())) {
                    sb.append(indicator.getFormula());
                }else {
                    sb.append(indicator.getTableId()).append(".").append(indicator.getLabelColumn());
                }
            }

            // ID作为别名
            sb.append(" as ").append(templateIndicator.getId());
        }
        sb.append(" from ");
        List<String> tableIds = templateTables.stream().map(CstTemplateTable::getTableId).collect(Collectors.toList());
        sb.append(String.join(",", tableIds)).append(" where 1 = 1 ");
        // 拼接关联表的单位代码条件
        if (!"30200000".equals(param.getUnitcode())) {
            for (CstTemplateTable templateTable : templateTables) {
                CstTable table = templateTable.getTable();
                if (StringUtils.isNotEmpty(table.getUnitCondition())) {
                    sb.append(" and ").append(table.getId()).append(".");
                    sb.append(table.getUnitCondition());
                }
            }
        }
        // 拼接关联SQL
        List<CstTableRelation> tableRelations = customSearchDao.selectTableRelations(tableIds);
        for (CstTableRelation relation : tableRelations) {
            sb.append(" and ").append(relation.getMainTableId()).append(".").append(relation.getMainTableColumn())
                    .append(" = ").append(relation.getRelationTableId()).append(".").append(relation.getRelationTableColumn());
        }
        // 拼接查询条件
        List<CstSearchParam.CstConditionValue> values = param.getValues();
        if (values != null && values.size() > 0) {
            for (CstSearchParam.CstConditionValue value : values) {
                // 查询指标
                CstTableIndicator indicator = customSearchDao.selectTableIndicator(value.getIndicatorId());
                Condition con = ConditionFactory.get(CstDataTypeEnum.valueOf(indicator.getDataTypeId()), value.getOperator());
                if (con != null) {
                    String sql = con.buildSql(indicator, value.getValue1(), value.getValue2());
                    if (StringUtils.isNotEmpty(sql)) {
                        sb.append(sql);
                    }
                }
            }
        }
        String sql = sb.toString();
        log.info("自定义查询SQL：{}", sql);
        if (page) {
            PageUtils.startPage(param);
        }
        List<Map<String, Object>> result = customSearchDao.search(sql);
        for (int i = 0; i < result.size(); i++) {
            result.get(i).put("index", (param.getPageNum() - 1) * param.getPageSize() + i + 1);
        }

        // 使用统计信息
        if (param.isUseGroup() && param.getPageNum() == 1) {
            boolean haveGroupType = false;
            sb = new StringBuilder("select ");
            for (CstSearchParam.CstGroupSetting groupSetting : param.getGroupSettings()) {
                String groupType = groupSetting.getGroupType();
                boolean isNumber = CstGroupType.SUM.equals(groupType) || CstGroupType.AVG.equals(groupType);
                if (StringUtils.isNotEmpty(groupType)) {
                    haveGroupType = true;
                    if (isNumber) sb.append("round(");
                    sb.append(groupType).append("(t.").append(groupSetting.getId()).append(")");
                    if (isNumber) sb.append(",2)");
                    sb.append(" as ").append(groupSetting.getId()).append(",");
                }
            }
            if (haveGroupType) {
                sb.deleteCharAt(sb.length() - 1);
                sb.append(" from (").append(sql).append(") t");
            }
            sql = sb.toString();
            List<Map<String, Object>> groupResults = customSearchDao.search(sql);
            if (groupResults.size() > 0) {
                Map<String, Object> groupResult = groupResults.get(0);
                if (groupResult != null) {
                    groupResult.put("index", "统计");
                    result.add(0, groupResult);
                }
            }
        }
        return result;
    }

    /**
     * 查询数据
     */
    public PageInfo<Map<String, Object>> search(CstSearchParam param) {
        List<Map<String, Object>> result = this.searchList(param, true);
        return new PageInfo<>(result);
    }

}
