"""spy_track_olap

Revision ID: b5859c20475c
Revises: d4a448c8acdc
Create Date: 2018-11-07 12:47:32.561124

"""

# revision identifiers, used by Alembic.
revision = 'b5859c20475c'
down_revision = 'd4a448c8acdc'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
    create or replace view
  vw_spy_track_olap
  as 
  select
   t.spyid spyid,
   first(t.primary_artist_spyid) artist_spyid,
   t.tags ?| array['label-sony', 'label-warner', 'label-universal', 'label-other'] is_signed,
   first(
     case t.album_data->>'release_date_precision'
       when 'day' then (t.album_data->>'release_date')::date
       else null
      end
   ) release_date,
   first(coalesce(t.data->>'popularity', '0'))::int track_popularity,
   first(coalesce(sa.data->>'popularity', '0'))::int artist_popularity,
   first(coalesce(sa.data->'followers'->>'total', '0'))::int artist_followers,
   first(c.country_codes)::text[] country_codes,
   (array_agg(distinct spt.playlist_spyid) filter (where last_seen > now() - interval '36 hours'))::text[] current_playlist_ids,
   array_agg(distinct spt.playlist_spyid)::text[] all_playlist_ids,
   sum(coalesce(sp.data->'followers'->>'total', '0')::int) filter (where last_seen > now() - interval '36 hours') as current_playlist_followers,
   sum(coalesce(sp.data->'followers'->>'total', '0')::int) as all_playlist_followers
 from spy_tracks t
 join spy_playlist_track spt on spt.track_spyid = t.spyid
 join spy_playlist sp on sp.spyid = spt.playlist_spyid
 join spy_artists sa on sa.spyid = t.primary_artist_spyid
 left join spy_artist_countries c on sa.spyid = c.artist_spyid
 group by 1
    ''')

    op.execute('''
    create materialized view
  spy_track_olap as select * from vw_spy_track_olap;
    ''')


def downgrade():
    op.execute('''drop materialized view spy_track_olap''')
    op.execute('''drop view vw_spy_track_olap''')