"""artist_ids_view

Revision ID: 310f707116bd
Revises: 799570f2758b
Create Date: 2018-08-20 12:59:29.929385

"""

# revision identifiers, used by Alembic.
revision = '310f707116bd'
down_revision = '799570f2758b'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.execute('''
    CREATE or replace VIEW vw_artist_ids AS SELECT (first(aa1.arid))::integer AS arid,
    u.scid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'tw'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'tw'::text)))::character varying))::integer AS eid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'spy'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'spy'::text)))::character varying))::text AS spyid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'in'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'in'::text)))::character varying))::text AS inid
   FROM ((((sc_users u
     LEFT JOIN artist_associations aa1 ON ((((aa1.source)::text = 'sc'::text) AND ((aa1.identifier)::text = (u.scid)::text))))
     LEFT JOIN artist_associations aa2 ON ((((aa2.source)::text <> 'sc'::text) AND (aa1.arid = aa2.arid))))
     LEFT JOIN entity_names n1 ON (((n1.source = 'sc'::text) AND (n1.id = (u.scid)::text))))
     LEFT JOIN entity_names n2 ON (((n2.source <> n1.source) AND (n1.name = n2.name))))
  WHERE u.active
  GROUP BY u.scid
UNION
 SELECT first(aa1.arid) AS arid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'sc'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'sc'::text)))::character varying))::integer AS scid,
    u.eid,
    COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'spy'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'spy'::text)))::character varying) AS spyid,
    COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'in'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'in'::text)))::character varying) AS inid
   FROM ((((twitter_artist_activity u
     LEFT JOIN artist_associations aa1 ON ((((aa1.source)::text = 'tw'::text) AND ((aa1.identifier)::text = (u.eid)::text))))
     LEFT JOIN artist_associations aa2 ON ((((aa2.source)::text <> 'tw'::text) AND (aa1.arid = aa2.arid))))
     LEFT JOIN entity_names n1 ON (((n1.source = 'tw'::text) AND (n1.id = (u.eid)::text))))
     LEFT JOIN entity_names n2 ON (((n2.source <> n1.source) AND (n1.name = n2.name))))
  GROUP BY u.eid
UNION
 SELECT first(aa1.arid) AS arid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'sc'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'sc'::text)))::character varying))::integer AS scid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'tw'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'tw'::text)))::character varying))::integer AS eid,
    u.spyid,
    COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'in'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'in'::text)))::character varying) AS inid
   FROM ((((spy_artists u
     LEFT JOIN artist_associations aa1 ON ((((aa1.source)::text = 'spy'::text) AND ((aa1.identifier)::text = (u.spyid)::text))))
     LEFT JOIN artist_associations aa2 ON ((((aa2.source)::text <> 'spy'::text) AND (aa1.arid = aa2.arid))))
     LEFT JOIN entity_names n1 ON (((n1.source = 'spy'::text) AND (n1.id = (u.spyid)::text))))
     LEFT JOIN entity_names n2 ON (((n2.source <> n1.source) AND (n1.name = n2.name))))
  GROUP BY u.spyid
UNION
 SELECT first(aa1.arid) AS arid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'sc'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'sc'::text)))::character varying))::integer AS scid,
    (COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'tw'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'tw'::text)))::character varying))::integer AS eid,
    COALESCE(first(aa2.identifier) FILTER (WHERE ((aa2.source)::text = 'spy'::text)), (first(n2.id) FILTER (WHERE (n2.source = 'spy'::text)))::character varying) AS spyid,
    u.inid
   FROM ((((in_artists u
     LEFT JOIN artist_associations aa1 ON ((((aa1.source)::text = 'in'::text) AND ((aa1.identifier)::text = (u.inid)::text))))
     LEFT JOIN artist_associations aa2 ON ((((aa2.source)::text <> 'in'::text) AND (aa1.arid = aa2.arid))))
     LEFT JOIN entity_names n1 ON (((n1.source = 'in'::text) AND (n1.id = (u.inid)::text))))
     LEFT JOIN entity_names n2 ON (((n2.source <> n1.source) AND (n1.name = n2.name))))
  GROUP BY u.inid;


    ''')

    op.execute('''
    drop materialized view if exists artist_ids
    ''')

    op.execute('''
    create materialized view artist_ids as (
      select arid, scid, eid, spyid, inid
      from vw_artist_ids
    )
    ''')

    op.execute('''
CREATE VIEW vw_artist_follows AS WITH sc_first_days AS (
         SELECT sc_followings.influencer_scid,
            (min(sc_followings.seen_at))::date AS first_day
           FROM sc_followings
          GROUP BY sc_followings.influencer_scid
        ), sc_fol AS (
         SELECT 'sc'::text AS source,
            (sc_followings.influencer_scid)::text AS inf_id,
            (sc_followings.artist_scid)::text AS art_id,
            sc_followings.seen_at
           FROM (sc_followings
             JOIN sc_first_days USING (influencer_scid))
          WHERE ((sc_followings.seen_at > (CURRENT_DATE - 60)) AND ((sc_followings.seen_at)::date <> sc_first_days.first_day))
        ), tw_first_days AS (
         SELECT followings.influencer_eid,
            (min(followings.seen_at))::date AS first_day
           FROM followings
          GROUP BY followings.influencer_eid
        ), tw_fol AS (
         SELECT 'tw'::text AS source,
            (followings.influencer_eid)::text AS inf_id,
            (followings.followee_eid)::text AS art_id,
            followings.seen_at
           FROM (followings
             JOIN tw_first_days USING (influencer_eid))
          WHERE ((followings.seen_at > (CURRENT_DATE - 60)) AND ((followings.seen_at)::date <> tw_first_days.first_day))
        ), in_first_days AS (
         SELECT in_follows.followed_by_inid,
            (min(in_follows.first_seen))::date AS first_day
           FROM in_follows
          GROUP BY in_follows.followed_by_inid
        ), in_fol AS (
         SELECT 'in'::text AS source,
            (in_follows.followed_by_inid)::text AS inf_id,
            (in_follows.followee_inid)::text AS art_id,
            in_follows.first_seen AS seen_at
           FROM (in_follows
             JOIN in_first_days USING (followed_by_inid))
          WHERE ((in_follows.first_seen > (CURRENT_DATE - 60)) AND ((in_follows.first_seen)::date <> in_first_days.first_day))
        )
 SELECT sc_fol.source,
    sc_fol.inf_id,
    sc_fol.art_id,
    sc_fol.seen_at
   FROM sc_fol
UNION
 SELECT tw_fol.source,
    tw_fol.inf_id,
    tw_fol.art_id,
    tw_fol.seen_at
   FROM tw_fol
UNION
 SELECT in_fol.source,
    in_fol.inf_id,
    in_fol.art_id,
    in_fol.seen_at
   FROM in_fol
  ORDER BY 4 DESC;
    ''')

    op.execute('''
       drop materialized view if exists artist_follows
    ''')

    op.execute('''
       create materialized view artist_follows as (
         select source, inf_id, art_id, seen_at
         from vw_artist_follows
       )
       ''')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###