from flask import request

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


class SoundCloudMyScoutings(ChartResource):
    def get_results_list(self, filters, username, limit, offset):
        params = {
            "username": username,
            "limit": limit,
            "offset": offset,
        }

        join_to_countries_filter = ""
        if filters.get("countries"):
            join_to_countries_filter = """ join sc_users u on u.scid = f.artist_scid and lower(u.data->>'country') 
                  in (select lower_sc_name from isrc_country_codes where iso2 = ANY(:countries)) """
            params["countries"] = filters.get('countries')

        join_to_name_search = ""
        if filters.get("nameStartsWith"):
            join_to_name_search = """ join lateral ( select 1 from sc_users u where u.scid = f.artist_scid and u.name ilike :nameQuery ) x on true """
            params['nameQuery'] = filters.get('nameStartsWith') + '%'

        sql = f"""
        select 
            artist_scid,
            max(seen_at) last_scouted,
            min(seen_at) first_scouted,
            count(distinct scout_scid) total_scouts,
            count(distinct seen_at) filter (where seen_at::date > current_date - 3) recent_scouts
        from sc_follows_and_likes f
        join users_sc_influencers i on i.scid = f.scout_scid and username = :username
        {join_to_countries_filter}
        {join_to_name_search}
        where seen_at > now() -  interval '120 days'
          and f.artist_scid <> 0
        group by 1
        order by 2 desc
        limit :limit
        offset :offset
        """

        if request.args.get('useMatrix'):
            params['limit'] = 5000
            params['offset'] = 0
            return {
                "matrix": [
                    [
                        f'sc/{scid}',
                        last_seen.timestamp()*1000,
                        first_seen.timestamp()*1000,
                        t,
                        r
                    ]
                    for (scid, last_seen, first_seen, t, r) in db.Session.execute(sql, params=params)
                ],
                "schema": ["artistKey", "last_scouted_at", "first_scouted_at", "total_scouts", "recent_scouts"]
            }

        ids_list = self.Session.execute(sql, params=params).fetchall()

        artists = self.fetch_artists("sc", [i[0] for i in ids_list])
        results = [
            {
                "scid": r[0],
                "artist": next(
                    (a["artist"] for a in artists if a["scid"] == r[0]), None
                ),
            }
            for r in ids_list
        ]
        return results


class InstagramMyScoutings(ChartResource):
    def get_results_list(self, filters, username, limit, offset):
        join_my_scouts_clause = """
        join users_in_influencers uii on uii.inid = if2.followed_by_inid and uii.username = :username
        """
        if filters.get("use_all_scouts"):
            join_my_scouts_clause = ""

        ids_list = self.Session.execute(
            """
select
  u.inid, max(if2.first_seen)
from in_artists u
join in_user iu on iu.inid = u.inid and iu.active and iu.not_found_at is null
join in_follows if2 ON u.inid = if2.followee_inid
{join_my_scouts_clause}
where COALESCE (iu.page_data->'entry_data'->'ProfilePage'->0->'user'->'followed_by'->>'count', '0')::int < 1000000
  and iu.active
  and iu.snooze_until <= CURRENT_DATE 
group by 1
order by 2 desc
limit :limit
offset :offset
            """.format(
                join_my_scouts_clause=join_my_scouts_clause
            ),
            params=dict(username=username, limit=limit, offset=offset),
        ).fetchall()
        artists = self.fetch_artists("in", [i[0] for i in ids_list])
        results = [
            {
                "inid": r[0],
                "artist": next(
                    (a["artist"] for a in artists if a["inid"] == r[0]), None
                ),
            }
            for r in ids_list
        ]
        return results