package com.talentcard.server.business.portal;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.business.payment.HuifuRefundService;
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 UsageRecordPortalService {

	private static final List<String> SUPPORTED_STATUSES = List.of(
			"pending_payment", "pending_verification", "completed", "cancelled", "processing", "received"
	);

	private final JdbcTemplate jdbcTemplate;
	private final HuifuRefundService refundService;

	public UsageRecordPortalService(JdbcTemplate jdbcTemplate, HuifuRefundService refundService) {
		this.jdbcTemplate = jdbcTemplate;
		this.refundService = refundService;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> list(Long talentId, String status, int page, int size) {
		if (talentId == null || talentId <= 0) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "talentId不能为空");
		}
		if (page < 1 || size < 1 || size > 50) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "分页参数不正确");
		}
		if (status != null && !status.isBlank() && !SUPPORTED_STATUSES.contains(status)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的记录状态");
		}
		// 退款到不到最终结果只有通道知道，退款通知又会遇到和支付通知一样的问题（本地/内网环境收不到）。
		// 所以在读列表之前先把在途退款收敛一次——否则刚退完款的订单会一直显示成「待核销」。这一步必须排在
		// 本事务的第一条读语句之前，否则列表读到的是收敛前的旧快照。
		refundService.confirmPendingRefunds(talentId, HuifuRefundService.PENDING_CONFIRM_LIMIT);
		Integer talentCount = jdbcTemplate.queryForObject(
				"select count(*) from talent_user where id = ? and deleted = 0", Integer.class, talentId);
		if (talentCount == null || talentCount == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "人才用户不存在");
		}

		String sourceSql = sourceSql();
		String where = status == null || status.isBlank() ? "" : " where usage_record.status = ?";
		List<Object> arguments = sourceArguments(talentId);
		if (!where.isEmpty()) arguments.add(status);
		Long total = jdbcTemplate.queryForObject(
				"select count(*) from (" + sourceSql + ") usage_record" + where,
				Long.class,
				arguments.toArray());

		List<Object> pageArguments = new ArrayList<>(arguments);
		pageArguments.add(size);
		pageArguments.add((page - 1) * size);
		String pageSql = "select * from (" + sourceSql + ") usage_record" + where
				+ " order by usage_record.occurred_at desc, usage_record.record_id desc limit ? offset ?";
		List<Map<String, Object>> records = jdbcTemplate.queryForList(pageSql, pageArguments.toArray()).stream()
				.map(DatabaseRows::normalize)
				.map(this::enrichRecord)
				.toList();
		return new PageResult<>(records, page, size, total == null ? 0 : total);
	}

	private List<Object> sourceArguments(Long talentId) {
		return new ArrayList<>(List.of(talentId, talentId, talentId, talentId));
	}

	private String sourceSql() {
		return """
			select concat('order-', o.id) as record_id,
			       'ORDER' as record_type,
			       o.order_no as business_id,
			       o.id as target_detail_id,
			       o.merchant_id,
				       coalesce(nullif(m.merchant_name, ''), '人才权益服务') as merchant_name,
				       coalesce(nullif(m.merchant_type_code, ''), 'service') as merchant_type,
				       coalesce(nullif(b.benefit_name, ''), o.order_type, '人才权益订单') as detail_name,
				       o.created_at as occurred_at,
				       case
				         when o.order_status = 0 then 'pending_payment'
				         when o.order_status = 2 or o.refund_status <> 0 then 'cancelled'
				         when o.verify_status = 1 then 'completed'
				         else 'pending_verification'
				       end as status,
				       o.refund_status as refund_status,
				       o.pay_amount as amount_cent,
				       'ORDER_DETAIL' as target_detail_type
				from benefit_order o
				left join merchant m on m.id = o.merchant_id
				left join benefit b on b.id = o.benefit_id
				where o.deleted = 0 and o.talent_id = ?
				union all
			select concat('service-', s.id) as record_id,
			       'SERVICE_APPLICATION' as record_type,
			       s.application_no as business_id,
			       s.id as target_detail_id,
			       null as merchant_id,
				       coalesce(nullif(s.merchant_name, ''), nullif(s.hospital_name, ''), nullif(s.parking_lot, ''), '人才服务') as merchant_name,
				       case
				         when s.application_type like 'MEDICAL%' then 'medical'
				         when s.application_type like 'HOTEL%' then 'hotel'
				         when s.application_type like 'PARKING%' then 'parking'
				         when s.application_type like 'FUEL%' then 'fuel'
				         else 'service'
				       end as merchant_type,
				       coalesce(nullif(s.product_name, ''), nullif(s.package_name, ''), s.application_type) as detail_name,
				       s.submitted_at as occurred_at,
				       case
				         when s.status in ('已处理', '已完成') then 'completed'
				         when s.status in ('已驳回', '已取消') then 'cancelled'
				         else 'processing'
				       end as status,
				       null as amount_cent,
				       'SERVICE_APPLICATION_DETAIL' as target_detail_type,
				       null as refund_status
				from service_application s
				where s.deleted = 0
				  and exists (
				    select 1 from talent_card c
				    where c.talent_id = ? and c.deleted = 0 and c.card_type = 'PERSONAL' and c.card_no = s.card_no
				  )
				union all
				select concat('movie-', r.id) as record_id,
				       'MOVIE_TICKET' as record_type,
				       r.receive_no as business_id,
				       coalesce(cc.id, r.id) as target_detail_id,
				       null as merchant_id,
				       '万达影城' as merchant_name,
				       'cinema' as merchant_type,
				       r.package_name as detail_name,
				       r.received_at as occurred_at,
				       'received' as status,
				       null as amount_cent,
				       'MOVIE_TICKET_DETAIL' as target_detail_type,
				       null as refund_status
				from movie_ticket_record r
				left join cinema_coupon_claim cc on cc.claim_no = r.receive_no and cc.deleted = 0
				where r.deleted = 0
				  and exists (
				    select 1 from talent_card c
				    where c.talent_id = ? and c.deleted = 0 and c.card_type = 'PERSONAL' and c.card_no = r.card_no
				  )
				union all
				select concat('ticket-package-', cl.id) as record_id,
				       'MOVIE_TICKET' as record_type,
				       cl.claim_no as business_id,
				       cl.id as target_detail_id,
				       cl.merchant_id,
				       coalesce(m.merchant_name, (
				           select m0.merchant_name
				           from ticket_package_merchant pm0
				           join merchant m0 on m0.id = pm0.merchant_id and m0.deleted = 0
				           where pm0.package_id = p.id and pm0.deleted = 0
				           order by pm0.sort_no asc, pm0.id asc
				           limit 1
				       ), '电影票兑换券') as merchant_name,
				       'cinema' as merchant_type,
				       p.package_name as detail_name,
				       cl.created_at as occurred_at,
				       'received' as status,
				       null as amount_cent,
				       'TICKET_PACKAGE_DETAIL' as target_detail_type,
				       null as refund_status
				from ticket_package_claim cl
				join ticket_package p on p.id = cl.package_id and p.deleted = 0
				left join merchant m on m.id = cl.merchant_id and m.deleted = 0
				where cl.deleted = 0 and cl.talent_id = ?
				""";
	}

	private Map<String, Object> enrichRecord(Map<String, Object> record) {
		Object amountCent = record.remove("amountCent");
		if (amountCent instanceof Number number) {
			record.put("amount", BigDecimal.valueOf(number.longValue(), 2));
		} else {
			record.put("amount", null);
		}
		record.put("merchantType", normalizedMerchantType(String.valueOf(record.getOrDefault("merchantType", "service"))));
		return record;
	}

	private String normalizedMerchantType(String merchantType) {
		return switch (merchantType) {
			case "food", "餐饮服务" -> "food";
			case "hotel", "酒店", "公寓" -> "hotel";
			case "cinema", "影院" -> "cinema";
			case "theatre", "剧院" -> "theatre";
			case "medical", "医疗" -> "medical";
			case "scenic", "景区" -> "scenic";
			case "parking", "停车" -> "parking";
			case "fuel", "加油" -> "fuel";
			case "carWash", "洗车" -> "carWash";
			case "shopping", "购物" -> "shopping";
			default -> "service";
		};
	}
}