import mysql.connector
from mysql.connector import Error

# 数据库配置信息
config = {
    'user': 'root',
    'password': '123456',
    'host': 'localhost',
    'database': 'drivingschool',
    'raise_on_warnings': True
}
# 连接数据库
def connect_to_db(config):
    try:
        connection = mysql.connector.connect(**config)
        return connection
    except Error as e:
        print(f"The error '{e}' occurred")

# 断开数据库连接
def close_db_connection(connection):
    if connection.is_connected():
        connection.close()

# 添加新学生
def add_new_student(connection, student_id, name, password, phone, email):
    cursor = connection.cursor()
    query = """
    CALL AddNewStudent(%s, %s, %s, %s, %s)
    """
    cursor.execute(query, (student_id, name, password, phone, email))
    connection.commit()
    cursor.close()

# 更新预约状态
def update_appointment_status(connection, appointment_id, new_status):
    cursor = connection.cursor()
    query = """
    CALL UpdateAppointmentStatus(%s, %s)
    """
    cursor.execute(query, (appointment_id, new_status))
    connection.commit()
    cursor.close()

# 查询学生详细信息
def get_student_details(connection, student_id):
    cursor = connection.cursor()
    query = """
    SELECT * FROM student_details WHERE student_id = %s
    """
    cursor.execute(query, (student_id,))
    result = cursor.fetchone()
    cursor.close()
    return result

# 查询教练详细信息
def get_coach_details(connection, coach_id):
    cursor = connection.cursor()
    query = """
    SELECT * FROM coach_details WHERE coach_id = %s
    """
    cursor.execute(query, (coach_id,))
    result = cursor.fetchone()
    cursor.close()
    return result

# 查询预约详细信息
def get_appointment_details(connection, appointment_id):
    cursor = connection.cursor()
    query = """
    SELECT * FROM appointment_details WHERE appointment_id = %s
    """
    cursor.execute(query, (appointment_id,))
    result = cursor.fetchone()
    cursor.close()
    return result

# 查询训练记录详细信息
def get_training_record_details(connection, record_id):
    cursor = connection.cursor()
    query = """
    SELECT * FROM training_record_view WHERE record_id = %s
    """
    cursor.execute(query, (record_id,))
    result = cursor.fetchone()
    cursor.close()
    return result

# 查询发票详细信息
def get_invoice_details(connection, invoice_id):
    cursor = connection.cursor()
    query = """
    SELECT * FROM invoice_details WHERE invoice_id = %s
    """
    cursor.execute(query, (invoice_id,))
    result = cursor.fetchone()
    cursor.close()
    return result

# 查询所有学生详细信息
def get_all_students(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM student_details
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

# 查询所有教练详细信息
def get_all_coaches(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM coach_details
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

# 查询所有预约详细信息
def get_all_appointments(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM appointment_details
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

# 查询所有训练记录详细信息
def get_all_training_records(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM training_record_view
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

# 查询所有发票详细信息
def get_all_invoices(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM invoice_details
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

# 查询所有反馈
def get_all_feedback(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM feedback
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

# 查询所有日志
def get_all_logs(connection):
    cursor = connection.cursor()
    query = """
    SELECT * FROM log
    """
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    return results

def delete_student(connection, student_id):
    """删除学生"""
    cursor = connection.cursor()
    query = """
    DELETE FROM students WHERE student_id = %s
    """
    try:
        cursor.execute(query, (student_id,))
        connection.commit()
        print("学生删除成功")
    except Error as e:
        print(f"删除学生时出错: {e}")
        connection.rollback()
    finally:
        cursor.close()
# 根据用户输入执行不同的操作
def update_student(connection, student_id, name=None, password=None, phone=None, email=None):
    """更新学生信息"""
    cursor = connection.cursor()
    updates = []
    params = [student_id]

    if name is not None:
        updates.append("name=%s")
        params.insert(0, name)
    if password is not None:
        updates.append("password=%s")
        params.insert(1, password)
    if phone is not None:
        updates.append("phone=%s")
        params.insert(2, phone)
    if email is not None:
        updates.append("email=%s")
        params.insert(3, email)

    if updates:
        query = f"UPDATE students SET {', '.join(updates)} WHERE student_id=%s"
        try:
            cursor.execute(query, tuple(params))
            connection.commit()
            print("学生信息更新成功")
        except Error as e:
            print(f"更新学生信息时出错: {e}")
            connection.rollback()
        finally:
            cursor.close()
    else:
        print("没有提供要更新的信息")
def main():
    connection = connect_to_db(config)
    if connection is not None:
        while True:
            print("\n1. 添加新学生")
            print("2. 更新预约状态")
            print("3. 查询学生详细信息")
            print("4. 查询教练详细信息")
            print("5. 查询预约详细信息")
            print("6. 查询训练记录详细信息")
            print("7. 查询发票详细信息")
            print("8. 查询所有学生详细信息")
            print("9. 查询所有教练详细信息")
            print("10. 查询所有预约详细信息")
            print("11. 查询所有训练记录详细信息")
            print("12. 查询所有发票详细信息")
            print("13. 查询所有反馈")
            print("14. 查询所有日志")
            print("15. 删除学生")
            print("16. 更新学生信息")
            print("17. 退出")
            choice = input("请选择一个操作：")

            if choice == '1':
                student_id = input("输入学生ID：")
                name = input("输入学生姓名：")
                password = input("输入学生密码：")
                phone = input("输入学生电话：")
                email = input("输入学生邮箱：")
                add_new_student(connection, student_id, name, password, phone, email)
                print("新学生添加成功。")
            elif choice == '2':
                appointment_id = int(input("输入预约ID："))
                new_status = input("输入新的预约状态：")
                update_appointment_status(connection, appointment_id, new_status)
                print("预约状态更新成功。")
            elif choice == '3':
                student_id = input("输入学生ID：")
                details = get_student_details(connection, student_id)
                print("学生详细信息：", details)
            elif choice == '4':
                coach_id = input("输入教练ID：")
                details = get_coach_details(connection, coach_id)
                print("教练详细信息：", details)
            elif choice == '5':
                appointment_id = int(input("输入预约ID："))
                details = get_appointment_details(connection, appointment_id)
                print("预约详细信息：", details)
            elif choice == '6':
                record_id = int(input("输入训练记录ID："))
                details = get_training_record_details(connection, record_id)
                print("训练记录详细信息：", details)
            elif choice == '7':
                invoice_id = int(input("输入发票ID："))
                details = get_invoice_details(connection, invoice_id)
                print("发票详细信息：", details)
            elif choice == '8':
                all_students = get_all_students(connection)
                print("所有学生详细信息：")
                for student in all_students:
                    print(student)
            elif choice == '9':
                all_coaches = get_all_coaches(connection)
                print("所有教练详细信息：")
                for coach in all_coaches:
                    print(coach)
            elif choice == '10':
                all_appointments = get_all_appointments(connection)
                print("所有预约详细信息：")
                for appointment in all_appointments:
                    print(appointment)
            elif choice == '11':
                all_training_records = get_all_training_records(connection)
                print("所有训练记录详细信息：")
                for record in all_training_records:
                    print(record)
            elif choice == '12':
                all_invoices = get_all_invoices(connection)
                print("所有发票详细信息：")
                for invoice in all_invoices:
                    print(invoice)
            elif choice == '13':
                all_feedback = get_all_feedback(connection)
                print("所有反馈：")
                for feedback in all_feedback:
                    print(feedback)
            elif choice == '14':
                all_logs = get_all_logs(connection)
                print("所有日志：")
                for log in all_logs:
                    print(log)
            elif choice == '15':
                student_id = input("输入要删除的学生ID：")
                delete_student(connection, student_id)
            elif choice == '16':
                student_id = input("输入要更新的学生ID：")
                name = input("输入新的学生姓名 (留空则不更改): ") or None
                password = input("输入新的学生密码 (留空则不更改): ") or None
                phone = input("输入新的学生电话 (留空则不更改): ") or None
                email = input("输入新的学生邮箱 (留空则不更改): ") or None
                update_student(connection, student_id, name, password, phone, email)
            elif choice == '17':
                break
            else:
                print("无效的选择，请重新输入。")

            close_db_connection(connection)

        close_db_connection(connection)

if __name__ == "__main__":
    main()