package com.talentcard.server.service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import com.talentcard.server.entity.DependentCardApply;
import com.talentcard.server.entity.DependentCardApplyStatusLog;
import com.talentcard.server.mapper.DependentCardApplyMapper;
import com.talentcard.server.mapper.DependentCardApplyStatusLogMapper;
import com.talentcard.server.request.DependentCardAuditRequest;
import com.talentcard.server.response.DependentCardApplyAdminResponse;
import com.talentcard.server.response.DependentCardApplyStatusLogResponse;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class DependentCardApplyAdminService {

	private static final String ELITE_CARD_LEVEL = "卓越人才卡";
	private static final int MAX_FAMILY_CARD_COUNT = 2;

	private final DependentCardApplyMapper mapper;
	private final DependentCardApplyStatusLogMapper statusLogMapper;
	private final JdbcTemplate jdbcTemplate;

	public DependentCardApplyAdminService(
			DependentCardApplyMapper mapper,
			DependentCardApplyStatusLogMapper statusLogMapper,
			JdbcTemplate jdbcTemplate
	) {
		this.mapper = mapper;
		this.statusLogMapper = statusLogMapper;
		this.jdbcTemplate = jdbcTemplate;
	}

	@Transactional(readOnly = true)
	public PageResult<DependentCardApplyAdminResponse> page(
			Long talentCardId,
			String familyKeyword,
			String talentKeyword,
			String cardLevel,
			Integer status,
			LocalDate applyStart,
			LocalDate applyEnd,
			boolean management,
			int page,
			int size
	) {
		validateStatusFilter(status);
		if (management && status != null
				&& status != DependentCardApply.STATUS_APPROVED
				&& status != DependentCardApply.STATUS_CANCELLED) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "家属卡管理状态只能为正常或已解绑");
		}
		if (applyStart != null && applyEnd != null && applyStart.isAfter(applyEnd)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST,
					management ? "注册开始时间不能晚于结束时间" : "申请开始时间不能晚于结束时间");
		}
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		LambdaQueryWrapper<DependentCardApply> query = Wrappers.<DependentCardApply>lambdaQuery()
				.eq(talentCardId != null, DependentCardApply::getTalentCardId, talentCardId)
				.eq(status != null, DependentCardApply::getStatus, status)
				.in(management && status == null, DependentCardApply::getStatus,
						DependentCardApply.STATUS_APPROVED, DependentCardApply.STATUS_CANCELLED)
				.eq(cardLevel != null && !cardLevel.isBlank(), DependentCardApply::getCardLevel, trimToNull(cardLevel))
				.and(familyKeyword != null && !familyKeyword.isBlank(), wrapper -> wrapper
						.like(DependentCardApply::getDependentName, familyKeyword.trim())
						.or()
						.like(DependentCardApply::getDependentMobile, familyKeyword.trim())
						.or()
						.like(DependentCardApply::getDependentIdCardNo, familyKeyword.trim()))
				.and(talentKeyword != null && !talentKeyword.isBlank(), wrapper -> wrapper
						.like(DependentCardApply::getTalentName, talentKeyword.trim())
						.or()
						.like(DependentCardApply::getTalentMobile, talentKeyword.trim())
						.or()
						.like(DependentCardApply::getTalentCardNo, talentKeyword.trim())
						.or()
						.apply("exists (select 1 from talent_user u where u.id = dependent_card_apply.talent_id and u.deleted = 0 and u.id_card_no like concat('%', {0}, '%'))", talentKeyword.trim()))
				.ge(!management && applyStart != null, DependentCardApply::getApplyTime,
						applyStart == null ? null : applyStart.atStartOfDay())
				.lt(!management && applyEnd != null, DependentCardApply::getApplyTime,
						applyEnd == null ? null : applyEnd.plusDays(1).atStartOfDay())
				.ge(management && applyStart != null, DependentCardApply::getAuditTime,
						applyStart == null ? null : applyStart.atStartOfDay())
				.lt(management && applyEnd != null, DependentCardApply::getAuditTime,
						applyEnd == null ? null : applyEnd.plusDays(1).atStartOfDay());
		long total = mapper.selectCount(query);
		if (total == 0) {
			return new PageResult<>(List.of(), safePage, safeSize, 0);
		}
		long offset = (long) (safePage - 1) * safeSize;
		List<DependentCardApplyAdminResponse> records = mapper.selectList(
						query.orderByDesc(management, DependentCardApply::getAuditTime)
								.orderByDesc(!management, DependentCardApply::getApplyTime)
								.orderByDesc(DependentCardApply::getId)
								.last("limit " + safeSize + " offset " + offset)
				).stream()
				.map(apply -> DependentCardApplyAdminResponse.from(apply, applicationExtra(apply)))
				.toList();
		return new PageResult<>(records, safePage, safeSize, total);
	}

	@Transactional(readOnly = true)
	public DependentCardApplyAdminResponse detail(Long id) {
		DependentCardApply apply = requireApplication(id);
		return DependentCardApplyAdminResponse.from(apply, applicationExtra(apply));
	}

	@Transactional(readOnly = true)
	public List<DependentCardApplyStatusLogResponse> statusLogs(Long id) {
		DependentCardApply apply = requireApplication(id);
		return statusLogMapper.selectList(
						Wrappers.<DependentCardApplyStatusLog>lambdaQuery()
								.eq(DependentCardApplyStatusLog::getApplyId, apply.getId())
								.orderByAsc(DependentCardApplyStatusLog::getCreatedAt)
								.orderByAsc(DependentCardApplyStatusLog::getId)
				).stream()
				.map(DependentCardApplyStatusLogResponse::from)
				.toList();
	}

	@Transactional
	public DependentCardApplyAdminResponse audit(
			Long id,
			DependentCardAuditRequest request,
			Long operatorId
	) {
		int targetStatus = request.status();
		if (targetStatus != DependentCardApply.STATUS_APPROVED
				&& targetStatus != DependentCardApply.STATUS_REJECTED) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "审核状态只能为通过或驳回");
		}
		String auditRemark = trimToNull(request.auditRemark());
		if (targetStatus == DependentCardApply.STATUS_REJECTED && auditRemark == null) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "驳回申请时必须填写审核备注");
		}
		DependentCardApply apply = mapper.selectByIdForUpdate(id);
		if (apply == null) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡申请不存在");
		}
		if (!Integer.valueOf(DependentCardApply.STATUS_PENDING_REVIEW).equals(apply.getStatus())) {
			throw new BusinessException(ErrorCode.CONFLICT, "该申请已审核或已取消");
		}
		if (targetStatus == DependentCardApply.STATUS_APPROVED) {
			ensureApprovalSlot(apply);
		}
		LocalDateTime auditTime = LocalDateTime.now();
		int updated = mapper.update(
				null,
				Wrappers.<DependentCardApply>lambdaUpdate()
						.eq(DependentCardApply::getId, id)
						.eq(DependentCardApply::getStatus, DependentCardApply.STATUS_PENDING_REVIEW)
						.set(DependentCardApply::getStatus, targetStatus)
						.set(DependentCardApply::getAuditBy, operatorId)
						.set(DependentCardApply::getAuditTime, auditTime)
						.set(DependentCardApply::getAuditRemark, auditRemark)
		);
		if (updated != 1) {
			throw new BusinessException(ErrorCode.CONFLICT, "申请状态已变化，请刷新后重试");
		}
		if (statusLogMapper.insert(new DependentCardApplyStatusLog(
				apply.getId(),
				apply.getApplyNo(),
				DependentCardApply.STATUS_PENDING_REVIEW,
				targetStatus,
				"ADMIN",
				operatorId,
				auditRemark
		)) != 1) {
			throw new BusinessException(ErrorCode.INTERNAL_ERROR, "审核状态记录保存失败");
		}
		DependentCardApply updatedApply = mapper.selectById(id);
		return DependentCardApplyAdminResponse.from(updatedApply, applicationExtra(updatedApply));
	}

	@Transactional
	public DependentCardApplyAdminResponse unbind(Long id, Long operatorId) {
		DependentCardApply apply = mapper.selectByIdForUpdate(id);
		if (apply == null) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡不存在");
		}
		if (!Integer.valueOf(DependentCardApply.STATUS_APPROVED).equals(apply.getStatus())) {
			throw new BusinessException(ErrorCode.CONFLICT, "仅正常状态的家属卡可以解除绑定");
		}
		int updated = mapper.update(
				null,
				Wrappers.<DependentCardApply>lambdaUpdate()
						.eq(DependentCardApply::getId, id)
						.eq(DependentCardApply::getStatus, DependentCardApply.STATUS_APPROVED)
						.set(DependentCardApply::getStatus, DependentCardApply.STATUS_CANCELLED)
		);
		if (updated != 1) {
			throw new BusinessException(ErrorCode.CONFLICT, "家属卡状态已变化，请刷新后重试");
		}
		if (statusLogMapper.insert(new DependentCardApplyStatusLog(
				apply.getId(),
				apply.getApplyNo(),
				DependentCardApply.STATUS_APPROVED,
				DependentCardApply.STATUS_CANCELLED,
				"ADMIN",
				operatorId,
				"管理后台解除绑定"
		)) != 1) {
			throw new BusinessException(ErrorCode.INTERNAL_ERROR, "解绑状态记录保存失败");
		}
		DependentCardApply updatedApply = mapper.selectById(id);
		return DependentCardApplyAdminResponse.from(updatedApply, applicationExtra(updatedApply));
	}

	private Map<String, Object> applicationExtra(DependentCardApply apply) {
		if (apply == null || apply.getTalentCardId() == null) {
			return Map.of();
		}
		List<Map<String, Object>> rows = jdbcTemplate.queryForList("""
				select u.id_card_no as talentIdCardNo,
				       u.gender as gender,
				       u.contact_address as contactAddress,
				       u.work_address as workAddress,
				       ao.display_name as auditorName,
				       c.valid_start as validStart,
				       c.valid_end as validEnd,
				       c.issue_time as issueTime,
				       c.family_card_limit as familyCardLimit,
				       (select count(*) from dependent_card_apply approved
				          where approved.deleted = 0
				            and approved.talent_card_id = a.talent_card_id
				            and approved.status = 1) as currentFamilyCardCount
				from dependent_card_apply a
				left join talent_user u on u.id = a.talent_id and u.deleted = 0
				left join talent_card c on c.id = a.talent_card_id and c.deleted = 0
				left join admin_operator ao on ao.id = a.audit_by
				where a.id = ? and a.deleted = 0
				limit 1
				""", apply.getId());
		return rows.isEmpty() ? Map.of() : rows.get(0);
	}

	private void ensureApprovalSlot(DependentCardApply apply) {
		DependentCardApply ownerCard = mapper.selectOwnerCard(apply.getTalentCardId());
		if (ownerCard == null) {
			throw new BusinessException(ErrorCode.CONFLICT, "主卡不存在或已失效，不能审核通过");
		}
		if (!ELITE_CARD_LEVEL.equals(ownerCard.getCardLevel())) {
			throw new BusinessException(ErrorCode.CONFLICT, "该主卡当前无家属附属卡权限");
		}
//		Integer configuredLimit = ownerCard.getFamilyCardLimit();
//		if (configuredLimit == null || configuredLimit <= 0) {
//			throw new BusinessException(ErrorCode.CONFLICT, "该主卡未配置家属附属卡名额");
//		}
		int familyCardLimit = Math.min(2, MAX_FAMILY_CARD_COUNT);
		long approvedCount = mapper.selectCount(
				Wrappers.<DependentCardApply>lambdaQuery()
						.eq(DependentCardApply::getTalentCardId, apply.getTalentCardId())
						.eq(DependentCardApply::getStatus, DependentCardApply.STATUS_APPROVED)
		);
		if (approvedCount >= familyCardLimit) {
			throw new BusinessException(ErrorCode.CONFLICT, "该主卡已达到家属附属卡绑定上限");
		}
	}

	private DependentCardApply requireApplication(Long id) {
		if (id == null || id <= 0) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "申请ID不能为空");
		}
		DependentCardApply apply = mapper.selectById(id);
		if (apply == null) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡申请不存在");
		}
		return apply;
	}

	private void validateStatusFilter(Integer status) {
		if (status != null && (status < DependentCardApply.STATUS_PENDING_REVIEW
				|| status > DependentCardApply.STATUS_CANCELLED)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "申请状态不正确");
		}
	}

	private String trimToNull(String value) {
		if (value == null || value.isBlank()) {
			return null;
		}
		return value.trim();
	}
}