package com.yd.csf.api.service.impl;

import com.yd.common.constant.CodeGeneratorConstants;
import com.yd.common.enums.CommonEnum;
import com.yd.common.enums.ProjectEnum;
import com.yd.common.enums.TenantEnum;
import com.yd.common.exception.BusinessException;
import com.yd.common.result.Result;
import com.yd.common.utils.RandomStringGenerator;
import com.yd.csf.api.dto.*;
import com.yd.csf.api.service.ApiAgentDetailFycService;
import com.yd.csf.api.service.ApiBasicLawCalculateService;
import com.yd.csf.api.utils.FormulaParser;
import com.yd.csf.feign.enums.VariableEnum;
import com.yd.csf.feign.request.basiclawcalculate.ApiGenerateBillingRequest;
import com.yd.csf.feign.response.basiclawcalculate.ApiGenerateBillingResponse;
import com.yd.csf.service.dto.*;
import com.yd.csf.service.enums.FortuneStatusEnum;
import com.yd.csf.service.model.*;
import com.yd.csf.service.service.*;
import com.yd.insurance.base.feign.client.ApiRelProductAnnouncementFeignClient;
import com.yd.user.feign.client.clientuser.ApiClientUserFeignClient;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.CollectionUtils;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ApiBasicLawCalculateServiceImpl implements ApiBasicLawCalculateService {

    @Autowired
    private PolicyService policyService;

    @Autowired
    private CommissionService commissionService;

    @Autowired
    private PolicyBrokerService policyBrokerService;

    @Autowired
    private IFormulaConfigService iFormulaConfigService;

    @Autowired
    private IVariableService iVariableService;

    @Autowired
    private IRelObjectConditionService iRelObjectConditionService;

    @Autowired
    private IRelObjectSqlService iRelObjectSqlService;

    @Autowired
    private ICommissionSqlTemplateService iCommissionSqlTemplateService;

    @Autowired
    private IRuleItemConfigService iRuleItemConfigService;

    @Autowired
    private IRelObjectFormulaService iRelObjectFormulaService;

    @Autowired
    private ICommissionRuleBindingService iCommissionRuleBindingService;

    @Autowired
    private ApiAgentDetailFycService apiAgentDetailFycService;

    @Autowired
    private FortuneService fortuneService;

    @Autowired
    private TransactionTemplate transactionTemplate;

    @Autowired
    private ApiClientUserFeignClient apiClientUserFeignClient;


    /**
     * 获取JdbcTemplate（需要注入）
     */
    @Autowired
    private DataSource dataSource;

    private NamedParameterJdbcTemplate getJdbcTemplate() {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    /**
     * 生成出账
     * @param request
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Result<ApiGenerateBillingResponse> generateBilling(ApiGenerateBillingRequest request) {
        //根据保单来佣业务id列表查询来佣保单转介人关系信息
        List<CommissionBindPolicyBrokerDto> policyBrokerDtoList = commissionService.queryCommissionBindPolicyBrokerList(CommissionDto.builder()
                .commissionBizIdList(request.getCommissionBizIdList())
                .build());
        if (CollectionUtils.isEmpty(policyBrokerDtoList)) {
            throw new BusinessException("来佣数据不存在");
        }

        //查询所有绑定基本法的业务员(客户端用户表用户), 计算业务员绑定的所有基本法对应的计算值, 新增积分明细和发佣记录
        List<CommissionRuleBinding> commissionRuleBindingList = new ArrayList<>();
        //根据租户ID和项目ID查询客户端用户ID列表
        Result<List<String>> result = apiClientUserFeignClient.clientUserBizIdList(TenantEnum.YD.getTenantBizId(), ProjectEnum.CSF_MINI_PROGRAM.getProjectBizId());
        if (!CollectionUtils.isEmpty(result.getData())) {
            commissionRuleBindingList = iCommissionRuleBindingService.queryList(CommissionRuleBindingDto.builder()
                    .targetIdList(result.getData())
                    .build());
        }
        if (CollectionUtils.isEmpty(commissionRuleBindingList)) {
            throw new BusinessException("绑定基本法数据不存在");
        }

        //保单发佣批次ID
        String batchBizId = RandomStringGenerator.generateBizId16(CodeGeneratorConstants.PREFIX_FORTUNE_BATCH_BIZ_ID);
        //遍历来佣保单转介人关系信息，跑基本法。
        for (CommissionBindPolicyBrokerDto brokerDto : policyBrokerDtoList) {
            //执行 - 销售佣金 - 出账 （销售佣金基本法）
            executeBilling(ExecuteBillingDto.builder()
                    .batchBizId(batchBizId)
                    .name(brokerDto.getBrokerName())
                    .brokerDto(brokerDto)
                    .build());

            //非销售佣金积基本法 - 遍历所有绑定基本法的用户(客户端用户表用户), 计算用户绑定的所有基本法对应的计算值, 新增积分明细和发佣记录 (和保单绑定的转介人在执行基本法项目的SQL时候进行关系对比)
            for (CommissionRuleBinding binding : commissionRuleBindingList) {
                //执行 - 奖励 (非销售佣金积基本法)
                executeReward(ExecuteBillingDto.builder()
                        .clientUserBizId(binding.getTargetId())
                        .name(binding.getTargetName())
                        .batchBizId(batchBizId)
                        .brokerDto(brokerDto)
                        .build());
            }
        }
        return Result.success();
    }

    /**
     * 执行 - 奖励（非销售佣金基本法）
     * @param dto
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Result executeReward(ExecuteBillingDto dto) {
        CommissionBindPolicyBrokerDto brokerDto = dto.getBrokerDto();
        String batchBizId = dto.getBatchBizId();
        //构造销售佣金基本法项目的顺序下标值执行
        List<Integer> executionOrderList = new ArrayList<>();
        executionOrderList.add(1);

        //执行获得积分用户的非销售佣金基本法项目
        Result<List<AlgorithmResDto>> result = policyBrokerAlgorithm(AlgorithmDto.builder()
                .brokerBizId(brokerDto.getBrokerBizId())
                .clientUserBizId(dto.getClientUserBizId())
                .sqlTemplateParamDto(SqlTemplateParamDto.builder()
                        .brokerBizId(brokerDto.getBrokerBizId())
                        .clientUserBizId(dto.getClientUserBizId())
                        .batchBizId(batchBizId)
                        .commissionBizId(brokerDto.getCommissionBizId())
                        .policyNo(brokerDto.getPolicyNo())
                        .productBizId(brokerDto.getPlanBizId())
                        .productCode(brokerDto.getProductCode())
                        .term(brokerDto.getTotalPeriod())
                        .issueNumber(brokerDto.getCommissionPeriod())
                        .build())
                .executionOrderList(executionOrderList)
                .isNegateExecutionOrderList(true)
                .build());

        //生成获得积分的用户的积分明细表记录
        generateAgentDetailFyc(GenerateAgentDetailFycDto.builder()
                .agentId(dto.getClientUserBizId())
                .provider(brokerDto.getBrokerBizId())
                .policyNo(brokerDto.getPolicyNo())
                .algorithmResDtoList(result.getData())
//                .sourceType()
                .build());


        //生成保单发佣表记录 (非销售佣金基本法)
        generateFortune(GenerateFortuneDto.builder()
                //获得积分的用户绑定的基本法列表对应计算值
                .algorithmResDtoList(result.getData())
                //保单发佣批次ID
                .batchBizId(batchBizId)
                //获得积分的用户姓名
                .broker(dto.getName())
                //获得积分的用户业务ID
                .brokerBizId(dto.getClientUserBizId())
                //发佣币种
                .currency(brokerDto.getCurrency())
                //发佣期数
                .fortunePeriod(brokerDto.getCommissionPeriod())
                //发佣总期数
                .fortuneTotalPeriod(brokerDto.getTotalPeriod())
                //发佣类型 TODO
//                .fortuneType()
                //发佣日期
                .payoutDate(new Date())
                //保单号
                .policyNo(brokerDto.getPolicyNo())
                //佣金发放状态 0=待出账 1=可出账 2=已出账
                .status(FortuneStatusEnum.WAIT.getItemValue())
                //所属团队 TODO
//                .team()
                //所属团队业务ID TODO
//                .teamBizId()
                .build());

        return Result.success();
    }

    /**
     * 执行 - 销售佣金 - 出账（销售佣金基本法）
     * @param dto
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Result executeBilling(ExecuteBillingDto dto) {
        String batchBizId = dto.getBatchBizId();
        CommissionBindPolicyBrokerDto brokerDto = dto.getBrokerDto();
        //构造销售佣金基本法项目的顺序下标值执行
        List<Integer> executionOrderList = new ArrayList<>();
        executionOrderList.add(1);

        //保单出账先执行销售佣金基本法项目，入完到积分明细表里后，再执行保单绑定人的其他基本法项目
        Result<List<AlgorithmResDto>> result = policyBrokerAlgorithm(AlgorithmDto.builder()
                .brokerBizId(brokerDto.getBrokerBizId())
                .clientUserBizId(brokerDto.getBrokerBizId())
                .sqlTemplateParamDto(SqlTemplateParamDto.builder()
                        .brokerBizId(brokerDto.getBrokerBizId())
                        .batchBizId(batchBizId)
                        .commissionBizId(brokerDto.getCommissionBizId())
                        .policyNo(brokerDto.getPolicyNo())
                        .productBizId(brokerDto.getPlanBizId())
                        .productCode(brokerDto.getProductCode())
                        .term(brokerDto.getTotalPeriod())
                        .issueNumber(brokerDto.getCommissionPeriod())
                        .build())
                .executionOrderList(executionOrderList)
                .isNegateExecutionOrderList(false)
                .build());

        //生成业务员（转介人）积分明细表记录（销售积分）
        generateAgentDetailFyc(GenerateAgentDetailFycDto.builder()
                .agentId(brokerDto.getBrokerBizId())
                .policyNo(brokerDto.getPolicyNo())
                .algorithmResDtoList(result.getData())
//                .sourceType()
                .build());


        //生成销售-保单发佣表记录
        generateFortune(GenerateFortuneDto.builder()
                //转介人绑定的基本法列表对应计算值
                .algorithmResDtoList(result.getData())
                //保单发佣批次ID
                .batchBizId(batchBizId)
                //获得积分的转介人（业务员）姓名
                .broker(dto.getName())
                //获得积分的转介人（业务员）业务ID
                .brokerBizId(dto.getClientUserBizId())
                //发佣币种
                .currency(brokerDto.getCurrency())
                //发佣期数
                .fortunePeriod(brokerDto.getCommissionPeriod())
                //发佣总期数
                .fortuneTotalPeriod(brokerDto.getTotalPeriod())
                //发佣类型 TODO
//                .fortuneType()
                //发佣日期
                .payoutDate(new Date())
                //保单号
                .policyNo(brokerDto.getPolicyNo())
                //佣金发放状态 0=待出账 1=可出账 2=已出账
                .status(FortuneStatusEnum.WAIT.getItemValue())
                //所属团队 TODO
//                .team()
                //所属团队业务ID TODO
//                .teamBizId()
                .build());

        return Result.success();
    }

    /**
     * 生成保单发佣表记录（出账记录）
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Result generateFortune(GenerateFortuneDto fortuneDto) {
        List<Fortune> fortuneList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(fortuneDto.getAlgorithmResDtoList())) {
            //获得积分业务员绑定的基本法列表对应计算值结果集合
            for (AlgorithmResDto algorithmResDto : fortuneDto.getAlgorithmResDtoList()) {
                if (Objects.isNull(algorithmResDto.getCalculatedValue())
                        || algorithmResDto.getCalculatedValue().compareTo(BigDecimal.ZERO) == 0){
                    //判空判0
                    continue;
                }
                Fortune fortune = new Fortune();
                //发佣金额 -> 计算值 - sqlAlgorithmResultDtoList集合里面的计算值和值
                fortune.setAmount(algorithmResDto.getCalculatedValue());
                //保单发佣批次ID
                fortune.setBatchBizId(fortuneDto.getBatchBizId());
                //转介人姓名 (获得积分的业务员)
                fortune.setBroker(fortuneDto.getBroker());
                //转介人业务ID (获得积分的业务员)
                fortune.setBrokerBizId(fortuneDto.getBrokerBizId());
                //出账项目名称 -> 基本法项目名称
                fortune.setFortuneName(algorithmResDto.getItemName());
                //保单发佣业务id
                fortune.setFortuneBizId(RandomStringGenerator.generateBizId16(CommonEnum.UID_TYPE_FORTUNE.getCode()));
                //基本法项目配置表唯一业务ID
                fortune.setRuleItemBizId(algorithmResDto.getRuleItemBizId());
                //发佣币种
                fortune.setCurrency(fortuneDto.getCurrency());
                //发佣期数
                fortune.setFortunePeriod(fortuneDto.getFortunePeriod());
                //发佣总期数
                fortune.setFortuneTotalPeriod(fortuneDto.getFortuneTotalPeriod());
                //保单号
                fortune.setPolicyNo(fortuneDto.getPolicyNo());

                fortuneList.add(fortune);
            }
        }

        if (!CollectionUtils.isEmpty(fortuneList)) {
            fortuneService.saveOrUpdateBatch(fortuneList);
        }

        return Result.success();
    }

    /**
     * 生成业务员（转介人）积分明细表记录
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public Result generateAgentDetailFyc(GenerateAgentDetailFycDto dto){
        //基本法计算 - 保存积分明细表
        apiAgentDetailFycService.saveAgentDetailFyc(dto);
        return Result.success();
    }

    /**
     * 算法-计算-转介人（销售业务员）绑定的基本法类型（基本法项目列表）对应的积分值
     * @param algorithmDto
     * @return
     */
    @Override
    public Result<List<AlgorithmResDto>> policyBrokerAlgorithm(AlgorithmDto algorithmDto) {
        List<AlgorithmResDto> algorithmResDtoList = new ArrayList<>();
        //校验 - 转介人业务ID（客户端用户表唯一业务ID）不能为空
        if (StringUtils.isBlank(algorithmDto.getBrokerBizId())) {
            throw new BusinessException("转介人业务ID（客户端用户表唯一业务ID）不能为空");
        }
        List<CommissionRuleBinding> commissionRuleBindingList = iCommissionRuleBindingService.queryList(CommissionRuleBindingDto.builder().targetId(algorithmDto.getClientUserBizId()).build());
        if (CollectionUtils.isEmpty(commissionRuleBindingList)) {
            throw new BusinessException("获得积分的用户所绑定的基本法信息不存在");
        }
        //执行获得积分的用户绑定的多个基本法类型 (一个基本法类型又有多个基本法项目)
        for (CommissionRuleBinding commissionRuleBinding : commissionRuleBindingList) {
            algorithmDto.setRuleBizId(commissionRuleBinding.getRuleBizId());
            Result<List<AlgorithmResDto>> result =commissionRuleAlgorithm(algorithmDto);
            //多个基本法类型执行结果列表汇总
            algorithmResDtoList.addAll(result.getData());
        }
        return Result.success(algorithmResDtoList);
    }

    /**
     * 算法-计算-基本法类型
     * @param algorithmDto 算法DTO - 公共入参封装
     * @return
     */
    public Result<List<AlgorithmResDto>> commissionRuleAlgorithm(AlgorithmDto algorithmDto) {
        //校验 - 基本法配置表唯一业务ID不能为空
        if (StringUtils.isBlank(algorithmDto.getRuleBizId())) {
            throw new BusinessException("基本法配置表唯一业务ID不能为空");
        }
        //查询基本法类型绑定的基本法项目列表 - 执行顺序，数值越小越先执行，用于控制佣金项目的计算顺序
        List<RuleItemConfig> ruleItemConfigList = iRuleItemConfigService.queryList(RuleItemConfigDto.builder().ruleBizId(algorithmDto.getRuleBizId()).build());
        if (CollectionUtils.isEmpty(ruleItemConfigList)) {
            throw new BusinessException("基本法项目列表不存在");
        }
        List<AlgorithmResDto> algorithmResDtoList = new ArrayList<>();
        //遍历基本法项目 - 计算对应值
        for (RuleItemConfig ruleItemConfig : ruleItemConfigList) {
            if (!CollectionUtils.isEmpty(algorithmDto.getExecutionOrderList())) {
                //基本法项目执行顺序和是否在需要执行的基本法项目的执行顺序下标值列表中
                List<Integer> isExistList = algorithmDto.getExecutionOrderList().stream().filter(s -> s.equals(ruleItemConfig.getExecutionOrder())).collect(Collectors.toList());
                //需要执行的基本法项目的执行顺序下标值列表（为空就是执行全部项目）
                if (algorithmDto.getIsNegateExecutionOrderList() && !CollectionUtils.isEmpty(isExistList)) {
                    //取反并且isExistList非空 -> 跳出当前循环
                    continue;
                }
                if (!algorithmDto.getIsNegateExecutionOrderList() && CollectionUtils.isEmpty(isExistList)) {
                    //非取反并且isExistList空 -> 跳出当前循环
                    continue;
                }
            }
            algorithmDto.setRuleItemBizId(ruleItemConfig.getRuleItemBizId());
            //算法-计算-基本法项目
            Result<AlgorithmResDto> result = ruleItemAlgorithm(algorithmDto);
            AlgorithmResDto algorithmResDto = result.getData();
            if (!Objects.isNull(algorithmResDto)) {
                //基本法项目名称
                algorithmResDto.setItemName(ruleItemConfig.getItemName());
            }
            algorithmResDtoList.add(algorithmResDto);
        }
        return Result.success(algorithmResDtoList);
    }

    /**
     * 算法-计算-基本法项目
     * @param algorithmDto 算法DTO - 公共入参封装
     * @return
     */
    public Result<AlgorithmResDto> ruleItemAlgorithm(AlgorithmDto algorithmDto) {
        //校验 - 基本法项目配置表唯一业务ID不能为空
        if (StringUtils.isBlank(algorithmDto.getRuleItemBizId())) {
            throw new BusinessException("基本法项目配置表唯一业务ID不能为空");
        }
        //查询对象公式关系 - 即基本法项目和公式关系数据
        List<RelObjectFormula> relObjectFormulaList = iRelObjectFormulaService.queryList(RelObjectFormulaDto.builder().objectBizId(algorithmDto.getRuleItemBizId()).build());
        if (CollectionUtils.isEmpty(relObjectFormulaList)) {
            throw new BusinessException("基本法项目和公式关系数据不存在");
        }
        RelObjectFormula relObjectFormula = relObjectFormulaList.get(0);
        //公式配置表唯一业务ID
        algorithmDto.setFormulaBizId(relObjectFormula.getFormulaBizId());
        //算法-计算-公式
        Result<AlgorithmResDto> result = calculationFormulaAlgorithm(algorithmDto);
        AlgorithmResDto dto = result.getData();
        dto.setRuleBizId(algorithmDto.getRuleBizId());
        dto.setRuleItemBizId(algorithmDto.getRuleItemBizId());
        return Result.success(dto);
    }

    /**
     * 算法-计算-公式
     * @param algorithmDto 算法DTO - 公共入参封装
     * @return
     */
    public Result<AlgorithmResDto> calculationFormulaAlgorithm(AlgorithmDto algorithmDto) {
        AlgorithmResDto resDto = new AlgorithmResDto();
        //校验算法DTO入参
        checkAlgorithmDto(algorithmDto);
        //获取计算公式对象
        FormulaConfig formulaConfig = iFormulaConfigService.queryOne(algorithmDto.getFormulaBizId());
        if (Objects.isNull(formulaConfig)) {
            throw new BusinessException("计算公式对象不存在");
        }
        //获取计算公式（变量表唯一业务ID组合）
        String calculationFormulaBizId = formulaConfig.getCalculationFormulaBizId();
        if (StringUtils.isBlank(calculationFormulaBizId)) {
            throw new BusinessException("获取计算公式（变量表唯一业务ID组合）不存在");
        }
        //解析计算公式-获取用到的变量表业务ID集合列表
        //从公式字符串中解析出所有变量业务ID（返回列表，保持顺序）
        List<String> variableBizIdList = new ArrayList<>();
        try {
            variableBizIdList = FormulaParser.parseVariableBizIdsOrdered(calculationFormulaBizId);
        }catch (Exception e) {
            throw new BusinessException("从公式字符串中解析出所有变量业务ID，解析失败");
        }
        //检测解析出来的变量表业务ID集合列表是否存在
        if (CollectionUtils.isEmpty(variableBizIdList)) {
            throw new BusinessException("解析计算公式-获取用到的变量表业务ID集合列表不存在");
        }
        //变量表业务ID集合列表去重
        variableBizIdList = variableBizIdList.stream().distinct().collect(Collectors.toList());
        algorithmDto.setVariableBizIdList(variableBizIdList);
        //算法-计算-变量
        Result<List<VariableAlgorithmDto>> variableResult = variableAlgorithm(algorithmDto);
        if (variableResult.getCode() != 200) {
            throw new BusinessException("变量计算失败: " + variableResult.getMsg());
        }

        List<VariableAlgorithmDto> variableValues = variableResult.getData();

        //将变量值代入计算公式进行计算
        BigDecimal result = evaluateFormula(calculationFormulaBizId, variableValues);

        resDto.setCalculatedValue(result);

        //通过计算公式-计算变量绑定的SQL模板计算结果集合列表
//        Result<List<SqlAlgorithmResultDto>> listResult = calculateSqlAlgorithmResultDtoList(calculationFormulaBizId,variableValues);
//        resDto.setSqlAlgorithmResultDtoList(listResult.getData());

        return Result.success(resDto);
    }

    /**
     * 通过计算公式-计算变量绑定的SQL模板计算结果集合列表（大于一条SQL结果记录需要按照积分贡献者或者其他分组情况维度计算，一条SQL结果不需要这样计算，单独调用方法计算）
     * @param calculationFormulaBizId
     * @param variableValues
     * @return
     */
    public Result<List<SqlAlgorithmResultDto>> calculateSqlAlgorithmResultDtoList(String calculationFormulaBizId,
                                                                                  List<VariableAlgorithmDto> variableValues) {
        List<SqlAlgorithmResultDto> sqlAlgorithmResultDtoList = new ArrayList<>();
        //查询出绑定的SQL模板计算结果集合列表长度大于1的记录
        List<VariableAlgorithmDto> filterList = variableValues.stream()
                .filter(dto -> !CollectionUtils.isEmpty(dto.getSqlAlgorithmResultDtoList())
                        && dto.getSqlAlgorithmResultDtoList().size() > 1).collect(Collectors.toList());
        //查询出绑定的SQL模板计算结果集合列表长度小于等于1的记录（作为SQL模板计算结果集合列表长度大于1的记录的公式算子）
        List<VariableAlgorithmDto> otherList = variableValues.stream()
                .filter(dto -> !CollectionUtils.isEmpty(dto.getSqlAlgorithmResultDtoList())
                        && dto.getSqlAlgorithmResultDtoList().size() <= 1).collect(Collectors.toList());
        if (!CollectionUtils.isEmpty(filterList) && filterList.size() == 1) {
            VariableAlgorithmDto algorithmDto = filterList.get(0);
            if (!CollectionUtils.isEmpty(algorithmDto.getSqlAlgorithmResultDtoList())) {
                for (SqlAlgorithmResultDto sqlAlgorithmResultDto : algorithmDto.getSqlAlgorithmResultDtoList()) {
                    List<VariableAlgorithmDto> dtoList = new ArrayList<>();
                    dtoList.addAll(otherList);
                    //构造evaluateFormula计算方法的VariableAlgorithmDto对象
                    VariableAlgorithmDto dto = new VariableAlgorithmDto();
                    dto.setCalculatedValue(sqlAlgorithmResultDto.getCalculatedValue());
                    dto.setVariableBizId(algorithmDto.getVariableBizId());
                    dtoList.add(dto);
                    //计算公式 - 将变量值代入公式进行计算
                    BigDecimal result = evaluateFormula(calculationFormulaBizId,dtoList);
                    SqlAlgorithmResultDto resultDto = new SqlAlgorithmResultDto();
                    BeanUtils.copyProperties(sqlAlgorithmResultDto,resultDto);
                    resultDto.setCalculatedValue(result.toString());
                    sqlAlgorithmResultDtoList.add(resultDto);
                }
            }
        }
        return Result.success(sqlAlgorithmResultDtoList);
    }

    /**
     * 计算公式 - 将变量值代入公式进行计算
     * @param formula 公式字符串，如："(variable_1001 + variable_2123) * variable_4455"
     * @param variableValues 变量值列表
     * @return 计算结果
     */
    private BigDecimal evaluateFormula(String formula, List<VariableAlgorithmDto> variableValues) {
        try {
            //创建变量值映射表
            Map<String, BigDecimal> variableMap = new HashMap<>();
            for (VariableAlgorithmDto variable : variableValues) {
                if (StringUtils.isNotBlank(variable.getCalculatedValue())) {
                    try {
                        BigDecimal value = new BigDecimal(variable.getCalculatedValue());
                        variableMap.put(variable.getVariableBizId(), value);
                    } catch (NumberFormatException e) {
                        throw new BusinessException("变量值格式错误: " + variable.getVariableBizId() + " = " + variable.getCalculatedValue());
                    }
                }
            }

            //替换公式中的变量业务ID为实际数值
            String expression = replaceVariablesWithValues(formula, variableMap);

            //计算表达式
            return calculateExpression(expression);

        } catch (Exception e) {
            log.error("计算公式失败, formula: {}, variables: {}", formula, variableValues, e);
            throw new BusinessException("计算公式失败: " + e.getMessage());
        }
    }

    /**
     * 将公式中的变量业务ID替换为实际数值
     * @param formula 原始公式
     * @param variableMap 变量映射表
     * @return 替换后的表达式
     */
    private String replaceVariablesWithValues(String formula, Map<String, BigDecimal> variableMap) {
        String expression = formula;

        //按变量业务ID长度从长到短排序，避免替换时出现部分匹配的问题
        List<String> sortedKeys = variableMap.keySet().stream()
                .sorted((a, b) -> Integer.compare(b.length(), a.length()))
                .collect(Collectors.toList());

        for (String variableBizId : sortedKeys) {
            BigDecimal value = variableMap.get(variableBizId);
            if (value != null) {
                //将变量业务ID替换为数值，确保完全匹配
                expression = expression.replace(variableBizId, value.toString());
            }
        }

        //检查是否还有未替换的变量
        if (containsUnreplacedVariables(expression)) {
            throw new BusinessException("公式中存在未计算的变量: " + expression);
        }

        return expression;
    }

    /**
     * 检查表达式中是否还有未替换的变量
     */
    private boolean containsUnreplacedVariables(String expression) {
        //假设变量业务ID的格式是 "variable_" 开头
        return expression.matches(".*variable_\\w+.*");
    }

    /**
     * 计算数学表达式
     * @param expression 数学表达式，如："(100.5 + 200.3) * 50.2"
     * @return 计算结果
     */
    private BigDecimal calculateExpression(String expression) {
        try {
            //使用JavaScript引擎计算表达式
            ScriptEngineManager manager = new ScriptEngineManager();
            ScriptEngine engine = manager.getEngineByName("JavaScript");

            //计算表达式
            Object result = engine.eval(expression);

            //转换为BigDecimal
            if (result instanceof Number) {
                return new BigDecimal(result.toString());
            } else {
                throw new BusinessException("表达式计算结果不是数字类型: " + result);
            }

        } catch (ScriptException e) {
            throw new BusinessException("表达式计算失败: " + expression + ", 错误: " + e.getMessage());
        }
    }

    /**
     * 算法-计算-变量
     * @param algorithmDto 算法DTO - 公共入参封装
     * @return
     */
    public Result<List<VariableAlgorithmDto>> variableAlgorithm(AlgorithmDto algorithmDto) {
        //校验算法DTO入参
        checkAlgorithmDto(algorithmDto);
        //查询变量表列表信息
        List<Variable> variableList = iVariableService.queryList(VariableDto.builder()
                .variableBizIdList(algorithmDto.getVariableBizIdList())
                .build());
        if (CollectionUtils.isEmpty(variableList)) {
            throw new BusinessException("变量表列表信息不存在");
        }
        List<VariableAlgorithmDto> variableAlgorithmDtoList = new ArrayList<>();
        //遍历变量列表 - 计算对应的值
        for (Variable variable : variableList) {
            VariableAlgorithmDto dto = new VariableAlgorithmDto();
            BeanUtils.copyProperties(variable,dto);
            //变量类型 - 固定值
            if (VariableEnum.FIXED_VALUE.getItemValue().equals(variable.getType())) {
                //固定值，直接取出固定值返回作为公式计算的值
                dto.setCalculatedValue(variable.getValue());
            }else if (VariableEnum.DYNAMIC_COMPUTATION.getItemValue().equals(variable.getType())){
                //动态计算值 - 结合通用入参参数、条件、SQL模板计算出值
                //校验 - 动态计算必须绑定SQL模板。查询对象和SQL模板配置关系表
                List<RelObjectSql> relObjectSqlList = iRelObjectSqlService.queryList(RelObjectSqlDto.builder().objectBizId(variable.getVariableBizId()).build());
                if (CollectionUtils.isEmpty(relObjectSqlList)) {
                    throw new BusinessException("动态计算的变量必须要绑定SQL模板");
                }
                RelObjectSql relObjectSql = relObjectSqlList.get(0);
                //SQL模板表唯一业务ID
                algorithmDto.setSqlTemplateBizId(relObjectSql.getSqlTemplateBizId());
                if (Objects.isNull(algorithmDto.getSqlTemplateParamDto())) {
                    //对象空构造空对象
                    algorithmDto.setSqlTemplateParamDto(new SqlTemplateParamDto());
                }
                //先查询是否有关联条件：有关联条件作为SQL模板的判断依据，有关联条件就查询关联条件类型业务ID带入到SQL模板关联查询判断条件情况。
                List<RelObjectCondition> relObjectConditionList = iRelObjectConditionService.queryList(RelObjectConditionDto.builder()
                        .objectBizId(variable.getVariableBizId())
                        .build());
                if (!CollectionUtils.isEmpty(relObjectConditionList)) {
                    RelObjectCondition relObjectCondition = relObjectConditionList.get(0);
                    //有关联条件就查询关联条件类型业务ID带入到SQL模板关联查询判断条件情况
                    algorithmDto.getSqlTemplateParamDto().setConditionTypeBizId(relObjectCondition.getConditionTypeBizId());
                }

                //执行 - 算法 - SQL模板
                Result<List<SqlAlgorithmResultDto>> result = sqlAlgorithm(algorithmDto);

                // 设置SQL模板计算结果列表
                dto.setSqlAlgorithmResultDtoList(result.getData());

                // 计算值 - 对sqlAlgorithmResultDtoList中所有calculatedValue求和
                if (result.getData() != null && !result.getData().isEmpty()) {
                    BigDecimal sum = BigDecimal.ZERO;
                    for (SqlAlgorithmResultDto sqlResult : result.getData()) {
                        if (StringUtils.isNotBlank(sqlResult.getCalculatedValue())) {
                            try {
                                BigDecimal value = new BigDecimal(sqlResult.getCalculatedValue());
                                sum = sum.add(value);
                            } catch (NumberFormatException e) {
                                log.warn("计算值格式错误，跳过: {}", sqlResult.getCalculatedValue());
                            }
                        }
                    }
                    dto.setCalculatedValue(sum.toString());
                } else {
                    dto.setCalculatedValue("0"); // 默认值
                }
            }
            variableAlgorithmDtoList.add(dto);
        }
        return Result.success(variableAlgorithmDtoList);
    }

    /**
     * 算法 - 执行 - SQL模板
     * @param algorithmDto
     * @return
     */
    public Result<List<SqlAlgorithmResultDto>> sqlAlgorithm(AlgorithmDto algorithmDto) {
        //SQL模板表唯一业务ID非空
        if (StringUtils.isBlank(algorithmDto.getSqlTemplateBizId())) {
            throw new BusinessException("SQL模板表唯一业务ID不能为空");
        }
        //查询SQL模板对象
        CommissionSqlTemplate commissionSqlTemplate = iCommissionSqlTemplateService.queryOne(algorithmDto.getSqlTemplateBizId());
        if (Objects.isNull(commissionSqlTemplate)) {
            throw new BusinessException("SQL模板对象不存在");
        }
        //SQL模板内容不能为空
        if (StringUtils.isBlank(commissionSqlTemplate.getSqlTemplate())) {
            throw new BusinessException("SQL模板内容SQL语句不能为空");
        }
        try {
            //获取SQL模板内容
            String sqlTemplate = commissionSqlTemplate.getSqlTemplate();

            //构建SQL模板入参参数Map
            Map<String, Object> paramMap = buildParamMap(algorithmDto);

            //执行SQL查询
            Object result = executeParameterizedQuery(sqlTemplate, paramMap);

            //将查询结果转换为 List<SqlAlgorithmResultDto>
            List<SqlAlgorithmResultDto> resultDtoList = convertToSqlAlgorithmResultDto(result);

            return Result.success(resultDtoList);

        } catch (Exception e) {
            log.error("执行SQL模板失败, sqlTemplateBizId: {}", algorithmDto.getSqlTemplateBizId(), e);
            throw new BusinessException("执行SQL模板失败: " + e.getMessage());
        }
    }

    /**
     * 将查询结果转换为 List<SqlAlgorithmResultDto>
     * @param result 原始查询结果
     * @return 转换后的结果列表
     */
    private List<SqlAlgorithmResultDto> convertToSqlAlgorithmResultDto(Object result) {
        List<SqlAlgorithmResultDto> resultDtoList = new ArrayList<>();

        if (result == null) {
            return resultDtoList;
        }

        if (result instanceof List) {
            List<?> resultList = (List<?>) result;
            for (Object item : resultList) {
                SqlAlgorithmResultDto dto = convertSingleResult(item);
                if (dto != null) {
                    resultDtoList.add(dto);
                }
            }
        } else {
            // 单个结果的情况
            SqlAlgorithmResultDto dto = convertSingleResult(result);
            if (dto != null) {
                resultDtoList.add(dto);
            }
        }

        return resultDtoList;
    }

    /**
     * 转换单个查询结果
     * @param item 查询结果项
     * @return 转换后的DTO
     */
    private SqlAlgorithmResultDto convertSingleResult(Object item) {
        SqlAlgorithmResultDto dto = new SqlAlgorithmResultDto();

        if (item instanceof Map) {
            // 处理Map类型的结果（多列）
            Map<String, Object> row = (Map<String, Object>) item;

            // 根据实际数据库列名设置provider和calculatedValue
            if (row.containsKey("provider")) {
                dto.setProvider(row.get("provider") != null ? row.get("provider").toString() : null);
            }
            if (row.containsKey("calculated_value")) {
                dto.setCalculatedValue(row.get("calculated_value") != null ? row.get("calculated_value").toString() : null);
            }
            // 可以添加其他字段的映射...

        } else if (item instanceof String || item instanceof Number) {
            // 处理单列查询结果
            dto.setCalculatedValue(item.toString());
            // 单列情况下，provider可以为空或设置默认值
            dto.setProvider("default_provider");
        }

        return dto;
    }

    /**
     * 构建参数Map
     * @param algorithmDto
     * @return
     */
    private Map<String, Object> buildParamMap(AlgorithmDto algorithmDto) {
        SqlTemplateParamDto sqlTemplateParamDto = algorithmDto.getSqlTemplateParamDto();
        if (Objects.isNull(sqlTemplateParamDto)) {
            throw new BusinessException("SQL模板条件入参参数及参数值对象不能为空");
        }
        Map<String, Object> paramMap = new HashMap<>();
        //添加参数 - 把所有SQL模板涉及到的参数都列出来put进去，SQL模板内容有对应的就替换，没有就不替换，就能达到高效通用性
        //条件类型表唯一业务ID
        paramMap.put("conditionTypeBizId", sqlTemplateParamDto.getConditionTypeBizId());
        //保单号
        paramMap.put("policyNo", sqlTemplateParamDto.getPolicyNo());
        //产品代码
        paramMap.put("productCode", sqlTemplateParamDto.getProductCode());
        //保险上架产品唯一业务ID
        paramMap.put("productLaunchBizId", sqlTemplateParamDto.getProductBizId());
        //供款年期（产品有5年期的有10年期等）(总期数)
        paramMap.put("term", sqlTemplateParamDto.getTerm());
        //期数（数字代表第几年）
        paramMap.put("issueNumber", sqlTemplateParamDto.getIssueNumber());
        //保单绑定转介人业务ID（销售业务员，客户端用户表唯一业务ID，也可以单独用作客户端用户ID非绑定保单使用）
        paramMap.put("brokerBizId", sqlTemplateParamDto.getBrokerBizId());
        //获得积分的用户（客户端用户表唯一业务ID）
        paramMap.put("clientUserBizId", sqlTemplateParamDto.getClientUserBizId());
        //保单来佣业务id
        paramMap.put("commissionBizId", sqlTemplateParamDto.getCommissionBizId());
        //保单发佣批次ID
        paramMap.put("batchBizId", sqlTemplateParamDto.getBatchBizId());
        return paramMap;
    }

    /**
     * 执行参数化查询
     * @param sqlTemplate
     * @param paramMap
     * @return
     */
    private Object executeParameterizedQuery(String sqlTemplate, Map<String, Object> paramMap) {
        //使用NamedParameterJdbcTemplate执行参数化查询
        NamedParameterJdbcTemplate jdbcTemplate = getJdbcTemplate();

        //判断SQL类型：查询还是更新
        if (isSelectQuery(sqlTemplate)) {
            //查询操作
            List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sqlTemplate, paramMap);

            //根据业务需求处理结果
            if (resultList.isEmpty()) {
                return null;
            } else if (resultList.size() == 1) {
                Map<String, Object> singleResult = resultList.get(0);
                //如果只有一列，直接返回值
                if (singleResult.size() == 1) {
                    return singleResult.values().iterator().next();
                }
                return singleResult;
            } else {
                return resultList;
            }
        } else {
            //更新操作（INSERT/UPDATE/DELETE）
            int affectedRows = jdbcTemplate.update(sqlTemplate, paramMap);
            return affectedRows;
        }
    }

    /**
     * 判断是否为SELECT查询
     * @param sql
     * @return
     */
    private boolean isSelectQuery(String sql) {
        String trimmedSql = sql.trim().toLowerCase();
        return trimmedSql.startsWith("select");
    }

    /**
     * 校验算法DTO入参
     * @param algorithmDto
     * @return
     */
    public Result checkAlgorithmDto(AlgorithmDto algorithmDto) {
//        if (Objects.isNull(algorithmDto)) {
//            throw new BusinessException("算法DTO入参对象不能为空");
//        }
//        if (StringUtils.isBlank(algorithmDto.getFormulaBizId())) {
//            throw new BusinessException("公式配置表唯一业务ID不能为空");
//        }
//        if (CollectionUtils.isEmpty(algorithmDto.getVariableBizIdList())) {
//            throw new BusinessException("变量表业务ID集合列表不能为空");
//        }
        return Result.success();
    }
}
