package com.aucma.base.utils;

import com.aucma.common.exception.ServiceException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 物料BOM滚算模板解析工具
 */
public final class MaterialImportExcelHelper
{
    private static final String HEADER_INDEX = "序号";
    private static final String HEADER_CODE = "编码";
    private static final String HEADER_NAME = "对象描述";
    private static final List<String> TEMPLATE_SHEET_NAMES = Arrays.asList("原件", "印刷件", "喷漆件");

    private MaterialImportExcelHelper()
    {
    }

    public static ParseResult parse(InputStream inputStream) throws IOException
    {
        try (Workbook workbook = WorkbookFactory.create(inputStream))
        {
            DataFormatter formatter = new DataFormatter();
            Map<String, MaterialImportRow> rowMap = new LinkedHashMap<>();
            List<DuplicateMaterialRow> duplicateRows = new ArrayList<>();
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++)
            {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                if (sheet == null || sheet.getPhysicalNumberOfRows() == 0)
                {
                    continue;
                }
                validateHeader(sheet, formatter);
                for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++)
                {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null)
                    {
                        continue;
                    }
                    String materialCode = normalizeCell(formatter, row.getCell(1));
                    String materialName = normalizeCell(formatter, row.getCell(2));
                    if (isBlank(materialCode) && isBlank(materialName))
                    {
                        continue;
                    }
                    if (isBlank(materialCode) || isBlank(materialName))
                    {
                        throw new ServiceException("模板第" + (sheetIndex + 1) + "个sheet[" + sheet.getSheetName() + "]第" + (rowIndex + 1) + "行存在空编码或空对象描述");
                    }
                    MaterialImportRow existed = rowMap.get(materialCode);
                    if (existed != null)
                    {
                        // 为什么直接跳过重复编码：BOM滚算模板按业务来源会跨sheet重复出现同一物料，导入目标表又以物料编码为唯一识别口径，保留首次出现即可避免整批失败。
                        duplicateRows.add(new DuplicateMaterialRow(existed, new MaterialImportRow(sheet.getSheetName(), rowIndex + 1, materialCode, materialName)));
                        continue;
                    }
                    rowMap.put(materialCode, new MaterialImportRow(sheet.getSheetName(), rowIndex + 1, materialCode, materialName));
                }
            }
            if (rowMap.isEmpty())
            {
                throw new ServiceException("导入数据不能为空，请检查Excel是否包含有效物料编码");
            }
            return new ParseResult(new ArrayList<>(rowMap.values()), duplicateRows);
        }
    }

    public static void writeTemplate(OutputStream outputStream) throws IOException
    {
        try (Workbook workbook = new XSSFWorkbook())
        {
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            for (String sheetName : TEMPLATE_SHEET_NAMES)
            {
                Sheet sheet = workbook.createSheet(sheetName);
                Row headerRow = sheet.createRow(0);
                createHeaderCell(headerRow, 0, HEADER_INDEX, headerStyle);
                createHeaderCell(headerRow, 1, HEADER_CODE, headerStyle);
                createHeaderCell(headerRow, 2, HEADER_NAME, headerStyle);
                sheet.setColumnWidth(0, 12 * 256);
                sheet.setColumnWidth(1, 18 * 256);
                sheet.setColumnWidth(2, 60 * 256);
            }
            workbook.write(outputStream);
            outputStream.flush();
        }
    }

    private static void validateHeader(Sheet sheet, DataFormatter formatter)
    {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null)
        {
            throw new ServiceException("sheet[" + sheet.getSheetName() + "]缺少表头");
        }
        String headerIndex = normalizeCell(formatter, headerRow.getCell(0));
        String headerCode = normalizeCell(formatter, headerRow.getCell(1));
        String headerName = normalizeCell(formatter, headerRow.getCell(2));
        if (!HEADER_INDEX.equals(headerIndex) || !HEADER_CODE.equals(headerCode) || !HEADER_NAME.equals(headerName))
        {
            throw new ServiceException("sheet[" + sheet.getSheetName() + "]表头不符合要求，必须为：序号/编码/对象描述");
        }
    }

    private static void createHeaderCell(Row headerRow, int columnIndex, String value, CellStyle headerStyle)
    {
        Cell cell = headerRow.createCell(columnIndex);
        cell.setCellValue(value);
        cell.setCellStyle(headerStyle);
    }

    private static String normalizeCell(DataFormatter formatter, Cell cell)
    {
        return normalizeText(cell == null ? null : formatter.formatCellValue(cell));
    }

    private static String normalizeText(String text)
    {
        if (text == null)
        {
            return null;
        }
        // 为什么要在这里抹平换行：模板里部分单元格只是为了展示自动换行，实际物料描述仍是一条业务数据，直接入库会污染检索和SQL脚本。
        return text.replace("\r", "").replace("\n", "").trim();
    }

    private static boolean isBlank(String text)
    {
        return text == null || text.trim().isEmpty();
    }

    public static class MaterialImportRow
    {
        private final String sheetName;
        private final int rowNum;
        private final String materialCode;
        private final String materialName;

        public MaterialImportRow(String sheetName, int rowNum, String materialCode, String materialName)
        {
            this.sheetName = sheetName;
            this.rowNum = rowNum;
            this.materialCode = materialCode;
            this.materialName = materialName;
        }

        public String getSheetName()
        {
            return sheetName;
        }

        public int getRowNum()
        {
            return rowNum;
        }

        public String getMaterialCode()
        {
            return materialCode;
        }

        public String getMaterialName()
        {
            return materialName;
        }
    }

    public static class DuplicateMaterialRow
    {
        private final MaterialImportRow firstRow;
        private final MaterialImportRow duplicateRow;

        public DuplicateMaterialRow(MaterialImportRow firstRow, MaterialImportRow duplicateRow)
        {
            this.firstRow = firstRow;
            this.duplicateRow = duplicateRow;
        }

        public MaterialImportRow getFirstRow()
        {
            return firstRow;
        }

        public MaterialImportRow getDuplicateRow()
        {
            return duplicateRow;
        }
    }

    public static class ParseResult
    {
        private final List<MaterialImportRow> materialRows;
        private final List<DuplicateMaterialRow> duplicateRows;

        public ParseResult(List<MaterialImportRow> materialRows, List<DuplicateMaterialRow> duplicateRows)
        {
            this.materialRows = materialRows;
            this.duplicateRows = duplicateRows;
        }

        public List<MaterialImportRow> getMaterialRows()
        {
            return materialRows;
        }

        public List<DuplicateMaterialRow> getDuplicateRows()
        {
            return duplicateRows;
        }
    }
}
