from datetime import timedelta
from typing import Any, Dict

from requests import Session  # type: ignore

from config import CMS_API_CONFIG
from steps.base import BaseStep
from steps.mixins.snowflake import SnowflakeMixin
from steps.validation.constants import DSP_TYPE_TIKTOK
from utils.external_clients.cms.client import CMSClient
from utils.external_clients.cms.client import Config as CMSConfig

from ..utils.cleanup import Cleanup


__all__ = ["DroppedArtistDataUpload"]


class DroppedArtistDataUpload(SnowflakeMixin, BaseStep):
    depends_on = {Cleanup}

    def _get_dropped_artist_data(self, created_at: str):
        query = f"""
            WITH DIFF_NON_SIGNED_ARTIST AS (
                SELECT
                    ARTIST.ARTIST_ID AS ARTIST_ID,
                    TO_CHAR(ANY_VALUE(ARTIST.CREATED_AT)) AS DROP_OFF_DATE,
                    CONCAT('https://alpha.dna.stream/artist/', ARTIST.ARTIST_ID) AS DNA_LINK,
                    CONCAT('https://app.chartmetric.com/artist?id=', ARTIST.ARTIST_ID) CHARTMETRIC_LINK
                FROM DNA.DNA_PUBLIC.DIFF_NOT_SIGNED_ARTIST AS ARTIST
                WHERE ARTIST.created_at > '{created_at}'
                GROUP BY ARTIST.ARTIST_ID
            ), SPOTIFY AS (
                SELECT
                    SP_ARTIST.SPOTIFY_ARTIST_ID AS SPOTIFY_ARTIST_ID,
                    SP_ARTIST.CM_ARTIST AS CM_ARTIST,
                    ANY_VALUE(SP_ARTIST.ARTIST_NAME) AS ARTIST_NAME,
                    SP_ARTIST.POPULARITY_LATEST AS POPULARITY_SCORE,
                    SP_ARTIST.FOLLOWERS_LATEST AS SPOTIFY_FOLLOWERS,
                    CONCAT('https://open.spotify.com/artist/', ANY_VALUE(SP_ARTIST.SPOTIFY_ARTIST_ID)) as SPOTIFY_LINK,
                    TO_CHAR(MAX(SP_ALBUM.RELEASE_DATE), 'YYYY-MM-DD') as RELEASE_DATE,
                    max(SP_ALBUM.LABEL) as LABEL,
                    ANY_VALUE(SP_ALBUM.ALBUM_TYPE) as ALBUM_TYPE
                FROM DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ARTIST AS SP_ARTIST
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY AS SPOTIFY ON SPOTIFY.SPOTIFY_ARTIST_ID = SP_ARTIST.SPOTIFY_ARTIST_ID
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_SPOTIFY_ALBUM AS SP_ALBUM ON SP_ALBUM.SPOTIFY_ALBUM_ID = SPOTIFY.SPOTIFY_ALBUM_ID
                GROUP BY
                    SP_ARTIST.CM_ARTIST,
                    SP_ARTIST.SPOTIFY_ARTIST_ID,
                    SP_ARTIST.POPULARITY_LATEST,
                    SP_ARTIST.FOLLOWERS_LATEST,
                    SPOTIFY.SPOTIFY_ARTIST_ID,
                    SP_ALBUM.ALBUM_TYPE,
                    SP_ALBUM.RELEASE_DATE,
                    SP_ALBUM.LABEL
                QUALIFY ROW_NUMBER() OVER (PARTITION BY SP_ARTIST.CM_ARTIST ORDER BY SP_ALBUM.RELEASE_DATE DESC, SP_ALBUM.LABEL ASC NULLS LAST) = 1
            ), TT AS (
                SELECT
                    CM.TARGET_ID AS TARGET_ID,
                    COUNT(TT.user_id) > 0 AS TIKTOK_VERIFIED,
                    TT.FOLLOWERS_LATEST AS TIKTOK_FOLLOWERS
              FROM DELPHI_EXPLORATION.CHARTMETRIC.V_CM_URL AS CM
              JOIN DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK_USER AS TT ON TT.USER_ID = CM.ACCOUNT_ID AND TT.VERIFIED = TRUE AND CM.TYPE = {DSP_TYPE_TIKTOK}
              GROUP BY CM.TARGET_ID, TT.FOLLOWERS_LATEST
            )
            SELECT
                DIFF_NON_SIGNED_ARTIST.DROP_OFF_DATE,
                DIFF_NON_SIGNED_ARTIST.DNA_LINK,
                DIFF_NON_SIGNED_ARTIST.CHARTMETRIC_LINK,
                SPOTIFY.ARTIST_NAME,
                SPOTIFY.POPULARITY_SCORE,
                SPOTIFY.SPOTIFY_FOLLOWERS,
                SPOTIFY.SPOTIFY_LINK,
                SPOTIFY.RELEASE_DATE,
                SPOTIFY.LABEL,
                SPOTIFY.ALBUM_TYPE,
                TT.TIKTOK_VERIFIED,
                TT.TIKTOK_FOLLOWERS
            FROM DIFF_NON_SIGNED_ARTIST
            LEFT JOIN SPOTIFY ON SPOTIFY.CM_ARTIST = DIFF_NON_SIGNED_ARTIST.ARTIST_ID
            LEFT JOIN TT ON TT.TARGET_ID = CAST(SPOTIFY.CM_ARTIST AS VARCHAR)
            ORDER BY DIFF_NON_SIGNED_ARTIST.DROP_OFF_DATE DESC
    """

        result = self.run_raw_query(query, is_async=False)
        return result

    def _pre_run(self):
        super()._pre_run()
        session = Session()
        self.cms_api = CMSClient(session=session, config=CMSConfig(**CMS_API_CONFIG))

    def process(self) -> Dict[str, Any]:
        dropped_artist_last_data = self.cms_api.get_dropped_artist_last_data()
        created_at = dropped_artist_last_data.get("created_at")

        # set the default `created_at` variable with `current date` minus 7 days before
        #  in case of weekly_report_last_data.get('created_at') is None
        if not created_at:
            today = self.key.timestamp.to_date()
            created_at = today - timedelta(days=7)

        dropped_artist_data_results = self._get_dropped_artist_data(created_at)

        result = {}
        if dropped_artist_data_results:
            result = self.cms_api.post_dropped_artist_last_data(dropped_artist_data_results)

        return result