import mysql.connector as cc

# connecting DB
con = cc.connect(
    host='localhost',
    user='root',
    password='password'
)

# create Db
cur = con.cursor()
cur.execute('show databases')
datalist = cur.fetchall()  # checks the total no of databases
dlempt = []
for i in datalist:
    dlempt.append(i[0])
if 'restaurant' in dlempt:
    cur.execute('use restaurant')
else:
    q1 = 'create database restaurant'
    q2 = 'use restaurant'
    q3 = 'create table Dish(Dish varchar(30), Cost int, DishID varchar(5))'
    q4 = 'create table Cook(Name varchar(40), Aadhar int(12), Salary int, DOB date)'
    q5 = 'create table Orders(Date date, Name varchar(40), Aadhar int(12), DishIDs varchar(5), Cost int)'
    q6 = 'create table Salary(Name varchar(40), Aadhar int(12), Bank varchar(30), Account int(15), Payday date, Salary int, Days int, Net int)'
    q7 = 'create table Expense(Type varchar(30), Expense int, Date date)'
    cur.execute(q1)
    cur.execute(q2)
    cur.execute(q3)
    cur.execute(q4)
    cur.execute(q5)
    cur.execute(q6)
    cur.execute(q7)
    con.commit()
    print('Created Database')


# signing in into the client

def signin():
    print('\n')
    print('     ------------------------------>>>>>>>>>>>>>>> WELCOME TO OREO\'S CAFE <<<<<<<<<<<<<<<-----------------------------')
    print('\n')
    client = input('Enter Password: ')
    if client == 'oreo123':
        options()
    else:
        print('Wrong password! Try again!')
        signin()


def options():
    print("""
    ------------------------------------------------------------------------------------------------------------------
                                                    1.      DISHES
                                                    2.      ORDER
                                                    3.      COOKS
                                                    4.      SALARY
                                                    5.      INCOME
                                                    6.      EXPENSE
    ------------------------------------------------------------------------------------------------------------------
    """)
    ch = input("Enter your Choice: ")
    while True:
        if ch == '1':
            dishes()
        elif ch == '2':
            order()
        elif ch == '3':
            cooks()
        elif ch == '4':
            salary()
        elif ch == '5':
            income()
        elif ch == '6':
            expense()
        else:
            print('Not a Valid Choice')


def dishes():
    ch = input('1. Add Food    2. Remove Food   3. Display Food    4. Main Menu: ')

    if ch == '1':
        dishname = input('Enter Dish Name: ')
        dishprice = input('Enter Dish Price: ')
        dishid = str(dishID())
        query = 'insert into Dish values({}, {}, {})'.format(
            dishname, dishprice, dishid)
        cur.execute(query)
        con.commit()
        print("Dish has added successfully")
    elif ch == '2':
        dishid = input('Enter Dish ID: ')
        query = 'delete from Dish where DishID={}'.format(dishid)
        cur.execute(query)
        con.commit()
        print("Dish has been deleted successfully")
    elif ch == '3':
        print('\n')
        query = ('select * from Dish')
        cur.execute(query)
        fetch = cur.fetchall()
        for i in fetch:
            print(i[0], '-', i[1], '-', i[2])
        print('\n')
    else:
        options()


def order():
    print('order')


def cooks():
    print('cooks')


def salary():
    print('salary')


def income():
    print('income')


def expense():
    print('dishes')


signin()


# extra functions

def dishID():
    query = 'select count(*), max(DishID) from Dish'
    cur.execute(query)
    fetch = cur.fetchall()
    for i in fetch:
        if i[0] == 0:
            return 1
        else:
            return (int(i[1]) + 1)