"""setf_artist_sources
;


Revision ID: 844550f120b1
Revises: 8b0d414a1798
Create Date: 2019-02-06 15:49:22.113267

"""

# revision identifiers, used by Alembic.
revision = '844550f120b1'
down_revision = '8b0d414a1798'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
create or replace function setf_artist_sources(querysrc text, variadic queryids text[])
-- This function returns the entity identifiers for an artist. 
-- 'querysrc' can be either a source or 'a' to lookup by arid.
-- 'queryids' is an array of text ids for the query src.
-- eg. setf_artist_sources('in', 'robinsonxmusic', 'nelly', 'mimi') 
  returns TABLE (
    groupkey text,  -- not null, is the querysrc and querykey
    arid     bigint,  -- null if no artist association record 
    scid     integer,
    eid      integer,
    inid     text,
    spyid    text,
    ytid     text,
    scid_associated boolean,
    eid_associated boolean,
    inid_associated boolean,
    spyid_associated boolean,
    ytid_associated boolean
  )
language sql
as $$

with ids_and_arids as (
  -- A distinct set of <queryid, arid?> where arid is null if no artist_association
  -- This cte defines the rows that can be returned by the function, and unifies
  -- 'a' querysrc with the others.
  
  -- non 'a' querys:
  select
    ids.id as queryid,
    aa.arid as arid
  from unnest(queryids) as ids(id)
  left join artist_associations aa
    on aa.source = querysrc
    and aa.identifier = ids.id
    and querysrc <> 'a'
  union all
  -- 'a' queries for non-merged arid
  select
    arid::text, arid
  from artists
  where arid::text = any(queryids)
    and querysrc = 'a'
    and merged_to_arid is null
  union all 
  -- 'a' query for an arid that's been merged (we return <mergedarid, canonicalarid>)
  select
    ids.id, a.arid
  from unnest(queryids) as ids(id)
  join artists a
  on a.merged_arids @> id::jsonb 
    and merged_to_arid is null
    and querysrc = 'a'
),

all_sources as (
  -- all artist_associations for the queryids, or a single row with a null arid if no association for a queryid
  select
    queryid,
    aa.arid,
    coalesce(aa.source, querysrc) as source,
    coalesce(aa.identifier, queryid) as identifier,
    aa.priority
  from ids_and_arids
  left join artist_associations aa using (arid)
),

artist_pivot as (
  -- a pivot on artists one or more artist_associations
  select
     arid,
    (first(identifier order by priority) filter (where aa2.source = 'sc' ))::int as scid,
    (first(identifier order by priority) filter (where aa2.source = 'tw' ))::int as eid,
     first(identifier order by priority) filter (where aa2.source = 'in' ) as inid,
     first(identifier order by priority) filter (where aa2.source = 'spy') as spyid,
     first(identifier order by priority) filter (where aa2.source = 'yt' ) as ytid
  from all_sources aa2
  where arid is not null
  group by arid
)

select

  querysrc||'/'||ids.queryid as group_key,
  ids.arid as arid,
  coalesce(artist_pivot.scid,  scu.scid,   name_pivot.scid) as scid,
  coalesce(artist_pivot.eid,   twu.eid,    name_pivot.eid) as eid,
  coalesce(artist_pivot.inid,  inu.inid,   name_pivot.inid) as inid,
  coalesce(artist_pivot.spyid, spyu.spyid, name_pivot.spyid) as spyid,
  coalesce(artist_pivot.ytid,  ytu.ytid,   name_pivot.ytid) as ytid,
  artist_pivot.scid is not null,
  artist_pivot.eid is not null,
  artist_pivot.inid is not null,
  artist_pivot.spyid is not null,
  artist_pivot.ytid is not null

from ids_and_arids ids

-- the entity identifiers found via artist_associations:
left join artist_pivot on artist_pivot.arid = ids.arid

-- if no arid, than we join directly to the entity of the querysrc to ensure it exists
left join sc_users scu           on ids.arid is null and querysrc = 'sc'  and scu.scid::text = ids.queryid
left join in_user inu            on ids.arid is null and querysrc = 'in'  and inu.inid::text = ids.queryid
left join tracked_entities twu   on ids.arid is null and querysrc = 'tw'  and twu.eid::text = ids.queryid
left join spy_artists spyu       on ids.arid is null and querysrc = 'spy' and spyu.spyid::text = ids.queryid
left join yt_artist_channels ytu on ids.arid is null and querysrc = 'yt'  and ytu.ytid::text = ids.queryid

-- join to entity_names, to find other identifiers sharing the same name.
-- we only lookup by name if there isn't an association for the source.
left join lateral ((
  select
    all_sources.queryid as src_id,
    (first(en2.id) filter (where en2.source = 'sc' ))::int as scid,
    (first(en2.id) filter (where en2.source = 'tw' ))::int as eid,
     first(en2.id) filter (where en2.source = 'in' ) as inid,
     first(en2.id) filter (where en2.source = 'spy') as spyid,
     first(en2.id) filter (where en2.source = 'yt' ) as ytid
  from all_sources
  -- join from all_sources to allow for matching of all names that are in the association.
  -- eg. if we lookup sc/1 that's associated to tw/1 and tw/1 matches in/1 by entity name
  join entity_names en1
    on en1.source = all_sources.source
    and en1.id = all_sources.identifier
  join entity_names en2
    on en2.source <> en1.source
    and en2.source <> querysrc
    and en1.name = en2.name
    and length(en2.name) > 2
    and (
      -- only join if there isn't an artist association for the source
      -- this is a performance optimization, as the coalescing in the select clause
      -- chooses association information first
      en2.source = 'sc' and artist_pivot.scid is null
      or en2.source = 'tw' and artist_pivot.eid is null
      or en2.source = 'in' and artist_pivot.inid is null
      or en2.source = 'spy' and artist_pivot.spyid is null
      or en2.source = 'yt' and artist_pivot.ytid is null
    )
  -- by grouping here, and using a lateral join, we avoid having to do grouping at the
  -- top level for this query.  This should keep the size of the joins smaller, and
  -- is easier to reason about.
  group by 1
)) name_pivot on name_pivot.src_id = ids.queryid

where
  -- enforce that some part of the artist exists.
  coalesce(ids.arid::text, scu.scid::text, spyu.spyid, inu.inid, twu.eid::text, ytu.ytid) is not null
$$;
    ''')

    op.execute('''
create or replace function setf_artist_profiles2(src text, variadic queryids text[])
returns TABLE (
    groupkey text,
    arid     bigint,
    scid     integer,
    eid      integer,
    inid     text,
    spyid    text,
    ytid     text,
    scid_associated boolean,
    eid_associated boolean,
    inid_associated boolean,
    spyid_associated boolean,
    ytid_associated boolean,
    keys jsonb, 
    links jsonb,
    name text,
    bio text,
    avatar_urls jsonb,
    country_codes jsonb,
    banner_images jsonb,
    text_locations jsonb,
    first_seen timestamp without time zone,
    snooze_until date,
    active boolean
  )
language sql
as $$

select
  s.groupkey,
  s.arid,
  s.scid,
  s.eid,
  s.inid,
  s.spyid,
  s.ytid,
  s.scid_associated,
  s.eid_associated,
  s.inid_associated,
  s.spyid_associated,
  s.ytid_associated,
  
  -- keys:
  to_jsonb(
    array_remove(
      array[
        ('a/'  || s.arid)::text,
        ('sc/' || s.scid)::text,
        ('tw/' || s.eid)::text,
        ('in/' || s.inid)::text,
        ('spy/'|| s.spyid)::text,
        ('yt/' || s.ytid)::text
      ],
      null
    )
  ) as keys,
  
  -- links:
  to_jsonb(
    array_remove(
      array[
        (case when sc_users.scid is null then null else 
          jsonb_build_object(
            'source', 'sc',  'id', s.scid::text, 'arid', s.arid,
            'source_url', sc_users.data->>'permalink_url'
          ) 
        end),
        (case when tracked_entities.eid is null then null else 
          jsonb_build_object(
            'source', 'tw',  'id', s.eid::text, 'arid', s.arid,
            'source_url', ('https://twitter.com/'||(twitter_data->>'screen_name'))
          )
        end),
        (case when in_user.inid is null then null else 
          jsonb_build_object(
            'source', 'in',  'id', s.inid, 'arid', s.arid,
            'source_url', ('https://www.instagram.com/'||s.inid||'/')
          ) 
        end),
        (case when spy_artists.spyid is null then null else 
          jsonb_build_object(
             'source', 'spy', 'id', s.spyid, 'arid', s.arid,
             'source_url', 'https://open.spotify.com/artist/'||s.spyid
          ) 
        end),
        (case when yt_artist_channels.ytid is null then null else 
          jsonb_build_object(
             'source', 'yt',  'id', s.ytid, 'arid', s.arid,
             'source_url', 'https://www.youtube.com/channel/'||s.ytid
          ) 
        end)
      ],
      null
    )
  ) as links,

  -- name: choose one, some sources more reliable, hence the custom sort
  coalesce(
    spy_artists.name,
    tracked_entities.name,
    nullif(sc_users.data->>'username', ''),
    nullif(in_user.page_data->'entry_data'->'ProfilePage'->0->'user'->>'full_name', ''),
    sc_users.name,
    yt_artist_channels.name,
    in_user.inid
  ) as name,

  -- bio: concat with newlines from any non null sources
  array_to_string(
    array_remove(
      array[
        sc_users.data->>'description',
        tracked_entities.twitter_data->>'description',
        in_user.page_data->'entry_data'->'ProfilePage'->0->'user'->>'biography'
      ],
      null
    ),
    '\n'
  )::text as bio,

  -- avatar_urls: to support fallbacks
  to_jsonb(
    array_remove(
      array[
        sc_users.data ->> 'avatar_url',
        tracked_entities.twitter_data ->> 'profile_image_url_https',
        in_user.page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'profile_pic_url',
        spy_artists.data -> 'images' -> 3 ->> 'url',   -- these go smallest first
        spy_artists.data -> 'images' -> 2 ->> 'url',
        spy_artists.data -> 'images' -> 1 ->> 'url',
        spy_artists.data -> 'images' -> 0 ->> 'url'
      ],
      null
    )
  ) as avatar_urls,

  -- country_codes:
  to_jsonb(
    array_remove(
      array[]::text[]
      || spy_artist_countries.isrc_countries
      || array[sc_cc.iso2]::text[],
      null
    )
  ) as country_codes,

  -- banner_images:
  to_jsonb(
    array_remove(
      array [tracked_entities.twitter_data ->> 'profile_banner_url']::text[],
      null
    )
  ) as banner_images,

  -- text_locations (free text field users fill in)
   to_jsonb(
    array_remove(
      array[
        tracked_entities.twitter_data->>'location',
        sc_users.data->>'city'
      ],
      null
    )
  ) as text_locations,

  least(
    sc_users.first_seen,
    spy_artists.first_seen,
    in_user.first_seen,
    tracked_entities.first_seen
  ) as first_seen,

  greatest(sc_users.snooze_until, in_user.snooze_until, tracked_entities.snooze_until) as snooze_until,
  (sc_users.active and in_user.active and tracked_entities.active and true) as active
from setf_artist_sources(src, variadic queryids) s

left join sc_users on sc_users.scid = s.scid
left join lateral ((
  -- 'United States' has multiple entries, so need the distinct
  select
    distinct iso2
  from isrc_country_codes sc_cc 
  where lower_sc_name = lower(sc_users.data->>'country')
)) as sc_cc on true
left join tracked_entities on tracked_entities.eid = s.eid
left join in_user on in_user.inid = s.inid
left join spy_artists on spy_artists.spyid = s.spyid

left join lateral ((
  select spy_tracks.primary_artist_spyid as spyid,
         array_agg(distinct upper(substring(spy_tracks.data -> 'external_ids' ->> 'isrc', 1, 2))) as isrc_countries
  from spy_tracks
  where primary_artist_spyid = spy_artists.spyid
  group by 1
)) as spy_artist_countries on true

left join yt_artist_channels on yt_artist_channels.ytid = s.ytid
$$;

    ''')

def downgrade():
    op.execute('''drop function setf_artist_profiles2(text, variadic text[])''')
    op.execute('''drop function setf_artist_sources(text, variadic text[])''')