package com.talentcard.server.business.merchant;

import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Duration;
import java.math.BigDecimal;
import java.util.ArrayList;
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.MerchantPrincipal;
import com.talentcard.server.business.portal.ProductPriceCalculator;
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 MerchantWorkService {
	private static final long REVOCATION_HOURS = 3;

	private final JdbcTemplate jdbcTemplate;
	private final ManualConsumptionService manualConsumptionService;

	public MerchantWorkService(JdbcTemplate jdbcTemplate, ManualConsumptionService manualConsumptionService) {
		this.jdbcTemplate = jdbcTemplate;
		this.manualConsumptionService = manualConsumptionService;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> orders(Long merchantId, Long storeId, int page, int size, MerchantOrderQuery query) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		OrderFilters filters = filters(query);
		String conditions = " where o.deleted = 0 and o.merchant_id = ? and (o.store_id is null or o.store_id = ?)" + filters.conditions();
		List<Object> arguments = new ArrayList<>();
		arguments.add(merchantId);
		arguments.add(storeId);
		arguments.addAll(filters.arguments());

		String recordSql = """
				select o.id, o.order_no, tu.name as talent_name, tu.mobile as talent_mobile, tu.gender, tc.card_level,
				       b.benefit_name, o.product_name_snapshot, o.order_type, o.order_amount, o.subsidy_amount, o.pay_amount,
				       o.order_status, o.verify_status, o.created_at, verified.verify_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 (select order_id, max(verify_time) verify_time from verify_record where status = 1 group by order_id) verified on verified.order_id = o.id
				""" + conditions + " order by o.id desc limit ? offset ?";
		List<Object> recordArguments = new ArrayList<>(arguments);
		recordArguments.add(safeSize);
		recordArguments.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query(recordSql, recordArguments.toArray()).stream().map(this::toListItem).toList();
		Long total = jdbcTemplate.queryForObject("select count(*) from benefit_order o" + conditions, Long.class, arguments.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	public PageResult<Map<String, Object>> orders(Long merchantId, Long storeId, int page, int size) {
		return orders(merchantId, storeId, page, size, new MerchantOrderQuery(null, null, null, null));
	}

	@Transactional(readOnly = true)
	public Map<String, Object> order(String orderNo, Long merchantId, Long storeId) {
		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, tu.gender,
				       o.card_id, tc.card_no, tc.card_level, o.benefit_id, b.benefit_name,
				       o.merchant_id, m.merchant_name, o.product_id, o.product_name_snapshot, p.rights_config,
				       o.order_type, o.order_amount, o.base_pay_amount, o.coupon_amount,
				       o.subsidy_amount, o.pay_amount, o.order_status, o.verify_status, o.created_at,
				       vr.verify_time, vr.status as verify_record_status
				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 m on m.id = o.merchant_id
				left join merchant_product p on p.id = o.product_id and p.deleted = 0
				left join verify_record vr on vr.order_id = o.id and vr.status = 1
				where o.deleted = 0 and o.order_no = ? and o.merchant_id = ? and (o.store_id is null or o.store_id = ?)
				""", orderNo, merchantId, storeId);
		if (rows.isEmpty()) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "订单不存在");
		}
		return enrichOrder(rows.get(0));
	}

	@Transactional
	public Map<String, Object> verify(String orderNo, MerchantPrincipal principal, VerifyRequest request, String idempotencyKey) {
		if (idempotencyKey == null || idempotencyKey.isBlank()) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "缺少幂等键");
		}
		Map<String, Object> order = order(orderNo, principal.merchantId(), principal.storeId());
		List<Map<String, Object>> repeated = query("""
				select order_id from verify_record where idempotency_key = ? and merchant_id = ? limit 1
				""", idempotencyKey.trim(), principal.merchantId());
		if (!repeated.isEmpty()) {
			if (!String.valueOf(order.get("id")).equals(String.valueOf(repeated.get(0).get("orderId")))) {
				throw new BusinessException(ErrorCode.CONFLICT, "幂等键已用于其他订单");
			}
			return order(orderNo, principal.merchantId(), principal.storeId());
		}
		if (((Number) order.get("orderStatus")).intValue() != 1) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单未支付，不能核销");
		}
		if (((Number) order.get("verifyStatus")).intValue() == 1) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单已核销");
		}

		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, idempotency_key)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				""",
				verifyNo,
				order.get("id"),
				order.get("talentId"),
				order.get("cardId"),
				order.get("benefitId"),
				order.get("merchantId"),
					principal.operatorId(),
				request.verifyType(),
				order.get("payAmount"),
				order.get("subsidyAmount"),
				request.remark(), idempotencyKey.trim());
		jdbcTemplate.update("update benefit_order set verify_status = 1 where order_no = ? and verify_status = 0", orderNo);
		return order(orderNo, principal.merchantId(), principal.storeId());
	}

	@Transactional
	public Map<String, Object> revokeVerification(MerchantPrincipal principal, MerchantRevokeVerificationRequest request, String idempotencyKey) {
		if (idempotencyKey == null || idempotencyKey.isBlank()) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "缺少幂等键");
		}
		String normalizedIdempotencyKey = idempotencyKey.trim();
		Long orderId;
		try {
			orderId = Long.valueOf(request.orderId().trim());
		} catch (NumberFormatException exception) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "订单编号无效");
		}
		List<Map<String, Object>> keyOwner = query("""
				select order_id from verify_record where revoke_idempotency_key = ? limit 1
				""", normalizedIdempotencyKey);
		if (!keyOwner.isEmpty() && number(keyOwner.get(0).get("orderId")) != orderId) {
			throw new BusinessException(ErrorCode.CONFLICT, "幂等键已用于其他订单");
		}
		List<Map<String, Object>> repeated = query("""
				select vr.revoked_at
				from verify_record vr
				join benefit_order o on o.id = vr.order_id
				where vr.revoke_idempotency_key = ? and o.id = ? and o.deleted = 0
				  and o.merchant_id = ? and (o.store_id is null or o.store_id = ?)
				limit 1
				""", normalizedIdempotencyKey, orderId, principal.merchantId(), principal.storeId());
		if (!repeated.isEmpty()) {
			return revokeResult(orderId, time(repeated.get(0).get("revokedAt")));
		}
		List<Map<String, Object>> orders = query("""
				select o.id, o.order_no, o.order_type, o.order_status, o.verify_status,
				       o.merchant_id, o.store_id, vr.id verify_record_id, vr.verify_time
				from benefit_order o
				left join verify_record vr on vr.order_id = o.id and vr.status = 1
				where o.id = ? and o.deleted = 0 and o.merchant_id = ? and (o.store_id is null or o.store_id = ?)
				for update
				""", orderId, principal.merchantId(), principal.storeId());
		if (orders.isEmpty()) throw new BusinessException(ErrorCode.NOT_FOUND, "订单不存在");
		Map<String, Object> order = orders.get(0);
		if (!"GROUP_BUY".equals(String.valueOf(order.get("orderType")))) {
			throw new BusinessException(ErrorCode.CONFLICT, "当前业务类型不支持撤销核销");
		}
		if (number(order.get("verifyStatus")) == null || number(order.get("verifyStatus")) != 1
				|| number(order.get("verifyRecordId")) == null) {
			throw new BusinessException(ErrorCode.CONFLICT, "订单当前不可撤销核销");
		}
		LocalDateTime verifyTime = time(order.get("verifyTime"));
		if (verifyTime == null || Duration.between(verifyTime, LocalDateTime.now()).toHours() >= REVOCATION_HOURS) {
			throw new BusinessException(ErrorCode.CONFLICT, "已过可撤销时间");
		}
		String reason = request.reason() == null || request.reason().isBlank() ? "商户撤销核销" : request.reason().trim();
		int updated = jdbcTemplate.update("""
				update verify_record set status = 2, revoked_at = now(), revoked_by = ?, revoke_reason = ?, revoke_idempotency_key = ?
				where id = ? and status = 1
				""", principal.operatorId(), reason, normalizedIdempotencyKey, number(order.get("verifyRecordId")));
		if (updated == 0) throw new BusinessException(ErrorCode.CONFLICT, "核销已被处理，请刷新后重试");
		jdbcTemplate.update("update benefit_order set verify_status = 0 where id = ? and verify_status = 1", orderId);
		return revokeResult(orderId, LocalDateTime.now());
	}

	private Map<String, Object> revokeResult(Long orderId, LocalDateTime revokedAt) {
		Map<String, Object> result = new LinkedHashMap<>();
		result.put("orderId", String.valueOf(orderId));
		result.put("orderStatus", "pending_verification");
		result.put("orderStatusText", "待核销");
		result.put("revokedAt", (revokedAt == null ? LocalDateTime.now() : revokedAt).toString());
		return result;
	}

	private Map<String, Object> enrichOrder(Map<String, Object> order) {
		Map<String, Object> result = new LinkedHashMap<>(order);
		String orderType = String.valueOf(order.get("orderType"));
		String category = "GROUP_BUY".equals(orderType) ? "group" : ("STORE_CONSUME".equals(orderType) ? "store" : "stay");
		Long orderStatus = number(order.get("orderStatus"));
		Long verifyStatus = number(order.get("verifyStatus"));
		String status = orderStatus != null && orderStatus == 0 ? "pending_payment" : (verifyStatus != null && verifyStatus == 1 ? "completed" : "pending_verification");
		result.put("category", category);
		result.put("status", status);
		result.put("statusText", "pending_payment".equals(status) ? "待支付" : ("completed".equals(status) ? "已完成" : "待核销"));
		result.put("customerName", displayName(order.get("talentName"), number(order.get("gender"))));
		result.put("customerMobile", maskMobile(String.valueOf(order.get("talentMobile"))));
		result.put("title", orderTitle(order, category));
		result.put("benefitName", currentBenefitText(order, category));
		long orderAmount = number(order.get("orderAmount")) == null ? 0 : number(order.get("orderAmount"));
		long basePayAmount = number(order.get("basePayAmount")) == null ? orderAmount : number(order.get("basePayAmount"));
		long couponAmount = number(order.get("couponAmount")) == null ? 0 : number(order.get("couponAmount"));
		result.put("talentDiscountAmount", Math.max(0, orderAmount - basePayAmount));
		result.put("couponAmount", Math.max(0, couponAmount));
		LocalDateTime verifyTime = time(order.get("verifyTime"));
		boolean canRevoke = "GROUP_BUY".equals(orderType) && verifyTime != null && Duration.between(verifyTime, LocalDateTime.now()).toHours() < REVOCATION_HOURS && verifyStatus != null && verifyStatus == 1;
		result.put("canRevoke", canRevoke);
		result.put("revokeDeadline", verifyTime == null ? null : verifyTime.plusHours(REVOCATION_HOURS).toString());
		result.put("revokeReason", canRevoke ? "" : ("GROUP_BUY".equals(orderType) ? "已过可撤销时间" : "当前业务类型不支持撤销核销"));
		return result;
	}

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

	private OrderFilters filters(MerchantOrderQuery query) {
		MerchantOrderQuery value = query == null ? new MerchantOrderQuery(null, null, null, null) : query;
		StringBuilder conditions = new StringBuilder();
		List<Object> arguments = new ArrayList<>();
		String category = normalize(value.category());
		if (!category.isEmpty()) {
			String orderType = switch (category) {
				case "group" -> "GROUP_BUY";
				case "store" -> "STORE_CONSUME";
				case "stay" -> "FREE_STAY";
				default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "订单业务类型无效");
			};
			conditions.append(" and o.order_type = ?");
			arguments.add(orderType);
		}
		String status = normalize(value.status());
		if (!status.isEmpty() && !"all".equals(status)) {
			switch (status) {
				case "pending_verification" -> conditions.append(" and o.order_status = 1 and o.verify_status = 0");
				case "pending_payment" -> conditions.append(" and o.order_status = 0");
				case "completed" -> conditions.append(" and o.order_status = 1 and o.verify_status = 1");
				default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "订单状态无效");
			}
		}
		LocalDate startDate = date(value.startDate(), "开始日期");
		LocalDate endDate = date(value.endDate(), "结束日期");
		if (startDate != null && endDate != null && startDate.isAfter(endDate)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "开始日期不能晚于结束日期");
		}
		if (startDate != null) {
			conditions.append(" and o.created_at >= ?");
			arguments.add(Timestamp.valueOf(startDate.atStartOfDay()));
		}
		if (endDate != null) {
			conditions.append(" and o.created_at < ?");
			arguments.add(Timestamp.valueOf(endDate.plusDays(1).atStartOfDay()));
		}
		return new OrderFilters(conditions.toString(), arguments);
	}

	private Map<String, Object> toListItem(Map<String, Object> order) {
		Map<String, Object> result = new LinkedHashMap<>(order);
		String category = category(String.valueOf(order.get("orderType")));
		String status = status(number(order.get("orderStatus")), number(order.get("verifyStatus")));
		result.put("category", category);
		result.put("status", status);
		result.put("statusText", statusText(status));
		result.put("customerName", displayName(order.get("talentName"), number(order.get("gender"))));
		result.put("customerMobile", maskMobile(String.valueOf(order.get("talentMobile"))));
		result.put("title", orderTitle(order, category));
		return result;
	}

	private String orderTitle(Map<String, Object> order, String category) {
		String snapshot = text(order.get("productNameSnapshot"));
		if (!snapshot.isBlank()) return snapshot;
		String benefit = text(order.get("benefitName"));
		return benefit.isBlank() ? categoryTitle(category) : benefit;
	}

	private String currentBenefitText(Map<String, Object> order, String category) {
		String cardLevel = text(order.get("cardLevel"));
		if ("store".equals(category)) {
			Long merchantId = number(order.get("merchantId"));
			return merchantId == null ? "以当前商户到店消费规则为准" : manualConsumptionService.benefitTextForMerchant(merchantId, cardLevel);
		}
		if ("group".equals(category)) {
			ProductPriceCalculator.ProductRight right = ProductPriceCalculator.productRight(order.get("rightsConfig"), cardLevel);
			if (right != null) return productBenefitText(right);
		}
		String configured = text(order.get("benefitName"));
		return configured.isBlank() ? "以当前订单权益规则为准" : configured;
	}

	private String productBenefitText(ProductPriceCalculator.ProductRight right) {
		String discountText = discountText(right.discount());
		if (!right.free()) return discountText.isBlank() ? "以商品配置为准" : "按市价" + discountText;
		String freeText = right.limitCount() > 0 ? "免费" + right.limitCount() + "次/" + cycleText(right.resetCycle()) : "免费";
		return discountText.isBlank() ? freeText : freeText + "，超出次数按市价" + discountText;
	}

	private String discountText(BigDecimal discount) {
		if (discount == null || discount.signum() <= 0) return "";
		return discount.stripTrailingZeros().toPlainString() + "折";
	}

	private String cycleText(String cycle) {
		String value = normalize(cycle).toUpperCase();
		return switch (value) {
			case "DAY", "DAILY", "每天" -> "天";
			case "WEEK", "WEEKLY", "每周" -> "周";
			case "MONTH", "MONTHLY", "每月" -> "月";
			default -> "年";
		};
	}

	private String category(String orderType) {
		if ("GROUP_BUY".equals(orderType)) return "group";
		if ("STORE_CONSUME".equals(orderType)) return "store";
		if ("FREE_STAY".equals(orderType)) return "stay";
		return "group";
	}

	private String status(Long orderStatus, Long verifyStatus) {
		if (orderStatus != null && orderStatus == 0) return "pending_payment";
		if (verifyStatus != null && verifyStatus == 1) return "completed";
		return "pending_verification";
	}

	private String statusText(String status) {
		if ("pending_payment".equals(status)) return "待支付";
		if ("completed".equals(status)) return "已完成";
		return "待核销";
	}

	private String categoryTitle(String category) {
		if ("store".equals(category)) return "到店消费订单";
		if ("stay".equals(category)) return "免费入住";
		return "团购订单";
	}

	private String maskName(String value) {
		if (value == null || value.isBlank() || "null".equals(value)) return "";
		return value.length() == 1 ? value + "*" : value.substring(0, 1) + "*".repeat(value.length() - 1);
	}

	private String displayName(Object rawName, Long gender) {
		String name = text(rawName);
		if (name.isBlank()) return "";
		if (gender != null && gender == 1) return name.substring(0, 1) + "先生";
		if (gender != null && gender == 2) return name.substring(0, 1) + "女士";
		return maskName(name);
	}

	private String text(Object value) {
		return value == null || "null".equals(String.valueOf(value)) ? "" : String.valueOf(value).trim();
	}

	private String maskMobile(String value) {
		if (value == null || value.length() < 7 || "null".equals(value)) return "";
		return value.substring(0, 3) + "****" + value.substring(value.length() - 4);
	}

	private String normalize(String value) {
		return value == null ? "" : value.trim();
	}

	private LocalDate date(String value, String field) {
		if (value == null || value.isBlank()) return null;
		try {
			return LocalDate.parse(value);
		} catch (Exception exception) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, field + "格式无效");
		}
	}

	private Long number(Object value) {
		return value instanceof Number number ? number.longValue() : null;
	}

	private LocalDateTime time(Object value) {
		if (value instanceof LocalDateTime dateTime) return dateTime;
		if (value instanceof Timestamp timestamp) return timestamp.toLocalDateTime();
		return null;
	}

	private record OrderFilters(String conditions, List<Object> arguments) {
	}
}