"""remove_unused_sc_track_indexes

Revision ID: 161405e5d7ef
Revises: b37cd8f013dc
Create Date: 2020-11-17 13:33:32.293797

"""

# revision identifiers, used by Alembic.
revision = '161405e5d7ef'
down_revision = '7e677b022eeb'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa

'''
create index if not exists  sc_tracks_artist_idx on sc_tracks (artist_scid);

create index if not exists  sc_tracks_created_at_scid_idx on sc_tracks (created_at, scid);

create index if not exists  sc_track_name_trgm_idx	on sc_tracks (name);

create index if not exists  sc_track_name_gin on sc_tracks (name);

create index if not exists  sc_track_name_upper_gin on sc_tracks (upper(name::text));

create index if not exists  sc_tracks_charts_idx1 on sc_tracks (artist_scid, duration, created_at, (COALESCE(data ->> 'playback_count'::text, '0'::text)::integer), (COALESCE(data ->> 'likes_count'::text, '0'::text)::integer), (COALESCE(data ->> 'comment_count'::text, '0'::text)::integer), scid);

create index if not exists  sc_tracks_genre_scid_idx on sc_tracks (genre, scid);

create index if not exists  sc_tracks_first_seen_scid_idx on sc_tracks (first_seen, scid);

create index if not exists  sc_tracks_nz_scid on sc_tracks (created_at, scid) where (artist_scid <> 0);

create index if not exists  sc_track_plays_as_of on sc_tracks (created_at, ((data ->> 'playback_count'::text)::integer), duration);

create index if not exists  sc_tracks_under_7_min on sc_tracks (created_at, ((data ->> 'playback_count'::text)::integer), scid) where (duration < ((7 * 60) * 1000));

create index if not exists  sc_tracks_7_min_or_less on sc_tracks (created_at, last_updated, ((data ->> 'playback_count'::text)::integer), scid) where (duration <= 420000);

create index if not exists  sct_recent_bigness on sc_tracks (last_playback_count, created_at, scid) where ((duration <= 420000) AND (last_playback_count >= 100));

# used for the promoting tracks logic:
create index if not exists  sc_tracks_not_promoted on sc_tracks ((((data -> 'user'::text) ->> 'id'::text)::integer), scid)	where (artist_scid = 0);

'''

def upgrade():
    op.execute('drop index if exists sc_tracks_genre_scid_idx')
    op.execute('drop index if exists sc_tracks_under_7_min')
    op.execute('drop index if exists sc_tracks_7_min_or_less')
    op.execute('drop index if exists sc_tracks_artist_idx')
    op.execute('drop index if exists sc_track_plays_as_of')
    op.execute('drop index if exists sc_tracks_first_seen_scid_idx')
    op.execute('drop index if exists sc_tracks_created_at_scid_idx')
    op.execute('drop index if exists sc_track_stats_44_as_of_idx')
    op.execute('drop index if exists sc_track_stats_43_as_of_idx')
    op.execute('drop index if exists sc_track_stats_45_as_of_idx')
    op.execute('drop index if exists sc_tracks_nz_scid')
    op.execute('drop index if exists sc_users_charts_idx1')
    op.execute('drop index if exists sct_recent_bigness')
    op.execute('drop index if exists sc_track_name_trgm_idx')
    op.execute('drop index if exists sc_track_name_upper_gin')




def downgrade():
    op.execute('create index sc_tracks_genre_scid_idx on sc_tracks (genre, scid)')
    op.execute('''create index sc_tracks_under_7_min
    on sc_tracks (created_at, ((data ->> 'playback_count'::text)::integer), scid)
    where (duration < ((7 * 60) * 1000));
    ''')
    op.execute('''create index sc_tracks_7_min_or_less
    on sc_tracks (created_at, last_updated, ((data ->> 'playback_count'::text)::integer), scid)
    where (duration <= 420000);
    ''')
    op.execute('''create index sc_tracks_artist_idx
    on sc_tracks (artist_scid);
    ''')
    op.execute('''
    create index sc_track_plays_as_of
    on sc_tracks (created_at, ((data ->> 'playback_count'::text)::integer), duration);
    ''')
    op.execute('''create index sc_tracks_first_seen_scid_idx
    on sc_tracks (first_seen, scid);
    ''')
    op.execute('''
        create index sc_tracks_created_at_scid_idx on sc_tracks (created_at, scid);
    ''')

    op.execute('''create index sc_tracks_nz_scid
        on sc_tracks (created_at, scid)
        where (artist_scid <> 0);
    ''')
    op.execute('''
    create index sc_users_charts_idx1
	on sc_users (active, (first_seen::date), (COALESCE(data ->> 'followers_count'::text, '0'::text)::integer), lower(COALESCE(data ->> 'country'::text, '00'::text)), scid);

    ''')
    op.execute('''create index sct_recent_bigness
        on sc_tracks (last_playback_count, created_at, scid)
        where ((duration <= 420000) AND (last_playback_count >= 100));
    ''')

    op.execute('''
    create index if not exists  sc_track_name_trgm_idx	on sc_tracks (name);
    create index if not exists  sc_track_name_upper_gin on sc_tracks (upper(name::text));
    ''')