#!/usr/bin/env python3

"""
Python program to access a database
Start at the bottom of this file under main
I can give more advise on making your database or queries to get data if you need help

More information on using the built in sqlite module can be found here:
https://docs.python.org/3/library/sqlite3.html

"""
# Obviously we need the sqlite3 library
import sqlite3


def setup_db():
    # Need to excplicitly enable primary keys
    # This is only necessary if you have multiple table and they rely on eachother
    conn.execute("PRAGMA foreign_keys = ON;")

    # Delete the table if it exists
    conn.execute("drop table if exists game;")
    conn.execute("drop table if exists games;")
    conn.execute("drop table if exists teams;")


    # Create a table to store teams
    conn.execute("""
        create table teams(
            id integer primary key autoincrement,
            name text
        );
    """)

    # Create a table for a game
    # This requires setting up relationships
    conn.execute("""
        create table games(
                game_id integer primary key autoincrement,
                h_team_id integer,
                o_team_id integer,
                h_points integer,
                o_points integer,
                date text,
                FOREIGN KEY(h_team_id) REFERENCES teams(id),
                FOREIGN KEY(o_team_id) REFERENCES teams(id)
        );
    """)
    # When inserting into a table pass null if you have an autoincrementing column

    # Seed with some sample data
    # Note that sql requires single quotes surrounding strings
    conn.execute("insert into teams values(null, 'Boston Celtics');")
    conn.execute("insert into teams values(null, 'Houston Rockets');")
    conn.execute("insert into teams values(null, 'Indiana Pacers');")
    conn.execute("insert into teams values(null, 'Memphis Grizzlies');")
    conn.execute("insert into teams values(null, 'Miami Heat');")
    conn.execute("insert into teams values(null, 'Milwaukee Bucks');")

    # Insert sample game
    conn.execute("insert into games values(null, 1, 2, 80, 87, '12-01-2019')")
    conn.execute("insert into games values(null, 3, 4, 99, 80, '12-01-2019')")
    conn.execute("insert into games values(null, 5, 6, 101, 92, '12-01-2019')")


def print_all_teams():
    """ This function prints each team id and name. """
    print('Teams:')
    result = conn.execute("select * from teams")
    for row in result:
        print(row)

       
def print_all_games():
    """ This function prints every game, but the data is not very useful without looking up team names. """
    print('Games:')
    result = conn.execute("select * from games")
    for row in result:
        print(row)

       
def print_all_games_with_names():
    """
    Same as before but also looking up related data using joins.
    This is why I would recommend keeping a simple database with as few tables as possible.
    There is probably more than I can cover in one week so talk to me before you get too adventurous.
    """
    print('Games:')
    result = conn.execute("""
    select home.name, h_points, opp.name, o_points, date
    from games
    inner join teams home on games.h_team_id = home.id
    inner join teams opp on games.o_team_id = opp.id;
    """)
    for row in result:
        print(row)


def get_game(date):
    result = conn.execute("select * from games where date = ?", (date,))
    for row in result:
        print(row)

       
def insert_team(name):
    conn.execute("insert into teams values(?, ?)", (None, name))


if __name__ == '__main__':
    DB_PATH = 'example.db'
    # This shows an example of tables contain related data which would probably be best for you to avoid.
    # I would recommend trying to keep one or a small number of tables.
    # First we need to open the database, this will create it if it doesn't exist
    conn = sqlite3.connect(DB_PATH)

    # Create the db and insert some sample data
    # Unless changed it will delete all data in the database each time you run this.
    setup_db()

    # Printing the data
    print_all_teams()
    print_all_games()

    insert_team('Orlando Magic')


    # You should always close a database connection when you are done.
    conn.close()