"""Query templates for saving flattened NR contract data."""

from jinja2 import Template


def insert_flat_nr_contract_data_template() -> Template:
    """Insert denormalized NR contract data into CONTRACT_DENORMALIZED_NR table."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_DENORMALIZED_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,
            SCHEDULE_ID,
            CONTRIBUTOR_ID,
            CONTRIBUTION_ID,
            COUNTRY_ID,
            STORE_ID,
            TRANSACTION_TYPE_ID,
            PRIORITY
        ) WITH all_nr AS (
            SELECT DISTINCT
                c.ACCOUNTING_RUN_ID,
                c.ACCOUNT_ID,
                c.ACCOUNT_CURRENCY_CODE,
                c.CONTRACT_ID,
                c.CONTRACT_TERM_ID,
                c.CONTRACT_TERM_CONDITION_ID,
                c.CONTRACT_TERM_START,
                c.CONTRACT_TERM_END,
                c.TERM_TYPE,
                c.TERM_RATE,
                ct.SCHEDULE_ID,
                ct.CONTRIBUTOR_ID,
                ct.CONTRIBUTION_ID,
                cn.COUNTRY_ID,
                s.STORE_ID,
                tt.TRANSACTION_TYPE_ID,
                c.PRIORITY,
                ROW_NUMBER() OVER (
                    partition BY
                        c.CONTRACT_ID,
                        ct.CONTRIBUTION_ID,
                        cn.COUNTRY_ID,
                        s.STORE_ID,
                        tt.TRANSACTION_TYPE_ID,
                        c.TERM_RATE
                    ORDER BY c.priority
                ) AS priority_rank
            FROM ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_SCHEDULE_NR AS ct
                    ON c.CONTRACT_TERM_CONDITION_ID = ct.CONTRACT_TERM_CONDITION_ID
                        AND c.ACCOUNTING_RUN_ID = ct.ACCOUNTING_RUN_ID
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_COUNTRY AS cn
                    ON c.CONTRACT_TERM_CONDITION_ID = cn.CONTRACT_TERM_CONDITION_ID
                        AND c.ACCOUNTING_RUN_ID = cn.ACCOUNTING_RUN_ID
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_STORE AS s
                    ON c.CONTRACT_TERM_CONDITION_ID = s.CONTRACT_TERM_CONDITION_ID
                        AND c.ACCOUNTING_RUN_ID = s.ACCOUNTING_RUN_ID
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_TRANSACTION_TYPE AS tt
                    ON c.CONTRACT_TERM_CONDITION_ID = tt.CONTRACT_TERM_CONDITION_ID
                        AND c.ACCOUNTING_RUN_ID = tt.ACCOUNTING_RUN_ID
            WHERE c.ACCOUNTING_RUN_ID = {{accounting_run_id}}
                AND ct.CONTRIBUTION_ID IS NOT NULL
                AND (cn.country_id <> 0 OR (cn.country_id = 0 AND cn.country_code = 'WW'))
            QUALIFY priority_rank = 1
        ) SELECT
            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,
            SCHEDULE_ID,
            CONTRIBUTOR_ID,
            CONTRIBUTION_ID,
            COUNTRY_ID,
            STORE_ID,
            TRANSACTION_TYPE_ID,
            PRIORITY
        FROM all_nr;
    """)


def insert_flat_nr_contract_contributor_only_data_template() -> Template:
    """Insert denormalized NR contract data into CONTRACT_DENORMALIZED_NR_CONTRIB_ONLY table."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_DENORMALIZED_NR_CONTRIB_ONLY(
            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,
            SCHEDULE_ID,
            CONTRIBUTOR_ID,
            COUNTRY_ID,
            STORE_ID,
            TRANSACTION_TYPE_ID,
            PRIORITY,
            RATE_RANK
        ) SELECT DISTINCT
            c.ACCOUNTING_RUN_ID,
            c.ACCOUNT_ID,
            c.ACCOUNT_CURRENCY_CODE,
            c.CONTRACT_ID,
            c.CONTRACT_TERM_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            c.CONTRACT_TERM_START,
            c.CONTRACT_TERM_END,
            c.TERM_TYPE,
            c.TERM_RATE,
            ct.SCHEDULE_ID,
            ct.CONTRIBUTOR_ID,
            cn.COUNTRY_ID,
            s.STORE_ID,
            tt.TRANSACTION_TYPE_ID,
            c.PRIORITY,
            ROW_NUMBER() OVER (
                partition BY
                    c.CONTRACT_ID,
                    ct.CONTRIBUTOR_ID,
                    cn.COUNTRY_ID,
                    s.STORE_ID,
                    tt.TRANSACTION_TYPE_ID
                ORDER BY c.TERM_RATE DESC -- all things being equal, give more to the client
            ) AS RATE_RANK
        FROM ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_SCHEDULE_NR AS ct
                ON c.CONTRACT_TERM_CONDITION_ID = ct.CONTRACT_TERM_CONDITION_ID
                    AND c.ACCOUNTING_RUN_ID = ct.ACCOUNTING_RUN_ID
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_COUNTRY AS cn
                ON c.CONTRACT_TERM_CONDITION_ID = cn.CONTRACT_TERM_CONDITION_ID
                    AND c.ACCOUNTING_RUN_ID = cn.ACCOUNTING_RUN_ID
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_STORE AS s
                ON c.CONTRACT_TERM_CONDITION_ID = s.CONTRACT_TERM_CONDITION_ID
                    AND c.ACCOUNTING_RUN_ID = s.ACCOUNTING_RUN_ID
            INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_CONTRACT_TERM_CONDITION_TRANSACTION_TYPE AS tt
                ON c.CONTRACT_TERM_CONDITION_ID = tt.CONTRACT_TERM_CONDITION_ID
                    AND c.ACCOUNTING_RUN_ID = tt.ACCOUNTING_RUN_ID
        WHERE c.ACCOUNTING_RUN_ID = {{accounting_run_id}}
            AND ct.CONTRIBUTION_ID IS NULL
            AND (cn.country_id <> 0 OR (cn.country_id = 0 AND cn.country_code = 'WW'))
        QUALIFY rate_rank = 1;
    """)