"""学生名单导入。"""

from __future__ import annotations

from pathlib import Path

from group_system.security import hash_password
from group_system.xlsx_io import read_xlsx_rows


class RosterStoreMixin:

    def import_roster(self, path: Path) -> tuple[int, int]:
        rows = read_xlsx_rows(path)
        students = []
        errors = []
        seen = set()
        for index, row in enumerate(rows, 2):
            student_no = str(row.get("学号", "")).strip()
            name = str(row.get("姓名", "")).strip()
            class_name = str(
                row.get("班级", row.get("班别", row.get("行政班", "")))
            ).strip()
            gender = str(row.get("性别", "")).strip()
            admission_no = str(row.get("录取编号", "")).strip()
            remark = str(row.get("备注", "")).strip()
            if not student_no or not name or not class_name:
                errors.append(f"第 {index} 行：学号、姓名、班级均不能为空")
                continue
            if student_no in seen:
                errors.append(f"第 {index} 行：学号 {student_no} 重复")
                continue
            seen.add(student_no)
            students.append(
                (class_name, student_no, name, gender, admission_no, remark)
            )
        if errors:
            raise ValueError("；".join(errors[:8]))
        if not students:
            raise ValueError("Excel 中没有可导入的学生")

        with self.lock, self.connection:
            self.connection.executescript(
                "DELETE FROM sessions; DELETE FROM group_members; DELETE FROM groups; DELETE FROM students;"
            )
            self.connection.executemany(
                """INSERT INTO students
                (class_name,student_no,name,gender,admission_no,remark,password_hash,must_change_password)
                VALUES(?,?,?,?,?,?,?,1)""",
                [
                    (
                        item[0],
                        item[1],
                        item[2],
                        item[3],
                        item[4],
                        item[5],
                        hash_password(item[1]),
                    )
                    for item in students
                ],
            )
            active = self.get_setting("active_class", conn=self.connection) or students[0][0]
            if not any(item[0] == active for item in students):
                active = students[0][0]
                self.connection.execute(
                    "UPDATE settings SET value=? WHERE key='active_class'", (active,)
                )
        return len(students), len({item[0] for item in students})