"""custom_item

Revision ID: e9061beede7d
Revises: 4c2fc51d95fd
Create Date: 2018-08-14 17:37:35.111667

"""

# revision identifiers, used by Alembic.
revision = 'e9061beede7d'
down_revision = '4c2fc51d95fd'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('chart_custom_items',
    sa.Column('item_id', sa.Integer(), nullable=False),
    sa.Column('title', sa.String(), nullable=False),
    sa.Column('headline', sa.String(), nullable=False),
    sa.Column('genre', sa.String(), nullable=True),
    sa.Column('url', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('now()')),
    sa.PrimaryKeyConstraint('item_id')
    )

    op.add_column('curated_list_items', sa.Column('custom_item_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'curated_list_items', 'chart_custom_items', ['custom_item_id'], ['item_id'])

    op.execute('alter table curated_list_items drop constraint curated_list_only_one_track_per_item')

    op.execute('''
    alter table curated_list_items 
    add CONSTRAINT curated_list_only_one_track_per_item CHECK (
      (track_scid is not null)::int 
      + (video_ytid is not null)::int 
      + (track_spyid is not null)::int 
      + (custom_item_id is not null)::int = 1);
    ''')

    op.execute('drop index curated_list_item_unique_in_list_idx')

    op.execute('''
    
create unique index curated_list_item_unique_in_list_idx on curated_list_items (
  list_id,
  COALESCE (track_scid, -1),
  COALESCE (video_ytid, ''),
  COALESCE (track_spyid, ''),
  COALESCE (custom_item_id, -1)
)
    ''')


    ### end Alembic commands ##

def downgrade():
    # op.drop_constraint(None, 'curated_list_items', type_='foreignkey')
    # op.drop_column('curated_list_items', 'custom_item_id')
    op.drop_table('chart_custom_items')

    ### end Alembic commands ###