package com.ecep.contract.service.tasker;

import static com.ecep.contract.util.ExcelUtils.setCellValue;
import static com.ecep.contract.util.ExcelUtils.getCellValue;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.time.LocalDate;
import java.util.Comparator;
import java.util.List;
import java.util.Objects;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.hibernate.Hibernate;
import org.springframework.util.StringUtils;

import com.ecep.contract.CustomerFileType;
import com.ecep.contract.MessageHolder;
import com.ecep.contract.SpringApp;
import com.ecep.contract.cloud.tyc.CloudTycService;
import com.ecep.contract.ds.customer.service.CompanyCustomerEvaluationFormFileService;
import com.ecep.contract.ds.customer.service.CompanyCustomerFileService;
import com.ecep.contract.ds.customer.service.CustomerService;
import com.ecep.contract.ds.other.model.CloudTyc;
import com.ecep.contract.ds.company.model.Company;
import com.ecep.contract.ds.customer.model.CompanyCustomer;
import com.ecep.contract.ds.customer.model.CompanyCustomerEvaluationFormFile;
import com.ecep.contract.ds.customer.model.CompanyCustomerFile;
import com.ecep.contract.ui.Tasker;
import com.ecep.contract.util.CompanyUtils;
import com.ecep.contract.util.ExcelUtils;
import com.fasterxml.jackson.databind.JsonNode;

public class CompanyCustomerEvaluationFormUpdateTask extends Tasker<Object> implements WebSocketServerTasker {
    private CompanyCustomer customer;

    @Override
    public void init(JsonNode argsNode) {
        int customerId = argsNode.get(0).asInt();
        customer = getCachedBean(CustomerService.class).getById(customerId);
    }

    CompanyCustomerFileService getCompanyCustomerFileService() {
        return getCachedBean(CompanyCustomerFileService.class);
    }

    CompanyCustomerEvaluationFormFileService getCompanyCustomerEvaluationFormFileService() {
        return getCachedBean(CompanyCustomerEvaluationFormFileService.class);
    }

    @Override
    protected Object execute(MessageHolder holder) throws Exception {
        updateEvaluationForm(holder);
        return null;
    }

    public void updateEvaluationForm(MessageHolder holder) {
        if (!StringUtils.hasText(customer.getPath())) {
            holder.error("供应商目录未设置，请先设置供应商目录");
            return;
        }
        File template = getCompanyCustomerFileService().getEvaluationFormTemplate();
        if (template == null) {
            holder.error("评价表模板文件未设置，请先设置评价表模板文件");
            return;
        }
        if (!template.exists()) {
            holder.error("评价表模板文件 " + template.getAbsolutePath() + " 不存在，请检查");
            return;
        }

        updateProgress(1, 10);
        File dir = new File(customer.getPath());
        String template_file_name = template.getName();
        File destFile = new File(dir, template_file_name);

        if (destFile.exists()) {
            holder.info("表单文件已经存在，" + destFile.getName());
            try (
                    InputStream inp = new FileInputStream(destFile);
                    Workbook wb = WorkbookFactory.create(inp)) {
                updateEvaluationForm(wb, destFile, holder);
                holder.info("评价表已更新");
            } catch (Exception e) {
                holder.error(e.getMessage());
            }
        } else {
            holder.info("根据模板 " + template_file_name + " 创建表单 " + destFile.getName());
            try (InputStream inp = new FileInputStream(template); Workbook wb = WorkbookFactory.create(inp)) {
                updateEvaluationForm(wb, destFile, holder);
                holder.info("评价表已创建");
                CompanyCustomerFile customerFile = new CompanyCustomerFile();
                customerFile.setCustomer(customer);
                customerFile.setFilePath(destFile.getAbsolutePath());
                customerFile.setType(CustomerFileType.General);
                getCompanyCustomerFileService().save(customerFile);
            } catch (Exception e) {
                holder.error(e.getMessage());
            }
        }
        updateProgress(10, 10);
    }

    /**
     * 更新客户评估表，依据模板创建，如果已经存在生成的文件，则更新评估表
     *
     * @param wb       work book
     * @param destFile 目标文件
     */
    public void updateEvaluationForm(Workbook wb, File destFile, MessageHolder holder) throws IOException {
        updateProgress(2, 10);
        Company company = customer.getCompany();
        if (!Hibernate.isInitialized(company)) {
            company = getCompanyService().getById(company.getId());
            customer.setCompany(company);
        }
        Sheet sheet = wb.getSheetAt(0);
        updateSheet(company, sheet, holder.sub(" - "));
        updateProgress(8, 10);
        // 输出到文件
        try (OutputStream fileOut = new FileOutputStream(destFile)) {
            wb.write(fileOut);
        } catch (FileNotFoundException e) {
            holder.error("写评估表时发生文件错误，请检查评估表是否被打开中:" + e.getMessage());
        }
    }

    private void updateSheet(Company company, Sheet sheet, MessageHolder holder) {
        String customerId = "客户编号：" + CompanyUtils.formatCompanyVendorId(customer.getId());
        String currentCustomerId = ExcelUtils.getCellValue(sheet, "B3");
        if (!Objects.equals(customerId, currentCustomerId)) {
            setCellValue(sheet, "B3", customerId);
            holder.info("更新客户编号为：" + CompanyUtils.formatCompanyVendorId(customer.getId()));
        }

        String customerName = "客户名称：" + company.getName();
        String currentCustomerName = ExcelUtils.getCellValue(sheet, "B4");
        if (!Objects.equals(customerName, currentCustomerName)) {
            setCellValue(sheet, "B4", customerName);
            holder.info("更新客户名称为：" + company.getName());
        }

        LocalDate suggestDate = getCompanyCustomerFileService().getNextSignDate(customer, holder);
        if (suggestDate == null) {
            suggestDate = LocalDate.now();
            holder.debug("未获取到推荐评定日期，使用当前日期：" + suggestDate);
        } else {
            holder.debug("获取到推荐评定日期：" + suggestDate);
        }
        String evalDate = "评定时间：" + suggestDate;
        String currentEvalDate = ExcelUtils.getCellValue(sheet, "H3");
        if (!Objects.equals(evalDate, currentEvalDate)) {
            setCellValue(sheet, "H3", evalDate);
            holder.info("更新评定时间为：" + suggestDate);
        }

        setCellValue(sheet, "H4", "统一社会信用代码：");
        
        if (!Objects.equals(company.getUniscid(), ExcelUtils.getCellValue(sheet, "H5"))) {
            setCellValue(sheet, "H5", company.getUniscid());
            holder.info("更新统一社会信用代码为：" + company.getUniscid());
        }

        // 注册所属地
        String district = "注册所属地：" + company.getDistrict();
        if (!Objects.equals(district, ExcelUtils.getCellValue(sheet, "B5"))) {
            setCellValue(sheet, "B5", district);
            holder.info("更新注册所属地为：" + company.getDistrict());
        }

        // 经营状态
        String entStatus = "经营状态：" + company.getEntStatus();
        if (!Objects.equals(entStatus, ExcelUtils.getCellValue(sheet, "D6"))) {
            setCellValue(sheet, "D6", entStatus);
            holder.info("更新经营状态为：" + company.getEntStatus());
        }

        // 成立日期
        String setupDate = "成立日期：" + company.getSetupDate();
        if (!Objects.equals(setupDate, ExcelUtils.getCellValue(sheet, "H6"))) {
            setCellValue(sheet, "H6", setupDate);
            holder.info("更新成立日期为：" + company.getSetupDate());
        }

        // 所属行业
        String industry = "所属行业：" + company.getIndustry();
        if (!Objects.equals(industry, ExcelUtils.getCellValue(sheet, "D7"))) {
            setCellValue(sheet, "D7", industry);
            holder.info("更新所属行业为：" + company.getIndustry());
        }

        String registeredCapital = "注册资金：" + company.getRegisteredCapital() + " " + company.getRegisteredCapitalCurrency();
        if (!Objects.equals(registeredCapital, ExcelUtils.getCellValue(sheet, "D8"))) {
            setCellValue(sheet, "D8", registeredCapital);
            holder.info("更新注册资金为：" + registeredCapital);
        }

        // 企业类型
        String entType = "企业类型：" + company.getEntType();
        if (!Objects.equals(entType, ExcelUtils.getCellValue(sheet, "H10"))) {
            setCellValue(sheet, "H10", entType);
            holder.info("更新企业类型为：" + company.getEntType());
        }

        // 天眼评分
        CloudTycService cloudTycService = SpringApp.getBean(CloudTycService.class);
        CloudTyc cloudTyc = cloudTycService.getOrCreateCloudTyc(company);
        String tycScore = "天眼评分：" + (cloudTyc.getScore() > 0 ? cloudTyc.getScore() : "");
        if (!Objects.equals(tycScore, ExcelUtils.getCellValue(sheet, "D10"))) {
            setCellValue(sheet, "D10", tycScore);
            if (cloudTyc.getScore() > 0) {
                holder.info("更新天眼评分为：" + cloudTyc.getScore());
            } else {
                holder.debug("天眼评分未设置或为0");
            }
        }

        // 检索评估表
        holder.debug("开始检索客户评价表文件");
        List<CompanyCustomerFile> customerFiles = getCompanyCustomerFileService().findAllByCustomerAndType(customer,
                CustomerFileType.EvaluationForm);
        holder.debug("共检索到 " + customerFiles.size() + " 个客户评价表文件");

        List<CompanyCustomerEvaluationFormFile> filteredList = customerFiles.stream().filter(file -> {
            return file.getSignDate() != null && file.isValid();
        })
                .sorted(Comparator.comparing(CompanyCustomerFile::getSignDate))
                .map(getCompanyCustomerEvaluationFormFileService()::findByCustomerFile)
                .toList();
        holder.debug("有效评价表数量：" + filteredList.size());

        if (filteredList.isEmpty()) {
            if (!Objects.equals("首次评价", ExcelUtils.getCellValue(sheet, "C40"))) {
                setCellValue(sheet, "C40", "首次评价");
                holder.info("设置客户为首次评价状态");
            }
            try {
                sheet.addMergedRegion(CellRangeAddress.valueOf("C40:K40"));
            } catch (Exception ignored) {
                holder.debug("合并单元格C40:K40失败，可能已合并");
            }
        } else {
            holder.info("客户有 " + filteredList.size() + " 条有效评价表记录");
            if (!Objects.equals("评价日期", ExcelUtils.getCellValue(sheet, "C40"))) {
                setCellValue(sheet, "C40", "评价日期");
                try {
                    sheet.addMergedRegion(CellRangeAddress.valueOf("C40:D40"));
                } catch (Exception ignored) {
                    holder.debug("合并单元格C40:D40失败，可能已合并");
                }
            }
            if (!Objects.equals("经济指标", ExcelUtils.getCellValue(sheet, "E40"))) {
                setCellValue(sheet, "E40", "经济指标");
            }
            if (!Objects.equals("综合指标", ExcelUtils.getCellValue(sheet, "F40"))) {
                setCellValue(sheet, "F40", "综合指标");
            }
            if (!Objects.equals("资信等级", ExcelUtils.getCellValue(sheet, "G40"))) {
                setCellValue(sheet, "G40", "资信等级");
            }
            String[] CreditLevelTitles = new String[] { "-", "差★", " 一般★★", " 较好★★★", " 好★★★★", " " };
            int baseRow = 40;
            for (CompanyCustomerEvaluationFormFile form : filteredList) {
                baseRow++;
                CompanyCustomerFile customerFile = form.getCustomerFile();
                if (!Hibernate.isInitialized(customerFile)) {
                    customerFile = getCompanyCustomerFileService().findById(customerFile.getId());
                    holder.debug("延迟加载评价表文件信息，文件ID：" + customerFile.getId());
                }
                setCellValue(sheet, baseRow, 2, String.valueOf(customerFile.getSignDate()));
                setCellValue(sheet, baseRow, 4, form.getCatalog());
                setCellValue(sheet, baseRow, 5, form.getLevel());
                if (form.getCreditLevel() == null) {
                    setCellValue(sheet, baseRow, 6, "-");
                } else {
                    setCellValue(sheet, baseRow, 6, CreditLevelTitles[form.getCreditLevel()]);
                }
                try {
                    sheet.addMergedRegion(new CellRangeAddress(baseRow, baseRow, 2, 3));
                } catch (Exception ignored) {
                    holder.debug("合并单元格第" + baseRow + "行的2-3列失败，可能已合并");
                }
            }
            holder.info("已成功更新所有评价表记录到Excel中");
        }
    }
}
