"""Test export_mechanical_deductions_to_s3 query."""

from templates.accounting_run_commit.snowflake_export_mech_deductions \
    import export_mechanical_deductions_to_s3


def test_export_mechanical_deductions_to_s3_template():
    """Test template renders a snowflake statement to export mech deductions to S3 as TSV."""
    s3_key = '456-accounting-period-name/123-run-controller-name/mechanical-export.tsv'

    template = export_mechanical_deductions_to_s3().render(
        accounting_run_id=123,
        s3_path=s3_key,
        schema='test',
        stage='s3://test-royalties-sales-files'
    )

    assert template == """
        COPY INTO @s3://test-royalties-sales-files/456-accounting-period-name/123-run-controller-name/mechanical-export.tsv
        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.test.ACCOUNTING_RUN_RESULTS_DISTRO_MECH AS arrm
                INNER JOIN ROYALTY_ACCOUNTING.test.CONTRACT_TRANSACTION_DISTRO AS ctd
                    ON arrm.contract_txn_id = ctd.contract_txn_id
                INNER JOIN  ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS txn
                    ON ctd.txn_id = txn.stmt_db_sales_distro_txn_id
                INNER JOIN ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION AS mt
                    ON arrm.mechanical_transaction_id = mt.mechanical_transaction_id
                INNER JOIN ROYALTY_ACCOUNTING.test.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 = 123
                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;
    """