package onework;

import java.sql.*;
import java.util.Map;

public class MySQL {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/studentsystem?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    private static Connection getConnection() throws SQLException, ClassNotFoundException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("正在连接数据库...");
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("数据库连接成功");
            return conn;
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            throw e;
        }
    }

    // 测试数据库连接
    public static boolean testConnection() {
        try (Connection conn = getConnection()) {
            return true;
        } catch (Exception e) {
            System.err.println("数据库连接测试失败：");
            e.printStackTrace();
            return false;
        }
    }

    // 保存班级数据
    public static void saveLessonData(Map<String, Lesson> lessonICO) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {

            System.out.println("正在保存班级数据，数量：" + lessonICO.size());

            stmt.execute("SET FOREIGN_KEY_CHECKS=0");
            stmt.execute("TRUNCATE TABLE class");

            for (Lesson lesson : lessonICO.values()) {
                String sql = String.format(
                        "INSERT INTO class (班级) VALUES ('%s')",
                        lesson.classname
                );
                stmt.execute(sql);
                System.out.println("已保存班级：" + lesson.classname);
            }

            stmt.execute("SET FOREIGN_KEY_CHECKS=1");
        } catch (Exception e) {
            System.err.println("保存班级数据时出错：");
            e.printStackTrace();
        }
    }

    // 保存小组数据
    public static void saveGroupData(Map<String, Group> groupICO) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {

            System.out.println("正在保存小组数据，数量：" + groupICO.size());

            stmt.execute("SET FOREIGN_KEY_CHECKS=0");
            stmt.execute("TRUNCATE TABLE `group`");

            for (Group group : groupICO.values()) {
                String sql = String.format(
                        "INSERT INTO `group` (小组, 成员1, 成员2, 分数) VALUES ('%s', '%s', '%s', '%s')",
                        group.number,
                        group.member1,
                        group.member2,
                        group.score
                );
                stmt.execute(sql);
                System.out.println("已保存小组：" + group.number);
            }

            stmt.execute("SET FOREIGN_KEY_CHECKS=1");
        } catch (Exception e) {
            System.err.println("保存小组数据时出错：");
            e.printStackTrace();
        }
    }

    // 保存学生数据
    public static void saveStudentData(Map<String, Student> studentICO) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {

            System.out.println("正在保存学生数据，数量：" + studentICO.size());

            stmt.execute("SET FOREIGN_KEY_CHECKS=0");
            stmt.execute("TRUNCATE TABLE student");

            for (Student student : studentICO.values()) {
                String sql = String.format(
                        "INSERT INTO student (学号, 姓名, 班级号, 小组号, 考勤, 答题情况, 成绩) " +
                                "VALUES (%s, '%s', %s, '%s', %s, '%s', %s)",
                        student.number,
                        student.name,
                        student.grade,
                        student.team,
                        student.attendance == null ? "0" : student.attendance,
                        student.reply == null ? "" : student.reply,
                        student.grades == null ? "0" : student.grades
                );
                stmt.execute(sql);
                System.out.println("已保存学生：" + student.name);
            }

            stmt.execute("SET FOREIGN_KEY_CHECKS=1");
        } catch (Exception e) {
            System.err.println("保存学生数据时出错：");
            e.printStackTrace();
        }
    }

    // 加载班级数据
    public static void loadLessonData(Map<String, Lesson> lessonICO) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM class")) {

            lessonICO.clear();
            while (rs.next()) {
                Lesson lesson = new Lesson();
                lesson.classname = rs.getString("班级");
                lessonICO.put(lesson.classname, lesson);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 加载小组数据
    public static void loadGroupData(Map<String, Group> groupICO) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM `group`")) {

            groupICO.clear();
            while (rs.next()) {
                Group group = new Group();
                group.number = rs.getString("小组");
                group.member1 = rs.getString("成员1");
                group.member2 = rs.getString("成员2");
                group.score = rs.getString("分数");
                groupICO.put(group.number, group);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 加载学生数据
    public static void loadStudentData(Map<String, Student> studentICO) {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM student")) {

            studentICO.clear();
            while (rs.next()) {
                Student student = new Student();
                student.number = rs.getString("学号");
                student.name = rs.getString("姓名");
                student.grade = rs.getString("班级号");
                student.team = rs.getString("小组");
                student.attendance = rs.getString("考勤");
                student.reply = rs.getString("答题情况");
                studentICO.put(student.name, student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 加载所有数据
    public static void loadAllData() {
        loadLessonData(Manage.lessonICO);    // 先加载班级数据
        loadGroupData(Manage.groupICO);      // 再加载小组数据
        loadStudentData(Manage.studentICO);  // 最后加载学生数据
    }

    // 保存所有数据
    public static void saveAllData() {
        try {
            System.out.println("开始保存数据...");

            System.out.println("保存班级数据...");
            saveLessonData(Manage.lessonICO);

            System.out.println("保存小组数据...");
            saveGroupData(Manage.groupICO);

            System.out.println("保存学生数据...");
            saveStudentData(Manage.studentICO);

            System.out.println("所有数据保存完成");
        } catch (Exception e) {
            System.err.println("保存数据时出错：");
            e.printStackTrace();
        }
    }
}