foodbackend_stage5

import sqlite3
import sys
import argparse


class FoodBlog:
    def __init__(self):
        # self.database()
        args = sys.argv
        self.db_name = args[1]
        self.conn = sqlite3.connect(self.db_name)
        self.cursor = self.conn.cursor()
        self.data = {"meals": ("breakfast", "brunch", "lunch", "supper"),
                     "ingredients": ("milk", "cacao", "strawberry", "blueberry", "blackberry", "sugar"),
                     "measures": ("ml", "g", "l", "cup", "tbsp", "tsp", "dsp", "")}
        self.meal_ids_names = ()
        self.recipe_name = ''
        self.all_meals_names = []
        self.ingredients_names = []

    def make_table(self, table=None, name=None, description=None):
        # This function creates tables. It inserts data when name is given
        item = f'{table[:-1]}'
        not_null = 'NOT NULL' if table in ['ingredients', 'meals', 'recipes'] else ''  # control null or not
        if table == 'recipes':
            des = f',{item}_description TEXT'
        else:
            des = ''
        # with sqlite3.connect(db_name) as data:
        # cursor = data.cursor()
        uni = 'UNIQUE' if table != 'recipes' else ''
        if name is None:
            self.cursor.execute(f'''
            CREATE TABLE IF NOT EXISTS {table}(
            {item}_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            {item}_name TEXT {not_null} {uni}
            {des}
            );
            ''')
        else:
            if table == 'recipes':
                self.cursor.execute(f'''
                INSERT into {table} ({table[:-1]}_description, {table[:-1]}_name) VALUES (?, ?);
                ''', (description, name))
            else:
                self.cursor.execute(f'''
                INSERT into {table} ({table[:-1]}_name) VALUES (?);
                ''', (name,))
        self.cursor.execute('''
        SELECT * FROM meals
        ''')
        self.meal_ids_names = self.cursor.fetchall()
        self.conn.commit()

    def make_serve_table(self):
        self.cursor.execute('PRAGMA foreign_keys = ON;')
        self.cursor.execute(f'''
        CREATE TABLE IF NOT EXISTS serve(
        serve_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        meal_id INTEGER NOT NULL,
        recipe_id INTEGER NOT NULL,
        FOREIGN KEY (meal_id)
            REFERENCES meals (meal_id),

        FOREIGN KEY (recipe_id)
            REFERENCES recipes (recipe_id)
        );
        ''')
        self.conn.commit()

    def make_quantity_table(self):
        self.cursor.execute('''
        CREATE TABLE IF NOT EXISTS quantity(
        quantity_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        quantity INTEGER NOT NULL,
        recipe_id INTEGER NOT NULL,
        measure_id INTEGER NOT NULL,
        ingredient_id INTEGER NOT NULL,
        FOREIGN KEY (recipe_id) REFERENCES recipes(recipe_id),
        FOREIGN KEY(measure_id)  REFERENCES measures(measure_id),
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(ingredient_id)
        );
        ''')
        self.conn.commit()

    def get_id(self, table_name, name):
        # This function checks if the user input will get more than one result in ingredients and measures
        # it then returns id
        # e.g : t for tbs and tbsp. berry for blueberry and strawberry. t and berry won't pass
        self.cursor.execute(f'''
        SELECT {table_name}_id FROM {table_name}s WHERE {table_name}_name LIKE (?)
        ''', ('%' + name + '%',))
        fetched = self.cursor.fetchone()
        fetched_all = self.cursor.fetchall()
        #print('fetched', fetched)
        #print('fetched all', fetched_all)
        if fetched and len(fetched_all) <= 1 or name == '':
            return int(fetched[0])
        else:
            print(table_name, name)
            print("This data doesn't exist")
            return False

    def change_serve_table(self, name):
        # insert into table serve with recipe_id meal_id
        self.cursor.execute('''
        SELECT recipe_id FROM recipes WHERE recipe_name = (?)
        ''', (name,))  # fetch all recipe names are not unique there are duplicates
        recipe_id = self.cursor.fetchone()

        all_meals_names = [x[1] for x in self.meal_ids_names]
        all_meals_ids = [x[0] for x in self.meal_ids_names]
        # print(all_meals_ids, all_meals_names)
        # [1, 2, 3, 4]['breakfast', 'brunch', 'lunch', 'supper']

        for x in range(len(all_meals_names)):
            print(f'{x + 1}) {all_meals_names[x]}', end='  ')
        print()
        print('When the dish can be served: ', end='')
        wanted_meals = input().split()
        for i in wanted_meals:
            self.cursor.execute('''
            INSERT INTO serve( meal_id, recipe_id) VALUES (?, ?);
            ''', (int(i), recipe_id[0]))
        self.conn.commit()

    def change_quantity_table(self):
        # insert into quantity table with recipe_id, measure_id, ingredient_id
        while True:
            print('Input quantity of ingredient <press enter to stop>:')
            quans = []
            quan_ids = []
            q = input().split()
            if len(q) == 2:
                print('insert q', q)
                q.insert(1, '')
                print('inserted', q)

            if q:
                quans.extend(q)
                if self.get_id('measure', quans[1]) and self.get_id('ingredient', quans[2]):
                    quan_ids.extend(
                        [int(quans[0]), self.get_id('recipe', self.recipe_name), self.get_id('measure', quans[1]),
                         self.get_id('ingredient', quans[2])])
                    print('add to db')
                    self.cursor.execute('''
                    INSERT INTO quantity (quantity, recipe_id, measure_id, ingredient_id) VALUES (?, ?, ?, ?)
                    ''', (quan_ids[0], quan_ids[1], quan_ids[2], quan_ids[3]))
                    self.conn.commit()
                else:
                    print('else q', q, len(q))
                    continue  # if len(q) == 2
            else:
                print('end of loop q', quans)
                break

    def database(self):
        for x in self.data:
            self.make_table(x)
            print('execute time', x)
            for y in self.data[x]:
                self.make_table(x, y)
        self.make_table('recipes')

    def do_args(self):
        self.cursor.execute('''
        SELECT * FROM ingredients
        ''')
        self.ingredients_names = self.cursor.fetchall()
        print(self.ingredients_names)
        parser = argparse.ArgumentParser()
        parser.add_argument('--ingredients', choices=self.ingredients_names)
        parser.add_argument('--meals', choices=self.all_meals_names)
        parser.add_argument('food_blog.db')
        args = parser.parse_args()
        wanted_ingredients = args.ingredients
        if wanted_ingredients:
            wanted_ingri_ids = []
            for i in wanted_ingredients:
                self.cursor.execute('''
                SELECT ingredient_id FROM ingredients WHERE ingredient_name = (?)
                ''', (i,))
                wanted_ingri_ids.append(self.cursor.fetchone())
            wanted_meals = args.meals
            for _id in wanted_ingri_ids:
                self.cursor.execute('''
                SELECT recipe_name FROM recipes AS r
                INNER JOIN quantity AS q ON r.recipe_id = q.recipe_id
                INNER JOIN ingredients AS i ON i.ingredient_id = (?)
                INNER JOIN serve AS s ON s.recipe_id = r.recipe_id
                INNER JOIN meals AS m ON s.meal_id = m.meal_id
                WHERE m.meal_name IN (?)
                ''', (_id, wanted_meals))
                recipes = self.cursor.fetchall()
                if recipes:
                    print('Recipes selected for you: ', *recipes)
                else:
                    print('There are no such recipes in the database.')

    def menu(self):
        self.database()
        self.make_serve_table()
        self.make_quantity_table()
        #self.do_args()
        # move the make db bases lines above in function database() after the tests are passed
        print('Pass the empty recipe name to exit.')
        while True:
            self.recipe_name = input('Recipe name: ')
            if self.recipe_name == '':
                break
            des = input('Recipe description: ')
            self.make_table(table='recipes', name=self.recipe_name, description=des)
            self.change_serve_table(self.recipe_name)
            self.change_quantity_table()  # this function deals with input
        self.conn.close()


FoodBlog().menu()