"""Query templates for snowflake_export_mechanicals task."""

from jinja2 import Template


def export_mechanical_deductions_to_s3() -> Template:
    """Unload mechanical deductions for approved accounting run to S3 as TSV."""
    return Template("""
        COPY INTO @{{stage}}/{{s3_path}}
        FROM (
            SELECT
                mt.mechanical_transaction_id,
                mtd.track_unique_id,
                cm.customer_id,
                p.year,
                p.quarter,
                CASE
                    WHEN txn.transaction_type IN ('OP', 'TP')
                        THEN 'PS'
                    WHEN txn.transaction_type IN ('OR', 'TR')
                        THEN 'RE'
                    WHEN txn.transaction_type IN ('DT', 'DA')
                        THEN 'DPD'
                    ELSE txn.transaction_type
                END AS transaction_type,
                txn.quantity,
                1 AS "OWNERSHIP",
                mtd.royalty_rate,
                mtd.royalty_amount,
                mt.statement_period_id
            FROM ROYALTY_ACCOUNTING.{{schema}}.ACCOUNTING_RUN_RESULTS_DISTRO_MECH AS arrm
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.CONTRACT_TRANSACTION_DISTRO AS ctd
                    ON arrm.contract_txn_id = ctd.contract_txn_id
                INNER JOIN  ROYALTY_ACCOUNTING.{{schema}}.STMT_DB_SALES_DISTRO AS txn
                    ON ctd.txn_id = txn.stmt_db_sales_distro_txn_id
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION AS mt
                    ON arrm.mechanical_transaction_id = mt.mechanical_transaction_id
                INNER JOIN ROYALTY_ACCOUNTING.{{schema}}.SNAPSHOT_MECHANICAL_TRANSACTION_DETAIL AS mtd
                    ON mt.mechanical_transaction_id = mtd.mechanical_transaction_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.customer_master AS cm
                    ON txn.store_id = cm.customer_master_master_id
                INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.period AS p
                    ON mt.statement_period_id = p.period_id
            WHERE ctd.accounting_run_id = {{accounting_run_id}}
                AND mtd.is_licensed = true
                AND cm.territory = 1
        )
        FILE_FORMAT = (
            TYPE = CSV
            FIELD_DELIMITER = '\t'
            COMPRESSION = NONE
        )
        HEADER = TRUE
        SINGLE = TRUE
        MAX_FILE_SIZE = 33554432;
    """)