from ...base import BaseStep
from ...mixins import ESMixin, PGMixin, SnowflakeMixin
from ...search.constants import ARTIST_SEARCH_INDEX_NAME
from ...utils import Cleanup
from ..constants import DSP_TYPE_YOUTUBE, MAX_LIST_LEN_SF_QUERY, OK_STATUS, VALIDATION_ARTIST_YOUTUBE_VIDEO


__all__ = ["Validation"]


class Validation(SnowflakeMixin, ESMixin, PGMixin, BaseStep):
    depends_on = {Cleanup}
    base_index_name: str = ARTIST_SEARCH_INDEX_NAME

    @property
    def index_name(self) -> str:
        return f"{self.base_index_name}_{self.timestamp}"

    def _get_youtube_artist_ids_es(self) -> list:
        artist_ids_es: list = []
        body = {
            "query": {"bool": {"must": {"exists": {"field": "youtube"}}}},
            "_source": False,
        }
        res_es, _scroll_id = self.es_client.search(index=self.index_name, body=body)
        if res_es:
            artist_ids_es = artist_ids_es + [i["_id"] for i in res_es]
            while len(res_es):
                res_es, _scroll_id = self.es_client.scroll(scroll_id=_scroll_id)
                if res_es:
                    artist_ids_es = artist_ids_es + [i["_id"] for i in res_es]
        return artist_ids_es

    def _get_youtube_artist_ids_pg_aggregates(self) -> list:
        artist_ids_pg = []
        query = """
        select
            id as id
        from dna.artist_youtube_videos
        """
        res_pg = self.execute_select_query(query=query)
        if res_pg:
            artist_ids_pg = [i["id"] for i in res_pg]
        return artist_ids_pg

    def _check_number_video_values(self):
        """
        Number of youtube videos for artist should be not more then 6 and exists at least
        """
        query = """
        select
          *
        from
          (
            select
              *,
              jsonb_array_length(value) as value_array_length
            from
              dna.artist_youtube_videos
          ) as basic_data
        where
          basic_data.value_array_length = 0
          or basic_data.value_array_length > 6
          or basic_data.value is null
        """
        status = OK_STATUS
        res = self.execute_select_query(query=query)
        if res:
            status = VALIDATION_ARTIST_YOUTUBE_VIDEO["correct_number_video_values"]
            artist_ids = [i["id"] for i in res]
            status = status + ":" + ",".join([str(x) for x in artist_ids])
        return status

    def _check_artists_pg_only(self, artist_ids_pg: list, artist_ids_es: list):
        """
        Check if artists that exist only in PG aggregates have some youtube data (chart data etc)
        """
        status = OK_STATUS
        if artist_ids_pg and artist_ids_es:
            artists_pg_only = set(artist_ids_pg) - set(artist_ids_es)
            str_artists_pg_only = ",".join([str(x) for x in artists_pg_only])
            if len(artists_pg_only) > 0:
                self.logger.info(f"Number of artists youtube videos only in PG aggregates: {len(artists_pg_only)}")
            else:
                return status

            query = f"""
            SELECT
                DISTINCT T_NON_SIGNED_ARTIST.ID
            FROM  (
            SELECT
                T_NON_SIGNED_ARTIST.ID,
                T_NON_SIGNED_ARTIST.YT AS ACCOUNT_ID
            FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
            WHERE
                T_NON_SIGNED_ARTIST.YT IS NOT NULL
            ) AS T_NON_SIGNED_ARTIST
            JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
                ON T_NON_SIGNED_ARTIST.ACCOUNT_ID = V_YOUTUBE.YOUTUBE_CHANNEL_ID
            JOIN WHITELIST_REPLICA.MAIN.YT_VIDEOS AS YT_VIDEOS
                ON YT_VIDEOS.YTID = V_YOUTUBE.ID
            WHERE
                T_NON_SIGNED_ARTIST.ID IN ({str_artists_pg_only})
            """
            res = self.run_raw_query(query=query, is_async=False)
            if res:
                status = VALIDATION_ARTIST_YOUTUBE_VIDEO["missed_artist_youtube_data"]
                status = status + ": " + ",".join([str(x["id"]) for x in res])  # type: ignore
        return status

    def _check_artists_es_only(self, artist_ids_pg: list, artist_ids_es: list):
        """
        Check if artist have youtube charts - do we miss youtube video data?
        Case when we have artist youtube data, but don't have any youtube video on page
        """
        status = OK_STATUS
        if artist_ids_pg and artist_ids_es:
            artists_es_only = list(set(artist_ids_es) - set(artist_ids_pg))
            if len(artists_es_only) > 0:
                self.logger.info(f"Number of artists youtube only in ES index: {len(artists_es_only)}")
            else:
                return status

            start = 0
            end = MAX_LIST_LEN_SF_QUERY
            total_res: list = []
            for i in range(round(len(artists_es_only) / MAX_LIST_LEN_SF_QUERY) - 1):
                data_slice = artists_es_only[start:end]
                start = end
                end = start + MAX_LIST_LEN_SF_QUERY
                str_artists_es_only = ",".join([str(x) for x in data_slice])
                query = f"""
                SELECT
                    DISTINCT T_NON_SIGNED_ARTIST.ID
                FROM  (
                SELECT
                    T_NON_SIGNED_ARTIST.ID,
                    T_NON_SIGNED_ARTIST.YT AS ACCOUNT_ID
                FROM DNA.DNA_PUBLIC.T_NON_SIGNED_ARTIST AS T_NON_SIGNED_ARTIST
                WHERE
                    T_NON_SIGNED_ARTIST.YT IS NOT NULL
                ) AS T_NON_SIGNED_ARTIST
                JOIN DELPHI_EXPLORATION.CHARTMETRIC.V_YOUTUBE AS V_YOUTUBE
                    ON T_NON_SIGNED_ARTIST.ACCOUNT_ID = V_YOUTUBE.YOUTUBE_CHANNEL_ID
                WHERE
                    T_NON_SIGNED_ARTIST.ID IN ({str_artists_es_only})
                """
                res = self.run_raw_query(query=query, is_async=False)
                if res:
                    total_res = total_res + res  # type: ignore
            if total_res:
                status = VALIDATION_ARTIST_YOUTUBE_VIDEO["missed_youtube_video_data_for_artist"]
                status = status + ": " + ",".join([str(x["id"]) for x in total_res])
        return status

    def process(self):
        artist_ids_pg = self._get_youtube_artist_ids_pg_aggregates()
        artist_ids_es = self._get_youtube_artist_ids_es()
        result = {
            "correct_number_video_values": self._check_number_video_values(),
            "missed_artist_youtube_data": self._check_artists_pg_only(artist_ids_pg, artist_ids_es),
            "missed_youtube_video_data_for_artist": self._check_artists_es_only(artist_ids_pg, artist_ids_es),
        }
        if all(x == OK_STATUS for x in result.values()):
            self.logger.info(result)
        else:
            self.logger.error(result)
        return result