"""Query templates for snowflake_get_eligible_sales task."""

from jinja2 import Template


def copy_distribution_sales_from_staging_to_temp():
    """Copy sales from stmt_db_sales_distro_staging to stmt_db_sales_distro_temp.

    Records in stmt_db_sales_*_staging tables are sourced from stmt_db via kafka.
    Records in stmt_db_sales_*_temp tables represent a "sales_file" in abacus.
    Records in stmt_db_sales_* tables represent all approved sales
        in an accounting period. These are the sales used downstream in the calc.
    """
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{env}}.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,
            {{sales_file_id}},
            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.{{env}}.STMT_DB_SALES_DISTRO_STAGING AS stg
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.{{env}}.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.{{env}}.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 copy_nr_sales_from_staging_to_temp():
    """Copy sales from stmt_db_sales_nr_staging to stmt_db_sales_nr_temp.

    Records in stmt_db_sales_*_staging tables are sourced from stmt_db via kafka.
    Records in stmt_db_sales_*_temp tables represent a "sales_file" in abacus.
    Records in stmt_db_sales_* tables represent all approved sales
        in an accounting period. These are the sales used downstream in the calc.
    """
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{env}}.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,
            {{sales_file_id}},
            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.{{env}}.STMT_DB_SALES_NR_STAGING AS stg
            LEFT OUTER JOIN ROYALTY_ACCOUNTING.{{env}}.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.{{env}}.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
    """)