## simple demo script for showing how to connect to an sqlite DB
# from Python, and run a simple SQL query

# import the python library for SQLite
import sqlite3

# connect to the database file, and create a connection object(location of cursor)
db_connection = sqlite3.connect('restaurants.db')

# create a database cursor object, which allows us to perform SQL on the database.
db_cursor = db_connection.cursor()

### Add your favorite 3 restaurants in Kreuzberg or Neukölln to the database
# additional restaurant row insert
db_cursor.execute("""
                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
                    VALUES ('4', 'Imren', '3', '2')
                    """)
db_cursor.execute("""
                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
                    VALUES ('5', 'Burgermeister', '1', '1')
                    """)
db_cursor.execute("""
                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
                    VALUES ('6', 'Somethingfancy', '3', '4')
                    """)

# Add a new neighborhood to the database,
db_cursor.execute("""
                    INSERT INTO neighborhoods (ID, NAME)
                    VALUES ('5', 'Schoeneberg')
                    """)
# and add 3 restaurants to it.
db_cursor.execute("""
                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
                    VALUES ('7', 'Chinese', '5', '3')
                    """)
db_cursor.execute("""
                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
                    VALUES ('8', 'Korean', '5', '1')
                    """)
db_cursor.execute("""
                    INSERT INTO restaurants (ID, NAME, NEIGHBORHOOD_ID, PRICE_RANGE_ID)
                    VALUES ('9', 'Japanese', '5', '2')
                    """)

# Create a new table for people, which contains their favorite restaurant.
# Add yourself to this table, and add Adam as well.
# His favorite is Curry 36.

# Adding new table 'favorites' with values 'ID, NAME, RESTAURANTS_ID'
db_cursor.execute("""
                    CREATE TABLE IF NOT EXISTS "favorites" (
                    "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    "NAME" TEXT NOT NULL,
                    "RESTAURANTS_ID" INTEGER NOT NULL REFERENCES "restaurants" ("NAME")
                    );
                    """)

# Adding people on the 'favorites'
db_cursor.execute("""
                    INSERT INTO favorites (ID, NAME, RESTAURANTS_ID)
                    VALUES ('1', 'Adam', '1')
                    """)

db_cursor.execute("""
                    INSERT INTO favorites (ID, NAME, RESTAURANTS_ID)
                    VALUES ('2', 'Jongwoo', '4')
                    """)

db_cursor.execute("""
                    INSERT INTO favorites (ID, NAME, RESTAURANTS_ID)
                    VALUES ('3', 'Soyoon', '3')
                    """)


'''
db_cursor.execute("""SELECT * from restaurants
                   INNER JOIN neighborhoods ON restaurants.NEIGHBORHOOD_ID=neighborhoods.ID
                   WHERE neighborhoods.NAME="Kreuzberg"
                   """)
'''


#showing all the info of restaurants
db_cursor.execute("SELECT * from restaurants")

#showing all the info of neighborhoods
#db_cursor.execute("SELECT * from neighborhoods")


# * means collect every att from restaurant db

# store the result in a local variable.
# this will be a list of tuples, where each tuple represents a row in the table
list_restaurants = db_cursor.fetchall() # getting readable format in a tuple

print("list_restaurants contents:")
print(list_restaurants)

db_connection.close()
# print out the list of tuple; immutable db