"""sc_follows_and_likes

Revision ID: 8fcb1ca23d49
Revises: ebdfcd2ee64a
Create Date: 2019-09-30 13:10:39.896715

"""

# revision identifiers, used by Alembic.
revision = '8fcb1ca23d49'
down_revision = 'ebdfcd2ee64a'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
create view vw_sc_follows_and_likes as (
with all_fols_and_likes as (
  select
    fol.artist_scid as artist_scid,
    fol.influencer_scid as scout_scid,
    inf.name::text as scout_name,
    (inf.data->>'avatar_url')::text as scout_avatar,
    fol.seen_at as seen_at
  from sc_followings fol
  join sc_users inf on inf.scid = fol.influencer_scid
  union all
  select
    a.scid as artist_scid,
    l.influencer_scid,
    inf.name::text as name,
    (inf.data->>'avatar_url')::text as avatar_url,
    l.seen_at as seen_at
  from sc_likes l
  join sc_tracks t on t.scid = l.track_scid
  join sc_users inf on inf.scid = l.influencer_scid
  join sc_users a on a.scid = t.artist_scid
)

select
  artist_scid,
  scout_scid,
  min(scout_name) as scout_name,
  min(scout_avatar) as scout_avatar,
  min(seen_at) as seen_at
from all_fols_and_likes
group by 1, 2
)
       
    ''')


    op.execute('''
create materialized view sc_follows_and_likes as (
  select
    artist_scid,
    scout_scid,
    scout_name,
    scout_avatar,
    seen_at
  from vw_sc_follows_and_likes
  order by seen_at desc
)
    ''')

    op.execute('''create unique index artist_model_sc_fol_and_likes on sc_follows_and_likes (artist_scid, scout_scid)''')

def downgrade():
    op.execute('''drop materialized view sc_follows_and_likes''')
    op.execute('''drop view vw_sc_follows_and_likes''')