"""artist_avatar_urls

Revision ID: d9eb79668b29
Revises: 645b2da4d782
Create Date: 2018-08-30 12:46:04.452357

"""

# revision identifiers, used by Alembic.
revision = 'd9eb79668b29'
down_revision = '645b2da4d782'
branch_labels = None
depends_on = None

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

def upgrade():
    op.execute('''drop function if exists setf_artist_profiles(src text, variadic idents text[])''')
    op.execute('''
create or replace function setf_artist_profiles(src text, variadic idents text[])
returns table(
  groupkey text,
  arid bigint,
  keys jsonb,
  scid int,
  eid int,
  inid text,
  spyid text,
  name text,
  bio text,
  avatar_urls jsonb,
  country_codes jsonb,
  banner_images jsonb,
  text_locations jsonb,
  first_seen timestamp,
  snooze_until date,
  active boolean,
  source_data jsonb
)
as
$body$
with all_assoc as (
  select arid, source, identifier, priority, groupkey
  from artist_associations
  join (
    select arid, src||'/'||identifier as groupkey
    from artist_associations
    where source = src
    and identifier = any(idents)
  ) as x using (arid)


  union all
  select arid, source, identifier, priority, src||'/'||arid as groupkey
  from artist_associations
  where src = 'a'
    and arid = any(idents::bigint[])

  union all

  select null as arid, src as source, nid as identifier, 10 as priority, src||'/'||nid as groupkey
  from unnest(idents) nid
  where src <> 'a'

  union all

  select
      null as arid,
      en2.source as source,
      en2.id as identifier,
      100 + coalesce(array_position(array['un', 'nm'], en2.col), 10) as priority,
      src||'/'||en1.id as groupkey
  from entity_names en1
         join entity_names en2 on en2.source <> src
                                    and en1.name = en2.name
                                    and length(en2.name) > 2
         left join sc_users on en2.source = 'sc' and sc_users.scid :: text = en2.id
         left join in_user on en2.source = 'in' and in_user.inid :: text = en2.id
         left join tracked_entities on en2.source = 'tw' and tracked_entities.eid :: text = en2.id
         left join spy_artists on en2.source = 'spy' and spy_artists.spyid :: text = en2.id
  where en1.source = src
    and en1.id = any(idents)
)

select
  groupkey,
  (first(arid order by priority) filter (where arid is not null))::bigint as arid,
  -- keys, only one for each source, and all the 'a's
  to_jsonb(
    array_remove(
      array[]::text[]
      || first('a/'||arid order by priority) filter (where arid is not null)
      || first('sc/'||identifier order by priority) filter (where source = 'sc')
      || first('tw/'||identifier order by priority) filter (where source = 'tw')
      || first('in/'||identifier order by priority) filter (where source = 'in')
      || first('spy/'||identifier order by priority) filter (where source = 'spy')
      -- include previous arids as keys...
      || coalesce(
           array_agg(
              distinct concat('a/', merged_artists.jsonb_array_elements_text)
           ) filter (
             where merged_artists.jsonb_array_elements_text is not null
           ),
           array[]::text[]
         ),
      null
    )
  ) as keys,

  (first(identifier order by priority) filter (where source = 'sc'))::int,
  (first(identifier order by priority) filter (where source = 'tw'))::int,
  (first(identifier order by priority) filter (where source = 'in'))::text,
  (first(identifier order by priority) filter (where source = 'spy'))::text,

  -- name: choose one, some sources more reliable, hence the custom sort
  first(
    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,
      in_user.inid
    )
    order by
      priority,
      array_position(array ['spy', 'tw', 'sc', 'in'], source :: text)
  ) as name,
  -- bio: concat with newlines from any non null sources
  array_to_string(
    array_agg(
      DISTINCT coalesce(
        sc_users.data->>'description',
        tracked_entities.twitter_data->>'description',
        in_user.page_data->'entry_data'->'ProfilePage'->0->'user'->>'biography',
        null
      )
    ),
    E'\n\n'
  )::text as bio,
  -- avatar_urls: to support fallbacks
  jsonb_agg(
    distinct coalesce(
        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',
        spy_artists.data -> 'images' -> 2 ->> 'url'
    )
  ) filter (
    where coalesce(
        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',
        spy_artists.data -> 'images' -> 2 ->> 'url'
    ) is not null
  ) as avatar_urls,
  -- countryCodes: multiple in array.
  jsonb_agg(
    DISTINCT coalesce(
      sc_cc.iso2,
      track_cc.iso2
    )
  ) filter (
    where coalesce(
      sc_cc.iso2,
      track_cc.iso2
    ) is not null
  ) as country_codes,
  -- banner_images:
  jsonb_agg(
    distinct
      tracked_entities.twitter_data ->> 'profile_banner_url'
  ) filter (
    where tracked_entities.twitter_data ->> 'profile_banner_url' is not null
  ) as banner_images,
  -- textLocations
  jsonb_agg(
    distinct coalesce (
        tracked_entities.twitter_data->>'location',
        sc_users.data->>'city'
    )
  ) filter (
    where coalesce (
        tracked_entities.twitter_data->>'location',
        sc_users.data->>'city'
    ) is not null
  ) as text_locations,
  min(coalesce(sc_users.first_seen, spy_artists.first_seen, in_user.first_seen, tracked_entities.first_seen)) as first_seen,
  max(coalesce(sc_users.snooze_until, in_user.snooze_until, tracked_entities.snooze_until)) as snooze_until,
  bool_and(coalesce(sc_users.active, in_user.active, tracked_entities.active, true)) as active,
  jsonb_build_object(
    'sc', first(sc_users.data order by priority),
    'tw', first(tracked_entities.twitter_data order by priority),
    'in', first(in_user.page_data->'entry_data'->'ProfilePage'->0->'user' order by priority),
    'spy', first(spy_artists.data order by priority)
  ) as source_data
  
from all_assoc
left join (
  SELECT arid, jsonb_array_elements_text(merged_arids)
  from artists
  where merged_arids is not NULL
) as merged_artists using (arid)
left join sc_users on sc_users.scid::text = identifier and source = 'sc'
left join isrc_country_codes sc_cc on sc_cc.lower_sc_name = lower(sc_users.data->>'country')
left join tracked_entities on tracked_entities.eid::text = identifier and source = 'tw'
left join in_user on in_user.inid::text = identifier and source = 'in'
left join spy_artists on spy_artists.spyid::text = identifier and source = 'spy'
left join spy_tracks on spy_tracks.primary_artist_spyid = spy_artists.spyid
left join isrc_country_codes track_cc on track_cc.isrc = upper(substring(spy_tracks.data->'external_ids'->>'isrc', 1, 2))
where coalesce(sc_users.scid::text, in_user.inid, spy_artists.spyid, tracked_entities.eid::text) is not null
group by groupkey
$body$
language sql;
    ''')


def downgrade():
    op.execute('''drop function if exists setf_artist_profiles(src text, variadic idents text[])''')