"""Unit tests for flattening and inserting a snapshot of contract_term_condition.conditions JSON."""

from templates.accounting_run_calculate \
    import snowflake_snapshot_flat_contract_term_conditions as templates


def test_snapshot_flat_contract_term_condition_countries_template():
    """Test that the template renders the flattening and inserting of contract_term_condition.conditions['countries']."""
    template = templates.snapshot_flat_contract_term_condition_countries_template().render(
        accounting_run_id=123,
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_TERM_CONDITION_COUNTRY(
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_CONDITION_ID,
            COUNTRY_CODE,
            COUNTRY_ID
        ) SELECT DISTINCT
            c.accounting_run_id,
            c.contract_term_condition_id,
            LISTAGG(IFNULL(cn.value, 'WW'), ',') AS country_code, -- 'WW' is 'World Wide' AKA 'ALL'
            CASE
                WHEN cn.value IN ('BES', 'CUW', 'SXM') THEN 196
                WHEN cn.value IN ('BLM', 'MAF') THEN 6
                WHEN cn.value IN ('MNE', 'SRB') THEN 190
                WHEN cn.value = 'SSD' THEN 165
                WHEN cn.value = 'SUR' THEN 166
                WHEN STARTSWITH(cn.value, 'X') OR cn.value = 'TBD' THEN -1
                ELSE IFNULL(cy.id, 0)
            END AS country_id
        FROM ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN (LATERAL FLATTEN (
                input => PARSE_JSON(c.conditions),
                path => 'countries',
                outer => true
            )) AS cn
            LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.country AS cy
                ON cy.iso3166a3 = cn.value
        WHERE c.accounting_run_id = 123
        GROUP BY
            accounting_run_id,
            contract_term_condition_id,
            country_id;
    """


def test_snapshot_contract_term_condition_stores_template():
    """Test that the template renders the flattening and inserting of contract_term_condition.conditions['stores']."""
    template = templates.snapshot_flat_contract_term_condition_stores_template().render(
        accounting_run_id=123,
        schema='test'
    )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_TERM_CONDITION_STORE(
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_CONDITION_ID,
            STORE_ID
        ) SELECT DISTINCT
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            IFNULL(s.value, 0) AS store_id
        FROM ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN (LATERAL FLATTEN (
                input => PARSE_JSON(c.conditions),
                path => 'stores',
                outer => true
            )) AS s
        WHERE c.ACCOUNTING_RUN_ID = 123;
    """


def test_snapshot_contract_term_condition_transaction_types_template():
    """Test that the template renders the flattening and inserting of contract_term_condition.conditions['transaction_types']."""
    template = templates\
        .snapshot_flat_contract_term_condition_transaction_types_template() \
        .render(
            accounting_run_id=123,
            schema='test'
        )

    assert template == """
        INSERT INTO ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_TERM_CONDITION_TRANSACTION_TYPE(
            ACCOUNTING_RUN_ID,
            CONTRACT_TERM_CONDITION_ID,
            TRANSACTION_TYPE_ID
        ) SELECT DISTINCT
            c.ACCOUNTING_RUN_ID,
            c.CONTRACT_TERM_CONDITION_ID,
            IFNULL(tt.value, 0) AS transaction_type_id
        FROM ROYALTY_ACCOUNTING.test.SNAPSHOT_CONTRACT_NR AS c
            INNER JOIN (LATERAL FLATTEN (
                input => PARSE_JSON(c.conditions),
                path => 'transaction_types',
                outer => true
            )) AS tt
        WHERE c.ACCOUNTING_RUN_ID = 123;
    """