"""Test snowflake_get_eligible_sales queries."""

from dags.templates.sales_get_eligible import snowflake_get_eligible_sales


def test_copy_distribution_sales_from_staging_to_temp():
    """Test that template renders insert statement correctly."""
    template = snowflake_get_eligible_sales.copy_distribution_sales_from_staging_to_temp() \
        .render(
            env='test',
            sales_file_id=123
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO_TEMP (
            STMT_DB_SALES_DISTRO_TXN_ID,
            UNIQUE_DETAIL_ID,
            SALES_FILE_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
            stg.STMT_DB_SALES_DISTRO_TXN_ID,
            stg.UNIQUE_DETAIL_ID,
            123,
            stg.BATCH_ID,
            stg.STATEMENT_ID,
            stg.START_DATE,
            stg.TRANSACTION_DATE,
            stg.STORE_ID,
            stg.SUBDISTRIBUTOR,
            stg.SALE_CURRENCY_CODE,
            stg.SALE_CURRENCY_CODE_ID,
            stg.ACTIVITY_RATE,
            stg.COUNTRY_ID,
            stg.CONFIGURATION,
            stg.TRANSACTION_TYPE,
            stg.TRANSACTION_TYPE_ID,
            stg.TRANSACTION_SUBTYPE_ID,
            stg.LABEL_ID,
            stg.UPC,
            stg.CD,
            stg.TRACK_ID,
            stg.ISRC,
            stg.TRACK_NAME,
            stg.VIDEO_ID,
            stg.QUANTITY,
            stg.UNIT_PRICE_USD,
            stg.TOTAL_USD,
            stg.WITHHOLDING_TAX_USD,
            stg.RETAIL_PRICE_USD,
            stg.ORIGINAL_PRICE_USD,
            stg.DISCOUNT_USD,
            stg.PHYS_PPD_USD,
            stg.IS_EXCLUDED_FROM_SAP,
            stg.ACTUAL_STATEMENT_NUMBER
        FROM ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO_STAGING AS stg
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS d
                ON stg.STMT_DB_SALES_DISTRO_TXN_ID = d.STMT_DB_SALES_DISTRO_TXN_ID
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO_TEMP AS temp
                ON stg.STMT_DB_SALES_DISTRO_TXN_ID = temp.STMT_DB_SALES_DISTRO_TXN_ID
        WHERE d.STMT_DB_SALES_DISTRO_TXN_ID IS NULL
            AND temp.STMT_DB_SALES_DISTRO_TXN_ID IS NULL
    """


def test_copy_nr_sales_from_staging_to_temp():
    """Test that template renders insert statement correctly."""
    template = snowflake_get_eligible_sales.copy_nr_sales_from_staging_to_temp() \
        .render(
            env='test',
            sales_file_id=123
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.STMT_DB_SALES_NR_TEMP (
            STMT_DB_SALES_NR_TXN_ID,
            UNIQUE_DETAIL_ID,
            SALES_FILE_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
            stg.STMT_DB_SALES_NR_TXN_ID,
            stg.UNIQUE_DETAIL_ID,
            123,
            stg.BATCH_ID,
            stg.STATEMENT_ID,
            stg.START_DATE,
            stg.END_DATE,
            stg.STORE_ID,
            stg.SUBDISTRIBUTOR,
            stg.SALE_CURRENCY_CODE,
            stg.SALE_CURRENCY_CODE_ID,
            stg.ACTIVITY_RATE,
            stg.COUNTRY_ID,
            stg.CONFIGURATION,
            stg.TRANSACTION_TYPE,
            stg.TRANSACTION_TYPE_ID,
            stg.TRANSACTION_SUBTYPE_ID,
            stg.CONTRIBUTOR_ONLY,
            stg.CONTRIBUTOR_ID,
            stg.CONTRIBUTOR_NAME,
            stg.CONTRIBUTION_ID,
            stg.ISRC,
            stg.SOUND_RECORDING_ID,
            stg.SOUND_RECORDING_NAME,
            stg.QUANTITY,
            stg.UNIT_PRICE_USD,
            stg.TOTAL_USD,
            stg.WITHHOLDING_TAX_USD
        FROM ROYALTY_ACCOUNTING.test.STMT_DB_SALES_NR_STAGING AS stg
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_NR AS d
                ON stg.STMT_DB_SALES_NR_TXN_ID = d.STMT_DB_SALES_NR_TXN_ID
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_NR_TEMP AS temp
                ON stg.STMT_DB_SALES_NR_TXN_ID = temp.STMT_DB_SALES_NR_TXN_ID
        WHERE d.STMT_DB_SALES_NR_TXN_ID IS NULL
            AND temp.STMT_DB_SALES_NR_TXN_ID IS NULL
    """