"""Lambda sme-isrc-finder function module."""
import json
import os
from datetime import datetime

from lambdacommon.common_config import logger
from lambdacommon.common_config import logging
import config
from src.connectors.graphql import GqlClient
from src.connectors.secrets_manager import SecretsManagerClient
from src.connectors.snowflake import SnowflakeClient

logging.getLogger('gql').setLevel(logging.WARNING)
queries_path = os.path.dirname(__file__) + '/queries/'
final_run_number = config.FINAL_RUN_NUMBER


def read_query_from_file(file_path):
    """Read query from file."""
    with open(file_path, 'r') as file:
        return file.read()


def evaluate_result(result, search_isrc):
    """Evaluate result."""
    isrcs = result['globalSoundRecordingSearchES']['items']
    num_of_isrcs = len(isrcs)
    if num_of_isrcs == 0:
        return False
    else:
        isrc = isrcs[0]['item']['isrc']
        return isrc == search_isrc


def get_grass_token():
    """Get grass token from m2m secret."""
    secrets_client = SecretsManagerClient()
    secret = secrets_client.get_secret(config.GRASS_TOKEN_SECRET)
    return json.loads(secret)['token']


def get_error_logs(missing_isrcs):
    """Get error logs from snowflake."""
    query = read_query_from_file(queries_path + 'get_isrc_errors.sql')
    missing_isrcs_str = ', '.join(
        f"'{isrc['isrc']}'" for isrc in missing_isrcs)
    query = query.replace('LIST_OF_MISSING_ISRCS', missing_isrcs_str)
    snowflake_client = SnowflakeClient()
    results = snowflake_client.execute_query(query)
    snowflake_client.close_connection()
    return results


def log_errors(missing_isrcs):
    """Log errors for missing isrcs."""
    results = get_error_logs(missing_isrcs)
    for isrc in missing_isrcs:
        log_prefix = ''
        if 'rerun' in isrc:
            log_prefix = 'Rerun: '
        isrc_id = isrc['isrc']
        isrc_data = results[results['ISRC'] == isrc_id]
        if isrc_data.empty:
            logger.info(
                f'{log_prefix}ISRC {isrc_id} has no error logs in'
                f' facts.prod.sme_labelcopy_ingest')
        else:
            logs_by_upc = []
            current_upc_log = {}
            current_upc = ''
            row_number = 1
            for index, row in isrc_data.iterrows():
                if row['UPC'] != current_upc:
                    if row_number != 1:
                        logs_by_upc.append(current_upc_log)
                    current_upc = row['UPC']
                    current_upc_log = {
                        'upc': row['UPC'],
                        'status': row['STATUS'],
                        'not_ingest_reason': row['NOT_INGEST_REASON'],
                        'errors': [row['ERRORS']]}
                else:
                    current_upc_log['errors'].append(row['ERRORS'])
                row_number += 1
            logs_by_upc.append(current_upc_log)
            logger.info(
                f'{log_prefix}ISRC {isrc_id} has error logs in'
                f' facts.prod.sme_labelcopy_ingest: {logs_by_upc}')


def filter_by_state(isrcs, field, state):
    """Filter JSON objects by state."""
    return [item for item in isrcs if item[field] == state]


def log_isrc_state(isrc, state, location):
    """Log isrc state."""
    isrc_id = isrc['isrc']
    log_prefix = ''
    if 'rerun' in isrc:
        log_prefix = 'Rerun: '
    if state == 'found':
        logger.info(f'{log_prefix}ISRC {isrc_id} found in {location}')
    else:
        logger.info(f'{log_prefix}ISRC {isrc_id} missing from {location}')


def check_isrcs_in_dim_track(input_isrcs, is_last_run):
    """Check if isrcs are in dim_track table."""
    new_isrcs = filter_by_state(input_isrcs, 'ingested_dim_track', False)
    if len(new_isrcs) == 0:
        return input_isrcs
    snowflake_client = SnowflakeClient()
    new_isrcs_str = ', '.join(f"'{isrc['isrc']}'" for isrc in new_isrcs)
    query = read_query_from_file(queries_path + 'get_isrcs_from_dim_track.sql')
    query = query.replace('LIST_OF_ISRCS', new_isrcs_str)
    results = snowflake_client.execute_query(query)
    isrcs_in_dim_track = results['ISRC'].tolist()
    for isrc in new_isrcs:
        isrc_id = isrc['isrc']
        if isrc_id in isrcs_in_dim_track:
            isrc['ingested_dim_track'] = True
            time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            isrc['ingested_dim_track_time'] = time
            log_isrc_state(isrc, 'found', 'dim_track')
        else:
            if is_last_run:
                log_isrc_state(isrc, 'missing', 'dim_track')
    return input_isrcs


def get_time_string(isrc, time_field):
    """Get time string in valid format for sql."""
    if isrc[time_field] != 'NULL':
        time = f"'{isrc[time_field]}'"
    else:
        time = 'NULL'
    return time


def insert_isrcs_to_db(isrcs):
    """Insert isrc data to results table."""
    end_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    snowflake_client = SnowflakeClient()
    query = read_query_from_file(
        queries_path + 'insert_test_data.sql')
    query = query.replace(
        '<SNOWFLAKE_RESULTS_TABLE>',
        config.SNOWFLAKE_RESULTS_TABLE)
    insertion_values = ''
    for index, isrc in enumerate(isrcs):
        dim_track_time = get_time_string(isrc, 'ingested_dim_track_time')
        insights_time = get_time_string(isrc, 'ingested_insights_time')
        if isrc['test_end_time'] == 'NULL':
            isrc['test_end_time'] = end_time
        insertion_values += (
            f"('{isrc['isrc']}',"
            f" '{isrc['upc']}',"
            f" '{isrc['release_date']}',"
            f" '{isrc['ingested_dim_track']}',"
            f' {dim_track_time},'
            f" '{isrc['ingested_insights']}',"
            f' {insights_time},'
            f" '{isrc['test_run_id']}',"
            f" '{isrc['test_start_time']}',"
            f" '{isrc['test_end_time']}')")
        if index != len(isrcs) - 1:
            insertion_values += ', '
    query = query.replace('<DATA>', insertion_values)
    snowflake_client.execute_query(query)


def check_isrcs_in_insights(isrcs, is_last_run):
    """Check if isrcs are in insights."""
    query = read_query_from_file(
        queries_path + 'globalSoundRecordingSearchES.gql')
    grass_token = get_grass_token()
    gql_client = GqlClient(grass_token, query)
    # If not last run, only check isrcs found in dim_track
    isrcs_to_check = isrcs
    isrcs_missing_from_insights = []
    isrcs_found_in_insights = []
    if not is_last_run:
        isrcs_to_check = filter_by_state(
            isrcs, 'ingested_dim_track', True)
        isrcs_missing_from_insights = filter_by_state(
            isrcs, 'ingested_dim_track', False)
    for isrc in isrcs_to_check:
        isrc_id = isrc['isrc']
        variables = {
            'term': isrc_id,
            'limit': 5,
            'offset': 0,
            'isEmployee': True,
            'isSearchV2FFEnabled': False,
            'shouldIncludeLabelsCompanyBrand': False,
            'catalogOnly': True
        }
        result = gql_client.execute_query(variables)
        upc_found = evaluate_result(result, isrc_id)
        if upc_found is True:
            isrc['ingested_insights'] = True
            time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            isrc['ingested_insights_time'] = time
            isrc['test_end_time'] = time
            log_isrc_state(isrc, 'found', 'insights')
            isrcs_found_in_insights.append(isrc)
        if upc_found is False:
            isrcs_missing_from_insights.append(isrc)
            if is_last_run:
                log_isrc_state(isrc, 'missing', 'insights')
    if is_last_run and len(isrcs_missing_from_insights) > 0:
        log_errors(isrcs_missing_from_insights)
    #  Insert isrcs found in insights to snowflake
    if len(isrcs_found_in_insights) > 0:
        insert_isrcs_to_db(isrcs_found_in_insights)
    return isrcs_missing_from_insights


def handler(event, context):
    """Lambda entry point."""
    try:
        if 'no_log' in event:
            logger.setLevel(logging.CRITICAL)
        isrcs = event['isrcs']
        next_run_number = event['run_number'] + 1
        is_last_run = next_run_number >= int(final_run_number)
        # Check if new isrcs found in dim_track
        isrcs = check_isrcs_in_dim_track(isrcs, is_last_run)
        # Check if isrcs found in dim_track are in insights
        missing_isrcs = check_isrcs_in_insights(isrcs, is_last_run)
        if is_last_run:
            insert_isrcs_to_db(missing_isrcs)
        return {
            'status': 'OK',
            'isrcs': missing_isrcs,
            'number_of_missing_isrcs': len(missing_isrcs),
            'run_number': next_run_number
        }
    except Exception as e:
        logger.exception(str(e))
        raise e