"""add_unique_constraint_for_artists_playlists

Revision ID: ab2eb7c0bd3b
Revises: b3da153c5d1e
Create Date: 2020-07-07 07:55:05.604853

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ab2eb7c0bd3b'
down_revision = 'b3da153c5d1e'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()

    # Fix duplicated Playlists
    result = conn.execute('''SELECT external_id FROM "Playlist" GROUP BY external_id HAVING COUNT(id) > 1''')
    duplicated_ids = [res[0] for res in result]
    for external_id in duplicated_ids:
        ids_result = conn.execute('''SELECT id FROM "Playlist" WHERE external_id='{}' '''.format(external_id))
        ids = [res[0] for res in ids_result]
        root_id = ids[0]
        ids_to_remove = ','.join(map(str, ids[1:]))
        conn.execute('''UPDATE "ProjectTargetItem" SET target_item_id={} WHERE target_item_id IN ({})'''.format(root_id, ids_to_remove))
        conn.execute('''DELETE FROM "Playlist" WHERE id IN ({})'''.format(ids_to_remove))

    op.create_unique_constraint(op.f('uq_Artist_external_id'), 'Artist', ['external_id'])
    op.create_unique_constraint(op.f('uq_Playlist_external_id'), 'Playlist', ['external_id'])
    op.drop_constraint('fk_Image_related_object_id_Polymorphable', 'Image', type_='foreignkey')
    op.create_foreign_key(op.f('fk_Image_related_object_id_Polymorphable'), 'Image', 'Polymorphable',
                          ['related_object_id'], ['id'], ondelete='CASCADE')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f('uq_Playlist_external_id'), 'Playlist', type_='unique')
    op.drop_constraint(op.f('uq_Artist_external_id'), 'Artist', type_='unique')
    op.drop_constraint(op.f('fk_Image_related_object_id_Polymorphable'), 'Image', type_='foreignkey')
    op.create_foreign_key('fk_Image_related_object_id_Polymorphable', 'Image', 'Polymorphable', ['related_object_id'],
                          ['id'])
    # ### end Alembic commands ###