"""Template for calculating and inserting transaction mechanical deductions."""

from jinja2 import Template


def calculate_mech_deductions_template():
    """Calculate and stage mechanical deductions for approval."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.ACCOUNTING_RUN_RESULTS_DISTRO_MECH_STAGING(
            CONTRACT_TXN_ID,
            MECHANICAL_TRANSACTION_ID,
            CONTRACT_MECHANICAL_DEDUCTION_ID,
            TERRITORY,
            MECHANICAL_DEDUCTION_ADMIN_TYPE,
            MECHANICAL_DEDUCTION_RATE,
            PUBLISHER_ADMIN_FEE_RATE,

            MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY,
            GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
            NET_REVENUE_SALE_CURRENCY,
            MECHANICAL_DEDUCTION_AMOUNT_SALE_CURRENCY,
            PUBLISHER_ADMIN_FEE_SALE_CURRENCY,
            NET_REVENUE_AFTER_MECHANICAL_SALE_CURRENCY,

            MECHANICAL_ROYALTY_AMOUNT_PAYEE_CURRENCY,
            GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
            NET_REVENUE_PAYEE_CURRENCY,
            MECHANICAL_DEDUCTION_AMOUNT_PAYEE_CURRENCY,
            PUBLISHER_ADMIN_FEE_PAYEE_CURRENCY,
            NET_REVENUE_AFTER_MECHANICAL_PAYEE_CURRENCY
        )
        SELECT DISTINCT
            sub.CONTRACT_TXN_ID,
            sub.MECHANICAL_TRANSACTION_ID,
            sub.contract_mechanical_deduction_id,
            sub.territory,
            sub.MECHANICAL_DEDUCTION_ADMIN_TYPE,
            sub.MECHANICAL_DEDUCTION_RATE,
            sub.PUBLISHER_ADMIN_FEE_RATE,

            sub.MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY,
            sub.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
            sub.NET_REVENUE_SALE_CURRENCY,
            sub.MECHANICAL_DEDUCTION_AMOUNT_SALE_CURRENCY,
            sub.PUBLISHER_ADMIN_FEE_SALE_CURRENCY,
            sub.NET_REVENUE_AFTER_MECHANICAL_SALE_CURRENCY,

            (sub.MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY * sub.exchange_rate) AS MECHANICAL_ROYALTY_AMOUNT_PAYEE_CURRENCY,
            sub.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
            sub.NET_REVENUE_PAYEE_CURRENCY,
            (sub.MECHANICAL_DEDUCTION_AMOUNT_SALE_CURRENCY * sub.exchange_rate) AS MECHANICAL_DEDUCTION_AMOUNT_PAYEE_CURRENCY,
            (sub.PUBLISHER_ADMIN_FEE_SALE_CURRENCY * sub.exchange_rate) AS PUBLISHER_ADMIN_FEE_PAYEE_CURRENCY,
            (sub.NET_REVENUE_AFTER_MECHANICAL_SALE_CURRENCY * sub.exchange_rate) AS NET_REVENUE_AFTER_MECHANICAL_PAYEE_CURRENCY
        FROM (
            SELECT
                cts.CONTRACT_TXN_ID,
                mech_txn.MECHANICAL_TRANSACTION_ID,
                cmd.contract_mechanical_deduction_id,
                cmd.territory,
                cmd.admin_type AS MECHANICAL_DEDUCTION_ADMIN_TYPE,
                arrs.TERM_RATE AS MECHANICAL_DEDUCTION_RATE,
                IFF(cmd.admin_fee IS NOT NULL, cmd.admin_fee/100, NULL) AS PUBLISHER_ADMIN_FEE_RATE,
                IFF(mech_txn.CURRENCY_CODE = acct_curr.ISO_CODE, 1, fx.rate) AS exchange_rate,

                mech_txn.ROYALTY_AMOUNT * -1 AS MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY,
                arrs.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
                arrs.NET_REVENUE_SALE_CURRENCY,
                CASE
                    WHEN cmd.admin_type = 'both'
                        THEN MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY * arrs.TERM_RATE
                    WHEN cmd.admin_type = 'business'
                        THEN 0
                    WHEN cmd.admin_type = 'customer'
                        THEN MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY
                END AS MECHANICAL_DEDUCTION_AMOUNT_SALE_CURRENCY,
                CASE
                    WHEN PUBLISHER_ADMIN_FEE_RATE IS NOT NULL
                        THEN PUBLISHER_ADMIN_FEE_RATE * MECHANICAL_ROYALTY_AMOUNT_SALE_CURRENCY
                    ELSE NULL
                END AS PUBLISHER_ADMIN_FEE_SALE_CURRENCY,
                (
                    NET_REVENUE_SALE_CURRENCY +
                    MECHANICAL_DEDUCTION_AMOUNT_SALE_CURRENCY +
                    IFNULL(PUBLISHER_ADMIN_FEE_SALE_CURRENCY, 0)
                ) AS NET_REVENUE_AFTER_MECHANICAL_SALE_CURRENCY,

                arrs.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
                arrs.NET_REVENUE_PAYEE_CURRENCY
            FROM ROYALTY_ACCOUNTING.{{schema}}.ACCOUNTING_RUN_RESULTS_DISTRO_STAGING AS arrs
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_TRANSACTION_STAGING AS cts
                    ON arrs.CONTRACT_TXN_ID = cts.CONTRACT_TXN_ID
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn
                    ON arrs.TXN_ID = mech_txn.TXN_ID
                INNER JOIN ORCHARD_APP_REPORTING_V2.{{schema}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_mechanical_deduction AS cmd
                    ON cts.CONTRACT_ID = cmd.contract_id
                INNER JOIN LATERAL FLATTEN (INPUT => cmd.mechanical_type) AS mt
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CURRENCY_MAP AS acct_curr
                    ON cts.PAYEE_CURRENCY_ID = acct_curr.ISO_NUMBER
                LEFT OUTER JOIN ORCHARD_APP_REPORTING_V2.{{schema}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.exchange_rate AS fx
                    ON fx.statement_period_id = mech_txn.STATEMENT_PERIOD_ID
                        AND fx.from_currency_code = mech_txn.CURRENCY_CODE
                        AND fx.to_currency_code = acct_curr.ISO_CODE
            WHERE arrs.ACCOUNTING_RUN_ID = {{accounting_run_id}}
                AND cts.ACCOUNTING_RUN_ID = {{accounting_run_id}}
                AND IFNULL(mech_txn.ROYALTY_AMOUNT, 0) != 0
                AND cmd.territory = 'USA'
                AND mech_txn.country_code = 'USA'
                AND IFF(mech_txn.mechanical_type = 'ringtone', 'digital', mech_txn.mechanical_type) = mt.value
                AND cmd.deleted_at IS NULL
                AND cmd.deleted_by IS NULL
        ) AS sub;
    """)