"""mat view entity_names

Revision ID: 1d1f4f675940
Revises: 8f2c80095005
Create Date: 2018-03-12 13:13:42.372830

"""

# revision identifiers, used by Alembic.
revision = '1d1f4f675940'
down_revision = '8f2c80095005'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute("""
CREATE MATERIALIZED VIEW entity_names AS 
WITH sc_web_profiles AS (

SELECT sc_users.scid,
    jsonb_array_elements(sc_users.web_profiles) AS r
   FROM sc_users
  WHERE ((sc_users.web_profiles IS NOT NULL) AND (sc_users.web_profiles <> '"null"'::jsonb) 
  AND ((sc_users.web_profiles)::text <> 'null'::text))
)
SELECT 'sc'::character(3) AS source,
    'nm'::character(2) AS col,
    lower((sc_users.name)::text) AS name,
    (sc_users.scid)::text AS id
   FROM sc_users
UNION
 SELECT 'sc'::bpchar AS source,
    'un'::character(2) AS col,
    lower((sc_users.data ->> 'username'::text)) AS name,
    (sc_users.scid)::text AS id
   FROM sc_users
UNION
 SELECT 'sc'::text AS source,
    'pl'::character(2) AS col,
    lower((sc_users.data ->> 'permalink'::text)) AS name,
    (sc_users.scid)::text AS id
   FROM sc_users
UNION
 SELECT 'tw'::text AS source,
    'nm'::character(3) AS col,
    lower((tracked_entities.name)::text) AS name,
    (tracked_entities.eid)::text AS id
   FROM tracked_entities
UNION
 SELECT 'tw'::text AS source,
    'sn'::character(2) AS col,
    lower((tracked_entities.twitter_data ->> 'screen_name'::text)) AS name,
    (tracked_entities.eid)::text AS id
   FROM tracked_entities
UNION
 SELECT 'spy'::text AS source,
    'nm'::character(2) AS col,
    lower((spy_artists.name)::text) AS name,
    spy_artists.spyid AS id
   FROM spy_artists
UNION
 SELECT 'spy'::text AS source,
    'id'::character(2) AS col,
    lower((spy_artists.spyid)::text) AS name,
    spy_artists.spyid AS id
   FROM spy_artists
UNION
 SELECT 'in'::text AS source,
    'un'::character(2) AS col,
    lower((in_user.inid)::text) AS name,
    in_user.inid AS id
   FROM in_user
UNION
 SELECT 'in'::text AS source,
    'fn'::character(2) AS col,
    lower((in_user.page_data->'entry_data'->'ProfilePage'->0->'user'->>'full_name')::text) AS name,
    in_user.inid AS id
   FROM in_user   
UNION
 SELECT 'sc'::bpchar AS source,
    'wp'::bpchar AS col,
    lower((sc_web_profiles.r ->> 'username'::text)) AS name,
    (sc_web_profiles.scid)::text AS id
   FROM sc_web_profiles
  WHERE ((sc_web_profiles.r ->> 'service'::text) = ANY (ARRAY['instagram'::text, 'twitter'::text]))
UNION
 SELECT 'sc'::bpchar AS source,
    'wp'::bpchar AS col,
    regexp_replace((sc_web_profiles.r ->> 'url'::text), '.*\/([^\?]*)\??.*'::text, '\1'::text) AS name,
    (sc_web_profiles.scid)::text AS id
   FROM sc_web_profiles
  WHERE (((sc_web_profiles.r ->> 'service'::text) = 'spotify'::text) 
    AND ((sc_web_profiles.r ->> 'url'::text) ~~ '%/artist/%'::text))""")

    op.execute("""
    CREATE INDEX entity_names_source_id_idx
      ON entity_names (source, id)""")

    op.execute("""
    CREATE INDEX entity_names_name_idx
      ON entity_names (name);""")

    op.execute("""
CREATE MATERIALIZED VIEW twitter_artist_activity AS 
SELECT te.eid, max(f.seen_at) AS last_followed
FROM (tracked_entities te
 JOIN followings f ON ((f.followee_eid = te.eid)))
WHERE ((((te.twitter_data ->> 'followers_count'::text))::integer < 50000) AND (lower((te.twitter_data ->> 'entities'::text)) ~ similar_escape('%(music|soundcloud|mixcloud|spotify|bandcamp|reverbnation|audiomack)%'::text, NULL::text)))
GROUP BY te.eid;""")

    op.execute("""
CREATE INDEX taa_eid_idx
  ON twitter_artist_activity (eid);""")
    op.execute("""
CREATE INDEX taa_last_followed_idx
  ON twitter_artist_activity (last_followed);""")



def downgrade():
    op.execute('''drop materialized view twitter_artist_activity''')
    op.execute('''drop materialized view entity_names''')