"""Backfill metadata from AR"""

from lambdacommon import common_query
from lambdacommon import mysql
import config


AR_DB_CONFIG = {
    'host': 'reportsar02.theorchard.com',
    'user': 'ratoui',
    'password': '<>',
    'database': 'art_relations'
}

AR_QUERY = f"""
    SELECT r.upc, r.release_name, DATE_FORMAT(r.release_date, '%%Y-%%m-%%d') as release_date, 
           r.label, a.name as artist, t.track_name, t.isrc, t.id as tuid, 
           CONCAT(r.upc, '_', t.cd, '_', t.track_id) as song_name
    FROM releases r 
    INNER JOIN artist_info a ON a.artist_id = r.artist_id
    LEFT JOIN track t ON r.upc = t.upc
    LEFT JOIN genre g ON r.genre_id = g.genre_id
    WHERE r.upc = %s and t.cd = %s and t.track_id = %s;
"""

BACKFILL_QUERY = f"""
    UPDATE songs SET 
        upc = %(upc)s,
        release_name = %(release_name)s,
        release_date = %(release_date)s,
        label = %(label)s,
        artist = %(artist)s,
        track_name = %(track_name)s,
        isrc = %(isrc)s,
        tuid = %(tuid)s
    WHERE song_name = %(song_name)s
"""


if __name__ == '__main__':
    with mysql.mysql_connection(**config.DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            cursor.execute(common_query.SELECT_SONGS)
            songs = list(cursor)
            with mysql.mysql_connection(**AR_DB_CONFIG) as ar_conn:
                with ar_conn.cursor() as ar_cursor:
                    for song in songs:
                        song_name = song['song_name']
                        parts = song_name.split('_')
                        ar_cursor.execute(AR_QUERY, (parts[0], parts[1], parts[2],))
                        metadata = ar_cursor.fetchone()
                        cursor.execute(BACKFILL_QUERY, metadata)