package com.talentcard.server.business.admin;

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

import com.talentcard.server.business.BusinessIdGenerator;
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 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, 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, 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> newsDetail(Long id) {
		return one("""
				select id, title, summary, cover_url, source, author, content, view_count, is_top, 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) {
		int status = defaultNewsStatus(request.status());
		jdbcTemplate.update("""
				insert into news_article(title, summary, cover_url, source, author, content, is_top, sort_no, publish_time, status)
				values (?, ?, ?, ?, ?, ?, ?, ?, case when ? = 1 then now() else null end, ?)
				""",
				request.title().trim(),
				blankToNull(request.summary()),
				blankToNull(request.coverUrl()),
				blankToNull(request.source()),
				blankToNull(request.author()),
				sanitizeNewsContent(request.content()),
				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 = ?, source = ?, author = ?, 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.summary()),
				blankToNull(request.coverUrl()),
				blankToNull(request.source()),
				blankToNull(request.author()),
				sanitizeNewsContent(request.content()),
				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) {
		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(request.author()), 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,
				       case
				         when m.status <> 1 then '休息中'
				         when lower(coalesce(m.business_status, 'open')) in ('closed', '休息中') then '休息中'
				         when exists (
				           select 1
				           from merchant_business_hours h
				           join merchant_store s on s.id = h.store_id and s.status = 1 and s.deleted = 0
				           where h.merchant_id = m.id
				             and (
				               (h.start_time <= h.end_time and current_time() >= h.start_time and current_time() < h.end_time)
				               or (h.start_time > h.end_time and (current_time() >= h.start_time or current_time() < h.end_time))
				             )
				         ) then '营业中'
				         else '休息中'
				       end as business_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");
		return page(sql.toString(), countSql.toString(), page, size, args.toArray());
	}

	@Transactional(readOnly = true)
	public Map<String, Object> merchant(Long id) {
		return one("""
				select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url,
				       address, 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, created_at, updated_at,
				       (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
				where deleted = 0 and id = ?
				""", id);
	}

	@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) {
		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);
		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_store(merchant_id, store_name, address, status) values (?, ?, ?, 1)", id, request.merchantName().trim(), request.address());
			Long storeId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
			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);
	}

	private boolean hasMerchantAppAccess(MerchantCreateRequest request) {
		return ("餐饮服务".equals(request.merchantType()) || "景区服务".equals(request.merchantType()))
				&& (request.canLoginMerchantApp() == null || request.canLoginMerchantApp() == 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);
		return merchant(id);
	}

	@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());
		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());
		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.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.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());
		jdbcTemplate.update("""
				insert into merchant_coupon(merchant_id, coupon_name, condition_type, condition_amount,
				                            coupon_amount, talent_levels, 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()),
				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());
		int updated = jdbcTemplate.update("""
				update merchant_coupon
				set merchant_id = ?, coupon_name = ?, condition_type = ?, condition_amount = ?,
				    coupon_amount = ?, talent_levels = ?, 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()),
				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 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 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 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("style", "script")
				.addAttributes("a", "target")
				.addProtocols("a", "href", "https")
				.addProtocols("img", "src", "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);
		}
	}
}