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

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
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.exception.ServiceException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.web.modules.industryservice.domain.BizCooperativeDevelopment;

/**
 * 按年月导出合作社发展数据（基于导入模板填充；不含合计行）
 */
@Component
public class CooperativeDevelopmentExcelExporter
{
    public static final String TEMPLATE_CLASSPATH = CooperativeDevelopmentExcelParser.TEMPLATE_CLASSPATH;

    private static final int COL_SEQ = CooperativeDevelopmentExcelParser.COL_SEQ;

    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<BizCooperativeDevelopment> reportList,
            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, reportList, fillerName);
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("导出 Excel 失败：" + ex.getMessage());
        }
    }

    byte[] exportWorkbook(Workbook workbook, Integer statYear, Integer statMonth,
            List<BizCooperativeDevelopment> reportList, String fillerName)
    {
        try (ByteArrayOutputStream out = new ByteArrayOutputStream())
        {
            Sheet sheet = CooperativeDevelopmentExcelParser.resolveSheet(workbook);
            int headerRowIndex = CooperativeDevelopmentExcelParser.detectHeaderRow(sheet);
            if (headerRowIndex < 0)
            {
                throw new ServiceException("未识别到表头行（村/乡镇/合作社名称）");
            }
            updateTitleYearMonth(sheet, statYear, statMonth);
            updateFiller(sheet, fillerName);
            int dataStartRow = headerRowIndex + CooperativeDevelopmentExcelParser.HEADER_SPAN;
            // 模板仅预置少量带边框数据行；超出行需复制首行样式，避免第 3 行起无边框
            CellStyle[] dataRowStyles = captureDataRowStyles(sheet, dataStartRow);
            Float dataRowHeight = captureRowHeight(sheet, dataStartRow);
            clearTemplateDataRegion(sheet, dataStartRow);
            int rowIndex = dataStartRow;
            int seq = 1;
            if (reportList != null)
            {
                for (BizCooperativeDevelopment report : reportList)
                {
                    if (report == null)
                    {
                        continue;
                    }
                    Row row = getOrCreateRow(sheet, rowIndex);
                    clearDataCells(row);
                    setIntegerCell(row, COL_SEQ, seq++);
                    fillDataRow(row, report);
                    applyDataRowStyles(row, dataRowStyles, dataRowHeight);
                    rowIndex++;
                }
            }
            // 无合计行：保留至最后一条数据行；无数据时保留至表头末行
            int lastKeepRow = rowIndex > dataStartRow ? rowIndex - 1 : dataStartRow - 1;
            removeRowsAfter(sheet, lastKeepRow);
            workbook.write(out);
            return out.toByteArray();
        }
        catch (ServiceException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new ServiceException("导出 Excel 失败：" + ex.getMessage());
        }
    }

    private static void clearTemplateDataRegion(Sheet sheet, int fromRow)
    {
        int lastRow = sheet.getLastRowNum();
        for (int i = fromRow; i <= lastRow; i++)
        {
            Row row = sheet.getRow(i);
            if (row == null)
            {
                continue;
            }
            clearDataCells(row);
            setStringCell(row, COL_SEQ, 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(CooperativeDevelopmentExcelParser.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);
                // 跳过填表人/填表时间行，避免把「XXXX年X月」先改成统计年导致填表日期无法落到当月
                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 + "月）");
                }
                cell.setCellValue(updated);
            }
        }
    }

    /**
     * 将填表人、填表时间/日期写为当前登录用户与<strong>系统当月</strong>（非导出统计年月）。
     */
    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) || !isFillerMetaText(text))
                {
                    continue;
                }
                String updated = text;
                if (StringUtils.isNotEmpty(fillerName))
                {
                    updated = updated.replace("登录用户名称", fillerName);
                }
                // 兼容「填表时间」「填表日期」及占位 XXXX年X月 / 已被误替换的 yyyy年X月
                updated = updated.replaceAll("(填表时间|填表日期)：\\s*XXXX年X月", "$1：" + fillTime);
                updated = updated.replaceAll("(填表时间|填表日期)：\\s*\\d{4}年X月", "$1：" + fillTime);
                updated = updated.replaceAll("(填表时间|填表日期)：\\s*\\d{4}年\\d{1,2}月", "$1：" + fillTime);
                if (updated.contains("XXXX年")
                        && (updated.contains("填表时间") || updated.contains("填表日期")))
                {
                    updated = updated.replaceFirst("XXXX年X?月?", fillTime);
                }
                cell.setCellValue(updated);
            }
        }
    }

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

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

    /**
     * 捕获模板数据行各列样式（边框等），供新增行复用。
     */
    private static CellStyle[] captureDataRowStyles(Sheet sheet, int styleSourceRow)
    {
        CellStyle[] styles = new CellStyle[CooperativeDevelopmentExcelParser.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();
    }

    /**
     * 将模板数据行样式套到导出行（含空单元格），保证边框与第 1、2 行一致。
     */
    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 Row getOrCreateRow(Sheet sheet, int rowIndex)
    {
        Row row = sheet.getRow(rowIndex);
        if (row == null)
        {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }

    private static void fillDataRow(Row row, BizCooperativeDevelopment report)
    {
        setStringCell(row, CooperativeDevelopmentExcelParser.COL_VILLAGE, report.getVillageName());
        setStringCell(row, CooperativeDevelopmentExcelParser.COL_TOWN, report.getTownName());
        setStringCell(row, CooperativeDevelopmentExcelParser.COL_COOP_NAME, report.getCooperativeName());
        setStringCell(row, CooperativeDevelopmentExcelParser.COL_LEGAL_REP, report.getLegalRepresentative());
        setStringCell(row, CooperativeDevelopmentExcelParser.COL_ESTABLISH_DATE, report.getEstablishDate());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_REGISTERED_CAPITAL, report.getRegisteredCapital());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_YEAR_END_ASSETS, report.getYearEndTotalAssets());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_MEMBER_HOUSEHOLD, report.getMemberHouseholdCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_MEMBER_POPULATION, report.getMemberPopulationCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_POVERTY_MONITOR, report.getPovertyMonitorCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_CATTLE_SHARE, report.getCattleShareCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_SHEEP_SHARE, report.getSheepShareCount());
        int livestockTotal = report.getLivestockShareTotal() != null
                ? report.getLivestockShareTotal()
                : safe(report.getCattleShareCount()) + safe(report.getSheepShareCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_LIVESTOCK_SHARE_TOTAL, livestockTotal);
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_GRASSLAND_SHARE, report.getGrasslandShareArea());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_LABOR_SHARE, report.getLaborShareCount());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_CASH_SHARE, report.getCashShareAmount());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_OTHER_SHARE, report.getOtherShareAmount());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_TOTAL_INCOME, report.getTotalIncome());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_NET_INCOME, report.getNetIncome());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_CASH_DIVIDEND, report.getCashDividend());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_IN_KIND_DIVIDEND, report.getInKindDividend());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_REVOLVING_FUND, report.getRevolvingFund());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_LOSS, report.getLossAmount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_COLLEGE_COUNT, report.getCollegeGraduateCount());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_COLLEGE_WAGE, report.getCollegeGraduateWage());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_FARMER_TRANSFER_COUNT,
                report.getFarmerTransferEmploymentCount());
        setDecimalCell(row, CooperativeDevelopmentExcelParser.COL_FARMER_TRANSFER_WAGE, report.getFarmerTransferWage());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_CATTLE_INVENTORY, report.getCattleInventoryCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_CATTLE_OUTBOUND, report.getCattleOutboundCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_SHEEP_INVENTORY, report.getSheepInventoryCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_SHEEP_OUTBOUND, report.getSheepOutboundCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_TRADEMARK, report.getTrademarkProductCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_QUALITY_CERT, report.getQualityCertProductCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_TRAIN_MANAGE, report.getTrainingManageCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_TRAIN_SKILL, report.getTrainingSkillCount());
        setIntegerCell(row, CooperativeDevelopmentExcelParser.COL_TRAIN_OTHER, report.getTrainingOtherCount());
        writeMultiSelect(row, CooperativeDevelopmentExcelParser.COL_SCOPE_START,
                CooperativeDevelopmentExcelParser.BUSINESS_SCOPE_LABELS, report.getBusinessScope());
        writeSingleSelect(row, CooperativeDevelopmentExcelParser.COL_MODEL_START,
                CooperativeDevelopmentExcelParser.BUSINESS_MODEL_LABELS, report.getBusinessModel());
        writeSingleSelect(row, CooperativeDevelopmentExcelParser.COL_STATUS_START,
                CooperativeDevelopmentExcelParser.OPERATION_STATUS_LABELS, report.getOperationStatus());
    }

    private static void writeMultiSelect(Row row, int startCol, String[] labels, String commaSeparated)
    {
        Set<String> selected = new HashSet<>();
        if (StringUtils.isNotEmpty(commaSeparated))
        {
            for (String part : commaSeparated.split("[,，]"))
            {
                if (StringUtils.isNotEmpty(part))
                {
                    selected.add(part.trim());
                }
            }
        }
        for (int i = 0; i < labels.length; i++)
        {
            if (selected.contains(labels[i]))
            {
                setStringCell(row, startCol + i, "1");
            }
        }
    }

    private static void writeSingleSelect(Row row, int startCol, String[] labels, String selected)
    {
        if (StringUtils.isEmpty(selected))
        {
            return;
        }
        for (int i = 0; i < labels.length; i++)
        {
            if (selected.equals(labels[i]))
            {
                setStringCell(row, startCol + i, "1");
                return;
            }
        }
    }

    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());
    }

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

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