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


class Query:
    @staticmethod
    def fetch_campaigns_query(marketing_accounts_ids: List[str]):
        return f"""
            WITH age_gender_report AS (
                SELECT
                    rep.CAMPAIGN_ID as campaign_id,
                    rep.GENDER as gender,
                    rep.AGE as age
                FROM {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_AGE_GENDER_REPORT" as rep
                GROUP BY campaign_id, gender, age
            ),
            campaigns_history_data as (
              SELECT
                LAST_VALUE({ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_NAME) OVER (PARTITION BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID ORDER BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".RAW_UPDATED_AT) as campaign_name,
                LAST_VALUE({ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID) OVER (PARTITION BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID ORDER BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".RAW_UPDATED_AT) as external_id,
                LAST_VALUE(to_date({ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CREATE_TIME)) OVER (PARTITION BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID ORDER BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".RAW_UPDATED_AT) AS start_date,
                LAST_VALUE({ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".ACCOUNT_ID) OVER (PARTITION BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID ORDER BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".RAW_UPDATED_AT) AS marketing_account_id,
                LAST_VALUE({ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".OBJECTIVE_TYPE) OVER (PARTITION BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID ORDER BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".RAW_UPDATED_AT) AS objective,
                LAST_VALUE(COALESCE({ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".BUDGET, 0)) OVER (PARTITION BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".CAMPAIGN_ID ORDER BY {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY".RAW_UPDATED_AT) AS planned_budget
              FROM {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY"
            ),
            campaign_metadata AS (
                SELECT
                    rep.CAMPAIGN_ID as campaign_id,
                    to_date(MAX(rep.SCHEDULE_END_TIME)) as end_date
                FROM {ads_db_schema}."V_TIKTOK_AD_SET_HISTORY" as rep
                GROUP BY rep.CAMPAIGN_ID
            ),
            campaign_daily_report AS (
                SELECT
                    rep.CAMPAIGN_ID as campaign_id,
                    SUM(rep.SPEND) as spend
                FROM {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_REPORT_DAILY" as rep
                GROUP BY rep.CAMPAIGN_ID
            ),
            countries_stats AS (
                SELECT
                    rep.CAMPAIGN_ID,
                    ARRAY_AGG(DISTINCT rep.COUNTRY_CODE) as country_codes
                FROM {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_COUNTRY_REPORT" as rep
                GROUP BY rep.CAMPAIGN_ID
            ),
            artist_stats as (
            WITH campaign_names as (
                SELECT
                  campaign_id,
                  name,
                  regexp_replace(
                    trim(value),
                    '^([0-9.\\-:]+|[0-9,.]+[kK]|Apple Music|Spotify|Apple|Filtr|Sony|INT|OST|Latino|US|UK)$', ''
                ) as artist_name
                FROM (
                    WITH report AS (
                        SELECT
                          CAMPAIGN_ID as campaign_id,
                          CAMPAIGN_NAME as name,
                          to_date(report.CREATE_TIME) AS start_date
                        FROM {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY" as report
                        WHERE start_date > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
                              AND report.CAMPAIGN_NAME IS NOT NULL
                              AND  report.ACCOUNT_ID IN ({",".join(marketing_accounts_ids)})
                        GROUP BY report.CAMPAIGN_ID, report.CAMPAIGN_NAME, start_date
                    )
                    SELECT value, report.campaign_id, report.name
                    FROM report, lateral split_to_table(
                        regexp_replace( report.name, '( [\\*|+\\-] | [\\*|+\\-]|[\\*|+\\-] |, |_)', ' @del@ ' ), '@del@'
                    )
                    UNION
                    SELECT value, report.campaign_id,report.name
                    FROM report, lateral split_to_table(
                        regexp_replace(report.name, '( [\\*|x&+\\-] | [\\*|+\\-]|[\\*|+\\-] |, |_)', ' @del@ '), '@del@'
                    )
                ) as asa
                WHERE artist_name <> ''
                GROUP BY campaign_id, name, artist_name
                ORDER BY campaign_id
            )
            SELECT
                campaign_names.campaign_id,
                ARRAY_COMPACT(
                  array_agg(
                    DISTINCT OBJECT_CONSTRUCT(
                      'artist_id', member_main_artist.PARTICIP_NO,
                      'artist_name', member_main_artist.PARTICIP_FULL_NAME,
                      'member_id', member.PARTICIP_NO,
                      'member_name', member.PARTICIP_FULL_NAME
                    ))
                ) as artists
            FROM campaign_names
            LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" as main_artist
              ON LOWER(main_artist.PARTICIP_FULL_NAME) = LOWER(trim(campaign_names.artist_name))
            LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT_MEMBER" existence_of_member
              ON main_artist.PARTICIP_NO = existence_of_member.MEMBER_PARTICIP_NO AND
                existence_of_member.MEMBER_TYPE_NAME in (
                    'Primary', 'Original', 'Featured To Primary (with Artist in Title)'
                )
            LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" member_main_artist
                ON existence_of_member.MEMBER_PARTICIP_NO = member_main_artist.PARTICIP_NO
            LEFT OUTER JOIN {artists_db_schema}."V_GRAS_PARTICIPANT" as artist
                ON LOWER(artist.PARTICIP_FULL_NAME) = LOWER(trim(campaign_names.artist_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
            WHERE campaign_names.artist_name <> ''
            GROUP BY campaign_names.CAMPAIGN_ID
            ORDER BY campaign_names.CAMPAIGN_ID
          )
            SELECT
                campaign_history.CAMPAIGN_NAME as campaign_name,
                campaign_history.external_id as external_id,
                to_date(campaign_history.start_date) AS start_date,
                MAX(campaign_metadata.end_date) as end_date,
                campaign_history.marketing_account_id AS marketing_account_id,
                campaign_history.objective AS objective,
                COALESCE(campaign_daily_report.spend, 0) as budget_spend,
                campaign_history.planned_budget AS planned_budget,
                ARRAY_AGG(DISTINCT age_gender_report.gender) AS genders,
                countries_stats.country_codes as country_codes,
                ARRAY_AGG(DISTINCT age_gender_report.age) AS ages,
                artist_stats.artists

            FROM campaigns_history_data as campaign_history
            LEFT OUTER JOIN campaign_metadata ON campaign_metadata.campaign_id = campaign_history.external_id
            LEFT OUTER JOIN age_gender_report ON age_gender_report.campaign_id = campaign_history.external_id
            LEFT OUTER JOIN campaign_daily_report ON campaign_daily_report.campaign_id = campaign_history.external_id
            LEFT OUTER JOIN countries_stats ON countries_stats.campaign_id = campaign_history.external_id
            LEFT OUTER JOIN artist_stats ON artist_stats.campaign_id = campaign_history.external_id
            WHERE start_date > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
                AND campaign_name IS NOT NULL AND marketing_account_id IN ({",".join(marketing_accounts_ids)})
            GROUP BY campaign_history.EXTERNAL_ID,
                     campaign_history.CAMPAIGN_NAME,
                     campaign_history.MARKETING_ACCOUNT_ID,
                     campaign_history.START_DATE,
                     campaign_history.OBJECTIVE,
                     campaign_history.PLANNED_BUDGET,
                     artist_stats.artists,
                     countries_stats.country_codes,
                     campaign_daily_report.spend
            ORDER BY external_id;
        """

    @staticmethod
    def fetch_campaign_ads_query(marketing_accounts_ids: List[str]):
        return f"""
            WITH age_gender_report AS (
                SELECT
                    rep.AD_SET_ID as ad_set_id,
                    rep.GENDER as gender,
                    rep.AGE as age
                FROM {ads_db_schema}."V_TIKTOK_AD_SET_AGE_GENDER_REPORT" as rep
                GROUP BY ad_set_id, gender, age
            ),
            campaigns_history_data as (
                SELECT
                    rep.OBJECTIVE_TYPE AS objective,
                    rep.CAMPAIGN_ID as campaign_id
                FROM {ads_db_schema}."V_TIKTOK_ADS_CAMPAIGN_HISTORY" as rep
            ),
            ad_set_history_data as (
              SELECT
                LAST_VALUE(rep.AD_SET_ID) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) as ad_set_id,
                LAST_VALUE(rep.AD_SET_NAME) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) as ad_set_name,
                LAST_VALUE(rep.LANDING_PAGE_URL) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) as destination_link,
                LAST_VALUE(rep.CAMPAIGN_ID) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) as campaign_id,
                LAST_VALUE(to_date(rep.SCHEDULE_START_TIME)) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) AS start_date,
                LAST_VALUE(to_date(rep.SCHEDULE_END_TIME)) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) AS end_date,
                LAST_VALUE(rep.ACCOUNT_ID) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) AS marketing_account_id,
                LAST_VALUE(rep.BUDGET) OVER (PARTITION BY rep.AD_SET_ID ORDER BY rep.RAW_UPDATED_AT) AS planned_budget
              FROM {ads_db_schema}."V_TIKTOK_AD_SET_HISTORY" as rep
            ),
            ad_set_daily_report AS (
                SELECT
                    rep.AD_SET_ID as ad_set_id,
                    rep.CAMPAIGN_ID,
                    SUM(rep.SPEND) as spend
                FROM {ads_db_schema}."V_TIKTOK_AD_SET_REPORT_DAILY" as rep
                GROUP BY rep.AD_SET_ID, CAMPAIGN_ID
            ),
            countries_stats AS (
                SELECT
                     rep.AD_SET_ID as ad_set_id,
                    ARRAY_AGG(DISTINCT rep.COUNTRY_CODE) as country_codes
                FROM {ads_db_schema}."V_TIKTOK_AD_SET_COUNTRY_REPORT" as rep
                GROUP BY ad_set_id
            )
            SELECT
                ad_set_history.ad_set_id AS ad_set_id,
                ad_set_history.ad_set_name AS ad_set_name,
                ad_set_history.campaign_id AS campaign_id,
                to_date(ad_set_history.start_date) AS start_date,
                to_date(ad_set_history.end_date) AS end_date,
                ad_set_history.destination_link AS destination_link,
                campaigns_history_data.objective as objective,
                COALESCE(ad_set_history.planned_budget, 0) AS planned_budget,
                COALESCE(ad_set_daily_report.spend, 0) as budget_spend,
                ARRAY_AGG(DISTINCT age_gender_report.gender) AS genders,
                countries_stats.country_codes as country_codes,
                ARRAY_AGG(DISTINCT age_gender_report.age) AS ages
            FROM ad_set_history_data as ad_set_history
            LEFT OUTER JOIN ad_set_daily_report ON ad_set_daily_report.ad_set_id = ad_set_history.ad_set_id
            LEFT OUTER JOIN age_gender_report ON age_gender_report.ad_set_id = ad_set_history.ad_set_id
            LEFT OUTER JOIN countries_stats ON countries_stats.ad_set_id = ad_set_history.ad_set_id
            LEFT OUTER JOIN campaigns_history_data ON campaigns_history_data.campaign_id = ad_set_history.campaign_id
            WHERE start_date > '{config.EARLIEST_SNOWFLAKE_DATA_FETCH_DATE}'
                 AND marketing_account_id IN ({",".join(marketing_accounts_ids)})
            GROUP BY ad_set_history.ad_set_id,
                     ad_set_history.ad_set_name,
                     ad_set_history.campaign_id,
                     start_date,
                     end_date,
                     objective,
                     ad_set_history.planned_budget,
                     countries_stats.country_codes,
                     ad_set_daily_report.spend,
                     ad_set_history.destination_link
            ORDER BY ad_set_history.ad_set_id;
"""