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

from collections.abc import Generator
from decimal import Decimal
import re
from unittest.mock import MagicMock
from unittest.mock import patch

from numpy import nan as NaN
import pandas
import pytest

import config
from config import RevenueDisplayType
from src.connectors import snowflake

REGEX_WHITESPACE = re.compile(r'\s')


def test_build_report_distro_query_territory_product():
    """Test building the distribution report query for territory/product."""
    column_dimension = 'territory'
    row_dimension = 'product'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        DIM_RELEASE.DISPLAY_UPC AS ROW_DIMENSION,
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_territory_transaction_type():
    """Test building the distribution report query for territory/transaction_type."""
    column_dimension = 'territory'
    row_dimension = 'transaction_type'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        DIM_TRANSACTIONTYPE.TRANSACTIONTYPEDESC AS ROW_DIMENSION,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    JOIN FACTS.TEST.DIM_TRANSACTIONTYPE
        ON DIM_TRANSACTIONTYPE.TRANSACTIONTYPEABBR = REVENUE_DISTRO_DBT.TRANSACTION_TYPE
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_territory_service():
    """Test building the distribution report query for territory/service."""
    column_dimension = 'territory'
    row_dimension = 'service'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        CUSTOMER_MASTER_MASTER.CUSTOMER_NAME AS ROW_DIMENSION,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
        ON CUSTOMER_MASTER_MASTER.CUSTOMER_MASTER_MASTER_ID =
            REVENUE_DISTRO_DBT.STORE_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_product():
    """Test building the distribution report query for statement period/product."""
    column_dimension = 'statement_period'
    row_dimension = 'product'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_RELEASE.DISPLAY_UPC AS ROW_DIMENSION,
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """
    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_subaccount():
    """Test building the distribution report query for statement period/subaccount."""
    column_dimension = 'statement_period'
    row_dimension = 'subaccount'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        SUBACCOUNT.SUBACCOUNT_NAME AS ROW_DIMENSION,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """
    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_project():
    """Test building the distribution report query for statement period/project."""
    column_dimension = 'statement_period'
    row_dimension = 'project'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        PROJECT.PROJECT_NAME AS ROW_DIMENSION,
        PROJECT.PROJECT_CODE,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        PROJECT.PROJECT_CODE,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """
    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_track():
    """Test building the distribution report query for statement period/track."""
    column_dimension = 'statement_period'
    row_dimension = 'track'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_TRACK.TRACKNAME AS ROW_DIMENSION,
        DIM_TRACK.VERSION,
        DIM_ARTIST.ARTISTNAME,
        DIM_RELEASE.RELEASENAME,
        DIM_IMPRINT.IMPRINT,
        DIM_RELEASE.DISPLAY_UPC,
        REVENUE_DISTRO_DBT.ISRC,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_TRACK
        ON DIM_TRACK.UPC = REVENUE_DISTRO_DBT.UPC
        AND DIM_TRACK.ISRC = REVENUE_DISTRO_DBT.ISRC
        AND DIM_TRACK.TRACK_ID = REVENUE_DISTRO_DBT.TRACK_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN FACTS.TEST.DIM_IMPRINT
        ON DIM_RELEASE.IMPRINTID = DIM_IMPRINT.IMPRINTID
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        DIM_TRACK.VERSION,
        DIM_ARTIST.ARTISTNAME,
        DIM_RELEASE.RELEASENAME,
        DIM_IMPRINT.IMPRINT,
        DIM_RELEASE.DISPLAY_UPC,
        REVENUE_DISTRO_DBT.ISRC,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """
    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_territory_imprint():
    """Test building the distribution report query for territory/imprint."""
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        DIM_IMPRINT.IMPRINT AS ROW_DIMENSION,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN FACTS.TEST.DIM_IMPRINT
        ON DIM_RELEASE.IMPRINTID = DIM_IMPRINT.IMPRINTID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION['territory'],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION['imprint'],
        ['account_id', 'statement_period_ids'],
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_product_artist():
    """Test building the distribution report query.

    for statement period/product artist
    """
    column_dimension = 'statement_period'
    row_dimension = 'product_artist'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_ARTIST.ARTISTNAME AS ROW_DIMENSION,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = DIM_RELEASE.ARTISTID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_territory_product_artist():
    """Test building the distribution report query.

    for statement period/product artist
    """
    column_dimension = 'territory'
    row_dimension = 'product_artist'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        DIM_ARTIST.ARTISTNAME AS ROW_DIMENSION,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = DIM_RELEASE.ARTISTID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_recording():
    """Test building the distribution report query.

    for statement period/recording
    """
    column_dimension = 'statement_period'
    row_dimension = 'recording'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        REVENUE_DISTRO_DBT.ISRC AS ROW_DIMENSION,
        DIM_TRACK.TRACKNAME,
        DIM_TRACK.VERSION,
        DIM_ARTIST.ARTISTNAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_TRACK
        ON DIM_TRACK.TRACK_UNIQUE_ID = REVENUE_DISTRO_DBT.TRACK_UNIQUE_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        DIM_TRACK.TRACKNAME,
        DIM_TRACK.VERSION,
        DIM_ARTIST.ARTISTNAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_statement_period_collection_society():
    """Test building the distribution report query.

    for statement period/collection_society
    """
    column_dimension = 'statement_period'
    row_dimension = 'collection_society'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        CUSTOMER_MASTER_MASTER.CUSTOMER_NAME AS ROW_DIMENSION,
        DIM_COUNTRY.COUNTRYNAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.CUSTOMER_MASTER_MASTER
        ON CUSTOMER_MASTER_MASTER.CUSTOMER_MASTER_MASTER_ID
            = REVENUE_DISTRO_DBT.STORE_ID
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        DIM_COUNTRY.COUNTRYNAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_statement_period_territory():
    """Test building the neighbouring rights report.

    query for territory/statement_period.
    """
    column_dimension = 'statement_period'
    row_dimension = 'territory'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_COUNTRY.COUNTRYNAME AS ROW_DIMENSION,
        REVENUE_NR_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_NR_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_NR_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_NR_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    WHERE
        REVENUE_NR_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_NR_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_statement_period_recording():
    """Test building the neighbouring rights report.

    query for recording/statement_period.
    """
    column_dimension = 'statement_period'
    row_dimension = 'recording'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        REVENUE_NR_DBT.SOUND_RECORDING_ID AS ROW_DIMENSION,
        REVENUE_NR_DBT.SOUND_RECORDING_NAME,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.VERSION,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.MAIN_ARTIST,
        REVENUE_NR_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_NR_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_NR_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_NR_DBT.STATEMENT_PERIOD_ID
    LEFT JOIN FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING
        ON PERFORMANCE_NR_SOUND_RECORDING.ID = REVENUE_NR_DBT.SOUND_RECORDING_ID
    WHERE
        REVENUE_NR_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_NR_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        REVENUE_NR_DBT.SOUND_RECORDING_NAME,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.VERSION,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.MAIN_ARTIST,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_territory_recording():
    """Test building the neighbouring rights report query for recording/territory."""
    column_dimension = 'territory'
    row_dimension = 'recording'
    filters = ['account_id', 'statement_period_ids']
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        REVENUE_NR_DBT.SOUND_RECORDING_ID AS ROW_DIMENSION,
        REVENUE_NR_DBT.SOUND_RECORDING_NAME,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.VERSION,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.MAIN_ARTIST,
        REVENUE_NR_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_NR_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_NR_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    LEFT JOIN FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING
        ON PERFORMANCE_NR_SOUND_RECORDING.ID = REVENUE_NR_DBT.SOUND_RECORDING_ID
    WHERE
        REVENUE_NR_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_NR_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        REVENUE_NR_DBT.SOUND_RECORDING_NAME,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.VERSION,
       FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.MAIN_ARTIST,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_contract_id_filter():
    """Test building the distribution report query while filtering with contract ID."""
    column_dimension = 'statement_period'
    row_dimension = 'product'
    filters = ['contract_id']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_RELEASE.DISPLAY_UPC AS ROW_DIMENSION,
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.CONTRACT_ID = %(contract_id)s
    GROUP BY
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_contract_id_filter():
    """Test building the neighbouring rights report.

    query while filtering with contract ID.
    """
    column_dimension = 'territory'
    row_dimension = 'statement_period'
    filters = ['contract_id']
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS ROW_DIMENSION,
        REVENUE_NR_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_NR_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_NR_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_NR_DBT.STATEMENT_PERIOD_ID
    WHERE
         REVENUE_NR_DBT.CONTRACT_ID = %(contract_id)s
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_no_filter():
    """Test building the distribution report query with #nofilter."""
    column_dimension = 'statement_period'
    row_dimension = 'product'
    filters = []
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_RELEASE.DISPLAY_UPC AS ROW_DIMENSION,
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    GROUP BY
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_no_filter():
    """Test building the neighbouring rights report query with #nofilter."""
    column_dimension = 'territory'
    row_dimension = 'statement_period'
    filters = []
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS ROW_DIMENSION,
        REVENUE_NR_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        SUM(REVENUE_NR_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_NR_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_NR_DBT.STATEMENT_PERIOD_ID
    GROUP BY
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


@pytest.mark.parametrize('filter_name', ['doesnotexistlol', 'whatisthis'])
def test_build_report_distro_query_unhandled_filter(filter_name):
    """Test building the distribution report query with an unhandled filter."""
    column_dimension = 'statement_period'
    row_dimension = 'product'
    expected = f'Unhandled query filter: {filter_name}'

    with pytest.raises(Exception) as exception_info:
        snowflake._build_report_query(
            config.REPORT_TABLE_DISTRIBUTION,
            config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
            config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
            [filter_name],
            RevenueDisplayType.NET,
        )

    assert str(exception_info.value) == expected


@pytest.mark.parametrize('filter_name', ['doesnotexistlol', 'whatisthis'])
def test_build_report_nr_query_unhandled_filter(filter_name):
    """Test building the neighbouring rights report query with an unhandled filter."""
    column_dimension = 'statement_period'
    row_dimension = 'territory'
    expected = f'Unhandled query filter: {filter_name}'

    with pytest.raises(Exception) as exception_info:
        snowflake._build_report_query(
            config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
            config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
            config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
            [filter_name],
            RevenueDisplayType.NET,
        )

    assert str(exception_info.value) == expected


@pytest.mark.parametrize(
    ('split_type', 'column'),
    [('Net', config.NET_REVENUE_COLUMN), ('Gross', config.GROSS_REVENUE_COLUMN)],
)
@patch('src.connectors.snowflake.get_subaccount_info')
def test_build_report_distro_query_with_subaccount(mock_get_subaccount_info, split_type, column):
    """Test building the distribution report query while filtering with subaccount ID."""
    column_dimension = 'statement_period'
    row_dimension = 'product'
    subaccount_id = 5
    mock_get_subaccount_info.return_value = {
        'COMMISSIONOVERRIDE': 0.8,
        'SUBACCOUNT_SPLIT_TYPE': split_type,
    }
    filters = ['subaccount_id']
    expected = f"""
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        DIM_RELEASE.DISPLAY_UPC AS ROW_DIMENSION,
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{column} * 0.8) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID = REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.SUBACCOUNT_ID = %(subaccount_id)s
    GROUP BY
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
        subaccount_id,
    )
    mock_get_subaccount_info.assert_called_with(subaccount_id)
    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


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

    mock_cursor = MagicMock()

    mock_connector.connect().__enter__().cursor().__enter__.return_value = mock_cursor
    mock_cursor.fetch_pandas_all.return_value = expected_results

    result = snowflake._pandas_query(sql, params)

    assert result == expected_results
    mock_cursor.execute.assert_called_once_with(sql, params)
    mock_cursor.fetch_pandas_all.assert_called_once()


@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]

    mock_cursor = MagicMock()

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

    result = snowflake._query(sql, params)

    assert isinstance(result, Generator)
    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._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_distro_report_data(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_query,
    mock_build_report_query,
):
    """Test getting distribution report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_should_include_mechanicals.return_value = False
    mock_build_report_query.return_value = sql
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.1),
            'TOTAL': Decimal(12.34),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(0),
            'TOTAL': Decimal(0.001),
        },
    ]
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty

    result = result.where(pandas.notnull(result), None)
    result_dict = result.to_dict()
    assert result_dict == {
        'PRODUCT NAME': {'5555555555555': 'Bad Songs', 'Total': None},
        'PRODUCT CODE': {'5555555555555': 'A111', 'Total': None},
        'PROJECT CODE': {'5555555555555': 'EXP1234', 'Total': None},
        'FORMAT': {'5555555555555': 'Single', 'Total': None},
        'PRIMARY ARTIST': {'5555555555555': 'Artist', 'Total': None},
        'SUBACCOUNT': {'5555555555555': 'subaccount', 'Total': None},
        'USA': {'5555555555555': 12.34, 'Total': 12.34},
        'Norway': {'5555555555555': 0.001, 'Total': 0.001},
        'CURRENCY': {'5555555555555': 'USD', 'Total': None},
        'Total': {'5555555555555': 12.341, 'Total': 12.341},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._pandas_query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_distro_report_data_pandas(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_pandas_query,
    mock_build_report_query,
):
    """Test getting distribution report data using Pandas."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = True
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_should_include_mechanicals.return_value = False
    mock_build_report_query.return_value = sql
    mock_pandas_query.return_value = pandas.DataFrame(
        [
            {
                'COLUMN_DIMENSION': 'USA',
                'RELEASENAME': 'Bad Songs',
                'PRODUCT_CODE': 'A111',
                'PROJECT_CODE': 'EXP1234',
                'FORMAT': 'Single',
                'SUBACCOUNT_NAME': 'subaccount',
                'ARTISTNAME': 'Artist',
                'ROW_DIMENSION': '5555555555555',
                'CURRENCY': 'USD',
                'MECHANICALS': Decimal(-0.1),
                'TOTAL': Decimal(12.34),
            },
            {
                'COLUMN_DIMENSION': 'Norway',
                'RELEASENAME': 'Bad Songs',
                'PRODUCT_CODE': 'A111',
                'PROJECT_CODE': 'EXP1234',
                'FORMAT': 'Single',
                'SUBACCOUNT_NAME': 'subaccount',
                'ARTISTNAME': 'Artist',
                'ROW_DIMENSION': '5555555555555',
                'CURRENCY': 'USD',
                'MECHANICALS': Decimal(0),
                'TOTAL': Decimal(0.001),
            },
        ]
    )
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )
    result_dict = result.where(pandas.notnull(result), None).to_dict()

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty
    assert result_dict == {
        'PRODUCT NAME': {'5555555555555': 'Bad Songs', 'Total': None},
        'PRODUCT CODE': {'5555555555555': 'A111', 'Total': None},
        'PROJECT CODE': {'5555555555555': 'EXP1234', 'Total': None},
        'FORMAT': {'5555555555555': 'Single', 'Total': None},
        'PRIMARY ARTIST': {'5555555555555': 'Artist', 'Total': None},
        'SUBACCOUNT': {'5555555555555': 'subaccount', 'Total': None},
        'USA': {'5555555555555': 12.34, 'Total': 12.34},
        'Norway': {'5555555555555': 0.001, 'Total': 0.001},
        'CURRENCY': {'5555555555555': 'USD', 'Total': None},
        'Total': {'5555555555555': 12.341, 'Total': 12.341},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_pandas_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_report_data_with_no_data(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_query,
    mock_build_report_query,
):
    """Test getting distribution report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_build_report_query.return_value = sql
    mock_query.return_value = []
    mock_should_include_mechanicals.return_value = True

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )
    result_dict = result.to_dict()

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty
    assert result_dict == {
        'PRODUCT NAME': {'Total': 0.0},
        'UPC': {'Total': 0.0},
        'PRODUCT CODE': {'Total': 0.0},
        'PROJECT CODE': {'Total': 0.0},
        'FORMAT': {'Total': 0.0},
        'PRIMARY ARTIST': {'Total': 0.0},
        'SUBACCOUNT': {'Total': 0.0},
        'Subtotal': {'Total': 0.0},
        'US Mechanicals': {'Total': 0.0},
        'Total': {'Total': 0.0},
        'CURRENCY': {'Total': 0.0},
    }
    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_nr_report_data(
    mock_is_feature_enabled, mock_is_distributor, mock_query, mock_build_report_query
):
    """Test getting neighbouring rights report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'statement_period'
    revenue_type = config.REVENUE_TYPE_NEIGHBOURING_RIGHTS
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'N'}
    mock_build_report_query.return_value = sql
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'ROW_DIMENSION': '222',
            'CURRENCY': 'USD',
            'TOTAL': Decimal(12.34),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'ROW_DIMENSION': '333',
            'CURRENCY': 'USD',
            'TOTAL': Decimal(0.001),
        },
    ]
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty

    result_dict = result.to_dict()
    del (
        result_dict['Norway']['222'],
        result_dict['USA']['333'],
        result_dict['CURRENCY']['Total'],
    )  # remove NaN values
    assert result_dict == {
        'Norway': {'333': 0.001, 'Total': 0.001},
        'Total': {'222': 12.34, '333': 0.001, 'Total': 12.341},
        'CURRENCY': {'222': 'USD', '333': 'USD'},
        'USA': {'222': 12.34, 'Total': 12.34},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_report_distro_data_with_contract(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_query,
    mock_build_report_query,
):
    """Test getting distribution report data while sending a contract ID."""
    account_id = 246701
    contract_id = 10001
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_build_report_query.return_value = sql
    mock_should_include_mechanicals.return_value = True
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.1),
            'TOTAL': Decimal(12.34),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(0),
            'TOTAL': Decimal(0.001),
        },
    ]
    expected_params = {
        'account_id': account_id,
        'statement_period_ids': statement_period_ids,
        'contract_id': contract_id,
    }

    result = snowflake.get_report_data(
        account_id,
        contract_id,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty

    result = result.where(pandas.notnull(result), None)
    result_dict = result.to_dict()
    assert result_dict == {
        'PRODUCT NAME': {'5555555555555': 'Bad Songs', 'Total': None},
        'PRODUCT CODE': {'5555555555555': 'A111', 'Total': None},
        'PROJECT CODE': {'5555555555555': 'EXP1234', 'Total': None},
        'FORMAT': {'5555555555555': 'Single', 'Total': None},
        'PRIMARY ARTIST': {'5555555555555': 'Artist', 'Total': None},
        'SUBACCOUNT': {'5555555555555': 'subaccount', 'Total': None},
        'USA': {'5555555555555': 12.34, 'Total': 12.34},
        'Norway': {'5555555555555': 0.001, 'Total': 0.001},
        'CURRENCY': {'5555555555555': 'USD', 'Total': None},
        'Subtotal': {'5555555555555': 12.341, 'Total': 12.341},
        'US Mechanicals': {'5555555555555': -0.1, 'Total': -0.1},
        'Total': {'5555555555555': 12.241, 'Total': 12.241},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_report_nr_data_with_contract(
    mock_is_feature_enabled, mock_is_distributor, mock_query, mock_build_report_query
):
    """Test getting neighbouring rights report data while sending a contract ID."""
    account_id = 246701
    contract_id = 10001
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'statement_period'
    revenue_type = config.REVENUE_TYPE_NEIGHBOURING_RIGHTS
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'N'}
    mock_build_report_query.return_value = sql
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'ROW_DIMENSION': 'March 2022',
            'CURRENCY': 'USD',
            'TOTAL': Decimal(12.34),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'ROW_DIMENSION': 'April 2022',
            'CURRENCY': 'USD',
            'TOTAL': Decimal(0.001),
        },
    ]
    expected_params = {
        'account_id': account_id,
        'statement_period_ids': statement_period_ids,
        'contract_id': contract_id,
    }

    result = snowflake.get_report_data(
        account_id,
        contract_id,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty

    result_dict = result.to_dict()
    del (
        result_dict['Norway']['March 2022'],
        result_dict['USA']['April 2022'],
        result_dict['CURRENCY']['Total'],
    )  # remove NaN values
    assert result_dict == {
        'Norway': {'April 2022': 0.001, 'Total': 0.001},
        'Total': {'March 2022': 12.34, 'April 2022': 0.001, 'Total': 12.341},
        'CURRENCY': {'March 2022': 'USD', 'April 2022': 'USD'},
        'USA': {'March 2022': 12.34, 'Total': 12.34},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_report_distro_data_with_subaccount(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_query,
    mock_build_report_query,
):
    """Test getting distribution report data while sending a subaccount ID."""
    account_id = 246701
    subaccount_id = 50001
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_build_report_query.return_value = sql
    mock_should_include_mechanicals.return_value = False
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.1),
            'TOTAL': Decimal(12.34),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(0),
            'TOTAL': Decimal(0.001),
        },
    ]
    expected_params = {
        'account_id': account_id,
        'statement_period_ids': statement_period_ids,
        'subaccount_id': subaccount_id,
    }

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
        subaccount_id,
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty

    result = result.where(pandas.notnull(result), None)
    result_dict = result.to_dict()
    assert result_dict == {
        'PRODUCT NAME': {'5555555555555': 'Bad Songs', 'Total': None},
        'PRODUCT CODE': {'5555555555555': 'A111', 'Total': None},
        'PROJECT CODE': {'5555555555555': 'EXP1234', 'Total': None},
        'FORMAT': {'5555555555555': 'Single', 'Total': None},
        'PRIMARY ARTIST': {'5555555555555': 'Artist', 'Total': None},
        'SUBACCOUNT': {'5555555555555': 'subaccount', 'Total': None},
        'USA': {'5555555555555': 12.34, 'Total': 12.34},
        'Norway': {'5555555555555': 0.001, 'Total': 0.001},
        'CURRENCY': {'5555555555555': 'USD', 'Total': None},
        'Total': {'5555555555555': 12.341, 'Total': 12.341},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        subaccount_id,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_report_data_with_extra_column(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_query,
    mock_build_report_query,
):
    """Test getting distribution report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_build_report_query.return_value = sql
    mock_should_include_mechanicals.return_value = True
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'ROW_DIMENSION': '012345',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.2),
            'TOTAL': Decimal(12.34),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'ROW_DIMENSION': '012345',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(0.0),
            'TOTAL': Decimal(0.001),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'ROW_DIMENSION': '987654',
            'RELEASENAME': 'More Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.01),
            'TOTAL': Decimal(0.09),
        },
        {
            'COLUMN_DIMENSION': 'Norway',
            'ROW_DIMENSION': '987654',
            'RELEASENAME': 'More Bad Songs (Remix)',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.1),
            'TOTAL': Decimal(1.23),
        },
    ]
    expected = pandas.DataFrame(
        {
            'UPC': ['012345', '987654', '987654', 'Total'],
            'PRODUCT NAME': ['Bad Songs', 'More Bad Songs', 'More Bad Songs (Remix)', NaN],
            'PRODUCT CODE': ['A111', 'A111', 'A111', NaN],
            'PROJECT CODE': ['EXP1234', 'EXP1234', 'EXP1234', NaN],
            'FORMAT': ['Single', 'Single', 'Single', NaN],
            'PRIMARY ARTIST': ['Artist', 'Artist', 'Artist', NaN],
            'SUBACCOUNT': ['subaccount', 'subaccount', 'subaccount', NaN],
            'Norway': pandas.to_numeric([0.001, 0.09, 1.23, 1.321]),
            'USA': pandas.to_numeric([12.34, NaN, NaN, 12.34]),
            'Subtotal': pandas.to_numeric([12.341, 0.09, 1.23, 13.661]),
            'US Mechanicals': pandas.to_numeric([-0.2, -0.01, -0.1, -0.31]),
            'Total': pandas.to_numeric([12.141, 0.08, 1.13, 13.351]),
            'CURRENCY': ['USD', 'USD', 'USD', NaN],
        }
    ).set_index('UPC')

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
    )

    assert isinstance(result, pandas.DataFrame)
    pandas.testing.assert_frame_equal(result.fillna(0), expected.fillna(0))


def test_build_report_nr_query_territory_financial_detail():
    """Test building the neighbouring rights report.

    query for territory/financial_detail.
    """
    row_dimension = 'territory'
    filters = ['account_id', 'statement_period_ids']
    expected = """
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS ROW_DIMENSION,
        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_REVENUE,
        SUM(REVENUE_NR_DBT.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY
                    - REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS COMMISSION,
        SUM(REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS NET_REVENUE,
        REVENUE_NR_DBT.ACCOUNT_PAYEE_CURRENCY AS CURRENCY
    FROM REVENUE_NR_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    WHERE
        REVENUE_NR_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_NR_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query_financial_detail(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_distro_query_territory_financial_detail():
    """Test building the neighbouring rights report.

    query for territory/financial_detail.
    """
    row_dimension = 'territory'
    filters = ['account_id', 'statement_period_ids']
    expected = """
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS ROW_DIMENSION,
        SUM(REVENUE_DISTRO_DBT.GROSS_REVENUE_PAYEE_CURRENCY) AS PRE_WHT_AMOUNT,
        SUM(REVENUE_DISTRO_DBT.WITHHOLDING_TAX_PAYEE_CURRENCY) AS WHT_AMOUNT,
        SUM(REVENUE_DISTRO_DBT.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY)
            AS GROSS_REVENUE,
        SUM(REVENUE_DISTRO_DBT.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY
                    - REVENUE_DISTRO_DBT.NET_SHARE_PAYEE_CURRENCY) AS COMMISSION,
        SUM(REVENUE_DISTRO_DBT.NET_SHARE_PAYEE_CURRENCY) AS NET_REVENUE,
        REVENUE_DISTRO_DBT.ACCOUNT_PAYEE_CURRENCY AS CURRENCY
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query_financial_detail(
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        config.REPORT_TABLE_DISTRIBUTION,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_financial_detail_query_contract_id_filter():
    """Test building the neighbouring rights report.

    query while filtering with contract ID.
    """
    row_dimension = 'territory'
    filters = ['contract_id']
    expected = """
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS ROW_DIMENSION,
        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_REVENUE,
        SUM(REVENUE_NR_DBT.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY
                    - REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS COMMISSION,
        SUM(REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS NET_REVENUE,
        REVENUE_NR_DBT.ACCOUNT_PAYEE_CURRENCY AS CURRENCY
    FROM REVENUE_NR_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_NR_DBT.COUNTRY_ID
    WHERE
        REVENUE_NR_DBT.CONTRACT_ID = %(contract_id)s
    GROUP BY
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query_financial_detail(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_recording_financial_detail():
    """Test building the neighbouring rights report.

    query for recording/financial_detail.
    """
    filters = ['account_id', 'statement_period_ids']
    row_dimension = 'recording'
    expected = """
    SELECT
        REVENUE_NR_DBT.SOUND_RECORDING_ID AS ROW_DIMENSION,
        REVENUE_NR_DBT.SOUND_RECORDING_NAME,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.VERSION,
        FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.MAIN_ARTIST,
        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_REVENUE,
        SUM(REVENUE_NR_DBT.GROSS_REVENUE_AFTER_WITHHOLDING_TAX_PAYEE_CURRENCY
                    - REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS COMMISSION,
        SUM(REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS NET_REVENUE,
        REVENUE_NR_DBT.ACCOUNT_PAYEE_CURRENCY AS CURRENCY
    FROM REVENUE_NR_DBT
    LEFT JOIN FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING
        ON PERFORMANCE_NR_SOUND_RECORDING.ID = REVENUE_NR_DBT.SOUND_RECORDING_ID
    WHERE
        REVENUE_NR_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_NR_DBT.STATEMENT_PERIOD_ID
            IN (%(statement_period_ids)s)
    GROUP BY
         REVENUE_NR_DBT.SOUND_RECORDING_NAME,
         FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.VERSION,
         FACTS.TEST.PERFORMANCE_NR_SOUND_RECORDING.MAIN_ARTIST,
         ROW_DIMENSION,
         CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query_financial_detail(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


def test_build_report_nr_query_statement_period_collection_society():
    """Test building the nr report query for statement_period/collection_society."""
    column_dimension = 'statement_period'
    row_dimension = 'collection_society'
    filters = ['account_id', 'statement_period_ids']
    expected = """
    SELECT
        STATEMENT_PERIOD.STATEMENT_PERIOD_NAME AS COLUMN_DIMENSION,
        CUSTOMER_MASTER_MASTER.CUSTOMER_NAME AS ROW_DIMENSION,
        DIM_COUNTRY.COUNTRYNAME,
        REVENUE_NR_DBT.ACCOUNT_PAYEE_CURRENCY AS CURRENCY,
        SUM(REVENUE_NR_DBT.NET_SHARE_PAYEE_CURRENCY) AS TOTAL
    FROM REVENUE_NR_DBT
        JOIN ORCHARD_APP_REPORTING_V2.
        TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        ON STATEMENT_PERIOD.STATEMENT_PERIOD_ID
            = REVENUE_NR_DBT.STATEMENT_PERIOD_ID
        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.ACCOUNT_ID = %(account_id)s
    AND REVENUE_NR_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    GROUP BY
        DIM_COUNTRY.COUNTRYNAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[column_dimension],
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


@patch('src.connectors.snowflake._build_report_query_financial_detail')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_nr_financial_detail_report_data(
    mock_is_feature_enabled, mock_query, mock_build_report_query
):
    """Test getting neighbouring rights financial detail report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    row_dimension = 'territory'
    revenue_type = 'neighbouring_rights'
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_build_report_query.return_value = sql
    mock_query.return_value = [
        {
            'ROW_DIMENSION': 'Brazil',
            'PRE_WHT_AMOUNT': 318.2115,
            'WHT_AMOUNT': -47.7317,
            'GROSS_REVENUE': 270.4798,
            'COMMISSION': 953.0900,
            'NET_REVENUE': 258.3082,
            'CURRENCY': 'USD',
        },
        {
            'ROW_DIMENSION': 'Germany',
            'PRE_WHT_AMOUNT': 109001.5657,
            'WHT_AMOUNT': -17249.42620,
            'GROSS_REVENUE': 91752.1389,
            'COMMISSION': 5.7300,
            'NET_REVENUE': 87623.2932,
            'CURRENCY': 'USD',
        },
    ]
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    result = snowflake.get_report_data_financial_detail(
        account_id, None, statement_period_ids, row_dimension, revenue_type
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty

    result_dict = result.to_dict()
    del result_dict['CURRENCY']['Total']  # remove NaN values
    assert result_dict == {
        'COMMISSION': {'Brazil': 953.0900, 'Germany': 5.7300, 'Total': 958.82},
        'GROSS_REVENUE': {'Brazil': 270.4798, 'Germany': 91752.1389, 'Total': 92022.6187},
        'NET_REVENUE': {'Brazil': 258.3082, 'Germany': 87623.2932, 'Total': 87881.6014},
        'PRE_WHT_AMOUNT': {'Brazil': 318.2115, 'Germany': 109001.5657, 'Total': 109319.77720000001},
        'WHT_AMOUNT': {'Brazil': -47.7317, 'Germany': -17249.42620, 'Total': -17297.157900000002},
        'CURRENCY': {'Brazil': 'USD', 'Germany': 'USD'},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        list(expected_params.keys()),
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query_financial_detail')
@patch('src.connectors.snowflake._pandas_query')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_nr_financial_detail_report_data_ff_enabled(
    mock_is_feature_enabled, mock_pandas_query, mock_build_report_query
):
    """Test getting neighbouring rights financial detail report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    row_dimension = 'territory'
    revenue_type = 'neighbouring_rights'
    sql = 'SELECT * FROM test'
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    mock_is_feature_enabled.return_value = True
    mock_build_report_query.return_value = sql
    mock_pandas_query.return_value = pandas.DataFrame(
        [
            {
                'ROW_DIMENSION': 'Brazil',
                'PRE_WHT_AMOUNT': 318.2115,
                'WHT_AMOUNT': -47.7317,
                'GROSS_REVENUE': 270.4798,
                'COMMISSION': 953.0900,
                'NET_REVENUE': 258.3082,
                'CURRENCY': 'USD',
            },
            {
                'ROW_DIMENSION': 'Germany',
                'PRE_WHT_AMOUNT': 109001.5657,
                'WHT_AMOUNT': -17249.42620,
                'GROSS_REVENUE': 91752.1389,
                'COMMISSION': 5.7300,
                'NET_REVENUE': 87623.2932,
                'CURRENCY': 'USD',
            },
        ]
    )

    result = snowflake.get_report_data_financial_detail(
        account_id, None, statement_period_ids, row_dimension, revenue_type
    )
    result_dict = result.to_dict()
    del result_dict['CURRENCY']['Total']  # remove NaN values

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty
    assert result_dict == {
        'COMMISSION': {'Brazil': 953.0900, 'Germany': 5.7300, 'Total': 958.82},
        'GROSS_REVENUE': {'Brazil': 270.4798, 'Germany': 91752.1389, 'Total': 92022.6187},
        'NET_REVENUE': {'Brazil': 258.3082, 'Germany': 87623.2932, 'Total': 87881.6014},
        'PRE_WHT_AMOUNT': {'Brazil': 318.2115, 'Germany': 109001.5657, 'Total': 109319.77720000001},
        'WHT_AMOUNT': {'Brazil': -47.7317, 'Germany': -17249.42620, 'Total': -17297.157900000002},
        'CURRENCY': {'Brazil': 'USD', 'Germany': 'USD'},
    }

    mock_build_report_query.assert_called_once_with(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        list(expected_params.keys()),
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )
    mock_pandas_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query_financial_detail')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_nr_financial_detail_report_data_with_no_data(
    mock_is_feature_enabled, mock_query, mock_build_report_query
):
    """Test getting neighbouring rights financial detail report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    row_dimension = 'recording'
    revenue_type = 'neighbouring_rights'
    sql = 'SELECT * FROM test'

    mock_is_feature_enabled.return_value = False
    mock_build_report_query.return_value = sql
    mock_query.return_value = []
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}
    result = snowflake.get_report_data_financial_detail(
        account_id, None, statement_period_ids, row_dimension, revenue_type
    )
    result_dict = result.to_dict()

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty
    assert result_dict == {
        'SOUND RECORDING ID': {'Total': 0.0},
        'RECORDING TITLE': {'Total': 0.0},
        'RECORDING ARTIST': {'Total': 0.0},
        'RECORDING VERSION': {'Total': 0.0},
        'PRE_WHT_AMOUNT': {'Total': 0.0},
        'WHT_AMOUNT': {'Total': 0.0},
        'GROSS_REVENUE': {'Total': 0.0},
        'COMMISSION': {'Total': 0.0},
        'NET_REVENUE': {'Total': 0.0},
        'CURRENCY': {'Total': 0.0},
    }
    mock_build_report_query.assert_called_once_with(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        list(expected_params.keys()),
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._build_report_query_financial_detail')
@patch('src.connectors.snowflake._pandas_query')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_nr_financial_detail_report_data_with_no_data_ff_enabled(
    mock_is_feature_enabled, mock_pandas_query, mock_build_report_query
):
    """Test getting neighbouring rights financial detail report data."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    row_dimension = 'recording'
    revenue_type = 'neighbouring_rights'
    sql = 'SELECT * FROM test'
    expected_params = {'account_id': account_id, 'statement_period_ids': statement_period_ids}

    mock_is_feature_enabled.return_value = True
    mock_build_report_query.return_value = sql
    mock_pandas_query.return_value = pandas.DataFrame()

    result = snowflake.get_report_data_financial_detail(
        account_id, None, statement_period_ids, row_dimension, revenue_type
    )
    result_dict = result.to_dict()

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty
    assert result_dict == {
        'SOUND RECORDING ID': {'Total': 0.0},
        'RECORDING TITLE': {'Total': 0.0},
        'RECORDING ARTIST': {'Total': 0.0},
        'RECORDING VERSION': {'Total': 0.0},
        'PRE_WHT_AMOUNT': {'Total': 0.0},
        'WHT_AMOUNT': {'Total': 0.0},
        'GROSS_REVENUE': {'Total': 0.0},
        'COMMISSION': {'Total': 0.0},
        'NET_REVENUE': {'Total': 0.0},
        'CURRENCY': {'Total': 0.0},
    }
    mock_build_report_query.assert_called_once_with(
        config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS[row_dimension],
        list(expected_params.keys()),
        config.REPORT_TABLE_NEIGHBOURING_RIGHTS,
    )
    mock_pandas_query.assert_called_once_with(sql, expected_params)


@pytest.mark.parametrize(
    'value, expected',
    [
        (None, None),
        ('', None),
        ('     ', None),
        ('\n\t  \r', None),
        ('  Cool song  ', 'Cool song'),
        ('Artist', 'Artist'),
        (float('nan'), None),
        (NaN, None),
    ],
)
def test_canon(value, expected):
    """Test normalizing values."""
    assert snowflake._canon(value) == expected


@pytest.mark.parametrize(
    'row_dimension, account_id, subaccount_id, expected',
    [
        (config.REPORT_DIMENSION_MAP_DISTRIBUTION['track'], 999, None, True),
        (config.REPORT_DIMENSION_MAP_DISTRIBUTION['track'], 999, 10001, False),
        (config.REPORT_DIMENSION_MAP_DISTRIBUTION['track'], 555, None, False),
        (config.REPORT_DIMENSION_MAP_NEIGHBOURING_RIGHTS['territory'], 999, None, False),
    ],
)
@patch('src.connectors.snowflake.OwsMoneyhub')
def test_should_include_mechanicals(
    mock_OwsMoneyhub, row_dimension, account_id, subaccount_id, expected
):
    """Test determining whether to include mechanicals."""

    def _account_activity_func(account_id):
        """Mock account activity, returning true only for one specific account ID."""
        return {'mechanicals': True} if account_id == 999 else {'mechanicals': False}

    mock_OwsMoneyhub.get_account_activity.side_effect = _account_activity_func

    result = snowflake._should_include_mechanicals(row_dimension, account_id, subaccount_id)

    assert result == expected


def test_build_where_condition_for_query_empty_filters():
    """Test building WHERE condition with no filters."""
    table_name = 'REVENUE_DISTRO_DBT'
    filters = []

    result = snowflake._build_where_condition_for_query(table_name, filters)

    assert result == ''


def test_build_where_condition_for_query_single_filter():
    """Test building WHERE condition with a single filter."""
    table_name = 'REVENUE_DISTRO_DBT'
    filters = ['account_id']

    result = snowflake._build_where_condition_for_query(table_name, filters)

    assert result == 'WHERE REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s'


def test_build_where_condition_for_query_multiple_filters():
    """Test building WHERE condition with multiple filters."""
    table_name = 'REVENUE_DISTRO_DBT'
    filters = ['account_id', 'statement_period_ids', 'artist_ids']

    result = snowflake._build_where_condition_for_query(table_name, filters)

    expected = 'WHERE REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s) AND REVENUE_DISTRO_DBT.ARTIST_ID IN (%(artist_ids)s)'  # noqa: E501
    assert result == expected


def test_build_report_distro_query_with_multiple_custom_filters():
    """Test building the distribution report query with all custom filters."""
    column_dimension = 'territory'
    row_dimension = 'product'
    filters = [
        'account_id',
        'statement_period_ids',
        'activity_period_ids',
        'artist_ids',
        'country_codes',
        'imprint_ids',
        'product_ids',
        'project_ids',
        'store_ids',
        'subaccount_ids',
        'recording_ids',
        'track_unique_ids',
        'transaction_type_ids',
    ]
    expected = f"""
    SELECT
        DIM_COUNTRY.COUNTRYNAME AS COLUMN_DIMENSION,
        DIM_RELEASE.DISPLAY_UPC AS ROW_DIMENSION,
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        REVENUE_DISTRO_DBT.{config.CURRENCY_COLUMN} AS CURRENCY,
        COALESCE(SUM(REVENUE_DISTRO_DBT.{config.MECHANICAL_COLUMN}), 0)
            + COALESCE(SUM(REVENUE_DISTRO_DBT.{config.ADMIN_FEE_COLUMN}), 0)
            AS MECHANICALS,
        SUM(REVENUE_DISTRO_DBT.{config.NET_REVENUE_COLUMN}) AS TOTAL
    FROM REVENUE_DISTRO_DBT
    JOIN FACTS.TEST.DIM_COUNTRY
        ON DIM_COUNTRY.COUNTRYID = REVENUE_DISTRO_DBT.COUNTRY_ID
    JOIN FACTS.TEST.DIM_RELEASE
        ON DIM_RELEASE.RELEASEID = REVENUE_DISTRO_DBT.UPC
    JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PROJECT
        ON PROJECT.PROJECT_ID = REVENUE_DISTRO_DBT.PROJECT_ID
    JOIN FACTS.TEST.DIM_ARTIST
        ON DIM_ARTIST.ARTISTID = REVENUE_DISTRO_DBT.ARTIST_ID
    LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.SUBACCOUNT
        ON SUBACCOUNT.SUBACCOUNT_ID = REVENUE_DISTRO_DBT.SUBACCOUNT_ID
    WHERE
        REVENUE_DISTRO_DBT.ACCOUNT_ID = %(account_id)s
        AND REVENUE_DISTRO_DBT.STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
        AND REVENUE_DISTRO_DBT.ACTIVITY_PERIOD_ID IN (%(activity_period_ids)s)
        AND REVENUE_DISTRO_DBT.ARTIST_ID IN (%(artist_ids)s)
        AND REVENUE_DISTRO_DBT.COUNTRY_CODE IN (%(country_codes)s)
        AND REVENUE_DISTRO_DBT.IMPRINT_ID IN (%(imprint_ids)s)
        AND REVENUE_DISTRO_DBT.PRODUCT_ID IN (%(product_ids)s)
        AND REVENUE_DISTRO_DBT.PROJECT_ID IN (%(project_ids)s)
        AND REVENUE_DISTRO_DBT.STORE_ID IN (%(store_ids)s)
        AND REVENUE_DISTRO_DBT.SUBACCOUNT_ID IN (%(subaccount_ids)s)
        AND REVENUE_DISTRO_DBT.SOUND_RECORDING_ID IN (%(recording_ids)s)
        AND REVENUE_DISTRO_DBT.TRACK_UNIQUE_ID IN (%(track_unique_ids)s)
        AND REVENUE_DISTRO_DBT.TRANSACTION_TYPE_ID IN (%(transaction_type_ids)s)
    GROUP BY
        DIM_RELEASE.RELEASENAME,
        REVENUE_DISTRO_DBT.PRODUCT_CODE,
        PROJECT.PROJECT_CODE,
        DIM_RELEASE.FORMAT,
        DIM_ARTIST.ARTISTNAME,
        SUBACCOUNT.SUBACCOUNT_NAME,
        COLUMN_DIMENSION,
        ROW_DIMENSION,
        CURRENCY
    ORDER BY ROW_DIMENSION ASC
    """

    result = snowflake._build_report_query(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        filters,
        RevenueDisplayType.NET,
    )

    assert REGEX_WHITESPACE.sub('', result) == REGEX_WHITESPACE.sub('', expected)


@patch('src.connectors.snowflake._build_report_query')
@patch('src.connectors.snowflake._query')
@patch('src.connectors.snowflake._should_include_mechanicals')
@patch('src.connectors.snowflake.is_distributor')
@patch('src.connectors.snowflake.is_feature_enabled')
def test_get_distro_report_data_with_custom_filters(
    mock_is_feature_enabled,
    mock_is_distributor,
    mock_should_include_mechanicals,
    mock_query,
    mock_build_report_query,
):
    """Test getting distribution report data with custom filters."""
    account_id = 246701
    statement_period_ids = [111, 222, 333]
    column_dimension = 'territory'
    row_dimension = 'product'
    revenue_type = config.REVENUE_TYPE_DISTRIBUTION
    sql = 'SELECT * FROM test'
    custom_filters = {'artist_ids': [10, 20], 'country_codes': ['US', 'GB'], 'store_ids': [100]}

    mock_is_feature_enabled.return_value = False
    mock_is_distributor.return_value = {'IS_DISTRIBUTOR': 'Y'}
    mock_build_report_query.return_value = sql
    mock_query.return_value = [
        {
            'COLUMN_DIMENSION': 'USA',
            'RELEASENAME': 'Bad Songs',
            'PRODUCT_CODE': 'A111',
            'PROJECT_CODE': 'EXP1234',
            'FORMAT': 'Single',
            'SUBACCOUNT_NAME': 'subaccount',
            'ARTISTNAME': 'Artist',
            'ROW_DIMENSION': '5555555555555',
            'CURRENCY': 'USD',
            'MECHANICALS': Decimal(-0.1),
            'TOTAL': Decimal(12.34),
        },
    ]
    mock_should_include_mechanicals.return_value = True
    expected_params = {
        'account_id': account_id,
        'statement_period_ids': statement_period_ids,
        'artist_ids': [10, 20],
        'country_codes': ['US', 'GB'],
        'store_ids': [100],
    }

    result = snowflake.get_report_data(
        account_id,
        None,
        statement_period_ids,
        column_dimension,
        row_dimension,
        revenue_type,
        RevenueDisplayType.NET,
        None,
        custom_filters,
    )

    assert isinstance(result, pandas.DataFrame)
    assert not result.empty
    mock_build_report_query.assert_called_once_with(
        config.REPORT_TABLE_DISTRIBUTION,
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[column_dimension],
        config.REPORT_DIMENSION_MAP_DISTRIBUTION[row_dimension],
        list(expected_params.keys()),
        RevenueDisplayType.NET,
        None,
    )
    mock_query.assert_called_once_with(sql, expected_params)


@patch('src.connectors.snowflake._query')
def test_get_statement_periods_parsed(mock_query):
    """Test getting statement period month and year."""
    statement_period_ids = [321, 322, 323]

    mock_query.return_value = [
        {'STATEMENT_MONTH': 9, 'STATEMENT_YEAR': 2025},
        {'STATEMENT_MONTH': 10, 'STATEMENT_YEAR': 2025},
        {'STATEMENT_MONTH': 11, 'STATEMENT_YEAR': 2025},
    ]

    result = snowflake.get_statement_periods_parsed(statement_period_ids)

    assert result == [(2025, 9), (2025, 10), (2025, 11)]

    expected_sql = """
        SELECT STATEMENT_MONTH, STATEMENT_YEAR
        FROM
            ORCHARD_APP_REPORTING_V2.TEST_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD
        WHERE STATEMENT_PERIOD_ID IN (%(statement_period_ids)s)
    """
    expected_params = {'statement_period_ids': statement_period_ids}

    mock_query.assert_called_once_with(expected_sql, expected_params)


@patch('src.connectors.snowflake._query_one')
def test_get_subaccount_info(mock_query_one):
    """Test getting the subaccount."""
    subaccount_id = 54321
    expected_row = {'subaccountname': 'party time'}
    expected_sql = """
    SELECT
        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_info(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.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)