from html import unescape

from api import models
from api.api_errors import NotFoundError
from api.resources.charts import ChartResource
from . import AuthenticatedResource as Resource
from flask import request

from tracker import db


def _unescape_title(vid_dict: dict):
    vid_dict['title'] = unescape(vid_dict['title'])
    return vid_dict

class YouTube(ChartResource):

    def get(self):
        return {
            "dataMatrixColumns": [
                "view_count",
                "like_count",
                "dislike_count",
                "favorite_count",
                "comment_count",
            ],
            "videos": [_unescape_title(v) for v in db.execute_to_dicts("""
    select
         yt_videos.ytid,
         first(yt_videos.published) published,
         first(yt_videos.title) title,
         first(ch.name) as "channel_name",
         jsonb_agg_uniq(
            jsonb_build_array(
              to_stat_date(as_of::date),
              view_count,
              like_count,
              dislike_count,
              favorite_count,
              comment_count
            ) order by as_of
          ) as "dataMatrix"
          
        from yt_videos
        join yt_channel_videos cv on cv.video_ytid = yt_videos.ytid
        join yt_channels ch on ch.ytid = cv.channel_ytid
        join users_yt_channels uyc on uyc.ytid = ch.ytid and username = :username
        join yt_video_statistics stats on stats.video_ytid = yt_videos.ytid 
        and CURRENT_DATE  - stats.as_of::date in (0,1,2,3,5,6,10,11,12,30,31,32,60,61,62)
        group by 1  -- Potentially we get multple statistics on the same day
    
    
                """, params=dict(username=self.user.name), read_replica=True)],
        }

    def post(self):

        filters = self.get_filters(maxPublishedDaysAgo=365, minPublishedDaysAgo=0)

        and_channel_clause = ""
        if filters.get('channelIds'):
            and_channel_clause = " and ch.ytid = any(:channelIds) "

        sql_query = """
    select
     yt_videos.ytid,
     first(yt_videos.published) published,
     first(yt_videos.title) title,
     first(ch.name) as "channel_name",
     first(ch.ytid) as "channel_ytid",
     jsonb_agg_uniq(
        jsonb_build_array(
          to_stat_date(as_of::date),
          view_count,
          like_count,
          dislike_count,
          favorite_count,
          comment_count
        ) order by as_of
      ) as "dataMatrix"
      
    from yt_videos
    join yt_channel_videos cv on cv.video_ytid = yt_videos.ytid
    join yt_channels ch on ch.ytid = cv.channel_ytid
    join users_yt_channels uyc on uyc.ytid = ch.ytid and username = :username {and_channel_clause}
    join yt_video_statistics stats 
      on stats.video_ytid = yt_videos.ytid 
      and stats.as_of > now() - interval '33 days'
      and CURRENT_DATE  - stats.as_of::date in (0,1,2,3,5,6,10,11,12,30,31,32)
    where yt_videos.published 
        between (now() - (:maxPublishedDaysAgo * interval '1 day')) 
        and (now() - (:minPublishedDaysAgo * interval '1 day'))
    group by 1
                """.format(and_channel_clause=and_channel_clause)

        return {
            "dataMatrixColumns": [
                "view_count",
                "like_count",
                "dislike_count",
                "favorite_count",
                "comment_count",
            ],
            "videos": [_unescape_title(v) for v in db.execute_to_dicts(sql_query, params=dict(filters, username=self.user.name), read_replica=True)],
        }


class VideoDetail(Resource):

    def get(self, ytid):

        result = next(db.execute_to_dicts("""
select
  video_ytid as ytid,
  jsonb_agg_uniq_by_first_item(
    jsonb_build_array(
      to_stat_date(as_of::date),
      view_count,
      like_count,
      dislike_count,
      favorite_count,
      comment_count
    ) order by as_of
  ) as "dataMatrix"
from yt_video_statistics
where video_ytid = :ytid
group by 1;
        """, params=dict(ytid=ytid)), None)
        if not result:
            raise NotFoundError("No video found with ytid: {}".format(ytid))

        models.youtube_videos([ytid])


        return dict(
            next(models.youtube_videos([ytid]))['video'],
            dataMatrix=result['dataMatrix'] or []
        )