Untitled

import sqlite3
from datetime import datetime

conn = sqlite3.connect("restaurant.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT
)""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS tables (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_number INTEGER UNIQUE,
    capacity INTEGER
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS menu (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    table_id INTEGER,
    order_date TEXT,
    FOREIGN KEY(customer_id) REFERENCES customers(id),
    FOREIGN KEY(table_id) REFERENCES tables(id)
)
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER,
    menu_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY(order_id) REFERENCES orders(id),
    FOREIGN KEY(menu_id) REFERENCES menu(id)
)""")
conn.commit()

def add_customer():
    name = input("Emri i klientit: ")
    phone = input("Telefoni: ")
    cursor.execute("INSERT INTO customers (name, phone) VALUES (?, ?)", (name, phone))
    conn.commit()
    print("Klienti u shtua me sukses!\n")

def add_table():
    number = int(input("Numri i tavolines: "))
    capacity = int(input("Kapaciteti: "))
    cursor.execute("INSERT INTO tables (table_number, capacity) VALUES (?, ?)",
                   (number, capacity))
    conn.commit()
    print("Tavolina u shtua!\n")

def add_menu_item():
    name = input("Emri i produktit: ")
    price = float(input("Cmimi: "))
    cursor.execute("INSERT INTO menu (name, price) VALUES (?, ?)", (name, price))
    conn.commit()
    print("Produkti u shtua ne menu!\n")

def create_order():
    customer_id = int(input("ID e klientit: "))
    table_id = int(input("ID e tavolines: "))
    date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute("INSERT INTO orders (customer_id,table_id,order_date) VALUES (?, ?, ?)",
                   (customer_id, table_id, date))
    conn.commit()
    print("Porosia u krijua!\n")

def add_order_item():
    order_id = int(input("ID e porosise: "))
    menu_id = int(input("ID e produktit: "))
    quantity = int(input("Sasia: "))
    cursor.execute("INSERT INTO order_items (order_id, menu_id, quantity) VALUES(?, ?, ?)",
                   (order_id, menu_id, quantity))
    conn.commit()
    print("Produkti u shtua ne porosi!\n")

def show_orders():
    cursor.execute("""
    SELECT orders.id, customers.name, tables.table_number, orders.order_date
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
    JOIN tables ON orders.table_id = tables.id""")
    rows = cursor.fetchall()
    print("\n=== POROSITE ===")
    for row in rows:
        print("Order ID:", row[0], "| Klienti:", row[1], "| Tavolina:",
              row[2], "| Data:", row[3])
    print()

def order_total():
    order_id = int(input("Vendos ID e porosise: "))
    cursor.execute("""
    SELECT SUM(menu.price * order_items.quantity)
    FROM order_items
    JOIN menu ON order_items.menu_id = menu.id
    WHERE order_items.order_id = ?
    """, (order_id,))
    total = cursor.fetchone()[0]
    print("Totali i porosise:", total if total else 0, "Euro\n")

def best_selling_products():
    cursor.execute("""
    SELECT menu.name, SUM(order_items.quantity) as total_sold
    FROM order_items
    JOIN menu ON order_items.menu_id = menu.id
    GROUP BY menu.name
    ORDER BY total_sold DESC""")
    rows = cursor.fetchall()
    print("\n=== PRODUKTET ME TE SHITURA ===")
    for row in rows:
        print(row[0], "- Shitur:", row[1])
    print()
def update_price():
    name = input("Emri i produktit: ")
    new_price = float(input("Cmimi i ri: "))
    cursor.execute("UPDATE menu SET price = ? WHERE name = ?", (new_price, name))
    conn.commit()
    print("Cmimi u perditesua!\n")

def delete_customer():
    customer_id = int(input("ID e klientit per fshirje: "))
    cursor.execute("DELETE FROM customers WHERE id = ?", (customer_id,))
    conn.commit()
    print("Klienti u fshi!\n")

def menu_system():
    while True:
        print("SISTEM RESTORANTI ")
        print("1. Shto klient")
        print("2. Shto tavoline")
        print("3. Shto produkt ne menu")
        print("4. Krijo porosi")
        print("5. Shto produkt ne porosi")
        print("6. Shfaq porosite")
        print("7. Totali i porosise")
        print("8. Produktet me te shitura")
        print("9. Perditeso cmimin")
        print("10. Fshi klient")
        print("0. Dil")
        choice = input("Zgjidh opsionin: ")

        if choice == "1":
            add_customer()
        elif choice == "2":
            add_table()
        elif choice == "3":
            add_menu_item()
        elif choice == "4":
            create_order()
        elif choice == "5":
            add_order_item()
        elif choice == "6":
            show_orders()
        elif choice == "7":
            order_total()
        elif choice == "8":
            best_selling_products()
        elif choice == "9":
            update_price()
        elif choice == "10":
            delete_customer()
        elif choice == "0":
            print("Programi u mbyll.")
            break
        else:
            print("Opsion i pavlefshem!\n")
menu_system()
conn.close()