"""vw_influencers2

Revision ID: d9276c7b62d6
Revises: 4c649d16f395
Create Date: 2019-06-22 07:18:19.979928

"""

# revision identifiers, used by Alembic.
revision = 'd9276c7b62d6'
down_revision = '4c649d16f395'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
create or replace function setf_influencer(src text, ident text)
returns TABLE (
    source text,
    identifier text,
    key text,
    name text,
    avatar text,
    source_url text,
    description text,
    last_follows jsonb,
    total_follow_count bigint,
    recent_follow_count bigint
  )
language sql
as $$
    -- twitter:
    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')
       ) AS source_url,
       first(
         COALESCE(inf.influencer_biography, inf.twitter_data ->> 'description')
       ) AS description,
       jsonb_agg_uniq(
          jsonb_build_array(art.eid, art.name, last_3.seen_at) ORDER BY last_3.seen_at DESC
       ) AS last_3_follows,
       first(counts.total) as total_follows,
       first(counts.recent) as follows_last_14_days

    FROM tracked_entities inf
    left join lateral (
      select
        f.*
      from followings f
      where src = 'tw' and f.influencer_eid = (ident)::int
      order by f.seen_at desc
      limit 3
    ) last_3 on true
    left join tracked_entities art on art.eid = last_3.followee_eid
    left join lateral (
      select
        count(*) as total,
        sum(case when f.seen_at::date > current_date - 14 then 1 else 0 end) as recent
      from followings f
      where src = 'tw' and f.influencer_eid = (ident)::int
    ) counts on true
    where src = 'tw' and inf.eid = (ident)::int
    GROUP BY 2

    union all
    -- soundcloud
    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),
                                          last_3.seen_at) ORDER BY last_3.seen_at DESC) AS followed,
       first(counts.total) as total_follows,
       first(counts.recent) as follows_last_14_days
  FROM sc_users inf
  left join lateral (
      select
        f.*
      from sc_followings f
      where src = 'sc' and f.influencer_scid = (ident)::int
      order by f.seen_at desc
      limit 3
    ) last_3 on true
    left join sc_users art on art.scid = last_3.artist_scid
    left join lateral (
      select
        count(*) as total,
        sum(case when f.seen_at::date > current_date - 14 then 1 else 0 end) as recent
      from sc_followings f
      where src = 'sc' and f.influencer_scid = (ident)::int
    ) counts on true
    where src = 'sc' and inf.scid = (ident)::int
    GROUP BY 2

   union all
    -- insta

   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),
                                      last_3.first_seen) ORDER BY last_3.first_seen DESC) AS followed,
    first(counts.total) as total_follows,
    first(counts.recent) as follows_last_14_days
  FROM in_user inf
    left join lateral (
      select
        f.*
      from in_follows f
      where src = 'in' and f.followed_by_inid = (ident)
      order by f.first_seen desc
      limit 3
    ) last_3 on true
    left join in_user art on art.inid = last_3.followee_inid
    left join lateral (
      select
        count(*) as total,
        sum(case when f.first_seen::date > current_date - 14 then 1 else 0 end) as recent
      from in_follows f
      where src = 'in' and f.followed_by_inid = (ident)
    ) counts on true
    where src = 'in' and inf.inid = (ident)
    GROUP BY 2
$$;
    ''')


def downgrade():
    op.execute('''drop function setf_influencer(src text, ident text)''')