package com.talentcard.server.business.portal;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Collections;

import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
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, sort_no 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, 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
				"""));
		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) {
		return page("""
				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, sort_no desc, publish_time desc, id desc
				""", "select count(*) from news_article where deleted = 0 and status = 1", page, size);
	}

	@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) {
		return page("""
				select id, policy_no, title, subtitle, category_name, cover_url, tags, author, source, 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);
	}

	@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,
			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, contact_name, contact_mobile, logo_url, cover_url, address,
				       is_recommend, 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, args.toArray());
		List<Map<String, Object>> records = result.records().stream().map(this::enrichMerchantSearchRow).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) {
		String merchantWhere = " where deleted = 0 and merchant_type in ('food', '餐饮服务')";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, 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(this::enrichMerchantSearchRow).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) {
		String merchantWhere = " where deleted = 0 and merchant_type = 'scenic'";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, 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(this::enrichMerchantSearchRow).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) {
		String requestedType = type == null ? "" : type.trim();
		String merchantType = switch (requestedType) {
			case "hotel", "酒店服务" -> "酒店服务";
			case "apartment", "公寓租赁服务" -> "公寓租赁服务";
			default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的住宿服务类型");
		};
		String responseMerchantType = merchantType.equals("公寓租赁服务") ? "apartment" : "hotel";
		String merchantWhere = " where deleted = 0 and merchant_type = ?";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, 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, merchantType);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> normalized = new LinkedHashMap<>(row);
			normalized.put("merchantType", responseMerchantType);
			return enrichMerchantSearchRow(normalized);
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@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 in ('cinema', 'theatre')";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       service_label, 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("discountText", textOrDefault(item.get("serviceLabel"), textOrDefault(item.get("discountText"), "人才专享")));
			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) {
		if (!List.of("fuel", "parking", "carWash").contains(type)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的行服务类型");
		}
		String merchantWhere = " where deleted = 0 and merchant_type = ?";
		PageResult<Map<String, Object>> result = page("""
				select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       service_label, 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, type);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("discountText", textOrDefault(item.get("serviceLabel"), textOrDefault(item.get("discountText"), "人才专享")));
			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) {
		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, "不支持的购物服务分类");
		String merchantWhere = """
				where m.deleted = 0 and m.merchant_type = 'shopping'
				  and mapping.deleted = 0 and mapping.category_key = ?
				""";
		PageResult<Map<String, Object>> result = page("""
				select m.id, m.merchant_name, m.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
				from merchant m
				join shopping_topic_merchant mapping on mapping.merchant_id = m.id
				""" + merchantWhere + " order by mapping.sort_no asc, m.sort_no asc, m.id asc",
				"select count(*) from merchant m join shopping_topic_merchant mapping on mapping.merchant_id = m.id " + merchantWhere,
				page, size, categoryId);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("discountText", textOrDefault(item.get("serviceLabel"), "人才专享"));
			return item;
		}).toList();
		return new PageResult<>(records, result.page(), result.size(), result.total());
	}

	@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 categoryId, int page, int size) {
		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, "不支持的臻享服务分类");
		String serviceWhere = """
				where m.deleted = 0 and mapping.deleted = 0 and mapping.category_key = ?
				""";
		PageResult<Map<String, Object>> result = page("""
				select m.id, m.merchant_name, m.merchant_type, m.contact_mobile, m.cover_url, m.address,
				       m.service_label, m.status, m.sort_no, m.longitude, m.latitude,
				       mapping.category_key, mapping.business_type
				from merchant m
				join premium_topic_merchant mapping on mapping.merchant_id = m.id
				""" + serviceWhere + " order by mapping.sort_no asc, m.sort_no asc, m.id asc",
				"select count(*) from merchant m join premium_topic_merchant mapping on mapping.merchant_id = m.id " + serviceWhere,
				page, size, categoryId);
		List<Map<String, Object>> records = result.records().stream().map(row -> {
			Map<String, Object> item = enrichMerchantSearchRow(row);
			item.put("businessType", row.get("businessType"));
			item.put("targetAction", row.get("businessType"));
			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 switch (merchantType) {
			case "food", "餐饮服务" -> "暖心食享";
			case "hotel", "apartment" -> "舒心安居";
			case "cinema", "theatre" -> "怡乐生活";
			case "scenic" -> "悦览花城";
			case "fuel", "parking", "carWash" -> "畅行无忧";
			case "shopping" -> "惠享好物";
			case "medical", "finance" -> "臻享礼遇";
			default -> "其他权益";
		};
	}

	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 = ?";
		}
		args.addAll(merchantTypes);
		String placeholders = String.join(",", Collections.nCopies(merchantTypes.size(), "?"));
		return merchantWhere + " and merchant_type in (" + placeholders + ")";
	}

	private List<String> merchantTypesForRequestedValue(String value) {
		List<String> categoryTypes = merchantTypesForCategory(value);
		return categoryTypes.isEmpty() ? List.of(value) : categoryTypes;
	}

	private List<String> merchantTypesForCategory(String value) {
		return switch (value) {
			case "暖心食享", "餐饮服务", "food" -> List.of("food", "餐饮服务");
			case "舒心安居", "hotel" -> List.of("hotel");
			case "怡乐生活", "cinema", "theatre" -> List.of("cinema", "theatre");
			case "悦览花城", "scenic" -> List.of("scenic");
			case "畅行无忧" -> List.of("fuel", "parking", "carWash");
			case "fuel" -> List.of("fuel");
			case "parking" -> List.of("parking");
			case "carWash" -> List.of("carWash");
			case "惠享好物", "shopping" -> List.of("shopping");
			case "臻享礼遇" -> List.of("medical", "finance");
			case "medical" -> List.of("medical");
			case "finance" -> List.of("finance");
			default -> List.of();
		};
	}

	private String detailType(String merchantType) {
		return switch (merchantType) {
			case "cinema" -> "CINEMA";
			case "theatre" -> "THEATRE";
			case "shopping" -> "SHOPPING";
			case "medical" -> "MEDICAL";
			case "finance" -> "FINANCE";
			case "carWash" -> "CAR_WASH";
			case "fuel" -> "FUEL";
			case "parking" -> "PARKING";
			default -> "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
				&& 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) {
		Map<String, Object> merchant = one("""
				select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
				       business_status, longitude, latitude, group_buying_enabled, usage_instructions, rights_text,
				       service_label, status, is_recommend, sort_no,
				       (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
				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("discountText", discountText(merchant.get("salePrice"), merchant.get("originalPrice")));
		return merchant;
	}

	@Transactional(readOnly = true)
	public Map<String, Object> productDetail(Long id) {
		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);
		return product;
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> availableOrderCoupons(Long merchantId, Long productId, String orderId) {
		Map<String, Object> product = orderCouponProduct(merchantId, productId, orderId);
		BigDecimal productAmount = decimal(product.get("salePrice"));
		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 (c.product_scope = 'ALL' or exists (
					select 1 from merchant_coupon_product cp
					where cp.coupon_id = c.id and cp.product_id = ?
				  ))
				order by c.coupon_amount desc, c.id desc
				""", merchantId, productAmount, productId).stream()
				.map(this::couponListItem)
				.toList();
	}

	@Transactional(readOnly = true)
	public Map<String, Object> orderCouponQuote(Long merchantId, Long productId, String orderId, Long couponId) {
		Map<String, Object> product = orderCouponProduct(merchantId, productId, orderId);
		BigDecimal originalAmount = decimal(product.get("originalPrice"));
		BigDecimal productAmount = decimal(product.get("salePrice"));
		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);
			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 (c.product_scope = 'ALL' or exists (
					select 1 from merchant_coupon_product cp
					where cp.coupon_id = c.id and cp.product_id = ?
				  ))
				""", couponId, merchantId, productAmount, productId);
		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));
		return result;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> products(Long merchantId, String type, 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
				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");
		return page(sql.toString(), countSql.toString(), page, size, args.toArray());
	}

	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, 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 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 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);
	}
}