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

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.web.modules.industryservice.domain.BizYakHerdInventory;

/**
 * 按年月导出全县乡镇牦牛存栏数据（基于导入模板填充）
 */
@Component
public class YakHerdInventoryExcelExporter
{
    public static final String TEMPLATE_CLASSPATH = "templates/yak_herd_inventory_import_template.xlsx";

    private static final int COL_SEQ = 0;

    private static final Pattern YEAR_IN_TEXT = Pattern.compile("(\\d{4})年");

    private static final Pattern MONTH_IN_PAREN = Pattern.compile("（\\s*\\d+\\s*月\\s*）|\\(\\s*\\d+\\s*月\\s*\\)|（X月）|\\(X月\\)",
            Pattern.CASE_INSENSITIVE);

    public byte[] export(Integer statYear, Integer statMonth, List<BizYakHerdInventory> inventoryList, String fillerName)
    {
        if (statYear == null)
        {
            throw new ServiceException("导出年份不能为空");
        }
        if (statMonth == null || statMonth < 1 || statMonth > 12)
        {
            throw new ServiceException("导出月份不合法");
        }
        if (!YakHerdInventoryYearSupport.isAllowedYear(statYear))
        {
            throw new ServiceException("导出年份不在可选范围内");
        }
        try (InputStream in = new ClassPathResource(TEMPLATE_CLASSPATH).getInputStream();
                Workbook workbook = WorkbookFactory.create(in))
        {
            return exportWorkbook(workbook, statYear, statMonth, inventoryList, fillerName);
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("导出 Excel 失败：" + ex.getMessage());
        }
    }

    /** @deprecated 兼容旧测试：按乡镇行导出 */
    @Deprecated
    public byte[] export(Integer statYear, Integer statMonth, List<SysDept> towns,
            Map<Long, BizYakHerdInventory> inventoryByTownId)
    {
        return export(statYear, statMonth, towns, inventoryByTownId, null);
    }

    /** @deprecated 兼容旧测试 */
    @Deprecated
    public byte[] export(Integer statYear, Integer statMonth, List<SysDept> towns,
            Map<Long, BizYakHerdInventory> inventoryByTownId, String fillerName)
    {
        List<BizYakHerdInventory> rows = new ArrayList<>();
        if (towns != null)
        {
            for (SysDept town : towns)
            {
                BizYakHerdInventory inv = inventoryByTownId == null ? null : inventoryByTownId.get(town.getDeptId());
                if (inv == null)
                {
                    BizYakHerdInventory empty = new BizYakHerdInventory();
                    empty.setTownDeptId(town.getDeptId());
                    empty.setTownName(town.getDeptName());
                    rows.add(empty);
                }
                else
                {
                    rows.add(inv);
                }
            }
        }
        return export(statYear, statMonth, rows, fillerName);
    }

    /** @deprecated 兼容旧测试：仅传年份时默认 12 月 */
    @Deprecated
    public byte[] export(Integer statYear, List<SysDept> towns, Map<Long, BizYakHerdInventory> inventoryByTownId)
    {
        return export(statYear, YakHerdInventoryRules.YEAR_END_STAT_MONTH, towns, inventoryByTownId, null);
    }

    byte[] exportWorkbook(Workbook workbook, Integer statYear, Integer statMonth,
            List<BizYakHerdInventory> inventoryList, String fillerName)
    {
        try (ByteArrayOutputStream out = new ByteArrayOutputStream())
        {
            Sheet sheet = workbook.getSheetAt(0);
            int headerRowIndex = YakHerdInventoryExcelParser.detectHeaderRow(sheet);
            if (headerRowIndex < 0)
            {
                throw new ServiceException("未识别到表头行（乡镇）");
            }
            updateTitleYearMonth(sheet, statYear, statMonth);
            updateFiller(sheet, fillerName);
            int dataStartRow = headerRowIndex + 3;
            CellStyle[] dataRowStyles = captureDataRowStyles(sheet, dataStartRow);
            Float dataRowHeight = captureRowHeight(sheet, dataStartRow);
            clearTemplateDataRegion(sheet, dataStartRow);
            Totals totals = new Totals();
            int rowIndex = dataStartRow;
            int seq = 1;
            if (inventoryList != null)
            {
                for (BizYakHerdInventory inv : inventoryList)
                {
                    if (inv == null)
                    {
                        continue;
                    }
                    Row row = getOrCreateRow(sheet, rowIndex);
                    clearDataCells(row);
                    setIntegerCell(row, COL_SEQ, seq++);
                    setStringCell(row, YakHerdInventoryExcelParser.COL_TOWN, inv.getTownName());
                    fillDataRow(row, inv);
                    applyDataRowStyles(row, dataRowStyles, dataRowHeight);
                    if (inv.getVillageDeptId() != null || inv.getYakTotal() != null || inv.getBullTotal() != null
                            || inv.getCowTotal() != null || inv.getGrandTotal() != null)
                    {
                        totals.add(inv);
                    }
                    rowIndex++;
                }
            }
            Row totalRow = getOrCreateRow(sheet, rowIndex);
            clearDataCells(totalRow);
            setStringCell(totalRow, COL_SEQ, "合计");
            totals.writeToRow(totalRow);
            applyDataRowStyles(totalRow, dataRowStyles, dataRowHeight);
            mergeTotalLabelCells(sheet, rowIndex);
            removeRowsAfter(sheet, rowIndex);
            workbook.write(out);
            return out.toByteArray();
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("导出 Excel 失败：" + ex.getMessage());
        }
    }

    /** 兼容旧测试签名 */
    byte[] exportWorkbook(Workbook workbook, Integer statYear, Integer statMonth, List<SysDept> towns,
            Map<Long, BizYakHerdInventory> inventoryByTownId, String fillerName)
    {
        List<BizYakHerdInventory> rows = new ArrayList<>();
        if (towns != null)
        {
            for (SysDept town : towns)
            {
                BizYakHerdInventory inv = inventoryByTownId == null ? null : inventoryByTownId.get(town.getDeptId());
                if (inv == null)
                {
                    BizYakHerdInventory empty = new BizYakHerdInventory();
                    empty.setTownDeptId(town.getDeptId());
                    empty.setTownName(town.getDeptName());
                    rows.add(empty);
                }
                else
                {
                    rows.add(inv);
                }
            }
        }
        return exportWorkbook(workbook, statYear, statMonth, rows, fillerName);
    }

    /** 兼容旧测试签名 */
    byte[] exportWorkbook(Workbook workbook, Integer statYear, List<SysDept> towns,
            Map<Long, BizYakHerdInventory> inventoryByTownId)
    {
        return exportWorkbook(workbook, statYear, YakHerdInventoryRules.YEAR_END_STAT_MONTH, towns, inventoryByTownId,
                null);
    }

    private static void clearTemplateDataRegion(Sheet sheet, int fromRow)
    {
        // 清除表体区域合并（模板合计行曾合并「序号+乡镇+村」）；合计合并稍后由 mergeTotalLabelCells 重建
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--)
        {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.getFirstRow() >= fromRow)
            {
                sheet.removeMergedRegion(i);
            }
        }
        int lastRow = sheet.getLastRowNum();
        for (int i = fromRow; i <= lastRow; i++)
        {
            Row row = sheet.getRow(i);
            if (row == null)
            {
                continue;
            }
            clearDataCells(row);
            setStringCell(row, YakHerdInventoryExcelParser.COL_TOWN, null);
        }
    }

    /**
     * 删除合计行之后的多余空行，使表体行数与数据一致。
     */
    private static void removeRowsAfter(Sheet sheet, int lastKeepRowInclusive)
    {
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--)
        {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.getFirstRow() > lastKeepRowInclusive)
            {
                sheet.removeMergedRegion(i);
            }
        }
        int lastRow = sheet.getLastRowNum();
        for (int r = lastRow; r > lastKeepRowInclusive; r--)
        {
            Row row = sheet.getRow(r);
            if (row != null)
            {
                sheet.removeRow(row);
            }
        }
        if (sheet instanceof org.apache.poi.xssf.usermodel.XSSFSheet)
        {
            org.apache.poi.xssf.usermodel.XSSFSheet xssf = (org.apache.poi.xssf.usermodel.XSSFSheet) sheet;
            String endCol = org.apache.poi.ss.util.CellReference.convertNumToColString(
                    Math.max(YakHerdInventoryExcelParser.COL_LAST, 0));
            xssf.getCTWorksheet().getDimension().setRef("A1:" + endCol + (lastKeepRowInclusive + 1));
        }
    }

    static void updateTitleYearMonth(Sheet sheet, int statYear, int statMonth)
    {
        DataFormatter formatter = new DataFormatter();
        int last = Math.min(sheet.getLastRowNum(), 4);
        for (int r = 0; r <= last; r++)
        {
            Row row = sheet.getRow(r);
            if (row == null)
            {
                continue;
            }
            short lastCell = row.getLastCellNum();
            if (lastCell < 0)
            {
                continue;
            }
            for (int c = 0; c < lastCell; c++)
            {
                Cell cell = row.getCell(c);
                if (cell == null)
                {
                    continue;
                }
                String text = formatter.formatCellValue(cell);
                // 跳过填表人/填表时间行，避免把占位年月改成统计年导致填表时间无法落到当月
                if (StringUtils.isEmpty(text) || !text.contains("年") || isFillerMetaText(text))
                {
                    continue;
                }
                Matcher yearMatcher = YEAR_IN_TEXT.matcher(text);
                String updated = text;
                if (yearMatcher.find())
                {
                    updated = yearMatcher.replaceAll(statYear + "年");
                }
                else if (updated.contains("XXXX年"))
                {
                    updated = updated.replace("XXXX年", statYear + "年");
                }
                Matcher monthMatcher = MONTH_IN_PAREN.matcher(updated);
                if (monthMatcher.find())
                {
                    updated = monthMatcher.replaceFirst("（" + statMonth + "月）");
                }
                else if (updated.contains("（X月）") || updated.contains("(X月)"))
                {
                    updated = updated.replace("（X月）", "（" + statMonth + "月）").replace("(X月)", "（" + statMonth + "月）");
                }
                cell.setCellValue(updated);
            }
        }
    }

    private static boolean isFillerMetaText(String text)
    {
        return text.contains("填表人") || text.contains("填表时间");
    }

    /**
     * 填写「填表人」与「填表时间」；填表时间固定为导出当日的当前年月。
     */
    static void updateFiller(Sheet sheet, String fillerName)
    {
        java.time.LocalDate today = java.time.LocalDate.now();
        String fillTime = today.getYear() + "年" + today.getMonthValue() + "月";
        DataFormatter formatter = new DataFormatter();
        int last = Math.min(sheet.getLastRowNum(), 4);
        for (int r = 0; r <= last; r++)
        {
            Row row = sheet.getRow(r);
            if (row == null)
            {
                continue;
            }
            short lastCell = row.getLastCellNum();
            if (lastCell < 0)
            {
                continue;
            }
            for (int c = 0; c < lastCell; c++)
            {
                Cell cell = row.getCell(c);
                if (cell == null)
                {
                    continue;
                }
                String text = formatter.formatCellValue(cell);
                if (StringUtils.isEmpty(text) || (!text.contains("填表人") && !text.contains("填表时间")))
                {
                    continue;
                }
                String updated = text;
                if (StringUtils.isNotEmpty(fillerName))
                {
                    updated = updated.replace("登录用户名称", fillerName);
                }
                updated = updated.replaceAll("填表时间：\\s*XXXX年X月", "填表时间：" + fillTime);
                updated = updated.replaceAll("填表时间：\\s*\\d{4}年\\d{1,2}月", "填表时间：" + fillTime);
                if (updated.contains("XXXX年") && updated.contains("填表时间"))
                {
                    updated = updated.replaceFirst("XXXX年X?月?", fillTime);
                }
                cell.setCellValue(updated);
            }
        }
    }

    /**
     * 合计行：「合计」合并占序号、乡镇、村三列。
     */
    private static void mergeTotalLabelCells(Sheet sheet, int rowIndex)
    {
        int firstCol = COL_SEQ;
        int lastCol = YakHerdInventoryExcelParser.COL_VILLAGE_NAME;
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--)
        {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.getFirstRow() <= rowIndex && region.getLastRow() >= rowIndex
                    && region.getFirstColumn() <= lastCol && region.getLastColumn() >= firstCol)
            {
                sheet.removeMergedRegion(i);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, firstCol, lastCol));
        Row row = sheet.getRow(rowIndex);
        if (row == null)
        {
            return;
        }
        Cell cell = row.getCell(firstCol);
        if (cell == null)
        {
            cell = row.createCell(firstCol);
            cell.setCellValue("合计");
        }
        CellStyle style = sheet.getWorkbook().createCellStyle();
        if (cell.getCellStyle() != null)
        {
            style.cloneStyleFrom(cell.getCellStyle());
        }
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(style);
    }

    private static void clearDataCells(Row row)
    {
        short lastCell = row.getLastCellNum();
        if (lastCell < 0)
        {
            return;
        }
        int maxCol = Math.max(lastCell, YakHerdInventoryExcelParser.COL_LAST + 1);
        for (int col = 0; col < maxCol; col++)
        {
            Cell cell = row.getCell(col);
            if (cell == null)
            {
                continue;
            }
            cell.setBlank();
        }
    }

    private static Row getOrCreateRow(Sheet sheet, int rowIndex)
    {
        Row row = sheet.getRow(rowIndex);
        if (row == null)
        {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }

    private static CellStyle[] captureDataRowStyles(Sheet sheet, int styleSourceRow)
    {
        CellStyle[] styles = new CellStyle[YakHerdInventoryExcelParser.COL_LAST + 1];
        Row src = sheet.getRow(styleSourceRow);
        if (src == null)
        {
            return styles;
        }
        for (int col = 0; col < styles.length; col++)
        {
            Cell cell = src.getCell(col);
            if (cell != null)
            {
                styles[col] = cell.getCellStyle();
            }
        }
        return styles;
    }

    private static Float captureRowHeight(Sheet sheet, int styleSourceRow)
    {
        Row src = sheet.getRow(styleSourceRow);
        if (src == null)
        {
            return null;
        }
        return src.getHeightInPoints();
    }

    private static void applyDataRowStyles(Row row, CellStyle[] styles, Float rowHeight)
    {
        if (row == null || styles == null)
        {
            return;
        }
        if (rowHeight != null && rowHeight > 0)
        {
            row.setHeightInPoints(rowHeight);
        }
        for (int col = 0; col < styles.length; col++)
        {
            CellStyle style = styles[col];
            if (style == null)
            {
                continue;
            }
            Cell cell = row.getCell(col);
            if (cell == null)
            {
                cell = row.createCell(col);
            }
            cell.setCellStyle(style);
        }
    }

    private static void fillDataRow(Row row, BizYakHerdInventory inv)
    {
        YakHerdInventoryValidation.recalculateDerived(inv);
        setStringCell(row, YakHerdInventoryExcelParser.COL_VILLAGE_NAME, inv.getVillageName());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_VILLAGE_COUNT, inv.getVillageCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_HERDSMAN, inv.getHerdsmanHouseholdCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_GRAND_TOTAL, inv.getGrandTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_GENDER_TOTAL, inv.getYakTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_GENDER_BULL, inv.getBullTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_GENDER_COW, inv.getCowTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_INSURED, inv.getInsuredYakCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_BULL_SECTION_TOTAL, inv.getBullTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_BREEDING_BULL, inv.getBreedingBullCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_BULL_CASTRATED, inv.getBullCastratedCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_COW_SECTION_TOTAL, inv.getCowTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_COW_YOUNG, inv.getCowYoungCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_COW_ADULT, inv.getCowAdultCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_DAIRY_SECTION_TOTAL, inv.getDairyTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_MILKING_COW, inv.getMilkingCowCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_FERTILE_COW, inv.getFertileCowCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_SHEEP_TOTAL, inv.getSheepTotal());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_MILKING_SHEEP, inv.getMilkingSheepCount());
        setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_SHEEP_TOTAL, inv.getYakSheepTotal());
    }

    private static void setStringCell(Row row, int colIndex, String value)
    {
        Cell cell = row.getCell(colIndex);
        if (cell == null)
        {
            cell = row.createCell(colIndex);
        }
        if (StringUtils.isEmpty(value))
        {
            cell.setBlank();
            return;
        }
        cell.setCellValue(value);
    }

    private static void setIntegerCell(Row row, int colIndex, Integer value)
    {
        if (value == null)
        {
            return;
        }
        Cell cell = row.getCell(colIndex);
        if (cell == null)
        {
            cell = row.createCell(colIndex);
        }
        cell.setCellValue(value.doubleValue());
    }

    public static Map<Long, BizYakHerdInventory> toTownMap(List<BizYakHerdInventory> list)
    {
        Map<Long, BizYakHerdInventory> map = new HashMap<>();
        if (list == null)
        {
            return map;
        }
        for (BizYakHerdInventory item : list)
        {
            if (item != null && item.getTownDeptId() != null)
            {
                map.put(item.getTownDeptId(), item);
            }
        }
        return map;
    }

    private static final class Totals
    {
        private int villageCount;

        private int herdsmanHouseholdCount;

        private int breedingBullCount;

        private int bullCastratedCount;

        private int bullTotal;

        private int cowYoungCount;

        private int cowAdultCount;

        private int cowTotal;

        private int milkingCowCount;

        private int fertileCowCount;

        private int dairyTotal;

        private int sheepTotal;

        private int milkingSheepCount;

        private int insuredYakCount;

        private int yakTotal;

        private int grandTotal;

        private int yakSheepTotal;

        void add(BizYakHerdInventory inv)
        {
            villageCount += safe(inv.getVillageCount());
            herdsmanHouseholdCount += safe(inv.getHerdsmanHouseholdCount());
            breedingBullCount += safe(inv.getBreedingBullCount());
            bullCastratedCount += safe(inv.getBullCastratedCount());
            bullTotal += safe(inv.getBullTotal());
            cowYoungCount += safe(inv.getCowYoungCount());
            cowAdultCount += safe(inv.getCowAdultCount());
            cowTotal += safe(inv.getCowTotal());
            milkingCowCount += safe(inv.getMilkingCowCount());
            fertileCowCount += safe(inv.getFertileCowCount());
            dairyTotal += safe(inv.getDairyTotal());
            sheepTotal += safe(inv.getSheepTotal());
            milkingSheepCount += safe(inv.getMilkingSheepCount());
            insuredYakCount += safe(inv.getInsuredYakCount());
            yakTotal += safe(inv.getYakTotal());
            grandTotal += safe(inv.getGrandTotal());
            yakSheepTotal += safe(inv.getYakSheepTotal());
        }

        void writeToRow(Row row)
        {
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_VILLAGE_COUNT, villageCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_HERDSMAN, herdsmanHouseholdCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_GRAND_TOTAL, grandTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_GENDER_TOTAL, yakTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_GENDER_BULL, bullTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_GENDER_COW, cowTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_INSURED, insuredYakCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_BULL_SECTION_TOTAL, bullTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_BREEDING_BULL, breedingBullCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_BULL_CASTRATED, bullCastratedCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_COW_SECTION_TOTAL, cowTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_COW_YOUNG, cowYoungCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_COW_ADULT, cowAdultCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_DAIRY_SECTION_TOTAL, dairyTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_MILKING_COW, milkingCowCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_FERTILE_COW, fertileCowCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_SHEEP_TOTAL, sheepTotal);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_MILKING_SHEEP, milkingSheepCount);
            setIntegerCell(row, YakHerdInventoryExcelParser.COL_YAK_SHEEP_TOTAL, yakSheepTotal);
        }

        private static int safe(Integer value)
        {
            return value == null ? 0 : value;
        }
    }
}
