package com.yd.oss.service.service.impl;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.yd.common.utils.CommonUtil;
import com.yd.common.utils.DateUtil;
import com.yd.oss.service.dto.FileProdDto;
import com.yd.oss.service.service.AppointmentExcelService;
import com.yd.oss.service.service.ExcelService;
import com.yd.oss.service.service.IFileTemplateService;
import com.yd.oss.service.service.OssService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.io.*;
import java.util.*;

/**
 * 预约Excel服务类
 */
@Slf4j
@Service
public class AppointmentExcelServiceImpl implements AppointmentExcelService {

    @Resource
    private OssService ossService;

    @Resource
    private ExcelService excelService;

    @Resource
    private IFileTemplateService iFileTemplateService;

    /**
     * 导出预约信息并上传到OSS
     * @param data
     * @param ossObjectKey
     * @param fileName
     * @return
     */
    public String exportAndUploadToOss(Map<String, Object> data, String ossObjectKey, String fileName) {
        File tempFile = null;
        File processedFile = null;

        try {
            // 1. 从OSS下载模板到临时文件
            tempFile = excelService.downloadTemplateToTempFile(ossObjectKey);

            // 2. 使用临时文件路径创建TemplateExportParams
            TemplateExportParams params = new TemplateExportParams(tempFile.getAbsolutePath());
            params.setSheetName(new String[]{"預約信息及個人資料", "健康信息及聲明", "財務資料分析(FNA)"});
            params.setScanAllsheet(true);

            // 3. 导出Excel
            Workbook workbook = ExcelExportUtil.exportExcel(params, data);

            // 4. 处理受益人动态行
            processedFile = processBeneficiaryRows(workbook, data);

            // 5. 重新读取处理后的工作簿
            workbook.close();
            workbook = WorkbookFactory.create(processedFile);

            // 6. 将Workbook转换为字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            byte[] excelBytes = outputStream.toByteArray();

            // 7. 上传到OSS
            String ossUrl = ossService.upload(excelBytes, fileName);

            // 8. 关闭资源
            outputStream.close();
            workbook.close();

            return ossUrl;

        } catch (Exception e) {
            throw new RuntimeException("从OSS模板导出失败", e);
        } finally {
            // 9. 删除临时文件
            if (tempFile != null && tempFile.exists()) {
                tempFile.delete();
            }
            if (processedFile != null && processedFile.exists()) {
                processedFile.delete();
            }
        }
    }

    /**
     * 处理受益人动态行
     * @param workbook
     * @param data
     * @return
     * @throws IOException
     */
    public File processBeneficiaryRows(Workbook workbook, Map<String, Object> data) throws IOException {
        Sheet sheet = workbook.getSheet("預約信息及個人資料");

        // 查找受益人数据区域的起始行和结束行
        int beneficiaryTitleRowNum = -1;
        int secondHolderRowNum = -1;

        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    String value = cell.getStringCellValue();
                    if ("受益人资料".equals(value.trim())) {
                        beneficiaryTitleRowNum = row.getRowNum();
                    } else if ("第二持有人资料(只适用儿童单)".equals(value.trim())) {
                        secondHolderRowNum = row.getRowNum();
                        break;
                    }
                }
            }
            if (beneficiaryTitleRowNum != -1 && secondHolderRowNum != -1) {
                break;
            }
        }

        if (beneficiaryTitleRowNum == -1 || secondHolderRowNum == -1) {
            throw new RuntimeException("未找到受益人资料或第二持有人资料区域");
        }

        // 获取第二持有人资料中英文姓名的列宽作为参考
        int englishNameColumnWidth = -1;
        Row secondHolderRow = sheet.getRow(secondHolderRowNum + 2); // 假设第二持有人资料在标题行后两行
        if (secondHolderRow != null) {
            Cell englishNameCell = secondHolderRow.getCell(3); // 英文姓名通常在D列(索引3)
            if (englishNameCell != null) {
                englishNameColumnWidth = sheet.getColumnWidth(3); // 获取D列的宽度
            }
        }

        // 获取受益人列表
        List<Map<String, Object>> beneficiaries = (List<Map<String, Object>>) data.get("beneficiaryList");
        if (beneficiaries == null || beneficiaries.isEmpty()) {
            return excelService.saveWorkbookToTempFile(workbook);
        }

        // 创建样式 - 保持与模板一致的边框
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);

        // 创建浅黄色背景样式 (#F4F492)
        CellStyle yellowBgStyle = workbook.createCellStyle();
        yellowBgStyle.cloneStyleFrom(dataStyle);
        yellowBgStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        yellowBgStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());

        // 创建浅橙色背景样式 (#F8CBAD)
        CellStyle orangeBgStyle = workbook.createCellStyle();
        orangeBgStyle.cloneStyleFrom(dataStyle);
        orangeBgStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        orangeBgStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());

        // 计算需要插入的行数
        int existingBeneficiaryRows = secondHolderRowNum - beneficiaryTitleRowNum - 1; // 减去标题行
        int neededBeneficiaryRows = beneficiaries.size() * 4 + (beneficiaries.size() - 1); // 每个受益人占4行，加上空行

        int rowsToInsert = neededBeneficiaryRows - existingBeneficiaryRows;

        // 修复：在移动行之前处理合并区域
        if (rowsToInsert != 0) {
            // 获取并移除受影响的合并区域
            List<CellRangeAddress> mergedRegionsToRemove = new ArrayList<>();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                // 如果合并区域在移动的范围内，需要移除
                if (mergedRegion.getFirstRow() >= secondHolderRowNum) {
                    mergedRegionsToRemove.add(mergedRegion);
                }
            }

            // 移除受影响的合并区域
            for (CellRangeAddress mergedRegion : mergedRegionsToRemove) {
                sheet.removeMergedRegion(sheet.getMergedRegions().indexOf(mergedRegion));
            }

            // 下移后续内容，包括第二持有人资料
            sheet.shiftRows(secondHolderRowNum, sheet.getLastRowNum(), rowsToInsert, true, false);
            secondHolderRowNum += rowsToInsert; // 更新第二持有人资料的行号

            // 重新添加合并区域（位置已自动调整）
            // 这里不需要手动重新添加，因为shiftRows会自动处理合并区域的移动
        }

        // 清除模板中的示例数据（保留"受益人资料"标题行）
        for (int i = beneficiaryTitleRowNum + 1; i < secondHolderRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                sheet.removeRow(row);
            }
        }

        // 插入新的受益人数据
        int currentRow = beneficiaryTitleRowNum + 1; // 从标题行下一行开始（空行）
        for (int i = 0; i < beneficiaries.size(); i++) {
            Map<String, Object> beneficiary = beneficiaries.get(i);

            // 1. 添加受益人标题行
            Row titleRow = sheet.createRow(currentRow++);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("受益人" + (i + 1));
            titleCell.setCellStyle(dataStyle);

            // 2. 添加姓名行
            Row nameRow = sheet.createRow(currentRow++);
            // 中文姓名
            Cell nameLabelCell = nameRow.createCell(0);
            nameLabelCell.setCellValue("中文姓名");
            nameLabelCell.setCellStyle(dataStyle);
            Cell nameValueCell = nameRow.createCell(1);
            nameValueCell.setCellValue((String) beneficiary.get("beneficiaryName"));
            nameValueCell.setCellStyle(yellowBgStyle); // 应用浅黄色背景

            // 英文姓名
            Cell enNameLabelCell = nameRow.createCell(2);
            enNameLabelCell.setCellValue("英文姓名 (同护照)");
            enNameLabelCell.setCellStyle(dataStyle);
            Cell enNameValueCell = nameRow.createCell(3);
            enNameValueCell.setCellValue((String) beneficiary.get("beneficiaryNameEn"));
            enNameValueCell.setCellStyle(yellowBgStyle); // 应用浅黄色背景

            // 设置英文姓名列宽与第二持有人资料一致
            if (englishNameColumnWidth != -1) {
                sheet.setColumnWidth(3, englishNameColumnWidth);
            }

            // 3. 添加生日/ID行
            Row infoRow = sheet.createRow(currentRow++);
            // 出生日期
            Cell birthLabelCell = infoRow.createCell(0);
            birthLabelCell.setCellValue("出生日期 (西元 年/月/日)");
            birthLabelCell.setCellStyle(dataStyle);
            Cell birthValueCell = infoRow.createCell(1);
            birthValueCell.setCellValue(DateUtil.formatDate(beneficiary.get("beneficiaryBirthTime")));
            birthValueCell.setCellStyle(yellowBgStyle); // 应用浅黄色背景

            // 身份证号码
            Cell idLabelCell = infoRow.createCell(2);
            idLabelCell.setCellValue("身份证号码");
            idLabelCell.setCellStyle(dataStyle);
            Cell idValueCell = infoRow.createCell(3);
            idValueCell.setCellValue((String) beneficiary.get("beneficiaryIdNumber"));
            idValueCell.setCellStyle(yellowBgStyle); // 应用浅黄色背景

            // 4. 添加关系/比例行
            Row relationRow = sheet.createRow(currentRow++);
            // 與受保人關係
            Cell relationLabelCell = relationRow.createCell(0);
            relationLabelCell.setCellValue("與受保人關係");
            relationLabelCell.setCellStyle(dataStyle);
            Cell relationValueCell = relationRow.createCell(1);
            relationValueCell.setCellValue((String) beneficiary.get("beneficiaryInsurantRel"));
            relationValueCell.setCellStyle(orangeBgStyle); // 应用浅橙色背景

            // 受益比例
            Cell percentLabelCell = relationRow.createCell(2);
            percentLabelCell.setCellValue("受益比例 (%)");
            percentLabelCell.setCellStyle(dataStyle);
            Cell percentValueCell = relationRow.createCell(3);
            percentValueCell.setCellValue(CommonUtil.formatPercentage(beneficiary.get("beneficiaryBenefitRatio")));
            percentValueCell.setCellStyle(yellowBgStyle); // 应用浅黄色背景

            // 5. 在受益人之间添加空行（除了最后一个受益人）
            if (i < beneficiaries.size() - 1) {
                currentRow++; // 添加空行
            }
        }

        // 确保第二持有人资料上方有一行空行
        secondHolderRow = sheet.getRow(secondHolderRowNum);
        if (secondHolderRow != null) {
            // 修复：在添加空行前检查并处理合并区域
            sheet.shiftRows(secondHolderRowNum, sheet.getLastRowNum(), 1, true, false);
            // 创建一个空行
            Row emptyRow = sheet.createRow(secondHolderRowNum);
            for (int i = 0; i < 4; i++) {
                Cell cell = emptyRow.createCell(i);
                cell.setCellValue("");
            }
        }

        return excelService.saveWorkbookToTempFile(workbook);
    }

    /**
     * 导出excel-预约信息
     * @param data
     * @param templateType
     * @param appointmentBizId
     * @return
     */
    @Override
    public String exportAppointment(Map<String, Object> data,
                                    String templateType,
                                    String appointmentBizId) {
        // 获取模板信息
        FileProdDto fileProdDto = iFileTemplateService.getFileProd("", templateType);
        // 生成文件名
        String fileName = "预约信息_" + appointmentBizId + "_" + System.currentTimeMillis() + ".xlsx";
        // 导出并上传到OSS
        return exportAndUploadToOss(data, fileProdDto.getFileKey(), fileName);
    }
}
