package com.b3.rbl.compensation.service;

import com.b3.rbl.compensation.model.jbpm.*;
import com.b3.rbl.compensation.util.ObjectUtil;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.ObjectWriter;

import lombok.extern.log4j.Log4j2;

import org.apache.commons.math3.analysis.function.Min;
import org.dmg.pmml.time_series.MA;
import org.drools.core.command.runtime.BatchExecutionCommandImpl;
import org.hibernate.transform.Transformers;
import org.kie.api.KieServices;
import org.kie.api.command.BatchExecutionCommand;
import org.kie.api.command.Command;
import org.kie.api.command.KieCommands;
import org.kie.api.runtime.ExecutionResults;
import org.kie.server.api.marshalling.MarshallingFormat;
import org.kie.server.api.model.KieContainerResource;
import org.kie.server.api.model.KieContainerResourceList;
import org.kie.server.api.model.KieServiceResponse;
import org.kie.server.api.model.ServiceResponse;
import org.kie.server.api.model.definition.ProcessDefinition;
import org.kie.server.api.model.instance.ProcessInstance;
import org.kie.server.api.model.instance.ProcessInstanceCustomVars;
import org.kie.server.api.model.instance.ProcessInstanceList;
import org.kie.server.api.model.instance.VariableInstance;
import org.kie.server.client.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.stereotype.Service;


import java.lang.reflect.Array;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Date;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;


import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;
import javax.transaction.Transactional;
import com.b3.rbl.compensation.service.JbpmService;


@Log4j2
@Service
public class MonthlyBonusService {
    @Autowired
    JbpmService jbpmService;


    @Value("${kie.server.container_id}")
    private String CONTAINERID;

    @Value("${kie.server.process_idMonthly}")
    private String PROCESSID;

    @Value("${kie.server.url}")
    private String URL;

    @Value("${kie.server.username}")
    private String USERNAME;

    @Value("${kie.server.password}")
    private String PASSWORD;

    private MarshallingFormat FORMAT = MarshallingFormat.JSON;

    private KieServicesConfiguration conf;
    private KieServicesClient kieServicesClient;

    

    @Autowired
    private EntityManager apiDatasource;

    private List<Map<String, Object>> mapDataToDB = new ArrayList<>(Arrays.asList(new HashMap<String, Object>() {
        {          
            put("MonthlyBonus", new HashMap<String, String>() {
                {
                    put("agentCode", "agent_code");
                    put("agentTitle", "agent_title");
                    put("agentName", "agent_Name");
                    put("agentPosition", "agent_level_code_detail");
                    put("benefitCode", "benefit_code");
                    put("persistencyRate", "persistency_rate");
                    put("persistencyController", "persistency_controller");
                    put("fycAmount", "fyc");
                    put("fycProduction", "fyc_production");
                    put("bonusRate", "benefit_rate");
                    put("bonus", "benefit_amount");
                    put("incomeTax", "income_tax");
                    put("deductTax", "deduct_tax");
                    put("calculationDate", "create_date");
                    put("policyNo", "policy_no");
                    put("description", "description");
                    
                    put("policyIssuesDate", "policy_issues_date");
                    put("policyProductType", "policy_product_type");
                    put("policyStatusCode", "policy_status_code");
                    put("policyStatusName", "policy_status_name");
                    put("premiumPeriod", "premium_period");
                    put("premiumYear", "premium_year");
                    put("transactionDate", "transaction_date");
                    put("transactionType", "transaction_type");
                    put("detailpolicyNo", "policy_no");
                    put("detailFycAmount", "fyc");
                    put("detailFycProduction", "fyc_production");
                   
                }
            });
        }
    }));

    public void initialize() {

        conf = KieServicesFactory.newRestConfiguration(URL, USERNAME, PASSWORD, 60000);
        conf.setMarshallingFormat(FORMAT);

        kieServicesClient = KieServicesFactory.newKieServicesClient(conf);

        log.info(kieServicesClient);
    }

   

    public void executeBusinessProcess(AgentDetailData agentDetail) throws JsonProcessingException {
        // System.out.println("== Start Business Processes ==");
        log.info("== Start Business Processes ==");
        log.info("LOGGG TYPEE:::", PROCESSID);
        ProcessServicesClient processServicesClient = kieServicesClient.getServicesClient(ProcessServicesClient.class);

        Map<String, Object> data = new HashMap<String, Object>();

        Class<?> cls = AgentDetailData.class;
        String classPackage = cls.getPackage().getName().substring(0, 24).concat(cls.getSimpleName());
        // System.out.println("Class : " + classPackage);
        log.info("Class : " + classPackage);
       
        Map<String, Object> stringMap = new HashMap<String, Object>();
        stringMap.put(classPackage, agentDetail);
        data.put("data", stringMap);

        Long process = processServicesClient.startProcess(CONTAINERID, PROCESSID, data);

        // System.out.println("== End Business Processes ==");
        log.info("== End Business Processes ==");

    }

    @Transactional
    public void getDataMonthlyBonus(Date startdate, Date enddate) {

        String sql = "SELECT * from compensation.f_rbl_monthly_bonus_detail(:start_date, :end_date) where policy_status_name = 'Active Member'";
        String benefitType = "MonthlyBonus";

        List<Map<String, Object>> data = new ArrayList<>();
        List<Map<String, Object>> query = this.apiDatasource.createNativeQuery(sql)
                .setParameter("start_date", startdate)
                .setParameter("end_date", enddate)
                .unwrap(org.hibernate.query.Query.class)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
        data = ObjectUtil.columnToCamelCase(query);

        log.info("res {}", data.size());
        log.info("resData {}", data);

        Map<String, List<Map<String, Object>>> groupedData = new HashMap<>();

        
        for (Map<String, Object> recordData : data) {
            String agentCode = (String) recordData.get("agentCode");
            if (!groupedData.containsKey(agentCode)) {
                groupedData.put(agentCode, new ArrayList<>());
            }
            groupedData.get(agentCode).add(recordData);
        }
 
        for (Map.Entry<String, List<Map<String, Object>>> entry : groupedData.entrySet()) {
            try {
                String agentCode = entry.getKey();
                List<Map<String, Object>> agentRecords = entry.getValue();
                log.info("Agent Code After group::: {}", agentCode);
                AgentDetailData agentDetailData = setupMonthlyPayload(agentRecords, benefitType);
                log.info("Size Aggent Data Log::{}",agentDetailData.getAgents().size());
                log.info("Aggent Data Log::{}",agentDetailData.getAgents());
                executeBusinessProcess(agentDetailData);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
        }
}



    public AgentDetailData setupMonthlyPayload(List<Map<String, Object>> data, String benefitType) {
        AgentDetailData agentData = new AgentDetailData();
        List<AgentResult> agentResultList = new ArrayList<>();
        List<AgentDetail> agentDetailList = new ArrayList<>();
        int count = 0;
        for (Map<String, Object> map : data) {
            // log.info("{} map {} {}",count, map.entrySet(), map.get("paidDate").toString());
            AgentResult agentResult = new AgentResult();
            AgentDetail agentDetail = new AgentDetail();
           

            if (benefitType.equals("MonthlyBonus")) {
                agentResult.setStartDate((Date) map.get("startDate"));
                agentResult.setEndDate((Date) map.get("endDate"));
                agentResult.setAgentCode((String) map.get("agentCode"));
                agentResult.setAgentName((String) map.get("agentName"));
                agentResult.setAgentPosition((String) map.get("agentLevelCodeDetail"));
                agentResult.setPolicyNo((String) map.get("policyNo"));
                agentResult.setFycAmount(Double.valueOf(String.valueOf(map.get("fyc"))));
                agentResult.setFycProduction(Double.valueOf(String.valueOf(map.get("fycProduction"))));
                // agentResult.setTransactionEffectiveDate((Date) map.get("transactionEffectiveDate"));
                // agentResult.setPolicyStatusCode((String) map.get("policyStatusCode"));
                // agentResult.setPolicyStatusName((String) map.get("policyStatusName"));
                agentResult.setAgentTitle((String) map.get("agentTitle"));
                agentResult.setBenefitCode((String) map.get("benefitCode"));
                //agentResult.setPercistencyController(Double.valueOf(String.valueOf(map.get("percistencyController"))));
                //agentResult.setPersistencyRate(Double.valueOf(String.valueOf(map.get("persistencyRate"))));
                agentResult.setBenefitType(benefitType);


                //FOR AGENT DETAIL
                agentDetail.setAgentCode((String) map.get("agentCode"));
                //agentResult.setAgentName((String) map.get("agentName"));
                //agentDetail.setAgentPosition((String) map.get("agentLevelCodeDetail"));
                agentDetail.setPolicyNo((String) map.get("policyNo"));
                agentDetail.setFycAmount(Double.valueOf(String.valueOf(map.get("fyc"))));
                agentDetail.setFycProduction(Double.valueOf(String.valueOf(map.get("fycProduction"))));
                agentDetail.setPolicyIssuesDate((Date) map.get("policyIssueDate"));
                agentDetail.setTransactionDate((Date) map.get("transactionDate"));
                agentDetail.setTransactionType((String) map.get("transactionType"));
                agentDetail.setPremiumPeriod((Integer.valueOf(String.valueOf(map.get("premiumPeriod")))));
                agentDetail.setPremiumYear((String) map.get("premiumYear"));
                agentDetail.setPolicyProductType((String) map.get("policyProductType"));
                agentDetail.setPolicyStatusCode((String) map.get("policyStatusCode"));
                agentDetail.setPolicyStatusCode((String) map.get("policyStatusCode"));
                agentDetail.setPolicyStatusName((String) map.get("policyStatusName"));

                
            }
            
            log.info("obj : {}", agentResult.getBenefitType());
            agentDetailList.add(agentDetail);
            agentResult.setAgentDetail(agentDetailList);
            agentResultList.add(agentResult);
            count++;
        }
        agentData.setAgents(agentResultList);
        //agentData.setAgentDetails(agentDetailList);
        // log.info("agents {}", agentResultList.get(0).getBenefitType());
        return agentData;
    }


    @Modifying
    @Transactional
    public void setupMonthlyData(AgentDetailData data) {
        log.info("data : {}", data.getAgents().get(0).getBenefitType());
        log.info("Agent Size:: {}", data.getAgents().size());
        String sql = "INSERT INTO compensation.";
        Integer count = 0;
        for (AgentResult agent : data.getAgents()) {
            
            if (agent.getBenefitType().equals("MonthlyBonus")) {

                Map<String, String> mp = (Map<String, String>) mapDataToDB.get(0).get("MonthlyBonus");

                if (count == 0) {
                    sql += "rbl_benefit_result (" 
                        + mp.get("agentCode") + ","
                        + mp.get("agentTitle") + "," 
                        + mp.get("agentName") + "," 
                        + mp.get("agentPosition") + ","
                        + mp.get("benefitCode") + "," 
                        + mp.get("persistencyRate") + "," 
                        + mp.get("persistencyController") + ","
                        + mp.get("fycAmount") + "," 
                        + mp.get("fycProduction") + "," 
                        + mp.get("bonusRate") + ","
                        + mp.get("bonus") + ","
                        + mp.get("incomeTax") + ","
                        + mp.get("deductTax") + ","
                        + mp.get("calculationDate") + ","
                        + mp.get("description")
            
                        + ") VALUES ";
                       

                        sql += "(" 
                        + concatSingleQuote(agent.getAgentCode().toString()) + ","
                        + concatSingleQuote(agent.getAgentTitle().toString()) + ","
                        + concatSingleQuote(agent.getAgentName().toString()) + "," 
                        + concatSingleQuote(agent.getAgentPosition().toString()) + ","
                        + concatSingleQuote(agent.getBenefitCode().toString()) + "," 
                        + agent.getPersistencyRate() + "," 
                        + agent.getPercistencyController() + ","
                        + jbpmService.roundingDecimalScale(agent.getFycAmount()) + "," 
                        + jbpmService.roundingDecimalScale(agent.getFycProduction()) + "," 
                        + agent.getBonusRate() + "," 
                        + jbpmService.roundingDecimalScale(agent.getBonus()) + "," 
                        + jbpmService.roundingDecimalScale(agent.getIncomeTax()) + "," 
                        + jbpmService.roundingDecimalScale(agent.getDeductTax()) + ","
                        + concatSingleQuote(java.time.LocalDate.now().toString()) + ","
                        + concatSingleQuote(agent.getDescription())

                        // + concatSingleQuote(agent.getStartDate().toString()) + "," 
                        // + concatSingleQuote(agent.getEndDate().toString()) + "," 
                        + "),";
                }
                else{
                    sql += "(" 
                    + agent.getAgentCode() + ","
                    + agent.getAgentTitle() + ","
                    + agent.getAgentName() + "," 
                    + agent.getAgentPosition() + ","
                    + agent.getBenefitCode() + "," 
                    + agent.getPersistencyRate() + "," 
                    + agent.getPercistencyController() + ","
                    + jbpmService.roundingDecimalScale(agent.getFycAmount()) + "," 
                    + jbpmService.roundingDecimalScale(agent.getFycProduction()) + "," 
                    + agent.getBonusRate() + "," 
                    + jbpmService.roundingDecimalScale(agent.getBonus()) + "," 
                    + jbpmService.roundingDecimalScale(agent.getIncomeTax()) + "," 
                    + jbpmService.roundingDecimalScale(agent.getDeductTax()) + ","
                    + concatSingleQuote(java.time.LocalDate.now().toString()) + ","
                    + concatSingleQuote(agent.getDescription())
                    + "),";
                }
                       
            }
            count++;
        }
        sql = sql.substring(0,sql.length()-1);
        log.info("sql {}",sql );
        saveData(sql);
        // selectLastIdResult();

        String sqlLastId = "SELECT MAX(Id) FROM compensation.rbl_benefit_result where benefit_code = 'FBM'";
        log.info("SQL lastId::", sqlLastId );
        List<Map<String, Object>> selectLastId = new ArrayList();
        List<Map<String, Object>> queryResult =  this.apiDatasource.createNativeQuery(sqlLastId)
        .unwrap(org.hibernate.query.Query.class)
        .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
        selectLastId = ObjectUtil.columnToCamelCase(queryResult);
        setupMonthlyDetailData(data, selectLastId.get(0).get("max").toString());

        log.info("lastd {}", selectLastId.get(0).get("max"));
        

    }


    

    @Modifying
    @Transactional
    public void setupMonthlyDetailData(AgentDetailData data, String benefitId) {
        String sql = "INSERT INTO compensation.";
        Integer count = 0;
        for (AgentResult agent : data.getAgents()) {
            for (AgentDetail detail : agent.getAgentDetail()) {
            if (agent.getBenefitType().equals("MonthlyBonus")) {
    
                Map<String, String> mp = (Map<String, String>) mapDataToDB.get(0).get("MonthlyBonus");
                if (count == 0) {
                sql += "rbl_benefit_detail (" 
                + mp.get("policyIssuesDate") + ","
                + mp.get("policyProductType") + "," 
                + mp.get("policyStatusCode") + ","
                + mp.get("policyStatusName") + "," 
                + mp.get("premiumPeriod") + "," 
                + mp.get("premiumYear") + ","
                + mp.get("transactionDate") + "," 
                + mp.get("transactionType") + "," 
                + mp.get("agentCode") + ","
                + mp.get("detailpolicyNo") + ","
                + mp.get("detailFycAmount") + ","
                + mp.get("detailFycProduction") + ","
                + "benefit_result_id" 
                + ") VALUES ";
               

                sql += "(" 
                
                + concatSingleQuote(detail.getPolicyIssuesDate().toString()) + ","                   
                + concatSingleQuote(detail.getPolicyProductType()) + ","                   
                + concatSingleQuote(detail.getPolicyStatusCode()) + ","                   
                + concatSingleQuote(detail.getPolicyStatusName()) + ","                   
                + detail.getPremiumPeriod() + ","                   
                + concatSingleQuote(detail.getPremiumYear()) + ","                   
                + concatSingleQuote(detail.getTransactionDate().toString()) + ","                   
                + concatSingleQuote(detail.getTransactionType()) + ","
                + concatSingleQuote(detail.getAgentCode().toString()) + ","                  
                + concatSingleQuote(detail.getPolicyNo()) + ","                   
                + jbpmService.roundingDecimalScale(detail.getFycAmount()) + ","                   
                + jbpmService.roundingDecimalScale(detail.getFycProduction()) + ","  
                + concatSingleQuote(benefitId)              

                + "),";
            }
            else{
                sql += "(" 
                + concatSingleQuote(detail.getPolicyIssuesDate().toString()) + ","                   
                + concatSingleQuote(detail.getPolicyProductType()) + ","                   
                + concatSingleQuote(detail.getPolicyStatusCode()) + ","                   
                + concatSingleQuote(detail.getPolicyStatusName()) + ","                   
                + detail.getPremiumPeriod() + ","                   
                + concatSingleQuote(detail.getPremiumYear()) + ","                   
                + concatSingleQuote(detail.getTransactionDate().toString()) + ","                   
                + concatSingleQuote(detail.getTransactionType()) + ","
                + concatSingleQuote(detail.getAgentCode().toString()) + ","                  
                + concatSingleQuote(detail.getPolicyNo()) + ","                   
                + jbpmService.roundingDecimalScale(detail.getFycAmount()) + ","                   
                + jbpmService.roundingDecimalScale(detail.getFycProduction()) + ","  
                + concatSingleQuote(benefitId)             
                + "),";
            }
            }
               
            count++;
        }
            }
        sql = sql.substring(0, sql.length() - 1);
        log.info("sql for detail {}", sql);
        saveData(sql);
    }


    @Modifying
    @Transactional
    public void saveData(String sql){
        try {
            this.apiDatasource.createNativeQuery(sql).executeUpdate();
        } catch (Exception e) {
            log.info("err {} {}",e.getMessage(),e);
            // TODO: handle exception
        }
    }
    public String convertDateFormat(String strDate){
          // Format of the original date string
        SimpleDateFormat originalFormat = new SimpleDateFormat("dd/MM/yyyy");

        // Desired format
        SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd");

        String formattedDate = "";

        try {
            // Parse the original date string to a Date object
            java.util.Date date = originalFormat.parse(strDate);

            // Format the Date object to the desired format
            formattedDate = targetFormat.format(date);

            // Print the formatted date
           log.info(formattedDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return "'"+formattedDate+"'";
    }

    public String concatSingleQuote(String str){
        return "'"+str+"'";
    }





}