from typing import List
from utils.snowflake.constants import linkfire_db_schema, artists_db_schema
import config


class Query:
    @staticmethod
    def fetch_campaigns_query(marketing_accounts_ids: List[str]):

        return f"""
                WITH campaign_artists_stats as (
                  SELECT
                    CAMPAIGN_ID as camp_id,
                    split_part(CAMPAIGN_NAME,' - ', 1) as artist_name
                  FROM "V_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" as report
                ),

                divided_by_x as (
                  SELECT
                    campaign_artists_stats.camp_id as campaign_id,
                    sts.VALUE as artist
                  FROM campaign_artists_stats, lateral SPLIT_TO_TABLE(campaign_artists_stats.artist_name,' x ') sts
                ),

                divided_by_plus as (
                  SELECT
                    campaign_artists_stats.camp_id as campaign_id,
                    sts.VALUE as artist
                  FROM campaign_artists_stats, lateral SPLIT_TO_TABLE(campaign_artists_stats.artist_name,' + ') sts
                ),

                divided_by_comma as (
                  SELECT
                    campaign_artists_stats.camp_id as campaign_id,
                    sts.VALUE as artist
                  FROM campaign_artists_stats, lateral SPLIT_TO_TABLE(campaign_artists_stats.artist_name,', ') sts
                ),

                age_stats as (
                  SELECT
                      report.CAMPAIGN_ID as campaign_id,
                      array_agg(DISTINCT report.CRITERIA) as ages
                  FROM "V_GOOGLE_AGE_RANGE_PERFORMANCE_REPORT" report
                  GROUP BY report.CAMPAIGN_ID
                ),
                gender_stats as (
                  SELECT
                      report.CAMPAIGN_ID as campaign_id,
                      array_agg(DISTINCT report.CRITERIA) as genders
                  FROM "V_GOOGLE_GENDER_PERFORMANCE_REPORT" report
                  GROUP BY report.CAMPAIGN_ID
                ),
                types_stats as (
                  SELECT
                      report.CAMPAIGN_ID as campaign_id,
                      array_agg(DISTINCT OBJECT_CONSTRUCT('main', report.AD_NETWORK_TYPE_1,
                                                          'secondary', report.AD_NETWORK_TYPE_2)) as types
                  FROM "V_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" report
                  GROUP BY report.CAMPAIGN_ID
                ),
                artists_stats as (
                  SELECT
                      report.CAMPAIGN_ID as campaign_id,
                      ARRAY_COMPACT(array_agg(DISTINCT OBJECT_CONSTRUCT('artist_id', artist.PARTICIP_NO,
                                                                        'member_id', member.PARTICIP_NO))) as artists
                  FROM "V_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" report
                  JOIN campaign_artists_stats ON report.CAMPAIGN_ID = campaign_artists_stats.camp_id
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" as artist
                    ON (campaign_artists_stats.artist_name = artist.PARTICIP_FULL_NAME)
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT_MEMBER" participant_member
                    ON artist.PARTICIP_NO = participant_member.PARTICIP_NO
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" member
                    ON participant_member.MEMBER_PARTICIP_NO = member.PARTICIP_NO
                  GROUP BY report.CAMPAIGN_ID
                ),
                alt_artists_stats_x as (
                  SELECT
                      divided_by_x.campaign_id as campaign_id,
                      ARRAY_COMPACT(array_agg(DISTINCT OBJECT_CONSTRUCT('artist_id', artist.PARTICIP_NO,
                                                                        'member_id', member.PARTICIP_NO))) as artists
                  FROM divided_by_x
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" as artist
                    ON (artist.PARTICIP_FULL_NAME = divided_by_x.artist)
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT_MEMBER" participant_member
                    ON artist.PARTICIP_NO = participant_member.PARTICIP_NO
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" member
                    ON participant_member.MEMBER_PARTICIP_NO = member.PARTICIP_NO
                  GROUP BY divided_by_x.CAMPAIGN_ID
                ),

                alt_artists_stats_comma as (
                  SELECT
                      divided_by_comma.campaign_id as campaign_id,
                      ARRAY_COMPACT(array_agg(DISTINCT OBJECT_CONSTRUCT('artist_id', artist.PARTICIP_NO,
                                                                        'member_id', member.PARTICIP_NO))) as artists
                  FROM divided_by_comma
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" as artist
                    ON (artist.PARTICIP_FULL_NAME = divided_by_comma.artist)
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT_MEMBER" participant_member
                    ON artist.PARTICIP_NO = participant_member.PARTICIP_NO
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" member
                    ON participant_member.MEMBER_PARTICIP_NO = member.PARTICIP_NO
                  GROUP BY divided_by_comma.CAMPAIGN_ID
                ),
                alt_artists_stats_plus as (
                  SELECT
                      divided_by_plus.campaign_id as campaign_id,
                      ARRAY_COMPACT(array_agg(DISTINCT OBJECT_CONSTRUCT('artist_id', artist.PARTICIP_NO,
                                                                        'member_id', member.PARTICIP_NO))) as artists
                  FROM divided_by_plus
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" as artist
                    ON (artist.PARTICIP_FULL_NAME = divided_by_plus.artist)
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT_MEMBER" participant_member
                    ON artist.PARTICIP_NO = participant_member.PARTICIP_NO
                  LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" member
                    ON participant_member.MEMBER_PARTICIP_NO = member.PARTICIP_NO
                  GROUP BY divided_by_plus.CAMPAIGN_ID
                ),
                campaign_aggregated_stats as (
                  SELECT
                    report.CAMPAIGN_ID as campaign_id,
                    SUM(report.COST) as spend,
                    array_agg(DISTINCT report.CUSTOMER_ID) as accounts,
                    array_agg(DISTINCT OBJECT_CONSTRUCT()) as url
                  FROM "V_GOOGLE_AD_PERFORMANCE_REPORT" report
                  GROUP BY report.CAMPAIGN_ID
                ),
                territories_stats as (
                  SELECT
                    report.CAMPAIGN_ID as campaign_id,
                    array_agg(DISTINCT report.COUNTRY_CRITERIA_ID) as territories
                  FROM "V_GOOGLE_GEO_PERFORMANCE_REPORT" report
                  GROUP BY report.CAMPAIGN_ID
                ),
                budget_stats as (
                  SELECT gbh.CAMPAIGN_ID, COALESCE(SUM(gbh.amount), 0) / 1000000  as budget
                  FROM V_GOOGLE_BUDGET_HISTORY gbh
                  WHERE gbh.CAMPAIGN_ID IS NOT NULL
                  GROUP BY gbh.CAMPAIGN_ID
                ),

                campaign_stats as (
                  SELECT DISTINCT
                    CAMPAIGN_ID as id,
                    LAST_VALUE(CAMPAIGN_NAME) OVER (PARTITION BY CAMPAIGN_ID ORDER BY DATE) as name,
                    LAST_VALUE(BIDDING_STRATEGY_TYPE) OVER (PARTITION BY CAMPAIGN_ID ORDER BY DATE) as objective,
                    LAST_VALUE(TOTAL_AMOUNT) OVER (PARTITION BY CAMPAIGN_ID ORDER BY DATE) as budget,
                    LAST_VALUE(START_DATE) OVER (PARTITION BY CAMPAIGN_ID ORDER BY DATE) as final_start_date,
                    LAST_VALUE(END_DATE) OVER (PARTITION BY CAMPAIGN_ID ORDER BY DATE) as final_end_date,
                    LAST_VALUE(CUSTOMER_ID) OVER (PARTITION BY CAMPAIGN_ID ORDER BY DATE) as account_id
                  FROM "V_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT"
                ),
                linkfire_links as (
                    SELECT DISTINCT
                        linkfire_campaign.CAMPAIGN_ID,
                        ARRAY_COMPACT(
                            array_agg(
                                DISTINCT OBJECT_CONSTRUCT(
                                    'linkfire_id', linkfire_link.LINKFIRE_LINK_ID,
                                    'linkfire_url', linkfire_link.LINKFIRE_LINK_URL
                                )
                            )
                        ) as links
                    FROM {linkfire_db_schema}."V_DIM_CAMPAIGN_LINK" linkfire_campaign
                    JOIN {linkfire_db_schema}."V_DIM_LINKFIRE_LINK" linkfire_link ON
                        linkfire_campaign.LINKFIRE_LINK_ID = linkfire_link.LINKFIRE_LINK_ID
                    WHERE linkfire_campaign.CAMPAIGN_ID != 0 AND linkfire_campaign.SOURCE = 'GOOGLE'
                    GROUP BY linkfire_campaign.CAMPAIGN_ID
                )
                SELECT DISTINCT
                    report.id,
                    report.name,
                    report.objective,
                    COALESCE(budget_stats.budget, report.budget),
                    types_stats.types,
                    gender_stats.genders,
                    age_stats.ages,
                    artists_stats.artists,
                    report.final_start_date,
                    report.final_end_date,
                    campaign_aggregated_stats.spend,
                    campaign_aggregated_stats.accounts,
                    campaign_aggregated_stats.url,
                    alt_artists_stats_x.artists as alt_artists_x,
                    alt_artists_stats_comma.artists as alt_artists_comma,
                    alt_artists_stats_plus.artists as alt_artists_plus,
                    territories_stats.territories,
                    linkfire_links.links
                FROM campaign_stats report
                LEFT OUTER JOIN campaign_aggregated_stats ON campaign_aggregated_stats.campaign_id = REPORT.ID
                LEFT OUTER JOIN alt_artists_stats_x ON alt_artists_stats_x.campaign_id = REPORT.ID
                LEFT OUTER JOIN alt_artists_stats_comma ON alt_artists_stats_comma.campaign_id = REPORT.ID
                LEFT OUTER JOIN alt_artists_stats_plus ON alt_artists_stats_plus.campaign_id = REPORT.ID
                LEFT OUTER JOIN age_stats ON age_stats.campaign_id = REPORT.ID
                LEFT OUTER JOIN gender_stats ON gender_stats.campaign_id = REPORT.ID
                LEFT OUTER JOIN types_stats ON types_stats.campaign_id = REPORT.ID
                LEFT OUTER JOIN artists_stats ON (artists_stats.campaign_id = REPORT.ID)
                LEFT OUTER JOIN territories_stats ON (territories_stats.campaign_id = REPORT.ID)
                LEFT OUTER JOIN budget_stats ON (budget_stats.campaign_id = REPORT.ID)
                LEFT OUTER JOIN linkfire_links ON (linkfire_links.CAMPAIGN_ID = REPORT.ID)
                WHERE report.final_start_date > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
                      AND report.account_id IN ({",".join(marketing_accounts_ids)})
                      AND ARRAY_SIZE(campaign_aggregated_stats.accounts) > 0
                ORDER BY REPORT.ID;
         """

    @staticmethod
    def fetch_ad_sets_query(marketing_accounts_ids: List[str]):
        return f"""
            WITH gender_stats as (
              SELECT
                  report.AD_GROUP_ID as ad_group_id,
                  array_agg(DISTINCT report.CRITERIA) as genders
              FROM "V_GOOGLE_GENDER_PERFORMANCE_REPORT" report
              GROUP BY report.AD_GROUP_ID
            ),

            age_stats as (
              SELECT
                  report.AD_GROUP_ID as ad_group_id,
                  array_agg(DISTINCT report.CRITERIA) as ages
              FROM "V_GOOGLE_AGE_RANGE_PERFORMANCE_REPORT" report
              GROUP BY report.AD_GROUP_ID
            ),
            types_stats as (
              SELECT
                  report.AD_GROUP_ID as ad_group_id,
                  array_agg(DISTINCT OBJECT_CONSTRUCT('main', report.AD_NETWORK_TYPE_1,
                                                      'secondary', report.AD_NETWORK_TYPE_2)) as types
              FROM "V_GOOGLE_AD_PERFORMANCE_REPORT" report
              GROUP BY report.AD_GROUP_ID
            ),
            ad_aggregated_stats as (
              SELECT
                report.AD_GROUP_ID as ad_group_id,
                SUM(report.COST) as spend,
                array_agg(DISTINCT OBJECT_CONSTRUCT()) as url
              FROM "V_GOOGLE_AD_PERFORMANCE_REPORT" report
              GROUP BY report.AD_GROUP_ID
            ),
            territories_stats as (
              SELECT
                report.AD_GROUP_ID as ad_group_id,
                array_agg(DISTINCT report.COUNTRY_CRITERIA_ID) as territories
              FROM "V_GOOGLE_GEO_PERFORMANCE_REPORT" report
              GROUP BY report.AD_GROUP_ID
            )
            SELECT DISTINCT
              ad_report.AD_GROUP_ID,
              ad_report.AD_GROUP_NAME,
              ad_report.CAMPAIGN_ID,
              campaign_report.START_DATE,
              campaign_report.END_DATE,
              campaign_report.BIDDING_STRATEGY_TYPE,
              age_stats.ages,
              gender_stats.genders,
              types_stats.types,
              ad_aggregated_stats.spend,
              ad_aggregated_stats.url,
              territories_stats.territories
            FROM "V_GOOGLE_AD_PERFORMANCE_REPORT" as ad_report
            JOIN "V_GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" as campaign_report
                ON campaign_report.CAMPAIGN_ID = ad_report.CAMPAIGN_ID
            LEFT OUTER JOIN age_stats ON age_stats.ad_group_id = ad_report.AD_GROUP_ID
            LEFT OUTER JOIN gender_stats ON gender_stats.ad_group_id = ad_report.AD_GROUP_ID
            LEFT OUTER JOIN types_stats ON types_stats.ad_group_id = ad_report.AD_GROUP_ID
            LEFT OUTER JOIN ad_aggregated_stats ON ad_aggregated_stats.ad_group_id = ad_report.AD_GROUP_ID
            LEFT OUTER JOIN territories_stats ON territories_stats.ad_group_id = ad_report.AD_GROUP_ID
            WHERE campaign_report.START_DATE > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
                  AND ad_report.CUSTOMER_ID IN ({",".join(marketing_accounts_ids)})
            ORDER BY ad_report.CAMPAIGN_ID, ad_report.AD_GROUP_ID
        """