"""Unit tests for creating NR contract_term and schedules snapshots in snowflake."""

from templates.accounting_run_calculate.snowflake_snapshot_contract_term_schedules_nr \
    import snapshot_contract_term_schedule_nr_template


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

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_TERM_SCHEDULE_NR(
            ACCOUNTING_RUN_ID,
            CONTRACT_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            CONTRIBUTOR_ID,
            CONTRIBUTION_ID,
            SCHEDULE_ID,
            SCHEDULE_NAME,
            TERM_TYPE
        ) SELECT
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_ID,
            c.CONTRACT_TERM_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            TRIM(sa.target_id) AS CONTRIBUTOR_ID,
            NULL AS CONTRIBUTION_ID,
            cts.schedule_id,
            s.schedule_name,
            c.TERM_TYPE
        FROM ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term_schedule AS cts
                ON c.CONTRACT_TERM_ID = cts.contract_term_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule AS s
                ON cts.schedule_id = s.schedule_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule_attachment AS sa
                ON s.schedule_id = sa.schedule_id
        WHERE c.ACCOUNTING_RUN_ID = 123
            AND c.TERM_TYPE = 'contributor_schedule'
            AND sa.target_type = 'contributor'
            AND sa.deleted_at IS NULL
            AND cts.deleted_at IS NULL

        UNION

        SELECT
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_ID,
            c.CONTRACT_TERM_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            TRIM(s.target_id) AS CONTRIBUTOR_ID,
            TRIM(sa.target_id) AS CONTRIBUTION_ID,
            cts.schedule_id,
            s.schedule_name,
            c.TERM_TYPE
        FROM ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term_schedule AS cts
                ON c.CONTRACT_TERM_ID = cts.contract_term_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule AS s
                ON cts.schedule_id = s.schedule_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule_attachment AS sa
                ON s.schedule_id = sa.schedule_id
        WHERE c.ACCOUNTING_RUN_ID = 123
            AND c.TERM_TYPE = 'contribution_schedule'
            AND sa.target_type = 'contribution'
            AND sa.deleted_at IS NULL
            AND cts.deleted_at IS NULL

        ORDER BY
            CONTRACT_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID,
            SCHEDULE_ID,
            CONTRIBUTOR_ID,
            CONTRIBUTION_ID;
    """