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.FreightTurnoverImport;
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
import com.trafficaudit.dataimport.entity.ImportBatch;
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
import com.trafficaudit.dataimport.entity.TransportAuthVehicle;
import com.trafficaudit.dataimport.entity.VehicleTrackMileage;
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
import com.trafficaudit.dataimport.mapper.ImportBatchMapper;
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.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 org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
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.text.SimpleDateFormat;

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

    private static final DataFormatter FORMATTER = new DataFormatter();

    @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;

    // ========== 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);
            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;
        }

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

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


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

    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);
            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);
            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())) {
            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);
            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 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 String getString(Row row, int idx) {
        Cell cell = row.getCell(idx);
        if (cell == null) return null;
        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();
        }
        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();
        }
        String val = FORMATTER.formatCellValue(cell).trim();
        if (val.isEmpty()) return null;
        try {
            return Double.parseDouble(val.replace(",", ""));
        } catch (NumberFormatException e) {
            return null;
        }
    }
}