"""ISRC utilities."""

from datetime import datetime

FIND_TRACK_SQL = """
    SELECT t.`id`
    FROM `accountingflat`.`dig_sales_detail` d
    INNER JOIN `art_relations`.`track` t
        ON d.`upc` = t.`upc`
        AND d.`cd` = t.`cd`
        AND d.`track_id` = t.`track_id`
    WHERE (d.`cd` <> 0 AND d.`track_id` <> 0)
    AND t.`isrc` IS NULL
"""

# SELECT ISRC where code like XXXXXYYXXXXX
# where YY is the 2 digit representation of the current year
GET_ISRC_SQL = """
    SELECT i.`isrc`
    FROM `isrcs` i
    WHERE i.`status` = 'unused'
    AND SUBSTRING(i.`isrc`, 6, 2) = '{str_year}'
    LIMIT 1
"""


UPDATE_TRACK_SQL = """
    UPDATE `track`
    SET `isrc` = '{isrc}'
    WHERE `id` = {track_id}
    AND `isrc` IS NULL
"""

UPDATE_ISRC_SQL = """
    UPDATE `isrcs`
    SET `status` = 'used',
    `last_updated` = NOW()
    WHERE `isrc` = '{isrc}'
    AND `status` = 'unused'
"""

LOG_MESSAGE = 'Track {track_id} updated with ISRC {isrc}'


def get_track_id_list(cursor):
    """Get a list of tracks with missing ISRCs."""
    cursor.execute(FIND_TRACK_SQL)
    return cursor.fetchall()


def allocate_track_isrc(cursor, track_id):
    """Update the database."""
    print('Setting ISRC for track id:', track_id)
    isrc = reserve_isrc(cursor)
    update_track_sql = UPDATE_TRACK_SQL.format(isrc=isrc, track_id=track_id)
    cursor.execute(update_track_sql)
    print(LOG_MESSAGE.format(track_id=track_id, isrc=isrc))


def reserve_isrc(cursor, max_retries=5):
    """Mark an ISRC as used and return it."""
    if max_retries < 1:
        raise Exception('Maximum ISRC fetch retries reached')
    unused_isrc = get_unused_isrc(cursor)
    cursor.execute(UPDATE_ISRC_SQL.format(isrc=unused_isrc))
    if not cursor.rowcount:
        # If no row was updated, it's likely another app beat us to it.
        return reserve_isrc(cursor, max_retries - 1)
    return unused_isrc


def get_unused_isrc(cursor):
    """Get an unused ISRC."""
    get_isrc_sql = GET_ISRC_SQL.format(str_year=datetime.now().strftime('%y'))
    cursor.execute(get_isrc_sql)
    result = cursor.fetchone()
    if not result:
        raise Exception('No unused ISRCs found')
    return result[0]