package com.xdap.self_development.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.definesys.mpaas.query.db.PageQueryResult;
import com.xdap.api.moudle.department.pojo.entity.XdapDepartments;
import com.xdap.api.moudle.user.pojo.XdapUsers;
import com.xdap.self_development.common.BusinessDatabase;
import com.xdap.self_development.common.MyResponse;
import com.xdap.self_development.controller.form.*;
import com.xdap.self_development.domain.pojo.ParameterPojo;
import com.xdap.self_development.domain.pojo.RevisionRecordPojo;
import com.xdap.self_development.domain.pojo.TemplateAndParameterPojo;
import com.xdap.self_development.domain.pojo.TemplatePojo;
import com.xdap.self_development.exception.CommonException;
import com.xdap.self_development.listener.SheetDataListener;
import com.xdap.self_development.service.TemplateService;
import com.xdap.self_development.utils.DescriptionWriteUtils;
import com.xdap.self_development.utils.FormulaValidatorUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 模板服务实现类
 *
 * @version 1.0
 * @since 2025
 */
@Service
@Slf4j
public class TemplateServiceImpl implements TemplateService {
    @Resource
    private BusinessDatabase bd;

    /**
     * 根据条件获取模板
     *
     * @param form 模板页面表单
     * @return 模板分页查询结果
     */
    @Override
    public PageQueryResult<TemplatePojo> getTemplateByCondition(TemplatePageForm form) {
        PageQueryResult<TemplatePojo> result = bd.getBusinessDatabase()
                .viewQueryMode(true)
                .view("getLatestTemplate")
                .like("template_name", form.getTemplateName())
                .doPageQuery(form.getPageNumber(), form.getPageSize(), TemplatePojo.class);
        result.getResult().forEach(templatePojo -> {
            if (Objects.equals(templatePojo.getCreateBy(), "超级管理员")) {
                templatePojo.setCreateBy("超级管理员");
                return;
            }
            XdapUsers user = bd.getSystemDatabase()
                    .eq("id", templatePojo.getCreateBy())
                    .doQueryFirst(XdapUsers.class);
            if (user == null || user.getUsername() == null) {
                templatePojo.setCreateBy("未知用户-" + templatePojo.getCreateBy());
            } else {
                templatePojo.setCreateBy(user.getUsername());
            }
        });
        return result;

    }

    /**
     * 插入模板
     *
     * @param form 插入模板表单
     */
    @Override
    public void insertTemplate(InsertTemplateForm form) {
        TemplatePojo template = bd.getBusinessDatabase()
                .viewQueryMode(true)
                .view("getNewTemplate")
                .eq("template_name", form.getTemplateName())
                .doQueryFirst(TemplatePojo.class);

        // 如果有同名称模板
        if (template != null) {
            throw new CommonException("已有同名版本，不允许新建模板");
        }

        // 创建新模板
        TemplatePojo newTemplate = new TemplatePojo(
                form.getTemplateName(), 1, "DRAFT", Timestamp.valueOf(LocalDateTime.now()),
                null, form.getUserId(), System.currentTimeMillis() / 1000, 0
        );

        bd.getBusinessDatabase().doInsert(newTemplate);
    }

    /**
     * 更新模板
     *
     * @param form 更新模板表单
     */
    @Override
    @Transactional
    public void updateTemplate(UpdateTemplateForm form) {
        TemplatePojo templateName = bd.getBusinessDatabase()
                .rowid("id", form.getTemplateRowId())
                .doQueryFirst(TemplatePojo.class);

        if (templateName == null) {
            throw new CommonException("没有找到模板");
        }

        List<TemplatePojo> templates = bd.getBusinessDatabase()
                .eq("template_name", templateName.getTemplateName())
                .doQuery(TemplatePojo.class);

        if (!templates.isEmpty()) {
            throw new CommonException("已有同名模板，不允许修改模板名称");
        }

        bd.getBusinessDatabase()
                .eq("template_name", templateName)
                .update("template_name", form.getTemplateName())
                .doUpdate(TemplatePojo.class);
    }

    /**
     * 导入模板
     *
     * @param file 模板文件
     * @param templateRowId 模板行ID
     * @param userId 用户ID
     * @return 操作响应结果
     */
    @Override
    @Transactional
    public MyResponse importTemplate(MultipartFile file, String templateRowId, String userId) {
        // 验证文件是否为工作表
        if (file == null || file.isEmpty()) {
            throw new CommonException("文件不能为空");
        }

        String filename = file.getOriginalFilename();
        if (filename == null ||
                !(filename.toLowerCase().endsWith(".xls") ||
                        filename.toLowerCase().endsWith(".xlsx"))) {
            throw new CommonException("只支持 .xls 或 .xlsx 格式的文件");
        }
        MyResponse problems =
                new MyResponse(false, new ArrayList<>(), new StringBuilder("已完成导入：无"));

        XdapDepartments targetDepartment = bd.getSystemDatabase()
                .eq("name", "研发总院")
                .doQueryFirst(XdapDepartments.class);

        if (targetDepartment == null) {
            throw new CommonException("找不到部门‘研发总院’");
        }

        List<String> subDepartments = bd.getSystemDatabase()
                .in("parent_id", targetDepartment.getId())
                .doQuery(XdapDepartments.class).stream().map(XdapDepartments::getName)
                .collect(Collectors.toList());


        try (InputStream inputStream = file.getInputStream()){
            EasyExcel.read(
                    inputStream,
                    ParameterPojo.class,
                            new SheetDataListener(this, templateRowId, userId, problems, subDepartments)
                    )
                    .ignoreEmptyRow(true)
                    .autoTrim(true)
                    .doReadAll();

        } catch (IOException e) {
            throw new CommonException("读取文件失败");
        }

        return problems;
    }

    /**
     * 导出模板
     *
     * @param form 导出模板表单
     * @param response HTTP响应对象
     */
    @Override
    public void exportTemplate(ExportTemplateForm form, HttpServletResponse response) {
        TemplatePojo templateDB = bd.getBusinessDatabase()
                .rowid("id", form.getTemplateRowId())
                .doQueryFirst(TemplatePojo.class);

        String fileName = templateDB.getTemplateName() +
                (form.isTemplateEmpty() ? "空模板_" : "参数模板_") +
                LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd_HH-mm-ss"));

        try {
            String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replace("+", "%20");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
            response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", 0);

            List<ParameterPojo> list;
            if (form.isTemplateEmpty()) {
                list = new ArrayList<>();
                list.add(new ParameterPojo());
            } else {
                list = bd.getBusinessDatabase()
                        .viewQueryMode(true)
                        .view("selectParametersByTId")
                        .setVar("templateId", templateDB.getId())
                        .orderBy("parameterNumber", "asc")
                        .doQuery(ParameterPojo.class);
            }
            EasyExcel.write(response.getOutputStream(), ParameterPojo.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new DescriptionWriteUtils(ParameterPojo.getFieldDescriptions()))
                    .includeColumnFieldNames(getIncludeColumnFieldNames())
                    .sheet(templateDB.getTemplateName())
                    .doWrite(list);

        } catch (Exception e) {
            throw new CommonException("导出Excel失败: " + e.getMessage());
        }
    }

    /**
     * 获取模板版本
     *
     * @param form 模板行ID表单
     * @return 模板版本列表
     */
    @Override
    public List<TemplatePojo> getTemplateVersion(TemplateRowIdForm form) {
        TemplatePojo template = bd.getBusinessDatabase()
                .rowid("id", form.getTemplateRowId())
                .doQueryFirst(TemplatePojo.class);
        return bd.getBusinessDatabase()
                .eq("template_name", template.getTemplateName())
                .orderBy("version", "desc")
                .doQuery(TemplatePojo.class);
    }

    /**
     * 根据ID获取模板
     *
     * @param templateId 模板ID
     * @return 模板POJO对象
     */
    @Override
    public TemplatePojo getTemplateById(String templateId) {
        return bd.getBusinessDatabase()
                .eq("id", templateId)
                .doQueryFirst(TemplatePojo.class);
    }


    /**
     * 删除模板
     *
     * @param templateId 模板ID
     */
    @Override
    public void deleteTemplate(String templateId) {
        TemplatePojo template = bd.getBusinessDatabase()
                .eq("id", templateId)
                .doQueryFirst(TemplatePojo.class);

        if (template == null) {
            throw new CommonException("模板不存在");
        }
        if (!Objects.equals(template.getStatus(), "DRAFT") || template.getVersion() != 1) {
            throw new CommonException("非草稿版本不允许删除");
        }
        bd.getBusinessDatabase().eq("id", templateId).doDelete(TemplatePojo.class);
    }

    /**
     * 处理表格数据
     *
     * @param dataList 参数数据列表
     * @param sheetName 表格名称
     * @param templateRowId 模板行ID
     * @param createBy 创建者
     * @param problems 问题收集对象
     */
    @Override
    @Transactional
    public void processSheetData(List<ParameterPojo> dataList, String sheetName,
                                 String templateRowId, String createBy, MyResponse problems) {
        if (dataList.isEmpty()) {
            log.warn("Sheet {} 没有数据", sheetName);
            return;
        }

        // 验证公式
        for (ParameterPojo data : dataList) {
            data.setCreatedBy(createBy);
            if (data.getParameterName() == null) {
                problems.setFlag(true);
                problems.getErrors().add("模板导入失败:" + sheetName + "模板的参数" + data.getParameterName() + "的参数名称字段为空");
                continue;
            }
            if (data.getSubsystemName() == null) {
                problems.setFlag(true);
                problems.getErrors().add("模板导入失败:" + sheetName + "模板的参数" + data.getParameterName() + "的子系统字段为空");
                continue;
            }
            if (data.getParameterType() == null) {
                problems.setFlag(true);
                problems.getErrors().add("模板导入失败:" + sheetName + "模板的参数" + data.getParameterName() + "的参数类型字段为空");
                continue;
            }

            if (data.getPartsName() == null || data.getPartsName().isEmpty()) {
                data.setPartsName("/");
            }
            if (Objects.equals(data.getParameterSource(), "公式计算")) {
                Map<String, Object> validationResult = FormulaValidatorUtils.validateFormulaDetailed(
                        data.getNumberOrFormula());
                if (!(boolean) validationResult.get("isValid")) {
                    String errorMsg = String.format("模板导入失败:"+"模板'%s'的参数'%s'公式格式错误， %s",
                            sheetName, data.getParameterName(),
                            validationResult.get("errorMessage"));
                    problems.setFlag(true);
                    problems.getErrors().add(errorMsg);
                }
            }
        }

        // 处理模板版本
        String templateName = sheetName;
        // 指定模板导入
        if (templateRowId != null && !templateRowId.isEmpty()) {
            TemplatePojo t = bd.getBusinessDatabase()
                    .rowid("id", templateRowId)
                    .doQueryFirst(TemplatePojo.class);
            templateName = t.getTemplateName();

            // 页名和模板名不一致不允许导入
            if (!Objects.equals(templateName, sheetName)) {
                problems.setFlag(true);
                problems.getErrors().add("模板导入失败:+Excel页名与模板名不一致");
            }
        }

        TemplatePojo template = bd.getBusinessDatabase()
                .eq("template_name", templateName)
                .orderBy("version", "desc")
                .doQueryFirst(TemplatePojo.class);
        if (template != null && Objects.equals(template.getStatus(), "APPROVING")) {
            problems.setFlag(true);
            problems.getErrors().add("模板导入失败:模板'" + templateName + "'正在审批中");
        }

        // 检查不通过，直接返回
        if (problems.isFlag()) {
            return;
        }

        // 首先查找是否有草稿版本
        TemplatePojo draftTemplate = bd.getBusinessDatabase()
                .eq("template_name", templateName)
                .eq("status", "DRAFT")
                .doQueryFirst(TemplatePojo.class);

        TemplatePojo targetTemplate;
        if (draftTemplate != null) {
            // 如果存在草稿版本，使用草稿版本
            targetTemplate = draftTemplate;
        } else {
            // 如果没有草稿版本，创建新版本
            TemplatePojo lastTemplate = bd.getBusinessDatabase()
                    .viewQueryMode(true)
                    .view("getNewTemplate")
                    .eq("template_name", templateName)
                    .doQueryFirst(TemplatePojo.class);

            List<ParameterPojo> lastTemplateParameters = new ArrayList<>();
            if (lastTemplate != null) {
                targetTemplate = new TemplatePojo(
                        templateName, lastTemplate.getVersion() + 1, "DRAFT",
                        Timestamp.valueOf(LocalDateTime.now()), null, createBy, System.currentTimeMillis() / 1000, 0
                );
                lastTemplateParameters = bd.getBusinessDatabase()
                        .viewQueryMode(true)
                        .view("selectParametersByTId")
                        .setVar("templateId", lastTemplate.getId())
                        .doQuery(ParameterPojo.class);
            } else {
                // 如果没有同名模板，创建第一个版本
                targetTemplate = new TemplatePojo(
                        templateName, 1, "DRAFT", Timestamp.valueOf(LocalDateTime.now()),
                        null, createBy, System.currentTimeMillis() / 1000, 0
                );
            }

            bd.getBusinessDatabase().doInsert(targetTemplate);

            if (!lastTemplateParameters.isEmpty()) {
                lastTemplateParameters.forEach(parameter -> parameter.setId(null));
                bd.getBusinessDatabase().doBatchInsert(lastTemplateParameters);
                List<TemplateAndParameterPojo> list = new ArrayList<>();
                for (ParameterPojo parameter : lastTemplateParameters) {
                    TemplateAndParameterPojo templateAndParameter = new TemplateAndParameterPojo();
                    templateAndParameter.setTemplateId(targetTemplate.getId());
                    templateAndParameter.setParameterId(parameter.getId());
                    list.add(templateAndParameter);
                }
                bd.getBusinessDatabase().doBatchInsert(list);
            }
        }

        // 获取目标模板的现有参数
        List<ParameterPojo> existingParameters = bd.getBusinessDatabase()
                .viewQueryMode(true)
                .view("selectParametersByTId")
                .setVar("templateId", targetTemplate.getId())
                .doQuery(ParameterPojo.class);

        // 构建现有参数的映射，以参数名、子系统和零部件组合作为键
        Map<String, ParameterPojo> existingParamMap = new HashMap<>();
        if (existingParameters != null && !existingParameters.isEmpty()) {
            existingParamMap = existingParameters.stream()
                    .collect(Collectors.toMap(
                            param -> param.getParameterName() + "|" +
                                    (param.getSubsystemName() != null ? param.getSubsystemName() : "") + "|" +
                                    (param.getPartsName() != null ? param.getPartsName() : ""),
                            param -> param,
                            (existing, replacement) -> existing
                    ));
        }

        // 处理参数新增和更新
        List<TemplateAndParameterPojo> relationsToAdd = new ArrayList<>();
        List<RevisionRecordPojo> revisionRecords = new ArrayList<>();

        for (ParameterPojo newParam : dataList) {
            String key = newParam.getParameterName() + "|" +
                    (newParam.getSubsystemName() != null ? newParam.getSubsystemName() : "") + "|" +
                    (newParam.getPartsName() != null ? newParam.getPartsName() : "");
            ParameterPojo existingParam = existingParamMap.get(key);
            if (existingParam != null) {
                newParam.setId(existingParam.getId());
                String datils = getParameterChange(existingParam, newParam);
                bd.getBusinessDatabase()
                        .eq("id", newParam.getId())
                        .doUpdate(newParam);

                // 记录修改记录
                if (!datils.isEmpty()) {
                    revisionRecords.add(new RevisionRecordPojo(
                            targetTemplate.getTemplateName(),
                            targetTemplate.getVersion(),
                            newParam.getParameterNumber(),
                            datils,
                            targetTemplate.getId(),
                            existingParam.getId(),
                            Timestamp.valueOf(LocalDateTime.now()),
                            newParam.getId(),
                            targetTemplate.getReturnNum()
                    ));
                }
            } else {
                newParam.setId(null);
                bd.getBusinessDatabase().doInsert(newParam);

                TemplateAndParameterPojo relation = new TemplateAndParameterPojo();
                relation.setTemplateId(targetTemplate.getId());
                relation.setParameterId(newParam.getId());
                relationsToAdd.add(relation);

                revisionRecords.add(new RevisionRecordPojo(
                        targetTemplate.getTemplateName(),
                        targetTemplate.getVersion(),
                        newParam.getParameterNumber(),
                        "新增参数",
                        targetTemplate.getId(),
                        null,
                        Timestamp.valueOf(LocalDateTime.now()),
                        newParam.getId(),
                        targetTemplate.getReturnNum()
                ));
            }
        }

        // 批量保存关联关系
        if (!relationsToAdd.isEmpty()) {
            bd.getBusinessDatabase().doBatchInsert(relationsToAdd);
        }

        // 批量保存修改记录
        if (!revisionRecords.isEmpty()) {
            bd.getBusinessDatabase().doBatchInsert(revisionRecords);
        }

        // 返回问题
        if ("已完成导入：无".equals(problems.getHasImported().toString())) {
            problems.setHasImported(new StringBuilder("已完成导入："));
            problems.getHasImported().append(templateName);
        } else {
            problems.getHasImported().append("、").append(templateName);
        }
        log.info("Sheet '{}' 导入完成，共处理 {} 个参数", sheetName, dataList.size());
    }

    private static Set<String> getIncludeColumnFieldNames() {
        Set<String> includeColumnFieldNames;
        includeColumnFieldNames = new HashSet<>();
        includeColumnFieldNames.add("parameterNumber");
        includeColumnFieldNames.add("subsystemName");
        includeColumnFieldNames.add("parameterType");
        includeColumnFieldNames.add("partsName");
        includeColumnFieldNames.add("parameterName");
        includeColumnFieldNames.add("unit");
        includeColumnFieldNames.add("isNumber");
        includeColumnFieldNames.add("parameterSource");
        includeColumnFieldNames.add("numberOrFormula");
        includeColumnFieldNames.add("department");
        includeColumnFieldNames.add("remarks");
        return includeColumnFieldNames;
    }

    private String getParameterChange(ParameterPojo originalParameter, ParameterPojo updatedParameter) {
        return handleParameterChange("参数名", originalParameter.getParameterName(), updatedParameter.getParameterName()) +
                handleParameterChange("参数类型", originalParameter.getParameterType(), updatedParameter.getParameterType()) +
                handleParameterChange("子系统", originalParameter.getSubsystemName(), updatedParameter.getSubsystemName()) +
                handleParameterChange("部件名", originalParameter.getPartsName(), updatedParameter.getPartsName()) +
                handleParameterChange("单位名", originalParameter.getUnit(), updatedParameter.getUnit()) +
                handleParameterChange("参数来源", originalParameter.getParameterSource(), updatedParameter.getParameterSource()) +
                handleParameterChange("公式或编号", originalParameter.getNumberOrFormula(), updatedParameter.getNumberOrFormula()) +
                handleParameterChange("部门名", originalParameter.getDepartment(), updatedParameter.getDepartment());
    }

    private String handleParameterChange(String name, String originalParameter, String updatedParameter) {
        StringBuilder sb = new StringBuilder();
        if (!Objects.equals(originalParameter, updatedParameter)) {
            if (originalParameter == null || originalParameter.isEmpty()) {
                if (updatedParameter == null || updatedParameter.isEmpty()) {
                    sb.append(name).append(": ").append("变更为空值").append("\n");
                } else {
                    sb.append(name).append(": ").append("变更为: '").append(updatedParameter).append("' ").append("\n");
                }
            } else {
                if (updatedParameter == null || updatedParameter.isEmpty()) {
                    sb.append(name).append(": '").append(originalParameter).append("' 变更为 空值").append("\n");
                } else {
                    sb.append(name).append(": '").append(originalParameter).append("' 变更为 '").append(updatedParameter).append("'\n");
                }
            }
        }
        return sb.toString();
    }


    @Override
    /**
     * 导出多个模板
     *
     * @param response HTTP响应对象
     */
    public void exportMultipleTemplates(HttpServletResponse response) {
        String fileName = "全部模板_" +
                LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd_HH-mm-ss"));

        try {
            setupResponseHeaders(response, fileName);

            // 获取所有模板
            List<TemplatePojo> templates = bd.getBusinessDatabase()
                    .eq("status", "COMPLETE")
                    .doQuery(TemplatePojo.class);

            if (templates.isEmpty()) {
                exportEmptyTemplate(response);
            } else {
                exportMultipleSheets(response, templates);
            }
        } catch (Exception e) {
            throw new CommonException("导出Excel失败: " + e.getMessage());
        }
    }

    /**
     * 设置响应头信息
     */
    private void setupResponseHeaders(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replace("+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setDateHeader("Expires", 0);
    }

    /**
     * 导出空模板（只有一个sheet）
     */
    private void exportEmptyTemplate(HttpServletResponse response) {
        try {
            List<ParameterPojo> list = new ArrayList<>();
            list.add(new ParameterPojo());

            EasyExcel.write(response.getOutputStream(), ParameterPojo.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new DescriptionWriteUtils(ParameterPojo.getFieldDescriptions()))
                    .includeColumnFieldNames(getIncludeColumnFieldNames())
                    .sheet("xxx")
                    .doWrite(list);
        } catch (Exception e) {
            throw new CommonException("导出空模板失败: " + e.getMessage());
        }
    }

    /**
     * 导出多个sheet（每个模板一个sheet）
     */
    private void exportMultipleSheets(HttpServletResponse response, List<TemplatePojo> templates) {
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ParameterPojo.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new DescriptionWriteUtils(ParameterPojo.getFieldDescriptions()))
                .includeColumnFieldNames(getIncludeColumnFieldNames())
                .build()) {

            writeMultipleSheets(excelWriter, templates);

        } catch (Exception e) {
            throw new CommonException("导出多sheet模板失败: " + e.getMessage());
        }
    }

    /**
     * 写入多个sheet的数据
     */
    private void writeMultipleSheets(ExcelWriter excelWriter, List<TemplatePojo> templates) {
        for (TemplatePojo template : templates) {
            List<ParameterPojo> parameters = new ArrayList<>();
            parameters.add(new ParameterPojo());

            WriteSheet writeSheet = EasyExcel
                    .writerSheet(template.getTemplateName())
                    .build();

            // 写入数据到对应的sheet
            excelWriter.write(parameters, writeSheet);
        }
    }
}
