from flask import request

from tracker import db
from . import ChartResource, charts
from api import models


sample = {
    "nextApi": "/api/charts/YouTubeNew?offset=20&sample",
    "results": [
        {
            "video": {
                "as_of": "2018-07-31T03:00:00",
                "channel_name": "Proximity",
                "channel_ytid": "UC3ifTl5zKiCAhHIBQYcaTeg",
                "comment_count": 166,
                "dislike_count": 134,
                "favorite_count": 0,
                "first_seen": "2018-07-31T03:03:40.412719",
                "last_updated": "2018-07-31T03:03:46.545957",
                "like_count": 3066,
                "published": "2018-07-30T17:31:00+00:00",
                "tags": [],
                "title": "Drake / Billie Eilish - Hotline Bling (Flyboy Remix)",
                "video_ytid": "Wy34nEh9vdo",
                "view_count": 79854,
                "ytid": "Wy34nEh9vdo",
            },
            "video_ytid": "Wy34nEh9vdo",
        },
        {
            "video": {
                "as_of": "2018-07-31T03:00:00",
                "channel_name": "MrSuicideSheep",
                "channel_ytid": "UC5nc_ZtjKW1htCVZVRxlQAQ",
                "comment_count": 242,
                "dislike_count": 109,
                "favorite_count": 0,
                "first_seen": "2018-07-31T03:05:35.89236",
                "last_updated": "2018-07-31T03:05:37.443861",
                "like_count": 5299,
                "published": "2018-07-30T15:06:36+00:00",
                "tags": [],
                "title": "Raffaella - Bruce Willis (lyrics)",
                "video_ytid": "MCVi8Rxig90",
                "view_count": 61837,
                "ytid": "MCVi8Rxig90",
            },
            "video_ytid": "MCVi8Rxig90",
        },
        {
            "video": {
                "as_of": "2018-07-31T03:00:00",
                "channel_name": "Trap City",
                "channel_ytid": "UC65afEgL62PGFWXY7n6CUbA",
                "comment_count": 315,
                "dislike_count": 109,
                "favorite_count": 0,
                "first_seen": "2018-07-31T03:03:51.099561",
                "last_updated": "2018-07-31T03:03:56.487635",
                "like_count": 3995,
                "published": "2018-07-30T16:00:03+00:00",
                "tags": [],
                "title": "DROELOE - Looking Back (Lyrics)",
                "video_ytid": "Npo8mHGlDiw",
                "view_count": 56210,
                "ytid": "Npo8mHGlDiw",
            },
            "video_ytid": "Npo8mHGlDiw",
        },
        {
            "video": {
                "as_of": "2018-07-31T03:00:00",
                "channel_name": "xKito Music",
                "channel_ytid": "UCMOgdURr7d8pOVlc-alkfRg",
                "comment_count": 187,
                "dislike_count": 36,
                "favorite_count": 0,
                "first_seen": "2018-07-31T03:03:56.988184",
                "last_updated": "2018-07-31T03:04:00.587988",
                "like_count": 3198,
                "published": "2018-07-30T19:53:06+00:00",
                "tags": [],
                "title": "DROELOE - Looking Back",
                "video_ytid": "TmYZTivC5FI",
                "view_count": 29621,
                "ytid": "TmYZTivC5FI",
            },
            "video_ytid": "TmYZTivC5FI",
        },
    ],
}


class YouTubeNew(ChartResource):

    def post(self):
        limit = 20
        offset = int(request.args.get("offset") or "0")
        username = self.user.name


        if request.args.get("sample"):
            return sample

        filters = charts.get_filters(request, publishedDaysAgo=2)
        interval = '{} days'.format(filters.get('publishedDaysAgo'))

        ids_list = self.Session.execute(
            """
select
   v.ytid,
   (max(s.like_count)*10 + max(s.comment_count)*100 + max(s.view_count))::float / (extract(epoch from max(s.as_of) - min(v.published)))::float as views_per_second
from yt_videos v
join yt_channel_videos cv on cv.video_ytid = v.ytid
join users_yt_channels uch on uch.ytid = cv.channel_ytid and uch.username = :username
join yt_video_statistics s on s.video_ytid = v.ytid
where published > now() - INTERVAL :interval
group by v.ytid
order by 2 desc
            limit :limit
            offset :offset
            """,
            params=dict(username=username, limit=limit, offset=offset, interval=interval),
        ).fetchall()

        videos = list(models.youtube_videos([i[0] for i in ids_list]))

        next_api = "/api/charts/YouTubeNew?offset={}".format(offset + limit) if ids_list else None

        return {
            "nextApi": next_api,
            "results": [
                {
                    "video_ytid": r[0],
                    "video": next((v["video"] for v in videos if v["ytid"] == r[0]), None),
                }
                for r in ids_list
            ],
        }