package com.zhyc.module.produce.breed.service.impl;

import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.ArrayList;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.io.IOException;
import java.io.InputStream;
import javax.servlet.http.HttpServletResponse;

import com.zhyc.module.produce.breed.domain.ScBreedPlan;
import com.zhyc.module.produce.breed.domain.ScBreedPlanGenerate;
import com.zhyc.module.produce.breed.mapper.ScBreedPlanGenerateMapper;
import com.zhyc.module.produce.breed.mapper.ScBreedPlanMapper;
import com.zhyc.module.produce.breed.service.IScBreedPlanGenerateService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import com.zhyc.common.utils.SecurityUtils;
import org.springframework.util.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.RequestParam;

/**
 * 配种计划生成Service业务层处理
 *
 * @author ruoyi
 * @date 2025-07-16
 */
@Service
public class ScBreedPlanGenerateServiceImpl implements IScBreedPlanGenerateService
{
    @Autowired
    private ScBreedPlanGenerateMapper scBreedPlanGenerateMapper;

    @Autowired
    private ScBreedPlanMapper scBreedPlanMapper;

    /**
     * 查询配种计划生成
     */
    @Override
    public ScBreedPlanGenerate selectScBreedPlanGenerateById(Long id)
    {
        return scBreedPlanGenerateMapper.selectScBreedPlanGenerateById(id);
    }

    /**
     * 查询配种计划生成列表
     */
    @Override
    public List<ScBreedPlanGenerate> selectScBreedPlanGenerateList(ScBreedPlanGenerate scBreedPlanGenerate)
    {
        return scBreedPlanGenerateMapper.selectScBreedPlanGenerateList(scBreedPlanGenerate);
    }

    /**
     * 筛选符合条件的母羊
     */
    @Override
    public List<Map<String, Object>> selectEligibleEwe(@RequestParam(value = "manageTags", required = false) String manageTags)
    {
        return scBreedPlanGenerateMapper.selectEligibleEwe();
    }

    /**
     * 筛选符合条件的公羊
     */
    @Override
    public List<Map<String, Object>> selectEligibleRam(@RequestParam(value = "manageTags", required = false) String manageTags)
    {
        return scBreedPlanGenerateMapper.selectEligibleRam();
    }

    /**
     * 导出已选母羊公羊配对表
     * 按轮询方式将母羊分配给公羊，导出Excel，两列：母羊耳号、公羊耳号
     *
     * @param response HTTP响应
     * @param eweIds   已选母羊ID列表
     * @param ramIds   已选公羊ID列表
     */
    @Override
    public void exportSelectedPairs(HttpServletResponse response, List<Long> eweIds, List<Long> ramIds)
    {
        try {
            // 查询母羊耳号（gender=1）
            List<Map<String, Object>> eweList = scBreedPlanGenerateMapper.selectEarNumbersByIds(eweIds, 1);
            // 查询公羊耳号（gender=2）
            List<Map<String, Object>> ramList = scBreedPlanGenerateMapper.selectEarNumbersByIds(ramIds, 2);

            // 建立ID->耳号 的快速查找
            Map<Long, String> eweEarMap = new HashMap<>();
            for (Map<String, Object> e : eweList) {
                eweEarMap.put(Long.valueOf(e.get("id").toString()), e.get("manage_tags").toString());
            }
            Map<Long, String> ramEarMap = new HashMap<>();
            for (Map<String, Object> r : ramList) {
                ramEarMap.put(Long.valueOf(r.get("id").toString()), r.get("manage_tags").toString());
            }

            // 母羊列、公羊列各自独立写入，行数取两者最大值，不做轮询配对
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("配种配对表");

            // 母羊表头样式（粉色）
            CellStyle eweHeaderStyle = workbook.createCellStyle();
            Font eweFont = workbook.createFont();
            eweFont.setBold(true);
            eweFont.setFontHeightInPoints((short) 12);
            eweHeaderStyle.setFont(eweFont);
            eweHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
            eweHeaderStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
            eweHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            setBorder(eweHeaderStyle);

            // 公羊表头样式（浅蓝）
            CellStyle ramHeaderStyle = workbook.createCellStyle();
            Font ramFont = workbook.createFont();
            ramFont.setBold(true);
            ramFont.setFontHeightInPoints((short) 12);
            ramHeaderStyle.setFont(ramFont);
            ramHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
            ramHeaderStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
            ramHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            setBorder(ramHeaderStyle);

            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            setBorder(dataStyle);

            // 写入表头
            Row headerRow = sheet.createRow(0);
            Cell c1 = headerRow.createCell(0);
            c1.setCellValue("母羊耳号");
            c1.setCellStyle(eweHeaderStyle);
            Cell c2 = headerRow.createCell(1);
            c2.setCellValue("公羊耳号");
            c2.setCellStyle(ramHeaderStyle);

            // 母羊列和公羊列各自独立写入，行数取两者最大值
            int maxRows = Math.max(eweIds.size(), ramIds.size());
            for (int i = 0; i < maxRows; i++) {
                Row row = sheet.createRow(i + 1);
                Cell dc1 = row.createCell(0);
                if (i < eweIds.size()) {
                    dc1.setCellValue(eweEarMap.getOrDefault(eweIds.get(i), ""));
                }
                dc1.setCellStyle(dataStyle);
                Cell dc2 = row.createCell(1);
                if (i < ramIds.size()) {
                    dc2.setCellValue(ramEarMap.getOrDefault(ramIds.get(i), ""));
                }
                dc2.setCellStyle(dataStyle);
            }

            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(1, 5000);

            // 响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = java.net.URLEncoder.encode("配种配对表", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            workbook.write(response.getOutputStream());
            workbook.close();

        } catch (IOException e) {
            throw new RuntimeException("导出配对表失败", e);
        }
    }

    /** 给样式设置边框 */
    private void setBorder(CellStyle style) {
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
    }

    /**
     * 解析导入的配对Excel，返回配对预览列表
     * Excel格式：第一列=母羊耳号，第二列=公羊耳号，第一行为表头（跳过）
     *
     * @param file 上传的Excel文件
     * @return 配对列表，每项包含 eweEarNo、ramEarNo、eweId（可能null）、ramId（可能null）
     */
    @Override
    public List<Map<String, Object>> parsePairsFromExcel(MultipartFile file)
    {
        List<Map<String, Object>> result = new ArrayList<>();
        try (InputStream is = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(is)) {

            Sheet sheet = workbook.getSheetAt(0);
            int lastRow = sheet.getLastRowNum();

            // 从第2行（index=1）开始，跳过表头
            for (int i = 1; i <= lastRow; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                String eweEarNo = getCellStringValue(row.getCell(0));
                String ramEarNo = getCellStringValue(row.getCell(1));

                if (eweEarNo.isEmpty() && ramEarNo.isEmpty()) continue;

                Map<String, Object> pair = new HashMap<>();
                pair.put("eweEarNo", eweEarNo);
                pair.put("ramEarNo", ramEarNo);
                pair.put("rowIndex", i);

                // 尝试查找对应的ID（用于校验）
                Long eweId = null;
                Long ramId = null;
                if (!eweEarNo.isEmpty()) {
                    eweId = scBreedPlanGenerateMapper.selectSheepIdByManageTags(eweEarNo, 1);  // 母羊 gender=1
                }
                if (!ramEarNo.isEmpty()) {
                    ramId = scBreedPlanGenerateMapper.selectSheepIdByManageTags(ramEarNo, 2);  // 公羊 gender=2
                }

                pair.put("eweId", eweId);
                pair.put("ramId", ramId);
                // 校验状态
                if (eweEarNo.isEmpty()) {
                    pair.put("valid", false);
                    pair.put("error", "母羊耳号不能为空");
                } else if (ramEarNo.isEmpty()) {
                    pair.put("valid", false);
                    pair.put("error", "公羊耳号不能为空");
                } else if (eweId == null) {
                    pair.put("valid", false);
                    pair.put("error", "母羊耳号[" + eweEarNo + "]未找到");
                } else if (ramId == null) {
                    pair.put("valid", false);
                    pair.put("error", "公羊耳号[" + ramEarNo + "]未找到");
                } else {
                    pair.put("valid", true);
                    pair.put("error", "");
                }

                result.add(pair);
            }

        } catch (Exception e) {
            throw new RuntimeException("解析Excel失败：" + e.getMessage(), e);
        }
        return result;
    }

    /** 安全读取单元格字符串值 */
    private String getCellStringValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue().trim();
            case NUMERIC: return String.valueOf((long) cell.getNumericCellValue());
            default: return "";
        }
    }

    /**
     * 根据导入的配对数据（耳号列表）生成配种计划
     * pairs 中每项包含 eweEarNo 和 ramEarNo（或 eweId/ramId）
     *
     * @param planType 计划类型
     * @param pairs    配对数据列表
     * @return 生成的配种计划
     */
    @Override
    @Transactional
    public ScBreedPlanGenerate autoGenerateBreedPlanFromPairs(Integer planType, List<Map<String, Object>> pairs)
    {
        // 收集所有有效的 eweId / ramId（去重统计用）
        List<Long> eweIds = new ArrayList<>();
        List<Long> ramIds = new ArrayList<>();

        for (Map<String, Object> pair : pairs) {
            Long eweId = null;
            Long ramId = null;

            // 优先用已解析的 ID；其次用耳号现查
            if (pair.get("eweId") != null) {
                eweId = Long.valueOf(pair.get("eweId").toString());
            } else if (pair.get("eweEarNo") != null) {
                eweId = scBreedPlanGenerateMapper.selectSheepIdByManageTags(pair.get("eweEarNo").toString(), 1);  // 母羊 gender=1
            }
            if (pair.get("ramId") != null) {
                ramId = Long.valueOf(pair.get("ramId").toString());
            } else if (pair.get("ramEarNo") != null) {
                ramId = scBreedPlanGenerateMapper.selectSheepIdByManageTags(pair.get("ramEarNo").toString(), 2);  // 公羊 gender=2
            }

            if (eweId == null || ramId == null) continue; // 跳过无效行

            eweIds.add(eweId);
            if (!ramIds.contains(ramId)) ramIds.add(ramId);
        }

        if (eweIds.isEmpty()) {
            throw new RuntimeException("没有有效的配对数据，请检查耳号是否存在");
        }

        // 创建计划汇总记录
        ScBreedPlanGenerate planGenerate = new ScBreedPlanGenerate();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(new Date());

        String planTypeName;
        switch (planType) {
            case 1: planTypeName = "供体母羊配种计划"; break;
            case 2: planTypeName = "同期发情人工授精计划"; break;
            case 3: planTypeName = "本交配种计划"; break;
            case 4: planTypeName = "自然发情人工授精计划"; break;
            default: planTypeName = "配种计划"; break;
        }

        planGenerate.setPlanName(dateStr + planTypeName);
        planGenerate.setPlanType(planType);
        planGenerate.setPlanDate(new Date());
        planGenerate.setTotalEweCount(eweIds.size());
        planGenerate.setTotalRamCount(ramIds.size());
        double ratio = ramIds.isEmpty() ? 0 : (double) eweIds.size() / ramIds.size();
        planGenerate.setBreedRatio(String.format("%.1f:1", ratio));
        planGenerate.setStatus(0); // 待审批
        planGenerate.setCreateBy(SecurityUtils.getUsername());
        planGenerate.setCreateTime(new Date());

        scBreedPlanGenerateMapper.insertScBreedPlanGenerate(planGenerate);

        // 按导入的配对逐条插入临时计划（保留用户指定的配对关系）
        for (Map<String, Object> pair : pairs) {
            Long eweId = null;
            Long ramId = null;

            if (pair.get("eweId") != null) {
                eweId = Long.valueOf(pair.get("eweId").toString());
            } else if (pair.get("eweEarNo") != null) {
                eweId = scBreedPlanGenerateMapper.selectSheepIdByManageTags(pair.get("eweEarNo").toString(), 1);  // 母羊 gender=1
            }
            if (pair.get("ramId") != null) {
                ramId = Long.valueOf(pair.get("ramId").toString());
            } else if (pair.get("ramEarNo") != null) {
                ramId = scBreedPlanGenerateMapper.selectSheepIdByManageTags(pair.get("ramEarNo").toString(), 2);  // 公羊 gender=2
            }

            if (eweId == null || ramId == null) continue;

            ScBreedPlan breedPlan = new ScBreedPlan();
            breedPlan.setRamId(ramId.toString());
            breedPlan.setEweId(eweId.toString());
            breedPlan.setBreedType(Long.valueOf(planType));
            scBreedPlanGenerateMapper.insertTempBreedPlan(planGenerate.getId(), breedPlan);
        }

        return planGenerate;
    }

    /**
     * 自动生成配种计划（按比例自动分配，原有逻辑不变）
     */
    @Override
    @Transactional
    public ScBreedPlanGenerate autoGenerateBreedPlan(Integer planType, String planName, List<Long> eweIds, List<Long> ramIds)
    {
        ScBreedPlanGenerate planGenerate = new ScBreedPlanGenerate();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(new Date());
        String planTypeName;
        switch (planType) {
            case 1: planTypeName = "供体母羊配种计划"; break;
            case 2: planTypeName = "同期发情人工授精计划"; break;
            case 3: planTypeName = "本交配种计划"; break;
            case 4: planTypeName = "自然发情人工授精计划"; break;
            default: planTypeName = "未知配种计划"; break;
        }
        planName = dateStr + planTypeName;

        planGenerate.setPlanName(planName);
        planGenerate.setPlanType(planType);
        planGenerate.setPlanDate(new Date());
        planGenerate.setTotalEweCount(eweIds.size());
        planGenerate.setTotalRamCount(ramIds.size());
        double ratio = (double) eweIds.size() / ramIds.size();
        planGenerate.setBreedRatio(String.format("%.1f:1", ratio));
        planGenerate.setStatus(0);
        planGenerate.setCreateBy(SecurityUtils.getUsername());
        planGenerate.setCreateTime(new Date());

        scBreedPlanGenerateMapper.insertScBreedPlanGenerate(planGenerate);
        generateBreedPlanDetails(planGenerate.getId(), eweIds, ramIds, planType);

        return planGenerate;
    }

    private void generateBreedPlanDetails(Long planGenerateId, List<Long> eweIds, List<Long> ramIds, Integer planType)
    {
        int ramIndex = 0;
        int ewesPerRam = (int) Math.ceil((double) eweIds.size() / ramIds.size());

        for (int i = 0; i < eweIds.size(); i++) {
            ScBreedPlan breedPlan = new ScBreedPlan();
            breedPlan.setRamId(ramIds.get(ramIndex).toString());
            breedPlan.setEweId(eweIds.get(i).toString());
            breedPlan.setBreedType(Long.valueOf(planType));

            scBreedPlanGenerateMapper.insertTempBreedPlan(planGenerateId, breedPlan);

            if ((i + 1) % ewesPerRam == 0 && ramIndex < ramIds.size() - 1) {
                ramIndex++;
            }
        }
    }

    /**
     * 新增配种计划生成
     */
    @Override
    public int insertScBreedPlanGenerate(ScBreedPlanGenerate scBreedPlanGenerate)
    {
        scBreedPlanGenerate.setCreateTime(new Date());
        return scBreedPlanGenerateMapper.insertScBreedPlanGenerate(scBreedPlanGenerate);
    }

    /**
     * 修改配种计划生成
     */
    @Override
    public int updateScBreedPlanGenerate(ScBreedPlanGenerate scBreedPlanGenerate)
    {
        scBreedPlanGenerate.setUpdateTime(new Date());
        return scBreedPlanGenerateMapper.updateScBreedPlanGenerate(scBreedPlanGenerate);
    }

    /**
     * 获取审批配种计划详情
     */
    @Override
    public Map<String, Object> getApproveBreedPlanDetails(Long id)
    {
        Map<String, Object> result = new HashMap<>();
        ScBreedPlanGenerate planGenerate = scBreedPlanGenerateMapper.selectScBreedPlanGenerateById(id);
        result.put("planInfo", planGenerate);
        List<Map<String, Object>> planDetails = scBreedPlanGenerateMapper.selectApproveBreedPlanDetails(id);
        result.put("planDetails", planDetails);
        List<Map<String, Object>> availableRams = scBreedPlanGenerateMapper.selectEligibleRam();
        result.put("availableRams", availableRams);
        return result;
    }

    /**
     * 确认审批配种计划
     */
    @Override
    @Transactional
    public int confirmApproveBreedPlan(Long planId, List<Map<String, Object>> planDetails, Integer status, String approveRemark)
    {
        ScBreedPlanGenerate planGenerate = new ScBreedPlanGenerate();
        planGenerate.setId(planId);
        planGenerate.setStatus(status);
        planGenerate.setApprover(SecurityUtils.getUsername());
        planGenerate.setApproveTime(new Date());
        planGenerate.setApproveRemark(approveRemark);
        planGenerate.setUpdateTime(new Date());

        int result = scBreedPlanGenerateMapper.updateScBreedPlanGenerate(planGenerate);

        if (result > 0 && status == 1) {
            if (planDetails != null && !planDetails.isEmpty()) {
                for (Map<String, Object> detail : planDetails) {
                    Long tempId = Long.valueOf(detail.get("id").toString());
                    Long ramId = Long.valueOf(detail.get("ram_id").toString());
                    Long breedType = Long.valueOf(detail.get("breed_type").toString());

                    Map<String, Object> updateParams = new HashMap<>();
                    updateParams.put("id", tempId);
                    updateParams.put("ramId", ramId);
                    updateParams.put("breedType", breedType);
                    scBreedPlanGenerateMapper.updateTempBreedPlan(updateParams);
                }
            }
            scBreedPlanGenerateMapper.transferTempToFormal(planId);
        }
        return result;
    }

    /**
     * 获取配种计划详情
     */
    @Override
    public Map<String, Object> getBreedPlanDetails(Long id)
    {
        Map<String, Object> result = new HashMap<>();
        ScBreedPlanGenerate planGenerate = scBreedPlanGenerateMapper.selectScBreedPlanGenerateById(id);
        result.put("planInfo", planGenerate);
        List<Map<String, Object>> planDetails = scBreedPlanGenerateMapper.selectBreedPlanDetails(id);
        result.put("planDetails", planDetails);
        return result;
    }

    /**
     * 导出配种计划详情（已审批）
     */
    @Override
    public void exportBreedPlanDetails(HttpServletResponse response, Long id)
    {
        try {
            ScBreedPlanGenerate planGenerate = scBreedPlanGenerateMapper.selectScBreedPlanGenerateById(id);
            if (planGenerate == null) throw new RuntimeException("配种计划不存在");
            if (planGenerate.getStatus() != 1) throw new RuntimeException("只有已审批的配种计划才能导出");

            List<Map<String, Object>> planDetails = scBreedPlanGenerateMapper.selectBreedPlanDetails(id);

            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("配种计划详情");

            CellStyle titleStyle = workbook.createCellStyle();
            Font titleFont = workbook.createFont();
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 16);
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);

            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setAlignment(HorizontalAlignment.CENTER);

            CellStyle eweHeaderStyle = workbook.createCellStyle();
            Font eweHeaderFont = workbook.createFont();
            eweHeaderFont.setBold(true);
            eweHeaderStyle.setFont(eweHeaderFont);
            eweHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
            eweHeaderStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
            eweHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            CellStyle ramHeaderStyle = workbook.createCellStyle();
            Font ramHeaderFont = workbook.createFont();
            ramHeaderFont.setBold(true);
            ramHeaderStyle.setFont(ramHeaderFont);
            ramHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
            ramHeaderStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
            ramHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            int rowNum = 0;

            Row titleRow = sheet.createRow(rowNum++);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(planGenerate.getPlanName() + " - 配种计划详情");
            titleCell.setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19));

            rowNum++;

            String planTypeNameStr;
            Integer type = planGenerate.getPlanType();
            if (type != null) {
                switch (type) {
                    case 1: planTypeNameStr = "供体母羊配种计划"; break;
                    case 2: planTypeNameStr = "同期发情人工授精计划"; break;
                    case 3: planTypeNameStr = "本交配种计划"; break;
                    case 4: planTypeNameStr = "自然发情人工授精计划"; break;
                    default: planTypeNameStr = "未知类型";
                }
            } else {
                planTypeNameStr = "";
            }

            Row infoRow1 = sheet.createRow(rowNum++);
            infoRow1.createCell(0).setCellValue("计划类型：");
            infoRow1.createCell(1).setCellValue(planTypeNameStr);
            infoRow1.createCell(3).setCellValue("计划日期：");
            infoRow1.createCell(4).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(planGenerate.getPlanDate()));

            Row infoRow2 = sheet.createRow(rowNum++);
            infoRow2.createCell(0).setCellValue("母羊数量：");
            infoRow2.createCell(1).setCellValue(planGenerate.getTotalEweCount());
            infoRow2.createCell(3).setCellValue("公羊数量：");
            infoRow2.createCell(4).setCellValue(planGenerate.getTotalRamCount());

            Row infoRow3 = sheet.createRow(rowNum++);
            infoRow3.createCell(0).setCellValue("配种比例：");
            infoRow3.createCell(1).setCellValue(planGenerate.getBreedRatio());
            infoRow3.createCell(3).setCellValue("创建人：");
            infoRow3.createCell(4).setCellValue(planGenerate.getCreateBy());

            rowNum++;

            Row groupHeaderRow = sheet.createRow(rowNum++);
            Cell gc1 = groupHeaderRow.createCell(1);
            gc1.setCellValue("母羊信息");
            gc1.setCellStyle(eweHeaderStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 12));
            Cell gc2 = groupHeaderRow.createCell(13);
            gc2.setCellValue("公羊信息");
            gc2.setCellStyle(ramHeaderStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 13, 19));

            Row headerRow = sheet.createRow(rowNum++);
            String[] headers = {
                    "序号",
                    "母羊耳号", "母羊品种", "母羊家系", "母羊类别", "繁育状态", "胎次", "月龄", "体重", "核心羊群", "是否种用", "羊舍", "备注",
                    "公羊耳号", "公羊品种", "公羊家系", "公羊类别", "生日", "月龄", "体重",
                    "配种类型"
            };
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                if (i == 0 || i == headers.length - 1) cell.setCellStyle(headerStyle);
                else if (i <= 12) cell.setCellStyle(eweHeaderStyle);
                else cell.setCellStyle(ramHeaderStyle);
            }

            for (int i = 0; i < planDetails.size(); i++) {
                Map<String, Object> detail = planDetails.get(i);
                Row dataRow = sheet.createRow(rowNum++);
                int colNum = 0;

                dataRow.createCell(colNum++).setCellValue(i + 1);
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_manage_tags"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_variety"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_family"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_sheep_type"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_breed_status"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_parity"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_month_age"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_current_weight"));
                dataRow.createCell(colNum++).setCellValue(getBooleanValue(detail, "ewe_is_core") ? "是" : "否");
                dataRow.createCell(colNum++).setCellValue(getBooleanValue(detail, "ewe_is_breeding") ? "是" : "否");
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_sheepfold_name"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ewe_comment"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_manage_tags"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_variety"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_family"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_sheep_type"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_birthday"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_month_age"));
                dataRow.createCell(colNum++).setCellValue(getStringValue(detail, "ram_current_weight"));

                Object breedTypeObj = detail.get("breed_type");
                String breedTypeName = "未知类型";
                if (breedTypeObj != null) {
                    try {
                        int tv = Integer.parseInt(breedTypeObj.toString());
                        switch (tv) {
                            case 1: breedTypeName = "供体母羊配种"; break;
                            case 2: breedTypeName = "同期发情人工授精"; break;
                            case 3: breedTypeName = "本交"; break;
                            case 4: breedTypeName = "自然发情人工授精"; break;
                        }
                    } catch (NumberFormatException ignored) {}
                }
                dataRow.createCell(colNum).setCellValue(breedTypeName);

                for (int j = 0; j < headers.length; j++) {
                    if (dataRow.getCell(j) != null) dataRow.getCell(j).setCellStyle(dataStyle);
                }
            }

            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
                if (sheet.getColumnWidth(i) < 2000) sheet.setColumnWidth(i, 2000);
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = java.net.URLEncoder.encode(planGenerate.getPlanName() + "_配种计划详情", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            workbook.write(response.getOutputStream());
            workbook.close();

        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败", e);
        }
    }

    private String getStringValue(Map<String, Object> map, String key) {
        Object value = map.get(key);
        return value != null ? value.toString() : "";
    }

    private boolean getBooleanValue(Map<String, Object> map, String key) {
        Object value = map.get(key);
        if (value == null) return false;
        if (value instanceof Boolean) return (Boolean) value;
        if (value instanceof Number) return ((Number) value).intValue() == 1;
        return "1".equals(value.toString()) || "true".equalsIgnoreCase(value.toString());
    }

    /**
     * 批量删除配种计划生成
     */
    @Override
    @Transactional
    public int deleteScBreedPlanGenerateByIds(Long[] ids)
    {
        for (Long id : ids) {
            scBreedPlanGenerateMapper.deleteTempBreedPlanByPlanId(id);
        }
        return scBreedPlanGenerateMapper.deleteScBreedPlanGenerateByIds(ids);
    }

    /**
     * 删除配种计划生成信息
     */
    @Override
    @Transactional
    public int deleteScBreedPlanGenerateById(Long id)
    {
        scBreedPlanGenerateMapper.deleteTempBreedPlanByPlanId(id);
        return scBreedPlanGenerateMapper.deleteScBreedPlanGenerateById(id);
    }

    @Override
    public List<String> searchEarNumbers(String query) {
        return scBreedPlanGenerateMapper.searchEarNumbers(query);
    }
}