"""empty message

Revision ID: 01d77f919af1
Revises: 
Create Date: 2019-11-28 19:39:11.518942

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('Departments',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('abbreviation', sa.String(length=5), nullable=False),
    sa.Column('chair', sa.String(length=255), nullable=False),
    sa.Column('office', sa.String(length=255), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('abbreviation'),
    sa.UniqueConstraint('name')
    )
    op.create_table('Terms',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('start_date', sa.Date(), nullable=False),
    sa.Column('end_date', sa.Date(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('end_date'),
    sa.UniqueConstraint('start_date')
    )
    op.create_table('Users',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('username', sa.String(length=8), nullable=False),
    sa.Column('password', sa.String(length=255), nullable=False),
    sa.Column('full_name', sa.String(length=255), nullable=False),
    sa.Column('display_name', sa.String(length=255), nullable=True),
    sa.Column('email', sa.String(length=254), nullable=False),
    sa.Column('join_date', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email'),
    sa.UniqueConstraint('username')
    )
    op.create_table('Courses',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('department_id', sa.Integer(), nullable=True),
    sa.Column('code', sa.String(length=5), nullable=True),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.ForeignKeyConstraint(['department_id'], ['Departments.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('department_id', 'code')
    )
    op.create_table('Majors',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('department_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['department_id'], ['Departments.id'], ondelete='SET NULL'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name')
    )
    op.create_table('Teachers',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('department_id', sa.Integer(), nullable=True),
    sa.Column('office', sa.String(length=255), nullable=True),
    sa.Column('office_hours', sa.String(length=255), nullable=True),
    sa.ForeignKeyConstraint(['department_id'], ['Departments.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['user_id'], ['Users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id')
    )
    op.create_table('Classes',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('teacher_id', sa.Integer(), nullable=True),
    sa.Column('term_id', sa.Integer(), nullable=True),
    sa.Column('course_id', sa.Integer(), nullable=True),
    sa.Column('section', sa.String(length=4), nullable=False),
    sa.Column('schedule', sa.String(length=255), nullable=False),
    sa.Column('location', sa.String(length=255), nullable=False),
    sa.ForeignKeyConstraint(['course_id'], ['Courses.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['teacher_id'], ['Teachers.user_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['term_id'], ['Terms.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('term_id', 'course_id', 'section')
    )
    op.create_table('Students',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('major_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['major_id'], ['Majors.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['user_id'], ['Users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id')
    )
    op.create_table('Attendance',
    sa.Column('student_id', sa.Integer(), nullable=False),
    sa.Column('class_id', sa.Integer(), nullable=False),
    sa.Column('date', sa.Date(), nullable=False),
    sa.ForeignKeyConstraint(['class_id'], ['Classes.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['student_id'], ['Students.user_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('student_id', 'class_id', 'date')
    )
    op.create_table('Enrollments',
    sa.Column('student_id', sa.Integer(), nullable=False),
    sa.Column('class_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['class_id'], ['Classes.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['student_id'], ['Students.user_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('student_id', 'class_id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('Enrollments')
    op.drop_table('Attendance')
    op.drop_table('Students')
    op.drop_table('Classes')
    op.drop_table('Teachers')
    op.drop_table('Majors')
    op.drop_table('Courses')
    op.drop_table('Users')
    op.drop_table('Terms')
    op.drop_table('Departments')
    # ### end Alembic commands ###
