package com.bonus.material.utils;

import com.bonus.material.devConfig.domain.EquipmentProperty;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * 多Sheet Excel工具类
 * 用于生成包含装备信息主Sheet和特征项数据Sheet的Excel模板
 */
public class MultiSheetExcelUtil {

    /**
     * 生成带特征项联动的Excel模板
     *
     * @param response HTTP响应对象
     * @param fileName 文件名
     * @param typePropertiesMap 装备类型与特征项的映射 (key: 装备类型名称, value: 特征项列表)
     * @param professionArray 装备类型下拉选项
     * @param manufacturerArray 生产厂家下拉选项
     * @param unitArray 计数单位下拉选项
     * @throws IOException IO异常
     */
    public static void generateMultiSheetTemplate(
            HttpServletResponse response,
            String fileName,
            Map<String, List<EquipmentProperty>> typePropertiesMap,
            String[] professionArray,
            String[] manufacturerArray,
            String[] unitArray) throws IOException {

        Workbook workbook = new XSSFWorkbook();

        // 创建样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle centerStyle = createCenterStyle(workbook);
        CellStyle dateStyle = createDateStyle(workbook);

        // 1. 先创建主Sheet（装备信息录入）- 索引0，默认打开
        Sheet mainSheet = workbook.createSheet("装备信息");

        // 创建表头
        createMainSheetHeader(mainSheet, headerStyle);

        // 创建1000行数据行
        createMainSheetDataRows(mainSheet, centerStyle, dateStyle, professionArray,
                               manufacturerArray, unitArray, 1000);

        // 设置列宽
        setMainSheetColumnWidths(mainSheet);

        // 设置下拉框数据验证
        setMainSheetDataValidation(mainSheet, professionArray, manufacturerArray, unitArray);

        // 为特征项名称列添加批注（显示可选值提示）
        addPropertyComments(mainSheet, typePropertiesMap);

        // 2. 再创建隐藏的数据源Sheet（包含特征项数据和下拉列表数据）- 索引1
        Sheet dataSheet = createPropertyDataSheet(workbook, typePropertiesMap, headerStyle, centerStyle,
                                                  professionArray, manufacturerArray, unitArray);

        // 输出到响应
        writeToResponse(response, workbook, fileName);
    }

    /**
     * 创建隐藏的数据Sheet（包含特征项数据和下拉列表数据）
     * 格式：
     * 列A-AA: 装备类型 | 特征项1名称 | 特征值1 | 输入类型1 | ... | 特征项9名称 | 特征值9 | 输入类型9
     * 列AC开始: 装备类型列表（用于下拉框）
     * 列AD开始: 生产厂家列表（用于下拉框）
     * 列AE开始: 计数单位列表（用于下拉框）
     */
    private static Sheet createPropertyDataSheet(Workbook workbook,
                                                 Map<String, List<EquipmentProperty>> typePropertiesMap,
                                                 CellStyle headerStyle, CellStyle centerStyle,
                                                 String[] professionArray,
                                                 String[] manufacturerArray,
                                                 String[] unitArray) {
        Sheet dataSheet = workbook.createSheet("数据源");

        // ========== 第一部分：特征项数据（A-AA列） ==========
        // 创建表头
        Row headerRow = dataSheet.createRow(0);
        headerRow.setHeight((short) 500);

        Cell headerCell = headerRow.createCell(0);
        headerCell.setCellValue("装备类型");
        headerCell.setCellStyle(headerStyle);

        for (int i = 0; i < 9; i++) {
            Cell nameHeaderCell = headerRow.createCell(1 + i * 3);
            nameHeaderCell.setCellValue("特征项" + (i + 1) + "名称");
            nameHeaderCell.setCellStyle(headerStyle);

            Cell valueHeaderCell = headerRow.createCell(1 + i * 3 + 1);
            valueHeaderCell.setCellValue("特征值" + (i + 1));
            valueHeaderCell.setCellStyle(headerStyle);

            Cell typeHeaderCell = headerRow.createCell(1 + i * 3 + 2);
            typeHeaderCell.setCellValue("输入类型" + (i + 1));
            typeHeaderCell.setCellStyle(headerStyle);
        }

        // 填充特征项数据
        int rowIndex = 1;
        for (Map.Entry<String, List<EquipmentProperty>> entry : typePropertiesMap.entrySet()) {
            String typeName = entry.getKey();
            List<EquipmentProperty> properties = entry.getValue();

            Row dataRow = dataSheet.createRow(rowIndex++);

            // 装备类型
            Cell typeCell = dataRow.createCell(0);
            typeCell.setCellValue(typeName);
            typeCell.setCellStyle(centerStyle);

            // 特征项、特征值、输入类型（最多9组）
            for (int i = 0; i < 9; i++) {
                Cell nameCell = dataRow.createCell(1 + i * 3);
                Cell valueCell = dataRow.createCell(1 + i * 3 + 1);
                Cell inputTypeCell = dataRow.createCell(1 + i * 3 + 2);

                if (i < properties.size()) {
                    EquipmentProperty prop = properties.get(i);
                    nameCell.setCellValue(prop.getPropertyName() != null ? prop.getPropertyName() : "");
                    valueCell.setCellValue(prop.getPropertyValue() != null ? prop.getPropertyValue() : "");
                    inputTypeCell.setCellValue(prop.getInputType() != null ? prop.getInputType() : 1L);
                } else {
                    nameCell.setCellValue("");
                    valueCell.setCellValue("");
                    inputTypeCell.setCellValue("");
                }

                nameCell.setCellStyle(centerStyle);
                valueCell.setCellStyle(centerStyle);
                inputTypeCell.setCellStyle(centerStyle);
            }
        }

        // ========== 第二部分：下拉列表数据（AC, AD, AE列） ==========
        // AC列（第29列）：装备类型列表
        Cell professionHeaderCell = headerRow.createCell(28); // AC列
        professionHeaderCell.setCellValue("装备类型列表");
        professionHeaderCell.setCellStyle(headerStyle);

        for (int i = 0; i < professionArray.length; i++) {
            Row row = dataSheet.getRow(i + 1);
            if (row == null) {
                row = dataSheet.createRow(i + 1);
            }
            Cell cell = row.createCell(28);
            cell.setCellValue(professionArray[i]);
            cell.setCellStyle(centerStyle);
        }

        // AD列（第30列）：生产厂家列表
        Cell manufacturerHeaderCell = headerRow.createCell(29); // AD列
        manufacturerHeaderCell.setCellValue("生产厂家列表");
        manufacturerHeaderCell.setCellStyle(headerStyle);

        for (int i = 0; i < manufacturerArray.length; i++) {
            Row row = dataSheet.getRow(i + 1);
            if (row == null) {
                row = dataSheet.createRow(i + 1);
            }
            Cell cell = row.createCell(29);
            cell.setCellValue(manufacturerArray[i]);
            cell.setCellStyle(centerStyle);
        }

        // AE列（第31列）：计数单位列表
        Cell unitHeaderCell = headerRow.createCell(30); // AE列
        unitHeaderCell.setCellValue("计数单位列表");
        unitHeaderCell.setCellStyle(headerStyle);

        for (int i = 0; i < unitArray.length; i++) {
            Row row = dataSheet.getRow(i + 1);
            if (row == null) {
                row = dataSheet.createRow(i + 1);
            }
            Cell cell = row.createCell(30);
            cell.setCellValue(unitArray[i]);
            cell.setCellStyle(centerStyle);
        }

        // 设置列宽
        dataSheet.setColumnWidth(0, 30 * 256);
        for (int i = 0; i < 9; i++) {
            dataSheet.setColumnWidth(1 + i * 3, 15 * 256);     // 特征项名称
            dataSheet.setColumnWidth(1 + i * 3 + 1, 20 * 256); // 特征值
            dataSheet.setColumnWidth(1 + i * 3 + 2, 10 * 256); // 输入类型
        }
        dataSheet.setColumnWidth(28, 30 * 256); // AC列
        dataSheet.setColumnWidth(29, 20 * 256); // AD列
        dataSheet.setColumnWidth(30, 12 * 256); // AE列

        // 测试阶段不隐藏此Sheet，方便查看数据
        // workbook.setSheetHidden(workbook.getSheetIndex(dataSheet), true);

        return dataSheet;
    }

    /**
     * 创建主Sheet的表头
     */
    private static void createMainSheetHeader(Sheet sheet, CellStyle headerStyle) {
        Row headerRow = sheet.createRow(0);
        headerRow.setHeight((short) 500);

        // 基础列（A-K列）
        String[] baseHeaders = {
            "装备类目", "装备名称", "规格型号", "资产原值(万元)", "生产厂家",
            "生产日期", "下次维保日期", "装备原始编码", "最大使用年限", "计数单位", "采购日期"
        };

        for (int i = 0; i < baseHeaders.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(baseHeaders[i]);
            cell.setCellStyle(headerStyle);
        }

        // 特征项列（L列开始，最多9组，每组2列）
        int colIndex = 11; // L列
        for (int i = 0; i < 9; i++) {
            // 特征项名称列
            Cell nameCell = headerRow.createCell(colIndex++);
            nameCell.setCellValue("特征项" + (i + 1));
            nameCell.setCellStyle(headerStyle);

            // 特征值列
            Cell valueCell = headerRow.createCell(colIndex++);
            valueCell.setCellValue("特征值" + (i + 1));
            valueCell.setCellStyle(headerStyle);
        }
    }

    /**
     * 创建主Sheet的数据行（带公式联动）
     */
    private static void createMainSheetDataRows(Sheet sheet, CellStyle centerStyle, CellStyle dateStyle,
                                               String[] professionArray, String[] manufacturerArray,
                                               String[] unitArray, int maxRows) {
        for (int rowIndex = 1; rowIndex <= maxRows; rowIndex++) {
            Row row = sheet.createRow(rowIndex);

            // A列：装备类目（下拉选择）
            Cell typeCell = row.createCell(0);
            typeCell.setCellStyle(centerStyle);

            // B-K列：基础列
            for (int colIndex = 1; colIndex < 11; colIndex++) {
                Cell cell = row.createCell(colIndex);
                // 日期列使用日期样式
                if (colIndex == 5 || colIndex == 6 || colIndex == 10) {
                    cell.setCellStyle(dateStyle);
                } else {
                    cell.setCellStyle(centerStyle);
                }
            }

            // L列开始：特征项和特征值（使用VLOOKUP公式从隐藏Sheet中查找）
            for (int i = 0; i < 9; i++) {
                int nameColIndex = 11 + i * 2;      // L, N, P, R, T, V, X, Z, AB
                int valueColIndex = nameColIndex + 1; // M, O, Q, S, U, W, Y, AA, AC

                Cell nameCell = row.createCell(nameColIndex);
                Cell valueCell = row.createCell(valueColIndex);

                // 特征项名称：使用VLOOKUP从隐藏Sheet查找
                // 公式：=IFERROR(VLOOKUP($A2,数据源!$A:$AA,列号,0),"")
                // 数据源Sheet结构：A列=装备类型，B列=特征项1名称，C列=特征值1，D列=输入类型1，E列=特征项2名称...
                String nameFormula = String.format(
                    "IFERROR(VLOOKUP($A%d,数据源!$A:$AA,%d,0),\"\")",
                    rowIndex + 1,  // Excel行号（从1开始，表头占第1行）
                    2 + i * 3      // 列号：特征项1名称在第2列(B)，特征项2名称在第5列(E)...
                );
                nameCell.setCellFormula(nameFormula);
                nameCell.setCellStyle(centerStyle);

                // 特征值：留空，让用户手动输入
                // 不使用公式，避免用户双击时看到公式
                valueCell.setCellStyle(centerStyle);
            }
        }
    }

    /**
     * 设置主Sheet的列宽
     */
    private static void setMainSheetColumnWidths(Sheet sheet) {
        // 基础列宽度
        sheet.setColumnWidth(0, 30 * 256); // 装备类目（加宽以显示完整路径）
        sheet.setColumnWidth(1, 20 * 256); // 装备名称
        sheet.setColumnWidth(2, 15 * 256); // 规格型号
        sheet.setColumnWidth(3, 18 * 256); // 资产原值
        sheet.setColumnWidth(4, 20 * 256); // 生产厂家
        sheet.setColumnWidth(5, 15 * 256); // 生产日期
        sheet.setColumnWidth(6, 18 * 256); // 下次维保日期
        sheet.setColumnWidth(7, 18 * 256); // 装备原始编码
        sheet.setColumnWidth(8, 15 * 256); // 最大使用年限
        sheet.setColumnWidth(9, 12 * 256); // 计数单位
        sheet.setColumnWidth(10, 15 * 256); // 采购日期

        // 特征项列宽度
        for (int i = 0; i < 9; i++) {
            sheet.setColumnWidth(11 + i * 2, 15 * 256);     // 特征项名称
            sheet.setColumnWidth(11 + i * 2 + 1, 20 * 256); // 特征值
        }
    }

    /**
     * 设置主Sheet的数据验证（下拉框）
     * 使用引用隐藏Sheet中的数据区域，避免255字符限制
     */
    private static void setMainSheetDataValidation(Sheet sheet, String[] professionArray,
                                                   String[] manufacturerArray, String[] unitArray) {
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();

        // 装备类目下拉框（A列，第2行到第1001行）
        // 引用隐藏Sheet中的AC列数据（数据源!$AC$2:$AC$xxx）
        if (professionArray != null && professionArray.length > 0) {
            CellRangeAddressList professionRange = new CellRangeAddressList(1, 1000, 0, 0);
            String professionFormula = "数据源!$AC$2:$AC$" + (professionArray.length + 1);
            DataValidationConstraint professionConstraint =
                validationHelper.createFormulaListConstraint(professionFormula);
            DataValidation professionValidation =
                validationHelper.createValidation(professionConstraint, professionRange);
            professionValidation.setShowErrorBox(true);
            professionValidation.setSuppressDropDownArrow(true);
            sheet.addValidationData(professionValidation);
        }

        // 生产厂家下拉框（E列，第2行到第1001行）
        // 引用隐藏Sheet中的AD列数据（数据源!$AD$2:$AD$xxx）
        if (manufacturerArray != null && manufacturerArray.length > 0) {
            CellRangeAddressList manufacturerRange = new CellRangeAddressList(1, 1000, 4, 4);
            String manufacturerFormula = "数据源!$AD$2:$AD$" + (manufacturerArray.length + 1);
            DataValidationConstraint manufacturerConstraint =
                validationHelper.createFormulaListConstraint(manufacturerFormula);
            DataValidation manufacturerValidation =
                validationHelper.createValidation(manufacturerConstraint, manufacturerRange);
            manufacturerValidation.setShowErrorBox(true);
            manufacturerValidation.setSuppressDropDownArrow(true);
            sheet.addValidationData(manufacturerValidation);
        }

        // 计数单位下拉框（J列，第2行到第1001行）
        // 引用隐藏Sheet中的AE列数据（数据源!$AE$2:$AE$xxx）
        if (unitArray != null && unitArray.length > 0) {
            CellRangeAddressList unitRange = new CellRangeAddressList(1, 1000, 9, 9);
            String unitFormula = "数据源!$AE$2:$AE$" + (unitArray.length + 1);
            DataValidationConstraint unitConstraint =
                validationHelper.createFormulaListConstraint(unitFormula);
            DataValidation unitValidation =
                validationHelper.createValidation(unitConstraint, unitRange);
            unitValidation.setShowErrorBox(true);
            unitValidation.setSuppressDropDownArrow(true);
            sheet.addValidationData(unitValidation);
        }
    }

    /**
     * 创建表头样式
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 背景色（浅灰色）
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 字体
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);

        return style;
    }

    /**
     * 创建居中样式（可编辑）
     */
    private static CellStyle createCenterStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        return style;
    }

    /**
     * 为特征值列添加批注（显示填写说明）
     */
    private static void addPropertyComments(Sheet mainSheet, Map<String, List<EquipmentProperty>> typePropertiesMap) {
        // 获取绘图patriarch（用于创建批注）
        Drawing<?> drawing = mainSheet.createDrawingPatriarch();
        CreationHelper factory = mainSheet.getWorkbook().getCreationHelper();

        // 在表头行的第一个特征值列（M列）添加批注说明
        Row headerRow = mainSheet.getRow(0);
        if (headerRow != null) {
            Cell valueHeaderCell = headerRow.getCell(12); // M列（第一个特征值列）

            if (valueHeaderCell != null) {
                // 创建批注锚点（批注显示位置）
                ClientAnchor anchor = factory.createClientAnchor();
                anchor.setCol1(12); // M列
                anchor.setCol2(15); // 批注宽度跨3列
                anchor.setRow1(0);  // 表头行
                anchor.setRow2(6);  // 批注高度跨6行

                // 创建批注
                Comment comment = drawing.createCellComment(anchor);
                RichTextString commentText = factory.createRichTextString(
                    "【特征值填写说明】\n\n" +
                    "1. 先在A列选择装备类型\n\n" +
                    "2. 特征项名称会自动填充到L、N、P等列\n\n" +
                    "3. 请在对应的特征值列（M、O、Q等）中填写值\n\n" +
                    "4. 部分特征项有可选值限制，请在\"数据源\"Sheet中查看每个装备类型的可选值列表\n\n" +
                    "5. 对于有可选值的特征项，请从可选值中选择填写（多个值用英文逗号分隔）"
                );
                comment.setString(commentText);
                comment.setAuthor("系统");

                // 将批注附加到单元格
                valueHeaderCell.setCellComment(comment);
            }
        }

        // 在第一行数据行的第一个特征值列添加示例批注
        Row firstDataRow = mainSheet.getRow(1);
        if (firstDataRow != null) {
            Cell valueCell = firstDataRow.getCell(12); // M列

            if (valueCell != null) {
                // 创建批注锚点
                ClientAnchor anchor = factory.createClientAnchor();
                anchor.setCol1(12);
                anchor.setCol2(15);
                anchor.setRow1(1);
                anchor.setRow2(5);

                // 创建批注
                Comment comment = drawing.createCellComment(anchor);
                RichTextString commentText = factory.createRichTextString(
                    "【填写示例】\n\n" +
                    "• 如果左侧特征项是\"电压等级\"，可选值可能是：10kV,35kV,110kV\n" +
                    "  请从中选择一个填写，如：35kV\n\n" +
                    "• 如果左侧特征项是\"功率\"，可能需要手动输入，如：500kW\n\n" +
                    "• 具体可选值请查看\"数据源\"Sheet"
                );
                comment.setString(commentText);
                comment.setAuthor("系统");

                // 将批注附加到单元格
                valueCell.setCellComment(comment);
            }
        }
    }

    /**
     * 创建居中样式（锁定/只读）
     */
    private static CellStyle createLockedCenterStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 锁定单元格
        style.setLocked(true);

        // 背景色（浅黄色，表示只读）
        style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return style;
    }

    /**
     * 创建日期样式
     */
    private static CellStyle createDateStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        // 日期格式
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("yyyy-MM-dd"));

        return style;
    }

    /**
     * 输出到HTTP响应
     */
    private static void writeToResponse(HttpServletResponse response, Workbook workbook, 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 + ".xlsx");

        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();
        } finally {
            workbook.close();
        }
    }
}
