import sqlite3

"""Connect to database connect() function takes in a string for the database name and returns a sqlite3.Connection class object.
If the database is already present, it just returns a Connection object, else the database is created and 
then the Connection object to the newly created database is returned"""

#Step 1: Create a the book table
def connect():
    conn=sqlite3.connect("book.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE if NOT exists book (id INTEGER PRIMARY KEY,title text,author text,year INTEGER,isbn INTEGER)")
    conn.commit()
    conn.close()


#Step 2: Function for inserting new book into book table
def insert(title,author,year,isbn):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute('INSERT INTO book VALUES(NULL,?,?,?,?)',(title,author,year,isbn))
    conn.commit()

#Step 3: Create request table function  
def request():
    conn=sqlite3.connect("book.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE if NOT exists request (id PRIMARY KEY,title text,author text,year INTEGER,isbn INTEGER)")
    conn.commit()
    conn.close()

#Step 4: Function for storing book request into request table
def request_insert(title,author,year,isbn):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute('INSERT INTO request VALUES(NULL,?,?,?,?)',(title,author,year,isbn))
    conn.commit()
    conn.close()

#Step 5: Function for viewing book request made
def request_view(title="",author="",year="",isbn=""):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("SELECT * FROM request")
    rows=cur.fetchall()
    conn.close()
    return rows

#Step 6: Function for deleting old request made
def request_delete(title):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("DELETE FROM request WHERE title=?",(title,) )
    conn.commit()
    conn.close()


#Step 7: Function for  viewing book information
def view():
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("SELECT * FROM book")
    rows=cur.fetchall()
    conn.close()
    return rows

#Step 8: Function for searching for book
def search(title="",author="",year="",isbn=""):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("SELECT * FROM book WHERE title=? OR author=? OR year=? OR isbn=?",(title,author,year,isbn))
    rows=cur.fetchall()
    conn.close()
    return rows

#Step 9: Function for deleting book information
def delete(id):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("DELETE FROM book WHERE id=?",(id,) )
    conn.commit()
    conn.close()

#Step 10: Function for  updating book information
def update(id,title,author,year,isbn):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("UPDATE book SET title=?,author=?,year=?,isbn=? WHERE id=?",(title,author,year,isbn,id))
    conn.commit()
    conn.close()

#Step 11: We also need to keep record and view book issued by administrator to student.
def issue():
    conn=sqlite3.connect("book.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE if NOT exists issue(id INTEGER NOT NULL ,title text,author text,year INTEGER,isbn INTEGER)")
    conn.commit()
    conn.close()

def issue_delete(id):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("DELETE FROM issue WHERE id=?",(id,) ) 
    conn.commit()
    conn.close()

def issue_insert(id):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute('INSERT INTO issue SELECT * FROM book WHERE id=?',(id,))
    conn.commit()
    conn.close()

def issue_view(title="",author="",year="",isbn=""):
    conn=sqlite3.connect('book.db')
    cur = conn.cursor()
    cur.execute("SELECT * FROM issue")
    rows=cur.fetchall()
    conn.close()
    return rows