"""in_artists_views

Revision ID: e272fdce3d1d
Revises: 2db25ffd2b33
Create Date: 2018-08-13 12:36:41.345415

"""

# revision identifiers, used by Alembic.
revision = 'e272fdce3d1d'
down_revision = '2db25ffd2b33'
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_in_artists as (

  WITH
  inurls AS (
      SELECT lower(canonical_url) AS url
      FROM artist_urls
      WHERE source = 'in'
  )

  SELECT
    inid,
    first_seen,
    (lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'external_url') ~
    '\/(www\.|)(\w+\.|)(smarturl.it|lnk.to|soundcloud\.com|mixcloud\.com|spotify\.com|bandcamp|reverbnation|audiomack)'
    OR
    -- biography doesn't require / at beginning...
    lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography') ~
     '(www\.|)(\w+\.|)(smarturl.it|lnk.to|soundcloud\.com|mixcloud\.com|spotify\.com|bandcamp|reverbnation|audiomack)') as has_url,
    (
      -- biography words (most noisy)
      lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography') ~ '\m(rapper|musician|booking|booking:|mgmt:|singer|songwriter|out now)'
      AND NOT
      -- words tend to mean non-artists:
      lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography') ~ '\m(videograph|hotel|ambassador|restaurant|studio|artist manager|artist management|talent manag|label|engineer|director|stylist|presenter|music executive|visual artist)'
      AND NOT
      -- a few common insta handles that are non-artists:
      lower(inid) ~ '(mgmt|agency|records|studio)'
    ) as has_bio
  FROM in_user u
  WHERE (
    -- confident artist urls:
    lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'external_url') ~
    '\/(www\.|)(\w+\.|)(smarturl.it|lnk.to|soundcloud\.com|mixcloud\.com|spotify\.com|bandcamp|reverbnation|audiomack)'
    OR
    -- biography doesn't require / at beginning...
    lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography') ~
     '(www\.|)(\w+\.|)(smarturl.it|lnk.to|soundcloud\.com|mixcloud\.com|spotify\.com|bandcamp|reverbnation|audiomack)'
  )
  OR (
    -- biography words (most noisy)
    lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography') ~ '\m(rapper|musician|booking|booking:|mgmt:|singer|songwriter|out now)'
    AND NOT
    -- words tend to mean non-artists:
    lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'biography') ~ '\m(makeup|videograph|hotel|restaurant|blogger|fashion|studio|artist manager|artist management|talent manag|label|engineer|director|stylist|presenter|music executive|visual artist)'
    AND NOT
    -- sometimes strivers put stuff in there name
    lower(page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'full_name') ~ '\m(makeup|videograph|hotel|restaurant|blogger|fashion|studio|artist manager|artist management|talent manag|label|engineer|director|stylist|presenter|music executive|visual artist)'
    AND NOT
    -- a few common insta handles that are non-artists:
    lower(inid) ~ '(mgmt|agency|records|studio)'
  )
);
    ''')

    op.execute('''
create MATERIALIZED VIEW in_artists AS (
    select inid from vw_in_artists
);
    ''')

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.execute("drop materialized view in_artists")
    op.execute("drop view vw_in_artists")
    ### end Alembic commands ###