import time

from api.resources import AuthenticatedResource
from api import models, api_errors

from flask import request

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


def get_filters(request, **defaults):
    defaults = dict(
        {
            "publishedDaysAgo": 10,
            "minPlays": 10000,
            "isScouted": True,
            "maxFollowers": 1000000,
            "countries": None,
            "locationRegex": None,
        },
        **defaults
    )

    if not request.get_data():
        # get_json will fail on empty body, I don't want to
        return defaults

    return dict(defaults, **((request.json or {}).get("filters") or {}))


class SoundCloudLocation(ChartResource):

    def get_results_list(self, filters, username, limit, offset):
        if not filters.get("locationRegex"):
            raise api_errors.BadRequestError('Requires "locationRegex"')

        ids_list_sql = """
with
tracks as (
  select
    t.scid as track_scid,
    u.scid as artist_scid
  from sc_tracks t
  join sc_users u on t.artist_scid = u.scid
  where t.scid between 465e6 and 600e6
    and u.data->>'city' ~* :locationRegex
    and u.active
    and u.snooze_until <= CURRENT_DATE 
),

scored as (
  select
    track_scid,
    hockey(
      array_agg(s.playback_count::float order by as_of)::float[],
      15,
      25
    ) as plays
  from sc_track_stats s
  where s.track_scid = any( (select array_agg(track_scid) from tracks)::integer[] )
    and s.as_of > current_date - interval '30 days'
  group by 1
    having count(*) > 20
      and max(playback_count) > 5000
)

select
  track_scid, plays
from scored
where plays[2] > 0
order by plays[2] desc
limit :limit
offset :offset
            """

        ids_list = self.Session.execute(
            ids_list_sql, params=dict(filters, limit=limit, offset=offset)
        ).fetchall()

        tracks = list(models.soundcloud_tracks([i[0] for i in ids_list]))
        artist_scids = [t["artist_scid"] for t in tracks]

        artists = self.fetch_artists("sc", artist_scids)

        def render_obj(idrow):
            track = next((t["track"] for t in tracks if t["scid"] == idrow[0]))
            return {
                "track_scid": idrow[0],
                "track": track,
                "artist": next(
                    (a["artist"] for a in artists if a["scid"] == track["artist_scid"]), None
                ),
            }

        return [render_obj(r) for r in ids_list]


class SoundCloudNewTracks(ChartResource):

    def post(self):
        limit = 20
        offset = int(request.args.get("offset") or "0")
        filters = get_filters(request)

        extra_clauses = {
            "and_is_scouted_clause": " and u.scid <> 0 and t.artist_scid <> 0 "
            if filters.get("isScouted")
            else " and u.scid = 0 ",
            "and_country_clause": "",
        }
        if filters.get("countries"):
            extra_clauses[
                "and_country_clause"
            ] = (
                " and lower(u.data->>'country') in (select lower_sc_name from isrc_country_codes where iso2 = ANY(:countries)) "
            )

        ids_list_sql = """
select
  distinct
  t.scid,
  t.artist_scid,
  coalesce(t.last_playback_count, 0)
from sc_tracks t
join sc_users u ON t.artist_scid = u.scid
where u.active
  and u.snooze_until <= CURRENT_DATE 
  and coalesce(u.data ->> 'followers_count', '0')::int < :maxFollowers
  and u.first_seen > '2015-01-01'
  {and_country_clause}
  and t.created_at::date > current_date - :publishedDaysAgo
  and t.last_playback_count > :minPlays
  {and_is_scouted_clause}
order by 3 desc
limit :limit
offset :offset
        """.format(
            **extra_clauses
        )

        sql_params = filters
        ids_list = self.Session.execute(
            ids_list_sql, params=dict(sql_params, limit=limit, offset=offset)
        ).fetchall()

        artists = self.fetch_artists("sc", [i[1] for i in ids_list])
        tracks = list(models.soundcloud_tracks([i[0] for i in ids_list]))

        return self.render_json(
            [
                {
                    "track_scid": r[0],
                    "artist": next((a["artist"] for a in artists if a["scid"] == r[1]), None),
                    "track": next((t["track"] for t in tracks if t["scid"] == r[0]), None),
                }
                for r in ids_list
            ],
            limit,
            offset,
        )


class InstagramFollowerJump(ChartResource):

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

        ids_list = self._get_inids(limit, offset)

        artists =  self.fetch_artists("in", [i[0] for i in ids_list])

        return self.render_json(
            [
                {
                    "inid": r[0],
                    "context": {"pctChange": r[2]},
                    "artist": next((a["artist"] for a in artists if a["inid"] == r[0]), None),
                }
                for r in ids_list
            ],
            limit,
            offset,
        )

    def _get_inids(self, limit, offset):
        return self.Session.execute(
            """
            select
              inid,
              min(followed_by_count) as min_fols,
              (first(followed_by_count order by as_of desc) - last(followed_by_count order by as_of desc))::float / (last(followed_by_count order by as_of desc) + 1)::float as pct_change
            from in_stats
            join in_artists using (inid)
            join in_user u using (inid)
            where as_of > current_date - interval '10 days'
              and u.active
              and u.snooze_until < CURRENT_DATE 
            group by 1 having min(followed_by_count) between 100 and 50000
            order by 3 desc
        limit :limit
        offset :offset
        """,
            params=dict(limit=limit, offset=offset),
        ).fetchall()


class Analyzer(InstagramFollowerJump):
    '''"recentlyReachedFollowers":0,"triangleDaysTotal":28,"triangleDaysBack":7},"sort":"firstSeen"'''

    def get_results_list(self, filters, username, limit, offset):
        ids_list = self.Session.execute(
            """
with
artist_with_scores as (
  select
  s.inid,
    hockey(array_agg(s.followed_by_count order by as_of), :triangleDaysBack, :triangleDaysTotal) scores
  from in_stats s
  join in_artists a on a.inid = s.inid
    where s.as_of > now() -  make_interval(days := :triangleDaysTotal + 5)
  group by 1 having count(*) > :triangleDaysTotal + 5
),

alerts as (
  select
    inid,
    scores
  from artist_with_scores
  where scores[1] > 0
    and scores[2] > scores[1]
    and scored[2]
  limit 500
)

select
  'https://www.instagram.com/'||u.inid,
  'https://bot.whtlst.in/#/artist/in/'||u.inid,
  u.user_data->>'full_name',
  u.user_data->>'biography'
from alerts
join vw_in_users u on u.inid = alerts.inid
order by scores[1] desc;""",
            params=dict(filters, limit=limit, offset=offset),
        ).fetchall()
        artists = self.fetch_artists("in", [i[0] for i in ids_list])

        def render_obj(r):
            return {
                "inid": r[0],
                "artist": next((a["artist"] for a in artists if a["inid"] == r[0]), None),
            }

        return [render_obj(r) for r in ids_list]


_PRESET_PLAYLISTS = {
    "UK": [
        "37i9dQZF1DX4JAvHpjipBk",
        "37i9dQZF1DX4W3aJJYCDfV",
        "37i9dQZEVXbqfk27YJKGdX",
        "37i9dQZF1DXcecv7ESbOPu",
        "37i9dQZF1DXb5BKLTO7ULa",
        "37i9dQZF1DWV3RrjH1jDkx",
        "3XXTVlRDc0eVMfAMZDxEhV",
        "37i9dQZF1DXa41CMuUARjl",
        "6zj2ZyaP4WLzE5AhilyeX0",
        "37i9dQZF1DWT2SPAYawYcO",
        "3rl2wHSooZj57G9zdMTfx0",
        "3g1FjZrN3Vg0nknlejTCV0",
        "6OWZjYNk2mDHFk9FVT6k78",
        "33G9VPNlmTCbhS5TdqxiGu",
        "0OW9wODqtbU4WnTNOcQASd",
        "6Fzw9Wz6uLnwbCNbTZ16Pb",
        "67uxYp1LZKSO4DiykmOfdE",
        "1Xf43rER49rVtWjinjP5fw",
        "0JyJ4Nt68jBV0sJUErDlmx",
        "2VFJHsdLlerM22TtaPagYa",
        "37i9dQZF1DXbpmT3HUTsZm",
        "37i9dQZF1DX8tZsk68tuDw",
        "10S981NoNhgn9tdUq9jNFE",
        "2k2VFAQUBw77Rv7niYMYIw",
        "1a5ECwY7KHnYSoAjOct2By",
        "37i9dQZF1DX4dyzvuaRJ0n",
        "5vlLu52OOsgIBsFwR9Sn2V",
        "5s7cNVeGfehrRfCatNN43P",
        "37i9dQZF1DX6PKX5dyBKeq",
        "37i9dQZF1DX8vwRmUsEIMT",
        "37i9dQZF1DWT2jS7NwYPVI",
        "37i9dQZF1DWZryfp6NSvtz",
        "6WWgGke7FrcjW8ZOvPGQaI",
        "4eDSwVknkNyDJGFrUHcDrS",
        "65xSncKQzG6Suseh5gfYP1",
        "1Hu8ON3GQpC1HxeYqyzD1M",
        "37i9dQZF1DXarRysLJmuju",
        "37i9dQZF1DWUa8ZRTfalHk",
        "37i9dQZF1DWYMfG0Phlxx8",
        "73iSQPz35bOpvQouxkAT6K",
        "37i9dQZF1DXdeMORbC1XNa",
        "37i9dQZF1DX5y8xoSWyhcz",
        "37i9dQZF1DX59ogDi1Z2XL",
        "37i9dQZF1DWVlLVXKTOAYa",
        "37i9dQZF1DWXetIPpoiRSb",
        "37i9dQZF1DWWEcRhUVtL8n",
        "5359l8Co8qztllR0Mxk4Zv",
        "37i9dQZF1DXcDoDDetPsEg",
        "37i9dQZF1DWSVtp02hITpN",
        "37i9dQZF1DXa8NOEUWPn9W",
        "0eeKZp7perkDfgJQt4pc6I",
        "37i9dQZF1DWWjGdmeTyeJ6",
        "37i9dQZF1DWYkaDif7Ztbp",
        "37i9dQZF1DXboDblu798Pk",
        "37i9dQZF1DXcRXFNfZr7Tp",
        "37i9dQZF1DXdIpacQDPDV5",
        "37i9dQZF1DWUSKuTscFFGY",
        "37i9dQZF1DWT1y71ZcMPe5",
        "37i9dQZF1DWY4lFlS4Pnso",
        "37i9dQZF1DX3rxVfibe1L0",
        "37i9dQZF1DX2YSAZIuAiB1",
        "37i9dQZF1DX1lp03JVa0o6",
        "37i9dQZF1DWTMYgB8TqtmR",
        "6AvLNaeJ7qeF1Ur1kNoiXT",
        "37i9dQZF1DWY5Nosj13GLt",
        "37i9dQZF1DX60OAKjsWlA2",
        "37i9dQZF1DXcBWIGoYBM5M",
        "37i9dQZF1DX889U0CL85jj",
        "37i9dQZF1DX942d1NzGgzk",
        "37i9dQZF1DX5CdVP4rz81C",
        "37i9dQZF1DXca8AyWK6Y7g",
        "37i9dQZF1DXcxvFzl58uP7",
        "3aG2GZfk5wE6MT9yy5wtRs",
        "37i9dQZF1DWXXs9GFYnvLB",
        "37i9dQZF1DX4a0nQYnltiQ",
        "37i9dQZF1DWSWyJydK4fTU",
        "37i9dQZF1DX2vTOtsQ5Isl",
        "37i9dQZF1DWX0o6sD1a6P5",
        "37i9dQZF1DXcZDD7cfEKhW",
        "37i9dQZF1DX0UrRvztWcAU",
        "37i9dQZF1DWSJHnPb1f0X3",
        "4AB1J5VRo8y3oc5QACLzRg",
        "37i9dQZF1DXdPec7aLTmlC",
        "5LwGTy1gOEG8SnvZb2s7pY",
        "37i9dQZF1DWT6MhXz0jw61",
        "37i9dQZF1DX843Qf4lrFtZ",
        "37i9dQZF1DX4WYpdgoIcn6",
        "37i9dQZF1DXaXB8fQg7xif",
        "37i9dQZF1DWWqNV5cS50j6",
        "37i9dQZF1DWVYHdtUb7Wil",
        "37i9dQZF1DWYBO1MoTDhZI",
        "37i9dQZF1DX50QitC6Oqtn",
        "37i9dQZF1DXdwmD5Q7Gxah",
        "37i9dQZF1DX5uokaTN4FTR",
        "37i9dQZF1DX2SK4ytI2KAZ",
        "37i9dQZF1DWSqPHam7LOqC",
        "6LY8RIt0Wg6IkpJBtxP2xu",
        "33yjWFG5onxZu3HdIzO1Zu",
        "7b9XqnXw5J47tmn0Y0IZeW",
        "37i9dQZEVXbLnolsZ8PSNw",
        "37i9dQZF1DX4SO57lOJWRB",
        "534FHdUH9qaG1WxiqsOnb9",
        "59ZrWq6NDDZXQJcUHnkruk",
        "03n3Y7ugrETGIdODxD1iFe",
    ],
    "US": [
        "37i9dQZF1DX4JAvHpjipBk",
        "37i9dQZF1DX5DfG8gQdC3F",
        "37i9dQZF1DX4W3aJJYCDfV",
        "37i9dQZEVXbqfk27YJKGdX",
        "37i9dQZF1DXcecv7ESbOPu",
        "37i9dQZF1DWT2SPAYawYcO",
        "37i9dQZF1DWXJfnUiYjUKT",
        "2yK0S7avadD3cB4i2Y2qVr",
        "4ei0MXEGNtYHBRLHZdYoPA",
        "3rl2wHSooZj57G9zdMTfx0",
        "3g1FjZrN3Vg0nknlejTCV0",
        "6OWZjYNk2mDHFk9FVT6k78",
        "33G9VPNlmTCbhS5TdqxiGu",
        "0OW9wODqtbU4WnTNOcQASd",
        "6Fzw9Wz6uLnwbCNbTZ16Pb",
        "67uxYp1LZKSO4DiykmOfdE",
        "1Xf43rER49rVtWjinjP5fw",
        "0JyJ4Nt68jBV0sJUErDlmx",
        "1gtJKSNHyZXfhA5pCw1s3K",
        "37i9dQZF1DXbpmT3HUTsZm",
        "37i9dQZF1DX8tZsk68tuDw",
        "10S981NoNhgn9tdUq9jNFE",
        "2k2VFAQUBw77Rv7niYMYIw",
        "37i9dQZF1DX1N5uK98ms5p",
        "1a5ECwY7KHnYSoAjOct2By",
        "79bqVRd4ZsTFw0SfrSLi7x",
        "5s7cNVeGfehrRfCatNN43P",
        "37i9dQZF1DWT2jS7NwYPVI",
        "37i9dQZF1DWZryfp6NSvtz",
        "6WWgGke7FrcjW8ZOvPGQaI",
        "4eDSwVknkNyDJGFrUHcDrS",
        "65xSncKQzG6Suseh5gfYP1",
        "37i9dQZF1DX2RxBh64BHjQ",
        "37i9dQZF1DXarRysLJmuju",
        "37i9dQZF1DWY6tYEFs22tT",
        "37i9dQZF1DWUa8ZRTfalHk",
        "37i9dQZF1DWYMfG0Phlxx8",
        "73iSQPz35bOpvQouxkAT6K",
        "37i9dQZF1DX5y8xoSWyhcz",
        "2X5BPknxexv0e7DdDKUj0C",
        "5UuPeWDR2I8a8pORvW9vmr",
        "37i9dQZF1DWVlLVXKTOAYa",
        "37i9dQZF1DWU4xkXueiKGW",
        "37i9dQZF1DWXetIPpoiRSb",
        "37i9dQZF1DWWEcRhUVtL8n",
        "5359l8Co8qztllR0Mxk4Zv",
        "37i9dQZF1DWSVtp02hITpN",
        "37i9dQZF1DX2WkIBRaChxW",
        "37i9dQZF1DWYs83FtTMQFw",
        "0eeKZp7perkDfgJQt4pc6I",
        "37i9dQZF1DWWjGdmeTyeJ6",
        "37i9dQZF1DXb8e91bM8R51",
        "37i9dQZF1DXboDblu798Pk",
        "37i9dQZF1DXcRXFNfZr7Tp",
        "37i9dQZF1DWXT8uSSn6PRy",
        "37i9dQZF1DWUSKuTscFFGY",
        "37i9dQZF1DWT1y71ZcMPe5",
        "37i9dQZF1DX0sDai2F5jCQ",
        "37i9dQZF1DX3rxVfibe1L0",
        "37i9dQZF1DX2YSAZIuAiB1",
        "2imbTn8YAeXVwvBhOyXUR0",
        "37i9dQZF1DX1lp03JVa0o6",
        "37i9dQZF1DWTMYgB8TqtmR",
        "37i9dQZF1DXa49JU4zzjb9",
        "37i9dQZF1DX0XUsuxWHRQd",
        "37i9dQZF1DX2A29LI7xHn1",
        "37i9dQZF1DWUuQAo5SmZL4",
        "6AvLNaeJ7qeF1Ur1kNoiXT",
        "37i9dQZF1DWY5Nosj13GLt",
        "37i9dQZF1DX60OAKjsWlA2",
        "37i9dQZF1DX8S0uQvJ4gaa",
        "37i9dQZF1DXcBWIGoYBM5M",
        "37i9dQZF1DX889U0CL85jj",
        "37i9dQZF1DXcF6B6QPhFDv",
        "37i9dQZF1DWVPRVPGc4ZVv",
        "37i9dQZF1DX5CdVP4rz81C",
        "37i9dQZF1DXca8AyWK6Y7g",
        "37i9dQZF1DXcxvFzl58uP7",
        "37i9dQZF1DWXXs9GFYnvLB",
        "37i9dQZF1DWSWyJydK4fTU",
        "37i9dQZF1DX2vTOtsQ5Isl",
        "37i9dQZF1DXcZDD7cfEKhW",
        "37i9dQZF1DX0UrRvztWcAU",
        "37i9dQZF1DWSJHnPb1f0X3",
        "37i9dQZF1DX1lVhptIYRda",
        "37i9dQZF1DX4y8h9WqDPAE",
        "4AB1J5VRo8y3oc5QACLzRg",
        "37i9dQZF1DXdPec7aLTmlC",
        "37i9dQZF1DX843Qf4lrFtZ",
        "37i9dQZF1DX4WYpdgoIcn6",
        "37i9dQZF1DXaXB8fQg7xif",
        "37i9dQZF1DX4SBhb3fqCJd",
        "37i9dQZF1DWWqNV5cS50j6",
        "37i9dQZF1DWVYHdtUb7Wil",
        "37i9dQZF1DWYBO1MoTDhZI",
        "37i9dQZF1DX50QitC6Oqtn",
        "37i9dQZF1DXdwmD5Q7Gxah",
        "37i9dQZF1DX5uokaTN4FTR",
        "37i9dQZF1DX2SK4ytI2KAZ",
        "37i9dQZF1DWSqPHam7LOqC",
        "37i9dQZEVXbLRQDuF5jeBp",
        "7b9XqnXw5J47tmn0Y0IZeW",
        "37i9dQZF1DX4SO57lOJWRB",
        "59ZrWq6NDDZXQJcUHnkruk",
        "03n3Y7ugrETGIdODxD1iFe",
        "7zTdbcsIbwJZzYkSWpiPI8",
    ],
}


class SpotifyOfficialChart(ChartResource):

    def get_filters(self, **defaults):
        return ChartResource.get_filters(
            self,
            includeSigned=False,
            chart_country_code=None,  # legacy old
            chartCountryCodes=["global"],
        )

    def get_results_list(self, filters, username, limit, offset):
        if filters.get("includeSigned"):
            and_not_signed_clause = ""
        else:
            # extra clause to handle tracks labeled on users, the '3 days' is to keep the list small
            # assuming that the olap table is generated daily (with a small buffer for if something fails
            # An index was added to support this 'index only' on spy tracks
            # create index spy_tracks_chart_part_tagged_idx
            #   on spy_tracks (autotagged_at, spyid) where (tags ?| array['label-other'])
            and_not_signed_clause = """ 
              and not t.tags ?| array['label-sony', 'label-warner', 'label-universal', 'label-other']
            """

        and_chart_countries_clause = """
        and country_code = any (:chartCountryCodes)
        """
        if filters.get("chart_country_code"):
            and_chart_countries_clause = """
            and country_code = :chart_country_code
            """

        as_of_date = self.Session.execute(
            """
        select max(as_of) from spy_official_charts 
        where position = 1 
          and country_code = 'global' 
          and as_of > current_date - 4
        """
        ).fetchone()[0]

        ids_list_sql = """
        select
            distinct t.spyid, t.primary_artist_spyid, c.position
        
        from spy_tracks t
        join spy_official_charts c
          on t.spyid = c.spyid 
          and as_of = :as_of_date
          {and_chart_countries_clause}
          {and_not_signed_clause}
        order by c.position asc
        limit :limit
        offset :offset
        
       """.format(
            and_chart_countries_clause=and_chart_countries_clause,
            and_not_signed_clause=and_not_signed_clause,
        )

        ids_list = self.Session.execute(
            ids_list_sql, params=dict(filters, as_of_date=as_of_date, limit=limit, offset=offset)
        ).fetchall()

        artists = self.fetch_artists("spy", [i[1] for i in ids_list], include_alerts=True)
        tracks = list(models.spotify_tracks([i[0] for i in ids_list]))

        spy_results_list = [
            {
                "track_spyid": r[0],
                "artist": next((a["artist"] for a in artists if a["spyid"] == r[1]), None),
                "track": next((t["track"] for t in tracks if t["spyid"] == r[0]), None),
            }
            for r in ids_list
        ]

        print("Results list ", len(spy_results_list))
        return spy_results_list


class SpotifyOlap(ChartResource):

    '''
{"filters": {"maxFollowers": 50000, "presetPlaylist": "US"}}
{"filters": {"maxFollowers": 50000, "presetPlaylist": "UK"}}
{"filters": {"countries": ["DE", "AT"], "maxFollowers": 50000, "minPopularity": 30}}
    '''

    def get_filters(self, **defaults):
        filters = ChartResource.get_filters(
            self,
            includeSigned=False,
            playlistIds=None,
            selectedGenres=None,
            minFollowers=0,
            maxFollowers=1e6,
            minPublishedDaysAgo=0,
            maxPublishedDaysAgo=30,
            minTotalPlaylistFollowers=0,
            maxTotalPlaylistFollowers=1e9,
            minTrackPopularity=0,
            maxTrackPopularity=100,
            minArtistPopularity=0,
            maxArtistPopularity=100,
            onlyChartPlaylists=False,
            countries=None,
            top200CountryCodes=None,
        )
        if 'presetPlaylist' in filters:
            presetPlaylist = filters.pop("presetPlaylist")
            filters['playlistIds'] = list(set(_PRESET_PLAYLISTS.get(presetPlaylist, []) + (filters.get("playlistIds") or []))) or None
        return filters

    def get_results_list(self, filters, username, limit, offset):
        times = []

        times.append(time.time())
        ids_list = self.get_spyids(filters, limit, offset)
        times.append(time.time())

        artists = self.fetch_artists("spy", [i[1] for i in ids_list], include_alerts=True)

        times.append(time.time())
        tracks = list(models.spotify_tracks([i[0] for i in ids_list]))
        times.append(time.time())

        renderes = [
            {
                "track_spyid": r[0],
                "artist": next((a["artist"] for a in artists if a["spyid"] == r[1]), None),
                "track": next((t["track"] for t in tracks if t["spyid"] == r[0]), None),
            }
            for r in ids_list
        ]
        times.append(time.time())
        print("---- TIMES ----", "\n".join([str(t - times[0]) for t in times]))
        return renderes

    def get_spyids(self, filters, limit, offset):
        if filters.get("includeSigned"):
            and_not_signed_clause = ""
        else:
            # extra clause to handle tracks labeled on users, the '3 days' is to keep the list small
            # assuming that the olap table is generated daily (with a small buffer for if something fails
            # An index was added to support this 'index only' on spy tracks
            # create index spy_tracks_chart_part_tagged_idx
            #   on spy_tracks (autotagged_at, spyid) where (tags ?| array['label-other'])
            and_not_signed_clause = """ 
              and not is_signed
              and spyid not in (
                select spyid
                from spy_tracks
                where tags ?| array['label-other']
                  and autotagged_at > now() - interval '3 days'
              )
            """
        if filters.get("playlistIds"):
            and_playlist_clause = """
              and current_playlist_ids && (:playlistIds)::text[]
            """
        elif filters.get("onlyChartPlaylists"):
            and_playlist_clause = """
              and current_playlist_ids && ( select array_agg(spyid) from spy_playlist where include_in_charts )::text[]
            """
        else:
            and_playlist_clause = ""
        if filters.get("countries"):
            and_countries_filter = """
              and country_codes && (:countries)::text[]
            """
        else:
            and_countries_filter = ""
        if filters.get("selectedGenres"):
            and_genres_filter = """
              and genres && (:selectedGenres)::text[]
            """
        else:
            and_genres_filter = ""
        if filters.get("top200CountryCodes"):
            and_top200_filter = """
                and spyid in (
                select spyid 
                from spy_official_charts 
                where as_of >= current_date - 1 
                  and country_code = any (:top200CountryCodes)
              )
            """
            filters["top200CountryCodes"] = [c.lower() for c in filters["top200CountryCodes"]]
        else:
            and_top200_filter = ""
        sort = self.get_setting("sort", "popularity")
        sort_field_name = {
            "popularity": "track_popularity",
            "new": "release_date",
            "playlist": "current_playlist_followers",
        }[sort]
        ids_list_sql = """
        select
          spyid, artist_spyid, current_date - release_date, track_popularity, artist_popularity, artist_followers
        from spy_track_olap
        where artist_followers between :minFollowers and :maxFollowers
          {and_not_signed_clause}
          and release_date between current_date - :maxPublishedDaysAgo and current_date - :minPublishedDaysAgo
          and track_popularity between :minTrackPopularity and :maxTrackPopularity
          and artist_popularity between :minArtistPopularity and :maxArtistPopularity
          and current_playlist_followers between :minTotalPlaylistFollowers and :maxTotalPlaylistFollowers
          {and_playlist_clause}
          {and_countries_filter}
          {and_genres_filter}
          {and_top200_filter}
          
        order by {sort_field_name} desc, spyid asc
        limit :limit
        offset :offset
        """.format(
            and_not_signed_clause=and_not_signed_clause,
            and_playlist_clause=and_playlist_clause,
            and_countries_filter=and_countries_filter,
            and_genres_filter=and_genres_filter,
            sort_field_name=sort_field_name,
            and_top200_filter=and_top200_filter,
        )
        ids_list = self.Session.execute(
            ids_list_sql, params=dict(filters, limit=limit, offset=offset)
        ).fetchall()
        return ids_list


class SpotifyEmerging(ChartResource):

    def get(self, days_since_release=None):
        return self.post(days_since_release)

    def post(self, days_since_release=None):
        limit = 20
        offset = int(request.args.get("offset") or "0")

        filters = self.get_filters(
            minPopularity=10,
            publishedDaysAgo=None,
            minPublishedDaysAgo=-1,
            maxPublishedDaysAgo=(days_since_release or 10),
            minFollowers=0,
            maxFollowers=1e6,
        )

        # use the new mat view table, we translate the filters to the new ones
        translated_playlist_ids = None
        if filters.get("presetPlaylist"):
            translated_playlist_ids = _PRESET_PLAYLISTS.get(filters.get("presetPlaylist")) or []
        elif filters.get("playlistIds"):
            translated_playlist_ids = filters.get("playlistIds")

        translated_filters = dict(
            SpotifyOlap.get_filters(self),
            includeSigned=filters.get("includeSigned"),
            playlistIds=translated_playlist_ids,
            minFollowers=filters.get("minFollowers"),
            maxFollowers=filters.get("maxFollowers"),
            minPublishedDaysAgo=filters.get("minPublishedDaysAgo"),
            maxPublishedDaysAgo=filters.get("publishedDaysAgo")
            or filters.get("maxPublishedDaysAgo"),  # even more legacy fields
            # minTotalPlaylistFollowers=0,
            # maxTotalPlaylistFollowers=1e9,
            minTrackPopularity=filters.get("minPopularity"),
            # maxTrackPopularity=100,
            # minArtistPopularity=0,
            # maxArtistPopularity=100,
            onlyChartPlaylists=True,
            countries=filters.get("countries"),
        )

        return self.render_json(
            SpotifyOlap.get_results_list(
                self, translated_filters, self.user.name, limit, offset
            ),
            limit,
            offset,
        )


class MostScoutedRecently(ChartResource):

    def get_results_list(self, filters, username, limit, offset):
        ids_list = self.Session.execute(
            """
with all_f as (
  SELECT
    ai.scid,
    ai.eid,
    ai.inid,
    'sc',
    af.inf_id
  FROM artist_ids ai
    JOIN artist_follows af ON af.art_id = ai.scid :: TEXT AND af.source = 'sc' and seen_at > now() - interval '30 days'
  UNION ALL
  SELECT
    ai.scid,
    ai.eid,
    ai.inid,
    'tw',
    af.inf_id
  FROM artist_ids ai
    JOIN artist_follows af ON af.art_id = ai.eid :: TEXT AND af.source = 'tw' and seen_at > now() - interval '30 days'
  UNION ALL
  SELECT
    ai.scid,
    ai.eid,
    ai.inid,
    'in',
    af.inf_id
  FROM artist_ids ai
    JOIN artist_follows af ON af.art_id = ai.inid :: TEXT AND af.source = 'in' and seen_at > now() - interval '30 days'
)

SELECT
  all_f.scid,
  all_f.inid,
  count(*)
from all_f
left join sc_users scu on scu.scid = all_f.scid
left join in_user inu on inu.inid = all_f.inid
where (all_f.scid is not null or all_f.inid is not null)  -- either sc or in
  and (scu.scid is null or (scu.active and scu.snooze_until <= current_date))  -- if there's an sc, ensure active
  and (inu.inid is null or (inu.active and inu.snooze_until <= current_date))  -- if there's an in, ensure active
group by 1, 2 having count(*) > 1
order by 3 desc
limit :limit
offset :offset
        """,
            params=dict(limit=limit, offset=offset),
        ).fetchall()

        artists = (
            self.fetch_artists("sc", [i[0] for i in ids_list if i[0]]) +
            self.fetch_artists("in", [i[1] for i in ids_list if i[1] and not i[0]])
        )

        return [
            {
                "scid": r[0],
                "inid": r[1],
                "context": {"numScouts": r[2]},
                "artist": next(
                    (a["artist"] for a in artists if (a["scid"] == r[0] or a["inid"] == r[1])),
                    None,
                ),
            }
            for r in ids_list
        ]


class TikTokTrending(ChartResource):
    def get_results_list(self, filters, username, limit, offset):
        ids_list = [r[0] for r in self.Session.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 :limit
                offset :offset
            """,
            params=dict(username=username, limit=limit, offset=offset),
        ).fetchall()]

        sounds = models.tiktok_sounds(ids_list)
        results = [
            {
                "soundTtid": ttid,
                "ttSound": next((s for s in sounds if s["ttid"] == ttid), None),
            }
            for ttid in ids_list
        ]
        return results