"""spy_artist_countries

Revision ID: 645b2da4d782
Revises: 310f707116bd
Create Date: 2018-08-24 13:22:22.735518

"""

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

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

def upgrade():
    op.execute('''
create or replace view vw_spy_artist_countries as
select
    t.primary_artist_spyid as artist_spyid,
    array_agg(distinct c.iso2)::text[] as country_codes
from spy_tracks t
join isrc_country_codes c on c.isrc = upper(substring(t.data->'external_ids'->>'isrc', 1, 2))
group by 1;
    ''')

    op.execute('''
create materialized view spy_artist_countries as (
 select artist_spyid, country_codes
 from vw_spy_artist_countries
);
    ''')

def downgrade():
    op.execute('''
drop materialized view spy_artist_countries;

drop view vw_spy_artist_countries;
    ''')