"""initial migration

Revision ID: 1c68862792f6
Revises: None
Create Date: 2016-03-24 16:18:53.875315

"""

# revision identifiers, used by Alembic.
revision = '1c68862792f6'
down_revision = None

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

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('work_url',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('url', sa.String(length=200), nullable=False),
    sa.Column('key', sa.String(length=50), nullable=True),
    sa.Column('type', sa.Integer(), nullable=True),
    sa.Column('description', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_work_url_description'), 'work_url', ['description'], unique=False)
    op.create_index(op.f('ix_work_url_key'), 'work_url', ['key'], unique=False)
    op.create_index(op.f('ix_work_url_url'), 'work_url', ['url'], unique=False)
    op.create_table('work_role',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=40), nullable=False),
    sa.Column('description', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_work_role_description'), 'work_role', ['description'], unique=False)
    op.create_index(op.f('ix_work_role_name'), 'work_role', ['name'], unique=False)
    op.create_table('user_work_rolemap',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('workrole_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], [u'user.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['workrole_id'], [u'work_role.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('work_pool_rolemap',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('role_id', sa.Integer(), nullable=False),
    sa.Column('pool_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['pool_id'], [u'pool.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['role_id'], [u'work_role.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('work_role_urlmap',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('work_role_id', sa.Integer(), nullable=False),
    sa.Column('url_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['url_id'], [u'work_url.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['work_role_id'], [u'work_role.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('work_task_rolemap',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('role_id', sa.Integer(), nullable=False),
    sa.Column('task_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['role_id'], [u'work_role.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['task_id'], [u'work_task_template.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('work_file_rolemap',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('role_id', sa.Integer(), nullable=False),
    sa.Column('workfile_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['role_id'], [u'work_role.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['workfile_id'], [u'work_file.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.drop_constraint(u'2', 'department', type_='foreignkey')
    op.drop_constraint(u'1', 'department', type_='foreignkey')
    op.create_foreign_key(None, 'department', 'user', ['leader_uid'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'department', 'user', ['bleader_uid'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'group', 'department', ['department_id'], ['id'], ondelete='CASCADE')
    op.alter_column(u'orders', 'content',
               existing_type=mysql.TEXT(),
               nullable=True)
    op.create_foreign_key(None, 'orders', 'user', ['approver_uid'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'orders', 'order_category', ['category_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'orders', 'user', ['applier_uid'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'orders_task', 'orders', ['tickets_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'orders_task', 'user', ['uid'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'sudogroupmap', 'sudo', ['sudo_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'sudogroupmap', 'group', ['group_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'sudousermap', 'user', ['user_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'sudousermap', 'sudo', ['sudo_id'], ['id'], ondelete='CASCADE')
    op.create_index(op.f('ix_user_cn_name'), 'user', ['cn_name'], unique=False)
    op.create_index(op.f('ix_user_name'), 'user', ['name'], unique=False)
    op.drop_index('index_cn_name', table_name='user')
    op.drop_index('index_name', table_name='user')
    op.create_foreign_key(None, 'usergroupmap', 'user', ['user_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'usergroupmap', 'group', ['group_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'userpoolmap', 'user', ['user_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'userpoolmap', 'pool', ['pool_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(None, 'work_file', 'department', ['department_id'], ['id'], onupdate='CASCADE', ondelete='CASCADE')
    op.create_foreign_key(None, 'work_file', 'work_type', ['workType_id'], ['id'], onupdate='CASCADE', ondelete='CASCADE')
    op.add_column(u'work_task', sa.Column('template_id', sa.Integer(), nullable=True))
    op.create_index(op.f('ix_work_task_info_ext'), 'work_task', ['info_ext'], unique=False)
    op.create_index(op.f('ix_work_task_log'), 'work_task', ['log'], unique=False)
    op.create_foreign_key(None, 'work_task', 'user', ['user_id'], ['id'], ondelete='CASCADE')
    op.create_index(op.f('ix_work_task_template_info_ext'), 'work_task_template', ['info_ext'], unique=False)
    op.create_foreign_key(None, 'work_task_template', 'user', ['user_id'], ['id'], ondelete='CASCADE')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'work_task_template', type_='foreignkey')
    op.drop_index(op.f('ix_work_task_template_info_ext'), table_name='work_task_template')
    op.drop_constraint(None, 'work_task', type_='foreignkey')
    op.drop_index(op.f('ix_work_task_log'), table_name='work_task')
    op.drop_index(op.f('ix_work_task_info_ext'), table_name='work_task')
    op.drop_column(u'work_task', 'template_id')
    op.drop_constraint(None, 'work_file', type_='foreignkey')
    op.drop_constraint(None, 'work_file', type_='foreignkey')
    op.drop_constraint(None, 'userpoolmap', type_='foreignkey')
    op.drop_constraint(None, 'userpoolmap', type_='foreignkey')
    op.drop_constraint(None, 'usergroupmap', type_='foreignkey')
    op.drop_constraint(None, 'usergroupmap', type_='foreignkey')
    op.create_index('index_name', 'user', ['name'], unique=False)
    op.create_index('index_cn_name', 'user', ['cn_name'], unique=False)
    op.drop_index(op.f('ix_user_name'), table_name='user')
    op.drop_index(op.f('ix_user_cn_name'), table_name='user')
    op.drop_constraint(None, 'sudousermap', type_='foreignkey')
    op.drop_constraint(None, 'sudousermap', type_='foreignkey')
    op.drop_constraint(None, 'sudogroupmap', type_='foreignkey')
    op.drop_constraint(None, 'sudogroupmap', type_='foreignkey')
    op.drop_constraint(None, 'orders_task', type_='foreignkey')
    op.drop_constraint(None, 'orders_task', type_='foreignkey')
    op.drop_constraint(None, 'orders', type_='foreignkey')
    op.drop_constraint(None, 'orders', type_='foreignkey')
    op.drop_constraint(None, 'orders', type_='foreignkey')
    op.alter_column(u'orders', 'content',
               existing_type=mysql.TEXT(),
               nullable=False)
    op.drop_constraint(None, 'group', type_='foreignkey')
    op.drop_constraint(None, 'department', type_='foreignkey')
    op.drop_constraint(None, 'department', type_='foreignkey')
    op.create_foreign_key(u'1', 'department', 'user', ['leader_uid'], ['id'], onupdate=u'CASCADE', ondelete=u'CASCADE')
    op.create_foreign_key(u'2', 'department', 'user', ['bleader_uid'], ['id'], onupdate=u'CASCADE', ondelete=u'CASCADE')
    op.drop_table('work_file_rolemap')
    op.drop_table('work_task_rolemap')
    op.drop_table('work_role_urlmap')
    op.drop_table('work_pool_rolemap')
    op.drop_table('user_work_rolemap')
    op.drop_index(op.f('ix_work_role_name'), table_name='work_role')
    op.drop_index(op.f('ix_work_role_description'), table_name='work_role')
    op.drop_table('work_role')
    op.drop_index(op.f('ix_work_url_url'), table_name='work_url')
    op.drop_index(op.f('ix_work_url_key'), table_name='work_url')
    op.drop_index(op.f('ix_work_url_description'), table_name='work_url')
    op.drop_table('work_url')
    ### end Alembic commands ###
