from db.art_relations import conn as art_relations_conn


def get_all_duplicate_releases(cursor):
    duplicates_query = """SELECT trackdata.upc, min(trackdata.track_id) as min_dup,
    max(total_tracks) as total_tracks
    FROM ( SELECT t.upc,t.track_id, uniquedata.totaltracks AS total_tracks
           FROM track t
           INNER JOIN
           (
            SELECT upc, COUNT(*)  AS totaltracks
            FROM track
            WHERE upc IN
                ( SELECT DISTINCT(trackdata.upc) AS uniqueupc
                  FROM
                    (SELECT COUNT(*), t.upc, t.cd, t.track_id
                    FROM track t
                    INNER JOIN releases r ON r.upc = t.upc
                    INNER JOIN youtube_channel_upc ycu ON ycu.`upc` = r.`upc`
                    WHERE r.release_status = 'in_content' AND r.deletions = 'N'
                    AND ycu.active = 'Y'
                    GROUP BY t.upc, t.cd, t.track_id
                    HAVING COUNT(*) > 1
                    ORDER BY r.release_id DESC) AS trackdata
                )
                GROUP BY upc
            ) AS uniquedata ON t.upc = uniquedata.upc
            GROUP BY t.upc, t.cd, t.track_id
            HAVING COUNT(t.track_id) > 1
        ) AS trackdata
    GROUP BY trackdata.upc
    ORDER BY trackdata.upc asc
    """
    cursor.execute(duplicates_query)
    return cursor.fetchall()


def get_duplicate_track_data(cursor, upc, min_dup):
    track_query = """SELECT t.id,t.upc,t.track_id
           FROM track t
           WHERE upc = {}
           AND track_id >= {}
           ORDER BY track_id, id
           """.format(upc, min_dup)
    cursor.execute(track_query)
    return cursor.fetchall()


def print_duplicate_track_query(track_data, min_dup, count):
    new_track_id = min_dup + count
    tuid = track_data['id']
    query_string = """UPDATE track SET track_id = {} WHERE id = {};;""".format(str(new_track_id), tuid)
    print(query_string)

cursor = art_relations_conn.cursor()

releases_with_duplicates = get_all_duplicate_releases(cursor)

for item in releases_with_duplicates:
    upc = item['upc']
    min_dup = int(item['min_dup'])
    print(upc)
    track_data = get_duplicate_track_data(cursor, upc, min_dup)
    # remove first duplicate since we won't be increasing the track_id
    track_data.pop(0)
    count = 1
    for track in track_data:
        print_duplicate_track_query(track, min_dup, count)
        count += 1