"""Unit tests for inserting and calculating SNAPSHOT_MECHANICAL_TRANSACTION_DETAIL in snowflake."""

from templates.accounting_period_mechanicals.snowflake_snapshot_mechanical_transaction_details \
    import insert_snapshot_mechanical_transaction_details_by_isrc
from templates.accounting_period_mechanicals.snowflake_snapshot_mechanical_transaction_details \
    import insert_snapshot_mechanical_transaction_details_by_upc


def test_insert_snapshot_mechanical_transaction_details_by_isrc():
    """Test that template renders query to snapshot/calculate track sale mechanicals."""
    template = insert_snapshot_mechanical_transaction_details_by_isrc().render(
        sales_file_ids=[4, 5, 6],
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION_DETAIL(
            MECHANICAL_TRANSACTION_ID,
            UPC,
            ISRC,
            TRACK_UNIQUE_ID,
            TRACK_SECONDS,
            IS_LICENSED,
            QUANTITY,
            ROYALTY_RATE,
            ROYALTY_AMOUNT
        )
        SELECT
            mech_txn.mechanical_transaction_id,
            txn.upc,
            txn.isrc,
            t.id AS track_unique_id,
            (IFNULL(t.length_minute, 0) * 60) + IFNULL(t.length_seconds, 0) AS track_seconds,
            CASE
                WHEN TRIM(t.third_party_publisher) = '' OR t.third_party_publisher IS NULL THEN FALSE
                ELSE TO_BOOLEAN(t.third_party_publisher)
            END AS is_licensed,
            txn.quantity,
            CASE
                WHEN txn.country_id != mech_rates.country_id OR is_licensed = false
                    THEN 0
                WHEN txn.quantity < 0 AND mech_txn.transaction_type_id IN (4, 19, 23) -- 'DR', 'DT', 'DA'
                    THEN 0
                WHEN mech_txn.transaction_type_id = 4 -- 'DR'
                    THEN mech_rates.ringtone_rate
                WHEN track_seconds > 300
                    THEN CEIL(track_seconds / 60) * mech_rates.minute_rate
                ELSE mech_rates.base_rate
            END AS royalty_rate,
            royalty_rate * quantity AS royalty_amount
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.track AS t
            INNER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS txn
                ON UPPER(t.isrc) = UPPER(txn.isrc)
                    AND t.upc = txn.UPC
                    AND t.cd = txn.CD
                    AND t.track_id = txn.TRACK_ID
            INNER JOIN ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn
                ON mech_txn.txn_id = txn.stmt_db_sales_distro_txn_id
            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 (
                SELECT
                    cy.id AS country_id,
                    rmr.country_code,
                    rmr.base_rate,
                    rmr.minute_rate,
                    rmr.ringtone_rate,
                    rmr.effective_start_date,
                    ROW_NUMBER() OVER (
                        PARTITION BY rmr.country_code
                        ORDER BY rmr.effective_start_date DESC
                    ) AS ranking
                FROM ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.reference_mechanical_rate AS rmr
                    INNER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.statement_period AS sp
                        ON YEAR(rmr.effective_start_date) = sp.statement_year
                            AND sp.statement_period_status = 'current'
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.country AS cy
                        ON rmr.country_code = cy.iso3166a3
                QUALIFY ranking = 1
            ) AS mech_rates
        WHERE txn.sales_file_id IN (4, 5, 6)
            AND IFF(txn.isrc = '', NULL, txn.isrc) IS NOT NULL
            AND rttgtt.reference_transaction_type_group_admin = 'contract_mechanical_admin';
    """


def test_insert_snapshot_mechanical_transaction_details_by_upc():
    """Test that template renders query to snapshot/calculate album sale mechanicals."""
    template = insert_snapshot_mechanical_transaction_details_by_upc().render(
        sales_file_ids=[4, 5, 6],
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION_DETAIL(
            MECHANICAL_TRANSACTION_ID,
            UPC,
            ISRC,
            TRACK_UNIQUE_ID,
            TRACK_SECONDS,
            IS_LICENSED,
            QUANTITY,
            ROYALTY_RATE,
            ROYALTY_AMOUNT
        )
        SELECT
            mech_txn.mechanical_transaction_id,
            txn.upc,
            NULL AS isrc,
            t.id AS track_unique_id,
            (IFNULL(t.length_minute, 0) * 60) + IFNULL(t.length_seconds, 0) AS track_seconds,
            CASE
                WHEN TRIM(t.third_party_publisher) = '' OR t.third_party_publisher IS NULL THEN FALSE
                ELSE TO_BOOLEAN(t.third_party_publisher)
            END AS is_licensed,
            txn.quantity,
            CASE
                WHEN txn.country_id != mech_rates.country_id OR is_licensed = false
                    THEN 0
                WHEN txn.quantity < 0 AND mech_txn.transaction_type_id IN (4, 19, 23) -- 'DR', 'DT', 'DA'
                    THEN 0
                WHEN mech_txn.transaction_type_id = 4 -- 'DR'
                    THEN mech_rates.ringtone_rate
                WHEN track_seconds > 300
                    THEN CEIL(track_seconds / 60) * mech_rates.minute_rate
                ELSE mech_rates.base_rate
            END AS royalty_rate,
            royalty_rate * quantity AS royalty_amount
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.track AS t
            INNER JOIN ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS txn
                ON t.upc = txn.UPC
            INNER JOIN ROYALTY_ACCOUNTING.test.SNAPSHOT_MECHANICAL_TRANSACTION AS mech_txn
                ON mech_txn.txn_id = txn.stmt_db_sales_distro_txn_id
            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 (
                SELECT
                    cy.id AS country_id,
                    rmr.country_code,
                    rmr.base_rate,
                    rmr.minute_rate,
                    rmr.ringtone_rate,
                    rmr.effective_start_date,
                    ROW_NUMBER() OVER (
                        PARTITION BY rmr.country_code
                        ORDER BY rmr.effective_start_date DESC
                    ) AS ranking
                FROM ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.reference_mechanical_rate AS rmr
                    INNER JOIN ORCHARD_APP_REPORTING_V2.test_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.statement_period AS sp
                        ON YEAR(rmr.effective_start_date) = sp.statement_year
                            AND sp.statement_period_status = 'current'
                    INNER JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.country AS cy
                        ON rmr.country_code = cy.iso3166a3
                QUALIFY ranking = 1
            ) AS mech_rates
        WHERE txn.sales_file_id IN (4, 5, 6)
            AND COALESCE(txn.ISRC, '') = ''
            AND rttgtt.reference_transaction_type_group_admin = 'contract_mechanical_admin';
    """