package hw.tagApi.service.utils;

import hw.tagApi.common.constant.HttpStatus;
import hw.tagApi.common.core.domain.AjaxResult;
import hw.tagApi.common.utils.poi.ExcelUtil;
import hw.tagApi.common.utils.spring.SpringUtils;
import hw.tagApi.common.utils.uuid.UUID;
import hw.tagApi.service.constant.ApiConstants;
import hw.tagApi.service.domain.HwTagRecord;
import hw.tagApi.system.domain.SysAttachInfo;
import hw.tagApi.system.service.ISysAttachInfoService;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 标签Excel工具类
 */
@Component
public class TagExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(TagExcelUtil.class);

    /**
     * 解析标签数据Excel
     *
     * @param is Excel文件输入流
     * @return 解析结果，包含订单信息、标签数据列表和文件名
     */
    public static Map<String, Object> parseTagExcel(InputStream is) {
        Map<String, Object> result = new HashMap<>();
        try (Workbook workbook = WorkbookFactory.create(is)) {
            Sheet sheet = workbook.getSheetAt(0);

            // 解析订单信息
            Map<String, String> orderInfo = parseOrderInfo(sheet);
            result.put("orderInfo", orderInfo);

            // 解析标签数据
            List<Map<String, String>> tagList = parseTagList(sheet);
            result.put("tagList", tagList);

            // 获取文件名
            String fileName = sheet.getSheetName();
            if (fileName == null || fileName.isEmpty()) {
                // 如果没有sheet名称，使用默认格式
                fileName = String.format("Lot_%s_%s.xlsx",
                    orderInfo.get("orderNo"),
                    new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
            }
            result.put("fileName", fileName);

            return result;
        } catch (Exception e) {
            log.error("解析Excel文件失败", e);
            throw new RuntimeException("解析Excel文件失败: " + e.getMessage());
        }
    }

    /**
     * 解析订单信息（前5行）
     */
    private static Map<String, String> parseOrderInfo(Sheet sheet) {
        Map<String, String> orderInfo = new HashMap<>();
        try {
            // 订单号 - 格式: 订单号：Lot20250109
            Row orderRow = sheet.getRow(0);
            String orderNoCell = getCellStringValue(orderRow.getCell(0));
            orderInfo.put("orderNo", orderNoCell.substring(orderNoCell.indexOf("：") + 1).trim());

            // 批次号 - 格式: 批次号：002
            Row batchRow = sheet.getRow(1);
            String batchNoCell = getCellStringValue(batchRow.getCell(0));
            orderInfo.put("batchNo", batchNoCell.substring(batchNoCell.indexOf("：") + 1).trim());

            // 操作人员号 - 格式: 操作人员号：WJL
            Row operatorRow = sheet.getRow(2);
            String operatorIdCell = getCellStringValue(operatorRow.getCell(0));
            orderInfo.put("operatorId", operatorIdCell.substring(operatorIdCell.indexOf("：") + 1).trim());

            // 加工时间 - 格式: 加工时间：2025/1/9 14:00:19
            Row timeRow = sheet.getRow(3);
            String processTimeCell = getCellStringValue(timeRow.getCell(0));
            String processTime = processTimeCell.substring(processTimeCell.indexOf("：") + 1).trim();
            if (processTime.contains("/")) {
                // 转换日期格式为标准格式
                String[] parts = processTime.split(" ");
                if (parts.length == 2) {
                    String datePart = parts[0].replace("/", "-");
                    processTime = datePart + " " + parts[1];
                }
            }
            orderInfo.put("processTime", processTime);

            // 数量信息 - 格式: 合格数量：2500，不合格数量：0，总数量：2500
            Row quantityRow = sheet.getRow(4);
            String quantityCell = getCellStringValue(quantityRow.getCell(0));
            String[] quantities = quantityCell.split("，");
            for (String quantity : quantities) {
                String[] parts = quantity.split("：");
                if (parts.length == 2) {
                    String value = parts[1].trim();
                    if (quantity.contains("合格数量") && !quantity.contains("不合格数量")) {
                        orderInfo.put("qualifiedCount", value);
                    } else if (quantity.contains("不合格数量")) {
                        orderInfo.put("unqualifiedCount", value);
                    } else if (quantity.contains("总数量")) {
                        orderInfo.put("totalCount", value);
                    }
                }
            }

            log.info("解析到的订单信息: {}", orderInfo);
        } catch (Exception e) {
            log.error("解析订单信息失败", e);
            throw new RuntimeException("解析订单信息失败: " + e.getMessage());
        }
        return orderInfo;
    }

    /**
     * 解析标签数据列表（从第8行开始）
     */
    private static List<Map<String, String>> parseTagList(Sheet sheet) {
        List<Map<String, String>> tagList = new ArrayList<>();
        try {
            // 获取表头
            Row headerRow = sheet.getRow(6);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(getCellStringValue(cell));
            }

            // 解析数据行
            int startRow = 7;
            Row row;
            while ((row = sheet.getRow(startRow)) != null) {
                Map<String, String> tagData = new HashMap<>();
                boolean hasData = false;

                for (int i = 0; i < headers.size(); i++) {
                    String value = getCellStringValue(row.getCell(i));
                    if (StringUtils.hasText(value)) {
                        hasData = true;
                        tagData.put(headers.get(i), value);
                    }
                }

                if (hasData) {
                    tagList.add(tagData);
                } else {
                    break;
                }
                startRow++;
            }
        } catch (Exception e) {
            log.error("解析标签数据失败", e);
            throw new RuntimeException("解析标签数据失败: " + e.getMessage());
        }
        return tagList;
    }

    /**
     * 获取单元格的字符串值
     */
    private static String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        String value = "";
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = sdf.format(cell.getDateCellValue());
                } else {
                    value = String.valueOf((long) cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                break;
        }
        return value.trim();
    }

    /**
     * 生成标签Excel文件
     *
     * @param records  标签记录列表
     * @param fileName 文件名
     * @return 文件路径
     */
    public static String generateTagExcel(List<HwTagRecord> records, String fileName) {
        try {
            // 使用ExcelUtil导出
            ExcelUtil<HwTagRecord> util = new ExcelUtil<>(HwTagRecord.class);
            // 导出Excel并获取结果
            AjaxResult result = util.exportExcel(records, "标签数据");
            if (result != null && HttpStatus.SUCCESS == Integer.parseInt(String.valueOf(result.get(AjaxResult.CODE_TAG)))) {
                String exportFileName = (String) result.get(AjaxResult.MSG_TAG);
                if (exportFileName == null || exportFileName.isEmpty()) {
                    throw new RuntimeException("导出文件名不能为空");
                }
                String fileUrl = ApiConstants.BASE_URL + UUID.randomUUID();
                String absoluteFile = ExcelUtil.getAbsoluteFile(exportFileName);
                // 存储文件信息
                saveFileInfo(fileUrl, fileName, absoluteFile);
                log.info("Excel文件生成成功: {}", absoluteFile);
                return fileUrl;
            } else {
                String errorMsg = result != null ? (String) result.get(AjaxResult.MSG_TAG) : "导出失败";
                throw new RuntimeException("导出Excel失败: " + errorMsg);
            }
        } catch (Exception e) {
            log.error("生成Excel文件失败", e);
            throw new RuntimeException("生成Excel文件失败: " + e.getMessage());
        }
    }

    /**
     * 保存文件信息到数据库
     *
     * @param filePath      文件路径
     * @param fileName      文件名
     * @param attachAddress 附件地址
     */
    private static void saveFileInfo(String filePath, String fileName, String attachAddress) {
        try {
            SysAttachInfo sysAttachInfo = new SysAttachInfo();
            sysAttachInfo.setAttachUrl(filePath);
            sysAttachInfo.setAttachAddress(attachAddress);
            sysAttachInfo.setAttachName(fileName);
            sysAttachInfo.setAttachType("1");
            Date currentDate = new Date();
            sysAttachInfo.setExpirationTime(new Date(currentDate.getTime() + ApiConstants.PASS_TIME));
            SpringUtils.getBean(ISysAttachInfoService.class).insertSysAttachInfo(sysAttachInfo);
            log.info("文件信息保存成功: {}", filePath);
        } catch (Exception e) {
            log.error("保存文件信息失败", e);
            throw new RuntimeException("保存文件信息失败: " + e.getMessage());
        }
    }
} 