"""matview artist activity

Revision ID: 79d4fbc64035
Revises: 15617871f51f
Create Date: 2018-03-02 13:00:16.443967

"""

# revision identifiers, used by Alembic.
revision = '79d4fbc64035'
down_revision = '15617871f51f'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    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
)
    """)

    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);""")


def downgrade():
    op.execute("""drop MATERIALIZED view artist_activity""")