package com.zhyc.module.base.util;

import com.zhyc.module.base.domain.SheepPedigree;
import com.zhyc.module.base.service.ISheepPedigreeService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.zhyc.common.utils.DictUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * 羊只系谱自定义Excel导出工具（适配羊只系谱表（2）样式）
 */
@Component
public class SheepPedigreeExcelUtils {
    // 日期格式化
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

    // 注入Service接口
    @Autowired
    private ISheepPedigreeService sheepPedigreeService;

    /**
     * 导出指定bsManageTags的羊只系谱（符合羊只系谱表（2）样式）
     */
    public void export(HttpServletResponse response, List<SheepPedigree> dataList) throws Exception {
        // 1. 创建Excel工作簿和工作表
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("羊只系谱");
        // 设置列宽（6列，索引0-5）
        sheet.setColumnWidth(0, 15 * 256);  // 第1列
        sheet.setColumnWidth(1, 15 * 256);  // 第2列
        sheet.setColumnWidth(2, 15 * 256);  // 第3列
        sheet.setColumnWidth(3, 15 * 256);  // 第4列
        sheet.setColumnWidth(4, 15 * 256);  // 第5列
        sheet.setColumnWidth(5, 15 * 256);  // 第6列

        // 2. 定义样式（新增：通用边框样式，所有单元格都应用）
        CellStyle titleStyle = createTitleStyle(workbook);
        CellStyle blockTitleStyle = createBlockTitleStyle(workbook);
        CellStyle contentStyle = createContentStyle(workbook);
        CellStyle borderStyle = createBorderStyle(workbook); // 纯边框样式，用于空单元格

        int rowNum = 0;
        // 遍历数据
        for (SheepPedigree pedigree : dataList) {
            // ========== 1. 大标题：羊只系谱 ==========
            Row titleRow = sheet.createRow(rowNum++);
            titleRow.setHeightInPoints(25);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("羊只系谱");
            titleCell.setCellStyle(titleStyle);
            // 给标题行所有列应用边框
            fillRowWithBorder(titleRow, 0, 5, borderStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 0, 5));

            // ========== 2. 基础信息区块 ==========
            // 普通耳号
            Row earTagRow = sheet.createRow(rowNum++);
            earTagRow.setHeightInPoints(20);
            Cell earTagLabelCell = earTagRow.createCell(0);
            earTagLabelCell.setCellValue("普通耳号");
            earTagLabelCell.setCellStyle(contentStyle);

            String earTagContent = pedigree.getBsManageTags() != null ? pedigree.getBsManageTags() : "";
            Cell earTagValueCell = earTagRow.createCell(1);
            earTagValueCell.setCellValue(earTagContent);
            earTagValueCell.setCellStyle(contentStyle);

            // 给当前行所有列填充边框
            fillRowWithBorder(earTagRow, 0, 5, borderStyle);
            CellRangeAddress earTagMerge = new CellRangeAddress(
                    rowNum - 1,
                    rowNum - 1,
                    1,
                    5
            );
            sheet.addMergedRegion(earTagMerge);

            // 品种 + 类别
            Row varietyRow = sheet.createRow(rowNum++);
            varietyRow.setHeightInPoints(20);
            Cell varietyLabelCell = varietyRow.createCell(0);
            varietyLabelCell.setCellValue("品种");
            varietyLabelCell.setCellStyle(contentStyle);

            String varietyContent = pedigree.getVariety() != null ? pedigree.getVariety() : "";
            Cell varietyValueCell = varietyRow.createCell(1);
            varietyValueCell.setCellValue(varietyContent);
            varietyValueCell.setCellStyle(contentStyle);

            Cell categoryLabelCell = varietyRow.createCell(2);
            categoryLabelCell.setCellValue("类别");
            categoryLabelCell.setCellStyle(contentStyle);

            String categoryContent = pedigree.getName() != null ? pedigree.getName() : "";
            Cell categoryValueCell = varietyRow.createCell(3);
            categoryValueCell.setCellValue(categoryContent);
            categoryValueCell.setCellStyle(contentStyle);

            // 填充边框
            fillRowWithBorder(varietyRow, 0, 5, borderStyle);
            CellRangeAddress categoryMerge = new CellRangeAddress(
                    rowNum - 1,
                    rowNum - 1,
                    3,
                    5
            );
            sheet.addMergedRegion(categoryMerge);

            // 性别 + 出生重量 + 出生日期
            Row genderRow = sheet.createRow(rowNum++);
            genderRow.setHeightInPoints(20);
            Cell genderLabelCell = genderRow.createCell(0);
            genderLabelCell.setCellValue("性别");
            genderLabelCell.setCellStyle(contentStyle);

            Cell genderValueCell = genderRow.createCell(1);
            genderValueCell.setCellValue(getGenderText(pedigree.getGender()));
            genderValueCell.setCellStyle(contentStyle);

            Cell birthWeightLabelCell = genderRow.createCell(2);
            birthWeightLabelCell.setCellValue("出生重量");
            birthWeightLabelCell.setCellStyle(contentStyle);

            Cell birthWeightValueCell = genderRow.createCell(3);
            String birthWeightContent = pedigree.getBirthWeight() != null ? pedigree.getBirthWeight() + "kg" : "";
            birthWeightValueCell.setCellValue(birthWeightContent);
            birthWeightValueCell.setCellStyle(contentStyle);

            Cell birthDateLabelCell = genderRow.createCell(4);
            birthDateLabelCell.setCellValue("出生日期");
            birthDateLabelCell.setCellStyle(contentStyle);

            Cell birthDateValueCell = genderRow.createCell(5);
            String birthDate = pedigree.getBirthday() != null ? DATE_FORMAT.format(pedigree.getBirthday()) : "";
            birthDateValueCell.setCellValue(birthDate);
            birthDateValueCell.setCellStyle(contentStyle);

            // 填充边框
            fillRowWithBorder(genderRow, 0, 5, borderStyle);

            // 毛色 + 月龄 + 状态
            Row furColorRow = sheet.createRow(rowNum++);
            furColorRow.setHeightInPoints(20);
            Cell furColorLabelCell = furColorRow.createCell(0);
            furColorLabelCell.setCellValue("毛色");
            furColorLabelCell.setCellStyle(contentStyle);

            Cell furColorValueCell = furColorRow.createCell(1);
            String furColorContent = pedigree.getSheepColor() != null ? pedigree.getSheepColor() : "";
            furColorValueCell.setCellValue(furColorContent);
            furColorValueCell.setCellStyle(contentStyle);

            Cell monthAgeLabelCell = furColorRow.createCell(2);
            monthAgeLabelCell.setCellValue("月龄");
            monthAgeLabelCell.setCellStyle(contentStyle);

            Cell monthAgeValueCell = furColorRow.createCell(3);
            String monthAgeContent = pedigree.getMonthAge() != null ? pedigree.getMonthAge() + "月" : "";
            monthAgeValueCell.setCellValue(monthAgeContent);
            monthAgeValueCell.setCellStyle(contentStyle);

            Cell statusLabelCell = furColorRow.createCell(4);
            statusLabelCell.setCellValue("状态");
            statusLabelCell.setCellStyle(contentStyle);

            Map<Object, String> statusDict = new HashMap<>();
            statusDict.put(1L, "在群");
            statusDict.put(2L, "不在群");
            statusDict.put(null, "");

            Object statusId = pedigree.getStatusId();
            String statusContent = statusDict.getOrDefault(statusId, "未知状态");

            Cell statusValueCell = furColorRow.createCell(5);
            statusValueCell.setCellValue(statusContent);
            statusValueCell.setCellStyle(contentStyle);

            // 填充边框
            fillRowWithBorder(furColorRow, 0, 5, borderStyle);

            // 繁育状态 + 来源
            Row breedStatusRow = sheet.createRow(rowNum++);
            breedStatusRow.setHeightInPoints(20);
            int currentRow = rowNum - 1;

            Cell breedStatusLabelCell = breedStatusRow.createCell(0);
            breedStatusLabelCell.setCellValue("繁育状态");
            breedStatusLabelCell.setCellStyle(contentStyle);

            Cell breedStatusValueCell = breedStatusRow.createCell(1);
            String breedStatusContent = "";
            try {
                Object breedObj = pedigree.getBreed();
                if (breedObj != null) {
                    breedStatusContent = breedObj.toString().trim();
                }
                if (breedStatusContent == null || breedStatusContent.isEmpty()) {
                    breedStatusContent = "";
                }
            } catch (Exception e) {
                breedStatusContent = "";
            }
            breedStatusValueCell.setCellValue(breedStatusContent);
            breedStatusValueCell.setCellStyle(contentStyle);

            Cell sourceLabelCell = breedStatusRow.createCell(2);
            sourceLabelCell.setCellValue("来源");
            sourceLabelCell.setCellStyle(contentStyle);

            Cell sourceValueCell = breedStatusRow.createCell(3);
            String sourceCode = pedigree.getSource() != null ? pedigree.getSource().toString() : "";
            String sourceContent = "";
            try {
                sourceContent = DictUtils.getDictLabel("source", sourceCode, "");
            } catch (Exception e) {
                sourceContent = "";
                System.err.println("来源字典映射失败：" + e.getMessage());
            }
            sourceValueCell.setCellValue(sourceContent);
            sourceValueCell.setCellStyle(contentStyle);

            // 填充边框
            fillRowWithBorder(breedStatusRow, 0, 5, borderStyle);
            CellRangeAddress sourceMerge = new CellRangeAddress(
                    currentRow,
                    currentRow,
                    3,
                    5
            );
            sheet.addMergedRegion(sourceMerge);

            // 等级 + 家系
            Row levelRow = sheet.createRow(rowNum++);
            levelRow.setHeightInPoints(20);
            int currentLevelRow = rowNum - 1;

            Cell levelLabelCell = levelRow.createCell(0);
            levelLabelCell.setCellValue("等级");
            levelLabelCell.setCellStyle(contentStyle);

            Cell levelValueCell = levelRow.createCell(1);
            String levelContent = pedigree.getGroupName() != null ? pedigree.getGroupName() : "";
            levelValueCell.setCellValue(levelContent);
            levelValueCell.setCellStyle(contentStyle);

            Cell familyLabelCell = levelRow.createCell(2);
            familyLabelCell.setCellValue("家系");
            familyLabelCell.setCellStyle(contentStyle);

            Cell familyValueCell = levelRow.createCell(3);
            String familyContent = pedigree.getFamily() != null ? pedigree.getFamily() : "";
            familyValueCell.setCellValue(familyContent);
            familyValueCell.setCellStyle(contentStyle);

            // 填充边框
            fillRowWithBorder(levelRow, 0, 5, borderStyle);
            CellRangeAddress familyMerge = new CellRangeAddress(
                    currentLevelRow,
                    currentLevelRow,
                    3,
                    5
            );
            sheet.addMergedRegion(familyMerge);

            // ========== 3. 系谱信息区块（最终修复版） ==========
            Row pedigreeTitleRow = sheet.createRow(rowNum++);
            pedigreeTitleRow.setHeightInPoints(25); // 区块标题行高
            Cell pedigreeTitleCell = pedigreeTitleRow.createCell(0);
            pedigreeTitleCell.setCellValue("系谱信息");
            pedigreeTitleCell.setCellStyle(blockTitleStyle);
            // 填充标题行边框
            fillRowWithBorder(pedigreeTitleRow, 0, 5, borderStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 0, 5));

            // 系谱区块起始行（此时rowNum是系谱内容第1行）
            int pedigreeStartRow = rowNum;
            int pedigreeTotalRows = 12;
            // 统一设置系谱12行的行高+填充边框
            for (int i = 0; i < pedigreeTotalRows; i++) {
                Row tempRow = sheet.createRow(pedigreeStartRow + i);
                tempRow.setHeightInPoints(20);
                // 给每行的0-5列都填充边框样式
                fillRowWithBorder(tempRow, 0, 5, borderStyle);
            }

            // ========== 1. 羊只列：第1列合并12行 ==========
            Row sheepRow = sheet.getRow(pedigreeStartRow);
            Cell sheepCell = sheepRow.createCell(0);
            String sheepValue = pedigree.getBsManageTags() != null && !pedigree.getBsManageTags().isEmpty()
                    ? "羊只：" + pedigree.getBsManageTags()
                    : "羊只：无";
            sheepCell.setCellValue(sheepValue);
            sheepCell.setCellStyle(contentStyle); // 覆盖为内容样式（带边框+字体）
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow,
                    pedigreeStartRow + 11,
                    0,
                    0
            ));

            // ========== 2. 父耳号：第2-3列合并前6行 ==========
            Row fatherRow = sheet.getRow(pedigreeStartRow);
            Cell fatherCell = fatherRow.createCell(1);
            String fatherValue = pedigree.getFatherManageTags() != null && !pedigree.getFatherManageTags().isEmpty()
                    ? "父耳号：" + pedigree.getFatherManageTags()
                    : "父耳号：无";
            fatherCell.setCellValue(fatherValue);
            fatherCell.setCellStyle(contentStyle);
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow,
                    pedigreeStartRow + 5,
                    1,
                    2
            ));

            // ========== 3. 母耳号：第2-3列合并后6行 ==========
            Row motherRow = sheet.getRow(pedigreeStartRow + 6);
            Cell motherCell = motherRow.createCell(1);
            String motherValue = pedigree.getMotherManageTags() != null && !pedigree.getMotherManageTags().isEmpty()
                    ? "母耳号：" + pedigree.getMotherManageTags()
                    : "母耳号：无";
            motherCell.setCellValue(motherValue);
            motherCell.setCellStyle(contentStyle);
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow + 6,
                    pedigreeStartRow + 11,
                    1,
                    2
            ));

            // ========== 4. 祖父耳号：第4-6列合并1-3行 ==========
            Row grandFatherRow = sheet.getRow(pedigreeStartRow);
            Cell grandFatherCell = grandFatherRow.createCell(3);
            String grandFatherValue = pedigree.getGrandfatherManageTags() != null && !pedigree.getGrandfatherManageTags().isEmpty()
                    ? "祖父耳号：" + pedigree.getGrandfatherManageTags()
                    : "祖父耳号：无";
            grandFatherCell.setCellValue(grandFatherValue);
            grandFatherCell.setCellStyle(contentStyle);
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow,
                    pedigreeStartRow + 2,
                    3,
                    5
            ));

            // ========== 5. 祖母耳号：第4-6列合并4-6行 ==========
            Row grandMotherRow = sheet.getRow(pedigreeStartRow + 3);
            Cell grandMotherCell = grandMotherRow.createCell(3);
            String grandMotherValue = pedigree.getGrandmotherManageTags() != null && !pedigree.getGrandmotherManageTags().isEmpty()
                    ? "祖母耳号：" + pedigree.getGrandmotherManageTags()
                    : "祖母耳号：无";
            grandMotherCell.setCellValue(grandMotherValue);
            grandMotherCell.setCellStyle(contentStyle);
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow + 3,
                    pedigreeStartRow + 5,
                    3,
                    5
            ));

            // ========== 6. 外祖父耳号：第4-6列合并7-9行 ==========
            Row maternalGrandFatherRow = sheet.getRow(pedigreeStartRow + 6);
            Cell maternalGrandFatherCell = maternalGrandFatherRow.createCell(3);
            String maternalGrandFatherValue = pedigree.getMaternalGrandfatherManageTags() != null && !pedigree.getMaternalGrandfatherManageTags().isEmpty()
                    ? "外祖父耳号：" + pedigree.getMaternalGrandfatherManageTags()
                    : "外祖父耳号：无";
            maternalGrandFatherCell.setCellValue(maternalGrandFatherValue);
            maternalGrandFatherCell.setCellStyle(contentStyle);
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow + 6,
                    pedigreeStartRow + 8,
                    3,
                    5
            ));

            // ========== 7. 外祖母耳号：第4-6列合并10-12行 ==========
            Row maternalGrandMotherRow = sheet.getRow(pedigreeStartRow + 9);
            Cell maternalGrandMotherCell = maternalGrandMotherRow.createCell(3);
            String maternalGrandMotherValue = pedigree.getMaternalGrandmotherManageTags() != null && !pedigree.getMaternalGrandmotherManageTags().isEmpty()
                    ? "外祖母耳号：" + pedigree.getMaternalGrandmotherManageTags()
                    : "外祖母耳号：无";
            maternalGrandMotherCell.setCellValue(maternalGrandMotherValue);
            maternalGrandMotherCell.setCellStyle(contentStyle);
            sheet.addMergedRegion(new CellRangeAddress(
                    pedigreeStartRow + 9,
                    pedigreeStartRow + 11,
                    3,
                    5
            ));

            // 更新rowNum到系谱区块结束行+1
            rowNum = pedigreeStartRow + pedigreeTotalRows;

        }

        // ========== 4. 输出Excel ==========
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("羊只系谱_" + System.currentTimeMillis(), "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
        workbook.close();
    }

    // ========== 新增：填充整行边框的工具方法 ==========
    private void fillRowWithBorder(Row row, int startCol, int endCol, CellStyle borderStyle) {
        for (int col = startCol; col <= endCol; col++) {
            Cell cell = row.getCell(col);
            if (cell == null) {
                cell = row.createCell(col);
            }
            // 仅当单元格无样式时设置边框样式（避免覆盖已有内容样式）
            if (cell.getCellStyle() == null || cell.getCellStyle().getBorderTop() == BorderStyle.NONE) {
                cell.setCellStyle(borderStyle);
            }
        }
    }

    // ========== 样式定义 ==========
    private static CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 16);
        font.setBold(true);
        style.setFont(font);
        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 CellStyle createBlockTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 区块标题带完整边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    private static CellStyle createContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容样式带完整边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    // 新增：纯边框样式（用于空单元格）
    private static CellStyle createBorderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 仅设置边框，无字体/背景色（避免覆盖内容样式）
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    // 性别数字转文字
    private static String getGenderText(Long gender) {
        if (gender == null) return "";
        switch (gender.intValue()) {
            case 1: return "母";
            case 2: return "公";
            case 3: return "阉羊";
            case 4: return "兼性";
            default: return "";
        }
    }
}