import mysql.connector

connector = mysql.connector.connect(
    host="localhost",
    user="root",
)

cursor = connector.cursor()


def setup():
    cursor.execute("CREATE DATABASE IF NOT EXISTS balalaika;")
    cursor.execute("USE balalaika;")
    cursor.execute("DROP TABLE IF EXISTS song;")
    cursor.execute("DROP TABLE IF EXISTS album;")
    cursor.execute("DROP TABLE IF EXISTS artist;")

    cursor.execute("""
        CREATE TABLE artist (
            id int NOT NULL AUTO_INCREMENT,
            name varchar(255),

            PRIMARY KEY (id)
        );
    """)

    cursor.execute("""
        CREATE TABLE album (
            id int NOT NULL AUTO_INCREMENT,
            name varchar(255),
            cover varchar(510),
            artist_id int,

            PRIMARY KEY (id),
            FOREIGN KEY (artist_id) REFERENCES artist(id)
        );
    """)

    cursor.execute("""
        CREATE TABLE song (
            id int NOT NULL AUTO_INCREMENT,
            name varchar(255),
            lyrics TEXT,

            album_id int,

            PRIMARY KEY (id),
            FOREIGN KEY (album_id) REFERENCES album(id)
        );
    """)

    cursor.execute("ALTER TABLE song CONVERT TO CHARACTER SET utf8")
    cursor.execute("ALTER TABLE album CONVERT TO CHARACTER SET utf8")
    cursor.execute("ALTER TABLE artist CONVERT TO CHARACTER SET utf8")


def process_albums(album_list):
    [process_album(album, album_id)
     for album_id, album in enumerate(album_list)]


def process_album(album, album_id):
    upload_album(album)
    [upload_song(song, album_id+1) for song in album.songs]


def upload_album(album):
    cursor.execute("""
        INSERT INTO album (
            name, cover, artist_id
        )
        VALUES (
            %(name)s, %(cover)s, %(artist_id)s
        );
    """, {
        'name': album.name,
        'cover': album.cover,
        'artist_id': album.artist
    })


def upload_song(song, album_id):
    cursor.execute("""
        INSERT INTO song (
            name, lyrics, album_id
        )
        VALUES (
            %(name)s, %(lyrics)s, %(album_id)s
        )
    """, {
        'name': song.name,
        'lyrics': song.lyrics,
        'album_id': album_id
    })


def process_artists(artist_names):
    [process_artist(artist) for artist in artist_names]


def process_artist(artist):
    cursor.execute("""
        INSERT INTO artist (
            name
        )
        VALUES (
            %(name)s
        )
    """, {
        'name': artist,
    })


def close():
    cursor.close()
    connector.commit()
    connector.close()