package com.talentcard.server.business.order;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
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.MoneyUtils;
import com.talentcard.server.business.merchant.MerchantPaymentReadinessService;
import com.talentcard.server.business.payment.HuifuRefundService;
import com.talentcard.server.business.PageResult;
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 OrderService {

	private static final String ORDER_CARD_LEVEL_SQL =
			"coalesce(nullif(o.card_level_snapshot, ''), tc.card_level, tu.talent_level)";

	private final JdbcTemplate jdbcTemplate;
	private final HuifuRefundService refundService;
	private final MerchantPaymentReadinessService merchantPaymentReadinessService;

	public OrderService(JdbcTemplate jdbcTemplate, HuifuRefundService refundService, MerchantPaymentReadinessService merchantPaymentReadinessService) {
		this.jdbcTemplate = jdbcTemplate;
		this.refundService = refundService;
		this.merchantPaymentReadinessService = merchantPaymentReadinessService;
	}

	@Transactional
	public Map<String, Object> create(OrderCreateRequest request) {
		String orderNo = BusinessIdGenerator.next("O");
		long orderAmount = MoneyUtils.yuanToCent(request.orderAmount());
		long subsidyAmount = MoneyUtils.yuanToCent(request.subsidyAmount());
		long payAmount = MoneyUtils.yuanToCent(request.payAmount());
		LocalDateTime expiresAt = payAmount == 0 ? null : LocalDateTime.now().plusMinutes(15);
		// 需要支付的订单同样在创建时固定收款子商户，支付请求不再推断收款方。
		MerchantPaymentReadinessService.CollectingMerchant collectingMerchant = payAmount > 0
				? merchantPaymentReadinessService.requireCollectingMerchant(request.merchantId(), request.storeId())
				: null;
		String cardLevel = jdbcTemplate.query("select card_level from talent_card where id = ? and deleted = 0", rs -> rs.next() ? rs.getString(1) : null, request.cardId());
		jdbcTemplate.update("""
				insert into benefit_order(order_no, talent_id, card_id, card_level_snapshot, benefit_id, merchant_id, store_id,
				                          order_type, order_amount, subsidy_amount, base_pay_amount, pay_amount, expires_at,
				                          collect_onboarding_id, collect_store_id, collect_huifu_id, order_status, verify_status)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)
				""",
				orderNo,
				request.talentId(),
				request.cardId(),
				cardLevel,
				request.benefitId(),
				request.merchantId(),
				request.storeId(),
				request.orderType(),
				orderAmount,
				subsidyAmount,
				payAmount,
				payAmount,
				expiresAt,
				MerchantPaymentReadinessService.CollectingMerchant.applicationIdOf(collectingMerchant),
				MerchantPaymentReadinessService.CollectingMerchant.storeIdOf(collectingMerchant),
				MerchantPaymentReadinessService.CollectingMerchant.huifuIdOf(collectingMerchant),
				payAmount == 0 ? 1 : 0);
		return detail(orderNo);
	}

	@Transactional
	public Map<String, Object> pay(String orderNo) {
		int updated = jdbcTemplate.update("""
				update benefit_order set order_status = 1
				where deleted = 0 and order_no = ? and order_status = 0
				""", orderNo);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单不存在或不可支付");
		}
		return detail(orderNo);
	}

	@Transactional
	public Map<String, Object> verify(String orderNo, OrderVerifyRequest request) {
		Map<String, Object> order = detail(orderNo);
		if (((Number) order.get("orderStatus")).intValue() != 1) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单未支付，不能核销");
		}
		if (((Number) order.get("verifyStatus")).intValue() == 1) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单已核销");
		}
		Long orderMerchantId = order.get("merchantId") == null ? null : ((Number) order.get("merchantId")).longValue();
		if (request.merchantId() != null && !request.merchantId().equals(orderMerchantId)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "订单不属于该商户");
		}
		String verifyNo = BusinessIdGenerator.next("V");
		jdbcTemplate.update("""
				insert into verify_record(verify_no, order_id, talent_id, card_id, benefit_id, merchant_id,
				                          staff_id, verify_type, verify_amount, subsidy_amount, remark)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				""",
				verifyNo,
				order.get("id"),
				order.get("talentId"),
				order.get("cardId"),
				order.get("benefitId"),
				orderMerchantId,
				request.staffId(),
				normalizeVerifyType(request.verifyType(), (String) order.get("orderType")),
				order.get("payAmount"),
				order.get("subsidyAmount"),
				request.remark());
		jdbcTemplate.update("update benefit_order set verify_status = 1 where order_no = ?", orderNo);
		return detail(orderNo);
	}

	public Map<String, Object> refund(String orderNo, OrderRefundRequest request) {
		return refundService.request(orderNo, request);
	}

	@Transactional(readOnly = true)
	public Map<String, Object> detail(String orderNo) {
		List<Map<String, Object>> rows = query("""
				select o.id, o.order_no, o.talent_id, tu.name as talent_name, tu.mobile as talent_mobile,
				       o.card_id, tc.card_no, %s as talent_level, o.benefit_id, b.benefit_name,
				       o.product_id, o.product_name_snapshot, o.product_cover_url_snapshot,
				       p.product_name, p.cover_url as product_cover_url,
				       o.merchant_id, m.merchant_name, m.merchant_type, m.contact_mobile,
				       m.cover_url as merchant_cover_url, o.store_id, o.order_type,
				       o.order_amount, o.subsidy_amount, o.base_pay_amount, o.pay_amount, o.coupon_id, o.coupon_amount,
				       mc.coupon_name, o.order_status,
				       o.verify_status, o.created_at, o.updated_at
				from benefit_order o
				left join talent_user tu on tu.id = o.talent_id
				left join talent_card tc on tc.id = o.card_id
				left join benefit b on b.id = o.benefit_id
				left join merchant_product p on p.id = o.product_id and p.deleted = 0
				left join merchant_coupon mc on mc.id = o.coupon_id and mc.deleted = 0
				left join merchant m on m.id = o.merchant_id
				where o.deleted = 0 and o.order_no = ?
				""".formatted(ORDER_CARD_LEVEL_SQL), orderNo);
		if (rows.isEmpty()) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "订单不存在");
		}
		Map<String, Object> detail = rows.get(0);
		List<Map<String, Object>> verificationRows = query("""
				select verify_time
				from verify_record
				where order_id = ?
				order by id desc limit 1
				""", detail.get("id"));
		Object verifyTime = verificationRows.isEmpty() ? null : verificationRows.get(0).get("verifyTime");
		// 到店消费在支付成功后即自动核销，历史数据未必有人工核销记录；此时以成功支付时间作为核销时间。
		if (verifyTime == null
				&& "STORE_CONSUME".equalsIgnoreCase(String.valueOf(detail.get("orderType")))
				&& detail.get("verifyStatus") instanceof Number verifyStatus
				&& verifyStatus.intValue() == 1) {
			List<Map<String, Object>> paidTransactions = query("""
					select paid_at as verify_time
					from payment_transaction
					where order_no = ? and status = 'SUCCEEDED' and paid_at is not null
					order by id desc limit 1
					""", orderNo);
			verifyTime = paidTransactions.isEmpty() ? null : paidTransactions.get(0).get("verifyTime");
		}
		detail.put("verifyTime", verifyTime);
		Object itemId = detail.get("productId") == null ? detail.get("benefitId") : detail.get("productId");
		Object itemName = firstNonNull(detail.get("productNameSnapshot"), detail.get("productName"), detail.get("benefitName"));
		Object itemCover = firstNonNull(detail.get("productCoverUrlSnapshot"), detail.get("productCoverUrl"), detail.get("merchantCoverUrl"));
		detail.put("items", List.of(Map.of(
				"id", itemId == null ? "-" : String.valueOf(itemId),
				"name", itemName == null ? "-" : itemName,
				"coverUrl", itemCover == null ? "" : itemCover,
				"amount", detail.get("orderAmount") == null ? 0 : detail.get("orderAmount")
		)));
		detail.put("payments", query("""
				select payment_no, provider, amount, status, provider_transaction_no, paid_at, created_at
				from payment_transaction
				where order_no = ?
				order by id desc
				""", orderNo));
		detail.put("refunds", query("""
				select refund_no, provider, paid_amount, refund_amount, refundable_amount,
				       refund_status, reason, provider_refund_no, refund_time, created_at
				from order_refund
				where deleted = 0 and order_no = ?
				order by id desc
				""", orderNo));
		return detail;
	}

	private Object firstNonNull(Object... values) {
		for (Object value : values) if (value != null && !String.valueOf(value).isBlank()) return value;
		return null;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> refunds(
			String orderType,
			String talentKeyword,
			String merchantName,
			String cardLevel,
			Integer hasSubsidy,
			Integer status,
			String orderStart,
			String orderEnd,
			int page,
			int size
	) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder where = new StringBuilder(" where r.deleted = 0");
		List<Object> args = new ArrayList<>();
		appendOrderTypeFilter(where, args, "r", orderType);
		if (talentKeyword != null && !talentKeyword.isBlank()) {
			where.append(" and (tu.name like ? or tu.mobile like ? or tc.card_no like ?)");
			String like = "%" + talentKeyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (merchantName != null && !merchantName.isBlank()) {
			where.append(" and m.merchant_name like ?");
			args.add("%" + merchantName.trim() + "%");
		}
		appendCardLevelFilter(where, args, cardLevel);
		if (hasSubsidy != null) {
			where.append(hasSubsidy == 1 ? " and o.subsidy_amount > 0" : " and o.subsidy_amount = 0");
		}
		if (status != null) {
			where.append(" and r.refund_status = ?");
			args.add(status);
		}
		if (orderStart != null && !orderStart.isBlank()) {
			where.append(" and date(r.refund_time) >= ?");
			args.add(orderStart);
		}
		if (orderEnd != null && !orderEnd.isBlank()) {
			where.append(" and date(r.refund_time) <= ?");
			args.add(orderEnd);
		}

		List<Object> pageArgs = new ArrayList<>(args);
		pageArgs.add(safeSize);
		pageArgs.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query("""
				select r.id, r.refund_no, r.order_no, r.order_type, r.paid_amount, r.refund_amount,
				       r.refundable_amount, r.refund_status, r.reason, r.operator_name, r.refund_time,
				       tu.name as talent_name, tu.mobile as talent_mobile,
				       %s as talent_level, tc.card_no,
				       m.merchant_name, m.merchant_type,
				       coalesce(nullif(o.product_name_snapshot, ''), nullif(p.product_name, ''), b.benefit_name) as benefit_name,
				       o.order_amount, o.subsidy_amount, o.pay_amount, o.created_at as order_time
				from order_refund r
				left join benefit_order o on o.id = r.order_id
				left join talent_user tu on tu.id = r.talent_id
				left join talent_card tc on tc.id = r.card_id
				left join benefit b on b.id = r.benefit_id
				left join merchant_product p on p.id = o.product_id and p.deleted = 0
				left join merchant m on m.id = r.merchant_id
				""".formatted(ORDER_CARD_LEVEL_SQL) + where + " order by r.created_at desc, r.id desc limit ? offset ?", pageArgs.toArray());
		Long total = jdbcTemplate.queryForObject("""
				select count(*)
				from order_refund r
				left join benefit_order o on o.id = r.order_id
				left join talent_user tu on tu.id = r.talent_id
				left join talent_card tc on tc.id = r.card_id
				left join merchant m on m.id = r.merchant_id
				""" + where, Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> verifications(
			String verifyType,
			String talentKeyword,
			String merchantName,
			String cardLevel,
			String status,
			String orderStart,
			String orderEnd,
			int page,
			int size
	) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder where = new StringBuilder(" where 1 = 1");
		List<Object> args = new ArrayList<>();
		if (verifyType != null && !verifyType.isBlank()) {
			if ("HOTEL_FREE".equalsIgnoreCase(verifyType)) {
				where.append(" and r.verify_type in ('HOTEL_FREE', 'HOTEL', '酒店免费', '酒店免费核销')");
			} else {
				where.append(" and r.verify_type = ?");
				args.add(verifyType);
			}
		}
		if (talentKeyword != null && !talentKeyword.isBlank()) {
			where.append(" and (tu.name like ? or tu.mobile like ? or tc.card_no like ?)");
			String like = "%" + talentKeyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (merchantName != null && !merchantName.isBlank()) {
			where.append(" and m.merchant_name like ?");
			args.add("%" + merchantName.trim() + "%");
		}
		appendCardLevelFilter(where, args, cardLevel);
		if ("未核销".equals(status)) {
			return new PageResult<>(List.of(), safePage, safeSize, 0);
		}
		if (orderStart != null && !orderStart.isBlank()) {
			where.append(" and date(r.verify_time) >= ?");
			args.add(orderStart);
		}
		if (orderEnd != null && !orderEnd.isBlank()) {
			where.append(" and date(r.verify_time) <= ?");
			args.add(orderEnd);
		}

		List<Object> pageArgs = new ArrayList<>(args);
		pageArgs.add(safeSize);
		pageArgs.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query("""
				select r.id, r.verify_no, r.verify_type, r.verify_amount, r.subsidy_amount, r.verify_time,
				       r.remark, o.order_no, o.order_type,
				       tu.name as talent_name, tu.mobile as talent_mobile,
				       %s as talent_level, tc.card_no,
				       m.merchant_name, m.merchant_type, b.benefit_name,
				       '已核销' as status
				from verify_record r
				left join benefit_order o on o.id = r.order_id
				left join talent_user tu on tu.id = r.talent_id
				left join talent_card tc on tc.id = r.card_id
				left join benefit b on b.id = r.benefit_id
				left join merchant m on m.id = r.merchant_id
				""".formatted(ORDER_CARD_LEVEL_SQL) + where + " order by r.verify_time desc, r.id desc limit ? offset ?", pageArgs.toArray());
		Long total = jdbcTemplate.queryForObject("""
				select count(*)
				from verify_record r
				left join benefit_order o on o.id = r.order_id
				left join talent_user tu on tu.id = r.talent_id
				left join talent_card tc on tc.id = r.card_id
				left join merchant m on m.id = r.merchant_id
				""" + where, Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> page(
			Long talentId,
			Long merchantId,
			String orderType,
			String talentKeyword,
			String merchantName,
			String cardLevel,
			Integer hasSubsidy,
			Integer familyOrder,
			Integer status,
			String orderStart,
			String orderEnd,
			int page,
			int size
	) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder where = new StringBuilder(" where o.deleted = 0");
		List<Object> args = new ArrayList<>();
		if (talentId != null) {
			where.append(" and o.talent_id = ?");
			args.add(talentId);
		}
		if (merchantId != null) {
			where.append(" and o.merchant_id = ?");
			args.add(merchantId);
		}
		if (orderType != null && !orderType.isBlank()) {
			if ("GROUP".equalsIgnoreCase(orderType)) {
				where.append(" and o.order_type in ('GROUP', 'GROUP_BUY', '团购', '团购订单')");
			} else if ("STORE".equalsIgnoreCase(orderType)) {
				where.append(" and o.order_type in ('STORE', 'STORE_CONSUME', '到店消费', '消费')");
			} else {
				where.append(" and o.order_type = ?");
				args.add(orderType);
			}
		}
		if (talentKeyword != null && !talentKeyword.isBlank()) {
			where.append(" and (tu.name like ? or tu.mobile like ? or tc.card_no like ?)");
			String like = "%" + talentKeyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (merchantName != null && !merchantName.isBlank()) {
			where.append(" and m.merchant_name like ?");
			args.add("%" + merchantName.trim() + "%");
		}
		appendCardLevelFilter(where, args, cardLevel);
		if (hasSubsidy != null) {
			where.append(hasSubsidy == 1 ? " and o.subsidy_amount > 0" : " and o.subsidy_amount = 0");
		}
		if (familyOrder != null) {
			where.append(familyOrder == 1 ? " and tc.card_type <> 'PERSONAL'" : " and (tc.card_type = 'PERSONAL' or tc.card_type is null)");
		}
		if (status != null) {
			where.append(" and o.order_status = ?");
			args.add(status);
		}
		if (orderStart != null && !orderStart.isBlank()) {
			where.append(" and date(o.created_at) >= ?");
			args.add(orderStart);
		}
		if (orderEnd != null && !orderEnd.isBlank()) {
			where.append(" and date(o.created_at) <= ?");
			args.add(orderEnd);
		}

		List<Object> pageArgs = new ArrayList<>(args);
		pageArgs.add(safeSize);
		pageArgs.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query("""
				select o.id, o.order_no, o.talent_id, tu.name as talent_name, tu.mobile as talent_mobile,
				       %s as talent_level,
				       o.card_id, tc.card_no, tc.card_type,
				       o.merchant_id, m.merchant_name, m.merchant_type,
				       o.benefit_id, b.benefit_name,
				       coalesce(nullif(o.product_name_snapshot, ''), p.product_name, b.benefit_name) as product_name,
				       o.order_type,
				       o.order_amount, o.subsidy_amount, o.pay_amount,
				       o.order_status, o.verify_status, o.created_at, o.updated_at as pay_time
				from benefit_order o
				left join talent_user tu on tu.id = o.talent_id
				left join talent_card tc on tc.id = o.card_id
				left join benefit b on b.id = o.benefit_id
				left join merchant_product p on p.id = o.product_id and p.deleted = 0
				left join merchant m on m.id = o.merchant_id
				""".formatted(ORDER_CARD_LEVEL_SQL) + where + " order by o.created_at desc, o.id desc limit ? offset ?", pageArgs.toArray());
		Long total = jdbcTemplate.queryForObject("""
				select count(*)
				from benefit_order o
				left join talent_user tu on tu.id = o.talent_id
				left join talent_card tc on tc.id = o.card_id
				left join merchant m on m.id = o.merchant_id
				""" + where, Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	private Map<String, Object> refundDetail(String refundNo) {
		List<Map<String, Object>> rows = query("""
				select r.id, r.refund_no, r.order_no, r.order_type, r.paid_amount, r.refund_amount,
				       r.refundable_amount, r.refund_status, r.reason, r.operator_name, r.refund_time
				from order_refund r
				where r.deleted = 0 and r.refund_no = ?
				""", refundNo);
		if (rows.isEmpty()) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "退款记录不存在");
		}
		return rows.get(0);
	}

	private void appendCardLevelFilter(StringBuilder where, List<Object> args, String cardLevel) {
		if (cardLevel == null || cardLevel.isBlank()) {
			return;
		}
		where.append(" and ").append(ORDER_CARD_LEVEL_SQL).append(" = ?");
		args.add(cardLevel.trim());
	}

	private void appendOrderTypeFilter(StringBuilder where, List<Object> args, String alias, String orderType) {
		if (orderType == null || orderType.isBlank()) {
			return;
		}
		if ("GROUP".equalsIgnoreCase(orderType)) {
			where.append(" and ").append(alias).append(".order_type in ('GROUP', 'GROUP_BUY', '团购', '团购订单')");
		} else if ("STORE".equalsIgnoreCase(orderType)) {
			where.append(" and ").append(alias).append(".order_type in ('STORE', 'STORE_CONSUME', '到店消费', '消费')");
		} else {
			where.append(" and ").append(alias).append(".order_type = ?");
			args.add(orderType);
		}
	}

	private String normalizeVerifyType(String requestType, String orderType) {
		if (requestType != null && !requestType.isBlank()) {
			return requestType;
		}
		if (orderType != null && ("STORE".equalsIgnoreCase(orderType) || "STORE_CONSUME".equalsIgnoreCase(orderType))) {
			return "STORE_CONSUME";
		}
		return "GROUP";
	}

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