"""Unit tests for matching contracts to sales and inserting results in snowflake."""

from templates.accounting_run_calculate.snowflake_match_contract_terms_nr import \
    insert_contract_transaction_staging_nr_template
from templates.accounting_run_calculate.snowflake_match_contract_terms_nr import \
    insert_contributor_only_contract_transaction_staging_nr_template


def test_insert_contract_transaction_staging_nr_template():
    """Test that the template renders to match sales to contracts."""
    template = insert_contract_transaction_staging_nr_template().render(
        accounting_run_id=123,
        sales_file_ids=[4, 5, 6],
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.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.test.STMT_DB_SALES_NR AS snr
            INNER JOIN ROYALTY_ACCOUNTING.test.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.test.CURRENCY_MAP AS curr
                ON c.ACCOUNT_CURRENCY_CODE = curr.ISO_CODE
        WHERE snr.CONTRIBUTOR_ONLY = 0
            AND c.ACCOUNTING_RUN_ID = 123
            AND snr.SALES_FILE_ID IN (4, 5, 6);
    """


def test_insert_contributor_only_contract_transaction_staging_nr_template():
    """Test that the template renders to match contrib-only sales to contrib-only contracts."""
    template = insert_contributor_only_contract_transaction_staging_nr_template().render(
        accounting_run_id=123,
        sales_file_ids=[4, 5, 6],
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.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.test.STMT_DB_SALES_NR AS snr
            INNER JOIN ROYALTY_ACCOUNTING.test.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.test.CURRENCY_MAP AS curr
                ON curr.ISO_CODE = c.ACCOUNT_CURRENCY_CODE
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.test.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 = 123
            AND snr.SALES_FILE_ID IN (4, 5, 6);
    """