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

from jinja2 import Template


def insert_stmt_db_sales_distro_staging() -> Template:
    """Re-insert unallocated distro sales into STMT_DB_SALES_DISTRO_STAGING."""
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_DISTRO_STAGING (
            UNIQUE_DETAIL_ID,
            BATCH_ID,
            STATEMENT_ID,
            START_DATE,
            TRANSACTION_DATE,
            STORE_ID,
            SUBDISTRIBUTOR,
            SALE_CURRENCY_CODE,
            SALE_CURRENCY_CODE_ID,
            ACTIVITY_RATE,
            COUNTRY_ID,
            CONFIGURATION,
            TRANSACTION_TYPE,
            TRANSACTION_TYPE_ID,
            TRANSACTION_SUBTYPE_ID,
            LABEL_ID,
            UPC,
            CD,
            TRACK_ID,
            ISRC,
            TRACK_NAME,
            VIDEO_ID,
            QUANTITY,
            UNIT_PRICE_USD,
            TOTAL_USD,
            WITHHOLDING_TAX_USD,
            RETAIL_PRICE_USD,
            ORIGINAL_PRICE_USD,
            DISCOUNT_USD,
            PHYS_PPD_USD,
            IS_EXCLUDED_FROM_SAP,
            ACTUAL_STATEMENT_NUMBER
        )
        SELECT
            UNIQUE_DETAIL_ID,
            '{{statement_period_id}}-rollover' AS BATCH_ID,
            STATEMENT_ID,
            START_DATE,
            TRANSACTION_DATE,
            STORE_ID,
            SUBDISTRIBUTOR,
            SALE_CURRENCY_CODE,
            SALE_CURRENCY_CODE_ID,
            ACTIVITY_RATE,
            COUNTRY_ID,
            CONFIGURATION,
            TRANSACTION_TYPE,
            TRANSACTION_TYPE_ID,
            TRANSACTION_SUBTYPE_ID,
            LABEL_ID,
            UPC,
            CD,
            TRACK_ID,
            ISRC,
            TRACK_NAME,
            VIDEO_ID,
            QUANTITY,
            UNIT_PRICE_USD,
            TOTAL_USD,
            WITHHOLDING_TAX_USD,
            RETAIL_PRICE_USD,
            ORIGINAL_PRICE_USD,
            DISCOUNT_USD,
            PHYS_PPD_USD,
            IS_EXCLUDED_FROM_SAP,
            ACTUAL_STATEMENT_NUMBER
        FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_DISTRO
        WHERE STMT_DB_SALES_DISTRO_TXN_ID IN (
            SELECT DISTINCT(s.STMT_DB_SALES_DISTRO_TXN_ID)
            FROM ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_DISTRO 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 ROYALTY_ACCOUNTING.{{schema}}.SEQUESTERED_SALES_DISTRO AS ssd
                    ON ssd.STMT_DB_SALES_DISTRO_TXN_ID = s.STMT_DB_SALES_DISTRO_TXN_ID
                LEFT OUTER JOIN (
                    SELECT
                        arr.TXN_ID,
                        ar.ACCOUNTING_PERIOD_ID,
                        ar.ACCOUNTING_RUN_ID
                    FROM ROYALTY_ACCOUNTING.{{schema}}.ACCOUNTING_RUN_RESULTS_DISTRO 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_DISTRO_TXN_ID
            WHERE sf.ACCOUNTING_PERIOD_ID= {{accounting_period_id}}
                AND sub.TXN_ID IS NULL
                AND ssd.STMT_DB_SALES_DISTRO_TXN_ID IS NULL
                AND sf._FIVETRAN_DELETED = FALSE
        );
    """)