"""Unit tests for creating NR contract snapshots in snowflake."""

from templates.accounting_run_calculate.snowflake_snapshot_contracts_nr \
    import insert_snapshot_contract_nr_template


def test_insert_snapshot_contract_nr_template():
    """Test that the template renders the copying of data from royalty_accounting_contract replica."""
    template = insert_snapshot_contract_nr_template().render(
        accounting_run_id=123,
        env='qa',
        schema='test',
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR(
            ACCOUNTING_RUN_ID,
            ACCOUNT_ID,
            ACCOUNT_CURRENCY_CODE,
            CONTRACT_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            CONTRACT_TERM_START,
            CONTRACT_TERM_END,
            TERM_TYPE,
            TERM_RATE,
            CONDITIONS,
            PRIORITY,
            CONTRACT_NAME,
            CONTRACT_TERM_NAME,
            CONTRACT_TERM_CONDITION_NAME
        ) WITH contract_nr AS (
            SELECT
                ar.accounting_run_id,
                ac.account_id,
                apt.currency_code,
                c.contract_id,
                ct.contract_term_id,
                ctc.contract_term_condition_id,
                IFNULL(cl.lifecycle_term_start, '1970-02-01') AS contract_term_start,
                IFNULL(cl.lifecycle_term_end, '2100-01-01') AS contract_term_end,
                ct.term_type,
                ctc.term_rate,
                ctc.conditions,
                ctc.priority,
                c.contract_name,
                ct.contract_term_name,
                ctc.contract_term_condition_name,
                ROW_NUMBER() OVER (
                    partition BY
                        c.contract_id,
                        ct.contract_term_id,
                        ctc.conditions
                    ORDER BY ctc.priority ASC
                ) AS priority_rank
            FROM ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.accounting_run AS ar
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.run_controller_contract AS rcc
                    ON ar.run_controller_id = rcc.run_controller_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.account_contract AS ac
                    ON rcc.contract_id = ac.contract_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.account_payment_term AS apt
                    ON ac.account_id = apt.account_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract AS c
                    ON rcc.contract_id = c.contract_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_lifecycle AS cl
                    ON c.contract_id = cl.contract_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term AS ct
                    ON c.contract_id = ct.contract_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term_condition AS ctc
                    ON ct.contract_term_id = ctc.contract_term_id
            WHERE ar.accounting_run_id = 123
                AND c.is_excluded_from_accounting_run = false
                AND cl.lifecycle_status NOT IN ('init', 'inactive')
                AND ct.term_type IN ('contributor_schedule', 'contribution_schedule')
                AND cl.deleted_at IS NULL
                AND ct.deleted_at IS NULL
                AND ctc.deleted_at IS NULL
            QUALIFY priority_rank = 1
        )
        SELECT
            accounting_run_id,
            account_id,
            currency_code,
            contract_id,
            contract_term_id,
            contract_term_condition_id,
            contract_term_start,
            contract_term_end,
            term_type,
            term_rate,
            conditions,
            priority,
            contract_name,
            contract_term_name,
            contract_term_condition_name
        FROM contract_nr;
    """