"""Database operations for OrchardSoundRecording nodes."""
import json

from connector_neo4j import get_session

from sound_recordings.cypher import orchard_sound_recordings as cypher
from sound_recordings.utils import snowflake as snowflake_util
from sound_recordings.utils.neo4j import by


SQLLoader = snowflake_util.SQLLoader(__file__)


class UnknownResourceTouchType(Exception):
    """Unknown Resource Touch Type."""

    pass


def fetch_full_delivery_history(
    osr_ids, execution_type=[], service=[], event_type=[], ack_status=[],
    limit=None, offset=None, start_date=None
):
    """Fetch OrchardSoundRecording delivery history based on id.

    Args:
        osr_ids (list): osr ID
        execution_type (list): list of execution types to filter by
        service (list): list of UGC services to filter by
        event_type (list): list of event types to filter by
        ack_status (list): list of ack statuses to filter by
            (e.g. ['success', 'awaited', 'error', 'missing'])

    Returns:
        [dict]: OrchardSoundRecording(s) delivery history events
    """
    filters_sql = ''
    if osr_ids:
        filters_sql += 'AND sound_recording_id in ({}) '.format(
            ', '.join("'{}'".format(x.strip()) for x in osr_ids)
        )
    if event_type:
        filters_sql += 'AND event_type in ({}) '.format(
            ', '.join("'{}'".format(x.strip()) for x in event_type)
        )
    if service:
        filters_sql += 'AND service in ({}) '.format(
            ', '.join("'{}'".format(x.strip()) for x in service)
        )
    if execution_type:
        includes_none = any(t.lower() == 'none' for t in execution_type)
        filtered_not_none = [t for t in execution_type if t.lower() != 'none']
        execution_type_sql = 'AND ('
        if includes_none:
            execution_type_sql += 'execution_type IS NULL'
        if includes_none and filtered_not_none:
            execution_type_sql += ' OR '
        if filtered_not_none:
            execution_type_sql += 'execution_type in ({})'.format(
                ', '.join("'{}'".format(x.strip()) for x in filtered_not_none)
            )
        execution_type_sql += ') '
        filters_sql += execution_type_sql

    if ack_status:
        # Non-TikTok services have their ack overridden to 'missing' in the
        # logic layer, so the SQL filter must mirror that: rows matching
        # 'missing' include every non-TikTok service; rows matching any
        # other ack value are restricted to TikTok deliveries.
        tiktok = "'TikTok (Audio Fingerprinting)'"
        includes_missing = any(s.lower() == 'missing' for s in ack_status)
        ack_in_list = ', '.join("'{}'".format(x.strip()) for x in ack_status)
        if includes_missing:
            filters_sql += (
                'AND (service != {} '
                'OR dack.ack in ({})) '.format(tiktok, ack_in_list)
            )
        else:
            filters_sql += (
                'AND service = {} '
                'AND dack.ack in ({}) '.format(tiktok, ack_in_list)
            )

    if start_date:
        filters_sql += "AND timestamp >= '{}' ".format(start_date)

    if limit:
        filters_sql += 'ORDER BY timestamp DESC LIMIT {} '.format(limit)
        if offset:
            filters_sql += 'OFFSET {} '.format(offset)

    sql = SQLLoader.load_query('full_delivery_history').format(filters=filters_sql)  # noqa:E501
    history = snowflake_util.fetchall(sql)

    if not history:
        return []

    results = []
    for event in history:
        results.append({
            'sound_recording_id': event[0],
            'version_id': event[1],
            'service': event[2],
            # this could be empty for ineligible delivery records
            'execution_type': event[3] if event[3] else None,
            # this could be empty for ineligible delivery records
            'sfn_execution_id': event[4] if event[4] else None,
            'datetime': event[5],
            'message': json.loads(event[6]),
            'event_type': event[7],
            'ack': event[8],
            'ack_message': event[9]
        })

    return results


def fetch(orchard_sound_recording_ids=[], track_ids=[], upcs=[], product_ids=[], project_ids=[], asset_ids=[], track_isrcs=[], include_deleted=False, include_transfer_to_content=False, include_inactive=False):  # noqa:E501, C901
    """Match OrchardSoundRecording based on ids.

    Args:
        orchard_sound_recording_ids (list): UUIDs for OrchardSoundRecording.id filter
        track_ids (list): ints for Track.id filter
        upcs (list): ints to filter by product upcs
        product_ids (list): ints to filter by product ids
        project_ids (list): ints to filter by project ids
        asset_ids (list): UUIDs for OrchardAsset.id filter
        track_isrcs (list): strings to filter by track isrcs
        include_deleted (bool): bool for track asset filter
        include_transfer_to_content (bool): bool for including products with this status
        include_inactive (bool): bool for including inactive tracks

    Returns:
        dict: OrchardSoundRecording(s) grouped by id each w/ OrchardAsset(s)
    """
    if not orchard_sound_recording_ids and not (
        track_ids or upcs or asset_ids or product_ids or project_ids or track_isrcs
    ):
        raise Exception('Unable to search for sound recordings with empty terms.')

    ids = list(set([str(uuid) for uuid in orchard_sound_recording_ids]))
    tuids = list(set([int(x) for x in track_ids]))
    upcs = list(set([str(upc) for upc in upcs]))
    product_ids = list(set([int(product_id) for product_id in product_ids]))
    project_ids = list(set([int(project_id) for project_id in project_ids]))
    asset_ids = list(set([str(asset_id) for asset_id in asset_ids]))
    track_isrcs = list(set([str(isrc) for isrc in track_isrcs]))

    track_to_asset_rel = 'rel:HAS_ASSET'
    if include_deleted or include_inactive:
        track_to_asset_rel = track_to_asset_rel + '|DELETED_HAS_ASSET'

    product_statuses = ['in_content']
    if include_transfer_to_content:
        product_statuses.append('transfer_to_content')

    if tuids:
        ids = list(set(_resolve_track_ids(tuids, track_to_asset_rel) + ids))

    if upcs:
        ids = list(set(_resolve_product_upcs(upcs, track_to_asset_rel) + ids))

    if asset_ids:
        ids = list(set(_resolve_asset_ids(asset_ids) + ids))

    if product_ids:
        ids = list(set(_resolve_product_ids(product_ids, track_to_asset_rel) + ids))

    if project_ids:
        ids = list(set(_resolve_project_ids(project_ids, track_to_asset_rel) + ids))

    if track_isrcs:
        ids = list(set(_resolve_track_isrcs(track_isrcs, track_to_asset_rel) + ids))

    sound_recordings = dict()
    if not ids:
        return sound_recordings
    filter_by_active = ' WHERE active ' if not include_inactive else ''
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.FETCH.format(track_to_asset_rel=track_to_asset_rel, filter_by_active=filter_by_active),  # noqa E501
        ids=ids,
        product_statuses=product_statuses
    )
    _format_fetch_response_data(results=results, sound_recordings=sound_recordings, include_deleted=include_deleted, include_inactive=include_inactive)  # noqa:E501
    return sound_recordings


def fetch_product(product_id, track_isrcs, include_transfer_to_content=False):
    """Match OrchardSoundRecording based on a product ID or track ISRCs.

    Args:
        product_id (int): product id
        track_isrcs (list): strings to filter by track isrcs
        include_transfer_to_content (bool): bool for including products with this status

    Returns:
        dict: OrchardSoundRecording(s) grouped by id each w/ OrchardAsset(s)
    """
    sound_recordings = dict()
    track_to_asset_rel = 'rel:HAS_ASSET'
    product_statuses = ['in_content']
    if include_transfer_to_content:
        product_statuses.append('transfer_to_content')
    ids = []

    if product_id:
        ids = _resolve_product_ids([product_id], track_to_asset_rel)

    elif track_isrcs:
        track_isrcs = list(set(track_isrcs))
        ids = _resolve_track_isrcs(track_isrcs, track_to_asset_rel)

    if not ids:
        return sound_recordings

    ids = list(set(ids))

    neo4j_session = get_session()
    results = []
    if len(ids) > 9:
        results = neo4j_session.run(
            cypher.FETCH_BY_ROW.format(track_to_asset_rel=track_to_asset_rel),
            ids=ids,
            product_statuses=product_statuses
        )
    else:
        results = neo4j_session.run(
            cypher.FETCH.format(track_to_asset_rel=track_to_asset_rel, filter_by_active= ' WHERE active '),  # noqa E501
            ids=ids,
            product_statuses=product_statuses
        )
    _format_fetch_response_data(results=results, sound_recordings=sound_recordings)
    return sound_recordings


def touch_osr(resource_id, resource_type, modified_before, limit):
    """Touch OrchardSoundRecording based on connected resource and datetime.

    Args:
        resource_id (int): Integer unique ID of resource
        resource_type (str): Object type of resource
        modified_before (datetime): modified date
        limit (int): maximum number of records to touch

    Returns:
        int: updated OSRs
    """
    if resource_type.lower() == 'vendor':
        resource_node = 'l:Vendor'
    elif resource_type.lower() == 'subaccount':
        resource_node = 'l:SubAccount'
    else:
        raise UnknownResourceTouchType()
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.TOUCH_OSR_BY_LABEL.format(resource_node=resource_node),
        resource_id=resource_id,
        modified_before=modified_before,
        limit=limit,
        by=by()
    )
    return results.single().value()


def update(osr_id, primary_track_id):
    """Update OrchardSoundRecording based on id.

    Args:
        primary_track_id (int): primary track id

    Returns:
        dict: OrchardSoundRecording
    """
    neo4j_session = get_session()

    results = neo4j_session.run(
        cypher.UPDATE,
        osr_id=osr_id,
        primary_track_id=primary_track_id
    )

    if not results:
        return results

    return results.single()


def search_by_isrc(term, inactive=False):
    """Search OrchardSoundRecording by isrc based on term.

    Args:
        term (str): term to search for

    Returns:
        list: OrchardSoundRecording.id
    """
    neo4j_session = get_session()

    results = []
    results = neo4j_session.run(
        cypher.SEARCH_BY_ISRC if not inactive else cypher.SEARCH_BY_ISRC_INACTIVE,
        term=term
    )

    return list(set([x['osr']['id'] for x in results]))


def _resolve_product_ids(product_ids, track_to_asset_rel):
    """Get OrchardSoundRecording.id based on Product.id.

    Args:
        product_ids (list): ints for Product.id
        track_to_asset_rel (str): rel between track and asset

    Returns:
        list: OrchardSoundRecording.id(s)
    """
    results = []
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.RESOLVE_PRODUCT_ID.format(track_to_asset_rel=track_to_asset_rel),
        product_ids=product_ids
    )
    return [x['soundRecording.id'] for x in results]


def _resolve_project_ids(project_ids, track_to_asset_rel):
    """Get OrchardSoundRecording.id based on Project.id.

    Args:
        project_ids (list): ints for Project.id
        track_to_asset_rel (str): rel between track and asset

    Returns:
        list: OrchardSoundRecording.id(s)
    """
    results = []
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.RESOLVE_PROJECT_ID.format(track_to_asset_rel=track_to_asset_rel),
        project_ids=project_ids
    )
    return [x['soundRecording.id'] for x in results]


def _resolve_track_ids(track_ids, track_to_asset_rel):
    """Get OrchardSoundRecording.id based on Track.id.

    Args:
        track_ids (list): ints for Track.id
        track_to_asset_rel (str): rel between track and asset

    Returns:
        list: OrchardSoundRecording.id(s)
    """
    results = []
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.RESOLVE_TRACK_ID.format(track_to_asset_rel=track_to_asset_rel),
        tuids=track_ids
    )
    return [x['soundRecording.id'] for x in results]


def _resolve_product_upcs(upcs, track_to_asset_rel):
    """Get OrchardSoundRecording.id based on Product.upc.

    Args:
        upcs (list): int for Product.upc
        track_to_asset_rel (str): rel between track and asset

    Returns:
        list: OrchardSoundRecording.id(s)
    """
    results = []
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.RESOLVE_PRODUCT_UPC.format(track_to_asset_rel=track_to_asset_rel),
        upcs=upcs
    )
    return [x['soundRecording.id'] for x in results]


def _resolve_asset_ids(asset_ids):
    """Get OrchardSoundRecording.id based on OrchardAsset.id.

    Args:
        asset_ids (list): str for OrchardAsset.id

    Returns:
        list: OrchardSoundRecording.id(s)
    """
    results = []
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.RESOLVE_ASSET_ID,
        asset_ids=asset_ids
    )
    return [x['soundRecording.id'] for x in results]


def _resolve_track_isrcs(isrcs, track_to_asset_rel):
    """Get OrchardSoundRecording.id based on Track.isrc.

    Args:
        isrcs (list): str for Track.ISRC

    Returns:
        list: OrchardSoundRecording.id(s)
    """
    results = []
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.RESOLVE_TRACK_ISRC.format(track_to_asset_rel=track_to_asset_rel),
        isrcs=isrcs
    )
    return [x['soundRecording.id'] for x in results]


def _get_track_info(track_id):
    """Extract Track data regarding OrchardSoundRecording.

    Args:
        track_id (int): track id to match

    Returns:
        list: track data
    """
    neo4j_session = get_session()
    track_results = neo4j_session.run(cypher.GET_TRACK_INFO, track_id=track_id)
    if not track_results:
        return track_results

    return track_results.single()


def _isrc_available(isrc):
    """Check if ISRC is used on OrchardSoundRecording node.

    Args:
        isrc (str): isrc to check

    Returns:
        bool: ISRC is available
    """
    neo4j_session = get_session()

    results = neo4j_session.run(
        cypher.MATCH_ISRC,
        isrc=isrc
    )
    return results.single() is None


def _create_osr(isrc, track_id, track_data):
    """Create Track / OrchardSoundRecording data.

    Args:
        isrc (str): track isrc
        track_id (int): track id
        track_data (dict): track info

    Returns:
        result (dict): result
    """
    neo4j_session = get_session()

    orchard_asset_id = track_data.get('assets')[0]
    acr_id = track_data.get('acr_ids')[0]

    write_results = neo4j_session.run(
        cypher.CREATE,
        track_id=track_id,
        orchard_asset_id=orchard_asset_id,
        acr_id=acr_id,
        isrc=isrc,
        by=by()
    )

    sr = write_results.single()
    summary = write_results.consume()

    return (
        sr['osr']['id'] if sr else None,
        summary.counters.nodes_created,
        summary.counters.relationships_created
    )


def _format_fetch_response_data(results, sound_recordings, include_deleted=False, include_inactive=False):  # noqa:E501
    """Format fetch response data."""
    for each in results:
        sound_recording = each['soundRecording']
        sound_recording_details = each['details']
        sr_id = sound_recording['id']

        if sr_id not in sound_recordings:
            sound_recordings[sr_id] = {
                'id': sr_id,
                'isrc': sound_recording['isrc'],
                'primary_track_id': sound_recording['primaryTrackId'],
                'bad_actor': sound_recording['badActor']
                if 'badActor' in sound_recording else False,
                'assets': {}
            }

        for detail in sound_recording_details:
            active = detail['active']
            inactive_reason = detail['inactive_reason'] if 'inactive_reason' in detail else None  # noqa:E501
            acrid = detail['acrid']
            asset = detail['asset']
            track = detail['track']
            product = detail['product']
            vendor = detail['vendor']
            subaccount = detail['subaccount']
            track_to_asset_rel = detail['rel']
            if active or include_inactive:
                if track:
                    asset_id = asset['id']
                    track_id = track['id']
                    include_deleted_and_inactive = include_deleted and include_inactive
                    if track_to_asset_rel and (include_deleted_and_inactive or track_to_asset_rel.type == 'HAS_ASSET'):  # noqa:E501
                        if asset_id not in sound_recordings[sr_id]['assets']:
                            sound_recordings[sr_id]['assets'][asset_id] = {
                                'asset_id': asset['id'],
                                'acr_id': acrid['id'],
                                'filename': asset['filename'],
                                'extension': asset['extension'],
                                'tuids': [track_id],
                                'tracks': {},
                                'source': asset['source']
                            }
                        sound_recording_asset = sound_recordings[sr_id]['assets'][asset_id]  # noqa:E501

                        if track_id not in sound_recording_asset['tuids']:
                            sound_recording_asset['tuids'].append(
                                track_id
                            )
                        if track_id not in sound_recording_asset['tracks']:
                            sound_recording_asset['tracks'][track_id] = {
                                'tuid': track_id,
                                'isrc': track['isrc'],
                                'product_id': product['id'],
                                'upc': product['upc'],
                                'release_status': product['releaseStatus'],
                                'subaccount_id': 0,
                                'vendor_id': None,
                                'active': active,
                                'inactive_reason': inactive_reason
                            }

                        current_track = sound_recording_asset['tracks'][track_id]

                        if subaccount:
                            current_track['subaccount_id'] = subaccount['id']
                            current_track['vendor_id'] = vendor['id']
                        else:
                            current_track['vendor_id'] = vendor['id']
    return