package com.trafficaudit.reportexport.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.trafficaudit.common.util.RegionUtil;
import com.trafficaudit.dataimport.entity.FreightTurnoverImport;
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 报表生成服务：3 类输出报表
 * 货运量口径：合计=模板_货运量周转量（左半今年1-N月，右半去年1-12月）；规上=H203-2按17市州汇总；规下=合计-规上
 * 周转量口径：全部来自规上规下拆分表（用户手动上传，含忠实导入的全省行）
 */
@Slf4j
@Service
public class ReportExportService {

    @Resource
    private H2032EnterpriseMonthlyMapper h2032Mapper;
    @Resource
    private ScaleSplitTransportMapper scaleSplitMapper;
    @Resource
    private FreightTurnoverImportMapper freightTurnoverMapper;

    // ==================== 1. 生成_货运量分市州明细.xlsx ====================

    public byte[] exportCityDetail(String period) throws Exception {
        int currentMonth = parseMonth(period);
        String year = period.substring(0, 4);
        Map<String, Map<Integer, ScaleSplitTransport>> monthData = loadMonthData(period);
        Map<Integer, ScaleSplitTransport> provinceMonthMap = loadProvinceMonthMap(period);
        ScaleSplitTransport provinceCum = getProvinceCumulative(period);
        Map<String, ScaleSplitTransport> cumMap = loadCumulativeMap(period);
        Map<String, Map<Integer, Double>> h2032Freight = loadH2032FreightByMonth(period);
        Map<String, Double> h2032FreightCum = loadH2032FreightCumulative(period);
        Map<String, FreightTurnoverImport> ftMap = loadFreightTurnover(period);

        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("货运量分市州明细");

        Row r1 = sheet.createRow(0);
        r1.createCell(0).setCellValue("报表期：" + year + "年" + currentMonth + "月");
        sheet.createRow(1);
        Row header = sheet.createRow(2);
        header.createCell(0).setCellValue("地区 名称");
        header.createCell(1).setCellValue("指标");
        CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy-mm-dd"));
        for (int m = 1; m <= currentMonth; m++) {
            Cell c = header.createCell(2 + (m - 1) * 2);
            c.setCellValue(java.sql.Date.valueOf(year + "-" + pad(m) + "-01"));
            c.setCellStyle(dateStyle);
            header.createCell(3 + (m - 1) * 2).setCellValue(m + "月与去年同比");
        }
        int cumCol = 2 + currentMonth * 2;
        header.createCell(cumCol).setCellValue("累计周转量");
        header.createCell(cumCol + 1).setCellValue("同比");
        sheet.createRow(3);

        int rowIdx = 4;
        String[][] provinceMetrics = {
            {"货运量         （万吨）", "freight", "total"},
            {"货物周转量     （万吨公里）", "turnover", "total"},
            {"其中规上货运量   (万吨)", "freight", "above"},
            {"其中规上货物周转量  (万吨公里)", "turnover", "above"},
            {"其中规下货运量   (万吨)", "freight", "below"},
            {"其中规下货物周转量  (万吨公里)", "turnover", "below"}
        };
        FreightTurnoverImport ftProvince = ftMap.get("湖北省");
        for (String[] metric : provinceMetrics) {
            Row row = sheet.createRow(rowIdx++);
            row.createCell(0).setCellValue("全省");
            row.createCell(1).setCellValue(metric[0]);
            for (int m = 1; m <= currentMonth; m++) {
                setNumeric(row, 2 + (m - 1) * 2, getProvinceMonthValue(provinceMonthMap, ftProvince, m, metric[1], metric[2], h2032Freight));
                setNumeric(row, 3 + (m - 1) * 2, getProvinceMonthYoy(provinceMonthMap, ftProvince, m, metric[1], metric[2]));
            }
            setNumeric(row, cumCol, getProvinceCumValue(provinceCum, ftProvince, metric[1], metric[2], h2032FreightCum, currentMonth));
            setNumeric(row, cumCol + 1, getProvinceCumYoy(provinceCum, ftProvince, metric[1], metric[2], currentMonth));
        }

        for (String city : RegionUtil.cityList()) {
            String[][] metrics = {
                {"规上+规下货运量(万吨)", "freight", "total"},
                {"规上货运量      (万吨)", "freight", "above"},
                {"规下货运量      (万吨)", "freight", "below"},
                {"规上+规下周转量  (万吨公里)", "turnover", "total"},
                {"规上货物周转量  (万吨公里)", "turnover", "above"},
                {"规下货物周转量  (万吨公里)", "turnover", "below"}
            };
            FreightTurnoverImport ftCity = ftMap.get(city);
            ScaleSplitTransport cum = cumMap.get(city);
            for (String[] metric : metrics) {
                Row row = sheet.createRow(rowIdx++);
                row.createCell(0).setCellValue(RegionUtil.shortName(city));
                row.createCell(1).setCellValue(metric[0]);
                for (int m = 1; m <= currentMonth; m++) {
                    setNumeric(row, 2 + (m - 1) * 2, getCityMonthValue(monthData, ftCity, city, m, metric[1], metric[2], h2032Freight));
                    setNumeric(row, 3 + (m - 1) * 2, getCityMonthYoy(monthData, ftCity, city, m, metric[1], metric[2]));
                }
                setNumeric(row, cumCol, getCityCumValue(cum, ftCity, metric[1], metric[2], h2032FreightCum, city, currentMonth));
                setNumeric(row, cumCol + 1, getCityCumYoy(cum, ftCity, metric[1], metric[2], currentMonth));
            }
        }

        autoWidth(sheet, cumCol + 2);
        return toBytes(wb);
    }
    // ==================== 2. 生成_货运量排名.xlsx ====================

    public byte[] exportFreightRank(String period) throws Exception {
        String year = period.substring(0, 4);
        int month = parseMonth(period);
        Map<String, FreightTurnoverImport> ftMap = loadFreightTurnover(period);
        Map<String, Double> h2032FreightCum = loadH2032FreightCumulative(period);
        Map<String, Double> h2032FreightYoy = computeH2032Yoy(period);

        Map<String, Double> above = new HashMap<>();
        Map<String, Double> below = new HashMap<>();
        Map<String, Double> total = new HashMap<>();
        Map<String, Double> aboveYoy = new HashMap<>();
        Map<String, Double> belowYoy = new HashMap<>();
        Map<String, Double> totalYoy = new HashMap<>();

        double provAbove = 0.0;
        for (Double v : h2032FreightCum.values()) {
            if (v != null) provAbove += v;
        }
        Double provAboveWan = round(provAbove / 10000.0, 4);
        Double provTotal = freightCum(ftMap.get("湖北省"), month);
        Double provBelow = provTotal == null ? null : round(provTotal - provAboveWan, 4);
        Double provTotalYoy = freightCumYoy(ftMap.get("湖北省"), month);

        for (String city : RegionUtil.cityList()) {
            Double a = h2032FreightCum.get(city);
            double aWan = a == null ? 0.0 : round(a / 10000.0, 4);
            Double t = freightCum(ftMap.get(city), month);
            above.put(city, aWan);
            total.put(city, t);
            below.put(city, t == null ? null : round(t - aWan, 4));
            aboveYoy.put(city, h2032FreightYoy.get(city));
            belowYoy.put(city, null);
            totalYoy.put(city, freightCumYoy(ftMap.get(city), month));
        }

        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("货运量排名");

        Row title = sheet.createRow(0);
        title.createCell(0).setCellValue(year + "年1-" + month + "月全省分市州累计完成公路货运量情况");

        int rowIdx = 1;
        rowIdx = writeRankHead(sheet, rowIdx, "货运量", "吨");
        rowIdx = writeFreightRankRows(sheet, rowIdx, null, above, below, total, aboveYoy, belowYoy, totalYoy,
            provAboveWan, provBelow, provTotal, null, null, provTotalYoy, true);
        for (String city : RegionUtil.cityList()) {
            rowIdx = writeFreightRankRows(sheet, rowIdx, city, above, below, total, aboveYoy, belowYoy, totalYoy,
                provAboveWan, provBelow, provTotal, null, null, provTotalYoy, false);
        }

        // 本月区块（当月标题 + 累计数据，与样例结构一致）
        sheet.createRow(rowIdx++);
        Row title2 = sheet.createRow(rowIdx++);
        title2.createCell(0).setCellValue(year + "年" + month + "月全省分市州累计完成" + "公路货运量情况");
        rowIdx = writeRankHead(sheet, rowIdx, "货运量", "吨");
        rowIdx = writeFreightRankRows(sheet, rowIdx, null, above, below, total, aboveYoy, belowYoy, totalYoy,
            provAboveWan, provBelow, provTotal, null, null, provTotalYoy, true);
        for (String city : RegionUtil.cityList()) {
            rowIdx = writeFreightRankRows(sheet, rowIdx, city, above, below, total, aboveYoy, belowYoy, totalYoy,
                provAboveWan, provBelow, provTotal, null, null, provTotalYoy, false);
        }

        // 末尾空行（与样例结构对齐）
        for (int i = 0; i < 4; i++) sheet.createRow(rowIdx++);

        autoWidth(sheet, 18);
        return toBytes(wb);
    }

    // ==================== 3. 生成_周转量排名.xlsx ====================

    public byte[] exportTurnoverRank(String period) throws Exception {
        String year = period.substring(0, 4);
        int month = parseMonth(period);
        Map<String, ScaleSplitTransport> cumMap = loadCumulativeMap(period);
        ScaleSplitTransport province = getProvinceCumulative(period);

        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("周转量排名");

        Row title = sheet.createRow(0);
        title.createCell(0).setCellValue(year + "年1-" + month + "月全省分市州累计完成公路货物运输周转量情况");

        int rowIdx = 1;
        rowIdx = writeRankHead(sheet, rowIdx, "周转量", "吨公里");
        rowIdx = writeTurnoverRankRows(sheet, rowIdx, null, cumMap, province, true);
        for (String city : RegionUtil.cityList()) {
            rowIdx = writeTurnoverRankRows(sheet, rowIdx, city, cumMap, province, false);
        }

        // 本月区块（当月标题 + 累计数据，与样例结构一致）
        sheet.createRow(rowIdx++);
        Row title2 = sheet.createRow(rowIdx++);
        title2.createCell(0).setCellValue(year + "年" + month + "月全省分市州累计完成" + "公路货物运输周转量情况");
        rowIdx = writeRankHead(sheet, rowIdx, "周转量", "吨公里");
        rowIdx = writeTurnoverRankRows(sheet, rowIdx, null, cumMap, province, true);
        for (String city : RegionUtil.cityList()) {
            rowIdx = writeTurnoverRankRows(sheet, rowIdx, city, cumMap, province, false);
        }

        // 末尾空行（与样例结构对齐）
        for (int i = 0; i < 4; i++) sheet.createRow(rowIdx++);

        autoWidth(sheet, 18);
        return toBytes(wb);
    }
    // ==================== 数据加载 ====================

    /** 当月数据: city -> month -> record（不含全省） */
    private Map<String, Map<Integer, ScaleSplitTransport>> loadMonthData(String period) {
        Map<String, Map<Integer, ScaleSplitTransport>> result = new HashMap<>();
        List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
            new LambdaQueryWrapper<ScaleSplitTransport>()
                .eq(ScaleSplitTransport::getPeriodType, "MONTH"));
        for (ScaleSplitTransport record : list) {
            if (record.getReportPeriod() == null) continue;
            if (record.getReportPeriod().compareTo(period) > 0) continue;
            String city = RegionUtil.normalizeCityName(record.getRegionName());
            if (city == null) continue;
            if ("湖北省".equals(city)) continue;
            int month = parseMonth(record.getReportPeriod());
            result.computeIfAbsent(city, k -> new HashMap<>()).put(month, record);
        }
        return result;
    }

    /** 累计数据: city -> 目标期及之前最近一期（不含全省） */
    private Map<String, ScaleSplitTransport> loadCumulativeMap(String period) {
        Map<String, ScaleSplitTransport> result = new HashMap<>();
        List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
            new LambdaQueryWrapper<ScaleSplitTransport>()
                .eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE"));
        for (ScaleSplitTransport record : list) {
            if (record.getReportPeriod() == null) continue;
            if (record.getReportPeriod().compareTo(period) > 0) continue;
            String city = RegionUtil.normalizeCityName(record.getRegionName());
            if (city == null) continue;
            if ("湖北省".equals(city)) continue;
            ScaleSplitTransport existing = result.get(city);
            if (existing == null || record.getReportPeriod().compareTo(existing.getReportPeriod()) > 0) {
                result.put(city, record);
            }
        }
        return result;
    }

    /** 全省当月: month -> record（忠实读取导入的全省行） */
    private Map<Integer, ScaleSplitTransport> loadProvinceMonthMap(String period) {
        Map<Integer, ScaleSplitTransport> result = new HashMap<>();
        List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
            new LambdaQueryWrapper<ScaleSplitTransport>()
                .eq(ScaleSplitTransport::getPeriodType, "MONTH"));
        for (ScaleSplitTransport record : list) {
            if (record.getReportPeriod() == null) continue;
            if (record.getReportPeriod().compareTo(period) > 0) continue;
            String name = RegionUtil.normalizeCityName(record.getRegionName());
            if (!"湖北省".equals(name)) continue;
            int month = parseMonth(record.getReportPeriod());
            result.put(month, record);
        }
        return result;
    }

    /** 全省累计（忠实读取导入的全省行，不求和） */
    private ScaleSplitTransport getProvinceCumulative(String period) {
        List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
            new LambdaQueryWrapper<ScaleSplitTransport>()
                .eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE"));
        ScaleSplitTransport best = null;
        for (ScaleSplitTransport record : list) {
            if (record.getReportPeriod() == null) continue;
            if (record.getReportPeriod().compareTo(period) > 0) continue;
            String name = RegionUtil.normalizeCityName(record.getRegionName());
            if (!"湖北省".equals(name)) continue;
            if (best == null || record.getReportPeriod().compareTo(best.getReportPeriod()) > 0) best = record;
        }
        return best;
    }

    /** H2032规上: city -> month -> 货运量(吨) */
    private Map<String, Map<Integer, Double>> loadH2032FreightByMonth(String period) {
        Map<String, Map<Integer, Double>> result = new HashMap<>();
        List<H2032EnterpriseMonthly> list = h2032Mapper.selectList(null);
        for (H2032EnterpriseMonthly record : list) {
            if (record.getReportPeriod() == null || record.getReportPeriod().compareTo(period) > 0) continue;
            String city = RegionUtil.cityByCode(record.getRegionCode());
            if (city == null) continue;
            int month = parseMonth(record.getReportPeriod());
            double freight = record.getFreightTotal() == null ? 0.0 : record.getFreightTotal();
            result.computeIfAbsent(city, k -> new HashMap<>()).merge(month, freight, Double::sum);
        }
        return result;
    }

    /** H2032规上累计: city -> 货运量(吨) */
    private Map<String, Double> loadH2032FreightCumulative(String period) {
        Map<String, Double> result = new HashMap<>();
        List<H2032EnterpriseMonthly> list = h2032Mapper.selectList(null);
        for (H2032EnterpriseMonthly record : list) {
            if (record.getReportPeriod() == null || record.getReportPeriod().compareTo(period) > 0) continue;
            String city = RegionUtil.cityByCode(record.getRegionCode());
            if (city == null) continue;
            double freight = record.getFreightTotal() == null ? 0.0 : record.getFreightTotal();
            result.merge(city, freight, Double::sum);
        }
        return result;
    }

    /** H2032规上同比（需去年同期的H2032数据，暂无则为空） */
    private Map<String, Double> computeH2032Yoy(String period) {
        Map<String, Double> result = new HashMap<>();
        String lastYear = (Integer.parseInt(period.substring(0, 4)) - 1) + period.substring(4);
        Map<String, Double> current = loadH2032FreightCumulative(period);
        Map<String, Double> last = loadH2032FreightCumulative(lastYear);
        for (Map.Entry<String, Double> entry : current.entrySet()) {
            double lastVal = last.getOrDefault(entry.getKey(), 0.0);
            if (lastVal != 0.0) {
                result.put(entry.getKey(), (entry.getValue() - lastVal) / lastVal);
            }
        }
        return result;
    }

    /** 模板_货运量周转量: city -> 行数据（含湖北省） */
    private Map<String, FreightTurnoverImport> loadFreightTurnover(String period) {
        Map<String, FreightTurnoverImport> result = new HashMap<>();
        List<FreightTurnoverImport> list = freightTurnoverMapper.selectList(
            new LambdaQueryWrapper<FreightTurnoverImport>()
                .eq(FreightTurnoverImport::getReportPeriod, period));
        for (FreightTurnoverImport e : list) {
            String city = RegionUtil.normalizeCityName(e.getRegionName());
            if (city != null) result.put(city, e);
        }
        return result;
    }
    // ==================== 排名行写入 ====================

    /** 排名表双行表头 */
    private int writeRankHead(Sheet sheet, int rowIdx, String metric, String unit) {
        Row h1 = sheet.createRow(rowIdx++);
        h1.createCell(0).setCellValue("市州");
        h1.createCell(1).setCellValue("规上" + metric);
        h1.createCell(4).setCellValue("规上增速");
        h1.createCell(6).setCellValue("规下" + metric);
        h1.createCell(9).setCellValue("规下增速");
        h1.createCell(11).setCellValue("合计" + metric);
        h1.createCell(14).setCellValue("合计增速");
        h1.createCell(16).setCellValue("分市州规上规下占比");

        Row h2 = sheet.createRow(rowIdx++);
        h2.createCell(1).setCellValue("累计完成    (万" + unit + "）");
        h2.createCell(2).setCellValue("排名");
        h2.createCell(3).setCellValue("占全省比重");
        h2.createCell(4).setCellValue("同比");
        h2.createCell(5).setCellValue("增速    排名");
        h2.createCell(6).setCellValue("累计完成    (万" + unit + "）");
        h2.createCell(7).setCellValue("排名");
        h2.createCell(8).setCellValue("占全省比重");
        h2.createCell(9).setCellValue("同比");
        h2.createCell(10).setCellValue("增速排名");
        h2.createCell(11).setCellValue("累计完成    (万" + unit + "）");
        h2.createCell(12).setCellValue("排名");
        h2.createCell(13).setCellValue("占全省比重");
        h2.createCell(14).setCellValue("同比");
        h2.createCell(15).setCellValue("增速排名");
        return rowIdx;
    }

    /** 货运量排名行：规上=H2032累计、合计=模板累计、规下=合计-规上（单位：万吨） */
    private int writeFreightRankRows(Sheet sheet, int rowIdx, String city,
                                     Map<String, Double> above, Map<String, Double> below, Map<String, Double> total,
                                     Map<String, Double> aboveYoy, Map<String, Double> belowYoy, Map<String, Double> totalYoy,
                                     Double provAbove, Double provBelow, Double provTotal,
                                     Double provAboveYoy, Double provBelowYoy, Double provTotalYoy,
                                     boolean isProvince) {
        Row row = sheet.createRow(rowIdx);
        row.createCell(0).setCellValue(isProvince ? "全省" : RegionUtil.shortName(city));

        Double a = isProvince ? provAbove : above.get(city);
        Double b = isProvince ? provBelow : below.get(city);
        Double t = isProvince ? provTotal : total.get(city);
        Double ay = isProvince ? provAboveYoy : aboveYoy.get(city);
        Double by = isProvince ? provBelowYoy : belowYoy.get(city);
        Double ty = isProvince ? provTotalYoy : totalYoy.get(city);

        setNumeric(row, 1, a);
        setNumeric(row, 2, isProvince ? null : rankOfMap(above, city));
        setNumeric(row, 3, isProvince ? null : ratioOf(a, provAbove));
        setNumeric(row, 4, ay);
        setNumeric(row, 5, isProvince ? null : yoyRankOfMap(aboveYoy, city));

        setNumeric(row, 6, b);
        setNumeric(row, 7, isProvince ? null : rankOfMap(below, city));
        setNumeric(row, 8, isProvince ? null : ratioOf(b, provBelow));
        setNumeric(row, 9, by);
        setNumeric(row, 10, isProvince ? null : yoyRankOfMap(belowYoy, city));

        setNumeric(row, 11, t);
        setNumeric(row, 12, isProvince ? null : rankOfMap(total, city));
        setNumeric(row, 13, isProvince ? null : ratioOf(t, provTotal));
        setNumeric(row, 14, ty);
        setNumeric(row, 15, isProvince ? null : yoyRankOfMap(totalYoy, city));

        if (t != null && t > 0 && a != null) {
            double share = round(a * 10.0 / t, 1);
            setNumeric(row, 16, share);
            setNumeric(row, 17, round(10.0 - share, 1));
        }
        return rowIdx + 1;
    }

    private int writeTurnoverRankRows(Sheet sheet, int rowIdx, String city,
                                      Map<String, ScaleSplitTransport> cumMap,
                                      ScaleSplitTransport province,
                                      boolean isProvince) {
        if (province == null) province = new ScaleSplitTransport();
        Row row = sheet.createRow(rowIdx);
        row.createCell(0).setCellValue(isProvince ? "全省" : RegionUtil.shortName(city));

        ScaleSplitTransport cum = isProvince ? null : cumMap.get(city);
        Double above = isProvince ? province.getAboveScaleTurnover() : (cum == null ? null : cum.getAboveScaleTurnover());
        Double below = isProvince ? province.getBelowScaleTurnover() : (cum == null ? null : cum.getBelowScaleTurnover());
        Double total = isProvince ? province.getTotalTurnover() : (cum == null ? null : cum.getTotalTurnover());

        Double provinceAbove = province.getAboveScaleTurnover();
        Double provinceBelow = province.getBelowScaleTurnover();
        Double provinceTotal = province.getTotalTurnover();

        setNumeric(row, 1, above);
        setNumeric(row, 2, isProvince || cum == null ? null : rankOfTurnover(cumMap, city, 0));
        setNumeric(row, 3, isProvince ? null : ratioOf(above, provinceAbove));
        setNumeric(row, 4, isProvince ? province.getAboveScaleYoy() : (cum == null ? null : cum.getAboveScaleYoy()));
        setNumeric(row, 5, isProvince || cum == null ? null : yoyRankOfTurnover(cumMap, city, 0));

        setNumeric(row, 6, below);
        setNumeric(row, 7, isProvince || cum == null ? null : rankOfTurnover(cumMap, city, 1));
        setNumeric(row, 8, isProvince ? null : ratioOf(below, provinceBelow));
        setNumeric(row, 9, isProvince ? province.getBelowScaleYoy() : (cum == null ? null : cum.getBelowScaleYoy()));
        setNumeric(row, 10, isProvince || cum == null ? null : yoyRankOfTurnover(cumMap, city, 1));

        setNumeric(row, 11, total);
        setNumeric(row, 12, isProvince || cum == null ? null : rankOfTurnover(cumMap, city, 2));
        setNumeric(row, 13, isProvince ? null : ratioOf(total, provinceTotal));
        setNumeric(row, 14, isProvince ? province.getTotalYoy() : (cum == null ? null : cum.getTotalYoy()));
        setNumeric(row, 15, isProvince || cum == null ? null : yoyRankOfTurnover(cumMap, city, 2));

        if (total != null && total > 0 && above != null) {
            double share = round(above * 10.0 / total, 1);
            setNumeric(row, 16, share);
            setNumeric(row, 17, round(10.0 - share, 1));
        }
        return rowIdx + 1;
    }
    // ==================== 明细取值辅助 ====================

    /** 全省当月值：周转量取拆分表，货运量取模板（合计）/H2032（规上）/差值（规下） */
    private Double getProvinceMonthValue(Map<Integer, ScaleSplitTransport> provinceMonthMap,
                                         FreightTurnoverImport ft, int month, String metric, String scale,
                                         Map<String, Map<Integer, Double>> h2032Freight) {
        if ("turnover".equals(metric)) {
            return getMetric(provinceMonthMap.get(month), metric, scale);
        }
        if ("total".equals(scale)) return freightMonth(ft, month);
        if ("above".equals(scale)) {
            double sum = 0.0;
            for (Map<Integer, Double> byMonth : h2032Freight.values()) {
                Double v = byMonth == null ? null : byMonth.get(month);
                if (v != null) sum += v;
            }
            return round(sum / 10000.0, 4);
        }
        Double total = freightMonth(ft, month);
        Double above = getProvinceMonthValue(provinceMonthMap, ft, month, "freight", "above", h2032Freight);
        if (total == null) return null;
        return total - above;
    }

    /** 全省当月同比：周转量取拆分表，货运量合计取模板（左半vs右半） */
    private Double getProvinceMonthYoy(Map<Integer, ScaleSplitTransport> provinceMonthMap,
                                       FreightTurnoverImport ft, int month, String metric, String scale) {
        if ("turnover".equals(metric)) {
            return getYoyMetric(provinceMonthMap.get(month), metric, scale);
        }
        if ("total".equals(scale)) return freightYoy(ft, month);
        return null;
    }

    /** 全省累计：周转量取拆分表累计，货运量合计=模板1-N月之和，规上=H2032累计，规下=差值 */
    private Double getProvinceCumValue(ScaleSplitTransport provinceCum, FreightTurnoverImport ft,
                                       String metric, String scale, Map<String, Double> h2032FreightCum,
                                       int monthCount) {
        if ("turnover".equals(metric)) {
            return getMetric(provinceCum, metric, scale);
        }
        if ("total".equals(scale)) return freightCum(ft, monthCount);
        if ("above".equals(scale)) {
            double sum = 0.0;
            for (Double v : h2032FreightCum.values()) {
                if (v != null) sum += v;
            }
            return round(sum / 10000.0, 4);
        }
        Double total = freightCum(ft, monthCount);
        Double above = getProvinceCumValue(provinceCum, ft, "freight", "above", h2032FreightCum, monthCount);
        if (total == null) return null;
        return total - above;
    }

    /** 全省累计同比：周转量取拆分表，货运量合计=模板累计同比 */
    private Double getProvinceCumYoy(ScaleSplitTransport provinceCum, FreightTurnoverImport ft,
                                     String metric, String scale, int monthCount) {
        if ("turnover".equals(metric)) {
            return getYoyMetric(provinceCum, metric, scale);
        }
        if ("total".equals(scale)) return freightCumYoy(ft, monthCount);
        return null;
    }

    /** 市州当月值 */
    private Double getCityMonthValue(Map<String, Map<Integer, ScaleSplitTransport>> monthData,
                                     FreightTurnoverImport ft, String city, int month,
                                     String metric, String scale,
                                     Map<String, Map<Integer, Double>> h2032Freight) {
        if ("turnover".equals(metric)) {
            return getMetric(getRecord(monthData, city, month), metric, scale);
        }
        if ("total".equals(scale)) return freightMonth(ft, month);
        if ("above".equals(scale)) {
            Map<Integer, Double> byMonth = h2032Freight.get(city);
            Double v = byMonth == null ? null : byMonth.get(month);
            return v == null ? 0.0 : round(v / 10000.0, 4);
        }
        Double total = freightMonth(ft, month);
        Double above = getCityMonthValue(monthData, ft, city, month, "freight", "above", h2032Freight);
        if (total == null) return null;
        return total - above;
    }

    /** 市州当月同比 */
    private Double getCityMonthYoy(Map<String, Map<Integer, ScaleSplitTransport>> monthData,
                                   FreightTurnoverImport ft, String city, int month,
                                   String metric, String scale) {
        if ("turnover".equals(metric)) {
            return getYoyMetric(getRecord(monthData, city, month), metric, scale);
        }
        if ("total".equals(scale)) return freightYoy(ft, month);
        return null;
    }

    /** 市州累计 */
    private Double getCityCumValue(ScaleSplitTransport cum, FreightTurnoverImport ft,
                                   String metric, String scale, Map<String, Double> h2032FreightCum,
                                   String city, int monthCount) {
        if ("turnover".equals(metric)) {
            return cum == null ? null : getMetric(cum, metric, scale);
        }
        if ("total".equals(scale)) return freightCum(ft, monthCount);
        if ("above".equals(scale)) {
            Double v = h2032FreightCum.get(city);
            return v == null ? 0.0 : round(v / 10000.0, 4);
        }
        Double total = freightCum(ft, monthCount);
        Double above = getCityCumValue(cum, ft, "freight", "above", h2032FreightCum, city, monthCount);
        if (total == null) return null;
        return total - above;
    }

    /** 市州累计同比 */
    private Double getCityCumYoy(ScaleSplitTransport cum, FreightTurnoverImport ft,
                                 String metric, String scale, int monthCount) {
        if ("turnover".equals(metric)) {
            return cum == null ? null : getYoyMetric(cum, metric, scale);
        }
        if ("total".equals(scale)) return freightCumYoy(ft, monthCount);
        return null;
    }
    // ==================== 模板_货运量周转量取值 ====================

    private Double freightMonth(FreightTurnoverImport e, int m) {
        if (e == null) return null;
        switch (m) {
            case 1: return e.getFreightM01();
            case 2: return e.getFreightM02();
            case 3: return e.getFreightM03();
            case 4: return e.getFreightM04();
            case 5: return e.getFreightM05();
            case 6: return e.getFreightM06();
            case 7: return e.getFreightM07();
            case 8: return e.getFreightM08();
            case 9: return e.getFreightM09();
            case 10: return e.getFreightM10();
            case 11: return e.getFreightM11();
            case 12: return e.getFreightM12();
            default: return null;
        }
    }

    private Double lastFreightMonth(FreightTurnoverImport e, int m) {
        if (e == null) return null;
        switch (m) {
            case 1: return e.getLastFreightM01();
            case 2: return e.getLastFreightM02();
            case 3: return e.getLastFreightM03();
            case 4: return e.getLastFreightM04();
            case 5: return e.getLastFreightM05();
            case 6: return e.getLastFreightM06();
            case 7: return e.getLastFreightM07();
            case 8: return e.getLastFreightM08();
            case 9: return e.getLastFreightM09();
            case 10: return e.getLastFreightM10();
            case 11: return e.getLastFreightM11();
            case 12: return e.getLastFreightM12();
            default: return null;
        }
    }

    /** 模板货运量当月同比 = (今年m - 去年m) / 去年m */
    private Double freightYoy(FreightTurnoverImport e, int m) {
        Double cur = freightMonth(e, m);
        Double last = lastFreightMonth(e, m);
        if (cur == null || last == null || last == 0.0) return null;
        return (cur - last) / last;
    }

    /** 模板货运量累计 = 左半 1..monthCount 之和 */
    private Double freightCum(FreightTurnoverImport e, int monthCount) {
        if (e == null) return null;
        double sum = 0.0;
        boolean any = false;
        for (int m = 1; m <= monthCount; m++) {
            Double v = freightMonth(e, m);
            if (v != null) {
                sum += v;
                any = true;
            }
        }
        return any ? sum : null;
    }

    /** 模板去年货运量累计 = 右半 1..monthCount 之和（去年1-12月固定，取前N个月） */
    private Double lastFreightCum(FreightTurnoverImport e, int monthCount) {
        if (e == null) return null;
        double sum = 0.0;
        boolean any = false;
        for (int m = 1; m <= monthCount; m++) {
            Double v = lastFreightMonth(e, m);
            if (v != null) {
                sum += v;
                any = true;
            }
        }
        return any ? sum : null;
    }

    /** 模板货运量累计同比 = (今年1-N累计 - 去年1-N累计) / 去年1-N累计 */
    private Double freightCumYoy(FreightTurnoverImport e, int monthCount) {
        Double cur = freightCum(e, monthCount);
        Double last = lastFreightCum(e, monthCount);
        if (cur == null || last == null || last == 0.0) return null;
        return (cur - last) / last;
    }
    // ==================== 拆分表取值辅助 ====================

    private Double getMetric(ScaleSplitTransport record, String metric, String scale) {
        if (record == null) return null;
        if ("freight".equals(metric)) {
            if ("above".equals(scale)) return record.getAboveScaleFreight();
            if ("below".equals(scale)) return record.getBelowScaleFreight();
            return record.getTotalFreight();
        } else {
            if ("above".equals(scale)) return record.getAboveScaleTurnover();
            if ("below".equals(scale)) return record.getBelowScaleTurnover();
            return record.getTotalTurnover();
        }
    }

    private Double getYoyMetric(ScaleSplitTransport record, String metric, String scale) {
        if (record == null) return null;
        if ("above".equals(scale)) return record.getAboveScaleYoy();
        if ("below".equals(scale)) return record.getBelowScaleYoy();
        return record.getTotalYoy();
    }

    private ScaleSplitTransport getRecord(Map<String, Map<Integer, ScaleSplitTransport>> monthData,
                                          String city, int month) {
        Map<Integer, ScaleSplitTransport> map = monthData.get(city);
        return map == null ? null : map.get(month);
    }

    private Integer rankOfMap(Map<String, Double> map, String city) {
        Double self = map.get(city);
        if (self == null) return null;
        int rank = 1;
        for (Double v : map.values()) {
            if (v != null && v > self) rank++;
        }
        return rank;
    }

    private Integer yoyRankOfMap(Map<String, Double> map, String city) {
        Double self = map.get(city);
        if (self == null) return null;
        int rank = 1;
        for (Double v : map.values()) {
            if (v != null && v > self) rank++;
        }
        return rank;
    }

    private Integer rankOfTurnover(Map<String, ScaleSplitTransport> cumMap, String city, int which) {
        Double self = null;
        for (Map.Entry<String, ScaleSplitTransport> e : cumMap.entrySet()) {
            ScaleSplitTransport r = e.getValue();
            Double v = which == 0 ? r.getAboveScaleTurnover()
                : which == 1 ? r.getBelowScaleTurnover() : r.getTotalTurnover();
            if (v == null) continue;
            if (RegionUtil.normalizeCityName(e.getKey()).equals(RegionUtil.normalizeCityName(city))) self = v;
        }
        if (self == null) return null;
        int rank = 1;
        for (ScaleSplitTransport r : cumMap.values()) {
            Double v = which == 0 ? r.getAboveScaleTurnover()
                : which == 1 ? r.getBelowScaleTurnover() : r.getTotalTurnover();
            if (v != null && v > self) rank++;
        }
        return rank;
    }

    private Integer yoyRankOfTurnover(Map<String, ScaleSplitTransport> cumMap, String city, int which) {
        Double self = null;
        for (Map.Entry<String, ScaleSplitTransport> e : cumMap.entrySet()) {
            ScaleSplitTransport r = e.getValue();
            Double v = which == 0 ? r.getAboveScaleYoy()
                : which == 1 ? r.getBelowScaleYoy() : r.getTotalYoy();
            if (v == null) continue;
            if (RegionUtil.normalizeCityName(e.getKey()).equals(RegionUtil.normalizeCityName(city))) self = v;
        }
        if (self == null) return null;
        int rank = 1;
        for (ScaleSplitTransport r : cumMap.values()) {
            Double v = which == 0 ? r.getAboveScaleYoy()
                : which == 1 ? r.getBelowScaleYoy() : r.getTotalYoy();
            if (v != null && v > self) rank++;
        }
        return rank;
    }

    // ==================== 通用辅助 ====================

    private int parseMonth(String period) {
        return Integer.parseInt(period.split("-")[1]);
    }

    private String pad(int m) {
        return m < 10 ? "0" + m : String.valueOf(m);
    }

    private double round(double v, int scale) {
        double factor = Math.pow(10, scale);
        return Math.round(v * factor) / factor;
    }

    private Double ratioOf(Double part, Double total) {
        if (part == null || total == null || total == 0.0) return null;
        return part / total;
    }

    private void setNumeric(Row row, int idx, Double value) {
        Cell cell = row.createCell(idx);
        if (value != null) cell.setCellValue(value);
    }

    private void setNumeric(Row row, int idx, Integer value) {
        if (value != null) setNumeric(row, idx, value.doubleValue());
    }

    private void autoWidth(Sheet sheet, int colCount) {
        for (int i = 0; i < colCount; i++) {
            sheet.setColumnWidth(i, 12 * 256);
        }
        sheet.setColumnWidth(0, 16 * 256);
        sheet.setColumnWidth(1, 22 * 256);
    }

    private byte[] toBytes(XSSFWorkbook wb) throws Exception {
        try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            wb.write(out);
            wb.close();
            return out.toByteArray();
        }
    }
}