"""influncers view

Revision ID: 437b99a2a533
Revises: e8347f2c8f07
Create Date: 2018-07-02 10:05:44.700164

"""

# revision identifiers, used by Alembic.
revision = '437b99a2a533'
down_revision = 'e8347f2c8f07'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.execute('''
    create or replace view vw_influencers as (
    SELECT
      'sc'::text as source,
      inf.scid::text as identifier,
      first(concat('sc/', inf.scid::text)) as key,
      first(inf.data->>'username') as name,
      first(inf.data->>'avatar_url') as avatar,
      first(inf.data->>'permalink_url') as source_url,
      first(coalesce(null, concat(inf.data->>'full_name', ' ', inf.data->>'description'))) as description,
      jsonb_agg_uniq(
        jsonb_build_array(art.scid, art.data->>'username', sc_followings.seen_at)
        order by sc_followings.seen_at desc
      ) as followed,
      count(*) filter (where sc_followings.seen_at::date > current_date - INTERVAL '14 days')
    from sc_followings
    join sc_users inf on inf.scid = sc_followings.influencer_scid
    join sc_users art on art.scid = sc_followings.artist_scid
  group by 1, 2

  union ALL

  select
    'tw'::text as source,
    inf.eid::text as identifier,
    first(concat('tw/', inf.eid::text)) as key,
    first(inf.name) as name,
    first(inf.twitter_data->>'profile_image_url_https') as avatar,
    first(concat('https://twitter.com/', inf.twitter_data->>'screen_name')) as source_url,
    first(coalesce(null, concat(inf.twitter_data->>'description'))) as description,
    jsonb_agg_uniq(
      jsonb_build_array(art.eid, art.name, followings.seen_at)
      order by followings.seen_at desc
    ) as followed,
    count(*) filter (where followings.seen_at::date > current_date - INTERVAL '14 days')
  from followings
  join tracked_entities inf on inf.eid = followings.influencer_eid
  join tracked_entities art on art.eid = followings.followee_eid
  group by 1,2

    union ALL

    select
    'in'::text as source,
    inf.inid::text as identifier,
    first(concat('in/', inf.inid::text)) as key,
    first(coalesce(inf.page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'full_name', inf.inid)) as name,
    first(inf.page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'profile_pic_url') as avatar,
    first(concat('https://www.instagram.com/', inf.inid, '/')) as source_url,
    first(coalesce(null, concat(inf.page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography'))) as description,
    jsonb_agg_uniq(
      jsonb_build_array(
          art.inid,
          coalesce(
              art.page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'full_name',
              art.inid),
          in_follows.first_seen)
      order by in_follows.first_seen desc
    ) as followed,
    count(*) filter (where in_follows.first_seen::date > current_date - INTERVAL '14 days')
  from in_follows
  join in_user inf on inf.inid = in_follows.followed_by_inid
  join in_user art on art.inid = in_follows.followee_inid
  group by 1,2
);
    ''')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.execute(''' drop view vw_influencers''')
    ### end Alembic commands ###