import mysql.connector
from mysql.connector import Error

def connect():
    """ Connect to MySQL database """
    try:
        bd = mysql.connector.connect(host='localhost',
                                     database='bd',
                                     user='root',
                                     password='toor'
                                     )
        if bd.is_connected():
            print('Connected to MySQL database')

        return bd

    except Error as e:
        print(e)


def import_users(bd):
    """Importing from db to access arrays"""
    cursor = bd.cursor()
    cursor.execute("SELECT * FROM user")
    users = cursor.fetchall()

    for user in users:
        print(user)
    return users


def login():
    """Login prints and it's validation"""
    logged_in = False
    while not logged_in:
        print("\nUsername:")
        username = input()
        print("Password:")
        password = input()
        for x in range(len(users)):
            if username == users[x][1] and password == users[x][2]:
                logged_in = True
                id = users[x][0] - 1
            elif username == users[x][1] and password != users[x][2]:
                print("\nPassword errada\n")
                print("\nTente Outra vez\n")
    print("--------------Logged in Sucessfully as "+users[id][1]+"----------------")
    return id


def searchMusic():
    search = input()
    cursor = bd.cursor()
    command = "SELECT * FROM music WHERE nome = "+"'"+search+"'"
    cursor.execute(command)
    music = cursor.fetchall()
    return music

def searchAlbum():
    search = input()
    cursor = bd.cursor()
    command = "SELECT * FROM album WHERE name = " + "'" + search + "'"
    cursor.execute(command)
    album = cursor.fetchall()
    return album

def searchBanda():
    search = input()
    cursor = bd.cursor()
    command = "SELECT * FROM banda WHERE name = " + "'" + search + "'"
    cursor.execute(command)
    banda = cursor.fetchall()
    return banda

def searchArtista():
    search = input()
    cursor = bd.cursor()
    command = "SELECT * FROM artista WHERE name = " + "'" + search + "'"
    cursor.execute(command)
    artista = cursor.fetchall()
    return artista

def register():
    print("Username:")
    username = input()
    print("Password:")
    password = input()
    permission = 'U'
    cursor = bd.cursor()
    sql = "INSERT INTO user (username,password,permission) VALUES (%s, %s, %s)"
    val = (username, password, permission)
    cursor.execute(sql, val)
    bd.commit()

    print("record inserted.")

def searchType():

    """Find ID of certain type"""
    search = input()
    cursor = bd.cursor()
    command = "SELECT id FROM musictype WHERE typename = " + "'" + search + "'"
    cursor.execute(command)
    type_id = cursor.fetchone()

    """With the ID Search all the albums that have that type"""

    command = "SELECT * FROM album WHERE musictype_id = "+ type_id
    cursor.execute(command)
    albums = cursor.fetchall()
    return albums

# search band
def menu():
    logged_in = False
    while (1):
        if logged_in == False:
            print("--------------------------Start--------------------------------")
            print("-----------------------1. login--------------------------------")
            print("-----------------------2. Register-----------------------------")
            value = input()
        if value == "1":
            user_id = login()
            logged_in = True

        elif value == "2":
            register()
            print("Exiting")
            exit()

        if logged_in:

            """Print Menu according the permission that they have"""
            print("--------------------------Menu--------------------------------")
            print("----------------------1. Choose Playlist----------------------")
            print("----------------------2. Search-------------------------------")
            print("----------------------3. My Playlists-------------------------")
            print("----------------------4. Change Password----------------------")
            if users[user_id][3] == 'E':
                print("----------------------5. Edit --------------------------------")

                choice = input()
                if (choice == 1):
                # chosse playlist
                elif (choice == 2):
                    Search()
                elif (choice == 3):
                # my playlist
                elif (choice == 4):
                # change password
                if (choice == 5):
                    # edit album
                    print("---------------------------EDIT-------------------------------")
                    print("----------------------1. Edit Users---------------------------")
                    print("----------------------2. Edit Album---------------------------")
                    print("----------------------3. Edit Artists-------------------------")
                    print("----------------------4. Edit Musics--------------------------")
                    choicev = input()
                    if (choicev == 1):
                        print("Edit Users")
                        editU("user")
                    elif (choicev == 2):
                        editAl("album")
                    elif (choicev == 3):
                        editA("artista")
                    elif (choicev == 4):
                        editM("music")


                elif (choice == "2"):
                    Search()
                elif (choice == "3"):
                    print(" my playlist")
                elif (choice == "4"):
                    print("change password")




def Search():
    print("---------------------------SEARCH-----------------------------")
    print("----------------------1. Search Music-------------------------")
    print("----------------------2. Search by Album-------------------------")
    print("----------------------3. Search by Type------------------------")
    print("----------------------4. Search by Artist------------------------")
    print("----------------------5. Search by Band--------------------------")
    choice = input()
    if (choice == "1"):
        music = searchMusic()
        print("Nome:" + music[0][1])
        print("------------------------MUSIC-----------------------------")
        print("----------------------1. Download-------------------------")
        print("----------------------2. Comment--------------------------")
        choice = input()
        if (choice == "1"):
            print("Download")
            # edit album
        elif (choice == "2"):
            # edit users
            print("Comment on Album")

    if (choice == "2"):
        album = searchAlbum()
        print("---------------------------------")
        print("Nome: " + album[0][1])
        print("Release Date: " + album[0][2])
        print("Rating: " + album[0][4])
        print("Description: " + album[0][3])
        print("---------------------------------")

    elif (choice == 3):
        print("Search type:")
        album_results = searchType()
        print("------------------------------------------------------------")
        print(album_results)
        print("------------------------------------------------------------")

    elif (choice == 4):
        print("Search artist:")
        artista = searchArtista()
        print("------------------------------------------------------------")
        print("Nome: " + artista[0][1])
        print("Birth Date: " + artista[0][2])
        print("Description: " + artista[0][3])
        print("------------------------------------------------------------")


    elif (choice == 5):
        print("Search Band:")
        banda = searchBanda()
        print("------------------------------------------------------------")
        print("Nome: " + banda[0][1])
        print("Description: " + banda[0][2])
        print("------------------------------------------------------------")

"""Start Code"""

bd = connect()

users = import_users(bd)

menu()

bd.close()