"""Query templates for snowflake_export_sales task."""

from jinja2 import Template


def export_approved_distro_sales_to_s3() -> Template:
    """Unload approved distribution sales to S3 as a parquet file."""
    return Template("""
        COPY INTO @{{stage}}/{{s3_path}}
        FROM (
            SELECT
                CAST(txn.STMT_DB_SALES_DISTRO_TXN_ID AS BIGINT) AS txnId,
                TO_DECIMAL(txn.ACTIVITY_RATE, 7, 6) AS activityRate,
                CAST(txn.COUNTRY_ID AS INT) AS countryId,
                txn.ISRC AS isrc,
                CAST(txn.LABEL_ID AS INT) AS labelId,
                CAST(txn.QUANTITY AS INT) AS quantity,
                CAST(txn.SALE_CURRENCY_CODE_ID AS INT) AS saleCurrencyId,
                CAST(txn.STORE_ID AS INT) AS storeId,
                txn.TRANSACTION_DATE AS transactionDate,
                CAST(txn.TRANSACTION_TYPE_ID AS INT) AS transactionTypeId,
                TO_DECIMAL(txn.UNIT_PRICE_USD, 18, 6) AS unitPriceUSD,
                CAST(txn.UPC AS BIGINT) AS upc,
                TO_DECIMAL(txn.WITHHOLDING_TAX_USD, 18, 6) AS withholdingTaxUSD
            FROM ROYALTY_ACCOUNTING.{{env}}.STMT_DB_SALES_DISTRO AS txn
            WHERE txn.SALES_FILE_ID = {{sales_file_id}}
        )
        FILE_FORMAT = (TYPE = PARQUET)
        HEADER = TRUE
    """)


def export_approved_nr_sales_to_s3() -> Template:
    """Unload approved NR sales to S3 as a parquet file."""
    return Template("""
        COPY INTO @{{stage}}/{{s3_path}}
        FROM (
            SELECT
                CAST(txn.STMT_DB_SALES_NR_TXN_ID AS BIGINT) AS txnId,
                TO_DECIMAL(txn.ACTIVITY_RATE, 7, 6) AS activityRate,
                CAST(txn.COUNTRY_ID AS INT) AS countryId,
                txn.ISRC AS isrc,
                txn.SOUND_RECORDING_ID AS soundRecordingId,
                txn.CONTRIBUTOR_ID AS contributorId,
                CAST(txn.QUANTITY AS INT) AS quantity,
                CAST(txn.SALE_CURRENCY_CODE_ID AS INT) AS saleCurrencyId,
                CAST(txn.STORE_ID AS INT) AS storeId,
                txn.START_DATE AS startDate,
                txn.END_DATE AS endDate,
                CAST(txn.TRANSACTION_TYPE_ID AS INT) AS transactionTypeId,
                TO_DECIMAL(txn.UNIT_PRICE_USD, 18, 6) AS unitPriceUSD,
                txn.CONTRIBUTION_ID AS contributionId,
                TO_DECIMAL(txn.WITHHOLDING_TAX_USD, 18, 6) AS withholdingTaxUSD
            FROM ROYALTY_ACCOUNTING.{{env}}.STMT_DB_SALES_NR AS txn
            WHERE txn.SALES_FILE_ID = {{sales_file_id}}
        )
        FILE_FORMAT = (TYPE = PARQUET)
        HEADER = TRUE
    """)