"""Test utilities."""

from commit_royalties.constants import snowflake
from commit_royalties.template import get_formatted_query


def test_get_formatted_query_totals_distro():
    """Test get_formatted_query for getting distro totals by contract/account."""
    result = get_formatted_query(
        snowflake.GET_TOTALS_BY_CONTRACT_ACCOUNT_DISTRO,
        {
            'db': 'ROYALTY_ACCOUNTING',
            'schema': 'DEV',
            'accounting_run_id': '1337',
            'account_ids': '1'
        }
    )

    assert result == """
SELECT
    ctd.CONTRACT_ID,
    ctd.ACCOUNT_ID,
    cm.ISO_CODE AS CURRENCY_CODE,
    SUM(IFNULL(arr.NET_REVENUE_PAYEE_CURRENCY, 0)) AS AMOUNT
FROM ROYALTY_ACCOUNTING.DEV.ACCOUNTING_RUN_RESULTS_DISTRO AS arr
    INNER JOIN ROYALTY_ACCOUNTING.DEV.CONTRACT_TRANSACTION_DISTRO AS ctd
        ON arr.TXN_ID = ctd.TXN_ID
            AND arr.ACCOUNTING_RUN_ID = ctd.ACCOUNTING_RUN_ID
    INNER JOIN ROYALTY_ACCOUNTING.DEV.CURRENCY_MAP AS cm
        ON ctd.PAYEE_CURRENCY_ID = cm.ISO_NUMBER
WHERE arr.ACCOUNTING_RUN_ID = 1337
    AND ctd.ACCOUNT_ID IN (1)
GROUP BY
    ctd.CONTRACT_ID,
    ctd.ACCOUNT_ID,
    cm.ISO_CODE
ORDER BY ctd.ACCOUNT_ID
"""


def test_get_formatted_query_totals_nr():
    """Test get_formatted_query for getting NR totals by contract/account."""
    result = get_formatted_query(
        snowflake.GET_TOTALS_BY_CONTRACT_ACCOUNT_NR,
        {
            'db': 'ROYALTY_ACCOUNTING',
            'schema': 'DEV',
            'accounting_run_id': '1337',
            'account_ids': '1'
        }
    )

    assert result == """
SELECT
    ctn.CONTRACT_ID,
    ctn.ACCOUNT_ID,
    cm.ISO_CODE AS CURRENCY_CODE,
    SUM(IFNULL(arr.NET_SHARE_PAYEE_CURRENCY, 0)) AS AMOUNT
FROM ROYALTY_ACCOUNTING.DEV.ACCOUNTING_RUN_RESULTS_NR AS arr
    INNER JOIN ROYALTY_ACCOUNTING.DEV.CONTRACT_TRANSACTION_NR AS ctn
        ON arr.TXN_ID = ctn.TXN_ID
            AND arr.ACCOUNTING_RUN_ID = ctn.ACCOUNTING_RUN_ID
    INNER JOIN ROYALTY_ACCOUNTING.DEV.CURRENCY_MAP AS cm
        ON cm.ISO_NUMBER = ctn.PAYEE_CURRENCY_ID
WHERE arr.ACCOUNTING_RUN_ID = 1337
    AND ctn.ACCOUNT_ID IN (1)
GROUP BY
    ctn.CONTRACT_ID,
    ctn.ACCOUNT_ID,
    CURRENCY_CODE
ORDER BY ctn.ACCOUNT_ID
"""


def test_get_formatted_query_accounts_distro():
    """Test get_formatted_query for getting distinct account_ids in distro run."""
    result = get_formatted_query(
        snowflake.GET_ACCOUNTS_WITH_TRANSACTIONS,
        {
            'db': 'ROYALTY_ACCOUNTING',
            'schema': 'DEV',
            'accounting_run_id': '1337',
            'contract_transaction_table': snowflake.CONTRACT_TRANSACTION_DISTRO
        }
    )

    assert result == """
    SELECT DISTINCT ACCOUNT_ID
    FROM ROYALTY_ACCOUNTING.DEV.CONTRACT_TRANSACTION_DISTRO
    WHERE ACCOUNTING_RUN_ID = 1337
    ORDER BY ACCOUNT_ID
"""


def test_get_formatted_query_accounts_nr():
    """Test get_formatted_query for getting distinct account_ids in NR run."""
    result = get_formatted_query(
        snowflake.GET_ACCOUNTS_WITH_TRANSACTIONS,
        {
            'db': 'ROYALTY_ACCOUNTING',
            'schema': 'DEV',
            'accounting_run_id': '1337',
            'contract_transaction_table': snowflake.CONTRACT_TRANSACTION_NR
        }
    )

    assert result == """
    SELECT DISTINCT ACCOUNT_ID
    FROM ROYALTY_ACCOUNTING.DEV.CONTRACT_TRANSACTION_NR
    WHERE ACCOUNTING_RUN_ID = 1337
    ORDER BY ACCOUNT_ID
"""