"""Unit tests for matching contracts to sales and inserting results in snowflake."""

from templates.accounting_run_calculate.snowflake_match_contract_terms \
    import insert_contract_transaction_staging_distro_template


def test_insert_contract_transaction_staging_distro():
    """Test template renders query matching contracts to sales and inserts results."""
    template = insert_contract_transaction_staging_distro_template().render(
        accounting_run_id=12,
        sales_file_ids=[1, 2, 3],
        schema='test'
    )
    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.CONTRACT_TRANSACTION_STAGING(
            TXN_ID,
            CONTRACT_ID,
            ACCOUNT_ID,
            PAYEE_CURRENCY_ID,
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_ID,
            CONTRACT_TERM_CONDITION_ID
        )
        WITH contract_labels AS (
            SELECT DISTINCT contract_id, label_id
            FROM ROYALTY_ACCOUNTING.test.CONTRACT_DENORMALIZED_DISTRO
            WHERE accounting_run_id = 12
        ),

        -- Filter sales once
        sales AS (
            SELECT
                sds.stmt_db_sales_distro_txn_id AS txn_id,
                cl.contract_id,
                sds.label_id,
                sds.upc,
                sds.isrc,
                sds.country_id,
                sds.store_id,
                sds.transaction_type_id
            FROM ROYALTY_ACCOUNTING.test.STMT_DB_SALES_DISTRO AS sds
                INNER JOIN contract_labels AS cl
                    ON sds.label_id = cl.label_id
            WHERE sds.sales_file_id in (1, 2, 3)
        ),

        -- Filter contract terms once
        cdd AS (
            SELECT
                accounting_run_id,
                contract_id,
                label_id,
                term_type,
                priority,
                term_rate,
                upc,
                isrc,
                store_id,
                country_id,
                transaction_type_id,
                account_id,
                account_currency_code,
                contract_term_id,
                contract_term_condition_id
            FROM ROYALTY_ACCOUNTING.test.CONTRACT_DENORMALIZED_DISTRO
            WHERE accounting_run_id = 12
                AND term_type IN ('label', 'product', 'track')
        ),

        -- Track
        track_match AS (
            SELECT
                s.txn_id,
                cdd.accounting_run_id,
                cdd.contract_id,
                cdd.contract_term_id,
                cdd.contract_term_condition_id,
                cdd.account_id,
                cdd.account_currency_code,
                ROW_NUMBER() OVER (
                    PARTITION BY s.txn_id, cdd.label_id, cdd.upc, cdd.isrc
                    ORDER BY cdd.priority ASC, cdd.term_rate DESC, cdd.contract_term_condition_id ASC
                ) AS ranking
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.upc        = s.upc
                    AND cdd.isrc       = s.isrc
                    AND cdd.term_type  = 'track'
                    AND cdd.store_id   IN (0, s.store_id)
                    AND cdd.country_id IN (0, s.country_id)
                    AND cdd.transaction_type_id IN (0, s.transaction_type_id)
            QUALIFY ranking = 1
        ),

        -- Product
        product_variants AS (
            -- Variant 1: exact store, exact txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.upc        = s.upc
                    AND cdd.store_id   = s.store_id
                    AND cdd.country_id IN (0, s.country_id)
                    AND cdd.transaction_type_id = s.transaction_type_id
            WHERE cdd.term_type = 'product'

            UNION ALL

            -- Variant 2: exact store, wildcard txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.upc        = s.upc
                    AND cdd.store_id   = s.store_id
                    AND cdd.country_id IN (0, s.country_id)
            WHERE cdd.transaction_type_id = 0
                AND cdd.term_type = 'product'

            UNION ALL

            -- Variant 3: wildcard store, exact txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.upc        = s.upc
                    AND cdd.country_id IN (0, s.country_id)
                    AND cdd.transaction_type_id = s.transaction_type_id
            WHERE cdd.store_id = 0
                AND cdd.term_type = 'product'

            UNION ALL

            -- Variant 4: wildcard store, wildcard txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.upc        = s.upc
                    AND cdd.country_id IN (0, s.country_id)
            WHERE cdd.store_id = 0
                AND cdd.transaction_type_id = 0
                AND cdd.term_type  = 'product'
        ),

        product_match AS (
            SELECT
                v.txn_id,
                v.accounting_run_id,
                v.contract_id,
                v.contract_term_id,
                v.contract_term_condition_id,
                v.account_id,
                v.account_currency_code,
                ROW_NUMBER() OVER (
                    PARTITION BY v.txn_id, v.label_id, v.upc
                    ORDER BY v.priority ASC, v.term_rate DESC, v.contract_term_condition_id ASC
                ) AS ranking
            FROM product_variants AS v
            WHERE NOT EXISTS (SELECT 1 FROM track_match t WHERE t.txn_id = v.txn_id)
            QUALIFY ranking = 1
        ),

        -- Label
        label_variants AS (
            -- Variant 1: exact store, exact txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
            INNER JOIN cdd
                ON cdd.contract_id = s.contract_id
                AND cdd.label_id   = s.label_id
                AND cdd.store_id   = s.store_id
                AND cdd.country_id IN (0, s.country_id)
                AND cdd.transaction_type_id = s.transaction_type_id
            WHERE cdd.term_type = 'label'

            UNION ALL

            -- Variant 2: exact store, wildcard txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.store_id   = s.store_id
                    AND cdd.country_id IN (0, s.country_id)
            WHERE cdd.transaction_type_id = 0
                AND cdd.term_type = 'label'

            UNION ALL

            -- Variant 3: wildcard store, exact txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.country_id IN (0, s.country_id)
                    AND cdd.transaction_type_id = s.transaction_type_id
            WHERE cdd.store_id = 0
                AND cdd.term_type = 'label'

            UNION ALL

            -- Variant 4: wildcard store, wildcard txn_type
            SELECT s.txn_id, cdd.*
            FROM sales AS s
                INNER JOIN cdd
                    ON cdd.contract_id = s.contract_id
                    AND cdd.label_id   = s.label_id
                    AND cdd.country_id IN (0, s.country_id)
            WHERE cdd.store_id = 0
                AND cdd.transaction_type_id = 0
                AND cdd.term_type = 'label'
        ),

        label_match AS (
            SELECT
                v.txn_id,
                v.accounting_run_id,
                v.contract_id,
                v.contract_term_id,
                v.contract_term_condition_id,
                v.account_id,
                v.account_currency_code,
                ROW_NUMBER() OVER (
                    PARTITION BY v.txn_id, v.label_id
                    ORDER BY v.priority ASC, v.term_rate DESC, v.contract_term_condition_id ASC
                ) AS ranking
            FROM label_variants AS v
            WHERE NOT EXISTS (SELECT 1 FROM track_match t WHERE t.txn_id = v.txn_id)
                AND NOT EXISTS (SELECT 1 FROM product_match p WHERE p.txn_id = v.txn_id)
            QUALIFY ranking = 1
        ),

        -- Results
        matches AS (
            SELECT * FROM track_match
            UNION ALL
            SELECT * FROM product_match
            UNION ALL
            SELECT * FROM label_match
        )

        SELECT
            m.txn_id,
            m.contract_id,
            m.account_id,
            cm.iso_number AS payee_currency_id,
            m.accounting_run_id,
            m.contract_term_id,
            m.contract_term_condition_id
        FROM matches AS m
        JOIN ROYALTY_ACCOUNTING.test.CURRENCY_MAP AS cm
        ON m.account_currency_code = cm.iso_code;
    """