"""migrate_gras_projects_to_label_space

Revision ID: 6774f4d2dcc5
Revises: 32e7ac7cea2b
Create Date: 2020-08-27 15:04:41.715960

"""
from alembic import op
import sqlalchemy as sa
import random

# revision identifiers, used by Alembic.
revision = '6774f4d2dcc5'
down_revision = '0e0e885f520c'
branch_labels = None
depends_on = None


def upgrade():
    op.add_column('GRASProject', sa.Column('label_id', sa.Integer(), nullable=True))
    bind = op.get_bind()

    rep_owners_with_label_space =  bind.execute(
        '''
            SELECT rep_owner.id, labels.id
                FROM "RepertoireOwner" AS rep_owner
                LEFT OUTER JOIN (SELECT id, external_id FROM "Label") AS labels ON rep_owner.external_id = labels.external_id
                WHERE rep_owner.external_id = labels.external_id
        '''
    ).fetchall()

    for rep_owner_id, label_id in rep_owners_with_label_space:
        bind.execute(
            f'''
                UPDATE "GRASProject"
                    SET label_id = {label_id}
                    WHERE repertoire_owner_id = {rep_owner_id}
            '''
        )

    rep_owners_without_label_space = bind.execute(
        '''
            SELECT id FROM "RepertoireOwner" WHERE external_id NOT IN (SELECT external_id FROM "Label")
        '''
    ).fetchall()

    for rep_owner_id in rep_owners_without_label_space:
        _, label_id = random.sample(rep_owners_with_label_space, 1)[0]
        bind.execute(
            f'''
                UPDATE "GRASProject"
                    SET label_id = {label_id}
                    WHERE repertoire_owner_id = {rep_owner_id[0]}
            '''
        )
    op.alter_column('GRASProject', 'label_id', existing_type=sa.Integer(), nullable=False)
    op.drop_column('GRASProject', 'repertoire_owner_id')
    op.create_foreign_key(op.f('fk_GRASProject_label_id_Label'), 'GRASProject', 'Label', ['label_id'], ['id'])
    op.drop_table('RepertoireOwner')

def downgrade():
    op.drop_column('GRASProject', 'label_id')
    op.add_column('GRASProject', sa.Column('repertoire_owner_id', sa.Integer(), nullable=True))