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

from templates.accounting_run_calculate.snowflake_calculate_run_results \
    import calculate_totals_distro_template


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

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING (
            CONTRACT_TXN_ID,
            TXN_ID,
            ACCOUNTING_RUN_ID,
            ACCOUNTING_RUN_NAME,
            TERM_RATE,

            UNIT_PRICE_SALE_CURRENCY,
            GROSS_REVENUE_SALE_CURRENCY,
            WITHHOLDING_TAX_SALE_CURRENCY,
            GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
            NET_REVENUE_SALE_CURRENCY,
            DISTRIBUTION_FEE_SALE_CURRENCY,

            UNIT_PRICE_PAYEE_CURRENCY,
            GROSS_REVENUE_PAYEE_CURRENCY,
            WITHHOLDING_TAX_PAYEE_CURRENCY,
            GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
            NET_REVENUE_PAYEE_CURRENCY,
            DISTRIBUTION_FEE_PAYEE_CURRENCY
        )
        SELECT DISTINCT
            sub.contract_txn_id,
            sub.txn_id,
            123,
            'Very Important Distro Sales',
            sub.term_rate,

            sub.unit_price_sale_currency,
            sub.gross_revenue_sale_currency,
            sub.withholding_tax_sale_currency,
            sub.gross_revenue_after_withholding_tax_sale_currency,
            sub.net_revenue_sale_currency,
            (sub.gross_revenue_after_withholding_tax_sale_currency - sub.net_revenue_sale_currency) * -1 AS distribution_fee_sale_currency,

            (sub.unit_price_sale_currency * sub.exchange_rate) AS unit_price_payee_currency,
            (sub.gross_revenue_sale_currency * sub.exchange_rate) AS gross_revenue_payee_currency,
            (sub.withholding_tax_sale_currency * sub.exchange_rate) AS withholding_tax_payee_currency,
            (sub.gross_revenue_after_withholding_tax_sale_currency * sub.exchange_rate) AS gross_revenue_after_withholding_tax_payee_currency,
            (sub.net_revenue_sale_currency * sub.exchange_rate) AS net_revenue_payee_currency,
            (
                (sub.gross_revenue_after_withholding_tax_sale_currency * sub.exchange_rate) -
                (sub.net_revenue_sale_currency * sub.exchange_rate)
            ) * -1 AS distribution_fee_payee_currency
        FROM (
            WITH cdd_rate AS (
                SELECT DISTINCT
                    contract_term_condition_id,
                    account_currency_code,
                    term_rate
                FROM ROYALTY_ACCOUNTING.test.CONTRACT_DENORMALIZED_DISTRO
                WHERE accounting_run_id = 123
            )
            SELECT
                cts.contract_txn_id,
                cts.txn_id,
                cdd.term_rate / 100 AS term_rate,
                IFF(sds.sale_currency_code = cdd.account_currency_code, 1, fx.rate) AS exchange_rate,

                sds.unit_price_usd / sds.activity_rate AS unit_price_sale_currency,
                (sds.quantity * (sds.unit_price_usd / sds.activity_rate)) AS gross_revenue_sale_currency,
                sds.withholding_tax_usd / sds.activity_rate AS withholding_tax_sale_currency,
                (
                    (sds.quantity * (sds.unit_price_usd / sds.activity_rate)) +
                    (sds.withholding_tax_usd / sds.activity_rate)
                ) AS gross_revenue_after_withholding_tax_sale_currency,
                (
                    (
                        (sds.quantity * (sds.unit_price_usd / sds.activity_rate)) +
                        (sds.withholding_tax_usd / sds.activity_rate)
                    ) * (cdd.term_rate / 100)
                ) AS net_revenue_sale_currency
            FROM ROYALTY_ACCOUNTING.test.CONTRACT_TRANSACTION_STAGING AS cts
                INNER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS sds
                    ON cts.txn_id = sds.stmt_db_sales_distro_txn_id
                INNER JOIN cdd_rate AS cdd ON cts.contract_term_condition_id = cdd.contract_term_condition_id
                LEFT OUTER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.exchange_rate AS fx
                    ON sds.sale_currency_code = fx.from_currency_code
                        AND cdd.account_currency_code = fx.to_currency_code
                        AND fx.statement_period_id = 456
            WHERE cts.accounting_run_id = 123
        ) AS sub;
    """