from typing import Any, Dict, List

from ..base import PrepareBase
from ..contsants import NON_SIGNED_ARTIST_DIFF_TABLE, NON_SIGNED_ARTIST_TABLE
from ..excluded import EXCLUDED_ARTIST_LIST, EXCLUDED_LABELS_LIST, STRICT_EXCLUDED_LABELS_LIST


__all__ = ["Prepare"]


class Prepare(PrepareBase):
    table_name: str = NON_SIGNED_ARTIST_TABLE
    diff_table_name: str = NON_SIGNED_ARTIST_DIFF_TABLE

    query_params = {
        "STRICT_EXCLUDED_LABELS": STRICT_EXCLUDED_LABELS_LIST,
        "EXCLUDED_LABELS": f"({'|'.join(EXCLUDED_LABELS_LIST)})",
        "EXCLUDED_ARTIST": f"({'|'.join(EXCLUDED_ARTIST_LIST)})",
    }

    def swap_tables(self, table: str, table_alt: str) -> List[Dict[str, Any]]:
        result = super().swap_tables(table, table_alt)
        # build diff after table swapping
        self.build_diff()
        return result

    def build_diff(self):
        self.run_raw_query(
            f"""
            CREATE SEQUENCE IF NOT EXISTS DNA.DNA_PUBLIC.{self.diff_table_name}_ID_SEQ START = 1 INCREMENT = 1
        """,
            is_async=False,
        )
        self.run_raw_query(
            f"""
            CREATE TABLE IF NOT EXISTS DNA.DNA_PUBLIC.{self.diff_table_name}(
                id INTEGER DEFAULT DNA.DNA_PUBLIC.{self.diff_table_name}_ID_SEQ.NEXTVAL NOT NULL,
                ARTIST_ID INTEGER NOT NULL,\
                CREATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            )
        """,
            is_async=False,
        )
        self.run_raw_query(
            f"""
            INSERT INTO DNA.DNA_PUBLIC.{self.diff_table_name}(ARTIST_ID)
            SELECT TMP.ID FROM DNA.DNA_PUBLIC.{self.alt_table_name} AS TMP
            LEFT JOIN DNA.DNA_PUBLIC.{self.table_name} AS S
            ON TMP.ID = S.ID
            WHERE S.ID IS NULL;
        """,
            is_async=False,
        )

    query: str = """
        WITH FILTERED_LINKS AS (
          SELECT *
          FROM DELPHI_EXPLORATION.CHARTMETRIC.V_CM_URL
          WHERE
            TARGET  = 'cm_artist'
            AND
            ACTIVE  = TRUE
        ), BASIC_DATA AS (
            SELECT
                V_CM_ARTIST.ID AS ID,
                CAST(V_CM_ARTIST.NAME AS VARCHAR) AS NAME,
                V_SPOTIFY_ALBUM.LABEL AS LABEL,
                RELEASE_DATE,
                V_CM_ARTIST.CODE2,
                V_SPOTIFY_ARTIST.ARTWORK_URL AS ARTWORK_URL,
                LINK_TT.ACCOUNT_ID AS TT,
                LINK_YT.ACCOUNT_ID AS YT,
                IFNULL(IFNULL(LINK_SP.ACCOUNT_ID, REPLACE(LINK_SP.URL, 'https://open.spotify.com/artist/', '')), V_SPOTIFY.SPOTIFY_ARTIST_ID) AS SP,
                LINK_SC.ACCOUNT_ID AS SC
            FROM DELPHI_EXPLORATION.CHARTMETRIC.V_CM_ARTIST AS V_CM_ARTIST
            LEFT JOIN FILTERED_LINKS AS LINK_TT
                ON LINK_TT.TARGET_ID = V_CM_ARTIST.ID
                    AND LINK_TT.TYPE = 19
            LEFT JOIN FILTERED_LINKS AS LINK_YT
                ON LINK_YT.TARGET_ID = V_CM_ARTIST.ID
                    AND LINK_YT.TYPE = 3
            LEFT JOIN FILTERED_LINKS AS LINK_SP
                ON LINK_SP.TARGET_ID = V_CM_ARTIST.ID
                    AND LINK_SP.TYPE = 14
            LEFT JOIN FILTERED_LINKS AS LINK_SC
                ON LINK_SC.TARGET_ID = V_CM_ARTIST.ID
                    AND LINK_SC.TYPE = 7
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE_ARTIST ON V_YOUTUBE_ARTIST.CM_ARTIST = V_CM_ARTIST.ID
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD_ARTIST ON V_SOUNDCLOUD_ARTIST.CM_ARTIST = V_CM_ARTIST.ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS V_SPOTIFY_ARTIST
                ON V_SPOTIFY_ARTIST.CM_ARTIST = V_CM_ARTIST.ID
                AND (
                    V_SPOTIFY_ARTIST.POPULARITY_LATEST > 0
                    OR LINK_TT.TARGET_ID IS NOT NULL
                    OR LINK_YT.TARGET_ID IS NOT NULL
                    OR LINK_SP.TARGET_ID IS NOT NULL
                    OR LINK_SC.TARGET_ID IS NOT NULL
                    OR V_YOUTUBE_ARTIST.CM_ARTIST IS NOT NULL
                    OR V_SOUNDCLOUD_ARTIST.CM_ARTIST IS NOT NULL
              )
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS V_SPOTIFY
                ON V_SPOTIFY.SPOTIFY_ARTIST_ID  = V_SPOTIFY_ARTIST.SPOTIFY_ARTIST_ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ALBUM AS V_SPOTIFY_ALBUM
                ON V_SPOTIFY.SPOTIFY_ALBUM_ID  = V_SPOTIFY_ALBUM.SPOTIFY_ALBUM_ID
            LEFT JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_NON_ARTIST AS V_CM_NON_ARTIST
                ON V_CM_NON_ARTIST.ID = V_CM_ARTIST.ID
            WHERE
                V_CM_ARTIST.DATE_OF_DEATH IS NULL
                AND
                V_CM_NON_ARTIST.ID IS NULL
            QUALIFY ROW_NUMBER() OVER (PARTITION BY V_CM_ARTIST.ID ORDER BY V_SPOTIFY_ALBUM.RELEASE_DATE DESC, V_SPOTIFY_ALBUM.LABEL ASC NULLS LAST) = 1
        ), FILTERED_BASIC_DATA AS (
            SELECT
                *
            FROM BASIC_DATA
            WHERE (
              NOT RLIKE (
                LABEL,
                CONCAT(%(EXCLUDED_LABELS)s, '[^a-zA-Z0-9].*'),
                'i'
              )
              AND NOT RLIKE (
                LABEL,
                CONCAT('.*[^a-zA-Z0-9]', %(EXCLUDED_LABELS)s),
                'i'
              )
              AND NOT RLIKE (
                LABEL,
                CONCAT(%(EXCLUDED_LABELS)s),
                'i'
              )
              AND NOT RLIKE (
                LABEL,
                CONCAT('.*[^a-zA-Z0-9]', %(EXCLUDED_LABELS)s, '[^a-zA-Z0-9].*'),
                'i'
              )
            ) AND NOT RLIKE (
                NAME,
                CONCAT(%(EXCLUDED_ARTIST)s),
                'i'
              ) AND LABEL NOT IN (%(STRICT_EXCLUDED_LABELS)s)
        ), TIKTOK_DATA AS (
            SELECT
                FILTERED_BASIC_DATA.ID,
                IFNULL(V_TIKTOK_USER.VERIFIED, FALSE) AS IS_VERIFIED,
                GREATEST(IFNULL(V_TIKTOK_USER.FOLLOWERS_LATEST, 0), STAT.FOLLOWERS) AS FOLLOWERS
            FROM FILTERED_BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER AS V_TIKTOK_USER ON FILTERED_BASIC_DATA.TT = V_TIKTOK_USER.USER_ID
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_TIKTOK_USER_STAT AS STAT ON STAT.TIKTOK_USER = V_TIKTOK_USER.ID
            WHERE FILTERED_BASIC_DATA.TT IS NOT NULL
            QUALIFY ROW_NUMBER() OVER (PARTITION BY FILTERED_BASIC_DATA.ID ORDER BY STAT.TIMESTP DESC NULLS LAST) = 1
        ), BASIC_ARTIST_ISRC_DATA AS (
            SELECT
                FILTERED_BASIC_DATA.ID AS ID,
                V_CM_TRACK.ISRC
            FROM FILTERED_BASIC_DATA
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_L_CM_TRACK_CM_ARTIST AS V_L_CM_TRACK_CM_ARTIST
                ON FILTERED_BASIC_DATA.ID = V_L_CM_TRACK_CM_ARTIST.CM_ARTIST
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_CM_TRACK AS V_CM_TRACK
                ON V_L_CM_TRACK_CM_ARTIST.CM_TRACK = V_CM_TRACK.ID
            WHERE
                V_L_CM_TRACK_CM_ARTIST.ORDERING = 0
                AND
                (
                    FILTERED_BASIC_DATA.SC IS NULL
                    OR
                    FILTERED_BASIC_DATA.YT IS NULL
                )
            GROUP BY
                FILTERED_BASIC_DATA.ID,
                V_CM_TRACK.ISRC
        )
        , SOUNDCLOUD_ISRC_DATA AS (
            SELECT
              ID,
              ANY_VALUE(SC_ID) AS SC_ID
            FROM (
                  SELECT
                      BASIC_ARTIST_ISRC_DATA.ID,
                      MODE((PARSE_JSON(V_SOUNDCLOUD.SOUNDCLOUD_USER)):id) OVER (PARTITION BY BASIC_ARTIST_ISRC_DATA.ID) AS SC_ID
                  FROM BASIC_ARTIST_ISRC_DATA
                  JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SOUNDCLOUD AS V_SOUNDCLOUD
                      ON BASIC_ARTIST_ISRC_DATA.ISRC  = V_SOUNDCLOUD.ISRC
                  WHERE
                      V_SOUNDCLOUD.SOUNDCLOUD_USER IS NOT NULL
            )
            GROUP BY
              ID
            )
        , YOUTUBE_ISRC_DATA AS (
            SELECT
                ID,
                YOUTUBE_CHANNEL_ID
            FROM (
            SELECT
              BASIC_ARTIST_ISRC_DATA.ID,
              V_YOUTUBE.YOUTUBE_CHANNEL_ID,
              COUNT(*) AS COUNTER,
              MAX(V_YOUTUBE.VIEWS_LATEST) AS VIEWS_MAX
          FROM BASIC_ARTIST_ISRC_DATA
          JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
              ON BASIC_ARTIST_ISRC_DATA.ISRC  = V_YOUTUBE.ISRC
          WHERE
              V_YOUTUBE.YOUTUBE_CHANNEL_ID IS NOT NULL AND V_YOUTUBE.VALID = TRUE
            GROUP BY
                BASIC_ARTIST_ISRC_DATA.ID,
              V_YOUTUBE.YOUTUBE_CHANNEL_ID
            )
            QUALIFY ROW_NUMBER () OVER (PARTITION BY ID ORDER BY COUNTER DESC, VIEWS_MAX DESC, YOUTUBE_CHANNEL_ID) = 1
        )
        , YT_DATA AS (
            SELECT
                YOUTUBE_ISRC_DATA.ID,
                YOUTUBE_ISRC_DATA.YOUTUBE_CHANNEL_ID  AS YOUTUBE_CHANNEL_ID
            FROM YOUTUBE_ISRC_DATA
            JOIN (
            SELECT
                YOUTUBE_CHANNEL_ID,
                COUNT(*) AS C
            FROM YOUTUBE_ISRC_DATA
            GROUP BY
                YOUTUBE_CHANNEL_ID
            HAVING C = 1
            ) AS SINGLE_ARTIST
                ON YOUTUBE_ISRC_DATA.YOUTUBE_CHANNEL_ID = SINGLE_ARTIST.YOUTUBE_CHANNEL_ID
        )
        SELECT
            FILTERED_BASIC_DATA.ID,
            FILTERED_BASIC_DATA.NAME,
            FILTERED_BASIC_DATA.LABEL,
            FILTERED_BASIC_DATA.RELEASE_DATE,
            FILTERED_BASIC_DATA.CODE2,
            FILTERED_BASIC_DATA.ARTWORK_URL,
            FILTERED_BASIC_DATA.TT,
            IFNULL(FILTERED_BASIC_DATA.YT, YT_DATA.YOUTUBE_CHANNEL_ID) AS YT,
            FILTERED_BASIC_DATA.SP,
            IFNULL(FILTERED_BASIC_DATA.SC, SOUNDCLOUD_ISRC_DATA.SC_ID) AS SC
        FROM FILTERED_BASIC_DATA
        LEFT JOIN SOUNDCLOUD_ISRC_DATA
            ON FILTERED_BASIC_DATA.ID = SOUNDCLOUD_ISRC_DATA.ID
        LEFT JOIN YT_DATA
            ON FILTERED_BASIC_DATA.ID = YT_DATA.ID
        LEFT JOIN TIKTOK_DATA
            ON TIKTOK_DATA.ID = FILTERED_BASIC_DATA.ID
        WHERE
            TIKTOK_DATA.ID IS NULL
            OR (TIKTOK_DATA.FOLLOWERS < 1000 * 1000)
            OR (
                TIKTOK_DATA.FOLLOWERS >= 1000 * 1000
                AND TIKTOK_DATA.IS_VERIFIED = FALSE
            )
    """