from typing import List

from utils.snowflake.metrics.queries.metrics_details_queries.metrics_details_utils_queries import (
    MetricsDetailsUtilsQueries,
)

from utils.snowflake.metrics.schema import MetricsFilters, AccountProvider
from utils.snowflake.metrics.queries.utils_queries import UtilsQueries, FacebookUtilsQueries


class MetricsArtistDetailsQueries:
    @staticmethod
    def fetch_details_by_project(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                project_campaigns.entity_id as main_entity_id,
                project_campaigns.entity_name as name,
                {UtilsQueries.default_combined_result_fields()}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}

            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY main_entity_id, name
        """

    @staticmethod
    def fetch_details_by_campaign(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                project_campaigns.campaign_id as main_entity_id,
                COALESCE(report.CAMPAIGN_NAME, ad_report.CAMPAIGN_NAME) as name,
                {UtilsQueries.default_combined_result_fields()}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}

            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "CAMPAIGN_NAME": "report.CAMPAIGN_NAME"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "CAMPAIGN_NAME": "report.CAMPAIGN_NAME"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY main_entity_id, name
        """

    @staticmethod
    def fetch_details_by_ad_set(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                COALESCE(report.ADSET_ID, ad_report.AD_GROUP_ID) as main_entity_id,
                COALESCE(report.ADSET_NAME, ad_report.AD_GROUP_NAME) as name,
                {UtilsQueries.default_combined_result_fields()}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}

            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={
                    "CAMPAIGN_ID": "report.CAMPAIGN_ID",
                    "ADSET_ID": "report.ADSET_ID",
                    "ADSET_NAME": "report.ADSET_NAME"
                },
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={
                    "CAMPAIGN_ID": "report.CAMPAIGN_ID",
                    "ADSET_SOURCE_ID": "report.ADSET_SOURCE_ID"
                })}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"ADSET_ID": "report.ADSET_ID"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "ADSET_ID": "report.ADSET_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={
                    "CAMPAIGN_ID": "report.CAMPAIGN_ID",
                    "AD_GROUP_ID": "report.AD_GROUP_ID",
                    "AD_GROUP_NAME": "report.AD_GROUP_NAME"
                },
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY main_entity_id, name
        """

    @staticmethod
    def fetch_details_by_category(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                CATEGORIES as ({MetricsDetailsUtilsQueries.campaign_categories_query()}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.category_name, ad_report.category_name) as name,
                {UtilsQueries.default_combined_result_fields(filters_by)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "CAMPAIGN_NAME": "report.CAMPAIGN_NAME"},
                custom_columns={"category_name": "'Digital'"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"category_name": "CATEGORIES.category"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join="JOIN CATEGORIES ON CATEGORIES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"category_name": "CATEGORIES.category"},
                additional_join="JOIN CATEGORIES ON CATEGORIES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
        """

    @staticmethod
    def fetch_details_by_objective(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):

        filters_by = UtilsQueries.filters_by(filters)
        objective_value = MetricsDetailsUtilsQueries.facebook_objective_mapping()
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                CAMPAIGN_OBJECTIVES as ({MetricsDetailsUtilsQueries.campaign_goal_objective_query()}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.objective_value, ad_report.objective) as name,
                {UtilsQueries.default_combined_result_fields(filters_by)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"objective_value": objective_value},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"objective": "CAMPAIGN_OBJECTIVES.objective"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join="JOIN CAMPAIGN_OBJECTIVES ON CAMPAIGN_OBJECTIVES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"objective": "CAMPAIGN_OBJECTIVES.objective"},
                additional_join="JOIN CAMPAIGN_OBJECTIVES ON CAMPAIGN_OBJECTIVES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
        """

    @staticmethod
    def fetch_details_by_goal(entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list):
        filters_by = UtilsQueries.filters_by(filters)
        goal = MetricsDetailsUtilsQueries.facebook_goal_mapping()
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                CAMPAIGN_OBJECTIVES as ({MetricsDetailsUtilsQueries.campaign_goal_objective_query()}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.goal, ad_report.goal) as name,
                {UtilsQueries.default_combined_result_fields(filters_by)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"objective_value": goal},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"goal": "CAMPAIGN_OBJECTIVES.goal"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join="JOIN CAMPAIGN_OBJECTIVES ON CAMPAIGN_OBJECTIVES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"goal": "CAMPAIGN_OBJECTIVES.goal"},
                additional_join="JOIN CAMPAIGN_OBJECTIVES ON CAMPAIGN_OBJECTIVES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
        """

    @staticmethod
    def fetch_details_by_type(entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list):
        filters_by = UtilsQueries.filters_by(filters)

        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                CATEGORIES as ({MetricsDetailsUtilsQueries.campaign_categories_query()}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.campaign_type_name, ad_report.campaign_type_name) as name,
                {UtilsQueries.default_combined_result_fields()}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"campaign_type_name": "'Social Ads'"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"campaign_type_name": "CATEGORIES.campaign_type"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join="JOIN CATEGORIES ON CATEGORIES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"campaign_type_name": "CATEGORIES.campaign_type"},
                additional_join="JOIN CATEGORIES ON CATEGORIES.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
              AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
        """

    @staticmethod
    def fetch_details_by_country(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({MetricsDetailsUtilsQueries.country_campaign_adsets(filters_by)}),
                territories_mapping as ({MetricsDetailsUtilsQueries.territories_mapping()}),
                campaign_territories as ({MetricsDetailsUtilsQueries.google_campaign_territories()})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.COUNTRY, ad_report.territory_name) as territory,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "COUNTRY": "report.COUNTRY"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as actions_report ON
                actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID AND report.COUNTRY = actions_report.COUNTRY
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.COUNTRY = unique_actions_report.COUNTRY
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.CAMPAIGN_ID = thruplays_report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as u_outbound_clicks_report ON report.COUNTRY = u_outbound_clicks_report.COUNTRY
                AND report.CAMPAIGN_ID = u_outbound_clicks_report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins,
                aggregation_func="AVG"
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.COUNTRY = u_outbound_clicks_ctr_report.COUNTRY
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as conversions_report ON report.CAMPAIGN_ID = conversions_report.CAMPAIGN_ID
                AND report.COUNTRY = conversions_report.COUNTRY
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"COUNTRY": "report.COUNTRY"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_COUNTRY_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.COUNTRY = video_views_report.COUNTRY
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "COUNTRY": "report.COUNTRY"},
                breakdown_type="COUNTRY",
                joins_callback=MetricsDetailsUtilsQueries.countries_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.COUNTRY = video_watched_report.COUNTRY
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"territory_name": "campaign_territories.territory_code"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join="JOIN campaign_territories ON campaign_territories.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"territory_name": "campaign_territories.territory_code"},
                additional_join="JOIN campaign_territories ON campaign_territories.CAMPAIGN_ID = report.CAMPAIGN_ID"
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND c_report.territory_name = ad_report.territory_name
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY territory
            ORDER BY territory
        """

    @staticmethod
    def fetch_details_by_day(entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.DATE, ad_report.DATE) as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND unique_actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND thruplays_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_ctr_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND conversions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_views_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_watched_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND c_report.DATE = ad_report.DATE
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_week(entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                WEEK(COALESCE(report.DATE, ad_report.DATE)) as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND unique_actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND thruplays_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_ctr_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND conversions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_views_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_watched_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND c_report.DATE = ad_report.DATE
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_month(entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                {MetricsDetailsUtilsQueries.month_mapping()} as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND unique_actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND thruplays_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_ctr_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND conversions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_views_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_watched_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND c_report.DATE = ad_report.DATE
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_chartweek(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                WEEK(DATEADD(day, -2, COALESCE(report.DATE, ad_report.DATE))) as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND unique_actions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND thruplays_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_ctr_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND conversions_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"DATE": "report.DATE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_views_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_watched_report.DATE = report.DATE
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "DATE": "report.DATE"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND c_report.DATE = ad_report.DATE
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_gender(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                CASE
                   WHEN COALESCE(report.GENDER, ad_report.CRITERIA) in ('unknown', 'Undetermined') THEN 'Unknown'
                   WHEN COALESCE(report.GENDER, ad_report.CRITERIA) = 'male' THEN 'Male'
                   WHEN COALESCE(report.GENDER, ad_report.CRITERIA) = 'female' THEN 'Female'
                   ELSE COALESCE(report.GENDER, ad_report.CRITERIA)
                END as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "GENDER": "report.GENDER"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND actions_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND unique_actions_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND thruplays_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_ctr_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND conversions_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"GENDER": "report.GENDER"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_views_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "GENDER": "report.GENDER"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_watched_report.GENDER = report.GENDER
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "CRITERIA": "report.CRITERIA"},
                table="V_GOOGLE_GENDER_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_provider(
        entity_id: int,
        filters: MetricsFilters,
        project_artists: list,
        project_campaigns: list,
        providers: List[AccountProvider],
    ):

        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
            PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
            PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
            campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                {MetricsDetailsUtilsQueries.build_provider_column(providers)} as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "ACCOUNT_ID": "report.ACCOUNT_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({
                MetricsDetailsUtilsQueries.facebook_ads_history_report(columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"})}
            ) as adset_history ON adset_history.CAMPAIGN_ID = project_campaigns.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "CUSTOMER_ID": "report.CUSTOMER_ID"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id

            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "CUSTOMER_ID": "report.CUSTOMER_ID"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND c_report.CUSTOMER_ID = ad_report.CUSTOMER_ID
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_age(entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.AGE, ad_report.age) as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "AGE": "report.AGE"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as actions_report ON actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND actions_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND unique_actions_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND thruplays_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_report ON u_outbound_clicks_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND u_outbound_clicks_ctr_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as conversions_report ON conversions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND conversions_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"AGE": "report.AGE"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_AGE_GENDER_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_views_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "AGE": "report.AGE"},
                breakdown_type="AGE_GENDER",
                joins_callback=MetricsDetailsUtilsQueries.age_and_gender_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND video_watched_report.AGE = report.AGE
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"age": MetricsDetailsUtilsQueries.google_age_mapping()},
                table="V_GOOGLE_AGE_RANGE_PERFORMANCE_REPORT",
                additional_join=""
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                additional_join=""
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """

    @staticmethod
    def fetch_details_by_platform(
        entity_id: int, filters: MetricsFilters, project_artists: list, project_campaigns: list
    ):
        filters_by = UtilsQueries.filters_by(filters)
        return f"""
            WITH
            PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
            PROJECT_ARTISTS as ({UtilsQueries.projects_artists_query(project_artists)}),
            PLATFORMS as ({MetricsDetailsUtilsQueries.platforms_query(filters)}),
            campaign_adsets as ({MetricsDetailsUtilsQueries.platforms_campaign_adsets(filters_by)})
            SELECT
                ROW_NUMBER() OVER (ORDER BY COUNT(*) ASC) as main_entity_id,
                COALESCE(report.PUBLISHER_PLATFORM, ad_report.PLATFORM) as name,
                {UtilsQueries.default_combined_result_fields(True)}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTISTS as project_artists
                ON project_artists.project_id = project_campaigns.entity_id AND project_artists.artist_id = {entity_id}
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_daily_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as report ON report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_actions_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as actions_report ON
                actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                    AND report.PUBLISHER_PLATFORM = actions_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_unique_actions_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as unique_actions_report ON unique_actions_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.PUBLISHER_PLATFORM = unique_actions_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_THRUPLAY_WATCHED_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as thruplays_report ON thruplays_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.PUBLISHER_PLATFORM = thruplays_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_OUTBOUND_CLICKS",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as u_outbound_clicks_report ON report.PUBLISHER_PLATFORM = u_outbound_clicks_report.PUBLISHER_PLATFORM
                AND report.CAMPAIGN_ID = u_outbound_clicks_report.CAMPAIGN_ID
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_OUTBOUND_CLICKS_CTR",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as u_outbound_clicks_ctr_report ON u_outbound_clicks_ctr_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.PUBLISHER_PLATFORM = u_outbound_clicks_ctr_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as conversions_report ON report.CAMPAIGN_ID = conversions_report.CAMPAIGN_ID
                AND report.PUBLISHER_PLATFORM = conversions_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_common_value_report(
                filters_by=filters_by,
                columns={"PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                custom_columns={"CAMPAIGN_ID": "campaign_adsets.CAMPAIGN_ID"},
                table="V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_VIDEO_PLAY_ACTIONS",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as video_views_report ON video_views_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.PUBLISHER_PLATFORM = video_views_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.facebook_video_watched_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID", "PUBLISHER_PLATFORM": "report.PUBLISHER_PLATFORM"},
                breakdown_type="PUBLISHER_PLATFORM",
                joins_callback=MetricsDetailsUtilsQueries.platforms_joins
            )}) as video_watched_report ON video_watched_report.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND report.PUBLISHER_PLATFORM = video_watched_report.PUBLISHER_PLATFORM
            LEFT JOIN ({MetricsDetailsUtilsQueries.google_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"platform": "PLATFORMS.platform"},
                table="V_GOOGLE_AD_PERFORMANCE_REPORT",
                additional_join="JOIN PLATFORMS ON (PLATFORMS.CAMPAIGN_ID = report.CAMPAIGN_ID)"
            )}) as ad_report ON ad_report.CAMPAIGN_ID = project_campaigns.campaign_id
            LEFT JOIN  ({MetricsDetailsUtilsQueries.google_campaign_group_report(
                filters_by=filters_by,
                columns={"CAMPAIGN_ID": "report.CAMPAIGN_ID"},
                custom_columns={"platform": "PLATFORMS.platform"},
                additional_join="JOIN PLATFORMS ON (PLATFORMS.CAMPAIGN_ID = report.CAMPAIGN_ID)"
            )}) as c_report ON c_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
                AND ad_report.platform = c_report.platform
            WHERE project_artists.artist_id = {entity_id}
                  AND (ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL)
            GROUP BY name
            ORDER BY name
        """