"""Templates for making unallocated neighbouring rights sales re-eligible ('rolling-over')."""

from jinja2 import Template


def insert_stmt_db_sales_nr_staging() -> Template:
    """Re-insert unallocated NR sales into STMT_DB_SALES_NR_STAGING."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_NR_STAGING (
            UNIQUE_DETAIL_ID,
            BATCH_ID,
            STATEMENT_ID,
            START_DATE,
            END_DATE,
            STORE_ID,
            SUBDISTRIBUTOR,
            SALE_CURRENCY_CODE,
            SALE_CURRENCY_CODE_ID,
            ACTIVITY_RATE,
            COUNTRY_ID,
            CONFIGURATION,
            TRANSACTION_TYPE,
            TRANSACTION_TYPE_ID,
            TRANSACTION_SUBTYPE_ID,
            CONTRIBUTOR_ONLY,
            CONTRIBUTOR_ID,
            CONTRIBUTOR_NAME,
            CONTRIBUTION_ID,
            ISRC,
            SOUND_RECORDING_ID,
            SOUND_RECORDING_NAME,
            QUANTITY,
            UNIT_PRICE_USD,
            TOTAL_USD,
            WITHHOLDING_TAX_USD
        )
        SELECT
            UNIQUE_DETAIL_ID,
            '{{statement_period_id}}-rollover' AS BATCH_ID,
            STATEMENT_ID,
            START_DATE,
            END_DATE,
            STORE_ID,
            SUBDISTRIBUTOR,
            SALE_CURRENCY_CODE,
            SALE_CURRENCY_CODE_ID,
            ACTIVITY_RATE,
            COUNTRY_ID,
            CONFIGURATION,
            TRANSACTION_TYPE,
            TRANSACTION_TYPE_ID,
            TRANSACTION_SUBTYPE_ID,
            CONTRIBUTOR_ONLY,
            CONTRIBUTOR_ID,
            CONTRIBUTOR_NAME,
            CONTRIBUTION_ID,
            ISRC,
            SOUND_RECORDING_ID,
            SOUND_RECORDING_NAME,
            QUANTITY,
            UNIT_PRICE_USD,
            TOTAL_USD,
            WITHHOLDING_TAX_USD
        FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_NR
        WHERE STMT_DB_SALES_NR_TXN_ID IN (
            SELECT DISTINCT(s.STMT_DB_SALES_NR_TXN_ID)
            FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_NR AS s
                INNER JOIN ORCHARD_APP_REPORTING_V2.{{schema}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.SALES_FILE AS sf
                    ON s.SALES_FILE_ID = sf.SALES_FILE_ID
                LEFT OUTER JOIN (
                    SELECT
                        arr.TXN_ID,
                        ar.ACCOUNTING_PERIOD_ID,
                        ar.ACCOUNTING_RUN_ID
                    FROM ROYALTY_ACCOUNTING.{{schema}}.ACCOUNTING_RUN_RESULTS_NR AS arr
                        INNER JOIN ORCHARD_APP_REPORTING_V2.{{schema}}_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN AS ar
                            ON ar.ACCOUNTING_RUN_ID = arr.ACCOUNTING_RUN_ID
                    WHERE ar.ACCOUNTING_PERIOD_ID = {{accounting_period_id}}
                        AND ar.RUN_STATUS = 'Committed'
                        AND ar._FIVETRAN_DELETED = FALSE
                ) AS sub
                    ON sub.TXN_ID = s.STMT_DB_SALES_NR_TXN_ID
            WHERE sf.ACCOUNTING_PERIOD_ID = {{accounting_period_id}}
                AND sub.TXN_ID IS NULL
                AND sf._FIVETRAN_DELETED = FALSE
        );
    """)