package com.talentcard.server.business.portal;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Locale;

import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.business.merchant.MerchantTypeCatalog;
import com.talentcard.server.business.merchant.MerchantTypeCatalog.MerchantType;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.jsoup.Jsoup;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import tools.jackson.core.type.TypeReference;
import tools.jackson.databind.ObjectMapper;

@Service
public class PortalService {

	private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();
	private final JdbcTemplate jdbcTemplate;

	public PortalService(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Transactional(readOnly = true)
	public Map<String, Object> home(Long talentId) {
		Map<String, Object> result = new LinkedHashMap<>();
		result.put("notices", query("""
				select id, title, content, notice_type, created_at
				from notice_message
				where receiver_type = 'TALENT'
				order by created_at desc
				limit 3
				"""));
		result.put("news", query("""
				select id, title, summary, cover_url, source, view_count, is_top, publish_time
				from news_article
				where deleted = 0 and status = 1
				order by is_top desc, case when is_top = 1 then updated_at end desc, publish_time desc, id desc
				limit 4
				"""));
		result.put("policies", query("""
				select id, policy_no, title, subtitle, cover_url, is_top, publish_time
				from policy_article
				where deleted = 0 and status = 1
				order by is_top desc, sort_no desc, publish_time desc, id desc
				limit 5
				"""));
		result.put("benefits", query("""
				select id, benefit_name, benefit_type, cover_url, description
				from benefit
				where deleted = 0 and status = 1
				order by sort_no desc, id desc
				limit 8
				"""));
		result.put("recommendedMerchants", query("""
				select id, merchant_name, merchant_type_code as merchant_type, logo_url, cover_url, address
				from merchant
				where deleted = 0 and status = 1 and is_recommend = 1
				order by sort_no desc, id desc
				limit 6
				"""));
		Map<String, Object> homeSettings = new LinkedHashMap<>();
		for (Map<String, Object> row : query("""
				select setting_key, setting_value from admin_setting
				where setting_key in ('homeVideoUrl', 'miniProgramUrl')
				""")) {
			homeSettings.put(String.valueOf(row.get("settingKey")), row.get("settingValue"));
		}
		result.put("homeVideoUrl", String.valueOf(homeSettings.getOrDefault("homeVideoUrl", "")));
		result.put("miniProgramUrl", String.valueOf(homeSettings.getOrDefault("miniProgramUrl", "")));
		if (talentId != null) {
			result.put("cards", query("""
					select id, talent_id, card_no, card_type, card_level, valid_start, valid_end, status
					from talent_card
					where deleted = 0 and talent_id = ?
					order by id desc
					""", talentId));
		}
		return result;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> news(int page, int size) {
		PageResult<Map<String, Object>> result = page("""
				select id, title, summary, cover_url, source, content, view_count, is_top, publish_time
				from news_article
				where deleted = 0 and status = 1
				order by is_top desc, case when is_top = 1 then updated_at end desc, publish_time desc, id desc
				""", "select count(*) from news_article where deleted = 0 and status = 1", page, size);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = new LinkedHashMap<>(row);
			item.put("contentPreview", htmlToText((String) item.get("content")));
			item.remove("content");
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional
	public Map<String, Object> newsDetail(Long id) {
		int updated = jdbcTemplate.update("update news_article set view_count = view_count + 1 where id = ? and deleted = 0 and status = 1", id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "资讯不存在");
		}
		return one("""
				select id, title, summary, cover_url, source, author, content, view_count, is_top, publish_time
				from news_article
				where deleted = 0 and status = 1 and id = ?
				""", id);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> policies(int page, int size) {
		PageResult<Map<String, Object>> result = page("""
				select id, policy_no, title, subtitle, category_name, cover_url, tags, author, source, content, view_count, is_top, publish_time
				from policy_article
				where deleted = 0 and status = 1
				order by is_top desc, sort_no desc, publish_time desc, id desc
				""", "select count(*) from policy_article where deleted = 0 and status = 1", page, size);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = new LinkedHashMap<>(row);
			item.put("contentPreview", htmlToText((String) item.get("content")));
			item.remove("content");
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional
	public Map<String, Object> policy(Long id) {
		int updated = jdbcTemplate.update("update policy_article set view_count = view_count + 1 where id = ? and deleted = 0 and status = 1", id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "政策已下架或不存在");
		}
		Map<String, Object> policy = one("""
				select id, policy_no, title, subtitle, category_name, cover_url, content_images, tags, author, source, content, view_count, publish_time
				from policy_article
				where deleted = 0 and status = 1 and id = ?
				""", id);
		policy.put("categoryName", textOrDefault(policy.get("categoryName"), "东区政策"));
		policy.put("heroImage", policy.get("coverUrl"));
		policy.put("contentImages", imageUrls(policy.get("contentImages")));
		policy.put("contentText", htmlToText((String) policy.get("content")));
		return policy;
	}

	private String htmlToText(String content) {
		return content == null || content.isBlank() ? "" : Jsoup.parse(content).wholeText().trim();
	}

	private String textOrDefault(Object value, String fallback) {
		if (value == null) return fallback;
		String text = String.valueOf(value).trim();
		return text.isEmpty() ? fallback : text;
	}

	private List<String> imageUrls(Object value) {
		if (value == null || String.valueOf(value).isBlank()) return List.of();
		try {
			return OBJECT_MAPPER.readValue(String.valueOf(value), new TypeReference<List<String>>() {}).stream()
					.filter(url -> url != null && !url.isBlank())
					.toList();
		} catch (Exception ignored) {
			return List.of();
		}
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> benefits(String type, int page, int size) {
		if (type == null || type.isBlank()) {
			return page("""
					select id, benefit_name, benefit_type, cover_url, description, valid_start, valid_end, total_quota, used_quota
					from benefit
					where deleted = 0 and status = 1
					order by sort_no desc, id desc
					""", "select count(*) from benefit where deleted = 0 and status = 1", page, size);
		}
		return page("""
				select id, benefit_name, benefit_type, cover_url, description, valid_start, valid_end, total_quota, used_quota
				from benefit
				where deleted = 0 and status = 1 and benefit_type = ?
				order by sort_no desc, id desc
				""", "select count(*) from benefit where deleted = 0 and status = 1 and benefit_type = ?", page, size, type);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> merchants(
			String type,
			String keyword,
			String categoryId,
			String mode,
			String sortField,
			String sortOrder,
			Long talentId,
			int page,
			int size
	) {
		String merchantWhere = " where deleted = 0 and status = 1";
		List<Object> args = new java.util.ArrayList<>();
		String requestedType = categoryId == null || categoryId.isBlank() ? type : categoryId;
		if (requestedType != null && !requestedType.isBlank() && !"all".equalsIgnoreCase(requestedType)) {
			merchantWhere = appendMerchantTypeFilter(merchantWhere, merchantTypesForRequestedValue(requestedType), args);
		}
		if (keyword != null && !keyword.isBlank()) {
			List<String> categoryTypes = merchantTypesForCategory(keyword.trim());
			if (!categoryTypes.isEmpty()) {
				merchantWhere = appendMerchantTypeFilter(merchantWhere, categoryTypes, args);
			} else {
				merchantWhere += " and (merchant_name like ? or address like ? or merchant_type like ?)";
			String like = "%" + keyword.trim() + "%";
				args.add(like);
				args.add(like);
				args.add(like);
			}
		}
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       is_recommend, sort_no, longitude, latitude, business_status, service_label, merchant_rights_config,
				       (exists(select 1 from merchant_coupon c
				               where c.merchant_id = merchant.id and c.deleted = 0 and c.status = 1
				                 and (c.valid_start is null or c.valid_start <= current_date)
				                 and (c.valid_end is null or c.valid_end >= current_date))
				        or (merchant.merchant_type_code = 'cinema' and exists(select 1 from ticket_package tp
				               where tp.deleted = 0 and tp.status = 1
				                 and (tp.valid_start is null or tp.valid_start <= current_date)
				                 and (tp.valid_end is null or tp.valid_end >= current_date)
				                 and (tp.merchant_id = merchant.id or exists (
				                     select 1 from ticket_package_merchant tpm
				                     where tpm.package_id = tp.id and tpm.merchant_id = merchant.id and tpm.deleted = 0
				                 ))))) as coupon_available,
				       (select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
				       (select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
				from merchant
				""" + merchantWhere + " order by sort_no asc, id asc",
				"select count(*) from merchant" + merchantWhere, page, size, args.toArray());
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			Long merchantId = item.get("id") instanceof Number id ? id.longValue() : null;
			item.put("couponAvailable", booleanValue(row.get("couponAvailable")));
			item.put("discountText", merchantId == null ? "" : merchantLowestTalentDiscount(merchantId, talentId));
			item.put("benefitTags", merchantListBenefitTags(item, row, talentId));
			item.remove("merchantRightsConfig");
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> foodTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'food'
				""");
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'food' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> foodMerchants(int page, int size, Long talentId) {
		String merchantWhere = " where deleted = 0 and merchant_type_code = 'food'";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       status, sort_no, longitude, latitude, business_status,
				       (select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
				       (select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
				from merchant
				""" + merchantWhere + " order by sort_no asc, id asc",
				"select count(*) from merchant" + merchantWhere, page, size);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			Long merchantId = item.get("id") instanceof Number id ? id.longValue() : null;
			boolean couponAvailable = merchantId != null && hasActiveMerchantCoupon(merchantId);
			String discountText = merchantId == null ? "" : merchantLowestTalentDiscount(merchantId, talentId);
			item.put("couponAvailable", couponAvailable);
			item.put("discountText", discountText);
			item.put("benefitTags", merchantBenefitTags("food", couponAvailable, discountText, null));
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> travelTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'city'
				""");
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'city' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> travelMerchants(int page, int size) {
		return travelMerchants(page, size, null);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> travelMerchants(int page, int size, Long talentId) {
		String merchantWhere = " where deleted = 0 and merchant_type_code = 'scenic'";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       status, sort_no, longitude, latitude, business_status,
				       (select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
				       (select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
				from merchant
				""" + merchantWhere + " order by sort_no asc, id asc",
				"select count(*) from merchant" + merchantWhere, page, size);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("merchantType", "scenic");
			Long merchantId = item.get("id") instanceof Number id ? id.longValue() : null;
			boolean couponAvailable = merchantId != null && hasActiveMerchantCoupon(merchantId);
			String discountText = merchantId == null ? "" : merchantLowestTalentDiscount(merchantId, talentId);
			item.put("couponAvailable", couponAvailable);
			item.put("discountText", discountText);
			item.put("benefitTags", merchantBenefitTags("scenic", couponAvailable, discountText, null));
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> stayTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'home'
				""");
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'home' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> stayMerchants(String type, int page, int size) {
		return stayMerchants(type, page, size, null);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> stayMerchants(String type, int page, int size, Long talentId) {
		String requestedType = MerchantTypeCatalog.canonicalCode(type);
		boolean apartment = switch (requestedType) {
			case "hotel" -> false;
			case "apartment" -> true;
			default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的住宿服务类型");
		};
		String responseMerchantType = apartment ? "apartment" : "hotel";
		String merchantWhere = " where deleted = 0 and merchant_type_code = '" + responseMerchantType + "'";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       status, sort_no, longitude, latitude, business_status, group_buying_enabled, merchant_rights_config,
				       (select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
				       (select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
				from merchant
				""" + merchantWhere + " order by sort_no asc, id asc",
				"select count(*) from merchant" + merchantWhere, page, size);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("merchantType", responseMerchantType);
			Long merchantId = item.get("id") instanceof Number id ? id.longValue() : null;
			boolean couponAvailable = merchantId != null && hasActiveMerchantCoupon(merchantId);
			String discountText = merchantId == null ? "" : merchantLowestTalentDiscount(merchantId, talentId);
			Map<String, Object> right = matchingMerchantRight(
					merchantRightsConfigs(row.get("merchantRightsConfig")),
					activeProductCardLevel(talentId),
					activeTalentSourceAreaType(talentId),
					true);
			item.put("couponAvailable", couponAvailable);
			item.put("discountText", discountText);
			item.put("benefitTags", merchantBenefitTags(responseMerchantType, couponAvailable, discountText, right));
			item.remove("merchantRightsConfig");
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	private List<String> merchantListBenefitTags(Map<String, Object> item, Map<String, Object> row, Long talentId) {
		String merchantType = MerchantTypeCatalog.canonicalCode(textOrDefault(item.get("merchantType"), ""));
		boolean couponAvailable = booleanValue(item.get("couponAvailable"));
		String discountText = textOrDefault(item.get("discountText"), "").trim();
		String cardLevel = activeProductCardLevel(talentId);
		String sourceAreaType = activeTalentSourceAreaType(talentId);
		Map<String, Object> right = matchingMerchantRight(
				merchantRightsConfigs(row.get("merchantRightsConfig")), cardLevel, sourceAreaType, true);
		if (usesMerchantDetailHeaderTags(merchantType) || "cinema".equals(merchantType)) {
			return merchantBenefitTags(merchantType, couponAvailable, discountText, right);
		}

		boolean shoppingType = MerchantTypeCatalog.find(merchantType)
				.map(type -> type.shoppingCategoryKey() != null)
				.orElse(false);
		if (shoppingType) {
			List<String> tags = new ArrayList<>();
			if (couponAvailable) tags.add("券");
			if (right != null) {
				String configuredDiscount = merchantRightDiscountText(right);
				if (!configuredDiscount.isBlank()) tags.add(configuredDiscount);
			}
			return tags;
		}

		String serviceLabel = textOrDefault(row.get("serviceLabel"), "").trim();
		String categoryTag = !serviceLabel.isBlank() ? serviceLabel : discountText;
		if (categoryTag.isBlank()) categoryTag = "人才专享";
		return List.of(categoryTag);
	}

	private boolean usesMerchantDetailHeaderTags(String merchantType) {
		return List.of("food", "scenic", "hotel", "apartment", "dental", "culture").contains(merchantType);
	}

	private List<String> merchantBenefitTags(
			String merchantType,
			boolean couponAvailable,
			String productDiscountText,
			Map<String, Object> right
	) {
		String type = MerchantTypeCatalog.canonicalCode(merchantType);
		if ("cinema".equals(type)) {
			return couponAvailable ? List.of("券") : List.of();
		}
		List<String> tags = new ArrayList<>();
		if (couponAvailable) tags.add("券");
		String discount = textOrDefault(productDiscountText, "").trim();
		if ("人才专享".equals(discount)) discount = "";
		boolean hotelLike = "hotel".equals(type) || "apartment".equals(type);
		if (hotelLike && right != null && booleanValue(right.get("freeVoucherEnabled"))) {
			String times = textOrDefault(right.get("freeVoucherTimes"), "").trim();
			discount = times.isBlank() ? "免费" : "免费" + times + "次";
		}
		if (!discount.isBlank() && !tags.contains(discount)) tags.add(discount);
		if (hotelLike && right != null && booleanValue(right.get("storeConsume")) && booleanValue(right.get("consumeEnabled"))) {
			String consumeType = textOrDefault(right.get("consumeType"), "");
			String consumeDiscount = textOrDefault(right.get("consumeDiscount"), "").trim();
			String consumeCoupon = textOrDefault(right.get("consumeCoupon"), "").trim();
			String consumeTag = "";
			if ("折扣".equals(consumeType) && !consumeDiscount.isBlank()) consumeTag = consumeDiscount + "折";
			else if ("优惠".equals(consumeType) && !consumeCoupon.isBlank()) consumeTag = "减" + consumeCoupon;
			if (!consumeTag.isBlank() && !tags.contains(consumeTag)) tags.add(consumeTag);
		}
		return tags;
	}

	@Transactional(readOnly = true)
	public Map<String, Object> entertainmentTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'entertainment'
				""");
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'entertainment' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> entertainmentMerchants(int page, int size) {
		String merchantWhere = " where deleted = 0 and merchant_type_code = 'cinema'";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       service_label, status, sort_no, longitude, latitude, business_status,
				       (exists(select 1 from merchant_coupon c
				               where c.merchant_id = merchant.id and c.deleted = 0 and c.status = 1
				                 and (c.valid_start is null or c.valid_start <= current_date)
				                 and (c.valid_end is null or c.valid_end >= current_date))
				        or exists(select 1 from ticket_package p
				                  where p.deleted = 0 and p.status = 1
				                    and (p.valid_start is null or p.valid_start <= current_date)
				                    and (p.valid_end is null or p.valid_end >= current_date)
				                    and (p.merchant_id = merchant.id or exists (
				                        select 1 from ticket_package_merchant pm
				                        where pm.package_id = p.id and pm.merchant_id = merchant.id and pm.deleted = 0
				                    )))) as coupon_available,
				       (select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
				       (select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
				from merchant
				""" + merchantWhere + " order by sort_no asc, id asc",
				"select count(*) from merchant" + merchantWhere, page, size);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			boolean couponAvailable = booleanValue(item.get("couponAvailable"));
			item.put("couponAvailable", couponAvailable);
			item.put("discountText", couponAvailable ? "券" : "");
			item.put("benefitTags", merchantBenefitTags("cinema", couponAvailable, "", null));
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> transportTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'transport'
				""");
		topic.put("title", topic.get("categoryLabel"));
		topic.put("themeColor", "#F26A38");
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'transport' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> transportMerchants(String type, int page, int size, Long talentId) {
		String requestedType = MerchantTypeCatalog.canonicalCode(type);
		if (!List.of("fuel", "parking", "carWash").contains(requestedType)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的行服务类型");
		}
		String merchantWhere = " where deleted = 0 and merchant_type_code = '" + requestedType + "'";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       service_label, status, sort_no, longitude, latitude, business_status, merchant_rights_config,
				       (select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
				       (select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
				from merchant
				""" + merchantWhere + " order by sort_no asc, id asc",
				"select count(*) from merchant" + merchantWhere, page, size);
		String cardLevel = "carWash".equals(requestedType) ? activeProductCardLevel(talentId) : null;
		String sourceAreaType = "carWash".equals(requestedType) ? activeTalentSourceAreaType(talentId) : "";
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("merchantType", requestedType);
			if ("carWash".equals(requestedType)) {
				List<Map<String, Object>> rights = merchantRightsConfigs(item.get("merchantRightsConfig"));
				String externalLinkType = carWashExternalLinkType(rights);
				Map<String, Object> right = matchingMerchantRight(rights, cardLevel, sourceAreaType, true);
				String discountText = carWashListDiscountText(externalLinkType, right);
				item.put("externalLinkType", externalLinkType);
				item.put("discountText", discountText);
				item.put("benefitTagVisible", !discountText.isBlank());
			} else {
				item.put("discountText", textOrDefault(item.get("serviceLabel"), textOrDefault(item.get("discountText"), "人才专享")));
			}
			item.remove("merchantRightsConfig");
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> shoppingTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'shopping'
				""");
		topic.put("title", topic.get("categoryLabel"));
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'shopping' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		topic.put("categories", query("""
				select category_key as id, category_name as name, sort_no
				from shopping_topic_category
				where deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> shoppingMerchants(String categoryId, int page, int size) {
		return shoppingMerchants(categoryId, page, size, null);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> shoppingMerchants(String categoryId, int page, int size, Long talentId) {
		if (categoryId == null || categoryId.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择购物服务分类");
		int categoryCount = jdbcTemplate.queryForObject("""
				select count(*) from shopping_topic_category
				where category_key = ? and enabled = 1 and deleted = 0
				""", Integer.class, categoryId);
		if (categoryCount == 0) throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的购物服务分类");
		List<String> merchantTypeCodes = MerchantTypeCatalog.codesForShoppingCategory(categoryId);
		if (merchantTypeCodes.isEmpty()) throw new BusinessException(ErrorCode.BAD_REQUEST, "该购物分类未配置商家类型");
		String placeholders = String.join(",", java.util.Collections.nCopies(merchantTypeCodes.size(), "?"));
		String merchantWhere = """
				where m.deleted = 0
				  and m.merchant_type_code in (%s)
				""".formatted(placeholders);
		List<Object> args = new ArrayList<>();
		args.add(categoryId);
		args.addAll(merchantTypeCodes);
		PageResult<Map<String, Object>> result = page("""
				select m.id, m.merchant_name, m.merchant_type_code as merchant_type, m.contact_name, m.contact_mobile, m.logo_url, m.cover_url, m.address,
				       m.service_label, m.status, m.sort_no, m.longitude, m.latitude, m.business_status, m.merchant_rights_config
				from merchant m
				left join shopping_topic_merchant mapping on mapping.merchant_id = m.id and mapping.deleted = 0 and mapping.category_key = ?
				""" + merchantWhere + " order by mapping.sort_no asc, m.sort_no asc, m.id asc",
				"select count(*) from merchant m left join shopping_topic_merchant mapping on mapping.merchant_id = m.id and mapping.deleted = 0 and mapping.category_key = ? " + merchantWhere,
				page, size, args.toArray());
		String cardLevel = activeProductCardLevel(talentId);
		String sourceAreaType = activeTalentSourceAreaType(talentId);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			List<Map<String, Object>> rights = merchantRightsConfigs(row.get("merchantRightsConfig"));
			Map<String, Object> configuredRight = matchingMerchantRight(rights, cardLevel, sourceAreaType, true);
			Map<String, Object> levelRight = matchingMerchantRight(rights, cardLevel, sourceAreaType, false);
			Long merchantId = row.get("id") instanceof Number number ? number.longValue() : null;
			item.put("couponAvailable", merchantId != null && hasActiveMerchantCoupon(merchantId));
			item.put("storeConsumeAvailable", configuredRight != null
					&& booleanValue(configuredRight.get("storeConsume"))
					&& booleanValue(configuredRight.get("consumeEnabled")));
			String merchantType = MerchantTypeCatalog.canonicalCode(textOrDefault(item.get("merchantType"), ""));
			boolean couponAvailable = booleanValue(item.get("couponAvailable"));
			if (configuredRight != null) {
				item.put("discountText", merchantRightDiscountText(configuredRight));
				item.put("rightsAvailable", true);
			} else if (levelRight != null || !rights.isEmpty()) {
				item.put("discountText", levelRight == null ? "" : merchantRightDiscountText(levelRight));
				item.put("rightsAvailable", false);
				item.put("available", false);
				item.put("unavailableReason", levelRight == null ? "当前人才卡暂未配置该权益" : "当前人才卡暂未开通该权益");
			} else {
				item.put("discountText", textOrDefault(item.get("serviceLabel"), ""));
				item.put("rightsAvailable", true);
			}
			if (usesMerchantDetailHeaderTags(merchantType)) {
				String productDiscount = merchantId == null ? "" : merchantLowestTalentDiscount(merchantId, talentId);
				item.put("discountText", productDiscount);
				item.put("benefitTags", merchantBenefitTags(merchantType, couponAvailable, productDiscount, configuredRight));
			} else {
				List<String> tags = new ArrayList<>();
				if (couponAvailable) tags.add("券");
				String storeDiscount = textOrDefault(item.get("discountText"), "").trim();
				if (!storeDiscount.isBlank() && !"人才专享".equals(storeDiscount)) tags.add(storeDiscount);
				item.put("benefitTags", tags);
			}
			item.remove("merchantRightsConfig");
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	private String merchantRightDiscountText(Map<String, Object> right) {
		if (!booleanValue(right.get("storeConsume")) || !booleanValue(right.get("consumeEnabled"))) return "";
		String discount = textOrDefault(right.get("discount"), "").trim();
		if (discount.isBlank()) return "";
		try {
			BigDecimal value = new BigDecimal(discount).stripTrailingZeros();
			if (value.compareTo(BigDecimal.ZERO) <= 0 || value.compareTo(BigDecimal.TEN) > 0) return "";
			return value.toPlainString() + "折";
		} catch (NumberFormatException ignored) {
			return "";
		}
	}

	@Transactional(readOnly = true)
	public Map<String, Object> premiumTopic() {
		Map<String, Object> topic = one("""
				select category_key, category_label, intro
				from equity_image_category
				where category_key = 'premium'
				""");
		topic.put("title", topic.get("categoryLabel"));
		topic.put("description", topic.get("intro"));
		topic.put("images", query("""
				select id, item_name as name, image_url, sort_no
				from equity_image_item
				where category_key = 'premium' and deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		topic.put("categories", query("""
				select category_key as id, category_name as name, business_type, sort_no
				from premium_topic_category
				where deleted = 0 and enabled = 1
				order by sort_no asc, id asc
				"""));
		return topic;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> premiumServices(String type, int page, int size) {
		String categoryId = jdbcTemplate.query("select category_key from premium_topic_category where category_name = ? and enabled = 1 and deleted = 0 limit 1", rs -> rs.next() ? rs.getString("category_key") : null, type == null ? "" : type.trim());
		if (categoryId == null || categoryId.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择臻享服务分类");
		Integer categoryCount = jdbcTemplate.queryForObject("""
				select count(*) from premium_topic_category
				where category_key = ? and enabled = 1 and deleted = 0
				""", Integer.class, categoryId);
		if (categoryCount == null || categoryCount == 0) throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的臻享服务分类");
		MerchantType merchantType = MerchantTypeCatalog.forPremiumCategory(categoryId)
				.orElseThrow(() -> new BusinessException(ErrorCode.BAD_REQUEST, "该臻享分类未配置商家类型"));
		String serviceWhere = " where m.deleted = 0 and m.merchant_type_code = ?";
		PageResult<Map<String, Object>> result = page("""
				select m.id, m.merchant_name, m.merchant_type_code as merchant_type, m.contact_mobile, m.cover_url, m.address,
				       m.service_label, m.status, m.sort_no, m.longitude, m.latitude,
				       mapping.sort_no as topic_sort_no
				from merchant m
				left join premium_topic_merchant mapping on mapping.merchant_id = m.id and mapping.deleted = 0 and mapping.category_key = ?
				""" + serviceWhere + " order by mapping.sort_no asc, m.sort_no asc, m.id asc",
				"select count(*) from merchant m left join premium_topic_merchant mapping on mapping.merchant_id = m.id and mapping.deleted = 0 and mapping.category_key = ? " + serviceWhere,
				page, size, categoryId, merchantType.code());
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("businessType", merchantType.premiumBusinessType());
			item.put("targetAction", merchantType.premiumBusinessType());
			item.put("benefitText", textOrDefault(row.get("serviceLabel"), "人才专享"));
			item.put("discountText", textOrDefault(row.get("serviceLabel"), "人才专享"));
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	private Map<String, Object> enrichMerchantSearchRow(Map<String, Object> row) {
		Map<String, Object> result = new LinkedHashMap<>(row);
		String merchantType = String.valueOf(result.getOrDefault("merchantType", ""));
		result.put("categoryName", categoryName(merchantType));
		result.put("detailType", detailType(merchantType));
		result.put("detailBusinessId", result.get("id"));
		result.put("targetAction", targetAction(merchantType));
		Object status = result.get("status");
		result.put("available", !(status instanceof Number number) || number.intValue() == 1);
		result.put("sort", result.get("sortNo"));
		result.put("discountText", discountText(result.get("salePrice"), result.get("originalPrice")));
		return result;
	}

	private String categoryName(String merchantType) {
		return MerchantTypeCatalog.find(merchantType).map(MerchantType::topicName).orElse("其他权益");
	}

	private String appendMerchantTypeFilter(String merchantWhere, List<String> merchantTypes, List<Object> args) {
		if (merchantTypes.size() == 1) {
			args.add(merchantTypes.get(0));
			return merchantWhere + " and merchant_type_code = ?";
		}
		args.addAll(merchantTypes);
		String placeholders = String.join(",", Collections.nCopies(merchantTypes.size(), "?"));
		return merchantWhere + " and merchant_type_code in (" + placeholders + ")";
	}

	private List<String> merchantTypesForRequestedValue(String value) {
		List<String> types = MerchantTypeCatalog.codesForRequestedValue(value);
		return types.isEmpty() ? List.of("other") : types;
	}

	private List<String> merchantTypesForCategory(String value) {
		return MerchantTypeCatalog.codesForRequestedValue(value);
	}

	private String detailType(String merchantType) {
		return MerchantTypeCatalog.find(merchantType).map(MerchantType::detailType).orElse("MERCHANT");
	}

	private String targetAction(String merchantType) {
		return detailType(merchantType);
	}

	private String discountText(Object salePrice, Object originalPrice) {
		if (salePrice instanceof Number sale && originalPrice instanceof Number original
				&& original.doubleValue() > 0 && sale.doubleValue() == 0) {
			return "免费";
		}
		if (salePrice instanceof Number sale && originalPrice instanceof Number original
				&& sale.doubleValue() > 0 && original.doubleValue() > sale.doubleValue()) {
			double discount = Math.round(sale.doubleValue() / original.doubleValue() * 100) / 10.0;
			return discount + "折";
		}
		return "人才专享";
	}

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

	@Transactional(readOnly = true)
	public Map<String, Object> merchantDetail(Long id, Long talentId) {
		Map<String, Object> merchant = one("""
				select id, merchant_name, merchant_type_code as merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       business_status, longitude, latitude, group_buying_enabled, usage_instructions, rights_text, merchant_rights_config,
				       service_label, status, is_recommend, sort_no
				from merchant
				where deleted = 0 and id = ?
				""", id);
		merchant.put("available", ((Number) merchant.getOrDefault("status", 0)).intValue() == 1);
		merchant.put("groupBuyingEnabled", ((Number) merchant.getOrDefault("groupBuyingEnabled", 1)).intValue() == 1);
		merchant.put("couponAvailable", hasActiveMerchantCoupon(id));
		merchant.put("discountText", merchantLowestTalentDiscount(id, talentId));
		String cardLevel = activeProductCardLevel(talentId);
		merchant.put("cardLevel", cardLevel);
		String sourceAreaType = activeTalentSourceAreaType(talentId);
		merchant.put("sourceAreaType", sourceAreaType);
		List<Map<String, Object>> rightsConfigs = merchantRightsConfigs(merchant.get("merchantRightsConfig"));
		Map<String, Object> configuredRight = matchingMerchantRight(rightsConfigs, cardLevel, sourceAreaType, true);
		Map<String, Object> levelRight = matchingMerchantRight(rightsConfigs, cardLevel, sourceAreaType, false);
		boolean freeVoucherEnabled = false;
		String freeVoucherTimes = "";
		String freeVoucherCycle = "";
		String consumeType = "";
		String consumeDiscount = "";
		String consumeCoupon = "";
		boolean storeConsume = false;
		boolean consumeEnabled = false;
		merchant.put("rightsAvailable", configuredRight != null);
		if (configuredRight != null) {
			merchant.put("rightsTag", textOrDefault(configuredRight.get("tag"), ""));
			merchant.put("rightsText", textOrDefault(configuredRight.get("detail"), textOrDefault(merchant.get("rightsText"), "")));
			merchant.put("applicableCards", textOrDefault(configuredRight.get("tag"), ""));
			merchant.put("rightsStatus", "AVAILABLE");
			freeVoucherEnabled = booleanValue(configuredRight.get("freeVoucherEnabled"));
			freeVoucherTimes = textOrDefault(configuredRight.get("freeVoucherTimes"), "");
			freeVoucherCycle = textOrDefault(configuredRight.get("freeVoucherCycle"), "");
			consumeType = textOrDefault(configuredRight.get("consumeType"), "");
			consumeDiscount = textOrDefault(configuredRight.get("consumeDiscount"), "");
			consumeCoupon = textOrDefault(configuredRight.get("consumeCoupon"), "");
			storeConsume = booleanValue(configuredRight.get("storeConsume"));
			consumeEnabled = booleanValue(configuredRight.get("consumeEnabled"));
		} else if (levelRight != null) {
			merchant.put("rightsTag", textOrDefault(levelRight.get("tag"), ""));
			merchant.put("rightsText", "当前人才卡暂未开通该权益");
			merchant.put("applicableCards", textOrDefault(levelRight.get("tag"), ""));
			merchant.put("rightsStatus", "DISABLED");
		} else if (!rightsConfigs.isEmpty()) {
			merchant.put("rightsTag", "");
			merchant.put("rightsText", "当前人才卡暂未配置该权益");
			merchant.put("applicableCards", "");
			merchant.put("rightsStatus", "MISSING");
		} else {
			merchant.put("rightsStatus", "UNCONFIGURED");
		}
		merchant.put("freeVoucherEnabled", freeVoucherEnabled);
		merchant.put("freeVoucherTimes", freeVoucherTimes);
		merchant.put("freeVoucherCycle", freeVoucherCycle);
		merchant.put("consumeType", consumeType);
		merchant.put("consumeDiscount", consumeDiscount);
		merchant.put("consumeCoupon", consumeCoupon);
		merchant.put("storeConsume", storeConsume);
		merchant.put("consumeEnabled", consumeEnabled);
		merchant.put("benefitTags", merchantBenefitTags(
				textOrDefault(merchant.get("merchantType"), ""),
				booleanValue(merchant.get("couponAvailable")),
				textOrDefault(merchant.get("discountText"), ""),
				configuredRight));
		merchant.put("usageInstructions", merchantUsageInstructions(
				id, merchant.get("merchantRightsConfig"), cardLevel, merchant.get("usageInstructions")));
		List<String> heroImages = new ArrayList<>();
		String coverUrl = textOrDefault(merchant.get("coverUrl"), "");
		if (!coverUrl.isBlank()) heroImages.add(coverUrl);
		for (Map<String, Object> product : query("""
				select cover_url
				from merchant_product
				where merchant_id = ? and deleted = 0 and status = 1 and audit_status = 2
				  and cover_url is not null and cover_url <> ''
				order by sort_no desc, id desc
				limit 8
				""", id)) {
			String imageUrl = textOrDefault(product.get("coverUrl"), "");
			if (!imageUrl.isBlank() && !heroImages.contains(imageUrl)) heroImages.add(imageUrl);
		}
		merchant.put("heroImages", heroImages);
		return merchant;
	}

	private Map<String, Object> matchingMerchantRight(List<Map<String, Object>> rights, String cardLevel, String sourceAreaType, boolean requireEnabled) {
		if (cardLevel == null) return null;
		List<String> candidates = merchantRightCandidates(cardLevel, sourceAreaType);
		for (String candidate : candidates) {
			for (Map<String, Object> right : rights) {
				if (requireEnabled && !booleanValue(right.get("enabled"))) continue;
				if (candidate.equals(textOrDefault(right.get("level"), ""))) return right;
				if (sameCardLevel(right.get("level"), candidate)) return right;
			}
		}
		return null;
	}

	private List<String> merchantRightCandidates(String cardLevel, String sourceAreaType) {
		String level = normalizedCardLevel(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 String cardLevelText(String cardLevel) {
		return switch (normalizedCardLevel(cardLevel)) {
			case "EXCELLENT" -> "卓越卡";
			case "OUTSTANDING" -> "杰出卡";
			case "ELITE" -> "菁英卡";
			default -> "人才卡";
		};
	}

	private String merchantUsageInstructions(Long merchantId, Object rightsConfig, String cardLevel, Object fallback) {
		if (cardLevel != null) {
			for (Map<String, Object> right : merchantRightsConfigs(rightsConfig)) {
				if (!booleanValue(right.get("enabled")) || !booleanValue(right.get("consumeEnabled"))
						|| !booleanValue(right.get("storeConsume")) || !sameCardLevel(right.get("level"), cardLevel)) continue;
				String description = textOrDefault(right.get("consumeDesc"), "");
				if (!description.isBlank()) return description;
			}
			List<Map<String, Object>> benefits = query("""
					select card_level, benefit_text
					from merchant_card_benefit
					where merchant_id = ? and status = 1
					order by sort_no desc, id desc
					""", merchantId);
			for (Map<String, Object> benefit : benefits) {
				if (sameCardLevel(benefit.get("cardLevel"), cardLevel)) {
					String description = textOrDefault(benefit.get("benefitText"), "");
					if (!description.isBlank()) return description;
				}
			}
		}
		return textOrDefault(fallback, "以当前商户到店消费规则为准");
	}

	private List<Map<String, Object>> merchantRightsConfigs(Object value) {
		if (value == null || String.valueOf(value).isBlank()) return List.of();
		try {
			return OBJECT_MAPPER.readValue(String.valueOf(value), new TypeReference<List<Map<String, Object>>>() {});
		} catch (Exception ignored) {
			return List.of();
		}
	}

	private String carWashExternalLinkType(List<Map<String, Object>> rights) {
		for (Map<String, Object> right : rights) {
			if (right.containsKey("externalLinkType")
					|| right.containsKey("externalMiniProgramShortLink")
					|| right.containsKey("externalMiniProgramPath")
					|| right.containsKey("externalWebUrl")) {
				return textOrDefault(right.get("externalLinkType"), "");
			}
		}
		return "";
	}

	private String carWashListDiscountText(String externalLinkType, Map<String, Object> right) {
		if ("miniProgram".equals(externalLinkType) || right == null) return "";
		return merchantRightDiscountText(right);
	}

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

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

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

	private String normalizedCardLevel(Object value) {
		String level = value == null ? "" : String.valueOf(value).trim().toUpperCase(Locale.ROOT);
		if (level.contains("卓越") || "EXCELLENT".equals(level)) return "EXCELLENT";
		if (level.contains("杰出") || "OUTSTANDING".equals(level)) return "OUTSTANDING";
		if (level.contains("菁英") || "ELITE".equals(level)) return "ELITE";
		return level;
	}

	private boolean hasActiveMerchantCoupon(Long merchantId) {
		Long count = jdbcTemplate.queryForObject("""
				select count(*)
				from merchant_coupon
				where merchant_id = ? and deleted = 0 and status = 1
				  and (valid_start is null or valid_start <= current_date)
				  and (valid_end is null or valid_end >= current_date)
				""", Long.class, merchantId);
		return count != null && count > 0;
	}

	private String merchantLowestTalentDiscount(Long merchantId, Long talentId) {
		String cardLevel = activeProductCardLevel(talentId);
		if (cardLevel == null) return "";
		BigDecimal lowestRate = null;
		for (Map<String, Object> product : query("""
				select id, original_price, sale_price, rights_config
				from merchant_product
				where merchant_id = ? and deleted = 0 and status = 1 and audit_status = 2
				""", merchantId)) {
			ProductPriceCalculator.ProductRight right = ProductPriceCalculator.productRight(
					product.get("rightsConfig"), cardLevel);
			if (right == null || right.discount().signum() <= 0) continue;
			BigDecimal rate = right.discount().divide(BigDecimal.TEN, 6, RoundingMode.HALF_UP);
			if (lowestRate == null || rate.compareTo(lowestRate) < 0) {
				lowestRate = rate;
			}
		}
		if (lowestRate == null || lowestRate.compareTo(BigDecimal.ONE) >= 0) return "";
		double discount = Math.round(lowestRate.doubleValue() * 100) / 10.0;
		return discount + "折";
	}

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

	@Transactional(readOnly = true)
	public Map<String, Object> productDetail(Long id, Long talentId) {
		Map<String, Object> product = one("""
				select id, merchant_id, product_no, product_type, product_name, subtitle, cover_url,
				       original_price, sale_price, subsidy_amount, stock, sales_count, detail_text, benefit_desc,
				       purchase_notice, product_images, rights_config, valid_start_time, valid_end_time,
				       audit_status, status
				from merchant_product
				where deleted = 0 and id = ?
				""", id);
		int stock = ((Number) product.getOrDefault("stock", 0)).intValue();
		boolean available = ((Number) product.getOrDefault("status", 0)).intValue() == 1
				&& ((Number) product.getOrDefault("auditStatus", 0)).intValue() == 2;
		product.put("available", available);
		product.put("soldOut", stock == 0);
		BigDecimal configuredOriginalPrice = decimal(product.get("originalPrice"));
		if (configuredOriginalPrice.signum() <= 0) {
			configuredOriginalPrice = decimal(product.get("salePrice"));
			product.put("originalPrice", configuredOriginalPrice);
		}
		BigDecimal configuredSalePrice = decimal(product.get("salePrice"));
		BigDecimal personalizedPrice = personalizedProductPrice(product, talentId);
		product.put("salePrice", personalizedPrice);
		product.put("personalized", talentId != null && personalizedPrice.compareTo(configuredSalePrice) != 0);
		Long merchantId = ((Number) product.get("merchantId")).longValue();
		product.put("couponAvailable", hasActiveMerchantCoupon(merchantId));
		product.put("discountText", merchantLowestTalentDiscount(merchantId, talentId));
		return product;
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> availableOrderCoupons(Long merchantId, Long productId, String orderId) {
		return availableOrderCoupons(merchantId, productId, orderId, null);
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> availableOrderCoupons(Long merchantId, Long productId, String orderId, Long talentId) {
		Map<String, Object> product = orderCouponProduct(merchantId, productId, orderId);
		BigDecimal productAmount = personalizedProductPrice(product, talentId);
		String cardLevel = talentId == null ? null : activeCouponCardLevel(talentId);
		return query("""
				select c.id, c.coupon_name, c.condition_type, c.condition_amount, c.coupon_amount,
				       c.valid_start, c.valid_end
				from merchant_coupon c
				where c.deleted = 0 and c.status = 1 and c.merchant_id = ?
				  and (c.valid_start is null or c.valid_start <= current_date)
				  and (c.valid_end is null or c.valid_end >= current_date)
			  and (c.condition_type = 'NONE' or c.condition_amount is null or c.condition_amount <= ?)
			  and (? is null or coalesce(c.talent_levels, '') = '' or find_in_set(?, replace(c.talent_levels, '、', ',')) > 0)
			  and (c.product_scope = 'ALL' or exists (
				select 1 from merchant_coupon_product cp
				where cp.coupon_id = c.id and cp.product_id = ?
			  ))
			  and (? is null or not exists (
				select 1 from benefit_order o
				where o.coupon_id = c.id and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
				  and (o.expires_at is null or o.expires_at > current_timestamp)
			  ))
			order by c.coupon_amount desc, c.id desc
			""", merchantId, productAmount, cardLevel, cardLevel, productId, talentId, talentId).stream()
				.map(this::couponListItem)
				.toList();
	}

	@Transactional(readOnly = true)
	public Map<String, Object> orderCouponQuote(Long merchantId, Long productId, String orderId, Long couponId) {
		return orderCouponQuote(merchantId, productId, orderId, couponId, null);
	}

	@Transactional(readOnly = true)
	public Map<String, Object> orderCouponQuote(Long merchantId, Long productId, String orderId, Long couponId, Long talentId) {
		Map<String, Object> product = orderCouponProduct(merchantId, productId, orderId);
		BigDecimal originalAmount = decimal(product.get("originalPrice"));
		if (originalAmount.signum() <= 0) originalAmount = decimal(product.get("salePrice"));
		BigDecimal productAmount = personalizedProductPrice(product, talentId);
		String cardLevel = talentId == null ? null : activeCouponCardLevel(talentId);
		Map<String, Object> result = new LinkedHashMap<>();
		result.put("orderId", orderId);
		result.put("merchantId", merchantId);
		result.put("productId", productId);
		result.put("productName", product.get("productName"));
		result.put("coverUrl", product.get("coverUrl"));
		result.put("originalAmount", originalAmount);
		result.put("productAmount", productAmount);
		result.put("productDiscountAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO));
		result.put("discountText", discountText(productAmount, originalAmount));

		if (couponId == null) {
			result.put("couponId", null);
			result.put("couponLabel", "无");
			result.put("couponAmount", BigDecimal.ZERO);
			result.put("discountAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO));
			result.put("payAmount", productAmount);
			result.put("subsidyAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO));
			return result;
		}

		List<Map<String, Object>> coupons = query("""
				select c.id, c.coupon_name, c.condition_type, c.condition_amount, c.coupon_amount,
				       c.valid_start, c.valid_end
				from merchant_coupon c
				where c.id = ? and c.deleted = 0 and c.status = 1 and c.merchant_id = ?
				  and (c.valid_start is null or c.valid_start <= current_date)
				  and (c.valid_end is null or c.valid_end >= current_date)
			  and (c.condition_type = 'NONE' or c.condition_amount is null or c.condition_amount <= ?)
			  and (? is null or coalesce(c.talent_levels, '') = '' or find_in_set(?, replace(c.talent_levels, '、', ',')) > 0)
			  and (c.product_scope = 'ALL' or exists (
				select 1 from merchant_coupon_product cp
				where cp.coupon_id = c.id and cp.product_id = ?
			  ))
			  and (? is null or not exists (
				select 1 from benefit_order o
				where o.coupon_id = c.id and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
				  and (o.expires_at is null or o.expires_at > current_timestamp)
			  ))
			""", couponId, merchantId, productAmount, cardLevel, cardLevel, productId, talentId, talentId);
		if (coupons.isEmpty()) {
			throw new BusinessException(ErrorCode.CONFLICT, "该优惠券当前不可用，请重新选择");
		}
		Map<String, Object> coupon = coupons.get(0);
		BigDecimal couponAmount = decimal(coupon.get("couponAmount")).min(productAmount);
		result.put("couponId", couponId);
		result.put("couponName", coupon.get("couponName"));
		result.put("couponLabel", "减" + moneyText(couponAmount) + "元");
		result.put("couponAmount", couponAmount);
		result.put("discountAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO).add(couponAmount));
		result.put("payAmount", productAmount.subtract(couponAmount).max(BigDecimal.ZERO));
		result.put("subsidyAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO).add(couponAmount));
		return result;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> products(Long merchantId, String type, Long talentId, int page, int size) {
		StringBuilder sql = new StringBuilder("""
				select id, merchant_id, product_no, product_type, product_name, subtitle, cover_url,
				       original_price, sale_price, subsidy_amount, stock, sales_count, benefit_desc, rights_config
				from merchant_product
				where deleted = 0 and status = 1 and audit_status = 2
				""");
		StringBuilder countSql = new StringBuilder("""
				select count(*) from merchant_product
				where deleted = 0 and status = 1 and audit_status = 2
				""");
		new Object();
		List<Object> args = new java.util.ArrayList<>();
		if (merchantId != null) {
			sql.append(" and merchant_id = ?");
			countSql.append(" and merchant_id = ?");
			args.add(merchantId);
		}
		if (type != null && !type.isBlank()) {
			sql.append(" and product_type = ?");
			countSql.append(" and product_type = ?");
			args.add(type);
		}
		sql.append(" order by sort_no desc, id desc");
		PageResult<Map<String, Object>> result = page(sql.toString(), countSql.toString(), page, size, args.toArray());
		List<Map<String, Object>> records = result.records().stream().map(product -> {
			Map<String, Object> item = new LinkedHashMap<>(product);
			BigDecimal configuredSalePrice = decimal(product.get("salePrice"));
			BigDecimal configuredOriginalPrice = decimal(product.get("originalPrice"));
			BigDecimal originalPrice = decimal(item.get("originalPrice"));
			if (originalPrice.signum() <= 0) originalPrice = decimal(item.get("salePrice"));
			BigDecimal personalizedPrice = personalizedProductPrice(item, talentId);
			item.put("configuredSalePrice", configuredSalePrice);
			item.put("configuredOriginalPrice", configuredOriginalPrice.signum() > 0 ? configuredOriginalPrice : originalPrice);
			item.put("originalPrice", originalPrice);
			item.put("salePrice", personalizedPrice);
			item.put("personalized", talentId != null && personalizedPrice.compareTo(decimal(product.get("salePrice"))) != 0);
			item.put("discountText", discountText(personalizedPrice, originalPrice));
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	private Map<String, Object> orderCouponProduct(Long merchantId, Long productId, String orderId) {
		if (merchantId == null || productId == null || orderId == null || orderId.isBlank()) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "订单、商户和商品信息不能为空");
		}
		Map<String, Object> product = one("""
				select id, merchant_id, product_name, cover_url, original_price, sale_price, rights_config, stock, status, audit_status
				from merchant_product
				where deleted = 0 and id = ? and merchant_id = ?
				""", productId, merchantId);
		boolean available = ((Number) product.getOrDefault("status", 0)).intValue() == 1
				&& ((Number) product.getOrDefault("auditStatus", 0)).intValue() == 2
				&& ((Number) product.getOrDefault("stock", 0)).intValue() != 0;
		if (!available) {
			throw new BusinessException(ErrorCode.CONFLICT, "商品当前不可购买");
		}
		return product;
	}

	private BigDecimal personalizedProductPrice(Map<String, Object> product, Long talentId) {
		if (talentId == null) return decimal(product.get("salePrice"));
		String cardLevel = activeProductCardLevel(talentId);
		if (cardLevel == null) return decimal(product.get("salePrice"));
		return ProductPriceCalculator.calculate(product.get("originalPrice"), product.get("salePrice"), product.get("rightsConfig"), cardLevel,
				usedFreeCount(talentId, product.get("id"), product.get("rightsConfig"), cardLevel)).salePrice();
	}

	private String activeProductCardLevel(Long talentId) {
		if (talentId == null) return null;
		List<Map<String, Object>> cards = query("select card_level from talent_card where deleted = 0 and status = 1 and talent_id = ? and (card_type = 'PERSONAL' or card_type is null) order by id desc limit 1", talentId);
		Object cardLevel = cards.isEmpty() ? null : cards.get(0).get("cardLevel");
		if (cardLevel == null) {
			List<Map<String, Object>> talents = query("select talent_level from talent_user where deleted = 0 and status = 1 and id = ? limit 1", talentId);
			if (!talents.isEmpty()) cardLevel = talents.get(0).get("talentLevel");
		}
		return cardLevel == null ? null : String.valueOf(cardLevel);
	}

	private String activeTalentSourceAreaType(Long talentId) {
		if (talentId == null) return "";
		List<Map<String, Object>> rows = query("""
				select source_area_type
				from talent_user
				where deleted = 0 and status = 1 and id = ?
				limit 1
				""", talentId);
		if (rows.isEmpty()) return "";
		return normalizedSourceAreaType(rows.get(0).get("sourceAreaType"));
	}

	private int usedFreeCount(Long talentId, Object productId, Object rightsConfig, Object cardLevel) {
		ProductPriceCalculator.ProductRight right = ProductPriceCalculator.productRight(rightsConfig, cardLevel);
		if (right == null || !right.free() || !(productId instanceof Number number)) return 0;
		Long count = jdbcTemplate.queryForObject("""
				select count(*)
				from benefit_order
				where deleted = 0 and talent_id = ? and product_id = ? and pay_amount = 0 and order_status = 1
				  and created_at >= ?
				""", Long.class, talentId, number.longValue(), freeWindowStart(right.resetCycle()));
		return count == null ? 0 : count.intValue();
	}

	private LocalDateTime freeWindowStart(String resetCycle) {
		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("天") || cycle.contains("日")) return today.atStartOfDay();
		if (cycle.contains("WEEK") || cycle.contains("周") || cycle.contains("星期")) return today.minusDays(today.getDayOfWeek().getValue() - 1L).atStartOfDay();
		if (cycle.contains("MONTH") || cycle.contains("月")) return today.withDayOfMonth(1).atStartOfDay();
		return today.with(TemporalAdjusters.firstDayOfYear()).atStartOfDay();
	}

	private Map<String, Object> couponListItem(Map<String, Object> coupon) {
		BigDecimal amount = decimal(coupon.get("couponAmount"));
		BigDecimal conditionAmount = decimal(coupon.get("conditionAmount"));
		boolean noThreshold = "NONE".equalsIgnoreCase(String.valueOf(coupon.get("conditionType")))
				|| coupon.get("conditionAmount") == null;
		Map<String, Object> item = new LinkedHashMap<>();
		item.put("id", coupon.get("id"));
		item.put("name", coupon.get("couponName"));
		item.put("amount", moneyText(amount));
		item.put("selectedLabel", "减" + moneyText(amount) + "元");
		item.put("scope", noThreshold ? "无门槛可用" : "满" + moneyText(conditionAmount) + "元可用");
		item.put("expireTime", coupon.get("validEnd") == null ? "长期有效" : String.valueOf(coupon.get("validEnd")));
		item.put("available", true);
		item.put("unavailableReason", "");
		return item;
	}

	private BigDecimal decimal(Object value) {
		if (value == null) return BigDecimal.ZERO;
		return new BigDecimal(String.valueOf(value)).setScale(2, RoundingMode.HALF_UP);
	}

	private String moneyText(BigDecimal value) {
		return value.setScale(2, RoundingMode.HALF_UP).stripTrailingZeros().toPlainString();
	}

	private String activeCouponCardLevel(Long talentId) {
		List<Map<String, Object>> cards = query("select card_level from talent_card where talent_id=? and deleted=0 and status=1 and card_type='PERSONAL' and (valid_end is null or valid_end >= current_date) order by id desc limit 1", talentId);
		if (cards.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "当前暂无有效人才卡");
		return String.valueOf(cards.get(0).get("cardLevel"));
	}

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

	private Map<String, Object> one(String sql, Object... args) {
		List<Map<String, Object>> rows = query(sql, args);
		if (rows.isEmpty()) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "数据不存在");
		}
		return rows.get(0);
	}

	private PageResult<Map<String, Object>> page(String sql, String countSql, int page, int size, Object... args) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		Object[] pageArgs = java.util.Arrays.copyOf(args, args.length + 2);
		pageArgs[args.length] = safeSize;
		pageArgs[args.length + 1] = (safePage - 1) * safeSize;
		List<Map<String, Object>> records = query(sql + " limit ? offset ?", pageArgs);
		Long total = jdbcTemplate.queryForObject(countSql, Long.class, args);
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}
}