package com.talentcard.server.business.portal;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.MoneyUtils;
import com.talentcard.server.business.payment.PaymentReconciliationService;
import com.talentcard.server.business.payment.PaymentReconciliationService.PendingPaymentConfirmation;
import com.talentcard.server.business.payment.PaymentReconciliationService.PendingPaymentState;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PendingOrderPortalService {
	private final JdbcTemplate jdbcTemplate;
	private final PaymentReconciliationService paymentReconciliationService;

	public PendingOrderPortalService(JdbcTemplate jdbcTemplate, PaymentReconciliationService paymentReconciliationService) {
		this.jdbcTemplate = jdbcTemplate;
		this.paymentReconciliationService = paymentReconciliationService;
	}

	@Transactional
	public Map<String, Object> detail(String orderNo, Long talentId) {
		expireOrder(orderNo, talentId);
		Map<String, Object> order = find(orderNo, talentId);
		return view(order);
	}

	/** Cancels every unpaid order that has reached its payment deadline. */
	@Transactional
	public int expireDueOrders() {
		return expireDueOrders(null);
	}

	/**
	 * Cancels expired unpaid orders for one talent user.  Besides the scheduled
	 * cleanup this is used on the purchase path, so an expired order can never
	 * continue to occupy that user's coupon while the scheduler is waiting for
	 * its next run.
	 *
	 * <p>订单带着未定局的支付尝试时一律不自动取消：那种「待支付」只说明本地不知道钱有没有收下，
	 * 到期时间不等于没收到钱。这些订单交给对账任务先向通道问清（已收款就落成已支付，通道说失败就
	 * 把流水置为已失败），下一轮清理自然会收掉它们。到期就自动取消会把已经收下的钱变成一笔没有
	 * 订单、也无法退款的悬账。
	 *
	 * <p>{@code CANCEL_UNCONFIRMED} 与 {@code CLOSING} 一并排除：它们是「关不掉的预支付单」，
	 * 通道既没说关掉也没说钱收下了，所以在通道侧仍然可能被支付。自动取消这样一笔订单，等于把已经
	 * 收下的钱变成没有出口的悬账。它们由关单补偿给出终止条件（重试上限后落成已取消并保留盯防）。
	 */
	@Transactional
	public int expireDueOrders(Long talentId) {
		String sql = """
			update benefit_order o
			set order_status = 2
			where o.deleted = 0 and o.order_status = 0
			  and o.expires_at is not null and o.expires_at <= current_timestamp
			  and not exists (
				select 1 from payment_transaction p
				where p.order_id = o.id
				  and p.status in ('CREATED', 'PREPAY_CREATED', 'CREATE_UNKNOWN', 'CLOSING', 'CANCEL_UNCONFIRMED')
			  )
			""";
		if (talentId == null) return jdbcTemplate.update(sql);
		return jdbcTemplate.update(sql + " and o.talent_id = ?", talentId);
	}

	@Transactional
	public Map<String, Object> chooseCoupon(String orderNo, PendingOrderCouponRequest request) {
		Map<String, Object> order = find(orderNo, request.talentId());
		ensurePayable(order, request.priceVersion());
		Integer prepayCount = jdbcTemplate.queryForObject("""
			select count(*) from payment_transaction
			where order_no = ? and talent_id = ?
			  and status in ('CREATED', 'PREPAY_CREATED', 'CREATE_UNKNOWN', 'CLOSING', 'CANCEL_UNCONFIRMED', 'SUCCEEDED')
			""", Integer.class, orderNo, request.talentId());
		if (prepayCount != null && prepayCount > 0) {
			throw new BusinessException(ErrorCode.CONFLICT, "支付已发起，不能再修改优惠券");
		}
		Long merchantId = longValue(order.get("merchantId"));
		Long productId = longValue(order.get("productId"));
		if (merchantId == null || productId == null) throw new BusinessException(ErrorCode.CONFLICT, "该订单暂不支持选择优惠券");
		BigDecimal basePayAmount = yuan(order.get("basePayAmount"));
		String cardLevel = activeCardLevel(request.talentId());
		List<Map<String, Object>> coupons = query("""
			select id, coupon_amount from merchant_coupon c
			where c.id = ? and c.deleted = 0 and c.status = 1 and c.merchant_id = ?
			  and (c.valid_start is null or c.valid_start <= current_date)
			  and (c.valid_end is null or c.valid_end >= current_date)
			  and (c.condition_type = 'NONE' or c.condition_amount is null or c.condition_amount <= ?)
			  and (coalesce(c.talent_levels, '') = '' or find_in_set(?, replace(c.talent_levels, '、', ',')) > 0)
			  and (c.product_scope = 'ALL' or exists (select 1 from merchant_coupon_product cp where cp.coupon_id = c.id and cp.product_id = ?))
			  and not exists (
				select 1 from benefit_order o
				where o.coupon_id = c.id and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
				  and o.order_no <> ? and (o.expires_at is null or o.expires_at > current_timestamp)
			  )
			""", request.couponId(), merchantId, basePayAmount, cardLevel, productId, request.talentId(), orderNo);
		if (coupons.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "该优惠券当前不可用，请重新选择");
		long couponAmount = Math.min(MoneyUtils.yuanToCent((BigDecimal) coupons.get(0).get("couponAmount")), longNumber(order.get("basePayAmount")));
		long payAmount = Math.max(0, longNumber(order.get("basePayAmount")) - couponAmount);
		long subsidyAmount = Math.max(0, resolveOriginalAmount(order, couponAmount, payAmount) - payAmount);
		jdbcTemplate.update("""
			update benefit_order set coupon_id = ?, coupon_amount = ?, pay_amount = ?, subsidy_amount = ?, price_version = price_version + 1
			where order_no = ? and talent_id = ? and order_status = 0 and price_version = ?
			""", request.couponId(), couponAmount, payAmount, subsidyAmount, orderNo, request.talentId(), request.priceVersion());
		return detail(orderNo, request.talentId());
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> coupons(String orderNo, Long talentId) {
		Map<String, Object> order = find(orderNo, talentId);
		Long merchantId = longValue(order.get("merchantId"));
		Long productId = longValue(order.get("productId"));
		if (merchantId == null || productId == null) return List.of();
		BigDecimal basePayAmount = yuan(order.get("basePayAmount"));
		String cardLevel = activeCardLevel(talentId);
		return query("""
			select c.id, c.coupon_name as name, c.coupon_amount as amount,
			       case when c.condition_type = 'NONE' or c.condition_amount is null then '无门槛使用' else concat('满', c.condition_amount, '元可用') end as scope,
			       c.valid_end as expire_time, 1 as available
			from merchant_coupon c
			where c.deleted = 0 and c.status = 1 and c.merchant_id = ?
			  and (c.valid_start is null or c.valid_start <= current_date)
			  and (c.valid_end is null or c.valid_end >= current_date)
			  and (c.condition_type = 'NONE' or c.condition_amount is null or c.condition_amount <= ?)
			  and (coalesce(c.talent_levels, '') = '' or find_in_set(?, replace(c.talent_levels, '、', ',')) > 0)
			  and (c.product_scope = 'ALL' or exists (select 1 from merchant_coupon_product cp where cp.coupon_id = c.id and cp.product_id = ?))
			  and not exists (
				select 1 from benefit_order o
				where o.coupon_id = c.id and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
				  and o.order_no <> ? and (o.expires_at is null or o.expires_at > current_timestamp)
			  )
			order by c.coupon_amount desc, c.id desc
			""", merchantId, basePayAmount, cardLevel, productId, talentId, orderNo);
	}

	@Transactional
	public Map<String, Object> pay(String orderNo, PendingOrderPaymentRequest request) {
		Map<String, Object> order = find(orderNo, request.talentId());
		ensurePayable(order, request.priceVersion());
		int updated = jdbcTemplate.update("""
			update benefit_order set order_status = 1,
			    verify_status = case when order_type = 'STORE_CONSUME' then 1 else verify_status end,
			    price_version = price_version + 1
			where order_no = ? and talent_id = ? and order_status = 0 and price_version = ? and (expires_at is null or expires_at > current_timestamp)
			""", orderNo, request.talentId(), request.priceVersion());
		if (updated == 0) throw new BusinessException(ErrorCode.CONFLICT, "订单状态或金额已变化，请重新确认");
		Map<String, Object> result = detail(orderNo, request.talentId());
		result.put("paymentStatus", "PAID");
		return result;
	}

	private void ensurePayable(Map<String, Object> order, int priceVersion) {
		if (longNumber(order.get("orderStatus")) != 0) throw new BusinessException(ErrorCode.CONFLICT, "订单当前不可支付");
		if (longNumber(order.get("priceVersion")) != priceVersion) throw new BusinessException(ErrorCode.CONFLICT, "订单金额已更新，请重新确认");
		Object expiresAt = order.get("expiresAt");
		if (expiresAt instanceof LocalDateTime time && !time.isAfter(LocalDateTime.now())) throw new BusinessException(ErrorCode.CONFLICT, "订单已超时，请重新下单");
	}

	private Map<String, Object> find(String orderNo, Long talentId) {
		List<Map<String, Object>> rows = query("""
			select o.order_no, o.talent_id, o.merchant_id, o.order_type, o.product_id, o.order_amount, o.subsidy_amount, o.base_pay_amount,
			       o.pay_amount, o.coupon_amount, o.coupon_id, o.order_status, o.verify_status, o.expires_at, o.price_version,
			       o.merchant_name_snapshot, o.merchant_address_snapshot, o.merchant_mobile_snapshot,
				       o.merchant_longitude_snapshot, o.merchant_latitude_snapshot, o.product_name_snapshot,
				       c.coupon_name, c.coupon_amount as configured_coupon_amount, p.original_price as product_original_price
			from benefit_order o left join merchant_coupon c on c.id = o.coupon_id
			left join merchant_product p on p.id = o.product_id and p.deleted = 0
			where o.deleted = 0 and o.order_no = ? and o.talent_id = ?
			""", orderNo, talentId);
		if (rows.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "订单不存在或已删除");
		return rows.get(0);
	}

	private Map<String, Object> view(Map<String, Object> order) {
		Map<String, Object> result = new LinkedHashMap<>(order);
		long orderStatus = longNumber(order.get("orderStatus"));
		boolean expired = orderStatus == 0 && order.get("expiresAt") instanceof LocalDateTime time && !time.isAfter(LocalDateTime.now());
		result.put("expired", expired);
		result.put("canPay", orderStatus == 0 && !expired);
		boolean couponSelectable = longValue(order.get("productId")) != null
				&& !"GROUP_BUY".equals(String.valueOf(order.get("orderType")))
				&& orderStatus == 0 && !expired;
		result.put("couponSelectable", couponSelectable);
		long couponAmount = longNumber(order.get("couponAmount"));
		if (couponAmount <= 0 && order.get("couponId") != null) {
			couponAmount = Math.min(
					MoneyUtils.yuanToCent(decimal(order.get("configuredCouponAmount"))),
					longNumber(order.get("basePayAmount")));
		}
		result.put("couponLabel", order.get("couponName") == null ? "无" : "减" + yuan(couponAmount).stripTrailingZeros().toPlainString() + "元");
		result.put("talentDiscountText", talentDiscountText(order));
		long originalAmount = resolveOriginalAmount(order, couponAmount, longNumber(order.get("payAmount")));
		result.put("originalAmount", yuan(originalAmount));
		// “优惠金额”在待支付页仅表示优惠券抵扣，不应把人才折扣或补贴混入其中。
		result.put("discountAmount", yuan(couponAmount));
		result.put("payAmount", yuan(order.get("payAmount")));
		// 待支付页的补贴金额展示人才折扣与优惠券抵扣合计，即原价减去用户实付。
		result.put("subsidyAmount", yuan(Math.max(0, originalAmount - longNumber(order.get("payAmount")))));
		return result;
	}

	private long resolveOriginalAmount(Map<String, Object> order, long couponAmount, long payAmount) {
		long originalAmount = longNumber(order.get("orderAmount"));
		if (originalAmount > 0) return originalAmount;
		BigDecimal productOriginalPrice = decimal(order.get("productOriginalPrice"));
		if (productOriginalPrice.signum() > 0) return MoneyUtils.yuanToCent(productOriginalPrice);
		long basePayAmount = longNumber(order.get("basePayAmount"));
		if (basePayAmount > 0) return basePayAmount;
		// Historical orders may have missed both price snapshots. Their selected
		// coupon and payable amount still represent the amount before coupon use.
		return Math.max(0, payAmount + couponAmount);
	}

	private List<Map<String, Object>> query(String sql, Object... args) { return jdbcTemplate.queryForList(sql, args).stream().map(DatabaseRows::normalize).toList(); }
	private BigDecimal decimal(Object value) {
		if (value == null) return BigDecimal.ZERO;
		if (value instanceof BigDecimal decimal) return decimal;
		try { return new BigDecimal(String.valueOf(value)); }
		catch (Exception ignored) { return BigDecimal.ZERO; }
	}
	private String activeCardLevel(Long talentId) {
		List<Map<String, Object>> cards = query("select card_level from talent_card where talent_id=? and deleted=0 and status=1 and card_type='PERSONAL' and (valid_end is null or valid_end >= current_date) order by id desc limit 1", talentId);
		if (cards.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "当前暂无有效人才卡");
		return String.valueOf(cards.get(0).get("cardLevel"));
	}

	@Transactional
	public Map<String, Object> cancel(String orderNo, Long talentId) {
		Map<String, Object> order = find(orderNo, talentId);
		if (longNumber(order.get("orderStatus")) == 2) return view(order);
		if (longNumber(order.get("orderStatus")) != 0) throw new BusinessException(ErrorCode.CONFLICT, "订单当前不可取消");
		// 回调没到达时「待支付」只说明本地不知道，钱可能已经被通道收下。取消是不可逆的，所以先问通道：
		// 已经收下的钱必须落成已支付（人才端才有退款入口）；通道说没收款的预支付单还要在通道侧关掉，
		// 否则用户手上的预支付单仍然能付进来。这一问必须排在本地写状态之前——它在自己的事务里，
		// 本方法一旦先加锁，它就会等自己的锁。
		PendingPaymentConfirmation confirmation = paymentReconciliationService.closePendingPayments(orderNo);
		if (confirmation.collected()) throw new BusinessException(ErrorCode.CONFLICT, "订单已支付，不能取消");
		if (confirmation.state() == PendingPaymentState.UNCONFIRMED) {
			// 关单没有结论（通道答「处理中」或干脆没答上来）：这次「支付结果不确定」只属于它所属的
			// 那一次支付尝试，不该把整笔订单停摆成一个永久报错。取消的意图先被接受并记录，订单本身
			// 不动——后台每分钟重试关单，有关闭或已收款的结论时再落地（关掉了就取消，钱收下了就
			// 翻成已支付并告知用户）。页面按「取消处理中」渲染。
			return cancellationPending(orderNo, talentId, confirmation.paymentNo());
		}
		// 过了支付时限的订单在这里结束：条件写在 SQL 里，跨实例只有一个赢家。
		expireOrder(orderNo, talentId);
		order = find(orderNo, talentId);
		if (longNumber(order.get("orderStatus")) == 2) return view(order);
		// 走到这里，通道侧每笔尝试都已经有了结论（关掉、或明确没收过钱）。这里只剩下取消与确认之间
		// 新起的支付尝试：它们没经过通道确认，所以不能记成已关闭，而是留成待对账状态——预支付单可能还
		// 能被支付，对账任务会继续盯着并留下问题记录。
		jdbcTemplate.update("""
			update payment_transaction
			set status = 'CANCEL_UNCONFIRMED', closed_at = coalesce(closed_at, now()),
			    provider_response_code = 'LOCAL_CANCELLED', provider_response_message = 'Cancelled by talent user'
			where order_no = ? and talent_id = ?
			  and status in ('CREATED', 'PREPAY_CREATED', 'CREATE_UNKNOWN', 'CREATE_FAILED')
			""", orderNo, talentId);
		Integer succeededPaymentCount = jdbcTemplate.queryForObject("""
			select count(*) from payment_transaction
			where order_no = ? and talent_id = ? and status = 'SUCCEEDED'
			""", Integer.class, orderNo, talentId);
		if (succeededPaymentCount != null && succeededPaymentCount > 0) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单已支付，不能取消");
		}
		int updated = jdbcTemplate.update("""
			update benefit_order set order_status = 2
			where order_no = ? and talent_id = ? and deleted = 0 and order_status = 0
			""", orderNo, talentId);
		if (updated == 0) throw new BusinessException(ErrorCode.CONFLICT, "订单当前不可取消");
		return view(find(orderNo, talentId));
	}

	/** 取消已受理、等通道关单结论：订单仍是待支付，人才端按「取消处理中」渲染。 */
	private Map<String, Object> cancellationPending(String orderNo, Long talentId, String paymentNo) {
		Map<String, Object> result = view(find(orderNo, talentId));
		result.put("cancellationPending", true);
		result.put("paymentNo", paymentNo);
		result.put("paymentStatus", "PAYMENT_PROCESSING");
		return result;
	}

	/** 单个订单的到期结束：条件写在 SQL 里，跨实例只有一个赢家。未定局的支付尝试照上面同样的理由排除。 */
	private void expireOrder(String orderNo, Long talentId) {
		jdbcTemplate.update("""
			update benefit_order o
			set order_status = 2
			where o.order_no = ? and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
			  and o.expires_at is not null and o.expires_at <= current_timestamp
			  and not exists (
				select 1 from payment_transaction p
				where p.order_id = o.id and p.status in ('CREATED', 'PREPAY_CREATED', 'CREATE_UNKNOWN', 'CLOSING', 'CANCEL_UNCONFIRMED')
			  )
			""", orderNo, talentId);
	}
	private long longNumber(Object value) { return value instanceof Number number ? number.longValue() : 0; }
	private Long longValue(Object value) { return value instanceof Number number ? number.longValue() : null; }
	private BigDecimal yuan(Object cent) { return BigDecimal.valueOf(longNumber(cent), 2); }
	private String talentDiscountText(Map<String, Object> order) {
		long originalAmount = longNumber(order.get("orderAmount"));
		long basePayAmount = longNumber(order.get("basePayAmount"));
		if (originalAmount <= 0 || basePayAmount >= originalAmount) return "无折扣";
		if (basePayAmount <= 0) return "免费";
		return BigDecimal.valueOf(basePayAmount).multiply(BigDecimal.TEN)
				.divide(BigDecimal.valueOf(originalAmount), 1, java.math.RoundingMode.HALF_UP)
				.stripTrailingZeros().toPlainString() + "折";
	}
}