import psycopg2

class MusicPatcher:
    connection = False
    def __init__(self):
        self.connection = psycopg2.connect(user="postgres",
                                        port="5432",
                                        database="mediaserver")
        self.connection.set_client_encoding('UTF8')

    def __update(self, sql, params=()):
        cur = self.connection.cursor()
        cur.execute(sql, params)
        updated_rows = cur.rowcount
        print("{} row changed for {}".format(updated_rows , sql))
        self.connection.commit()
        cur.close()

    def __select(self, sql, params=()):
        cur = self.connection.cursor()
        cur.execute(sql, params)
        rows = cur.fetchall()
        cur.close()
        return rows
    def __update_music_metadata(self, id, title, artist, album,album_artist, orginal_info):
        sql = """ UPDATE MUSIC
                        SET title = %s, artist= %s, album= %s, album_artist= %s, comment= %s
                        WHERE id = %s"""
        # execute the UPDATE  statement
        self.__update(self,sql,(title, artist, album, album_artist, orginal_info, id))
    def close(self):
        if self.connection:
            self.connection.close()

    def process_music(self):
        postgreSQL_select_Query = "SELECT ID,path FROM track"
        track_records = self.__select(postgreSQL_select_Query)
        for track_record in track_records:
            path_tokens = track_record[1].split("/")
            tokens =path_tokens[len(path_tokens)-1].replace(".mp3","").split("_")
            if len(tokens) != 4:
                continue
            artist_tracks = self.__select("SELECT track from artist_track where track = {}".format(track_record[0]))
            if(len(artist_tracks) ==0):
                sql_query = "INSERT INTO artist_track (track, artist, artist_sort, artist_search, has_album_artist) values ({},'{}','{}','{}','f')".format(track_record[0],tokens[1],tokens[1],tokens[1])
                self.__update(sql_query)
            else:
                sql_query = "UPDATE artist_track set artist='{}', artist_sort='{}', artist_search='{}' where track = {}".format(tokens[1],tokens[1],tokens[1],track_record[0])
                self.__update(sql_query)

            album_tracks = self.__select("SELECT * from album_track where track = {}".format(track_record[0]))
            if(len(album_tracks) ==0):
                sql_query = "INSERT INTO album_track (track, album, album_sort, album_search, album_artist, album_artist_sort, album_artist_search,from_album_artist) values ({},'{}','{}','{}','{}','{}','{}','t')".format(track_record[0],tokens[2],tokens[2],tokens[2],tokens[1],tokens[1],tokens[1])
                self.__update(sql_query)
            else:
                sql_query = "UPDATE album_track set album='{}', album_sort='{}', album_search='{}', album_artist='{}',album_artist_sort='{}', album_artist_search='{}'  where track = {}".format(tokens[2],tokens[2],tokens[2],tokens[1],tokens[1],tokens[1],track_record[0])
                self.__update(sql_query)

            sql_query = "UPDATE track set title='{}' , title_search='{}', comment={} where id={}".format(tokens[0],tokens[0],tokens[3],track_record[0])
            self.__update(sql_query)

MusicPatcher().process_music()