"""search_table

Revision ID: e8347f2c8f07
Revises: 47b86fc3e54a
Create Date: 2018-06-27 16:36:22.022648

"""

# revision identifiers, used by Alembic.
revision = 'e8347f2c8f07'
down_revision = '47b86fc3e54a'
branch_labels = None
depends_on = None

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

def upgrade():
    op.execute('''drop materialized view if exists search_table cascade''')
    op.execute('''drop materialized view if exists entity_names cascade''')

    op.execute('''
CREATE or replace VIEW vw_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 active
     and ((sc_users.web_profiles IS NOT NULL) 
     AND (sc_users.web_profiles <> '"null"'::jsonb) 
     AND ((sc_users.web_profiles)::text <> 'null'::text))
),

all_names as (
SELECT 'sc'::text AS source,
    'nm'::text AS col,
    lower((sc_users.name)::text) AS name,
    (sc_users.scid)::text AS id
FROM sc_users 
where active
UNION
SELECT 'sc'::text AS source,
    'un'::text AS col,
    lower((sc_users.data ->> 'username'::text)) AS name,
    (sc_users.scid)::text AS id
FROM sc_users 
where active
UNION
SELECT 'sc'::text AS source,
    'pl'::text AS col,
    lower((sc_users.data ->> 'permalink'::text)) AS name,
    (sc_users.scid)::text AS id
FROM sc_users 
where active
UNION
SELECT 'tw'::text AS source,
    'nm'::text AS col,
    lower((tracked_entities.name)::text) AS name,
    (tracked_entities.eid)::text AS id
FROM tracked_entities
where active
UNION
SELECT 'tw'::text AS source,
    'sn'::text AS col,
    lower((tracked_entities.twitter_data ->> 'screen_name'::text)) AS name,
    (tracked_entities.eid)::text AS id
FROM tracked_entities
where active
UNION
SELECT 'spy'::text AS source,
    'nm'::text AS col,
    lower((spy_artists.name)::text) AS name,
    spy_artists.spyid AS id
FROM spy_artists
UNION
SELECT 'spy'::text AS source,
    'id'::text AS col,
    lower((spy_artists.spyid)::text) AS name,
    spy_artists.spyid AS id
FROM spy_artists
UNION
SELECT 'in'::text AS source,
    'un'::text AS col,
    lower((in_user.inid)::text) AS name,
    in_user.inid AS id
FROM in_user 
where active
UNION
SELECT 'in'::text AS source,
    'fn'::text AS col,
    lower((((((in_user.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'full_name'::text)) AS name,
    in_user.inid AS id
FROM in_user 
where active
UNION
SELECT 'sc'::text AS source,
    'wp'::text 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'::text AS source,
    'wp'::text AS col,
    regexp_replace((sc_web_profiles.r ->> 'url'::text), '.*\/([^\?]*)\??.*'::text, '
'::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))
)

SELECT
source::text,
col::text,
name::text,
id::text
from all_names
where length(name) >= 3
    ''')

    op.execute('''
    create materialized view entity_names as (
      select source::text, col::text, name::text, id::text
      from vw_entity_names
    )
    ''')

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

    op.execute('''
    create index entity_names_source_id_idx
	on entity_names (source, id)
    ''')

    op.execute('''
    CREATE or replace VIEW vw_search_table AS
WITH
all_names AS (
   SELECT en.name AS term,
      'sc'::text AS source,
      (a.scid)::text AS identifier,
      a.name,
      (a.data ->> 'username'::text) AS username
     FROM (vw_entity_names en
       JOIN sc_users a ON ((((a.scid)::text = en.id) AND (en.source = 'sc'::text))))
  UNION ALL
   SELECT en.name,
      'tw'::text,
      (a.eid)::text AS eid,
      a.name,
      (a.twitter_data ->> 'screen_name'::text) AS screen_name
     FROM ((vw_entity_names en
       JOIN tracked_entities a ON ((((a.eid)::text = en.id) AND (en.source = 'tw'::text))))
       JOIN twitter_artist_activity USING (eid))
  UNION ALL
   SELECT en.name,
      'in'::text,
      a.inid,
      (((((a.page_data -> 'entry_data'::text) -> 'ProfilePage'::text) -> 0) -> 'user'::text) ->> 'full_name'::text),
      a.inid
     FROM (vw_entity_names en
       JOIN in_user a ON ((((a.inid)::text = en.id) AND (en.source = 'in'::text))))
  UNION ALL
   SELECT en.name,
      'spy'::text,
      a.spyid,
      a.name,
      a.name
     FROM (vw_entity_names en
       JOIN spy_artists a ON ((((a.spyid)::text = en.id) AND (en.source = 'spy'::text))))
),

search_table AS (
  SELECT DISTINCT all_names.term,
    jsonb_build_array(all_names.source, all_names.identifier, all_names.name, all_names.username) AS matches
  FROM all_names
  where term is not null
    and length(term) > 0
)
SELECT
  search_table.term,
  search_table.matches
FROM search_table;
    ''')

    op.execute('''
    create materialized view search_table as (
      select term::text, matches::jsonb
      from vw_search_table
    )
    ''')

    op.execute('''
    create index on search_table using btree (term text_pattern_ops);
    ''')

    op.execute('''
    create index on entity_names (name, source, id);
    create index on entity_names (source, id);
    ''')

def downgrade():
    op.execute('''drop materialized view if exists search_table cascade''')
    op.execute('''drop materialized view if exists entity_names cascade''')