"""artist_profile_view

Revision ID: b4457e2e2228
Revises: 0b16e24b80ea
Create Date: 2018-06-22 12:08:15.882657

"""

# revision identifiers, used by Alembic.
revision = 'b4457e2e2228'
down_revision = '0b16e24b80ea'
branch_labels = None
depends_on = None

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

def upgrade():
    print("Adding merged_arids column")
    op.add_column(
        'artists',
        sa.Column('merged_arids', postgresql.JSONB(), nullable=True)
    )

    print("Creating artist_profiles")
    op.create_table('artist_profiles',
    sa.Column('arid', sa.BigInteger(), nullable=False),
    sa.Column('keys', postgresql.JSONB(), nullable=True),
    sa.Column('scid', sa.Integer(), nullable=True),
    sa.Column('eid', sa.Integer(), nullable=True),
    sa.Column('inid', sa.Text(), nullable=True),
    sa.Column('spyid', sa.Text(), nullable=True),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('bio', sa.Text(), nullable=True),
    sa.Column('avatar', sa.Text(), nullable=True),
    sa.Column('country_codes', postgresql.JSONB(), nullable=True),
    sa.PrimaryKeyConstraint('arid'),
    sa.UniqueConstraint('scid'),
    sa.UniqueConstraint('eid'),
    sa.UniqueConstraint('inid'),
    sa.UniqueConstraint('spyid'),
    )

    op.execute('''
create or replace view vw_artist_profiles as (
select
  arid::bigint,
  jsonb_build_array(concat('a/', arid::text)) 
    -- include previous arids as keys...
    || COALESCE(
         jsonb_agg(distinct concat('a/', merged_artists.jsonb_array_elements_text)) 
             filter (where merged_artists.jsonb_array_elements_text is not null), 
         '[]'::jsonb)
    || jsonb_agg(distinct concat(source, '/', identifier)) 
  as keys,
  first(identifier ORDER BY priority) FILTER (WHERE source = 'sc')::int scid,
  first(identifier ORDER BY priority) FILTER (WHERE source = 'tw')::int eid,
  first(identifier ORDER BY priority) FILTER (WHERE source = 'in')::Text inid,
  first(identifier ORDER BY priority) FILTER (WHERE source = 'spy')::text spyid,
  -- name: choose one, some sources more reliable, hence the custom sort
  first(
    coalesce(
      spy_artists.name,
      tracked_entities.name,
      sc_users.data->>'username',
      in_user.page_data->'entry_data'->'ProfilePage'->0->'user'->>'full_name')
    order by
      priority,
      array_position(array['spy','tw','sc','in'], source::text)
  ) as name,
  -- bio: concat with newlines from anay 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
      )
    ),
    '\n\n'
  )::text as bio,
  -- avatar: choose one.
  first(
    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',
        in_user.page_data -> 'entry_data' -> 'ProfilePage' -> 0 -> 'user' ->> 'full_name',
        spy_artists.data -> 'images' -> 3 ->> 'url',
        spy_artists.data -> 'images' -> 2 ->> 'url',
        NULL
    )
    ORDER BY
      priority,
      array_position(ARRAY ['in', 'tw', 'sc', 'spy'], source :: TEXT)
  )::text as avatar,
  -- 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
from artist_associations
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))
group by arid
);
    ''')


    op.execute('''
create or replace function upsert_artist_profile(arid int)
  returns void
language sql
as $$
DELETE from artist_profiles where arid = upsert_artist_profile.arid;
INSERT INTO artist_profiles
    (arid, keys, scid, eid, inid, spyid, name, bio, avatar, country_codes)
select
    arid, keys, scid, eid, inid, spyid, name, bio, avatar, country_codes
from vw_artist_profiles
where arid = upsert_artist_profile.arid
$$;
    ''')

    op.execute('''
CREATE or REPLACE FUNCTION trig_artist_associations()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    if (TG_OP = 'DELETE') then
      PERFORM upsert_artist_profile(OLD.arid::int);
    elsif (TG_OP = 'UPDATE') then
      PERFORM upsert_artist_profile(OLD.arid::int);
      PERFORM upsert_artist_profile(NEW.arid::int);
    else
      PERFORM upsert_artist_profile(NEW.arid::int);
    END IF;
    
    RETURN NEW;
END;
$$;


CREATE TRIGGER artist_associations_trigger
    after insert or UPDATE OR DELETE ON artist_associations
    FOR EACH ROW
    EXECUTE PROCEDURE trig_artist_associations();
    ''')


def downgrade():
    op.execute('''drop trigger if exists artist_associations_trigger ON artist_associations''')
    op.execute('''drop function if exists trig_artist_association()''')
    op.execute('''drop view vw_artist_profiles''')
    op.execute('''drop table artist_profiles''')
    op.execute('''alter table artists drop column merged_arids''')