package com.trafficaudit.holiday.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.trafficaudit.holiday.HolidayConstants;
import com.trafficaudit.holiday.HolidayNumberUtils;
import com.trafficaudit.holiday.entity.HolidayCalendar;
import com.trafficaudit.holiday.entity.HolidayPassengerCoefficient;
import com.trafficaudit.holiday.entity.HolidayPassengerFlow;
import com.trafficaudit.holiday.entity.HolidayVehicleFlow;
import com.trafficaudit.holiday.mapper.HolidayPassengerCoefficientMapper;
import com.trafficaudit.holiday.mapper.HolidayPassengerFlowMapper;
import com.trafficaudit.holiday.mapper.HolidayVehicleFlowMapper;
import lombok.Data;
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.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class HolidayWorkbookService {

    private static final Map<String, String> PASSENGER_SHEET_MODE = new LinkedHashMap<>();
    private static final int FIRST_DATA_ROW_INDEX = 2;
    private static final int[] REFERENCE_YEARS = {2024, 2025, 2026};

    static {
        PASSENGER_SHEET_MODE.put("铁路", HolidayConstants.MODE_RAIL);
        PASSENGER_SHEET_MODE.put("民航", HolidayConstants.MODE_CIVIL_AVIATION);
        PASSENGER_SHEET_MODE.put("道路", HolidayConstants.MODE_ROAD);
        PASSENGER_SHEET_MODE.put("水路", HolidayConstants.MODE_WATER);
    }

    @Resource
    private HolidayConfigService configService;
    @Resource
    private HolidayPassengerFlowMapper passengerFlowMapper;
    @Resource
    private HolidayVehicleFlowMapper vehicleFlowMapper;
    @Resource
    private HolidayPassengerCoefficientMapper coefficientMapper;

    @Value("${holiday.reference-path:docs/专项统计/重要节假日/2026年十一数据.xlsx}")
    private String referencePath;

    public File resolveReferenceFile() {
        List<File> candidates = new ArrayList<>();
        File configured = new File(referencePath);
        candidates.add(configured);
        if (!configured.isAbsolute()) {
            candidates.add(new File(System.getProperty("user.dir"), referencePath));
            candidates.add(new File(System.getProperty("user.dir"), "../" + referencePath));
        }
        for (File candidate : candidates) {
            try {
                if (candidate.exists() && candidate.isFile()) {
                    return candidate.getCanonicalFile();
                }
            } catch (Exception ignored) {
                // Keep checking the remaining candidate paths.
            }
        }
        return configured;
    }

    public String referenceFileName() {
        return resolveReferenceFile().getName();
    }

    @Transactional
    public ImportSummary importWorkbook(MultipartFile file, int year, String holidayType, boolean allowHistoricalOverwrite) {
        if (file == null || file.isEmpty()) {
            throw new IllegalArgumentException("请选择要导入的 Excel 文件");
        }
        List<HolidayCalendar> holidayDays = configService.findHolidayDays(year, holidayType);
        if (holidayDays.isEmpty()) {
            throw new IllegalArgumentException("所选年份和节假日尚未配置日期，请先在系统配置中维护节假日");
        }
        Map<Integer, LocalDate> dateByDay = new LinkedHashMap<>();
        for (HolidayCalendar day : holidayDays) {
            dateByDay.put(day.getDayIndex(), day.getHolidayDate());
        }

        if (!allowHistoricalOverwrite) {
            ImportSummary historicalSummary = detectHistoricalChanges(file, year, dateByDay);
            if (historicalSummary.isConfirmationRequired()) {
                return historicalSummary;
            }
        }

        ImportSummary summary = new ImportSummary();
        try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            importPassengerSheets(workbook, evaluator, year, dateByDay, summary);
            importVehicleSheets(workbook, evaluator, year, dateByDay, summary);
        } catch (Exception e) {
            throw new IllegalArgumentException("工作簿解析失败: " + e.getMessage(), e);
        }
        return summary;
    }

    private ImportSummary detectHistoricalChanges(MultipartFile file, int year, Map<Integer, LocalDate> dateByDay) {
        ImportSummary summary = new ImportSummary();
        try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            int latestDay = findLatestIncomingDay(workbook, evaluator, year, dateByDay);
            if (latestDay <= 1) {
                return summary;
            }
            Map<LocalDate, Map<String, BigDecimal>> existingPassenger = loadPassengerByDate(year);
            Map<LocalDate, Map<String, BigDecimal>> existingVehicle = loadVehicleByDate(year);
            detectPassengerHistory(workbook, evaluator, year, dateByDay, latestDay, existingPassenger, summary);
            detectVehicleHistory(workbook, evaluator, year, dateByDay, latestDay, existingVehicle, summary);
            if (!summary.getHistoricalChanges().isEmpty()) {
                summary.setConfirmationRequired(true);
            }
        } catch (Exception e) {
            throw new IllegalArgumentException("工作簿解析失败: " + e.getMessage(), e);
        }
        return summary;
    }

    private int findLatestIncomingDay(Workbook workbook, FormulaEvaluator evaluator, int year, Map<Integer, LocalDate> dateByDay) {
        int latest = 0;
        for (Integer dayIndex : dateByDay.keySet()) {
            if (hasPassengerValue(workbook, evaluator, year, dayIndex)
                    || hasVehicleValue(workbook, evaluator, year, dayIndex)) {
                latest = Math.max(latest, dayIndex);
            }
        }
        return latest;
    }

    private boolean hasPassengerValue(Workbook workbook, FormulaEvaluator evaluator, int year, int dayIndex) {
        for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
            Sheet sheet = workbook.getSheet(entry.getKey());
            if (sheet == null) continue;
            Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
            if (yearColumn == null) continue;
            Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
            if (row == null) continue;
            BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
            if (value != null) return true;
        }
        return false;
    }

    private boolean hasVehicleValue(Workbook workbook, FormulaEvaluator evaluator, int year, int dayIndex) {
        Sheet expressway = workbook.getSheet("高速公路");
        if (expressway != null) {
            Integer yearColumn = findYearColumn(expressway, evaluator, year, 2, 7);
            if (yearColumn != null) {
                Row row = expressway.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
                if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
            }
        }
        Sheet nationalProvincial = workbook.getSheet("普通国省道");
        if (nationalProvincial != null) {
            Integer yearColumn = findYearColumn(nationalProvincial, evaluator, year, 3, 8);
            if (yearColumn != null) {
                Row row = nationalProvincial.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
                if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
            }
        }
        return false;
    }

    private void detectPassengerHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
                                       Map<Integer, LocalDate> dateByDay, int latestDay,
                                       Map<LocalDate, Map<String, BigDecimal>> existing,
                                       ImportSummary summary) {
        for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
            Sheet sheet = workbook.getSheet(entry.getKey());
            if (sheet == null) continue;
            Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
            if (yearColumn == null) continue;
            for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
                if (day.getKey() >= latestDay) continue;
                Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
                if (row == null) continue;
                BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
                if (incoming == null) continue;
                Map<String, BigDecimal> existingDay = existing.get(day.getValue());
                BigDecimal oldValue = existingDay == null ? null : existingDay.get(entry.getValue());
                if (oldValue != null && !sameValue(oldValue, incoming)) {
                    summary.addHistoricalChange("第" + day.getKey() + "日 " + day.getValue() + " "
                            + entry.getKey() + "：原值 " + formatChangeValue(oldValue) + "，导入值 " + formatChangeValue(incoming));
                }
            }
        }
    }

    private void detectVehicleHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
                                      Map<Integer, LocalDate> dateByDay, int latestDay,
                                      Map<LocalDate, Map<String, BigDecimal>> existing,
                                      ImportSummary summary) {
        detectVehicleSheetHistory(workbook, evaluator, year, dateByDay, latestDay, existing, summary,
                "高速公路", HolidayConstants.ROAD_EXPRESSWAY, 2, 7);
        detectVehicleSheetHistory(workbook, evaluator, year, dateByDay, latestDay, existing, summary,
                "普通国省道", HolidayConstants.ROAD_NATIONAL_PROVINCIAL, 3, 8);
        detectComparisonBaseHistory(workbook, evaluator, year, dateByDay, latestDay, existing, summary);
    }

    private void detectVehicleSheetHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
                                           Map<Integer, LocalDate> dateByDay, int latestDay,
                                           Map<LocalDate, Map<String, BigDecimal>> existing,
                                           ImportSummary summary, String sheetName, String roadCategory,
                                           int startColumn, int endColumn) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) return;
        Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
        if (yearColumn == null) return;
        for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
            if (day.getKey() >= latestDay) continue;
            Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
            if (row == null) continue;
            BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
            if (incoming == null) continue;
            Map<String, BigDecimal> existingDay = existing.get(day.getValue());
            BigDecimal oldValue = existingDay == null ? null : existingDay.get(roadCategory);
            if (oldValue != null && !sameValue(oldValue, incoming)) {
                summary.addHistoricalChange("第" + day.getKey() + "日 " + day.getValue() + " "
                        + sheetName + "：原值 " + formatChangeValue(oldValue) + "，导入值 " + formatChangeValue(incoming));
            }
        }
    }

    private void detectComparisonBaseHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
                                             Map<Integer, LocalDate> dateByDay, int latestDay,
                                             Map<LocalDate, Map<String, BigDecimal>> existingVehicle,
                                             ImportSummary summary) {
        if (!HolidayConstants.supportsComparisonBase(year)) return;
        Sheet sheet = workbook.getSheet("普通国省道");
        if (sheet == null) return;
        Map<LocalDate, BigDecimal> existingBase = loadComparisonBaseByDate(year);
        for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
            if (day.getKey() >= latestDay) continue;
            Map<String, BigDecimal> existingDay = existingVehicle.get(day.getValue());
            if (existingDay == null || existingDay.get(HolidayConstants.ROAD_NATIONAL_PROVINCIAL) == null) continue;
            Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
            if (row == null) continue;
            BigDecimal incoming;
            try {
                incoming = numberValue(row.getCell(2), evaluator);
            } catch (Exception e) {
                summary.addError("普通国省道 第" + (row.getRowNum() + 1) + "行环比基期：" + e.getMessage());
                continue;
            }
            BigDecimal oldValue = existingBase.get(day.getValue());
            if (!sameNullableValue(oldValue, incoming)) {
                summary.addHistoricalChange("第" + day.getKey() + "日 " + day.getValue() + " 普通国省道环比基期：原值 "
                        + nullableChangeValue(oldValue) + "，导入值 " + nullableChangeValue(incoming));
            }
        }
    }

        Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
    private Map<LocalDate, Map<String, BigDecimal>> loadPassengerByDate(int year) {
        List<HolidayPassengerFlow> rows = passengerFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayPassengerFlow>().eq(HolidayPassengerFlow::getYear, year));
        for (HolidayPassengerFlow row : rows) {
            result.computeIfAbsent(row.getFlowDate(), key -> new LinkedHashMap<>())
                    .put(row.getTransportMode(), row.getPassengerCount());
        }
        return result;
    }

    private Map<LocalDate, Map<String, BigDecimal>> loadVehicleByDate(int year) {
        Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
        List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayVehicleFlow>().eq(HolidayVehicleFlow::getYear, year));
        for (HolidayVehicleFlow row : rows) {
            result.computeIfAbsent(row.getFlowDate(), key -> new LinkedHashMap<>())
                    .put(row.getRoadCategory(), row.getVehicleCount());
        }
        return result;
    }

    private Map<LocalDate, BigDecimal> loadComparisonBaseByDate(int year) {
        Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
        List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayVehicleFlow>()
                        .eq(HolidayVehicleFlow::getYear, year)
                        .eq(HolidayVehicleFlow::getRoadCategory, HolidayConstants.ROAD_NATIONAL_PROVINCIAL));
        for (HolidayVehicleFlow row : rows) result.put(row.getFlowDate(), row.getComparisonBase());
        return result;
    }

    private boolean sameValue(BigDecimal left, BigDecimal right) {
        return HolidayNumberUtils.sameValue(left, right);
    }

    private boolean sameNullableValue(BigDecimal left, BigDecimal right) {
        if (left == null || right == null) return left == right;
        return sameValue(left, right);
    }

    private String nullableChangeValue(BigDecimal value) {
        return value == null ? "空" : formatChangeValue(value);
    }

    private String formatChangeValue(BigDecimal value) {
        return HolidayNumberUtils.changeText(value);
    }

    @Transactional
    public void seedReferenceDataIfNeeded() {
        File reference = resolveReferenceFile();
        if (!reference.exists() || !reference.isFile()) {
            log.warn("Holiday reference workbook not found: {}", reference.getAbsolutePath());
            return;
        }
        try (InputStream in = new FileInputStream(reference); Workbook workbook = WorkbookFactory.create(in)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            seedNationalDayCalendar();
            for (int year : REFERENCE_YEARS) {
                if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
                        .eq(HolidayPassengerFlow::getYear, year)) == 0) {
                    seedPassengerYear(workbook, evaluator, year);
                }
                if (vehicleFlowMapper.selectCount(new LambdaQueryWrapper<HolidayVehicleFlow>()
                        .eq(HolidayVehicleFlow::getYear, year)) == 0) {
                    seedVehicleYear(workbook, evaluator, year);
                }
                if (coefficientMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerCoefficient>()
                        .eq(HolidayPassengerCoefficient::getYear, year)) == 0) {
                    seedCoefficientYear(workbook, evaluator, year);
                }
            }
        } catch (Exception e) {
            log.error("Failed to seed holiday reference data", e);
        }
    }

    public byte[] exportWorkbook(int year, String holidayType) {
        File reference = resolveReferenceFile();
        if (!reference.exists() || !reference.isFile()) {
            throw new IllegalArgumentException("节假日模板文件不存在: " + reference.getAbsolutePath());
        }
        try (InputStream in = new FileInputStream(reference); XSSFWorkbook workbook = new XSSFWorkbook(in)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            fillPassengerSheets(workbook, evaluator, year, holidayType);
            fillVehicleSheets(workbook, evaluator, year, holidayType);
            fillCoefficients(workbook, evaluator, year, holidayType);
            workbook.setForceFormulaRecalculation(true);
            try {
                evaluator.evaluateAll();
            } catch (Exception e) {
                log.warn("Workbook formula recalculation did not complete; Excel will recalculate on open", e);
            }
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            return out.toByteArray();
        } catch (Exception e) {
            throw new IllegalArgumentException("节假日数据导出失败: " + e.getMessage(), e);
        }
    }

    private void importPassengerSheets(Workbook workbook, FormulaEvaluator evaluator, int year,
                                       Map<Integer, LocalDate> dateByDay, ImportSummary summary) {
        for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
            Sheet sheet = workbook.getSheet(entry.getKey());
            if (sheet == null) {
                summary.addError(entry.getKey() + "：找不到工作表");
                continue;
            }
            Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
            if (yearColumn == null) {
                summary.addError(entry.getKey() + "：找不到 " + year + " 年数据列");
                continue;
            }
            for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
                Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
                if (row == null) {
                    summary.addError(entry.getKey() + " 第" + day.getKey() + "日：工作表缺少该行");
                    continue;
                }
                Integer rowDay = parseDayIndex(row.getCell(0), evaluator);
                if (rowDay == null || !rowDay.equals(day.getKey())) {
                    summary.addError(entry.getKey() + " 第" + (row.getRowNum() + 1) + "行：第几日与节假日配置不一致");
                    continue;
                }
                BigDecimal value;
                try {
                    value = numberValue(row.getCell(yearColumn), evaluator);
                } catch (Exception e) {
                    summary.addError(entry.getKey() + " 第" + (row.getRowNum() + 1) + "行：" + e.getMessage());
                    continue;
                }
                if (value == null) {
                    summary.incrementSkipped();
                    continue;
                }
                upsertPassenger(year, day.getValue(), entry.getValue(), value);
                summary.incrementPassenger();
            }
        }
    }

    private void importVehicleSheets(Workbook workbook, FormulaEvaluator evaluator, int year,
                                     Map<Integer, LocalDate> dateByDay, ImportSummary summary) {
        importVehicleSheet(workbook, evaluator, year, dateByDay, summary,
                "高速公路", HolidayConstants.ROAD_EXPRESSWAY, HolidayConstants.VEHICLE_SMALL_PASSENGER, 2, 7, null);
        importVehicleSheet(workbook, evaluator, year, dateByDay, summary,
                "普通国省道", HolidayConstants.ROAD_NATIONAL_PROVINCIAL, HolidayConstants.VEHICLE_MEDIUM_SMALL_PASSENGER, 3, 8,
                HolidayConstants.supportsComparisonBase(year) ? 2 : null);
    }

    private void importVehicleSheet(Workbook workbook, FormulaEvaluator evaluator, int year,
                                    Map<Integer, LocalDate> dateByDay, ImportSummary summary,
                                    String sheetName, String roadCategory, String vehicleType,
                                    int startColumn, int endColumn, Integer comparisonBaseColumn) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            summary.addError(sheetName + "：找不到工作表");
            return;
        }
        Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
        if (yearColumn == null) {
            summary.addError(sheetName + "：找不到 " + year + " 年数据列");
            return;
        }
        for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
            Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
            if (row == null) {
                summary.addError(sheetName + " 第" + day.getKey() + "日：工作表缺少该行");
                continue;
            }
            Integer rowDay = parseDayIndex(row.getCell(0), evaluator);
            if (rowDay == null || !rowDay.equals(day.getKey())) {
                summary.addError(sheetName + " 第" + (row.getRowNum() + 1) + "行：第几日与节假日配置不一致");
                continue;
            }
            BigDecimal value;
            try {
                value = numberValue(row.getCell(yearColumn), evaluator);
            } catch (Exception e) {
                summary.addError(sheetName + " 第" + (row.getRowNum() + 1) + "行：" + e.getMessage());
                continue;
            }
            if (value == null) {
                summary.incrementSkipped();
                continue;
            }
            BigDecimal comparisonBase = null;
            if (comparisonBaseColumn != null) {
                try {
                    comparisonBase = numberValue(row.getCell(comparisonBaseColumn), evaluator);
                } catch (Exception e) {
                    summary.addError(sheetName + " 第" + (row.getRowNum() + 1) + "行环比基期：" + e.getMessage());
                    continue;
                }
            }
            upsertVehicle(year, day.getValue(), roadCategory, vehicleType, value, comparisonBase);
            summary.incrementVehicle();
        }
    }

    private void fillPassengerSheets(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
        for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
            Sheet sheet = workbook.getSheet(entry.getKey());
            if (sheet == null) continue;
            for (int year : REFERENCE_YEARS) {
                Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
                if (yearColumn == null) continue;
                Map<Integer, LocalDate> dayDates = dayDates(year, holidayType);
                Map<LocalDate, BigDecimal> values = loadPassengerValues(year, entry.getValue());
                for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
                    if (!values.containsKey(day.getValue())) continue;
                    setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, yearColumn, values.get(day.getValue()));
                }
            }
        }
    }

    private void fillVehicleSheets(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
        fillVehicleSheet(workbook, evaluator, "高速公路", HolidayConstants.ROAD_EXPRESSWAY, 2, 7, holidayType, null);
        fillVehicleSheet(workbook, evaluator, "普通国省道", HolidayConstants.ROAD_NATIONAL_PROVINCIAL, 3, 8, holidayType,
                HolidayConstants.supportsComparisonBase(selectedYear) ? 2 : null);
    }

    private void fillVehicleSheet(XSSFWorkbook workbook, FormulaEvaluator evaluator, String sheetName,
                                  String roadCategory, int startColumn, int endColumn, String holidayType,
                                  Integer comparisonBaseColumn) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) return;
        for (int year : REFERENCE_YEARS) {
            Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
            if (yearColumn == null) continue;
            Map<Integer, LocalDate> dayDates = dayDates(year, holidayType);
            Map<LocalDate, BigDecimal> values = loadVehicleValues(year, roadCategory);
            for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
                if (!values.containsKey(day.getValue())) continue;
                setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, yearColumn, values.get(day.getValue()));
            }
            if (comparisonBaseColumn != null && HolidayConstants.supportsComparisonBase(year)) {
                Map<LocalDate, BigDecimal> bases = loadComparisonBaseValues(year);
                for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
                    if (!bases.containsKey(day.getValue())) continue;
                    setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, comparisonBaseColumn, bases.get(day.getValue()));
                }
                for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
                    if (!bases.containsKey(day.getValue())) continue;
                    int rowIndex = FIRST_DATA_ROW_INDEX + day.getKey() - 1;
                    int excelRow = rowIndex + 1;
                    setFormula(sheet, rowIndex, 4, "=(D" + excelRow + "-C" + excelRow + ")/C" + excelRow);
                }
            }
        }
    }

    private void fillCoefficients(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
        fillCoefficientSheet(workbook, evaluator, "高速公路", 8, 10, holidayType);
        fillCoefficientSheet(workbook, evaluator, "普通国省道", 9, 11, holidayType);
    }

    private void fillCoefficientSheet(XSSFWorkbook workbook, FormulaEvaluator evaluator, String sheetName,
                                      int startColumn, int endColumn, String holidayType) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) return;
        for (int year : REFERENCE_YEARS) {
            Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
            if (yearColumn == null) continue;
            BigDecimal coefficient = configService.coefficientOf(year, HolidayConstants.CATEGORY_HOLIDAY);
            if (coefficient == null) continue;
            Map<Integer, LocalDate> dayDates = dayDates(year, holidayType);
            for (Integer dayIndex : dayDates.keySet()) {
                setNumeric(sheet, FIRST_DATA_ROW_INDEX + dayIndex - 1, yearColumn, coefficient);
            }
        }
    }

    private void seedNationalDayCalendar() {
        // Calendar is admin-managed after the first bootstrap. Re-adding missing day indexes on
        // every start would resurrect days the admin deleted, so only seed a fresh table.
        if (!configService.listHolidays(null, null).isEmpty()) {
            return;
        }
        int[] lengths = {7, 8, 8};
        for (int i = 0; i < REFERENCE_YEARS.length; i++) {
            int year = REFERENCE_YEARS[i];
            List<HolidayCalendar> existing = configService.findHolidayDays(year, HolidayConstants.TYPE_NATIONAL_DAY);
            Map<Integer, HolidayCalendar> byDay = new LinkedHashMap<>();
            for (HolidayCalendar row : existing) {
                byDay.put(row.getDayIndex(), row);
            }
            for (int day = 1; day <= lengths[i]; day++) {
                if (byDay.containsKey(day)) continue;
                HolidayCalendar calendar = new HolidayCalendar();
                calendar.setYear(year);
                calendar.setHolidayType(HolidayConstants.TYPE_NATIONAL_DAY);
                calendar.setHolidayDate(LocalDate.of(year, 10, day));
                calendar.setDayIndex(day);
                calendar.setCreatedAt(LocalDateTime.now());
                calendar.setUpdatedAt(LocalDateTime.now());
                configService.saveHoliday(toMap(calendar));
            }
        }
    }

    private void seedPassengerYear(Workbook workbook, FormulaEvaluator evaluator, int year) {
        Map<Integer, LocalDate> dayDates = dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY);
        if (dayDates.isEmpty()) return;
        for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
            Sheet sheet = workbook.getSheet(entry.getKey());
            if (sheet == null) continue;
            Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
            if (yearColumn == null) continue;
            for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
                Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
                if (row == null) continue;
                BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
                if (value != null) {
                    upsertPassenger(year, day.getValue(), entry.getValue(), value);
                }
            }
        }
    }

    private void seedVehicleYear(Workbook workbook, FormulaEvaluator evaluator, int year) {
        Map<Integer, LocalDate> dayDates = dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY);
        if (dayDates.isEmpty()) return;
        seedVehicleSheet(workbook, evaluator, year, dayDates, "高速公路",
                HolidayConstants.ROAD_EXPRESSWAY, HolidayConstants.VEHICLE_SMALL_PASSENGER, 2, 7);
        seedVehicleSheet(workbook, evaluator, year, dayDates, "普通国省道",
                HolidayConstants.ROAD_NATIONAL_PROVINCIAL, HolidayConstants.VEHICLE_MEDIUM_SMALL_PASSENGER, 3, 8);
    }

    private void seedVehicleSheet(Workbook workbook, FormulaEvaluator evaluator, int year,
                                  Map<Integer, LocalDate> dayDates, String sheetName,
                                  String roadCategory, String vehicleType, int startColumn, int endColumn) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) return;
        Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
        if (yearColumn == null) return;
        for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
            Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
            if (row == null) continue;
            BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
            if (value != null) {
                upsertVehicle(year, day.getValue(), roadCategory, vehicleType, value, null);
            }
        }
    }

    private void seedCoefficientYear(Workbook workbook, FormulaEvaluator evaluator, int year) {
        List<BigDecimal> values = new ArrayList<>();
        collectCoefficientValues(workbook, evaluator, "高速公路", year, 8, 10, values);
        collectCoefficientValues(workbook, evaluator, "普通国省道", year, 9, 11, values);
        if (values.isEmpty()) return;
        BigDecimal sum = BigDecimal.ZERO;
        for (BigDecimal value : values) {
            sum = HolidayNumberUtils.normalize(sum.add(value));
        }
        BigDecimal coefficient = HolidayNumberUtils.divide(sum, BigDecimal.valueOf(values.size()));
        upsertCoefficient(year, HolidayConstants.CATEGORY_HOLIDAY, coefficient);
        upsertCoefficient(year, HolidayConstants.CATEGORY_NON_HOLIDAY, coefficient);
    }

    private void collectCoefficientValues(Workbook workbook, FormulaEvaluator evaluator, String sheetName,
                                          int year, int startColumn, int endColumn, List<BigDecimal> values) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) return;
        Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
        if (yearColumn == null) return;
        for (int rowIndex = FIRST_DATA_ROW_INDEX; rowIndex < FIRST_DATA_ROW_INDEX + 10; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;
            try {
                BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
                if (value != null && value.compareTo(BigDecimal.ZERO) > 0) values.add(value);
            } catch (Exception ignored) {
                // Skip cells that are not numeric coefficients.
            }
        }
    }

    private Map<Integer, LocalDate> dayDates(int year, String holidayType) {
        Map<Integer, LocalDate> result = new LinkedHashMap<>();
        for (HolidayCalendar row : configService.findHolidayDays(year, holidayType)) {
            result.put(row.getDayIndex(), row.getHolidayDate());
        }
        return result;
    }

    private Map<LocalDate, BigDecimal> loadPassengerValues(int year, String transportMode) {
        Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
        List<HolidayPassengerFlow> rows = passengerFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayPassengerFlow>()
                        .eq(HolidayPassengerFlow::getYear, year)
                        .eq(HolidayPassengerFlow::getTransportMode, transportMode));
        for (HolidayPassengerFlow row : rows) result.put(row.getFlowDate(), row.getPassengerCount());
        return result;
    }

    private Map<LocalDate, BigDecimal> loadVehicleValues(int year, String roadCategory) {
        Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
        List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayVehicleFlow>()
                        .eq(HolidayVehicleFlow::getYear, year)
                        .eq(HolidayVehicleFlow::getRoadCategory, roadCategory));
        for (HolidayVehicleFlow row : rows) result.put(row.getFlowDate(), row.getVehicleCount());
        return result;
    }

    private Map<LocalDate, BigDecimal> loadComparisonBaseValues(int year) {
        Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
        List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayVehicleFlow>()
                        .eq(HolidayVehicleFlow::getYear, year)
                        .eq(HolidayVehicleFlow::getRoadCategory, HolidayConstants.ROAD_NATIONAL_PROVINCIAL));
        for (HolidayVehicleFlow row : rows) {
            if (row.getComparisonBase() != null) result.put(row.getFlowDate(), row.getComparisonBase());
        }
        return result;
    }

    private void upsertPassenger(int year, LocalDate date, String mode, BigDecimal value) {
        HolidayPassengerFlow existing = passengerFlowMapper.selectOne(new LambdaQueryWrapper<HolidayPassengerFlow>()
                .eq(HolidayPassengerFlow::getYear, year)
                .eq(HolidayPassengerFlow::getFlowDate, date)
                .eq(HolidayPassengerFlow::getTransportMode, mode)
                .last("limit 1"));
        if (existing == null) {
            existing = new HolidayPassengerFlow();
            existing.setYear(year);
            existing.setFlowDate(date);
            existing.setTransportMode(mode);
            existing.setPassengerCount(value);
            existing.setCreatedAt(LocalDateTime.now());
            existing.setUpdatedAt(LocalDateTime.now());
            passengerFlowMapper.insert(existing);
        } else {
            existing.setPassengerCount(value);
            existing.setUpdatedAt(LocalDateTime.now());
            passengerFlowMapper.updateById(existing);
        }
    }

    private void upsertVehicle(int year, LocalDate date, String roadCategory, String vehicleType,
                               BigDecimal value, BigDecimal comparisonBase) {
        HolidayVehicleFlow existing = vehicleFlowMapper.selectOne(new LambdaQueryWrapper<HolidayVehicleFlow>()
                .eq(HolidayVehicleFlow::getYear, year)
                .eq(HolidayVehicleFlow::getFlowDate, date)
                .eq(HolidayVehicleFlow::getRoadCategory, roadCategory)
                .eq(HolidayVehicleFlow::getVehicleType, vehicleType)
                .last("limit 1"));
        if (existing == null) {
            existing = new HolidayVehicleFlow();
            existing.setYear(year);
            existing.setFlowDate(date);
            existing.setRoadCategory(roadCategory);
            existing.setVehicleType(vehicleType);
            existing.setVehicleCount(value);
            existing.setComparisonBase(comparisonBase);
            existing.setCreatedAt(LocalDateTime.now());
            existing.setUpdatedAt(LocalDateTime.now());
            vehicleFlowMapper.insert(existing);
        } else {
            existing.setVehicleCount(value);
            existing.setComparisonBase(comparisonBase);
            existing.setUpdatedAt(LocalDateTime.now());
            vehicleFlowMapper.updateById(existing);
        }
    }

    private void upsertCoefficient(int year, String dateCategory, BigDecimal value) {
        HolidayPassengerCoefficient existing = coefficientMapper.selectOne(
                new LambdaQueryWrapper<HolidayPassengerCoefficient>()
                        .eq(HolidayPassengerCoefficient::getYear, year)
                        .eq(HolidayPassengerCoefficient::getDateCategory, dateCategory)
                        .last("limit 1"));
        if (existing == null) {
            existing = new HolidayPassengerCoefficient();
            existing.setYear(year);
            existing.setDateCategory(dateCategory);
            existing.setCoefficient(value);
            existing.setCreatedAt(LocalDateTime.now());
            existing.setUpdatedAt(LocalDateTime.now());
            coefficientMapper.insert(existing);
        } else {
            existing.setCoefficient(value);
            existing.setUpdatedAt(LocalDateTime.now());
            coefficientMapper.updateById(existing);
        }
    }

    private Integer findYearColumn(Sheet sheet, FormulaEvaluator evaluator, int year, int startColumn, int endColumn) {
        Row header = sheet.getRow(1);
        if (header == null) return null;
        String wanted = year + "年";
        DataFormatter formatter = new DataFormatter();
        for (int column = startColumn; column <= endColumn; column++) {
            Cell cell = header.getCell(column);
            if (cell == null) continue;
            String text = formatter.formatCellValue(cell, evaluator).replace("\n", "").trim();
            if (wanted.equals(text) || text.startsWith(wanted + "累计")) return column;
        }
        return null;
    }

    private Integer parseDayIndex(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) return null;
        DataFormatter formatter = new DataFormatter();
        String text = formatter.formatCellValue(cell, evaluator).trim();
        java.util.regex.Matcher matcher = java.util.regex.Pattern.compile("第\\s*(\\d+)\\s*日").matcher(text);
        return matcher.find() ? Integer.valueOf(matcher.group(1)) : null;
    }

    private BigDecimal numberValue(Cell cell, FormulaEvaluator evaluator) {
        if (cell == null) return null;
        CellType type = cell.getCellType();
        if (type == CellType.FORMULA) {
            type = evaluator.evaluateFormulaCell(cell);
        }
        if (type == CellType.BLANK) return null;
        if (type == CellType.NUMERIC) return HolidayNumberUtils.excelValue(cell.getNumericCellValue());
        if (type == CellType.STRING) {
            String text = cell.getStringCellValue().trim().replace(",", "");
            if (text.isEmpty() || "-".equals(text) || "—".equals(text)) return null;
            try {
                return HolidayNumberUtils.decimalValue(text);
            } catch (NumberFormatException e) {
                throw new IllegalArgumentException("单元格不是有效数字: " + text);
            }
        }
        if (type == CellType.BOOLEAN) throw new IllegalArgumentException("单元格不能是布尔值");
        throw new IllegalArgumentException("单元格不是有效数字");
    }

    private void setNumeric(Sheet sheet, int rowIndex, int columnIndex, BigDecimal value) {
        if (value == null) return;
        Row row = sheet.getRow(rowIndex);
        if (row == null) row = sheet.createRow(rowIndex);
        Cell cell = row.getCell(columnIndex);
        if (cell == null) cell = row.createCell(columnIndex);
        cell.setCellValue(value.doubleValue());
    }

    private void setFormula(Sheet sheet, int rowIndex, int columnIndex, String formula) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) row = sheet.createRow(rowIndex);
        Cell cell = row.getCell(columnIndex);
        if (cell == null) cell = row.createCell(columnIndex);
        cell.setCellFormula(formula);
    }

    private Map<String, Object> toMap(HolidayCalendar calendar) {
        Map<String, Object> params = new LinkedHashMap<>();
        params.put("year", calendar.getYear());
        params.put("holidayType", calendar.getHolidayType());
        params.put("holidayDate", calendar.getHolidayDate().toString());
        params.put("dayIndex", calendar.getDayIndex());
        return params;
    }

    @Data
    public static class ImportSummary {
        private int passengerRows;
        private int vehicleRows;
        private int skippedRows;
        private boolean confirmationRequired;
        private final List<String> errors = new ArrayList<>();
        private final List<String> historicalChanges = new ArrayList<>();

        public void incrementPassenger() {
            passengerRows++;
        }

        public void incrementVehicle() {
            vehicleRows++;
        }

        public void incrementSkipped() {
            skippedRows++;
        }

        public void addError(String message) {
            errors.add(message);
        }

        public void addHistoricalChange(String message) {
            if (historicalChanges.size() < 200) {
                historicalChanges.add(message);
            }
        }

        public int getSuccessRows() {
            return passengerRows + vehicleRows;
        }

        public int getFailRows() {
            return errors.size();
        }
    }
}