"""empty message

Revision ID: c226d7f7ad5a
Revises: 
Create Date: 2020-11-25 21:23:24.378872

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('classrooms',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(length=64), nullable=False),
    sa.Column('owner', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('config',
    sa.Column('cid', sa.Integer(), nullable=False),
    sa.Column('key', sa.Unicode(length=32), nullable=True),
    sa.Column('value', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('cid')
    )
    op.create_index(op.f('ix_config_key'), 'config', ['key'], unique=False)
    op.create_table('eggs',
    sa.Column('eid', sa.Integer(), nullable=False),
    sa.Column('flag', sa.Unicode(length=64), nullable=False),
    sa.PrimaryKeyConstraint('eid')
    )
    op.create_index(op.f('ix_eggs_flag'), 'eggs', ['flag'], unique=True)
    op.create_table('judge_api_keys',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('key', sa.String(length=64), nullable=True),
    sa.Column('ip', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_judge_api_keys_key'), 'judge_api_keys', ['key'], unique=False)
    op.create_table('problems',
    sa.Column('pid', sa.Integer(), nullable=False),
    sa.Column('author', sa.Unicode(length=32), nullable=True),
    sa.Column('name', sa.String(length=32), nullable=True),
    sa.Column('title', sa.Unicode(length=64), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('hint', sa.Text(), nullable=True),
    sa.Column('category', sa.Unicode(length=64), nullable=True),
    sa.Column('value', sa.Integer(), nullable=True),
    sa.Column('grader', sa.UnicodeText(), nullable=True),
    sa.Column('autogen', sa.Boolean(), nullable=True),
    sa.Column('programming', sa.Boolean(), nullable=True),
    sa.Column('threshold', sa.Integer(), nullable=True),
    sa.Column('weightmap', sa.PickleType(), nullable=True),
    sa.Column('test_cases', sa.Integer(), nullable=True),
    sa.Column('time_limit', sa.Integer(), nullable=True),
    sa.Column('memory_limit', sa.Integer(), nullable=True),
    sa.Column('generator', sa.Text(), nullable=True),
    sa.Column('source_verifier', sa.Text(), nullable=True),
    sa.Column('path', sa.String(length=128), nullable=True),
    sa.PrimaryKeyConstraint('pid'),
    sa.UniqueConstraint('name')
    )
    op.create_index(op.f('ix_problems_pid'), 'problems', ['pid'], unique=False)
    op.create_table('teams',
    sa.Column('tid', sa.Integer(), nullable=False),
    sa.Column('teamname', sa.Unicode(length=32), nullable=True),
    sa.Column('school', sa.Unicode(length=64), nullable=True),
    sa.Column('owner', sa.Integer(), nullable=True),
    sa.Column('admin', sa.Boolean(), nullable=True),
    sa.Column('shell_user', sa.String(length=16), nullable=True),
    sa.Column('shell_pass', sa.String(length=32), nullable=True),
    sa.Column('banned', sa.Boolean(), nullable=True),
    sa.Column('avatar', sa.String(length=128), nullable=True),
    sa.PrimaryKeyConstraint('tid'),
    sa.UniqueConstraint('shell_user'),
    sa.UniqueConstraint('teamname')
    )
    op.create_index(op.f('ix_teams_tid'), 'teams', ['tid'], unique=False)
    op.create_table('autogen_files',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('pid', sa.Integer(), nullable=True),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('filename', sa.Unicode(length=64), nullable=True),
    sa.Column('url', sa.String(length=128), nullable=True),
    sa.ForeignKeyConstraint(['pid'], ['problems.pid'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_autogen_files_filename'), 'autogen_files', ['filename'], unique=False)
    op.create_index(op.f('ix_autogen_files_id'), 'autogen_files', ['id'], unique=False)
    op.create_index(op.f('ix_autogen_files_pid'), 'autogen_files', ['pid'], unique=False)
    op.create_index(op.f('ix_autogen_files_tid'), 'autogen_files', ['tid'], unique=False)
    op.create_table('classroom_invitation',
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('classroom_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['classroom_id'], ['classrooms.id'], ),
    sa.ForeignKeyConstraint(['team_id'], ['teams.tid'], ),
    sa.PrimaryKeyConstraint('team_id', 'classroom_id')
    )
    op.create_table('files',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('pid', sa.Integer(), nullable=True),
    sa.Column('filename', sa.Unicode(length=64), nullable=True),
    sa.Column('url', sa.String(length=128), nullable=True),
    sa.ForeignKeyConstraint(['pid'], ['problems.pid'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_files_id'), 'files', ['id'], unique=False)
    op.create_index(op.f('ix_files_pid'), 'files', ['pid'], unique=False)
    op.create_table('team_classroom',
    sa.Column('team_id', sa.Integer(), nullable=False),
    sa.Column('classroom_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['classroom_id'], ['classrooms.id'], ),
    sa.ForeignKeyConstraint(['team_id'], ['teams.tid'], ),
    sa.PrimaryKeyConstraint('team_id', 'classroom_id')
    )
    op.create_table('users',
    sa.Column('uid', sa.Integer(), nullable=False),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('name', sa.Unicode(length=32), nullable=True),
    sa.Column('easyctf', sa.Boolean(), nullable=True),
    sa.Column('username', sa.String(length=16), nullable=True),
    sa.Column('email', sa.String(length=128), nullable=True),
    sa.Column('password', sa.String(length=128), nullable=True),
    sa.Column('admin', sa.Boolean(), nullable=True),
    sa.Column('level', sa.Integer(), nullable=True),
    sa.Column('register_time', sa.DateTime(), nullable=True),
    sa.Column('reset_token', sa.String(length=32), nullable=True),
    sa.Column('otp_secret', sa.String(length=16), nullable=True),
    sa.Column('otp_confirmed', sa.Boolean(), nullable=True),
    sa.Column('email_token', sa.String(length=32), nullable=True),
    sa.Column('email_verified', sa.Boolean(), nullable=True),
    sa.Column('avatar', sa.String(length=128), nullable=True),
    sa.ForeignKeyConstraint(['tid'], ['teams.tid'], ),
    sa.PrimaryKeyConstraint('uid'),
    sa.UniqueConstraint('email')
    )
    op.create_index(op.f('ix_users_easyctf'), 'users', ['easyctf'], unique=False)
    op.create_index(op.f('ix_users_uid'), 'users', ['uid'], unique=False)
    op.create_index(op.f('ix_users_username'), 'users', ['username'], unique=True)
    op.create_table('egg_solves',
    sa.Column('sid', sa.Integer(), nullable=False),
    sa.Column('eid', sa.Integer(), nullable=True),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('date', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['eid'], ['eggs.eid'], ),
    sa.ForeignKeyConstraint(['tid'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('sid')
    )
    op.create_index(op.f('ix_egg_solves_eid'), 'egg_solves', ['eid'], unique=False)
    op.create_index(op.f('ix_egg_solves_tid'), 'egg_solves', ['tid'], unique=False)
    op.create_index(op.f('ix_egg_solves_uid'), 'egg_solves', ['uid'], unique=False)
    op.create_table('game_states',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('last_updated', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=True),
    sa.Column('state', sa.UnicodeText(), nullable=False),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('last_updated'),
    sa.UniqueConstraint('uid')
    )
    op.create_table('jobs',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('pid', sa.Integer(), nullable=True),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('submitted', sa.DateTime(), nullable=True),
    sa.Column('claimed', sa.DateTime(), nullable=True),
    sa.Column('completed', sa.DateTime(), nullable=True),
    sa.Column('execution_time', sa.Float(), nullable=True),
    sa.Column('execution_memory', sa.Float(), nullable=True),
    sa.Column('language', sa.String(length=16), nullable=False),
    sa.Column('contents', sa.Text(), nullable=False),
    sa.Column('feedback', sa.Text(), nullable=True),
    sa.Column('status', sa.Integer(), nullable=False),
    sa.Column('verdict', sa.String(length=8), nullable=True),
    sa.Column('last_ran_case', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['pid'], ['problems.pid'], ),
    sa.ForeignKeyConstraint(['tid'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_jobs_pid'), 'jobs', ['pid'], unique=False)
    op.create_index(op.f('ix_jobs_status'), 'jobs', ['status'], unique=False)
    op.create_index(op.f('ix_jobs_tid'), 'jobs', ['tid'], unique=False)
    op.create_index(op.f('ix_jobs_uid'), 'jobs', ['uid'], unique=False)
    op.create_table('password_reset_tokens',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('token', sa.String(length=32), nullable=True),
    sa.Column('email', sa.Unicode(length=128), nullable=True),
    sa.Column('expire', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_password_reset_tokens_uid'), 'password_reset_tokens', ['uid'], unique=False)
    op.create_table('player_team_invitation',
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('team_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['team_id'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.uid'], )
    )
    op.create_table('solves',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('pid', sa.Integer(), nullable=True),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('date', sa.DateTime(), nullable=True),
    sa.Column('flag', sa.Unicode(length=256), nullable=True),
    sa.ForeignKeyConstraint(['pid'], ['problems.pid'], ),
    sa.ForeignKeyConstraint(['tid'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('pid', 'tid')
    )
    op.create_index(op.f('ix_solves_id'), 'solves', ['id'], unique=False)
    op.create_index(op.f('ix_solves_pid'), 'solves', ['pid'], unique=False)
    op.create_index(op.f('ix_solves_tid'), 'solves', ['tid'], unique=False)
    op.create_index(op.f('ix_solves_uid'), 'solves', ['uid'], unique=False)
    op.create_table('team_player_invitation',
    sa.Column('team_id', sa.Integer(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['team_id'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.uid'], )
    )
    op.create_table('wrong_egg',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('eid', sa.Integer(), nullable=True),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('date', sa.DateTime(), nullable=True),
    sa.Column('submission', sa.Unicode(length=64), nullable=True),
    sa.ForeignKeyConstraint(['eid'], ['eggs.eid'], ),
    sa.ForeignKeyConstraint(['tid'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_wrong_egg_eid'), 'wrong_egg', ['eid'], unique=False)
    op.create_index(op.f('ix_wrong_egg_tid'), 'wrong_egg', ['tid'], unique=False)
    op.create_index(op.f('ix_wrong_egg_uid'), 'wrong_egg', ['uid'], unique=False)
    op.create_table('wrong_flags',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('pid', sa.Integer(), nullable=True),
    sa.Column('tid', sa.Integer(), nullable=True),
    sa.Column('uid', sa.Integer(), nullable=True),
    sa.Column('date', sa.DateTime(), nullable=True),
    sa.Column('flag', sa.Unicode(length=256), nullable=True),
    sa.ForeignKeyConstraint(['pid'], ['problems.pid'], ),
    sa.ForeignKeyConstraint(['tid'], ['teams.tid'], ),
    sa.ForeignKeyConstraint(['uid'], ['users.uid'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_wrong_flags_flag'), 'wrong_flags', ['flag'], unique=False)
    op.create_index(op.f('ix_wrong_flags_id'), 'wrong_flags', ['id'], unique=False)
    op.create_index(op.f('ix_wrong_flags_pid'), 'wrong_flags', ['pid'], unique=False)
    op.create_index(op.f('ix_wrong_flags_tid'), 'wrong_flags', ['tid'], unique=False)
    op.create_index(op.f('ix_wrong_flags_uid'), 'wrong_flags', ['uid'], unique=False)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_wrong_flags_uid'), table_name='wrong_flags')
    op.drop_index(op.f('ix_wrong_flags_tid'), table_name='wrong_flags')
    op.drop_index(op.f('ix_wrong_flags_pid'), table_name='wrong_flags')
    op.drop_index(op.f('ix_wrong_flags_id'), table_name='wrong_flags')
    op.drop_index(op.f('ix_wrong_flags_flag'), table_name='wrong_flags')
    op.drop_table('wrong_flags')
    op.drop_index(op.f('ix_wrong_egg_uid'), table_name='wrong_egg')
    op.drop_index(op.f('ix_wrong_egg_tid'), table_name='wrong_egg')
    op.drop_index(op.f('ix_wrong_egg_eid'), table_name='wrong_egg')
    op.drop_table('wrong_egg')
    op.drop_table('team_player_invitation')
    op.drop_index(op.f('ix_solves_uid'), table_name='solves')
    op.drop_index(op.f('ix_solves_tid'), table_name='solves')
    op.drop_index(op.f('ix_solves_pid'), table_name='solves')
    op.drop_index(op.f('ix_solves_id'), table_name='solves')
    op.drop_table('solves')
    op.drop_table('player_team_invitation')
    op.drop_index(op.f('ix_password_reset_tokens_uid'), table_name='password_reset_tokens')
    op.drop_table('password_reset_tokens')
    op.drop_index(op.f('ix_jobs_uid'), table_name='jobs')
    op.drop_index(op.f('ix_jobs_tid'), table_name='jobs')
    op.drop_index(op.f('ix_jobs_status'), table_name='jobs')
    op.drop_index(op.f('ix_jobs_pid'), table_name='jobs')
    op.drop_table('jobs')
    op.drop_table('game_states')
    op.drop_index(op.f('ix_egg_solves_uid'), table_name='egg_solves')
    op.drop_index(op.f('ix_egg_solves_tid'), table_name='egg_solves')
    op.drop_index(op.f('ix_egg_solves_eid'), table_name='egg_solves')
    op.drop_table('egg_solves')
    op.drop_index(op.f('ix_users_username'), table_name='users')
    op.drop_index(op.f('ix_users_uid'), table_name='users')
    op.drop_index(op.f('ix_users_easyctf'), table_name='users')
    op.drop_table('users')
    op.drop_table('team_classroom')
    op.drop_index(op.f('ix_files_pid'), table_name='files')
    op.drop_index(op.f('ix_files_id'), table_name='files')
    op.drop_table('files')
    op.drop_table('classroom_invitation')
    op.drop_index(op.f('ix_autogen_files_tid'), table_name='autogen_files')
    op.drop_index(op.f('ix_autogen_files_pid'), table_name='autogen_files')
    op.drop_index(op.f('ix_autogen_files_id'), table_name='autogen_files')
    op.drop_index(op.f('ix_autogen_files_filename'), table_name='autogen_files')
    op.drop_table('autogen_files')
    op.drop_index(op.f('ix_teams_tid'), table_name='teams')
    op.drop_table('teams')
    op.drop_index(op.f('ix_problems_pid'), table_name='problems')
    op.drop_table('problems')
    op.drop_index(op.f('ix_judge_api_keys_key'), table_name='judge_api_keys')
    op.drop_table('judge_api_keys')
    op.drop_index(op.f('ix_eggs_flag'), table_name='eggs')
    op.drop_table('eggs')
    op.drop_index(op.f('ix_config_key'), table_name='config')
    op.drop_table('config')
    op.drop_table('classrooms')
    # ### end Alembic commands ###