package com.doumee.core.annotation.excel;

import com.doumee.dao.business.model.GoodsImportAsync;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

/**
 * 商品异步导入：按表头名称解析列，避免列顺序变更导致字段错位。
 */
public class AsyncImportExcelSupport {

    private static final int DEFAULT_COL_PARAM = 6;
    private static final int DEFAULT_COL_SUB_CATEGORY = 7;
    private static final int DEFAULT_COL_MAIN_IMAGE = 8;
    private static final int DEFAULT_COL_MULTI_IMAGE = 9;

    private AsyncImportExcelSupport() {
    }

    public static ColumnMapping readAndApplyTextFields(String filePath, int sheetIndex, int headerRowIndex,
                                                       List<GoodsImportAsync> dataList) throws IOException {
        try (InputStream in = new FileInputStream(filePath)) {
            return readAndApplyTextFields(in, sheetIndex, headerRowIndex, dataList);
        }
    }

    public static ColumnMapping readAndApplyTextFields(InputStream in, int sheetIndex, int headerRowIndex,
                                                     List<GoodsImportAsync> dataList) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(in)) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Row headerRow = sheet.getRow(headerRowIndex);
            Map<String, Integer> headerCols = buildHeaderColumnMap(headerRow);
            ColumnMapping mapping = new ColumnMapping();
            mapping.paramCol = resolveCol(headerCols, DEFAULT_COL_PARAM, "产品参数");
            mapping.subCategoryCol = resolveCol(headerCols, DEFAULT_COL_SUB_CATEGORY, "商品二级类别");
            mapping.mainImageCol = resolveCol(headerCols, DEFAULT_COL_MAIN_IMAGE, "商品主图");
            mapping.multiImageCol = resolveCol(headerCols, DEFAULT_COL_MULTI_IMAGE, "商品多图");
            applyTextFields(sheet, headerRowIndex, headerCols, mapping, dataList);
            return mapping;
        }
    }

    public static ColumnMapping readColumnMapping(String filePath, int sheetIndex, int headerRowIndex) throws IOException {
        try (InputStream in = new FileInputStream(filePath)) {
            return readColumnMapping(in, sheetIndex, headerRowIndex);
        }
    }

    public static ColumnMapping readColumnMapping(InputStream in, int sheetIndex, int headerRowIndex) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(in)) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Row headerRow = sheet.getRow(headerRowIndex);
            Map<String, Integer> headerCols = buildHeaderColumnMap(headerRow);
            ColumnMapping mapping = new ColumnMapping();
            mapping.paramCol = resolveCol(headerCols, DEFAULT_COL_PARAM, "产品参数");
            mapping.subCategoryCol = resolveCol(headerCols, DEFAULT_COL_SUB_CATEGORY, "商品二级类别");
            mapping.mainImageCol = resolveCol(headerCols, DEFAULT_COL_MAIN_IMAGE, "商品主图");
            mapping.multiImageCol = resolveCol(headerCols, DEFAULT_COL_MULTI_IMAGE, "商品多图");
            return mapping;
        }
    }

    private static void applyTextFields(Sheet sheet, int headerRowIndex, Map<String, Integer> headerCols,
                                        ColumnMapping mapping, List<GoodsImportAsync> dataList) {
        if (sheet == null || dataList == null || dataList.isEmpty()) {
            return;
        }
        DataFormatter formatter = new DataFormatter();
        int dataStartRow = headerRowIndex + 1;
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.getRow(dataStartRow + i);
            if (row == null) {
                continue;
            }
            GoodsImportAsync item = dataList.get(i);
            setIfPresent(headerCols, row, formatter, "商品文件夹名称", item::setId);
            setIfPresent(headerCols, row, formatter, "商品名称", item::setName);
            setIfPresent(headerCols, row, formatter, "商品类别", item::setCategory);
            setIfPresent(headerCols, row, formatter, "商品品牌", item::setBrand);
            setIfPresent(headerCols, row, formatter, "指导价", item::setZdPrice);
            setIfPresent(headerCols, row, formatter, "入手价", item::setPrice);
            if (mapping.paramCol != null) {
                item.setParamStr(readCellString(row, mapping.paramCol, formatter));
            }
            if (mapping.subCategoryCol != null) {
                item.setSubCategory(readCellString(row, mapping.subCategoryCol, formatter));
            }
        }
    }

    private static void setIfPresent(Map<String, Integer> headerCols, Row row, DataFormatter formatter,
                                     String header, Consumer<String> setter) {
        Integer col = headerCols.get(header);
        if (col != null) {
            setter.accept(readCellString(row, col, formatter));
        }
    }

    private static Map<String, Integer> buildHeaderColumnMap(Row headerRow) {
        Map<String, Integer> map = new HashMap<>();
        if (headerRow == null) {
            return map;
        }
        DataFormatter formatter = new DataFormatter();
        short lastCell = headerRow.getLastCellNum();
        for (int c = 0; c < lastCell; c++) {
            Cell cell = headerRow.getCell(c);
            if (cell == null) {
                continue;
            }
            String header = normalizeHeader(formatter.formatCellValue(cell));
            if (StringUtils.isNotBlank(header)) {
                map.put(header, c);
            }
        }
        return map;
    }

    private static Integer resolveCol(Map<String, Integer> headerCols, int defaultCol, String headerName) {
        Integer col = headerCols.get(headerName);
        return col != null ? col : defaultCol;
    }

    private static String normalizeHeader(String header) {
        if (header == null) {
            return "";
        }
        return header.trim().replace('\u00A0', ' ');
    }

    static String readCellString(Row row, int col, DataFormatter formatter) {
        if (row == null || col < 0) {
            return "";
        }
        Cell cell = row.getCell(col);
        if (cell == null) {
            return "";
        }
        String value = formatter.formatCellValue(cell);
        if (value == null) {
            return "";
        }
        value = value.trim();
        if (StringUtils.endsWith(value, ".0") && value.matches("^\\d+\\.0$")) {
            value = StringUtils.substringBefore(value, ".0");
        }
        return value;
    }

    public static class ColumnMapping {
        private Integer paramCol;
        private Integer subCategoryCol;
        private Integer mainImageCol;
        private Integer multiImageCol;

        public int getMainImageCol() {
            return mainImageCol != null ? mainImageCol : DEFAULT_COL_MAIN_IMAGE;
        }

        public int getMultiImageCol() {
            return multiImageCol != null ? multiImageCol : DEFAULT_COL_MULTI_IMAGE;
        }
    }
}