"""Test utilities."""

from ledger_accounting_run_balance.constants import snowflake
from ledger_accounting_run_balance.template import get_formatted_query


def test_get_formatted_query_acc_run_distro():
    """Test get_formatted_query for accounting run in distribution accounting period."""
    result = get_formatted_query(snowflake.LEDGER_ACC_RUN_BALANCE_DISTRO_OLD_SQL, {
        'db': 'ROYALTY_ACCOUNTING',
        'schema': 'dev',
        'accounting_run_id': '1337',
        'contract_ids': '1'
    })
    assert result == """
SELECT
    arrs.ACCOUNTING_RUN_ID,
    cts.CONTRACT_ID,
    cm.ISO_CODE AS CURRENCY_CODE,
    SUM(arrs.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY) AS TOTAL_GROSS_REVENUE_AMOUNT,
    0 AS MECHANICAL_DEDUCTION_TOTAL,
    0 AS MECHANICAL_DEDUCTION_ADMIN_FEE_TOTAL,
    SUM(arrs.NET_REVENUE_PAYEE_CURRENCY) AS TOTAL_NET_REVENUE_AMOUNT,
    SUM(arrs.DISTRIBUTION_FEE_PAYEE_CURRENCY) AS DISTRIBUTION_FEE,
    SUM(arrs.NET_REVENUE_PAYEE_CURRENCY) AS ADJUSTED_NET_REVENUE
FROM ROYALTY_ACCOUNTING.dev.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING AS arrs
    INNER JOIN ROYALTY_ACCOUNTING.dev.CONTRACT_TRANSACTION_STAGING AS cts
        ON arrs.TXN_ID = cts.TXN_ID
            AND arrs.ACCOUNTING_RUN_ID = cts.ACCOUNTING_RUN_ID
    INNER JOIN ROYALTY_ACCOUNTING.dev.CURRENCY_MAP AS cm
        ON cts.PAYEE_CURRENCY_ID = cm.ISO_NUMBER
WHERE arrs.ACCOUNTING_RUN_ID = 1337
    AND cts.CONTRACT_ID IN (1)
GROUP BY
    arrs.ACCOUNTING_RUN_ID,
    cts.CONTRACT_ID,
    cm.ISO_CODE
ORDER BY cts.CONTRACT_ID;
"""     # noqa E501


def test_get_formatted_query_acc_run_distro_with_mech_totals():
    """Test get_formatted_query for accounting run in distribution accounting period."""
    result = get_formatted_query(snowflake.LEDGER_ACC_RUN_BALANCE_DISTRO_SQL, {
        'db': 'ROYALTY_ACCOUNTING',
        'schema': 'dev',
        'accounting_run_id': '1337',
        'contract_ids': '1'
    })
    assert result == """
SELECT
    DISTINCT
    ACCOUNTING_RUN_ID,
    ledger_balance_subquery.CONTRACT_ID,
    CURRENCY_CODE,
    TOTAL_GROSS_REVENUE_AMOUNT,
    CASE
        WHEN cmd.CONTRACT_MECHANICAL_DEDUCTION_ID IS NULL THEN NULL
        ELSE MECHANICAL_DEDUCTION_TOTAL
    END AS MECHANICAL_DEDUCTION_TOTAL,
    CASE
        WHEN cmd.CONTRACT_MECHANICAL_DEDUCTION_ID IS NULL THEN NULL
        ELSE MECHANICAL_DEDUCTION_ADMIN_FEE_TOTAL
    END AS MECHANICAL_DEDUCTION_ADMIN_FEE_TOTAL,
    TOTAL_NET_REVENUE_AMOUNT,
    DISTRIBUTION_FEE,
    ADJUSTED_NET_REVENUE
FROM (
    SELECT
        arrs.ACCOUNTING_RUN_ID,
        cts.CONTRACT_ID,
        cm.ISO_CODE AS CURRENCY_CODE,
        SUM(arrs.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY) AS TOTAL_GROSS_REVENUE_AMOUNT,
        SUM(IFNULL(arrdms.MECHANICAL_DEDUCTION_AMOUNT_PAYEE_CURRENCY, 0)) AS MECHANICAL_DEDUCTION_TOTAL,
        SUM(IFNULL(arrdms.PUBLISHER_ADMIN_FEE_PAYEE_CURRENCY, 0)) AS MECHANICAL_DEDUCTION_ADMIN_FEE_TOTAL,
        SUM(arrs.NET_REVENUE_PAYEE_CURRENCY) AS TOTAL_NET_REVENUE_AMOUNT,
        SUM(arrs.DISTRIBUTION_FEE_PAYEE_CURRENCY) AS DISTRIBUTION_FEE,
        SUM(arrs.NET_REVENUE_PAYEE_CURRENCY) AS ADJUSTED_NET_REVENUE
    FROM ROYALTY_ACCOUNTING.dev.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING AS arrs
        INNER JOIN ROYALTY_ACCOUNTING.dev.CONTRACT_TRANSACTION_STAGING AS cts
            ON arrs.CONTRACT_TXN_ID = cts.CONTRACT_TXN_ID
        INNER JOIN ROYALTY_ACCOUNTING.dev.CURRENCY_MAP AS cm
            ON cts.PAYEE_CURRENCY_ID = cm.ISO_NUMBER
        LEFT JOIN ROYALTY_ACCOUNTING.dev.ACCOUNTING_RUN_RESULTS_DISTRO_MECH_STAGING as arrdms
            ON arrs.CONTRACT_TXN_ID = arrdms.CONTRACT_TXN_ID
    WHERE arrs.ACCOUNTING_RUN_ID = 1337
        AND cts.CONTRACT_ID IN (1)
    GROUP BY
        arrs.ACCOUNTING_RUN_ID,
        cts.CONTRACT_ID,
        cm.ISO_CODE
    ORDER BY cts.CONTRACT_ID
) as ledger_balance_subquery
LEFT JOIN ORCHARD_APP_REPORTING_V2.dev_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_MECHANICAL_DEDUCTION as cmd
    ON ledger_balance_subquery.CONTRACT_ID = cmd.CONTRACT_ID AND cmd.TERRITORY = 'USA';
"""     # noqa E501


def test_get_formatted_query_acc_run_nr():
    """Test get_formatted_query for accounting run in NR accounting period."""
    result = get_formatted_query(snowflake.LEDGER_ACC_RUN_BALANCE_NR_SQL, {
        'db': 'ROYALTY_ACCOUNTING',
        'schema': 'dev',
        'accounting_run_id': '1337',
        'contract_ids': '1'
    })
    assert result == """
SELECT
    TXN_STAGING.ACCOUNTING_RUN_ID,
    TXN_CONTRACT.CONTRACT_ID,
    C.ISO_CODE AS CURRENCY_CODE,
    SUM(TXN_STAGING.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY)
        AS TOTAL_GROSS_REVENUE_AMOUNT,
    0 AS MECHANICAL_DEDUCTION_TOTAL,
    0 AS MECHANICAL_DEDUCTION_ADMIN_FEE_TOTAL,
    SUM(TXN_STAGING.NET_SHARE_PAYEE_CURRENCY) AS TOTAL_NET_REVENUE_AMOUNT,
    SUM(
        TXN_STAGING.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY
        - TXN_STAGING.NET_SHARE_PAYEE_CURRENCY
    ) * -1 AS DISTRIBUTION_FEE,
    SUM(TXN_STAGING.NET_SHARE_PAYEE_CURRENCY) AS ADJUSTED_NET_REVENUE
FROM ROYALTY_ACCOUNTING.dev.ACCOUNTING_RUN_RESULTS_NR_STAGING AS TXN_STAGING
    INNER JOIN ROYALTY_ACCOUNTING.dev.CONTRACT_TRANSACTION_NR_STAGING AS TXN_CONTRACT
        ON TXN_STAGING.TXN_ID = TXN_CONTRACT.TXN_ID
            AND TXN_STAGING.ACCOUNTING_RUN_ID = TXN_CONTRACT.ACCOUNTING_RUN_ID
    INNER JOIN ROYALTY_ACCOUNTING.dev.CURRENCY_MAP AS C
        ON TXN_CONTRACT.PAYEE_CURRENCY_ID = C.ISO_NUMBER
WHERE TXN_STAGING.ACCOUNTING_RUN_ID = 1337
    AND TXN_CONTRACT.CONTRACT_ID IN (1)
GROUP BY
    TXN_STAGING.ACCOUNTING_RUN_ID,
    TXN_CONTRACT.CONTRACT_ID,
    C.ISO_CODE
ORDER BY CONTRACT_ID;
"""


def test_get_formatted_query_get_contracts():
    """Test get_formatted_query for getting contracts."""
    result = get_formatted_query(snowflake.GET_CONTRACTS_SQL, {
        'db': 'ROYALTY_ACCOUNTING',
        'schema': 'dev',
        'accounting_run_id': '1337',
        'contract_ids': '1',
        'contract_transaction_table': snowflake.CONTRACT_TRANSACTION_STAGING,
        'accounting_run_results_table': snowflake.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING
    })
    assert result == """
SELECT DISTINCT TXN_CONTRACT.CONTRACT_ID
FROM ROYALTY_ACCOUNTING.dev.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING AS TXN_STAGING
    INNER JOIN ROYALTY_ACCOUNTING.dev.CONTRACT_TRANSACTION_STAGING TXN_CONTRACT
        ON TXN_STAGING.TXN_ID = TXN_CONTRACT.TXN_ID
        AND TXN_STAGING.ACCOUNTING_RUN_ID = TXN_CONTRACT.ACCOUNTING_RUN_ID
WHERE TXN_STAGING.ACCOUNTING_RUN_ID = 1337;
"""


def test_get_formatted_query_get_contracts_nr():
    """Test get_formatted_query for getting contracts, neighbouring_rights."""
    result = get_formatted_query(snowflake.GET_CONTRACTS_SQL, {
        'db': 'ROYALTY_ACCOUNTING',
        'schema': 'dev',
        'accounting_run_id': '1337',
        'contract_ids': '1',
        'contract_transaction_table': snowflake.CONTRACT_TRANSACTION_NR_STAGING,
        'accounting_run_results_table': snowflake.ACCOUNTING_RUN_RESULTS_NR_STAGING
    })
    assert result == """
SELECT DISTINCT TXN_CONTRACT.CONTRACT_ID
FROM ROYALTY_ACCOUNTING.dev.ACCOUNTING_RUN_RESULTS_NR_STAGING AS TXN_STAGING
    INNER JOIN ROYALTY_ACCOUNTING.dev.CONTRACT_TRANSACTION_NR_STAGING TXN_CONTRACT
        ON TXN_STAGING.TXN_ID = TXN_CONTRACT.TXN_ID
        AND TXN_STAGING.ACCOUNTING_RUN_ID = TXN_CONTRACT.ACCOUNTING_RUN_ID
WHERE TXN_STAGING.ACCOUNTING_RUN_ID = 1337;
"""