reservations.py

import tkinter as tk
from tkinter import ttk, messagebox
from common import get_db_connection, get_current_datetime, get_waiters

def show_reservation_page(main_frame, customer_name_var, num_people_var, date_var, turn_var, waiter_var):
    def get_least_busy_waiter(reservation_date, reservation_turn):
        """
        Restituisce l'ID del cameriere con il minor numero di tavoli totali assegnati e prenotati.
        Stampa il carico totale di ogni cameriere.
        """
        conn = get_db_connection()
        cursor = conn.cursor()

        # Conta il numero totale di tavoli assegnati e prenotati per ciascun cameriere
        cursor.execute("""
            SELECT Camerieri.id, Camerieri.nome,
                COALESCE(COUNT(Prenotazioni.id), 0) AS num_reservations,
                COALESCE(SUM(CASE WHEN Tavoli.stato = 'occupato' THEN 1 ELSE 0 END), 0) AS num_assigned_tables,
                (COALESCE(COUNT(Prenotazioni.id), 0) + 
                    COALESCE(SUM(CASE WHEN Tavoli.stato = 'occupato' THEN 1 ELSE 0 END), 0)) AS total_load
            FROM Camerieri
            LEFT JOIN Prenotazioni ON Camerieri.id = Prenotazioni.cameriere_id
                                    AND Prenotazioni.data = ?
                                    AND Prenotazioni.turno = ?
            LEFT JOIN Tavoli ON Camerieri.id = Tavoli.cameriere_id
            GROUP BY Camerieri.id
            ORDER BY total_load ASC, Camerieri.id ASC
        """, (reservation_date, reservation_turn))

        waiters = cursor.fetchall()
        conn.close()

        # Stampa il carico per ciascun cameriere
        print("\n[INFO] Carico totale dei camerieri:")
        print("ID    Nome       Prenotazioni    Tavoli Assegnati    Carico Totale")
        print("---------------------------------------------------------------")
        for waiter in waiters:
            waiter_id, waiter_name, num_reservations, num_assigned_tables, total_load = waiter
            print(f"{waiter_id:<5} {waiter_name:<10} {num_reservations:<15} {num_assigned_tables:<18} {total_load}")

        # Ritorna l'ID del cameriere con il minor carico
        if waiters:
            return waiters[0][0]
        else:
            return None

    def update_reservations_table():
        reservation_list.delete(*reservation_list.get_children()) 
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT Prenotazioni.data, Prenotazioni.turno, Prenotazioni.cliente_nome, Prenotazioni.tavolo_id, Camerieri.nome
            FROM Prenotazioni
            JOIN Camerieri ON Camerieri.id = Prenotazioni.cameriere_id
            ORDER BY Prenotazioni.data,
                    CASE Prenotazioni.turno
                        WHEN 'pranzo' THEN 1
                        WHEN 'cena' THEN 2
                    END
        """
        )
        reservations = cursor.fetchall()
        conn.close()

        # Raggruppa le prenotazioni per data e turno
        grouped_reservations = {}
        for row in reservations:
            date, turno = row[0], row[1]
            if date not in grouped_reservations:
                grouped_reservations[date] = {"pranzo": [], "cena": []}
            grouped_reservations[date][turno].append(row)

        # Popola la tabella con le prenotazioni raggruppate per data e turno
        for date, turn_data in grouped_reservations.items():
            reservation_list.insert("", "end", values=(f"Date: {date}", "", "", "", ""), tags=("date_row",))

            if turn_data["pranzo"]:
                reservation_list.insert("", "end", values=("Pranzo", "", "", "", ""), tags=("turn_row",))
                for row in turn_data["pranzo"]:
                    _, _, cliente_nome, tavolo_id, cameriere_nome = row
                    reservation_list.insert("", "end", values=(cliente_nome, tavolo_id, date, "Pranzo", cameriere_nome))

            if turn_data["cena"]:
                reservation_list.insert("", "end", values=("Cena", "", "", "", ""), tags=("turn_row",))
                for row in turn_data["cena"]:
                    _, _, cliente_nome, tavolo_id, cameriere_nome = row
                    reservation_list.insert("", "end", values=(cliente_nome, tavolo_id, date, "Cena", cameriere_nome))

    def delete_reservation():
        selected_items = reservation_list.selection() 
        if not selected_items:
            messagebox.showerror("Error", "Please select a reservation to delete.")
            return

        conn = get_db_connection()
        cursor = conn.cursor()

        for item in selected_items:
            values = reservation_list.item(item, "values")

            if len(values) < 5 or not values[1] or not values[2] or not values[3]:
                continue

            try:
                table_id = int(values[1])
            except ValueError:
                messagebox.showerror("Error", "Invalid table ID selected.")
                return

            reservation_date = values[2]
            reservation_turn = values[3].lower()

            try:
                # Elimina la prenotazione dal database
                cursor.execute("""
                    DELETE FROM Prenotazioni
                    WHERE tavolo_id = ? AND data = ? AND LOWER(turno) = ?
                """, (table_id, reservation_date, reservation_turn))

                # Aggiorna il tavolo a "libero"
                cursor.execute("""
                    UPDATE Tavoli 
                    SET stato = 'libero', turno = NULL, data = NULL, customer_name = NULL, cameriere_id = NULL
                    WHERE id = ?
                """, (table_id,))
            except Exception as e:
                print(f"Error during SQL execution: {e}")
                messagebox.showerror("Error", "Failed to delete reservation.")
                conn.rollback()  # Annulla in caso di errore
                return

        conn.commit()
        conn.close()

        messagebox.showinfo("Success", "Selected reservation(s) deleted.")
        update_reservations_table()

    def assign_table():
        selected_items = reservation_list.selection()
        if not selected_items:
            messagebox.showerror("Error", "Please select a reservation to assign.")
            return

        # Ottieni data e turno correnti
        current_date, current_turn = get_current_datetime()

        conn = get_db_connection()
        cursor = conn.cursor()

        for item in selected_items:
            values = reservation_list.item(item, "values")

            if len(values) < 5 or not values[1] or not values[2] or not values[3]:
                continue

            try:
                table_id = int(values[1])
            except ValueError:
                messagebox.showerror("Error", "Invalid table ID selected.")
                return

            reservation_date = values[2]
            reservation_turn = values[3].lower() 

            # Controlla se la prenotazione corrisponde a data e turno correnti
            if reservation_date == current_date and reservation_turn == current_turn.lower():
                try:
                    # Recupera i dettagli del cliente e del cameriere associati alla prenotazione
                    cursor.execute("""
                        SELECT cliente_nome, cameriere_id
                        FROM Prenotazioni
                        WHERE tavolo_id = ? AND data = ? AND turno = ?
                    """, (table_id, current_date, current_turn))
                    reservation_details = cursor.fetchone()

                    if not reservation_details:
                        messagebox.showerror("Error", f"No reservation found for Table {table_id}.")
                        continue

                    cliente_nome, cameriere_id = reservation_details

                    # Recupera il numero di posti del tavolo
                    cursor.execute("""
                        SELECT posti FROM Tavoli WHERE id = ?
                    """, (table_id,))
                    table_capacity = cursor.fetchone()[0]

                    # Verifica che il tavolo non sia stato assegnato a una sola persona
                    if table_capacity == 1:
                        messagebox.showerror("Error", f"Table {table_id} cannot be assigned to one person.")
                        continue

                    # Aggiorna lo stato del tavolo a "occupato" e aggiorna i dettagli
                    cursor.execute("""
                        UPDATE Tavoli
                        SET stato = 'occupato', customer_name = ?, cameriere_id = ?, turno = ?, data = ?
                        WHERE id = ?
                    """, (cliente_nome, cameriere_id, current_turn, current_date, table_id))

                    # Elimina la prenotazione dalla tabella `Prenotazioni`
                    cursor.execute("""
                        DELETE FROM Prenotazioni
                        WHERE tavolo_id = ? AND data = ? AND turno = ?
                    """, (table_id, current_date, current_turn))

                    conn.commit()
                    messagebox.showinfo("Success", f"Table {table_id} assigned successfully!")
                except Exception as e:
                    conn.rollback()
                    messagebox.showerror("Error", f"Failed to assign table {table_id}. Error: {e}")
            else:
                messagebox.showerror("Error", "You can only assign tables for the current date and turn.")
                conn.close()
                return

        conn.close()
        update_reservations_table() 

    def add_reservation():
        customer_name = customer_name_var.get().strip()
        num_people = num_people_var.get().strip()
        date_input = date_var.get().strip()
        turno = turn_var.get()

        if not customer_name or not num_people.isdigit() or not date_input or turno not in ["pranzo", "cena"]:
            messagebox.showerror("Error", "Please fill all fields correctly.")
            return
        
        # Verifica che il numero di persone sia maggiore di 1
        if int(num_people) == 1:
            messagebox.showerror("Error", "Reservations for one person are not allowed.")
            return

        waiter_id = get_least_busy_waiter(date_input, turno)
        if not waiter_id:
            messagebox.showerror("Error", "No waiter available to assign.")
            return

        conn = get_db_connection()
        cursor = conn.cursor()

        try:
            # Seleziona un tavolo disponibile per il giorno e il turno specificati
            cursor.execute("""
                SELECT id FROM Tavoli
                WHERE stato = 'libero'
                AND id NOT IN (
                    SELECT tavolo_id FROM Prenotazioni
                    WHERE data = ? AND turno = ?
                )
                AND posti >= ?
                ORDER BY posti ASC
                LIMIT 1
            """, (date_input, turno, num_people))
            table = cursor.fetchone()

            if table:
                table_id = table[0]

                # Inserisci la prenotazione
                cursor.execute("""
                    INSERT INTO Prenotazioni (cliente_nome, tavolo_id, cameriere_id, data, turno)
                    VALUES (?, ?, ?, ?, ?)
                """, (customer_name, table_id, waiter_id, date_input, turno))

                conn.commit()
                messagebox.showinfo("Success", f"Reservation added: {customer_name} -> Table {table_id}")
                update_reservations_table()
            else:
                messagebox.showerror("Error", "No suitable table available for the specified date and time.")
        except Exception as e:
            conn.rollback()
            messagebox.showerror("Error", f"Failed to add reservation. Error: {e}")
        finally:
            conn.close()

    for widget in main_frame.winfo_children():
        widget.destroy()

    date, turno = get_current_datetime()
    date_var.set(date)
    turn_var.set(turno)

    tk.Label(main_frame, text=f"Data e Turno Corrente: {date}, {turno.capitalize()}", font=("Arial", 12), anchor="e").pack(anchor="ne", padx=10, pady=5)

    tk.Label(main_frame, text="Reservations", font=("Arial", 24, "bold")).pack(pady=20)

    frame_form = tk.Frame(main_frame)
    frame_form.pack(pady=10)

    tk.Label(frame_form, text="Customer Name:").grid(row=0, column=0)
    tk.Entry(frame_form, textvariable=customer_name_var).grid(row=0, column=1)

    tk.Label(frame_form, text="Number of People:").grid(row=1, column=0)
    tk.Entry(frame_form, textvariable=num_people_var).grid(row=1, column=1)

    tk.Label(frame_form, text="Date (YYYY-MM-DD):").grid(row=2, column=0)
    tk.Entry(frame_form, textvariable=date_var).grid(row=2, column=1)

    tk.Label(frame_form, text="Turn:").grid(row=3, column=0)
    tk.Radiobutton(frame_form, text="Pranzo", variable=turn_var, value="pranzo").grid(row=3, column=1)
    tk.Radiobutton(frame_form, text="Cena", variable=turn_var, value="cena").grid(row=3, column=2)

    tk.Button(frame_form, text="Add Reservation", command=add_reservation).grid(row=5, column=0, columnspan=2)

    # Creazione della Treeview
    reservation_list = ttk.Treeview(
        main_frame,
        columns=("Customer", "Table", "Date", "Turn", "Waiter"),
        show="headings"
    )
    reservation_list.heading("Customer", text="Customer")
    reservation_list.heading("Table", text="Table")
    reservation_list.heading("Date", text="Date")
    reservation_list.heading("Turn", text="Turn")
    reservation_list.heading("Waiter", text="Waiter")

    reservation_list.column("Customer", width=200, anchor="center")
    reservation_list.column("Table", width=150, anchor="center")
    reservation_list.column("Date", width=150, anchor="center")
    reservation_list.column("Turn", width=150, anchor="center")
    reservation_list.column("Waiter", width=150, anchor="center")
    reservation_list.pack(pady=10)

    style = ttk.Style()
    style.configure("Treeview", rowheight=25)
    style.configure("Treeview.Heading", font=("Arial", 12, "bold"))
    style.map("Treeview", background=[("selected", "#D3D3D3")])

    reservation_list.tag_configure("date_row", background="#FFD700", font=("Arial", 10, "bold"))
    reservation_list.tag_configure("turn_row", background="#ADD8E6", font=("Arial", 10, "italic"))
    reservation_list.tag_configure("header", background="#E0E0E0", font=("Arial", 10, "bold"))

    reservation_list.insert("", "end", values=("Customer", "Table ID", "Reservation Date", "Turn", "Waiter"), tags=("header",))

    # Bottoni per Delete e Assign
    button_frame = tk.Frame(main_frame)
    button_frame.pack(pady=5)

    tk.Button(button_frame, text="Delete Reservation", command=delete_reservation).grid(row=0, column=0, padx=5)
    tk.Button(button_frame, text="Assign Table", command=assign_table).grid(row=0, column=1, padx=5)

    update_reservations_table()