"""Unit tests for re-inserting unallocated distro sales ('rolling over sales')."""

from templates.accounting_period_close.snowflake_rollover_unallocated_sales_distro \
    import insert_stmt_db_sales_distro_staging


def test_insert_stmt_db_sales_distro_staging():
    """Test that template renders insert statement to rollover unallocated distro sales."""
    template = insert_stmt_db_sales_distro_staging().render(
        accounting_period_id=123,
        statement_period_id=311,
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.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,
            '311-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.test.STMT_DB_SALES_DISTRO
        WHERE STMT_DB_SALES_DISTRO_TXN_ID IN (
            SELECT DISTINCT(s.STMT_DB_SALES_DISTRO_TXN_ID)
            FROM ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS s
                INNER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.SALES_FILE AS sf
                    ON s.SALES_FILE_ID = sf.SALES_FILE_ID
                LEFT OUTER JOIN ROYALTY_ACCOUNTING.test.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.test.ACCOUNTING_RUN_RESULTS_DISTRO AS arr
                        INNER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNTING_RUN AS ar
                            ON ar.ACCOUNTING_RUN_ID = arr.ACCOUNTING_RUN_ID
                    WHERE ar.ACCOUNTING_PERIOD_ID = 123
                        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= 123
                AND sub.TXN_ID IS NULL
                AND ssd.STMT_DB_SALES_DISTRO_TXN_ID IS NULL
                AND sf._FIVETRAN_DELETED = FALSE
        );
    """