"""Templates for calculating and inserting NR contract sales totals."""

from jinja2 import Template


def calculate_totals_nr_template() -> Template:
    """Calculate and stage NR totals for approval."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.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.{{schema}}.CONTRACT_TRANSACTION_NR_STAGING AS cts
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_NR AS snr
                    ON cts.TXN_ID = snr.STMT_DB_SALES_NR_TXN_ID
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.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.{{schema}}_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 = {{statement_period_id}}
            WHERE cts.ACCOUNTING_RUN_ID = {{accounting_run_id}}
        ) SELECT
            contract_transaction_id,
            transaction_id,
            accounting_run_id,
            '{{accounting_run_name}}' 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;
    """)