"""Tests for the snowflake connector."""

from typing import Generator
from unittest.mock import MagicMock
from unittest.mock import patch

import pytest

from src.connectors import snowflake


@patch('src.connectors.snowflake.connector')
def test_query(mock_connector):
    """Test making a query."""
    sql = 'SELECT * FROM test'
    params = {'one': 1, 'dos': 2}
    expected_results = [1, 2, 3, 4]
    num_rows = 123

    mock_cursor = MagicMock()

    mock_connector.connect().__enter__().cursor().__enter__.return_value = mock_cursor
    mock_cursor.fetchmany.side_effect = [expected_results, None]
    mock_cursor.rowcount = num_rows

    result = snowflake._query(sql, params)

    assert isinstance(result, Generator)
    assert next(result) == num_rows
    for item in result:  # consume the generator
        assert item in expected_results

    mock_cursor.execute.assert_called_once_with(sql, params)


@patch('src.connectors.snowflake.connector')
def test_query_one(mock_connector):
    """Test making a single-row query."""
    sql = 'SELECT * FROM test WHERE id = %(id)s'
    params = {'id': 123}
    expected_row = {'id': 123, 'name': 'test'}
    mock_cursor = MagicMock()
    mock_connector.connect().__enter__().cursor().__enter__.return_value = mock_cursor
    mock_cursor.fetchone.return_value = expected_row

    result = snowflake._query_one(sql, params)

    assert result == expected_row
    assert not isinstance(result, Generator)
    mock_cursor.execute.assert_called_once_with(sql, params)


@patch('src.connectors.snowflake.connector')
def test_pandas_query(mock_connector):
    """Test making a pandas query."""
    sql = 'SELECT * FROM test'
    params = {'one': 1, 'dos': 2}
    expected_results = [1, 2, 3, 4]
    num_rows = 123

    mock_cursor = MagicMock()

    mock_connector.connect().__enter__().cursor().__enter__.return_value = mock_cursor
    mock_cursor.fetch_pandas_batches.return_value = expected_results
    mock_cursor.rowcount = num_rows

    result_gen, result_rows = snowflake._pandas_query(sql, params)

    assert result_gen == expected_results
    assert result_rows == num_rows

    mock_cursor.execute.assert_called_once_with(sql, params)


@patch('src.connectors.snowflake._pandas_query')
@pytest.mark.parametrize(
    'account_id, contract_id, statement_period_id',
    [
        (
            123,
            1001,
            321,
        ),
        (
            1,
            1,
            1,
        ),
        (
            111,
            222,
            333,
        ),
    ],
)
def test_get_distribution_fact_sales(mock_query, account_id, contract_id, statement_period_id):
    """Test getting distribution fact sales data."""
    num_rows = 123
    iterator = iter([num_rows, 'CORRECT'])
    mock_query.return_value = (iterator, num_rows)
    expected_sql = """
    WITH TRACK_PERFORMERS AS (
        SELECT
            LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK.TRACK_ID,
            LISTAGG(DISTINCT LABEL_PARTICIPANT.NAME, '|') AS PERFORMERS
        FROM FACTS.TEST.LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK
        JOIN FACTS.TEST.LABEL_PARTICIPANT
            ON LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK.LABEL_PARTICIPANT_ID
                = LABEL_PARTICIPANT.ID
        WHERE
            PARTICIPATED_AS = 'performer'
        GROUP BY
            LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK.TRACK_ID
    )
    SELECT
        SP1.STATEMENT_PERIOD_NAME,
        RDD.ACCOUNT_ID,
        A.ACCOUNT_NAME,
        RDD.CONTRACT_ID,
        RDD.TRANSACTION_DATE,
        DC.COUNTRYNAME,
        CMM.CUSTOMER_NAME,
        RDD.SUBDISTRIBUTOR,
        DR.IMPRINT AS LABEL_IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        DR.VERSION AS RELEASE_VERSION,
        DR.PRODUCT_CODE,
        DR.DISPLAY_UPC,
        DR.MANUFACTURER_UPC,
        TP.PERFORMERS,
        DT.TRACKNAME,
        DT.VERSION AS TRACK_VERSION,
        RDD.ISRC,
        RDD.VIDEO_ID,
        DTT.TRANSACTIONTYPEDESC,
        RDD.TRANSACTION_SUBTYPE,
        RDD.UNIT_PRICE_SALE_CURRENCY,
        RDD.QUANTITY,
        RDD.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
        RDD.SALE_CURRENCY_CODE,
        ER.RATE AS EXCHANGE_RATE,
        RDD.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
        RDD.ACCOUNT_PAYEE_CURRENCY,
        RDD.ROYALTY_RATE,
        RDD.NET_SHARE_PAYEE_CURRENCY,
        RDD.PHYS_PPD_USD,
        RDD.MECHANICAL_DEDUCTION_AMOUNT_PAYEE_CURRENCY,
        RDD.PUBLISHER_ADMIN_FEE_PAYEE_CURRENCY,
        RDD.ABACUS_SALE_TYPE,
        SP2.STATEMENT_PERIOD_NAME as ORIGINAL_STATEMENT_PERIOD
    FROM REVENUE_DISTRO_DBT AS RDD
    LEFT JOIN FACTS.TEST.DIM_COUNTRY AS DC
        ON DC.COUNTRYID = RDD.COUNTRY_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
            AS CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = RDD.STORE_ID
    LEFT JOIN FACTS.TEST.DIM_RELEASE AS DR
        ON DR.RELEASEID = RDD.UPC
    LEFT JOIN (
        SELECT DIM_TRACK.* FROM FACTS.TEST.DIM_TRACK
        JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK
        AS T on T.ID = DIM_TRACK.TRACK_UNIQUE_ID
    ) AS DT
        ON DT.UPC = RDD.UPC
        AND DT.ISRC = RDD.ISRC
        AND DT.TRACK_ID = RDD.TRACK_ID
    LEFT JOIN FACTS.TEST.DIM_ARTIST AS DA
        ON DA.ARTISTID = DR.ARTISTID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.EXCHANGE_RATE
            AS ER
        ON RDD.STATEMENT_PERIOD_ID = ER.STATEMENT_PERIOD_ID
        AND RDD.SALE_CURRENCY_CODE = ER.FROM_CURRENCY_CODE
        AND RDD.ACCOUNT_PAYEE_CURRENCY = ER.TO_CURRENCY_CODE
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP1
        ON RDD.STATEMENT_PERIOD_ID = SP1.STATEMENT_PERIOD_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT
            AS A
        ON RDD.ACCOUNT_ID = A.ACCOUNT_ID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE AS DTT
        ON DTT.TRANSACTIONTYPEABBR = RDD.TRANSACTION_TYPE
    LEFT JOIN FACTS.TEST.DIM_LABEL AS DL
        ON DL.LABELID = RDD.LABEL_ID
    LEFT JOIN TRACK_PERFORMERS TP
        ON RDD.TRACK_UNIQUE_ID = TP.TRACK_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP2
        ON RDD.ORIGINAL_STATEMENT_PERIOD_ID = SP2.STATEMENT_PERIOD_ID
    WHERE RDD.STATEMENT_PERIOD_ID = %(statement_period_id)s
    AND RDD.ACCOUNT_ID = %(account_id)s
    AND RDD.CONTRACT_ID = %(contract_id)s
    """
    expected_params = {
        'account_id': account_id,
        'contract_id': contract_id,
        'statement_period_id': statement_period_id,
    }

    result = snowflake.get_distribution_fact_sales(
        statement_period_id, account_id, contract_id, None
    )

    assert result == (iterator, num_rows)

    mock_query.assert_called_once_with(expected_sql, expected_params)


@patch('src.connectors.snowflake._pandas_query')
def test_get_distribution_fact_sales_subaccount(mock_query):
    """Test getting distribution fact sales data with filtering by subaccount ID."""
    account_id = 24601
    subaccount_id = 54321
    statement_period_id = 123
    num_rows = 100
    iterator = iter([num_rows, 'CORRECT'])
    mock_query.return_value = (iterator, num_rows)
    expected_sql = """
    WITH TRACK_PERFORMERS AS (
        SELECT
            LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK.TRACK_ID,
            LISTAGG(DISTINCT LABEL_PARTICIPANT.NAME, '|') AS PERFORMERS
        FROM FACTS.TEST.LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK
        JOIN FACTS.TEST.LABEL_PARTICIPANT
            ON LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK.LABEL_PARTICIPANT_ID
                = LABEL_PARTICIPANT.ID
        WHERE
            PARTICIPATED_AS = 'performer'
        GROUP BY
            LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_TRACK.TRACK_ID
    )
    SELECT
        SP1.STATEMENT_PERIOD_NAME,
        RDD.ACCOUNT_ID,
        A.ACCOUNT_NAME,
        RDD.CONTRACT_ID,
        RDD.TRANSACTION_DATE,
        DC.COUNTRYNAME,
        CMM.CUSTOMER_NAME,
        RDD.SUBDISTRIBUTOR,
        DR.IMPRINT AS LABEL_IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        DR.VERSION AS RELEASE_VERSION,
        DR.PRODUCT_CODE,
        DR.DISPLAY_UPC,
        DR.MANUFACTURER_UPC,
        TP.PERFORMERS,
        DT.TRACKNAME,
        DT.VERSION AS TRACK_VERSION,
        RDD.ISRC,
        RDD.VIDEO_ID,
        DTT.TRANSACTIONTYPEDESC,
        RDD.TRANSACTION_SUBTYPE,
        RDD.UNIT_PRICE_SALE_CURRENCY,
        RDD.QUANTITY,
        RDD.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_SALE_CURRENCY,
        RDD.SALE_CURRENCY_CODE,
        ER.RATE AS EXCHANGE_RATE,
        RDD.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
        RDD.ACCOUNT_PAYEE_CURRENCY,
        RDD.ROYALTY_RATE,
        RDD.NET_SHARE_PAYEE_CURRENCY,
        RDD.PHYS_PPD_USD,
        RDD.MECHANICAL_DEDUCTION_AMOUNT_PAYEE_CURRENCY,
        RDD.PUBLISHER_ADMIN_FEE_PAYEE_CURRENCY,
        RDD.ABACUS_SALE_TYPE,
        SP2.STATEMENT_PERIOD_NAME as ORIGINAL_STATEMENT_PERIOD
    FROM REVENUE_DISTRO_DBT AS RDD
    LEFT JOIN FACTS.TEST.DIM_COUNTRY AS DC
        ON DC.COUNTRYID = RDD.COUNTRY_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
            AS CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = RDD.STORE_ID
    LEFT JOIN FACTS.TEST.DIM_RELEASE AS DR
        ON DR.RELEASEID = RDD.UPC
    LEFT JOIN (
        SELECT DIM_TRACK.* FROM FACTS.TEST.DIM_TRACK
        JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK
        AS T on T.ID = DIM_TRACK.TRACK_UNIQUE_ID
    ) AS DT
        ON DT.UPC = RDD.UPC
        AND DT.ISRC = RDD.ISRC
        AND DT.TRACK_ID = RDD.TRACK_ID
    LEFT JOIN FACTS.TEST.DIM_ARTIST AS DA
        ON DA.ARTISTID = DR.ARTISTID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.EXCHANGE_RATE
            AS ER
        ON RDD.STATEMENT_PERIOD_ID = ER.STATEMENT_PERIOD_ID
        AND RDD.SALE_CURRENCY_CODE = ER.FROM_CURRENCY_CODE
        AND RDD.ACCOUNT_PAYEE_CURRENCY = ER.TO_CURRENCY_CODE
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP1
        ON RDD.STATEMENT_PERIOD_ID = SP1.STATEMENT_PERIOD_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT
            AS A
        ON RDD.ACCOUNT_ID = A.ACCOUNT_ID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE AS DTT
        ON DTT.TRANSACTIONTYPEABBR = RDD.TRANSACTION_TYPE
    LEFT JOIN FACTS.TEST.DIM_LABEL AS DL
        ON DL.LABELID = RDD.LABEL_ID
    LEFT JOIN TRACK_PERFORMERS TP
        ON RDD.TRACK_UNIQUE_ID = TP.TRACK_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP2
        ON RDD.ORIGINAL_STATEMENT_PERIOD_ID = SP2.STATEMENT_PERIOD_ID
    WHERE RDD.STATEMENT_PERIOD_ID = %(statement_period_id)s
    AND RDD.ACCOUNT_ID = %(account_id)s
    AND RDD.SUBACCOUNT_ID = %(subaccount_id)s
    """
    expected_params = {
        'account_id': account_id,
        'subaccount_id': subaccount_id,
        'statement_period_id': statement_period_id,
    }

    result = snowflake.get_distribution_fact_sales(
        statement_period_id, account_id, None, subaccount_id
    )

    assert result == (iterator, num_rows)

    mock_query.assert_called_once_with(expected_sql, expected_params)


@patch('src.connectors.snowflake._query')
@pytest.mark.parametrize(
    'account_id, contract_id, statement_period_id',
    [
        (
            24601,
            20002,
            321,
        ),
        (
            888,
            888,
            888,
        ),
        (
            3,
            2,
            1,
        ),
    ],
)
def test_get_neighbouring_rights_label_fact_sales(
    mock_query, account_id, contract_id, statement_period_id
):
    """Test getting neighbouring rights label fact sales data."""
    num_rows = 123
    iterator = iter([num_rows, 'CORRECT'])
    mock_query.return_value = iterator
    expected_sql = """
    SELECT
        RDD.ACCOUNT_PAYEE_CURRENCY,
        RDD.CONTRACT_ID,
        RDD.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
        RDD.GROSS_REVENUE_PAYEE_CURRENCY,
        RDD.ISRC,
        RDD.NET_SHARE_PAYEE_CURRENCY,
        RDD.ROYALTY_RATE,
        RDD.START_DATE,
        RDD.TRANSACTION_DATE,
        RDD.TRANSACTION_SUBTYPE,
        RDD.WITHHOLDING_TAX_PAYEE_CURRENCY,
        DA.ARTISTNAME,
        DC.COUNTRYNAME,
        DR.IMPRINT,
        CMM.CUSTOMER_NAME,
        DT.TRACKNAME,
        DT.VERSION,
        DTT.TRANSACTIONTYPEDESC,
        SP1.STATEMENT_PERIOD_NAME,
        RDD.ABACUS_SALE_TYPE,
        SP2.STATEMENT_PERIOD_NAME as ORIGINAL_STATEMENT_PERIOD
    FROM REVENUE_DISTRO_DBT AS RDD
    LEFT JOIN FACTS.TEST.DIM_COUNTRY AS DC
        ON DC.COUNTRYID = RDD.COUNTRY_ID
    LEFT JOIN FACTS.TEST.DIM_RELEASE AS DR
        ON DR.RELEASEID = RDD.UPC
    LEFT JOIN FACTS.TEST.DIM_ARTIST AS DA
        ON DA.ARTISTID = DR.ARTISTID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
            AS CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = RDD.STORE_ID
    LEFT JOIN (
        SELECT DIM_TRACK.* FROM FACTS.TEST.DIM_TRACK
        JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.TRACK
        AS T on T.ID = DIM_TRACK.TRACK_UNIQUE_ID
    ) AS DT
        ON DT.UPC = RDD.UPC
        AND DT.ISRC = RDD.ISRC
        AND DT.TRACK_ID = RDD.TRACK_ID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE AS DTT
        ON DTT.TRANSACTIONTYPEABBR = RDD.TRANSACTION_TYPE
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP1
        ON SP1.STATEMENT_PERIOD_ID = RDD.STATEMENT_PERIOD_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP2
        ON RDD.ORIGINAL_STATEMENT_PERIOD_ID = SP2.STATEMENT_PERIOD_ID
    WHERE RDD.STATEMENT_PERIOD_ID = %(statement_period_id)s
    AND RDD.ACCOUNT_ID = %(account_id)s
    AND RDD.CONTRACT_ID = %(contract_id)s
    """
    expected_params = {
        'account_id': account_id,
        'contract_id': contract_id,
        'statement_period_id': statement_period_id,
    }

    result = snowflake.get_neighbouring_rights_label_fact_sales(
        statement_period_id, account_id, contract_id
    )

    assert result == (iterator, num_rows)

    mock_query.assert_called_once_with(expected_sql, expected_params)


@patch('src.connectors.snowflake._query')
@pytest.mark.parametrize(
    'account_id, contract_id, statement_period_id',
    [
        (
            24601,
            10001,
            123,
        ),
        (
            999,
            999,
            999,
        ),
        (
            1,
            2,
            3,
        ),
    ],
)
def test_get_neighbouring_rights_performer_fact_sales(
    mock_query, account_id, contract_id, statement_period_id
):
    """Test getting neighbouring rights performer fact sales data."""
    num_rows = 123
    iterator = iter([num_rows, 'CORRECT'])
    mock_query.return_value = iterator
    expected_sql = """
    SELECT
        RND.ACCOUNT_PAYEE_CURRENCY,
        RND.CONTRACT_ID,
        RND.CONTRIBUTOR_NAME,
        RND.END_DATE,
        RND.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY,
        RND.GROSS_REVENUE_PAYEE_CURRENCY,
        RND.ISRC,
        RND.NET_SHARE_PAYEE_CURRENCY,
        RND.ROYALTY_RATE,
        RND.SOUND_RECORDING_NAME,
        RND.START_DATE,
        RND.TRANSACTION_SUBTYPE_DESCRIPTION,
        RND.WITHHOLDING_TAX_PAYEE_CURRENCY,
        CT.CONTRACT_TERM_NAME,
        CT.TERM_TYPE,
        SCH.SCHEDULE_NAME,
        DC.COUNTRYNAME,
        CMM.CUSTOMER_NAME,
        DTT.TRANSACTIONTYPEDESC,
        SP1.STATEMENT_PERIOD_NAME,
        SR.MAIN_ARTIST,
        SR.VERSION,
        RND.ABACUS_SALE_TYPE,
        SP2.STATEMENT_PERIOD_NAME as ORIGINAL_STATEMENT_PERIOD
    FROM REVENUE_NR_DBT AS RND
    LEFT JOIN CONTRACT_TRANSACTION_NR AS CTNR
        ON RND.CONTRACT_TRANSACTION_NR_CONTRACT_TXN_ID = CTNR.CONTRACT_TXN_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM_CONDITION
            AS CTC
        ON CTC.CONTRACT_TERM_CONDITION_ID = RND.CONTRACT_TERM_CONDITION_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.CONTRACT_TERM
            AS CT
        ON CT.CONTRACT_TERM_ID = CTC.CONTRACT_TERM_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.SCHEDULE
            AS SCH
        ON SCH.SCHEDULE_ID = CTNR.SCHEDULE_ID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY AS DC
        ON DC.COUNTRYID = RND.COUNTRY_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
            AS CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = RND.STORE_ID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE AS DTT
        ON DTT.TRANSACTIONTYPEABBR = RND.TRANSACTION_TYPE
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP1
        ON RND.STATEMENT_PERIOD_ID = SP1.STATEMENT_PERIOD_ID
    LEFT JOIN FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING AS SR
        ON SR.ID = RND.SOUND_RECORDING_ID
    LEFT JOIN
        ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
            AS SP2
        ON RND.ORIGINAL_STATEMENT_PERIOD_ID = SP2.STATEMENT_PERIOD_ID
    WHERE RND.STATEMENT_PERIOD_ID = %(statement_period_id)s
    AND RND.ACCOUNT_ID = %(account_id)s
    AND RND.CONTRACT_ID = %(contract_id)s
    """
    expected_params = {
        'account_id': account_id,
        'contract_id': contract_id,
        'statement_period_id': statement_period_id,
    }

    result = snowflake.get_neighbouring_rights_performer_fact_sales(
        statement_period_id, account_id, contract_id
    )

    assert result == (iterator, num_rows)

    mock_query.assert_called_once_with(expected_sql, expected_params)


@patch('src.connectors.snowflake._query')
@pytest.mark.parametrize(
    'account_id, contract_id, statement_period_id, statement_attachment_type',
    [
        (24601, 10001, 123, 'collection_summary_performer'),
        (999, 999, 999, 'collection_summary_performer'),
        (1, 2, 3, 'collection_summary_performer'),
    ],
)
def test_get_collection_summary_data(
    mock_query, account_id, contract_id, statement_period_id, statement_attachment_type
):
    """Test getting collection summary data."""
    num_rows = 8
    iterator = iter([num_rows, 'CORRECT'])
    mock_query.return_value = iterator
    expected_sql = """
    SELECT
        CUSTOMER_MASTER_MASTER.CUSTOMER_NAME AS COLLECTION_SOCIETY,
        DIM_COUNTRY.COUNTRYNAME AS COUNTRY,
        REVENUE_NR_DBT.ACCOUNT_PAYEE_CURRENCY AS CURRENCY,
        SUM(REVENUE_NR_DBT.GROSS_REVENUE_PAYEE_CURRENCY) AS PRE_WHT_AMOUNT,
        SUM(REVENUE_NR_DBT.WITHHOLDING_TAX_PAYEE_CURRENCY) AS WHT_AMOUNT,
        SUM(REVENUE_NR_DBT.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY)
            AS GROSS_AMOUNT,
        (SELECT REVENUE_NR_DBT.ROYALTY_RATE * 100) AS CLIENT_SHARE,
        SUM(REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS NET_REVENUE
    FROM REVENUE_NR_DBT
    JOIN
        ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
            ON CUSTOMER_MASTER_MASTER.CUSTOMER_MASTER_MASTER_ID
                = REVENUE_NR_DBT.STORE_ID
    JOIN FACTS.TEST.DIM_COUNTRY
            ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    WHERE REVENUE_NR_DBT.STATEMENT_PERIOD_ID = %(statement_period_id)s
    AND REVENUE_NR_DBT.ACCOUNT_ID = %(account_id)s
    AND REVENUE_NR_DBT.CONTRACT_ID = %(contract_id)s
    GROUP BY
        COUNTRY,
        COLLECTION_SOCIETY,
        CURRENCY,
        CLIENT_SHARE
    ORDER BY COLLECTION_SOCIETY, COUNTRY ASC
    """
    expected_params = {
        'account_id': account_id,
        'contract_id': contract_id,
        'statement_period_id': statement_period_id,
    }

    result = snowflake.get_collection_summary_data(
        statement_period_id, account_id, contract_id, statement_attachment_type
    )

    assert result == (iterator, num_rows)

    mock_query.assert_called_once_with(expected_sql, expected_params)


@patch('src.connectors.snowflake._query')
@pytest.mark.parametrize(
    'account_id, statement_period_id',
    [
        (202402, 1001),
        (202306, 123),
        (202501, 789),
    ],
)
def test_get_workstation_fact_sales(mock_query, statement_period_id, account_id):
    """Test fetching workstation fact sales with expected SQL."""
    num_rows = 3
    iterator = iter([num_rows, {'some': 'data'}])
    mock_query.return_value = iterator
    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.MANUFACTURER_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 THEN 'FULL ALBUM'
                ELSE DT.TRACKNAME
            END,
            ''
        ) AS TRACKNAME,
        (
            SELECT
                LISTAGG(NAME, '|') WITHIN GROUP(ORDER BY NAME)
            FROM FACTS.PROD.TRACK_ARTIST TA
            WHERE TA.TRACK_ID = DT.TRACK_UNIQUE_ID
              AND TYPE = 'performer'
        ) AS TRACKARTIST,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 THEN AS_VARCHAR(DR.RELEASEID)
                ELSE DT.ISRC
            END,
            ''
        ) AS ISRC,
        COALESCE(DT.CD, 0) AS CD,
        COALESCE(DT.TRACK_ID, 0) AS TRACK_ID,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_RINGTONE_PUBLISHING, 0.0) AS FX_RINGTONE_PUBLISHING,
        COALESCE(WFS.FX_CLOUD_PUBLISHING, 0.0) AS FX_CLOUD_PUBLISHING,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRACK_CLEAN_MV DT
        ON DT.TRACKID = WFS.TRACKID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID = %(accountingperiodid)s
      AND WFS.LABELID = %(labelid)s
    """

    expected_params = {'accountingperiodid': statement_period_id, 'labelid': account_id}

    result = snowflake.get_workstation_fact_sales(statement_period_id, account_id, None)

    assert result == (iterator, num_rows)

    actual_sql, actual_params = mock_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._query')
def test_get_workstation_fact_sales_subaccount(mock_query):
    """Test fetching workstation fact sales with subaccount filter."""
    account_id = 24601
    statement_period_id = 123
    subaccount_id = 54321
    num_rows = 3
    iterator = iter([num_rows, {'some': 'data'}])
    mock_query.return_value = iterator
    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.MANUFACTURER_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 THEN 'FULL ALBUM'
                ELSE DT.TRACKNAME
            END,
            ''
        ) AS TRACKNAME,
        (
            SELECT
                LISTAGG(NAME, '|') WITHIN GROUP(ORDER BY NAME)
            FROM FACTS.PROD.TRACK_ARTIST TA
            WHERE TA.TRACK_ID = DT.TRACK_UNIQUE_ID
              AND TYPE = 'performer'
        ) AS TRACKARTIST,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 THEN AS_VARCHAR(DR.RELEASEID)
                ELSE DT.ISRC
            END,
            ''
        ) AS ISRC,
        COALESCE(DT.CD, 0) AS CD,
        COALESCE(DT.TRACK_ID, 0) AS TRACK_ID,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_RINGTONE_PUBLISHING, 0.0) AS FX_RINGTONE_PUBLISHING,
        COALESCE(WFS.FX_CLOUD_PUBLISHING, 0.0) AS FX_CLOUD_PUBLISHING,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRACK_CLEAN_MV DT
        ON DT.TRACKID = WFS.TRACKID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID = %(accountingperiodid)s
      AND WFS.LABELID = %(labelid)s
      AND WFS.SUBACCOUNTID = %(subaccountid)s
    """

    expected_params = {
        'accountingperiodid': statement_period_id,
        'labelid': account_id,
        'subaccountid': subaccount_id,
    }

    result = snowflake.get_workstation_fact_sales(statement_period_id, account_id, subaccount_id)

    assert result == (iterator, num_rows)

    actual_sql, actual_params = mock_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._pandas_query')
@pytest.mark.parametrize(
    'account_id, statement_period_id',
    [
        (202402, (1001)),
        (202306, (123)),
        (202501, (789, 987)),
    ],
)
def test_get_workstation_fact_sales_pandas(mock_pandas_query, statement_period_id, account_id):
    """Test fetching workstation fact sales with expected SQL (using Pandas)."""
    num_rows = 3
    results = [1, 2, 3]
    mock_pandas_query.return_value = (results, num_rows)
    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.MANUFACTURER_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 AND DT.TRACK_UNIQUE_ID = 0
                THEN 'Full Album'
                ELSE DT.TRACKNAME
            END,
            ''
        ) AS TRACKNAME,
        (
            SELECT
                LISTAGG(NAME, '|') WITHIN GROUP(ORDER BY NAME)
            FROM FACTS.PROD.TRACK_ARTIST TA
            WHERE TA.TRACK_ID = DT.TRACK_UNIQUE_ID
              AND TYPE = 'performer'
        ) AS TRACKARTIST,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 AND DT.TRACK_UNIQUE_ID = 0
                THEN CAST(DR.RELEASEID as VARCHAR)
                ELSE DT.ISRC
            END,
            ''
        ) AS ISRC,
        COALESCE(DT.CD, 0) AS CD,
        COALESCE(DT.TRACK_ID, 0) AS TRACK_ID,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_RINGTONE_PUBLISHING, 0.0) AS FX_RINGTONE_PUBLISHING,
        COALESCE(WFS.FX_CLOUD_PUBLISHING, 0.0) AS FX_CLOUD_PUBLISHING,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRACK DT
        ON DT.TRACKID = WFS.TRACKID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID IN (%(accountingperiod_ids)s)
      AND WFS.LABELID = %(labelid)s
    """
    expected_params = {'accountingperiod_ids': statement_period_id, 'labelid': account_id}

    result = snowflake.get_workstation_fact_sales_pandas(statement_period_id, account_id, None)

    assert result == (results, num_rows)
    actual_sql, actual_params = mock_pandas_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._pandas_query')
def test_get_workstation_fact_sales_pandas_subaccount(mock_pandas_query):
    """Test fetching workstation fact sales with subaccount filter (using Pandas)."""
    account_id = 24601
    statement_period_ids = 123
    subaccount_id = 54321
    num_rows = 3
    results = [1, 2, 3]
    mock_pandas_query.return_value = (results, num_rows)
    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.MANUFACTURER_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 AND DT.TRACK_UNIQUE_ID = 0
                THEN 'Full Album'
                ELSE DT.TRACKNAME
            END,
            ''
        ) AS TRACKNAME,
        (
            SELECT
                LISTAGG(NAME, '|') WITHIN GROUP(ORDER BY NAME)
            FROM FACTS.PROD.TRACK_ARTIST TA
            WHERE TA.TRACK_ID = DT.TRACK_UNIQUE_ID
              AND TYPE = 'performer'
        ) AS TRACKARTIST,
        COALESCE(
            CASE
                WHEN DT.CD = 0 AND DT.TRACK_ID = 0 AND DT.TRACK_UNIQUE_ID = 0
                THEN CAST(DR.RELEASEID as VARCHAR)
                ELSE DT.ISRC
            END,
            ''
        ) AS ISRC,
        COALESCE(DT.CD, 0) AS CD,
        COALESCE(DT.TRACK_ID, 0) AS TRACK_ID,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_RINGTONE_PUBLISHING, 0.0) AS FX_RINGTONE_PUBLISHING,
        COALESCE(WFS.FX_CLOUD_PUBLISHING, 0.0) AS FX_CLOUD_PUBLISHING,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRACK DT
        ON DT.TRACKID = WFS.TRACKID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID IN (%(accountingperiod_ids)s)
      AND WFS.LABELID = %(labelid)s
      AND WFS.SUBACCOUNTID = %(subaccountid)s
    """
    expected_params = {
        'accountingperiod_ids': statement_period_ids,
        'labelid': account_id,
        'subaccountid': subaccount_id,
    }

    result = snowflake.get_workstation_fact_sales_pandas(
        statement_period_ids, account_id, subaccount_id
    )

    assert result == (results, num_rows)
    actual_sql, actual_params = mock_pandas_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._query')
@pytest.mark.parametrize(
    'account_id, statement_period_id',
    [
        (202402, 1001),
        (202306, 123),
        (202501, 789),
    ],
)
def test_get_workstation_physical_sales(mock_query, statement_period_id, account_id):
    """Test fetching workstation physical sales with expected SQL."""
    num_rows = 3
    iterator = iter([num_rows, {'some': 'physical data'}])
    mock_query.return_value = iterator

    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        DR.PHYSICAL_PRODUCT_TYPE,
        DR.PHYSICAL_PRODUCT_FORMAT,
        DR.DISPLAY_CONFIGURATION,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID = %(accountingperiodid)s
      AND WFS.LABELID = %(labelid)s
      AND DTT.TRANSACTIONTYPEABBR IN (
        'PS', 'PH', 'OP', 'LP', 'MP', 'TP', 'RE',
        'OR', 'LR', 'MR', 'TR'
      )
    """

    expected_params = {'accountingperiodid': statement_period_id, 'labelid': account_id}

    result = snowflake.get_workstation_physical_sales(statement_period_id, account_id, None)

    assert result == (iterator, num_rows)

    actual_sql, actual_params = mock_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._pandas_query')
@pytest.mark.parametrize(
    'account_id, statement_period_ids',
    [
        (202402, (1001)),
        (202306, (123, 456)),
        (202501, (789)),
    ],
)
def test_get_workstation_physical_sales_pandas(mock_query, statement_period_ids, account_id):
    """Test fetching workstation physical sales pandas with expected SQL."""
    num_rows = 3
    iterator = iter([num_rows, {'some': 'physical data'}])
    mock_query.return_value = (iterator, num_rows)
    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        DR.PHYSICAL_PRODUCT_TYPE,
        DR.PHYSICAL_PRODUCT_FORMAT,
        DR.DISPLAY_CONFIGURATION,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID IN (%(accountingperiod_ids)s)
      AND WFS.LABELID = %(labelid)s
      AND DTT.TRANSACTIONTYPEABBR IN (
        'PS', 'PH', 'OP', 'LP', 'MP', 'TP', 'RE',
        'OR', 'LR', 'MR', 'TR'
      )
    """

    expected_params = {'accountingperiod_ids': statement_period_ids, 'labelid': account_id}

    result = snowflake.get_workstation_physical_sales_pandas(statement_period_ids, account_id, None)

    assert result == (iterator, num_rows)
    actual_sql, actual_params = mock_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._query')
def test_get_workstation_physical_sales_subaccount(mock_query):
    """Test fetching workstation physical sales for a subaccount."""
    account_id = 24601
    statement_period_id = 123
    subaccount_id = 54321
    num_rows = 3
    iterator = iter([num_rows, {'some': 'physical data'}])
    mock_query.return_value = iterator

    expected_sql = """
    SELECT
        CONCAT(WFS.ACCOUNTINGYEAR, 'M', WFS.ACCOUNTINGMONTH) AS PERIOD,
        CONCAT(WFS.ACTIVITYYEAR, 'M', WFS.ACTIVITYMONTH) AS ACTIVITYPERIOD,
        TRIM(CMM.CUSTOMER_NAME) AS CUSTOMER_NAME,
        DC.COUNTRYNAME,
        DR.DISPLAY_UPC,
        DR.VENDOR_CATALOG_NUMBER,
        DR.PRODUCT_CODE,
        SA.SUBACCOUNT_NAME,
        IM.IMPRINT,
        DA.ARTISTNAME,
        DR.RELEASENAME,
        DR.PHYSICAL_PRODUCT_TYPE,
        DR.PHYSICAL_PRODUCT_FORMAT,
        DR.DISPLAY_CONFIGURATION,
        DTT.TRANSACTIONTYPEABBR,
        DTT.TRANSACTIONTYPEDESC,
        WFS.ORIGINAL_PRICE,
        WFS.DISCOUNT,
        CASE
            WHEN WFS.SALES <> 0 THEN (WFS.FX_GROSS / WFS.SALES)
            ELSE 0
        END AS ACTUAL_PRICE,
        CAST(COALESCE(WFS.SALES, 0) AS INT) AS SALES,
        WFS.FX_GROSS,
        WFS.FX_ADJUSTED_GROSS,
        CASE
            WHEN WFS.FX_ADJUSTED_GROSS <> 0
            THEN (WFS.FX_NET_RECEIPT::FLOAT / WFS.FX_ADJUSTED_GROSS::FLOAT)::DECIMAL(38, 19)
            ELSE 0
        END AS SPLITRATE,
        WFS.FX_NET_RECEIPT,
        COALESCE(WFS.FX_DPD_PUBLISHING, 0.0) AS FX_DPD_PUBLISHING,
        COALESCE(WFS.FX_OMS_FEES, 0.0) AS FX_OMS_FEES,
        DIMC.CURRENCY_CODE
    FROM WORKSTATION_FACT_SALES_UNIFIED_DBT WFS
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER CMM
        ON CMM.CUSTOMER_MASTER_MASTER_ID = WFS.STOREID
    LEFT JOIN FACTS.TEST.DIM_COUNTRY DC
        ON DC.COUNTRYID = WFS.COUNTRYID
    LEFT JOIN FACTS.TEST.DIM_RELEASE DR
        ON DR.RELEASEID = WFS.RELEASEID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT SA
        ON SA.SUBACCOUNT_ID = WFS.SUBACCOUNTID
    LEFT JOIN FACTS.TEST.DIM_IMPRINT IM
        ON IM.IMPRINTID = WFS.IMPRINTID
    LEFT JOIN FACTS.TEST.DIM_ARTIST DA
        ON DA.ARTISTID = WFS.ARTISTID
    LEFT JOIN FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        ON DTT.TRANSACTIONTYPEID = WFS.TRANSACTIONTYPEID
    LEFT JOIN FACTS.TEST.DIM_CURRENCY DIMC
        ON WFS.PAYOUT_CURRENCY_ID = DIMC.CURRENCYID
    WHERE WFS.ACCOUNTINGPERIODID = %(accountingperiodid)s
      AND WFS.LABELID = %(labelid)s
      AND DTT.TRANSACTIONTYPEABBR IN (
        'PS', 'PH', 'OP', 'LP', 'MP', 'TP', 'RE',
        'OR', 'LR', 'MR', 'TR'
      )
      AND WFS.SUBACCOUNTID = %(subaccountid)s
    """

    expected_params = {
        'accountingperiodid': statement_period_id,
        'labelid': account_id,
        'subaccountid': subaccount_id,
    }

    result = snowflake.get_workstation_physical_sales(
        statement_period_id, account_id, subaccount_id
    )

    assert result == (iterator, num_rows)

    actual_sql, actual_params = mock_query.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._query_one')
def test_get_subaccount(mock_query_one):
    """Test getting the subaccount."""
    subaccount_id = 54321
    expected_row = {'subaccountname': 'party time'}
    expected_sql = """
    SELECT
        SUBACCOUNTNAME,
        COMMISSIONOVERRIDE,
        SUBACCOUNT_SPLIT_TYPE
    FROM FACTS.TEST.DIM_SUBACCOUNT
    WHERE SUBACCOUNTID = %(subaccount_id)s
    """
    expected_params = {'subaccount_id': subaccount_id}

    mock_query_one.return_value = expected_row

    result = snowflake.get_subaccount(subaccount_id)

    assert result == expected_row
    actual_sql, actual_params = mock_query_one.call_args[0]
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._query')
def test_get_transaction_types_names(mock_query):
    """Test getting transaction type names by IDs."""
    transaction_type_ids = [1, 2, 3]
    expected_rows = [
        {'TRANSACTIONTYPEDESC': 'Download'},
        {'TRANSACTIONTYPEDESC': 'Stream'},
        {'TRANSACTIONTYPEDESC': 'Ad-Supported Stream'},
    ]
    expected_sql = """
        SELECT TRANSACTIONTYPEDESC
        FROM FACTS.TEST.DIM_TRANSACTIONTYPE DTT
        WHERE TRANSACTIONTYPEID IN (%(transaction_type_ids)s)
    """
    expected_params = {'transaction_type_ids': transaction_type_ids}

    mock_query.return_value = iter([3] + expected_rows)

    result = snowflake.get_transaction_types_names(transaction_type_ids)
    actual_sql, actual_params = mock_query.call_args[0]

    assert result == ['Download', 'Stream', 'Ad-Supported Stream']
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


@patch('src.connectors.snowflake._query_one')
def test_get_statement_periods_name(mock_query_one):
    """Test getting statement period name."""
    statement_period_id = 123
    expected_row = {'STATEMENT_PERIOD_NAME': 'December 2018'}
    expected_sql = """
    SELECT STATEMENT_PERIOD_NAME
        FROM ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
    WHERE STATEMENT_PERIOD_ID = %(statement_period_id)s
    """
    expected_params = {'statement_period_id': statement_period_id}

    mock_query_one.return_value = expected_row

    result = snowflake.get_statement_periods_name(statement_period_id)
    actual_sql, actual_params = mock_query_one.call_args[0]

    assert result == expected_row
    assert actual_sql.strip() == expected_sql.strip()
    assert actual_params == expected_params


def test_build_transaction_type_filters_with_include_and_exclude():
    """Test building filters with both include and exclude filters."""
    filters = {'transaction_type_ids': [1, 2], 'exclude_transaction_type_ids': [5, 6]}
    params = {}

    result = snowflake._build_transaction_type_filters(filters, params)

    assert 'AND DTT.TRANSACTIONTYPEID IN (%(transaction_type_ids)s)' in result
    assert 'AND DTT.TRANSACTIONTYPEID NOT IN (%(exclude_transaction_type_ids)s)' in result
    assert params['transaction_type_ids'] == [1, 2]
    assert params['exclude_transaction_type_ids'] == [5, 6]


@patch('src.connectors.snowflake._pandas_query')
def test_get_workstation_fact_sales_pandas_with_filters(mock_pandas_query):
    """Test fetching workstation fact sales with transaction type filters."""
    account_id = 24601
    statement_period_ids = (123,)
    filters = {'transaction_type_ids': [1, 2, 3], 'variant': 'digital'}
    num_rows = 5
    results = [1, 2, 3, 4, 5]
    mock_pandas_query.return_value = (results, num_rows)

    result_gen, result_rows = snowflake.get_workstation_fact_sales_pandas(
        statement_period_ids, account_id, None, filters
    )
    actual_sql, actual_params = mock_pandas_query.call_args[0]

    assert result_gen == results
    assert result_rows == num_rows
    assert 'AND DTT.TRANSACTIONTYPEID IN (%(transaction_type_ids)s)' in actual_sql
    assert actual_params['transaction_type_ids'] == [1, 2, 3]


@patch('src.connectors.snowflake._pandas_query')
def test_get_workstation_physical_sales_pandas_with_filters(mock_pandas_query):
    """Test fetching workstation physical sales with exclude filters."""
    account_id = 24601
    statement_period_ids = (123,)
    filters = {'exclude_transaction_type_ids': [10, 11, 12]}
    num_rows = 3
    results = [1, 2, 3]
    mock_pandas_query.return_value = (results, num_rows)

    result_gen, result_rows = snowflake.get_workstation_physical_sales_pandas(
        statement_period_ids, account_id, None, filters
    )
    actual_sql, actual_params = mock_pandas_query.call_args[0]

    assert result_gen == results
    assert result_rows == num_rows
    assert 'AND DTT.TRANSACTIONTYPEID NOT IN (%(exclude_transaction_type_ids)s)' in actual_sql
    assert actual_params['exclude_transaction_type_ids'] == [10, 11, 12]