package com.trafficaudit.reportexport.calc;

import com.trafficaudit.common.util.RegionUtil;
import com.trafficaudit.reportexport.calc.FreightCalc.FreightMatrix;
import com.trafficaudit.reportexport.calc.FreightCalc.FreightMetrics;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 汇总整本“数据驱动回填”引擎（M2 台账层，2026-09-09 接入）。
 *
 * 目标：生成任意月份《道路运输量汇总表（整本）》时不再依赖当月人工母版；
 * 母版只当版式底稿，当月值按已验证口径从库现算写入（班线包车 2026-07 与人工母版
 * 350 格 0 差异、公交/出租/轨道轮渡同批 0 差异）。
 *
 * 本版覆盖 4 张台账页：班线包车、公交、出租、轨道轮渡；
 * 网约车依赖《网约车订单及全省总量》拆分输入、货运页在 M3 接入，后续版本补充。
 *
 * 写入规则：
 * 1) 只写“市州明细行”的 2026 年 m 月当月值列（m 月值列 = 第 2+2*(m-1) 列，0 基，即 C 起）；
 * 2) 全省行（=17 市州求和公式）与跨页合成页均为公式，不动，Excel 打开自动重算；
 * 3) 2025 同期参照列保留母版缓存值（同比公式自动引用）；
 * 4) 源数据整月缺失时收集提示、不回填；市州当月全为 0 时保持母版空单元格。
 */
@Service
public class SummaryWorkbookFiller {

    private static final Logger log = LoggerFactory.getLogger(SummaryWorkbookFiller.class);

    @Resource
    private MidCalc midCalc;
    @Resource
    private PaxCalc paxCalc;
    @Resource
    private FreightCalc freightCalc;
    @Resource
    private WycSplitCalc wycSplitCalc;

    /** 2026 年 1 月当月值列（0 基，即 C 列） */
    private static final int FIRST_MONTH_COL = 2;

    /** 回填 1..keepMonths 月台账页，返回写入格数；缺数提示写入 problems */
    public int fillMonthlyLedger(XSSFWorkbook wb, String period, int year, int keepMonths, List<String> problems) {
        if (wb == null || keepMonths <= 0) return 0;
        if (problems == null) problems = new ArrayList<>();
        String yearPrefix = year + "-";
        int filled = 0;
        filled += fillCityRows(wb, "班线包车", midCalc.banxianMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
        filled += fillCityRows(wb, "公交", paxCalc.busMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
        filled += fillCityRows(wb, "出租", paxCalc.taxiMonthly(yearPrefix, keepMonths), year, keepMonths, problems, false);
        filled += fillTrackFerry(wb, paxCalc.railFerryMonthly(yearPrefix, keepMonths), year, keepMonths, problems);
        filled += fillFreightSheet(wb, period, keepMonths, problems);
        filled += fillWycSheet(wb, period, keepMonths, problems);
        return filled;
    }

    /** 通用台账页（班线包车/公交/出租）回填：按“列 A 市州名 + 列 B 指标文案”定位行，不写死行号 */
    private int fillCityRows(XSSFWorkbook wb, String sheetName,
                             Map<Integer, Map<String, double[]>> monthly,
                             int year, int keepMonths, List<String> problems, boolean wyc) {
        Sheet sh = wb.getSheet(sheetName);
        if (sh == null) {
            problems.add(sheetName + "页不存在（母版缺失该页），跳过自动回填");
            return 0;
        }
        int cap = monthColumnCapacity(sh);
        int mMax = Math.min(keepMonths, cap);
        if (mMax <= 0) {
            problems.add(sheetName + "页未识别到 2026 年累计列，跳过自动回填");
            return 0;
        }
        // 1) 市州起始行（列 A 为规范市州名）
        Map<String, Integer> cityStart = new LinkedHashMap<>();
        for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String city = cityOf(row.getCell(0));
            if (city != null && !cityStart.containsKey(city)) cityStart.put(city, r);
        }
        if (cityStart.isEmpty()) {
            problems.add(sheetName + "页未识别到市州行，跳过自动回填");
            return 0;
        }
        List<Integer> starts = new ArrayList<>(cityStart.values());
        int filled = 0;
        List<String> missingMonths = new ArrayList<>();
        for (int m = 1; m <= mMax; m++) {
            if (!monthly.containsKey(m)) missingMonths.add(m + "月");
        }
        if (!missingMonths.isEmpty()) {
            problems.add(sheetName + "缺 " + year + " 年 " + String.join("、", missingMonths) + " 源数据（未回填）");
        }
        for (int i = 0; i < starts.size(); i++) {
            String city = findCityByStart(sh, starts.get(i));
            if (city == null) continue;
            int end = (i + 1 < starts.size()) ? starts.get(i + 1) - 1 : sh.getLastRowNum();
            // 2) 行内各指标所在行（B 列文案 → 指标位 0..3）
            int[] metricRow = new int[]{-1, -1, -1, -1};
            for (int r = starts.get(i); r <= end; r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                int idx = metricIndexOf(text(row.getCell(1)));
                if (idx >= 0 && metricRow[idx] < 0) metricRow[idx] = r;
            }
            if (metricRow[0] < 0 && metricRow[1] < 0) continue;
            for (int m = 1; m <= mMax; m++) {
                Map<String, double[]> mm = monthly.get(m);
                if (mm == null) continue;
                double[] arr = mm.get(city);
                if (arr == null || (arr[0] == 0.0 && arr[1] == 0.0)) continue;
                int col = FIRST_MONTH_COL + 2 * (m - 1);
                for (int idx = 0; idx < 4; idx++) {
                    if (metricRow[idx] < 0) continue;
                    double val = arr[idx];
                    if (val == 0.0) continue;
                    filled += setNumeric(sh, metricRow[idx], col, val);
                }
            }
        }
        return filled;
    }

    /** 货运页回填：全省块 + 17 市州块的 规上/规下/合计 × 货运量、周转量（逐月现算；当月值列=2+2*(m-1)） */
    private int fillFreightSheet(XSSFWorkbook wb, String period, int keepMonths, List<String> problems) {
        Sheet sh = wb.getSheet(" 货运");
        if (sh == null) sh = wb.getSheet("货运");
        if (sh == null) {
            problems.add("货运页不存在（母版缺失该页），跳过自动回填");
            return 0;
        }
        int cap = monthColumnCapacity(sh);
        int mMax = Math.min(keepMonths, cap);
        if (mMax <= 0) {
            problems.add("货运页未识别到 2026 年累计列，跳过自动回填");
            return 0;
        }
        int year = periodYear(period);
        // 块（全省/市州）：指标键 -> 行号
        Map<String, Map<String, Integer>> blockRows = new LinkedHashMap<>();
        String cur = null;
        for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String a = text(row.getCell(0));
            if (a != null && !a.trim().isEmpty()) {
                String norm = RegionUtil.normalizeCityName(a.trim());
                if (FreightCalc.PROVINCE.equals(norm) || RegionUtil.cityList().contains(norm)) {
                    cur = norm;
                    blockRows.computeIfAbsent(cur, k -> new LinkedHashMap<>());
                } else {
                    cur = null; // 标题行 / 非全省非市州 -> 脱离块
                }
            }
            if (cur == null) continue;
            String key = freightMetricKey(text(row.getCell(1)));
            if (key == null) continue;
            blockRows.get(cur).putIfAbsent(key, r);
        }
        if (blockRows.isEmpty()) {
            problems.add("货运页未识别到全省/市州行，跳过自动回填");
            return 0;
        }
        int filled = 0;
        // 只回填目标期当月：1..目标月-1 的值以母版（含同期备份母版还原）为准，避免改写已确认口径
        int targetM = Math.min(periodMonth(period), mMax);
        for (int m = targetM; m <= targetM; m++) {
            String mp = String.format("%04d-%02d", year, m);
            FreightMatrix mx;
            try {
                mx = freightCalc.calc(mp);
            } catch (Exception e) {
                problems.add("货运页 " + mp + " 取数失败：" + e.getMessage());
                continue;
            }
            int col = FIRST_MONTH_COL + 2 * (m - 1);
            for (Map.Entry<String, Map<String, Integer>> e : blockRows.entrySet()) {
                FreightMetrics fm = mx.getMonth().get(e.getKey());
                if (fm == null) continue;
                Map<String, Integer> rows = e.getValue();
                // 1 月列是公式的“派生行”（全省合计 货运量/周转量、市州 规上+规下周转量）不写值，
                // 由 ensureMonthFormulas 把公式按月右移，保持与母版一致
                filled += setFreightCell(sh, skipIfDerived(sh, rows.get("totalFreight")), col, fm.getTotalFreight());
                filled += setFreightCell(sh, skipIfDerived(sh, rows.get("aboveFreight")), col, fm.getAboveFreight());
                filled += setFreightCell(sh, skipIfDerived(sh, rows.get("belowFreight")), col, fm.getBelowFreight());
                filled += setFreightCell(sh, skipIfDerived(sh, rows.get("totalTurnover")), col, fm.getTotalTurnover());
                filled += setFreightCell(sh, skipIfDerived(sh, rows.get("aboveTurnover")), col, fm.getAboveTurnover());
                filled += setFreightCell(sh, skipIfDerived(sh, rows.get("belowTurnover")), col, fm.getBelowTurnover());
            }
        }
        return filled;
    }

    /** 该行 1 月列（C）是公式 -> 派生行，返回 null 表示不回填数值 */
    private Integer skipIfDerived(Sheet sh, Integer r) {
        if (r == null) return null;
        Row row = sh.getRow(r);
        if (row == null) return r;
        Cell c = row.getCell(FIRST_MONTH_COL);
        if (c != null && c.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) return null;
        return r;
    }

    private int setFreightCell(Sheet sh, Integer r, int col, Double v) {
        if (r == null || v == null) return 0;
        return setNumeric(sh, r, col, v);
    }

    /** 货运页 B 列指标文案 -> 指标键（规上+规下 / 规上 / 规下 × 货运量 / 周转量） */
    private String freightMetricKey(String b) {
        if (b == null) return null;
        String s = b.replaceAll("\\s+", "");
        if (s.isEmpty()) return null;
        boolean turnover = s.contains("周转量");
        if (s.contains("规上+规下")) return turnover ? "totalTurnover" : "totalFreight";
        if (s.contains("规上")) return turnover ? "aboveTurnover" : "aboveFreight";
        if (s.contains("规下")) return turnover ? "belowTurnover" : "belowFreight";
        return turnover ? "totalTurnover" : "totalFreight"; // 全省块的“货运量/货物周转量”= 合计
    }

    /** 网约车页回填：17 市州 的 客运量/周转量/其中城市内客运量/其中城市内周转量（逐月现算）
     *  全省行（=17 市州求和公式）由 ensureMonthFormulas 按月扩列，不在此处写值。 */
    private int fillWycSheet(XSSFWorkbook wb, String period, int keepMonths, List<String> problems) {
        Sheet sh = wb.getSheet("网约车");
        if (sh == null) {
            problems.add("网约车页不存在（母版缺失该页），跳过自动回填");
            return 0;
        }
        int cap = monthColumnCapacity(sh);
        int mMax = Math.min(keepMonths, cap);
        if (mMax <= 0) {
            problems.add("网约车页未识别到 2026 年累计列，跳过自动回填");
            return 0;
        }
        int year = periodYear(period);
        Map<String, Map<String, Integer>> cityMetricRows = new LinkedHashMap<>();
        String cur = null;
        for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String a = text(row.getCell(0));
            if (a != null && !a.trim().isEmpty()) {
                String norm = RegionUtil.normalizeCityName(a.trim());
                cur = RegionUtil.cityList().contains(norm) ? norm : null;
                if (cur != null) cityMetricRows.computeIfAbsent(cur, k -> new LinkedHashMap<>());
            }
            if (cur == null) continue;
            String key = wycMetricKey(text(row.getCell(1)));
            if (key == null) continue;
            cityMetricRows.get(cur).putIfAbsent(key, r);
        }
        if (cityMetricRows.isEmpty()) {
            problems.add("网约车页未识别到市州行，跳过自动回填");
            return 0;
        }
        int filled = 0;
        // 只回填目标期当月（历史月以母版/历史生成件为准）
        int targetM = Math.min(periodMonth(period), mMax);
        for (int m = targetM; m <= targetM; m++) {
            String mp = String.format("%04d-%02d", year, m);
            WycSplitCalc.WycResult wr;
            try {
                wr = wycSplitCalc.calc(mp);
            } catch (Exception e) {
                if (m == mMax) {
                    problems.add("网约车页 " + mp + " 源数据缺失，当月未回填：" + e.getMessage());
                }
                continue;
            }
            int col = FIRST_MONTH_COL + 2 * (m - 1);
            for (Map.Entry<String, Map<String, Integer>> e : cityMetricRows.entrySet()) {
                WycSplitCalc.WycMetrics wm = wr.getByCity().get(e.getKey());
                if (wm == null) continue;
                Map<String, Integer> rows = e.getValue();
                filled += setFreightCell(sh, rows.get("pax"), col, wm.getTotalPax());
                filled += setFreightCell(sh, rows.get("turnover"), col, wm.getTotalTurnover());
                filled += setFreightCell(sh, rows.get("cityPax"), col, wm.getCityPax());
                filled += setFreightCell(sh, rows.get("cityTurnover"), col, wm.getCityTurnover());
            }
        }
        return filled;
    }

    /** 网约车页 B 列指标文案 -> 指标键 */
    private String wycMetricKey(String b) {
        if (b == null) return null;
        String s = b.replaceAll("\\s+", "");
        if (s.isEmpty()) return null;
        boolean city = s.contains("城市内");
        boolean turnover = s.contains("周转量");
        if (city) return turnover ? "cityTurnover" : "cityPax";
        return turnover ? "turnover" : "pax";
    }

    /** 轨道轮渡页（轨道=武汉/黄石；轮渡仅武汉）专用回填 */
    private int fillTrackFerry(XSSFWorkbook wb, Map<Integer, Map<String, double[]>> monthly,
                               int year, int keepMonths, List<String> problems) {
        String sheetName = "轨道、轮渡";
        Sheet sh = wb.getSheet(sheetName);
        if (sh == null) return 0;
        int cap = monthColumnCapacity(sh);
        int mMax = Math.min(keepMonths, cap);
        if (mMax <= 0) return 0;
        // 轨道块与轮渡块标题行（列 A 文案）
        int trackTitle = -1, ferryTitle = -1;
        for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
            String a = text(sh.getRow(r) == null ? null : sh.getRow(r).getCell(0));
            if (a == null) continue;
            if (trackTitle < 0 && a.startsWith("轨道客运量")) trackTitle = r;
            else if (ferryTitle < 0 && a.startsWith("轮渡客运量")) ferryTitle = r;
        }
        if (trackTitle < 0 || ferryTitle < 0) {
            problems.add(sheetName + "页未识别到轨道/轮渡块标题，跳过自动回填");
            return 0;
        }
        int filled = 0;
        filled += fillFerryBlock(sh, monthly, trackTitle + 3, ferryTitle - 1, 0, mMax, problems, sheetName);
        filled += fillFerryBlock(sh, monthly, ferryTitle + 3, sh.getLastRowNum(), 2, mMax, problems, sheetName);
        return filled;
    }

    /** 填充一段“标题行后数据区”：base 为该块指标位基（轨道 0 / 轮渡 2）；block 内
     *  每市州 2 行：客运量行（列 A=市州名）、旅客周转量行（A 空，B 含“周转量”） */
    private int fillFerryBlock(Sheet sh, Map<Integer, Map<String, double[]>> monthly,
                               int start, int end, int base, int mMax,
                               List<String> problems, String sheetName) {
        int filled = 0;
        // 收集市州客运量行
        Map<String, Integer> paxRow = new LinkedHashMap<>();
        for (int r = start; r <= end; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String city = cityOf(row.getCell(0));
            if (city != null) paxRow.put(city, r);
        }
        for (Map.Entry<String, Integer> e : paxRow.entrySet()) {
            String city = e.getKey();
            int rp = e.getValue();
            int rt = findTurnoverRow(sh, rp + 1, Math.min(end, rp + 6));
            if (rt < 0) continue;
            for (int m = 1; m <= mMax; m++) {
                Map<String, double[]> mm = monthly.get(m);
                if (mm == null) continue;
                double[] arr = mm.get(city);
                if (arr == null) continue;
                int col = FIRST_MONTH_COL + 2 * (m - 1);
                double pax = arr[base];
                double turn = arr[base + 1];
                if (pax != 0.0) filled += setNumeric(sh, rp, col, pax);
                if (turn != 0.0) filled += setNumeric(sh, rt, col, turn);
            }
        }
        return filled;
    }

    private int findTurnoverRow(Sheet sh, int from, int to) {
        for (int r = from; r <= to; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String a = text(row.getCell(0));
            if (a != null && !a.trim().isEmpty()) return -1; // 进入下一市州块
            String b = text(row.getCell(1));
            if (b != null && b.contains("周转量")) return r;
        }
        return -1;
    }

    /** 写数值：格不存在则建格；格带日期样式（如公交/出租 8 月列继承了表头“yyyy年m月”）时改回同行 1 月列的数值样式，避免数值显示成日期 */
    private int setNumeric(Sheet sh, int r, int col, double val) {
        Row row = sh.getRow(r);
        if (row == null) row = sh.createRow(r);
        Cell c = row.getCell(col);
        if (c != null && c.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
            return 0; // 母版本来就靠公式算的行（合计/同页求和）保持公式，交 Excel 重算
        }
        Cell ref = row.getCell(FIRST_MONTH_COL);
        boolean needStyle = false;
        if (c == null) {
            c = row.createCell(col);
            needStyle = true;
        } else if (c.getCellType() == org.apache.poi.ss.usermodel.CellType.BLANK
                || (c.getCellType() == org.apache.poi.ss.usermodel.CellType.NUMERIC
                    && org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(c))) {
            // 母版把 8 月列的表头日期格式带到了数据格（或有格式无值），写值前改回同行 1 月列的数字格式
            needStyle = true;
        }
        if (needStyle && ref != null && ref.getCellStyle() != null) {
            c.setCellStyle(ref.getCellStyle());
        }
        c.setCellValue(val);
        return 1;
    }

    /** 行 B 列文案 → 指标位：0 客运量 / 1 旅客周转量 / 2 其中城市内客运量或其中个体客运量 / 3 对应周转量 */
    private int metricIndexOf(String b) {
        if (b == null) return -1;
        if (b.contains("其中个体旅客周转量") || b.contains("其中城市内旅客周转量")) return 3;
        if (b.contains("其中个体客运量") || b.contains("其中城市内客运量")) return 2;
        if (b.contains("旅客周转量")) return 1;
        if (b.contains("客运量")) return 0;
        return -1;
    }

    /** 列 A 文案 → 规范市州名（湖北省/全省、非市州行返回 null） */
    private String cityOf(Cell a) {
        String t = text(a);
        if (t == null || t.trim().isEmpty()) return null;
        String norm = RegionUtil.normalizeCityName(t.trim());
        return RegionUtil.cityList().contains(norm) ? norm : null;
    }

    private String findCityByStart(Sheet sh, int startRow) {
        Row row = sh.getRow(startRow);
        return row == null ? null : cityOf(row.getCell(0));
    }

    /**
     * 为第 month 月补“当月值”列公式：取同一行最近一个月仍为公式的值列，整列右移过来
     * （如公交 7 月列 O 的 =O9+O13+... -> 8 月列 Q 的 =Q9+Q13+...）。
     * 只补空单元格；已有数值（货运全省块/市州块由数据回填）或已有公式的格不动。
     */
    public int ensureMonthFormulas(XSSFWorkbook wb, int year, int month) {
        if (wb == null || month <= 1) return 0;
        int targetCol = FIRST_MONTH_COL + 2 * (month - 1);
        int written = 0;
        int carried = 0;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sh = wb.getSheetAt(i);
            if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
            boolean derivedSheet = isDerivedFormulaSheet(sh.getSheetName());
            for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                if (hasContent(row.getCell(targetCol))) continue;
                Cell srcCell = null;
                int srcMonth = -1;
                for (int s = month - 1; s >= 1; s--) {
                    Cell c = row.getCell(FIRST_MONTH_COL + 2 * (s - 1));
                    if (c != null && c.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) {
                        srcCell = c;
                        srcMonth = s;
                        break;
                    }
                }
                if (srcCell == null) {
                    if (!derivedSheet) continue;
                    // 派生页（城市客运/中口径明细/公路总客运）里「各月都是同一个常量」的结构性常量行
                    // （如中口径明细 武汉 城际城乡巡游出租=0），按月沿用该常量，避免当月值列空着导致累计缺项
                    double struct = structuralConstant(row, month);
                    if (Double.isNaN(struct)) continue;
                    Cell target = row.getCell(targetCol);
                    if (target == null) target = row.createCell(targetCol);
                    Cell prev = row.getCell(FIRST_MONTH_COL + 2 * (month - 2));
                    if (prev != null && prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
                    target.setCellValue(struct);
                    carried++;
                    written++;
                    continue;
                }
                String f = srcCell.getCellFormula();
                if (f == null || f.isEmpty()) continue;
                Cell target = row.getCell(targetCol);
                if (target == null) target = row.createCell(targetCol);
                if (srcCell.getCellStyle() != null) target.setCellStyle(srcCell.getCellStyle());
                target.setCellFormula(shiftFormulaColumns(f, 2 * (month - srcMonth)));
                written++;
            }
        }
        if (carried > 0 && log != null) {
            log.info("汇总工作簿：派生页结构性常量行按月沿用 {} 格（第 {} 月）", carried, month);
        }
        return written;
    }

    /** 该行 1..month-1 月的当月值列是否全为同一个数值常量（结构性常量行，如各月恒为 0） */
    private double structuralConstant(Row row, int month) {
        if (row == null || month <= 2) return Double.NaN;
        Double v = null;
        for (int s = 1; s < month; s++) {
            Cell c = row.getCell(FIRST_MONTH_COL + 2 * (s - 1));
            if (c == null || c.getCellType() != org.apache.poi.ss.usermodel.CellType.NUMERIC) return Double.NaN;
            double d = c.getNumericCellValue();
            if (v == null) v = d;
            else if (Math.abs(d - v) > 1e-9) return Double.NaN;
        }
        return v == null ? Double.NaN : v;
    }

    /** 派生页（当月值全部来自其他页/常量，纯公式驱动）：城市客运 / 中口径明细 / 公路总客运 / 中口径客运量 */
    private boolean isDerivedFormulaSheet(String name) {
        if (name == null) return false;
        String n = name.trim();
        return "城市客运".equals(n) || "中口径明细".equals(n) || "公路总客运".equals(n) || "中口径客运量".equals(n);
    }

    /** 该格是否已有内容（数值/文本/公式）；仅带样式的空壳（如母版里继承表头日期格式的格子）视为空 */
    private boolean hasContent(Cell c) {
        if (c == null) return false;
        switch (c.getCellType()) {
            case BLANK: return false;
            case STRING: return c.getStringCellValue() != null && !c.getStringCellValue().trim().isEmpty();
            default: return true;
        }
    }

    /** 0 基列号 + 1 基行号 -> "Q5" */
    private String cellRef(int col0, int row1) {
        StringBuilder sb = new StringBuilder();
        int n = col0 + 1;
        while (n > 0) {
            int rem = (n - 1) % 26;
            sb.insert(0, (char) ('A' + rem));
            n = (n - 1) / 26;
        }
        return sb.toString() + row1;
    }

    /** 从 "2026-08" 取月份（解析失败返回 1） */
    private int periodMonth(String period) {
        if (period != null && period.length() >= 7) {
            try { return Integer.parseInt(period.substring(5, 7)); } catch (Exception ignore) { }
        }
        return 1;
    }

    /** 从 "2026-08" 取年份 */
    private int periodYear(String period) {
        if (period != null && period.length() >= 4) {
            try { return Integer.parseInt(period.substring(0, 4)); } catch (Exception ignore) { }
        }
        return 0;
    }

    /**
     * 为第 m 月补“与去年同比”公式：把第 m-1 月同比列的公式整体右移 2 列
     * （如公交 7 月 P5 公式 =O5/AH5-1 -> 8 月 R5 =Q5/AJ5-1），
     * 因此同比分母取的就是汇总表 2025 年同月列（用户口径）。
     */
    public int ensureMonthYoyFormulas(XSSFWorkbook wb, int year, int month) {
        if (wb == null || month <= 1) return 0;
        int valueCol = FIRST_MONTH_COL + 2 * (month - 1);
        int yoyCol = valueCol + 1;
        int prevYoyCol = yoyCol - 2;
        int written = 0;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sh = wb.getSheetAt(i);
            if (sh == null) continue;
            if (!isMonthLedgerSheet(sh.getSheetName())) continue;
            for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                Cell prev = row.getCell(prevYoyCol);
                if (prev == null || prev.getCellType() != org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
                Cell target = row.getCell(yoyCol);
                if (target != null && target.getCellType() == org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
                String f = prev.getCellFormula();
                if (f == null || f.isEmpty()) continue;
                String shifted = shiftFormulaToNextMonth(sh, f, year, month);
                if (target == null) target = row.createCell(yoyCol);
                // 母版把新月份的同比列做成了普通数值格式（显示成小数），统一沿用上月同比列的样式（百分数）
                if (prev.getCellStyle() != null) target.setCellStyle(prev.getCellStyle());
                try {
                    target.setCellFormula(shifted);
                    written++;
                } catch (Exception e) {
                    throw new IllegalStateException(String.format(
                            "同比公式补写失败：sheet=%s cell=%s 原式=%s 目标式=%s",
                            sh.getSheetName(), cellRef(yoyCol, r + 1), f, shifted), e);
                }
            }
        }
        return written;
    }

    /** 随月扩列的长表页（表头在 1..3 行、月份组为“当月值+同比”交替） */
    private boolean isMonthLedgerSheet(String name) {
        if (name == null) return false;
        String n = name.trim();
        return "货运".equals(n) || "公路总客运".equals(n) || "班线包车".equals(n) || "城市客运".equals(n)
                || "公交".equals(n) || "出租".equals(n) || "网约车".equals(n)
                || "轨道、轮渡".equals(n) || "轨道轮渡".equals(n)
                || "中口径明细".equals(n) || "中口径客运量".equals(n);
    }

    /** 公式内所有 A1 形式的列引用右移 delta 列（SUM 等函数名后无数字，不会被匹配） */
    private String shiftFormulaColumns(String formula, int delta) {
        java.util.regex.Matcher m = java.util.regex.Pattern
                .compile("(?<![A-Za-z0-9_$])([$]?)([A-Z]{1,3})([$]?)([0-9]+)")
                .matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String col = m.group(2);
            int idx = 0;
            for (int i = 0; i < col.length(); i++) idx = idx * 26 + (col.charAt(i) - 'A' + 1);
            int nidx = idx + delta;
            StringBuilder nc = new StringBuilder();
            while (nidx > 0) {
                int rem = (nidx - 1) % 26;
                nc.insert(0, (char) ('A' + rem));
                nidx = (nidx - 1) / 26;
            }
            m.appendReplacement(sb, java.util.regex.Matcher.quoteReplacement(m.group(1) + nc + m.group(3) + m.group(4)));
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /** 公式内 A1 列引用匹配（SUM 等函数名后无数字，不会被匹配） */
    private static final java.util.regex.Pattern A1_REF =
            java.util.regex.Pattern.compile("(?<![A-Za-z0-9_$])([$]?)([A-Z]{1,3})([$]?)([0-9]+)");
    /** “2025年8月” 这种单月表头 */
    private static final java.util.regex.Pattern YM_HEADER =
            java.util.regex.Pattern.compile("^(\\d{4})年(\\d{1,2})月$");
    /** “2025年1-7月累计” 这种区间累计辅助列（不属于任何单月） */
    private static final java.util.regex.Pattern YM_RANGE_CUM =
            java.util.regex.Pattern.compile("^\\d{4}年\\d{1,2}-\\d{1,2}月累计$");

    /** A1 列字母 -> 0 基列号（与 POI 的 Cell.getColumnIndex() 对齐，A=0、AJ=35） */
    private int colIndexOf(String letters) {
        int idx = 0;
        for (int i = 0; i < letters.length(); i++) idx = idx * 26 + (letters.charAt(i) - 'A' + 1);
        return idx - 1;
    }

    /** 0 基列号 -> "Q" */
    private String colLettersOf(int idx0) {
        StringBuilder sb = new StringBuilder();
        int n = idx0 + 1;
        while (n > 0) {
            int rem = (n - 1) % 26;
            sb.insert(0, (char) ('A' + rem));
            n = (n - 1) / 26;
        }
        return sb.toString();
    }

    /** 表头（前 5 行）建立 (年*100+月) -> 列号（0 基）：识别 “2025年8月” 文本格与 2025-08-01 这类日期格；
     *  同一 (年,月) 取最左一列；“2025年1-7月累计” 这类区间累计辅助列不属于任何单月，不参与映射 */
    private Map<Integer, Integer> yearMonthCols(Sheet sh, int year) {
        Map<Integer, Integer> m = new LinkedHashMap<>();
        if (sh == null) return m;
        int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
        for (int r = sh.getFirstRowNum(); r <= last; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Integer ym = yearMonthOf(row.getCell(c), year);
                if (ym != null && !m.containsKey(ym)) m.put(ym, c);
            }
        }
        return m;
    }

    /**
     * 单个月份表头 -> 年*100+月；只认今年/去年，其余一律不认（避免把 2024/2023 对比块、杂格当月份列）。
     * 两种写法：文本 “2026年8月”；数值日期序列（母版城市客运页 3..8 月的表头是 46235 这种裸序列、
     * 数字格式还是 General，只按“当月 1 号”判断，不能依赖 isCellDateFormatted）。
     */
    private Integer yearMonthOf(Cell cell, int year) {
        if (cell == null) return null;
        org.apache.poi.ss.usermodel.CellType t = cell.getCellType();
        if (t == org.apache.poi.ss.usermodel.CellType.STRING) {
            String v = cell.getStringCellValue();
            if (v == null || v.isEmpty()) return null;
            v = v.trim();
            if (v.contains("累计") || v.contains("同比")) return null;
            java.util.regex.Matcher m = YM_HEADER.matcher(v);
            if (!m.matches()) return null;
            int y = Integer.parseInt(m.group(1));
            if (y != year && y != year - 1) return null;
            return y * 100 + Integer.parseInt(m.group(2));
        }
        if (t == org.apache.poi.ss.usermodel.CellType.NUMERIC) {
            double v = cell.getNumericCellValue();
            if (v < 1 || v > 200000) return null;
            try {
                java.util.Calendar cal = java.util.Calendar.getInstance();
                cal.setTime(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v));
                if (cal.get(java.util.Calendar.DAY_OF_MONTH) != 1) return null;
                int y = cal.get(java.util.Calendar.YEAR);
                if (y != year && y != year - 1) return null;
                return y * 100 + (cal.get(java.util.Calendar.MONTH) + 1);
            } catch (Exception ignore) {
                return null;
            }
        }
        return null;
    }

    /** 表头里 “20XX年a-b月累计” 这类区间累计辅助列的 0 基列号集合 */
    private java.util.Set<Integer> cumulativeHelperCols(Sheet sh) {
        java.util.Set<Integer> set = new java.util.HashSet<>();
        if (sh == null) return set;
        int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
        for (int r = sh.getFirstRowNum(); r <= last; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
                String v = cell.getStringCellValue();
                if (v != null && YM_RANGE_CUM.matcher(v.trim()).matches()) set.add(c);
            }
        }
        return set;
    }

    /** 月份映射的可用性守卫：去年与今年 1..month 各月列必须齐全且列号严格递增，
     *  任一不满足（表头没写月份、或抓到杂格）就返回空表，调用方退回「整体右移 2 列」的旧行为 */
    private Map<Integer, Integer> usableMonthCols(Sheet sh, int year, int month) {
        Map<Integer, Integer> usable = new LinkedHashMap<>();
        Map<Integer, Integer> all = yearMonthCols(sh, year);
        int prevLy = -1, prevCy = -1;
        for (int m = 1; m <= month; m++) {
            Integer ly = all.get((year - 1) * 100 + m);
            Integer cy = all.get(year * 100 + m);
            if (ly == null || cy == null || ly <= prevLy || cy <= prevCy) return new LinkedHashMap<>();
            prevLy = ly;
            prevCy = cy;
            usable.put((year - 1) * 100 + m, ly);
            usable.put(year * 100 + m, cy);
        }
        return usable;
    }

    /**
     * 「上月同比」公式 → 「本月同比」公式：公式里引用到月份列的引用按表头 (年,月) 顺延一个月，
     * 其余引用退回「整体右移 2 列」。
     *
     * 为什么不能一律右移 2 列：母版《城市客运》页在 2025 年段插了一列「2025年1-7月累计」辅助列，
     * 2025 各月列从 8 月起不再等距（2025年8月在 AK、不在 AJ），右移 2 列会落到辅助列上，
     * 于是 8 月同比被算成「8月值 ÷ 2025年1-7月累计 − 1」（2026-09-20 用户上报的问题）。
     */
    private String shiftFormulaToNextMonth(Sheet sh, String formula, int year, int month) {
        if (formula == null || formula.isEmpty()) return formula;
        Map<Integer, Integer> cols = usableMonthCols(sh, year, month);
        if (cols.isEmpty()) return shiftFormulaColumns(formula, 2);
        Map<Integer, Integer> colToYm = new LinkedHashMap<>();
        for (Map.Entry<Integer, Integer> e : cols.entrySet()) colToYm.put(e.getValue(), e.getKey());
        java.util.regex.Matcher m = A1_REF.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            int idx = colIndexOf(m.group(2));
            Integer ym = colToYm.get(idx);
            Integer next = null;
            if (ym != null) {
                next = cols.get((ym / 100) * 100 + (ym % 100) + 1);
            }
            m.appendReplacement(sb, java.util.regex.Matcher.quoteReplacement(
                    m.group(1) + colLettersOf(next != null ? next : idx + 2) + m.group(3) + m.group(4)));
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /**
     * 修正「累计同比」分母。母版《城市客运》页把 2025 年 1..M 月各月列**和**它们的
     * 「2025年1-M月累计」辅助列一起写进分母（1..M-1 月重复计一次、当月漏计），
     * 另有在母版里删掉辅助列后残留 #REF! 的情形；两种都会让累计同比严重失真
     * （2026-09-20 用户上报：城市客运 8 月同比 −86.5%、累计同比 −43.5%）。
     * 统一按表头把分母重写为「去年 1..当月 各月值列之和」。
     *
     * 2026-09-20 已在《2026年8月道路运输量汇总表.xlsx》母版里删掉该辅助列（备份见
     * docs/生成汇总大表/_bak_202608母版删除AJ列前_20260920.xlsx），正常情况本方法命中 0 格；
     * 保留它是为了兜住「同期备份母版 / 历史部署包 / 之后又插了辅助列的母版」这类情况。
     */
    public int repairCumulativeYoyFormulas(XSSFWorkbook wb, int year, int month) {
        if (wb == null || month < 1) return 0;
        int fixed = 0;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sh = wb.getSheetAt(i);
            if (sh == null || !isMonthLedgerSheet(sh.getSheetName())) continue;
            int[] cum = cumulativeCols(sh, year);
            if (cum == null) continue;
            java.util.Set<Integer> helperCols = cumulativeHelperCols(sh);
            Map<Integer, Integer> all = yearMonthCols(sh, year);
            List<Integer> lastYearValueCols = new ArrayList<>();
            int prev = -1;
            for (int m = 1; m <= month; m++) {
                Integer c = all.get((year - 1) * 100 + m);
                if (c == null || c <= prev) {
                    lastYearValueCols.clear();
                    break;
                }
                prev = c;
                lastYearValueCols.add(c);
            }
                if (lastYearValueCols.isEmpty()) continue;
                for (int r = sh.getFirstRowNum(); r <= sh.getLastRowNum(); r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                Cell cc = row.getCell(cum[1]);
                if (cc == null || cc.getCellType() != org.apache.poi.ss.usermodel.CellType.FORMULA) continue;
                String f = cc.getCellFormula();
                if (f == null || !cumulativeYoyBroken(f, helperCols)) continue;
                // 注意：POI 的 setCellFormula 不接受以 "=" 开头的公式串（会抛 FormulaParseException）
                StringBuilder sb = new StringBuilder();
                sb.append(cellRef(cum[0], r + 1)).append("/(");
                for (int k = 0; k < lastYearValueCols.size(); k++) {
                    if (k > 0) sb.append('+');
                    sb.append(cellRef(lastYearValueCols.get(k), r + 1));
                }
                sb.append(")-1");
                try {
                    cc.setCellFormula(sb.toString());
                    fixed++;
                } catch (Exception ex) {
                    // 个别公式改写失败不阻塞导出（打开时仍按原公式重算）
                    if (log != null) log.warn("累计同比公式改写失败：sheet={} cell={} 原式={} 新式={}（{}）",
                            sh.getSheetName(), cellRef(cum[1], r + 1), f, sb, ex.toString());
                }
            }
        }
        if (fixed > 0 && log != null) {
            log.info("汇总工作簿：累计同比分母修正 {} 格（第 {} 月，分母取去年 1..{} 月各月值列）", fixed, month, month);
        }
        return fixed;
    }

    /** 累计同比公式是否“坏了”：含 #REF!，或分母引用了区间累计辅助列（与各月列重复计入） */
    private boolean cumulativeYoyBroken(String formula, java.util.Set<Integer> helperCols) {
        if (formula.contains("#REF!")) return true;
        java.util.regex.Matcher m = A1_REF.matcher(formula);
        while (m.find()) {
            if (helperCols.contains(colIndexOf(m.group(2)))) return true;
        }
        return false;
    }

    /** 该页「累计 / 累计同比」两列（0 基），按表头前 5 行找 “累计” 或 “{year}年累计”，且其右列含 “同比” */
    private int[] cumulativeCols(Sheet sh, int year) {
        int last = Math.min(sh.getLastRowNum(), sh.getFirstRowNum() + 4);
        for (int r = sh.getFirstRowNum(); r <= last; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = row.getFirstCellNum(); c >= 0 && c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
                String v = cell.getStringCellValue();
                if (v == null) continue;
                v = v.trim();
                if (!"累计".equals(v) && !(year + "年累计").equals(v)) continue;
                Cell nxt = row.getCell(c + 1);
                if (nxt == null || nxt.getCellType() != org.apache.poi.ss.usermodel.CellType.STRING) continue;
                String nt = nxt.getStringCellValue();
                if (nt != null && nt.contains("同比")) return new int[]{c, c + 1};
            }
        }
        return null;
    }

    /** 月份容量：优先取表头（前 5 行）“2026…累计”列反推；货运页无该文案，
     *  再用表头里最后一个“2026年M月”/日期格式的月列兜底（如货运 Q3=2026-08-01 -> 8） */
    private int monthColumnCapacity(Sheet sh) {
        int byCumulative = 0;
        int byMonthHeader = 0;
        for (int r = 0; r <= 4 && r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (Cell c : row) {
                if (c == null) continue;
                int idx = c.getColumnIndex();
                if (idx <= FIRST_MONTH_COL || (idx - FIRST_MONTH_COL) % 2 != 0) continue; // 只看“当月值”列
                String t = text(c);
                if (byCumulative == 0 && t != null && t.contains("2026") && t.contains("累计")) {
                    byCumulative = (idx - FIRST_MONTH_COL) / 2;
                }
                if (t != null && t.contains("2026") && t.contains("月") && !t.contains("累计")
                        && !t.contains("同比")) {
                    byMonthHeader = Math.max(byMonthHeader, (idx - FIRST_MONTH_COL) / 2 + 1);
                } else if (c.getCellType() == org.apache.poi.ss.usermodel.CellType.NUMERIC
                        && org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(c)) {
                    byMonthHeader = Math.max(byMonthHeader, (idx - FIRST_MONTH_COL) / 2 + 1);
                }
            }
        }
        int cap = Math.max(byCumulative, byMonthHeader);
        return cap > 0 ? cap : 7;
    }

    private String text(Cell c) {
        if (c == null) return null;
        switch (c.getCellType()) {
            case STRING: return c.getStringCellValue();
            case NUMERIC: return Double.toString(c.getNumericCellValue());
            default: return null;
        }
    }
}