package com.talentcard.server.business.admin;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.talentcard.server.business.BusinessIdGenerator;
import com.alibaba.fastjson.JSON;
import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.business.merchant.auth.MerchantPasswordHasher;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.jsoup.Jsoup;
import org.jsoup.safety.Safelist;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class AdminBusinessService {

	private static final Set<String> MERCHANT_APP_LOGIN_TYPES = Set.of(
			"餐饮服务", "景区服务", "免费体检", "配偶推荐就业", "金融服务", "行政审批", "子女托管",
			"酒店服务", "公寓租赁", "加油服务", "停车服务", "自助洗车", "购房优惠", "购车服务",
			"家电服务", "数码服务", "文化服务", "电信服务", "口腔治疗保健"
	);

	private final JdbcTemplate jdbcTemplate;

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

	@Transactional(readOnly = true)
	public Map<String, Object> dashboard() {
		Map<String, Object> result = new LinkedHashMap<>();
		result.put("talentCount", count("select count(*) from talent_user where deleted = 0"));
		result.put("cardCount", count("select count(*) from talent_card where deleted = 0"));
		result.put("merchantCount", count("select count(*) from merchant where deleted = 0"));
		result.put("orderCount", count("select count(*) from benefit_order where deleted = 0"));
		result.put("pendingProductCount", count("select count(*) from merchant_product where deleted = 0 and audit_status = 1"));
		result.put("todayVerifyCount", count("select count(*) from verify_record where date(verify_time) = current_date"));
		return result;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> news(
			String title,
			String source,
			Integer status,
			String publishStart,
			String publishEnd,
			int page,
			int size
	) {
		StringBuilder sql = new StringBuilder("""
				select id, title, summary, cover_url, source, author, view_count, is_top, top_time, sort_no,
				       publish_time, status, created_at, updated_at
				from news_article
				where deleted = 0
				""");
		StringBuilder countSql = new StringBuilder("select count(*) from news_article where deleted = 0");
		List<Object> args = new ArrayList<>();
		if (title != null && !title.isBlank()) {
			sql.append(" and title like ?");
			countSql.append(" and title like ?");
			args.add("%" + title.trim() + "%");
		}
		if (source != null && !source.isBlank()) {
			sql.append(" and (source like ? or author like ?)");
			countSql.append(" and (source like ? or author like ?)");
			String like = "%" + source.trim() + "%";
			args.add(like);
			args.add(like);
		}
		if (status != null) {
			sql.append(" and status = ?");
			countSql.append(" and status = ?");
			args.add(status);
		}
		if (publishStart != null && !publishStart.isBlank()) {
			sql.append(" and date(publish_time) >= ?");
			countSql.append(" and date(publish_time) >= ?");
			args.add(publishStart);
		}
		if (publishEnd != null && !publishEnd.isBlank()) {
			sql.append(" and date(publish_time) <= ?");
			countSql.append(" and date(publish_time) <= ?");
			args.add(publishEnd);
		}
		sql.append(" order by is_top desc, case when is_top = 1 then top_time end desc, publish_time desc, id desc");
		return page(sql.toString(), countSql.toString(), page, size, args.toArray());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> newsDetail(Long id) {
		return one("""
				select id, title, summary, cover_url, source, author, content, view_count, is_top, top_time, sort_no,
				       publish_time, status, created_at, updated_at
				from news_article
				where deleted = 0 and id = ?
				""", id);
	}

	@Transactional
	public Map<String, Object> createNews(NewsArticleRequest request, String operatorName) {
		int status = defaultNewsStatus(request.status());
		jdbcTemplate.update("""
				insert into news_article(title, summary, cover_url, source, author, content, is_top, top_time, sort_no, publish_time, status)
				values (?, ?, ?, ?, ?, ?, ?, case when ? = 1 then now() else null end, ?, case when ? = 1 then now() else null end, ?)
				""",
				request.title().trim(),
				blankToNull(request.summary()),
				blankToNull(request.coverUrl()),
				null,
				blankToNull(operatorName),
				sanitizeNewsContent(request.content()),
				Boolean.TRUE.equals(request.isTop()) ? 1 : 0,
				Boolean.TRUE.equals(request.isTop()) ? 1 : 0,
				request.sortNo() == null ? 0 : request.sortNo(),
				status,
				status);
		Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		return newsDetail(id);
	}

	@Transactional
	public Map<String, Object> updateNews(Long id, NewsArticleRequest request) {
		int status = defaultNewsStatus(request.status());
		int updated = jdbcTemplate.update("""
				update news_article
				set title = ?, summary = ?, cover_url = ?, content = ?,
				    top_time = case when ? = 1 and coalesce(is_top, 0) = 0 then now() when ? = 0 then null else top_time end,
				    is_top = ?, sort_no = ?,
				    status = ?, publish_time = case when ? = 1 then coalesce(publish_time, now()) else publish_time end
				where deleted = 0 and id = ?
				""",
				request.title().trim(),
				blankToNull(request.summary()),
				blankToNull(request.coverUrl()),
				sanitizeNewsContent(request.content()),
				Boolean.TRUE.equals(request.isTop()) ? 1 : 0,
				Boolean.TRUE.equals(request.isTop()) ? 1 : 0,
				Boolean.TRUE.equals(request.isTop()) ? 1 : 0,
				request.sortNo() == null ? 0 : request.sortNo(),
				status,
				status,
				id);
		ensureUpdated(updated);
		return newsDetail(id);
	}

	@Transactional
	public Map<String, Object> updateNewsStatus(Long id, Byte status) {
		int updated = jdbcTemplate.update("""
				update news_article
				set status = ?, publish_time = case when ? = 1 then coalesce(publish_time, now()) else publish_time end
				where deleted = 0 and id = ?
				""", status, status, id);
		ensureUpdated(updated);
		return newsDetail(id);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> policies(String title, String author, Integer status, String publishStart, String publishEnd, int page, int size) {
		StringBuilder sql = new StringBuilder("select id, policy_no, title, subtitle, cover_url, author, source, view_count, is_top, sort_no, publish_time, status, created_at, updated_at from policy_article where deleted = 0");
		StringBuilder countSql = new StringBuilder("select count(*) from policy_article where deleted = 0");
		List<Object> args = new ArrayList<>();
		if (title != null && !title.isBlank()) { sql.append(" and title like ?"); countSql.append(" and title like ?"); args.add("%" + title.trim() + "%"); }
		if (author != null && !author.isBlank()) {
			sql.append(" and (author like ? or source like ?)"); countSql.append(" and (author like ? or source like ?)");
			String like = "%" + author.trim() + "%"; args.add(like); args.add(like);
		}
		if (status != null) { sql.append(" and status = ?"); countSql.append(" and status = ?"); args.add(status); }
		if (publishStart != null && !publishStart.isBlank()) { sql.append(" and date(publish_time) >= ?"); countSql.append(" and date(publish_time) >= ?"); args.add(publishStart); }
		if (publishEnd != null && !publishEnd.isBlank()) { sql.append(" and date(publish_time) <= ?"); countSql.append(" and date(publish_time) <= ?"); args.add(publishEnd); }
		sql.append(" order by is_top desc, sort_no desc, publish_time desc, id desc");
		return page(sql.toString(), countSql.toString(), page, size, args.toArray());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> policyDetail(Long id) {
		return one("select id, policy_no, title, subtitle, cover_url, author, source, content, view_count, is_top, sort_no, publish_time, status, created_at, updated_at from policy_article where deleted = 0 and id = ?", id);
	}

	@Transactional
	public Map<String, Object> createPolicy(PolicyArticleRequest request, String operatorName) {
		int status = defaultNewsStatus(request.status());
		jdbcTemplate.update("insert into policy_article(policy_no, title, subtitle, cover_url, author, source, content, is_top, sort_no, publish_time, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?, case when ? = 1 then now() else null end, ?)",
				BusinessIdGenerator.next("POLICY"), request.title().trim(), blankToNull(request.subtitle()), blankToNull(request.coverUrl()), blankToNull(operatorName), blankToNull(request.source()), sanitizeNewsContent(request.content()), Boolean.TRUE.equals(request.isTop()) ? 1 : 0, request.sortNo() == null ? 0 : request.sortNo(), status, status);
		return policyDetail(jdbcTemplate.queryForObject("select last_insert_id()", Long.class));
	}

	@Transactional
	public Map<String, Object> updatePolicy(Long id, PolicyArticleRequest request) {
		int status = defaultNewsStatus(request.status());
		int updated = jdbcTemplate.update("update policy_article set title = ?, subtitle = ?, cover_url = ?, author = ?, source = ?, content = ?, is_top = ?, sort_no = ?, status = ?, publish_time = case when ? = 1 then coalesce(publish_time, now()) else publish_time end where deleted = 0 and id = ?",
				request.title().trim(), blankToNull(request.subtitle()), blankToNull(request.coverUrl()), blankToNull(request.author()), blankToNull(request.source()), sanitizeNewsContent(request.content()), Boolean.TRUE.equals(request.isTop()) ? 1 : 0, request.sortNo() == null ? 0 : request.sortNo(), status, status, id);
		ensureUpdated(updated);
		return policyDetail(id);
	}

	@Transactional
	public Map<String, Object> updatePolicyStatus(Long id, Byte status) {
		int updated = jdbcTemplate.update("update policy_article set status = ?, publish_time = case when ? = 1 then coalesce(publish_time, now()) else publish_time end where deleted = 0 and id = ?", status, status, id);
		ensureUpdated(updated);
		return policyDetail(id);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> merchants(
			String keyword,
			String contactMobile,
			String type,
			Integer status,
			Integer recommend,
			String createdStart,
			String createdEnd,
			int page,
			int size
	) {
		StringBuilder sql = new StringBuilder("""
				select m.id, m.merchant_name, m.merchant_type, m.contact_name, m.contact_mobile,
				       m.logo_url, m.cover_url, m.address, m.is_recommend, m.sort_no, m.status,
				       m.created_at,
				       (select count(*) from merchant_product p where p.merchant_id = m.id and p.deleted = 0) as product_count,
				       (select count(*) from benefit_order o where o.merchant_id = m.id and o.deleted = 0) as order_count,
				       0 as reservation_count
				from merchant m
				where m.deleted = 0
				""");
		StringBuilder countSql = new StringBuilder("select count(*) from merchant m where m.deleted = 0");
		List<Object> args = new ArrayList<>();
		if (keyword != null && !keyword.isBlank()) {
			sql.append(" and (m.merchant_name like ? or m.contact_mobile like ? or m.address like ?)");
			countSql.append(" and (m.merchant_name like ? or m.contact_mobile like ? or m.address like ?)");
			String like = "%" + keyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (contactMobile != null && !contactMobile.isBlank()) {
			sql.append(" and m.contact_mobile like ?");
			countSql.append(" and m.contact_mobile like ?");
			args.add("%" + contactMobile.trim() + "%");
		}
		if (type != null && !type.isBlank()) {
			sql.append(" and m.merchant_type = ?");
			countSql.append(" and m.merchant_type = ?");
			args.add(type);
		}
		if (status != null) {
			sql.append(" and m.status = ?");
			countSql.append(" and m.status = ?");
			args.add(status);
		}
		if (recommend != null) {
			sql.append(" and m.is_recommend = ?");
			countSql.append(" and m.is_recommend = ?");
			args.add(recommend);
		}
		if (createdStart != null && !createdStart.isBlank()) {
			sql.append(" and date(m.created_at) >= ?");
			countSql.append(" and date(m.created_at) >= ?");
			args.add(createdStart);
		}
		if (createdEnd != null && !createdEnd.isBlank()) {
			sql.append(" and date(m.created_at) <= ?");
			countSql.append(" and date(m.created_at) <= ?");
			args.add(createdEnd);
		}
		sql.append(" order by m.is_recommend desc, m.sort_no desc, m.id desc");
		PageResult<Map<String, Object>> result = page(sql.toString(), countSql.toString(), page, size, args.toArray());
		applyBusinessStatus(result.records());
		return result;
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> pendingNotices() {
		List<Map<String, Object>> notices = new ArrayList<>();
		addPendingNotice(notices, "product-audit", "团单审核", "您有 %d 条团单待审核", "/products",
				"select count(*) as pending_count, max(coalesce(submit_time, created_at)) as latest_time from merchant_product where deleted = 0 and audit_status = 1");
		addPendingNotice(notices, "family-card", "家属卡申请", "您有 %d 条家属卡申请待审核", "/family-card-applications",
				"select count(*) as pending_count, max(apply_time) as latest_time from family_card_application where deleted = 0 and status = '待审核'");
		addPendingNotice(notices, "upgrade", "升级申请", "您有 %d 条升级申请待审核", "/upgrade-applications",
				"select count(*) as pending_count, max(apply_time) as latest_time from upgrade_application where deleted = 0 and status = '待审核'");
		addPendingNotice(notices, "merchant-onboarding", "商户进件", "您有 %d 条商户进件待审核", "/merchant-settlement?kind=onboardings",
				"select count(*) as pending_count, max(created_at) as latest_time from merchant_onboarding_application where status = 'pending'");
		addPendingNotice(notices, "merchant-bank-card", "银行卡申请", "您有 %d 条银行卡申请待审核", "/merchant-settlement?kind=bank-cards",
				"select count(*) as pending_count, max(created_at) as latest_time from merchant_bank_card where status = 'pending'");
		addPendingNotice(notices, "merchant-withdrawal", "提现审核", "您有 %d 条提现申请待审核", "/merchant-settlement?kind=withdrawals",
				"select count(*) as pending_count, max(created_at) as latest_time from merchant_withdrawal where status = 'pending'");
		addPendingNotice(notices, "medical-reservation", "体检预约", "您有 %d 条体检预约待处理", "/medical-reservations",
				"select count(*) as pending_count, max(submitted_at) as latest_time from service_application where deleted = 0 and application_type = 'MEDICAL_RESERVATION' and status = '待处理'");
		addPendingNotice(notices, "medical-application", "就医申请", "您有 %d 条就医申请待处理", "/medical-applications",
				"select count(*) as pending_count, max(submitted_at) as latest_time from service_application where deleted = 0 and application_type = 'MEDICAL_APPLICATION' and status = '待处理'");
		notices.sort((left, right) -> String.valueOf(right.get("time")).compareTo(String.valueOf(left.get("time"))));
		return notices;
	}

	private void addPendingNotice(List<Map<String, Object>> notices, String id, String category, String template, String path, String sql) {
		Map<String, Object> summary = DatabaseRows.normalize(jdbcTemplate.queryForMap(sql));
		long count = summary.get("pendingCount") instanceof Number number ? number.longValue() : 0;
		if (count == 0) return;
		Map<String, Object> notice = new LinkedHashMap<>();
		notice.put("id", id);
		notice.put("category", category);
		notice.put("content", template.formatted(count));
		notice.put("time", summary.get("latestTime"));
		notice.put("path", path);
		notices.add(notice);
	}

	@Transactional(readOnly = true)
	public Map<String, Object> merchant(Long id) {
		Map<String, Object> result = one("""
				select merchant.id, merchant.merchant_name, merchant.merchant_type, merchant.contact_name, merchant.contact_mobile,
				       merchant.logo_url, merchant.cover_url, merchant.address, merchant.business_status, merchant.longitude,
				       merchant.latitude, merchant.can_login_merchant_app, merchant.group_buying_enabled, merchant.usage_instructions,
				       merchant.rights_text, merchant.merchant_rights_config, merchant.service_label, merchant.is_recommend,
				       merchant.sort_no, merchant.status, merchant.created_at, merchant.updated_at,
				       s.external_link_type as car_wash_external_link_type,
				       s.external_mini_program_app_id as car_wash_external_mini_program_app_id,
				       s.external_mini_program_path as car_wash_external_mini_program_path,
				       s.external_web_url as car_wash_external_web_url,
				       (select o.account from merchant_operator o
				        where o.merchant_id = merchant.id and o.role_code = 'owner' and o.deleted = 0
				        order by o.id asc limit 1) as login_account
				from merchant
				left join transport_car_wash_service s on s.merchant_id = merchant.id and s.deleted = 0
				where merchant.deleted = 0 and merchant.id = ?
				""", id);
		result.put("businessHours", DatabaseRows.normalize(jdbcTemplate.queryForList("""
				select start_time, end_time
				from merchant_business_hours
				where merchant_id = ?
				order by sort_no, id
				""", id)));
		return result;
	}

	@Transactional(readOnly = true)
	public Map<String, Object> merchantLoginAccountAvailable(String account) {
		String normalizedAccount = account == null ? "" : account.trim();
		if (!normalizedAccount.matches("^[A-Za-z0-9]{1,32}$")) {
			return Map.of("available", false);
		}
		Long count = jdbcTemplate.queryForObject(
				"select count(*) from merchant_operator where account = ?", Long.class, normalizedAccount);
		return Map.of("available", count == null || count == 0);
	}

	@Transactional
	public Map<String, Object> createMerchant(MerchantCreateRequest request) {
		validateMerchantAddress(request.merchantType(), request.address());
		validateRequiredMerchantCreateFields(request);
		jdbcTemplate.update("""
				insert into merchant(merchant_name, merchant_type, contact_name, contact_mobile, address,
					                     logo_url, cover_url, business_status, longitude, latitude, can_login_merchant_app, group_buying_enabled,
					                     usage_instructions, rights_text, merchant_rights_config, service_label, is_recommend, status, sort_no)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?)
				""",
				request.merchantName(),
				request.merchantType(),
				request.contactName(),
				request.contactMobile(),
				request.address(),
				request.logoUrl(),
				request.coverUrl(),
				request.businessStatus(),
				request.longitude(),
				request.latitude(),
				request.canLoginMerchantApp() == null ? 1 : request.canLoginMerchantApp(),
				request.groupBuyingEnabled() == null ? 1 : request.groupBuyingEnabled(),
				request.usageInstructions(),
				request.rightsText(),
				toJson(request.merchantRightsConfig()),
				request.serviceLabel(),
				request.recommend() == null ? defaultRecommend(request.merchantType()) : request.recommend(),
				request.sortNo() == null ? 0 : request.sortNo());
		Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		ensureParkingBenefit(id, request.merchantType());
		Long storeId = createDefaultStore(id, request.merchantName(), request.address());
		syncMerchantBusinessHours(id, storeId, request.businessHours());
		if (hasMerchantAppAccess(request)) {
			String account = request.loginAccount().trim();
			if (!account.matches("^[A-Za-z0-9]{1,32}$")) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录账号只能包含英文字母和数字");
			}
			if (request.loginPassword() == null || request.loginPassword().length() < 6 || request.loginPassword().length() > 64) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录密码长度须为6至64位");
			}
			Integer occupied = jdbcTemplate.queryForObject("select count(*) from merchant_operator where account = ?", Integer.class, account);
			if (occupied != null && occupied > 0) throw new BusinessException(ErrorCode.CONFLICT, "商户登录账号已存在");
			jdbcTemplate.update("insert into merchant_operator(merchant_id, account, password_hash, display_name, role_code, status) values (?, ?, ?, ?, 'owner', 1)",
					id, account, MerchantPasswordHasher.encode(request.loginPassword()), request.merchantName().trim());
			Long operatorId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
			jdbcTemplate.update("insert into merchant_operator_store(operator_id, store_id) values (?, ?)", operatorId, storeId);
		}
		return one("select * from merchant where id = ?", id);
	}

	void validateMerchantAddress(String merchantType, String address) {
		if (!"公寓租赁".equals(merchantType) && (address == null || address.isBlank())) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "请输入商家地址");
		}
	}

	private boolean hasMerchantAppAccess(MerchantCreateRequest request) {
		return supportsMerchantAppLogin(request.merchantType())
				&& (request.canLoginMerchantApp() == null || request.canLoginMerchantApp() == 1);
	}

	boolean supportsMerchantAppLogin(String merchantType) {
		return MERCHANT_APP_LOGIN_TYPES.contains(merchantType);
	}

	private void validateRequiredMerchantCreateFields(MerchantCreateRequest request) {
		if (!Set.of("景区服务", "餐饮服务").contains(request.merchantType())) {
			return;
		}
		if (request.loginAccount() == null || !request.loginAccount().trim().matches("^[A-Za-z0-9]{1,32}$")) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录账号只能包含英文字母和数字");
		}
		if (request.loginPassword() == null || request.loginPassword().length() < 6 || request.loginPassword().length() > 64) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录密码长度须为6至64位");
		}
	}

	private Long createDefaultStore(Long merchantId, String merchantName, String address) {
		jdbcTemplate.update("insert into merchant_store(merchant_id, store_name, address, status) values (?, ?, ?, 1)",
				merchantId, merchantName.trim(), address);
		return jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
	}

	private Long findOrCreateDefaultStore(Long merchantId, String merchantName, String address) {
		List<Long> stores = jdbcTemplate.queryForList("""
				select id from merchant_store
				where merchant_id = ? and deleted = 0
				order by id
				limit 1
				""", Long.class, merchantId);
		if (stores.isEmpty()) return createDefaultStore(merchantId, merchantName, address);
		Long storeId = stores.get(0);
		jdbcTemplate.update("update merchant_store set store_name = ?, address = ? where id = ?", merchantName.trim(), address, storeId);
		return storeId;
	}

	private void syncMerchantBusinessHours(Long merchantId, Long storeId, List<MerchantBusinessHourRequest> businessHours) {
		if (businessHours == null) return;
		jdbcTemplate.update("delete from merchant_business_hours where merchant_id = ? and store_id = ?", merchantId, storeId);
		if (businessHours.isEmpty()) {
			return;
		}
		if (businessHours.stream().anyMatch(item -> item.startTime().equals(item.endTime()))) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "营业开始时间和结束时间不能相同");
		}
		for (int index = 0; index < businessHours.size(); index++) {
			MerchantBusinessHourRequest item = businessHours.get(index);
			jdbcTemplate.update("""
					insert into merchant_business_hours(merchant_id, store_id, start_time, end_time, sort_no)
					values (?, ?, ?, ?, ?)
					""", merchantId, storeId, item.startTime(), item.endTime(), index + 1);
		}
	}

	private byte defaultRecommend(String merchantType) {
		return (byte) (("餐饮服务".equals(merchantType) || "景区服务".equals(merchantType)) ? 1 : 0);
	}

	@Transactional
	public Map<String, Object> updateMerchant(Long id, MerchantUpdateRequest request) {
		int updated = jdbcTemplate.update("""
				update merchant
				set merchant_name = ?, merchant_type = ?, contact_name = ?, contact_mobile = ?,
				    address = ?, logo_url = ?, cover_url = ?, business_status = ?, longitude = ?, latitude = ?, can_login_merchant_app = ?,
				    group_buying_enabled = ?, usage_instructions = ?, rights_text = ?, merchant_rights_config = ?, service_label = ?,
				    is_recommend = ?, sort_no = ?, status = ?
				where deleted = 0 and id = ?
				""",
				request.merchantName(),
				request.merchantType(),
				request.contactName(),
				request.contactMobile(),
				request.address(),
				request.logoUrl(),
				request.coverUrl(),
				request.businessStatus(),
				request.longitude(),
				request.latitude(),
				request.canLoginMerchantApp() == null ? 1 : request.canLoginMerchantApp(),
				request.groupBuyingEnabled() == null ? 1 : request.groupBuyingEnabled(),
				request.usageInstructions(),
				request.rightsText(),
				toJson(request.merchantRightsConfig()),
				request.serviceLabel(),
				request.recommend() == null ? 0 : request.recommend(),
				request.sortNo() == null ? 0 : request.sortNo(),
				request.status() == null ? 1 : request.status(),
				id);
		ensureUpdated(updated);
		ensureParkingBenefit(id, request.merchantType());
		Long storeId = findOrCreateDefaultStore(id, request.merchantName(), request.address());
		syncMerchantBusinessHours(id, storeId, request.businessHours());
		if (request.loginPassword() != null && !request.loginPassword().isBlank()) {
			if (request.loginPassword().length() < 6 || request.loginPassword().length() > 64) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录密码长度须为6至64位");
			}
			int passwordUpdated = jdbcTemplate.update("""
					update merchant_operator
					set password_hash = ?
					where merchant_id = ? and role_code = 'owner' and deleted = 0
					""", MerchantPasswordHasher.encode(request.loginPassword()), id);
			if (passwordUpdated == 0) {
				createMerchantOperator(id, request);
			}
		}
		if ((request.loginPassword() == null || request.loginPassword().isBlank()) && supportsMerchantAppLogin(request.merchantType())
				&& (request.canLoginMerchantApp() == null || request.canLoginMerchantApp() == 1)
				&& (request.loginAccount() != null && !request.loginAccount().isBlank())) {
			createMerchantOperator(id, request);
		}
		return merchant(id);
	}

	private void createMerchantOperator(Long merchantId, MerchantUpdateRequest request) {
		if (request.loginAccount() == null || !request.loginAccount().trim().matches("^[A-Za-z0-9]{1,32}$")) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录账号只能包含英文字母和数字");
		}
		if (request.loginPassword() == null || request.loginPassword().length() < 6 || request.loginPassword().length() > 64) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "商户登录密码长度须为6至64位");
		}
		Integer occupied = jdbcTemplate.queryForObject("select count(*) from merchant_operator where account = ? and deleted = 0", Integer.class, request.loginAccount().trim());
		if (occupied != null && occupied > 0) throw new BusinessException(ErrorCode.CONFLICT, "登录账号已存在");
		jdbcTemplate.update("insert into merchant_operator(merchant_id, account, password_hash, display_name, role_code, status) values (?, ?, ?, ?, 'owner', 1)",
				merchantId, request.loginAccount().trim(), MerchantPasswordHasher.encode(request.loginPassword()), request.merchantName().trim());
	}

	private void ensureParkingBenefit(Long merchantId, String merchantType) {
		if (!"停车服务".equals(merchantType) && !"parking".equalsIgnoreCase(merchantType)) return;
		jdbcTemplate.update("""
				insert into transport_parking_benefit(merchant_id, talent_levels, benefit_text, action_mode, plate_form_mode, usage_instructions, rule_text, status, deleted)
				select ?, '', '停车权益', 'plate', 'withParking',
				       '登记车牌后可享受当前停车场的人才专属停车权益。',
				       '具体免费时长、适用时段和停车场范围以现场说明为准。', 1, 0
				where not exists (select 1 from transport_parking_benefit where merchant_id = ? and deleted = 0)
				""", merchantId, merchantId);
	}

	@Transactional
	public Map<String, Object> updateMerchantStatus(Long id, Byte status) {
		int updated = jdbcTemplate.update("update merchant set status = ? where deleted = 0 and id = ?", status, id);
		ensureUpdated(updated);
		return merchant(id);
	}

	@Transactional
	public Map<String, Object> updateMerchantRecommend(Long id, Byte recommend) {
		int updated = jdbcTemplate.update("update merchant set is_recommend = ? where deleted = 0 and id = ?", recommend, id);
		ensureUpdated(updated);
		return merchant(id);
	}

	@Transactional
	public Map<String, Object> createBenefit(BenefitCreateRequest request) {
		jdbcTemplate.update("""
				insert into benefit(benefit_name, benefit_type, cover_url, description, total_quota, sort_no, status)
				values (?, ?, ?, ?, ?, ?, 1)
				""",
				request.benefitName(),
				request.benefitType(),
				request.coverUrl(),
				request.description(),
				request.totalQuota(),
				request.sortNo() == null ? 0 : request.sortNo());
		Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		return one("select * from benefit where id = ?", id);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> products(
			Long merchantId,
			String merchantName,
			String contactMobile,
			String keyword,
			String type,
			Integer status,
			String createdStart,
			String createdEnd,
			int page,
			int size
	) {
		StringBuilder sql = new StringBuilder("""
				select p.id, p.merchant_id, m.merchant_name, m.merchant_type, m.contact_mobile,
				       p.product_no, p.product_type, p.product_name,
				       p.subtitle, p.cover_url, p.original_price, p.sale_price, p.subsidy_amount,
				       p.is_government_subsidy, p.stock, p.sales_count, p.detail_text, p.benefit_desc,
				       p.purchase_notice, p.valid_start_time, p.valid_end_time, p.product_images, p.rights_config,
				       p.audit_status, p.audit_remark, p.status, p.sort_no, p.submit_time, p.created_at
				from merchant_product p
				left join merchant m on m.id = p.merchant_id
				where p.deleted = 0
				""");
		StringBuilder countSql = new StringBuilder("""
				select count(*)
				from merchant_product p
				left join merchant m on m.id = p.merchant_id
				where p.deleted = 0
				""");
		List<Object> args = new ArrayList<>();
		if (merchantId != null) {
			sql.append(" and p.merchant_id = ?");
			countSql.append(" and p.merchant_id = ?");
			args.add(merchantId);
		}
		if (merchantName != null && !merchantName.isBlank()) {
			sql.append(" and m.merchant_name like ?");
			countSql.append(" and m.merchant_name like ?");
			args.add("%" + merchantName.trim() + "%");
		}
		if (contactMobile != null && !contactMobile.isBlank()) {
			sql.append(" and m.contact_mobile like ?");
			countSql.append(" and m.contact_mobile like ?");
			args.add("%" + contactMobile.trim() + "%");
		}
		if (keyword != null && !keyword.isBlank()) {
			sql.append(" and p.product_name like ?");
			countSql.append(" and p.product_name like ?");
			String like = "%" + keyword.trim() + "%";
			args.add(like);
		}
		if (type != null && !type.isBlank()) {
			sql.append(" and m.merchant_type = ?");
			countSql.append(" and m.merchant_type = ?");
			args.add(type);
		}
		if (status != null) {
			sql.append(" and p.audit_status = ?");
			countSql.append(" and p.audit_status = ?");
			args.add(status);
		}
		if (createdStart != null && !createdStart.isBlank()) {
			sql.append(" and date(p.created_at) >= ?");
			countSql.append(" and date(p.created_at) >= ?");
			args.add(createdStart);
		}
		if (createdEnd != null && !createdEnd.isBlank()) {
			sql.append(" and date(p.created_at) <= ?");
			countSql.append(" and date(p.created_at) <= ?");
			args.add(createdEnd);
		}
		sql.append(" order by p.submit_time desc, p.id desc");
		return page(sql.toString(), countSql.toString(), page, size, args.toArray());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> product(Long id) {
		return one("""
				select p.id, p.merchant_id, m.merchant_name, m.merchant_type, m.contact_mobile,
				       p.product_no, p.product_type, p.product_name,
				       p.subtitle, p.cover_url, p.original_price, p.sale_price, p.subsidy_amount,
				       p.is_government_subsidy, p.stock, p.sales_count, p.detail_text, p.benefit_desc,
				       p.purchase_notice, p.valid_start_time, p.valid_end_time, p.product_images, p.rights_config,
				       p.audit_status, p.audit_remark, p.status, p.sort_no, p.submit_time, p.created_at, p.updated_at
				from merchant_product p
				left join merchant m on m.id = p.merchant_id
				where p.deleted = 0 and p.id = ?
				""", id);
	}

	@Transactional
	public Map<String, Object> createProduct(ProductCreateRequest request) {
		ensureMerchantExists(request.merchantId());
		validateProductConfiguration(request.merchantId(), request.salePrice(), request.productImages());
		validateProductRights(request.merchantId(), request.rightsConfig());
		String productNo = BusinessIdGenerator.next("P");
		BigDecimal subsidyAmount = valueOrZero(request.subsidyAmount());
		jdbcTemplate.update("""
				insert into merchant_product(product_no, merchant_id, product_type, product_name, subtitle, cover_url,
				                             original_price, sale_price, subsidy_amount, is_government_subsidy, stock,
				                             detail_text, benefit_desc, purchase_notice, valid_start_time, valid_end_time,
				                             product_images, rights_config, audit_status, status, submit_time)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 0, now())
				""",
				productNo,
				request.merchantId(),
				request.productType(),
				request.productName(),
				request.subtitle(),
				request.coverUrl(),
				valueOrZero(request.originalPrice()),
				valueOrZero(request.salePrice()),
				subsidyAmount,
				subsidyAmount.compareTo(BigDecimal.ZERO) > 0 ? 1 : 0,
				request.stock() == null ? -1 : request.stock(),
				request.detailText(),
				request.benefitDesc(),
				toJson(request.purchaseNotice()),
				blankToNull(request.validStartTime()),
				blankToNull(request.validEndTime()),
				toJson(request.productImages()),
				toJson(request.rightsConfig()));
		return one("select * from merchant_product where product_no = ?", productNo);
	}

	@Transactional
	public Map<String, Object> updateProduct(Long id, ProductUpdateRequest request) {
		ensureMerchantExists(request.merchantId());
		validateProductConfiguration(request.merchantId(), request.salePrice(), request.productImages());
		validateProductRights(request.merchantId(), request.rightsConfig());
		BigDecimal subsidyAmount = valueOrZero(request.subsidyAmount());
		int updated = jdbcTemplate.update("""
				update merchant_product
				set merchant_id = ?, product_type = ?, product_name = ?, subtitle = ?, cover_url = ?,
				    original_price = ?, sale_price = ?, subsidy_amount = ?, is_government_subsidy = ?,
				    stock = ?, detail_text = ?, benefit_desc = ?, purchase_notice = ?, valid_start_time = ?,
				    valid_end_time = ?, product_images = ?, rights_config = ?, status = 0, sort_no = ?,
				    audit_status = 1, audit_remark = null, audit_time = null, submit_time = now()
				where deleted = 0 and id = ?
				""",
				request.merchantId(),
				request.productType(),
				request.productName(),
				request.subtitle(),
				request.coverUrl(),
				valueOrZero(request.originalPrice()),
				valueOrZero(request.salePrice()),
				subsidyAmount,
				subsidyAmount.compareTo(BigDecimal.ZERO) > 0 ? 1 : 0,
				request.stock() == null ? -1 : request.stock(),
				request.detailText(),
				request.benefitDesc(),
				toJson(request.purchaseNotice()),
				blankToNull(request.validStartTime()),
				blankToNull(request.validEndTime()),
				toJson(request.productImages()),
				toJson(request.rightsConfig()),
				request.sortNo() == null ? 0 : request.sortNo(),
				id);
		ensureUpdated(updated);
		return product(id);
	}

	@Transactional
	public Map<String, Object> updateProductStatus(Long id, Byte status) {
		Number auditStatus = (Number) product(id).get("auditStatus");
		if (auditStatus == null || auditStatus.intValue() != 2) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "Only approved products can be listed or unlisted");
		}
		int updated = jdbcTemplate.update("update merchant_product set status = ? where deleted = 0 and id = ?", status, id);
		ensureUpdated(updated);
		return product(id);
	}

	@Transactional
	public Map<String, Object> deleteProduct(Long id) {
		Map<String, Object> existing = product(id);
		int updated = jdbcTemplate.update("update merchant_product set deleted = 1 where deleted = 0 and id = ?", id);
		ensureUpdated(updated);
		return existing;
	}

	@Transactional
	public Map<String, Object> auditProduct(Long id, ProductAuditRequest request) {
		if (request.auditStatus() != 2 && request.auditStatus() != 3) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "Invalid audit status");
		}
		Number currentAuditStatus = (Number) product(id).get("auditStatus");
		if (currentAuditStatus == null || currentAuditStatus.intValue() != 1) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "Product is not pending audit");
		}
		int updated = jdbcTemplate.update("""
				update merchant_product
				set audit_status = ?, audit_remark = ?, audit_time = now(), status = ?
				where deleted = 0 and id = ?
				""", request.auditStatus(), request.auditRemark(), request.auditStatus() == 2 ? 1 : 0, id);
		ensureUpdated(updated);
		return product(id);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> coupons(
			Long merchantId,
			String keyword,
			Integer status,
			String createdStart,
			String createdEnd,
			int page,
			int size
	) {
		StringBuilder sql = new StringBuilder("""
				select c.id, c.merchant_id, m.merchant_name, c.coupon_name, c.condition_type,
				       c.condition_amount, c.coupon_amount, c.talent_levels, c.talent_level_quantities, c.valid_start, c.valid_end,
				       c.product_scope, c.store_consume, c.government_subsidy, c.use_count, c.status,
				       c.created_at, c.updated_at,
				       (select count(*) from merchant_coupon_product cp where cp.coupon_id = c.id) as product_count,
				       (select group_concat(p.product_name separator '、')
				        from merchant_coupon_product cp
				        left join merchant_product p on p.id = cp.product_id
				        where cp.coupon_id = c.id and p.deleted = 0) as product_name
				from merchant_coupon c
				left join merchant m on m.id = c.merchant_id
				where c.deleted = 0
				""");
		StringBuilder countSql = new StringBuilder("""
				select count(*)
				from merchant_coupon c
				left join merchant m on m.id = c.merchant_id
				where c.deleted = 0
				""");
		List<Object> args = new ArrayList<>();
		if (merchantId != null) {
			sql.append(" and c.merchant_id = ?");
			countSql.append(" and c.merchant_id = ?");
			args.add(merchantId);
		}
		if (keyword != null && !keyword.isBlank()) {
			sql.append(" and c.coupon_name like ?");
			countSql.append(" and c.coupon_name like ?");
			args.add("%" + keyword.trim() + "%");
		}
		if (status != null) {
			sql.append(" and c.status = ?");
			countSql.append(" and c.status = ?");
			args.add(status);
		}
		if (createdStart != null && !createdStart.isBlank()) {
			sql.append(" and date(c.created_at) >= ?");
			countSql.append(" and date(c.created_at) >= ?");
			args.add(createdStart);
		}
		if (createdEnd != null && !createdEnd.isBlank()) {
			sql.append(" and date(c.created_at) <= ?");
			countSql.append(" and date(c.created_at) <= ?");
			args.add(createdEnd);
		}
		sql.append(" order by c.id desc");
		return page(sql.toString(), countSql.toString(), page, size, args.toArray());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> coupon(Long id) {
		Map<String, Object> result = one("""
				select c.id, c.merchant_id, m.merchant_name, c.coupon_name, c.condition_type,
				       c.condition_amount, c.coupon_amount, c.talent_levels, c.talent_level_quantities, c.valid_start, c.valid_end,
				       c.product_scope, c.store_consume, c.government_subsidy, c.use_count, c.status,
				       c.created_at, c.updated_at
				from merchant_coupon c
				left join merchant m on m.id = c.merchant_id
				where c.deleted = 0 and c.id = ?
				""", id);
		result.put("productIds", couponProductIds(id));
		result.put("products", couponProducts(id));
		return result;
	}

	@Transactional
	public Map<String, Object> createCoupon(CouponCreateRequest request) {
		ensureMerchantExists(request.merchantId());
		validateCouponConfiguration(request.validStart(), request.validEnd(), request.productScope(), request.productIds());
		validateCouponAmount(request.couponAmount());
		validateTalentLevelQuantities(request.talentLevelQuantities());
		jdbcTemplate.update("""
				insert into merchant_coupon(merchant_id, coupon_name, condition_type, condition_amount,
				                            coupon_amount, talent_levels, talent_level_quantities, valid_start, valid_end,
				                            product_scope, store_consume, government_subsidy, status)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
				""",
				request.merchantId(),
				request.couponName(),
				normalizeConditionType(request.conditionType()),
				request.conditionAmount(),
				valueOrZero(request.couponAmount()),
				joinTalentLevels(request.talentLevels()),
				levelQuantitiesJson(request.talentLevelQuantities()),
				blankToNull(request.validStart()),
				blankToNull(request.validEnd()),
				normalizeProductScope(request.productScope()),
				booleanToByte(request.storeConsume(), true),
				booleanToByte(request.governmentSubsidy(), true));
		Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		syncCouponProducts(id, request.productIds(), request.productScope());
		return coupon(id);
	}

	@Transactional
	public Map<String, Object> updateCoupon(Long id, CouponUpdateRequest request) {
		ensureMerchantExists(request.merchantId());
		validateCouponConfiguration(request.validStart(), request.validEnd(), request.productScope(), request.productIds());
		validateCouponAmount(request.couponAmount());
		validateTalentLevelQuantities(request.talentLevelQuantities());
		int updated = jdbcTemplate.update("""
				update merchant_coupon
				set merchant_id = ?, coupon_name = ?, condition_type = ?, condition_amount = ?,
				    coupon_amount = ?, talent_levels = ?, talent_level_quantities = ?, valid_start = ?, valid_end = ?,
				    product_scope = ?, store_consume = ?, government_subsidy = ?, status = ?
				where deleted = 0 and id = ?
				""",
				request.merchantId(),
				request.couponName(),
				normalizeConditionType(request.conditionType()),
				request.conditionAmount(),
				valueOrZero(request.couponAmount()),
				joinTalentLevels(request.talentLevels()),
				levelQuantitiesJson(request.talentLevelQuantities()),
				blankToNull(request.validStart()),
				blankToNull(request.validEnd()),
				normalizeProductScope(request.productScope()),
				booleanToByte(request.storeConsume(), true),
				booleanToByte(request.governmentSubsidy(), true),
				request.status() == null ? 1 : request.status(),
				id);
		ensureUpdated(updated);
		syncCouponProducts(id, request.productIds(), request.productScope());
		return coupon(id);
	}

	@Transactional
	public Map<String, Object> updateCouponStatus(Long id, Byte status) {
		int updated = jdbcTemplate.update("update merchant_coupon set status = ? where deleted = 0 and id = ?", status, id);
		ensureUpdated(updated);
		return coupon(id);
	}

	@Transactional
	public Map<String, Object> deleteCoupon(Long id) {
		Map<String, Object> existing = coupon(id);
		int updated = jdbcTemplate.update("update merchant_coupon set deleted = 1 where deleted = 0 and id = ?", id);
		ensureUpdated(updated);
		jdbcTemplate.update("delete from merchant_coupon_product where coupon_id = ?", id);
		return existing;
	}

	private long count(String sql) {
		Long value = jdbcTemplate.queryForObject(sql, Long.class);
		return value == null ? 0 : value;
	}

	private Map<String, Object> one(String sql, Object... args) {
		List<Map<String, Object>> rows = DatabaseRows.normalize(jdbcTemplate.queryForList(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 = Arrays.copyOf(args, args.length + 2);
		pageArgs[args.length] = safeSize;
		pageArgs[args.length + 1] = (safePage - 1) * safeSize;
		List<Map<String, Object>> records = DatabaseRows.normalize(jdbcTemplate.queryForList(sql + " limit ? offset ?", pageArgs));
		Long total = jdbcTemplate.queryForObject(countSql, Long.class, args);
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	private void applyBusinessStatus(List<Map<String, Object>> merchants) {
		if (merchants.isEmpty()) return;
		List<Long> merchantIds = merchants.stream().map(row -> Long.valueOf(String.valueOf(row.get("id")))).toList();
		String placeholders = String.join(",", java.util.Collections.nCopies(merchantIds.size(), "?"));
		List<Map<String, Object>> periods = DatabaseRows.normalize(jdbcTemplate.queryForList("""
				select h.merchant_id, h.start_time, h.end_time
				from merchant_business_hours h
				join merchant_store s on s.id = h.store_id
				where s.status = 1 and s.deleted = 0 and h.merchant_id in (""" + placeholders + ")", merchantIds.toArray()));
		LocalTime now = LocalTime.now(ZoneId.of("Asia/Shanghai"));
		Set<Long> openMerchantIds = new HashSet<>();
		Set<Long> merchantsWithBusinessHours = new HashSet<>();
		for (Map<String, Object> period : periods) {
			Long merchantId = Long.valueOf(String.valueOf(period.get("merchantId")));
			merchantsWithBusinessHours.add(merchantId);
			if (isWithinBusinessPeriod(now, String.valueOf(period.get("startTime")), String.valueOf(period.get("endTime")))) {
				openMerchantIds.add(merchantId);
			}
		}
		for (Map<String, Object> merchant : merchants) {
			Long merchantId = Long.valueOf(String.valueOf(merchant.get("id")));
			boolean enabled = "1".equals(String.valueOf(merchant.get("status")));
			boolean open = !merchantsWithBusinessHours.contains(merchantId) || openMerchantIds.contains(merchantId);
			merchant.put("businessStatus", enabled && open ? "营业中" : "休息中");
		}
	}

	private boolean isWithinBusinessPeriod(LocalTime now, String startValue, String endValue) {
		try {
			LocalTime start = LocalTime.parse(startValue);
			LocalTime end = LocalTime.parse(endValue);
			return start.isBefore(end)
					? !now.isBefore(start) && now.isBefore(end)
					: !now.isBefore(start) || now.isBefore(end);
		} catch (DateTimeParseException ignored) {
			return false;
		}
	}

	private void ensureUpdated(int updated) {
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "Data not found");
		}
	}

	private void ensureMerchantExists(Long merchantId) {
		Long count = jdbcTemplate.queryForObject(
				"select count(*) from merchant where deleted = 0 and id = ?",
				Long.class,
				merchantId);
		if (count == null || count == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "Merchant not found");
		}
	}

	private BigDecimal valueOrZero(BigDecimal value) {
		return value == null ? BigDecimal.ZERO : value;
	}

	private void validateCouponAmount(BigDecimal value) {
		if (value == null || value.compareTo(new BigDecimal("0.1")) < 0 || value.compareTo(new BigDecimal("9999.9")) > 0) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "优惠券金额必须在0.1-9999.9元之间");
		}
	}
	private void validateTalentLevelQuantities(Map<String, Integer> values) {
		if (values == null) return;
		values.values().forEach(value -> { if (value == null || value < 1 || value > 99) throw new BusinessException(ErrorCode.BAD_REQUEST, "人才卡发放数量必须为1-99"); });
	}
	private String levelQuantitiesJson(Map<String, Integer> values) { return values == null || values.isEmpty() ? null : JSON.toJSONString(values); }

	private String toJson(Object value) {
		if (value == null) {
			return null;
		}
		if (value instanceof Map<?, ?> map) {
			List<String> entries = new ArrayList<>();
			for (Map.Entry<?, ?> entry : map.entrySet()) {
				entries.add(quoteJson(String.valueOf(entry.getKey())) + ":" + toJsonValue(entry.getValue()));
			}
			return "{" + String.join(",", entries) + "}";
		}
		if (value instanceof List<?> list) {
			return "[" + String.join(",", list.stream().map(this::toJsonValue).toList()) + "]";
		}
		return toJsonValue(value);
	}

	private String toJsonValue(Object value) {
		if (value == null) {
			return "null";
		}
		if (value instanceof Number || value instanceof Boolean) {
			return String.valueOf(value);
		}
		if (value instanceof Map<?, ?> || value instanceof List<?>) {
			return toJson(value);
		}
		return quoteJson(String.valueOf(value));
	}

	private String quoteJson(String value) {
		return "\"" + value
				.replace("\\", "\\\\")
				.replace("\"", "\\\"")
				.replace("\r", "\\r")
				.replace("\n", "\\n") + "\"";
	}

	private String joinTalentLevels(List<String> talentLevels) {
		if (talentLevels == null || talentLevels.isEmpty()) {
			return "";
		}
		return String.join(",", talentLevels);
	}

	private String normalizeConditionType(String value) {
		return "NONE".equalsIgnoreCase(value) ? "NONE" : "FULL";
	}

	private String normalizeProductScope(String value) {
		return "ALL".equalsIgnoreCase(value) ? "ALL" : "SELECTED";
	}

	private void validateProductRights(Long merchantId, List<Map<String, Object>> rightsConfig) {
		if (rightsConfig == null) {
			return;
		}
		if (isHotelMerchant(merchantId) || isHealthCheckMerchant(merchantId)) {
			return;
		}
		for (Map<String, Object> right : rightsConfig) {
			if (!Boolean.TRUE.equals(right.get("free"))) {
				continue;
			}
			Object value = right.get("limitCount");
			try {
				int count = new BigDecimal(String.valueOf(value)).intValueExact();
				if (count < 1 || count > 999) {
					throw new NumberFormatException();
				}
			} catch (NumberFormatException | ArithmeticException exception) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "免费次数必须为 1 至 999 的整数");
			}
		}
	}

	private void validateProductConfiguration(Long merchantId, BigDecimal salePrice, List<String> productImages) {
		if (isHealthCheckMerchant(merchantId)) {
			return;
		}
		if (salePrice == null) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "商品金额不能为空");
		}
		if (productImages == null || productImages.isEmpty()) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "请至少添加一张商品图片");
		}
	}

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

	private boolean isHealthCheckMerchant(Long merchantId) {
		String type = merchantType(merchantId);
		return "healthCheck".equalsIgnoreCase(type) || "免费体检".equals(type);
	}

	private String merchantType(Long merchantId) {
		List<String> types = jdbcTemplate.queryForList(
				"select merchant_type from merchant where deleted = 0 and id = ? limit 1",
				String.class,
				merchantId);
		if (types.isEmpty()) {
			return "";
		}
		return types.get(0) == null ? "" : types.get(0).trim();
	}

	private void validateCouponConfiguration(String validStart, String validEnd, String productScope, List<Long> productIds) {
		if (validStart == null || validStart.isBlank() || validEnd == null || validEnd.isBlank()) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "优惠券有效期不能为空");
		}
		try {
			LocalDate start = LocalDate.parse(validStart);
			LocalDate end = LocalDate.parse(validEnd);
			if (start.isAfter(end)) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "优惠券结束日期不能早于开始日期");
			}
		} catch (DateTimeParseException exception) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "优惠券有效期格式错误");
		}
		if (!"ALL".equalsIgnoreCase(productScope) && (productIds == null || productIds.isEmpty())) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "请至少选择一个可用商品");
		}
	}

	private int defaultNewsStatus(Integer status) {
		return status == null ? 1 : status;
	}

	private String sanitizeNewsContent(String content) {
		if (content == null || content.isBlank()) {
			return null;
		}
		Safelist safelist = Safelist.relaxed()
				.removeTags("script")
				.addTags("span", "video", "source")
				.addAttributes(":all", "style", "class")
				.addAttributes("a", "target")
				.addAttributes("video", "src", "controls", "poster", "width", "height")
				.addAttributes("source", "src", "type")
				.addProtocols("a", "href", "http", "https")
				.addProtocols("img", "src", "http", "https")
				.addProtocols("video", "src", "http", "https")
				.addProtocols("source", "src", "http", "https");
		return Jsoup.clean(content, safelist);
	}

	private String blankToNull(String value) {
		return value == null || value.isBlank() ? null : value;
	}

	private byte booleanToByte(Boolean value, boolean defaultValue) {
		return Boolean.TRUE.equals(value == null ? defaultValue : value) ? (byte) 1 : (byte) 0;
	}

	private List<Long> couponProductIds(Long couponId) {
		return jdbcTemplate.queryForList(
				"select product_id from merchant_coupon_product where coupon_id = ? order by id",
				Long.class,
				couponId);
	}

	private List<Map<String, Object>> couponProducts(Long couponId) {
		return DatabaseRows.normalize(jdbcTemplate.queryForList("""
				select p.id, p.product_name, p.cover_url, p.sale_price, p.sales_count, p.submit_time, p.created_at,
				       p.audit_status, p.status
				from merchant_coupon_product cp
				join merchant_product p on p.id = cp.product_id
				where cp.coupon_id = ? and p.deleted = 0
				order by cp.id
				""", couponId));
	}

	private void syncCouponProducts(Long couponId, List<Long> productIds, String productScope) {
		jdbcTemplate.update("delete from merchant_coupon_product where coupon_id = ?", couponId);
		if ("ALL".equalsIgnoreCase(productScope) || productIds == null || productIds.isEmpty()) {
			return;
		}
		for (Long productId : productIds.stream().distinct().toList()) {
			jdbcTemplate.update(
					"insert into merchant_coupon_product(coupon_id, product_id) values (?, ?)",
					couponId,
					productId);
		}
	}
}