"""artist_ids mat view

Revision ID: 2f6b188e024d
Revises: 13d2471469f1
Create Date: 2018-07-23 09:26:45.441886

"""

# revision identifiers, used by Alembic.
revision = '2f6b188e024d'
down_revision = '13d2471469f1'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
CREATE MATERIALIZED VIEW in_artist AS 
SELECT u.inid
FROM in_user u
WHERE (
  (
    concat(
      lower(((((((u.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) -> 'biography'::text))::text), 
      ' ', 
      lower(((((((u.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) -> 'external_url'::text))::text)
    ) ~ similar_escape(
      '%(booking.*\@|mymixtapez|smarturl.it|lnk.to|fanlink.to|linkfire|sptfy|youtube|youtu.be|soundcloud|mixcloud|spotify|bandcamp|reverbnation|audiomack)%'::text, 
    NULL::text)
  ) 
  
  AND u.active
);

CREATE INDEX in_artist_inid_idx
  ON in_artist (inid);


    ''')

    op.execute('''
create MATERIALIZED VIEW artist_ids as (
  -- soundcloud
  SELECT
    first(aa1.arid) :: INT AS arid,
    scid :: INT,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'tw'),
        first(n2.id)
          FILTER (WHERE n2.source = 'tw')
    ) :: INT               AS eid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'spy'),
        first(n2.id)
          FILTER (WHERE n2.source = 'spy')
    ) :: TEXT              AS spyid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'in'),
        first(n2.id)
          FILTER (WHERE n2.source = 'in')
    ) :: TEXT              AS inid
  FROM sc_users u
    LEFT JOIN artist_associations aa1 ON aa1.source = 'sc' AND aa1.identifier = u.scid :: TEXT
    LEFT JOIN artist_associations aa2 ON aa2.source <> 'sc' AND aa1.arid = aa2.arid
    LEFT JOIN entity_names n1 ON n1.source = 'sc' 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 scid

  UNION

  -- twitter
  SELECT
    first(aa1.arid) AS arid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'sc'),
        first(n2.id)
          FILTER (WHERE n2.source = 'sc')
    ) :: INT        AS scid,
    eid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'spy'),
        first(n2.id)
          FILTER (WHERE n2.source = 'spy')
    )               AS spyid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'in'),
        first(n2.id)
          FILTER (WHERE n2.source = 'in')
    )               AS inid
  FROM tracked_entities u
    LEFT JOIN artist_associations aa1 ON aa1.source = 'tw' AND aa1.identifier = u.eid :: TEXT
    LEFT JOIN artist_associations aa2 ON aa2.source <> 'tw' AND aa1.arid = aa2.arid
    LEFT JOIN entity_names n1 ON n1.source = 'tw' AND n1.id = u.eid :: TEXT
    LEFT JOIN entity_names n2 ON n2.source <> n1.source AND n1.name = n2.name
  WHERE u.active
  GROUP BY eid

  UNION

  SELECT
    first(aa1.arid) AS arid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'sc'),
        first(n2.id)
          FILTER (WHERE n2.source = 'sc')
    ) :: INT        AS scid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'tw'),
        first(n2.id)
          FILTER (WHERE n2.source = 'tw')
    ) :: INT        AS eid,
    spyid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'in'),
        first(n2.id)
          FILTER (WHERE n2.source = 'in')
    )               AS inid
  FROM spy_artists u
    LEFT JOIN artist_associations aa1 ON aa1.source = 'spy' AND aa1.identifier = u.spyid :: TEXT
    LEFT JOIN artist_associations aa2 ON aa2.source <> 'spy' AND aa1.arid = aa2.arid
    LEFT JOIN entity_names n1 ON n1.source = 'spy' AND n1.id = u.spyid :: TEXT
    LEFT JOIN entity_names n2 ON n2.source <> n1.source AND n1.name = n2.name
  GROUP BY spyid

  UNION

  SELECT
    first(aa1.arid) AS arid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'sc'),
        first(n2.id)
          FILTER (WHERE n2.source = 'sc')
    ) :: INT        AS scid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'tw'),
        first(n2.id)
          FILTER (WHERE n2.source = 'tw')
    ) :: INT        AS eid,
    coalesce(
        first(aa2.identifier)
          FILTER (WHERE aa2.source = 'spy'),
        first(n2.id)
          FILTER (WHERE n2.source = 'spy')
    )               AS spyid,
    inid
  FROM in_user u
    LEFT JOIN artist_associations aa1 ON aa1.source = 'in' AND aa1.identifier = u.inid :: TEXT
    LEFT JOIN artist_associations aa2 ON aa2.source <> 'in' AND aa1.arid = aa2.arid
    LEFT JOIN entity_names n1 ON n1.source = 'in' AND n1.id = u.inid :: TEXT
    LEFT JOIN entity_names n2 ON n2.source <> n1.source AND n1.name = n2.name
  WHERE u.active
  GROUP BY inid
);

    ''')


    op.execute('''
CREATE MATERIALIZED VIEW 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;

    ''')



def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    ### end Alembic commands ###
    op.execute('drop materialized view artist_follows cascade')
    op.execute('drop materialized view artist_ids cascade')
    op.execute('drop materialized view in_artist cascade')