package com.erp.dataimport.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.erp.dataimport.exception.DataImportException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;

/**
 * Excel工具类
 */
@Slf4j
public class ExcelUtils {

    private static final String XLSX = "xlsx";
    private static final String XLS = "xls";

    /**
     * 读取Excel文件（支持xlsx和xls）
     */
    public static List<Map<Integer, String>> readExcel(InputStream inputStream, String fileName, int headerRowIndex) {
        try {
            if (fileName.toLowerCase().endsWith(XLSX)) {
                return readXlsx(inputStream, headerRowIndex);
            } else if (fileName.toLowerCase().endsWith(XLS)) {
                return readXls(inputStream, headerRowIndex);
            } else {
                throw new DataImportException("不支持的文件格式，仅支持xlsx和xls");
            }
        } catch (Exception e) {
            log.error("读取Excel失败", e);
            throw new DataImportException("READ_ERROR", "读取Excel文件失败: " + e.getMessage());
        }
    }

    /**
     * 读取xlsx文件
     */
    private static List<Map<Integer, String>> readXlsx(InputStream inputStream, int headerRowIndex) throws Exception {
        List<Map<Integer, String>> result = new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        readSheet(sheet, headerRowIndex, result);
        workbook.close();
        return result;
    }

    /**
     * 读取xls文件
     */
    private static List<Map<Integer, String>> readXls(InputStream inputStream, int headerRowIndex) throws Exception {
        List<Map<Integer, String>> result = new ArrayList<>();
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        readSheet(sheet, headerRowIndex, result);
        workbook.close();
        return result;
    }

    /**
     * 读取Sheet页
     */
    private static void readSheet(Sheet sheet, int headerRowIndex, List<Map<Integer, String>> result) {
        if (sheet == null) return;
        int lastRowNum = sheet.getLastRowNum();
        for (int i = headerRowIndex; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            Map<Integer, String> rowData = new LinkedHashMap<>();
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                rowData.put(j, getCellValue(cell));
            }
            result.add(rowData);
        }
    }

    /**
     * 获取单元格值
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue().toString();
                }
                double value = cell.getNumericCellValue();
                if (value == Math.floor(value)) {
                    return String.valueOf((long) value);
                }
                return String.valueOf(value);
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return "";
        }
    }

    /**
     * 读取Excel并转换为List<Map>
     */
    public static <T> List<T> readExcelWithListener(InputStream inputStream, String fileName, Class<T> clazz, ReadListener<T> listener) {
        try {
            ExcelReader reader = EasyExcel.read(inputStream, clazz, listener).build();
            reader.readAll();
            reader.finish();
            // Note: listener.getList() not available in ReadListener interface
            // Return collected data via a custom listener implementation
            return java.util.Collections.emptyList();
        } catch (Exception e) {
            log.error("读取Excel失败", e);
            throw new DataImportException("READ_ERROR", "读取Excel文件失败: " + e.getMessage());
        }
    }

    /**
     * 生成Excel文件
     */
    public static void generateExcel(OutputStream outputStream, String sheetName, List<String> headers, List<List<Object>> dataList) {
        try {
            ExcelWriter writer = EasyExcel.write(outputStream).build();
            WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
            
            // 写入表头
            com.alibaba.excel.write.metadata.WriteTable table = EasyExcel.writerTable(0).head(createHeaderList(headers)).build();
            writer.write(dataList, sheet, table);
            writer.finish();
        } catch (Exception e) {
            log.error("生成Excel失败", e);
            throw new DataImportException("WRITE_ERROR", "生成Excel文件失败: " + e.getMessage());
        }
    }

    /**
     * 创建表头列表
     */
    private static List<List<String>> createHeaderList(List<String> headers) {
        List<List<String>> result = new ArrayList<>();
        for (String header : headers) {
            result.add(Collections.singletonList(header));
        }
        return result;
    }

    /**
     * 生成导入模板
     */
    public static void generateTemplate(OutputStream outputStream, String sheetName, List<String> headers, List<List<Object>> sampleData) {
        try {
            ExcelWriter writer = EasyExcel.write(outputStream).build();
            WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
            
            // 先写表头
            WriteSheet headerSheet = EasyExcel.writerSheet(sheetName + "_表头").head(createHeaderList(headers)).build();
            writer.write(Collections.singletonList(new ArrayList<>()), headerSheet);
            
            // 再写示例数据
            if (sampleData != null && !sampleData.isEmpty()) {
                WriteSheet dataSheet = EasyExcel.writerSheet(sheetName + "_数据").head(createHeaderList(headers)).build();
                writer.write(sampleData, dataSheet);
            }
            
            writer.finish();
        } catch (Exception e) {
            log.error("生成模板失败", e);
            throw new DataImportException("WRITE_ERROR", "生成导入模板失败: " + e.getMessage());
        }
    }

    /**
     * 验证文件格式
     */
    public static void validateExcelFormat(String fileName) {
        if (fileName == null || (!fileName.toLowerCase().endsWith(XLSX) && !fileName.toLowerCase().endsWith(XLS))) {
            throw new DataImportException("INVALID_FORMAT", "不支持的文件格式，仅支持xlsx和xls");
        }
    }

    /**
     * MultipartFile转InputStream
     */
    public static InputStream multipartFileToInputStream(MultipartFile file) throws IOException {
        return file.getInputStream();
    }

    /**
     * 获取Excel行数
     */
    public static int getRowCount(InputStream inputStream, String fileName) {
        try {
            Workbook workbook;
            if (fileName.toLowerCase().endsWith(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.toLowerCase().endsWith(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new DataImportException("INVALID_FORMAT", "不支持的文件格式");
            }
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            workbook.close();
            return lastRowNum;
        } catch (Exception e) {
            log.error("获取行数失败", e);
            return 0;
        }
    }
}
