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

from jinja2 import Template


def calculate_totals_distro_template():
    """Calculate and stage distribution totals for approval."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.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,
            {{accounting_run_id}},
            '{{accounting_run_name}}',
            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.{{schema}}.CONTRACT_DENORMALIZED_DISTRO
                WHERE accounting_run_id = {{accounting_run_id}}
            )
            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.{{schema}}.CONTRACT_TRANSACTION_STAGING AS cts
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.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.{{schema}}_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 = {{statement_period_id}}
            WHERE cts.accounting_run_id = {{accounting_run_id}}
        ) AS sub;
    """)