from app_types import S3Path
from constants import FIRST_CHART_DAY, HISTORICAL_CHART_DAY, LAST_CHART_DAY

from ....preparation import ArtistGenre
from ...base import SearchExtractBase
from ...constants import ARTIST_YOUTUBE_RAW_DATA, N_A_VALUE


__all__ = ["Extract"]


class Extract(SearchExtractBase):
    depends_on = {ArtistGenre}
    raw_data_path: S3Path = ARTIST_YOUTUBE_RAW_DATA

    @property
    def query(self):
        return f"""
        WITH BASIC_DATA AS (
            SELECT
                T_NON_SIGNED_ARTIST.ID AS ID,
                ANY_VALUE(YT) AS ACCOUNT_ID,
                ANY_VALUE(NAME) AS NAME,
                ANY_VALUE(SP) AS SPOTIFY_ARTIST_ID,
                ANY_VALUE(CODE2) AS CODE2,
                ANY_VALUE(LABEL) AS LABEL,
                ANY_VALUE(ARTWORK_URL) AS ARTWORK_URL,
                ARRAY_AGG(DISTINCT IFNULL(NON_SIGNED_ARTIST_GENRE.GENRE, '{N_A_VALUE}')) AS GENRES
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
            LEFT JOIN DNA.DNA_PUBLIC.NON_SIGNED_ARTIST_GENRE ON NON_SIGNED_ARTIST_GENRE.ID = T_NON_SIGNED_ARTIST.ID
            WHERE
                T_NON_SIGNED_ARTIST.YT IS NOT NULL
            GROUP BY T_NON_SIGNED_ARTIST.ID
        )
        , MERGED_WITH_WL AS (
            SELECT
                V_YOUTUBE.YOUTUBE_CHANNEL_ID,
                cast(IFNULL(SUM(YT_VIDEOS.DURATION) / COUNT(YT_VIDEOS.YTID), 0) as integer) as AVG_DURATION,
                IFNULL(max(V_YOUTUBE.UPLOAD_DATE), max(YT_VIDEOS.PUBLISHED)) as LATEST_RELEASE_DATE
            FROM DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
            LEFT JOIN WHITELIST_REPLICA.MAIN.YT_VIDEOS AS YT_VIDEOS
                ON YT_VIDEOS.YTID = V_YOUTUBE.ID
            GROUP BY
                V_YOUTUBE.YOUTUBE_CHANNEL_ID
        )
        , SUBSCRIBERS_HISTORICAL_POINT AS (
            SELECT
                V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID AS ACCOUNT_ID,
                V_YOUTUBE_CHANNEL_STAT.SUBSCRIBERS AS SUBSCRIBERS,
                V_YOUTUBE_CHANNEL_STAT.TIMESTP::date AS DATE
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE_CHANNEL_STAT AS V_YOUTUBE_CHANNEL_STAT
                ON  BASIC_DATA.ACCOUNT_ID = V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID
            WHERE
                V_YOUTUBE_CHANNEL_STAT.SUBSCRIBERS IS NOT NULL
               AND V_YOUTUBE_CHANNEL_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID,
                V_YOUTUBE_CHANNEL_STAT.TIMESTP,
                V_YOUTUBE_CHANNEL_STAT.SUBSCRIBERS
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID ORDER BY V_YOUTUBE_CHANNEL_STAT.TIMESTP DESC) = 1
        )
        , VIEWS_HISTORICAL_POINT AS (
            SELECT
                V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID AS ACCOUNT_ID,
                V_YOUTUBE_CHANNEL_STAT.VIEWS AS VIEWS,
                V_YOUTUBE_CHANNEL_STAT.TIMESTP::date AS DATE
            FROM BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE_CHANNEL_STAT AS V_YOUTUBE_CHANNEL_STAT
                ON  BASIC_DATA.ACCOUNT_ID = V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID
            WHERE
                V_YOUTUBE_CHANNEL_STAT.VIEWS IS NOT NULL
               AND V_YOUTUBE_CHANNEL_STAT.TIMESTP <= {HISTORICAL_CHART_DAY}
            GROUP BY
                V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID,
                V_YOUTUBE_CHANNEL_STAT.TIMESTP,
                V_YOUTUBE_CHANNEL_STAT.VIEWS
            QUALIFY
                ROW_NUMBER() OVER (PARTITION BY V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID ORDER BY V_YOUTUBE_CHANNEL_STAT.TIMESTP DESC) = 1
        )
        , SUBSCRIBERS AS (
            SELECT
                ACCOUNT_ID,
                ARRAY_AGG(SUBSCRIBERS) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID AS ACCOUNT_ID,
                    V_YOUTUBE_CHANNEL_STAT.SUBSCRIBERS AS SUBSCRIBERS,
                    V_YOUTUBE_CHANNEL_STAT.TIMESTP AS DATE
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE_CHANNEL_STAT AS V_YOUTUBE_CHANNEL_STAT
                    ON  BASIC_DATA.ACCOUNT_ID = V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID
                WHERE
                    V_YOUTUBE_CHANNEL_STAT.SUBSCRIBERS IS NOT NULL
                    AND V_YOUTUBE_CHANNEL_STAT.TIMESTP <= {FIRST_CHART_DAY}
                   AND V_YOUTUBE_CHANNEL_STAT.TIMESTP >= {LAST_CHART_DAY}
                GROUP BY
                    V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID,
                    V_YOUTUBE_CHANNEL_STAT.TIMESTP,
                    V_YOUTUBE_CHANNEL_STAT.SUBSCRIBERS
            )
            GROUP BY
                ACCOUNT_ID
        )
        , VIEWS AS (
            SELECT
                ACCOUNT_ID,
                ARRAY_AGG(VIEWS) WITHIN GROUP (ORDER BY DATE) AS CHART,
                ARRAY_AGG(DATE) WITHIN GROUP (ORDER BY DATE) AS CHART_DATES
            FROM (
                SELECT
                    V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID AS ACCOUNT_ID,
                    V_YOUTUBE_CHANNEL_STAT.VIEWS AS VIEWS,
                    V_YOUTUBE_CHANNEL_STAT.TIMESTP AS DATE
                FROM BASIC_DATA
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE_CHANNEL_STAT AS V_YOUTUBE_CHANNEL_STAT
                    ON  BASIC_DATA.ACCOUNT_ID = V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID
                WHERE
                    V_YOUTUBE_CHANNEL_STAT.VIEWS IS NOT NULL
                    AND V_YOUTUBE_CHANNEL_STAT.TIMESTP <= {FIRST_CHART_DAY}
                   AND V_YOUTUBE_CHANNEL_STAT.TIMESTP >= {LAST_CHART_DAY}
                GROUP BY
                    V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID,
                    V_YOUTUBE_CHANNEL_STAT.TIMESTP,
                    V_YOUTUBE_CHANNEL_STAT.VIEWS
            )
            GROUP BY
                ACCOUNT_ID
        )
        SELECT
            BASIC_DATA.ID AS ID,
            ANY_VALUE(BASIC_DATA.NAME) AS NAME,
            IFNULL(ANY_VALUE(BASIC_DATA.CODE2), '{N_A_VALUE}') AS COUNTRY,
            ANY_VALUE(BASIC_DATA.GENRES) AS GENRES,
            ANY_VALUE(BASIC_DATA.LABEL) AS LABEL,
            ANY_VALUE(BASIC_DATA.ARTWORK_URL) AS ARTWORK_URL,
            ANY_VALUE(BASIC_DATA.ACCOUNT_ID) AS CHANNEL_ID,
            ANY_VALUE(BASIC_DATA.SPOTIFY_ARTIST_ID) AS SPOTIFY_ARTIST_ID,
            MAX(V_YOUTUBE_CHANNEL_STAT.VIDEOS) AS VIDEOS,
            IFNULL(MAX(V_YOUTUBE_CHANNEL_STAT.COMMENTS), 0) AS COMMENTS,
            MAX(MERGED_WITH_WL.AVG_DURATION) AS AVG_DURATION,
            TO_DATE(max(MERGED_WITH_WL.LATEST_RELEASE_DATE)) AS LATEST_RELEASE_DATE,
            ANY_VALUE(SUBSCRIBERS_HISTORICAL_POINT.DATE) AS SUBSCRIBERS_CHART_HISTORICAL_DATE,
            ANY_VALUE(SUBSCRIBERS_HISTORICAL_POINT.SUBSCRIBERS) AS SUBSCRIBERS_CHART_HISTORICAL_VALUE,
            ANY_VALUE(VIEWS_HISTORICAL_POINT.DATE) AS VIEWS_CHART_HISTORICAL_DATE,
            ANY_VALUE(VIEWS_HISTORICAL_POINT.VIEWS) AS VIEWS_CHART_HISTORICAL_VALUE,
            IFNULL(ANY_VALUE(SUBSCRIBERS.CHART), []) AS SUBSCRIBERS_CHART,
            IFNULL(ANY_VALUE(SUBSCRIBERS.CHART_DATES), []) AS SUBSCRIBERS_CHART_DATES,
            IFNULL(ANY_VALUE(VIEWS.CHART), []) AS VIEWS_CHART,
            IFNULL(ANY_VALUE(VIEWS.CHART_DATES), []) AS VIEWS_CHART_DATES
        FROM BASIC_DATA
        LEFT JOIN MERGED_WITH_WL
            ON BASIC_DATA.ACCOUNT_ID = MERGED_WITH_WL.YOUTUBE_CHANNEL_ID
        LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE_CHANNEL_STAT AS V_YOUTUBE_CHANNEL_STAT
            ON BASIC_DATA.ACCOUNT_ID = V_YOUTUBE_CHANNEL_STAT.ACCOUNT_ID
        LEFT JOIN SUBSCRIBERS_HISTORICAL_POINT
            ON BASIC_DATA.ACCOUNT_ID = SUBSCRIBERS_HISTORICAL_POINT.ACCOUNT_ID
        LEFT JOIN VIEWS_HISTORICAL_POINT
            ON BASIC_DATA.ACCOUNT_ID = VIEWS_HISTORICAL_POINT.ACCOUNT_ID
        LEFT JOIN SUBSCRIBERS
            ON BASIC_DATA.ACCOUNT_ID = SUBSCRIBERS.ACCOUNT_ID
        LEFT JOIN VIEWS
            ON BASIC_DATA.ACCOUNT_ID = VIEWS.ACCOUNT_ID
        GROUP BY BASIC_DATA.ID
        ORDER BY BASIC_DATA.ID
        """