"""empty message

Revision ID: cee839a12c4a
Revises: 7903a539dcf2
Create Date: 2020-03-29 21:05:24.234674

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = 'cee839a12c4a'
down_revision = '7903a539dcf2'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('eventusers',
    sa.Column('db_created_at', sa.DateTime(), nullable=True),
    sa.Column('db_updated_at', sa.DateTime(), nullable=True),
    sa.Column('db_created_by', sa.String(length=64), nullable=True),
    sa.Column('db_updated_by', sa.String(length=64), nullable=True),
    sa.Column('guid', sa.GUID(), nullable=True),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(length=64), nullable=True),
    sa.Column('email', sa.String(length=128), nullable=True),
    sa.Column('locale', sa.String(length=32), nullable=True),
    sa.Column('about_me', sa.String(length=256), nullable=True),
    sa.Column('event_id', sa.Integer(), nullable=True),
    sa.Column('profile_picture_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['event_id'], ['events.id'], ),
    sa.ForeignKeyConstraint(['profile_picture_id'], ['images.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_eventusers_email'), 'eventusers', ['email'], unique=False)
    op.create_index(op.f('ix_eventusers_event_id'), 'eventusers', ['event_id'], unique=False)
    op.create_index(op.f('ix_eventusers_guid'), 'eventusers', ['guid'], unique=True)
    op.create_index(op.f('ix_eventusers_username'), 'eventusers', ['username'], unique=False)
    op.drop_column('users', 'timezone')
    # ### end Alembic commands ###
    
    # ### data migration ###
    op.execute('''INSERT eventusers (username, email, locale, about_me, profile_picture_id, event_id)
                  SELECT u.username, u.email, u.locale, u.about_me, u.profile_picture_id, eu.event_id
                  FROM users u, event_users eu
                  WHERE u.id=eu.user_id''')
                  
    op.drop_constraint('events_ibfk_1', 'events', type_='foreignkey')
    op.execute('''UPDATE 
                    events e 
                  SET e.accountant_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.id and u.username=eu.username and u.id=e.accountant_id )''')
    op.create_foreign_key(None, 'events', 'eventusers', ['accountant_id'], ['id'])
    
    op.drop_constraint('expenses_ibfk_4', 'expenses', type_='foreignkey')
    op.execute('''UPDATE 
                    expenses e 
                  SET e.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.event_id and u.username=eu.username and u.id=e.user_id )''')
    op.create_foreign_key(None, 'expenses', 'eventusers', ['user_id'], ['id'])
    
    op.drop_constraint('settlements_ibfk_5', 'settlements', type_='foreignkey')
    op.drop_constraint('settlements_ibfk_4', 'settlements', type_='foreignkey')
    op.execute('''UPDATE 
                    settlements s 
                  SET s.sender_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.sender_id )''')
    op.execute('''UPDATE 
                    settlements s 
                  SET s.recipient_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.recipient_id )''')
    op.create_foreign_key(None, 'settlements', 'eventusers', ['sender_id'], ['id'])
    op.create_foreign_key(None, 'settlements', 'eventusers', ['recipient_id'], ['id'])
    
    op.drop_constraint('posts_ibfk_2', 'posts', type_='foreignkey')
    op.execute('''UPDATE 
                    posts p      
                  SET p.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=p.event_id and u.username=eu.username and u.id=p.user_id )''')
    op.create_foreign_key(None, 'posts', 'eventusers', ['user_id'], ['id'])
    
    op.rename_table('expense_affected_users', 'expense_affected_users_old')
    op.create_table('expense_affected_users',
    sa.Column('expense_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['expense_id'], ['expenses.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['eventusers.id'], ),
    sa.PrimaryKeyConstraint('expense_id', 'user_id')
    )
    op.execute('''INSERT expense_affected_users (expense_id, user_id)
                  SELECT eau_o.expense_id, eu.id 
                  FROM users u, eventusers eu, expenses e, expense_affected_users_old eau_o WHERE eu.event_id=e.event_id and e.id=eau_o.expense_id and u.username=eu.username and u.id=eau_o.user_id''')
    op.drop_table('expense_affected_users_old')
    ### end of data migration ###
    
    
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('timezone', mysql.VARCHAR(collation='utf8mb4_unicode_ci', length=32), nullable=True))
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.create_foreign_key('settlements_ibfk_4', 'settlements', 'users', ['recipient_id'], ['id'])
    op.create_foreign_key('settlements_ibfk_5', 'settlements', 'users', ['sender_id'], ['id'])
    op.drop_constraint(None, 'posts', type_='foreignkey')
    op.create_foreign_key('posts_ibfk_2', 'posts', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'expenses', type_='foreignkey')
    op.create_foreign_key('expenses_ibfk_4', 'expenses', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'expense_affected_users', type_='foreignkey')
    op.create_foreign_key('expense_affected_users_ibfk_2', 'expense_affected_users', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'events', type_='foreignkey')
    op.create_foreign_key('events_ibfk_1', 'events', 'users', ['accountant_id'], ['id'])
    op.drop_index(op.f('ix_eventusers_username'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_guid'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_event_id'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_email'), table_name='eventusers')
    op.drop_table('eventusers')
    # ### end Alembic commands ###

e0397ed7e0fcc90cbc9d593a4b9af4d6fbd0c389
<mask token>

<mask token>


def upgrade():
    op.create_table('eventusers', sa.Column('db_created_at', sa.DateTime(),
        nullable=True), sa.Column('db_updated_at', sa.DateTime(), nullable=
        True), sa.Column('db_created_by', sa.String(length=64), nullable=
        True), sa.Column('db_updated_by', sa.String(length=64), nullable=
        True), sa.Column('guid', sa.GUID(), nullable=True), sa.Column('id',
        sa.Integer(), nullable=False), sa.Column('username', sa.String(
        length=64), nullable=True), sa.Column('email', sa.String(length=128
        ), nullable=True), sa.Column('locale', sa.String(length=32),
        nullable=True), sa.Column('about_me', sa.String(length=256),
        nullable=True), sa.Column('event_id', sa.Integer(), nullable=True),
        sa.Column('profile_picture_id', sa.Integer(), nullable=True), sa.
        ForeignKeyConstraint(['event_id'], ['events.id']), sa.
        ForeignKeyConstraint(['profile_picture_id'], ['images.id']), sa.
        PrimaryKeyConstraint('id'))
    op.create_index(op.f('ix_eventusers_email'), 'eventusers', ['email'],
        unique=False)
    op.create_index(op.f('ix_eventusers_event_id'), 'eventusers', [
        'event_id'], unique=False)
    op.create_index(op.f('ix_eventusers_guid'), 'eventusers', ['guid'],
        unique=True)
    op.create_index(op.f('ix_eventusers_username'), 'eventusers', [
        'username'], unique=False)
    op.drop_column('users', 'timezone')
    op.execute(
        """INSERT eventusers (username, email, locale, about_me, profile_picture_id, event_id)
                  SELECT u.username, u.email, u.locale, u.about_me, u.profile_picture_id, eu.event_id
                  FROM users u, event_users eu
                  WHERE u.id=eu.user_id"""
        )
    op.drop_constraint('events_ibfk_1', 'events', type_='foreignkey')
    op.execute(
        """UPDATE 
                    events e 
                  SET e.accountant_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.id and u.username=eu.username and u.id=e.accountant_id )"""
        )
    op.create_foreign_key(None, 'events', 'eventusers', ['accountant_id'],
        ['id'])
    op.drop_constraint('expenses_ibfk_4', 'expenses', type_='foreignkey')
    op.execute(
        """UPDATE 
                    expenses e 
                  SET e.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.event_id and u.username=eu.username and u.id=e.user_id )"""
        )
    op.create_foreign_key(None, 'expenses', 'eventusers', ['user_id'], ['id'])
    op.drop_constraint('settlements_ibfk_5', 'settlements', type_='foreignkey')
    op.drop_constraint('settlements_ibfk_4', 'settlements', type_='foreignkey')
    op.execute(
        """UPDATE 
                    settlements s 
                  SET s.sender_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.sender_id )"""
        )
    op.execute(
        """UPDATE 
                    settlements s 
                  SET s.recipient_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.recipient_id )"""
        )
    op.create_foreign_key(None, 'settlements', 'eventusers', ['sender_id'],
        ['id'])
    op.create_foreign_key(None, 'settlements', 'eventusers', [
        'recipient_id'], ['id'])
    op.drop_constraint('posts_ibfk_2', 'posts', type_='foreignkey')
    op.execute(
        """UPDATE 
                    posts p      
                  SET p.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=p.event_id and u.username=eu.username and u.id=p.user_id )"""
        )
    op.create_foreign_key(None, 'posts', 'eventusers', ['user_id'], ['id'])
    op.rename_table('expense_affected_users', 'expense_affected_users_old')
    op.create_table('expense_affected_users', sa.Column('expense_id', sa.
        Integer(), nullable=False), sa.Column('user_id', sa.Integer(),
        nullable=False), sa.ForeignKeyConstraint(['expense_id'], [
        'expenses.id']), sa.ForeignKeyConstraint(['user_id'], [
        'eventusers.id']), sa.PrimaryKeyConstraint('expense_id', 'user_id'))
    op.execute(
        """INSERT expense_affected_users (expense_id, user_id)
                  SELECT eau_o.expense_id, eu.id 
                  FROM users u, eventusers eu, expenses e, expense_affected_users_old eau_o WHERE eu.event_id=e.event_id and e.id=eau_o.expense_id and u.username=eu.username and u.id=eau_o.user_id"""
        )
    op.drop_table('expense_affected_users_old')


def downgrade():
    op.add_column('users', sa.Column('timezone', mysql.VARCHAR(collation=
        'utf8mb4_unicode_ci', length=32), nullable=True))
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.create_foreign_key('settlements_ibfk_4', 'settlements', 'users', [
        'recipient_id'], ['id'])
    op.create_foreign_key('settlements_ibfk_5', 'settlements', 'users', [
        'sender_id'], ['id'])
    op.drop_constraint(None, 'posts', type_='foreignkey')
    op.create_foreign_key('posts_ibfk_2', 'posts', 'users', ['user_id'], ['id']
        )
    op.drop_constraint(None, 'expenses', type_='foreignkey')
    op.create_foreign_key('expenses_ibfk_4', 'expenses', 'users', [
        'user_id'], ['id'])
    op.drop_constraint(None, 'expense_affected_users', type_='foreignkey')
    op.create_foreign_key('expense_affected_users_ibfk_2',
        'expense_affected_users', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'events', type_='foreignkey')
    op.create_foreign_key('events_ibfk_1', 'events', 'users', [
        'accountant_id'], ['id'])
    op.drop_index(op.f('ix_eventusers_username'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_guid'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_event_id'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_email'), table_name='eventusers')
    op.drop_table('eventusers')

<mask token>
revision = 'cee839a12c4a'
down_revision = '7903a539dcf2'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table('eventusers', sa.Column('db_created_at', sa.DateTime(),
        nullable=True), sa.Column('db_updated_at', sa.DateTime(), nullable=
        True), sa.Column('db_created_by', sa.String(length=64), nullable=
        True), sa.Column('db_updated_by', sa.String(length=64), nullable=
        True), sa.Column('guid', sa.GUID(), nullable=True), sa.Column('id',
        sa.Integer(), nullable=False), sa.Column('username', sa.String(
        length=64), nullable=True), sa.Column('email', sa.String(length=128
        ), nullable=True), sa.Column('locale', sa.String(length=32),
        nullable=True), sa.Column('about_me', sa.String(length=256),
        nullable=True), sa.Column('event_id', sa.Integer(), nullable=True),
        sa.Column('profile_picture_id', sa.Integer(), nullable=True), sa.
        ForeignKeyConstraint(['event_id'], ['events.id']), sa.
        ForeignKeyConstraint(['profile_picture_id'], ['images.id']), sa.
        PrimaryKeyConstraint('id'))
    op.create_index(op.f('ix_eventusers_email'), 'eventusers', ['email'],
        unique=False)
    op.create_index(op.f('ix_eventusers_event_id'), 'eventusers', [
        'event_id'], unique=False)
    op.create_index(op.f('ix_eventusers_guid'), 'eventusers', ['guid'],
        unique=True)
    op.create_index(op.f('ix_eventusers_username'), 'eventusers', [
        'username'], unique=False)
    op.drop_column('users', 'timezone')
    op.execute(
        """INSERT eventusers (username, email, locale, about_me, profile_picture_id, event_id)
                  SELECT u.username, u.email, u.locale, u.about_me, u.profile_picture_id, eu.event_id
                  FROM users u, event_users eu
                  WHERE u.id=eu.user_id"""
        )
    op.drop_constraint('events_ibfk_1', 'events', type_='foreignkey')
    op.execute(
        """UPDATE 
                    events e 
                  SET e.accountant_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.id and u.username=eu.username and u.id=e.accountant_id )"""
        )
    op.create_foreign_key(None, 'events', 'eventusers', ['accountant_id'],
        ['id'])
    op.drop_constraint('expenses_ibfk_4', 'expenses', type_='foreignkey')
    op.execute(
        """UPDATE 
                    expenses e 
                  SET e.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.event_id and u.username=eu.username and u.id=e.user_id )"""
        )
    op.create_foreign_key(None, 'expenses', 'eventusers', ['user_id'], ['id'])
    op.drop_constraint('settlements_ibfk_5', 'settlements', type_='foreignkey')
    op.drop_constraint('settlements_ibfk_4', 'settlements', type_='foreignkey')
    op.execute(
        """UPDATE 
                    settlements s 
                  SET s.sender_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.sender_id )"""
        )
    op.execute(
        """UPDATE 
                    settlements s 
                  SET s.recipient_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.recipient_id )"""
        )
    op.create_foreign_key(None, 'settlements', 'eventusers', ['sender_id'],
        ['id'])
    op.create_foreign_key(None, 'settlements', 'eventusers', [
        'recipient_id'], ['id'])
    op.drop_constraint('posts_ibfk_2', 'posts', type_='foreignkey')
    op.execute(
        """UPDATE 
                    posts p      
                  SET p.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=p.event_id and u.username=eu.username and u.id=p.user_id )"""
        )
    op.create_foreign_key(None, 'posts', 'eventusers', ['user_id'], ['id'])
    op.rename_table('expense_affected_users', 'expense_affected_users_old')
    op.create_table('expense_affected_users', sa.Column('expense_id', sa.
        Integer(), nullable=False), sa.Column('user_id', sa.Integer(),
        nullable=False), sa.ForeignKeyConstraint(['expense_id'], [
        'expenses.id']), sa.ForeignKeyConstraint(['user_id'], [
        'eventusers.id']), sa.PrimaryKeyConstraint('expense_id', 'user_id'))
    op.execute(
        """INSERT expense_affected_users (expense_id, user_id)
                  SELECT eau_o.expense_id, eu.id 
                  FROM users u, eventusers eu, expenses e, expense_affected_users_old eau_o WHERE eu.event_id=e.event_id and e.id=eau_o.expense_id and u.username=eu.username and u.id=eau_o.user_id"""
        )
    op.drop_table('expense_affected_users_old')


def downgrade():
    op.add_column('users', sa.Column('timezone', mysql.VARCHAR(collation=
        'utf8mb4_unicode_ci', length=32), nullable=True))
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.create_foreign_key('settlements_ibfk_4', 'settlements', 'users', [
        'recipient_id'], ['id'])
    op.create_foreign_key('settlements_ibfk_5', 'settlements', 'users', [
        'sender_id'], ['id'])
    op.drop_constraint(None, 'posts', type_='foreignkey')
    op.create_foreign_key('posts_ibfk_2', 'posts', 'users', ['user_id'], ['id']
        )
    op.drop_constraint(None, 'expenses', type_='foreignkey')
    op.create_foreign_key('expenses_ibfk_4', 'expenses', 'users', [
        'user_id'], ['id'])
    op.drop_constraint(None, 'expense_affected_users', type_='foreignkey')
    op.create_foreign_key('expense_affected_users_ibfk_2',
        'expense_affected_users', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'events', type_='foreignkey')
    op.create_foreign_key('events_ibfk_1', 'events', 'users', [
        'accountant_id'], ['id'])
    op.drop_index(op.f('ix_eventusers_username'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_guid'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_event_id'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_email'), table_name='eventusers')
    op.drop_table('eventusers')

<mask token>
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
revision = 'cee839a12c4a'
down_revision = '7903a539dcf2'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table('eventusers', sa.Column('db_created_at', sa.DateTime(),
        nullable=True), sa.Column('db_updated_at', sa.DateTime(), nullable=
        True), sa.Column('db_created_by', sa.String(length=64), nullable=
        True), sa.Column('db_updated_by', sa.String(length=64), nullable=
        True), sa.Column('guid', sa.GUID(), nullable=True), sa.Column('id',
        sa.Integer(), nullable=False), sa.Column('username', sa.String(
        length=64), nullable=True), sa.Column('email', sa.String(length=128
        ), nullable=True), sa.Column('locale', sa.String(length=32),
        nullable=True), sa.Column('about_me', sa.String(length=256),
        nullable=True), sa.Column('event_id', sa.Integer(), nullable=True),
        sa.Column('profile_picture_id', sa.Integer(), nullable=True), sa.
        ForeignKeyConstraint(['event_id'], ['events.id']), sa.
        ForeignKeyConstraint(['profile_picture_id'], ['images.id']), sa.
        PrimaryKeyConstraint('id'))
    op.create_index(op.f('ix_eventusers_email'), 'eventusers', ['email'],
        unique=False)
    op.create_index(op.f('ix_eventusers_event_id'), 'eventusers', [
        'event_id'], unique=False)
    op.create_index(op.f('ix_eventusers_guid'), 'eventusers', ['guid'],
        unique=True)
    op.create_index(op.f('ix_eventusers_username'), 'eventusers', [
        'username'], unique=False)
    op.drop_column('users', 'timezone')
    op.execute(
        """INSERT eventusers (username, email, locale, about_me, profile_picture_id, event_id)
                  SELECT u.username, u.email, u.locale, u.about_me, u.profile_picture_id, eu.event_id
                  FROM users u, event_users eu
                  WHERE u.id=eu.user_id"""
        )
    op.drop_constraint('events_ibfk_1', 'events', type_='foreignkey')
    op.execute(
        """UPDATE 
                    events e 
                  SET e.accountant_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.id and u.username=eu.username and u.id=e.accountant_id )"""
        )
    op.create_foreign_key(None, 'events', 'eventusers', ['accountant_id'],
        ['id'])
    op.drop_constraint('expenses_ibfk_4', 'expenses', type_='foreignkey')
    op.execute(
        """UPDATE 
                    expenses e 
                  SET e.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.event_id and u.username=eu.username and u.id=e.user_id )"""
        )
    op.create_foreign_key(None, 'expenses', 'eventusers', ['user_id'], ['id'])
    op.drop_constraint('settlements_ibfk_5', 'settlements', type_='foreignkey')
    op.drop_constraint('settlements_ibfk_4', 'settlements', type_='foreignkey')
    op.execute(
        """UPDATE 
                    settlements s 
                  SET s.sender_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.sender_id )"""
        )
    op.execute(
        """UPDATE 
                    settlements s 
                  SET s.recipient_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.recipient_id )"""
        )
    op.create_foreign_key(None, 'settlements', 'eventusers', ['sender_id'],
        ['id'])
    op.create_foreign_key(None, 'settlements', 'eventusers', [
        'recipient_id'], ['id'])
    op.drop_constraint('posts_ibfk_2', 'posts', type_='foreignkey')
    op.execute(
        """UPDATE 
                    posts p      
                  SET p.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=p.event_id and u.username=eu.username and u.id=p.user_id )"""
        )
    op.create_foreign_key(None, 'posts', 'eventusers', ['user_id'], ['id'])
    op.rename_table('expense_affected_users', 'expense_affected_users_old')
    op.create_table('expense_affected_users', sa.Column('expense_id', sa.
        Integer(), nullable=False), sa.Column('user_id', sa.Integer(),
        nullable=False), sa.ForeignKeyConstraint(['expense_id'], [
        'expenses.id']), sa.ForeignKeyConstraint(['user_id'], [
        'eventusers.id']), sa.PrimaryKeyConstraint('expense_id', 'user_id'))
    op.execute(
        """INSERT expense_affected_users (expense_id, user_id)
                  SELECT eau_o.expense_id, eu.id 
                  FROM users u, eventusers eu, expenses e, expense_affected_users_old eau_o WHERE eu.event_id=e.event_id and e.id=eau_o.expense_id and u.username=eu.username and u.id=eau_o.user_id"""
        )
    op.drop_table('expense_affected_users_old')


def downgrade():
    op.add_column('users', sa.Column('timezone', mysql.VARCHAR(collation=
        'utf8mb4_unicode_ci', length=32), nullable=True))
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.create_foreign_key('settlements_ibfk_4', 'settlements', 'users', [
        'recipient_id'], ['id'])
    op.create_foreign_key('settlements_ibfk_5', 'settlements', 'users', [
        'sender_id'], ['id'])
    op.drop_constraint(None, 'posts', type_='foreignkey')
    op.create_foreign_key('posts_ibfk_2', 'posts', 'users', ['user_id'], ['id']
        )
    op.drop_constraint(None, 'expenses', type_='foreignkey')
    op.create_foreign_key('expenses_ibfk_4', 'expenses', 'users', [
        'user_id'], ['id'])
    op.drop_constraint(None, 'expense_affected_users', type_='foreignkey')
    op.create_foreign_key('expense_affected_users_ibfk_2',
        'expense_affected_users', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'events', type_='foreignkey')
    op.create_foreign_key('events_ibfk_1', 'events', 'users', [
        'accountant_id'], ['id'])
    op.drop_index(op.f('ix_eventusers_username'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_guid'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_event_id'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_email'), table_name='eventusers')
    op.drop_table('eventusers')

"""empty message

Revision ID: cee839a12c4a
Revises: 7903a539dcf2
Create Date: 2020-03-29 21:05:24.234674

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = 'cee839a12c4a'
down_revision = '7903a539dcf2'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('eventusers',
    sa.Column('db_created_at', sa.DateTime(), nullable=True),
    sa.Column('db_updated_at', sa.DateTime(), nullable=True),
    sa.Column('db_created_by', sa.String(length=64), nullable=True),
    sa.Column('db_updated_by', sa.String(length=64), nullable=True),
    sa.Column('guid', sa.GUID(), nullable=True),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(length=64), nullable=True),
    sa.Column('email', sa.String(length=128), nullable=True),
    sa.Column('locale', sa.String(length=32), nullable=True),
    sa.Column('about_me', sa.String(length=256), nullable=True),
    sa.Column('event_id', sa.Integer(), nullable=True),
    sa.Column('profile_picture_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['event_id'], ['events.id'], ),
    sa.ForeignKeyConstraint(['profile_picture_id'], ['images.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_eventusers_email'), 'eventusers', ['email'], unique=False)
    op.create_index(op.f('ix_eventusers_event_id'), 'eventusers', ['event_id'], unique=False)
    op.create_index(op.f('ix_eventusers_guid'), 'eventusers', ['guid'], unique=True)
    op.create_index(op.f('ix_eventusers_username'), 'eventusers', ['username'], unique=False)
    op.drop_column('users', 'timezone')
    # ### end Alembic commands ###
    
    # ### data migration ###
    op.execute('''INSERT eventusers (username, email, locale, about_me, profile_picture_id, event_id)
                  SELECT u.username, u.email, u.locale, u.about_me, u.profile_picture_id, eu.event_id
                  FROM users u, event_users eu
                  WHERE u.id=eu.user_id''')
                  
    op.drop_constraint('events_ibfk_1', 'events', type_='foreignkey')
    op.execute('''UPDATE 
                    events e 
                  SET e.accountant_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.id and u.username=eu.username and u.id=e.accountant_id )''')
    op.create_foreign_key(None, 'events', 'eventusers', ['accountant_id'], ['id'])
    
    op.drop_constraint('expenses_ibfk_4', 'expenses', type_='foreignkey')
    op.execute('''UPDATE 
                    expenses e 
                  SET e.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=e.event_id and u.username=eu.username and u.id=e.user_id )''')
    op.create_foreign_key(None, 'expenses', 'eventusers', ['user_id'], ['id'])
    
    op.drop_constraint('settlements_ibfk_5', 'settlements', type_='foreignkey')
    op.drop_constraint('settlements_ibfk_4', 'settlements', type_='foreignkey')
    op.execute('''UPDATE 
                    settlements s 
                  SET s.sender_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.sender_id )''')
    op.execute('''UPDATE 
                    settlements s 
                  SET s.recipient_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=s.event_id and u.username=eu.username and u.id=s.recipient_id )''')
    op.create_foreign_key(None, 'settlements', 'eventusers', ['sender_id'], ['id'])
    op.create_foreign_key(None, 'settlements', 'eventusers', ['recipient_id'], ['id'])
    
    op.drop_constraint('posts_ibfk_2', 'posts', type_='foreignkey')
    op.execute('''UPDATE 
                    posts p      
                  SET p.user_id =(SELECT eu.id FROM users u, eventusers eu WHERE eu.event_id=p.event_id and u.username=eu.username and u.id=p.user_id )''')
    op.create_foreign_key(None, 'posts', 'eventusers', ['user_id'], ['id'])
    
    op.rename_table('expense_affected_users', 'expense_affected_users_old')
    op.create_table('expense_affected_users',
    sa.Column('expense_id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['expense_id'], ['expenses.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['eventusers.id'], ),
    sa.PrimaryKeyConstraint('expense_id', 'user_id')
    )
    op.execute('''INSERT expense_affected_users (expense_id, user_id)
                  SELECT eau_o.expense_id, eu.id 
                  FROM users u, eventusers eu, expenses e, expense_affected_users_old eau_o WHERE eu.event_id=e.event_id and e.id=eau_o.expense_id and u.username=eu.username and u.id=eau_o.user_id''')
    op.drop_table('expense_affected_users_old')
    ### end of data migration ###
    
    
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('timezone', mysql.VARCHAR(collation='utf8mb4_unicode_ci', length=32), nullable=True))
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.drop_constraint(None, 'settlements', type_='foreignkey')
    op.create_foreign_key('settlements_ibfk_4', 'settlements', 'users', ['recipient_id'], ['id'])
    op.create_foreign_key('settlements_ibfk_5', 'settlements', 'users', ['sender_id'], ['id'])
    op.drop_constraint(None, 'posts', type_='foreignkey')
    op.create_foreign_key('posts_ibfk_2', 'posts', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'expenses', type_='foreignkey')
    op.create_foreign_key('expenses_ibfk_4', 'expenses', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'expense_affected_users', type_='foreignkey')
    op.create_foreign_key('expense_affected_users_ibfk_2', 'expense_affected_users', 'users', ['user_id'], ['id'])
    op.drop_constraint(None, 'events', type_='foreignkey')
    op.create_foreign_key('events_ibfk_1', 'events', 'users', ['accountant_id'], ['id'])
    op.drop_index(op.f('ix_eventusers_username'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_guid'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_event_id'), table_name='eventusers')
    op.drop_index(op.f('ix_eventusers_email'), table_name='eventusers')
    op.drop_table('eventusers')
    # ### end Alembic commands ###
