package com.qianhe.zqcz.pftb.service.impl;

import com.qianhe.common.enums.zqcz.ZqczXmlbEnum;
import com.qianhe.common.exception.BusinessException;
import com.qianhe.common.utils.MtTimeUtil;
import com.qianhe.common.utils.SecurityUtils;
import com.qianhe.common.utils.StringUtils;
import com.qianhe.domain.ExcelCellConfig;
import com.qianhe.util.MtExcelImportUtils;
import com.qianhe.zqcz.jcdw.mapper.ZqczJcdwMapper;
import com.qianhe.zqcz.jcrw.domain.ZqczJcrwVo;
import com.qianhe.zqcz.pftb.domain.ZqczPftb;
import com.qianhe.zqcz.pftb.domain.ZqczPftbQuery;
import com.qianhe.zqcz.pftb.mapper.ZqczPftbMapper;
import com.qianhe.zqcz.pftb.service.ZqczPftbService;
import com.qianhe.zqcz.pwxx.domain.ZqczPwxxQuery;
import com.qianhe.zqcz.pwxx.domain.ZqczPwxxVo;
import com.qianhe.zqcz.pwxx.mapper.ZqczPwxxMapper;
import com.qianhe.zqcz.zsrw.domain.ZqczZsrwVo;
import com.qianhe.zqcz.zsrw.mapper.ZqczZsrwMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class ZqczPftbServiceImpl implements ZqczPftbService {
    @Autowired
    private ZqczPftbMapper zqczPftbMapper;
    @Autowired
    private ZqczPwxxMapper zqczPwxxMapper;
    @Autowired
    private ZqczZsrwMapper zqczZsrwMapper;

    /**
     * 条件查询
     */
    @Override
    public Map<String, Object> list(ZqczPftbQuery zqczPftbQuery) {
        if (zqczPftbQuery.getZsrwid() == null) {
            throw new BusinessException("直属任务ID不能为空");
        }
        if (zqczPftbQuery.getJcrwid() == null) {
            throw new BusinessException("基层任务ID不能为空");
        }

        // 根据基层任务ID查询评委信息
        List<ZqczPwxxVo> pwxxList = zqczPwxxMapper.list(ZqczPwxxQuery.builder().jcrwid(zqczPftbQuery.getJcrwid()).build());
        if (Optional.ofNullable(pwxxList).orElse(Collections.emptyList()).isEmpty()) {
            throw new BusinessException("评委信息未填报");
        }

        zqczPftbQuery.setPwxxList(pwxxList);
        List<Map<String, Object>> dataList = zqczPftbMapper.list(zqczPftbQuery);
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("dataList", dataList);
        dataMap.put("fields", pwxxList);
        return dataMap;
    }

    /**
     * 新增
     */
    @Override
    public int add(ZqczPftb zqczPftb, String username) {
        zqczPftb.setCreate_by(username);
        zqczPftb.setUpdate_by(username);
        zqczPftb.setCreate_time(new Date());
        zqczPftb.setUpdate_time(new Date());
        return zqczPftbMapper.add(zqczPftb);
    }

    /**
     * 删除
     */
    @Override
    public int deleteByJcrwid(Long jcrwid) {
        return zqczPftbMapper.deleteByJcrwid(jcrwid);
    }

    /**
     * 导出
     */
    @Override
    public void export(HttpServletResponse response, ZqczPftbQuery zqczPftbQuery) {
        Map<String, Object> resMap = list(zqczPftbQuery);  // 查询数据
        List<ZqczPwxxVo> fields = (List<ZqczPwxxVo>) resMap.get("fields"); // 获取动态表头
        List<Map<String, Object>> dataList = (List<Map<String, Object>>) resMap.get("dataList");  // 获取数据

        // 表格标题
        List<String> tableTitle = new ArrayList<>();  // 拼接完成的标题列表
        List<String> tableTitlePreFixList = Arrays.asList("固定字段(别删)", "时间", "基层单位名称", "班站名称", "项目类别", "盲抽人员姓名", "盲抽项目名称");  // 前缀
        List<String> tableTitleSuffixList = Arrays.asList("得分(平均)", "存在问题");  // 后缀
        List<String> tableTitleDynamicList = fields.stream().map(ZqczPwxxVo::getPwmc).collect(Collectors.toList());  // 动态
        tableTitle.addAll(tableTitlePreFixList);
        for (int i = 0; i < tableTitleDynamicList.size(); i++) {
            tableTitle.add(String.format("评委%s分数(%s)", i + 1, tableTitleDynamicList.get(i)));
        }
        tableTitle.addAll(tableTitleSuffixList);

        // 处理表格数据
        List<Object[]> finalList = new ArrayList<>();

        // 遍历数据
        for (Map<String, Object> rowMap : dataList) {
            List<Object> rowList = new ArrayList<>();

            // 固定字段（直接添加，无需多余对象接收）
            rowList.add(rowMap.get("xmryid"));   // 项目人员ID（用于导入修改时用到）
            rowList.add(rowMap.get("update_time"));   // 更新时间
            rowList.add(rowMap.get("jcdwmc"));        // 基层单位名称
            rowList.add(rowMap.get("dwmc"));          // 班站名称

            String xmlb = rowMap.get("xmlb").toString();
            rowList.add(ZqczXmlbEnum.getNameByCode(xmlb));          // 项目类型

            rowList.add(rowMap.get("rymc"));          // 盲抽人员姓名
            rowList.add(rowMap.get("xmmc"));          // 盲抽项目名称

            // 动态列
            for (String dynamic : tableTitleDynamicList) {
                rowList.add(rowMap.get(dynamic));
            }

            // 末尾固定字段
            rowList.add(rowMap.get("pjdf"));          // 平均得分
            rowList.add(rowMap.get("remark"));        // 存在问题

            // 转数组并加入列表
            finalList.add(rowList.toArray());
        }

        // 最终二维数组
        Object[][] finalArray = finalList.toArray(new Object[0][]);

        // 查询直属任务
        ZqczZsrwVo zqczZsrwVo = zqczZsrwMapper.info(zqczPftbQuery.getZsrwid());
        if (zqczZsrwVo == null) {
            throw new BusinessException("暂无查询到关联的直属任务");
        }

        Workbook workbook = new XSSFWorkbook();
        try {
            Sheet sheet = workbook.createSheet();  // 创建标签页
            createMainTitle(workbook, sheet, tableTitle.size() - 1);
            createSubTitle(workbook, sheet, zqczZsrwVo.getDeptName());
            createTableTitle(workbook, sheet, tableTitle);
            writeTableData(workbook, sheet, finalArray, 3);
            setExportResponseHeader(response, String.format("附表1“最强操作”抽取人员及项目评分统计表_%s.xlsx", new Date().getTime()));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
            try {
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.getWriter().write("导出失败：" + e.getMessage());
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 导入
     */
    @Override
    public int importExcel(Long zsrwid, Long jcrwid, MultipartFile file) {
        if (zsrwid == null) {
            throw new BusinessException("直属任务ID不能为空");
        }
        if (jcrwid == null) {
            throw new BusinessException("基层任务ID不能为空");
        }
        if (file == null) {
            throw new BusinessException("文件不能为空");
        }
        // 查询评委信息
        List<ZqczPwxxVo> pwxxList = zqczPwxxMapper.list(ZqczPwxxQuery.builder().jcrwid(jcrwid).build());
        if (Optional.ofNullable(pwxxList).orElse(Collections.emptyList()).isEmpty()) {
            throw new BusinessException("评委信息未填报");
        }

        // 定义通用校验规则
        List<ExcelCellConfig> cellConfigs = new ArrayList<>();
        cellConfigs.add(new ExcelCellConfig(0, "固定字段(别删)", true));  // 固定字段(别删)（A列）
        cellConfigs.add(new ExcelCellConfig(1, "时间", true));  // 时间（B列）
        cellConfigs.add(new ExcelCellConfig(2, "基层单位名称", true));  // 基层单位名称（C列）
        cellConfigs.add(new ExcelCellConfig(3, "班站名称", true));  // 班站名称（D列）
        cellConfigs.add(new ExcelCellConfig(4, "项目类别", true));  // 项目类别（E列）
        cellConfigs.add(new ExcelCellConfig(5, "盲抽人员姓名", true));  // 盲抽人员姓名（F列）
        cellConfigs.add(new ExcelCellConfig(6, "盲抽项目名称", true));  // 盲抽项目名称（G列）
        for (int i = 0; i < pwxxList.size(); i++) {
            cellConfigs.add(new ExcelCellConfig(6 + (i + 1), String.format("评委%s分数(%s)", (i + 1), pwxxList.get(i).getPwmc()), false));  // 评委信息
        }
        cellConfigs.add(new ExcelCellConfig(6 + pwxxList.size() + 1, "得分(平均)", false));  // 得分（平均）
        cellConfigs.add(new ExcelCellConfig(6 + pwxxList.size() + 2, "存在问题", false));  // 存在问题

        return MtExcelImportUtils.importXlsx(file, true, (i, row) -> {
            List<ZqczPftb> dataList = new ArrayList<>();
            if (i > 2) {
                Map<String, String> cellValueMap = MtExcelImportUtils.parseAndValidateCells(row, i, cellConfigs);
                String xmryidStr = cellValueMap.get("固定字段(别删)");  // 获取项目人员ID
                Long xmryid = Long.valueOf(xmryidStr);

                for (int j = 0; j < pwxxList.size(); j++) {
                    Long pwid = pwxxList.get(j).getPwid();
                    String pwmc = pwxxList.get(j).getPwmc();

                    String khdfStr = cellValueMap.get(String.format("评委%s分数(%s)", j + 1, pwmc));  // 获取评委信息
                    BigDecimal khdf = null;
                    if (StringUtils.isNotEmpty(khdfStr)) {
                        khdf = new BigDecimal(khdfStr);
                    }

                    dataList.add(new ZqczPftb(xmryid, pwid, zsrwid, jcrwid, khdf));
                }
            }
            return dataList;
        }, dataList -> {
            List<ZqczPftb> finalList = Optional.ofNullable(dataList)
                    .orElseGet(Collections::emptyList)
                    .stream()
                    .filter(Objects::nonNull)
                    .flatMap(Collection::stream)
                    .collect(Collectors.toList());  // 数据扁平化

            zqczPftbMapper.batchAdd(finalList);
        });
    }

    /**
     * 创建大标题
     */
    private void createMainTitle(Workbook workbook, Sheet sheet, Integer lastCol) {
        // ====================== 创建标题样式 ======================
        CellStyle mainTitleStyle = workbook.createCellStyle();
        mainTitleStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        mainTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        Font mainTitleFont = workbook.createFont();  // 设置字体
        mainTitleFont.setBold(true);  // 加粗
        mainTitleFont.setFontHeightInPoints((short) 22);  // 字体大小
        mainTitleFont.setFontName("宋体");  // 字体名称
        mainTitleStyle.setFont(mainTitleFont);

        Row mainTitleRow = sheet.createRow(0);  // 第一行
        mainTitleRow.setHeightInPoints(46);  // 设置行高

        Cell cell = mainTitleRow.createCell(0);  // 创建单元格
        cell.setCellValue("附表1“最强操作”抽取人员及项目评分统计表");  // 设置单元格的内容
        cell.setCellStyle(mainTitleStyle);  // 设置单元格样式
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastCol));  // 单元格合并
    }

    /**
     * 创建副标题
     */
    public void createSubTitle(Workbook workbook, Sheet sheet, String subTitle) {
        // ====================== 创建副标题样式 ======================
        CellStyle subTitleStyle = workbook.createCellStyle();
        subTitleStyle.setAlignment(HorizontalAlignment.LEFT);  // 左对齐
        subTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        Font subTitleFont = workbook.createFont();  // 设置字体
        subTitleFont.setBold(true);  // 加粗
        subTitleFont.setFontHeightInPoints((short) 11);  // 字体大小
        subTitleFont.setFontName("宋体");  // 字体名称
        subTitleStyle.setFont(subTitleFont);

        Row subTitleRow = sheet.createRow(1);  // 第二行
        subTitleRow.setHeightInPoints(25);  // 设置行高

        Cell cell = subTitleRow.createCell(0);  // 创建单元格
        cell.setCellValue(String.format("直属单位：%s", subTitle));  // 设置单元格的内容
        cell.setCellStyle(subTitleStyle);  // 设置单元格样式
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));  // 单元格合并
    }

    /**
     * 创建表格标题
     */
    public static void createTableTitle(Workbook workbook, Sheet sheet, List<String> tableTitle) {
        CellStyle tableHeaderStyle = workbook.createCellStyle();  // 创建单元格样式类
        tableHeaderStyle.setAlignment(HorizontalAlignment.CENTER);  // 左对齐
        tableHeaderStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中

        tableHeaderStyle.setBorderTop(BorderStyle.THIN);     // 上边框
        tableHeaderStyle.setBorderBottom(BorderStyle.THIN);  // 下边框
        tableHeaderStyle.setBorderLeft(BorderStyle.THIN);    // 左边框
        tableHeaderStyle.setBorderRight(BorderStyle.THIN);   // 右边框

        Font tableHeaderFont = workbook.createFont();  // 设置字体
        tableHeaderFont.setBold(true);  // 加粗
        tableHeaderFont.setFontHeightInPoints((short) 11);  // 字体大小
        tableHeaderFont.setFontName("宋体");  // 字体名称
        tableHeaderStyle.setFont(tableHeaderFont);

        Row tableHeaderRow = sheet.createRow(2);  // 创建第三行
        tableHeaderRow.setHeightInPoints(43);  // 设置行高

        CellStyle redStyle = workbook.createCellStyle();
        redStyle.cloneStyleFrom(tableHeaderStyle);
        redStyle.setAlignment(HorizontalAlignment.CENTER);
        redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        redStyle.setFillForegroundColor(IndexedColors.RED1.getIndex());

        // 循环创建每一列表头
        for (int i = 0; i < tableTitle.size(); i++) {
            // 在当前行创建第 i 列（i 从 0 开始）
            Cell cell = tableHeaderRow.createCell(i);

            // 设置单元格的值为表头文字
            cell.setCellValue(tableTitle.get(i));

            // 将表头样式应用到该单元格
            cell.setCellStyle(i == 0 ? redStyle : tableHeaderStyle);
        }
    }

    /**
     * 设置数据
     */
    public static void writeTableData(Workbook workbook, Sheet sheet, Object[][] finalArray, Integer firstRow) {
        if (finalArray == null || finalArray.length == 0) {
            return;
        }

        // 1. 创建通用基础样式（边框+垂直居中）
        CellStyle baseStyle = workbook.createCellStyle();
        baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        baseStyle.setBorderTop(BorderStyle.THIN);
        baseStyle.setBorderBottom(BorderStyle.THIN);
        baseStyle.setBorderLeft(BorderStyle.THIN);
        baseStyle.setBorderRight(BorderStyle.THIN);

        // 2. 普通文本样式（左对齐，继承基础样式）
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.cloneStyleFrom(baseStyle); // 继承边框+垂直居中
        dataStyle.setAlignment(HorizontalAlignment.LEFT);

        // 3. 数字千分位样式（右对齐，继承基础样式）
        CellStyle numberStyle = workbook.createCellStyle();
        numberStyle.cloneStyleFrom(baseStyle);
        numberStyle.setAlignment(HorizontalAlignment.RIGHT);
        numberStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0"));

        // 4. 红色背景样式（居中+红底，继承基础样式）
        CellStyle redStyle = workbook.createCellStyle();
        redStyle.cloneStyleFrom(baseStyle);
        redStyle.setAlignment(HorizontalAlignment.CENTER);
        redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        redStyle.setFillForegroundColor(IndexedColors.RED1.getIndex());

        for (int i = 0; i < finalArray.length; i++) {
            Row row = sheet.createRow(i + firstRow);
            row.setHeightInPoints(20);  // 设置行高

            for (int j = 0; j < finalArray[i].length; j++) {
                // 创建单元格
                Cell cell = row.createCell(j);
                // 单元格赋值
                Object value = finalArray[i][j];
                if (value instanceof String) {
                    cell.setCellValue((String) value);  // 设置为字符串
                } else if (value instanceof Integer) {
                    int intValue = (Integer) value;
                    cell.setCellValue(intValue);  // 设置为数值
                } else if (value instanceof LocalDateTime) {
                    String timeValue = MtTimeUtil.dateTimeFormatNyr((LocalDateTime) value);
                    cell.setCellValue(timeValue);  // 将日期类型转化为字符串类型
                }
                // 设置单元格样式
                if (j == 0) {
                    cell.setCellStyle(redStyle);
                } else {
                    cell.setCellStyle(dataStyle);
                }
            }
        }

        // ====================== 2. 所有数据写完后，统一自动列宽（关键！） ======================
        int colCount = finalArray[0].length;
        for (int j = 0; j < colCount; j++) {
            // 自动计算宽度
            sheet.autoSizeColumn(j);

            // 对中文进行加宽（关键！解决显示不全）
            int width = sheet.getColumnWidth(j) * 14 / 10;

            // 最大宽度限制（Excel 限制 255 个字符宽度）
            int maxWidth = 255 * 256;
            if (width > maxWidth) {
                width = maxWidth;
            }

            sheet.setColumnWidth(j, width);
        }

        sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow + finalArray.length - 1, 1, 1));  // 单元格合并（时间）
        sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow + finalArray.length - 1, 2, 2));  // 单元格合并（基层单位名称）
    }

    /**
     * 设置响应头
     */
    private void setExportResponseHeader(HttpServletResponse response, String fileName) throws IOException {
        // 设置内容类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");

        // 处理中文文件名
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8")
                .replaceAll("\\+", "%20");
        response.setHeader("Content-disposition",
                "attachment;filename*=utf-8''" + encodedFileName);

        // 禁用缓存
        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setDateHeader("Expires", 0);
    }
}
