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


class MetricsQueries:
    @staticmethod
    def fetch_projects_metrics_query(filters: MetricsFilters, project_campaigns: list):
        filter_by = UtilsQueries.filters_by(table_alias="report", filters=filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filter_by)})
            SELECT {UtilsQueries.metrics_with_totals("project_campaigns.entity_id")}
            FROM PROJECT_CAMPAIGN as project_campaigns
            {UtilsQueries.dsp_age_gender_joins(filter_by)}
            WHERE ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL
            GROUP BY project_campaigns.entity_id
            ORDER BY {UtilsQueries.order_by(filters)}
            LIMIT {filters.limit} OFFSET {filters.offset};
        """

    @staticmethod
    def fetch_artists_metrics_query(filters: MetricsFilters, project_campaigns: list, project_artists: list):
        filter_by = UtilsQueries.filters_by(table_alias="report", filters=filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                PROJECT_ARTIST as ({UtilsQueries.projects_artists_query(project_artists)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filter_by)})
            SELECT {UtilsQueries.metrics_with_totals("project_artists.artist_id")}
            FROM PROJECT_CAMPAIGN as project_campaigns
            JOIN PROJECT_ARTIST as project_artists ON (project_artists.project_id = project_campaigns.entity_id)
            {UtilsQueries.dsp_age_gender_joins(filter_by)}
            WHERE ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL
            GROUP BY project_artists.artist_id
            ORDER BY {UtilsQueries.order_by(filters)}
            LIMIT {filters.limit} OFFSET {filters.offset};
        """

    @staticmethod
    def fetch_campaigns_metrics_query(filters: MetricsFilters, project_campaigns: list):
        filter_by = UtilsQueries.filters_by(table_alias="report", filters=filters)
        return f"""
            WITH
                PROJECT_CAMPAIGN as ({UtilsQueries.entity_campaigns_query(project_campaigns)}),
                campaign_adsets as ({FacebookUtilsQueries.age_gender_campaign_adsets(filter_by)})
            SELECT {UtilsQueries.metrics_with_totals("project_campaigns.campaign_id")}
            FROM PROJECT_CAMPAIGN as project_campaigns
            {UtilsQueries.dsp_age_gender_joins(filter_by)}
            WHERE ad_report.CAMPAIGN_ID is not NULL OR report.CAMPAIGN_ID is not NULL
            GROUP BY project_campaigns.campaign_id
            ORDER BY {UtilsQueries.order_by(filters)}
            LIMIT {filters.limit} OFFSET {filters.offset};
        """