package com.trafficaudit.observation.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.trafficaudit.observation.dto.ObservationDateSummary;
import com.trafficaudit.observation.entity.ObservationStationFlow;
import com.trafficaudit.observation.mapper.ObservationStationFlowMapper;
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.DateUtil;
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.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Imports the "observation station daily survey" workbook (.xls/.xlsx).
 * Header names are matched by text, so column order may change between releases.
 * Re-importing the same date replaces that date's rows.
 */
@Slf4j
@Service
public class ObservationStationImportService {

    private static final String H_DATE = "观测日期";
    private static final String H_CODE = "观测站编号";
    private static final String H_NAME = "观测站名称";
    private static final String H_MILEAGE = "观测里程";
    private static final String H_CITY = "地市名称";
    private static final String H_COUNTY = "区县名称";
    private static final String H_DIRECTION = "行驶方向";
    private static final String H_SMALL_MEDIUM_PASSENGER = "中小客流量";
    private static final String H_LARGE_PASSENGER = "大客车流量";
    private static final String H_SMALL_TRUCK = "小货车流量";
    private static final String H_MEDIUM_TRUCK = "中货车流量";
    private static final String H_LARGE_TRUCK = "大货车流量";
    private static final String H_EXTRA_LARGE_TRUCK = "特大货流量";
    private static final String H_CONTAINER = "集装箱流量";
    private static final String H_MOTORCYCLE = "摩托车流量";
    private static final String H_TRACTOR = "拖拉机流量";
    private static final String H_PASSENGER_TOTAL = "客车流量";
    private static final String H_TRUCK_TOTAL = "货车流量";
    private static final String H_CAR_TOTAL = "汽车流量";
    private static final String H_MOTOR_TOTAL = "机动车流量";
    private static final String H_PASSENGER_EQUIVALENT = "客车当量";
    private static final String H_TRUCK_EQUIVALENT = "货车当量";
    private static final String H_CAR_EQUIVALENT = "汽车当量";
    private static final String H_MOTOR_EQUIVALENT = "机动车当量";
    private static final String H_SPEED = "机动车速度";
    private static final String H_CONGESTION = "拥挤度";

    private static final int HEADER_SCAN_ROWS = 10;
    private static final int MAX_REPORTED_ERRORS = 50;

    private static final DateTimeFormatter[] DATE_FORMATS = {
            DateTimeFormatter.ofPattern("yyyy-M-d"),
            DateTimeFormatter.ofPattern("yyyy/M/d"),
            DateTimeFormatter.ofPattern("yyyy.M.d"),
            DateTimeFormatter.ofPattern("yyyyMMdd")
    };

    @Resource
    private ObservationStationFlowMapper stationFlowMapper;

    @Transactional
    public Map<String, Object> importWorkbook(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new IllegalArgumentException("请选择要导入的 Excel 文件");
        }
        String sourceFile = file.getOriginalFilename();
        List<ObservationStationFlow> rows = new ArrayList<>();
        Set<LocalDate> dates = new LinkedHashSet<>();
        Set<String> stationCodes = new LinkedHashSet<>();
        List<String> errors = new ArrayList<>();
        int skipped = 0;

        DataFormatter formatter = new DataFormatter();
        try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
            Sheet sheet = workbook.getNumberOfSheets() > 0 ? workbook.getSheetAt(0) : null;
            if (sheet == null) {
                throw new IllegalArgumentException("工作簿中没有可读取的工作表");
            }
            int headerRowIndex = findHeaderRow(sheet, formatter);
            if (headerRowIndex < 0) {
                throw new IllegalArgumentException("未找到表头，请确认文件包含\"观测日期\"和\"观测站编号\"两列");
            }
            Map<String, Integer> headerIndex = buildHeaderIndex(sheet.getRow(headerRowIndex), formatter);

            for (int r = headerRowIndex + 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    skipped++;
                    continue;
                }
                String code = stringCell(row, headerIndex, H_CODE, formatter);
                LocalDate obsDate = dateCell(row, headerIndex, H_DATE, formatter);
                if (isBlank(code) && obsDate == null) {
                    skipped++;
                    continue;
                }
                int rowNo = r + 1;
                if (obsDate == null) {
                    addError(errors, "第" + rowNo + "行：观测日期无法识别");
                    continue;
                }
                if (isBlank(code)) {
                    addError(errors, "第" + rowNo + "行：观测站编号为空");
                    continue;
                }
                ObservationStationFlow entity = new ObservationStationFlow();
                entity.setObsDate(obsDate);
                entity.setStationCode(code);
                entity.setStationName(stringCell(row, headerIndex, H_NAME, formatter));
                entity.setMileage(decimalCell(row, headerIndex, H_MILEAGE, formatter));
                entity.setCityName(stringCell(row, headerIndex, H_CITY, formatter));
                entity.setCountyName(stringCell(row, headerIndex, H_COUNTY, formatter));
                String direction = stringCell(row, headerIndex, H_DIRECTION, formatter);
                entity.setDirection(direction == null ? "" : direction);
                entity.setSmallMediumPassenger(decimalCell(row, headerIndex, H_SMALL_MEDIUM_PASSENGER, formatter));
                entity.setLargePassenger(decimalCell(row, headerIndex, H_LARGE_PASSENGER, formatter));
                entity.setSmallTruck(decimalCell(row, headerIndex, H_SMALL_TRUCK, formatter));
                entity.setMediumTruck(decimalCell(row, headerIndex, H_MEDIUM_TRUCK, formatter));
                entity.setLargeTruck(decimalCell(row, headerIndex, H_LARGE_TRUCK, formatter));
                entity.setExtraLargeTruck(decimalCell(row, headerIndex, H_EXTRA_LARGE_TRUCK, formatter));
                entity.setContainerTruck(decimalCell(row, headerIndex, H_CONTAINER, formatter));
                entity.setMotorcycle(decimalCell(row, headerIndex, H_MOTORCYCLE, formatter));
                entity.setTractor(decimalCell(row, headerIndex, H_TRACTOR, formatter));
                entity.setPassengerTotal(decimalCell(row, headerIndex, H_PASSENGER_TOTAL, formatter));
                entity.setTruckTotal(decimalCell(row, headerIndex, H_TRUCK_TOTAL, formatter));
                entity.setCarTotal(decimalCell(row, headerIndex, H_CAR_TOTAL, formatter));
                entity.setMotorTotal(decimalCell(row, headerIndex, H_MOTOR_TOTAL, formatter));
                entity.setPassengerEquivalent(decimalCell(row, headerIndex, H_PASSENGER_EQUIVALENT, formatter));
                entity.setTruckEquivalent(decimalCell(row, headerIndex, H_TRUCK_EQUIVALENT, formatter));
                entity.setCarEquivalent(decimalCell(row, headerIndex, H_CAR_EQUIVALENT, formatter));
                entity.setMotorEquivalent(decimalCell(row, headerIndex, H_MOTOR_EQUIVALENT, formatter));
                entity.setVehicleSpeed(decimalCell(row, headerIndex, H_SPEED, formatter));
                entity.setCongestionDegree(decimalCell(row, headerIndex, H_CONGESTION, formatter));
                entity.setSourceFile(sourceFile);
                rows.add(entity);
                dates.add(obsDate);
                stationCodes.add(code);
            }
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalArgumentException("工作簿解析失败: " + e.getMessage(), e);
        }

        if (!rows.isEmpty()) {
            stationFlowMapper.delete(new LambdaQueryWrapper<ObservationStationFlow>()
                    .in(ObservationStationFlow::getObsDate, dates));
            for (ObservationStationFlow row : rows) {
                stationFlowMapper.insert(row);
            }
        }

        List<String> dateTexts = new ArrayList<>();
        for (LocalDate date : dates) {
            dateTexts.add(date.toString());
        }
        BigDecimal smallMediumTotal = BigDecimal.ZERO;
        for (ObservationStationFlow row : rows) {
            if (row.getSmallMediumPassenger() != null) {
                smallMediumTotal = smallMediumTotal.add(row.getSmallMediumPassenger());
            }
        }

        Map<String, Object> summary = new LinkedHashMap<>();
        summary.put("totalRows", rows.size());
        summary.put("stationCount", stationCodes.size());
        summary.put("dates", dateTexts);
        summary.put("skippedRows", skipped);
        summary.put("smallMediumPassengerTotal", smallMediumTotal);
        summary.put("errors", errors);
        log.info("Observation station import done: file={} rows={} dates={} skipped={} failed={}",
                sourceFile, rows.size(), dateTexts, skipped, errors.size());
        return summary;
    }

    public List<ObservationDateSummary> listDateSummaries() {
        return stationFlowMapper.selectDateSummary();
    }

    public List<ObservationStationFlow> listRows(LocalDate date, int limit) {
        int effectiveLimit = limit <= 0 ? 2000 : Math.min(limit, 5000);
        return stationFlowMapper.selectList(new LambdaQueryWrapper<ObservationStationFlow>()
                .eq(date != null, ObservationStationFlow::getObsDate, date)
                .orderByAsc(ObservationStationFlow::getId)
                .last("LIMIT " + effectiveLimit));
    }

    public int deleteDate(LocalDate date) {
        if (date == null) {
            throw new IllegalArgumentException("请选择要删除的观测日期");
        }
        return stationFlowMapper.delete(new LambdaQueryWrapper<ObservationStationFlow>()
                .eq(ObservationStationFlow::getObsDate, date));
    }

    private void addError(List<String> errors, String message) {
        if (errors.size() < MAX_REPORTED_ERRORS) {
            errors.add(message);
        }
    }

    private int findHeaderRow(Sheet sheet, DataFormatter formatter) {
        int lastRow = Math.min(sheet.getLastRowNum(), HEADER_SCAN_ROWS);
        for (int r = 0; r <= lastRow; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            Map<String, Integer> index = buildHeaderIndex(row, formatter);
            if (index.containsKey(normalize(H_DATE)) && index.containsKey(normalize(H_CODE))) {
                return r;
            }
        }
        return -1;
    }

    private Map<String, Integer> buildHeaderIndex(Row row, DataFormatter formatter) {
        Map<String, Integer> index = new LinkedHashMap<>();
        if (row == null) {
            return index;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            String header = normalize(formatter.formatCellValue(cell));
            if (!header.isEmpty() && !index.containsKey(header)) {
                index.put(header, c);
            }
        }
        return index;
    }

    private String normalize(String value) {
        if (value == null) {
            return "";
        }
        StringBuilder builder = new StringBuilder(value.length());
        for (int i = 0; i < value.length(); i++) {
            char ch = value.charAt(i);
            if (ch == 0xFEFF || Character.isWhitespace(ch)) {
                continue;
            }
            builder.append(ch);
        }
        return builder.toString();
    }

    private String stringCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
        Integer column = headerIndex.get(normalize(header));
        if (column == null) {
            return null;
        }
        Cell cell = row.getCell(column);
        if (cell == null) {
            return null;
        }
        String value = formatter.formatCellValue(cell).trim();
        return value.isEmpty() ? null : value;
    }

    private LocalDate dateCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
        Integer column = headerIndex.get(normalize(header));
        if (column == null) {
            return null;
        }
        return readDate(row.getCell(column), formatter);
    }

    private BigDecimal decimalCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
        Integer column = headerIndex.get(normalize(header));
        if (column == null) {
            return null;
        }
        return readDecimal(row.getCell(column), formatter);
    }

    private LocalDate readDate(Cell cell, DataFormatter formatter) {
        if (cell == null) {
            return null;
        }
        CellType type = cell.getCellType();
        if (type == CellType.FORMULA) {
            type = cell.getCachedFormulaResultType();
        }
        if (type == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            return cell.getLocalDateTimeCellValue().toLocalDate();
        }
        String text = formatter.formatCellValue(cell).trim();
        if (text.isEmpty()) {
            return null;
        }
        text = text.replace("年", "-").replace("月", "-").replace("日", "").trim();
        for (DateTimeFormatter format : DATE_FORMATS) {
            try {
                return LocalDate.parse(text, format);
            } catch (Exception ignored) {
                // Try the next supported pattern.
            }
        }
        return null;
    }

    private BigDecimal readDecimal(Cell cell, DataFormatter formatter) {
        if (cell == null) {
            return null;
        }
        CellType type = cell.getCellType();
        if (type == CellType.FORMULA) {
            type = cell.getCachedFormulaResultType();
        }
        if (type == CellType.NUMERIC) {
            return BigDecimal.valueOf(cell.getNumericCellValue());
        }
        if (type == CellType.BLANK) {
            return null;
        }
        String text = formatter.formatCellValue(cell).trim().replace(",", "").replace(" ", "");
        if (text.isEmpty() || "-".equals(text) || "—".equals(text) || "/".equals(text)) {
            return null;
        }
        try {
            return new BigDecimal(text);
        } catch (NumberFormatException e) {
            return null;
        }
    }

    private boolean isBlank(String value) {
        return value == null || value.trim().isEmpty();
    }
}