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

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
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.BizFamilyRanch;

/**
 * 家庭牧场 Excel 解析（对齐 family_ranch_import_template.xlsx）
 * <p>多级表头：识别含「乡镇名称」「村居名称」的行后，HEADER_SPAN=3，数据从 headerRow+3 起。</p>
 */
public final class FamilyRanchExcelParser
{
    public static final String TEMPLATE_CLASSPATH = "templates/family_ranch_import_template.xlsx";

    /** 表头识别行之后跳过的子表头行数（样本：表头行 2，数据行 5） */
    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_ENTERPRISE_NAME = 3;

    static final int COL_LEGAL_REP = 4;

    static final int COL_ENTERPRISE_TYPE = 5;

    static final int COL_REGISTERED_CAPITAL = 6;

    static final int COL_ESTABLISH_DATE = 7;

    static final int COL_LIVESTOCK_LABOR = 8;

    /** 存栏小计：导入跳过，导出写入 */
    static final int COL_STOCK_SUBTOTAL = 9;

    static final int COL_CATTLE_YEAR_END = 10;

    static final int COL_CATTLE_BREEDING = 11;

    static final int COL_SHEEP_YEAR_END = 12;

    static final int COL_SHEEP_BREEDING = 13;

    static final int COL_CATTLE_OUTBOUND = 14;

    static final int COL_SHEEP_OUTBOUND = 15;

    /** 草场面积小计：导入跳过，导出写入 */
    static final int COL_GRASSLAND_SUBTOTAL = 16;

    static final int COL_GRASS_BALANCE_AREA = 17;

    static final int COL_TRANSFER_GRASS_AREA = 18;

    static final int COL_APPROVED_CAPACITY = 19;

    static final int COL_STOCK_SHEEP_UNIT = 20;

    /** 是否草畜平衡：导入跳过，导出写入 */
    static final int COL_IS_GRASS_BALANCED = 21;

    static final int COL_INCOME = 22;

    static final int COL_EXPENSE = 23;

    /** 利润：导入跳过，导出写入 */
    static final int COL_PROFIT = 24;

    static final int COL_SUBSIDY_YEAR = 25;

    static final int COL_HAS_SUBSIDY = 26;

    static final int COL_SUBSIDY_AMOUNT = 27;

    static final int COL_HAS_TRADEMARK = 28;

    static final int COL_HAS_QUALITY_CERT = 29;

    static final int COL_SALES_CHANNEL = 30;

    static final int COL_DRIVEN_POPULATION = 31;

    static final int COL_WAGE_PAID = 32;

    static final int COL_LAST = COL_WAGE_PAID;

    private static final DataFormatter FORMATTER = new DataFormatter();

    private FamilyRanchExcelParser()
    {
    }

    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);
                String enterpriseName = cellString(row, COL_ENTERPRISE_NAME);
                if (StringUtils.isNotEmpty(seqText) && seqText.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isNotEmpty(townName) && townName.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isNotEmpty(villageName) && villageName.contains("合计"))
                {
                    break;
                }
                if (StringUtils.isEmpty(enterpriseName) && StringUtils.isEmpty(villageName)
                        && StringUtils.isEmpty(townName))
                {
                    continue;
                }
                ParsedRow parsed = new ParsedRow();
                parsed.setExcelRowNum(i + 1);
                parsed.setTownName(townName);
                parsed.setVillageName(villageName);
                parsed.setEnterpriseName(enterpriseName);
                parsed.setLegalRepresentative(cellString(row, COL_LEGAL_REP));
                parsed.setEnterpriseType(cellString(row, COL_ENTERPRISE_TYPE));
                parsed.setRegisteredCapital(cellDecimal(row, COL_REGISTERED_CAPITAL));
                parsed.setEstablishDate(cellEstablishDate(row, COL_ESTABLISH_DATE));
                parsed.setLivestockLaborCount(cellInteger(row, COL_LIVESTOCK_LABOR));
                parsed.setCattleYearEndStock(cellInteger(row, COL_CATTLE_YEAR_END));
                parsed.setCattleBreedingFemaleStock(cellInteger(row, COL_CATTLE_BREEDING));
                parsed.setSheepYearEndStock(cellInteger(row, COL_SHEEP_YEAR_END));
                parsed.setSheepBreedingFemaleStock(cellInteger(row, COL_SHEEP_BREEDING));
                parsed.setCattleOutbound(cellInteger(row, COL_CATTLE_OUTBOUND));
                parsed.setSheepOutbound(cellInteger(row, COL_SHEEP_OUTBOUND));
                parsed.setGrassBalanceArea(cellDecimal(row, COL_GRASS_BALANCE_AREA));
                parsed.setTransferGrassArea(cellDecimal(row, COL_TRANSFER_GRASS_AREA));
                parsed.setApprovedCarryingCapacity(cellDecimal(row, COL_APPROVED_CAPACITY));
                parsed.setYearEndStockSheepUnit(cellDecimal(row, COL_STOCK_SHEEP_UNIT));
                parsed.setIncome(cellDecimal(row, COL_INCOME));
                parsed.setExpense(cellDecimal(row, COL_EXPENSE));
                parsed.setSubsidyYear(cellInteger(row, COL_SUBSIDY_YEAR));
                parsed.setHasSubsidy(cellString(row, COL_HAS_SUBSIDY));
                parsed.setSubsidyAmount(cellDecimal(row, COL_SUBSIDY_AMOUNT));
                parsed.setHasTrademark(cellString(row, COL_HAS_TRADEMARK));
                parsed.setHasQualityCert(cellString(row, COL_HAS_QUALITY_CERT));
                parsed.setSalesChannel(cellString(row, COL_SALES_CHANNEL));
                parsed.setDrivenPopulation(cellInteger(row, COL_DRIVEN_POPULATION));
                parsed.setWagePaid(cellDecimal(row, COL_WAGE_PAID));
                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 cellEstablishDate(Row row, int colIndex)
    {
        Cell cell = row.getCell(colIndex);
        if (cell == null)
        {
            return null;
        }
        CellType type = cell.getCellType();
        if (type == CellType.FORMULA)
        {
            type = cell.getCachedFormulaResultType();
        }
        if (type == CellType.NUMERIC)
        {
            double numeric = cell.getNumericCellValue();
            if (DateUtil.isCellDateFormatted(cell) || looksLikeExcelSerial(numeric))
            {
                Date date = DateUtil.getJavaDate(numeric);
                return formatDate(date);
            }
        }
        String text = FORMATTER.formatCellValue(cell);
        if (StringUtils.isEmpty(text))
        {
            return null;
        }
        text = text.trim();
        if (text.matches("^\\d+(\\.0+)?$"))
        {
            try
            {
                double serial = Double.parseDouble(text);
                if (looksLikeExcelSerial(serial))
                {
                    return formatDate(DateUtil.getJavaDate(serial));
                }
            }
            catch (NumberFormatException ignored)
            {
                // fall through
            }
        }
        return normalizeDateString(text, row.getRowNum() + 1);
    }

    private static boolean looksLikeExcelSerial(double numeric)
    {
        return numeric >= 20000 && numeric <= 80000;
    }

    private static String formatDate(Date date)
    {
        return new SimpleDateFormat("yyyy-MM-dd", Locale.CHINA).format(date);
    }

    private static String normalizeDateString(String text, int excelRowNum)
    {
        String cleaned = text.replace("年", "-").replace("月", "-").replace("日", "")
                .replace("/", "-").replace(".", "-").trim();
        String[] parts = cleaned.split("-");
        if (parts.length >= 3)
        {
            try
            {
                int year = Integer.parseInt(parts[0].trim());
                int month = Integer.parseInt(parts[1].trim());
                int day = Integer.parseInt(parts[2].trim());
                return String.format(Locale.CHINA, "%04d-%02d-%02d", year, month, day);
            }
            catch (NumberFormatException ex)
            {
                throw new ServiceException("第" + excelRowNum + "行成立时间格式错误");
            }
        }
        if (cleaned.matches("\\d{8}"))
        {
            return cleaned.substring(0, 4) + "-" + cleaned.substring(4, 6) + "-" + cleaned.substring(6, 8);
        }
        return text;
    }

    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 BizFamilyRanch toEntity(ParsedRow parsed, Integer statYear, Integer statMonth,
            Long townDeptId, String townName, Long villageDeptId, String villageName)
    {
        BizFamilyRanch row = new BizFamilyRanch();
        row.setStatYear(statYear);
        row.setStatMonth(statMonth);
        row.setTownDeptId(townDeptId);
        row.setTownName(townName);
        row.setVillageDeptId(villageDeptId);
        row.setVillageName(villageName);
        row.setEnterpriseName(parsed.getEnterpriseName());
        row.setLegalRepresentative(parsed.getLegalRepresentative());
        row.setEnterpriseType(parsed.getEnterpriseType());
        row.setRegisteredCapital(parsed.getRegisteredCapital());
        row.setEstablishDate(parsed.getEstablishDate());
        row.setLivestockLaborCount(parsed.getLivestockLaborCount());
        row.setCattleYearEndStock(parsed.getCattleYearEndStock());
        row.setCattleBreedingFemaleStock(parsed.getCattleBreedingFemaleStock());
        row.setSheepYearEndStock(parsed.getSheepYearEndStock());
        row.setSheepBreedingFemaleStock(parsed.getSheepBreedingFemaleStock());
        row.setCattleOutbound(parsed.getCattleOutbound());
        row.setSheepOutbound(parsed.getSheepOutbound());
        row.setGrassBalanceArea(parsed.getGrassBalanceArea());
        row.setTransferGrassArea(parsed.getTransferGrassArea());
        row.setApprovedCarryingCapacity(parsed.getApprovedCarryingCapacity());
        row.setYearEndStockSheepUnit(parsed.getYearEndStockSheepUnit());
        row.setIncome(parsed.getIncome());
        row.setExpense(parsed.getExpense());
        row.setSubsidyYear(parsed.getSubsidyYear());
        row.setHasSubsidy(parsed.getHasSubsidy());
        row.setSubsidyAmount(parsed.getSubsidyAmount());
        row.setHasTrademark(parsed.getHasTrademark());
        row.setHasQualityCert(parsed.getHasQualityCert());
        row.setSalesChannel(parsed.getSalesChannel());
        row.setDrivenPopulation(parsed.getDrivenPopulation());
        row.setWagePaid(parsed.getWagePaid());
        return row;
    }

    public static class ParsedRow
    {
        private int excelRowNum;

        private String townName;

        private String villageName;

        private String enterpriseName;

        private String legalRepresentative;

        private String enterpriseType;

        private BigDecimal registeredCapital;

        private String establishDate;

        private Integer livestockLaborCount;

        private Integer cattleYearEndStock;

        private Integer cattleBreedingFemaleStock;

        private Integer sheepYearEndStock;

        private Integer sheepBreedingFemaleStock;

        private Integer cattleOutbound;

        private Integer sheepOutbound;

        private BigDecimal grassBalanceArea;

        private BigDecimal transferGrassArea;

        private BigDecimal approvedCarryingCapacity;

        private BigDecimal yearEndStockSheepUnit;

        private BigDecimal income;

        private BigDecimal expense;

        private Integer subsidyYear;

        private String hasSubsidy;

        private BigDecimal subsidyAmount;

        private String hasTrademark;

        private String hasQualityCert;

        private String salesChannel;

        private Integer drivenPopulation;

        private BigDecimal wagePaid;

        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 String getEnterpriseName()
        {
            return enterpriseName;
        }

        public void setEnterpriseName(String enterpriseName)
        {
            this.enterpriseName = enterpriseName;
        }

        public String getLegalRepresentative()
        {
            return legalRepresentative;
        }

        public void setLegalRepresentative(String legalRepresentative)
        {
            this.legalRepresentative = legalRepresentative;
        }

        public String getEnterpriseType()
        {
            return enterpriseType;
        }

        public void setEnterpriseType(String enterpriseType)
        {
            this.enterpriseType = enterpriseType;
        }

        public BigDecimal getRegisteredCapital()
        {
            return registeredCapital;
        }

        public void setRegisteredCapital(BigDecimal registeredCapital)
        {
            this.registeredCapital = registeredCapital;
        }

        public String getEstablishDate()
        {
            return establishDate;
        }

        public void setEstablishDate(String establishDate)
        {
            this.establishDate = establishDate;
        }

        public Integer getLivestockLaborCount()
        {
            return livestockLaborCount;
        }

        public void setLivestockLaborCount(Integer livestockLaborCount)
        {
            this.livestockLaborCount = livestockLaborCount;
        }

        public Integer getCattleYearEndStock()
        {
            return cattleYearEndStock;
        }

        public void setCattleYearEndStock(Integer cattleYearEndStock)
        {
            this.cattleYearEndStock = cattleYearEndStock;
        }

        public Integer getCattleBreedingFemaleStock()
        {
            return cattleBreedingFemaleStock;
        }

        public void setCattleBreedingFemaleStock(Integer cattleBreedingFemaleStock)
        {
            this.cattleBreedingFemaleStock = cattleBreedingFemaleStock;
        }

        public Integer getSheepYearEndStock()
        {
            return sheepYearEndStock;
        }

        public void setSheepYearEndStock(Integer sheepYearEndStock)
        {
            this.sheepYearEndStock = sheepYearEndStock;
        }

        public Integer getSheepBreedingFemaleStock()
        {
            return sheepBreedingFemaleStock;
        }

        public void setSheepBreedingFemaleStock(Integer sheepBreedingFemaleStock)
        {
            this.sheepBreedingFemaleStock = sheepBreedingFemaleStock;
        }

        public Integer getCattleOutbound()
        {
            return cattleOutbound;
        }

        public void setCattleOutbound(Integer cattleOutbound)
        {
            this.cattleOutbound = cattleOutbound;
        }

        public Integer getSheepOutbound()
        {
            return sheepOutbound;
        }

        public void setSheepOutbound(Integer sheepOutbound)
        {
            this.sheepOutbound = sheepOutbound;
        }

        public BigDecimal getGrassBalanceArea()
        {
            return grassBalanceArea;
        }

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

        public BigDecimal getTransferGrassArea()
        {
            return transferGrassArea;
        }

        public void setTransferGrassArea(BigDecimal transferGrassArea)
        {
            this.transferGrassArea = transferGrassArea;
        }

        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 BigDecimal getIncome()
        {
            return income;
        }

        public void setIncome(BigDecimal income)
        {
            this.income = income;
        }

        public BigDecimal getExpense()
        {
            return expense;
        }

        public void setExpense(BigDecimal expense)
        {
            this.expense = expense;
        }

        public Integer getSubsidyYear()
        {
            return subsidyYear;
        }

        public void setSubsidyYear(Integer subsidyYear)
        {
            this.subsidyYear = subsidyYear;
        }

        public String getHasSubsidy()
        {
            return hasSubsidy;
        }

        public void setHasSubsidy(String hasSubsidy)
        {
            this.hasSubsidy = hasSubsidy;
        }

        public BigDecimal getSubsidyAmount()
        {
            return subsidyAmount;
        }

        public void setSubsidyAmount(BigDecimal subsidyAmount)
        {
            this.subsidyAmount = subsidyAmount;
        }

        public String getHasTrademark()
        {
            return hasTrademark;
        }

        public void setHasTrademark(String hasTrademark)
        {
            this.hasTrademark = hasTrademark;
        }

        public String getHasQualityCert()
        {
            return hasQualityCert;
        }

        public void setHasQualityCert(String hasQualityCert)
        {
            this.hasQualityCert = hasQualityCert;
        }

        public String getSalesChannel()
        {
            return salesChannel;
        }

        public void setSalesChannel(String salesChannel)
        {
            this.salesChannel = salesChannel;
        }

        public Integer getDrivenPopulation()
        {
            return drivenPopulation;
        }

        public void setDrivenPopulation(Integer drivenPopulation)
        {
            this.drivenPopulation = drivenPopulation;
        }

        public BigDecimal getWagePaid()
        {
            return wagePaid;
        }

        public void setWagePaid(BigDecimal wagePaid)
        {
            this.wagePaid = wagePaid;
        }
    }
}
