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


class Query:
    @staticmethod
    def fetch_campaigns_query(marketing_accounts_ids: List[str]):
        return f"""
            WITH countries_stats AS (
                SELECT
                    CAMPAIGN_ID,
                    ARRAY_AGG(DISTINCT COUNTRY) as country_codes
                FROM "V_FACEBOOK_COUNTRY_DAILY_REPORT"
                GROUP BY CAMPAIGN_ID
            ),
            platforms_stats as (
                SELECT
                    CAMPAIGN_ID,
                    ARRAY_AGG(DISTINCT PUBLISHER_PLATFORM) as platforms
                FROM "V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT"
                GROUP BY CAMPAIGN_ID
            ),
            links_stats as (
                SELECT
                    CAMPAIGN_ID,
                    ARRAY_AGG(DISTINCT OBJECT_CONSTRUCT(
                        'final_urls', LINK_URL_ASSET_WEBSITE_URL,
                        'display_url', LINK_URL_ASSET_DISPLAY_URL
                    )) as urls
                FROM "V_FACEBOOK_LINK_URL_ASSET_DAILY_REPORT"
                GROUP BY CAMPAIGN_ID
            ),
            campaign_metadata as (
                SELECT
                    rep.CAMPAIGN_ID as campaign_id,
                    rep.ADSET_SOURCE_ID as adset_source_id,
                    div0(SUM(rep.daily_budget), 100) as budget,
                    to_date(MIN(rep.start_time)) as start_date,
                    to_date(MAX(rep.end_time)) as end_date
                FROM "V_FACEBOOK_AD_SET_HISTORY" as rep
                GROUP BY rep.CAMPAIGN_ID, rep.ADSET_SOURCE_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
                            FROM "V_FACEBOOK_AGE_GENDER_DAILY_REPORT" as report
                            WHERE report.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
                        )
                        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
            ),
            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 = 'FACEBOOK'
                GROUP BY linkfire_campaign.CAMPAIGN_ID
            )
            SELECT
                report.CAMPAIGN_ID,
                report.CAMPAIGN_NAME,
                report.ACCOUNT_ID,
                report.OBJECTIVE,
                SUM(report.spend) as spend,
                COALESCE(MIN(campaign_metadata.start_date), MIN(report.DATE)) as start_date,
                MAX(campaign_metadata.end_date) as end_date,
                platforms_report.platforms,
                ARRAY_AGG(DISTINCT report.GENDER) as genders,
                ARRAY_AGG(DISTINCT report.AGE) as ages,
                countries_report.country_codes,
                links_reports.urls,
                artist_stats.artists,
                COALESCE(SUM(DISTINCT campaign_metadata.budget), 0) as budget,
                linkfire_links.links
            FROM "V_FACEBOOK_AGE_GENDER_DAILY_REPORT" as report
            LEFT OUTER JOIN platforms_stats as platforms_report ON platforms_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT OUTER JOIN countries_stats as countries_report ON countries_report.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT OUTER JOIN links_stats as links_reports ON links_reports.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT OUTER JOIN artist_stats ON artist_stats.CAMPAIGN_ID = report.CAMPAIGN_ID
            LEFT OUTER JOIN campaign_metadata ON campaign_metadata.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND campaign_metadata.ADSET_SOURCE_ID = report.ADSET_ID
            LEFT OUTER JOIN linkfire_links ON linkfire_links.CAMPAIGN_ID = report.campaign_id
            WHERE report.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,
                     report.ACCOUNT_ID,
                     report.OBJECTIVE,
                     countries_report.country_codes,
                     platforms_report.platforms,
                     links_reports.urls,
                     artist_stats.artists,
                     linkfire_links.links
            ORDER BY report.CAMPAIGN_ID
        """

    @staticmethod
    def fetch_campaign_ads_query(marketing_accounts_ids: List[str]):
        return f"""
            WITH countries_stats AS (
                SELECT
                    ADSET_ID,
                    ARRAY_AGG(DISTINCT COUNTRY) as country_codes
                FROM "V_FACEBOOK_COUNTRY_DAILY_REPORT"
                GROUP BY ADSET_ID
            ),
            platforms_stats as (
                SELECT
                    ADSET_ID,
                    ARRAY_AGG(DISTINCT PUBLISHER_PLATFORM) as platforms
                FROM "V_FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT"
                GROUP BY ADSET_ID
            ),
            links_stats as (
                SELECT
                    ADSET_ID,
                    ARRAY_AGG(DISTINCT OBJECT_CONSTRUCT(
                        'final_urls', LINK_URL_ASSET_WEBSITE_URL,
                        'display_url', LINK_URL_ASSET_DISPLAY_URL
                    )) as urls
                FROM "V_FACEBOOK_LINK_URL_ASSET_DAILY_REPORT"
                GROUP BY ADSET_ID
            ),
            adset_history as (
                SELECT
                    rep.CAMPAIGN_ID as campaign_id,
                    rep.ADSET_SOURCE_ID as adset_source_id,
                    div0(SUM(rep.BUDGET_REMAINING), 100) as budget
                FROM "V_FACEBOOK_AD_SET_HISTORY" as rep
                GROUP BY rep.CAMPAIGN_ID, rep.ADSET_SOURCE_ID
            )
            SELECT
                report.ADSET_ID,
                report.CAMPAIGN_ID,
                report.ACCOUNT_ID,
                report.ADSET_NAME,
                report.OBJECTIVE,
                SUM(report.spend) as spend,
                MIN(report.DATE) as start_date,
                MAX(report.DATE) as end_date,
                ARRAY_AGG(DISTINCT report.GENDER) as genders,
                ARRAY_AGG(DISTINCT report.AGE) as ages,
                countries_report.country_codes,
                platforms_report.platforms,
                links_report.urls,
                COALESCE(adset_history.budget, 0) as budget
            FROM "V_FACEBOOK_AGE_GENDER_DAILY_REPORT" as report
            LEFT OUTER JOIN countries_stats as countries_report ON report.ADSET_ID = countries_report.ADSET_ID
            LEFT OUTER JOIN platforms_stats as platforms_report ON report.ADSET_ID = platforms_report.ADSET_ID
            LEFT OUTER JOIN links_stats as links_report ON report.ADSET_ID = links_report.ADSET_ID
            LEFT OUTER JOIN adset_history ON adset_history.CAMPAIGN_ID = report.CAMPAIGN_ID
                AND adset_history.ADSET_SOURCE_ID = report.ADSET_ID
            WHERE report.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.ADSET_ID,
                     report.CAMPAIGN_ID,
                     report.ADSET_NAME,
                     report.ACCOUNT_ID,
                     report.OBJECTIVE,
                     countries_report.country_codes,
                     platforms_report.platforms,
                     links_report.urls,
                     adset_history.budget
            ORDER BY report.CAMPAIGN_ID, report.ADSET_ID
        """