"""influencer_biogrqphy

Revision ID: 6ecd63a78e7f
Revises: 437b99a2a533
Create Date: 2018-07-03 08:42:03.923146

"""

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

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('in_user', sa.Column('influencer_biography', sa.String(), nullable=True))
    op.add_column('sc_users', sa.Column('influencer_biography', sa.String(), nullable=True))
    op.add_column('tracked_entities', sa.Column('influencer_biography', sa.String(), nullable=True))

    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'::text)) AS name,
    first((inf.data ->> 'avatar_url'::text)) AS avatar,
    first((inf.data ->> 'permalink_url'::text)) AS source_url,
    first(COALESCE(inf.influencer_biography::text, concat((inf.data ->> 'full_name'::text), ' ', (inf.data ->> 'description'::text)))) AS description,
    jsonb_agg_uniq(jsonb_build_array(art.scid, (art.data ->> 'username'::text), sc_followings.seen_at) ORDER BY sc_followings.seen_at DESC) AS followed,
    count(*) FILTER (WHERE ((sc_followings.seen_at)::date > (('now'::text)::date - '14 days'::interval))) AS count
   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 'sc'::text, (inf.scid)::text
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'::text)) AS avatar,
    first(concat('https://twitter.com/', (inf.twitter_data ->> 'screen_name'::text))) AS source_url,
    first(COALESCE(inf.influencer_biography::text, concat((inf.twitter_data ->> 'description'::text)))) 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 > (('now'::text)::date - '14 days'::interval))) AS count
   FROM ((followings
     JOIN tracked_entities inf ON ((inf.eid = followings.influencer_eid)))
     JOIN tracked_entities art ON ((art.eid = followings.followee_eid)))
  GROUP BY 'tw'::text, (inf.eid)::text
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'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'full_name'::text), (inf.inid)::text)) AS name,
    first((((((inf.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'profile_pic_url'::text)) AS avatar,
    first(concat('https://www.instagram.com/', inf.inid, '/')) AS source_url,
    first(COALESCE(inf.influencer_biography::text, concat((((((inf.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'biography'::text)))) AS description,
    jsonb_agg_uniq(jsonb_build_array(art.inid, COALESCE((((((art.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'full_name'::text), (art.inid)::text), in_follows.first_seen) ORDER BY in_follows.first_seen DESC) AS followed,
    count(*) FILTER (WHERE ((in_follows.first_seen)::date > (('now'::text)::date - '14 days'::interval))) AS count
   FROM ((in_follows
     JOIN in_user inf ON (((inf.inid)::text = (in_follows.followed_by_inid)::text)))
     JOIN in_user art ON (((art.inid)::text = (in_follows.followee_inid)::text)))
  GROUP BY 'in'::text, (inf.inid)::text;    
    
    ''')
    ### end Alembic commands ###


def downgrade():
    ### 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'::text)) AS name,
    first((inf.data ->> 'avatar_url'::text)) AS avatar,
    first((inf.data ->> 'permalink_url'::text)) AS source_url,
    first(COALESCE(NULL::text, concat((inf.data ->> 'full_name'::text), ' ', (inf.data ->> 'description'::text)))) AS description,
    jsonb_agg_uniq(jsonb_build_array(art.scid, (art.data ->> 'username'::text), sc_followings.seen_at) ORDER BY sc_followings.seen_at DESC) AS followed,
    count(*) FILTER (WHERE ((sc_followings.seen_at)::date > (('now'::text)::date - '14 days'::interval))) AS count
   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 'sc'::text, (inf.scid)::text
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'::text)) AS avatar,
    first(concat('https://twitter.com/', (inf.twitter_data ->> 'screen_name'::text))) AS source_url,
    first(COALESCE(NULL::text, concat((inf.twitter_data ->> 'description'::text)))) 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 > (('now'::text)::date - '14 days'::interval))) AS count
   FROM ((followings
     JOIN tracked_entities inf ON ((inf.eid = followings.influencer_eid)))
     JOIN tracked_entities art ON ((art.eid = followings.followee_eid)))
  GROUP BY 'tw'::text, (inf.eid)::text
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'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'full_name'::text), (inf.inid)::text)) AS name,
    first((((((inf.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'profile_pic_url'::text)) AS avatar,
    first(concat('https://www.instagram.com/', inf.inid, '/')) AS source_url,
    first(COALESCE(NULL::text, concat((((((inf.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'biography'::text)))) AS description,
    jsonb_agg_uniq(jsonb_build_array(art.inid, COALESCE((((((art.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'full_name'::text), (art.inid)::text), in_follows.first_seen) ORDER BY in_follows.first_seen DESC) AS followed,
    count(*) FILTER (WHERE ((in_follows.first_seen)::date > (('now'::text)::date - '14 days'::interval))) AS count
   FROM ((in_follows
     JOIN in_user inf ON (((inf.inid)::text = (in_follows.followed_by_inid)::text)))
     JOIN in_user art ON (((art.inid)::text = (in_follows.followee_inid)::text)))
  GROUP BY 'in'::text, (inf.inid)::text;
    
    ''')
    op.drop_column('tracked_entities', 'influencer_biography')
    op.drop_column('sc_users', 'influencer_biography')
    op.drop_column('in_user', 'influencer_biography')
    ### end Alembic commands ###