package com.talentcard.server.service;

import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.talentcard.server.business.BusinessIdGenerator;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import com.talentcard.server.entity.BenefitMerchant;
import com.talentcard.server.entity.TicketPackage;
import com.talentcard.server.entity.TicketPackageClaim;
import com.talentcard.server.entity.TicketPackageCode;
import com.talentcard.server.entity.TicketPackageLevelRule;
import com.talentcard.server.entity.TicketPackageMerchant;
import com.talentcard.server.mapper.BenefitMerchantMapper;
import com.talentcard.server.mapper.TicketPackageClaimMapper;
import com.talentcard.server.mapper.TicketPackageCodeMapper;
import com.talentcard.server.mapper.TicketPackageLevelRuleMapper;
import com.talentcard.server.mapper.TicketPackageMapper;
import com.talentcard.server.mapper.TicketPackageMerchantMapper;
import com.talentcard.server.request.TicketPackageCodeImportRequest;
import com.talentcard.server.request.TicketPackageCodeGenerateRequest;
import com.talentcard.server.request.TicketPackageCodeLockRequest;
import com.talentcard.server.request.TicketPackageCodeStatusRequest;
import com.talentcard.server.request.TicketPackageSupplementRequest;
import com.talentcard.server.request.TicketPackageSaveRequest;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

@Service
public class TicketPackageAdminService {
    private static final List<String> CARD_LEVELS = List.of("卓越人才卡", "杰出人才卡", "菁英人才卡");

    private final TicketPackageMapper packageMapper;
    private final TicketPackageCodeMapper codeMapper;
    private final TicketPackageClaimMapper claimMapper;
    private final BenefitMerchantMapper merchantMapper;
    private final TicketPackageMerchantMapper packageMerchantMapper;
    private final TicketPackageLevelRuleMapper levelRuleMapper;
    private final JdbcTemplate jdbcTemplate;

    public TicketPackageAdminService(
            TicketPackageMapper packageMapper,
            TicketPackageCodeMapper codeMapper,
            TicketPackageClaimMapper claimMapper,
            BenefitMerchantMapper merchantMapper,
            TicketPackageMerchantMapper packageMerchantMapper,
            TicketPackageLevelRuleMapper levelRuleMapper,
            JdbcTemplate jdbcTemplate
    ) {
        this.packageMapper = packageMapper;
        this.codeMapper = codeMapper;
        this.claimMapper = claimMapper;
        this.merchantMapper = merchantMapper;
        this.packageMerchantMapper = packageMerchantMapper;
        this.levelRuleMapper = levelRuleMapper;
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 票包表单的「适用商家」下拉选项：只回启用的观影服务商家（id + 名称）。
     *
     * <p>单独开这个只读接口，是为了让「电影票管理」这个权限自己够用——之前页面复用
     * {@code /api/admin/business/merchants}，票包运营账号没有「商家查询」权限就整页报错。
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> merchantOptions(String keyword) {
        StringBuilder sql = new StringBuilder("""
                select id, merchant_name, merchant_type
                from merchant
                where deleted = 0 and status = 1 and merchant_type_code = 'cinema'
                """);
        List<Object> arguments = new ArrayList<>();
        if (keyword != null && !keyword.isBlank()) {
            sql.append(" and merchant_name like ?");
            arguments.add("%" + keyword.trim() + "%");
        }
        sql.append(" order by sort_no desc, id asc");
        return jdbcTemplate.queryForList(sql.toString(), arguments.toArray()).stream().map(row -> {
            Map<String, Object> option = new LinkedHashMap<>();
            option.put("id", row.get("id"));
            option.put("merchantName", row.get("merchant_name"));
            option.put("merchantType", row.get("merchant_type"));
            return option;
        }).toList();
    }

    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> packages(Long merchantId, Integer status, String keyword, String merchantKeyword, int page, int size) {
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        Integer normalizedStatus = normalizeStatusFilter(status);
        Long total = packageMapper.countAdminPage(merchantId, normalizedStatus, clean(keyword), clean(merchantKeyword));
        List<Map<String, Object>> records = packageMapper.selectAdminPage(merchantId, normalizedStatus, clean(keyword), clean(merchantKeyword), safeSize, (safePage - 1) * safeSize)
                .stream()
                .map(this::packageView)
                .toList();
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }

    @Transactional(readOnly = true)
    public Map<String, Object> packageDetail(Long id) {
        return packageView(requirePackageDetail(id));
    }

    @Transactional
    public Map<String, Object> createPackage(TicketPackageSaveRequest request) {
        List<Long> merchantIds = validateMerchants(request);
        TicketPackage entity = new TicketPackage();
        entity.setPackageNo(BusinessIdGenerator.next("TP"));
        apply(entity, request);
        entity.setDeleted(0);
        packageMapper.insert(entity);
        savePackageMerchants(entity.getId(), merchantIds);
        saveLevelRules(entity.getId(), request);
        return packageDetail(entity.getId());
    }

    @Transactional
    public Map<String, Object> updatePackage(Long id, TicketPackageSaveRequest request) {
        Long packageId = requireId(id, "票包ID不能为空");
        List<Long> merchantIds = validateMerchants(request);
        TicketPackage current = packageMapper.selectByIdForUpdate(packageId);
        if (current == null) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "票包不存在");
        }
        apply(current, request);
        int updated = packageMapper.updateById(current);
        if (updated != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "票包配置已变化，请刷新后重试");
        }
        savePackageMerchants(packageId, merchantIds);
        saveLevelRules(packageId, request);
        return packageDetail(packageId);
    }

    @Transactional
    public Map<String, Object> updatePackageStatus(Long id, Integer status) {
        Long packageId = requireId(id, "票包ID不能为空");
        int normalized = normalizeStatus(status);
        int updated = packageMapper.update(null, new UpdateWrapper<TicketPackage>()
                .set("status", normalized)
                .eq("id", packageId)
                .eq("deleted", 0));
        if (updated != 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "票包不存在");
        }
        return packageDetail(packageId);
    }

    @Transactional
    public Map<String, Object> importCodes(Long packageId, TicketPackageCodeImportRequest request) {
        TicketPackage ticketPackage = requirePackage(packageId);
        String batchNo = clean(request.batchNo()).isBlank() ? BusinessIdGenerator.next("TPC") : clean(request.batchNo());
        String remark = clean(request.remark());
        Set<String> distinctCodes = new LinkedHashSet<>();
        for (String code : request.codes()) {
            String cleaned = clean(code);
            if (!cleaned.isBlank()) {
                distinctCodes.add(cleaned);
            }
        }
        if (distinctCodes.isEmpty()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码列表不能为空");
        }
        int imported = 0;
        int duplicate = 0;
        for (String exchangeCode : distinctCodes) {
            TicketPackageCode entity = new TicketPackageCode();
            entity.setPackageId(ticketPackage.getId());
            entity.setExchangeCode(exchangeCode);
            entity.setBatchNo(batchNo);
            entity.setStatus("AVAILABLE");
            entity.setRemark(remark);
            entity.setDeleted(0);
            try {
                codeMapper.insert(entity);
                imported++;
            } catch (DuplicateKeyException exception) {
                duplicate++;
            }
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("packageId", ticketPackage.getId());
        result.put("batchNo", batchNo);
        result.put("submittedCount", request.codes().size());
        result.put("distinctCount", distinctCodes.size());
        result.put("importedCount", imported);
        result.put("duplicateCount", duplicate);
        result.put("stock", stock(ticketPackage.getId()));
        return result;
    }

    @Transactional
    public Map<String, Object> generateCodes(Long packageId, TicketPackageCodeGenerateRequest request) {
        TicketPackage ticketPackage = requirePackage(packageId);
        int target = Math.max(request.codeCount() == null ? 0 : request.codeCount(), 0);
        if (target < 1) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "生成数量不能为空");
        }
        String batchNo = clean(request.batchNo()).isBlank() ? BusinessIdGenerator.next("TPC") : clean(request.batchNo());
        String remark = clean(request.remark());
        String codePrefix = normalizedGeneratedCodePrefix(request.codePrefix());
        Set<String> generatedCodes = new LinkedHashSet<>();
        int imported = 0;
        int duplicate = 0;
        int attempts = 0;
        int maxAttempts = Math.max(target * 20, 100);
        while (imported < target && attempts < maxAttempts) {
            attempts++;
            String exchangeCode = buildGeneratedExchangeCode(codePrefix);
            if (!generatedCodes.add(exchangeCode)) {
                duplicate++;
                continue;
            }
            TicketPackageCode entity = new TicketPackageCode();
            entity.setPackageId(ticketPackage.getId());
            entity.setExchangeCode(exchangeCode);
            entity.setBatchNo(batchNo);
            entity.setStatus("AVAILABLE");
            entity.setRemark(remark);
            entity.setDeleted(0);
            try {
                codeMapper.insert(entity);
                imported++;
            } catch (DuplicateKeyException exception) {
                duplicate++;
                generatedCodes.remove(exchangeCode);
            }
        }
        if (imported < target) {
            throw new BusinessException(ErrorCode.CONFLICT, "兑换码生成失败，请重试");
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("packageId", ticketPackage.getId());
        result.put("batchNo", batchNo);
        result.put("submittedCount", target);
        result.put("distinctCount", generatedCodes.size());
        result.put("importedCount", imported);
        result.put("duplicateCount", duplicate);
        result.put("stock", stock(ticketPackage.getId()));
        return result;
    }

    @Transactional
    public Map<String, Object> importCodeFile(Long packageId, MultipartFile file, String batchNo, String remark) {
        if (file == null || file.isEmpty()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码文件不能为空");
        }
        List<String> codes = readExcelCodes(file);
        return importCodes(packageId, new TicketPackageCodeImportRequest(batchNo, codes, remark));
    }

    @Transactional(readOnly = true)
    public Map<String, Object> stock(Long packageId) {
        return packageView(requirePackageDetail(packageId));
    }

    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> codes(Long packageId, String status, String batchNo, String keyword, String packageKeyword, String merchantKeyword, int page, int size) {
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        String normalizedStatus = normalizeCodeStatusFilter(status);
        Long total = codeMapper.countAdminPage(packageId, normalizedStatus, clean(batchNo), clean(keyword), clean(packageKeyword), clean(merchantKeyword));
        List<Map<String, Object>> records = codeMapper.selectAdminPage(packageId, normalizedStatus, clean(batchNo), clean(keyword), clean(packageKeyword), clean(merchantKeyword), safeSize, (safePage - 1) * safeSize)
                .stream()
                .map(this::codeView)
                .toList();
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }

    @Transactional
    public Map<String, Object> updateCodeStatus(Long codeId, TicketPackageCodeStatusRequest request) {
        Long id = requireId(codeId, "兑换码ID不能为空");
        String nextStatus = normalizeCodeStatus(request.status(), false);
        TicketPackageCode current = codeMapper.selectById(id);
        if (current == null || intValue(current.getDeleted()) == 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
        }
        if ("ISSUED".equals(current.getStatus()) && !"USED".equals(nextStatus)) {
            throw new BusinessException(ErrorCode.CONFLICT, "已发放兑换码只能标记为已使用");
        }
        if ("USED".equals(current.getStatus())) {
            throw new BusinessException(ErrorCode.CONFLICT, "已使用兑换码不能变更状态");
        }
        UpdateWrapper<TicketPackageCode> update = new UpdateWrapper<TicketPackageCode>()
                .set("status", nextStatus)
                .set("remark", clean(request.remark()))
                .eq("id", id)
                .eq("deleted", 0);
        if ("USED".equals(nextStatus)) {
            update.set("used_at", LocalDateTime.now());
        }
        int updated = codeMapper.update(null, update);
        if (updated != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "兑换码状态已变化，请刷新后重试");
        }
        if ("USED".equals(nextStatus) && current.getIssuedClaimId() != null) {
            claimMapper.update(null, new UpdateWrapper<com.talentcard.server.entity.TicketPackageClaim>()
                    .set("status", "USED")
                    .set("used_at", LocalDateTime.now())
                    .eq("id", current.getIssuedClaimId())
                    .eq("deleted", 0));
        }
        return codeView(codeMapper.selectAdminPage(current.getPackageId(), "", "", current.getExchangeCode(), "", "", 1, 0).stream().findFirst().orElse(Map.of()));
    }

    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> claims(Long packageId, Long talentId, String status, String keyword, String packageKeyword, String talentKeyword, String cardLevel, LocalDate claimedStart, LocalDate claimedEnd, int page, int size) {
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        String normalizedStatus = normalizeClaimStatusFilter(status);
        Long total = claimMapper.countAdminPage(packageId, talentId, normalizedStatus, clean(keyword), clean(packageKeyword), clean(talentKeyword), clean(cardLevel), claimedStart, claimedEnd);
        List<Map<String, Object>> records = claimMapper.selectAdminPage(packageId, talentId, normalizedStatus, clean(keyword), clean(packageKeyword), clean(talentKeyword), clean(cardLevel), claimedStart, claimedEnd, safeSize, (safePage - 1) * safeSize)
                .stream()
                .map(this::claimView)
                .toList();
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }

    /**
     * 票包核销记录：只列已核销（status = USED）的领取记录，供后台「电影票管理 → 核销记录」查询与导出。
     *
     * <p>「核销商家」取领取记录上的 merchant_id。核销接口要求核销方必须等于该商户，所以这里不需要
     * 再关联 benefit_verify_record 才能知道是谁核销的。
     */
    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> verifications(Long packageId, String talentKeyword, String cardLevel,
                                                         LocalDate claimedStart, LocalDate claimedEnd,
                                                         LocalDate verifiedStart, LocalDate verifiedEnd,
                                                         String merchantKeyword, int page, int size) {
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        Long total = claimMapper.countAdminVerificationPage(packageId, clean(talentKeyword), clean(cardLevel),
                claimedStart, claimedEnd, verifiedStart, verifiedEnd, clean(merchantKeyword));
        List<Map<String, Object>> records = claimMapper.selectAdminVerificationPage(packageId, clean(talentKeyword),
                        clean(cardLevel), claimedStart, claimedEnd, verifiedStart, verifiedEnd, clean(merchantKeyword),
                        safeSize, (safePage - 1) * safeSize)
                .stream()
                .map(this::verificationView)
                .toList();
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }

    private Map<String, Object> verificationView(Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("id", value(row, "id"));
        result.put("claimNo", text(row, "claimNo", "claim_no"));
        result.put("packageId", value(row, "packageId", "package_id"));
        result.put("packageName", text(row, "packageName", "package_name"));
        result.put("merchantNames", text(row, "merchantNames", "merchant_names"));
        result.put("talentName", text(row, "talentName", "talent_name"));
        result.put("talentMobile", text(row, "talentMobile", "talent_mobile"));
        result.put("cardLevel", text(row, "cardLevel", "card_level"));
        result.put("claimedAt", value(row, "createdAt", "created_at"));
        result.put("usedAt", value(row, "usedAt", "used_at"));
        result.put("verifyMerchantName", text(row, "verifyMerchantName", "verify_merchant_name"));
        return result;
    }


    @Transactional(readOnly = true)
    public Map<String, Object> codeDetail(Long codeId) {
        Long id = requireId(codeId, "兑换码ID不能为空");
        Map<String, Object> row = codeMapper.selectAdminDetailById(id);
        if (row == null || row.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
        }
        return codeDetailView(row);
    }

    @Transactional(readOnly = true)
    public Map<String, Object> validateCode(String exchangeCode, Long packageId, Long merchantId) {
        String codeValue = clean(exchangeCode);
        if (codeValue.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码码值不能为空");
        }
        Map<String, Object> row = codeMapper.selectAdminDetailByExchangeCode(codeValue, packageId);
        if (row == null || row.isEmpty()) {
            return validationResult(false, "兑换码不存在", row);
        }
        String reason = validationReason(row, merchantId);
        if (reason.isBlank() && "AVAILABLE".equals(text(row, "status"))) {
            return validationResult(true, "", row);
        }
        if (reason.isBlank()) {
            reason = codeStatusReason(text(row, "status"));
        }
        return validationResult(false, reason, row);
    }

    @Transactional
    public Map<String, Object> lockForIssue(Long codeId, TicketPackageCodeLockRequest request) {
        Long id = requireId(codeId, "兑换码ID不能为空");
        TicketPackageCode code = codeMapper.selectByIdForUpdate(id);
        if (code == null || intValue(code.getDeleted()) == 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
        }
        if (!"AVAILABLE".equals(code.getStatus())) {
            throw new BusinessException(ErrorCode.CONFLICT, "仅可用状态兑换码可锁定");
        }
        validateLockablePackage(packageMapper.selectById(code.getPackageId()));
        ClaimerInfo claimer = resolveClaimer(request.talentId(), request.cardId(), false);
        int locked = codeMapper.lockForIssue(code.getId(), claimer.talentId(), claimer.cardId());
        if (locked != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "兑换码已被领取或状态已变化，请刷新后重试");
        }
        if (!clean(request.remark()).isBlank()) {
            codeMapper.update(null, new UpdateWrapper<TicketPackageCode>()
                    .set("remark", clean(request.remark()))
                    .eq("id", code.getId())
                    .eq("deleted", 0));
        }
        return codeDetailView(codeMapper.selectAdminDetailById(code.getId()));
    }

    @Transactional
    public Map<String, Object> releaseLock(Long codeId) {
        Long id = requireId(codeId, "兑换码ID不能为空");
        TicketPackageCode code = codeMapper.selectByIdForUpdate(id);
        if (code == null || intValue(code.getDeleted()) == 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
        }
        if (!"LOCKED".equals(code.getStatus())) {
            throw new BusinessException(ErrorCode.CONFLICT, "仅锁定状态兑换码可解锁");
        }
        int released = codeMapper.releaseLock(id);
        if (released != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "兑换码状态已变化，请刷新后重试");
        }
        return codeDetailView(codeMapper.selectAdminDetailById(id));
    }

    @Transactional
    public Map<String, Object> voidCode(Long codeId, String remark) {
        Long id = requireId(codeId, "兑换码ID不能为空");
        TicketPackageCode code = codeMapper.selectByIdForUpdate(id);
        if (code == null || intValue(code.getDeleted()) == 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
        }
        String current = code.getStatus();
        if ("USED".equals(current) || "VOID".equals(current)) {
            throw new BusinessException(ErrorCode.CONFLICT, "已使用或已作废兑换码不能作废");
        }
        UpdateWrapper<TicketPackageCode> update = new UpdateWrapper<TicketPackageCode>()
                .set("status", "VOID")
                .set("remark", clean(remark))
                .eq("id", id)
                .eq("deleted", 0);
        if ("LOCKED".equals(current)) {
            update.set("locked_by_talent_id", null)
                    .set("locked_by_card_id", null)
                    .set("locked_at", null);
        }
        int updated = codeMapper.update(null, update);
        if (updated != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "兑换码状态已变化，请刷新后重试");
        }
        if (code.getIssuedClaimId() != null) {
            claimMapper.update(null, new UpdateWrapper<TicketPackageClaim>()
                    .set("status", "CANCELLED")
                    .eq("id", code.getIssuedClaimId())
                    .eq("deleted", 0));
        }
        return codeDetailView(codeMapper.selectAdminDetailById(id));
    }

    @Transactional
    public Map<String, Object> supplementClaim(TicketPackageSupplementRequest request) {
        Long packageId = requireId(request.packageId(), "票包ID不能为空");
        TicketPackage ticketPackage = packageMapper.selectByIdForUpdate(packageId);
        validateLockablePackage(ticketPackage);
        TicketPackageCode code = resolveSupplementCode(request, packageId);
        TicketPackageClaim existing = claimMapper.selectByCodeId(code.getId());
        if (existing != null) {
            return claimAdminView(existing);
        }
        String current = code.getStatus();
        if (!"AVAILABLE".equals(current) && !"LOCKED".equals(current)) {
            throw new BusinessException(ErrorCode.CONFLICT, "仅可用或锁定状态兑换码可补录发放");
        }
        ClaimerInfo claimer = resolveClaimer(request.talentId(), request.cardId(), true);
        LocalDateTime now = LocalDateTime.now();
        if ("AVAILABLE".equals(current)) {
            int locked = codeMapper.lockForIssue(code.getId(), claimer.talentId(), claimer.cardId());
            if (locked != 1) {
                throw new BusinessException(ErrorCode.CONFLICT, "兑换码已被领取，请刷新后重试");
            }
        }
        TicketPackageClaim claim = new TicketPackageClaim();
        claim.setClaimNo(BusinessIdGenerator.next("TPC"));
        claim.setPackageId(ticketPackage.getId());
        claim.setCodeId(code.getId());
        claim.setMerchantId(ticketPackage.getMerchantId());
        claim.setTalentId(claimer.talentId());
        claim.setCardId(claimer.cardId());
        claim.setPrimaryCardId(claimer.primaryCardId());
        claim.setCardNo(claimer.cardNo());
        claim.setCardLevel(claimer.cardLevel());
        claim.setTalentName(claimer.talentName());
        claim.setTalentMobile(claimer.talentMobile());
        claim.setExchangeCode(code.getExchangeCode());
        claim.setStatus("ISSUED");
        claim.setValidEnd(ticketPackage.getValidEnd());
        claim.setIdempotencyKey("MANUAL-" + claim.getClaimNo());
        claim.setDeleted(0);
        try {
            claimMapper.insert(claim);
        } catch (DuplicateKeyException exception) {
            codeMapper.releaseLock(code.getId());
            TicketPackageClaim concurrent = claimMapper.selectByCodeId(code.getId());
            if (concurrent != null) {
                return claimAdminView(concurrent);
            }
            throw new BusinessException(ErrorCode.CONFLICT, "补录请求已提交，请稍后查询发放结果");
        } catch (RuntimeException exception) {
            codeMapper.releaseLock(code.getId());
            throw exception;
        }
        int issued = codeMapper.update(null, new UpdateWrapper<TicketPackageCode>()
                .set("status", "ISSUED")
                .set("issued_claim_id", claim.getId())
                .set("issued_at", now)
                .set("locked_by_talent_id", claimer.talentId())
                .set("locked_by_card_id", claimer.cardId())
                .set("locked_at", now)
                .set("remark", clean(request.remark()))
                .eq("id", code.getId())
                .eq("status", "LOCKED")
                .eq("deleted", 0));
        if (issued != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "兑换码发放失败，请重试");
        }
        return claimAdminView(claim);
    }

    private TicketPackageCode resolveSupplementCode(TicketPackageSupplementRequest request, Long packageId) {
        if (request.codeId() != null) {
            TicketPackageCode code = codeMapper.selectByIdForUpdate(request.codeId());
            if (code == null || intValue(code.getDeleted()) == 1) {
                throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
            }
            if (!packageId.equals(code.getPackageId())) {
                throw new BusinessException(ErrorCode.CONFLICT, "兑换码不属于该票包");
            }
            return code;
        }
        String codeValue = clean(request.exchangeCode());
        if (codeValue.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码ID与兑换码码值至少填一个");
        }
        TicketPackageCode code = codeMapper.selectByExchangeCodeForUpdate(codeValue, packageId);
        if (code == null || intValue(code.getDeleted()) == 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "兑换码不存在");
        }
        return code;
    }
    private List<String> readExcelCodes(MultipartFile file) {
        List<String> codes = new ArrayList<>();
        DataFormatter formatter = new DataFormatter();
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            if (workbook.getNumberOfSheets() < 1) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码文件没有工作表");
            }
            Sheet sheet = workbook.getSheetAt(0);
            boolean firstValue = true;
            for (Row row : sheet) {
                if (row == null) {
                    continue;
                }
                String code = clean(formatter.formatCellValue(row.getCell(0)));
                if (code.isBlank()) {
                    continue;
                }
                if (firstValue && isCodeHeader(code)) {
                    firstValue = false;
                    continue;
                }
                firstValue = false;
                codes.add(code);
                if (codes.size() > 5000) {
                    throw new BusinessException(ErrorCode.BAD_REQUEST, "单次最多导入5000个兑换码");
                }
            }
        } catch (IOException | EncryptedDocumentException | IllegalArgumentException exception) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码文件读取失败，请上传未加密的Excel文件");
        }
        if (codes.isEmpty()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码文件中未读取到兑换码");
        }
        return codes;
    }

    private boolean isCodeHeader(String value) {
        String text = clean(value).toUpperCase(Locale.ROOT);
        return List.of("兑换码", "券码", "码", "EXCHANGE_CODE", "CODE").contains(text);
    }

    private String normalizedGeneratedCodePrefix(String codePrefix) {
        String prefix = clean(codePrefix).replaceAll("[^A-Za-z0-9]", "").toUpperCase(Locale.ROOT);
        if (prefix.isBlank()) {
            return "EX";
        }
        return prefix.length() > 16 ? prefix.substring(0, 16) : prefix;
    }

    private String buildGeneratedExchangeCode(String codePrefix) {
        return normalizedGeneratedCodePrefix(codePrefix) + UUID.randomUUID().toString().replace("-", "").toUpperCase(Locale.ROOT);
    }

    private void apply(TicketPackage entity, TicketPackageSaveRequest request) {
        entity.setPackageName(cleanRequired(request.packageName(), "票包名称不能为空"));
        List<Long> merchantIds = normalizedMerchantIds(request);
        entity.setTotalQuantity(Math.max(request.totalQuantity() == null ? 0 : request.totalQuantity(), 0));
        entity.setMerchantId(merchantIds.isEmpty() ? null : merchantIds.get(0));
        entity.setCoverUrl(clean(request.coverUrl()));
        entity.setTalentLevels(joinTalentLevels(levelsFromRules(request)));
        entity.setClaimPrice(request.claimPrice() == null ? BigDecimal.ZERO : request.claimPrice());
        entity.setPerUserLimit(defaultPerUserLimit(request));
        entity.setValidStart(request.validStart());
        entity.setValidEnd(request.validEnd());
        if (entity.getValidStart() != null && entity.getValidEnd() != null && entity.getValidEnd().isBefore(entity.getValidStart())) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "有效结束日期不能早于有效开始日期");
        }
        entity.setAppName(clean(request.appName()));
        entity.setAppJumpUrl(clean(request.appJumpUrl()));
        entity.setUsageText(clean(request.usageText()));
        entity.setStatus(normalizeStatus(request.status()));
        entity.setSortNo(request.sortNo() == null ? 0 : request.sortNo());
    }

    private Map<String, Object> packageView(Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("id", value(row, "id"));
        result.put("packageNo", text(row, "packageNo", "package_no"));
        result.put("packageName", text(row, "packageName", "package_name"));
        result.put("totalQuantity", intValue(value(row, "totalQuantity", "total_quantity")));
        result.put("merchantId", value(row, "merchantId", "merchant_id"));
        result.put("merchantName", text(row, "merchantName", "merchant_name"));
        List<Long> merchantIds = splitLongs(text(row, "merchantIdsText", "merchant_ids_text"));
        Long legacyMerchantId = longValue(row, "merchantId", "merchant_id");
        if (merchantIds.isEmpty() && legacyMerchantId != null) {
            merchantIds = List.of(legacyMerchantId);
        }
        result.put("merchantIds", merchantIds);
        result.put("merchantNames", text(row, "merchantNames", "merchant_names"));
        result.put("coverUrl", text(row, "coverUrl", "cover_url"));
        result.put("talentLevels", splitLevels(text(row, "talentLevels", "talent_levels")));
        result.put("levelRules", parseLevelRules(text(row, "levelRulesText", "level_rules_text")));
        result.put("claimPrice", value(row, "claimPrice", "claim_price"));
        result.put("perUserLimit", intValue(value(row, "perUserLimit", "per_user_limit")));
        result.put("validStart", value(row, "validStart", "valid_start"));
        result.put("validEnd", value(row, "validEnd", "valid_end"));
        result.put("appName", text(row, "appName", "app_name"));
        result.put("appJumpUrl", text(row, "appJumpUrl", "app_jump_url"));
        result.put("usageText", text(row, "usageText", "usage_text"));
        result.put("status", intValue(value(row, "status")));
        result.put("sortNo", intValue(value(row, "sortNo", "sort_no")));
        result.put("totalCodes", intValue(value(row, "totalCodes", "total_codes")));
        result.put("availableCodes", intValue(value(row, "availableCodes", "available_codes")));
        result.put("lockedCodes", intValue(value(row, "lockedCodes", "locked_codes")));
        result.put("issuedCodes", intValue(value(row, "issuedCodes", "issued_codes")));
        result.put("usedCodes", intValue(value(row, "usedCodes", "used_codes")));
        result.put("voidCodes", intValue(value(row, "voidCodes", "void_codes")));
        result.put("claimedCount", intValue(value(row, "claimedCount", "claimed_count")));
        result.put("remainingCount", Math.max(intValue(value(row, "totalQuantity", "total_quantity")) - intValue(result.get("claimedCount")), 0));
        result.put("createdAt", value(row, "createdAt", "created_at"));
        result.put("updatedAt", value(row, "updatedAt", "updated_at"));
        return result;
    }

    private Map<String, Object> codeView(Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("id", value(row, "id"));
        result.put("packageId", value(row, "packageId", "package_id"));
        result.put("codeId", value(row, "id"));
        result.put("packageNo", text(row, "packageNo", "package_no"));
        result.put("packageName", text(row, "packageName", "package_name"));
        result.put("merchantName", text(row, "merchantName", "merchant_name"));
        result.put("merchantNames", text(row, "merchantNames", "merchant_names"));
        result.put("exchangeCode", text(row, "exchangeCode", "exchange_code"));
        result.put("batchNo", text(row, "batchNo", "batch_no"));
        result.put("status", text(row, "status"));
        result.put("statusName", codeStatusName(text(row, "status")));
        result.put("lockedByTalentId", value(row, "lockedByTalentId", "locked_by_talent_id"));
        result.put("lockedByCardId", value(row, "lockedByCardId", "locked_by_card_id"));
        result.put("lockedTalentName", text(row, "lockedTalentName", "locked_talent_name"));
        result.put("lockedTalentMobile", text(row, "lockedTalentMobile", "locked_talent_mobile"));
        result.put("lockedAt", value(row, "lockedAt", "locked_at"));
        result.put("issuedClaimId", value(row, "issuedClaimId", "issued_claim_id"));
        result.put("issuedAt", value(row, "issuedAt", "issued_at"));
        result.put("usedAt", value(row, "usedAt", "used_at"));
        result.put("remark", text(row, "remark"));
        result.put("createdAt", value(row, "createdAt", "created_at"));
        result.put("updatedAt", value(row, "updatedAt", "updated_at"));
        return result;
    }

    private Map<String, Object> claimView(Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("id", value(row, "id"));
        result.put("claimNo", text(row, "claimNo", "claim_no"));
        result.put("packageId", value(row, "packageId", "package_id"));
        result.put("packageName", text(row, "packageName", "package_name"));
        result.put("merchantId", value(row, "merchantId", "merchant_id"));
        result.put("merchantName", text(row, "merchantName", "merchant_name"));
        result.put("merchantNames", text(row, "merchantNames", "merchant_names"));
        result.put("talentId", value(row, "talentId", "talent_id"));
        result.put("cardId", value(row, "cardId", "card_id"));
        result.put("cardNo", text(row, "cardNo", "card_no"));
        result.put("cardLevel", text(row, "cardLevel", "card_level"));
        result.put("talentName", text(row, "talentName", "talent_name"));
        result.put("talentMobile", text(row, "talentMobile", "talent_mobile"));
        result.put("exchangeCode", text(row, "exchangeCode", "exchange_code"));
        result.put("status", text(row, "status"));
        result.put("statusName", claimStatusName(text(row, "status")));
        result.put("claimStatus", text(row, "status"));
        result.put("claimStatusName", claimStatusName(text(row, "status")));
        result.put("useStatus", "USED".equalsIgnoreCase(text(row, "status")) ? "USED" : "ACTIVE");
        result.put("useStatusName", "USED".equalsIgnoreCase(text(row, "status")) ? "已使用" : "未使用");
        result.put("claimedAt", value(row, "createdAt", "created_at"));
        result.put("validEnd", value(row, "validEnd", "valid_end"));
        result.put("source", claimSource(text(row, "idempotencyKey")));
        result.put("sourceName", "MANUAL".equals(claimSource(text(row, "idempotencyKey"))) ? "后台补录" : "线上领取");
        result.put("usedAt", value(row, "usedAt", "used_at"));
        result.put("createdAt", value(row, "createdAt", "created_at"));
        result.put("updatedAt", value(row, "updatedAt", "updated_at"));
        return result;
    }

    private Map<String, Object> requirePackageDetail(Long id) {
        Long packageId = requireId(id, "票包ID不能为空");
        Map<String, Object> row = packageMapper.selectAdminDetail(packageId);
        if (row == null || row.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "票包不存在");
        }
        return row;
    }

    private TicketPackage requirePackage(Long id) {
        Long packageId = requireId(id, "票包ID不能为空");
        TicketPackage ticketPackage = packageMapper.selectOne(new QueryWrapper<TicketPackage>()
                .eq("id", packageId)
                .eq("deleted", 0)
                .last("limit 1"));
        if (ticketPackage == null) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "票包不存在");
        }
        return ticketPackage;
    }

    private List<Long> validateMerchants(TicketPackageSaveRequest request) {
        List<Long> merchantIds = normalizedMerchantIds(request);
        for (Long merchantId : merchantIds) {
            BenefitMerchant merchant = merchantMapper.selectOne(new QueryWrapper<BenefitMerchant>()
                    .eq("id", merchantId)
                    .eq("deleted", 0)
                    .last("limit 1"));
            if (merchant == null) {
                throw new BusinessException(ErrorCode.NOT_FOUND, "指定商家不存在");
            }
        }
        return merchantIds;
    }

    private List<Long> normalizedMerchantIds(TicketPackageSaveRequest request) {
        List<Long> result = new ArrayList<>();
        if (request.merchantIds() != null) {
            for (Long merchantId : request.merchantIds()) {
                if (merchantId != null && merchantId > 0 && !result.contains(merchantId)) {
                    result.add(merchantId);
                }
            }
        }
        if (result.isEmpty() && request.merchantId() != null && request.merchantId() > 0) {
            result.add(request.merchantId());
        }
        return result;
    }

    private void savePackageMerchants(Long packageId, List<Long> merchantIds) {
        UpdateWrapper<TicketPackageMerchant> clear = new UpdateWrapper<TicketPackageMerchant>()
                .set("deleted", 1)
                .eq("package_id", packageId)
                .eq("deleted", 0);
        if (!merchantIds.isEmpty()) {
            clear.notIn("merchant_id", merchantIds);
        }
        packageMerchantMapper.update(null, clear);
        int sortNo = 0;
        for (Long merchantId : merchantIds) {
            TicketPackageMerchant current = packageMerchantMapper.selectOne(new QueryWrapper<TicketPackageMerchant>()
                    .eq("package_id", packageId)
                    .eq("merchant_id", merchantId)
                    .last("limit 1"));
            if (current == null) {
                TicketPackageMerchant row = new TicketPackageMerchant();
                row.setPackageId(packageId);
                row.setMerchantId(merchantId);
                row.setSortNo(sortNo++);
                row.setDeleted(0);
                packageMerchantMapper.insert(row);
            } else {
                packageMerchantMapper.update(null, new UpdateWrapper<TicketPackageMerchant>()
                        .set("sort_no", sortNo++)
                        .set("deleted", 0)
                        .eq("id", current.getId()));
            }
        }
    }

    private void saveLevelRules(Long packageId, TicketPackageSaveRequest request) {
        List<TicketPackageSaveRequest.TicketPackageLevelRuleRequest> rules = levelRules(request);
        List<String> ruleLevels = rules.stream()
                .map(rule -> normalizeCardLevel(rule.talentLevel()))
                .filter(level -> !level.isBlank())
                .distinct()
                .toList();
        UpdateWrapper<TicketPackageLevelRule> clear = new UpdateWrapper<TicketPackageLevelRule>()
                .set("deleted", 1)
                .eq("package_id", packageId)
                .eq("deleted", 0);
        if (!ruleLevels.isEmpty()) {
            clear.notIn("talent_level", ruleLevels);
        }
        levelRuleMapper.update(null, clear);
        for (TicketPackageSaveRequest.TicketPackageLevelRuleRequest rule : rules) {
            String level = normalizeCardLevel(rule.talentLevel());
            if (!CARD_LEVELS.contains(level)) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "适用人才卡等级仅支持卓越人才卡、杰出人才卡、菁英人才卡");
            }
            int enabled = rule.enabled() == null ? 1 : normalizeStatus(rule.enabled());
            int claimableCount = Math.max(rule.claimableCount() == null ? 1 : rule.claimableCount(), 1);
            String resetCycle = normalizeResetCycle(rule.resetCycle());
            TicketPackageLevelRule current = levelRuleMapper.selectOne(new QueryWrapper<TicketPackageLevelRule>()
                    .eq("package_id", packageId)
                    .eq("talent_level", level)
                    .last("limit 1"));
            if (current == null) {
                TicketPackageLevelRule entity = new TicketPackageLevelRule();
                entity.setPackageId(packageId);
                entity.setTalentLevel(level);
                entity.setEnabled(enabled);
                entity.setClaimableCount(claimableCount);
                entity.setResetCycle(resetCycle);
                entity.setDeleted(0);
                levelRuleMapper.insert(entity);
                continue;
            }
            TicketPackageLevelRule entity = new TicketPackageLevelRule();
            entity.setEnabled(enabled);
            entity.setClaimableCount(claimableCount);
            entity.setResetCycle(resetCycle);
            entity.setDeleted(0);
            levelRuleMapper.update(entity, new UpdateWrapper<TicketPackageLevelRule>().eq("id", current.getId()));
        }
    }

    private List<TicketPackageSaveRequest.TicketPackageLevelRuleRequest> levelRules(TicketPackageSaveRequest request) {
        if (request.levelRules() != null && !request.levelRules().isEmpty()) {
            return request.levelRules();
        }
        List<String> levels = request.talentLevels() == null || request.talentLevels().isEmpty() ? CARD_LEVELS : request.talentLevels();
        List<TicketPackageSaveRequest.TicketPackageLevelRuleRequest> result = new ArrayList<>();
        for (String level : CARD_LEVELS) {
            int enabled = levels.stream().anyMatch(item -> normalizeCardLevel(item).equals(level)) ? 1 : 0;
            result.add(new TicketPackageSaveRequest.TicketPackageLevelRuleRequest(level, enabled,
                    request.perUserLimit() == null ? 1 : request.perUserLimit(), "每天"));
        }
        return result;
    }

    private List<String> levelsFromRules(TicketPackageSaveRequest request) {
        List<String> result = new ArrayList<>();
        for (TicketPackageSaveRequest.TicketPackageLevelRuleRequest rule : levelRules(request)) {
            if (rule.enabled() == null || rule.enabled() == 1) {
                String level = normalizeCardLevel(rule.talentLevel());
                if (!result.contains(level)) {
                    result.add(level);
                }
            }
        }
        return result;
    }

    private int defaultPerUserLimit(TicketPackageSaveRequest request) {
        int limit = request.perUserLimit() == null ? 1 : request.perUserLimit();
        for (TicketPackageSaveRequest.TicketPackageLevelRuleRequest rule : levelRules(request)) {
            if (rule.enabled() == null || rule.enabled() == 1) {
                limit = Math.max(rule.claimableCount() == null ? limit : rule.claimableCount(), 1);
                break;
            }
        }
        return Math.max(limit, 1);
    }

    private String joinTalentLevels(List<String> levels) {
        if (levels == null || levels.isEmpty()) {
            return "";
        }
        List<String> cleaned = new ArrayList<>();
        for (String level : levels) {
            String value = clean(level);
            if (value.isBlank()) {
                continue;
            }
            value = normalizeCardLevel(value);
            if (!CARD_LEVELS.contains(value)) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, "适用人才卡等级仅支持卓越人才卡、杰出人才卡、菁英人才卡");
            }
            if (!cleaned.contains(value)) {
                cleaned.add(value);
            }
        }
        return String.join("、", cleaned);
    }

    private List<String> splitLevels(String value) {
        String text = clean(value);
        if (text.isBlank()) {
            return List.of();
        }
        return List.of(text.split("[、,，]"));
    }

    private List<Long> splitLongs(String value) {
        String text = clean(value);
        if (text.isBlank()) {
            return List.of();
        }
        List<Long> result = new ArrayList<>();
        for (String part : text.split(",")) {
            try {
                Long id = Long.valueOf(part.trim());
                if (id > 0 && !result.contains(id)) {
                    result.add(id);
                }
            } catch (NumberFormatException ignored) {
                // Ignore dirty historical data in aggregate text.
            }
        }
        return result;
    }

    private List<Map<String, Object>> parseLevelRules(String value) {
        Map<String, Map<String, Object>> byLevel = new HashMap<>();
        String text = clean(value);
        if (!text.isBlank()) {
            for (String item : text.split(";;")) {
                String[] parts = item.split("\\|", -1);
                if (parts.length < 4) {
                    continue;
                }
                String level = normalizeCardLevel(parts[0]);
                if (!CARD_LEVELS.contains(level)) {
                    continue;
                }
                Map<String, Object> row = new LinkedHashMap<>();
                row.put("talentLevel", level);
                row.put("enabled", parseInt(parts[1], 1));
                row.put("claimableCount", Math.max(parseInt(parts[2], 1), 1));
                row.put("resetCycle", normalizeResetCycle(parts[3]));
                byLevel.put(level, row);
            }
        }
        List<Map<String, Object>> result = new ArrayList<>();
        for (String level : CARD_LEVELS) {
            result.add(byLevel.getOrDefault(level, defaultLevelRule(level)));
        }
        return result;
    }

    private Map<String, Object> defaultLevelRule(String level) {
        Map<String, Object> row = new LinkedHashMap<>();
        row.put("talentLevel", level);
        row.put("enabled", 1);
        row.put("claimableCount", 1);
        row.put("resetCycle", "每天");
        return row;
    }

    private int parseInt(String value, int fallback) {
        try {
            return Integer.parseInt(clean(value));
        } catch (NumberFormatException exception) {
            return fallback;
        }
    }

    private String normalizeCardLevel(String level) {
        String value = clean(level);
        String upper = value.toUpperCase(Locale.ROOT);
        if (upper.contains("EXCELLENT") || value.contains("卓越")) {
            return "卓越人才卡";
        }
        if (upper.contains("OUTSTANDING") || value.contains("杰出")) {
            return "杰出人才卡";
        }
        if (upper.contains("ELITE") || value.contains("菁英")) {
            return "菁英人才卡";
        }
        return value;
    }

    private String normalizeResetCycle(String resetCycle) {
        String value = clean(resetCycle);
        if (value.isBlank()) {
            return "每天";
        }
        String upper = value.toUpperCase(Locale.ROOT);
        if (upper.contains("DAY") || value.contains("天") || value.contains("日")) {
            return "每天";
        }
        if (upper.contains("WEEK") || value.contains("周") || value.contains("星期")) {
            return "每周";
        }
        if (upper.contains("MONTH") || value.contains("月")) {
            return "每月";
        }
        if (upper.contains("YEAR") || upper.contains("ANNUAL") || value.contains("年")) {
            return "每年";
        }
        return value;
    }

    private int normalizeStatus(Integer status) {
        if (status == null) {
            return 1;
        }
        if (status != 0 && status != 1) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "状态只能为0停用或1启用");
        }
        return status;
    }

    private Integer normalizeStatusFilter(Integer status) {
        if (status == null) {
            return null;
        }
        return normalizeStatus(status);
    }

    private String normalizeCodeStatusFilter(String status) {
        String value = clean(status);
        return value.isBlank() ? "" : normalizeCodeStatus(value, true);
    }

    private String normalizeCodeStatus(String status, boolean allowIssuedLocked) {
        String value = clean(status).toUpperCase(Locale.ROOT);
        return switch (value) {
            case "AVAILABLE", "可发放" -> "AVAILABLE";
            case "VOID", "作废" -> "VOID";
            case "USED", "已使用" -> "USED";
            case "LOCKED", "锁定" -> allowIssuedLocked ? "LOCKED" : throwStatus();
            case "ISSUED", "已发放" -> allowIssuedLocked ? "ISSUED" : throwStatus();
            default -> throwStatus();
        };
    }

    private String throwStatus() {
        throw new BusinessException(ErrorCode.BAD_REQUEST, "兑换码状态不正确");
    }

    private String normalizeClaimStatusFilter(String status) {
        String value = clean(status);
        if (value.isBlank()) {
            return "";
        }
        return switch (value.toUpperCase(Locale.ROOT)) {
            case "ISSUED", "已发放" -> "ISSUED";
            case "USED", "已使用" -> "USED";
            case "EXPIRED", "已过期" -> "EXPIRED";
            case "CANCELLED", "CANCELED", "已取消" -> "CANCELLED";
            default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "发放状态不正确");
        };
    }

    private String codeStatusName(String status) {
        return switch (clean(status).toUpperCase(Locale.ROOT)) {
            case "LOCKED" -> "锁定";
            case "ISSUED" -> "已发放";
            case "USED" -> "已使用";
            case "VOID" -> "作废";
            default -> "可发放";
        };
    }

    private String claimStatusName(String status) {
        return switch (clean(status).toUpperCase(Locale.ROOT)) {
            case "USED" -> "已使用";
            case "EXPIRED" -> "已过期";
            case "CANCELLED", "CANCELED" -> "已取消";
            default -> "已发放";
        };
    }


    private void validateLockablePackage(TicketPackage ticketPackage) {
        if (ticketPackage == null || intValue(ticketPackage.getDeleted()) == 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "票包不存在");
        }
        if (intValue(ticketPackage.getStatus()) != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "票包当前不可发放");
        }
        LocalDate today = LocalDate.now();
        if (ticketPackage.getValidStart() != null && ticketPackage.getValidStart().isAfter(today)) {
            throw new BusinessException(ErrorCode.CONFLICT, "票包尚未开始发放");
        }
        if (ticketPackage.getValidEnd() != null && ticketPackage.getValidEnd().isBefore(today)) {
            throw new BusinessException(ErrorCode.CONFLICT, "票包已过期");
        }
    }

    private ClaimerInfo resolveClaimer(Long talentId, Long cardId, boolean lock) {
        if ((talentId == null || talentId <= 0) && (cardId == null || cardId <= 0)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "持卡人ID或人才卡ID不能为空");
        }
        Map<String, Object> source = lock
                ? claimMapper.selectActiveCardSnapshotForUpdate(talentId, cardId)
                : claimMapper.selectActiveCardSnapshot(talentId, cardId);
        if (source == null || source.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "有效人才卡不存在");
        }
        String cardType = text(source, "cardType", "card_type").toUpperCase(Locale.ROOT);
        if ("PERSONAL".equals(cardType) || cardType.isBlank()) {
            return new ClaimerInfo(
                    longValue(source, "talentId", "talent_id"),
                    longValue(source, "cardId", "card_id"),
                    text(source, "cardNo", "card_no"),
                    text(source, "cardLevel", "card_level"),
                    text(source, "talentName", "talent_name"),
                    text(source, "talentMobile", "talent_mobile"),
                    longValue(source, "cardId", "card_id"),
                    text(source, "cardLevel", "card_level")
            );
        }
        Map<String, Object> primary = lock
                ? claimMapper.selectPrimaryCardByFamilyCardForUpdate(longValue(source, "cardId", "card_id"))
                : claimMapper.selectPrimaryCardByFamilyCard(longValue(source, "cardId", "card_id"));
        if (primary == null || primary.isEmpty()) {
            throw new BusinessException(ErrorCode.CONFLICT, "亲属附属卡未找到有效主卡");
        }
        return new ClaimerInfo(
                longValue(source, "talentId", "talent_id"),
                longValue(source, "cardId", "card_id"),
                text(source, "cardNo", "card_no"),
                text(primary, "cardLevel", "card_level"),
                text(source, "talentName", "talent_name"),
                text(source, "talentMobile", "talent_mobile"),
                longValue(primary, "cardId", "card_id"),
                text(primary, "cardLevel", "card_level")
        );
    }

    private Map<String, Object> codeDetailView(Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("id", value(row, "id"));
        result.put("packageId", value(row, "packageId"));
        result.put("packageNo", text(row, "packageNo"));
        result.put("packageName", text(row, "packageName"));
        result.put("merchantId", value(row, "merchantId"));
        result.put("merchantName", text(row, "merchantName"));
        result.put("exchangeCode", text(row, "exchangeCode"));
        result.put("batchNo", text(row, "batchNo"));
        result.put("validStart", value(row, "validStart"));
        result.put("validEnd", value(row, "validEnd"));
        result.put("status", text(row, "status"));
        result.put("statusName", codeStatusName(text(row, "status")));
        result.put("lockedByTalentId", value(row, "lockedByTalentId"));
        result.put("lockedByCardId", value(row, "lockedByCardId"));
        result.put("lockedAt", value(row, "lockedAt"));
        result.put("issuedClaimId", value(row, "issuedClaimId"));
        result.put("issuedAt", value(row, "issuedAt"));
        result.put("usedAt", value(row, "usedAt"));
        result.put("claimNo", text(row, "claimNo"));
        result.put("claimStatus", text(row, "claimStatus"));
        result.put("claimTalentId", value(row, "claimTalentId"));
        result.put("claimTalentName", text(row, "claimTalentName"));
        result.put("claimTalentMobile", text(row, "claimTalentMobile"));
        result.put("claimCardNo", text(row, "claimCardNo"));
        result.put("claimCardLevel", text(row, "claimCardLevel"));
        result.put("remark", text(row, "remark"));
        result.put("createdAt", value(row, "createdAt"));
        result.put("updatedAt", value(row, "updatedAt"));
        return result;
    }

    private Map<String, Object> validationResult(boolean claimable, String reason, Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("found", row != null && !row.isEmpty());
        result.put("claimable", claimable);
        result.put("reason", reason);
        if (row != null && !row.isEmpty()) {
            result.put("codeId", value(row, "id"));
            result.put("exchangeCode", text(row, "exchangeCode"));
            result.put("packageId", value(row, "packageId"));
            result.put("packageNo", text(row, "packageNo"));
            result.put("packageName", text(row, "packageName"));
            result.put("merchantId", value(row, "merchantId"));
            result.put("merchantName", text(row, "merchantName"));
            result.put("validStart", value(row, "validStart"));
            result.put("validEnd", value(row, "validEnd"));
            result.put("status", text(row, "status"));
            result.put("statusName", codeStatusName(text(row, "status")));
            result.put("batchNo", text(row, "batchNo"));
            result.put("claimNo", text(row, "claimNo"));
            result.put("claimStatus", text(row, "claimStatus"));
            result.put("claimTalentName", text(row, "claimTalentName"));
            result.put("claimTalentMobile", text(row, "claimTalentMobile"));
            result.put("claimCardNo", text(row, "claimCardNo"));
            result.put("usedAt", value(row, "claimUsedAt"));
        }
        return result;
    }

    private String validationReason(Map<String, Object> row, Long merchantId) {
        if (intValue(value(row, "packageStatus")) != 1) {
            return "票包已停用";
        }
        LocalDate today = LocalDate.now();
        LocalDate validStart = toLocalDate(value(row, "validStart"));
        LocalDate validEnd = toLocalDate(value(row, "validEnd"));
        if (validStart != null && validStart.isAfter(today)) {
            return "票包尚未开始领取";
        }
        if (validEnd != null && validEnd.isBefore(today)) {
            return "票包已过期";
        }
        Long rowMerchantId = longValue(row, "merchantId");
        if (merchantId != null && rowMerchantId != null && !merchantId.equals(rowMerchantId)) {
            return "兑换码不适用于该商户";
        }
        return "";
    }

    private String codeStatusReason(String status) {
        return switch (clean(status).toUpperCase(Locale.ROOT)) {
            case "LOCKED" -> "兑换码已锁定";
            case "ISSUED" -> "兑换码已发放";
            case "USED" -> "兑换码已使用";
            case "VOID" -> "兑换码已作废";
            default -> "兑换码当前不可领取";
        };
    }

    private Map<String, Object> claimAdminView(TicketPackageClaim claim) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("id", claim.getId());
        result.put("claimNo", claim.getClaimNo());
        result.put("packageId", claim.getPackageId());
        result.put("codeId", claim.getCodeId());
        result.put("merchantId", claim.getMerchantId());
        result.put("talentId", claim.getTalentId());
        result.put("cardId", claim.getCardId());
        result.put("primaryCardId", claim.getPrimaryCardId());
        result.put("cardNo", claim.getCardNo());
        result.put("cardLevel", claim.getCardLevel());
        result.put("talentName", claim.getTalentName());
        result.put("talentMobile", claim.getTalentMobile());
        result.put("exchangeCode", claim.getExchangeCode());
        result.put("status", claim.getStatus());
        result.put("statusName", claimStatusName(claim.getStatus()));
        result.put("validEnd", claim.getValidEnd());
        result.put("source", claimSource(claim.getIdempotencyKey()));
        result.put("sourceName", "MANUAL".equals(claimSource(claim.getIdempotencyKey())) ? "后台补录" : "线上领取");
        result.put("usedAt", claim.getUsedAt());
        result.put("createdAt", claim.getCreatedAt());
        result.put("updatedAt", claim.getUpdatedAt());
        return result;
    }

    private String claimSource(String idempotencyKey) {
        return clean(idempotencyKey).startsWith("MANUAL-") ? "MANUAL" : "CLAIM";
    }

    private LocalDate toLocalDate(Object value) {
        if (value instanceof LocalDate date) {
            return date;
        }
        if (value instanceof java.sql.Date date) {
            return date.toLocalDate();
        }
        if (value instanceof java.sql.Timestamp timestamp) {
            return timestamp.toLocalDateTime().toLocalDate();
        }
        return null;
    }

    private Long longValue(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        return value instanceof Number number ? number.longValue() : null;
    }

    private record ClaimerInfo(
            Long talentId,
            Long cardId,
            String cardNo,
            String cardLevel,
            String talentName,
            String talentMobile,
            Long primaryCardId,
            String primaryCardLevel
    ) {
    }
    private Long requireId(Long id, String message) {
        if (id == null || id <= 0) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, message);
        }
        return id;
    }

    private String cleanRequired(String value, String message) {
        String text = clean(value);
        if (text.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, message);
        }
        return text;
    }

    private Object value(Map<String, Object> row, String... keys) {
        for (String key : keys) {
            if (row.containsKey(key)) {
                return row.get(key);
            }
        }
        return null;
    }

    private String text(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        return value == null ? "" : String.valueOf(value).trim();
    }

    private int intValue(Object value) {
        return value instanceof Number number ? number.intValue() : 0;
    }

    private String clean(String value) {
        return value == null ? "" : value.trim();
    }
}