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.BizYakHerdInventory;

/**
 * 牦牛存栏 Excel 解析（对齐 yak_herd_inventory_import_template.xlsx）
 */
public final class YakHerdInventoryExcelParser
{
    static final int COL_TOWN = 1;

    static final int COL_VILLAGE_NAME = 2;

    static final int COL_VILLAGE_COUNT = 3;

    static final int COL_HERDSMAN = 4;

    static final int COL_GRAND_TOTAL = 5;

    /** 牦牛公母分类 — 合计 */
    static final int COL_YAK_GENDER_TOTAL = 6;

    static final int COL_YAK_GENDER_BULL = 7;

    static final int COL_YAK_GENDER_COW = 8;

    static final int COL_INSURED = 9;

    static final int COL_BULL_SECTION_TOTAL = 10;

    static final int COL_BREEDING_BULL = 11;

    static final int COL_BULL_CASTRATED = 12;

    static final int COL_COW_SECTION_TOTAL = 13;

    static final int COL_COW_YOUNG = 14;

    static final int COL_COW_ADULT = 15;

    static final int COL_DAIRY_SECTION_TOTAL = 16;

    static final int COL_MILKING_COW = 17;

    static final int COL_FERTILE_COW = 18;

    static final int COL_SHEEP_TOTAL = 19;

    static final int COL_MILKING_SHEEP = 20;

    static final int COL_YAK_SHEEP_TOTAL = 21;

    static final int COL_LAST = COL_YAK_SHEEP_TOTAL;

    private static final DataFormatter FORMATTER = new DataFormatter();

    private YakHerdInventoryExcelParser()
    {
    }

    public static List<ParsedRow> parse(InputStream inputStream)
    {
        try (Workbook workbook = WorkbookFactory.create(inputStream))
        {
            Sheet sheet = workbook.getSheetAt(0);
            int headerRowIndex = detectHeaderRow(sheet);
            if (headerRowIndex < 0)
            {
                throw new ServiceException("未识别到表头行（乡镇）");
            }
            int dataStartRow = headerRowIndex + 3;
            List<ParsedRow> rows = new ArrayList<>();
            for (int i = dataStartRow; i <= sheet.getLastRowNum(); i++)
            {
                Row row = sheet.getRow(i);
                if (row == null)
                {
                    continue;
                }
                String townName = cellString(row, COL_TOWN);
                if (StringUtils.isEmpty(townName))
                {
                    break;
                }
                if (townName.contains("合计"))
                {
                    break;
                }
                ParsedRow parsed = new ParsedRow();
                parsed.setExcelRowNum(i + 1);
                parsed.setTownName(townName.trim());
                parsed.setVillageName(cellString(row, COL_VILLAGE_NAME));
                parsed.setVillageCount(cellInteger(row, COL_VILLAGE_COUNT));
                parsed.setHerdsmanHouseholdCount(cellInteger(row, COL_HERDSMAN));
                parsed.setInsuredYakCount(cellInteger(row, COL_INSURED));
                parsed.setBreedingBullCount(cellInteger(row, COL_BREEDING_BULL));
                parsed.setBullCastratedCount(cellInteger(row, COL_BULL_CASTRATED));
                parsed.setCowYoungCount(cellInteger(row, COL_COW_YOUNG));
                parsed.setCowAdultCount(cellInteger(row, COL_COW_ADULT));
                parsed.setMilkingCowCount(cellInteger(row, COL_MILKING_COW));
                parsed.setFertileCowCount(cellInteger(row, COL_FERTILE_COW));
                parsed.setSheepTotal(cellInteger(row, COL_SHEEP_TOTAL));
                parsed.setMilkingSheepCount(cellInteger(row, COL_MILKING_SHEEP));
                parsed.setExcelGrandTotal(cellInteger(row, COL_GRAND_TOTAL));
                parsed.setExcelYakTotal(cellInteger(row, COL_YAK_GENDER_TOTAL));
                parsed.setExcelBullTotal(cellInteger(row, COL_BULL_SECTION_TOTAL));
                parsed.setExcelCowTotal(cellInteger(row, COL_COW_SECTION_TOTAL));
                parsed.setExcelDairyTotal(cellInteger(row, COL_DAIRY_SECTION_TOTAL));
                parsed.setExcelYakSheepTotal(cellInteger(row, COL_YAK_SHEEP_TOTAL));
                rows.add(parsed);
            }
            return rows;
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("Excel 解析失败：" + ex.getMessage());
        }
    }

    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 seqHeader = cellString(row, 0);
            if (StringUtils.isNotEmpty(townHeader) && townHeader.contains("乡镇")
                    && StringUtils.isNotEmpty(seqHeader) && seqHeader.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) + "行数字格式错误");
        }
    }

    public static BizYakHerdInventory toInventory(ParsedRow parsed, Integer statYear, Integer statMonth, Long townDeptId,
            String townName, Long villageDeptId, String villageName)
    {
        BizYakHerdInventory row = new BizYakHerdInventory();
        row.setStatYear(statYear);
        row.setStatMonth(statMonth);
        row.setTownDeptId(townDeptId);
        row.setTownName(townName);
        row.setVillageDeptId(villageDeptId);
        row.setVillageName(villageName);
        row.setVillageCount(parsed.getVillageCount());
        row.setHerdsmanHouseholdCount(parsed.getHerdsmanHouseholdCount());
        row.setBreedingBullCount(parsed.getBreedingBullCount());
        row.setBullCastratedCount(parsed.getBullCastratedCount());
        row.setCowYoungCount(parsed.getCowYoungCount());
        row.setCowAdultCount(parsed.getCowAdultCount());
        row.setMilkingCowCount(parsed.getMilkingCowCount());
        row.setFertileCowCount(parsed.getFertileCowCount());
        row.setSheepTotal(parsed.getSheepTotal());
        row.setMilkingSheepCount(parsed.getMilkingSheepCount());
        row.setInsuredYakCount(parsed.getInsuredYakCount());
        YakHerdInventoryValidation.recalculateDerived(row);
        return row;
    }

    public static class ParsedRow
    {
        private int excelRowNum;

        private String townName;

        private String villageName;

        private Integer villageCount;

        private Integer herdsmanHouseholdCount;

        private Integer breedingBullCount;

        private Integer bullCastratedCount;

        private Integer cowYoungCount;

        private Integer cowAdultCount;

        private Integer milkingCowCount;

        private Integer fertileCowCount;

        private Integer sheepTotal;

        private Integer milkingSheepCount;

        private Integer insuredYakCount;

        private Integer excelGrandTotal;

        private Integer excelYakTotal;

        private Integer excelBullTotal;

        private Integer excelCowTotal;

        private Integer excelDairyTotal;

        private Integer excelYakSheepTotal;

        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 getVillageCount()
        {
            return villageCount;
        }

        public void setVillageCount(Integer villageCount)
        {
            this.villageCount = villageCount;
        }

        public Integer getHerdsmanHouseholdCount()
        {
            return herdsmanHouseholdCount;
        }

        public void setHerdsmanHouseholdCount(Integer herdsmanHouseholdCount)
        {
            this.herdsmanHouseholdCount = herdsmanHouseholdCount;
        }

        public Integer getBreedingBullCount()
        {
            return breedingBullCount;
        }

        public void setBreedingBullCount(Integer breedingBullCount)
        {
            this.breedingBullCount = breedingBullCount;
        }

        public Integer getBullCastratedCount()
        {
            return bullCastratedCount;
        }

        public void setBullCastratedCount(Integer bullCastratedCount)
        {
            this.bullCastratedCount = bullCastratedCount;
        }

        public Integer getCowYoungCount()
        {
            return cowYoungCount;
        }

        public void setCowYoungCount(Integer cowYoungCount)
        {
            this.cowYoungCount = cowYoungCount;
        }

        public Integer getCowAdultCount()
        {
            return cowAdultCount;
        }

        public void setCowAdultCount(Integer cowAdultCount)
        {
            this.cowAdultCount = cowAdultCount;
        }

        public Integer getMilkingCowCount()
        {
            return milkingCowCount;
        }

        public void setMilkingCowCount(Integer milkingCowCount)
        {
            this.milkingCowCount = milkingCowCount;
        }

        public Integer getFertileCowCount()
        {
            return fertileCowCount;
        }

        public void setFertileCowCount(Integer fertileCowCount)
        {
            this.fertileCowCount = fertileCowCount;
        }

        public Integer getSheepTotal()
        {
            return sheepTotal;
        }

        public void setSheepTotal(Integer sheepTotal)
        {
            this.sheepTotal = sheepTotal;
        }

        public Integer getMilkingSheepCount()
        {
            return milkingSheepCount;
        }

        public void setMilkingSheepCount(Integer milkingSheepCount)
        {
            this.milkingSheepCount = milkingSheepCount;
        }

        public Integer getInsuredYakCount()
        {
            return insuredYakCount;
        }

        public void setInsuredYakCount(Integer insuredYakCount)
        {
            this.insuredYakCount = insuredYakCount;
        }

        public Integer getExcelGrandTotal()
        {
            return excelGrandTotal;
        }

        public void setExcelGrandTotal(Integer excelGrandTotal)
        {
            this.excelGrandTotal = excelGrandTotal;
        }

        public Integer getExcelYakTotal()
        {
            return excelYakTotal;
        }

        public void setExcelYakTotal(Integer excelYakTotal)
        {
            this.excelYakTotal = excelYakTotal;
        }

        public Integer getExcelBullTotal()
        {
            return excelBullTotal;
        }

        public void setExcelBullTotal(Integer excelBullTotal)
        {
            this.excelBullTotal = excelBullTotal;
        }

        public Integer getExcelCowTotal()
        {
            return excelCowTotal;
        }

        public void setExcelCowTotal(Integer excelCowTotal)
        {
            this.excelCowTotal = excelCowTotal;
        }

        public Integer getExcelDairyTotal()
        {
            return excelDairyTotal;
        }

        public void setExcelDairyTotal(Integer excelDairyTotal)
        {
            this.excelDairyTotal = excelDairyTotal;
        }

        public Integer getExcelYakSheepTotal()
        {
            return excelYakSheepTotal;
        }

        public void setExcelYakSheepTotal(Integer excelYakSheepTotal)
        {
            this.excelYakSheepTotal = excelYakSheepTotal;
        }

        /** 解析后按公式计算的牦牛总存栏数，供测试断言 */
        public Integer getYakTotal()
        {
            BizYakHerdInventory tmp = new BizYakHerdInventory();
            tmp.setBreedingBullCount(breedingBullCount);
            tmp.setBullCastratedCount(bullCastratedCount);
            tmp.setCowYoungCount(cowYoungCount);
            tmp.setCowAdultCount(cowAdultCount);
            tmp.setInsuredYakCount(insuredYakCount);
            YakHerdInventoryValidation.recalculateDerived(tmp);
            return tmp.getYakTotal();
        }

        public Integer getCowTotal()
        {
            BizYakHerdInventory tmp = new BizYakHerdInventory();
            tmp.setCowYoungCount(cowYoungCount);
            tmp.setCowAdultCount(cowAdultCount);
            YakHerdInventoryValidation.recalculateDerived(tmp);
            return tmp.getCowTotal();
        }
    }
}
