package com.talentcard.server.service;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeParseException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.talentcard.server.business.BusinessIdGenerator;
import com.talentcard.server.request.ServiceAppointmentSubmitRequest;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import com.talentcard.server.common.SensitiveDataCrypto;
import com.talentcard.server.entity.ChildInformation;
import com.talentcard.server.entity.ServiceApplication;
import com.talentcard.server.entity.ServiceApplicationOperationLog;
import com.talentcard.server.mapper.ChildInformationMapper;
import com.talentcard.server.mapper.ServiceApplicationMapper;
import com.talentcard.server.mapper.ServiceApplicationOperationLogMapper;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ServiceAppointmentApplicationService {
    public static final String TYPE_MEDICAL = "MEDICAL_RESERVATION";
    public static final String TYPE_FAST_TRACK = "FAST_TRACK";
    public static final String TYPE_HOTEL = "HOTEL_RESERVATION";
    public static final String TYPE_SPOUSE = "SPOUSE_EMPLOYMENT";
    public static final String TYPE_CHILD = "CHILD_ENTRUSTMENT";

    private final ServiceApplicationMapper applicationMapper;
    private final ChildInformationMapper childInformationMapper;
    private final ServiceApplicationOperationLogMapper operationLogMapper;
    private final SensitiveDataCrypto sensitiveDataCrypto;

    public ServiceAppointmentApplicationService(
            ServiceApplicationMapper applicationMapper,
            ChildInformationMapper childInformationMapper,
            ServiceApplicationOperationLogMapper operationLogMapper,
            SensitiveDataCrypto sensitiveDataCrypto
    ) {
        this.applicationMapper = applicationMapper;
        this.childInformationMapper = childInformationMapper;
        this.operationLogMapper = operationLogMapper;
        this.sensitiveDataCrypto = sensitiveDataCrypto;
    }

    @Transactional
    public Map<String, Object> submit(ServiceAppointmentSubmitRequest request, String idempotencyKey) {
        String key = idempotencyKey(idempotencyKey);
        ServiceApplication duplicate = applicationByIdempotencyKey(key);
        if (duplicate != null) {
            return appResult(duplicate);
        }

        String applicationType = normalizeType(request.applicationType());
        Map<String, Object> card = activeCard(request.talentId(), request.cardId());
        ServiceApplication application = baseApplication(applicationType, key, card, request.remark());

        switch (applicationType) {
            case TYPE_MEDICAL -> fillMedical(application, request);
            case TYPE_FAST_TRACK -> fillFastTrack(application, request);
            case TYPE_HOTEL -> fillHotel(application, request);
            case TYPE_SPOUSE -> fillSpouseEmployment(application, request);
            case TYPE_CHILD -> fillChildEntrustment(application, request, key);
            default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "预约类型不正确");
        }

        try {
            applicationMapper.insert(application);
        } catch (DuplicateKeyException exception) {
            ServiceApplication existing = applicationByIdempotencyKey(key);
            if (existing != null) {
                return appResult(existing);
            }
            throw exception;
        }
        ServiceApplication saved = applicationMapper.selectById(application.getId());
        if (saved == null) {
            throw new BusinessException(ErrorCode.INTERNAL_ERROR, "预约申请保存失败");
        }
        ServiceApplicationOperationLog log = new ServiceApplicationOperationLog();
        log.setApplicationId(saved.getId());
        log.setApplicationNo(saved.getApplicationNo());
        log.setOperationType("SUBMIT");
        log.setAfterStatus(saved.getStatus());
        log.setOperatorType("TALENT");
        log.setOperatorId(saved.getTalentId());
        log.setOperatorName(saved.getTalentName());
        log.setRemark("提交预约申请");
        log.setCreatedAt(saved.getSubmittedAt());
        if (operationLogMapper.insert(log) != 1) {
            throw new BusinessException(ErrorCode.INTERNAL_ERROR, "预约申请操作日志保存失败");
        }
        return appResult(saved);
    }

    private ServiceApplication baseApplication(String applicationType, String idempotencyKey, Map<String, Object> card, String remark) {
        ServiceApplication application = new ServiceApplication();
        application.setApplicationNo(BusinessIdGenerator.next("SA"));
        application.setApplicationType(applicationType);
        application.setIdempotencyKey(idempotencyKey);
        application.setTalentId(longValue(card, "talentId", "talent_id"));
        application.setCardId(longValue(card, "cardId", "card_id"));
        application.setTalentName(text(card, "talentName", "talent_name"));
        application.setTalentMobile(text(card, "talentMobile", "talent_mobile"));
        application.setTalentLevel(text(card, "talentLevel", "talent_level"));
        application.setCardNo(text(card, "cardNo", "card_no"));
        application.setApplicantIdCardNo(blankToNull(text(card, "talentIdCardNo", "talent_id_card_no")));
        application.setSubmittedAt(LocalDateTime.now());
        application.setStatus(TYPE_CHILD.equals(applicationType) ? "待审核" : "待处理");
        application.setRejectReason(blankToNull(clean(remark)));
        application.setDeleted(0);
        return application;
    }

    private void fillMedical(ServiceApplication application, ServiceAppointmentSubmitRequest request) {
        Map<String, Object> merchant = optionalMerchant(request.merchantId(), "medical", "medical", "体检医院不存在或暂未开放");
        applyInstitution(application, merchant, request.hospitalName(), request.contactMobile(), true);
        application.setApplicantName(first(clean(request.applicantName()), application.getTalentName()));
        application.setApplicantMobile(first(clean(request.applicantMobile()), application.getTalentMobile()));
        application.setApplicantIdCardNo(blankToNull(first(request.applicantIdCardNo(), application.getApplicantIdCardNo())));
        applyMedicalPackage(application, request.packageId(), request.packageName());
        String expected = first(clean(request.expectedTime()), clean(request.expectedDate()));
        if (expected.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择期望预约日期");
        }
        application.setExpectedTime(expected);
        application.setEstimatedProcessingTime("提交后1个工作日内由医院确认");
    }

    private void fillFastTrack(ServiceApplication application, ServiceAppointmentSubmitRequest request) {
        Map<String, Object> merchant = optionalMerchant(request.merchantId(), "medical", "medical", "医院快捷通道服务不存在或暂未开放");
        applyInstitution(application, merchant, request.hospitalName(), request.contactMobile(), true);
        application.setPackageName(first(clean(request.packageName()), "医院快捷通道"));
        application.setProductName(first(clean(request.productName()), "快捷通道预约"));
        application.setApplicantName(first(clean(request.applicantName()), application.getTalentName()));
        application.setApplicantMobile(first(clean(request.applicantMobile()), application.getTalentMobile()));
        application.setApplicantIdCardNo(blankToNull(first(request.applicantIdCardNo(), application.getApplicantIdCardNo())));
        String expected = first(clean(request.expectedTime()), clean(request.expectedDate()));
        if (expected.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择期望预约日期");
        }
        application.setExpectedTime(expected);
        application.setEstimatedProcessingTime("提交后1个工作日内由服务专员联系确认");
    }

    private void fillHotel(ServiceApplication application, ServiceAppointmentSubmitRequest request) {
        Map<String, Object> merchant = optionalMerchant(request.merchantId(), "hotel", null, "酒店不存在或暂未开放");
        applyInstitution(application, merchant, request.merchantName(), request.contactMobile(), false);
        application.setApplicantName(first(clean(request.applicantName()), application.getTalentName()));
        application.setApplicantMobile(first(clean(request.applicantMobile()), application.getTalentMobile()));
        application.setGuestCount(request.guestCount());
        application.setSubsidy(blankToNull(clean(request.subsidy())));
        applyHotelProduct(application, request.productId(), request.productName());
        LocalDate checkIn = date(request.checkInDate(), "入住日期格式不正确", false);
        LocalDate checkOut = date(request.checkOutDate(), "离店日期格式不正确", false);
        if (checkIn == null || checkOut == null) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择入住日期和离店日期");
        }
        if (!checkOut.isAfter(checkIn)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "离店日期必须晚于入住日期");
        }
        application.setCheckInDate(checkIn);
        application.setCheckOutDate(checkOut);
        application.setExpectedTime(checkIn + " 至 " + checkOut);
        application.setEstimatedProcessingTime("提交后1个工作日内由酒店确认");
    }

    private void fillSpouseEmployment(ServiceApplication application, ServiceAppointmentSubmitRequest request) {
        Map<String, Object> merchant = requiredPremiumMerchant(request.merchantId(), "employment", "配偶推荐就业服务不存在或暂未开放");
        applyInstitution(application, merchant, null, null, false);
        application.setProductName("配偶推荐就业");
        application.setSpouseName(requiredText(request.spouseName(), "请填写配偶姓名"));
        application.setSpouseMobile(sensitiveDataCrypto.encrypt(requiredText(request.spouseMobile(), "请填写配偶手机号")));
        application.setSpouseIdCardNo(sensitiveDataCrypto.encrypt(requiredText(request.spouseIdCardNo(), "请填写配偶证件号").toUpperCase(Locale.ROOT)));
        application.setApplicantIdCardNo(application.getSpouseIdCardNo());
        application.setSpousePoliticalStatus(requiredText(request.spousePoliticalStatus(), "请填写政治面貌"));
        application.setSpouseEducation(requiredText(request.spouseEducation(), "请选择学历"));
        application.setSpouseSchoolMajor(requiredText(request.spouseSchoolMajor(), "请填写毕业院校及专业"));
        application.setSpouseWorkUnit(requiredText(request.spouseWorkUnit(), "请填写现工作单位及职务"));
        application.setSpouseInInstitution(requiredText(request.spouseInInstitution(), "请选择是否为机关事业单位在编人员"));
        application.setSpouseWorkExperience(requiredText(request.spouseWorkExperience(), "请填写工作经历"));
        application.setApplicantName(application.getSpouseName());
        application.setApplicantMobile(sensitiveDataCrypto.decrypt(application.getSpouseMobile()));
        application.setExpectedTime(first(clean(request.expectedTime()), "提交后由服务专员联系"));
        application.setEstimatedProcessingTime("提交后5个工作日内由服务专员联系确认");
    }

    private void fillChildEntrustment(ServiceApplication application, ServiceAppointmentSubmitRequest request, String idempotencyKey) {
        Map<String, Object> merchant = request.merchantId() == null
                ? Map.of()
                : requiredPremiumMerchant(request.merchantId(), "children", "学生托管服务不存在或暂未开放");
        applyInstitution(application, merchant, first(clean(request.merchantName()), "学生托管服务"), request.contactMobile(), false);
        applyChildrenSchool(application, request.schoolId());
        ChildInformation child = childInformation(application.getTalentId(), request, idempotencyKey);
        application.setChildId(child.getId());
        application.setChildName(child.getChildName());
        application.setChildGender(child.getGender());
        application.setChildBirthDate(child.getBirthDate());
        application.setProofFileIds(child.getProofFileIds());
        application.setApplicantName(child.getChildName());
        application.setApplicantMobile(null);
        application.setApplicantIdCardNo(null);
        application.setProductName(first(application.getProductName(), "学生托管"));
        application.setExpectedTime(first(clean(request.expectedTime()), clean(request.expectedDate()), "审核通过后由服务专员联系"));
        application.setEstimatedProcessingTime("提交后3个工作日内完成审核");
        application.setStatus("待审核");
    }

    private void applyMedicalPackage(ServiceApplication application, Long packageId, String packageName) {
        if (packageId == null) {
            application.setPackageName(first(clean(packageName), "体检预约"));
            return;
        }
        Map<String, Object> row = applicationMapper.selectMedicalPackage(packageId, application.getMerchantId());
        if (row == null || row.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "体检套餐不存在");
        }
        if (intValue(row, "status") != 1 || intValue(row, "stock") == 0) {
            throw new BusinessException(ErrorCode.CONFLICT, first(text(row, "unavailableReason"), "体检套餐当前不可预约"));
        }
        Long merchantId = longValue(row, "merchantId", "merchant_id");
        if (application.getMerchantId() == null && merchantId != null) {
            Map<String, Object> merchant = optionalMerchant(merchantId, "medical", "medical", "体检医院不存在或暂未开放");
            applyInstitution(application, merchant, null, null, true);
        }
        application.setPackageName(text(row, "packageName", "package_name"));
    }

    private void applyHotelProduct(ServiceApplication application, Long productId, String productName) {
        if (productId == null) {
            application.setProductName(requiredText(productName, "请选择酒店房型或套餐"));
            return;
        }
        Map<String, Object> row = applicationMapper.selectMerchantProduct(productId, application.getMerchantId());
        if (row == null || row.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "酒店房型或套餐不存在");
        }
        if (intValue(row, "status") != 1 || intValue(row, "stock") == 0) {
            throw new BusinessException(ErrorCode.CONFLICT, "酒店房型或套餐当前不可预约");
        }
        Long merchantId = longValue(row, "merchantId", "merchant_id");
        if (application.getMerchantId() == null && merchantId != null) {
            Map<String, Object> merchant = optionalMerchant(merchantId, "hotel", null, "酒店不存在或暂未开放");
            applyInstitution(application, merchant, null, null, false);
        }
        application.setProductName(text(row, "productName", "product_name"));
    }

    private void applyChildrenSchool(ServiceApplication application, Long schoolId) {
        if (schoolId == null) {
            return;
        }
        Map<String, Object> row = applicationMapper.selectChildrenSchool(schoolId);
        if (row == null || row.isEmpty() || intValue(row, "enabled") != 1) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "托管学校不存在或暂未开放");
        }
        application.setProductName(text(row, "schoolName", "school_name"));
        application.setContactMobile(first(application.getContactMobile(), text(row, "contactMobile", "contact_mobile")));
        application.setInstitutionAddressSnapshot(first(application.getInstitutionAddressSnapshot(), text(row, "address")));
        application.setInstitutionLatitudeSnapshot(decimalValue(row, "latitude"));
        application.setInstitutionLongitudeSnapshot(decimalValue(row, "longitude"));
    }

    private ChildInformation childInformation(Long talentId, ServiceAppointmentSubmitRequest request, String idempotencyKey) {
        if (request.childId() != null) {
            ChildInformation existing = childInformationMapper.selectOne(new QueryWrapper<ChildInformation>()
                    .eq("id", request.childId())
                    .eq("talent_id", talentId)
                    .eq("deleted", 0)
                    .last("limit 1"));
            if (existing == null) {
                throw new BusinessException(ErrorCode.NOT_FOUND, "子女信息不存在或无权使用");
            }
            return existing;
        }

        ChildInformation duplicate = childInformationMapper.selectOne(new QueryWrapper<ChildInformation>()
                .eq("talent_id", talentId)
                .eq("idempotency_key", idempotencyKey)
                .eq("deleted", 0)
                .last("limit 1"));
        if (duplicate != null) {
            return duplicate;
        }

        List<String> proofIds = proofFileIds(request.proofFileIds());
        if (proofIds.isEmpty()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请上传亲属关系证明材料");
        }
        ChildInformation child = new ChildInformation();
        child.setTalentId(talentId);
        child.setChildName(requiredText(request.childName(), "请填写子女姓名"));
        child.setGender(gender(request.childGender()));
        child.setBirthDate(childBirthDate(request.childBirthDate()));
        child.setProofFileIds(String.join(",", proofIds));
        child.setAuditStatus("待审核");
        child.setIdempotencyKey(idempotencyKey);
        child.setSubmittedAt(LocalDateTime.now());
        child.setDeleted(0);
        try {
            childInformationMapper.insert(child);
        } catch (DuplicateKeyException exception) {
            ChildInformation existing = childInformationMapper.selectOne(new QueryWrapper<ChildInformation>()
                    .eq("talent_id", talentId)
                    .eq("idempotency_key", idempotencyKey)
                    .eq("deleted", 0)
                    .last("limit 1"));
            if (existing != null) {
                return existing;
            }
            throw exception;
        }
        return child;
    }

    private void applyInstitution(ServiceApplication application, Map<String, Object> merchant, String fallbackName, String fallbackMobile, boolean hospital) {
        if (merchant != null && !merchant.isEmpty()) {
            application.setMerchantId(longValue(merchant, "merchantId", "merchant_id", "id"));
            application.setMerchantName(text(merchant, "merchantName", "merchant_name"));
            application.setContactMobile(text(merchant, "contactMobile", "contact_mobile"));
            application.setInstitutionAddressSnapshot(text(merchant, "address"));
            application.setInstitutionLongitudeSnapshot(decimalValue(merchant, "longitude"));
            application.setInstitutionLatitudeSnapshot(decimalValue(merchant, "latitude"));
            if (hospital) {
                application.setHospitalName(application.getMerchantName());
            }
            return;
        }
        String name = clean(fallbackName);
        if (name.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, hospital ? "请选择预约医院" : "请选择服务机构");
        }
        if (hospital) {
            application.setHospitalName(name);
        } else {
            application.setMerchantName(name);
        }
        application.setContactMobile(blankToNull(clean(fallbackMobile)));
    }

    private Map<String, Object> optionalMerchant(Long merchantId, String expectedType, String categoryKey, String message) {
        if (merchantId == null) {
            return Map.of();
        }
        Map<String, Object> merchant = categoryKey == null
                ? applicationMapper.selectMerchantSnapshot(merchantId)
                : applicationMapper.selectPremiumMerchantSnapshot(merchantId, categoryKey);
        if (merchant == null || merchant.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, message);
        }
        if (expectedType != null && !expectedType.equalsIgnoreCase(text(merchant, "merchantType", "merchant_type"))) {
            throw new BusinessException(ErrorCode.CONFLICT, message);
        }
        return merchant;
    }

    private Map<String, Object> requiredPremiumMerchant(Long merchantId, String categoryKey, String message) {
        if (merchantId == null || merchantId <= 0) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择服务机构");
        }
        Map<String, Object> merchant = applicationMapper.selectPremiumMerchantSnapshot(merchantId, categoryKey);
        if (merchant == null || merchant.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, message);
        }
        return merchant;
    }

    private Map<String, Object> activeCard(Long talentId, Long cardId) {
        if (talentId == null && cardId == null) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "人才用户ID或人才卡ID不能为空");
        }
        Map<String, Object> card = applicationMapper.selectActiveCardSnapshot(talentId, cardId);
        if (card == null || card.isEmpty()) {
            throw new BusinessException(ErrorCode.CONFLICT, "未找到有效人才卡");
        }
        return card;
    }

    private ServiceApplication applicationByIdempotencyKey(String idempotencyKey) {
        return applicationMapper.selectOne(new QueryWrapper<ServiceApplication>()
                .eq("idempotency_key", idempotencyKey)
                .eq("deleted", 0)
                .last("limit 1"));
    }

    private Map<String, Object> appResult(ServiceApplication application) {
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("applicationId", application.getId());
        result.put("applicationNo", application.getApplicationNo());
        result.put("applicationType", application.getApplicationType());
        result.put("applicationTypeName", typeName(application.getApplicationType()));
        result.put("status", application.getStatus());
        result.put("reviewRequired", TYPE_CHILD.equals(application.getApplicationType()));
        result.put("submittedAt", application.getSubmittedAt());
        result.put("serviceName", first(application.getHospitalName(), application.getMerchantName(), application.getProductName(), "人才服务"));
        result.put("appointmentContent", first(application.getProductName(), application.getPackageName(), typeName(application.getApplicationType())));
        result.put("appointmentTime", first(application.getExpectedTime(), "以专员通知为准"));
        result.put("estimatedProcessingTime", first(application.getEstimatedProcessingTime(), "提交后由服务专员联系确认"));
        result.put("contactMobile", first(application.getContactMobile(), ""));
        result.put("childId", application.getChildId());
        return result;
    }

    private String normalizeType(String raw) {
        String value = clean(raw).toUpperCase(Locale.ROOT).replace('-', '_');
        return switch (value) {
            case "MEDICAL", "MEDICAL_CHECKUP", "MEDICAL_RESERVATION", "PHYSICAL_EXAM", "HEALTH_CHECK" -> TYPE_MEDICAL;
            case "FAST_TRACK", "QUICK_CHANNEL", "GREEN_CHANNEL", "MEDICAL_APPLICATION", "HOSPITAL", "HOSPITAL_APPOINTMENT" -> TYPE_FAST_TRACK;
            case "HOTEL", "HOTEL_RESERVATION", "HOTEL_BOOKING" -> TYPE_HOTEL;
            case "SPOUSE", "SPOUSE_EMPLOYMENT", "EMPLOYMENT" -> TYPE_SPOUSE;
            case "CHILD", "CHILDREN", "CHILD_ENTRUSTMENT", "CHILDREN_ENTRUSTMENT", "STUDENT_CARE", "STUDENT_ENTRUSTMENT" -> TYPE_CHILD;
            default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "预约类型仅支持体检、医院快捷通道、酒店、配偶就业、学生托管");
        };
    }

    private String typeName(String type) {
        return switch (type) {
            case TYPE_MEDICAL -> "体检预约";
            case TYPE_FAST_TRACK -> "医院快捷通道";
            case TYPE_HOTEL -> "酒店预约";
            case TYPE_SPOUSE -> "配偶推荐就业";
            case TYPE_CHILD -> "学生托管";
            default -> "预约申请";
        };
    }

    private String idempotencyKey(String raw) {
        String key = clean(raw);
        if (key.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "提交凭证不能为空");
        }
        if (key.length() > 128) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "提交凭证长度不能超过128");
        }
        return key;
    }

    private List<String> proofFileIds(List<String> values) {
        if (values == null) {
            return List.of();
        }
        if (values.size() > 3) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "证明材料最多上传3个");
        }
        return values.stream()
                .map(this::clean)
                .filter(item -> !item.isBlank())
                .peek(item -> {
                    if (item.length() > 255) {
                        throw new BusinessException(ErrorCode.BAD_REQUEST, "证明材料文件ID长度不能超过255");
                    }
                })
                .collect(Collectors.toList());
    }

    private String gender(String value) {
        String gender = requiredText(value, "请选择子女性别");
        if (!List.of("男", "女").contains(gender)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择有效的子女性别");
        }
        return gender;
    }

    private LocalDate childBirthDate(String value) {
        LocalDate date = date(value, "子女出生日期格式不正确", true);
        if (date == null) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择子女出生日期");
        }
        if (date.isAfter(LocalDate.now())) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "子女出生日期不能晚于今天");
        }
        return date;
    }

    private LocalDate date(String value, String message, boolean required) {
        String text = clean(value);
        if (text.isBlank()) {
            if (required) {
                throw new BusinessException(ErrorCode.BAD_REQUEST, message);
            }
            return null;
        }
        try {
            return LocalDate.parse(text);
        } catch (DateTimeParseException exception) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, message);
        }
    }

    private String requiredText(String value, String message) {
        String text = clean(value);
        if (text.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, message);
        }
        return text;
    }

    private String first(String... values) {
        for (String value : values) {
            String text = clean(value);
            if (!text.isBlank()) {
                return text;
            }
        }
        return "";
    }

    private String text(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        return value == null ? "" : String.valueOf(value).trim();
    }

    private Long longValue(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        if (value instanceof Number number) {
            return number.longValue();
        }
        if (value == null || String.valueOf(value).isBlank()) {
            return null;
        }
        return Long.parseLong(String.valueOf(value));
    }

    private int intValue(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        if (value instanceof Number number) {
            return number.intValue();
        }
        if (value == null || String.valueOf(value).isBlank()) {
            return 0;
        }
        return Integer.parseInt(String.valueOf(value));
    }

    private BigDecimal decimalValue(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        if (value instanceof BigDecimal decimal) {
            return decimal;
        }
        if (value instanceof Number number) {
            return BigDecimal.valueOf(number.doubleValue());
        }
        if (value == null || String.valueOf(value).isBlank()) {
            return null;
        }
        return new BigDecimal(String.valueOf(value));
    }

    private Object value(Map<String, Object> row, String... keys) {
        if (row == null || row.isEmpty()) {
            return null;
        }
        for (String key : keys) {
            if (row.containsKey(key)) {
                return row.get(key);
            }
            String lower = key.toLowerCase(Locale.ROOT);
            String upper = key.toUpperCase(Locale.ROOT);
            if (row.containsKey(lower)) {
                return row.get(lower);
            }
            if (row.containsKey(upper)) {
                return row.get(upper);
            }
        }
        return row.entrySet().stream()
                .filter(entry -> keysMatch(entry.getKey(), keys))
                .map(Map.Entry::getValue)
                .filter(Objects::nonNull)
                .findFirst()
                .orElse(null);
    }

    private boolean keysMatch(String actual, String[] keys) {
        String normalizedActual = actual.replace("_", "").toLowerCase(Locale.ROOT);
        for (String key : keys) {
            if (normalizedActual.equals(key.replace("_", "").toLowerCase(Locale.ROOT))) {
                return true;
            }
        }
        return false;
    }

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

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