package com.talentcard.server.business.talent;

import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.Arrays;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.Set;
import java.time.LocalDate;

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;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@Service
public class TalentService {
	private static final Set<String> TALENT_LEVELS = Set.of("卓越人才卡", "杰出人才卡", "菁英人才卡", "普通游客");

	private static final String[] IMPORT_HEADERS = {
			"姓名（必填）", "手机号码（必填）", "身份证号（必填）", "性别（必填）", "卡片等级（必填）", "人才卡号（必填）",
			"外地/本地（必填）", "工作单位（必填）", "工作地址（必填）", "联系地址（必填）", "服务专员（必填）"
	};
	private record ImportTalentRow(TalentCreateRequest request, Long advisorId) {}

	private final JdbcTemplate jdbcTemplate;

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

	@Transactional
	public Map<String, Object> create(TalentCreateRequest request) {
		String talentLevel = blankToNull(request.talentLevel());
		String cardNo = request.talentCardNo().trim().toUpperCase();
		Integer cardCount = jdbcTemplate.queryForObject("select count(*) from talent_card where card_no = ?", Integer.class, cardNo);
		if (cardCount != null && cardCount > 0) {
			throw new BusinessException(ErrorCode.CONFLICT, "人才卡号已存在");
		}
		String mobile = blankToNull(request.mobile());
		// 一个手机号只能存在一个账号：新建持卡人前先查重（编辑手机号走 updateMobile，同样有查重）。
		if (mobile != null) {
			Integer mobileCount = jdbcTemplate.queryForObject(
					"select count(*) from talent_user where deleted = 0 and mobile = ?", Integer.class, mobile);
			if (mobileCount != null && mobileCount > 0) {
				throw new BusinessException(ErrorCode.CONFLICT, "该手机号已存在账号，一个手机号只能存在一个账号");
			}
		}
		jdbcTemplate.update("""
				insert into talent_user(openid, name, mobile, id_card_no, gender, work_unit, talent_level,
				                        source_area_type, work_address, contact_address)
				values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				""",
				blankToNull(request.openid()),
				request.name(),
				mobile,
				blankToNull(request.idCardNo()),
				request.gender(),
				blankToNull(request.workUnit()),
				talentLevel,
				defaultValue(request.sourceAreaType(), "LOCAL"),
				blankToNull(request.workAddress()),
				blankToNull(request.contactAddress()));
		Long id = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		jdbcTemplate.update("""
				insert into talent_card(talent_id, card_no, card_type, card_level, valid_start, valid_end, issue_time, status)
				values (?, ?, 'PERSONAL', ?, curdate(), date_add(curdate(), interval 1 year), now(), 1)
				""", id, cardNo, talentLevel);
		jdbcTemplate.update("update talent_user set talent_card_no = ? where id = ?", cardNo, id);
		return detail(id);
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> page(
			String keyword,
			String mobile,
			String cardNo,
			String level,
			Integer status,
			LocalDate registerStart,
			LocalDate registerEnd,
			int page,
			int size
	) {
		if (registerStart != null && registerEnd != null && registerStart.isAfter(registerEnd)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "注册开始日期不能晚于结束日期");
		}
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		StringBuilder sql = new StringBuilder("""
				select id, name, mobile, id_card_no, talent_level, talent_card_no, source_area_type,
				       status, register_time,
				       (select count(*) from benefit_order o where o.talent_id = talent_user.id and o.deleted = 0) as order_count
				from talent_user
				where deleted = 0
				""");
		StringBuilder countSql = new StringBuilder("select count(*) from talent_user where deleted = 0");
		List<Object> args = new ArrayList<>();
		if (keyword != null && !keyword.isBlank()) {
			sql.append(" and name like ?");
			countSql.append(" and name like ?");
			args.add("%" + keyword.trim() + "%");
		}
		if (mobile != null && !mobile.isBlank()) {
			sql.append(" and mobile like ?");
			countSql.append(" and mobile like ?");
			args.add("%" + mobile.trim() + "%");
		}
		if (cardNo != null && !cardNo.isBlank()) {
			sql.append(" and talent_card_no like ?");
			countSql.append(" and talent_card_no like ?");
			args.add("%" + cardNo.trim() + "%");
		}
		if (level != null && !level.isBlank()) {
			sql.append(" and talent_level = ?");
			countSql.append(" and talent_level = ?");
			args.add(level);
		}
		if (status != null) {
			sql.append(" and status = ?");
			countSql.append(" and status = ?");
			args.add(status);
		}
		if (registerStart != null) {
			sql.append(" and register_time >= ?");
			countSql.append(" and register_time >= ?");
			args.add(registerStart.atStartOfDay());
		}
		if (registerEnd != null) {
			sql.append(" and register_time < ?");
			countSql.append(" and register_time < ?");
			args.add(registerEnd.plusDays(1).atStartOfDay());
		}
		sql.append(" order by register_time desc, id desc limit ? offset ?");
		Object[] pageArgs = Arrays.copyOf(args.toArray(), args.size() + 2);
		pageArgs[args.size()] = safeSize;
		pageArgs[args.size() + 1] = (safePage - 1) * safeSize;
		List<Map<String, Object>> records = query(sql.toString(), pageArgs);
		Long total = jdbcTemplate.queryForObject(countSql.toString(), Long.class, args.toArray());
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

	@Transactional(readOnly = true)
	public Map<String, Object> detail(Long id) {
		List<Map<String, Object>> rows = query("""
				select id, openid, name, mobile, id_card_no, gender, work_unit, talent_level,
				       source_area_type, talent_card_no, register_time, status, work_address, contact_address, advisor_id, service_staff_id
				from talent_user
				where deleted = 0 and id = ?
				""", id);
		if (rows.isEmpty()) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		}
		return rows.get(0);
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> availableAdvisors() {
		return query("""
				select id, advisor_name, department, mobile, card_level
				from talent_card_advisor
				where status = 1
				order by card_level, id
				""");
	}

	@Transactional
	public Map<String, Object> updateMobile(Long id, String mobile) {
		String normalizedMobile = mobile.trim();
		Integer duplicateCount = jdbcTemplate.queryForObject("""
				select count(*) from talent_user
				where deleted = 0 and mobile = ? and id <> ?
				""", Integer.class, normalizedMobile, id);
		if (duplicateCount != null && duplicateCount > 0) {
			throw new BusinessException(ErrorCode.CONFLICT, "手机号已被其他持卡人使用");
		}
		int updated = jdbcTemplate.update("""
				update talent_user set mobile = ?
				where id = ? and deleted = 0
				""", normalizedMobile, id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		}
		return detail(id);
	}

	@Transactional
	public Map<String, Object> updateCertification(Long id, TalentCertificationUpdateRequest request) {
		String idCardNo = blankToNull(request.idCardNo());
		if (idCardNo != null) {
			idCardNo = idCardNo.toUpperCase();
			Integer duplicateCount = jdbcTemplate.queryForObject("""
					select count(*) from talent_user
					where deleted = 0 and id_card_no = ? and id <> ?
					""", Integer.class, idCardNo, id);
			if (duplicateCount != null && duplicateCount > 0) {
				throw new BusinessException(ErrorCode.CONFLICT, "身份证号已被其他持卡人使用");
			}
		}
		int updated = jdbcTemplate.update("""
				update talent_user
				set name = ?, id_card_no = ?, gender = ?, work_unit = ?, work_address = ?, contact_address = ?
				where id = ? and deleted = 0
				""",
				request.name().trim(),
				idCardNo,
				request.gender(),
				blankToNull(request.workUnit()),
				blankToNull(request.workAddress()),
				blankToNull(request.contactAddress()),
				id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		}
		return detail(id);
	}

	@Transactional
	public Map<String, Object> updateCardLevel(Long id, String talentLevel) {
		Integer cardCount = jdbcTemplate.queryForObject("""
				select count(*) from talent_card
				where deleted = 0 and talent_id = ? and card_type = 'PERSONAL'
				""", Integer.class, id);
		if (cardCount == null || cardCount == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "该持卡人没有有效的主人才卡");
		}
		int updated = jdbcTemplate.update("""
				update talent_user set talent_level = ?
				where id = ? and deleted = 0
				""", talentLevel, id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		}
		jdbcTemplate.update("""
				update talent_card set card_level = ?
				where deleted = 0 and talent_id = ? and card_type = 'PERSONAL'
				""", talentLevel, id);
		return detail(id);
	}

	@Transactional
	public Map<String, Object> updateAdvisor(Long id, Long advisorId) {
		if (advisorId != null) {
			Integer advisorCount = jdbcTemplate.queryForObject("""
					select count(*) from talent_card_advisor where id = ? and status = 1
					""", Integer.class, advisorId);
			if (advisorCount == null || advisorCount == 0) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "服务专员不存在或已停用");
			}
		}
		int updated = jdbcTemplate.update("""
				update talent_user set advisor_id = ?
				where id = ? and deleted = 0
				""", advisorId, id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		}
		return detail(id);
	}

	@Transactional(readOnly = true)
	public List<Map<String, Object>> availableServiceStaff() {
		return query("""
				select id, display_name, mobile, position_name
				from admin_operator
				where deleted = 0 and status = 1
				order by display_name, id
				""");
	}

	@Transactional
	public Map<String, Object> updateStatus(Long id, Byte status) {
		if (status == null || (status != 0 && status != 1)) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "状态只能为0停用或1启用");
		}
		int updated = jdbcTemplate.update("update talent_user set status = ? where id = ? and deleted = 0", status, id);
		if (updated == 0) throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		return detail(id);
	}

	@Transactional
	public Map<String, Object> updateServiceStaff(Long id, Long staffId) {
		if (staffId != null) {
			Integer staffCount = jdbcTemplate.queryForObject("""
					select count(*) from admin_operator where id = ? and deleted = 0 and status = 1
					""", Integer.class, staffId);
			if (staffCount == null || staffCount == 0) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "服务人员不存在或已停用");
			}
		}
		int updated = jdbcTemplate.update("""
				update talent_user set service_staff_id = ?
				where id = ? and deleted = 0
				""", staffId, id);
		if (updated == 0) {
			throw new BusinessException(ErrorCode.NOT_FOUND, "持卡人不存在");
		}
		return detail(id);
	}

	@Transactional(readOnly = true)
	public byte[] exportExcel(String keyword, String mobile, String cardNo, String level, Integer status,
			LocalDate registerStart, LocalDate registerEnd) {
		List<Object[]> rows = new ArrayList<>();
		int pageNumber = 1;
		long rowNumber = 1;
		while (true) {
			PageResult<Map<String, Object>> result = page(keyword, mobile, cardNo, level, status,
					registerStart, registerEnd, pageNumber, 100);
			for (Map<String, Object> row : result.records()) {
				rows.add(new Object[] {
						rowNumber++,
						row.get("name"),
						maskMobile(row.get("mobile")),
						row.get("talentCardNo"),
						row.get("talentLevel"),
						"OUTSIDE".equals(row.get("sourceAreaType")) ? "外地" : "本地",
						row.get("orderCount"),
						formatDateTime(row.get("registerTime")),
						Integer.valueOf(0).equals(row.get("status")) ? "停用" : "正常"
				});
			}
			if ((long) pageNumber * result.size() >= result.total() || result.records().isEmpty()) {
				break;
			}
			pageNumber++;
		}

		String[] headers = { "编号", "持卡人名称", "持卡人手机号", "人才卡号", "卡片等级", "外地/本地", "订单数", "注册时间", "状态" };
		try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
			Sheet sheet = workbook.createSheet("持卡人列表");
			sheet.createFreezePane(0, 1);
			CellStyle headerStyle = createHeaderStyle(workbook);
			Row headerRow = sheet.createRow(0);
			for (int index = 0; index < headers.length; index++) {
				headerRow.createCell(index).setCellValue(headers[index]);
				headerRow.getCell(index).setCellStyle(headerStyle);
			}
			for (int rowIndex = 0; rowIndex < rows.size(); rowIndex++) {
				Row excelRow = sheet.createRow(rowIndex + 1);
				Object[] values = rows.get(rowIndex);
				for (int columnIndex = 0; columnIndex < values.length; columnIndex++) {
					excelRow.createCell(columnIndex).setCellValue(values[columnIndex] == null ? "" : String.valueOf(values[columnIndex]));
				}
			}
			int[] widths = { 10, 20, 18, 20, 16, 12, 10, 22, 10 };
			for (int index = 0; index < widths.length; index++) {
				sheet.setColumnWidth(index, widths[index] * 256);
			}
			workbook.write(output);
			return output.toByteArray();
		} catch (IOException exception) {
			throw new IllegalStateException("生成持卡人 Excel 失败", exception);
		}
	}

	public byte[] importTemplate() {
		try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
			Sheet sheet = workbook.createSheet("持卡人导入模板");
			CellStyle headerStyle = createHeaderStyle(workbook);
			Row headerRow = sheet.createRow(0);
			for (int index = 0; index < IMPORT_HEADERS.length; index++) {
				headerRow.createCell(index).setCellValue(IMPORT_HEADERS[index]);
				headerRow.getCell(index).setCellStyle(headerStyle);
			}
			int[] widths = { 16, 18, 24, 10, 18, 20, 14, 24, 28, 28, 18 };
			for (int index = 0; index < widths.length; index++) {
				sheet.setColumnWidth(index, widths[index] * 256);
			}
			sheet.createFreezePane(0, 1);
			workbook.write(output);
			return output.toByteArray();
		} catch (IOException exception) {
			throw new IllegalStateException("生成持卡人导入模板失败", exception);
		}
	}

	@Transactional
	public Map<String, Object> importExcel(InputStream inputStream) {
		List<Map<String, Object>> failures = new ArrayList<>();
		List<TalentCreateRequest> validRows = new ArrayList<>();
		List<Long> staffIds = new ArrayList<>();
		Set<String> fileMobiles = new HashSet<>();
		Set<String> fileIdCards = new HashSet<>();
		Set<String> fileCardNos = new HashSet<>();
		try (Workbook workbook = new XSSFWorkbook(inputStream)) {
			Sheet sheet = workbook.getNumberOfSheets() == 0 ? null : workbook.getSheetAt(0);
			if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 中没有可导入的数据");
			}
			validateImportHeaders(sheet.getRow(0));
			DataFormatter formatter = new DataFormatter();
			int dataRows = 0;
			for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				Row row = sheet.getRow(rowIndex);
				if (row == null || isEmptyRow(row, formatter)) {
					continue;
				}
				dataRows++;
				if (dataRows > 1000) {
					throw new BusinessException(ErrorCode.BAD_REQUEST, "单次最多导入1000条持卡人数据");
				}
				String name = cellText(row, 0, formatter);
				String mobile = cellText(row, 1, formatter);
				String idCardNo = cellText(row, 2, formatter).toUpperCase();
				String gender = cellText(row, 3, formatter);
				String level = cellText(row, 4, formatter);
				String talentCardNo = cellText(row, 5, formatter).toUpperCase();
				String area = cellText(row, 6, formatter);
				String workUnit = cellText(row, 7, formatter);
				String workAddress = cellText(row, 8, formatter);
				String contactAddress = cellText(row, 9, formatter);
				String advisorName = cellText(row, 10, formatter);
				String error = validateImportRow(name, mobile, idCardNo, gender, level, talentCardNo, area, workUnit, workAddress,
						contactAddress, fileMobiles, fileIdCards, fileCardNos);
				if (error != null) {
					failures.add(importFailure(rowIndex + 1, name, error));
					continue;
				}
				Long staffId = serviceStaffIdByName(advisorName);
				if (staffId == null) {
					failures.add(importFailure(rowIndex + 1, name,
							advisorName.isBlank() ? "服务专员不能为空" : "服务专员不存在、已停用或在员工管理中姓名不唯一"));
					continue;
				}
				fileMobiles.add(mobile);
				fileIdCards.add(idCardNo);
				fileCardNos.add(talentCardNo);
				staffIds.add(staffId);
				validRows.add(new TalentCreateRequest(null, name, mobile, idCardNo, genderValue(gender), workUnit, level, talentCardNo,
						"外地".equals(area) ? "OUTSIDE" : "LOCAL", workAddress, contactAddress));
			}
			if (dataRows == 0) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 中没有可导入的数据");
			}
		} catch (BusinessException exception) {
			throw exception;
		} catch (IOException | RuntimeException exception) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 文件无法解析，请使用下载的导入模板");
		}

		for (int index = 0; index < validRows.size(); index++) {
			Map<String, Object> created = create(validRows.get(index));
			// 服务专员写进专属服务人员字段（和「持卡人管理」里选服务人员是同一个字段）。
			updateServiceStaff(((Number) created.get("id")).longValue(), staffIds.get(index));
		}
		Map<String, Object> result = new LinkedHashMap<>();
		result.put("successCount", validRows.size());
		result.put("failureCount", failures.size());
		result.put("failures", failures);
		return result;
	}

	@Transactional(readOnly = true)
	public PageResult<Map<String, Object>> cards(Long id, int page, int size) {
		int safePage = Math.max(page, 1);
		int safeSize = Math.min(Math.max(size, 1), 100);
		List<Map<String, Object>> records = query("""
				select id, talent_id, card_no, card_type, card_level, valid_start, valid_end,
				       issue_time, status, expire_time, is_family_card_enabled, family_card_limit
				from talent_card
				where deleted = 0 and talent_id = ?
				order by id desc
				limit ? offset ?
				""", id, safeSize, (safePage - 1) * safeSize);
		Long total = jdbcTemplate.queryForObject("""
				select count(*) from talent_card where deleted = 0 and talent_id = ?
				""", Long.class, id);
		return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
	}

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

	private String defaultValue(String value, String fallback) {
		return value == null || value.isBlank() ? fallback : value;
	}

	private String blankToNull(String value) {
		return value == null || value.isBlank() ? null : value;
	}

	private CellStyle createHeaderStyle(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		Font font = workbook.createFont();
		font.setBold(true);
		style.setFont(font);
		return style;
	}

	private void validateImportHeaders(Row row) {
		DataFormatter formatter = new DataFormatter();
		if (row == null) {
			throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 表头缺失，请使用下载的导入模板");
		}
		for (int index = 0; index < IMPORT_HEADERS.length; index++) {
			if (!IMPORT_HEADERS[index].equals(cellText(row, index, formatter))) {
				throw new BusinessException(ErrorCode.BAD_REQUEST, "Excel 表头不正确，请使用下载的导入模板");
			}
		}
	}

	private boolean isEmptyRow(Row row, DataFormatter formatter) {
		for (int index = 0; index < IMPORT_HEADERS.length; index++) {
			if (!cellText(row, index, formatter).isBlank()) {
				return false;
			}
		}
		return true;
	}

	private String cellText(Row row, int index, DataFormatter formatter) {
		return row.getCell(index) == null ? "" : formatter.formatCellValue(row.getCell(index)).trim();
	}

	private String validateImportRow(String name, String mobile, String idCardNo, String gender, String level,
			String talentCardNo, String area, String workUnit, String workAddress, String contactAddress,
			Set<String> fileMobiles, Set<String> fileIdCards, Set<String> fileCardNos) {
		if (name.isBlank()) return "姓名不能为空";
		if (mobile.isBlank()) return "手机号不能为空";
		if (idCardNo.isBlank()) return "身份证号不能为空";
		if (gender.isBlank()) return "性别不能为空";
		if (level.isBlank()) return "卡片等级不能为空";
		if (talentCardNo.isBlank()) return "人才卡号不能为空";
		if (area.isBlank()) return "外地/本地不能为空";
		if (workUnit.isBlank()) return "工作单位不能为空";
		if (workAddress.isBlank()) return "工作地址不能为空";
		if (contactAddress.isBlank()) return "联系地址不能为空";
		if (name.isBlank()) return "姓名不能为空";
		if (name.length() > 64) return "姓名不能超过64个字符";
		if (!mobile.matches("^1\\d{10}$")) return "手机号须为11位数字";
		if (!idCardNo.matches("^[0-9A-Z]{18}$")) return "身份证号须为18位数字或大写英文字母";
		if (!gender.isBlank() && !Set.of("男", "女").contains(gender)) return "性别只能填写男或女";
		if (!TALENT_LEVELS.contains(level)) return "卡片等级须填写卓越人才卡、杰出人才卡、菁英人才卡或普通游客";
		if (!talentCardNo.matches("^[0-9A-Z-]{1,64}$")) return "人才卡号只能包含大写字母、数字和短横线";
		if (!area.isBlank() && !Set.of("本地", "外地").contains(area)) return "外地/本地只能填写本地或外地";
		if (workUnit.length() > 128) return "工作单位不能超过128个字符";
		if (workAddress.length() > 255) return "工作地址不能超过255个字符";
		if (contactAddress.length() > 255) return "联系地址不能超过255个字符";
		if (fileMobiles.contains(mobile)) return "Excel 内手机号重复";
		if (fileIdCards.contains(idCardNo)) return "Excel 内身份证号重复";
		if (fileCardNos.contains(talentCardNo)) return "Excel 内人才卡号重复";
		Integer mobileCount = jdbcTemplate.queryForObject("select count(*) from talent_user where deleted = 0 and mobile = ?", Integer.class, mobile);
		if (mobileCount != null && mobileCount > 0) return "手机号已存在";
		Integer idCardCount = jdbcTemplate.queryForObject("select count(*) from talent_user where deleted = 0 and id_card_no = ?", Integer.class, idCardNo);
		if (idCardCount != null && idCardCount > 0) return "身份证号已存在";
		Integer cardCount = jdbcTemplate.queryForObject("select count(*) from talent_card where card_no = ?", Integer.class, talentCardNo);
		if (cardCount != null && cardCount > 0) return "人才卡号已存在";
		return null;
	}

	/**
	 * 导入时"服务专员"填的是员工姓名，对应「系统管理 → 员工管理」里的员工（admin_operator）。
	 *
	 * 以前查的是老的 talent_card_advisor 顾问表，员工管理里新建的人根本查不到，
	 * 于是导入一直报"服务专员不存在"。这里改成按员工姓名查（未删除、已启用、且姓名唯一）。
	 */
	Long serviceStaffIdByName(String staffName) {
		if (staffName == null || staffName.isBlank()) return null;
		List<Long> ids = jdbcTemplate.queryForList("""
				select id from admin_operator
				where deleted = 0 and status = 1 and display_name = ?
				""", Long.class, staffName.trim());
		return ids.size() == 1 ? ids.get(0) : null;
	}

	private Byte genderValue(String gender) {
		if ("男".equals(gender)) return 1;
		if ("女".equals(gender)) return 2;
		return null;
	}

	private Map<String, Object> importFailure(int rowNumber, String name, String reason) {
		Map<String, Object> failure = new LinkedHashMap<>();
		failure.put("rowNumber", rowNumber);
		failure.put("name", name);
		failure.put("reason", reason);
		return failure;
	}

	private String maskMobile(Object value) {
		String mobile = value == null ? "" : String.valueOf(value);
		return mobile.length() == 11 ? mobile.substring(0, 3) + "****" + mobile.substring(7) : mobile;
	}

	private String formatDateTime(Object value) {
		return value == null ? "" : String.valueOf(value).replace('T', ' ');
	}
}