from api import models
from api.api_errors import BadRequestError, NotFoundError
from . import execute_to_dicts
from . import AuthenticatedResource
from flask import request
from tracker import db
from sqlalchemy import text



class TikToks(AuthenticatedResource):

    def get(self):
        ttids = [
            r[0]
            for r in db.ROSession.execute(
                """select
                      ttid
                    from tiktok_sounds
                    where last_visited is not null
                      and has_trended
                      and not_found_at is null
                      
                    order by first_seen desc limit 500""",
            )
        ]

        return {"tiktoks": list(models.tiktok_sounds(ttids))}


class TikTokSounds(AuthenticatedResource):

    def get(self):
        ttids = request.args.get('ttids').split(',')

        return {'results': list(models.tiktok_sounds(ttids)) }


class TikTokScoutings(AuthenticatedResource):
    def post(self):
        sql = '''
        select
          s.ttid as "soundTtid",
          -- first(coalesce(s.web_url, 'https://www.tiktok.com/share/music/' || s.ttid)) as link,
          -- first(s.title) as title,
          -- first(s.author) as author,
          extract(epoch from min(tv.created_at)) as earliest_scout_video_created,
          current_date - min(tv.created_at)::date as earliest_scout_video_days_ago,
          extract(epoch from max(tv.created_at)) as last_scout_video_created,
          extract(epoch from min(tv.first_seen)) as first_video_seen,
          max(last_posts) as num_videos,
          count(distinct tu.unique_id) as num_unique_scouts,
          sum(last_digg_count) as top_video_likes,
          avg(last_digg_count) as avg_video_likes,
          count(*) as num_scout_videos,
          array_agg(distinct ti.category) as scout_categories
        from tiktok_sounds s
        join tiktok_videos tv on s.ttid = tv.sound_ttid
        join tiktok_users tu on tv.author_ttid = tu.ttid
        join users_tt_influencers ti on tu.unique_id = ti.unique_id and username = :username
        group by 1
        order by first_video_seen desc, avg(last_digg_count) desc
        limit 500
        '''
        cur = db.Session.execute(sql, params=dict(username=self.user.name))
        return {
            "schema": [c.name for c in cur.cursor.description],
            "matrix": cur.fetchall(),
        }