"""Test snowflake_load_sales queries."""

from dags.templates.sales_ingest import snowflake_load_sales


def test_insert_sales_to_distro_staging_table():
    """Test that template renders insert statement correctly."""
    template = snowflake_load_sales.insert_sales_to_distro_staging_table() \
        .render(
            schema='TEST',
            batch_id=123,
            stage='s3://sales-files',
            s3_path=f'extract-sales/distro/123/parts/'
    )

    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
            t.$30 AS UNIQUE_DETAIL_ID,
            t.$1 AS BATCH_ID,
            t.$2 AS STATEMENT_ID,
            t.$9 AS START_DATE,
            t.$10 AS DATE,
            t.$4 AS CUSTOMER_MASTER_MASTER_ID,
            t.$5 AS SUBDISTRIBUTOR,
            t.$6 AS ORIGINAL_CURRENCY_ISO,
            cm.ISO_NUMBER,
            t.$7 AS ACTIVITY_RATE,
            t.$8 AS COUNTRY_ID,
            t.$20 AS CONFIGURATION,
            t.$21 AS TRANS_TYPE,
            dtt.TRANSACTIONTYPEID,
            t.$22 AS TRANS_SUBTYPE,
            t.$3 AS VENDOR_ID,
            t.$11 AS UPC,
            t.$12 AS CD,
            t.$13 AS TRACK_ID,
            t.$14 AS ISRC,
            t.$15 AS TRACK_NAME,
            t.$27 AS VIDEO_ID,
            t.$16 AS QTY,
            t.$17 AS UNIT_PRICE,
            t.$18 AS TOTAL,
            t.$19 AS WHT,
            t.$23 AS RETAIL_PRICE,
            t.$24 AS ORIGINAL_PRICE,
            t.$25 AS DISCOUNT,
            t.$26 AS PHYS_PPD,
            t.$29 AS SAP_EXCLUDE,
            t.$28 AS ACTUAL_STATEMENT_NO
        FROM @s3://sales-files/extract-sales/distro/123/parts/
            (FILE_FORMAT => ROYALTY_ACCOUNTING.TEST.ABACUS_SALES_FILE_INGEST_FORMAT) t
        LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE dtt ON dtt.TRANSACTIONTYPEABBR = t.$21
        LEFT JOIN ROYALTY_ACCOUNTING.TEST.CURRENCY_MAP cm ON cm.ISO_CODE = t.$6
    """


def test_insert_sales_to_nr_staging_table():
    """Test that template renders insert statement correctly."""
    template = snowflake_load_sales.insert_sales_to_nr_staging_table() \
        .render(
            schema='TEST',
            batch_id=123,
            stage='s3://sales-files',
            s3_path=f'extract-sales/nr/123/parts/'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.TEST.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
            t.$24 AS UNIQUE_DETAIL_ID,
            t.$1 AS BATCH_ID,
            t.$2 AS STATEMENT_ID,
            t.$8 AS START_DATE,
            t.$9 AS DATE,
            t.$3 AS CUSTOMER_MASTER_MASTER_ID,
            t.$4 AS SUBDISTRIBUTOR,
            t.$5 AS ORIGINAL_CURRENCY_ISO,
            cm.ISO_NUMBER,
            t.$6 AS ACTIVITY_RATE,
            t.$7 AS COUNTRY_ID,
            t.$20 AS CONFIGURATION,
            t.$21 AS TRANS_TYPE,
            dtt.TRANSACTIONTYPEID,
            t.$22 AS TRANS_SUBTYPE,
            t.$23 AS CONTRIBUTOR_ONLY,
            t.$10 AS CONTRIBUTOR_ID,
            t.$13 AS CONTRIBUTOR_NAME,
            t.$11 AS CONTRIBUTION_ID,
            t.$15 AS ISRC,
            t.$12 AS SOUND_RECORDING_ID,
            t.$14 AS SOUND_RECORDING_NAME,
            t.$16 AS QTY,
            t.$17 AS UNIT_PRICE,
            t.$18 AS TOTAL,
            t.$19 AS WHT
        FROM @s3://sales-files/extract-sales/nr/123/parts/
            (FILE_FORMAT => ROYALTY_ACCOUNTING.TEST.ABACUS_SALES_FILE_INGEST_FORMAT) t
        LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE dtt ON dtt.TRANSACTIONTYPEABBR = t.$21
        LEFT JOIN ROYALTY_ACCOUNTING.TEST.CURRENCY_MAP cm ON cm.ISO_CODE = t.$5
    """