import timeit
import pymysql
import requests

from db import pg_connection
from db.map_assets_to_tracks_sql import *
from sys import exit as sysexit
from sqlalchemy import select
from configparser import ConfigParser
from helpers import art_relations_helpers as arhelp
from masters_registry_backfill.transform_YT_ownership import *

start = timeit.default_timer()

config = ConfigParser()

if not config.read('map_assets_to_tracks.ini'):
    sysexit("map_assets_to_tracks.ini could not be found.")

ar_host = config.get('art_relations', 'ar_host')
ar_user = config.get('art_relations', 'ar_user')
ar_password = config.get('art_relations', 'ar_password')
ar_database = config.get('art_relations', 'ar_database')
ar_port = config.getint('art_relations', 'ar_port')

target_table = config.get('target', 'target_table')
target_host = config.get('target', 'target_host')
target_user = config.get('target', 'target_user')
target_password = config.get('target', 'target_password')
target_database = config.get('target', 'target_database')
target_port = config.getint('target', 'target_port')

source_table = config.get('source', 'source_table')
source_host = config.get('source', 'source_host')
source_user = config.get('source', 'source_user')
source_password = config.get('source', 'source_password')
source_database = config.get('source', 'source_database')
source_port = config.getint('source', 'source_port')
source_schema = config.get('source', 'source_schema')

result_chunk_length = config.getint('config', 'row_step')
result_boundary = config.getint('config', 'max_rows')
start_row = config.getint('config', 'start_row')

# Target mysql connection
target_conn = pymysql.connect(host=target_host,
                              user=target_user,
                              password=target_password,
                              db=target_database,
                              port=target_port,
                              charset='utf8mb4',
                              cursorclass=pymysql.cursors.DictCursor)

# art_relations mysql connection
ar_conn = pymysql.connect(host=ar_host,
                          user=ar_user,
                          password=ar_password,
                          db=ar_database,
                          port=ar_port,
                          charset='utf8mb4',
                          cursorclass=pymysql.cursors.DictCursor)

# Source PostgreSQL connection
source_conn, meta = pg_connection.connect(
    source_user, source_password, source_database, source_host, source_schema,
    source_port)

records = meta.tables[source_table]

# Target Cursor
t_cursor = target_conn.cursor()

# Art Relations Cursor
ar_cursor = ar_conn.cursor()

# Check for table existence
t_cursor.execute(check_table_query, target_table)
check_table = t_cursor.fetchone()

# Make table if necessary
if not check_table:
    t_cursor.execute(create_table_query.format(target_table))
    print('Table `{}` created.'.format(target_table))

t_cursor.execute(truncate_target_table.format(target_table))
print('Table `{}` truncated.'.format(target_table))


# This function makes use of GLOBAL namespace variables instantiated in the
# main body - For legibility in the if/else block below
def insert_tuid(track_unique_id, msg):

    # Check for delivery history
    if upc and arhelp.is_upc(upc):
        ar_cursor.execute(get_delivery_history, (upc))

        date_results = ar_cursor.fetchone()
    else:
        date_results = None

    if not date_results:
        delivery = "ERROR"
    else:
        delivery = date_results['date_delivered']

    t_cursor.execute(
        insert_tuid_query.format(target_table),
        (custom_id,
         filename,
         asset_id,
         upc,
         isrc,
         track_unique_id,
         msg,
         derived_ownership,
         delivery
         ))
territories_standard = 'ISO_3166_1_2016'

headers = {'Correlation-Id': 'helpmeicantseesogooddammnit'}

r = requests.get(
    'http://ows-territories.theorchard.io/territory/{}'
        .format(territories_standard), headers=headers)


# Basically array_column(). Returns a list
def p(index):
    return [val[index] for val in r.json().get('items')]

# Grab the territory list
ISO_3166_1_2016_list_2_chr = p('territory_code_a2')

# TODO: add delivery_history check for orch_deliv_date
# TODO: UPC/ISRC for missing combos
for i in range(
        start_row, result_boundary, result_chunk_length):
    loop_start = timeit.default_timer()  # TODO: REMOVE ME
    print(
        'Now processing rows {0} to {1} of {2}'.format(
            i, i + result_chunk_length, result_boundary))

    clause = select([records.c.row_id,
                     records.c.filename,
                     records.c.asset_id,
                     records.c.isrc,
                     records.c.upc,
                     records.c.custom_id,
                     records.c.ownership,
                     records.c.conflicting_territories]) \
        .where(records.c.asset_type == 'Sound Recording') \
        .limit(result_chunk_length) \
        .offset(i)

    yt_rows = clause.execute()

    db_stop = timeit.default_timer()

    print("DB query took {} secs.".format(
        db_stop - loop_start))

    for asset in yt_rows:

        # Init some loop vars
        custom_id = asset.custom_id

        if custom_id:
            custom_id_list = custom_id.split('_')
        else:
            custom_id_list = []

        upc = asset.upc
        is_upc = arhelp.is_upc(asset.upc)
        isrc = asset.isrc
        is_isrc = arhelp.is_isrc(asset.isrc)
        ydh_id = asset.row_id
        asset_id = asset.asset_id
        filename = asset.filename
        tuid = None

        # Transform ownership
        derived_ownership = transform_ownership(
            asset.ownership, ISO_3166_1_2016_list_2_chr)

        # DEPRECATED
        # derived_conflicts = transform_conflicts(
        #     asset.conflicting_territories, ISO_3166_1_2016_list_2_chr)

        # Catch non-processable row and forget it
        if upc == '\\N' and isrc == '\\N' and custom_id == '\\N':
            insert_tuid(None, 'No searchable fields given')

        # if upc and isrc fields are filled, use them
        elif upc \
                and isrc \
                and is_upc \
                and is_isrc:

            # Get tuid using filled fields
            ar_cursor.execute(get_track_by_upc_and_isrc_query, (upc, isrc))
            results = ar_cursor.fetchall()

            # No Matches
            if len(results) < 1:
                insert_tuid(None, 'NOT FOUND - ISRC and UPC')
            else:  # Will be only one by design
                insert_tuid(results[0].get('id'), 'ISRC and UPC')

        # GGL USR ID
        elif custom_id is not None \
                and 'GGL_USR_ID' in custom_id:
            insert_tuid(None, 'GGL_USR_ID')

        # ArtTrack - THIS SHOULD NEVER HAPPEN
        elif custom_id is not None \
                and 'ArtTrack' in custom_id \
                and custom_id:
            insert_tuid(None, 'ArtTrack')

        # If custom id has a tuid, use it - This relies on the failure of
        # earlier cascading logic, and may need to be moved or edited
        elif custom_id is not None \
                and len(custom_id_list) == 3:
            # Use the 3rd element
            insert_tuid(custom_id_list[2], 'TUID from custom_id')

        # if custom id has UPC/ISRC pair, use them
        elif custom_id is not None \
                and len(custom_id_list) == 2 \
                and arhelp.is_upc(custom_id_list[0]) \
                and arhelp.is_isrc(custom_id_list[1]):

            # Find track using sliced fields
            ar_cursor.execute(
                get_track_by_upc_and_isrc_query,
                (custom_id_list[0], custom_id_list[1]))
            results = ar_cursor.fetchall()

            # No Matches
            if len(results) < 1:
                insert_tuid(None, 'NOT FOUND - custom_id UPC/ISRC pair')
            else:  # Will be only one by design
                tuid = results[0].get('id')
                upc = results[0].get('upc')
                isrc = results[0].get('isrc')

                insert_tuid(tuid, 'TUID from custom_id UPC/ISRC pair')

        # if custom id cannot be used for tuid, use isrc field (case where
        # there's no upc, choose first release)
        elif (upc is None or upc == '\\N') \
                and (isrc is not None and isrc != '\\N' and is_isrc):

            # Get track list by ISRC
            ar_cursor.execute(get_track_by_isrc_query, (isrc))
            results = ar_cursor.fetchall()

            if len(results) < 1:
                # No matches
                insert_tuid(None, 'NOT FOUND - ISRC but no UPC')

            elif len(results) > 1:
                # Too many matches
                insert_tuid(None, 'TOO MANY - ISRC but no UPC')

            else:  # Juuuuust Right
                tuid = results[0].get('tuid')
                upc = results[0].get('upc')
                insert_tuid(tuid, 'ISRC but no UPC - One match')

        # if there's no isrc field and custom id doesnt have tuid,
        # but custom_id has upc and isrc, use them
        elif len(custom_id_list) == 2 \
                and arhelp.is_upc(custom_id_list[0]) \
                and arhelp.is_isrc(custom_id_list[1]):

            # TODO: Any NEED FOR SWAPPING MISMATCHED VALUES? UPC <-> ISRC?

            # Get a track from the sliced fields
            ar_cursor.execute(
                get_track_by_upc_and_isrc_query,
                (custom_id_list[0], custom_id_list[1]))
            results = ar_cursor.fetchall()

            if len(results) < 1:
                # No matches
                insert_tuid(None, 'NOT FOUND - ISRC and UPC from custom_id')

            elif len(results) > 1:
                # Too many matches
                insert_tuid(None, 'TOO MANY - ISRC and UPC from custom_id')

            else:  # Juuuuust Right
                tuid = results[0].get('tuid')
                upc = custom_id_list[0]
                isrc = custom_id_list[1]
                insert_tuid(tuid, 'ISRC and UPC from custom_id - One match')

        # TODO: I don't think this last condition is fully fleshed out. It
        # needs to handle the fact that a UPC may be the only item.
        # if custom_id doesn't have both isrc and upc, use isrc in custom_id
        # and choose first release
        elif len(custom_id_list) == 1 and arhelp.is_isrc(custom_id_list[0]):

            # Get track list by ISRC
            ar_cursor.execute(get_track_by_isrc_query, (custom_id_list[0]))
            results = ar_cursor.fetchall()

            if len(results) < 1:
                # No matches
                insert_tuid(None, 'NOT FOUND - ISRC but no UPC')

            elif len(results) > 1:
                # Too many matches
                insert_tuid(None, 'TOO MANY - ISRC but no UPC')

            else:  # Juuuuust Right
                tuid = results[0].get('tuid')
                upc = results[0].get('upc')
                isrc = custom_id_list[0]
                insert_tuid(tuid, 'ISRC from custom_id but no UPC - One match')

        elif len(custom_id_list) == 1 and arhelp.is_upc(custom_id_list[0]):
            insert_tuid(None, 'UPC from custom_id but no ISRC')

        # if custom id is IODA id, use it
        elif custom_id is not None \
                 and custom_id != '\\N' \
                 and len(custom_id) < 9 \
                 and "IODA" == filename[18:22]:

            # Query IODA table and get tuid
            ar_cursor.execute(get_track_by_itm_query, (custom_id,))

            # Will be only one by design
            track = ar_cursor.fetchone()
            tuid = track.get('id')
            upc = track.get('upc')
            isrc = track.get('isrc')

            insert_tuid(tuid, 'IODA id in custom_id')

        else:
            insert_tuid(None, 'COULD NOT PROCESS')

    target_conn.commit()

    loop_stop = timeit.default_timer()
    print("Program executed {} lines in {}\n".format(
        result_chunk_length, loop_stop - loop_start))

target_conn.close()
ar_conn.close()

stop = timeit.default_timer()

print("Program executed {} lines in {}".format(
    result_boundary, stop - start))