"""add_names_column_in_accounts_table

Revision ID: c4b5b57dbfeb
Revises: e9fa68d476a6
Create Date: 2021-09-22 09:45:22.379163

"""
from alembic import op
import sqlalchemy as sa

data = [
    {"id": "10152362231207721", "name": "Sony Music Argentina", "source": "facebook"},
    {"id": "10152480687163981", "name": "SME Marketing", "source": "facebook"},
    {"id": "10153208127585452", "name": "Four Music", "source": "facebook"},
    {"id": "10154355637658586", "name": "Sony Music - iTunes Retargeting", "source": "facebook"},
    {"id": "10154355643988586", "name": "Sony Music - Spotify Retargeting", "source": "facebook"},
    {"id": "10154360480058586", "name": "Sony Music Belgium", "source": "facebook"},
    {"id": "10154633859042935", "name": "Filtr", "source": "facebook"},
    {"id": "1021693261336794", "name": "Legacy Recordings (US)", "source": "facebook"},
    {"id": "104202143076853", "name": "Sony Music Brasil", "source": "facebook"},
    {"id": "1047672435265977", "name": "Sony Music NL", "source": "facebook"},
    {"id": "105108209658736", "name": "Sony Music Nashville", "source": "facebook"},
    {"id": "1073652073", "name": "Sony Music Brasil", "source": "google"},
    {"id": "108170885940775", "name": "Epic Records Retargeting", "source": "facebook"},
    {"id": "1359978377408397", "name": "Sony Music Entertainment", "source": "facebook"},
    {"id": "1376013932614746", "name": "Sony Latin Retargeting", "source": "facebook"},
    {"id": "1376098445939200", "name": "Sony International Retargeting", "source": "facebook"},
    {"id": "1377217462493790", "name": "RCA Records Retargeting", "source": "facebook"},
    {"id": "1383134378632539", "name": "Sony Masterworks Retargeting", "source": "facebook"},
    {"id": "1387355094731337", "name": "Masterworks - Ad Credits", "source": "facebook"},
    {"id": "157619175665308", "name": "SME Streaming", "source": "facebook"},
    {"id": "1578898082220650", "name": "Columbia - Ad Credits", "source": "facebook"},
    {"id": "1708809069252994", "name": "Sony Music - Filtr Live", "source": "facebook"},
    {"id": "183005119794849", "name": "Gupta – Sony US Latin – Catalog", "source": "facebook"},
    {"id": "1887479554680002", "name": "Sony Music Nashville 2", "source": "facebook"},
    {"id": "192227596", "name": "Sony Music Portugal", "source": "facebook"},
    {"id": "210070156974883", "name": "Gupta – Sony US Latin – Frontline", "source": "facebook"},
    {"id": "2172078946186087", "name": "Epic - Ad Credits", "source": "facebook"},
    {"id": "2191370510920980", "name": "RCA UK - Ad Credits", "source": "facebook"},
    {"id": "220678908623791", "name": "RCA (US)", "source": "facebook"},
    {"id": "2257399607879318", "name": "Filtr Live / ArtIntel", "source": "facebook"},
    {"id": "227960311301140", "name": "Sony Music Centro America", "source": "facebook"},
    {"id": "2333825793", "name": "Epic Records", "source": "google"},
    {"id": "2351639881791021", "name": "SME Andes / Ads", "source": "facebook"},
    {"id": "237818583774340", "name": "Columbia UK - Ad Credits", "source": "facebook"},
    {"id": "241279976492311", "name": "Partner Development - Measurement Testing", "source": "facebook"},
    {"id": "2413196617", "name": "Sony Music México", "source": "google"},
    {"id": "2437040523002171", "name": "Latin America - Priority and Catalog", "source": "facebook"},
    {"id": "249200361", "name": "Columbia Records Retargeting", "source": "facebook"},
    {"id": "249205600", "name": "Legacy Records Retargeting", "source": "facebook"},
    {"id": "272365527010225", "name": "SME Spain – Ad Credits", "source": "facebook"},
    {"id": "2802154940", "name": "Sony Music Portugal | Principal", "source": "google"},
    {"id": "296350974500581", "name": "Wavo - RCA Records", "source": "facebook"},
    {"id": "3029121817", "name": "US-Latin", "source": "google"},
    {"id": "3098880474", "name": "Sony Music US Latin", "source": "google"},
    {"id": "355257005256501", "name": "SME Germany // Ad Credits", "source": "facebook"},
    {"id": "403395130429589", "name": "SME UK / Commercial Music Group - Ad Credits", "source": "facebook"},
    {"id": "4732633891", "name": "Sony Music Latin Iberia Region", "source": "google"},
    {"id": "5176914881", "name": "SONY Music Spain", "source": "google"},
    {"id": "562408950939930", "name": "Commercial Music Group UK - Ad Credits", "source": "facebook"},
    {"id": "580362375784985", "name": "Certified - Ad Credits", "source": "facebook"},
    {"id": "6449900252", "name": "Legacy Recordings", "source": "google"},
    {"id": "6483554878", "name": "Arista Records", "source": "google"},
    {"id": "654207068366389", "name": "Fan Stage Store", "source": "facebook"},
    {"id": "768164510232785", "name": "4th Floor Creative - Ad Credits", "source": "facebook"},
    {"id": "8653387137", "name": "Columbia Records", "source": "google"},
    {"id": "9038993320", "name": "Sony Latin Retargeting", "source": "google"},
    {"id": "9963444288", "name": "Sony Music Argentina", "source": "google"},
]

marketing_accounts_black_list = [
    "1905498269540420",
    "1704153223049150",
    "2121729928142320",
    "2390352294378610",
    "1021693261336790",
]


# revision identifiers, used by Alembic.
revision = 'c4b5b57dbfeb'
down_revision = 'cc02d87147ce'
branch_labels = None
depends_on = None


def upgrade():
    op.add_column('MarketingAccount', sa.Column('name', sa.String()))
    op.add_column('MarketingAccount', sa.Column('source', sa.String()))
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    marketing_account_table = meta.tables["MarketingAccount"]
    for item in data:
        op.execute(
                marketing_account_table.update().where(marketing_account_table.c.external_id == item["id"])
                .values(name=item["name"], source=item["source"])
        )

    bind.execute(
        marketing_account_table.delete().where(
            marketing_account_table.c.external_id.in_(marketing_accounts_black_list)
        )
    )


def downgrade():
    op.drop_column('MarketingAccount', 'name')
    op.drop_column('MarketingAccount', 'source')