"""Templates for using snowflake to match NR contract terms to NR sales."""

from jinja2 import Template


def insert_contract_transaction_staging_nr_template() -> Template:
    """Match NR contract terms to NR sales and insert into contract_txn_staging."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_TRANSACTION_NR_STAGING(
            TXN_ID,
            CONTRACT_ID,
            ACCOUNT_ID,
            PAYEE_CURRENCY_ID,
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            SCHEDULE_ID,
            TERM_RATE
        ) SELECT
            snr.STMT_DB_SALES_NR_TXN_ID AS TXN_ID,
            c.CONTRACT_ID,
            c.ACCOUNT_ID,
            curr.ISO_NUMBER AS PAYEE_CURRENCY_ID,
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_TERM_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            c.SCHEDULE_ID,
            (c.TERM_RATE / 100) AS TERM_RATE
        FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_NR AS snr
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_DENORMALIZED_NR AS c
                ON TRIM(snr.CONTRIBUTOR_ID) = c.CONTRIBUTOR_ID
                    AND TRIM(snr.CONTRIBUTION_ID) = c.CONTRIBUTION_ID
                    AND (c.COUNTRY_ID = 0 OR c.COUNTRY_ID = snr.COUNTRY_ID)
                    AND (c.STORE_ID = 0 OR c.STORE_ID = snr.STORE_ID)
                    AND (c.TRANSACTION_TYPE_ID = 0 OR c.TRANSACTION_TYPE_ID = snr.TRANSACTION_TYPE_ID)
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CURRENCY_MAP AS curr
                ON c.ACCOUNT_CURRENCY_CODE = curr.ISO_CODE
        WHERE snr.CONTRIBUTOR_ONLY = 0
            AND c.ACCOUNTING_RUN_ID = {{accounting_run_id}}
            AND snr.SALES_FILE_ID IN ({{sales_file_ids|join(', ' )}});
    """)


def insert_contributor_only_contract_transaction_staging_nr_template() -> Template:
    """Match contributor-only NR contract terms to NR sales and insert into contract_txn_staging."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_TRANSACTION_NR_STAGING(
            TXN_ID,
            CONTRACT_ID,
            ACCOUNT_ID,
            PAYEE_CURRENCY_ID,
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            SCHEDULE_ID,
            TERM_RATE
        ) SELECT
            snr.STMT_DB_SALES_NR_TXN_ID AS TXN_ID,
            c.CONTRACT_ID,
            c.ACCOUNT_ID,
            curr.ISO_NUMBER AS PAYEE_CURRENCY_ID,
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_TERM_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            c.SCHEDULE_ID,
            (c.TERM_RATE / 100) AS TERM_RATE
        FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_NR AS snr
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_DENORMALIZED_NR_CONTRIB_ONLY AS c
                ON c.CONTRIBUTOR_ID = TRIM(snr.CONTRIBUTOR_ID)
                    AND (c.COUNTRY_ID = 0 OR c.COUNTRY_ID = snr.COUNTRY_ID)
                    AND (c.STORE_ID = 0 OR c.STORE_ID = snr.STORE_ID)
                    AND (c.TRANSACTION_TYPE_ID = 0 OR c.TRANSACTION_TYPE_ID = snr.TRANSACTION_TYPE_ID)
                    AND c.RATE_RANK = 1
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CURRENCY_MAP AS curr
                ON curr.ISO_CODE = c.ACCOUNT_CURRENCY_CODE
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_TRANSACTION_NR_STAGING AS cts
                ON cts.TXN_ID = snr.STMT_DB_SALES_NR_TXN_ID
                    AND cts.ACCOUNTING_RUN_ID = c.ACCOUNTING_RUN_ID
                    AND cts.TXN_ID IS NULL
        WHERE snr.CONTRIBUTOR_ONLY = 1
            AND c.ACCOUNTING_RUN_ID = {{accounting_run_id}}
            AND snr.SALES_FILE_ID IN ({{sales_file_ids|join(', ' )}});
    """)