"""Add sc_artists materialized view

Revision ID: 8acf7a594845
Revises: f3f3618c32da
Create Date: 2017-06-05 13:26:56.594497

"""

# revision identifiers, used by Alembic.
revision = '8acf7a594845'
down_revision = 'f3f3618c32da'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute("""
    create materialized view sc_activity as (
      with 
likes as (
  select
    influencer_scid||'L'||track_scid as action_id,
    seen_at as "timestamp",
    'Like' as type,
    influencer_scid,
    influencer.name as influencer,
    artist_scid,
    track_scid,
    sc_tracks.name as track,
    influencer.groups as groups,
    sc_tracks.data->'permalink_url' as track_link
  from sc_likes 
  join sc_tracks on sc_tracks.scid = track_scid
  join sc_users influencer on influencer.scid = influencer_scid and influencer.is_influencer
),

followings as (
  select
    influencer_scid||'F'||artist_scid as action_id,
    seen_at as "timestamp",
    'Follow' as type,
    influencer_scid,
    influencer.name as influencer,
    artist_scid,
    null as track_scid,
    null as track,
    influencer.groups as groups,
    null as track_link
  from sc_followings 
  join sc_users on sc_users.scid = artist_scid
  join sc_users influencer on influencer.scid = influencer_scid and influencer.is_influencer
),

activity as (
  select artist_scid, influencer_scid, "timestamp", row_to_json(likes) as a from likes
  UNION ALL 
  select artist_scid, influencer_scid, "timestamp", row_to_json(followings) as a from followings
),

influencer_days as (
  select
   influencer_scid, 
   "timestamp"::date,
   count(*)::float as actions
 from activity 
 group by 1, 2
),

influencer_quality as (
 select 
   influencer_scid, 
   1.0/max(actions)::float as quality
 from influencer_days 
 group by 1
),


unique_artist_influencers as (
  select 
    distinct influencer_scid, artist_scid, quality
  from activity
  join influencer_quality using (influencer_scid)
),

artist_signal as (
  select 
    artist_scid,
    sum(quality) as signal,
    count(*) as cnt
  from unique_artist_influencers
  group by 1
),

artists as (
  select 
    name as artist,
    scid,
    (data->>'permalink_url')::text as link,
    substring((data->>'description')::text, 1, 50) as description,
    (data->>'followers_count')::integer as followers_count,
    first_seen as seen,
    array_to_json(array_agg(activity.a order by "timestamp" desc)) AS influencer_activity,
    sum(signal) as signal
  from sc_users
  join activity on activity.artist_scid = sc_users.scid
  join artist_signal on sc_users.scid = artist_signal.artist_scid
  group by 1,2,3,4,5,6
)

select * from artists
    )
    """)


def downgrade():
    op.execute("""
    drop materialized view sc_activity""")