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

/**
 * 牦牛出栏 Excel 解析（对齐 yak_outbound_report_import_template.xlsx）
 */
public final class YakOutboundReportExcelParser
{
    public static final String TEMPLATE_CLASSPATH = "templates/yak_outbound_report_import_template.xlsx";

    static final int COL_TOWN = 1;

    static final int COL_VILLAGE_NAME = 2;

    static final int COL_FARMER_HOUSEHOLD = 3;

    static final int COL_FARMER_POPULATION = 4;

    static final int COL_YAK_OUTBOUND = 5;

    static final int COL_CATTLE_OUTBOUND = 6;

    static final int COL_SHEEP_OUTBOUND = 7;

    static final int COL_GOAT_OUTBOUND = 8;

    static final int COL_HORSE_MULE_OUTBOUND = 9;

    static final int COL_DONKEY_OUTBOUND = 10;

    /** 出栏合计（导入跳过，导出计算） */
    static final int COL_OUTBOUND_SUBTOTAL = 11;

    static final int COL_SELF_CATTLE = 12;

    static final int COL_SELF_SHEEP = 13;

    static final int COL_SELF_GOAT = 14;

    /** 自食合计（导入跳过，导出计算） */
    static final int COL_SELF_SUBTOTAL = 15;

    static final int COL_LAST = COL_SELF_SUBTOTAL;

    private static final DataFormatter FORMATTER = new DataFormatter();

    private YakOutboundReportExcelParser()
    {
    }

    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 townName = cellString(row, COL_TOWN);
                String seqText = cellString(row, 0);
                if (StringUtils.isNotEmpty(seqText) && seqText.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isNotEmpty(townName) && townName.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isEmpty(townName))
                {
                    continue;
                }
                ParsedRow parsed = new ParsedRow();
                parsed.setExcelRowNum(i + 1);
                parsed.setTownName(townName.trim());
                parsed.setVillageName(cellString(row, COL_VILLAGE_NAME));
                parsed.setFarmerHouseholdCount(cellInteger(row, COL_FARMER_HOUSEHOLD));
                parsed.setFarmerPopulationCount(cellInteger(row, COL_FARMER_POPULATION));
                parsed.setYakOutboundCount(cellInteger(row, COL_YAK_OUTBOUND));
                parsed.setCattleOutboundCount(cellInteger(row, COL_CATTLE_OUTBOUND));
                parsed.setSheepOutboundCount(cellInteger(row, COL_SHEEP_OUTBOUND));
                parsed.setGoatOutboundCount(cellInteger(row, COL_GOAT_OUTBOUND));
                parsed.setHorseMuleOutboundCount(cellInteger(row, COL_HORSE_MULE_OUTBOUND));
                parsed.setDonkeyOutboundCount(cellInteger(row, COL_DONKEY_OUTBOUND));
                parsed.setSelfConsumptionCattleCount(cellInteger(row, COL_SELF_CATTLE));
                parsed.setSelfConsumptionSheepCount(cellInteger(row, COL_SELF_SHEEP));
                parsed.setSelfConsumptionGoatCount(cellInteger(row, COL_SELF_GOAT));
                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_NAME);
            String householdHeader = cellString(row, COL_FARMER_HOUSEHOLD);
            String yakHeader = cellString(row, COL_YAK_OUTBOUND);
            if (StringUtils.isNotEmpty(townHeader) && townHeader.contains("乡镇")
                    && StringUtils.isNotEmpty(villageHeader) && villageHeader.contains("村")
                    && StringUtils.isNotEmpty(householdHeader) && householdHeader.contains("户数")
                    && StringUtils.isNotEmpty(yakHeader) && yakHeader.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 BizYakOutboundReport toReport(ParsedRow parsed, Integer statYear, Integer statMonth, Long townDeptId,
            String townName, Long villageDeptId, String villageName)
    {
        BizYakOutboundReport row = new BizYakOutboundReport();
        row.setStatYear(statYear);
        row.setStatMonth(statMonth);
        row.setTownDeptId(townDeptId);
        row.setTownName(townName);
        row.setVillageDeptId(villageDeptId);
        row.setVillageName(villageName);
        row.setFarmerHouseholdCount(parsed.getFarmerHouseholdCount());
        row.setFarmerPopulationCount(parsed.getFarmerPopulationCount());
        row.setYakOutboundCount(parsed.getYakOutboundCount());
        row.setCattleOutboundCount(parsed.getCattleOutboundCount());
        row.setSheepOutboundCount(parsed.getSheepOutboundCount());
        row.setGoatOutboundCount(parsed.getGoatOutboundCount());
        row.setHorseMuleOutboundCount(parsed.getHorseMuleOutboundCount());
        row.setDonkeyOutboundCount(parsed.getDonkeyOutboundCount());
        row.setSelfConsumptionCattleCount(parsed.getSelfConsumptionCattleCount());
        row.setSelfConsumptionSheepCount(parsed.getSelfConsumptionSheepCount());
        row.setSelfConsumptionGoatCount(parsed.getSelfConsumptionGoatCount());
        return row;
    }

    public static class ParsedRow
    {
        private int excelRowNum;

        private String townName;

        private String villageName;

        private Integer farmerHouseholdCount;

        private Integer farmerPopulationCount;

        private Integer yakOutboundCount;

        private Integer cattleOutboundCount;

        private Integer sheepOutboundCount;

        private Integer goatOutboundCount;

        private Integer horseMuleOutboundCount;

        private Integer donkeyOutboundCount;

        private Integer selfConsumptionCattleCount;

        private Integer selfConsumptionSheepCount;

        private Integer selfConsumptionGoatCount;

        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 getFarmerHouseholdCount()
        {
            return farmerHouseholdCount;
        }

        public void setFarmerHouseholdCount(Integer farmerHouseholdCount)
        {
            this.farmerHouseholdCount = farmerHouseholdCount;
        }

        public Integer getFarmerPopulationCount()
        {
            return farmerPopulationCount;
        }

        public void setFarmerPopulationCount(Integer farmerPopulationCount)
        {
            this.farmerPopulationCount = farmerPopulationCount;
        }

        public Integer getYakOutboundCount()
        {
            return yakOutboundCount;
        }

        public void setYakOutboundCount(Integer yakOutboundCount)
        {
            this.yakOutboundCount = yakOutboundCount;
        }

        public Integer getCattleOutboundCount()
        {
            return cattleOutboundCount;
        }

        public void setCattleOutboundCount(Integer cattleOutboundCount)
        {
            this.cattleOutboundCount = cattleOutboundCount;
        }

        public Integer getSheepOutboundCount()
        {
            return sheepOutboundCount;
        }

        public void setSheepOutboundCount(Integer sheepOutboundCount)
        {
            this.sheepOutboundCount = sheepOutboundCount;
        }

        public Integer getGoatOutboundCount()
        {
            return goatOutboundCount;
        }

        public void setGoatOutboundCount(Integer goatOutboundCount)
        {
            this.goatOutboundCount = goatOutboundCount;
        }

        public Integer getHorseMuleOutboundCount()
        {
            return horseMuleOutboundCount;
        }

        public void setHorseMuleOutboundCount(Integer horseMuleOutboundCount)
        {
            this.horseMuleOutboundCount = horseMuleOutboundCount;
        }

        public Integer getDonkeyOutboundCount()
        {
            return donkeyOutboundCount;
        }

        public void setDonkeyOutboundCount(Integer donkeyOutboundCount)
        {
            this.donkeyOutboundCount = donkeyOutboundCount;
        }

        public Integer getSelfConsumptionCattleCount()
        {
            return selfConsumptionCattleCount;
        }

        public void setSelfConsumptionCattleCount(Integer selfConsumptionCattleCount)
        {
            this.selfConsumptionCattleCount = selfConsumptionCattleCount;
        }

        public Integer getSelfConsumptionSheepCount()
        {
            return selfConsumptionSheepCount;
        }

        public void setSelfConsumptionSheepCount(Integer selfConsumptionSheepCount)
        {
            this.selfConsumptionSheepCount = selfConsumptionSheepCount;
        }

        public Integer getSelfConsumptionGoatCount()
        {
            return selfConsumptionGoatCount;
        }

        public void setSelfConsumptionGoatCount(Integer selfConsumptionGoatCount)
        {
            this.selfConsumptionGoatCount = selfConsumptionGoatCount;
        }
    }
}
