"""Unit tests for sales data util."""

from datetime import date
from unittest.mock import ANY
from unittest.mock import Mock
from unittest.mock import patch

from pytest import fixture

from flows.sales_data import util


@fixture
def raw_daily_aggregates():
    """Calculated daily aggregates with date ranges."""
    return [
        {
            'daily_amount': 12.34,
            'date_start': date(2000, 1, 1),
            'date_end': date(2000, 1, 3),
            'country_id': 1,
            'store_id': 2,
            'transaction_type_id': 3,
            'upc': 4},
        {
            'daily_amount': 0.12,
            'date_start': date(2001, 3, 30),
            'date_end': date(2001, 4, 2),
            'country_id': 11,
            'store_id': 22,
            'transaction_type_id': 33,
            'upc': 44}]


@fixture
def daily_revenue_insert_params():
    """Daily aggregates rows expanded for insert to revenue table."""
    return [
        {
            'amount': 12.34,
            'date': date(2000, 1, 1),
            'country_id': 1,
            'store_id': 2,
            'transaction_type_id': 3,
            'upc': 4},
        {
            'amount': 12.34,
            'date': date(2000, 1, 2),
            'country_id': 1,
            'store_id': 2,
            'transaction_type_id': 3,
            'upc': 4},
        {
            'amount': 12.34,
            'date': date(2000, 1, 3),
            'country_id': 1,
            'store_id': 2,
            'transaction_type_id': 3,
            'upc': 4},
        {
            'amount': 0.12,
            'date': date(2001, 3, 30),
            'country_id': 11,
            'store_id': 22,
            'transaction_type_id': 33,
            'upc': 44},
        {
            'amount': 0.12,
            'date': date(2001, 3, 31),
            'country_id': 11,
            'store_id': 22,
            'transaction_type_id': 33,
            'upc': 44},
        {
            'amount': 0.12,
            'date': date(2001, 4, 1),
            'country_id': 11,
            'store_id': 22,
            'transaction_type_id': 33,
            'upc': 44},
        {
            'amount': 0.12,
            'date': date(2001, 4, 2),
            'country_id': 11,
            'store_id': 22,
            'transaction_type_id': 33,
            'upc': 44}]


def test_delete_raw_sql():
    """Test delete_raw_sql function."""
    upcs = [111, 222, 333, 444]
    results = util.delete_raw_sql(upcs, 220)
    for each_sql in results:
        assert 'DELETE' in each_sql
        assert 'accounting_revenue_raw' in each_sql
        assert "upc IN ('111', '222', '333', '444')" in each_sql
        assert 'accounting_period_id = 220' in each_sql


@patch('flows.sales_data.util.art_relations')
def test_get_period_data(db):
    """Test get_period_data function."""
    period_id = 123
    result_set = Mock()
    result_set.fetchone.return_value = (217, 2017, 1, 1)
    db.query.return_value = result_set
    actual = util.get_period_data(period_id)
    assert actual


@patch('flows.sales_data.util.art_relations')
def test_get_period_data_invalid_id(db):
    """Test get_period_data function with invalid period_id."""
    period_id = 123
    result_set = Mock()
    result_set.fetchone.return_value = None
    db.query.return_value = result_set
    actual = util.get_period_data(period_id)
    assert not actual


@patch('flows.sales_data.util.datastore')
def test_get_digital_storeids(db):
    """Test get_digital_storeids function."""
    result_set = Mock()
    result_set.fetchall.return_value = [[100], [463], [1]]
    db.query.return_value = result_set
    actual = util.get_digital_storeids()
    assert actual == ['100', '463', '1']


@patch('flows.sales_data.util.get_digital_storeids')
def test_get_unload_from_snowflake_sql(mock_get_storeids):
    """Test get_unload_from_snowflake_sql function."""
    mock_get_storeids.retun_value = ['100', '463', '1']
    s3_destination = 's3://dev-bucket/test'
    upcs = [12, 34, 56]

    actual = util.get_unload_from_snowflake_sql(
        s3_destination, 'cc123', 200, 220, upcs)
    for each_sql in actual:
        assert 'COPY INTO' in each_sql
        assert s3_destination in each_sql
        assert 'fact_sales fs' in each_sql
        assert "fs.releaseid IN ('12', '34', '56')" in each_sql
        assert 'fs.accountingperiodid BETWEEN 200 AND 220' in each_sql
        assert 'AWS_KEY_ID=' in each_sql
        assert 'FILE_FORMAT=' in each_sql
        assert 'SINGLE = TRUE' in each_sql


@patch('flows.sales_data.util.sql_upcs_condition_in')
def test_get_aggregate_raw_data_sql(upcs_in):
    """Test aggregate sql generator."""
    upcs = ['123', '234', '345', '456', '567']
    upc_batches = [upcs[:3], upcs[3:]]
    where_condition = 'some where condition for this test'
    upcs_in.return_value = where_condition

    util.get_aggregate_raw_data_sql.batch_size = 3
    results_raw = util.get_aggregate_raw_data_sql(upcs)
    results = list(results_raw)

    assert len(results) == len(upc_batches)
    for upc_batch in upc_batches:
        upcs_in.assert_any_call(upc_batch)
    for result in results:
        assert where_condition in result


@patch('flows.sales_data.util.sql_upcs_condition_in')
def test_delete_accounting_data_sql(upcs_in):
    """Test query to delete data for upcs."""
    upcs = ['123', '234', '345', '456', '567']
    upc_batches = [upcs[:3], upcs[3:]]
    where_condition = 'some where condition for this test'
    upcs_in.return_value = where_condition

    util.delete_accounting_data_sql.batch_size = 3
    results_raw = util.delete_accounting_data_sql(upcs)
    results = list(results_raw)

    assert len(results) == len(upc_batches)
    for upc_batch in upc_batches:
        upcs_in.assert_any_call(upc_batch)
    for result in results:
        assert where_condition in result


@patch('flows.sales_data.util.datastore')
def test_get_aggregate_raw_data(datastore):
    """Test aggregate raw data lookup function."""
    period_id = '111'
    upcs = ['123', '234', '345', '456', '567']
    upc_batches = [upcs[:3], upcs[3:]]

    columns = (
        'daily_amount', 'date_start', 'date_end', 'country_id', 'store_id',
        'transaction_type_id', 'upc')
    query_results = [
        ['1a', '1b', '1c', '1d', '1e', '1f', '1g'],
        ['2a', '2b', '2c', '2d', '2e', '2f', '2g'],
        ['3a', '3b', '3c', '3d', '3e', '3f', '3g'],
        ['4a', '4b', '4c', '4d', '4e', '4f', '4g'],
        ['5a', '5b', '5c', '5d', '5e', '5f', '5g']]
    query_result_batches = [query_results[:3], query_results[3:]]
    expected = [dict(zip(columns, row)) for row in query_results]
    datastore.query.side_effect = query_result_batches

    util.get_aggregate_raw_data_sql.batch_size = 3
    results_raw = util.get_aggregate_raw_data(upcs, period_id)
    results = list(results_raw)

    assert results == expected
    queries = datastore.query.call_args_list
    for i, query in enumerate(queries):
        for upc in upc_batches[i]:
            assert "'{}'".format(upc) in query[0][0]
            assert {'accounting_period_id': period_id} == query[0][1]


def test_get_daily_revenue_query_params(
        raw_daily_aggregates, daily_revenue_insert_params):
    """Test get_daily_revenue_query_params generator."""
    results_raw = util.get_daily_revenue_query_params(raw_daily_aggregates)
    results = list(results_raw)
    assert results == daily_revenue_insert_params


@patch('flows.sales_data.util.datastore')
def test_has_ingested_accounting_period_id(datastore):
    """Test accounting period ID lookup function."""
    accounting_period_id = 123
    cursor = Mock()
    cursor.fetchone.return_value = (1,)
    datastore.query.return_value = cursor
    result = util.has_ingested_accounting_period_id(accounting_period_id)
    datastore.query.assert_called_with(
        ANY, {'accounting_period_id': accounting_period_id})
    assert result is True


@patch('flows.sales_data.util.datastore')
def test_has_ingested_accounting_period_id_count_zero(datastore):
    """Test accounting period ID lookup function."""
    accounting_period_id = 123
    cursor = Mock()
    cursor.fetchone.return_value = (0,)
    datastore.query.return_value = cursor
    result = util.has_ingested_accounting_period_id(accounting_period_id)
    datastore.query.assert_called_with(
        ANY, {'accounting_period_id': accounting_period_id})
    assert result is False