package top.r3944realms.docchecktoolrefactored.io.reader;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.r3944realms.docchecktoolrefactored.core.LogicalAddressFileGenerator;
import top.r3944realms.docchecktoolrefactored.ui.utils.DialogUtil;
import top.r3944realms.docchecktoolrefactored.util.FileUtil;
import top.r3944realms.docchecktoolrefactored.util.LoggerMarker;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelFileReader implements CatalogFileReader {
    // 常量定义（避免硬编码）
    private static final String FIELD_ARCHIVE_CODE = "档号";
    private static final String FIELD_PAGE = "页数";
    
    @Override
    public List<LogicalAddressFileGenerator.Record> readCatalogFile(String filePath) throws Exception {
        File file = FileUtil.fileCheckAndGet(filePath, "xlsx", "xls");
        boolean isXlsx = file.getName().endsWith(".xlsx");
        return readExcelFile(file, isXlsx);
    }
    private List<LogicalAddressFileGenerator.Record> readExcelFile(File file, boolean isXlsx) throws Exception {
        List<LogicalAddressFileGenerator.Record> records = new ArrayList<>();
        log.debug(LoggerMarker.DEBUG_MARKER, "开始解析Excel文件，格式: {}", isXlsx ? "xlsx" : "xls");
        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = isXlsx ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis)) {
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            log.debug(LoggerMarker.DEBUG_MARKER, "读取工作表: {}", sheet.getSheetName());

            // 获取列标题行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                log.error(LoggerMarker.RELEASE_MARKER, "Excel文件缺少列标题行");
                throw new IllegalArgumentException("Excel文件缺少列标题行");
            }
            // 查找"档号"和"页数"列的索引
            int archiveCodeIndex = -1;
            int pageIndex = -1;
            log.debug(LoggerMarker.DEBUG_MARKER, "开始查找'档号'和'页数'列的索引");
            boolean foundExactMatch = false;
            for (Cell cell : headerRow) {
                String cellValue = getCellValueAsString(cell).trim();
                if (FIELD_ARCHIVE_CODE.equals(cellValue)) {
                    archiveCodeIndex = cell.getColumnIndex();
                    log.debug(LoggerMarker.DEBUG_MARKER, "找到'档号'列，索引: {}", archiveCodeIndex);
                } else if (FIELD_PAGE.equals(cellValue)) {
                    pageIndex = cell.getColumnIndex();
                    foundExactMatch = true;
                    log.debug(LoggerMarker.DEBUG_MARKER, "找到精确匹配'页数'列，索引: {}", pageIndex);
                }
            }
            // 如果没有精确匹配，进行模糊查找
            if (!foundExactMatch) {
                for (Cell cell : headerRow) {
                    String cellValue = getCellValueAsString(cell).trim();
                    if (cellValue.contains(FIELD_PAGE)) {
                        pageIndex = cell.getColumnIndex();
                        log.debug(LoggerMarker.DEBUG_MARKER, "找到模糊匹配'页数'列，索引: {}", pageIndex);
                    }
                }
            }
            // 检查是否找到必需的列
            if (archiveCodeIndex == -1 || pageIndex == -1) {
                log.error(LoggerMarker.RELEASE_MARKER, "未找到必要字段，档号: {}, 页数: {}",
                        archiveCodeIndex == -1 ? "未找到" : archiveCodeIndex,
                        pageIndex == -1 ? "未找到" : pageIndex
                );
                throw new IllegalArgumentException(
                        String.format("Excel文件缺少必要字段: %s=%s, %s=%s",
                                FIELD_ARCHIVE_CODE, archiveCodeIndex == -1,
                                FIELD_PAGE, pageIndex == -1
                        )
                );
            }
            // 从第二行开始读取数据（跳过列标题行，只取数据部分内容）
            int totalRows = sheet.getLastRowNum();
            int validRecords = 0;
            int skippedRecords = 0;

            for (int i = 1; i <= totalRows; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    skippedRecords++;
                    continue;
                }

                // 读取档号
                Cell archiveCodeCell = row.getCell(archiveCodeIndex);
                String archiveCode = "";
                if (archiveCodeCell != null) {
                    archiveCode = getCellValueAsString(archiveCodeCell).trim();
                }

                // 读取页数
                Cell pageCell = row.getCell(pageIndex);
                int page = 0;
                if (pageCell != null) {
                    page = getCellValueAsInteger(pageCell);
                }

                // 验证数据有效性
                if (archiveCode.isEmpty()) {
                    DialogUtil.showWarningDialog("警告", "操作有误", "第" + ( i + 1 ) + "行档号为空，停止处理");
                    throw new IllegalArgumentException("第" + ( i + 1 ) + "行档号为空，停止处理");

                }
                if (page <= 0) {
                    DialogUtil.showWarningDialog("警告", "操作有误", "第" + ( i + 1 ) + "第" + ( i + 1 ) + "行页数无效: " + page + "，停止处理");
                    throw new IllegalArgumentException("第" + ( i + 1 ) + "行页数无效: " + page + "，停止处理");
                }

                // 只有数据有效时才添加记录
                records.add(new LogicalAddressFileGenerator.Record(archiveCode, page));
                validRecords++;
                log.debug(LoggerMarker.DEBUG_MARKER, "读取有效记录 - 档号: {}, 页数: {}", archiveCode, page);
            }

            log.info(LoggerMarker.RELEASE_MARKER, "数据读取完成，有效记录: {} 条，跳过记录: {} 条", validRecords, skippedRecords);
        } catch (Exception e) {
            log.error(LoggerMarker.RELEASE_MARKER, "读取Excel文件时发生错误: {}", e.getMessage(), e);
            throw e;
        }
        return records;
    }
    /**
     * 获取单元格值作为字符串
     * @param cell 单元格
     * @return 字符串值
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // 判断是否为日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 数值转换为字符串，避免科学计数法
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == Math.floor(numericValue)) {
                        return String.valueOf((int) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    return cell.getCellFormula();
                }
            default:
                return "";
        }
    }

    /**
     * 获取单元格值作为整数
     * @param cell 单元格
     * @return 整数值
     */
    private int getCellValueAsInteger(Cell cell) {
        if (cell == null) {
            return 0;
        }

        try {
            switch (cell.getCellType()) {
                case NUMERIC:
                    return (int) Math.round(cell.getNumericCellValue());
                case STRING:
                    String stringValue = cell.getStringCellValue().trim();
                    if (!stringValue.isEmpty()) {
                        return Integer.parseInt(stringValue);
                    }
                    return 0;
                default:
                    return 0;
            }
        } catch (NumberFormatException e) {
            log.warn(LoggerMarker.DEBUG_MARKER, "无法将单元格值转换为整数: {}", cell);
            return 0;
        }
    }
}
