package com.njuzr.eaibackend.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.njuzr.eaibackend.dto.ClassDTO;
import com.njuzr.eaibackend.enums.ClassStudentState;
import com.njuzr.eaibackend.enums.Role;
import com.njuzr.eaibackend.exception.MyException;
import com.njuzr.eaibackend.mapper.*;
import com.njuzr.eaibackend.po.Class;
import com.njuzr.eaibackend.po.ClassStudent;
import com.njuzr.eaibackend.po.Enrollment;
import com.njuzr.eaibackend.po.User;
import com.njuzr.eaibackend.service.ClassService;
import com.njuzr.eaibackend.utils.WebClientUtil;
import com.njuzr.eaibackend.vo.BatchStudentImportDetailVO;
import com.njuzr.eaibackend.vo.BatchStudentImportResultVO;
import com.njuzr.eaibackend.vo.StudentInfoVO;
import com.njuzr.eaibackend.vo.ClassVO;
import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvValidationException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpStatus;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import org.springframework.beans.BeanUtils;

@Slf4j
@Service
public class ClassServiceImpl implements ClassService {
    private final ClassMapper classMapper;
    private final ClassStudentMapper classStudentMapper;
    private final UserMapper userMapper;
    private final CourseStudentMapper courseStudentMapper;
    private final WebClientUtil webClientUtil;

    @Value("${portal.base-url:https://p-nju.seec.seecoder.cn}")
    private String portalBaseUrl;

    @Value("${portal.batch-register-path:/api/user/register/batch/internal}")
    private String portalBatchRegisterPath;

    private final PasswordEncoder passwordEncoder = new BCryptPasswordEncoder();

    public ClassServiceImpl(ClassMapper classMapper,
                            ClassStudentMapper classStudentMapper,
                            UserMapper userMapper,
                            CourseStudentMapper courseStudentMapper,
                            WebClientUtil webClientUtil) {
        this.classMapper = classMapper;
        this.classStudentMapper = classStudentMapper;
        this.userMapper = userMapper;
        this.courseStudentMapper = courseStudentMapper;
        this.webClientUtil = webClientUtil;
    }

    @Override
    public ClassVO createClass(ClassDTO classDTO) {
        Class aClass = new Class();
        aClass.setClassName(classDTO.getClassName());
        aClass.setCourseId(classDTO.getCourseId());
        aClass.setClassTime(classDTO.getClassTime());
        aClass.setTeacherId(classDTO.getTeacherId());
        aClass.setStuNumber(0); // 初始人数为0

        // 生成班级码
        String classCode = generateClassCode();
        aClass.setClassCode(classCode);

        int status = classMapper.insert(aClass);
        if (status == 0) {
            log.error("创建班级失败，数据库插入错误！");
            throw new MyException(HttpStatus.INTERNAL_SERVER_ERROR.value(), HttpStatus.INTERNAL_SERVER_ERROR.getReasonPhrase()+"："+"创建班级错误");
        }

        return convertToVO(aClass);
    }

    public String generateClassCode() {
        // 生成6位字母数字组合的班级码
        String characters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random = new Random();
        StringBuilder codeBuilder = new StringBuilder();

        for (int i = 0; i < 6; i++) {
            int index = random.nextInt(characters.length());
            codeBuilder.append(characters.charAt(index));
        }

        String classCode = codeBuilder.toString();

        // 检查唯一性，如果不唯一则重新生成
        if (!isClassCodeUnique(classCode)) {
            return generateClassCode();
        }

        return classCode;
    }

    public boolean isClassCodeUnique(String classCode) {
        QueryWrapper<Class> wrapper = new QueryWrapper<>();
        wrapper.eq("class_code", classCode);
        Class existingClass = classMapper.selectOne(wrapper);
        return existingClass == null;
    }

    private ClassVO convertToVO(Class aClass) {
        ClassVO classVO = new ClassVO();
        BeanUtils.copyProperties(aClass, classVO);
        return classVO;
    }

    @Override
    public Class updateClass(ClassDTO classDTO) {
        Class aClass=new Class();
        aClass.setClassName(classDTO.getClassName());
        aClass.setClassId(classDTO.getClassId());
        aClass.setClassTime(classDTO.getClassTime());
        try {
            int code = classMapper.updateById(aClass);
            if (code == 0) {
                throw MyException.create(HttpStatus.INTERNAL_SERVER_ERROR, "更新班级失败");
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            throw MyException.create(HttpStatus.INTERNAL_SERVER_ERROR, "更新班级失败");
        }
        aClass= classMapper.selectById(classDTO.getClassId());
        return aClass;
    }

    @Override
    public List<Class> select(Long courseId) {
        QueryWrapper<Class> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("course_id", courseId); // 按course_id查询
        List<Class> res= classMapper.selectList(queryWrapper);
        return res;
    }

    @Override
    @Transactional
    public String deleteClassWithCheck(Long classId) {
        // 1. 查询班级信息
        Class cls = classMapper.selectById(classId);
        if (cls == null) {
            return "班级不存在";
        }

        // 2. 检查学生数量
        if (cls.getStuNumber() > 0) {
            return "班级中还有学生，无法删除";
        }

        // 3. 使用Mapper执行带条件的删除
        QueryWrapper<Class> deleteWrapper = new QueryWrapper<>();
        deleteWrapper.eq("class_id", classId)
                .eq("stu_number", 0); // 双重检查

        int affectedRows = classMapper.delete(deleteWrapper);

        if (affectedRows == 0) {
            return "删除失败（可能学生数量已变化）";
        }

        return "删除成功";
    }

    @Override
    @Transactional
    public BatchStudentImportResultVO batchAddStudents(Long classId, MultipartFile file, String accessToken) {
        // 检查班级是否存在
        Class cls = classMapper.selectById(classId);
        if (cls == null) {
            throw new MyException(404, "班级不存在");
        }

        // 解析文件
        List<StudentImportRow> rows;
        String fileName = file.getOriginalFilename();

        try {
            if (fileName != null && (fileName.endsWith(".xls") || fileName.endsWith(".xlsx"))) {
                rows = parseExcelFile(file);
            } else if (fileName != null && fileName.endsWith(".csv")) {
                rows = parseCsvFile(file);
            } else {
                throw new MyException(400, "不支持的文件类型");
            }
        } catch (Exception e) {
            log.error("解析学生文件失败", e);
            throw new MyException(500, "解析学生文件失败: " + e.getMessage());
        }

        BatchStudentImportResultVO result = new BatchStudentImportResultVO();
        result.setTotal(rows.size());
        result.setSuccessCount(0);
        result.setSkippedCount(0);
        result.setFailedCount(0);

        if (rows.isEmpty()) {
            return result;
        }

        Set<String> fileOfficialNumbers = new HashSet<>();
        Set<String> filePhones = new HashSet<>();

        List<StudentImportRow> rowsToRegister = new ArrayList<>();
        Map<Integer, BatchStudentImportDetailVO> pendingRegisterDetails = new HashMap<>();
        List<User> usersToInsert = new ArrayList<>();
        List<ClassStudent> studentsToInsert = new ArrayList<>();
        Set<Long> enrollmentStudentIds = new HashSet<>();

        for (StudentImportRow row : rows) {
            BatchStudentImportDetailVO detail = new BatchStudentImportDetailVO();
            detail.setRowIndex(row.getRowIndex());
            detail.setOfficialNumber(row.getOfficialNumber());
            detail.setStuName(row.getStuName());
            detail.setPhone(row.getPhone());
            result.getDetails().add(detail);

            String validationError = validateRow(row, fileOfficialNumbers, filePhones);
            if (validationError != null) {
                detail.setStatus("FAILED");
                detail.setMessage(validationError);
                result.setFailedCount(result.getFailedCount() + 1);
                continue;
            }

            User existsByNumber = userMapper.selectByOfficialNumber(row.getOfficialNumber());

            QueryWrapper<ClassStudent> classStudentWrapper = new QueryWrapper<>();
            classStudentWrapper.eq("class_id", classId)
                    .eq("official_number", row.getOfficialNumber());
            if (classStudentMapper.selectCount(classStudentWrapper) > 0) {
                detail.setStatus("SKIPPED");
                detail.setMessage("学生已在班级中，已跳过");
                result.setSkippedCount(result.getSkippedCount() + 1);
                continue;
            }

            if (existsByNumber != null) {
                ClassStudent student = new ClassStudent();
                student.setClassId(classId);
                student.setOfficialNumber(row.getOfficialNumber());
                student.setStuName(row.getStuName());
                student.setState(ClassStudentState.NOT_JOINED);
                studentsToInsert.add(student);

                enrollmentStudentIds.add(existsByNumber.getId());

                detail.setStatus("SUCCESS");
                detail.setMessage("学生已存在，已关联班级和课程");
                result.setSuccessCount(result.getSuccessCount() + 1);
                fileOfficialNumbers.add(row.getOfficialNumber());
                filePhones.add(row.getPhone());
                continue;
            }

            User existsByPhone = userMapper.selectByPhone(row.getPhone());
            if (existsByPhone != null) {
                detail.setStatus("SKIPPED");
                detail.setMessage("EAI中手机号已存在，已跳过");
                result.setSkippedCount(result.getSkippedCount() + 1);
                continue;
            }

            rowsToRegister.add(row);
            pendingRegisterDetails.put(row.getRowIndex(), detail);
            fileOfficialNumbers.add(row.getOfficialNumber());
            filePhones.add(row.getPhone());
        }

        if (!rowsToRegister.isEmpty()) {
            Map<Integer, Map<String, Object>> portalResultByRow = registerUsersInPortal(rowsToRegister, accessToken);
            for (StudentImportRow row : rowsToRegister) {
                BatchStudentImportDetailVO detail = pendingRegisterDetails.get(row.getRowIndex());
                Map<String, Object> portalRow = portalResultByRow.get(row.getRowIndex());
                if (portalRow == null || !"SUCCESS".equals(String.valueOf(portalRow.get("status")))) {
                    detail.setStatus("SKIPPED");
                    detail.setMessage(portalRow == null ? "Portal未返回该行处理结果" : String.valueOf(portalRow.get("message")));
                    result.setSkippedCount(result.getSkippedCount() + 1);
                    continue;
                }

                User user = new User();
                user.setName(row.getStuName());
                user.setOfficialNumber(row.getOfficialNumber());
                user.setPhone(row.getPhone());
                user.setRole(Role.STUDENT);
                user.setCreateTime(new Date());
                Object portalUserId = portalRow.get("userId");
                if (portalUserId instanceof Number) {
                    user.setPid(String.valueOf(((Number) portalUserId).longValue()));
                }
                user.setPassword(passwordEncoder.encode(generateDefaultPassword(row.getOfficialNumber())));
                usersToInsert.add(user);

                ClassStudent student = new ClassStudent();
                student.setClassId(classId);
                student.setOfficialNumber(row.getOfficialNumber());
                student.setStuName(row.getStuName());
                student.setState(ClassStudentState.NOT_JOINED);
                studentsToInsert.add(student);

                detail.setStatus("SUCCESS");
                detail.setMessage("导入成功");
                result.setSuccessCount(result.getSuccessCount() + 1);
            }
        }

        if (!usersToInsert.isEmpty()) {
            userMapper.batchInsert(usersToInsert);
            List<String> importedOfficialNumbers = usersToInsert.stream()
                    .map(User::getOfficialNumber)
                    .collect(Collectors.toList());
            List<User> importedUsers = userMapper.selectUserIdByOfficialNumbers(importedOfficialNumbers);
            for (User importedUser : importedUsers) {
                enrollmentStudentIds.add(importedUser.getId());
            }
        }

        if (!studentsToInsert.isEmpty()) {
            classStudentMapper.batchInsert(studentsToInsert);
            classMapper.increaseStuNumber(classId, studentsToInsert.size());
        }

        for (Long studentId : enrollmentStudentIds) {
            QueryWrapper<Enrollment> enrollmentWrapper = new QueryWrapper<>();
            enrollmentWrapper.eq("course_id", cls.getCourseId())
                    .eq("student_id", studentId);
            if (!courseStudentMapper.exists(enrollmentWrapper)) {
                Enrollment enrollment = new Enrollment();
                enrollment.setCourseId(cls.getCourseId());
                enrollment.setStudentId(studentId);
                courseStudentMapper.insert(enrollment);
            }
        }

        return result;
    }

    @SuppressWarnings("unchecked")
    private Map<Integer, Map<String, Object>> registerUsersInPortal(List<StudentImportRow> rows, String accessToken) {
        String endpoint = portalBaseUrl + portalBatchRegisterPath;
        if (accessToken == null || accessToken.trim().isEmpty()) {
            throw new MyException(401, "缺少访问令牌");
        }
        List<Map<String, Object>> users = new ArrayList<>();
        for (StudentImportRow row : rows) {
            Map<String, Object> user = new HashMap<>();
            user.put("rowIndex", row.getRowIndex());
            user.put("username", row.getOfficialNumber());
            user.put("name", row.getStuName());
            user.put("phone", row.getPhone());
            user.put("role", "STUDENT");
            users.add(user);
        }
        Map<String, Object> request = new HashMap<>();
        request.put("users", users);

        Map<String, Object> response;
        try {
            response = webClientUtil.postWithToken(endpoint, request, Map.class, accessToken);
        } catch (Exception e) {
            throw new MyException(502, "调用Portal批量注册失败：" + e.getMessage());
        }

        if (response == null || !response.containsKey("code")) {
            throw new MyException(502, "Portal批量注册返回格式异常");
        }
        Number code = (Number) response.get("code");
        if (code == null || code.intValue() != 0) {
            throw new MyException(502, "Portal批量注册失败：" + String.valueOf(response.get("message")));
        }

        Object dataObj = response.get("data");
        if (!(dataObj instanceof Map)) {
            throw new MyException(502, "Portal批量注册返回数据为空");
        }
        Map<String, Object> data = (Map<String, Object>) dataObj;
        Object detailsObj = data.get("details");
        if (!(detailsObj instanceof List)) {
            throw new MyException(502, "Portal批量注册结果缺少details字段");
        }

        Map<Integer, Map<String, Object>> resultByRow = new HashMap<>();
        for (Object detailObj : (List<?>) detailsObj) {
            if (!(detailObj instanceof Map)) {
                continue;
            }
            Map<String, Object> detail = (Map<String, Object>) detailObj;
            Number rowIndex = (Number) detail.get("rowIndex");
            if (rowIndex != null) {
                resultByRow.put(rowIndex.intValue(), detail);
            }
        }
        return resultByRow;
    }

    private String validateRow(StudentImportRow row, Set<String> officialNumberSet, Set<String> phoneSet) {
        if (row.getStuName() == null || row.getStuName().isEmpty()
                || row.getOfficialNumber() == null || row.getOfficialNumber().isEmpty()
                || row.getPhone() == null || row.getPhone().isEmpty()) {
            return "必填字段缺失：学号/姓名/手机号不能为空";
        }
        if (!Pattern.matches("^\\d{11}$", row.getPhone())) {
            return "手机号格式错误，必须为11位数字";
        }
        if (officialNumberSet.contains(row.getOfficialNumber())) {
            return "导入文件中学号重复";
        }
        if (phoneSet.contains(row.getPhone())) {
            return "导入文件中手机号重复";
        }
        return null;
    }

    private String generateDefaultPassword(String officialNumber) {
        if (officialNumber == null || officialNumber.length() < 6) {
            throw new MyException(400, "学号长度不足6位，无法生成默认密码");
        }
        String suffix = officialNumber.substring(officialNumber.length() - 6);
        if (!Pattern.matches("^\\d{6}$", suffix)) {
            throw new MyException(400, "学号后6位必须是数字");
        }
        return "Stu" + suffix;
    }

    @Override
    @Transactional
    public String addStudentByOfficialNumber(Long classId, String officialNumber) {
        // 1. 检查班级是否存在
        Class cls = classMapper.selectById(classId);
        if (cls == null) {
            throw new MyException(404, "班级不存在");
        }

        // 2. 根据学号查询用户
        QueryWrapper<User> userWrapper = new QueryWrapper<>();
        userWrapper.eq("official_number", officialNumber);
        User user = userMapper.selectOne(userWrapper);

        if (user == null) {
            throw new MyException(404, "未找到学号为 " + officialNumber + " 的学生");
        }

        // 3. 检查是否已在班级中
        QueryWrapper<ClassStudent> existWrapper = new QueryWrapper<>();
        existWrapper.eq("class_id", classId)
                .eq("official_number", officialNumber);

        if (classStudentMapper.selectCount(existWrapper) > 0) {
            throw new MyException(400, "该学生已在班级中");
        }

        // 4. 创建学生记录
        ClassStudent student = new ClassStudent();
        student.setClassId(classId);
        student.setOfficialNumber(officialNumber);
        student.setStuName(user.getName()); // 从用户表获取姓名
        student.setState(ClassStudentState.NOT_JOINED); // 默认状态

        // 5. 插入记录
        int insertResult = classStudentMapper.insert(student);
        if (insertResult <= 0) {
            throw new MyException(500, "添加学生失败");
        }

        // 6. 更新班级人数
        classMapper.increaseStuNumber(classId, 1);

        return "成功添加学生: " + user.getName() + " (" + officialNumber + ")";
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public String batchDeleteStudents(Long classId, List<String> officialNumbers) {
        // 1. 检查班级是否存在
        Class cls = classMapper.selectById(classId);
        if (cls == null) {
            throw new MyException(404, "班级不存在");
        }

        // 2. 验证输入
        if (officialNumbers == null || officialNumbers.isEmpty()) {
            throw new MyException(400, "学号列表不能为空");
        }

        // 3. 检查学生是否在班级中
        QueryWrapper<ClassStudent> wrapper = new QueryWrapper<>();
        wrapper.eq("class_id", classId)
                .in("official_number", officialNumbers);

        List<ClassStudent> students = classStudentMapper.selectList(wrapper);
        if (students.isEmpty()) {
            throw new MyException(404, "没有找到匹配的学生");
        }

        // 4. 获取实际存在的学号列表
        List<String> existingNumbers = students.stream()
                .map(ClassStudent::getOfficialNumber)
                .collect(Collectors.toList());

        // 5. 批量删除学生记录
        int deleteCount = classStudentMapper.batchDeleteByClassIdAndNumbers(classId, existingNumbers);
        if (deleteCount <= 0) {
            throw new MyException(500, "删除学生失败");
        }

        // 6. 更新班级人数
        int updateCount = classMapper.decreaseStuNumber(classId, deleteCount);
        if (updateCount <= 0) {
            throw new MyException(500, "更新班级人数失败");
        }

        // 7. 处理部分成功的情况
        List<String> notFoundNumbers = officialNumbers.stream()
                .filter(number -> !existingNumbers.contains(number))
                .collect(Collectors.toList());

        List<Long> studentIds = students.stream().map(student ->{
            User user = userMapper.selectByOfficialNumber(student.getOfficialNumber());
            return user.getId();
        }).toList();
        // 8. 删除课程，学生表中的记录
        Class aClass = classMapper.selectById(classId);
        courseStudentMapper.deleteByStudentIds(studentIds,aClass.getCourseId());
        String successMsg = "成功删除 " + deleteCount + " 名学生";
        if (!notFoundNumbers.isEmpty()) {
            String notFoundMsg = "，但以下学号未在班级中找到: " + String.join(", ", notFoundNumbers);
            successMsg += notFoundMsg;
        }

        return successMsg;
    }

    @Override
    public List<StudentInfoVO> getStudentsByClassId(Long classId) {
        return classStudentMapper.selectStudentsByClassId(classId);
    }

    @Override
    public Class getClassById(Long classId) {
        if (classId == null || classId <= 0) {
            log.warn("无效的 classId: {}", classId);
            throw new IllegalArgumentException("无效的班级ID");
        }
        Class courseClass = classMapper.selectById(classId);
        return courseClass;
    }

    @Override
    public Class getClassByStudentIdAndCourseId(Long studentId, Long courseId) {
        return classStudentMapper.findClassByStudentIdAndCourseId(studentId, courseId);
    }

    private List<StudentImportRow> parseExcelFile(MultipartFile file) throws IOException {
        List<StudentImportRow> rows = new ArrayList<>();
        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);

            Row headerRow = sheet.getRow(0);
            int nameCol = findColumnIndex(headerRow, "学生姓名", "姓名");
            int numberCol = findColumnIndex(headerRow, "学生学号", "学号");
            int phoneCol = findColumnIndex(headerRow, "手机号", "手机号码");

            if (nameCol == -1 || numberCol == -1 || phoneCol == -1) {
                throw new MyException(400, "文件缺少必要列：学生姓名 / 学生学号 / 手机号");
            }

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                StudentImportRow importRow = new StudentImportRow();
                importRow.setRowIndex(i + 1);
                importRow.setStuName(getCellStringValue(row.getCell(nameCol)));
                importRow.setOfficialNumber(getCellStringValue(row.getCell(numberCol)).replace(",", ""));
                importRow.setPhone(getCellStringValue(row.getCell(phoneCol)).replace(",", ""));
                rows.add(importRow);
            }
        }
        return rows;
    }

    private List<StudentImportRow> parseCsvFile(MultipartFile file)
            throws IOException, CsvValidationException {

        List<StudentImportRow> rows = new ArrayList<>();
        try (CSVReader csvReader = new CSVReader(new InputStreamReader(file.getInputStream()))) {
            String[] header = csvReader.readNext();
            if (header == null) {
                throw new MyException(400, "CSV文件为空");
            }

            int nameCol = findColumnIndex(header, "学生姓名", "姓名");
            int numberCol = findColumnIndex(header, "学生学号", "学号");
            int phoneCol = findColumnIndex(header, "手机号", "手机号码");

            if (nameCol == -1 || numberCol == -1 || phoneCol == -1) {
                throw new MyException(400, "文件缺少必要列：学生姓名 / 学生学号 / 手机号");
            }

            String[] nextRecord;
            int rowNum = 1;
            while ((nextRecord = csvReader.readNext()) != null) {
                rowNum++;
                int maxIndex = Math.max(Math.max(nameCol, numberCol), phoneCol);
                if (nextRecord.length < maxIndex + 1) {
                    StudentImportRow importRow = new StudentImportRow();
                    importRow.setRowIndex(rowNum);
                    importRow.setStuName("");
                    importRow.setOfficialNumber("");
                    importRow.setPhone("");
                    rows.add(importRow);
                    continue;
                }

                StudentImportRow importRow = new StudentImportRow();
                importRow.setRowIndex(rowNum);
                importRow.setStuName(nextRecord[nameCol] == null ? "" : nextRecord[nameCol].trim());
                importRow.setOfficialNumber(nextRecord[numberCol] == null ? "" : nextRecord[numberCol].trim().replace(",", ""));
                importRow.setPhone(nextRecord[phoneCol] == null ? "" : nextRecord[phoneCol].trim().replace(",", ""));
                rows.add(importRow);
            }
        }
        return rows;
    }

    private int findColumnIndex(Row headerRow, String... columnNames) {
        if (headerRow == null) {
            return -1;
        }
        for (Cell cell : headerRow) {
            String value = getCellStringValue(cell);
            for (String columnName : columnNames) {
                if (value.equalsIgnoreCase(columnName)) {
                    return cell.getColumnIndex();
                }
            }
        }
        return -1;
    }

    private int findColumnIndex(String[] header, String... columnNames) {
        for (int i = 0; i < header.length; i++) {
            String value = header[i] == null ? "" : header[i].trim();
            for (String columnName : columnNames) {
                if (value.equalsIgnoreCase(columnName)) {
                    return i;
                }
            }
        }
        return -1;
    }

    private String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        DataFormatter formatter = new DataFormatter();
        String cellValue = formatter.formatCellValue(cell).trim();
        if (cellValue.contains("E") && cell.getCellType() == CellType.NUMERIC) {
            BigDecimal bigDecimal = BigDecimal.valueOf(cell.getNumericCellValue());
            cellValue = bigDecimal.toPlainString();
        }
        return cellValue;
    }

    private static class StudentImportRow {
        private Integer rowIndex;
        private String officialNumber;
        private String stuName;
        private String phone;

        public Integer getRowIndex() {
            return rowIndex;
        }

        public void setRowIndex(Integer rowIndex) {
            this.rowIndex = rowIndex;
        }

        public String getOfficialNumber() {
            return officialNumber;
        }

        public void setOfficialNumber(String officialNumber) {
            this.officialNumber = officialNumber;
        }

        public String getStuName() {
            return stuName;
        }

        public void setStuName(String stuName) {
            this.stuName = stuName;
        }

        public String getPhone() {
            return phone;
        }

        public void setPhone(String phone) {
            this.phone = phone;
        }
    }


}