"""merge_repowners_to_labels

Revision ID: 0e0e885f520c
Revises: f02d5471cd0f
Create Date: 2020-08-27 11:26:50.635901

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql.expression import bindparam

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

labels_mappings = [
        {"id":1, "name": "Columbia Records", "external_id": "D755", "external_name": "Columbia Records"},
        {"id":2, "name":"Epic Records", "external_id": "D042", "external_name": "Epic Records"},
        {"id":3, "name":"RCA Records", "external_id": "L784", "external_name": "RCA Records"},
        {"id":4, "name":"Legacy", "external_id": "M603", "external_name": "Legacy"},
        {"id":5, "name":"SME Nashville", "external_id": "E287", "external_name": "SME Nashville"},
        {"id":6, "name":"SME US Latin", "external_id": "E008", "external_name": "SME US Latin"},
        {"id":7, "name":"Arista Records", "external_id": "M823", "external_name": "Arista Records"}
    ]

def upgrade():
    op.add_column('Label', sa.Column('external_id', sa.String(), nullable=True))
    op.add_column('Label', sa.Column('external_name', sa.String(), nullable=True))
    op.create_unique_constraint('uq_Label_external_id', 'Label', ['external_id'])
    bind = op.get_bind()
    ## Remove “Latin Iberia” space id = 8
    bind.execute(
        '''
            DELETE FROM "UserLabel" WHERE label_id = 8;
            DELETE FROM "Project" WHERE label_id = 8;
            DELETE FROM "Label" WHERE id = 8;
        '''
    )

    bind.execute(
        '''
            UPDATE "Label" SET name='Legacy' WHERE name='CMG';
            UPDATE "Label" SET name='SME Nashville' WHERE name='Nashville';
            UPDATE "Label" SET name='SME US Latin' WHERE name='US Latin';
            UPDATE "Label" SET name='Arista Records' WHERE name='Arista';

        '''
    )

    rs = bind.execute(
        '''
            SELECT id, external_id, name
                FROM "RepertoireOwner"
                WHERE name IN (
                    'Columbia Records',
                    'Epic Records',
                    'RCA Records',
                    'Legacy',
                    'SME Nashville',
                    'SME US Latin',
                    'Arista Records'
                )
        '''
    ).fetchall()

    if not rs:
        for label in labels_mappings:
            bind.execute(
                f'''
                    UPDATE "Label"
                        SET external_id={"'{}'".format(label["external_id"])},
                            external_name={"'{}'".format(label["external_name"])}
                        WHERE id={"'{}'".format(label["id"])}
                '''
            )

    for _, external_id, name in rs:
        bind.execute(
            f'''
                UPDATE "Label"
                    SET external_id={"'{}'".format(external_id)},
                        external_name={"'{}'".format(name)}
                    WHERE name={"'{}'".format(name)}
            '''
        )

    resp = bind.execute('''SELECT id, external_id, name FROM "RepertoireOwner" WHERE name IN (
        'Sony Masterworks',
        'SME Canada',
        'SME US',
        'SME USA',
        'SME Global Digital Business',
        'SME International US',
        'SME Germany',
        'SME Denmark',
        'SME UK',
        'SME International UK',
        'SME France',
        'SME Sweden',
        'SME Finland',
        'SME Italy',
        'SME Poland',
        'SME Norway',
        'SME Austria',
        'SME Switzerland',
        'SME Belgium',
        'SME Greece',
        'SME Russia',
        'SME Ireland',
        'SME Netherlands',
        'SME Brasil',
        'SME Mexico',
        'SME España',
        'SME Argentina',
        'SME Colombia',
        'SME Portugal',
        'SME Chile',
        'SME Venezuela',
        'SME Central America',
        'SME Antigua',
        'SME Uruguay'
    )''').fetchall()
    bind.execute('''ALTER SEQUENCE "Label_id_seq" RESTART WITH 8 ''')

    for _, external_id, name in resp:
        bind.execute(
            '''
                INSERT INTO "Label" (name, external_id, external_name) VALUES (%s, %s, %s);
            ''',
            tuple([name, external_id, name])
        )

    op.alter_column('Label', 'external_id', existing_type=sa.String(), nullable=False)
    op.alter_column('Label', 'external_id', existing_type=sa.String(), nullable=False)
    pass

def downgrade():
    op.drop_column('Label', 'external_id')
    op.drop_column('Label', 'external_name')