"""merge_CMG_Legacy_labels

Revision ID: ff131364a862
Revises: 7b5996540a6d
Create Date: 2021-05-31 10:26:13.759419

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import select, func
from sqlalchemy.dialects.postgresql import insert

from migrations.utils import get_table

revision = 'ff131364a862'
down_revision = '0ec001ff264b'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()

    labels_table = get_table(bind, "Label")
    marketing_accounts_table = get_table(bind, "MarketingAccount")
    projects_table = get_table(bind, "Project")
    user_label_table = get_table(bind, "UserLabel")
    prs_map_table = get_table(bind, "RawPRSPurchaseOrdersMap")

    bind.execute(labels_table.update().where(labels_table.c.id == 44).values(name="CMG/Legacy"))
    bind.execute(marketing_accounts_table.update().where(marketing_accounts_table.c.label_id == 4).values(label_id=44))
    bind.execute(projects_table.update().where(projects_table.c.label_id == 4).values(label_id=44))

    # Fetch all Users with Legacy label
    users_with_legacy_label = bind.execute(
        select([func.distinct(user_label_table.c.user_id)])
        .select_from(user_label_table)
        .where(user_label_table.c.label_id == 4)
    ).fetchall()

    # Drop User to Legacy label connections
    bind.execute(user_label_table.delete().where(user_label_table.c.label_id == 4))

    # Insert User to Legacy label connections if needed
    for user_id in users_with_legacy_label:
        bind.execute(
            insert(user_label_table).values(user_id=user_id[0], label_id=44).on_conflict_do_nothing()
        )
    merged_rep_owners = ["D239", "D312", "C585", "D311", "D360", "D362", "D469", "D580", "E126", "E890",
                         "F130", "F462", "D398", "F142", "D418", "D458", "D464", "E926", "E931", "D821",
                         "D460", "D489", "D420", "J640", "C545", "C555", "C556", "C558", "C569", "D392",
                         "D365", "C188", "C567", "D820", "D493", "D502", "E895", "E949", "D470", "D506",
                         "D510", "D402", "D482", "D498", "D639", "E903", "F393", "D593", "K100", "M699",
                         "C554", "C589", "C620", "D356", "D394", "C592", "D363", "C615", "D491", "E894",
                         "D481", "E902", "E948", "F355", "F357", "D486", "E925", "F243", "D371", "D814",
                         "D598", "C549", "C633", "D189", "D387", "A941", "C587", "D025", "D261", "D355",
                         "C577", "C625", "D268", "D281", "D367", "C564", "D354", "D429", "D454", "F545",
                         "D462", "E933", "H009", "D492", "D403", "D421", "D509", "E938", "E916", "D504",
                         "D478", "H422", "M670", "D361", "D375", "D381", "C550", "D184", "D185", "D369",
                         "D379", "D484", "D485", "F356", "F747", "D417", "D483", "D487", "D591", "F055",
                         "F556", "E910", "D416", "D579", "D609", "E900", "F137", "C285", "D216", "C604",
                         "C540", "C591", "D179", "D395", "B330", "C521", "C536", "D412", "D473", "F123",
                         "F353", "D488", "D587", "D466", "D490", "D529", "V670", "C130", "C579", "D353",
                         "C568", "D188", "D186", "D446", "E031", "D406", "D497", "D576", "D683", "D494",
                         "G192", "D468", "D501", "C541", "C563", "B185", "C544", "D257", "D342", "C517",
                         "D183", "E932", "D505", "D574", "E892", "D507", "D517", "E905", "E929", "D496",
                         "H132", "G102", "G103", "M229", "M844"]
    bind.execute(
        labels_table.update()
        .where(labels_table.c.id == 44)
        .values(child_rep_owner_keys=merged_rep_owners)
    )

    bind.execute(prs_map_table.update().where(prs_map_table.c.label_id == 4).values(label_id=44))

    bind.execute(labels_table.delete().where(labels_table.c.id == 4))


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###