"""add whitelist matized view

Revision ID: a5c2dfdea707
Revises: 15b4dbd93f9d
Create Date: 2017-08-27 13:46:47.222044

"""

# revision identifiers, used by Alembic.
revision = 'a5c2dfdea707'
down_revision = '15b4dbd93f9d'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():

    op.execute("""
    CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
    RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
    $$;
    """)
    op.execute("""
    CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
    RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
    $$;
    """)

    op.execute("""
    CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
    );
    """)

    op.execute("""
    CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
    );
    """)
    
    op.execute("""
    create materialized view whitelist as (
        WITH playlists AS (
         SELECT spy_playlist_track.track_spyid,
            json_agg(DISTINCT spy_playlist_track.playlist_spyid) AS lists
           FROM spy_playlist_track
          GROUP BY spy_playlist_track.track_spyid
        ), adjustment_factor AS (
         SELECT spy_tracks.spyid AS track_spyid,
            min(10.0::double precision / (sqrt((spy_artist_metrics.followers + 70000)::double precision) - sqrt((70000 - 1)::double precision) + 100::double precision)) AS factor
           FROM spy_artist_metrics
             JOIN spy_tracks ON spy_tracks.primary_artist_spyid::text = spy_artist_metrics.artist_spyid::text
          GROUP BY spy_tracks.spyid
        ), all_popularity AS (
         SELECT spy_track_popularity.track_spyid,
            json_agg((((('['::text || ('now'::text::date - spy_track_popularity.as_of::date)) || ','::text) || (spy_track_popularity.value::double precision * adjustment_factor.factor)) || ']'::text)::json ORDER BY spy_track_popularity.as_of) AS vals,
            last(spy_track_popularity.value ORDER BY spy_track_popularity.as_of)::double precision * min(adjustment_factor.factor) AS cur,
            (last(spy_track_popularity.value ORDER BY spy_track_popularity.as_of) - first(spy_track_popularity.value ORDER BY spy_track_popularity.as_of))::double precision * min(adjustment_factor.factor) AS diff
           FROM spy_track_popularity
             JOIN adjustment_factor USING (track_spyid)
          WHERE spy_track_popularity.as_of > ('now'::text::date - '7 days'::interval)
          GROUP BY spy_track_popularity.track_spyid
        ), whitelist AS (
         SELECT spy_tracks.spyid,
            spy_tracks.data -> 'name'::text AS tune,
            ((spy_tracks.data -> 'artists'::text) -> 0) -> 'name'::text AS artist,
            (spy_tracks.data -> 'external_urls'::text) ->> 'spotify'::text AS link,
            (((spy_tracks.data -> 'album'::text) -> 'images'::text) -> 2) -> 'url'::text AS imglink,
            'now'::text::date AS as_of,
            all_popularity.vals,
            all_popularity.cur,
            all_popularity.diff,
            spy_tracks.first_seen,
            playlists.lists,
            COALESCE(spy_tracks.tags, '[]'::jsonb) AS track_tags,
            row_number() OVER (ORDER BY all_popularity.diff DESC) AS current_rank
           FROM spy_tracks
             JOIN all_popularity ON all_popularity.track_spyid::text = spy_tracks.spyid::text
             JOIN playlists ON playlists.track_spyid::text = spy_tracks.spyid::text
          WHERE NOT spy_tracks.tags ?| ARRAY['interested'::text]
        )
 SELECT whitelist.spyid,
    whitelist.tune,
    whitelist.artist,
    whitelist.link,
    whitelist.imglink,
    whitelist.as_of,
    whitelist.vals,
    whitelist.cur,
    whitelist.diff,
    whitelist.first_seen,
    whitelist.lists,
    whitelist.track_tags,
    whitelist.current_rank
   FROM whitelist
  ORDER BY whitelist.diff DESC
)""")






def downgrade():

    op.execute("""
       drop materialized view whitelist""")

    op.execute("""drop aggregate public.first""")
    op.execute("""drop aggregate public.last""")
    op.execute("""drop function public.first_agg""")
    op.execute("""drop function public.last_agg""")