import sqlite3
from tkinter import *
from tkinter import messagebox as MessageBox

def create_database():

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

    try:

        cursor.execute("""
        CREATE TABLE category (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(100) UNIQUE NOT NULL
        )
        """)

    except sqlite3.OperationalError:

        print("The board \"category\" already exists")

    else:

        print("The board \"category\" has been created successfully")

    try:

        cursor.execute("""
        CREATE TABLE plate (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR(100) UNIQUE NOT NULL,
            category_id INTEGER NOT NULL,
            FOREIGN KEY(category_id) REFERENCES category(id)
        )
        """)
    
    except sqlite3.OperationalError:

        print("The board \"plate\" already exists")

    else:

        print("The board \"plate\" has been created successfully")

    connection.close()

# Queries for the database

def run_query(query, parameters = ()):

    connection = sqlite3.connect("restaurant.db")
    cursor = connection.cursor()
    result = cursor.execute(query, parameters)
    connection.commit()
    return result
    connection.close()

# Users adding categorys

def add_category():

    category_user = input("\nHow is the name of your category?\n\n> ")
    query = "INSERT INTO category VALUES (null, '{}')".format(category_user)
    try:

        run_query(query)
    
    except sqlite3.IntegrityError:

        print("\nThe category {} already exists, please try again!".format(category_user))
        add_category()

    else:
        print("\nThe category {} has been created successfully".format(category_user))
        menu()

# Adding plates for a menu of the restaurant

def add_food_plate():

    query = "SELECT * FROM category"
    execute = run_query(query)
    categories = execute.fetchall()
    print("\nSelect a category to add the food plate or please type \"exit\" for go to back the menu\n")
    
    for category in categories:
    
        print("{}) {}".format(category[0], category[1]))      

    select_category = input("\n> ")

    if select_category == "exit":
    
        menu()
    
    else:
    
        food_plate = input(" \nWhat is the name for the plate that you want put on the menu?\n\n> ")

        try:

            query = "INSERT INTO plate VALUES (null, '{}', {})".format(food_plate, select_category)
            run_query(query)
        
        except sqlite3.IntegrityError:

            print("\nThe plate \"{}\" already exists, please try again!".format(food_plate))
            add_food_plate()

        else:

            print("\nThe plate \"{}\" has been created successfully".format(food_plate))
            menu()

# Show the food plates menu

def show_food_plates_menu():

    query = "SELECT * FROM category"
    execute = run_query(query)
    categories = execute.fetchall()

    for category in categories:

        print("\n=== {} ===".format(category[1]))
        query = "SELECT * FROM plate WHERE category_id = {}".format(category[0])
        execute = run_query(query)
        plates = execute.fetchall()
        for plate in plates:
            print("\n{}".format(plate[1]))

# Program options menu

def menu():

    print("\n=== Welcome to the program ===\n")
    print("""What do you want to do?\n
1) Create a category
2) Add food plate on the menu
3) Show food plates menu
4) Exit""")

    option = input("\n> ")

    if option == "1":
    
        add_category()    

    elif option == "2":

        add_food_plate()

    elif option == "3":

        show_food_plates_menu()

    elif option == "4":
            
        print("\nThanks you for using the program!")

    else:

        print("\nUnknown command or incorrect option, please try again!")
        menu()

# Functions that initiated with the program

create_database()
menu()





