"""Query template to create snapshot of NR contract_terms and schedules."""

from jinja2 import Template


def snapshot_contract_term_schedule_nr_template() -> Template:
    """Copy data from royalty_accounting snowflake replica into snapshot_contract_term_schedule_nr."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.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.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{env}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term_schedule AS cts
                ON c.CONTRACT_TERM_ID = cts.contract_term_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{env}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule AS s
                ON cts.schedule_id = s.schedule_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{env}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule_attachment AS sa
                ON s.schedule_id = sa.schedule_id
        WHERE c.ACCOUNTING_RUN_ID = {{accounting_run_id}}
            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.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{env}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.contract_term_schedule AS cts
                ON c.CONTRACT_TERM_ID = cts.contract_term_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{env}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule AS s
                ON cts.schedule_id = s.schedule_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.{{env}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.schedule_attachment AS sa
                ON s.schedule_id = sa.schedule_id
        WHERE c.ACCOUNTING_RUN_ID = {{accounting_run_id}}
            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;
    """)