"""user_label

Revision ID: 794c34df8d18
Revises: 57d7f103dc0f
Create Date: 2019-09-02 15:37:09.869873

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '794c34df8d18'
down_revision = '57d7f103dc0f'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table('User',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('email', sa.String(length=100), nullable=False),
        sa.Column('auth0_id', sa.String(length=50), nullable=False),
        sa.Column('updated', sa.TIMESTAMP(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_User')),
        sa.UniqueConstraint('auth0_id', name=op.f('uq_User_auth0_id')),
        sa.UniqueConstraint('email', name=op.f('uq_User_email'))
    )

    op.create_table('UserLabel',
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('label_id', sa.Integer(), nullable=False),
        sa.ForeignKeyConstraint(['label_id'], ['Label.id'], name=op.f('fk_UserLabel_label_id_Label')),
        sa.ForeignKeyConstraint(['user_id'], ['User.id'], name=op.f('fk_UserLabel_user_id_User'), ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('user_id', 'label_id', name=op.f('pk_UserLabel'))
    )

    conn = op.get_bind()
    conn.execute('''
      INSERT INTO "User" (email, auth0_id, updated)
      SELECT email, id, edit_date 
        FROM "HistoryUser"
       WHERE id <> 'system'
    ''')


def downgrade():
    op.drop_table('UserLabel')
    op.drop_table('User')