"""drop_artist_activity_view

Revision ID: 40c74dc37bbd
Revises: 774aab53d4c0
Create Date: 2020-09-18 14:58:36.245912

"""

# revision identifiers, used by Alembic.
revision = '40c74dc37bbd'
down_revision = '774aab53d4c0'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('drop materialized view if exists artist_activity')


def downgrade():
    op.execute("""
        create MATERIALIZED view artist_activity as (
    SELECT
    'tw':: TEXT AS source,
    aa.arid,
    f.followee_eid:: TEXT AS artist_identifier,
    f.influencer_eid:: TEXT AS influencer_identifier,
    f.seen_at AS seen_at
    FROM followings f
    LEFT JOIN artist_associations aa ON aa.source = 'tw' AND aa.identifier = f.followee_eid:: TEXT

    UNION ALL

    SELECT
    'sc':: TEXT AS source,
    aa.arid,
    f.artist_scid:: TEXT AS artist_identifier,
    f.influencer_scid:: TEXT AS influencer_identifier,
    f.seen_at AS seen_at
    FROM sc_followings f
    LEFT JOIN artist_associations aa ON aa.source = 'sc' AND aa.identifier = f.artist_scid:: TEXT

    UNION ALL

    SELECT
    'sc':: TEXT AS source,
    aa.arid,
    t.artist_scid:: TEXT AS artist_identifier,
    f.influencer_scid:: TEXT AS influencer_identifier,
    MIN (f.seen_at) AS seen_at
    FROM sc_likes f JOIN sc_tracks t ON f.track_scid = t.scid
    LEFT JOIN artist_associations aa ON aa.source = 'sc' AND aa.identifier = t.artist_scid:: TEXT
    WHERE NOT EXISTS ( SELECT 1 FROM sc_followings fol WHERE f.influencer_scid = fol.influencer_scid AND fol.artist_scid = t.artist_scid)
    GROUP BY 1, 2, 3, 4

    UNION ALL

    SELECT
    'in':: TEXT AS source,
    aa.arid,
    f.followee_inid:: TEXT AS artist_identifier,
    f.followed_by_inid:: TEXT AS influencer_identifier,
    f.first_seen AS seen_at
    FROM in_follows f
    LEFT JOIN artist_associations aa ON aa.source = 'sc' AND aa.identifier = f.followee_inid:: TEXT

    UNION ALL

    SELECT
    'spy':: TEXT AS source,
    aa.arid,
    t.primary_artist_spyid:: TEXT AS artist_identifier,
    f.playlist_spyid:: TEXT AS influencer_identifier,
    f.first_seen AS seen_at
    FROM spy_playlist_track f
    JOIN spy_tracks t ON t.spyid = f.track_spyid
    LEFT JOIN artist_associations aa ON aa.source = 'sc' AND aa.identifier = t.primary_artist_spyid:: TEXT
    )
    WITH NO DATA""")

    op.execute("""create index artist_activity_idx1 on artist_activity(arid);""")
    op.execute("""create index artist_activity_idx2 on artist_activity(source, artist_identifier);""")
    op.execute("""create index artist_activity_idx3 on artist_activity(source, influencer_identifier);""")
    op.execute("""create index artist_activity_idx4 on artist_activity(seen_at);""")