import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import pymysql
import csv

# 全局变量
conn = None
username = None

# 数据库连接函数
def get_connection(user, password):
    try:
        connection = pymysql.connect(
            host="localhost",  # 数据库主机地址
            user='root',  # 数据库用户名
            password='lgs2018231',  # 数据库密码
            database="hotelmanagementsystem",  # 数据库名称
            port=3306,  # MySQL 的默认端口
            charset='utf8mb4'  # 确保支持中文和特殊字符
        )
        print("数据库连接成功！")
        return connection
    except pymysql.MySQLError as e:
        messagebox.showerror("连接失败", f"数据库连接失败: {e}")
        return None

# 登录验证
def authenticate_user(login_window, user_entry, password_entry):
    global conn, username
    username = user_entry.get()
    password = password_entry.get()
    conn = get_connection(username, password)
    if conn:
        messagebox.showinfo("登录成功", f"欢迎 {username}！")
        login_window.destroy()
        main_window()
    else:
        messagebox.showerror("登录失败", "用户名或密码错误！")
"""前台查询顾客信息"""
def query_customer_info():

    def submit_query():
        customer_name = name_entry.get().strip()

        try:
            cursor = conn.cursor()
            query = """
            SELECT * FROM Customer WHERE Name LIKE %s;
            """
            cursor.execute(query, (f"%{customer_name}%",))
            results = cursor.fetchall()
            display_results("顾客信息", ["CustomerID", "Name", "Gender", "Age", "ContactInfo", "Address", "IDCardNumber"], results)
        except pymysql.MySQLError as e:
            messagebox.showerror("查询失败", f"查询失败: {e}")

    # 查询窗口
    query_window = tk.Toplevel()
    query_window.title("查询顾客信息")

    tk.Label(query_window, text="顾客姓名:").grid(row=0, column=0)
    name_entry = tk.Entry(query_window)
    name_entry.grid(row=0, column=1)

    submit_button = tk.Button(query_window, text="查询", command=submit_query)
    submit_button.grid(row=1, column=0, columnspan=2)

# 查询顾客预订信息视图
def query_customer_reservation_view():
    try:
        cursor = conn.cursor()
        query = "SELECT * FROM CustomerReservationView;"
        cursor.execute(query)
        results = cursor.fetchall()
        # 显示查询结果
        display_results("顾客预订信息", ["CustomerID", "CustomerName", "ContactInfo", "RoomNumber", "RoomType", "CheckInDate", "CheckOutDate", "ReservationStatus"], results)
    except pymysql.MySQLError as e:
        messagebox.showerror("查询失败", f"查询失败: {e}")

# 查询入住详细信息视图
def query_stay_details_view():
    try:
        cursor = conn.cursor()
        query = "SELECT * FROM StayDetailsView;"
        cursor.execute(query)
        results = cursor.fetchall()
        # 显示查询结果
        display_results("入住详细信息", ["StayID", "CustomerName", "RoomNumber", "RoomType", "PaymentAmount", "PaymentMethod", "AdditionalServices"], results)
    except pymysql.MySQLError as e:
        messagebox.showerror("查询失败", f"查询失败: {e}")
# 查询房间状态视图
def query_room_status_view():
    try:
        # 执行查询房间状态视图
        cursor = conn.cursor()
        query = "SELECT * FROM RoomStatusView;"  # 假设视图名为 RoomStatusView
        cursor.execute(query)
        results = cursor.fetchall()

        # 显示查询结果
        display_results(
            "房间状态",
            ["RoomID", "RoomNumber", "RoomType", "RoomStatus"],
            results
        )
    except pymysql.MySQLError as e:
        messagebox.showerror("查询失败", f"查询失败: {e}")

# 修改房间状态
def update_room_status():
    def submit_room_status():
        room_number = room_number_entry.get()
        new_status = room_status_entry.get()
        try:
            cursor = conn.cursor()
            query = "CALL UpdateRoomStatus(%s, %s);"
            cursor.execute(query, (room_number, new_status))
            conn.commit()
            messagebox.showinfo("成功", f"房间 {room_number} 状态已更新为 {new_status}。")
            room_window.destroy()
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"修改失败: {e}")

    # 修改房间状态窗口
    room_window = tk.Toplevel()
    room_window.title("修改房间状态")

    tk.Label(room_window, text="房间号:").grid(row=0, column=0)
    room_number_entry = tk.Entry(room_window)
    room_number_entry.grid(row=0, column=1)

    tk.Label(room_window, text="新状态 (Available/Occupied):").grid(row=1, column=0)
    room_status_entry = tk.Entry(room_window)
    room_status_entry.grid(row=1, column=1)

    submit_button = tk.Button(room_window, text="提交", command=submit_room_status)
    submit_button.grid(row=2, column=0, columnspan=2)

# 更新预订状态
def update_reservation_status():
    def submit_reservation_status():
        reservation_id = reservation_id_entry.get()
        new_status = reservation_status_entry.get()
        try:
            cursor = conn.cursor()
            query = "UPDATE Reservation SET Status = %s WHERE ReservationID = %s;"
            cursor.execute(query, (new_status, reservation_id))
            conn.commit()
            messagebox.showinfo("成功", f"预订编号 {reservation_id} 状态已更新为 {new_status}。")
            reservation_window.destroy()
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"修改失败: {e}")

    # 更新预订状态窗口
    reservation_window = tk.Toplevel()
    reservation_window.title("更新预订状态")

    tk.Label(reservation_window, text="预订编号:").grid(row=0, column=0)
    reservation_id_entry = tk.Entry(reservation_window)
    reservation_id_entry.grid(row=0, column=1)

    tk.Label(reservation_window, text="新状态 (Confirmed/Pending/CheckedIn):").grid(row=1, column=0)
    reservation_status_entry = tk.Entry(reservation_window)
    reservation_status_entry.grid(row=1, column=1)

    submit_button = tk.Button(reservation_window, text="提交", command=submit_reservation_status)
    submit_button.grid(row=2, column=0, columnspan=2)

    # 新增员工
def add_employee():
    def submit_employee():
        name = name_entry.get()
        gender = gender_entry.get()
        age = int(age_entry.get())
        contact_info = contact_entry.get()
        position = position_entry.get()
        hire_date = hire_date_entry.get()
        salary = float(salary_entry.get())

        try:
            cursor = conn.cursor()
            query = """
                    INSERT INTO Employee (Name, Gender, Age, ContactInfo, Position, HireDate, Salary)
                    VALUES (%s, %s, %s, %s, %s, %s, %s);
                    """
            cursor.execute(query, (name, gender, age, contact_info, position, hire_date, salary))
            conn.commit()
            messagebox.showinfo("成功", f"员工 {name} 已添加。")
            employee_window.destroy()
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"添加失败: {e}")

        # 新增员工窗口
    employee_window = tk.Toplevel()
    employee_window.title("新增员工")

    tk.Label(employee_window, text="姓名:").grid(row=0, column=0)
    name_entry = tk.Entry(employee_window)
    name_entry.grid(row=0, column=1)

    tk.Label(employee_window, text="性别 (Male/Female):").grid(row=1, column=0)
    gender_entry = tk.Entry(employee_window)
    gender_entry.grid(row=1, column=1)

    tk.Label(employee_window, text="年龄:").grid(row=2, column=0)
    age_entry = tk.Entry(employee_window)
    age_entry.grid(row=2, column=1)

    tk.Label(employee_window, text="联系方式:").grid(row=3, column=0)
    contact_entry = tk.Entry(employee_window)
    contact_entry.grid(row=3, column=1)

    tk.Label(employee_window, text="职位:").grid(row=4, column=0)
    position_entry = tk.Entry(employee_window)
    position_entry.grid(row=4, column=1)

    tk.Label(employee_window, text="入职日期 (YYYY-MM-DD):").grid(row=5, column=0)
    hire_date_entry = tk.Entry(employee_window)
    hire_date_entry.grid(row=5, column=1)

    tk.Label(employee_window, text="工资:").grid(row=6, column=0)
    salary_entry = tk.Entry(employee_window)
    salary_entry.grid(row=6, column=1)

    submit_button = tk.Button(employee_window, text="提交", command=submit_employee)
    submit_button.grid(row=7, column=0, columnspan=2)


# 删除员工
def delete_employee():
    def submit_delete_employee():
        employee_id = employee_id_entry.get()

        try:
            cursor = conn.cursor()
            query = "DELETE FROM Employee WHERE EmployeeID = %s;"
            cursor.execute(query, (employee_id,))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"员工编号 {employee_id} 已删除。")
            else:
                messagebox.showwarning("失败", f"员工编号 {employee_id} 不存在。")
            delete_window.destroy()
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"删除失败: {e}")

    # 删除员工窗口
    delete_window = tk.Toplevel()
    delete_window.title("删除员工")

    tk.Label(delete_window, text="员工编号:").grid(row=0, column=0)
    employee_id_entry = tk.Entry(delete_window)
    employee_id_entry.grid(row=0, column=1)

    submit_button = tk.Button(delete_window, text="提交", command=submit_delete_employee)
    submit_button.grid(row=1, column=0, columnspan=2)


# 修改员工排班信息
def update_employee_shift():
    def submit_shift_update():
        shift_id = shift_id_entry.get()
        employee_id = employee_id_entry.get()
        shift_date = shift_date_entry.get()
        shift_start_time = shift_start_time_entry.get()
        shift_end_time = shift_end_time_entry.get()

        try:
            cursor = conn.cursor()
            query = """
            UPDATE Shift
            SET EmployeeID = %s, ShiftDate = %s, ShiftStartTime = %s, ShiftEndTime = %s
            WHERE ShiftID = %s;
            """
            cursor.execute(query, (employee_id, shift_date, shift_start_time, shift_end_time, shift_id))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"排班编号 {shift_id} 的信息已更新。")
            else:
                messagebox.showwarning("失败", f"排班编号 {shift_id} 不存在。")
            shift_window.destroy()
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"更新失败: {e}")

    # 修改排班窗口
    shift_window = tk.Toplevel()
    shift_window.title("修改员工排班信息")

    tk.Label(shift_window, text="排班编号 (ShiftID):").grid(row=0, column=0)
    shift_id_entry = tk.Entry(shift_window)
    shift_id_entry.grid(row=0, column=1)

    tk.Label(shift_window, text="员工编号 (EmployeeID):").grid(row=1, column=0)
    employee_id_entry = tk.Entry(shift_window)
    employee_id_entry.grid(row=1, column=1)

    tk.Label(shift_window, text="排班日期 (YYYY-MM-DD):").grid(row=2, column=0)
    shift_date_entry = tk.Entry(shift_window)
    shift_date_entry.grid(row=2, column=1)

    tk.Label(shift_window, text="开始时间 (HH:MM:SS):").grid(row=3, column=0)
    shift_start_time_entry = tk.Entry(shift_window)
    shift_start_time_entry.grid(row=3, column=1)

    tk.Label(shift_window, text="结束时间 (HH:MM:SS):").grid(row=4, column=0)
    shift_end_time_entry = tk.Entry(shift_window)
    shift_end_time_entry.grid(row=4, column=1)

    submit_button = tk.Button(shift_window, text="提交", command=submit_shift_update)
    submit_button.grid(row=5, column=0, columnspan=2)
# 修改房型信息
def update_room_type():
    def submit_room_type_update():
        room_type_id = room_type_id_entry.get()
        new_name = type_name_entry.get()
        new_description = description_entry.get()
        new_base_price = base_price_entry.get()

        try:
            cursor = conn.cursor()
            query = """
            UPDATE RoomType
            SET TypeName = %s, Description = %s, BasePrice = %s
            WHERE RoomTypeID = %s;
            """
            cursor.execute(query, (new_name, new_description, new_base_price, room_type_id))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"房型编号 {room_type_id} 的信息已更新。")
            else:
                messagebox.showwarning("失败", f"房型编号 {room_type_id} 不存在。")
            room_type_window.destroy()
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"更新失败: {e}")

    # 修改房型信息窗口
    room_type_window = tk.Toplevel()
    room_type_window.title("修改房型信息")

    tk.Label(room_type_window, text="房型编号 (RoomTypeID):").grid(row=0, column=0)
    room_type_id_entry = tk.Entry(room_type_window)
    room_type_id_entry.grid(row=0, column=1)

    tk.Label(room_type_window, text="房型名称 (TypeName):").grid(row=1, column=0)
    type_name_entry = tk.Entry(room_type_window)
    type_name_entry.grid(row=1, column=1)

    tk.Label(room_type_window, text="房型描述 (Description):").grid(row=2, column=0)
    description_entry = tk.Entry(room_type_window)
    description_entry.grid(row=2, column=1)

    tk.Label(room_type_window, text="房型价格 (BasePrice):").grid(row=3, column=0)
    base_price_entry = tk.Entry(room_type_window)
    base_price_entry.grid(row=3, column=1)

    submit_button = tk.Button(room_type_window, text="提交", command=submit_room_type_update)
    submit_button.grid(row=4, column=0, columnspan=2)



# 修改顾客信息（新增、修改、删除）
def manage_customer_info():
    def add_customer():
        """新增顾客信息"""
        name = name_entry.get()
        gender = gender_entry.get()
        age = int(age_entry.get())
        contact_info = contact_entry.get()
        address = address_entry.get()
        id_card_number = id_entry.get()

        try:
            cursor = conn.cursor()
            query = """
            INSERT INTO Customer (Name, Gender, Age, ContactInfo, Address, IDCardNumber)
            VALUES (%s, %s, %s, %s, %s, %s);
            """
            cursor.execute(query, (name, gender, age, contact_info, address, id_card_number))
            conn.commit()
            messagebox.showinfo("成功", f"顾客 {name} 信息已新增。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"新增失败: {e}")

    def update_customer():
        """修改顾客信息"""
        customer_id = customer_id_entry.get()
        name = name_entry.get()
        gender = gender_entry.get()
        age = int(age_entry.get())
        contact_info = contact_entry.get()
        address = address_entry.get()
        id_card_number = id_entry.get()

        try:
            cursor = conn.cursor()
            query = """
            UPDATE Customer
            SET Name = %s, Gender = %s, Age = %s, ContactInfo = %s, Address = %s, IDCardNumber = %s
            WHERE CustomerID = %s;
            """
            cursor.execute(query, (name, gender, age, contact_info, address, id_card_number, customer_id))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"顾客编号 {customer_id} 的信息已更新。")
            else:
                messagebox.showwarning("失败", f"顾客编号 {customer_id} 不存在。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"更新失败: {e}")

    def delete_customer():
        """删除顾客信息"""
        customer_id = customer_id_entry.get()

        try:
            cursor = conn.cursor()
            query = "DELETE FROM Customer WHERE CustomerID = %s;"
            cursor.execute(query, (customer_id,))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"顾客编号 {customer_id} 已删除。")
            else:
                messagebox.showwarning("失败", f"顾客编号 {customer_id} 不存在。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"删除失败: {e}")

    # 管理顾客信息窗口
    customer_window = tk.Toplevel()
    customer_window.title("管理顾客信息")

    # 顾客编号（仅用于修改和删除）
    tk.Label(customer_window, text="顾客编号 (CustomerID):").grid(row=0, column=0)
    customer_id_entry = tk.Entry(customer_window)
    customer_id_entry.grid(row=0, column=1)

    # 通用字段
    tk.Label(customer_window, text="姓名 (Name):").grid(row=1, column=0)
    name_entry = tk.Entry(customer_window)
    name_entry.grid(row=1, column=1)

    tk.Label(customer_window, text="性别 (Gender: Male/Female):").grid(row=2, column=0)
    gender_entry = tk.Entry(customer_window)
    gender_entry.grid(row=2, column=1)

    tk.Label(customer_window, text="年龄 (Age):").grid(row=3, column=0)
    age_entry = tk.Entry(customer_window)
    age_entry.grid(row=3, column=1)

    tk.Label(customer_window, text="联系方式 (ContactInfo):").grid(row=4, column=0)
    contact_entry = tk.Entry(customer_window)
    contact_entry.grid(row=4, column=1)

    tk.Label(customer_window, text="地址 (Address):").grid(row=5, column=0)
    address_entry = tk.Entry(customer_window)
    address_entry.grid(row=5, column=1)

    tk.Label(customer_window, text="身份证号 (IDCardNumber):").grid(row=6, column=0)
    id_entry = tk.Entry(customer_window)
    id_entry.grid(row=6, column=1)

    # 操作按钮
    tk.Button(customer_window, text="新增顾客", command=add_customer).grid(row=7, column=0, pady=5)
    tk.Button(customer_window, text="修改顾客", command=update_customer).grid(row=7, column=1, pady=5)
    tk.Button(customer_window, text="删除顾客", command=delete_customer).grid(row=8, column=0, columnspan=2, pady=5)


# 管理服务信息（新增、修改、删除）
def manage_service_info():
    def add_service():
        """新增服务信息"""
        service_name = service_name_entry.get().strip()
        description = service_description_entry.get().strip()
        price = service_price_entry.get().strip()

        # 验证输入
        if not service_name:
            messagebox.showerror("错误", "服务名称不能为空！")
            return
        if not price.replace('.', '', 1).isdigit() or float(price) < 0:
            messagebox.showerror("错误", "价格必须是非负数！")
            return

        price = float(price)

        # 插入服务信息到数据库
        try:
            cursor = conn.cursor()
            query = """
            INSERT INTO Service (ServiceName, Description, Price)
            VALUES (%s, %s, %s);
            """
            cursor.execute(query, (service_name, description, price))
            conn.commit()
            messagebox.showinfo("成功", f"服务 {service_name} 已新增。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"新增失败: {e}")

    def update_service():
        """修改服务信息"""
        service_id = service_id_entry.get().strip()
        service_name = service_name_entry.get().strip()
        description = service_description_entry.get().strip()
        price = service_price_entry.get().strip()

        # 验证输入
        if not service_id.isdigit():
            messagebox.showerror("错误", "服务编号必须是数字！")
            return
        if not service_name:
            messagebox.showerror("错误", "服务名称不能为空！")
            return
        if not price.replace('.', '', 1).isdigit() or float(price) < 0:
            messagebox.showerror("错误", "价格必须是非负数！")
            return

        service_id = int(service_id)
        price = float(price)

        # 更新服务信息到数据库
        try:
            cursor = conn.cursor()
            query = """
            UPDATE Service
            SET ServiceName = %s, Description = %s, Price = %s
            WHERE ServiceID = %s;
            """
            cursor.execute(query, (service_name, description, price, service_id))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"服务编号 {service_id} 的信息已更新。")
            else:
                messagebox.showwarning("失败", f"服务编号 {service_id} 不存在。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"更新失败: {e}")

    def delete_service():
        """删除服务信息"""
        service_id = service_id_entry.get().strip()

        # 验证输入
        if not service_id.isdigit():
            messagebox.showerror("错误", "服务编号必须是数字！")
            return

        service_id = int(service_id)

        # 删除服务信息
        try:
            cursor = conn.cursor()
            query = "DELETE FROM Service WHERE ServiceID = %s;"
            cursor.execute(query, (service_id,))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"服务编号 {service_id} 已删除。")
            else:
                messagebox.showwarning("失败", f"服务编号 {service_id} 不存在。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"删除失败: {e}")

    # 创建管理服务信息的窗口
    service_window = tk.Toplevel()
    service_window.title("管理服务信息")

    # 界面布局
    tk.Label(service_window, text="服务编号 (ServiceID):").grid(row=0, column=0)
    service_id_entry = tk.Entry(service_window)
    service_id_entry.grid(row=0, column=1)

    tk.Label(service_window, text="服务名称 (ServiceName):").grid(row=1, column=0)
    service_name_entry = tk.Entry(service_window)
    service_name_entry.grid(row=1, column=1)

    tk.Label(service_window, text="服务描述 (Description):").grid(row=2, column=0)
    service_description_entry = tk.Entry(service_window)
    service_description_entry.grid(row=2, column=1)

    tk.Label(service_window, text="服务价格 (Price):").grid(row=3, column=0)
    service_price_entry = tk.Entry(service_window)
    service_price_entry.grid(row=3, column=1)

    # 按钮
    tk.Button(service_window, text="新增服务", command=add_service).grid(row=4, column=0, pady=5)
    tk.Button(service_window, text="修改服务", command=update_service).grid(row=4, column=1, pady=5)
    tk.Button(service_window, text="删除服务", command=delete_service).grid(row=5, column=0, columnspan=2, pady=5)

# 管理员工绩效信息（新增、修改、删除）
def manage_employee_performance():
    def add_performance():
        """新增员工绩效记录"""
        employee_id = employee_id_entry.get().strip()
        evaluation_date = evaluation_date_entry.get().strip()
        score = score_entry.get().strip()
        comments = comments_entry.get().strip()

        # 验证输入
        if not employee_id.isdigit():
            messagebox.showerror("错误", "员工编号必须是数字！")
            return
        if not score.isdigit() or int(score) < 0 or int(score) > 100:
            messagebox.showerror("错误", "评分必须是 0 到 100 的整数！")
            return
        if not evaluation_date:
            messagebox.showerror("错误", "评估日期不能为空！")
            return

        employee_id = int(employee_id)
        score = int(score)

        # 插入绩效记录到数据库
        try:
            cursor = conn.cursor()
            query = """
            INSERT INTO Performance (EmployeeID, EvaluationDate, Score, Comments)
            VALUES (%s, %s, %s, %s);
            """
            cursor.execute(query, (employee_id, evaluation_date, score, comments))
            conn.commit()
            messagebox.showinfo("成功", f"员工编号 {employee_id} 的绩效记录已新增。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"新增失败: {e}")

    def update_performance():
        """修改员工绩效记录"""
        performance_id = performance_id_entry.get().strip()
        employee_id = employee_id_entry.get().strip()
        evaluation_date = evaluation_date_entry.get().strip()
        score = score_entry.get().strip()
        comments = comments_entry.get().strip()

        # 验证输入
        if not performance_id.isdigit():
            messagebox.showerror("错误", "绩效编号必须是数字！")
            return
        if not employee_id.isdigit():
            messagebox.showerror("错误", "员工编号必须是数字！")
            return
        if not score.isdigit() or int(score) < 0 or int(score) > 100:
            messagebox.showerror("错误", "评分必须是 0 到 100 的整数！")
            return
        if not evaluation_date:
            messagebox.showerror("错误", "评估日期不能为空！")
            return

        performance_id = int(performance_id)
        employee_id = int(employee_id)
        score = int(score)

        # 更新绩效记录到数据库
        try:
            cursor = conn.cursor()
            query = """
            UPDATE Performance
            SET EmployeeID = %s, EvaluationDate = %s, Score = %s, Comments = %s
            WHERE PerformanceID = %s;
            """
            cursor.execute(query, (employee_id, evaluation_date, score, comments, performance_id))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"绩效编号 {performance_id} 的信息已更新。")
            else:
                messagebox.showwarning("失败", f"绩效编号 {performance_id} 不存在。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"更新失败: {e}")

    def delete_performance():
        """删除员工绩效记录"""
        performance_id = performance_id_entry.get().strip()

        # 验证输入
        if not performance_id.isdigit():
            messagebox.showerror("错误", "绩效编号必须是数字！")
            return

        performance_id = int(performance_id)

        # 删除绩效记录
        try:
            cursor = conn.cursor()
            query = "DELETE FROM Performance WHERE PerformanceID = %s;"
            cursor.execute(query, (performance_id,))
            conn.commit()

            if cursor.rowcount > 0:
                messagebox.showinfo("成功", f"绩效编号 {performance_id} 已删除。")
            else:
                messagebox.showwarning("失败", f"绩效编号 {performance_id} 不存在。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"删除失败: {e}")

    # 创建管理绩效信息的窗口
    performance_window = tk.Toplevel()
    performance_window.title("管理员工绩效信息")

    # 界面布局
    tk.Label(performance_window, text="绩效编号 (PerformanceID):").grid(row=0, column=0)
    performance_id_entry = tk.Entry(performance_window)
    performance_id_entry.grid(row=0, column=1)

    tk.Label(performance_window, text="员工编号 (EmployeeID):").grid(row=1, column=0)
    employee_id_entry = tk.Entry(performance_window)
    employee_id_entry.grid(row=1, column=1)

    tk.Label(performance_window, text="评估日期 (YYYY-MM-DD):").grid(row=2, column=0)
    evaluation_date_entry = tk.Entry(performance_window)
    evaluation_date_entry.grid(row=2, column=1)

    tk.Label(performance_window, text="评分 (Score: 0-100):").grid(row=3, column=0)
    score_entry = tk.Entry(performance_window)
    score_entry.grid(row=3, column=1)

    tk.Label(performance_window, text="评语 (Comments):").grid(row=4, column=0)
    comments_entry = tk.Entry(performance_window)
    comments_entry.grid(row=4, column=1)

    # 按钮
    tk.Button(performance_window, text="新增绩效记录", command=add_performance).grid(row=5, column=0, pady=5)
    tk.Button(performance_window, text="修改绩效记录", command=update_performance).grid(row=5, column=1, pady=5)
    tk.Button(performance_window, text="删除绩效记录", command=delete_performance).grid(row=6, column=0, columnspan=2, pady=5)


def check_in_guest():
    """前台办理入住"""
    def submit_check_in():
        reservation_id = reservation_id_entry.get().strip()
        actual_check_in_date = check_in_date_entry.get().strip()

        try:
            cursor = conn.cursor()
            query = """
            UPDATE Reservation
            SET Status = 'CheckedIn', CheckInDate = %s
            WHERE ReservationID = %s;
            """
            cursor.execute(query, (actual_check_in_date, reservation_id))
            conn.commit()
            messagebox.showinfo("成功", f"预订编号 {reservation_id} 已办理入住。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"办理失败: {e}")

    # 办理入住窗口
    check_in_window = tk.Toplevel()
    check_in_window.title("办理入住")

    tk.Label(check_in_window, text="预订编号 (ReservationID):").grid(row=0, column=0)
    reservation_id_entry = tk.Entry(check_in_window)
    reservation_id_entry.grid(row=0, column=1)

    tk.Label(check_in_window, text="入住日期 (YYYY-MM-DD):").grid(row=1, column=0)
    check_in_date_entry = tk.Entry(check_in_window)
    check_in_date_entry.grid(row=1, column=1)

    submit_button = tk.Button(check_in_window, text="提交", command=submit_check_in)
    submit_button.grid(row=2, column=0, columnspan=2)



# 增加付款信息
def add_payment_info():
    def submit_payment():
        """提交付款信息到数据库"""
        stay_id = stay_id_entry.get().strip()
        amount = amount_entry.get().strip()
        payment_method = payment_method_entry.get().strip()
        payment_date = payment_date_entry.get().strip()

        # 验证输入
        if not stay_id.isdigit():
            messagebox.showerror("错误", "入住编号 (StayID) 必须是数字！")
            return
        if not amount.replace('.', '', 1).isdigit() or float(amount) <= 0:
            messagebox.showerror("错误", "付款金额必须是正数！")
            return
        if not payment_method:
            messagebox.showerror("错误", "付款方式不能为空！")
            return
        if not payment_date:
            messagebox.showerror("错误", "付款日期不能为空！")
            return

        stay_id = int(stay_id)
        amount = float(amount)

        # 插入付款信息到数据库
        try:
            cursor = conn.cursor()
            query = """
            INSERT INTO Payment (StayID, Amount, PaymentMethod, PaymentDate)
            VALUES (%s, %s, %s, %s);
            """
            cursor.execute(query, (stay_id, amount, payment_method, payment_date))
            conn.commit()
            messagebox.showinfo("成功", f"入住编号 {stay_id} 的付款信息已新增。")
        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"新增失败: {e}")

    # 创建付款信息窗口
    payment_window = tk.Toplevel()
    payment_window.title("新增付款信息")

    # 界面布局
    tk.Label(payment_window, text="入住编号 (StayID):").grid(row=0, column=0)
    stay_id_entry = tk.Entry(payment_window)
    stay_id_entry.grid(row=0, column=1)

    tk.Label(payment_window, text="付款金额 (Amount):").grid(row=1, column=0)
    amount_entry = tk.Entry(payment_window)
    amount_entry.grid(row=1, column=1)

    tk.Label(payment_window, text="付款方式 (PaymentMethod):").grid(row=2, column=0)
    payment_method_entry = tk.Entry(payment_window)
    payment_method_entry.grid(row=2, column=1)

    tk.Label(payment_window, text="付款日期 (YYYY-MM-DD):").grid(row=3, column=0)
    payment_date_entry = tk.Entry(payment_window)
    payment_date_entry.grid(row=3, column=1)

    # 提交按钮
    tk.Button(payment_window, text="提交付款信息", command=submit_payment).grid(row=4, column=0, columnspan=2, pady=10)


def query_employee_info():
    """查询员工信息"""
    def submit_query():
        employee_id = employee_id_entry.get().strip()
        name = name_entry.get().strip()
        position = position_entry.get().strip()

        # 构建查询条件
        query_conditions = []
        query_values = []

        if employee_id:
            if not employee_id.isdigit():
                messagebox.showerror("错误", "员工编号 (EmployeeID) 必须是数字！")
                return
            query_conditions.append("EmployeeID = %s")
            query_values.append(int(employee_id))

        if name:
            query_conditions.append("Name LIKE %s")
            query_values.append(f"%{name}%")

        if position:
            query_conditions.append("Position LIKE %s")
            query_values.append(f"%{position}%")

        if not query_conditions:
            messagebox.showerror("错误", "请至少填写一个查询条件！")
            return

        query = "SELECT * FROM Employee WHERE " + " AND ".join(query_conditions)

        # 执行查询
        try:
            cursor = conn.cursor()
            cursor.execute(query, query_values)
            results = cursor.fetchall()
            display_results(
                "员工信息查询结果",
                ["EmployeeID", "Name", "Gender", "Age", "ContactInfo", "Position", "HireDate", "Salary"],
                results
            )
        except pymysql.MySQLError as e:
            messagebox.showerror("查询失败", f"查询失败: {e}")

    # 创建查询窗口
    query_window = tk.Toplevel()
    query_window.title("查询员工信息")

    # 界面布局
    tk.Label(query_window, text="员工编号 (EmployeeID):").grid(row=0, column=0)
    employee_id_entry = tk.Entry(query_window)
    employee_id_entry.grid(row=0, column=1)

    tk.Label(query_window, text="员工姓名 (Name):").grid(row=1, column=0)
    name_entry = tk.Entry(query_window)
    name_entry.grid(row=1, column=1)

    tk.Label(query_window, text="职位 (Position):").grid(row=2, column=0)
    position_entry = tk.Entry(query_window)
    position_entry.grid(row=2, column=1)

    tk.Button(query_window, text="查询", command=submit_query).grid(row=3, column=0, columnspan=2, pady=10)


def check_out_guest():
    """办理退房"""
    def submit_check_out():
        stay_id = stay_id_entry.get().strip()
        actual_check_out_date = check_out_date_entry.get().strip()

        # 验证输入
        if not stay_id.isdigit():
            messagebox.showerror("错误", "入住编号 (StayID) 必须是数字！")
            return
        if not actual_check_out_date:
            messagebox.showerror("错误", "退房日期不能为空！")
            return

        stay_id = int(stay_id)

        try:
            # 更新入住记录的退房日期
            cursor = conn.cursor()
            query = """
            UPDATE Stay
            SET CheckOutDate = %s
            WHERE StayID = %s;
            """
            cursor.execute(query, (actual_check_out_date, stay_id))

            # 查询入住记录以获取房间编号
            query = """
            SELECT RoomID FROM Stay WHERE StayID = %s;
            """
            cursor.execute(query, (stay_id,))
            result = cursor.fetchone()

            if not result:
                messagebox.showerror("错误", f"入住编号 {stay_id} 不存在！")
                return

            room_id = result[0]

            # 更新房间状态为“空闲”
            query = """
            UPDATE Room
            SET Status = 'Available'
            WHERE RoomID = %s;
            """
            cursor.execute(query, (room_id,))

            conn.commit()
            messagebox.showinfo("成功", f"退房成功！入住编号 {stay_id} 的记录已更新，房间编号 {room_id} 已设为空闲。")
            check_out_window.destroy()

        except pymysql.MySQLError as e:
            messagebox.showerror("失败", f"退房失败: {e}")

    # 创建退房窗口
    check_out_window = tk.Toplevel()
    check_out_window.title("办理退房")

    # 界面布局
    tk.Label(check_out_window, text="入住编号 (StayID):").grid(row=0, column=0)
    stay_id_entry = tk.Entry(check_out_window)
    stay_id_entry.grid(row=0, column=1)

    tk.Label(check_out_window, text="实际退房日期 (YYYY-MM-DD):").grid(row=1, column=0)
    check_out_date_entry = tk.Entry(check_out_window)
    check_out_date_entry.grid(row=1, column=1)

    submit_button = tk.Button(check_out_window, text="提交退房信息", command=submit_check_out)
    submit_button.grid(row=2, column=0, columnspan=2, pady=10)



def query_payment_info():
    """查询付款信息"""
    def submit_query():
        stay_id = stay_id_entry.get().strip()
        payment_date = payment_date_entry.get().strip()

        # 构建查询条件
        query_conditions = []
        query_values = []

        if stay_id:
            if not stay_id.isdigit():
                messagebox.showerror("错误", "入住编号 (StayID) 必须是数字！")
                return
            query_conditions.append("StayID = %s")
            query_values.append(int(stay_id))

        if payment_date:
            query_conditions.append("PaymentDate = %s")
            query_values.append(payment_date)

        if not query_conditions:
            messagebox.showerror("错误", "请至少填写一个查询条件！")
            return

        query = "SELECT * FROM Payment WHERE " + " AND ".join(query_conditions)

        # 执行查询
        try:
            cursor = conn.cursor()
            cursor.execute(query, query_values)
            results = cursor.fetchall()
            display_results(
                "付款信息查询结果",
                ["PaymentID", "StayID", "Amount", "PaymentMethod", "PaymentDate"],
                results
            )
        except pymysql.MySQLError as e:
            messagebox.showerror("查询失败", f"查询失败: {e}")

    # 创建查询窗口
    query_window = tk.Toplevel()
    query_window.title("查询付款信息")

    # 界面布局
    tk.Label(query_window, text="入住编号 (StayID):").grid(row=0, column=0)
    stay_id_entry = tk.Entry(query_window)
    stay_id_entry.grid(row=0, column=1)

    tk.Label(query_window, text="付款日期 (YYYY-MM-DD):").grid(row=1, column=0)
    payment_date_entry = tk.Entry(query_window)
    payment_date_entry.grid(row=1, column=1)

    tk.Button(query_window, text="查询", command=submit_query).grid(row=2, column=0, columnspan=2, pady=10)

# 导出查询结果到 CSV
def export_to_csv(title, columns, results):
    filepath = filedialog.asksaveasfilename(defaultextension=".csv", filetypes=[("CSV files", "*.csv")])
    if filepath:
        try:
            with open(filepath, mode="w", newline="", encoding="utf-8") as file:
                writer = csv.writer(file)
                writer.writerow(columns)
                writer.writerows(results)
            messagebox.showinfo("导出成功", f"{title} 已成功导出到 {filepath}")
        except Exception as e:
            messagebox.showerror("导出失败", f"导出失败: {e}")

# 显示查询结果
def display_results(title, columns, results):
    result_window = tk.Toplevel()
    result_window.title(title)

    tree = ttk.Treeview(result_window, columns=columns, show="headings")
    for col in columns:
        tree.heading(col, text=col)
        tree.column(col, width=150)
    for row in results:
        tree.insert("", "end", values=row)
    tree.pack(fill="both", expand=True)

    export_button = tk.Button(result_window, text="导出到 CSV", command=lambda: export_to_csv(title, columns, results))
    export_button.pack()

# 主界面
def main_window():
    window = tk.Tk()
    window.title("酒店管理系统")

    if username == "admin_user":
        tk.Button(window, text="管理服务信息", command=manage_service_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="管理顾客信息", command=manage_customer_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="管理员工绩效信息", command=manage_employee_performance).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="新增员工", command=add_employee).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="删除员工", command=delete_employee).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="修改员工排班信息", command=update_employee_shift).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="修改房型信息", command=update_room_type).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="修改房间状态", command=update_room_status).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询顾客信息", command=query_customer_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询顾客预订信息视图", command=query_customer_reservation_view).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询房间状态信息视图", command=query_room_status_view).pack(fill="x", padx=20,pady=10)
        tk.Button(window, text="退出", command=lambda: close_connection(window)).pack(fill="x", padx=20, pady=10)

    elif username == "receptionist_user":
        tk.Button(window, text="管理顾客信息", command=manage_customer_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="修改房间状态", command=update_room_status).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="更新预订状态", command=update_reservation_status).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="办理入住", command=check_in_guest).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="办理退房", command=check_out_guest).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="新增付款项目", command=add_payment_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询付款信息", command=query_payment_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询顾客信息", command=query_customer_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询顾客预订信息视图", command=query_customer_reservation_view).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询房间状态信息视图", command=query_room_status_view).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="退出", command=lambda: close_connection(window)).pack(fill="x", padx=20, pady=10)
    elif username == "auditor_user":
        tk.Button(window, text="查询顾客信息", command=query_customer_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询付款信息", command=query_payment_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询员工信息", command=query_employee_info).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询入住详细信息视图", command=query_stay_details_view).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="查询房间状态信息视图", command=query_room_status_view).pack(fill="x", padx=20, pady=10)
        tk.Button(window, text="退出", command=lambda: close_connection(window)).pack(fill="x", padx=20, pady=10)

    window.mainloop()

# 安全断开数据库连接
def close_connection(window):
    if conn:
        conn.close()
    window.destroy()
    print("数据库连接已关闭！")

# 登录界面
def login_window():
    window = tk.Tk()
    window.title("登录")

    tk.Label(window, text="用户名:").grid(row=0, column=0)
    user_entry = tk.Entry(window)
    user_entry.grid(row=0, column=1)

    tk.Label(window, text="密码:").grid(row=1, column=0)
    password_entry = tk.Entry(window, show="*")
    password_entry.grid(row=1, column=1)

    login_button = tk.Button(window, text="登录", command=lambda: authenticate_user(window, user_entry, password_entry))
    login_button.grid(row=2, column=0, columnspan=2)

    window.mainloop()

if __name__ == "__main__":
    login_window()