package com.talentcard.server.business.card;

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.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 CardAdminService {

	private final JdbcTemplate jdbcTemplate;

	public CardAdminService(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> familyApplications(
			String familyKeyword,
			String talentKeyword,
			String cardLevel,
			String status,
			String applyStart,
			String applyEnd,
			int page,
			int size
	) {
		return familyPage(false, familyKeyword, talentKeyword, cardLevel, status, applyStart, applyEnd, page, size);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> familyCards(
			String familyKeyword,
			String talentKeyword,
			String cardLevel,
			Integer status,
			String applyStart,
			String applyEnd,
			int page,
			int size
	) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder where = new StringBuilder(" where f.deleted = 0 and f.status = '已通过' and c.deleted = 0");
		List<Object> args = new ArrayList<>();
		appendQualifiedFamilyFilters(where, args, familyKeyword, talentKeyword, cardLevel, applyStart, applyEnd);
		if (status != null) {
			where.append(" and c.status = ?");
			args.add(status);
		}
		List<Object> pageArgs = new ArrayList<>(args);
		pageArgs.add(safeSize);
		pageArgs.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query("""
				select f.id, f.application_no, f.family_name, f.family_mobile, f.relationship,
				       f.talent_name, f.talent_mobile, f.talent_card_no, f.card_level, f.apply_time,
				       c.id as card_id, c.card_no, c.status, c.issue_time, c.valid_start, c.valid_end
				from family_card_application f
				join talent_card c on c.id = f.family_card_id
				""" + where + " order by f.apply_time desc, f.id desc limit ? offset ?", pageArgs.toArray());
		Long total = jdbcTemplate.queryForObject("""
				select count(*)
				from family_card_application f
				join talent_card c on c.id = f.family_card_id
				""" + where, Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	@Transactional(readOnly = true)
	public Map<String, Object> familyApplication(Long id) {
		return one("select * from family_card_application where deleted = 0 and id = ?", id, "家属卡申请不存在");
	}

	@Transactional
	public Map<String, Object> auditFamilyApplication(Long id, CardAuditRequest request) {
		Map<String, Object> application = familyApplication(id);
		String status = request.status();
		if (!"已通过".equals(status) && !"已驳回".equals(status)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "审核状态不正确");
		}
		if (!"待审核".equals(application.get("status"))) {
			throw new BusinessException(ErrorCode.CONFLICT, "该申请已审核");
		}
		Long familyTalentId = null;
		Long familyCardId = null;
		if ("已通过".equals(status)) {
			familyTalentId = createFamilyTalent(application);
			familyCardId = createFamilyCard(application, familyTalentId);
		}
		jdbcTemplate.update("""
				update family_card_application
				set status = ?, audit_time = now(), auditor_name = ?, audit_remark = ?,
				    family_talent_id = ?, family_card_id = ?
				where deleted = 0 and id = ?
				""",
				status,
				defaultAuditor(request.auditorName()),
				request.auditRemark(),
				familyTalentId,
				familyCardId,
				id);
		return familyApplication(id);
	}

	@Transactional
	public Map<String, Object> updateFamilyCardStatus(Long cardId, Byte status) {
		if (status == null || (status != 0 && status != 1)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "卡片状态不正确");
		}
		int updated = jdbcTemplate.update("update talent_card set status = ? where deleted = 0 and id = ?", status, cardId);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "家属卡不存在");
		}
		return one("""
				select f.id, f.application_no, f.family_name, f.family_mobile, f.relationship,
				       f.talent_name, f.talent_mobile, f.talent_card_no, f.card_level, f.apply_time,
				       c.id as card_id, c.card_no, c.status, c.issue_time, c.valid_start, c.valid_end
				from family_card_application f
				join talent_card c on c.id = f.family_card_id
				where c.id = ? and f.deleted = 0 and c.deleted = 0
				""", cardId, "家属卡不存在");
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> upgradeApplications(
			String keyword,
			String targetLevel,
			String status,
			String applyStart,
			String applyEnd,
			int page,
			int size
	) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder where = new StringBuilder(" where deleted = 0");
		List<Object> args = new ArrayList<>();
		if (keyword != null && !keyword.isBlank()) {
			where.append(" and (talent_name like ? or talent_mobile like ? or talent_card_no like ? or application_no like ?)");
			String like = "%" + keyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (targetLevel != null && !targetLevel.isBlank()) {
			where.append(" and target_level = ?");
			args.add(targetLevel);
		}
		if (status != null && !status.isBlank()) {
			where.append(" and status = ?");
			args.add(status);
		}
		if (applyStart != null && !applyStart.isBlank()) {
			where.append(" and date(apply_time) >= ?");
			args.add(applyStart);
		}
		if (applyEnd != null && !applyEnd.isBlank()) {
			where.append(" and date(apply_time) <= ?");
			args.add(applyEnd);
		}
		List<Object> pageArgs = new ArrayList<>(args);
		pageArgs.add(safeSize);
		pageArgs.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query("""
				select *
				from upgrade_application
				""" + where + " order by apply_time desc, id desc limit ? offset ?", pageArgs.toArray());
		Long total = jdbcTemplate.queryForObject("select count(*) from upgrade_application " + where, Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	@Transactional(readOnly = true)
	public Map<String, Object> upgradeApplication(Long id) {
		Map<String, Object> application = one("""
				select a.*, t.id_card_no as talent_id_card_no,
				       t.contact_address as contact_address,
				       t.work_address as work_address,
				       t.gender as gender
				from upgrade_application a
				left join talent_user t on t.id = a.talent_id and t.deleted = 0
				where a.deleted = 0 and a.id = ?
				""", id, "升级申请不存在");
		application.put("honors", query("""
				select id, honor_name, obtained_date, proof_file_id, sort_no
				from upgrade_application_honor where application_id = ? order by sort_no, id
				""", id));
		return application;
	}

	@Transactional
	public Map<String, Object> auditUpgradeApplication(Long id, CardAuditRequest request) {
		Map<String, Object> application = upgradeApplication(id);
		String status = request.status();
		if (!"已通过".equals(status) && !"已驳回".equals(status)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "审核状态不正确");
		}
		if (!"待审核".equals(application.get("status"))) {
			throw new BusinessException(ErrorCode.CONFLICT, "该申请已审核");
		}
		// 通过升级申请只改申请单状态：持卡人等级由运营在「持卡人管理」里手动修改
		//（申请等级只作参考，实际等级以后台操作为准），所以这里不再自动改 talent_level / card_level。
		jdbcTemplate.update("""
				update upgrade_application
				set status = ?, audit_time = now(), auditor_name = ?, audit_remark = ?
				where deleted = 0 and id = ?
				""", status, defaultAuditor(request.auditorName()), request.auditRemark(), id);
		return upgradeApplication(id);
	}

	private PageResult<Map<String, Object>> familyPage(
			boolean approvedOnly,
			String familyKeyword,
			String talentKeyword,
			String cardLevel,
			String status,
			String applyStart,
			String applyEnd,
			int page,
			int size
	) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder where = new StringBuilder(" where deleted = 0");
		List<Object> args = new ArrayList<>();
		if (approvedOnly) {
			where.append(" and status = '已通过'");
		}
		appendFamilyFilters(where, args, familyKeyword, talentKeyword, cardLevel, status, applyStart, applyEnd);
		List<Object> pageArgs = new ArrayList<>(args);
		pageArgs.add(safeSize);
		pageArgs.add((safePage - 1) * safeSize);
		List<Map<String, Object>> records = query("""
				select *
				from family_card_application
				""" + where + " order by apply_time desc, id desc limit ? offset ?", pageArgs.toArray());
		Long total = jdbcTemplate.queryForObject("select count(*) from family_card_application " + where, Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	private void appendFamilyFilters(
			StringBuilder where,
			List<Object> args,
			String familyKeyword,
			String talentKeyword,
			String cardLevel,
			String status,
			String applyStart,
			String applyEnd
	) {
		if (familyKeyword != null && !familyKeyword.isBlank()) {
			where.append(" and (family_name like ? or family_mobile like ?)");
			String like = "%" + familyKeyword.trim() + "%";
			args.add(like);
			args.add(like);
		}
		if (talentKeyword != null && !talentKeyword.isBlank()) {
			where.append(" and (talent_name like ? or talent_mobile like ? or talent_card_no like ?)");
			String like = "%" + talentKeyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (cardLevel != null && !cardLevel.isBlank()) {
			where.append(" and card_level = ?");
			args.add(cardLevel);
		}
		if (status != null && !status.isBlank()) {
			where.append(" and status = ?");
			args.add(status);
		}
		if (applyStart != null && !applyStart.isBlank()) {
			where.append(" and date(apply_time) >= ?");
			args.add(applyStart);
		}
		if (applyEnd != null && !applyEnd.isBlank()) {
			where.append(" and date(apply_time) <= ?");
			args.add(applyEnd);
		}
	}

	private void appendQualifiedFamilyFilters(
			StringBuilder where,
			List<Object> args,
			String familyKeyword,
			String talentKeyword,
			String cardLevel,
			String applyStart,
			String applyEnd
	) {
		if (familyKeyword != null && !familyKeyword.isBlank()) {
			where.append(" and (f.family_name like ? or f.family_mobile like ?)");
			String like = "%" + familyKeyword.trim() + "%";
			args.add(like);
			args.add(like);
		}
		if (talentKeyword != null && !talentKeyword.isBlank()) {
			where.append(" and (f.talent_name like ? or f.talent_mobile like ? or f.talent_card_no like ?)");
			String like = "%" + talentKeyword.trim() + "%";
			args.add(like);
			args.add(like);
			args.add(like);
		}
		if (cardLevel != null && !cardLevel.isBlank()) {
			where.append(" and f.card_level = ?");
			args.add(cardLevel);
		}
		if (applyStart != null && !applyStart.isBlank()) {
			where.append(" and date(f.apply_time) >= ?");
			args.add(applyStart);
		}
		if (applyEnd != null && !applyEnd.isBlank()) {
			where.append(" and date(f.apply_time) <= ?");
			args.add(applyEnd);
		}
	}

	private Long createFamilyTalent(Map<String, Object> application) {
		jdbcTemplate.update("""
				insert into talent_user(name, mobile, id_card_no, talent_level, source_area_type, status)
				values (?, ?, ?, ?, 'FAMILY', 1)
				""",
				application.get("familyName"),
				application.get("familyMobile"),
				application.get("idCardNo"),
				application.get("cardLevel"));
		return jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
	}

	private Long createFamilyCard(Map<String, Object> application, Long familyTalentId) {
		String cardNo = BusinessIdGenerator.next("FC");
		jdbcTemplate.update("""
				insert into talent_card(talent_id, card_no, card_type, card_level, valid_start, valid_end, issue_time, status, remark)
				values (?, ?, 'FAMILY', ?, curdate(), date_add(curdate(), interval 1 year), now(), 1, ?)
				""",
				familyTalentId,
				cardNo,
				application.get("cardLevel"),
				"主卡：" + application.get("talentCardNo"));
		Long cardId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		jdbcTemplate.update("update talent_user set talent_card_no = ? where id = ?", cardNo, familyTalentId);
		return cardId;
	}

	private String defaultAuditor(String value) {
		return value == null || value.isBlank() ? "管理员" : value;
	}

	private Map<String, Object> one(String sql, Object arg, String message) {
		List<Map<String, Object>> rows = query(sql, arg);
		if (rows.isEmpty()) {
			throw new BusinessException(ErrorCode.NOT_FOUND, message);
		}
		return rows.get(0);
	}

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