"""campaign_owner

Revision ID: 028133698746
Revises: d22221aa8d6b
Create Date: 2019-11-19 15:45:43.288601

"""
from alembic import op
import sqlalchemy as sa

from models.user import User

# revision identifiers, used by Alembic.
revision = '028133698746'
down_revision = 'd22221aa8d6b'
branch_labels = None
depends_on = None


users_data = [
    {
        "name": "Ricardo Chamberlain",
        "email": "ricardo.chamberlain@sonymusic.com"
    },
    {
        "name": "Francesca Trullàs",
        "email": "francesca.trullas@sonymusic.com"
    },
    {
        "name": "DEV Marketing User",
        "email": "dev-marketing-user-1@example.com"
    },
    {
        "name": "darya.biryukova@dataart.com",
        "email": "darya.biryukova@dataart.com"
    },
    {
        "name": "Aileen Fernandez",
        "email": "aileen.fernandez@sonymusic.com"
    },
    {
        "name": "Paolo Bettaglio",
        "email": "paolo.bettaglio@sonymusic.com"
    },
    {
        "name": "Jose Abreu",
        "email": "jose.abreu@sonymusic.com"
    },
    {
        "name": "Kathryn Sullivan",
        "email": "kathryn.sullivan@aristarecordings.com"
    },
    {
        "name": "Caroline Abs",
        "email": "caroline.abs@sonymusic.com"
    },
    {
        "name": "Ethan Geltzer",
        "email": "ethan.geltzer@aristarecordings.com"
    },
    {
        "name": "Brian Vinikoor",
        "email": "brian.vinikoor@aristarecordings.com"
    },
    {
        "name": "Ellen Mangan",
        "email": "ellen.mangan@aristarecordings.com"
    },
    {
        "name": "dev-marketing-user-2@example.com",
        "email": "dev-marketing-user-2@example.com"
    },
    {
        "name": "dev-marketing-user-ari@example.com",
        "email": "dev-marketing-user-ari@example.com"
    },
    {
        "name": "dev-marketing-user-reg@example.com",
        "email": "dev-marketing-user-reg@example.com"
    },
    {
        "name": "Katy Wong",
        "email": "katy.wong@aristarecordings.com"
    },
    {
        "name": "Mazi Massachi",
        "email": "mazi.massachi.sme@sonymusic.com"
    },
    {
        "name": "Blocked by AP-1414, case 2",
        "email": "sinallcom+x2@gmail.com"
    },
    {
        "name": "Katherine Dejesus",
        "email": "katherine.dejesus.sme@sonymusic.com"
    },
    {
        "name": "Spencer Swan",
        "email": "spencer.swan.sme@sonymusic.com"
    },
    {
        "name": "Eugenia Sproatt",
        "email": "eugenia.sproatt@sonymusic.com"
    },
    {
        "name": "dev-marketing-user-lat@example.com",
        "email": "dev-marketing-user-lat@example.com"
    },
    {
        "name": "natalie.delrosal.sme@sonymusic.com",
        "email": "natalie.delrosal.sme@sonymusic.com"
    },
    {
        "name": "test-user-1@example.com",
        "email": "test-user-1@example.com"
    },
    {
        "name": "stg-marketing-user-1@example.com",
        "email": "stg-marketing-user-1@example.com"
    },
    {
        "name": "test0@gmail.com",
        "email": "test0@gmail.com"
    },
    {
        "name": "denis.chernysh@dataart.com",
        "email": "denis.chernysh@dataart.com"
    },
    {
        "name": "denis.chernysh@dataart.com",
        "email": "denis.chernysh@dataart.com"
    },
    {
        "name": "julia.prikhodko@dataart.com",
        "email": "julia.prikhodko@dataart.com"
    },
    {
        "name": "julia.prikhodko@dataart.com",
        "email": "julia.prikhodko@dataart.com"
    },
    {
        "name": "dchernysh@dataart.com",
        "email": "dchernysh@dataart.com"
    },
    {
        "name": "dchernysh@dataart.com",
        "email": "dchernysh@dataart.com"
    },
    {
        "name": "svetlana.shubina@dataart.com",
        "email": "svetlana.shubina@dataart.com"
    },
    {
        "name": "test@test.com",
        "email": "test@test.com"
    },
    {
        "name": "test@test.com",
        "email": "test@test.com"
    },
    {
        "name": "testuser@test.com",
        "email": "testuser@test.com"
    },
    {
        "name": "blocking@test.com",
        "email": "blocking@test.com"
    },
    {
        "name": "dev-apollo-user1@example.com",
        "email": "dev-apollo-user1@example.com"
    },
    {
        "name": "sinallcom+x3@gmail.com",
        "email": "sinallcom+x3@gmail.com"
    },
    {
        "name": "sinallcom+x4@gmail.com",
        "email": "sinallcom+x4@gmail.com"
    },
    {
        "name": "sinallcom+x4@gmail.com",
        "email": "sinallcom+x4@gmail.com"
    },
    {
        "name": "sinallcom+x1@gmail.com",
        "email": "sinallcom+x1@gmail.com"
    },
    {
        "name": "svyatoslav.potyeyenko@dataart.com",
        "email": "svyatoslav.potyeyenko@dataart.com"
    },
    {
        "name": "andrenewgarden@gmail.com",
        "email": "andrenewgarden@gmail.com"
    },
    {
        "name": "dev-marketing-user-3@example.com",
        "email": "dev-marketing-user-3@example.com"
    },

]


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('Campaign', sa.Column('owner_id', sa.Integer(), nullable=False, server_default="1"))
    op.create_foreign_key(op.f('fk_Campaign_owner_id_User'), 'Campaign', 'User', ['owner_id'], ['id'])

    op.add_column('User', sa.Column('name', sa.String(), nullable=False, server_default=""))
    op.add_column('User', sa.Column('phone', sa.String(), nullable=False, server_default=""))

    bind = op.get_bind()

    for user_data in users_data:
        bind.execute('''
            UPDATE "User" SET name = '{name}' WHERE lower(email) = '{email}'
        '''.format(name=user_data["name"], email=user_data["email"]))

    bind.execute(
        '''UPDATE "Campaign" SET owner_id = 1 WHERE create_user_id = 'system';'''
    )

    bind.execute(
      'UPDATE "Campaign" as campaigns SET owner_id = u.id FROM "User" as u WHERE campaigns.create_user_id = u.auth0_id'
    )

    op.drop_constraint('fk_Campaign_contact_id_Contact', 'Campaign', type_='foreignkey')
    op.drop_column('Campaign', 'contact_id')
    op.drop_table('Contact')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('User', 'phone')
    op.drop_column('User', 'name')

    op.drop_constraint(op.f('fk_Campaign_owner_id_User'), 'Campaign', type_='foreignkey')
    op.drop_column('Campaign', 'owner_id')

    op.add_column('Campaign', sa.Column('contact_id', sa.INTEGER(), autoincrement=False, nullable=True))

    op.create_table('Contact',
        sa.Column('id', sa.INTEGER(), server_default=sa.text('nextval(\'"Contact_id_seq"\'::regclass)'), autoincrement=True, nullable=False),
        sa.Column('name', sa.VARCHAR(), nullable=True),
        sa.Column('email', sa.VARCHAR(), nullable=True),
        sa.Column('phone', sa.VARCHAR(),  nullable=True),
        sa.PrimaryKeyConstraint('id', name='pk_Contact'),
        sa.UniqueConstraint('name', 'email', 'phone', name='uq_Contact_name')
    )

    op.create_foreign_key('fk_Campaign_contact_id_Contact', 'Campaign', 'Contact', ['contact_id'], ['id'])
    # ### end Alembic commands ###