"""mat_view_improvements

Revision ID: ca28a741ed17
Revises: b5859c20475c
Create Date: 2018-12-03 11:02:25.129680

"""

# revision identifiers, used by Alembic.
revision = 'ca28a741ed17'
down_revision = 'b5859c20475c'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''drop index if exists in_user_active_idx;''')
    op.execute('''create index in_user_active_idx on in_user (active);''')  # speed up entity
    op.execute('''drop materialized view if exists whitelist;''')   # NOT USED ANYMOE
    op.execute('''drop materialized view if exists in_artist;''')   # duplicate of in_artists (sloppy)

    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 (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 AS text,
           (a.eid) :: text AS eid,
           a.name,
           (a.twitter_data ->> 'screen_name' :: text) AS screen_name
    FROM ((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 AS text,
           a.inid,
           (((((a.page_data -> 'entry_data' :: text) -> 'ProfilePage' :: text) -> 0) -> 'user' :: text) ->>
            'full_name' :: text),
           a.inid
    FROM (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 AS text, a.spyid, a.name, a.name
    FROM (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 ((all_names.term IS NOT NULL) AND (length(all_names.term) > 0))
  )
  SELECT search_table.term, search_table.matches
  FROM search_table
;
    ''')


def downgrade():
    pass