"""Test geographics model with connection to test database."""
import json

import pytest

from analytics.connectors import redis
from analytics.models import geographics, utils
from tests.integration.models.db_setup import DBSetup


CREATE_TABLE_DEMOGRAPHICS_REGION_MAPPING_V2 = """
    CREATE TABLE demographics_region_mapping_v2
    LIKE facts.qa.demographics_region_mapping_v2"""
INSERT_INTO_DEMOGRAPHICS_REGION_MAPPING_V2 = """
    INSERT INTO demographics_region_mapping_v2
      (store_id, territory_code, region_code, orchard_region_name,
      orchard_region_code)
    VALUES
      (286, 'US', '501', 'New-York', null),
      (286, 'FR', 'FR-ARA', 'Auvergne-Rhône-Alpes', null),
      (286, 'GB', 'GB-GLG', 'Glasgow', null)"""

CREATE_TABLE_SUMMARY_GEOGRAPHICS_BY_ISRC_V2 = """
    create table summary_geographics_by_isrc_v2
    LIKE facts.qa.summary_geographics_by_isrc_v3"""

CREATE_TABLE_SUMMARY_GEOGRAPHICS_BY_ARTIST_V2 = """
    create table summary_geographics_by_artist_v2
    LIKE facts.qa.summary_geographics_by_artist_v3"""

CREATE_TABLE_SUMMARY_GEOGRAPHICS_BY_LABEL_V2 = """
    create table summary_geographics_by_label_v2
    LIKE facts.qa.summary_geographics_by_label_v3"""


@pytest.yield_fixture(scope='module', autouse=True)
def db_setup():
    """Create object for setting up test database."""
    with DBSetup() as db:
        db.execute_queries(
            CREATE_TABLE_DEMOGRAPHICS_REGION_MAPPING_V2,
            INSERT_INTO_DEMOGRAPHICS_REGION_MAPPING_V2,
            CREATE_TABLE_SUMMARY_GEOGRAPHICS_BY_ISRC_V2,
            CREATE_TABLE_SUMMARY_GEOGRAPHICS_BY_ARTIST_V2,
            CREATE_TABLE_SUMMARY_GEOGRAPHICS_BY_LABEL_V2
        )
        db.execute_seed_scripts(
            'summary_geographics_by_isrc_v2.sql',
            'summary_geographics_by_artist_v2.sql',
            'summary_geographics_by_label_v2.sql'
        )
        yield db


def test_get_geographic_insights_by_label_spotify():
    """Test get_geographic_insights."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [286],
        'subaccountid': None
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'BR',
            'unique_listeners': 0,
            'number_of_streams': 66
        },
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 66
        },
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 10
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_label_apple():
    """Test get_geographic_insights."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': None
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'BR',
            'unique_listeners': 0,
            'number_of_streams': 132
        },
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 132
        },
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 20
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_label_apple_cache():
    """Test get_geographic_insights cache."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': None,
        'territory_code': '',
        'region_code': '',
        'query': 'get_geographic_insights_v3'
    }

    expected_message = [
        {
            'territory_code': 'BR',
            'unique_listeners': 0,
            'number_of_streams': 132
        },
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 132
        },
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 20
        }
    ]

    cache_key = utils.get_cache_key(params)
    cached_result = redis.client.get(cache_key)

    assert geographics._format_geographic_insights_by_territory(
        json.loads(cached_result.decode('utf8'))) == expected_message


def test_get_geographic_insights_by_label_with_subaccount_spotify():
    """Test get_geographic_insights when subaccount used."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [286],
        'subaccountid': 2
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 66
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_label_with_subaccount_apple():
    """Test get_geographic_insights when subaccount used."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': 2
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 132
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_artist_spotify():
    """Test get_geographic_insights."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [286],
        'subaccountid': None
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'BR',
            'unique_listeners': 0,
            'number_of_streams': 70
        },
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 22
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_artist_apple():
    """Test get_geographic_insights."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [1],
        'subaccountid': None
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'BR',
            'unique_listeners': 0,
            'number_of_streams': 140
        },
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 44
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_artist_with_subaccount_spotify():
    """Test get_geographic_insights when subaccount used."""
    params = {
        'labelid': 21755,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [286],
        'subaccountid': 2
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 44
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_artist_with_subaccount_apple():
    """Test get_geographic_insights when subaccount used."""
    params = {
        'labelid': 21755,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [1],
        'subaccountid': 2
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'CA',
            'unique_listeners': 0,
            'number_of_streams': 88
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_isrc_spotify(monkeypatch):
    """Test get_geographic_insights."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': ['BRRGE1704890'],
        'artist_ids': None,
        'store_ids': [286],
        'subaccountid': None
    }

    response = geographics.get_geographic_insights(**params)

    # TODO same result until store id clause is active
    expected_message = [
        {
            'number_of_streams': 28,
            'unique_listeners': 0,
            'territory_code': 'BR'
        },
        {
            'number_of_streams': 23,
            'unique_listeners': 0,
            'territory_code': 'NY'
        },
        {
            'number_of_streams': 13,
            'unique_listeners': 0,
            'territory_code': 'CA'
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_isrc_apple(monkeypatch):
    """Test get_geographic_insights."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': ['BRRGE1704890'],
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': None
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'number_of_streams': 56,
            'unique_listeners': 0,
            'territory_code': 'BR'
        },
        {
            'number_of_streams': 46,
            'unique_listeners': 0,
            'territory_code': 'NY'
        },
        {
            'number_of_streams': 26,
            'unique_listeners': 0,
            'territory_code': 'CA'
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_isrc_with_subaccount_spotify():
    """Test get_geographic_insights when subaccount used."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': ['BRRGE1704890'],
        'artist_ids': None,
        'store_ids': [286],
        'subaccountid': 2
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 23
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_isrc_with_subaccount_apple():
    """Test get_geographic_insights when subacc used and apple is selected."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': ['BRRGE1704890'],
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': 2
    }

    response = geographics.get_geographic_insights(**params)

    expected_message = [
        {
            'territory_code': 'NY',
            'unique_listeners': 0,
            'number_of_streams': 46
        }
    ]

    assert response.message == expected_message


def test_get_geographic_insights_by_region_by_label_spotify():
    """Test get_geographic_insights_by_region for label query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [286],
        'subaccountid': None
    }

    expected_response = [
        {
            'region_code': 'BR-AC',
            'unique_listeners': 0,
            'territory_code': 'BR',
            'number_of_streams': 66,
            'region_name': 'Acre'
        },
        {
            'region_code': 'BR-AC',
            'unique_listeners': 0,
            'territory_code': 'NY',
            'number_of_streams': 66,
            'region_name': 'Acre'
        },
        {
            'region_code': None,
            'unique_listeners': 0,
            'territory_code': 'CA',
            'number_of_streams': 10,
            'region_name': 'Unknown'
        }
    ]

    response = geographics.get_geographic_insights_by_region(**params)

    assert response.message == expected_response


def test_get_geographic_insights_by_region_by_label_apple():
    """Test get_geographic_insights_by_region for label query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': None
    }

    expected_response = [
        {
            'region_code': 'BR-AC',
            'unique_listeners': 0,
            'territory_code': 'BR',
            'number_of_streams': 132,
            'region_name': 'Acre'
        },
        {
            'region_code': 'BR-AC',
            'unique_listeners': 0,
            'territory_code': 'NY',
            'number_of_streams': 132,
            'region_name': 'Acre'
        },
        {
            'region_code': None,
            'unique_listeners': 0,
            'territory_code': 'CA',
            'number_of_streams': 20,
            'region_name': 'Unknown'
        }
    ]

    response = geographics.get_geographic_insights_by_region(**params)

    assert response.message == expected_response


def test_get_geographic_insights_by_region_by_artist_spotify():
    """Test get_geographic_insights_by_region for artist query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [286],
        'subaccountid': None
    }

    expected_response = [
        {
            'region_name': 'Acre',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 50
        },
        {
            'region_name': 'Unknown',
            'territory_code': 'CA',
            'unique_listeners': 0,
            'region_code': None,
            'number_of_streams': 22
        },
        {
            'region_name': 'Other Region',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-DF',
            'number_of_streams': 20
        }
    ]

    response = geographics.get_geographic_insights_by_region(
        **params)

    assert response.message == expected_response


def test_get_geographic_insights_by_region_by_artist_apple():
    """Test get_geographic_insights_by_region for artist query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [1],
        'subaccountid': None
    }

    expected_response = [
        {
            'region_name': 'Acre',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 100
        },
        {
            'region_name': 'Unknown',
            'territory_code': 'CA',
            'unique_listeners': 0,
            'region_code': None,
            'number_of_streams': 44
        },
        {
            'region_name': 'Other Region',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-DF',
            'number_of_streams': 40
        }
    ]

    response = geographics.get_geographic_insights_by_region(
        **params)

    assert response.message == expected_response


def test_get_geographic_insights_by_region_by_artist_apple_cache():
    """Test get_geographic_insights_by_region for artist query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': None,
        'artist_ids': [556039],
        'store_ids': [1],
        'subaccountid': None,
        'territory_code': '',
        'region_code': '',
        'query': 'get_geographic_insights_by_region_v3'
    }

    expected_response = [
        {
            'region_name': 'Acre',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 100
        },
        {
            'region_name': 'Unknown',
            'territory_code': 'CA',
            'unique_listeners': 0,
            'region_code': None,
            'number_of_streams': 44
        },
        {
            'region_name': 'Other Region',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-DF',
            'number_of_streams': 40
        }
    ]

    cache_key = utils.get_cache_key(params)
    cached_result = redis.client.get(cache_key)

    assert geographics._format_geographic_insights_by_region(
        json.loads(cached_result.decode('utf8'))) == expected_response


def test_get_geographic_insights_by_region_by_isrc_spotiy():
    """Test get_geographic_insights_by_region for artist query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': ['BRRGE1704890'],
        'artist_ids': None,
        'store_ids': [286],
        'subaccountid': None
    }

    expected_response = [
        {
            'region_name': 'Acre',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 23
        },
        {
            'region_name': 'Acre',
            'territory_code': 'NY',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 23
        },
        {
            'region_name': 'Unknown',
            'territory_code': 'CA',
            'unique_listeners': 0,
            'region_code': None,
            'number_of_streams': 13
        },
        {
            'region_name': 'Other Region',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-DF',
            'number_of_streams': 5
        }
    ]

    response = geographics.get_geographic_insights_by_region(
        **params)

    assert response.message == expected_response


def test_get_geographic_insights_by_region_by_isrc_apple():
    """Test get_geographic_insights_by_region for artist query."""
    params = {
        'labelid': 21786,
        'start_date': '2018-01-01',
        'end_date': '2018-01-01',
        'isrcs': ['BRRGE1704890'],
        'artist_ids': None,
        'store_ids': [1],
        'subaccountid': None
    }

    expected_response = [
        {
            'region_name': 'Acre',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 46
        },
        {
            'region_name': 'Acre',
            'territory_code': 'NY',
            'unique_listeners': 0,
            'region_code': 'BR-AC',
            'number_of_streams': 46
        },
        {
            'region_name': 'Unknown',
            'territory_code': 'CA',
            'unique_listeners': 0,
            'region_code': None,
            'number_of_streams': 26
        },
        {
            'region_name': 'Other Region',
            'territory_code': 'BR',
            'unique_listeners': 0,
            'region_code': 'BR-DF',
            'number_of_streams': 10
        }
    ]

    response = geographics.get_geographic_insights_by_region(**params)

    assert response.message == expected_response


def test_get_geographic_orchard_regions():
    """Test get_geographic_orchard_regions."""
    expected_message = [
            {
                'territory_code': 'FR',
                'region_code': 'FR-ARA',
                'region_name': 'Auvergne-Rhône-Alpes'
            },
            {
                'territory_code': 'GB',
                'region_code': 'GB-GLG',
                'region_name': 'Glasgow'
            },
            {
                'territory_code': 'US',
                'region_code': '501',
                'region_name': 'New-York'
            }
        ]

    response = geographics.get_geographic_orchard_regions().message

    assert response == expected_message


def test_get_geographic_orchard_regions_cached():
    """Test get_geographic_orchard_regions."""
    expected_message = [
            {
                'territory_code': 'FR',
                'region_code': 'FR-ARA',
                'region_name': 'Auvergne-Rhône-Alpes'
            },
            {
                'territory_code': 'GB',
                'region_code': 'GB-GLG',
                'region_name': 'Glasgow'
            },
            {
                'territory_code': 'US',
                'region_code': '501',
                'region_name': 'New-York'
            }
        ]

    cache_key = 'get_geographic_orchard_regions_v2_spotify_update'
    cached_result = redis.client.get(cache_key)

    assert geographics._format_geographic_orchard_region(
        json.loads(cached_result.decode('utf8'))) == expected_message


def test_get_geographic_latest_date():
    """Test get latest date."""
    expected_message = '2018-01-01'

    response = geographics.get_geographics_latest_date().message

    assert response == expected_message


def test_get_geographic_latest_date_cached():
    """Test geographic latest date cached."""
    expected_message = '2018-01-01'

    cache_key = 'get_summary_geographics_max_date_v2'
    cached_result = redis.client.get(cache_key)

    assert json.loads(cached_result.decode('utf8')) == expected_message