import sqlite3


SEP = ','  # The separator of our CSV files


def create_customers(db, f):
    """Creates and populates the Customer table with the data from f.

    Args:
        db: the filename of a SQLite database to connect to
        f: a freshly opened file in the format of addresses.txt

    Returns:
        None
    """
    # connect to the database and create a cursor
    con = sqlite3.connect(db)
    cur = con.cursor()

    # drop the customers table if it already exists; re-create it
    cur.execute('DROP TABLE IF EXISTS customers')
    cur.execute(
        'CREATE TABLE customers(id TEXT, last_name TEXT, first_name TEXT, '
        'street_num TEXT, street_name TEXT, city TEXT, province TEXT, '
        'code TEXT, tel TEXT, alt_tel TEXT, email TEXT)')

    query = ('INSERT INTO customers VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
    # Populate the Custormer Table
    # Loop through each line in the file:
    for line in f:
        # Write the body of this loop yourself. Handle the whitespace around
        # each line, split each line on SEP, and insert the relevant values
        # into the customers table.
        # In lecture, David showed you how to use a tuple of values to insert
        # into placeholders in a SQL query. Eg:
        # cur.execute(query, tuple_of_values)
        # Hint: one can also insert a list instead of a tuple, eg:
        # cur.execute(query, list_of_values)
        # Hint: line.strip().split(SEP) will return you a list.
        line = line.strip().split(SEP)
        cur.execute(query, line)

    # close the cursor, commit our changes, and close the connection
    cur.close()
    con.commit()
    con.close()


def create_books(db, f):
    """Creates and populates the books table with the data from f.

    Args:
        db: the filename of a SQLite database to connect to
        f: a freshly opened file in the format of books.txt

    Returns:
        None
    """
    # connect to the database and create a cursor
    con = sqlite3.connect(db)
    cur = con.cursor()

    # Populate the books Table
    # Loop through each line in the file:
    # PUT YOUR CODE HERE
    cur.execute('DROP TABLE IF EXISTS books')
    cur.execute('CREATE TABLE books(id TEXT, title TEXT, author TEXT)')

    query = ('INSERT INTO books VALUES (?, ?, ?)')
    for line in f:
        line = line.strip().split(SEP)
        cur.execute(query, line)

    # close the cursor, commit our changes, and close the connection
    cur.close()
    con.commit()
    con.close()


def create_loans(db):
    """Creates the loans table.

    The table may contain any entries afterwards, including none.

    Args:
        db: the filename of a SQLite database to connect to

    Returns:
        None
    """

    # connect to the database and create a cursor
    con = sqlite3.connect(db)
    cur = con.cursor()

    # Create the loans table; drop the table beforehand if it already exists
    # PUT YOUR CODE HERE
    cur.execute('DROP TABLE IF EXISTS loans')
    cur.execute('CREATE TABLE loans(customer_id TEXT, book_id TEXT, '
                'borrowed_date TEXT, due_date TEXT, return_date TEXT)')
    # Note that it's slightly unrealistic to see a docstring of a real function
    # saying "The table may contain any entries afterwards". I'm only doing
    # this so that you can add your own testing entries and then not have to
    # worry about deleting them before submitting.

    # close the cursor, commit our changes, and close the connection
    cur.close()
    con.commit()
    con.close()


def run_query(db, query, args=None):
    """Returns the results of running the given query on database db.

    Args:
        db: the filename of a SQLite database to connect to
        query: a str containing a SQLite query to run on db
        args: optional argument. If provided, contains the query arguments (ie.
            the values which will inserted into query placeholders)

    Returns:
        a list of all tuples returned by the given query on db
    """
    # You don't have to do anything for this function! It's already written for
    # you. It's meant as a helper function to reduce the amount of copy-pasting
    # you'd have to do.
    con = sqlite3.connect(db)
    cur = con.cursor()
    if args is None:
        cur.execute(query)
    else:
        cur.execute(query, args)
    # Note that we're using cur.fetchall() here instead of a for loop because
    # we WANT a list of the values we've SELECTed.
    data = cur.fetchall()
    cur.close()
    con.close()
    return data


def get_info_toronto(db):
    """Returns the name, address, phone numbers and email for all residents of
    Toronto according to the database db.

    Args:
        db: the filename of a SQLite database to connect to

    Returns:
        a list of tuples of (first name, last name, street number, street name,
        primary phone number, alternate phone number, email)
    """
    query = ('SELECT first_Name, last_name, street_num, street_name, tel, '
             'alt_tel, email '
             'FROM customers '
             'WHERE city = "Toronto"')
    return run_query(db, query)


def get_info_by_id(db, cust_id):
    """Returns the name, address, phone numbers and email for all customers
    with the given id according to the database db.

    Args:
        db: the filename of a SQLite database to connect to
        cust_id: a str denoting a customer id

    Returns:
        a list of tuples of (first name, last name, street number, street name,
        primary phone number, alternate phone number, email) of all customers
        with the given id (since the id is unique, this list will contain at
        most one element)
    """
    # Hint: a one-item tuple containing 'blah' looks like: ('blah',)
    query = ('SELECT first_Name, last_name, street_num, street_name, tel, '
             'alt_tel, email '
             'FROM customers '
             'WHERE id = (?)')
    return run_query(db, query, (cust_id,))


def get_id_by_name(db, last, first):
    """Returns a list of IDs of every customer with the given last and first
    names.

    Args:
        db: the filename of a SQLite database to connect to
        last: a str. A customer's last name
        first: a str. A customer's first name

    Returns: a list of strs of customer IDs who match the given name
    """
    # Note: for this function, you can't just run_query and then return the
    # result because the return type won't be correct. You'll need to post-
    # process the results.
    query = ('SELECT id '
             'FROM customers '
             'WHERE last_name = (?) AND first_name = (?)')
    result = run_query(db, query, (last, first))
    for i, row in enumerate(result):
        assert len(row) == 1
        result[i] = row[0]  # Replace each one-element tuple with its element
    return result


def get_all_loans_by_id(db, cust_id):
    """Returns the book ID, borrowed date, due date, and return date for all
    loans taken out by the customer with the given ID

    Args:
        db: the filename of a SQLite database to connect to
        cust_id: a str denoting a customer id

    Returns:
        a list of tuples of (book ID, borrowed date, due date, and return date)
        of all loans taken out by customer with the given id
    """
    query = ('SELECT book_id, borrowed_date, due_date, return_date '
             'FROM loans '
             'WHERE customer_id = (?)')
    return run_query(db, query, (cust_id,))


if __name__ == '__main__':
    # populate the tables
    db = 'library.db'
    addresses_file = 'addresses.txt'
    books_file = 'books.txt'
    with open(addresses_file) as f:
        create_customers(db, f)
    with open(books_file) as f:
        create_books(db, f)
    create_loans(db)

    # Some sanity checks for your convenience. They assume you haven't modified
    # the files addresses.txt or books.txt. They should all print True.
    # Uncomment them as you go.
    # expected = []
    # print(get_info_toronto(db) == expected)

    # cust_id = 'NONEXISTENT ID'
    # expected = []
    # print(get_info_by_id(db, cust_id) == expected)

    # cust_id = '730'
    # expected = [('Kristin', 'Burgess', '7', 'Walton Street', '985-390-1851',
    #              '906-365-7246', 'kristin.burgess@utoronto.ca')]
    # print(get_info_by_id(db, cust_id) == expected)

    # last = 'Wiley'
    # first = 'Robert'
    # expected = ['816']
    # print(get_id_by_name(db, last, first) == expected)