package com.trafficaudit.dataimport.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.trafficaudit.auditengine.entity.AuditResult;
import com.trafficaudit.auditengine.mapper.AuditResultMapper;
import com.trafficaudit.common.util.RegionUtil;
import com.trafficaudit.dataimport.entity.EnergyAuthVehicle;
import com.trafficaudit.dataimport.entity.EnergyVehicleQuarterly;
import com.trafficaudit.dataimport.entity.FreightTurnoverImport;
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
import com.trafficaudit.dataimport.entity.PassengerAuthVehicle;
import com.trafficaudit.dataimport.entity.PassengerEnterpriseMonthly;
import com.trafficaudit.dataimport.entity.PassengerIndividualMonthly;
import com.trafficaudit.dataimport.entity.ImportBatch;
import com.trafficaudit.dataimport.entity.InvestmentMonthly;
import com.trafficaudit.dataimport.entity.InvestmentProject;
import com.trafficaudit.dataimport.entity.InvestmentSystem;
import com.trafficaudit.dataimport.entity.CityBusMonthly;
import com.trafficaudit.dataimport.entity.CityTaxiMonthly;
import com.trafficaudit.dataimport.entity.CityTaxiAuth;
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
import com.trafficaudit.dataimport.entity.TransportAuthVehicle;
import com.trafficaudit.dataimport.entity.VehicleTrackMileage;
import com.trafficaudit.dataimport.mapper.EnergyAuthVehicleMapper;
import com.trafficaudit.dataimport.mapper.EnergyVehicleQuarterlyMapper;
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.PassengerAuthVehicleMapper;
import com.trafficaudit.dataimport.mapper.PassengerEnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.PassengerIndividualMonthlyMapper;
import com.trafficaudit.rulemanage.mapper.AuditRuleMapper;
import com.trafficaudit.dataimport.mapper.ImportBatchMapper;
import com.trafficaudit.dataimport.mapper.InvestmentMonthlyMapper;
import com.trafficaudit.dataimport.mapper.InvestmentProjectMapper;
import com.trafficaudit.dataimport.mapper.InvestmentSystemMapper;
import com.trafficaudit.dataimport.mapper.CityBusMonthlyMapper;
import com.trafficaudit.dataimport.mapper.CityTaxiMonthlyMapper;
import com.trafficaudit.dataimport.mapper.CityTaxiAuthMapper;
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
import com.trafficaudit.dataimport.mapper.TransportAuthVehicleMapper;
import com.trafficaudit.dataimport.mapper.VehicleTrackMileageMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import cn.hutool.core.io.IoUtil;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.trafficaudit.dataimport.dto.ImportResult;
import org.apache.poi.ss.usermodel.DateUtil;
import java.util.Comparator;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.stream.Collectors;

import java.text.SimpleDateFormat;

/**
 * 数据导入服务：支持4类模板导入
 */
@Slf4j
@Service
public class DataImportService {

    private static final DataFormatter FORMATTER = new DataFormatter();

    /** H204 能耗明细车辆类型码 -> 中文 */
    private static final Map<String, String> ENERGY_VEHICLE_TYPE_NAMES = new HashMap<>();

    /** H204 能耗明细燃料类型码 -> 中文 */
    private static final Map<String, String> ENERGY_FUEL_TYPE_NAMES = new HashMap<>();

    /** H204 燃料1计量单位码 -> 中文单位 */
    private static final Map<String, String> ENERGY_FUEL_UNIT_NAMES = new HashMap<>();

    static {
        ENERGY_VEHICLE_TYPE_NAMES.put("01", "普通货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("02", "平板货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("03", "仓栅式货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("04", "厢式货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("06", "罐式货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("07", "特殊结构货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("08", "自卸货车");
        ENERGY_VEHICLE_TYPE_NAMES.put("09", "车辆运输车");
        ENERGY_VEHICLE_TYPE_NAMES.put("11", "重型半挂牵引车");

        ENERGY_FUEL_TYPE_NAMES.put("01", "汽油");
        ENERGY_FUEL_TYPE_NAMES.put("02", "柴油");
        ENERGY_FUEL_TYPE_NAMES.put("03", "压缩天然气");
        ENERGY_FUEL_TYPE_NAMES.put("04", "液化天然气");
        ENERGY_FUEL_TYPE_NAMES.put("07", "电动");
        ENERGY_FUEL_TYPE_NAMES.put("08", "氢气");

        ENERGY_FUEL_UNIT_NAMES.put("1", "升");
        ENERGY_FUEL_UNIT_NAMES.put("2", "升");
        ENERGY_FUEL_UNIT_NAMES.put("4", "千克");
        ENERGY_FUEL_UNIT_NAMES.put("5", "千克");
        ENERGY_FUEL_UNIT_NAMES.put("7", "千瓦时");
    }

    @Resource
    private H2032EnterpriseMonthlyMapper h2032Mapper;
    @Resource
    private TransportAuthVehicleMapper transportAuthMapper;
    @Resource
    private VehicleTrackMileageMapper trackMileageMapper;
    @Resource
    private ScaleSplitTransportMapper scaleSplitMapper;
    @Resource
    private ImportBatchMapper importBatchMapper;
    @Resource
    private FreightTurnoverImportMapper freightTurnoverMapper;
    @Resource
    private AuditResultMapper auditResultMapper;
    @Resource
    private PassengerEnterpriseMonthlyMapper passengerMapper;
    @Resource
    private PassengerIndividualMonthlyMapper passengerIndividualMapper;
    @Resource
    private PassengerAuthVehicleMapper passengerAuthMapper;
    @Resource
    private AuditRuleMapper ruleMapper;
    @Resource
    private EnergyVehicleQuarterlyMapper energyMapper;
    @Resource
    private EnergyAuthVehicleMapper energyAuthMapper;
    @Resource
    private InvestmentProjectMapper investProjectMapper;
    @Resource
    private InvestmentMonthlyMapper investMonthlyMapper;
    @Resource
    private InvestmentSystemMapper investSystemMapper;
    @Resource
    private CityBusMonthlyMapper cityBusMapper;
    @Resource
    private CityTaxiMonthlyMapper cityTaxiMapper;
    @Resource
    private CityTaxiAuthMapper cityTaxiAuthMapper;
    @Resource
    private SqlSessionFactory sqlSessionFactory;

    // ========== H2032 月报 ==========

    public ImportResult importH2032(MultipartFile file, String period) throws Exception {
        List<H2032Row> rows = new ArrayList<>();
        Map<String, Integer> colMap;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderCols(sheet, "道路货物运输月度生产情况", "企业名称", "货运量_总计");
            colMap = buildHeaderMap(sheet.getRow(0));
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = gs(row, colMap, "企业名称");
                if (name == null || name.trim().isEmpty()) continue;
                H2032EnterpriseMonthly entity = new H2032EnterpriseMonthly();
                entity.setReportPeriod(period);
                entity.setRegionCode(gs(row, colMap, "所属地区"));
                entity.setEnterpriseCode(gs(row, colMap, "企业代码"));
                entity.setEnterpriseName(name);
                entity.setUnifiedCreditCode(gs(row, colMap, "统一社会信用代码"));
                entity.setReportUnit(gs(row, colMap, "填报单位"));
                entity.setVehicleTotal(gi(row, colMap, "车辆数_总计"));
                entity.setTonsTotal(gd(row, colMap, "标记吨位数_总计"));
                entity.setVehicleTractor(gi(row, colMap, "车辆数_牵引车"));
                entity.setVehicleTrailer(gi(row, colMap, "车辆数_挂车"));
                entity.setTonsTrailer(gd(row, colMap, "标记吨位数_挂车"));
                entity.setVehicleContainer(gi(row, colMap, "车辆数_集装箱车"));
                entity.setTonsContainer(gd(row, colMap, "标记吨位数_集装箱车"));
                entity.setFreightTotal(gd(row, colMap, "货运量_总计"));
                entity.setTurnoverTotal(gd(row, colMap, "货物周转量_总计"));
                entity.setFreightContainer(gd(row, colMap, "货运量_集装箱"));
                entity.setFreightCoal(gd(row, colMap, "货运量_煤炭及制品"));
                entity.setFreightOilGas(gd(row, colMap, "货运量_石油、天然气及制品（吨）"));
                entity.setFreightCrudeOil(gd(row, colMap, "货运量_其中：原油（吨）"));
                entity.setFreightMetalOre(gd(row, colMap, "货运量_金属矿石"));
                entity.setFreightIronOre(gd(row, colMap, "货运量_其中：铁矿石（吨）"));
                entity.setFreightBuilding(gd(row, colMap, "货运量_矿物性建筑材料（吨）"));
                entity.setFreightGrain(gd(row, colMap, "货运量_粮食（吨）"));
                entity.setUnitLeader(gs(row, colMap, "单位负责人"));
                entity.setStatsLeader(gs(row, colMap, "统计负责人"));
                entity.setContactPerson(gs(row, colMap, "填表人"));
                entity.setContactPhone(gs(row, colMap, "联系电话"));
                entity.setReportDate(gds(row, colMap, "报出日期"));
                entity.setAvgTonnage(gd(row, colMap, "货车平均吨位"));
                entity.setAvgTonnageTrailer(gd(row, colMap, "平均吨位_挂车"));
                entity.setAvgTonnageWhole(gd(row, colMap, "平均吨位_整车"));
                entity.setAvgDistance(gd(row, colMap, "货车平均运距"));
                entity.setDailyTripsPerTon(gd(row, colMap, "单吨日均载运次数"));
                entity.setDailyMileagePerVehicle(gd(row, colMap, "单车单天行驶里程"));
                entity.setDailyVolumePerVehicle(gd(row, colMap, "单车日均运量"));
                entity.setDailyTripsWithTractor(gd(row, colMap, "单吨日均载运次数（结合牵引车）"));
                entity.setVehicleMomGrowth(gd(row, colMap, "车辆数-总计环比增速"));
                entity.setTonsMomGrowth(gd(row, colMap, "标记吨位-总计环比增速"));
                entity.setFreightMomGrowth(gd(row, colMap, "货运量-总计环比增速"));
                entity.setTurnoverMomGrowth(gd(row, colMap, "货物周转量-总计环比增速"));
                entity.setAvgDistanceMomGrowth(gd(row, colMap, "货车平均运距-环比增速"));
                entity.setVehicleYoyGrowth(gd(row, colMap, "车辆数-总计同比增速"));
                entity.setTonsYoyGrowth(gd(row, colMap, "标记吨位-总计同比增速"));
                entity.setTractorTrailerRatio(gd(row, colMap, "牵引车挂车比"));
                entity.setFreightYoyGrowth(gd(row, colMap, "货运量-总计同比增速"));
                entity.setTurnoverYoyGrowth(gd(row, colMap, "货物周转量-总计同比增速"));
                entity.setAvgDistanceYoyGrowth(gd(row, colMap, "货车平均运距-同比增速"));
                entity.setVehicleWhole(gi(row, colMap, "车辆数_整车"));
                entity.setTonsWhole(gd(row, colMap, "标记吨位数_整车"));
                entity.setVerifyExplanation(gs(row, colMap, "核实性原因解释"));
                entity.setReportNotes(gs(row, colMap, "上报说明"));
                entity.setModifyRecord(gs(row, colMap, "修改记录"));
                rows.add(new H2032Row(r + 1, entity));
            }
        } catch (Exception e) {
            log.error("H2032 parse error", e);
            throw e;
        }

        // 幂等：同报表期重新导入，先删除旧数据（只清 H2032 规则的审核结果）
        h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()
            .eq(H2032EnterpriseMonthly::getReportPeriod, period));
        List<Long> h2032RuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("H2032".equals(rule.getReportType())) h2032RuleIds.add(rule.getId());
        }
        if (!h2032RuleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, h2032RuleIds));
        }

        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (H2032Row item : rows) {
            try {
                h2032Mapper.insert(item.entity);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) {
                    cause = cause.getCause();
                }
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) {
                    reason = e.getMessage();
                }
                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);
                log.error("H2032 insert error: row {}, {}", item.excelRow, item.entity.getEnterpriseName(), e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) {
            errorDetail = errorDetail.substring(0, 4000) + "\n……";
        }
        recordBatch(file.getOriginalFilename(), "H2032", period, rows.size(), success, failDetails.size(), errorDetail);
        log.info("H2032 imported: {} rows for {} (fail {})", success, period, failDetails.size());
        return new ImportResult(success, failDetails.size(), failDetails);
    }


    // ========== H203-1 公路旅客月报（含运政车辆数sheet） ==========

    public ImportResult importPassengerMonthly(MultipartFile file, String period) throws Exception {
        List<PassengerRow> rows = new ArrayList<>();
        List<PassengerAuthVehicle> authList = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            // sheet0: 公路旅客6月报
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderCols(sheet, "公路旅客运输月度生产情况", "企业名称", "客运量_总计");
            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = gs(row, colMap, "企业名称");
                if (name == null || name.trim().isEmpty()) continue;
                PassengerEnterpriseMonthly entity = new PassengerEnterpriseMonthly();
                entity.setReportPeriod(period);
                entity.setRegionCode(gs(row, colMap, "所属地区"));
                entity.setEnterpriseCode(gs(row, colMap, "企业代码"));
                entity.setEnterpriseName(name.trim());
                entity.setVehicleTotal(gi(row, colMap, "客运车辆_总计"));
                entity.setSeatTotal(gi(row, colMap, "核定载客位_总计"));
                entity.setVehicleSchedule(gi(row, colMap, "车辆数_客运班车（含定线旅游客运客车）"));
                entity.setSeatSchedule(gi(row, colMap, "核定载客位_客运班车（含定线旅游客运客车）"));
                entity.setVehicleCharter(gi(row, colMap, "车辆数_客运包车（含非定线旅游客运客车）"));
                entity.setSeatCharter(gi(row, colMap, "核定载客位_客运包车（含非定线旅游客运客车）"));
                entity.setPassengerTotal(gd(row, colMap, "客运量_总计"));
                entity.setTurnoverTotal(gd(row, colMap, "旅客周转量_总计"));
                entity.setPassengerClass1(gd(row, colMap, "客运量_一类客运班线（含定线旅游）"));
                entity.setTurnoverClass1(gd(row, colMap, "旅客周转量_一类客运班线（含定线旅游）"));
                entity.setPassengerClass2(gd(row, colMap, "客运量_二类客运班线（含定线旅游）"));
                entity.setTurnoverClass2(gd(row, colMap, "旅客周转量_二类客运班线（含定线旅游）"));
                entity.setPassengerClass3(gd(row, colMap, "客运量_三类客运班线（含定线旅游）"));
                entity.setTurnoverClass3(gd(row, colMap, "旅客周转量_三类客运班线（含定线旅游）"));
                entity.setPassengerClass4(gd(row, colMap, "客运量_四类客运班线（含定线旅游）"));
                entity.setTurnoverClass4(gd(row, colMap, "旅客周转量_四类客运班线（含定线旅游）"));
                entity.setPassengerCharter(gd(row, colMap, "客运量_客运包车（含非定线旅游）"));
                entity.setTurnoverCharter(gd(row, colMap, "旅客周转量_客运包车（含非定线旅游）"));
                entity.setAvgDistanceTotal(gd(row, colMap, "客运平均运距"));
                entity.setAvgDistanceClass1(gd(row, colMap, "一类客运班线平均运距"));
                entity.setAvgDistanceClass2(gd(row, colMap, "二类客运班线平均运距"));
                entity.setAvgDistanceClass3(gd(row, colMap, "三类客运班线平均运距"));
                entity.setAvgDistanceClass4(gd(row, colMap, "四类客运班线平均运距"));
                entity.setAvgDistanceCharter(gd(row, colMap, "旅游、包车平均运距"));
                entity.setVerifyExplanation(gs(row, colMap, "核实性原因解释"));
                rows.add(new PassengerRow(r + 1, entity));
            }
            // sheet1: 运政车辆数（固定4列：企业代码/企业名称/车辆数/载客位数）
            if (wb.getNumberOfSheets() > 1) {
                Sheet authSheet = wb.getSheetAt(1);
                for (int r = 1; r <= authSheet.getLastRowNum(); r++) {
                    Row row = authSheet.getRow(r);
                    if (row == null) continue;
                    String name = getString(row, 1);
                    if (name == null || name.trim().isEmpty()) continue;
                    PassengerAuthVehicle auth = new PassengerAuthVehicle();
                    auth.setReportPeriod(period);
                    auth.setEnterpriseCode(getString(row, 0));
                    auth.setEnterpriseName(name.trim());
                    auth.setVehicleCount(getInt(row, 2));
                    auth.setSeatCount(getInt(row, 3));
                    authList.add(auth);
                }
            }
        } catch (Exception e) {
            log.error("Passenger monthly parse error", e);
            throw e;
        }

        // 幂等：同报表期重新导入，先删除旧数据
        passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()
            .eq(PassengerEnterpriseMonthly::getReportPeriod, period));
        passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()
            .eq(PassengerAuthVehicle::getReportPeriod, period));
        // 只清 H2031 规则产生的审核结果，避免误删同期的货运审核结果
        List<Long> h2031RuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("H2031".equals(rule.getReportType())) h2031RuleIds.add(rule.getId());
        }
        if (!h2031RuleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, h2031RuleIds));
        }

        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (PassengerRow item : rows) {
            try {
                passengerMapper.insert(item.entity);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) {
                    cause = cause.getCause();
                }
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) {
                    reason = e.getMessage();
                }
                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);
                log.error("Passenger insert error: row {}, {}", item.excelRow, item.entity.getEnterpriseName(), e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) {
            errorDetail = errorDetail.substring(0, 4000) + "\n……";
        }
        recordBatch(file.getOriginalFilename(), "H2031", period, rows.size(), success, failDetails.size(), errorDetail);

        int authSuccess = 0;
        for (PassengerAuthVehicle auth : authList) {
            try {
                passengerAuthMapper.insert(auth);
                authSuccess++;
            } catch (Exception e) {
                log.error("passenger auth insert error: {}", auth.getEnterpriseName(), e);
            }
        }
        recordBatch(file.getOriginalFilename(), "PASSENGER_AUTH", period, authList.size(), authSuccess, 0, null);
        log.info("Passenger monthly imported: {} rows, auth {} rows for {}", success, authSuccess, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }



    /** 公路旅客个体客运量/周转量（企业线下汇报数据，按所属地区导入，供分市州表个体行填充） */
    public ImportResult importPassengerIndividual(MultipartFile file, String period) throws Exception {
        List<PassengerIndividualMonthly> rows = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
            Integer codeCol = colMap.get("所属地区");
            Integer entCol = colMap.get("企业代码");
            Integer nameCol = colMap.get("企业名称");
            Integer passCol = null;
            Integer turnCol = null;
            for (Map.Entry<String, Integer> e : colMap.entrySet()) {
                String k = e.getKey();
                if (k != null && k.contains("个体") && k.contains("客运量")) passCol = e.getValue();
                if (k != null && k.contains("个体") && (k.contains("周转量") || k.contains("周转"))) turnCol = e.getValue();
            }
            if (passCol == null && turnCol == null) {
                throw new RuntimeException("未找到「个体客运量/个体旅客周转量」列，请检查模板（列名需包含“个体客运量”“个体周转量”）");
            }
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = nameCol != null ? getString(row, nameCol) : null;
                if (name == null || name.trim().isEmpty()) continue;
                Double pass = passCol != null ? getDecimalOrNull(row, passCol) : null;
                Double turn = turnCol != null ? getDecimalOrNull(row, turnCol) : null;
                if ((pass == null || pass == 0.0) && (turn == null || turn == 0.0)) continue;
                PassengerIndividualMonthly entity = new PassengerIndividualMonthly();
                entity.setReportPeriod(period);
                entity.setRegionCode(codeCol != null ? getString(row, codeCol) : null);
                entity.setEnterpriseCode(entCol != null ? getString(row, entCol) : null);
                entity.setEnterpriseName(name.trim());
                entity.setPassengerCount(pass);
                entity.setTurnover(turn);
                rows.add(entity);
            }
        } catch (Exception e) {
            log.error("Passenger individual parse error", e);
            throw e;
        }
        passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()
            .eq(PassengerIndividualMonthly::getReportPeriod, period));
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (PassengerIndividualMonthly item : rows) {
            try {
                passengerIndividualMapper.insert(item);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) {
                    cause = cause.getCause();
                }
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) {
                    reason = e.getMessage();
                }
                failDetails.add("第 " + (rows.indexOf(item) + 2) + " 行（" + item.getEnterpriseName() + "）：" + reason);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) {
            errorDetail = errorDetail.substring(0, 4000) + "\n……";
        }
        recordBatch(file.getOriginalFilename(), "PASSENGER_INDIVIDUAL", period, rows.size(), success, failDetails.size(), errorDetail);
        log.info("Passenger individual imported: {} rows for {}", success, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    /** 公路旅客运政车辆数（独立模板：企业代码/企业名称/车辆数/载客位数） */
    public ImportResult importPassengerAuth(MultipartFile file, String period) throws Exception {
        List<PassengerAuthVehicle> authList = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
            requireHeaderCols(sheet, "旅客运政车辆数", "企业名称", "车辆数", "载客位数");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = gs(row, colMap, "企业名称");
                if (name == null || name.trim().isEmpty()) continue;
                PassengerAuthVehicle auth = new PassengerAuthVehicle();
                auth.setReportPeriod(period);
                auth.setEnterpriseCode(gs(row, colMap, "企业代码"));
                auth.setEnterpriseName(name.trim());
                auth.setVehicleCount(gi(row, colMap, "车辆数"));
                auth.setSeatCount(gi(row, colMap, "载客位数"));
                authList.add(auth);
            }
        } catch (Exception e) {
            log.error("Passenger auth parse error", e);
            throw e;
        }
        // 幂等：同报表期重新导入，先删除旧数据
        passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()
            .eq(PassengerAuthVehicle::getReportPeriod, period));
        deleteRuleResults("H2031", period);
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (PassengerAuthVehicle auth : authList) {
            try {
                passengerAuthMapper.insert(auth);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add("第 " + (authList.indexOf(auth) + 2) + " 行（" + auth.getEnterpriseName() + "）：" + reason);
                log.error("Passenger auth insert error: {}", auth.getEnterpriseName(), e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch(file.getOriginalFilename(), "PASSENGER_AUTH", period, authList.size(), success, failDetails.size(), errorDetail);
        log.info("Passenger auth imported: {} rows for {}", success, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    // ========== H204 道路货运车辆能源消耗情况（季报，含运政车辆信息sheet） ==========

    /** 能耗明细每组固定18列，车牌号起始列（0-based）：3,21,39,57,75 */
    private static final int[] ENERGY_GROUP_COLS = {3, 21, 39, 57, 75};

    public ImportResult importEnergyMonthly(MultipartFile file, String period) throws Exception {
        List<EnergyRow> rows = new ArrayList<>();
        List<EnergyAuthVehicle> authList = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            // sheet0: 第二季度货车能耗明细（每行左右各5组车辆，每组固定18列）
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "道路货运车辆能源消耗情况", "企业名称", "车牌号");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String enterpriseName = getString(row, 2);
                if (enterpriseName == null || enterpriseName.trim().isEmpty()) continue;
                for (int group : ENERGY_GROUP_COLS) {
                    String plate = getString(row, group);
                    if (plate == null || plate.trim().isEmpty()) continue;
                    EnergyVehicleQuarterly e = new EnergyVehicleQuarterly();
                    e.setReportPeriod(period);
                    e.setRegionCode(getString(row, 0));
                    e.setEnterpriseCode(getString(row, 1));
                    e.setEnterpriseName(enterpriseName.trim());
                    e.setPlateNo(plate.trim());
                    String vtc = getString(row, group + 1);
                    e.setVehicleTypeCode(vtc);
                    e.setVehicleType(vtc == null ? null : ENERGY_VEHICLE_TYPE_NAMES.getOrDefault(vtc, vtc));
                    String ftc = getString(row, group + 2);
                    e.setFuelTypeCode(ftc);
                    e.setFuelType(ftc == null ? null : ENERGY_FUEL_TYPE_NAMES.getOrDefault(ftc, ftc));
                    e.setManufactureYear(getIntOrNull(row, group + 3));
                    e.setMarkedTonnage(getDecimalOrNull(row, group + 4));
                    e.setTotalMileage(getDecimalOrNull(row, group + 5));
                    e.setLoadedMileage(getDecimalOrNull(row, group + 6));
                    e.setEmptyMileage(getDecimalOrNull(row, group + 7));
                    e.setFreight(getDecimalOrNull(row, group + 8));
                    e.setTurnover(getDecimalOrNull(row, group + 9));
                    e.setTripCount(getIntOrNull(row, group + 10));
                    String unit = getString(row, group + 11);
                    e.setFuelUnit(unit == null ? null : ENERGY_FUEL_UNIT_NAMES.getOrDefault(unit, unit));
                    e.setFuelConsumption(getDecimalOrNull(row, group + 12));
                    e.setAvgDistance(getDecimalOrNull(row, group + 13));
                    e.setAvgLoadedTonnage(getDecimalOrNull(row, group + 14));
                    e.setAvgTripMileage(getDecimalOrNull(row, group + 15));
                    e.setFuelPer100km(getDecimalOrNull(row, group + 16));
                    e.setTurnoverFuel(getDecimalOrNull(row, group + 17));
                    rows.add(new EnergyRow(r + 1, e));
                }
            }
            // sheet1: 运政车辆信息（固定5列：车牌号/车辆类型/燃料类型/标记吨位/准牵引质量）
            if (wb.getNumberOfSheets() > 1) {
                Sheet authSheet = wb.getSheetAt(1);
                for (int r = 1; r <= authSheet.getLastRowNum(); r++) {
                    Row row = authSheet.getRow(r);
                    if (row == null) continue;
                    String plate = getString(row, 0);
                    if (plate == null || plate.trim().isEmpty()) continue;
                    EnergyAuthVehicle auth = new EnergyAuthVehicle();
                    auth.setReportPeriod(period);
                    auth.setPlateNo(plate.trim());
                    auth.setVehicleType(getString(row, 1));
                    auth.setFuelType(getString(row, 2));
                    auth.setMarkedTonnage(getDecimal(row, 3));
                    auth.setTractionQuality(getDecimal(row, 4));
                    authList.add(auth);
                }
            }
        } catch (Exception e) {
            log.error("Energy monthly parse error", e);
            throw e;
        }

        // 幂等：同报表期重新导入，先删除旧数据（两表 + H204 规则审核结果）
        energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()
            .eq(EnergyVehicleQuarterly::getReportPeriod, period));
        energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()
            .eq(EnergyAuthVehicle::getReportPeriod, period));
        List<Long> h204RuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("H204".equals(rule.getReportType())) h204RuleIds.add(rule.getId());
        }
        if (!h204RuleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, h204RuleIds));
        }

        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (EnergyRow item : rows) {
            try {
                energyMapper.insert(item.entity);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName()
                    + " " + item.entity.getPlateNo() + "）：" + reason);
                log.error("Energy insert error: row {}, {}", item.excelRow, item.entity.getPlateNo(), e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) {
            errorDetail = errorDetail.substring(0, 4000) + "\n……";
        }
        recordBatch(file.getOriginalFilename(), "H204", period, rows.size(), success, failDetails.size(), errorDetail);

        int authSuccess = 0;
        for (EnergyAuthVehicle auth : authList) {
            try {
                energyAuthMapper.insert(auth);
                authSuccess++;
            } catch (Exception e) {
                log.error("energy auth insert error: {}", auth.getPlateNo(), e);
            }
        }
        recordBatch(file.getOriginalFilename(), "ENERGY_AUTH", period, authList.size(), authSuccess, 0, null);
        log.info("Energy monthly imported: {} vehicles, auth {} rows for {}", success, authSuccess, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    /** 能耗车辆运政信息（独立模板：车牌号/车辆类型/燃料类型/标记吨位/准牵引质量） */
    public ImportResult importEnergyAuth(MultipartFile file, String period) throws Exception {
        List<EnergyAuthVehicle> authList = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "能耗车辆运政信息", "车牌号");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String plate = getString(row, 0);
                if (plate == null || plate.trim().isEmpty()) continue;
                EnergyAuthVehicle auth = new EnergyAuthVehicle();
                auth.setReportPeriod(period);
                auth.setPlateNo(plate.trim());
                auth.setVehicleType(getString(row, 1));
                auth.setFuelType(getString(row, 2));
                auth.setMarkedTonnage(getDecimal(row, 3));
                auth.setTractionQuality(getDecimal(row, 4));
                authList.add(auth);
            }
        } catch (Exception e) {
            log.error("Energy auth parse error", e);
            throw e;
        }
        // 幂等：同报表期重新导入，先删除旧数据
        energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()
            .eq(EnergyAuthVehicle::getReportPeriod, period));
        deleteRuleResults("H204", period);
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (EnergyAuthVehicle auth : authList) {
            try {
                energyAuthMapper.insert(auth);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add("第 " + (authList.indexOf(auth) + 2) + " 行（" + auth.getPlateNo() + "）：" + reason);
                log.error("Energy auth insert error: {}", auth.getPlateNo(), e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch(file.getOriginalFilename(), "ENERGY_AUTH", period, authList.size(), success, failDetails.size(), errorDetail);
        log.info("Energy auth imported: {} rows for {}", success, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    /** H204 行记录：保留 Excel 行号用于失败定位 */
    private static class EnergyRow {
        final int excelRow;
        final EnergyVehicleQuarterly entity;

        EnergyRow(int excelRow, EnergyVehicleQuarterly entity) {
            this.excelRow = excelRow;
            this.entity = entity;
        }
    }

    /** 空单元格返回 null（用于能耗明细未填写项与 0 区分） */
    private Integer getIntOrNull(Row row, int idx) {
        Double val = getDecimalOrNull(row, idx);
        return val == null ? null : val.intValue();
    }

    // ========== 运政车辆 ==========

    public int importTransportAuth(MultipartFile file, String period) throws Exception {
        List<TransportAuthVehicle> list = new ArrayList<>();
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "运政车辆", "企业名称");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = getString(row, 0);
                if (name == null || name.trim().isEmpty()) continue;
                TransportAuthVehicle entity = new TransportAuthVehicle();
                entity.setReportPeriod(period);
                entity.setEnterpriseName(name);
                entity.setTractorCount(getInt(row, 1));
                entity.setTrailerCount(getInt(row, 2));
                entity.setOtherCount(getInt(row, 3));
                entity.setTrailerTons(getDecimal(row, 4));
                entity.setOtherTons(getDecimal(row, 5));
                list.add(entity);
            }
        } catch (Exception e) {
            throw e;
        }

        // 幂等：同报表期重新导入，先删除旧数据
        transportAuthMapper.delete(new LambdaQueryWrapper<TransportAuthVehicle>()
            .eq(TransportAuthVehicle::getReportPeriod, period));
        auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
            .eq(AuditResult::getReportPeriod, period));

        int success = 0;
        for (TransportAuthVehicle entity : list) {
            try {
                transportAuthMapper.insert(entity);
                success++;
            } catch (Exception e) {
                log.error("transport auth insert error: {}", entity.getEnterpriseName(), e);
                failRows++;
            }
        }
        recordBatch(file.getOriginalFilename(), "TRANSPORT_AUTH", period, list.size(), success, failRows, null);
        log.info("Transport auth imported: {} rows for {}", success, period);
        return success;
    }

    // ========== 轨迹里程 ==========

    public int importTrackMileage(MultipartFile file, String period) throws Exception {
        List<VehicleTrackMileage> list = new ArrayList<>();
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "轨迹里程", "企业名称");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = getString(row, 1);
                if (name == null || name.trim().isEmpty()) continue;
                VehicleTrackMileage entity = new VehicleTrackMileage();
                entity.setEnterpriseName(name);
                entity.setMonthlyMileage(getDecimal(row, 2));
                entity.setTrackedVehicles(getInt(row, 3));
                entity.setReportPeriod(period);
                list.add(entity);
            }
        } catch (Exception e) {
            throw e;
        }

        // 幂等：同报表期先删除
        trackMileageMapper.delete(new LambdaQueryWrapper<VehicleTrackMileage>()
            .eq(VehicleTrackMileage::getReportPeriod, period));
        auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
            .eq(AuditResult::getReportPeriod, period));

        int success = 0;
        for (VehicleTrackMileage entity : list) {
            try {
                trackMileageMapper.insert(entity);
                success++;
            } catch (Exception e) {
                log.error("track mileage insert error: {}", entity.getEnterpriseName(), e);
                failRows++;
            }
        }
        recordBatch(file.getOriginalFilename(), "TRACK_MILEAGE", period, list.size(), success, failRows, null);
        log.info("Track mileage imported: {} rows for {}", success, period);
        return success;
    }

    // ========== 规上规下拆分 ==========

    public int importScaleSplit(MultipartFile file, String period) throws Exception {
        List<ScaleSplitTransport> list = new ArrayList<>();
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            boolean hasPeriodSheet = false;
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                String sn = wb.getSheetAt(i).getSheetName();
                if (sn != null && (sn.contains("当月") || sn.contains("累计"))) { hasPeriodSheet = true; break; }
            }
            if (!hasPeriodSheet) {
                throw new RuntimeException("所选数据类型【规上规下拆分】与文件内容不符：未找到含 当月/累计 的 sheet，请确认是否选错了数据类型");
            }
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                String periodType = null;
                if (sheetName.contains("当月")) periodType = "MONTH";
                else if (sheetName.contains("累计")) periodType = "CUMULATIVE";
                if (periodType == null) continue;

                // 检测子表头：是否包含货运量列（扩展布局）
                Row subHeader = sheet.getRow(2);
                boolean hasFreight = subHeader != null
                    && "货运量".equals(getString(subHeader, 0));

                for (int r = 3; r <= sheet.getLastRowNum(); r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) continue;
                    String regionName = getString(row, 0);
                    if (regionName == null || regionName.trim().isEmpty()) continue;

                    ScaleSplitTransport entity = new ScaleSplitTransport();
                    entity.setReportPeriod(period);
                    entity.setPeriodType(periodType);
                    entity.setRegionName(regionName.trim());

                    if (hasFreight) {
                        entity.setAboveScaleFreight(getDecimal(row, 0));
                        entity.setAboveScaleTurnover(getDecimal(row, 1));
                        entity.setAboveScaleRank(getInt(row, 2));
                        entity.setAboveScaleYoy(getDecimal(row, 3));
                        entity.setAboveScaleYoyRank(getInt(row, 4));
                        entity.setBelowScaleFreight(getDecimal(row, 5));
                        entity.setBelowScaleTurnover(getDecimal(row, 6));
                        entity.setBelowScaleRank(getInt(row, 7));
                        entity.setBelowScaleYoy(getDecimal(row, 8));
                        entity.setBelowScaleYoyRank(getInt(row, 9));
                        entity.setTotalFreight(getDecimal(row, 10));
                        entity.setTotalTurnover(getDecimal(row, 11));
                        entity.setTotalRank(getInt(row, 12));
                        entity.setTotalYoy(getDecimal(row, 13));
                        entity.setTotalYoyRank(getInt(row, 14));
                    } else {
                        entity.setAboveScaleTurnover(getDecimal(row, 1));
                        entity.setAboveScaleRank(getInt(row, 2));
                        entity.setAboveScaleYoy(getDecimal(row, 3));
                        entity.setAboveScaleYoyRank(getInt(row, 4));
                        entity.setBelowScaleTurnover(getDecimal(row, 5));
                        entity.setBelowScaleRank(getInt(row, 6));
                        entity.setBelowScaleYoy(getDecimal(row, 7));
                        entity.setBelowScaleYoyRank(getInt(row, 8));
                        entity.setTotalTurnover(getDecimal(row, 9));
                        entity.setTotalRank(getInt(row, 10));
                        entity.setTotalYoy(getDecimal(row, 11));
                        entity.setTotalYoyRank(getInt(row, 12));
                    }
                    list.add(entity);
                }
            }
        } catch (Exception e) {
            throw e;
        }

        // 幂等：同报表期先删除
        scaleSplitMapper.delete(new LambdaQueryWrapper<ScaleSplitTransport>()
            .eq(ScaleSplitTransport::getReportPeriod, period));

        int success = 0;
        for (ScaleSplitTransport entity : list) {
            try {
                scaleSplitMapper.insert(entity);
                success++;
            } catch (Exception e) {
                log.error("scale split insert error: {} - {}", entity.getRegionName(), entity.getPeriodType(), e);
                failRows++;
            }
        }
        recordBatch(file.getOriginalFilename(), "SCALE_SPLIT", period, list.size(), success, failRows, null);
        log.info("Scale split imported: {} rows for {}", success, period);
        return success;
    }


    // ========== 货运量周转量（模板_货运量周转量.xlsx，每月导入） ==========

    public int importFreightTurnover(MultipartFile file, String period) throws Exception {
        List<FreightTurnoverImport> list = new ArrayList<>();
        int failRows = 0;
        // 报表期 N 月：左半为今年 1-N 月（列数不固定），右半固定为去年 1-12 月（算同比用）
        int monthCount = Integer.parseInt(period.split("-")[1]);
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            boolean cityRowFound = false;
            for (int r = 1; r <= Math.min(sheet.getLastRowNum(), 30); r++) {
                Row rr = sheet.getRow(r);
                if (rr == null) continue;
                String nm = getString(rr, 0);
                if (nm == null || nm.trim().isEmpty()) continue;
                String region = RegionUtil.normalizeCityName(nm);
                if (RegionUtil.cityList().contains(region) || "湖北省".equals(region)) { cityRowFound = true; break; }
            }
            if (!cityRowFound) {
                throw new RuntimeException("所选数据类型【货运量周转量】与文件内容不符：未识别到市州行（首列应为市州名称），请确认是否选错了数据类型");
            }
            Map<String, FreightTurnoverImport> freightMap = new HashMap<>();
            Map<String, FreightTurnoverImport> turnoverMap = new HashMap<>();
            Set<String> seen = new HashSet<>();
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = getString(row, 0);
                if (name == null || name.trim().isEmpty()) continue;
                String region = RegionUtil.normalizeCityName(name);
                boolean isCity = RegionUtil.cityList().contains(region);
                if (!isCity && !"湖北省".equals(region)) continue; // 表头行
                if (seen.contains(region)) {
                    // 该名称已出现过 -> 周转量区块
                    FreightTurnoverImport entity = turnoverMap.get(region);
                    if (entity == null) {
                        entity = new FreightTurnoverImport();
                        entity.setReportPeriod(period);
                        entity.setRegionName(name.trim());
                        turnoverMap.put(region, entity);
                    }
                    fillTurnoverRow(row, entity, monthCount);
                } else {
                    seen.add(region);
                    FreightTurnoverImport entity = freightMap.get(region);
                    if (entity == null) {
                        entity = new FreightTurnoverImport();
                        entity.setReportPeriod(period);
                        entity.setRegionName(name.trim());
                        freightMap.put(region, entity);
                    }
                    fillFreightRow(row, entity, monthCount);
                }
            }
            // 合并：一个市州一行（货运量 + 周转量）
            for (Map.Entry<String, FreightTurnoverImport> entry : freightMap.entrySet()) {
                FreightTurnoverImport entity = entry.getValue();
                FreightTurnoverImport turnover = turnoverMap.get(entry.getKey());
                if (turnover != null) {
                    for (int m = 1; m <= 12; m++) {
                        setTurnover(entity, m, getTurnover(turnover, m));
                        setLastTurnover(entity, m, getLastTurnover(turnover, m));
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            throw e;
        }

        // 幂等：同报表期先删除
        freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
            .eq(FreightTurnoverImport::getReportPeriod, period));

        int success = 0;
        for (FreightTurnoverImport entity : list) {
            try {
                freightTurnoverMapper.insert(entity);
                success++;
            } catch (Exception e) {
                log.error("freight turnover insert error: {}", entity.getRegionName(), e);
                failRows++;
            }
        }
        recordBatch(file.getOriginalFilename(), "FREIGHT_TURNOVER", period, list.size(), success, failRows, null);
        log.info("Freight turnover imported: {} rows for {}", success, period);
        return success;
    }

    /** 解析货运量区块行：左半 B 起 N 列为今年 1-N 月，右半为去年 1-12 月 */
    private void fillFreightRow(Row row, FreightTurnoverImport entity, int monthCount) {
        int rightNameCol = findSecondRegionCol(row);
        for (int m = 1; m <= monthCount; m++) {
            setFreight(entity, m, getDecimalOrNull(row, m));
        }
        for (int m = 1; m <= 12; m++) {
            int col = rightNameCol >= 0 ? rightNameCol + m : 9 + m;
            setLastFreight(entity, m, getDecimalOrNull(row, col));
        }
    }

    /** 解析周转量区块行：左半 B 起 N 列为今年 1-N 月，右半为去年 1-12 月 */
    private void fillTurnoverRow(Row row, FreightTurnoverImport entity, int monthCount) {
        int rightNameCol = findSecondRegionCol(row);
        for (int m = 1; m <= monthCount; m++) {
            setTurnover(entity, m, getDecimalOrNull(row, m));
        }
        for (int m = 1; m <= 12; m++) {
            int col = rightNameCol >= 0 ? rightNameCol + m : 9 + m;
            setLastTurnover(entity, m, getDecimalOrNull(row, col));
        }
    }

    /** 找行内第二个市州名列（右半市州名），找不到返回 -1（回退 J 列起 12 列） */
    private int findSecondRegionCol(Row row) {
        String firstName = getString(row, 0);
        if (firstName == null) return -1;
        for (int c = 1; c <= 20; c++) {
            String v = getString(row, c);
            if (v != null && !v.isEmpty()
                && RegionUtil.normalizeCityName(v).equals(RegionUtil.normalizeCityName(firstName))) {
                return c;
            }
        }
        return -1;
    }

    private Double getFreight(FreightTurnoverImport e, int m) {
        return monthValue(m, e::getFreightM01, e::getFreightM02, e::getFreightM03, e::getFreightM04,
            e::getFreightM05, e::getFreightM06, e::getFreightM07, e::getFreightM08,
            e::getFreightM09, e::getFreightM10, e::getFreightM11, e::getFreightM12);
    }

    private Double getLastFreight(FreightTurnoverImport e, int m) {
        return monthValue(m, e::getLastFreightM01, e::getLastFreightM02, e::getLastFreightM03, e::getLastFreightM04,
            e::getLastFreightM05, e::getLastFreightM06, e::getLastFreightM07, e::getLastFreightM08,
            e::getLastFreightM09, e::getLastFreightM10, e::getLastFreightM11, e::getLastFreightM12);
    }

    private Double getTurnover(FreightTurnoverImport e, int m) {
        return monthValue(m, e::getTurnoverM01, e::getTurnoverM02, e::getTurnoverM03, e::getTurnoverM04,
            e::getTurnoverM05, e::getTurnoverM06, e::getTurnoverM07, e::getTurnoverM08,
            e::getTurnoverM09, e::getTurnoverM10, e::getTurnoverM11, e::getTurnoverM12);
    }

    private Double getLastTurnover(FreightTurnoverImport e, int m) {
        return monthValue(m, e::getLastTurnoverM01, e::getLastTurnoverM02, e::getLastTurnoverM03, e::getLastTurnoverM04,
            e::getLastTurnoverM05, e::getLastTurnoverM06, e::getLastTurnoverM07, e::getLastTurnoverM08,
            e::getLastTurnoverM09, e::getLastTurnoverM10, e::getLastTurnoverM11, e::getLastTurnoverM12);
    }

    private Double monthValue(int m, java.util.function.Supplier<Double> g1, java.util.function.Supplier<Double> g2,
                              java.util.function.Supplier<Double> g3, java.util.function.Supplier<Double> g4,
                              java.util.function.Supplier<Double> g5, java.util.function.Supplier<Double> g6,
                              java.util.function.Supplier<Double> g7, java.util.function.Supplier<Double> g8,
                              java.util.function.Supplier<Double> g9, java.util.function.Supplier<Double> g10,
                              java.util.function.Supplier<Double> g11, java.util.function.Supplier<Double> g12) {
        switch (m) {
            case 1: return g1.get();
            case 2: return g2.get();
            case 3: return g3.get();
            case 4: return g4.get();
            case 5: return g5.get();
            case 6: return g6.get();
            case 7: return g7.get();
            case 8: return g8.get();
            case 9: return g9.get();
            case 10: return g10.get();
            case 11: return g11.get();
            default: return g12.get();
        }
    }

    private void setFreight(FreightTurnoverImport e, int m, Double v) {
        switch (m) {
            case 1: e.setFreightM01(v); break;
            case 2: e.setFreightM02(v); break;
            case 3: e.setFreightM03(v); break;
            case 4: e.setFreightM04(v); break;
            case 5: e.setFreightM05(v); break;
            case 6: e.setFreightM06(v); break;
            case 7: e.setFreightM07(v); break;
            case 8: e.setFreightM08(v); break;
            case 9: e.setFreightM09(v); break;
            case 10: e.setFreightM10(v); break;
            case 11: e.setFreightM11(v); break;
            case 12: e.setFreightM12(v); break;
            default: break;
        }
    }

    private void setLastFreight(FreightTurnoverImport e, int m, Double v) {
        switch (m) {
            case 1: e.setLastFreightM01(v); break;
            case 2: e.setLastFreightM02(v); break;
            case 3: e.setLastFreightM03(v); break;
            case 4: e.setLastFreightM04(v); break;
            case 5: e.setLastFreightM05(v); break;
            case 6: e.setLastFreightM06(v); break;
            case 7: e.setLastFreightM07(v); break;
            case 8: e.setLastFreightM08(v); break;
            case 9: e.setLastFreightM09(v); break;
            case 10: e.setLastFreightM10(v); break;
            case 11: e.setLastFreightM11(v); break;
            case 12: e.setLastFreightM12(v); break;
            default: break;
        }
    }

    private void setTurnover(FreightTurnoverImport e, int m, Double v) {
        switch (m) {
            case 1: e.setTurnoverM01(v); break;
            case 2: e.setTurnoverM02(v); break;
            case 3: e.setTurnoverM03(v); break;
            case 4: e.setTurnoverM04(v); break;
            case 5: e.setTurnoverM05(v); break;
            case 6: e.setTurnoverM06(v); break;
            case 7: e.setTurnoverM07(v); break;
            case 8: e.setTurnoverM08(v); break;
            case 9: e.setTurnoverM09(v); break;
            case 10: e.setTurnoverM10(v); break;
            case 11: e.setTurnoverM11(v); break;
            case 12: e.setTurnoverM12(v); break;
            default: break;
        }
    }

    private void setLastTurnover(FreightTurnoverImport e, int m, Double v) {
        switch (m) {
            case 1: e.setLastTurnoverM01(v); break;
            case 2: e.setLastTurnoverM02(v); break;
            case 3: e.setLastTurnoverM03(v); break;
            case 4: e.setLastTurnoverM04(v); break;
            case 5: e.setLastTurnoverM05(v); break;
            case 6: e.setLastTurnoverM06(v); break;
            case 7: e.setLastTurnoverM07(v); break;
            case 8: e.setLastTurnoverM08(v); break;
            case 9: e.setLastTurnoverM09(v); break;
            case 10: e.setLastTurnoverM10(v); break;
            case 11: e.setLastTurnoverM11(v); break;
            case 12: e.setLastTurnoverM12(v); break;
            default: break;
        }
    }

    // ========== 辅助方法 ==========

    private void recordBatch(String fileName, String importType, String period,
                             int total, int success, int fail, String errorDetail) {
        try {
            ImportBatch batch = new ImportBatch();
            batch.setFileName(fileName);
            batch.setImportType(importType);
            batch.setReportPeriod(period);
            batch.setTotalRows(total);
            batch.setSuccessRows(success);
            batch.setFailRows(fail);
            batch.setErrorDetail(errorDetail);
            importBatchMapper.insert(batch);
        } catch (Exception e) {
            log.error("record batch error", e);
        }
    }

    /** 数据变化后删除指定报表类型指定报表期的审核结果（需重新执行审核） */
    private void deleteRuleResults(String reportType, String period) {
        List<Long> ruleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if (reportType.equals(rule.getReportType())) ruleIds.add(rule.getId());
        }
        if (!ruleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, ruleIds));
        }
    }

    /** 类型-表头校验：按必需列名（解析器实际依赖的列），缺失即抛"类型与文件不符" */
    private void requireHeaderCols(Sheet sheet, String typeLabel, String... cols) {
        Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
        List<String> missing = new ArrayList<>();
        for (String c : cols) {
            if (!colMap.containsKey(c)) missing.add(c);
        }
        if (!missing.isEmpty()) {
            throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头缺少必需列【" + String.join("】【", missing) + "】，请确认是否选错了数据类型");
        }
    }

    /** 类型-表头校验：扫描表头行，命中任一关键字即通过 */
    private void requireHeaderKeyword(Sheet sheet, String typeLabel, String... keywords) {
        Row header = sheet == null ? null : sheet.getRow(0);
        if (header != null) {
            for (Cell cell : header) {
                String v = FORMATTER.formatCellValue(cell);
                if (v == null || v.isEmpty()) continue;
                for (String k : keywords) {
                    if (v.contains(k)) return;
                }
            }
        }
        throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头未包含【" + String.join("】【", keywords) + "】等列，请确认是否选错了数据类型");
    }

    /** 按表头名称建立列号映射，兼容新旧模板列位差异 */
    private Map<String, Integer> buildHeaderMap(Row header) {
        Map<String, Integer> colMap = new HashMap<>();
        if (header == null) return colMap;
        for (Cell cell : header) {
            String h = FORMATTER.formatCellValue(cell).trim();
            if (!h.isEmpty() && !colMap.containsKey(h)) {
                colMap.put(h, cell.getColumnIndex());
            }
        }
        return colMap;
    }

    private int colIdx(Map<String, Integer> colMap, String header) {
        Integer idx = colMap.get(header);
        return idx == null ? -1 : idx;
    }

    private String gs(Row row, Map<String, Integer> colMap, String header) {
        int idx = colIdx(colMap, header);
        return idx < 0 ? null : getString(row, idx);
    }

    private Double gd(Row row, Map<String, Integer> colMap, String header) {
        int idx = colIdx(colMap, header);
        return idx < 0 ? 0.0 : getDecimal(row, idx);
    }

    private Integer gi(Row row, Map<String, Integer> colMap, String header) {
        int idx = colIdx(colMap, header);
        return idx < 0 ? 0 : getInt(row, idx);
    }

    private String gds(Row row, Map<String, Integer> colMap, String header) {
        int idx = colIdx(colMap, header);
        return idx < 0 ? null : getDateString(row, idx);
    }

    /** 报出日期：Excel 日期单元格或文本统一转为 yyyy-MM-dd，空值返回 null */
    private String getDateString(Row row, int idx) {
        Cell cell = row.getCell(idx);
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
        }
        String val = FORMATTER.formatCellValue(cell).trim();
        if (val.isEmpty()) return null;
        return val.length() > 10 ? val.substring(0, 10) : val;
    }

    /** H2032 行记录：保留 Excel 行号用于失败定位 */
    private static class H2032Row {
        final int excelRow;
        final H2032EnterpriseMonthly entity;

        H2032Row(int excelRow, H2032EnterpriseMonthly entity) {
            this.excelRow = excelRow;
            this.entity = entity;
        }
    }

    private static class PassengerRow {
        int excelRow;
        PassengerEnterpriseMonthly entity;

        PassengerRow(int excelRow, PassengerEnterpriseMonthly entity) {
            this.excelRow = excelRow;
            this.entity = entity;
        }
    }


    private String getString(Row row, int idx) {
        Cell cell = row.getCell(idx);
        if (cell == null) return null;
        // 公式单元格：读缓存字符串（.et/WPS 文件常见，DataFormatter 无 evaluator 时只返回公式文本）
        if (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.STRING) {
            String cached = cell.getStringCellValue();
            cached = cached == null ? null : cached.trim();
            return cached == null || cached.isEmpty() ? null : cached;
        }
        String val = FORMATTER.formatCellValue(cell).trim();
        return val.isEmpty() ? null : val;
    }

    private Double getDecimal(Row row, int idx) {
        Cell cell = row.getCell(idx);
        if (cell == null) return 0.0;
        // 数值单元格直接取原始值，避免被 Excel 显示格式（百分比/0_ 等）影响解析
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }
        // 公式单元格：读缓存数值（.et/WPS 跨表公式常见，DataFormatter 无 evaluator 时只返回公式文本）
        if (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }
        String val = FORMATTER.formatCellValue(cell).trim();
        if (val.isEmpty()) return 0.0;
        try {
            return Double.parseDouble(val.replace(",", ""));
        } catch (NumberFormatException e) {
            return 0.0;
        }
    }

    private Integer getInt(Row row, int idx) {
        Double val = getDecimal(row, idx);
        if (val == null) return 0;
        return val.intValue();
    }

    /** 空单元格返回 null（用于货运量周转量模板，未填月份与 0 区分） */
    private Double getDecimalOrNull(Row row, int idx) {
        Cell cell = row.getCell(idx);
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }
        if (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        }
        String val = FORMATTER.formatCellValue(cell).trim();
        if (val.isEmpty()) return null;
        try {
            return Double.parseDouble(val.replace(",", ""));
        } catch (NumberFormatException e) {
            return null;
        }
    }
    // ========== 投资模块（客运站场 / 物流园区，全省汇总大表） ==========

    /** 亿元投资项目固定名单（2026年规上项目表） */
    private static final java.util.Set<String> INVEST_BILLION_NAMES = new LinkedHashSet<>(Arrays.asList(
        "汉口客运中心", "钟祥市综合交通客运枢纽一期项目", "航发智慧物流园", "国胜公路港",
        "十堰生产服务型国家物流枢纽工程项目", "秭归县脐橙产业综合物流中心", "宜都供销商贸物流园",
        "长阳县城乡冷链物流园", "宜昌港兴山港区管公水多式联运建设项目", "玉湖冷链（襄阳）交易中心项目",
        "武汉万吨华中冷链港二期项目", "湖北长江现代物流产业集聚示范区·现代物流园",
        "鄂州花湖国际机场空港型国家物流枢纽智慧公共国际货站项目", "荆门智慧冷链物流园",
        "荆门北站顺洋物流基地（荆门北子陵铁路物流园）", "沙洋多式联运现代物流园一期（沙洋煤炭储备•集装箱散改集基地）",
        "首衡城智慧冷链物流二期项目G区", "孝昌广浔智慧物流电商产业园", "申通（孝感）智慧物流电商产业园（三期）",
        "公安县多式联运综合物流园", "荆州市铁水联运物流中心", "荆州市瑞海国际物流园一期", "荆州经开区智能仓储物流园项目",
        "鄂东（黄冈）数智物流综合服务中心",
        "鄂湘赣商贸物流中心一期（通城县物流配送中心、鄂南智慧物流产业园建设、鄂南智慧物流产业园建设项目（二期）、冷链配送中心）项目",
        "咸宁国际陆港物流园-咸宁市国际陆港综合物流园（一期）", "咸宁国际陆港物流园-新建咸宁铁路物流基地项目",
        "崇阳县综合物流园及配套设施建设项目", "恩施七里坪物流园", "职业工装产业集聚区仓储物流项目",
        "极兔速递华中物流枢纽基地项目", "湖北迈睿达供应链总部研发及生产基地项目", "东楚航空冷链物流中心",
        "三峡智慧航空物流产业园", "汉江襄阳（小河）港多式联运物流园", "宏茂全球通运项目", "武汉天河综合客运枢纽",
        "广水市杨家寨公铁物流园项目---广水市杨寨铁路货场（广水市华鑫冶金物流货场）", "阳新综合客运枢纽站",
        "当阳市高铁综合客运站", "宜昌北客运站", "南漳县公铁换乘中心(郑万高铁南漳综合客运枢纽)", "京山南公铁综合客运枢纽",
        "沿江高铁汉川北站站前广场(综合客运枢纽)", "武杭高铁黄冈西综合客运枢纽", "黄黄高铁浠水综合客运枢纽",
        "郑万高铁巴东综合客运枢纽", "中通供应链湖北管理中心项目", "阳逻港铁水联运二期中欧班列集结中心",
        "西马国际（黄石）智慧物流园", "十堰市城发仓储物流中心", "枝江市顾家店仓储物流中心（一期）", "三峡建材物流园",
        "宜都市陆城综合物流运输廊道", "宜昌传化公路港二期", "武汉港航襄阳智慧物流园", "谷城县城乡智慧仓储物流产业园",
        "九州通沃田国际供应链中心项目", "湖北恒峰国际物流园", "湖北中青供应链有限公司包装配套、仓储运输项目",
        "大悟县电商物流产业园", "安陆市城乡冷链物流配送中心", "大别山智慧物流产业园", "武穴市供销社集采集配综合服务中心工程",
        "罗田县综合物流园", "黑豹国际物流园（黄冈综合园区）", "麻城市农产品综合物流园", "巴东经济开发区青龙桥智慧物流产业园",
        "鹤峰武陵山仓储物流园", "咸丰县综合物流园", "宣恩县寄递物流园", "来凤县龙凤物流园交通物流中心",
        "仙桃国家高新区物流枢纽中心建设项目", "天门市物流中心建设项目"));

    /** 经济强县项目固定名单 */
    private static final java.util.Set<String> INVEST_COUNTY_NAMES = new LinkedHashSet<>(Arrays.asList(
        "西马国际（黄石）智慧物流园", "黄石大冶湖高新区智慧冷链物流园", "大冶湖高新智慧物流港项目（城西北供应链服务中心）",
        "秭归县脐橙产业综合物流中心", "宜都供销商贸物流园", "长阳县城乡冷链物流园及其配套设施建设项目",
        "宜昌港兴山港区管公水多式联运建设项目", "枝江市顾家店仓储物流中心（一期）", "宜都市陆城综合物流运输廊道",
        "老河口市农产品仓储物流交易中心建设项目", "谷城县城乡智慧仓储物流产业园", "沙洋多式联运现代物流园",
        "大悟县电商物流产业园", "应城市智慧冷链仓储物流园", "孝昌广浔智慧物流电商产业园建设项目", "安陆市城乡冷链物流配送中心",
        "松滋市星络物流园", "公安县多式联运综合物流园", "中国供销公安商贸物流园（公安县域集采集配中心）", "同洲智慧物流园",
        "大别山智慧物流产业园", "武穴市供销社集采集配综合服务中心工程", "罗田县综合物流园", "鄂东（黄冈）数智物流综合服务中心",
        "麻城市农产品综合物流园", "鄂湘赣商贸物流中心（一期）", "嘉鱼县智慧交通物流枢纽中心",
        "崇阳县国家级综合物流枢纽及配套设施建设项目", "康华智慧物流园", "综合物流园（湖北交投随州智慧供应链产业园）",
        "巴东经济开发区青龙桥智慧物流产业园", "恩施市七里坪物流园", "鹤峰武陵山仓储物流园", "咸丰县综合物流园",
        "来凤县龙凤物流园交通物流中心", "宣恩县寄递物流园", "仙桃国家高新区物流枢纽中心建设项目",
        "职业工装产业集聚区仓储物流项目", "中国虾谷冷链仓储物流园", "天门市物流中心建设项目", "神农架物流园"));

    /** "十五五"重点物流项目固定名单 */
    private static final java.util.Set<String> INVEST_FIVE_YEAR_NAMES = new LinkedHashSet<>(Arrays.asList(
        "航发智慧物流园", "中通供应链湖北管理中心项目", "阳逻港铁水联运二期中欧班列集结中心", "极兔速递华中物流枢纽基地项目",
        "中欧班列配套多式联运中转中心项目", "新开发银行贷款长江中游（武汉）智慧物流枢纽项目铁路工业站子项",
        "黄石新港国胜智慧公路港项目", "十堰生产服务型国家物流枢纽工程项目", "十堰市城发仓储物流中心",
        "寿康永乐智能化物流设备升级改造", "十堰汽配（广场）物流中心扩能升级项目", "十堰武当山机场航空物流园项目",
        "丹江口市陈家港铁水公空联运基地", "长阳县城乡冷链物流园及其配套设施建设项目", "三峡建材物流园",
        "宜都市陆城综合物流运输廊道", "三峡智慧航空物流产业园", "当阳市坝陵化工园区综合物流园", "远安县物流中心提能升级项目",
        "宜昌市智慧物流集散中心", "宜都市枝城多式联运临港物流园", "玉湖冷链（襄阳）交易中心项目", "传化鄂西北智慧物流中心项目",
        "武汉港航襄阳智慧物流园", "唐白河港散货转运中心项目", "汉江襄阳（小河）港多式联运物流园", "襄州区大宗商品多式联运交易中心",
        "综合性供应链产业园区（中国有机谷供应链智慧产业园、谷城智慧冷链物流供应链产业园）", "南漳县现代智能物流园区",
        "湖北长江现代物流产业集聚示范区·现代物流园", "弘业现代物流产业园", "宏茂全球通运项目", "沙洋多式联运现代物流园",
        "京山市公铁物流中心", "新材料工业品智慧物流园", "长荆铁路（钟祥）公铁联运集装箱物流园", "屈家岭智慧物流园",
        "东方冷链智慧物流配送中心", "应城市智慧冷链仓储物流园", "孝昌广浔智慧物流电商产业园建设项目",
        "京汉粤大通道孝感国家综合货运枢纽", "公安县多式联运综合物流园", "荆州经开区智能仓储物流园项目",
        "中国供销公安商贸物流园（公安县域集采集配中心）", "监利市港口物流园（城区园）", "鄂东（黄冈）数智物流综合服务中心",
        "崇阳县国家级综合物流枢纽及配套设施建设项目", "咸宁高新区冻库冷链项目（一期）", "咸安区数字物流产业园及基础设施建设项目",
        "通山县特色农产品现代物流基地建设项目", "赤壁沿江公铁联运绿色物流中心项目", "咸宁城乡智慧物流园",
        "综合物流园（湖北交投随州智慧供应链产业园）", "随州综合仓储配送中心",
        "广水市杨家寨公铁物流园项目---第一期广水市杨寨铁路货场（广水市华鑫冶金物流货场）",
        "巴东经济开发区青龙桥智慧物流产业园", "鹤峰武陵山仓储物流园", "咸丰县综合物流园",
        "仙桃国家高新区物流枢纽中心建设项目", "天门市域公共综合物流园", "潜江市农产品现代物流基础设施建设项目",
        "潜江现代农业科技示范园冷链物流中心项目", "湖北潜电物流有限公司现代物流中心（一、二、三期）"));

    /** 汇总大表市州短名 -> 规范名 */
    private static final Map<String, String> INVEST_CITY_SHORT_TO_FULL = new LinkedHashMap<>();

    static {
        INVEST_CITY_SHORT_TO_FULL.put("武汉", "武汉市");
        INVEST_CITY_SHORT_TO_FULL.put("黄石", "黄石市");
        INVEST_CITY_SHORT_TO_FULL.put("十堰", "十堰市");
        INVEST_CITY_SHORT_TO_FULL.put("宜昌", "宜昌市");
        INVEST_CITY_SHORT_TO_FULL.put("襄阳", "襄阳市");
        INVEST_CITY_SHORT_TO_FULL.put("鄂州", "鄂州市");
        INVEST_CITY_SHORT_TO_FULL.put("荆门", "荆门市");
        INVEST_CITY_SHORT_TO_FULL.put("孝感", "孝感市");
        INVEST_CITY_SHORT_TO_FULL.put("荆州", "荆州市");
        INVEST_CITY_SHORT_TO_FULL.put("黄冈", "黄冈市");
        INVEST_CITY_SHORT_TO_FULL.put("咸宁", "咸宁市");
        INVEST_CITY_SHORT_TO_FULL.put("随州", "随州市");
        INVEST_CITY_SHORT_TO_FULL.put("恩施", "恩施州");
        INVEST_CITY_SHORT_TO_FULL.put("仙桃", "仙桃市");
        INVEST_CITY_SHORT_TO_FULL.put("潜江", "潜江市");
        INVEST_CITY_SHORT_TO_FULL.put("天门", "天门市");
        INVEST_CITY_SHORT_TO_FULL.put("林区", "神农架林区");
    }

    /**
     * 投资月报导入（全省汇总大表）。
     * category: 客运站场（xlsx 明细 sheet）/ 物流园区（xls 分项目投资完成情况 sheet）
     * 幂等：同报表期旧月度数据与 INVEST 审核结果先删，项目主档按 项目名+类别 upsert。
     */
    public ImportResult importInvestment(MultipartFile file, String period, String category) throws Exception {
        boolean passenger = "客运站场".equals(category);
        List<InvestRow> rows = new ArrayList<>();
        List<String> cityOrder = new ArrayList<>();
        String currentCity = null;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = pickInvestmentSheet(wb, passenger);
            // 定位表头行：包含"项目名称"且其后有数据
            int headerRow = -1;
            for (int r = 0; r <= Math.min(sheet.getLastRowNum(), 8); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String a = getString(row, 0);
                String b = getString(row, 1);
                if (b != null && b.contains("项目名称") && (a == null || a.contains("序号") || a.contains("序"))) {
                    headerRow = r;
                    break;
                }
            }
            if (headerRow < 0) {
                throw new RuntimeException("所选数据类型【" + (passenger ? "投资月报（客运站场）" : "投资月报（物流园区）") + "】与文件内容不符：未找到表头行（需包含 序号/项目名称 列），请确认是否选错了数据类型");
            }
            for (int r = headerRow + 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String a = getString(row, 0);
                String b = getString(row, 1);
                if (a == null || a.trim().isEmpty()) {
                    if (b == null || b.trim().isEmpty()) continue;
                }
                String aTrim = a == null ? "" : a.trim();
                String bTrim = b == null ? "" : b.trim();
                // 合计/标题行
                if (aTrim.contains("合计") || aTrim.contains("总计")) continue;
                // 资金类别/分组标题行（一、二、三、四、…）
                if (aTrim.matches("^[一二三四五六七八九十]+、.*")) continue;
                // 市州小节行
                String city = investmentCity(aTrim);
                if (city != null) {
                    currentCity = city;
                    if (!cityOrder.contains(city)) cityOrder.add(city);
                    continue;
                }
                // 项目行：序号为数字且项目名非空
                boolean seqNum = aTrim.matches("\\d+(\\.\\d+)?");
                if (seqNum && !bTrim.isEmpty()) {
                    InvestmentProject project = new InvestmentProject();
                    project.setProjectName(bTrim);
                    project.setCategory(category);
                    project.setCity(currentCity);
                    project.setBuilderName(getString(row, 2));
                    project.setConstructNature(getString(row, 3));
                    project.setStartTime(investTime(getString(row, 4)));
                    project.setEndTime(investTime(getString(row, 5)));
                    project.setTotalInvestment(getDecimal(row, 6));
                    project.setApprovalGk(getString(row, 18));
                    project.setApprovalCs(getString(row, 19));
                    project.setSource("全省汇总大表");
                    project.setIsBillion(INVEST_BILLION_NAMES.contains(bTrim) ? 1 : 0);
                    project.setIsCounty(INVEST_COUNTY_NAMES.contains(bTrim) ? 1 : 0);
                    project.setIsFiveYear(INVEST_FIVE_YEAR_NAMES.contains(bTrim) ? 1 : 0);

                    InvestmentMonthly monthly = new InvestmentMonthly();
                    monthly.setReportPeriod(period);
                    monthly.setYearPlan(getDecimal(row, 8));
                    monthly.setYearCum(getDecimal(row, 9));
                    monthly.setMonthDone(getDecimal(row, 10));
                    monthly.setStartCum(getDecimal(row, 7));
                    monthly.setProgressStage(getString(row, 11));
                    monthly.setProgressDesc(getString(row, 12));
                    monthly.setBuildingArea(getDecimalOrNull(row, 15));
                    rows.add(new InvestRow(r + 1, project, monthly));
                }
            }
        } catch (Exception e) {
            log.error("Investment import parse error: {}", category, e);
            throw e;
        }

        // 幂等清理：同报表期、同类别月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）
        List<InvestmentProject> sameCat = investProjectMapper.selectList(
            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
        java.util.Set<Long> catProjectIds = new HashSet<>();
        for (InvestmentProject pc : sameCat) catProjectIds.add(pc.getId());
        if (!catProjectIds.isEmpty()) {
            investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
                .eq(InvestmentMonthly::getReportPeriod, period)
                .in(InvestmentMonthly::getProjectId, catProjectIds));
        }
        List<Long> investRuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());
        }
        if (!investRuleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, investRuleIds));
        }

        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (InvestRow item : rows) {
            try {
                // 主档 upsert（按 项目名+类别）
                InvestmentProject exist = investProjectMapper.selectOne(new LambdaQueryWrapper<InvestmentProject>()
                    .eq(InvestmentProject::getProjectName, item.project.getProjectName())
                    .eq(InvestmentProject::getCategory, category)
                    .last("LIMIT 1"));
                if (exist == null) {
                    investProjectMapper.insert(item.project);
                    item.monthly.setProjectId(item.project.getId());
                } else {
                    item.project.setId(exist.getId());
                    item.project.setCreatedAt(exist.getCreatedAt());
                    investProjectMapper.updateById(item.project);
                    item.monthly.setProjectId(exist.getId());
                }
                investMonthlyMapper.insert(item.monthly);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add("第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
                log.error("Investment insert error: row {}", item.excelRow, e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch(file.getOriginalFilename(), "INVEST_" + category, period, rows.size(), success, failDetails.size(), errorDetail);
        log.info("Investment {} imported: {} rows for {}", category, success, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    // ========== 投资模块 M2：市州单表批量导入（容错解析） ==========

    /**
     * 市州单表批量导入：扫描 docs/投资/输入/{类别目录}/{X月} 下全部 xls/xlsx/et（跳过 pdf），
     * 容错解析每个文件（定位表头行 + 列名映射），upsert 项目主档 + 写入月度，
     * 并与「十五五」模板清单规范化匹配后标记 is_five_year。
     */
    public Map<String, Object> importInvestCityDir(String period, String category) throws Exception {
        boolean passenger = "客运站场".equals(category);
        String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";
        String monthDir = investMonthDir(period);
        if (monthDir.isEmpty()) throw new RuntimeException("报表期格式应为 yyyy-MM，例如 2026-07");
        File dir = resolveInvestInputDir(catDir, monthDir);
        if (dir == null || !dir.isDirectory()) {
            throw new RuntimeException("未找到市州单表目录：docs/投资/输入/" + catDir + "/" + monthDir);
        }
        Set<String> fiveYearNames = loadFiveYearProjectNames();
        List<InvestCityRow> rows = new ArrayList<>();
        List<Map<String, Object>> fileResults = new ArrayList<>();
        File[] files = dir.listFiles((d, n) -> {
            String lower = n.toLowerCase();
            return lower.endsWith(".xls") || lower.endsWith(".xlsx") || lower.endsWith(".et");
        });
        if (files == null || files.length == 0) {
            throw new RuntimeException("目录中没有可解析的报表文件（xls/xlsx/et）：" + dir.getAbsolutePath());
        }
        Arrays.sort(files, Comparator.comparing(File::getName));
        for (File f : files) {
            Map<String, Object> fr = new LinkedHashMap<>();
            fr.put("fileName", f.getName());
            try (java.io.InputStream is = new java.io.FileInputStream(f);
                 Workbook wb = WorkbookFactory.create(is)) {
                parseInvestCityWorkbook(wb, f.getName(), category, period, fiveYearNames, rows, fr);
            } catch (Exception e) {
                fr.put("success", 0);
                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
                log.error("invest city file parse error: {}", f.getName(), e);
            }
            fileResults.add(fr);
        }
        // 幂等清理：按本次文件涉及市州清理月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）
        cleanInvestMonthlyByCity(category, period, rows);
        Map<String, Set<String>> knownCities = loadInvestKnownCities(category);
        Map<String, String> fileFirstMismatch = new LinkedHashMap<>();
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (InvestCityRow item : rows) {
            String mismatch = investCityMismatchReason(item, knownCities);
            if (mismatch != null) {
                failDetails.add(mismatch);
                fileFirstMismatch.putIfAbsent(item.fileName, mismatch);
                continue;
            }
            try {
                saveInvestProjectRow(item.project, item.monthly);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
                log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);
            }
        }
        // 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到
        for (Map<String, Object> fr2 : fileResults) {
            String msg = fileFirstMismatch.get(fr2.get("fileName"));
            if (msg != null && fr2.get("error") == null) fr2.put("error", msg);
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch("市州单表批量导入（" + catDir + "/" + monthDir + "）", "INVEST_" + category + "_CITY",
            period, rows.size(), success, failDetails.size(), errorDetail);
        int fiveYearMatched = 0;
        for (InvestCityRow item : rows) {
            if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("total", rows.size());
        result.put("success", success);
        result.put("fail", failDetails.size());
        result.put("failDetails", failDetails);
        result.put("files", fileResults);
        result.put("fiveYearTemplateProjects", fiveYearNames.size());
        result.put("fiveYearMatched", fiveYearMatched);
        log.info("Invest city {} {} imported: {} rows (fiveYear matched {}/{})",
            category, period, success, fiveYearMatched, fiveYearNames.size());
        return result;
    }

    /**
     * 多文件批量导入：前端一次选择多个 Excel 上传。
     * 投资类型（investment/investmentLogistics）按市州单表解析（文件名/填报单位识别市州，十五五自动标记）；
     * 其他类型逐个走现有解析逻辑（importDirFile）。
     */
    public Map<String, Object> importBatchFiles(String type, String period, List<MultipartFile> files) throws Exception {
        if (files == null || files.isEmpty()) {
            throw new RuntimeException("未接收到任何文件");
        }
        if ("investment".equals(type) || "investmentLogistics".equals(type)) {
            String category = "investment".equals(type) ? "客运站场" : "物流园区";
            return importInvestCityFiles(period, category, files);
        }
        List<Map<String, Object>> fileResults = new ArrayList<>();
        int totalSuccess = 0, totalFail = 0;
        for (MultipartFile f : files) {
            Map<String, Object> fr = new LinkedHashMap<>();
            fr.put("fileName", f.getOriginalFilename());
            try {
                ImportResult r = importDirFile(type, f, period);
                fr.put("success", r.getSuccess());
                fr.put("fail", r.getFail());
                if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
                    fr.put("error", r.getFailDetails().get(0));
                }
                totalSuccess += r.getSuccess();
                totalFail += r.getFail();
            } catch (Exception e) {
                fr.put("success", 0);
                fr.put("fail", 0);
                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
                totalFail++;
            }
            fileResults.add(fr);
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("type", type);
        result.put("total", files.size());
        result.put("success", totalSuccess);
        result.put("fail", totalFail);
        result.put("files", fileResults);
        return result;
    }

    /** 投资多文件批量：逐文件按市州单表解析，统一幂等清理落库，批次汇总 */
    public Map<String, Object> importInvestCityFiles(String period, String category, List<MultipartFile> files) throws Exception {
        boolean passenger = "客运站场".equals(category);
        Set<String> fiveYearNames = loadFiveYearProjectNames();
        List<InvestCityRow> rows = new ArrayList<>();
        List<Map<String, Object>> fileResults = new ArrayList<>();
        for (MultipartFile f : files) {
            Map<String, Object> fr = new LinkedHashMap<>();
            fr.put("fileName", f.getOriginalFilename());
            try (Workbook wb = WorkbookFactory.create(f.getInputStream())) {
                parseInvestCityWorkbook(wb, f.getOriginalFilename(), category, period, fiveYearNames, rows, fr);
            } catch (Exception e) {
                fr.put("success", 0);
                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
                log.error("invest city file parse error: {}", f.getOriginalFilename(), e);
            }
            fileResults.add(fr);
        }
        String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";
        cleanInvestMonthlyByCity(category, period, rows);
        Map<String, Set<String>> knownCities = loadInvestKnownCities(category);
        Map<String, String> fileFirstMismatch = new LinkedHashMap<>();
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (InvestCityRow item : rows) {
            String mismatch = investCityMismatchReason(item, knownCities);
            if (mismatch != null) {
                failDetails.add(mismatch);
                fileFirstMismatch.putIfAbsent(item.fileName, mismatch);
                continue;
            }
            try {
                saveInvestProjectRow(item.project, item.monthly);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
                log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);
            }
        }
        // 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到
        for (Map<String, Object> fr2 : fileResults) {
            String msg = fileFirstMismatch.get(fr2.get("fileName"));
            if (msg != null && fr2.get("error") == null) fr2.put("error", msg);
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch("多文件批量导入（" + catDir + "/" + period + "）", "INVEST_" + category + "_CITY",
            period, rows.size(), success, failDetails.size(), errorDetail);
        int fiveYearMatched = 0;
        for (InvestCityRow item : rows) {
            if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("total", rows.size());
        result.put("success", success);
        result.put("fail", failDetails.size());
        result.put("failDetails", failDetails);
        result.put("files", fileResults);
        result.put("fiveYearTemplateProjects", fiveYearNames.size());
        result.put("fiveYearMatched", fiveYearMatched);
        log.info("Invest city multi-file {} {} imported: {} rows (fiveYear matched {}/{})",
            category, period, success, fiveYearMatched, fiveYearNames.size());
        return result;
    }

    /** 多 sheet 历史文件（襄阳 .et 等）：sheet 名含报表期优先，其次选数据行最多 */
    private boolean investSheetMatchesPeriod(Sheet sh, String period) {
        if (period == null || !period.matches("\\d{4}-\\d{2}")) return false;
        String name = sh.getSheetName();
        if (name == null || name.isEmpty()) return false;
        String norm = name.replace(" ", "");
        int month = Integer.parseInt(period.substring(5, 7));
        return norm.contains(month + "月") || norm.contains(String.format("%02d", month) + "月")
            || name.contains(period) || name.contains(period.replace("-0", "-"));
    }

    /** 解析单个市州报表：选 sheet（报表期名优先 + 数据行数），按列名映射提取项目行 */
    private void parseInvestCityWorkbook(Workbook wb, String fileName, String category, String period,
                                         Set<String> fiveYearNames, List<InvestCityRow> rows, Map<String, Object> fr) {
        Sheet best = null;
        Map<String, Integer> bestCol = null;
        int bestScore = -1;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sh = wb.getSheetAt(i);
            Map<String, Integer> col = findInvestHeader(sh);
            if (col == null) continue;
            int cnt = countInvestRows(sh, col);
            int score = cnt + (cnt > 0 && investSheetMatchesPeriod(sh, period) ? 1000000 : 0);
            if (score > bestScore) {
                best = sh;
                bestCol = col;
                bestScore = score;
            }
        }
        if (best == null) {
            fr.put("success", 0);
            fr.put("error", "未找到含「项目名称」的表头");
            return;
        }
        String city = cityFromFileName(fileName);
        if (city == null) city = cityFromSheetHeader(best);
        fr.put("city", city == null ? "" : city);
        String currentCity = city;
        Map<String, Integer> col = bestCol;
        int nameCol = col.get("name");
        boolean hasSeq = col.containsKey("seq");
        int seqCol = hasSeq ? col.get("seq") : 0;
        int parsed = 0;
        for (int r = col.get("headerRow") + 1; r <= best.getLastRowNum(); r++) {
            Row row = best.getRow(r);
            if (row == null) continue;
            String name = cellStr(row, nameCol);
            String seq = hasSeq ? cellStr(row, seqCol) : null;
            // 市州小节行：部分汇总表把市州名写在序号列（如十堰汇总表 A 列写"武汉"），需同样识别
            if (seq != null && !seq.trim().isEmpty()) {
                String seqCity = investmentCity(seq.trim());
                if (seqCity != null) {
                    currentCity = seqCity;
                    continue;
                }
            }
            if (name == null || name.trim().isEmpty()) continue;
            String nt = name.trim().replaceAll("[\r\n]+", "");
            if (nt.contains("合计") || nt.contains("总计")) continue;
            if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
            if (nt.matches("^（[一二三四五六七八九十]+）.*")) continue;
            if (nt.replace(" ", "").matches(".*小\\s*计.*")) continue;
            if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;
            if (hasSeq) {
                if (seq == null || seq.trim().isEmpty()) {
                    // 市州小节行（如 "武汉" "天 门"），更新当前市州
                    String c = investmentCity(nt);
                    if (c != null) currentCity = c;
                    continue;
                }
                if (!seq.trim().matches("\\d+(\\.\\d+)?")) continue;
            } else {
                // 无序号列（随州等）：项目名非空即项目行，市州小节行用 investmentCity 识别
                String c = investmentCity(nt);
                if (c != null) { currentCity = c; continue; }
            }
            InvestmentProject project = new InvestmentProject();
            project.setProjectName(nt);
            project.setCategory(category);
            project.setCity(currentCity);
            project.setBuilderName(cellStr(row, col.get("builder")));
            project.setConstructNature(cellStr(row, col.get("nature")));
            project.setStartTime(investTime(cellStr(row, col.get("start"))));
            project.setEndTime(investTime(cellStr(row, col.get("end"))));
            project.setTotalInvestment(cellNum(row, col.get("total")));
            project.setApprovalGk(cellStr(row, col.get("gk")));
            project.setApprovalCs(cellStr(row, col.get("cs")));
            project.setBuildingArea(cellNumOrNull(row, col.get("area")));
            project.setSource("市州单表");
            project.setIsBillion(INVEST_BILLION_NAMES.contains(nt) ? 1 : 0);
            project.setIsCounty(INVEST_COUNTY_NAMES.contains(nt) ? 1 : 0);
            project.setIsFiveYear(fiveYearNames.contains(normalizeProjectName(nt)) ? 1 : 0);
            InvestmentMonthly monthly = new InvestmentMonthly();
            monthly.setReportPeriod(period);
            monthly.setStartCum(cellNum(row, col.get("startcum")));
            monthly.setYearPlan(cellNum(row, col.get("yearplan")));
            monthly.setYearCum(cellNum(row, col.get("yearcum")));
            monthly.setMonthDone(cellNum(row, col.get("monthdone")));
            monthly.setProgressStage(cellStr(row, col.get("stage")));
            monthly.setProgressDesc(cellStr(row, col.get("desc")));
            monthly.setBuildingArea(cellNumOrNull(row, col.get("area")));
            rows.add(new InvestCityRow(r + 1, fileName, project, monthly));
            parsed++;
        }
        fr.put("city", currentCity);
        fr.put("success", parsed);
    }

    /** 定位含「项目名称」的表头行并按列名关键词建立列映射（支持双层表头合并） */
    private Map<String, Integer> findInvestHeader(Sheet sh) {
        int maxHeaderRow = Math.min(22, sh.getLastRowNum());
        for (int r = 0; r <= maxHeaderRow; r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            boolean hasName = false;
            for (int c = 0; c <= row.getLastCellNum(); c++) {
                String v = cellStr(row, c);
                if (v != null && v.contains("项目名称")) { hasName = true; break; }
            }
            if (!hasName) continue;
            Map<Integer, String> colTexts = new HashMap<>();
            for (int rr = r; rr <= Math.min(r + 3, sh.getLastRowNum()); rr++) {
                Row rr2 = sh.getRow(rr);
                if (rr2 == null) continue;
                for (int c = 0; c <= rr2.getLastCellNum(); c++) {
                    String v = cellStr(rr2, c);
                    if (v == null) continue;
                    colTexts.merge(c, v, (a, b) -> a + "|" + b);
                }
            }
            Map<String, Integer> col = new HashMap<>();
            for (Map.Entry<Integer, String> e : colTexts.entrySet()) {
                int c = e.getKey();
                String v = e.getValue();
                if (v.contains("项目名称")) col.putIfAbsent("name", c);
                else if (v.contains("投资建设") || v.contains("建设单位") || v.contains("项目业主")) col.putIfAbsent("builder", c);
                else if (v.contains("建设性质")) col.putIfAbsent("nature", c);
                else if (v.contains("开工")) col.putIfAbsent("start", c);
                else if (v.contains("竣工") || v.contains("建成")) col.putIfAbsent("end", c);
                else if ((v.contains("计划总投资") || v.contains("总投资")) && !v.contains("累计")) col.putIfAbsent("total", c);
                else if (v.contains("自开始建设")) col.putIfAbsent("startcum", c);
                else if (v.contains("本年计划")) col.putIfAbsent("yearplan", c);
                else if (v.contains("自年初累计")) col.putIfAbsent("yearcum", c);
                else if (v.contains("本月完成")) col.putIfAbsent("monthdone", c);
                else if (v.contains("建设阶段")) col.putIfAbsent("stage", c);
                else if (v.contains("形象进度")) col.putIfAbsent("desc", c);
                else if (v.contains("工可") && !v.contains("项目名称")) col.putIfAbsent("gk", c);
                else if (v.contains("初设") && !v.contains("项目名称")) col.putIfAbsent("cs", c);
                else if (v.contains("建筑面积") && !v.contains("新增")) col.putIfAbsent("area", c);
            }
            if (!col.containsKey("name")) continue;
            for (int c = 0; c <= row.getLastCellNum(); c++) {
                String v = cellStr(row, c);
                if (v != null && v.trim().matches("序\\s*号")) { col.put("seq", c); break; }
            }
            // fallback：yearcum 缺失时按 startcum+2（总投资/自开始累计/本年计划/自年初累计 的标准列序）
            if (!col.containsKey("yearcum") && col.containsKey("startcum")) {
                int yc = col.get("startcum") + 2;
                if (yc <= row.getLastCellNum()) col.put("yearcum", yc);
            }
            // monthdone 缺失时按 yearcum+1
            if (!col.containsKey("monthdone") && col.containsKey("yearcum")) {
                int md = col.get("yearcum") + 1;
                if (md <= row.getLastCellNum()) col.put("monthdone", md);
            }
            for (String k : new String[]{"builder", "nature", "start", "end", "total", "startcum",
                "yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area"}) {
                col.putIfAbsent(k, -1);
            }
            col.put("headerRow", r);
            return col;
        }
        return null;
    }

    /** 统计 sheet 内项目数据行数（用于多 sheet 历史文件选当月 sheet） */
    private int countInvestRows(Sheet sh, Map<String, Integer> col) {
        int nameCol = col.get("name");
        boolean hasSeq = col.containsKey("seq");
        int seqCol = hasSeq ? col.get("seq") : 0;
        int count = 0;
        for (int r = col.get("headerRow") + 1; r <= sh.getLastRowNum(); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            String name = cellStr(row, nameCol);
            if (name == null || name.trim().isEmpty()) continue;
            String nt = name.trim().replaceAll("[\r\n]+", "");
            if (nt.contains("合计") || nt.contains("总计")) continue;
            if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
            if (nt.replace(" ", "").matches(".*小\\s*计.*")) continue;
            if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;
            if (!hasSeq) {
                if (nt.length() > 4) count++;
            } else {
                String seq = cellStr(row, seqCol);
                if (seq != null && seq.trim().matches("\\d+(\\.\\d+)?")) count++;
            }
        }
        return count;
    }

    /** 从文件名提取市州（如 "（孝感市道运中心）…" -> 孝感市） */
    private String cityFromFileName(String fileName) {
        for (Map.Entry<String, String> e : INVEST_CITY_SHORT_TO_FULL.entrySet()) {
            if (fileName.contains(e.getKey())) return e.getValue();
        }
        return null;
    }

    /** 从 sheet 头部「填报单位：XX」行提取市州 */
    private String cityFromSheetHeader(Sheet sh) {
        for (int r = 0; r < Math.min(6, sh.getLastRowNum() + 1); r++) {
            Row row = sh.getRow(r);
            if (row == null) continue;
            for (int c = 0; c <= row.getLastCellNum(); c++) {
                String v = cellStr(row, c);
                if (v == null || !v.contains("填报单位")) continue;
                java.util.regex.Matcher m = java.util.regex.Pattern
                    .compile("填报单位[：:（(]?(盖章|签章)?[）)]?[：:]*([^\\s，,；;]{2,14})")
                    .matcher(v);
                if (m.find()) {
                    String raw = m.group(2);
                    for (Map.Entry<String, String> e : INVEST_CITY_SHORT_TO_FULL.entrySet()) {
                        if (raw.contains(e.getKey())) return e.getValue();
                    }
                    String city = investmentCity(raw);
                    if (city != null) return city;
                }
            }
        }
        return null;
    }

    /** 解析「十五五」规划物流项目模板清单，返回规范化项目名集合 */
    private Set<String> loadFiveYearProjectNames() {
        Set<String> names = new LinkedHashSet<>();
        File template = resolveInvestTemplate("模板_十五五规划物流项目进展情况.xls");
        if (template == null || !template.exists()) {
            log.warn("十五五模板不存在，is_five_year 标记跳过: {}", template);
            return names;
        }
        try (java.io.InputStream is = new java.io.FileInputStream(template);
             Workbook wb = WorkbookFactory.create(is)) {
            Sheet sh = null;
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if (wb.getSheetName(i).contains("分项目投资")) { sh = wb.getSheetAt(i); break; }
            }
            if (sh == null) sh = wb.getSheetAt(0);
            Map<String, Integer> col = findInvestHeader(sh);
            if (col == null) return names;
            int nameCol = col.get("name");
            int seqCol = col.containsKey("seq") ? col.get("seq") : 0;
            for (int r = col.get("headerRow") + 1; r <= sh.getLastRowNum(); r++) {
                Row row = sh.getRow(r);
                if (row == null) continue;
                String seq = cellStr(row, seqCol);
                String name = cellStr(row, nameCol);
                if (seq == null || name == null) continue;
                if (!seq.trim().matches("\\d+")) continue;
                names.add(normalizeProjectName(name.trim()));
            }
        } catch (Exception e) {
            log.error("load five year project names error", e);
        }
        return names;
    }

    /** 项目名规范化：去空白/换行/括号内容/标点/「项目」后缀，用于十五五清单匹配 */
    private String normalizeProjectName(String name) {
        String s = name.replaceAll("\\s+", "").replace("\n", "");
        s = s.replaceAll("[（(【\\[][^）)】\\]]*[）)】\\]]", "");
        s = s.replaceAll("[，,。.、；;：:—\\-－_/\\\\|]", "");
        s = s.replace("物流项目", "物流").replace("项目", "");
        return s;
    }

    /** 报表期 "2026-07" -> "7月" */
    private String investMonthDir(String period) {
        if (period == null || !period.matches("\\d{4}-\\d{2}")) return "";
        int month = Integer.parseInt(period.substring(5, 7));
        return month + "月";
    }

    /** 投资输入目录定位：user.dir -> user.dir/.. 逐级回退（spring-boot:run 的 user.dir 是 traffic-audit-server） */
    private File resolveInvestInputDir(String catDir, String monthDir) {
        String rel = "docs/投资/输入/" + catDir + "/" + monthDir;
        for (String baseDir : new String[]{System.getProperty("user.dir"), System.getProperty("user.dir") + "/.."}) {
            File f = new File(baseDir, rel);
            if (f.isDirectory()) return f;
        }
        return null;
    }

    private File resolveInvestTemplate(String fileName) {
        String rel = "docs/投资/模板/" + fileName;
        for (String baseDir : new String[]{System.getProperty("user.dir"), System.getProperty("user.dir") + "/.."}) {
            File f = new File(baseDir, rel);
            if (f.isFile()) return f;
        }
        return null;
    }

    /** 同类别同报表期月度数据 + INVEST 审核结果清理（幂等，全省汇总大表全量清理） */
    private void cleanInvestMonthly(String category, String period) {
        List<InvestmentProject> sameCat = investProjectMapper.selectList(
            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
        java.util.Set<Long> catProjectIds = new HashSet<>();
        for (InvestmentProject pc : sameCat) catProjectIds.add(pc.getId());
        if (!catProjectIds.isEmpty()) {
            investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
                .eq(InvestmentMonthly::getReportPeriod, period)
                .in(InvestmentMonthly::getProjectId, catProjectIds));
        }
        deleteInvestAuditResults(period, null);
    }

    /** 按市州清理：只删除本次文件涉及市州（含未识别市州的 null 项目）的月度数据 + INVEST 审核结果 */
    private void cleanInvestMonthlyByCity(String category, String period, List<InvestCityRow> rows) {
        Set<String> citySet = new HashSet<>();
        boolean hasNullCity = false;
        for (InvestCityRow item : rows) {
            String c = item.project.getCity();
            if (c == null || c.trim().isEmpty()) hasNullCity = true;
            else citySet.add(c.trim());
        }
        if (citySet.isEmpty() && !hasNullCity) return;
        boolean cleanNullCity = hasNullCity;
        LambdaQueryWrapper<InvestmentProject> pw = new LambdaQueryWrapper<InvestmentProject>()
            .eq(InvestmentProject::getCategory, category);
        pw.and(w -> {
            if (!citySet.isEmpty()) w.in(InvestmentProject::getCity, citySet);
            if (cleanNullCity) {
                if (!citySet.isEmpty()) w.or();
                w.isNull(InvestmentProject::getCity);
            }
        });
        List<InvestmentProject> cityProjects = investProjectMapper.selectList(pw);
        java.util.Set<Long> catProjectIds = new HashSet<>();
        for (InvestmentProject pc : cityProjects) catProjectIds.add(pc.getId());
        if (catProjectIds.isEmpty()) return;
        List<InvestmentMonthly> oldMonthlies = investMonthlyMapper.selectList(
            new LambdaQueryWrapper<InvestmentMonthly>()
                .eq(InvestmentMonthly::getReportPeriod, period)
                .in(InvestmentMonthly::getProjectId, catProjectIds));
        java.util.Set<Long> oldMonthlyIds = new HashSet<>();
        for (InvestmentMonthly m : oldMonthlies) oldMonthlyIds.add(m.getId());
        investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
            .eq(InvestmentMonthly::getReportPeriod, period)
            .in(InvestmentMonthly::getProjectId, catProjectIds));
        deleteInvestAuditResults(period, oldMonthlyIds);
    }

    /** 清理 INVEST 审核结果（monthlyIds 为 null 表示该期间全量清理） */
    private void deleteInvestAuditResults(String period, java.util.Set<Long> monthlyIds) {
        List<Long> investRuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());
        }
        if (investRuleIds.isEmpty()) return;
        LambdaQueryWrapper<AuditResult> w = new LambdaQueryWrapper<AuditResult>()
            .eq(AuditResult::getReportPeriod, period)
            .in(AuditResult::getRuleId, investRuleIds);
        if (monthlyIds != null) {
            if (monthlyIds.isEmpty()) return;
            w.in(AuditResult::getReportId, monthlyIds);
        }
        auditResultMapper.delete(w);
    }

    /** 项目主档 upsert（按 项目名+类别）+ 月度写入 */
    private void saveInvestProjectRow(InvestmentProject project, InvestmentMonthly monthly) {
        InvestmentProject exist = investProjectMapper.selectOne(new LambdaQueryWrapper<InvestmentProject>()
            .eq(InvestmentProject::getProjectName, project.getProjectName())
            .eq(InvestmentProject::getCategory, project.getCategory())
            .last("LIMIT 1"));
        if (exist == null) {
            investProjectMapper.insert(project);
            monthly.setProjectId(project.getId());
        } else {
            project.setId(exist.getId());
            project.setCreatedAt(exist.getCreatedAt());
            investProjectMapper.updateById(project);
            monthly.setProjectId(exist.getId());
        }
        investMonthlyMapper.insert(monthly);
    }

    /** 载入某类别「规范化项目名 -> 已入库市州集合」，用于市州一致性校验（名称在多个市州重复时放弃校验） */
    private Map<String, Set<String>> loadInvestKnownCities(String category) {
        Map<String, Set<String>> map = new HashMap<>();
        List<InvestmentProject> list = investProjectMapper.selectList(
            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
        for (InvestmentProject p : list) {
            if (p.getCity() == null || p.getCity().trim().isEmpty()) continue;
            map.computeIfAbsent(normalizeProjectName(p.getProjectName()), k -> new HashSet<>())
               .add(p.getCity().trim());
        }
        return map;
    }

    /** 市州一致性校验：库中唯一归属市州与本次解析不一致时返回错误说明，否则返回 null */
    private String investCityMismatchReason(InvestCityRow item, Map<String, Set<String>> knownCities) {
        String parsed = item.project.getCity();
        if (parsed == null || parsed.trim().isEmpty()) return null;
        Set<String> known = knownCities.get(normalizeProjectName(item.project.getProjectName()));
        if (known == null || known.size() != 1) return null;
        String knownCity = known.iterator().next();
        if (knownCity.equals(parsed.trim())) return null;
        return item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName()
            + "）：市州一致性校验未通过——库中该项目归属「" + knownCity + "」，本次文件解析为「" + parsed.trim()
            + "」，疑似市州错位，已跳过导入，请核对源文件";
    }

    private String cellStr(Row row, int idx) {
        if (idx < 0) return null;
        return getString(row, idx);
    }

    private Double cellNum(Row row, int idx) {
        if (idx < 0) return 0.0;
        return getDecimal(row, idx);
    }

    private Double cellNumOrNull(Row row, int idx) {
        if (idx < 0) return null;
        return getDecimalOrNull(row, idx);
    }

    /** 市州单表行记录：保留文件 + Excel 行号用于失败定位 */
    private static class InvestCityRow {
        final int excelRow;
        final String fileName;
        final InvestmentProject project;
        final InvestmentMonthly monthly;

        InvestCityRow(int excelRow, String fileName, InvestmentProject project, InvestmentMonthly monthly) {
            this.excelRow = excelRow;
            this.fileName = fileName;
            this.project = project;
            this.monthly = monthly;
        }
    }
    /** 投资系统导出导入（审核比对基准），固定8列：行号/单位/时期/计划总投资/自开始累计/本年计划/自年初累计/当月完成 */
    public int importInvestmentSystem(MultipartFile file, String period) throws Exception {
        List<InvestmentSystem> list = new ArrayList<>();
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "投资系统导出", "项目名称");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = getString(row, 1);
                if (name == null || name.trim().isEmpty()) continue;
                InvestmentSystem sys = new InvestmentSystem();
                sys.setReportPeriod(period);
                sys.setProjectName(name.trim());
                sys.setTotalInvestment(getDecimal(row, 3));
                sys.setStartCum(getDecimal(row, 4));
                sys.setYearPlan(getDecimal(row, 5));
                sys.setYearCum(getDecimal(row, 6));
                sys.setMonthDone(getDecimal(row, 7));
                list.add(sys);
            }
        } catch (Exception e) {
            log.error("Investment system parse error", e);
            throw e;
        }
        investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()
            .eq(InvestmentSystem::getReportPeriod, period));
        int success = 0;
        for (InvestmentSystem sys : list) {
            try {
                investSystemMapper.insert(sys);
                success++;
            } catch (Exception e) {
                failRows++;
                log.error("Investment system insert error: {}", sys.getProjectName(), e);
            }
        }
        recordBatch(file.getOriginalFilename(), "INVEST_SYSTEM", period, list.size(), success, failRows, null);
        log.info("Investment system imported: {} rows for {}", success, period);
        return success;
    }

    /** 城市公交月报导入（城市公共交通月度运营情况，企业级明细，取公共汽电车字段） */
    public ImportResult importCityBus(MultipartFile file, String period) throws Exception {
        List<CityBusRow> rows = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            Row header = sheet.getRow(0);
            if (header == null || getString(header, 2) == null
                    || !getString(header, 2).contains("企业名称")) {
                throw new RuntimeException("未找到表头行（需包含 企业名称 列）");
            }
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = getString(row, 2);
                if (name == null || name.trim().isEmpty()) continue;
                CityBusMonthly item = new CityBusMonthly();
                item.setReportPeriod(period);
                item.setRegionCode(getString(row, 0));
                item.setCity(RegionUtil.cityByCode(getString(row, 0)));
                item.setEnterpriseCode(getString(row, 1));
                item.setEnterpriseName(name.trim());
                item.setOpVehicles(getDecimal(row, 5));
                item.setStopTotal(getDecimal(row, 6));
                item.setStopCity(getDecimal(row, 7));
                item.setPassengerVolume(getDecimal(row, 8));
                item.setPassengerCity(getDecimal(row, 9));
                item.setTurnover(getDecimal(row, 10));
                item.setTurnoverCity(getDecimal(row, 11));
                item.setRailPassengerVolume(getDecimal(row, 13));
                item.setRailTurnover(getDecimal(row, 14));
                item.setFerryPassengerVolume(getDecimal(row, 17));
                item.setFerryTurnover(getDecimal(row, 18));
                item.setAvgDistance(getDecimal(row, 19));
                item.setAvgDistanceCity(getDecimal(row, 20));
                item.setContactPerson(getString(row, 24));
                item.setContactPhone(getString(row, 25));
                item.setReportDate(getDateString(row, 26));
                item.setRateOpVehiclesMom(getDecimal(row, 29));
                item.setRatePassengerMom(getDecimal(row, 30));
                item.setRatePassengerCityMom(getDecimal(row, 31));
                item.setRateChengxiangMom(getDecimal(row, 32));
                item.setRateChengxiangTurnoverMom(getDecimal(row, 33));
                item.setAvgDistanceMomChange(getDecimal(row, 34));
                item.setAvgDistanceCityMomChange(getDecimal(row, 35));
                item.setDailyPassengerPerVehicle(getDecimal(row, 36));
                item.setRateOpVehiclesYoy(getDecimal(row, 39));
                item.setRatePassengerYoy(getDecimal(row, 40));
                item.setRatePassengerCityYoy(getDecimal(row, 41));
                item.setRateChengxiangYoy(getDecimal(row, 42));
                item.setRateChengxiangTurnoverYoy(getDecimal(row, 43));
                item.setStopCityRatio(getDecimal(row, 44));
                item.setPassengerCityRatio(getDecimal(row, 45));
                item.setChengxiangRatio(getDecimal(row, 46));
                item.setRateTurnoverYoy(getDecimal(row, 47));
                item.setRateTurnoverMom(getDecimal(row, 48));
                item.setPassengerChengxiang(getDecimal(row, 49));
                item.setTurnoverChengxiang(getDecimal(row, 50));
                item.setAvgDistanceChengxiang(getDecimal(row, 51));
                item.setVerifyExplanation(getString(row, 65));
                item.setReportNote(getString(row, 66));
                item.setModifyRecord(getString(row, 67));
                rows.add(new CityBusRow(r + 1, item));
            }
        } catch (Exception e) {
            log.error("CityBus import parse error: {}", e.getMessage());
            throw e;
        }
        // 幂等：同报表期先清旧数据与 CITY_BUS 审核结果
        cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()
            .eq(CityBusMonthly::getReportPeriod, period));
        List<Long> busRuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("CITY_BUS".equals(rule.getReportType())) busRuleIds.add(rule.getId());
        }
        if (!busRuleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, busRuleIds));
        }
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (CityBusRow item : rows) {
            try {
                cityBusMapper.insert(item.entity);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);
                log.error("CityBus insert error: row {}", item.excelRow, e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch(file.getOriginalFilename(), "CITY_BUS", period, rows.size(), success, failDetails.size(), errorDetail);
        log.info("CityBus imported: {} rows for {}", success, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    /** 城市公交行记录：保留 Excel 行号用于失败定位 */
    private static class CityBusRow {
        final int excelRow;
        final CityBusMonthly entity;

        CityBusRow(int excelRow, CityBusMonthly entity) {
            this.excelRow = excelRow;
            this.entity = entity;
        }
    }

    /** 巡游出租月报导入（市州级，17 行；含核实说明/上报说明/修改记录） */
    public ImportResult importCityTaxi(MultipartFile file, String period) throws Exception {
        List<TaxiRow> rows = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            Row header = sheet.getRow(0);
            if (header == null || getString(header, 2) == null
                    || !getString(header, 2).contains("企业名称")) {
                throw new RuntimeException("未找到表头行（需包含 企业名称 列）");
            }
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String name = getString(row, 2);
                if (name == null || name.trim().isEmpty()) continue;
                CityTaxiMonthly item = new CityTaxiMonthly();
                item.setReportPeriod(period);
                item.setRegionCode(getString(row, 0));
                item.setCity(cityOf(getString(row, 0), name));
                item.setTripTotal(getDecimal(row, 4));
                item.setTripCity(getDecimal(row, 5));
                item.setPassengerVolume(getDecimal(row, 6));
                item.setPassengerCity(getDecimal(row, 7));
                item.setTurnover(getDecimal(row, 8));
                item.setTurnoverCity(getDecimal(row, 9));
                item.setOpVehicles(getDecimal(row, 10));
                item.setAvgDistance(getDecimal(row, 11));
                item.setAvgDistanceCity(getDecimal(row, 12));
                item.setContactPerson(getString(row, 15));
                item.setContactPhone(getString(row, 16));
                item.setReportDate(getDateString(row, 17));
                item.setDailyTripsPerVehicle(getDecimal(row, 18));
                item.setDailyPassengerPerVehicle(getDecimal(row, 19));
                item.setPassengersPerTrip(getDecimal(row, 20));
                item.setRateOpVehiclesMom(getDecimal(row, 21));
                item.setRateOpVehiclesYoy(getDecimal(row, 22));
                item.setRateTripMom(getDecimal(row, 23));
                item.setRateTripYoy(getDecimal(row, 24));
                item.setRatePassengerMom(getDecimal(row, 25));
                item.setRatePassengerYoy(getDecimal(row, 26));
                item.setAvgDistanceMomChange(getDecimal(row, 27));
                item.setPassengersPerTripCity(getDecimal(row, 28));
                item.setRateTripCityMom(getDecimal(row, 29));
                item.setRatePassengerCityMom(getDecimal(row, 30));
                item.setAvgDistanceCityMomChange(getDecimal(row, 31));
                item.setTripCityRatio(getDecimal(row, 32));
                item.setPassengerCityRatio(getDecimal(row, 33));
                item.setPassengersPerTripChengxiang(getDecimal(row, 34));
                item.setRateTripChengxiangMom(getDecimal(row, 35));
                item.setRatePassengerChengxiangMom(getDecimal(row, 36));
                item.setAvgDistanceChengxiang(getDecimal(row, 37));
                item.setVerifyExplanation(getString(row, 38));
                item.setReportNote(getString(row, 39));
                item.setModifyRecord(getString(row, 40));
                rows.add(new TaxiRow(r + 1, item));
            }
        } catch (Exception e) {
            log.error("CityTaxi import parse error: {}", e.getMessage());
            throw e;
        }
        // 幂等：同报表期先清旧数据与 CITY_TAXI 审核结果
        cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()
            .eq(CityTaxiMonthly::getReportPeriod, period));
        deleteAuditResults(period, "CITY_TAXI");
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (TaxiRow item : rows) {
            try {
                cityTaxiMapper.insert(item.entity);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getCity() + "）：" + reason);
                log.error("CityTaxi insert error: row {}", item.excelRow, e);
            }
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch(file.getOriginalFilename(), "CITY_TAXI", period, rows.size(), success, failDetails.size(), errorDetail);
        log.info("CityTaxi imported: {} rows for {}", success, period);
        return new ImportResult(success, failDetails.size(), failDetails);
    }

    /** 出租车运政车辆信息导入（车辆级明细，按市州归属统计用） */
    public synchronized int importCityTaxiAuth(MultipartFile file, String period) throws Exception {
        List<CityTaxiAuth> list = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "出租车运政车辆", "车牌");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                String plate = getString(row, 0);
                if (plate == null || plate.trim().isEmpty()) continue;
                CityTaxiAuth item = new CityTaxiAuth();
                item.setReportPeriod(period);
                item.setCity(taxiCity(getString(row, 9), getString(row, 8), getString(row, 4), getString(row, 18)));
                item.setPlateNo(plate.trim());
                item.setVehicleType(getString(row, 1));
                item.setBizType(getString(row, 2));
                item.setOwnerName(getString(row, 3));
                item.setLicenseNo(getString(row, 4));
                item.setFuelType(getString(row, 7));
                item.setArchiveNo(getString(row, 8));
                item.setRoadCertNo(getString(row, 9));
                item.setOperatorName(getString(row, 17));
                item.setOperatorAddr(getString(row, 18));
                item.setOpStatus(getString(row, 19));
                item.setCertStatus(getString(row, 20));
                list.add(item);
            }
        } catch (Exception e) {
            log.error("CityTaxiAuth import parse error", e);
            throw e;
        }
        // 幂等：同报表期先清旧数据（运政车辆量大，批量清理）
        cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()
            .eq(CityTaxiAuth::getReportPeriod, period));
        deleteAuditResults(period, "CITY_TAXI");
        // 批量插入（BATCH 模式）：42636 行约 20~40 秒，逐条插入需约 6 分钟会触发前端超时
        int success = 0;
        int fail = 0;
        try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            CityTaxiAuthMapper batchMapper = session.getMapper(CityTaxiAuthMapper.class);
            for (CityTaxiAuth item : list) {
                batchMapper.insert(item);
            }
            session.flushStatements();
            session.commit();
            success = list.size();
        } catch (Exception e) {
            fail = list.size();
            log.error("CityTaxiAuth batch insert error", e);
        }
        recordBatch(file.getOriginalFilename(), "CITY_TAXI_AUTH", period, list.size(), success, fail, null);
        log.info("CityTaxiAuth imported: {} rows for {}", success, period);
        return success;
    }

    /** 运政出租车市州提取：道路运输证字号→档案号→经营权号→业户地址，取 6 位区划代码 */
    private static final java.util.regex.Pattern TAXI_REGION_PAT =
        java.util.regex.Pattern.compile("(4290\\d{2}|42(?:0[1-9]|1[0-3]|28)\\d{2})");

    private String taxiCity(String... fields) {
        for (String f : fields) {
            if (f == null) continue;
            java.util.regex.Matcher m = TAXI_REGION_PAT.matcher(f);
            if (m.find()) {
                String city = RegionUtil.cityByCode(m.group(1));
                if (city != null) return city;
            }
        }
        return null;
    }

    /** 市州归属：优先按区划代码识别，其次按名称规范化 */
    private String cityOf(String regionCode, String name) {
        String city = RegionUtil.cityByCode(regionCode);
        if (city != null) return city;
        return RegionUtil.normalizeCityName(name);
    }

    /** 清除某报表类型在指定报表期的审核结果（导入幂等） */
    private void deleteAuditResults(String period, String reportType) {
        List<Long> ruleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if (reportType.equals(rule.getReportType())) ruleIds.add(rule.getId());
        }
        if (!ruleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, ruleIds));
        }
    }

    /** 巡游出租行记录：保留 Excel 行号用于失败定位 */
    private static class TaxiRow {
        final int excelRow;
        final CityTaxiMonthly entity;

        TaxiRow(int excelRow, CityTaxiMonthly entity) {
            this.excelRow = excelRow;
            this.entity = entity;
        }
    }

    private Sheet pickInvestmentSheet(Workbook wb, boolean passenger) {
        if (passenger) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                String name = wb.getSheetName(i);
                if (name != null && name.contains("明细")) return wb.getSheetAt(i);
            }
            return wb.getSheetAt(0);
        }
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            String name = wb.getSheetName(i);
            if (name != null && name.contains("分项目投资")) return wb.getSheetAt(i);
        }
        return wb.getSheetAt(0);
    }

    /** 市州小节行识别：短名/规范名 -> 规范名，非市州行返回 null */
    private String investmentCity(String a) {
        if (a == null) return null;
        String full = INVEST_CITY_SHORT_TO_FULL.get(a);
        if (full != null) return full;
        String norm = RegionUtil.normalizeCityName(a);
        if (RegionUtil.CITY_LIST.contains(norm)) return norm;
        return null;
    }

    /** 开工/建成时间：数字（2025 或 202501）或文本统一为 yyyy 或 yyyyMM */
    private String investTime(String v) {
        if (v == null || v.trim().isEmpty()) return null;
        String t = v.trim();
        if (t.matches("\\d+\\.0")) t = t.substring(0, t.length() - 2);
        if (t.matches("\\d{4}")) return t;
        if (t.matches("\\d{6}")) return t;
        if (t.matches("\\d{4}-\\d{2}-\\d{2}")) return t;
        return t;
    }

    /** 投资行记录：保留 Excel 行号用于失败定位 */
    private static class InvestRow {
        final int excelRow;
        final InvestmentProject project;
        final InvestmentMonthly monthly;

        InvestRow(int excelRow, InvestmentProject project, InvestmentMonthly monthly) {
            this.excelRow = excelRow;
            this.project = project;
            this.monthly = monthly;
        }
    }

    // ========== 通用文件夹批量导入（服务端目录浏览器） ==========

    /**
     * 目录浏览器：path 为空时返回项目 docs 数据目录下的子目录；
     * 返回当前目录绝对路径、上级目录、子目录列表及其中直接包含的 Excel 文件名/数量。
     */
    public Map<String, Object> listDirs(String path) throws Exception {
        File cur;
        if (path == null || path.trim().isEmpty()) {
            File docs = resolveDocsDir();
            if (!docs.isDirectory()) {
                throw new RuntimeException("未找到数据目录 docs（当前运行目录：" + System.getProperty("user.dir") + "）");
            }
            cur = docs;
        } else {
            cur = new File(path);
            if (!cur.isDirectory()) {
                throw new RuntimeException("目录不存在或不是文件夹：" + path);
            }
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("current", cur.getAbsolutePath());
        result.put("parent", cur.getParent() == null ? "" : cur.getParent());
        List<Map<String, Object>> dirs = new ArrayList<>();
        File[] subs = cur.listFiles(File::isDirectory);
        int dirTotal = 0;
        if (subs != null) {
            Arrays.sort(subs, Comparator.comparing(File::getName));
            for (File d : subs) {
                if (d.isHidden()) continue;
                dirTotal++;
                if (dirs.size() >= 300) continue;
                Map<String, Object> m = new LinkedHashMap<>();
                m.put("name", d.getName());
                m.put("path", d.getAbsolutePath());
                dirs.add(m);
            }
        }
        result.put("dirs", dirs);
        result.put("dirsTruncated", dirTotal > dirs.size());
        List<String> excelFiles = new ArrayList<>();
        File[] files = cur.listFiles((d, n) -> {
            String lower = n.toLowerCase();
            return lower.endsWith(".xlsx") || lower.endsWith(".xls");
        });
        int excelTotal = files == null ? 0 : files.length;
        if (files != null) {
            Arrays.sort(files, Comparator.comparing(File::getName));
            int take = Math.min(files.length, 300);
            for (int i = 0; i < take; i++) excelFiles.add(files[i].getName());
        }
        result.put("excelCount", excelTotal);
        result.put("files", excelFiles);
        result.put("filesTruncated", excelTotal > excelFiles.size());
        return result;
    }

    /** 向上查找项目 docs 数据目录（兼容从 IDEA 以 server 模块目录启动的情况） */
    private File resolveDocsDir() {
        File base = new File(System.getProperty("user.dir"));
        File cur = base;
        for (int i = 0; i < 4 && cur != null; i++) {
            File candidate = new File(cur, "docs");
            if (candidate.isDirectory()) return candidate;
            cur = cur.getParentFile();
        }
        return new File(base, "docs");
    }

    /**
     * 从服务端文件夹批量导入：只扫描 dir 直接包含的 .xlsx/.xls，
     * 按 type 逐个调用对应导入方法（与上传导入同一套解析逻辑），返回逐文件结果。
     */
    public Map<String, Object> importFromDir(String type, String period, boolean skipImported, String dir) throws Exception {
        File folder = new File(dir);
        if (!folder.isDirectory()) {
            throw new RuntimeException("目录不存在或不是文件夹：" + dir);
        }
        File[] files = folder.listFiles((d, n) -> {
            String lower = n.toLowerCase();
            return lower.endsWith(".xlsx") || lower.endsWith(".xls");
        });
        if (files == null || files.length == 0) {
            throw new RuntimeException("目录中没有 Excel 文件（.xlsx/.xls）：" + folder.getAbsolutePath());
        }
        Arrays.sort(files, Comparator.comparing(File::getName));
        List<Map<String, Object>> fileResults = new ArrayList<>();
        int totalSuccess = 0, totalFail = 0;
        for (File f : files) {
            Map<String, Object> fr = new LinkedHashMap<>();
            fr.put("fileName", f.getName());
            try {
                String filePeriod = detectPeriod(f.getName(), period);
                if (filePeriod == null || filePeriod.trim().isEmpty()) {
                    throw new RuntimeException("无法从文件名识别报表期（支持 2026年7月 / 2026.7 / 2026-07 / 7月），也未选择报表期：" + f.getName());
                }
                fr.put("period", filePeriod);
                if (skipImported && alreadyImported(filePeriod, f.getName())) {
                    fr.put("skipped", true);
                    fr.put("success", 0);
                    fr.put("fail", 0);
                    totalFail++;
                    fileResults.add(fr);
                    continue;
                }
                byte[] bytes;
                try (InputStream is = new FileInputStream(f)) {
                    bytes = IoUtil.readBytes(is);
                }
                MultipartFile mf = new ByteArrayMultipartFile(f.getName(), bytes);
                ImportResult r = importDirFile(type, mf, filePeriod);
                fr.put("success", r.getSuccess());
                fr.put("fail", r.getFail());
                if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
                    String d = r.getFailDetails().get(0);
                    if (d.length() > 300) d = d.substring(0, 300);
                    fr.put("error", d);
                }
                totalSuccess += r.getSuccess();
                totalFail += r.getFail();
            } catch (Exception e) {
                String msg = e.getMessage() == null ? e.toString() : e.getMessage();
                if (msg.length() > 300) msg = msg.substring(0, 300);
                fr.put("success", 0);
                fr.put("fail", 0);
                fr.put("error", msg);
                totalFail++;
            }
            fileResults.add(fr);
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("type", type);
        result.put("total", fileResults.size());
        result.put("success", totalSuccess);
        result.put("fail", totalFail);
        result.put("files", fileResults);
        return result;
    }

    /**
     * 按文件路径导入单个 Excel（供前端逐文件导入显示进度）。
     * 报表期优先从文件名识别（如 2026年7月 / 2026.7 / 2026-07 / 7月），识别不到才用 period。
     */
    public Map<String, Object> importDirFileByPath(String type, String period, boolean skipImported, String filePath) throws Exception {
        File f = new File(filePath);
        if (!f.isFile()) {
            throw new RuntimeException("文件不存在：" + filePath);
        }
        String filePeriod = detectPeriod(f.getName(), period);
        if (filePeriod == null || filePeriod.trim().isEmpty()) {
            throw new RuntimeException("无法从文件名识别报表期（支持 2026年7月 / 2026.7 / 2026-07 / 7月），也未选择报表期：" + f.getName());
        }
        Map<String, Object> fr = new LinkedHashMap<>();
        fr.put("fileName", f.getName());
        fr.put("period", filePeriod);
        if (skipImported && alreadyImported(filePeriod, f.getName())) {
            fr.put("skipped", true);
            fr.put("success", 0);
            fr.put("fail", 0);
            fr.put("error", "该报表期已导入过此文件，已跳过（如需覆盖请取消勾选“跳过已导入文件”）");
            return fr;
        }
        byte[] bytes;
        try (InputStream is = new FileInputStream(f)) {
            bytes = IoUtil.readBytes(is);
        }
        ImportResult r = importDirFile(type, new ByteArrayMultipartFile(f.getName(), bytes), filePeriod);
        fr.put("success", r.getSuccess());
        fr.put("fail", r.getFail());
        if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
            String d = r.getFailDetails().get(0);
            if (d.length() > 300) d = d.substring(0, 300);
            fr.put("error", d);
        }
        return fr;
    }

    /** 该报表期是否已成功导入过同名文件（用于文件夹重复导入时跳过） */
    private boolean alreadyImported(String period, String fileName) {
        if (period == null || fileName == null) return false;
        Long cnt = importBatchMapper.selectCount(new LambdaQueryWrapper<ImportBatch>()
            .eq(ImportBatch::getReportPeriod, period)
            .eq(ImportBatch::getFileName, fileName)
            .eq(ImportBatch::getFailRows, 0));
        return cnt != null && cnt > 0;
    }

    /** 从文件名识别报表期：2026年7月 / 2026年07月 / 2026.7 / 2026-07 / 202607 / 7月（无年份用 period 年份或当前年份） */
    private String detectPeriod(String fileName, String defaultPeriod) {
        if (fileName == null) return defaultPeriod;
        String name = fileName.replace("（", "(").replace("）", ")").replace(" ", "");
        java.util.regex.Matcher m;
        m = java.util.regex.Pattern.compile("(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月").matcher(name);
        if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));
        m = java.util.regex.Pattern.compile("(\\d{4})[.\\-/\\s](\\d{1,2})").matcher(name);
        if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));
        m = java.util.regex.Pattern.compile("(?:^|[^\\d])(\\d{4})(\\d{2})(?=[^\\d]|$)").matcher(name);
        if (m.find()) {
            int y = Integer.parseInt(m.group(1));
            int mo = Integer.parseInt(m.group(2));
            if (y >= 2000 && y <= 2100 && mo >= 1 && mo <= 12) {
                return String.format("%04d-%02d", y, mo);
            }
        }
        m = java.util.regex.Pattern.compile("(\\d{1,2})\\s*月").matcher(name);
        if (m.find()) {
            int mo = Integer.parseInt(m.group(1));
            if (mo >= 1 && mo <= 12) {
                return String.format("%04d-%02d", defaultYear(defaultPeriod), mo);
            }
        }
        return defaultPeriod;
    }

    private int defaultYear(String defaultPeriod) {
        if (defaultPeriod != null && defaultPeriod.matches("\\d{4}-\\d{2}")) {
            return Integer.parseInt(defaultPeriod.substring(0, 4));
        }
        return java.time.Year.now().getValue();
    }

    /** 类型分发：与上传导入同一套解析方法 */
    private ImportResult importDirFile(String type, MultipartFile file, String period) throws Exception {
        switch (type) {
            case "h2032": return importH2032(file, period);
            case "passengerMonthly": return importPassengerMonthly(file, period);
            case "passengerIndividual": return importPassengerIndividual(file, period);
            case "passengerAuth": return importPassengerAuth(file, period);
            case "energyMonthly": return importEnergyMonthly(file, period);
            case "energyAuth": return importEnergyAuth(file, period);
            case "cityBus": return importCityBus(file, period);
            case "cityTaxi": return importCityTaxi(file, period);
            case "cityTaxiAuth": return new ImportResult(importCityTaxiAuth(file, period), 0, new ArrayList<>());
            case "investment": return importInvestment(file, period, "客运站场");
            case "investmentLogistics": return importInvestment(file, period, "物流园区");
            case "investmentSystem": return new ImportResult(importInvestmentSystem(file, period), 0, new ArrayList<>());
            case "transportAuth": return new ImportResult(importTransportAuth(file, period), 0, new ArrayList<>());
            case "trackMileage": return new ImportResult(importTrackMileage(file, period), 0, new ArrayList<>());
            case "freightTurnover": return new ImportResult(importFreightTurnover(file, period), 0, new ArrayList<>());
            case "scaleSplit": return new ImportResult(importScaleSplit(file, period), 0, new ArrayList<>());
            default: throw new RuntimeException("不支持的导入类型：" + type);
        }
    }

    /** 把服务端文件包装为 MultipartFile，复用各导入方法 */
    public static class ByteArrayMultipartFile implements MultipartFile {
        private final String name;
        private final byte[] content;

        public ByteArrayMultipartFile(String name, byte[] content) {
            this.name = name;
            this.content = content;
        }

        @Override
        public String getName() { return name; }

        @Override
        public String getOriginalFilename() { return name; }

        @Override
        public String getContentType() {
            String lower = name.toLowerCase();
            return lower.endsWith(".xls")
                ? "application/vnd.ms-excel"
                : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        }

        @Override
        public boolean isEmpty() { return content == null || content.length == 0; }

        @Override
        public long getSize() { return content == null ? 0 : content.length; }

        @Override
        public byte[] getBytes() { return content; }

        @Override
        public InputStream getInputStream() { return new ByteArrayInputStream(content); }

        @Override
        public void transferTo(File dest) throws IOException {
            try (FileOutputStream out = new FileOutputStream(dest)) {
                out.write(content);
            }
        }
    }
}