"""curated lists

Revision ID: c237ce8bd100
Revises: 2f6b188e024d
Create Date: 2018-08-02 09:21:17.256715

"""

# revision identifiers, used by Alembic.
revision = 'c237ce8bd100'
down_revision = '2f6b188e024d'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('curated_lists',
    sa.Column('list_id', sa.String(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('list_id')
    )
    op.create_table('curated_list_items',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('list_id', sa.String(), nullable=False),
    sa.Column('track_scid', sa.Integer(), nullable=True),
    sa.Column('video_ytid', sa.String(), nullable=True),
    sa.Column('track_spyid', sa.String(), nullable=True),
    sa.ForeignKeyConstraint(['list_id'], ['curated_lists.list_id'], ),
    sa.ForeignKeyConstraint(['track_scid'], ['sc_tracks.scid'], ),
    sa.ForeignKeyConstraint(['track_spyid'], ['spy_tracks.spyid'], ),
    sa.ForeignKeyConstraint(['video_ytid'], ['yt_videos.ytid'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('list_id', 'track_scid', 'video_ytid', 'track_spyid', name='curated_list_item_uniq_idx'),
    sa.CheckConstraint(
        '(track_scid is not null)::int + (video_ytid is not null)::int + (track_spyid is not null)::int = 1',
        name='curated_list_only_one_track_per_item'
    ),
    )


    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('curated_list_items')
    op.drop_table('curated_lists')
    ### end Alembic commands ###