"""Query templates for snowflake_copy_approved_sales task."""

from jinja2 import Template


def copy_approved_distribution_sales() -> Template:
    """Copy sales from stmt_db_sales_distro_temp to stmt_db_sales_distro.

    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 (
            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
            temp.STMT_DB_SALES_DISTRO_TXN_ID,
            temp.UNIQUE_DETAIL_ID,
            temp.SALES_FILE_ID,
            temp.BATCH_ID,
            temp.STATEMENT_ID,
            temp.START_DATE,
            temp.TRANSACTION_DATE,
            temp.STORE_ID,
            temp.SUBDISTRIBUTOR,
            temp.SALE_CURRENCY_CODE,
            temp.SALE_CURRENCY_CODE_ID,
            temp.ACTIVITY_RATE,
            temp.COUNTRY_ID,
            temp.CONFIGURATION,
            temp.TRANSACTION_TYPE,
            temp.TRANSACTION_TYPE_ID,
            temp.TRANSACTION_SUBTYPE_ID,
            temp.LABEL_ID,
            temp.UPC,
            temp.CD,
            temp.TRACK_ID,
            temp.ISRC,
            temp.TRACK_NAME,
            temp.VIDEO_ID,
            temp.QUANTITY,
            temp.UNIT_PRICE_USD,
            temp.TOTAL_USD,
            temp.WITHHOLDING_TAX_USD,
            temp.RETAIL_PRICE_USD,
            temp.ORIGINAL_PRICE_USD,
            temp.DISCOUNT_USD,
            temp.PHYS_PPD_USD,
            temp.IS_EXCLUDED_FROM_SAP,
            temp.ACTUAL_STATEMENT_NUMBER
        FROM ROYALTY_ACCOUNTING.{{env}}.STMT_DB_SALES_DISTRO_TEMP AS temp
        WHERE temp.SALES_FILE_ID = {{sales_file_id}};
    """)


def copy_approved_nr_sales() -> Template:
    """Copy sales from stmt_db_sales_nr_temp to stmt_db_sales_nr.

    Records in stmt_db_sales_*_temp tables represent a "sales_file" in abacus.
    """
    return Template("""
        INSERT INTO ROYALTY_ACCOUNTING.{{env}}.STMT_DB_SALES_NR (
            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
            temp.STMT_DB_SALES_NR_TXN_ID,
            temp.UNIQUE_DETAIL_ID,
            temp.SALES_FILE_ID,
            temp.BATCH_ID,
            temp.STATEMENT_ID,
            temp.START_DATE,
            temp.END_DATE,
            temp.STORE_ID,
            temp.SUBDISTRIBUTOR,
            temp.SALE_CURRENCY_CODE,
            temp.SALE_CURRENCY_CODE_ID,
            temp.ACTIVITY_RATE,
            temp.COUNTRY_ID,
            temp.CONFIGURATION,
            temp.TRANSACTION_TYPE,
            temp.TRANSACTION_TYPE_ID,
            temp.TRANSACTION_SUBTYPE_ID,
            temp.CONTRIBUTOR_ONLY,
            temp.CONTRIBUTOR_ID,
            temp.CONTRIBUTOR_NAME,
            temp.CONTRIBUTION_ID,
            temp.ISRC,
            temp.SOUND_RECORDING_ID,
            temp.SOUND_RECORDING_NAME,
            temp.QUANTITY,
            temp.UNIT_PRICE_USD,
            temp.TOTAL_USD,
            temp.WITHHOLDING_TAX_USD
        FROM ROYALTY_ACCOUNTING.{{env}}.STMT_DB_SALES_NR_TEMP AS temp
        WHERE temp.SALES_FILE_ID = {{sales_file_id}};
    """)