"""Query templates for load_sales_to_staging_table task."""

from jinja2 import Template


def insert_sales_to_distro_staging_table():
    """Insert sales from S3 to 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
            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 @{{stage}}/{{s3_path}}
            (FILE_FORMAT => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_SALES_FILE_INGEST_FORMAT) t
        LEFT JOIN FACTS.{{schema}}.DIM_TRANSACTIONTYPE dtt ON dtt.TRANSACTIONTYPEABBR = t.$21
        LEFT JOIN ROYALTY_ACCOUNTING.{{schema}}.CURRENCY_MAP cm ON cm.ISO_CODE = t.$6
    """)


def insert_sales_to_nr_staging_table():
    """Insert sales from S3 to 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
            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 @{{stage}}/{{s3_path}}
            (FILE_FORMAT => ROYALTY_ACCOUNTING.{{schema}}.ABACUS_SALES_FILE_INGEST_FORMAT) t
        LEFT JOIN FACTS.{{schema}}.DIM_TRANSACTIONTYPE dtt ON dtt.TRANSACTIONTYPEABBR = t.$21
        LEFT JOIN ROYALTY_ACCOUNTING.{{schema}}.CURRENCY_MAP cm ON cm.ISO_CODE = t.$5
    """)