package com.talentcard.server.service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.TemporalAdjusters;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import com.talentcard.server.entity.BenefitCinemaCouponClaim;
import com.talentcard.server.entity.BenefitFamilyCardApplication;
import com.talentcard.server.entity.BenefitFreeStayVoucher;
import com.talentcard.server.entity.BenefitMerchant;
import com.talentcard.server.entity.BenefitMerchantBenefitRule;
import com.talentcard.server.entity.BenefitMovieTicketRule;
import com.talentcard.server.entity.BenefitOrder;
import com.talentcard.server.entity.BenefitTalentCard;
import com.talentcard.server.entity.BenefitVerifyRecord;
import com.talentcard.server.mapper.BenefitCinemaCouponClaimMapper;
import com.talentcard.server.mapper.BenefitFamilyCardApplicationMapper;
import com.talentcard.server.mapper.BenefitFreeStayVoucherMapper;
import com.talentcard.server.mapper.BenefitMerchantBenefitRuleMapper;
import com.talentcard.server.mapper.BenefitMerchantMapper;
import com.talentcard.server.mapper.BenefitMovieTicketRuleMapper;
import com.talentcard.server.mapper.BenefitOrderMapper;
import com.talentcard.server.mapper.BenefitTalentCardMapper;
import com.talentcard.server.mapper.BenefitVerifyRecordMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import tools.jackson.core.type.TypeReference;
import tools.jackson.databind.ObjectMapper;

@Service
public class BenefitQuotaService {
    private static final ObjectMapper JSON_MAPPER = new ObjectMapper();
    private static final TypeReference<List<Map<String, Object>>> RIGHTS_TYPE = new TypeReference<>() {};

    public static final String MOVIE_TICKET = "MOVIE_TICKET";
    public static final String HOTEL = "HOTEL";

    private static final int DEFAULT_HOTEL_TOTAL = 5;
    private static final List<String> HOTEL_VERIFY_TYPES = List.of(
            "HOTEL", "HOTEL_FREE", "FREE_STAY", "HOTEL_ORDER", "HOTEL_RESERVATION"
    );

    private final BenefitTalentCardMapper cardMapper;
    private final BenefitFamilyCardApplicationMapper familyMapper;
    private final BenefitMerchantMapper merchantMapper;
    private final BenefitMovieTicketRuleMapper movieRuleMapper;
    private final BenefitCinemaCouponClaimMapper claimMapper;
    private final BenefitFreeStayVoucherMapper voucherMapper;
    private final BenefitMerchantBenefitRuleMapper merchantBenefitRuleMapper;
    private final BenefitVerifyRecordMapper verifyRecordMapper;
    private final BenefitOrderMapper orderMapper;
    private final JdbcTemplate jdbcTemplate;

    public BenefitQuotaService(
            BenefitTalentCardMapper cardMapper,
            BenefitFamilyCardApplicationMapper familyMapper,
            BenefitMerchantMapper merchantMapper,
            BenefitMovieTicketRuleMapper movieRuleMapper,
            BenefitCinemaCouponClaimMapper claimMapper,
            BenefitFreeStayVoucherMapper voucherMapper,
            BenefitMerchantBenefitRuleMapper merchantBenefitRuleMapper,
            BenefitVerifyRecordMapper verifyRecordMapper,
            BenefitOrderMapper orderMapper,
            JdbcTemplate jdbcTemplate
    ) {
        this.cardMapper = cardMapper;
        this.familyMapper = familyMapper;
        this.merchantMapper = merchantMapper;
        this.movieRuleMapper = movieRuleMapper;
        this.claimMapper = claimMapper;
        this.voucherMapper = voucherMapper;
        this.merchantBenefitRuleMapper = merchantBenefitRuleMapper;
        this.verifyRecordMapper = verifyRecordMapper;
        this.orderMapper = orderMapper;
        this.jdbcTemplate = jdbcTemplate;
    }

    public QuotaSnapshot requireAvailable(String rawBenefitType, Long cardId, Long talentId) {
        return requireAvailable(rawBenefitType, cardId, talentId, null, null, 1);
    }

    public QuotaSnapshot requireAvailable(String rawBenefitType, Long cardId, Long talentId,
                                          Long merchantId, Long productId, int quantity) {
        if (quantity < 1) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "核销数量必须大于0");
        }
        QuotaSnapshot snapshot = current(rawBenefitType, cardId, talentId, merchantId, productId);
        if (snapshot.remaining() < quantity) {
            throw new BusinessException(ErrorCode.CONFLICT, "主卡权益剩余次数不足");
        }
        return snapshot;
    }

    public QuotaSnapshot current(String rawBenefitType, Long cardId, Long talentId) {
        return current(rawBenefitType, cardId, talentId, null, null);
    }

    public QuotaSnapshot current(String rawBenefitType, Long cardId, Long talentId, Long merchantId, Long productId) {
        String benefitType = normalizeBenefitType(rawBenefitType);
        PrimaryCard primary = lockPrimaryCard(cardId, talentId);
        QuotaPolicy policy = policy(benefitType, primary, merchantId, productId);
        int used = usedQuota(benefitType, primary.sharedCardIds(), merchantId, policy.windowStart());
        return new QuotaSnapshot(
                benefitType,
                primary.sourceTalentId(),
                primary.sourceCardId(),
                primary.sourceCardNo(),
                primary.sourceCardType(),
                primary.primaryTalentId(),
                primary.primaryCardId(),
                primary.primaryCardNo(),
                primary.cardLevel(),
                policy.total(),
                used,
                Math.max(policy.total() - used, 0),
                policy.resetCycle(),
                policy.detail(),
                policy.windowStart(),
                primary.familyCard(),
                primary.sharedCardIds()
        );
    }

    public String normalizeBenefitType(String value) {
        String type = value == null ? "" : value.trim().toUpperCase(Locale.ROOT).replace('-', '_');
        return switch (type) {
            case "MOVIE", "MOVIE_TICKET", "CINEMA", "CINEMA_TICKET" -> MOVIE_TICKET;
            case "HOTEL", "FREE_STAY", "HOTEL_RESERVATION", "STAY" -> HOTEL;
            default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "权益类型仅支持MOVIE_TICKET电影票或HOTEL酒店");
        };
    }

    public List<String> cardLevelAliases(String cardLevel) {
        Set<String> aliases = new LinkedHashSet<>();
        String raw = cardLevel == null ? "" : cardLevel.trim();
        if (!raw.isBlank()) {
            aliases.add(raw);
        }
        String upper = raw.toUpperCase(Locale.ROOT);
        if (upper.contains("EXCELLENT") || raw.contains("卓越")) {
            aliases.add("EXCELLENT");
            aliases.add("卓越人才卡");
            aliases.add("卓越");
        }
        if (upper.contains("OUTSTANDING") || raw.contains("杰出")) {
            aliases.add("OUTSTANDING");
            aliases.add("杰出人才卡");
            aliases.add("杰出");
        }
        if (upper.contains("ELITE") || raw.contains("菁英") || raw.contains("精英")) {
            aliases.add("ELITE");
            aliases.add("菁英人才卡");
            aliases.add("菁英");
        }
        return aliases.isEmpty() ? List.of(raw) : List.copyOf(aliases);
    }

    private PrimaryCard lockPrimaryCard(Long cardId, Long talentId) {
        if (cardId == null && talentId == null) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "人才卡ID或人才用户ID不能为空");
        }
        BenefitTalentCard sourceCard = cardId == null ? activeCardForTalent(talentId) : card(cardId);
        Long sourceCardId = sourceCard.getId();
        Long sourceTalentId = sourceCard.getTalentId();
        String sourceCardType = text(sourceCard.getCardType()).toUpperCase(Locale.ROOT);
        String sourceAreaType = sourceAreaType(sourceTalentId);
        if ("PERSONAL".equals(sourceCardType) || sourceCardType.isBlank()) {
            return primaryCard(sourceCardId, sourceTalentId, sourceCard.getCardNo(), sourceCardType, sourceCardId, sourceTalentId);
        }

        BenefitFamilyCardApplication owner = familyMapper.selectOne(new QueryWrapper<BenefitFamilyCardApplication>()
                .eq("deleted", 0)
                .eq("family_card_id", sourceCardId)
                .isNotNull("talent_id")
                .orderByDesc("id")
                .last("limit 1"));
        if (owner == null) {
            throw new BusinessException(ErrorCode.CONFLICT, "亲属附属卡未找到绑定的主卡");
        }
        BenefitTalentCard primary = activePersonalCard(owner.getTalentId());
        return primaryCard(sourceCardId, sourceTalentId, sourceCard.getCardNo(), sourceCardType,
                primary.getId(), primary.getTalentId());
    }

    private PrimaryCard primaryCard(Long sourceCardId, Long sourceTalentId, String sourceCardNo, String sourceCardType,
                                    Long primaryCardId, Long primaryTalentId) {
        BenefitTalentCard primary = cardMapper.selectOne(new QueryWrapper<BenefitTalentCard>()
                .eq("id", primaryCardId)
                .eq("talent_id", primaryTalentId)
                .eq("deleted", 0)
                .last("limit 1 for update"));
        if (primary == null) {
            throw new BusinessException(ErrorCode.CONFLICT, "主卡不存在");
        }
        if (intValue(primary.getStatus()) != 1 || notStarted(primary.getValidStart())
                || expiredDate(primary.getValidEnd()) || expiredDateTime(primary.getExpireTime())) {
            throw new BusinessException(ErrorCode.CONFLICT, "主卡当前不可用");
        }
        return new PrimaryCard(
                sourceTalentId,
                sourceCardId,
                sourceCardNo,
                sourceCardType,
                primaryTalentId,
                primaryCardId,
                primary.getCardNo(),
                text(primary.getCardLevel()),
                sourceAreaType(primaryTalentId),
                !"PERSONAL".equals(sourceCardType),
                sharedCardIds(primaryCardId, primaryTalentId)
        );
    }

    private List<Long> sharedCardIds(Long primaryCardId, Long primaryTalentId) {
        Set<Long> ids = new LinkedHashSet<>();
        ids.add(primaryCardId);
        List<BenefitFamilyCardApplication> applications = familyMapper.selectList(new QueryWrapper<BenefitFamilyCardApplication>()
                .eq("deleted", 0)
                .eq("talent_id", primaryTalentId)
                .isNotNull("family_card_id"));
        for (BenefitFamilyCardApplication application : applications) {
            if (application.getFamilyCardId() != null) {
                ids.add(application.getFamilyCardId());
            }
        }
        if (ids.size() == 1) {
            return List.copyOf(ids);
        }
        List<BenefitTalentCard> activeCards = cardMapper.selectList(new QueryWrapper<BenefitTalentCard>()
                .select("id")
                .eq("deleted", 0)
                .eq("status", 1)
                .in("id", ids));
        Set<Long> activeIds = new LinkedHashSet<>();
        activeIds.add(primaryCardId);
        for (BenefitTalentCard card : activeCards) {
            activeIds.add(card.getId());
        }
        return List.copyOf(activeIds);
    }

    private BenefitTalentCard card(Long cardId) {
        BenefitTalentCard card = cardMapper.selectOne(new QueryWrapper<BenefitTalentCard>()
                .eq("id", cardId)
                .eq("deleted", 0)
                .last("limit 1"));
        if (card == null) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "人才卡不存在");
        }
        if (intValue(card.getStatus()) != 1 || notStarted(card.getValidStart())
                || expiredDate(card.getValidEnd()) || expiredDateTime(card.getExpireTime())) {
            throw new BusinessException(ErrorCode.CONFLICT, "人才卡当前不可用");
        }
        return card;
    }

    private BenefitTalentCard activeCardForTalent(Long talentId) {
        BenefitTalentCard card = cardMapper.selectOne(new QueryWrapper<BenefitTalentCard>()
                .eq("talent_id", talentId)
                .eq("deleted", 0)
                .eq("status", 1)
                .and(wrapper -> wrapper.isNull("valid_start").or().le("valid_start", LocalDate.now()))
                .and(wrapper -> wrapper.isNull("valid_end").or().ge("valid_end", LocalDate.now()))
                .and(wrapper -> wrapper.isNull("expire_time").or().gt("expire_time", LocalDateTime.now()))
                .last("order by case when card_type = 'PERSONAL' then 0 else 1 end, id desc limit 1"));
        if (card == null) {
            throw new BusinessException(ErrorCode.CONFLICT, "未找到有效人才卡");
        }
        return card;
    }

    private BenefitTalentCard activePersonalCard(Long talentId) {
        BenefitTalentCard card = cardMapper.selectOne(new QueryWrapper<BenefitTalentCard>()
                .eq("talent_id", talentId)
                .eq("card_type", "PERSONAL")
                .eq("deleted", 0)
                .eq("status", 1)
                .and(wrapper -> wrapper.isNull("valid_start").or().le("valid_start", LocalDate.now()))
                .and(wrapper -> wrapper.isNull("valid_end").or().ge("valid_end", LocalDate.now()))
                .and(wrapper -> wrapper.isNull("expire_time").or().gt("expire_time", LocalDateTime.now()))
                .orderByDesc("id")
                .last("limit 1"));
        if (card == null) {
            throw new BusinessException(ErrorCode.CONFLICT, "未找到有效主卡");
        }
        return card;
    }

    private QuotaPolicy policy(String benefitType, PrimaryCard primary, Long merchantId, Long productId) {
        if (MOVIE_TICKET.equals(benefitType)) {
            return moviePolicy(primary);
        }
        return hotelPolicy(primary, merchantId, productId);
    }

    private QuotaPolicy moviePolicy(PrimaryCard primary) {
        List<String> aliases = cardLevelAliases(primary.cardLevel());
        BenefitMovieTicketRule rule = movieRuleMapper.selectOne(new QueryWrapper<BenefitMovieTicketRule>()
                .in("talent_level", aliases)
                .last("limit 1"));
        if (rule == null || intValue(rule.getEnabled()) != 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前卡类型未启用电影票权益");
        }
        int total = intValue(rule.getClaimableCount());
        if (total < 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前卡类型未配置电影票权益次数");
        }
        String resetCycle = text(rule.getResetCycle());
        return new QuotaPolicy(total, resetCycle, "", windowStart(resetCycle, true));
    }

    private QuotaPolicy hotelPolicy(PrimaryCard primary, Long merchantId, Long productId) {
        if (merchantId != null) {
            QuotaPolicy merchantRightsPolicy = merchantRightsPolicy(primary, merchantId);
            if (merchantRightsPolicy != null) {
                return merchantRightsPolicy;
            }

            List<String> aliases = cardLevelAliases(primary.cardLevel());
            QueryWrapper<BenefitMerchantBenefitRule> query = new QueryWrapper<BenefitMerchantBenefitRule>()
                    .eq("deleted", 0)
                    .eq("status", 1)
                    .eq("merchant_id", merchantId)
                    .in("card_level", aliases)
                    .isNotNull("free_count")
                    .gt("free_count", 0);
            if (productId == null) {
                query.isNull("product_id").orderByDesc("id").last("limit 1");
            } else {
                query.and(wrapper -> wrapper.eq("product_id", productId).or().isNull("product_id"))
                        .last("order by case when product_id = " + productId + " then 0 else 1 end, id desc limit 1");
            }
            BenefitMerchantBenefitRule rule = merchantBenefitRuleMapper.selectOne(query);
            if (rule != null) {
                if (primary.familyCard() && intValue(rule.getCanFamilyUse()) != 1) {
                    throw new BusinessException(ErrorCode.CONFLICT, "亲属附属卡不可使用该酒店权益");
                }
                String resetCycle = text(rule.getResetCycle());
                return new QuotaPolicy(intValue(rule.getFreeCount()), resetCycle, "", windowStart(resetCycle, false));
            }
        }

        QueryWrapper<BenefitFreeStayVoucher> voucherQuery = new QueryWrapper<BenefitFreeStayVoucher>()
                .select("total_count")
                .eq("talent_id", primary.primaryTalentId())
                .eq("status", 1)
                .ge("valid_end", LocalDate.now())
                .orderByDesc("total_count")
                .last("limit 1");
        if (merchantId != null) {
            voucherQuery.eq("merchant_id", merchantId);
        }
        BenefitFreeStayVoucher voucher = voucherMapper.selectOne(voucherQuery);
        int configured = voucher == null || intValue(voucher.getTotalCount()) < 1 ? DEFAULT_HOTEL_TOTAL : voucher.getTotalCount();
        return new QuotaPolicy(configured, "YEARLY", "", windowStart("YEARLY", false));
    }

    private QuotaPolicy merchantRightsPolicy(PrimaryCard primary, Long merchantId) {
        BenefitMerchant merchant = merchantMapper.selectById(merchantId);
        if (merchant == null || intValue(merchant.getDeleted()) != 0 || intValue(merchant.getStatus()) != 1
                || !isHotelMerchantType(merchant.getMerchantType())) {
            throw new BusinessException(ErrorCode.CONFLICT, "未找到有效酒店商户");
        }
        String config = text(merchant.getMerchantRightsConfig());
        if (config.isBlank()) {
            return null;
        }

        Map<String, Object> matched = null;
        Map<String, Object> disabledMatched = null;
        String cardLevel = normalizeCardLevel(primary.cardLevel());
        List<String> candidates = cardLevelCandidates(cardLevel, primary.sourceAreaType());
        try {
            List<Map<String, Object>> rights = JSON_MAPPER.readValue(config, RIGHTS_TYPE);
            matched = matchingMerchantRight(rights, candidates, true, primary.sourceAreaType());
            if (matched == null) {
                disabledMatched = matchingMerchantRight(rights, candidates, false, primary.sourceAreaType());
            }
        } catch (Exception exception) {
            throw new BusinessException(ErrorCode.INTERNAL_ERROR, "酒店商户权益配置格式不正确");
        }
        if (matched == null && disabledMatched == null) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前主卡类型未配置酒店权益");
        }
        Map<String, Object> selected = matched != null ? matched : disabledMatched;
        if (selected == null || !booleanValue(selected.get("enabled"))) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前主卡类型未启用酒店权益");
        }

        Object countValue = firstValue(selected, "freeVoucherTimes", "freeCount", "freeLimitCount", "limitCount");
        Object freeValue = firstValue(selected, "freeVoucherEnabled", "freeEnabled", "free");
        if (freeValue != null && !booleanValue(freeValue)) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前主卡类型未启用酒店免费券");
        }
        int total = intValue(countValue);
        if (total < 1) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前主卡类型未配置酒店免费券次数");
        }

        Object familyShared = firstValue(selected, "familyCardShared", "canFamilyUse", "familyUseEnabled");
        if (primary.familyCard() && familyShared != null && !booleanValue(familyShared)) {
            throw new BusinessException(ErrorCode.CONFLICT, "亲属附属卡不可共享该酒店权益");
        }
        String resetCycle = text(firstValue(selected, "freeVoucherCycle", "freeResetCycle", "resetCycle"));
        if (resetCycle.isBlank()) {
            throw new BusinessException(ErrorCode.CONFLICT, "当前主卡类型未配置酒店免费券重置周期");
        }
        String detail = text(firstValue(selected, "detail", "benefitDetail", "description"));
        return new QuotaPolicy(total, resetCycle, detail, windowStart(resetCycle, false));
    }

    private List<String> cardLevelCandidates(String cardLevel, String sourceAreaType) {
        String level = normalizeCardLevel(cardLevel);
        String area = normalizedSourceAreaType(sourceAreaType);
        if ("EXCELLENT".equals(level)) {
            if ("LOCAL".equals(area)) {
                return List.of("卓越人才卡（本地）", "卓越人才卡");
            }
            if ("OUTSIDE".equals(area)) {
                return List.of("卓越人才卡（外地）", "卓越人才卡");
            }
            return List.of("卓越人才卡");
        }
        if ("OUTSTANDING".equals(level)) {
            return List.of("杰出人才卡");
        }
        if ("ELITE".equals(level)) {
            return List.of("菁英人才卡");
        }
        return cardLevel == null || cardLevel.isBlank() ? List.of() : List.of(cardLevel);
    }

    private Map<String, Object> matchingMerchantRight(List<Map<String, Object>> rights, List<String> candidates, boolean requireEnabled, String sourceAreaType) {
        if (rights == null || rights.isEmpty() || candidates == null || candidates.isEmpty()) {
            return null;
        }
        // 第一轮：完整等级名匹配，按“（本地）/（外地）”分开配置时命中对应地区的那一条。
        for (String candidate : candidates) {
            for (Map<String, Object> right : rights) {
                if (requireEnabled && !booleanValue(right.get("enabled"))) {
                    continue;
                }
                String configured = text(right.get("level"));
                if (candidate.equals(configured)) return right;
            }
        }
        // 第二轮：退化成只比等级，但人才地区已知时不能用地区对不上的配置。
        String area = normalizedSourceAreaType(sourceAreaType);
        for (String candidate : candidates) {
            for (Map<String, Object> right : rights) {
                if (requireEnabled && !booleanValue(right.get("enabled"))) {
                    continue;
                }
                String configured = text(right.get("level"));
                if (!sameCardLevel(configured, candidate)) continue;
                if (!areaMatchesConfiguredLevel(configured, area)) continue;
                return right;
            }
        }
        return null;
    }

    /** 人才地区已知时，带“（本地）/（外地）”后缀的商家配置必须与人才地区一致；没带后缀的配置不受限制。 */
    private boolean areaMatchesConfiguredLevel(String configuredLevel, String area) {
        if (area == null || area.isBlank()) return true;
        String level = text(configuredLevel).toUpperCase(Locale.ROOT);
        boolean configuredOutside = level.contains("外地") || level.contains("OUTSIDE");
        boolean configuredLocal = level.contains("本地") || level.contains("LOCAL");
        if (!configuredOutside && !configuredLocal) return true;
        return "OUTSIDE".equals(area) ? configuredOutside : ("LOCAL".equals(area) && configuredLocal);
    }

    private Object firstValue(Map<String, Object> values, String... keys) {
        for (String key : keys) {
            Object value = values.get(key);
            if (value != null && !text(value).isBlank()) {
                return value;
            }
        }
        return null;
    }

    private String normalizeCardLevel(Object value) {
        String raw = text(value);
        String upper = raw.toUpperCase(Locale.ROOT);
        if (upper.contains("EXCELLENT") || raw.contains("卓越")) {
            return "EXCELLENT";
        }
        if (upper.contains("OUTSTANDING") || raw.contains("杰出")) {
            return "OUTSTANDING";
        }
        if (upper.contains("ELITE") || raw.contains("菁英") || raw.contains("精英")) {
            return "ELITE";
        }
        return upper;
    }

    private boolean booleanValue(Object value) {
        if (value instanceof Boolean bool) {
            return bool;
        }
        if (value instanceof Number number) {
            return number.intValue() != 0;
        }
        String raw = text(value);
        return "true".equalsIgnoreCase(raw) || "1".equals(raw) || "yes".equalsIgnoreCase(raw);
    }

    private boolean isHotelMerchantType(String merchantType) {
        String type = text(merchantType);
        return "hotel".equalsIgnoreCase(type) || "酒店服务".equals(type);
    }

    private String sourceAreaType(Long talentId) {
        if (talentId == null) return "";
        List<Map<String, Object>> rows = query("""
                select source_area_type
                from talent_user
                where deleted = 0 and id = ? and status = 1
                limit 1
                """, talentId);
        if (rows.isEmpty()) return "";
        String raw = text(rows.get(0).get("sourceAreaType"));
        if ("OUTSIDE".equalsIgnoreCase(raw) || raw.contains("外地")) return "OUTSIDE";
        if ("LOCAL".equalsIgnoreCase(raw) || raw.contains("本地")) return "LOCAL";
        return "";
    }

    private String normalizedSourceAreaType(Object value) {
        String raw = text(value).toUpperCase(Locale.ROOT);
        if (raw.contains("OUTSIDE") || raw.contains("外地")) return "OUTSIDE";
        if (raw.contains("LOCAL") || raw.contains("本地")) return "LOCAL";
        return "";
    }

    private boolean sameCardLevel(Object configured, String actual) {
        return normalizeCardLevel(configured).equals(normalizeCardLevel(actual));
    }

    private int usedQuota(String benefitType, List<Long> sharedCardIds, Long merchantId, LocalDateTime windowStart) {
        if (MOVIE_TICKET.equals(benefitType)) {
            return usedMovieQuota(sharedCardIds, windowStart);
        }
        return usedHotelQuota(sharedCardIds, merchantId, windowStart);
    }

    private int usedMovieQuota(List<Long> sharedCardIds, LocalDateTime windowStart) {
        Long used = claimMapper.selectCount(new QueryWrapper<BenefitCinemaCouponClaim>()
                .eq("deleted", 0)
                .in("card_id", sharedCardIds)
                .apply("((upper(coalesce(code_status, 'ACTIVE')) = 'USED' and used_at >= {0}) or "
                        + "(upper(coalesce(code_status, 'ACTIVE')) = 'ACTIVE' and updated_at >= {1}))", windowStart, windowStart));
        return intValue(used);
    }

    private int usedHotelQuota(List<Long> sharedCardIds, Long merchantId, LocalDateTime windowStart) {
        QueryWrapper<BenefitVerifyRecord> recordQuery = new QueryWrapper<BenefitVerifyRecord>()
                .in("card_id", sharedCardIds)
                .eq("status", 1)
                .ge("verify_time", windowStart)
                .and(wrapper -> wrapper.in("verify_type", HOTEL_VERIFY_TYPES)
                        .or()
                        .apply("exists (select 1 from benefit_order o "
                                + "join merchant m on m.id = o.merchant_id and m.deleted = 0 "
                                + "and m.merchant_type_code = 'hotel' "
                                + "where o.id = verify_record.order_id and o.deleted = 0)"));
        if (merchantId != null) {
            recordQuery.and(wrapper -> wrapper.eq("merchant_id", merchantId)
                    .or()
                    .apply("exists (select 1 from benefit_order o where o.id = verify_record.order_id "
                            + "and o.deleted = 0 and o.merchant_id = {0})", merchantId));
        }
        Long records = verifyRecordMapper.selectCount(recordQuery);

        QueryWrapper<BenefitOrder> orderQuery = new QueryWrapper<BenefitOrder>()
                .eq("deleted", 0)
                .in("card_id", sharedCardIds)
                .eq("verify_status", 1)
                .ge("updated_at", windowStart)
                .apply("exists (select 1 from merchant m where m.id = benefit_order.merchant_id "
                        + "and m.deleted = 0 and m.merchant_type_code = 'hotel')")
                .apply("not exists (select 1 from verify_record r where r.order_id = benefit_order.id and r.status = 1)");
        if (merchantId != null) {
            orderQuery.eq("merchant_id", merchantId);
        }
        Long ordersWithoutRecord = orderMapper.selectCount(orderQuery);
        return intValue(records) + intValue(ordersWithoutRecord);
    }

    private List<Map<String, Object>> query(String sql, Object... args) {
        return DatabaseRows.normalize(jdbcTemplate.queryForList(sql, args));
    }

    private LocalDateTime windowStart(String resetCycle, boolean monthlyDefault) {
        String cycle = resetCycle == null ? "" : resetCycle.trim().toUpperCase(Locale.ROOT);
        LocalDate today = LocalDate.now();
        if (cycle.isBlank() || "NONE".equals(cycle)) {
            return LocalDate.of(1970, 1, 1).atStartOfDay();
        }
        if (cycle.contains("DAY") || cycle.contains("DAILY") || cycle.contains("天") || cycle.contains("日")) {
            return today.atStartOfDay();
        }
        if (cycle.contains("WEEK") || cycle.contains("周") || cycle.contains("星期")) {
            return today.minusDays(today.getDayOfWeek().getValue() - 1L).atStartOfDay();
        }
        if (cycle.contains("YEAR") || cycle.contains("ANNUAL") || cycle.contains("年")) {
            return today.with(TemporalAdjusters.firstDayOfYear()).atStartOfDay();
        }
        if (cycle.contains("MONTH") || cycle.contains("月") || monthlyDefault) {
            return today.withDayOfMonth(1).atStartOfDay();
        }
        return today.with(TemporalAdjusters.firstDayOfYear()).atStartOfDay();
    }

    private boolean notStarted(LocalDate value) {
        return value != null && value.isAfter(LocalDate.now());
    }

    private boolean expiredDate(LocalDate value) {
        return value != null && value.isBefore(LocalDate.now());
    }

    private boolean expiredDateTime(LocalDateTime value) {
        return value != null && !value.isAfter(LocalDateTime.now());
    }

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

    private int intValue(Object value) {
        if (value instanceof Number number) {
            return number.intValue();
        }
        try {
            String raw = text(value);
            return raw.isBlank() ? 0 : Integer.parseInt(raw);
        } catch (NumberFormatException ignored) {
            return 0;
        }
    }

    private record PrimaryCard(
            Long sourceTalentId,
            Long sourceCardId,
            String sourceCardNo,
            String sourceCardType,
            Long primaryTalentId,
            Long primaryCardId,
            String primaryCardNo,
            String cardLevel,
            String sourceAreaType,
            boolean familyCard,
            List<Long> sharedCardIds
    ) {
    }

    private record QuotaPolicy(int total, String resetCycle, String detail, LocalDateTime windowStart) {
    }

    public record QuotaSnapshot(
            String benefitType,
            Long talentId,
            Long cardId,
            String cardNo,
            String cardType,
            Long primaryTalentId,
            Long primaryCardId,
            String primaryCardNo,
            String cardLevel,
            int total,
            int used,
            int remaining,
            String resetCycle,
            String detail,
            LocalDateTime windowStart,
            boolean familyCard,
            List<Long> sharedCardIds
    ) {
    }
}