DBFINAL

"""
-------------------------------------------------------
notown.py
-------------------------------------------------------
Author:  Andrew Georgescu
ID:      130280590
Email:   geor0590@mylaurier.ca
-------------------------------------------------------
"""
from _mysql_connector import MySQL


"""
-------------------------------------------------------
Main Program
-------------------------------------------------------
"""


import mysql.connector
db = mysql.connector.connect(user = 'geor0590', host = 'hopper.wlu.ca', password = 'bigtop4',database = 'geor0590')
cur = db.cursor()

UID = -1

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


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_Producer = """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))"""

Songs_Appears = """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) )"""

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))"""

Credit_Cards = """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))"""

def choices():
    print("Menu:\n1. Login\n2. Search\n3. Register new user\n4. Add items to cart\n5. Check Out\n6. See purchase total (rewards)\n");
    choice = input("Enter Selection: ");
    return choice;

def one():
    login(input("Please enter username: "), input("Please enter password: "))
    return;

def two():
    val = input("Search Text: ");
    search(val)
    return;

def three():
    register(input("Please enter username: "), input("Please enter password: "), input("Please enter address: "), input("Please enter phone number: "))
    return;

def four():
    item_add(input("Please enter exact album name: "));
    return;

def five():
    checkout()
    return;

def six():
    purchase_total()
    return;

def search(val):
    val = '"%'+val+'%"'
    cur.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(val))
    searched = cur.fetchall()
    print(searched)


def register(username, password, address, phone,):
    cur.execute('insert into Users (username, password, address) VALUES ("{0}","{1}","{2}")'.format(username, password, address))
    print("Created new user: " + username)
    db.commit()
    login (username, password)
    i = ""
    print ("Add credit cards. Use text STOP when finished")
    while i != "STOP":
        i = input("Add new credit card: ")
        if i == "STOP":
            break
        cc_add(i)

def login(username, password):
    cur.execute('select * from Users where username = "{0}" and password = "{1}"'.format(username,password))
    data = cur.fetchone()
    if data != "()":
        print (data)
        global UID
        UID = data[0]
        print("User: {0} has logged on".format(UID))
    else:
        print("Login unsuccessful")


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

def cc_add(creditCard):
    if (UID != -1):
        cur.execute('insert into CreditCards (uid, creditCard) values ("{0}","{1}")'.format(UID,creditCard))
        db.commit()


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


def purchase_total():
    count = 0
    if (UID != -1):
        cur.execute('SELECT * from Likes where uid = "{0}"'.format(UID))
        dataLikes = cur.fetchall()
        for x in dataLikes:
            count += 1
    print("Your total purchases are now: {0}".format(count))

cur.execute(Musicians)
cur.execute(Album_Producer)
cur.execute(Songs_Appears)
cur.execute(Perform)
cur.execute(Users)
cur.execute(Credit_Cards)
cur.execute(Likes)
cur.execute(Cart)



i=0;
while i<1:
    selection = choices();
    if selection == '1':
        one();
    elif selection == '2':
        two()
    elif selection == '3':
        three();
    elif selection == '4':
        four();
    elif selection == '5':
        five();
    elif selection == '6':
        six();
    else:
        print("Invalid.\n");