"""Tests for fact_conflict model."""

from copy import deepcopy
import datetime
import re

import pytest

from conflict_manager.constants import feature_flag
from conflict_manager.models import fact_conflict
from conflict_manager.utils import account_utils


def _parse_sql(sql):
    """Parse out SQL into individual components."""
    sql = re.sub(r'\s+', ' ', sql).replace(' ,', ',').lower().strip()

    sql_parts = {}
    sql_part_keywords = ['select', 'from', 'where', 'order by', 'limit']

    start_index = len('select ')
    for sql_kw_ind in range(0, len(sql_part_keywords) - 1):
        key, find_str = sql_part_keywords[sql_kw_ind:sql_kw_ind + 2]

        search_str = ' {} '.format(find_str)
        end_index = sql.find(search_str)
        sql_substr = sql[start_index:end_index]
        start_index = end_index + len(search_str)

        sql_parts[key] = sql_substr

    sql_parts['limit'] = sql[start_index:]
    return sql_parts


def test_sort_columns_outer(feature_engine):
    """Test sort columns outer."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, True)
    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    columns = conflict_queries._sort_columns_outer(
        sort_by='product_name', sort_order='desc')

    assert columns == [
        'grouped_conflict.product_name desc', 'views_in_conflict',
        'conflict.conflict_date', 'conflict.tuid',
        'conflict.conflicting_owner', 'conflict.territory']


def test_sort_columns_inner(feature_engine):
    """Test sort columns inner."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, True)
    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    columns = conflict_queries._sort_columns_inner(
        sort_by='product_name', sort_order='desc')

    assert columns == [
        'releases.release_name desc', 'views_in_conflict',
        'inner_conflict.conflict_date', 'inner_conflict.tuid',
        'inner_conflict.conflicting_owner']


def test_inner_query_sql(feature_engine):
    """Test grouped conflict sql renders correctly with default options."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, False)

    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    sql = conflict_queries._inner_query_sql()
    sql_parts = _parse_sql(sql)

    assert sql_parts['select'] == (
        'distinct inner_conflict.conflicting_owner, '
        'inner_conflict.conflict_date, inner_conflict.isrc, '
        'inner_conflict.tuid, inner_conflict.{account_id_field}, '
        'inner_conflict.yt_recent_daily_average, track.track_name, '
        'track.version, inner_artist.artist_names, '
        'track.release_id as product_id, '
        'releases.release_name as product_name, releases.display_upc, '
        'subaccount.subaccount_name')
    assert sql_parts['from'] == (
        '{fact_conflict_table} inner_conflict '
        'left join {track} on (inner_conflict.tuid = track.id) '
        'left join {releases} on (track.release_id = releases.release_id) '
        "left join ( select track_id, listagg(name, '\\0,') "
        "within group(order by decode( lower(type), 'performer', 1, 'remixer', 2, 'producer', 3 ), name) as artist_names "  # noqa: E501
        'from {track_artist} group by track_id ) inner_artist '
        'on (inner_artist.track_id = track.id) '
        'left join {subaccount} on ('
        'subaccount.subaccount_id = inner_conflict.subaccount_id)')
    assert sql_parts['where'] == (
        'inner_conflict.{account_id_field} = :account_id')
    assert sql_parts['order by'] == (
        'inner_conflict.conflict_date, inner_conflict.tuid, '
        'inner_conflict.conflicting_owner')
    assert sql_parts['limit'] == ':limit offset :offset'


def test_inner_query_sql_with_view_in_conflicts(feature_engine):
    """Test grouped conflict sql renders correctly with default options."""
    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    sql = conflict_queries._inner_query_sql()
    sql_parts = _parse_sql(sql)

    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, True)

    assert sql_parts['select'] == (
        'distinct inner_conflict.conflicting_owner, '
        'inner_conflict.conflict_date, inner_conflict.isrc, '
        'inner_conflict.tuid, inner_conflict.{account_id_field}, '
        'inner_conflict.yt_recent_daily_average, track.track_name, '
        'track.version, inner_artist.artist_names, '
        'track.release_id as product_id, '
        'releases.release_name as product_name, releases.display_upc, '
        'subaccount.subaccount_name')
    assert sql_parts['from'] == (
        '{fact_conflict_table} inner_conflict '
        'left join {track} on (inner_conflict.tuid = track.id) '
        'left join {releases} on (track.release_id = releases.release_id) '
        "left join ( select track_id, listagg(name, '\\0,') "
        "within group(order by decode( lower(type), 'performer', 1, 'remixer', 2, 'producer', 3 ), name) as artist_names "  # noqa: E501
        'from {track_artist} group by track_id ) inner_artist '
        'on (inner_artist.track_id = track.id) '
        'left join {subaccount} on ('
        'subaccount.subaccount_id = inner_conflict.subaccount_id)')
    assert sql_parts['where'] == (
        'inner_conflict.{account_id_field} = :account_id')
    assert sql_parts['order by'] == (
        'inner_conflict.conflict_date, inner_conflict.tuid, '
        'inner_conflict.conflicting_owner')
    assert sql_parts['limit'] == ':limit offset :offset'


def test_inner_query_sql_columns(feature_engine):
    """Test grouped conflict sql renders with extra column."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, False)
    columns = ['inner_action.action', 'inner_action.action_date']
    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    conflict_queries.inner_columns = columns
    sql = conflict_queries._inner_query_sql()
    sql_parts = _parse_sql(sql)

    assert ', {}'.format(columns[0]) in sql_parts['select']
    assert ', {}'.format(columns[1]) in sql_parts['select']


def test_inner_query_sql_joins(feature_engine):
    """Test grouped conflict sql renders with extra join."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, False)
    join_cond = (
        'left join {action_table} inner_action '
        'on (inner_conflict.conflict_id = inner_action.conflict_id)')
    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    conflict_queries.inner_joins = [join_cond]
    sql = conflict_queries._inner_query_sql()
    sql_parts = _parse_sql(sql)

    assert (' ' + join_cond) in sql_parts['from']


def test_inner_query_sql_filters(feature_engine):
    """Test grouped conflict sql renders with where clause filters."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, False)
    filters = [
        'inner_action.conflict_id is null',
        'inner_conflict.resolved_datetime is null']
    conflict_queries = fact_conflict.BaseConflictQueries(session=None)
    conflict_queries.inner_filters = filters
    sql = conflict_queries._inner_query_sql()
    sql_parts = _parse_sql(sql)

    assert ' and {} '.format(filters[0]) in sql_parts['where']
    assert ' and {}'.format(filters[1]) in sql_parts['where']


@pytest.fixture
def conflict_list(test_values, test_values2, test_values3, test_vendor_id):
    """Fixture data for conflicts."""
    conflict = {
        'conflict_id': 1,
        'isrc': test_values['isrc'],
        'conflicting_owner': 'owner',
        'conflict_date': datetime.date(year=2017, month=1, day=1),
        'territory_standard': 'ISO_3166_2106',
        'artist_names': 'Flurry\0,Snowball',
        'product_id': test_values['product_id'],
        'product_name': 'snowball on ice',
        'display_upc': test_values['display_upc'],
        'tuid': test_values['tuid'],
        'track_name': 'track name',
        'version': '',
        'vendor_id': test_vendor_id,
        'subaccount_id': None,
        'subaccount_name': None,
        'yt_recent_daily_average': 5,
        'territory': 'US',
        'account_id': '123',
        'account_type': 'vendor',
        'es_id': None,
        'views_in_conflict': 2
    }
    conflict_list = [
        conflict, deepcopy(conflict), deepcopy(conflict), deepcopy(conflict)]
    conflict_list[1]['conflict_id'] = 2
    conflict_list[1]['territory'] = 'FR'

    conflict = conflict_list[2]
    conflict['conflict_id'] = 3
    conflict['track_name'] = 'other'
    conflict['artist_names'] = None
    conflict['es_id'] = 'EmAvpWcBLzAzl7XpJ9Dv'
    conflict_with_subaccount = conflict_list[3]
    conflict_with_subaccount['conflict_id'] = 4
    conflict_with_subaccount['track_name'] = 'other'
    conflict_with_subaccount['artist_names'] = None
    conflict_with_subaccount['subaccount_id'] = 1
    conflict_with_subaccount['subaccount_name'] = 'some subaccount label'
    conflict_with_subaccount['es_id'] = 'EGAvpWcBLzAzl7XpJ9Dv'

    for field in ('isrc', 'product_id', 'display_upc', 'tuid'):
        conflict[field] = test_values2[field]
        conflict_with_subaccount[field] = test_values3[field]

    return conflict_list


def test_conflicts_formatter(
        conflict_list, test_values, test_values2, test_values3,
        test_vendor_id):
    """Test group conflicts function."""
    formatter = fact_conflict.BaseConflictsFormatter()
    results = formatter.output(conflict_list)

    assert results == [
        {
            'isrc': test_values['isrc'],
            'conflicting_owner': 'owner',
            'conflict_date': datetime.date(2017, 1, 1),
            'product_id': test_values['product_id'],
            'product_name': 'snowball on ice',
            'display_upc': test_values['display_upc'],
            'tuid': test_values['tuid'],
            'track_name': 'track name',
            'track_artists': ['Flurry', 'Snowball'],
            'territory_standard': 'ISO_3166_2106',
            'vendor_id': test_vendor_id,
            'subaccount_id': None,
            'subaccount_name': '',
            'es_id': '',
        },
        {
            'isrc': test_values2['isrc'],
            'conflicting_owner': 'owner',
            'conflict_date': datetime.date(2017, 1, 1),
            'product_id': test_values2['product_id'],
            'product_name': 'snowball on ice',
            'display_upc': test_values2['display_upc'],
            'tuid': test_values2['tuid'],
            'track_name': 'other',
            'track_artists': [],
            'territory_standard': 'ISO_3166_2106',
            'vendor_id': test_vendor_id,
            'subaccount_id': None,
            'subaccount_name': '',
            'es_id': 'EmAvpWcBLzAzl7XpJ9Dv',
        },
        {
            'isrc': test_values3['isrc'],
            'conflicting_owner': 'owner',
            'conflict_date': datetime.date(2017, 1, 1),
            'product_id': test_values3['product_id'],
            'product_name': 'snowball on ice',
            'display_upc': test_values3['display_upc'],
            'tuid': test_values3['tuid'],
            'track_name': 'other',
            'track_artists': [],
            'territory_standard': 'ISO_3166_2106',
            'vendor_id': test_vendor_id,
            'subaccount_id': 1,
            'subaccount_name': 'some subaccount label',
            'es_id': 'EGAvpWcBLzAzl7XpJ9Dv'
        }
    ]


def test_conflicts_formatter_with_version(conflict_list):
    """Test group conflicts function with filled in version field."""
    conflict_list[0]['track_name'] = 'Better'
    conflict_list[0]['version'] = 'radio'
    conflict_list[1]['track_name'] = 'Better'
    conflict_list[1]['version'] = 'radio'
    conflict_list[2]['track_name'] = 'Better'
    conflict_list[2]['version'] = ''
    conflict_list[3]['track_name'] = ''
    conflict_list[3]['version'] = 'radio'

    results = fact_conflict.BaseConflictsFormatter().output(conflict_list)
    assert results[0]['track_name'] == 'Better (radio)'
    assert results[1]['track_name'] == 'Better'
    assert results[2]['track_name'] == ' (radio)'


def test_conflicts_formatter_init_conflict(
        conflict_list, test_values, test_values2, test_vendor_id):
    """Test group conflicts function."""
    class _ConflictFormatter(fact_conflict.BaseConflictsFormatter):

        def conflict_item_init(self, conflict, data):
            conflict['daily_average_views'] = data['yt_recent_daily_average']

    results = _ConflictFormatter().output(conflict_list)

    assert len(results) == 3
    for conflict in results:
        assert conflict['daily_average_views'] == \
            conflict_list[0]['yt_recent_daily_average']


def test_conflicts_formatter_add_territory(
        conflict_list, test_values, test_values2, test_vendor_id):
    """Test group conflicts function."""
    class _ConflictFormatter(fact_conflict.BaseConflictsFormatter):

        def conflict_item_init(self, conflict, data):
            conflict['territories'] = []

        def conflict_item_add_territory(self, conflict, data):
            conflict['territories'].append(data['territory'])

    results = _ConflictFormatter().output(conflict_list)

    assert results[0]['territories'] == ['US', 'FR']
    assert results[1]['territories'] == ['US']


def test_new_youtube_conflicts_formatter(conflict_list, feature_engine):
    """Test new youtube conflicts formatter."""
    feature_engine.force_flag(
        feature_flag.CONFLICT_MGR_VIEWS_IN_CONFLICT_DATA, True)
    results = fact_conflict.NewYouTubeConflictsFormatter().output(
        conflict_list)

    assert len(results) == 3
    assert results[0]['territories'] == [
        {'conflict_id': 1, 'code': 'US'}, {'conflict_id': 2, 'code': 'FR'}]
    assert results[1]['territories'] == [
        {'conflict_id': 3, 'code': 'US'}]
    for item in results:
        assert item['status'] == 'NEW'
        assert item['daily_average_views'] == \
            conflict_list[0]['yt_recent_daily_average']
        assert item['views_in_conflict'] == \
            conflict_list[0]['views_in_conflict']


def test_actioned_youtube_conflicts_formatter(conflict_list):
    """Test actioned youtube conflicts formatter."""
    conflict_list[0]['action_date'] = datetime.datetime(2017, 5, 14, 1, 30, 50)
    conflict_list[0]['action'] = 'assert'
    conflict_list[0]['reason'] = ''
    conflict_list[0]['additional_information'] = ''
    conflict_list[1]['action_date'] = datetime.datetime(2017, 5, 14, 1, 30, 50)
    conflict_list[1]['action'] = 'release'
    conflict_list[1]['reason'] = ''
    conflict_list[1]['additional_information'] = ''
    conflict_list[2]['action_date'] = datetime.datetime(2017, 6, 16, 2, 43, 10)
    conflict_list[2]['action'] = 'assert'
    conflict_list[2]['reason'] = ''
    conflict_list[2]['additional_information'] = ''
    conflict_list[3]['action_date'] = datetime.datetime(2017, 6, 16, 2, 43, 10)
    conflict_list[3]['action'] = 'assert'
    conflict_list[3]['reason'] = ''
    conflict_list[3]['additional_information'] = ''
    results = fact_conflict.ActionedYouTubeConflictsFormatter().output(
        conflict_list)

    assert len(results) == 3
    assert results[0]['assert_action']['territories'] == [
        {'conflict_id': 1, 'code': 'US'}]
    assert results[0]['release_action']['territories'] == [
        {'conflict_id': 2, 'code': 'FR'}]
    assert results[1]['assert_action']['territories'] == [
        {'conflict_id': 3, 'code': 'US'}]
    assert results[2]['assert_action']['territories'] == [
        {'conflict_id': 4, 'code': 'US'}]
    for item in results:
        assert item['status'] == 'ACTIONED'
        assert 'action_date' in item
        assert 'reason' in item['assert_action']
        assert 'reason' in item['release_action']
        assert 'additional_information' in item['assert_action']
        assert 'additional_information' in item['release_action']

    assert results[0]['action_date'] == (
        datetime.datetime(2017, 5, 14, 1, 30, 50))
    assert results[1]['action_date'] == (
        datetime.datetime(2017, 6, 16, 2, 43, 10))
    assert results[2]['action_date'] == (
        datetime.datetime(2017, 6, 16, 2, 43, 10))

    for item in results:
        assert item['response_account_id'] == '123'
        assert item['response_account_type'] == 'vendor'


def test_resolved_youtube_conflicts_formatter(conflict_list):
    """Test resolved youtube conflicts formatter."""
    resolved_datetime = datetime.datetime(2017, 10, 10, 2, 43, 10)
    for conflict in conflict_list:
        conflict['resolved_datetime'] = resolved_datetime
    results = fact_conflict.ResolvedYouTubeConflictsFormatter().output(
        conflict_list)

    assert len(results) == 3
    assert results[0]['territories'] == [
        {'conflict_id': 1, 'code': 'US'}, {'conflict_id': 2, 'code': 'FR'}]
    assert results[1]['territories'] == [
        {'conflict_id': 3, 'code': 'US'}]
    for item in results:
        assert item['status'] == 'RESOLVED'
        assert item['resolved_datetime'] == resolved_datetime


def test_get_conflicts_count_for_bulk_actions_test(
        mocker, mock_snowflake_config):
    """Test test_get_conflicts_count_for_bulk_actions."""
    from conflict_manager.utils import model_utils
    run_query_mock = mocker.patch.object(model_utils, 'run_query')
    account = account_utils.Account('vendor', 123)
    actions = [
        {
            'isrc': 'isrc_1',
            'tuid': 'tuid_1',
            'conflicting_owner': 'owner_1',
            'conflict_date': 'conflict_date_1'
        },
        {
            'isrc': 'isrc_2',
            'tuid': 'tuid_2',
            'conflicting_owner': 'owner_2',
            'conflict_date': 'conflict_date_2'
        }
    ]

    expected_sql = """
        SELECT LISTAGG(conflict_id, ',') as conflict_ids,
            isrc, tuid, conflicting_owner, conflict_date
        FROM {fact_conflict_table}
        WHERE
        (
            {account_id_field} = :account_id AND
            isrc = :isrc0 AND
            tuid = :tuid0 AND
            conflicting_owner = :conflicting_owner0 AND
            conflict_date = :conflict_date0 AND
            resolved_datetime IS NULL
        )
        OR
        (
            {account_id_field} = :account_id AND
            isrc = :isrc1 AND
            tuid = :tuid1 AND
            conflicting_owner = :conflicting_owner1 AND
            conflict_date = :conflict_date1 AND
            resolved_datetime IS NULL
        )
        group by isrc, vendor_id, isrc, tuid,conflicting_owner, conflict_date;
    """

    expected_params = {
        'isrc0': 'isrc_1',
        'tuid0': 'tuid_1',
        'conflicting_owner0': 'owner_1',
        'conflict_date0': 'conflict_date_1',
        'isrc1': 'isrc_2',
        'tuid1': 'tuid_2',
        'conflicting_owner1': 'owner_2',
        'conflict_date1': 'conflict_date_2',
    }

    fact_conflict.get_conflicts_ids_for_bulk_actions(account, actions)
    args, kwargs = run_query_mock.call_args
    sql = args[1]

    assert expected_params == kwargs['params']
    assert re.sub(r'\s+', '', sql) == re.sub(r'\s+', '', expected_sql)


def test_get_grouped_conflicts_ids(mocker, mock_snowflake_config):
    """Test get_grouped_conflicts_ids."""
    from conflict_manager.utils import model_utils
    run_query_mock = mocker.patch.object(model_utils, 'run_query')

    grouped_conflicts = [
        {
            'tuid': 'tuid_1',
            'conflicting_owner': 'owner_1',
            'conflict_date': 'conflict_date_1',
            'action': 'action_1',
        },
        {
            'tuid': 'tuid_2',
            'conflicting_owner': 'owner_2',
            'conflict_date': 'conflict_date_2',
            'action': 'action_2',
        }
    ]
    expected_sql = """
        SELECT
            tuid || '|' || CONFLICT_DATE || '|' || CONFLICTING_OWNER
            || '|' || action as grouped_id
        FROM {fact_conflict_table} conflict
        JOIN {action_table} action
            ON conflict.conflict_id = action.conflict_id
        WHERE
        (
            tuid = :tuid0 AND
            conflicting_owner = :conflicting_owner0 AND
            conflict_date = :conflict_date0 AND
            action = :action0
        )
        OR
        (
            tuid = :tuid1 AND
            conflicting_owner = :conflicting_owner1 AND
            conflict_date = :conflict_date1 AND
            action = :action1
        )
        GROUP BY  tuid, conflict_date, conflicting_owner, action;
    """

    expected_params = {
        'tuid0': 'tuid_1',
        'conflicting_owner0': 'owner_1',
        'conflict_date0': 'conflict_date_1',
        'action0': 'action_1',
        'tuid1': 'tuid_2',
        'conflicting_owner1': 'owner_2',
        'conflict_date1': 'conflict_date_2',
        'action1': 'action_2',
    }

    fact_conflict.get_grouped_conflicts_ids(grouped_conflicts)
    _, kwargs = run_query_mock.call_args

    assert kwargs['params'] == expected_params
    assert re.sub(r'\s+', '', kwargs['sql']) == re.sub(
        r'\s+', '', expected_sql)


def test_get_conflicts_info(mocker, mock_snowflake_config):
    """Test get_conflicts_territories."""
    from conflict_manager.utils import model_utils
    run_query_mock = mocker.patch.object(model_utils, 'run_query')

    conflict_ids = [126354, 152354, 2345235]
    expected_sql = """
        SELECT
            tuid, CONFLICT_DATE, CONFLICTING_OWNER, isrc,
            LISTAGG(territory, ',') as territories
        FROM {fact_conflict_table} conflict
        WHERE conflict_id in (:ids)
        GROUP BY tuid, conflict_date, conflicting_owner, isrc;
    """

    expected_params = {'ids': conflict_ids}

    fact_conflict.get_conflicts_territories(conflict_ids)
    _, kwargs = run_query_mock.call_args

    assert kwargs['params'] == expected_params
    assert re.sub(r'\s+', '', kwargs['sql']) == re.sub(
        r'\s+', '', expected_sql)