package com.ruoyi.web.modules.industryservice.support;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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 com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.web.modules.industryservice.domain.BizVaccinationData;

/**
 * 疫苗接种 Excel 解析，对齐 {@code templates/vaccination_data_import_template.xlsx}。
 * <p>
 * 表头约 1～4 行：第 3 行主表头（乡镇/村），第 4 行子表头（应免数/实免数/免疫率）；
 * 数据从主表头下一行起扫描，自动跳过子表头与空白行。免疫率列导入时跳过，由服务端计算。
 * </p>
 */
public final class VaccinationDataExcelParser
{
    public static final String TEMPLATE_CLASSPATH = "templates/vaccination_data_import_template.xlsx";

    /** 官方模板主表头 + 子表头共 2 行；导出写数据起始仍按 header+HEADER_SPAN */
    static final int HEADER_SPAN = 2;

    static final int COL_SEQ = 0;

    static final int COL_TOWN = 1;

    static final int COL_VILLAGE = 2;

    static final int COL_SHOULD = 3;

    static final int COL_ACTUAL = 4;

    /** 免疫率：导入跳过，导出写入 */
    static final int COL_RATE = 5;

    private static final DataFormatter FORMATTER = new DataFormatter();

    private VaccinationDataExcelParser()
    {
    }

    public static List<ParsedRow> parse(InputStream inputStream)
    {
        try (Workbook workbook = WorkbookFactory.create(inputStream))
        {
            Sheet sheet = resolveSheet(workbook);
            int headerRowIndex = detectHeaderRow(sheet);
            if (headerRowIndex < 0)
            {
                throw new ServiceException("未识别到表头行（乡镇/村）");
            }
            int dataStartRow = headerRowIndex + 1;
            List<ParsedRow> rows = new ArrayList<>();
            for (int i = dataStartRow; i <= sheet.getLastRowNum(); i++)
            {
                Row row = sheet.getRow(i);
                if (row == null)
                {
                    continue;
                }
                String seqText = cellString(row, COL_SEQ);
                String townName = cellString(row, COL_TOWN);
                String villageName = cellString(row, COL_VILLAGE);
                if (StringUtils.isNotEmpty(seqText) && seqText.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isNotEmpty(townName) && townName.contains("合计"))
                {
                    break;
                }
                if (isSkippableRow(row, townName, villageName, seqText))
                {
                    continue;
                }
                ParsedRow parsed = new ParsedRow();
                parsed.setExcelRowNum(i + 1);
                parsed.setTownName(townName);
                parsed.setVillageName(villageName);
                parsed.setShouldVaccinateCount(cellInteger(row, COL_SHOULD));
                parsed.setActualVaccinateCount(cellInteger(row, COL_ACTUAL));
                rows.add(parsed);
            }
            return rows;
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("Excel 解析失败：" + ex.getMessage());
        }
    }

    static boolean isSkippableRow(Row row, String townName, String villageName, String seqText)
    {
        if (StringUtils.isEmpty(townName) && StringUtils.isEmpty(villageName))
        {
            return true;
        }
        if (StringUtils.isNotEmpty(seqText) && seqText.contains("序号"))
        {
            return true;
        }
        if ("乡镇".equals(townName) || "乡镇名称".equals(townName))
        {
            return true;
        }
        if ("村".equals(villageName) || "村居名称".equals(villageName) || "村居".equals(villageName))
        {
            return true;
        }
        // 子表头：应免数（头）
        String shouldLabel = cellString(row, COL_SHOULD);
        return StringUtils.isNotEmpty(shouldLabel) && shouldLabel.contains("应免");
    }

    static Sheet resolveSheet(Workbook workbook)
    {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++)
        {
            Sheet sheet = workbook.getSheetAt(i);
            String name = sheet.getSheetName();
            if (name != null && name.contains("疫苗"))
            {
                return sheet;
            }
        }
        return workbook.getSheetAt(0);
    }

    static int detectHeaderRow(Sheet sheet)
    {
        int last = Math.min(sheet.getLastRowNum(), 30);
        for (int i = 0; i <= last; i++)
        {
            Row row = sheet.getRow(i);
            if (row == null)
            {
                continue;
            }
            String townHeader = cellString(row, COL_TOWN);
            String villageHeader = cellString(row, COL_VILLAGE);
            if (StringUtils.isNotEmpty(townHeader) && townHeader.contains("乡镇")
                    && StringUtils.isNotEmpty(villageHeader) && villageHeader.contains("村"))
            {
                return i;
            }
        }
        return -1;
    }

    static String cellString(Row row, int colIndex)
    {
        if (row == null)
        {
            return null;
        }
        Cell cell = row.getCell(colIndex);
        if (cell == null)
        {
            return null;
        }
        String text = FORMATTER.formatCellValue(cell);
        return StringUtils.isEmpty(text) ? null : text.trim();
    }

    static Integer cellInteger(Row row, int colIndex)
    {
        String text = cellString(row, colIndex);
        if (StringUtils.isEmpty(text))
        {
            return null;
        }
        text = text.replace(",", "").replace("%", "").trim();
        try
        {
            return (int) Math.round(Double.parseDouble(text));
        }
        catch (NumberFormatException ex)
        {
            throw new ServiceException("第" + (row.getRowNum() + 1) + "行数字格式错误");
        }
    }

    public static BizVaccinationData toEntity(ParsedRow parsed, Integer statYear, Integer statMonth, Long townDeptId,
            String townName, Long villageDeptId, String villageName)
    {
        BizVaccinationData row = new BizVaccinationData();
        row.setStatYear(statYear);
        row.setStatMonth(statMonth);
        row.setTownDeptId(townDeptId);
        row.setTownName(townName);
        row.setVillageDeptId(villageDeptId);
        row.setVillageName(villageName);
        row.setShouldVaccinateCount(parsed.getShouldVaccinateCount());
        row.setActualVaccinateCount(parsed.getActualVaccinateCount());
        return row;
    }

    public static class ParsedRow
    {
        private int excelRowNum;

        private String townName;

        private String villageName;

        private Integer shouldVaccinateCount;

        private Integer actualVaccinateCount;

        public int getExcelRowNum()
        {
            return excelRowNum;
        }

        public void setExcelRowNum(int excelRowNum)
        {
            this.excelRowNum = excelRowNum;
        }

        public String getTownName()
        {
            return townName;
        }

        public void setTownName(String townName)
        {
            this.townName = townName;
        }

        public String getVillageName()
        {
            return villageName;
        }

        public void setVillageName(String villageName)
        {
            this.villageName = villageName;
        }

        public Integer getShouldVaccinateCount()
        {
            return shouldVaccinateCount;
        }

        public void setShouldVaccinateCount(Integer shouldVaccinateCount)
        {
            this.shouldVaccinateCount = shouldVaccinateCount;
        }

        public Integer getActualVaccinateCount()
        {
            return actualVaccinateCount;
        }

        public void setActualVaccinateCount(Integer actualVaccinateCount)
        {
            this.actualVaccinateCount = actualVaccinateCount;
        }
    }
}
