"""partition_spy_tables

Revision ID: 7e4ca0d58ad7
Revises: 9d15a7d63b16
Create Date: 2020-12-02 13:18:06.445338

"""

# revision identifiers, used by Alembic.
revision = '7e4ca0d58ad7'
down_revision = '9d15a7d63b16'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
        -- cities:
        create table spy_artist_listener_cities_parts 
        (like spy_artist_listener_cities including defaults)
        partition by range(as_of);
        
        create index on spy_artist_listener_cities_parts (artist_spyid, as_of);
        
        create table spy_artist_listener_cities_default
        partition of spy_artist_listener_cities_parts default;
        
        create table spy_artist_listener_cities_2010
        partition of spy_artist_listener_cities_parts
        for values from ('2020-10-01') to ('2020-11-01');
        
        create table spy_artist_listener_cities_2011
        partition of spy_artist_listener_cities_parts
        for values from ('2020-11-01') to ('2020-12-01');
        
        create table spy_artist_listener_cities_2012
        partition of spy_artist_listener_cities_parts
        for values from ('2020-12-01') to ('2021-01-01');
        
        create table spy_artist_listener_cities_2101
        partition of spy_artist_listener_cities_parts
        for values from ('2021-01-01') to ('2021-02-01');
        
        -- playlists:
        create table spy_artist_listener_playlists_parts 
        (like spy_artist_listener_playlists including defaults)
        partition by range(as_of);
        
        create index on spy_artist_listener_playlists_parts (artist_spyid, as_of);
        
        create table spy_artist_listener_playlists_default
        partition of spy_artist_listener_playlists_parts default;
        
        create table spy_artist_listener_playlists_2010
        partition of spy_artist_listener_playlists_parts
        for values from ('2020-10-01') to ('2020-11-01');
        create table spy_artist_listener_playlists_2011
        partition of spy_artist_listener_playlists_parts
        for values from ('2020-11-01') to ('2020-12-01');
        create table spy_artist_listener_playlists_2012
        partition of spy_artist_listener_playlists_parts
        for values from ('2020-12-01') to ('2021-01-01');
        create table spy_artist_listener_playlists_2101
        partition of spy_artist_listener_playlists_parts
        for values from ('2021-01-01') to ('2021-02-01');      
    ''')


def downgrade():
    op.execute('''drop table if exists spy_artist_listener_playlists_parts''')
    op.execute('''drop table if exists spy_artist_listener_cities_parts''')