"""Unit tests for inserting and updating SNAPSHOT_MECHANICAL_TRANSACTIONS in snowflake."""

from templates.accounting_period_mechanicals.snowflake_snapshot_mechanical_transactions \
    import calculate_mechanical_royalty_amounts
from templates.accounting_period_mechanicals.snowflake_snapshot_mechanical_transactions \
    import insert_snapshot_mechanical_transactions


def test_insert_snapshot_mechanical_transactions():
    """Test that template renders a query to snapshot mechanical_transactions."""
    template = insert_snapshot_mechanical_transactions().render(
        accounting_period_id=123,
        sales_file_ids=[4, 5, 6],
        schema='test',
        statement_period_id=789
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION(
            STATEMENT_PERIOD_ID,
            ACCOUNTING_PERIOD_ID,
            TXN_ID,
            TRANSACTION_TYPE_ID,
            MECHANICAL_TYPE,
            COUNTRY_CODE,
            CURRENCY_CODE,
            ROYALTY_AMOUNT
        )
        SELECT
            789 AS statement_period_id,
            123 AS accounting_period_id,
            txn.stmt_db_sales_distro_txn_id AS txn_id,
            txn.transaction_type_id,
            CASE WHEN rttgtt.reference_transaction_type_id = 4 -- 'DR'
                    THEN 'ringtone'
                WHEN rttgtt.reference_transaction_type_group_id = 2 -- 'Download - Digital Distribution'
                    THEN 'digital'
                ELSE 'physical'
            END AS mechanical_type,
            cy.iso3166a3 AS country_code,
            txn.sale_currency_code,
            NULL AS royalty_amount
        FROM ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS txn
            INNER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.reference_transaction_type_group_transaction_type AS rttgtt
                ON txn.transaction_type_id = rttgtt.reference_transaction_type_id
            INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.country AS cy
                ON txn.country_id = cy.id
        WHERE txn.sales_file_id IN (4, 5, 6)
            AND txn.store_id NOT IN (615, 1688, 1690, 1811, 1865)
            AND rttgtt.reference_transaction_type_group_admin = 'contract_mechanical_admin';
    """


def test_calculate_mechanical_royalty_amounts():
    """Test template renders an update to calculate mechanical royalty amounts."""
    template = calculate_mechanical_royalty_amounts().render(
        accounting_period_id=123,
        schema='test'
    )

    assert template == """
        UPDATE ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn
        SET mech_txn.royalty_amount = sub.royalty_amount
        FROM (
            SELECT
                detail.mechanical_transaction_id,
                SUM(detail.royalty_amount) AS royalty_amount
            FROM ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION_DETAIL AS detail
                INNER JOIN ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn_2
                    ON detail.mechanical_transaction_id = mech_txn_2.mechanical_transaction_id
            WHERE mech_txn_2.accounting_period_id = 123
                AND detail.is_licensed = true
            GROUP BY detail.mechanical_transaction_id
        ) AS sub
        WHERE mech_txn.mechanical_transaction_id = sub.mechanical_transaction_id;
    """