"""Unit tests for calculating NR accounting run totals and staging them in snowflake."""

from templates.accounting_run_calculate.snowflake_calculate_run_results_nr import \
    calculate_totals_nr_template


def test_calculate_totals_nr_template():
    """Test that template renders calculate NR totals insert correctly."""
    template = calculate_totals_nr_template().render(
        accounting_run_id=123,
        accounting_run_name='Very Important NR Sales',
        schema='test',
        statement_period_id=456
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.ACCOUNTING_RUN_RESULTS_NR_STAGING (
            CONTRACT_TXN_ID,
            TXN_ID,
            ACCOUNTING_RUN_ID,
            ACCOUNTING_RUN_NAME,
            ROYALTY_RATE,

            UNIT_PRICE_SALE_CURRENCY,
            GROSS_REVENUE_SALE_CURRENCY,
            WITHHOLDING_TAX_SALE_CURRENCY,
            GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
            NET_SHARE_SALE_CURRENCY,

            UNIT_PRICE_PAYEE_CURRENCY,
            GROSS_REVENUE_PAYEE_CURRENCY,
            WITHHOLDING_TAX_PAYEE_CURRENCY,
            GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
            NET_SHARE_PAYEE_CURRENCY
        ) WITH sale_currency (
            contract_transaction_id,
            transaction_id,
            accounting_run_id,
            royalty_rate,
            unit_price_sale_currency,
            gross_revenue_sale_currency,
            withholding_tax_sale_currency,
            gross_revenue_after_withholding_tax_sale_currency,
            net_share_sale_currency,
            exchange_rate
        ) AS (
            SELECT
                cts.CONTRACT_TXN_ID AS contract_transaction_id,
                cts.TXN_ID AS transaction_id,
                cts.ACCOUNTING_RUN_ID,
                cts.TERM_RATE AS royalty_rate,
                snr.UNIT_PRICE_USD / snr.ACTIVITY_RATE AS unit_price_sale_currency,
                (snr.QUANTITY * (snr.UNIT_PRICE_USD / snr.ACTIVITY_RATE)) AS gross_revenue_sale_currency,
                snr.WITHHOLDING_TAX_USD / snr.ACTIVITY_RATE AS withholding_tax_sale_currency,
                (
                    (snr.QUANTITY * (snr.UNIT_PRICE_USD / snr.ACTIVITY_RATE)) +
                    (snr.WITHHOLDING_TAX_USD / snr.ACTIVITY_RATE)
                ) AS gross_revenue_after_withholding_tax_sale_currency,
                (
                    (
                        (snr.QUANTITY * (snr.UNIT_PRICE_USD / snr.ACTIVITY_RATE)) +
                        (snr.WITHHOLDING_TAX_USD / snr.ACTIVITY_RATE)
                    ) * cts.TERM_RATE
                ) AS net_share_sale_currency,
                IFF(snr.SALE_CURRENCY_CODE = c.ACCOUNT_CURRENCY_CODE, 1, fx.rate) AS exchange_rate
            FROM ROYALTY_ACCOUNTING.test.CONTRACT_TRANSACTION_NR_STAGING AS cts
                INNER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_NR AS snr
                    ON cts.TXN_ID = snr.STMT_DB_SALES_NR_TXN_ID
                INNER JOIN ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR AS c
                    ON cts.CONTRACT_TERM_CONDITION_ID = c.CONTRACT_TERM_CONDITION_ID
                        AND cts.ACCOUNTING_RUN_ID = c.ACCOUNTING_RUN_ID
                LEFT OUTER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.exchange_rate AS fx
                    ON snr.SALE_CURRENCY_CODE = fx.FROM_CURRENCY_CODE
                        AND c.ACCOUNT_CURRENCY_CODE = fx.TO_CURRENCY_CODE
                        AND fx.STATEMENT_PERIOD_ID = 456
            WHERE cts.ACCOUNTING_RUN_ID = 123
        ) SELECT
            contract_transaction_id,
            transaction_id,
            accounting_run_id,
            'Very Important NR Sales' AS accounting_run_name,
            royalty_rate,

            unit_price_sale_currency,
            gross_revenue_sale_currency,
            withholding_tax_sale_currency,
            gross_revenue_after_withholding_tax_sale_currency,
            net_share_sale_currency,

            unit_price_sale_currency * exchange_rate AS unit_price_payee_currency,
            gross_revenue_sale_currency * exchange_rate AS gross_revenue_payee_currency,
            withholding_tax_sale_currency * exchange_rate AS withholding_tax_payee_currency,
            gross_revenue_after_withholding_tax_sale_currency * exchange_rate AS gross_revenue_after_withholding_tax_payee_currency,
            net_share_sale_currency * exchange_rate AS net_share_payee_currency
        FROM sale_currency;
    """