"""spy_olap_genre

Revision ID: b7e2d45d9be5
Revises: ca28a741ed17
Create Date: 2018-12-07 09:47:42.610743

"""

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

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''

create or replace view vw_spy_track_olap as
SELECT
  t.spyid,
  first(t.primary_artist_spyid) AS artist_spyid,
  (t.tags ?| ARRAY['label-sony'::text, 'label-warner'::text, 'label-universal'::text, 'label-other'::text]) AS is_signed,
  first(
    CASE (t.album_data ->> 'release_date_precision' :: text)
      WHEN 'day' :: text
      THEN ((t.album_data ->> 'release_date' :: text)) :: date
      ELSE NULL :: date
    END
  ) AS release_date,
  (first(COALESCE((t.data ->> 'popularity' :: text), '0' :: text))) :: integer AS track_popularity,
  (first(COALESCE((sa.data ->> 'popularity' :: text), '0' :: text))) :: integer AS artist_popularity,
  (first(COALESCE(((sa.data -> 'followers' :: text) ->> 'total' :: text), '0' :: text))) :: integer AS artist_followers,
  first(c.country_codes) AS country_codes,
  coalesce(
    (array_agg(DISTINCT spt.playlist_spyid)
          FILTER (WHERE (spt.last_seen > (now() - '36:00:00' :: interval)))) :: text [],
    array[] :: text []
  ) AS current_playlist_ids,
  coalesce(
    (array_agg(DISTINCT spt.playlist_spyid)) :: text [],
    array[] :: text []
  ) AS all_playlist_ids,
  coalesce(
    (sum((COALESCE(((sp.data -> 'followers' :: text) ->> 'total' :: text), '0' :: text)) :: integer)
        FILTER (WHERE (spt.last_seen > (now() - '36:00:00' :: interval)))),
    0
  ) AS current_playlist_followers,
  coalesce(
    (sum((COALESCE(((sp.data -> 'followers' :: text) ->> 'total' :: text), '0' :: text)) :: integer)),
    0
  ) AS all_playlist_followers,
  first(g.genres)::text[] as genres,
  to_tsvector(first(t.name)) as ts_title,
  to_tsvector(first(sa.name)) as ts_artist_name
FROM ((((spy_tracks t
JOIN spy_artists sa ON (((sa.spyid) :: text = (t.primary_artist_spyid) :: text)))
left JOIN spy_playlist_track spt ON (((spt.track_spyid) :: text = (t.spyid) :: text)))
left JOIN spy_playlist sp ON (((sp.spyid) :: text = (spt.playlist_spyid) :: text)))
LEFT JOIN spy_artist_countries c ON (((sa.spyid) :: text = (c.artist_spyid) :: text))
cross join lateral (
   SELECT array_agg(genre::text) AS genres
   FROM jsonb_array_elements_text(sa.data->'genres') AS g(genre)
) g)
GROUP BY t.spyid
;


create materialized view if not exists spy_track_olap_new as
SELECT vw_spy_track_olap.spyid,
         vw_spy_track_olap.artist_spyid,
         vw_spy_track_olap.is_signed,
         vw_spy_track_olap.release_date,
         vw_spy_track_olap.track_popularity,
         vw_spy_track_olap.artist_popularity,
         vw_spy_track_olap.artist_followers,
         vw_spy_track_olap.country_codes,
         vw_spy_track_olap.current_playlist_ids,
         vw_spy_track_olap.all_playlist_ids,
         vw_spy_track_olap.current_playlist_followers,
         vw_spy_track_olap.all_playlist_followers,
         vw_spy_track_olap.genres,
         vw_spy_track_olap.ts_title,
         vw_spy_track_olap.ts_artist_name
  FROM vw_spy_track_olap
;


DROP MATERIALIZED VIEW spy_track_olap;
alter materialized view spy_track_olap_new rename to spy_track_olap;

create materialized view spy_genres as (
  select genre, count(*) as num_artists
  from spy_artists sa
  cross join lateral (
     SELECT genre::text as genre
     FROM jsonb_array_elements_text(sa.data->'genres') AS g(genre)
  ) g
  group by genre
  order by num_artists desc
)
    ''')


def downgrade():
    pass