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

import java.io.InputStream;
import java.math.BigDecimal;
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.BizFarmerHousehold;

/**
 * 农牧户 Excel 解析（对齐 farmer_household_import_template.xlsx）
 * <p>多级表头：识别含「乡镇名称」「村」的行后，HEADER_SPAN=3，数据从 headerRow+3 起。
 * 导入跳过派生列：草场承包小计、补助奖励小计、合计。</p>
 */
public final class FarmerHouseholdExcelParser
{
    public static final String TEMPLATE_CLASSPATH = "templates/farmer_household_import_template.xlsx";

    /** 表头识别行之后跳过的子表头行数 */
    static final int HEADER_SPAN = 3;

    static final int COL_SEQ = 0;

    static final int COL_TOWN = 1;

    static final int COL_VILLAGE = 2;

    static final int COL_HOUSEHOLD = 3;

    static final int COL_POPULATION = 4;

    /** 草场承包小计：导入跳过，导出写入 */
    static final int COL_PASTURE_TOTAL = 5;

    static final int COL_GRAZING_BAN_AREA = 6;

    static final int COL_GRASS_BALANCE_AREA = 7;

    static final int COL_APPROVED_CAPACITY = 8;

    static final int COL_YEAR_END_STOCK = 9;

    static final int COL_IS_PURE_HERDER = 10;

    static final int COL_IS_NO_LIVESTOCK = 11;

    static final int COL_IS_EXTINCT = 12;

    static final int COL_IS_OVERLOAD = 13;

    static final int COL_NEED_CAP = 14;

    static final int COL_NEED_FLOOR = 15;

    static final int COL_GRAZING_BAN_SUBSIDY = 16;

    static final int COL_GRASS_BALANCE_REWARD = 17;

    /** 补助奖励小计：导入跳过，导出写入 */
    static final int COL_SUBSIDY_SUBTOTAL = 18;

    static final int COL_AFTER_CAP_FLOOR = 19;

    static final int COL_VILLAGE_SUPERVISOR = 20;

    /** 合计：导入跳过，导出写入 */
    static final int COL_SUBSIDY_TOTAL = 21;

    static final int COL_LAST = COL_SUBSIDY_TOTAL;

    private static final DataFormatter FORMATTER = new DataFormatter();

    private FarmerHouseholdExcelParser()
    {
    }

    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 + HEADER_SPAN;
            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 (StringUtils.isNotEmpty(villageName) && villageName.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isEmpty(townName) && StringUtils.isEmpty(villageName))
                {
                    continue;
                }
                ParsedRow parsed = new ParsedRow();
                parsed.setExcelRowNum(i + 1);
                parsed.setTownName(townName);
                parsed.setVillageName(villageName);
                parsed.setHouseholdCount(cellInteger(row, COL_HOUSEHOLD));
                parsed.setPopulationCount(cellInteger(row, COL_POPULATION));
                parsed.setGrazingBanArea(cellDecimal(row, COL_GRAZING_BAN_AREA));
                parsed.setGrassBalanceArea(cellDecimal(row, COL_GRASS_BALANCE_AREA));
                parsed.setApprovedCarryingCapacity(cellDecimal(row, COL_APPROVED_CAPACITY));
                parsed.setYearEndStockSheepUnit(cellDecimal(row, COL_YEAR_END_STOCK));
                parsed.setIsPureHerder(cellString(row, COL_IS_PURE_HERDER));
                parsed.setIsNoLivestock(cellString(row, COL_IS_NO_LIVESTOCK));
                parsed.setIsExtinctHousehold(cellString(row, COL_IS_EXTINCT));
                parsed.setIsOverloadHerder(cellString(row, COL_IS_OVERLOAD));
                parsed.setNeedCap(cellString(row, COL_NEED_CAP));
                parsed.setNeedFloor(cellString(row, COL_NEED_FLOOR));
                parsed.setGrazingBanSubsidy(cellDecimal(row, COL_GRAZING_BAN_SUBSIDY));
                parsed.setGrassBalanceReward(cellDecimal(row, COL_GRASS_BALANCE_REWARD));
                parsed.setAfterCapFloorSubsidy(cellDecimal(row, COL_AFTER_CAP_FLOOR));
                parsed.setVillageSupervisorSubsidy(cellDecimal(row, COL_VILLAGE_SUPERVISOR));
                rows.add(parsed);
            }
            return rows;
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("Excel 解析失败：" + ex.getMessage());
        }
    }

    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)
    {
        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;
        }
        try
        {
            return (int) Math.round(Double.parseDouble(text.replace(",", "")));
        }
        catch (NumberFormatException ex)
        {
            throw new ServiceException("第" + (row.getRowNum() + 1) + "行数字格式错误");
        }
    }

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

    public static BizFarmerHousehold toEntity(ParsedRow parsed, Integer statYear, Integer statMonth, Long townDeptId,
            String townName, Long villageDeptId, String villageName)
    {
        BizFarmerHousehold row = new BizFarmerHousehold();
        row.setStatYear(statYear);
        row.setStatMonth(statMonth);
        row.setTownDeptId(townDeptId);
        row.setTownName(townName);
        row.setVillageDeptId(villageDeptId);
        row.setVillageName(villageName);
        row.setHouseholdCount(parsed.getHouseholdCount());
        row.setPopulationCount(parsed.getPopulationCount());
        row.setGrazingBanArea(parsed.getGrazingBanArea());
        row.setGrassBalanceArea(parsed.getGrassBalanceArea());
        row.setApprovedCarryingCapacity(parsed.getApprovedCarryingCapacity());
        row.setYearEndStockSheepUnit(parsed.getYearEndStockSheepUnit());
        row.setIsPureHerder(parsed.getIsPureHerder());
        row.setIsNoLivestock(parsed.getIsNoLivestock());
        row.setIsExtinctHousehold(parsed.getIsExtinctHousehold());
        row.setIsOverloadHerder(parsed.getIsOverloadHerder());
        row.setNeedCap(parsed.getNeedCap());
        row.setNeedFloor(parsed.getNeedFloor());
        row.setGrazingBanSubsidy(parsed.getGrazingBanSubsidy());
        row.setGrassBalanceReward(parsed.getGrassBalanceReward());
        row.setAfterCapFloorSubsidy(parsed.getAfterCapFloorSubsidy());
        row.setVillageSupervisorSubsidy(parsed.getVillageSupervisorSubsidy());
        return row;
    }

    public static class ParsedRow
    {
        private int excelRowNum;

        private String townName;

        private String villageName;

        private Integer householdCount;

        private Integer populationCount;

        private BigDecimal grazingBanArea;

        private BigDecimal grassBalanceArea;

        private BigDecimal approvedCarryingCapacity;

        private BigDecimal yearEndStockSheepUnit;

        private String isPureHerder;

        private String isNoLivestock;

        private String isExtinctHousehold;

        private String isOverloadHerder;

        private String needCap;

        private String needFloor;

        private BigDecimal grazingBanSubsidy;

        private BigDecimal grassBalanceReward;

        private BigDecimal afterCapFloorSubsidy;

        private BigDecimal villageSupervisorSubsidy;

        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 getHouseholdCount()
        {
            return householdCount;
        }

        public void setHouseholdCount(Integer householdCount)
        {
            this.householdCount = householdCount;
        }

        public Integer getPopulationCount()
        {
            return populationCount;
        }

        public void setPopulationCount(Integer populationCount)
        {
            this.populationCount = populationCount;
        }

        public BigDecimal getGrazingBanArea()
        {
            return grazingBanArea;
        }

        public void setGrazingBanArea(BigDecimal grazingBanArea)
        {
            this.grazingBanArea = grazingBanArea;
        }

        public BigDecimal getGrassBalanceArea()
        {
            return grassBalanceArea;
        }

        public void setGrassBalanceArea(BigDecimal grassBalanceArea)
        {
            this.grassBalanceArea = grassBalanceArea;
        }

        public BigDecimal getApprovedCarryingCapacity()
        {
            return approvedCarryingCapacity;
        }

        public void setApprovedCarryingCapacity(BigDecimal approvedCarryingCapacity)
        {
            this.approvedCarryingCapacity = approvedCarryingCapacity;
        }

        public BigDecimal getYearEndStockSheepUnit()
        {
            return yearEndStockSheepUnit;
        }

        public void setYearEndStockSheepUnit(BigDecimal yearEndStockSheepUnit)
        {
            this.yearEndStockSheepUnit = yearEndStockSheepUnit;
        }

        public String getIsPureHerder()
        {
            return isPureHerder;
        }

        public void setIsPureHerder(String isPureHerder)
        {
            this.isPureHerder = isPureHerder;
        }

        public String getIsNoLivestock()
        {
            return isNoLivestock;
        }

        public void setIsNoLivestock(String isNoLivestock)
        {
            this.isNoLivestock = isNoLivestock;
        }

        public String getIsExtinctHousehold()
        {
            return isExtinctHousehold;
        }

        public void setIsExtinctHousehold(String isExtinctHousehold)
        {
            this.isExtinctHousehold = isExtinctHousehold;
        }

        public String getIsOverloadHerder()
        {
            return isOverloadHerder;
        }

        public void setIsOverloadHerder(String isOverloadHerder)
        {
            this.isOverloadHerder = isOverloadHerder;
        }

        public String getNeedCap()
        {
            return needCap;
        }

        public void setNeedCap(String needCap)
        {
            this.needCap = needCap;
        }

        public String getNeedFloor()
        {
            return needFloor;
        }

        public void setNeedFloor(String needFloor)
        {
            this.needFloor = needFloor;
        }

        public BigDecimal getGrazingBanSubsidy()
        {
            return grazingBanSubsidy;
        }

        public void setGrazingBanSubsidy(BigDecimal grazingBanSubsidy)
        {
            this.grazingBanSubsidy = grazingBanSubsidy;
        }

        public BigDecimal getGrassBalanceReward()
        {
            return grassBalanceReward;
        }

        public void setGrassBalanceReward(BigDecimal grassBalanceReward)
        {
            this.grassBalanceReward = grassBalanceReward;
        }

        public BigDecimal getAfterCapFloorSubsidy()
        {
            return afterCapFloorSubsidy;
        }

        public void setAfterCapFloorSubsidy(BigDecimal afterCapFloorSubsidy)
        {
            this.afterCapFloorSubsidy = afterCapFloorSubsidy;
        }

        public BigDecimal getVillageSupervisorSubsidy()
        {
            return villageSupervisorSubsidy;
        }

        public void setVillageSupervisorSubsidy(BigDecimal villageSupervisorSubsidy)
        {
            this.villageSupervisorSubsidy = villageSupervisorSubsidy;
        }
    }
}
