########## Intro to SQLite and Creating a Database ##########

import sqlite3 #included with Python

conn = sqlite3.connect(":memory:")

#creating this goes off of the cwd.  CD in terminal changes cwd
#so either cd to correct folder or figure out how to do a more concrete path:
#import os.path
#conn = sqlite3.connect(os.path.realpath('./books.db'))


#we use a cusror object to work with the database 
c = conn.cursor()
#https://stackoverflow.com/questions/6318126/why-do-you-need-to-create-a-cursor-when-querying-a-sqlite-database


########## Create a Table with Python ##########

# Create table
c.execute('''CREATE TABLE books
             (title TEXT, pages INTEGER)''')


########## Insert Data with Python ##########


# Insert a row of data
c.execute("INSERT INTO books VALUES ('Are You My Mother?', 72)")

# Save (commit) the changes
conn.commit()


########## Retrieve Data in Python ##########

c.execute('SELECT * FROM books WHERE title=?', ('Are You My Mother?',))
print(c.fetchone())

books = [
    ('The Digging-est Dog', 72),
    ('Goodnight Moon', 31),
    ('The Giving Tree', 66)
]

c.executemany('INSERT INTO books VALUES (?,?)', books)
conn.commit()

c.execute('SELECT * FROM books')
data = c.fetchall()
print(data)


########## Delete SQLite data in Python ##########

c.execute('SELECT * FROM books WHERE title=?', ('Are You My Mother?',))
print(c.fetchone())
c.execute('DELETE FROM books WHERE title=?', ('Are You My Mother?',)) #don't forget where lol
c.execute('SELECT * FROM books WHERE title=?', ('Are You My Mother?',))
print(c.fetchone())


########## Update SQLite Data in Python ##########

c.execute('SELECT * FROM books WHERE title=?', ('Goodnight Moon',))
print(c.fetchone())
c.execute('UPDATE books SET pages="30" WHERE title="Goodnight Moon"')
c.execute('SELECT * FROM books WHERE title=?', ('Goodnight Moon',))
print(c.fetchone())
conn.commit
c.close()

########## Create an SDK ##########


import booksSDK
from book import Book

book = Book("Are You My Mother?", 1000)
print("Added book id:", booksSDK.add_book(book))
print("Get book by title:", booksSDK.get_book_by_title(book.title))
print("Not a valid book:", booksSDK.get_book_by_title("yeet"))

booksSDK.add_book(Book('The Digging-est Dog', 76))
print("get books:", booksSDK.get_books())

book = booksSDK.update_book(book, book.title, 76)
print("Updated book:", book)
print("Deleted:", booksSDK.delete_book(book))
print("After delete:", booksSDK.get_book_by_title("Are You My Mother?"))

print("All books:", booksSDK.get_books())