import MySQLdb

userID = 0

db = MySQLdb.connect('hopper.wlu.ca','haba9050', 'bigtop4','haba9050')
cursor = db.cursor()

sql = "show tables;"


cursor.execute(sql)
data = cursor.fetchall()

print(data)



Musicians = """ CREATE TABLE IF NOT EXISTS Musicians (
            ssn varCHAR(10),
            name varCHAR(30),
            PRIMARY KEY (ssn))"""

Songs = """CREATE TABLE IF NOT EXISTS Songs(
                songId INTEGER NOT NULL AUTO_INCREMENT,
                author varCHAR(30),
                title varCHAR(30),
                albumIdentifier INTEGER NOT NULL,
                PRIMARY KEY (songId),
                FOREIGN KEY (albumIdentifier) References Album (albumIdentifier) )"""

Perform = """CREATE TABLE IF NOT EXISTS Perform(
            songId INTEGER ,
            ssn varCHAR(10),
            title varCHAR(30),
            PRIMARY KEY (ssn,songId),
            FOREIGN KEY (songId) References  Songs(songId),
            FOREIGN KEY (ssn) References Musicians (ssn))"""

Album = """CREATE TABLE IF NOT EXISTS Album(
                albumIdentifier INTEGER NOT NULL AUTO_INCREMENT,
                ssn varCHAR(10),
                producerName varchar(30),
                copyrightDate DATE,
                title CHAR(30),
    PRIMARY KEY (albumIdentifier),
    FOREIGN KEY (ssn) References  Musicians(ssn))"""

Users = """CREATE TABLE IF NOT EXISTS Users(
        uid INTEGER NOT NULL AUTO_INCREMENT,
        username varCHAR(20),
        password varCHAR(20),
        address varCHAR(30),
        PRIMARY KEY(uid)
        )"""

CreditCards = """CREATE TABLE IF NOT EXISTS CreditCards(
                uid INTEGER,
                creditCard INTEGER,
                PRIMARY KEY (creditCard),
                FOREIGN KEY (uid) REFERENCES  Users(uid)
                )"""
Likes = """CREATE TABLE IF NOT EXISTS Likes(
        lid INTEGER NOT NULL AUTO_INCREMENT,
        uid INTEGER NOT NULL,
        ssn varCHAR(10),
        PRIMARY KEY(lid),
        FOREIGN KEY (uid) REFERENCES  Users(uid),
        FOREIGN KEY (ssn) REFERENCES  Musicians(ssn)
        )"""

Cart = """CREATE TABLE IF NOT EXISTS Cart(
        cid INTEGER NOT NULL AUTO_INCREMENT,
        uid INTEGER,
        albumIdentifier INTEGER,
        PRIMARY KEY(cid),
        FOREIGN KEY (uid) REFERENCES  Users(uid),
        FOREIGN KEY (albumIdentifier) REFERENCES  Album(albumIdentifier))"""

cursor.execute(Musicians)
cursor.execute(Album)
cursor.execute(Songs)
cursor.execute(Perform)
cursor.execute(Users)
cursor.execute(CreditCards)
cursor.execute(Likes)
cursor.execute(Cart)



def search(cursor, value):
    value = '"%'+value+'%"'
    #cursor.execute('select * from Songs as S, Album as A, Musicians M where S.title like {0} or A.title like {0} or M.name like {0} or A.producerName like {0}'.format(value))
    cursor.execute('select * from Songs as S, Album as A, Musicians M where S.title like {0} or A.title like {0} or M.name like {0}'.format(value))

    searched = cursor.fetchall()
    print(searched)


def register(username, password, address, phone,):
    cursor.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
    # cursor.execute('insert into Users values({0},{1},{2},{3},{4})'.format(username, password, address, phone))
    print("Created new user: " + username)
    db.commit()
    addCreditCard()

def login(username, password):
    cursor.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
    data = cursor.fetchone()
    if data != "()":
        print (data)
        global userID
        userID = data[0]
        print("You have successfully logged in with userid {0}".format(userID))
    else:
        print("Login failed")


def addItem(albumName):
    if (userID != 0):
        cursor.execute('select * from Album as A where A.title = "{0}"'.format(albumName))
        albumData = cursor.fetchone()
        cursor.execute('insert into Cart (uid, albumIdentifier) VALUES("{0}", "{1}")'.format(userID, albumData[0]))
        print("Added {0} to Cart".format(albumData[3]))
        db.commit()

def addCreditCard(creditCard):
    if (UserID != 0):
        cursor.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(userID,creditCard))
        db.commit()


def checkOut():
    if (userID != 0):
        cursor.execute('select * from Cart as C, Album as A where C.albumIdentifier = A.albumIdentifier and C.uid = "{0}"'.format(userID))
        cartData = cursor.fetchall()
        for i in cartData:
            cursor.execute('insert into Likes (uid, ssn) values ("{0}", "{1}")'.format(userID, i[4]))
            db.commit()
            print (i)
            cursor.execute('delete from Cart where uid = "{0}" and albumIdentifier = "{1}"'.format(userID, i[2]))
            db.commit()
            print("Cart has been cleared.")
    purchaseTotal()


def purchaseTotal():
    count = 0
    #print("You like these albums!")
    if (userID != 0):
        cursor.execute('SELECT * from Likes where uid = "{0}"'.format(userID))
        dataLikes = cursor.fetchall()
        for x in dataLikes:
            count += 1
            #print x
    print("Your total purchases are now: {0}".format(count))
#search(cursor, 'swag')
#register('Bobbie Dyl', '123456789', '123 fake st','12396358980')
login("Bobbie Dyl", '123456789')
#addItem('Album of Swag')
checkOut()


print(userID)