package com.rj.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.rj.entity.LbDailyUserTrade;
import com.rj.entity.LbDailyUserTradeReport;
import com.rj.mapper.LbDailyUserTradeMapper;
import com.rj.service.ILbDailyUserTradeService;
import com.rj.service.ILbDailyUserTradeReportService;
import org.apache.poi.poifs.filesystem.FileMagic;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.web.multipart.MultipartFile;

import com.rj.tenant.TenantContextHolder;

import java.io.ByteArrayInputStream;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.locks.ReentrantLock;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * <p>
 * 当天用户交易表 服务实现类
 * </p>
 */
@Service
public class LbDailyUserTradeServiceImpl extends ServiceImpl<LbDailyUserTradeMapper, LbDailyUserTrade> implements ILbDailyUserTradeService {
    private static final Logger log = LoggerFactory.getLogger(LbDailyUserTradeServiceImpl.class);
    private static final BigDecimal SERVICE_RATE = new BigDecimal("0.01");
    /**
     * Each {@code saveBatch(chunk)} holds one JDBC connection until that chunk commits (BatchExecutor).
     * Keep this small so remote/slow DB stays under Hikari {@code leakDetectionThreshold} (borrow time).
     */
    private static final int EXCEL_IMPORT_SAVE_CHUNK_SIZE = 100;
    /** JDBC batch flush size inside MyBatis-Plus for each chunk (must be positive). */
    private static final int EXCEL_IMPORT_INNER_BATCH_SIZE = 50;
    /** 与表 lb_daily_user_trade 中 VARCHAR 长度一致，避免超长导致批量插入整批失败或行为异常 */
    private static final int DB_TENANT_ID_MAX = 64;
    private static final int DB_USER_ID_MAX = 64;
    private static final int DB_NICKNAME_MAX = 100;
    private static final int DB_PROMOTER_ID_MAX = 64;
    private static final int DB_PROMOTER_NAME_MAX = 100;
    private static final int DB_DESC_MAX = 512;
    private static final int ID_IN_QUERY_BATCH = 500;
    /** 同一租户同时只允许一个 Excel 导入，避免并发请求或连点导致重复插入 */
    private static final ConcurrentHashMap<String, ReentrantLock> TENANT_LB_DAILY_TRADE_IMPORT_LOCKS = new ConcurrentHashMap<>();

    @Autowired
    private ILbDailyUserTradeReportService lbDailyUserTradeReportService;

    @Autowired
    private PlatformTransactionManager transactionManager;

    @Override
    public Map<String, Object> calculateDailyTradeReport(LocalDate reportDate, String tenantId) {
        Map<String, Object> result = new HashMap<>();
        if (reportDate == null) {
            result.put("success", false);
            result.put("message", "reportDate不能为空");
            return result;
        }
        if (StringUtils.isBlank(tenantId)) {
            result.put("success", false);
            result.put("message", "tenantId不能为空");
            return result;
        }

        String normalizedTenantId = tenantId.trim();
        LambdaQueryWrapper<LbDailyUserTrade> currentDayWrapper = new LambdaQueryWrapper<>();
        currentDayWrapper.eq(LbDailyUserTrade::getTenantId, normalizedTenantId)
                .eq(LbDailyUserTrade::getReportDate, reportDate)
                .and(wrapper -> wrapper
                        .ne(LbDailyUserTrade::getDailySellAmt, BigDecimal.ZERO)
                        .or()
                        .ne(LbDailyUserTrade::getDailyBuyAmt, BigDecimal.ZERO)
                        .or()
                        .ne(LbDailyUserTrade::getDiffAmt, BigDecimal.ZERO));

        List<LbDailyUserTrade> currentDayTrades = this.list(currentDayWrapper);
        if (currentDayTrades.isEmpty()) {
            result.put("success", true);
            result.put("message", "未匹配到可计算数据");
            result.put("insertCount", 0);
            return result;
        }

        LocalDate yesterday = reportDate.minusDays(1);
        LocalDateTime now = LocalDateTime.now();
        List<LbDailyUserTradeReport> reportList = new ArrayList<>();

        for (LbDailyUserTrade trade : currentDayTrades) {
            BigDecimal dailySellAmt = trade.getDailySellAmt() == null ? BigDecimal.ZERO : trade.getDailySellAmt();
            BigDecimal dailyBuyAmt = trade.getDailyBuyAmt() == null ? BigDecimal.ZERO : trade.getDailyBuyAmt();
            BigDecimal diffAmt = trade.getDiffAmt() == null ? BigDecimal.ZERO : trade.getDiffAmt();

            LambdaQueryWrapper<LbDailyUserTrade> yesterdayWrapper = new LambdaQueryWrapper<>();
            yesterdayWrapper.eq(LbDailyUserTrade::getTenantId, normalizedTenantId)
                    .eq(LbDailyUserTrade::getReportDate, yesterday);
            if (StringUtils.isNotBlank(trade.getUserId())) {
                // 以 userId 作为昨日数据关联主键，避免昵称重复/变更导致错配。
                yesterdayWrapper.eq(LbDailyUserTrade::getUserId, trade.getUserId().trim());
            } else if (StringUtils.isNotBlank(trade.getNickname())) {
                // 历史脏数据兜底：仅当 userId 缺失时才按昵称匹配。
                yesterdayWrapper.eq(LbDailyUserTrade::getNickname, trade.getNickname().trim());
            }
            LbDailyUserTrade lastDateRecord = null;
            if (StringUtils.isNotBlank(trade.getUserId()) || StringUtils.isNotBlank(trade.getNickname())) {
                yesterdayWrapper
                        .orderByDesc(LbDailyUserTrade::getUpdatedAt)
                        .last("limit 1");
                lastDateRecord = this.getOne(yesterdayWrapper, false);
            }
            BigDecimal yestodayBuyAmt = lastDateRecord == null || lastDateRecord.getDailyBuyAmt() == null
                    ? BigDecimal.ZERO
                    : lastDateRecord.getDailyBuyAmt();

            LbDailyUserTradeReport report = new LbDailyUserTradeReport();
            report.setId(UUID.randomUUID().toString());
            report.setTenantId(normalizedTenantId);
            report.setUserId(trade.getUserId());
            report.setNickname(trade.getNickname());
            report.setYestodayBuyAmt(yestodayBuyAmt);
            report.setDailySellAmt(dailySellAmt);
            report.setDailyBuyAmt(dailyBuyAmt);
            report.setServiceAmt(dailyBuyAmt.multiply(SERVICE_RATE));
            report.setDiffAmt(diffAmt);
            report.setActualReceiptsPayments(diffAmt.subtract(report.getServiceAmt()));
            report.setReportDate(reportDate.atStartOfDay());
            report.setDataType("report_detail");
            report.setCreatedAt(now);
            report.setUpdatedAt(now);
            reportList.add(report);
        }
        // 保存前先删除同租户同报表日期的历史报表，避免重复插入
        LambdaQueryWrapper<LbDailyUserTradeReport> reportDeleteWrapper = new LambdaQueryWrapper<>();
        reportDeleteWrapper.eq(LbDailyUserTradeReport::getTenantId, normalizedTenantId)
                .eq(LbDailyUserTradeReport::getReportDate, reportDate.atStartOfDay());
        lbDailyUserTradeReportService.remove(reportDeleteWrapper);

        boolean saved = lbDailyUserTradeReportService.saveBatch(reportList);
        if (!saved) {
            result.put("success", false);
            result.put("message", "报表插入失败");
            return result;
        }

        result.put("success", true);
        result.put("message", "计算并写入报表成功");
        result.put("insertCount", reportList.size());
        return result;
    }

    @Override
    public Map<String, Object> importFromExcel(MultipartFile file, String defaultReportDate, String tenantId) {
        Map<String, Object> result = new HashMap<>();
        if (file == null || file.isEmpty()) {
            result.put("success", false);
            result.put("message", "上传文件不能为空");
            return result;
        }
        if (StringUtils.isBlank(tenantId)) {
            result.put("success", false);
            result.put("message", "tenantId不能为空");
            return result;
        }
        String normalizedTenantId = tenantId.trim();
        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null || !isSupportedExcelSuffix(originalFilename)) {
            result.put("success", false);
            result.put("message", "仅支持 .xlsx 或 .xls 文件");
            return result;
        }

        ReentrantLock tenantImportLock = TENANT_LB_DAILY_TRADE_IMPORT_LOCKS.computeIfAbsent(
                normalizedTenantId, k -> new ReentrantLock());
        if (!tenantImportLock.tryLock()) {
            result.put("success", false);
            result.put("message", "该租户已有导入任务正在执行，请等待完成后再试，避免重复写入数据");
            return result;
        }
        String previousTenantId = TenantContextHolder.getTenantId();
        TenantContextHolder.setTenantId(normalizedTenantId);
        try {
        LocalDate fallbackReportDate = null;
        if (StringUtils.isNotBlank(defaultReportDate)) {
            try {
                fallbackReportDate = LocalDate.parse(defaultReportDate.trim());
            } catch (Exception e) {
                result.put("success", false);
                result.put("message", "defaultReportDate 格式错误，请使用 yyyy-MM-dd");
                return result;
            }
        } else {
            // 未传导入日期时，尝试从文件名中提取 yyyy-MM-dd 作为导入日期
            fallbackReportDate = extractDateFromFilename(originalFilename);
            if (fallbackReportDate != null) {
                log.info("未提供 defaultReportDate，已从文件名解析报表日期，fileName={}, reportDate={}",
                        originalFilename, fallbackReportDate);
            } else {
                log.info("未提供 defaultReportDate，且文件名未包含日期，将使用当前日期作为报表日期，fileName={}", originalFilename);
            }
        }

        List<LbDailyUserTrade> importList = new ArrayList<>();
        List<String> skippedRows = new ArrayList<>();
        int skippedHiddenRows = 0;
        /** 表头以下：非空行且非隐藏行，即参与解析的 Excel 行数（含解析失败/跳过的行） */
        int excelTableRowCount = 0;
        DataFormatter dataFormatter = new DataFormatter();
        log.info("开始导入Excel，fileName={}, size={} bytes, defaultReportDate={}",
                originalFilename, file.getSize(), defaultReportDate);
        try (Workbook workbook = openWorkbook(file)) {
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                result.put("success", false);
                result.put("message", "Excel内容为空");
                return result;
            }

            int headerRowNum = findHeaderRow(sheet, dataFormatter);
            if (headerRowNum < 0) {
                result.put("success", false);
                result.put("message", "未识别到表头，请确认模板至少包含：用户ID、单日卖出金额、当日买入金额等字段");
                return result;
            }

            Map<String, Integer> headerIndexMap = buildHeaderIndexMap(sheet.getRow(headerRowNum), dataFormatter);
            for (int i = headerRowNum + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row, dataFormatter)) {
                    continue;
                }
                // 部分导出模板会把同一笔数据再存一份「行高为 0」的隐藏行；与业务是否重复无关，仅排除此类重复物理行。
                if (row.getZeroHeight()) {
                    skippedHiddenRows++;
                    continue;
                }
                excelTableRowCount++;
                try {
                    LbDailyUserTrade trade = parseTradeRow(row, headerIndexMap, dataFormatter, fallbackReportDate, normalizedTenantId);
                    if (trade == null) {
                        skippedRows.add(String.valueOf(i + 1));
                        continue;
                    }
                    importList.add(trade);
                } catch (Exception ex) {
                    log.warn("解析Excel行失败，rowNum={}, message={}", i + 1, ex.getMessage(), ex);
                    skippedRows.add((i + 1) + "(" + ex.getMessage() + ")");
                }
            }
            log.info("Excel解析完成，fileName={}, parsedCount={}, skippedCount={}, skippedHiddenRows={}",
                    originalFilename, importList.size(), skippedRows.size(), skippedHiddenRows);
            if (importList.isEmpty()) {
                log.warn("Excel解析后无有效数据，fileName={}, tenantId={}, skippedRows={}",
                        originalFilename, normalizedTenantId, skippedRows);
            }
        } catch (IOException e) {
            log.error("读取Excel失败，fileName={}, message={}", originalFilename, e.getMessage(), e);
            result.put("success", false);
            result.put("message", "读取Excel失败：" + e.getMessage());
            return result;
        } catch (IllegalArgumentException e) {
            log.error("Excel格式校验失败，fileName={}, message={}", originalFilename, e.getMessage(), e);
            result.put("success", false);
            result.put("message", e.getMessage());
            return result;
        } catch (Exception e) {
            log.error("解析Excel异常，fileName={}, message={}", originalFilename, e.getMessage(), e);
            result.put("success", false);
            result.put("message", "解析Excel异常：" + e.getMessage());
            return result;
        }

        if (importList.isEmpty()) {
            result.put("success", false);
            result.put("message", "没有可导入的数据，请检查模板与内容");
            result.put("skippedRows", skippedRows);
            result.put("skippedHiddenRowCount", skippedHiddenRows);
            return result;
        }

        normalizeTradeStringsForDb(importList);

        int total = importList.size();
        try {
            TransactionTemplate tx = new TransactionTemplate(transactionManager);
            tx.executeWithoutResult(status -> {
                for (int from = 0; from < total; from += EXCEL_IMPORT_SAVE_CHUNK_SIZE) {
                    int to = Math.min(from + EXCEL_IMPORT_SAVE_CHUNK_SIZE, total);
                    List<LbDailyUserTrade> chunk = new ArrayList<>(importList.subList(from, to));
                    if (!this.saveBatch(chunk, EXCEL_IMPORT_INNER_BATCH_SIZE)) {
                        throw new IllegalStateException("saveBatch 返回 false，批次 [" + from + ", " + to + ")");
                    }
                }
                int verified = countRowsByIdsInCurrentTransaction(importList);
                if (verified != total) {
                    throw new IllegalStateException(String.format(
                            "写入条数校验失败：解析 %d 条，当前事务内按主键 id 查询仅 %d 条。"
                                    + " 常见原因：库表存在唯一约束（如 tenant_id+user_id+report_date）导致部分行未插入、"
                                    + "或历史上线租户上下文与请求参数 tenantId 不一致。本次导入已整单回滚。",
                            total, verified));
                }
            });
        } catch (RuntimeException ex) {
            log.error("导入写入或校验失败，fileName={}, tenantId={}, parsedCount={}",
                    originalFilename, normalizedTenantId, total, ex);
            result.put("success", false);
            result.put("message", "导入写入失败：" + ex.getMessage());
            result.put("importCount", 0);
            result.put("parsedCount", total);
            result.put("skipCount", skippedRows.size());
            result.put("skippedRows", skippedRows);
            result.put("skippedHiddenRowCount", skippedHiddenRows);
            return result;
        }

        int dbInsertedRowCount = importList.size();
        log.info("lb_daily_user_trade 导入落库完成：Excel表格行数={}，插入数据库行数={}", excelTableRowCount, dbInsertedRowCount);
        log.info("导入成功，fileName={}, importCount={}, skipCount={}, skippedHiddenRows={}",
                originalFilename, importList.size(), skippedRows.size(), skippedHiddenRows);
        result.put("success", true);
        result.put("message", "导入成功");
        result.put("importCount", importList.size());
        result.put("skipCount", skippedRows.size());
        result.put("skippedRows", skippedRows);
        result.put("skippedHiddenRowCount", skippedHiddenRows);
        return result;
        } finally {
            if (previousTenantId != null) {
                TenantContextHolder.setTenantId(previousTenantId);
            } else {
                TenantContextHolder.clear();
            }
            tenantImportLock.unlock();
        }
    }

    private void normalizeTradeStringsForDb(List<LbDailyUserTrade> importList) {
        for (LbDailyUserTrade t : importList) {
            t.setTenantId(truncateToMaxLength(t.getTenantId(), DB_TENANT_ID_MAX));
            t.setUserId(truncateToMaxLength(t.getUserId(), DB_USER_ID_MAX));
            t.setNickname(truncateToMaxLength(t.getNickname(), DB_NICKNAME_MAX));
            t.setPromoterId(truncateToMaxLength(t.getPromoterId(), DB_PROMOTER_ID_MAX));
            t.setPromoterName(truncateToMaxLength(t.getPromoterName(), DB_PROMOTER_NAME_MAX));
            t.setDescContent(truncateToMaxLength(t.getDescContent(), DB_DESC_MAX));
        }
    }

    private static String truncateToMaxLength(String value, int maxChars) {
        if (value == null) {
            return null;
        }
        return value.length() <= maxChars ? value : value.substring(0, maxChars);
    }

    /**
     * 在当前事务内按主键统计，用于发现“解析条数”与“实际可查询条数”不一致（批量静默失败、唯一约束、租户改写等）。
     */
    private int countRowsByIdsInCurrentTransaction(List<LbDailyUserTrade> importList) {
        int sum = 0;
        List<String> ids = new ArrayList<>(importList.size());
        for (LbDailyUserTrade t : importList) {
            ids.add(t.getId());
        }
        for (int i = 0; i < ids.size(); i += ID_IN_QUERY_BATCH) {
            int end = Math.min(i + ID_IN_QUERY_BATCH, ids.size());
            List<String> batch = ids.subList(i, end);
            LambdaQueryWrapper<LbDailyUserTrade> q = new LambdaQueryWrapper<>();
            q.in(LbDailyUserTrade::getId, batch);
            sum += this.count(q);
        }
        return sum;
    }

    private int findHeaderRow(Sheet sheet, DataFormatter formatter) {
        int maxRow = Math.min(sheet.getLastRowNum(), 10);
        for (int i = 0; i <= maxRow; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Map<String, Integer> headerMap = buildHeaderIndexMap(row, formatter);
            if (headerMap.containsKey("userId")) {
                return i;
            }
        }
        return -1;
    }

    private Map<String, Integer> buildHeaderIndexMap(Row headerRow, DataFormatter formatter) {
        Map<String, Integer> map = new HashMap<>();
        if (headerRow == null) {
            return map;
        }
        for (Cell cell : headerRow) {
            String text = normalizeHeader(formatter.formatCellValue(cell));
            if (text.isEmpty()) {
                continue;
            }
            String field = resolveFieldByHeader(text);
            if (field != null) {
                map.putIfAbsent(field, cell.getColumnIndex());
            }
        }
        completeAmountColumnByOrder(map, headerRow, formatter);
        return map;
    }

    private void completeAmountColumnByOrder(Map<String, Integer> map, Row headerRow, DataFormatter formatter) {
        if (map.containsKey("dailySellAmt") && map.containsKey("dailyBuyAmt")) {
            return;
        }
        Integer nicknameIndex = map.get("nickname");
        Integer userIdIndex = map.get("userId");
        Integer diffIndex = map.get("diffAmt");

        int startIndex = -1;
        if (nicknameIndex != null) {
            startIndex = nicknameIndex;
        } else if (userIdIndex != null) {
            startIndex = userIdIndex;
        }

        if (startIndex < 0) {
            return;
        }

        int candidateSellIndex = startIndex + 1;
        int candidateBuyIndex = startIndex + 2;

        int lastCellNum = headerRow.getLastCellNum();
        if (lastCellNum < 0) {
            return;
        }

        if (diffIndex != null) {
            if (candidateSellIndex >= diffIndex || candidateBuyIndex >= diffIndex) {
                candidateSellIndex = -1;
                candidateBuyIndex = -1;
                for (int i = startIndex + 1; i < diffIndex; i++) {
                    String header = normalizeHeader(formatter.formatCellValue(headerRow.getCell(i)));
                    if (header.isEmpty()) {
                        continue;
                    }
                    if (candidateSellIndex < 0) {
                        candidateSellIndex = i;
                    } else {
                        candidateBuyIndex = i;
                        break;
                    }
                }
            }
        }

        if (!map.containsKey("dailySellAmt")
                && candidateSellIndex >= 0
                && candidateSellIndex < lastCellNum) {
            map.put("dailySellAmt", candidateSellIndex);
        }
        if (!map.containsKey("dailyBuyAmt")
                && candidateBuyIndex >= 0
                && candidateBuyIndex < lastCellNum) {
            map.put("dailyBuyAmt", candidateBuyIndex);
        }
    }

    private String resolveFieldByHeader(String header) {
        Map<String, Set<String>> aliasMap = new HashMap<>();
        aliasMap.put("tenantId", Set.of("tenantid", "tenant_id", "租户id", "租户"));
        aliasMap.put("userId", Set.of("userid", "user_id", "用户id", "用户"));
        aliasMap.put("nickname", Set.of("nickname", "昵称", "用户名", "客户昵称"));
        aliasMap.put("dailySellAmt", Set.of("dailysellamt", "daily_sell_amt", "单日卖出金额", "卖出金额", "卖出", "卖总"));
        aliasMap.put("dailyBuyAmt", Set.of("dailybuyamt", "daily_buy_amt", "当日买入金额", "买入金额", "买入", "买总"));
        aliasMap.put("diffAmt", Set.of("diffamt", "diff_amt", "差额", "净额"));
        aliasMap.put("promoterId", Set.of("promoterid", "promoter_id", "推广人id"));
        aliasMap.put("promoterName", Set.of("promotername", "promoter_name", "推广人", "邀请人"));
        aliasMap.put("descContent", Set.of("desccontent", "desc_content", "描述", "备注", "说明"));
        aliasMap.put("reportDate", Set.of("reportdate", "report_date", "报表日期", "统计日期", "日期"));

        for (Map.Entry<String, Set<String>> entry : aliasMap.entrySet()) {
            if (entry.getValue().contains(header)) {
                return entry.getKey();
            }
        }
        if (header.contains("卖") && header.contains("总")) {
            return "dailySellAmt";
        }
        if (header.contains("买") && header.contains("总")) {
            return "dailyBuyAmt";
        }
        return null;
    }

    private LbDailyUserTrade parseTradeRow(Row row,
                                           Map<String, Integer> headerMap,
                                           DataFormatter formatter,
                                           LocalDate fallbackReportDate,
                                           String defaultTenantId) {
        String tenantId = defaultTenantId == null ? "" : defaultTenantId;
        String userId = readString(row, headerMap.get("userId"), formatter);
        String nickname = readString(row, headerMap.get("nickname"), formatter);
        if (tenantId.isEmpty()) {
            return null;
        }

        // 严格按 Excel 行写入：不查库合并/补全 userId 或推广人；与库内是否重复无关，每行一条 INSERT。
        if (userId.isEmpty()) {
            userId = "AUTO_USERID_" + UUID.randomUUID().toString().replace("-", "");
        }

        LbDailyUserTrade trade = new LbDailyUserTrade();
        trade.setId(UUID.randomUUID().toString());
        trade.setTenantId(tenantId);
        trade.setUserId(userId);
        trade.setNickname(nickname);
        trade.setDailySellAmt(readDecimal(row, headerMap.get("dailySellAmt"), formatter));
        trade.setDailyBuyAmt(readDecimal(row, headerMap.get("dailyBuyAmt"), formatter));
        trade.setPromoterId(readString(row, headerMap.get("promoterId"), formatter));
        trade.setPromoterName(readString(row, headerMap.get("promoterName"), formatter));
        trade.setDescContent(readString(row, headerMap.get("descContent"), formatter));

        BigDecimal sell = trade.getDailySellAmt() == null ? BigDecimal.ZERO : trade.getDailySellAmt();
        BigDecimal buy = trade.getDailyBuyAmt() == null ? BigDecimal.ZERO : trade.getDailyBuyAmt();
        BigDecimal diff = readDecimal(row, headerMap.get("diffAmt"), formatter);
        trade.setDailySellAmt(sell);
        trade.setDailyBuyAmt(buy);
        trade.setDiffAmt(diff == null ? sell.subtract(buy) : diff);

        LocalDate reportDate = readDate(row, headerMap.get("reportDate"), formatter);
        trade.setReportDate(reportDate != null ? reportDate : (fallbackReportDate != null ? fallbackReportDate : LocalDate.now()));
        trade.setCreatedAt(LocalDateTime.now());
        trade.setUpdatedAt(LocalDateTime.now());
        return trade;
    }

    private boolean isEmptyRow(Row row, DataFormatter formatter) {
        if (row == null) {
            return true;
        }
        // getLastCellNum 为「最后一个逻辑列下标 + 1」；勿仅用物理已创建单元格迭代，否则会漏判有数据的行
        int lastCellNum = row.getLastCellNum();
        if (lastCellNum <= 0) {
            return true;
        }
        for (int cn = 0; cn < lastCellNum; cn++) {
            Cell cell = row.getCell(cn);
            if (cell != null && !formatter.formatCellValue(cell).trim().isEmpty()) {
                return false;
            }
        }
        return true;
    }

    private String readString(Row row, Integer index, DataFormatter formatter) {
        if (index == null) {
            return "";
        }
        Cell cell = row.getCell(index);
        if (cell == null) {
            return "";
        }
        return formatter.formatCellValue(cell).trim();
    }

    private BigDecimal readDecimal(Row row, Integer index, DataFormatter formatter) {
        String value = readString(row, index, formatter);
        if (value.isEmpty()) {
            return BigDecimal.ZERO;
        }
        String normalized = value.replace(",", "").replace("，", "");
        try {
            return new BigDecimal(normalized);
        } catch (Exception e) {
            throw new IllegalArgumentException("金额格式错误: " + value);
        }
    }

    private LocalDate readDate(Row row, Integer index, DataFormatter formatter) {
        if (index == null) {
            return null;
        }
        Cell cell = row.getCell(index);
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            return cell.getLocalDateTimeCellValue().toLocalDate();
        }
        String text = formatter.formatCellValue(cell).trim();
        if (text.isEmpty()) {
            return null;
        }
        try {
            return LocalDate.parse(text);
        } catch (Exception ignore) {
        }
        try {
            return LocalDate.parse(text, DateTimeFormatter.ofPattern("yyyy/MM/dd"));
        } catch (Exception ignore) {
        }
        try {
            return LocalDate.parse(text, DateTimeFormatter.ofPattern("yyyy-M-d"));
        } catch (Exception ignore) {
        }
        throw new IllegalArgumentException("日期格式错误: " + text);
    }

    private String normalizeHeader(String header) {
        return header == null ? "" : header.toLowerCase().replace(" ", "").replace("　", "").trim();
    }

    private boolean isSupportedExcelSuffix(String fileName) {
        String lowerCaseName = fileName.toLowerCase();
        return lowerCaseName.endsWith(".xlsx") || lowerCaseName.endsWith(".xls");
    }

    private Workbook openWorkbook(MultipartFile file) throws IOException {
        byte[] fileBytes = file.getBytes();
        FileMagic fileMagic = detectFileMagic(fileBytes);
        try {
            return WorkbookFactory.create(new ByteArrayInputStream(fileBytes));
        } catch (Exception e) {
            log.warn("标准Excel解析失败，尝试文本格式兜底，fileName={}, fileMagic={}, contentType={}, headHex={}",
                    file.getOriginalFilename(), fileMagic, file.getContentType(), toHexPreview(fileBytes, 16));
            Workbook textWorkbook = tryCreateWorkbookFromText(fileBytes, file.getOriginalFilename());
            if (textWorkbook != null) {
                log.info("文本格式兜底解析成功，fileName={}", file.getOriginalFilename());
                return textWorkbook;
            }
            log.error("文本格式兜底也失败，fileName={}, fileMagic={}, contentType={}",
                    file.getOriginalFilename(), fileMagic, file.getContentType(), e);
            throw new IllegalArgumentException(
                    "上传文件内容不是有效的Excel文件，请确认文件未损坏，且不要仅修改后缀名。检测到文件签名：" + fileMagic, e);
        }
    }

    private FileMagic detectFileMagic(byte[] fileBytes) throws IOException {
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(fileBytes)) {
            return FileMagic.valueOf(FileMagic.prepareToCheckMagic(inputStream));
        }
    }

    private Workbook tryCreateWorkbookFromText(byte[] fileBytes, String fileName) {
        for (Charset charset : List.of(StandardCharsets.UTF_8, Charset.forName("GBK"))) {
            try {
                Workbook workbook = createWorkbookFromDelimitedText(fileBytes, charset);
                if (workbook != null) {
                    log.info("文本文件按分隔格式解析成功，fileName={}, charset={}", fileName, charset.name());
                    return workbook;
                }
            } catch (Exception ex) {
                log.debug("文本格式兜底解析失败，fileName={}, charset={}, message={}",
                        fileName, charset.name(), ex.getMessage());
            }
        }
        return null;
    }

    private Workbook createWorkbookFromDelimitedText(byte[] fileBytes, Charset charset) throws IOException {
        List<String> lines = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new ByteArrayInputStream(fileBytes), charset))) {
            String line;
            while ((line = reader.readLine()) != null) {
                if (!line.trim().isEmpty()) {
                    lines.add(removeUtf8Bom(line));
                }
            }
        }
        if (lines.isEmpty()) {
            return null;
        }
        char delimiter = detectDelimiter(lines.get(0));
        if (delimiter == 0) {
            return null;
        }

        List<List<String>> parsedRows = new ArrayList<>();
        int maxColumns = 0;
        for (String line : lines) {
            List<String> cells = parseDelimitedLine(line, delimiter);
            if (cells.size() > 1) {
                parsedRows.add(cells);
                maxColumns = Math.max(maxColumns, cells.size());
            }
        }
        if (parsedRows.isEmpty() || maxColumns < 2) {
            return null;
        }

        Workbook workbook = WorkbookFactory.create(true);
        Sheet sheet = workbook.createSheet("Sheet1");
        for (int rowIndex = 0; rowIndex < parsedRows.size(); rowIndex++) {
            Row row = sheet.createRow(rowIndex);
            List<String> cells = parsedRows.get(rowIndex);
            for (int colIndex = 0; colIndex < cells.size(); colIndex++) {
                row.createCell(colIndex).setCellValue(cells.get(colIndex));
            }
        }
        return workbook;
    }

    private char detectDelimiter(String line) {
        int tabCount = countChar(line, '\t');
        int commaCount = countChar(line, ',');
        int semicolonCount = countChar(line, ';');
        if (tabCount > 0 && tabCount >= commaCount && tabCount >= semicolonCount) {
            return '\t';
        }
        if (commaCount > 0 && commaCount >= semicolonCount) {
            return ',';
        }
        if (semicolonCount > 0) {
            return ';';
        }
        return 0;
    }

    private int countChar(String text, char ch) {
        int count = 0;
        for (int i = 0; i < text.length(); i++) {
            if (text.charAt(i) == ch) {
                count++;
            }
        }
        return count;
    }

    private List<String> parseDelimitedLine(String line, char delimiter) {
        List<String> cells = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        boolean inQuotes = false;
        for (int i = 0; i < line.length(); i++) {
            char c = line.charAt(i);
            if (c == '"') {
                if (inQuotes && i + 1 < line.length() && line.charAt(i + 1) == '"') {
                    current.append('"');
                    i++;
                } else {
                    inQuotes = !inQuotes;
                }
                continue;
            }
            if (c == delimiter && !inQuotes) {
                cells.add(current.toString().trim());
                current.setLength(0);
                continue;
            }
            current.append(c);
        }
        cells.add(current.toString().trim());
        return cells;
    }

    private String removeUtf8Bom(String line) {
        if (!line.isEmpty() && line.charAt(0) == '\uFEFF') {
            return line.substring(1);
        }
        return line;
    }

    private String toHexPreview(byte[] bytes, int len) {
        int size = Math.min(bytes.length, len);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < size; i++) {
            sb.append(String.format("%02X", bytes[i]));
            if (i < size - 1) {
                sb.append(" ");
            }
        }
        return sb.toString();
    }

    /**
     * 从文件名中提取 yyyy-MM-dd 格式的日期，例如：
     * 用户下单统计表_2026-04-23.xlsx -> 2026-04-23
     */
    private LocalDate extractDateFromFilename(String fileName) {
        if (fileName == null) {
            return null;
        }
        // 只看文件名本身，去掉路径
        String simpleName = fileName;
        int slashIdx = Math.max(simpleName.lastIndexOf('/'), simpleName.lastIndexOf('\\'));
        if (slashIdx >= 0 && slashIdx + 1 < simpleName.length()) {
            simpleName = simpleName.substring(slashIdx + 1);
        }
        // 匹配第一个 yyyy-MM-dd
        Pattern pattern = Pattern.compile("(\\d{4}-\\d{2}-\\d{2})");
        Matcher matcher = pattern.matcher(simpleName);
        if (matcher.find()) {
            String dateStr = matcher.group(1);
            try {
                return LocalDate.parse(dateStr, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            } catch (Exception ignore) {
                // 忽略解析异常，返回 null 走默认逻辑
            }
        }
        return null;
    }

}
