"""Deleted the articles table, renamed statuses->questions, renamed
catgories->tags, renamed study_groups->groups.

Revision ID: 10061b77aa2a
Revises: 54ad01e8de85
Create Date: 2012-06-17 05:04:06.915881

"""

# downgrade revision identifier, used by Alembic.
revision = '10061b77aa2a'
down_revision = '54ad01e8de85'

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


def upgrade():
    # Create tables
    op.create_table('tags',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('groups',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=100), nullable=True),
    sa.Column('description', sa.String(length=500), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('edited', sa.DateTime(), nullable=True),
    sa.Column('network_id', sa.Integer(), nullable=True),
    sa.Column('founder_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['founder_id'], ['users.id'], ),
    sa.ForeignKeyConstraint(['network_id'], ['networks.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('questions',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('founder_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['founder_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('question_comments',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('question_id', sa.Integer(), nullable=True),
    sa.Column('comment_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['comment_id'], ['comments.id'], ),
    sa.ForeignKeyConstraint(['question_id'], ['questions.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('question_comment_notifications',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('comment_id', sa.Integer(), nullable=True),
    sa.Column('question_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['comment_id'], ['comments.id'], ),
    sa.ForeignKeyConstraint(['id'], ['notification_items.id'], ),
    sa.ForeignKeyConstraint(['question_id'], ['questions.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('question_history',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('revision', sa.Integer(), nullable=True),
    sa.Column('author_id', sa.Integer(), nullable=True),
    sa.Column('question_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('body', sa.String(length=1000), nullable=True),
    sa.ForeignKeyConstraint(['author_id'], ['users.id'], ),
    sa.ForeignKeyConstraint(['question_id'], ['questions.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('post_tags',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('post_id', sa.Integer(), nullable=True),
    sa.Column('tag_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['post_id'], ['posts.id'], ),
    sa.ForeignKeyConstraint(['tag_id'], ['tags.id'], ),
    sa.PrimaryKeyConstraint('id')
    )

    # Add/Drop Columns
    op.add_column('broadcasts', sa.Column('group_id', sa.Integer(), nullable=True))
    op.add_column('broadcasts', sa.Column('question_id', sa.Integer(), nullable=True))
    op.drop_column('broadcasts', u'study_group_id')
    op.drop_column('broadcasts', u'status_id')
    op.add_column('comments', sa.Column('score', sa.Integer(), nullable=True))
    op.add_column('group_invite_notifications', sa.Column('group_id', sa.Integer(), nullable=True))
    op.drop_column('group_invite_notifications', u'study_group_id')
    op.add_column('moderators', sa.Column('group_id', sa.Integer(), nullable=True))
    op.drop_column('moderators', u'study_group_id')
    op.add_column('posts', sa.Column('group_id', sa.Integer(), nullable=True))
    op.drop_column('posts', u'study_group_id')
    op.add_column('subscriptions', sa.Column('group_id', sa.Integer(), nullable=True))
    op.drop_column('subscriptions', u'study_group_id')
    op.add_column('votes', sa.Column('comment_id', sa.Integer(), nullable=True))
    op.drop_column('votes', u'post_id')

    # Drop Tables
    op.drop_table(u'post_categories')
    op.drop_table(u'status_comment_notifications')
    op.drop_table(u'article_history')
    op.drop_table(u'categories')
    op.drop_table(u'study_groups')
    op.drop_table(u'article_comments')
    op.drop_table(u'articles')
    op.drop_table(u'status_comments')
    op.drop_table(u'status_history')
    op.drop_table(u'statuses')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('votes', sa.Column(u'post_id', sa.INTEGER(), nullable=True))
    op.drop_column('votes', 'comment_id')
    op.add_column('subscriptions', sa.Column(u'study_group_id', sa.INTEGER(), nullable=True))
    op.drop_column('subscriptions', 'group_id')
    op.add_column('posts', sa.Column(u'study_group_id', sa.INTEGER(), nullable=True))
    op.drop_column('posts', 'group_id')
    op.add_column('moderators', sa.Column(u'study_group_id', sa.INTEGER(), nullable=True))
    op.drop_column('moderators', 'group_id')
    op.add_column('group_invite_notifications', sa.Column(u'study_group_id', sa.INTEGER(), nullable=True))
    op.drop_column('group_invite_notifications', 'group_id')
    op.drop_column('comments', 'score')
    op.add_column('broadcasts', sa.Column(u'status_id', sa.INTEGER(), nullable=True))
    op.add_column('broadcasts', sa.Column(u'study_group_id', sa.INTEGER(), nullable=True))
    op.drop_column('broadcasts', 'question_id')
    op.drop_column('broadcasts', 'group_id')
    op.create_table(u'statuses',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'created', postgresql.TIMESTAMP(), nullable=True),
    sa.Column(u'founder_id', sa.INTEGER(), nullable=True),
    sa.PrimaryKeyConstraint(u'id', name=u'statuses_pkey')
    )
    op.create_table(u'status_history',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'revision', sa.INTEGER(), nullable=True),
    sa.Column(u'author_id', sa.INTEGER(), nullable=True),
    sa.Column(u'status_id', sa.INTEGER(), nullable=True),
    sa.Column(u'created', postgresql.TIMESTAMP(), nullable=True),
    sa.Column(u'body', sa.VARCHAR(length=1000), nullable=True),
    sa.ForeignKeyConstraint(['author_id'], [u'users.id'], name=u'status_history_author_id_fkey'),
    sa.ForeignKeyConstraint(['status_id'], [u'statuses.id'], name=u'status_history_status_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'status_history_pkey')
    )
    op.create_table(u'status_comments',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'status_id', sa.INTEGER(), nullable=True),
    sa.Column(u'comment_id', sa.INTEGER(), nullable=True),
    sa.ForeignKeyConstraint(['comment_id'], [u'comments.id'], name=u'status_comments_comment_id_fkey'),
    sa.ForeignKeyConstraint(['status_id'], [u'statuses.id'], name=u'status_comments_status_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'status_comments_pkey')
    )
    op.create_table(u'articles',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'founder_id', sa.INTEGER(), nullable=True),
    sa.Column(u'created', postgresql.TIMESTAMP(), nullable=True),
    sa.ForeignKeyConstraint(['founder_id'], [u'users.id'], name=u'articles_founder_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'articles_pkey')
    )
    op.create_table(u'article_comments',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'article_id', sa.INTEGER(), nullable=True),
    sa.Column(u'comment_id', sa.INTEGER(), nullable=True),
    sa.ForeignKeyConstraint(['article_id'], [u'articles.id'], name=u'article_comments_article_id_fkey'),
    sa.ForeignKeyConstraint(['comment_id'], [u'comments.id'], name=u'article_comments_comment_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'article_comments_pkey')
    )
    op.create_table(u'study_groups',
    sa.Column(u'id', sa.INTEGER(),  nullable=False),
    sa.Column(u'name', sa.VARCHAR(length=100), nullable=True),
    sa.Column(u'created', postgresql.TIMESTAMP(), nullable=True),
    sa.Column(u'edited', postgresql.TIMESTAMP(), nullable=True),
    sa.Column(u'network_id', sa.INTEGER(), nullable=True),
    sa.Column(u'founder_id', sa.INTEGER(), nullable=True),
    sa.Column(u'description', sa.VARCHAR(length=500), nullable=True),
    sa.ForeignKeyConstraint(['founder_id'], [u'users.id'], name=u'study_groups_founder_id_fkey'),
    sa.ForeignKeyConstraint(['network_id'], [u'networks.id'], name=u'study_groups_network_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'study_groups_pkey')
    )
    op.create_table(u'categories',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'name', sa.VARCHAR(length=100), nullable=True),
    sa.PrimaryKeyConstraint(u'id', name=u'categories_pkey')
    )
    op.create_table(u'article_history',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'revision', sa.INTEGER(), nullable=True),
    sa.Column(u'created', postgresql.TIMESTAMP(), nullable=True),
    sa.Column(u'author_id', sa.INTEGER(), nullable=True),
    sa.Column(u'article_id', sa.INTEGER(), nullable=True),
    sa.Column(u'body', sa.VARCHAR(length=1000), nullable=True),
    sa.Column(u'title', sa.VARCHAR(length=100), nullable=True),
    sa.ForeignKeyConstraint(['article_id'], [u'articles.id'], name=u'article_history_article_id_fkey'),
    sa.ForeignKeyConstraint(['author_id'], [u'users.id'], name=u'article_history_author_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'article_history_pkey')
    )
    op.create_table(u'status_comment_notifications',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'user_id', sa.INTEGER(), nullable=True),
    sa.Column(u'comment_id', sa.INTEGER(), nullable=True),
    sa.Column(u'status_id', sa.INTEGER(), nullable=True),
    sa.ForeignKeyConstraint(['comment_id'], [u'comments.id'], name=u'status_comment_notifications_comment_id_fkey'),
    sa.ForeignKeyConstraint(['id'], [u'notification_items.id'], name=u'status_comment_notifications_id_fkey'),
    sa.ForeignKeyConstraint(['status_id'], [u'statuses.id'], name=u'status_comment_notifications_status_id_fkey'),
    sa.ForeignKeyConstraint(['user_id'], [u'users.id'], name=u'status_comment_notifications_user_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'status_comment_notifications_pkey')
    )
    op.create_table(u'post_categories',
    sa.Column(u'id', sa.INTEGER(), nullable=False),
    sa.Column(u'post_id', sa.INTEGER(), nullable=True),
    sa.Column(u'category_id', sa.INTEGER(), nullable=True),
    sa.ForeignKeyConstraint(['category_id'], [u'categories.id'], name=u'post_categories_category_id_fkey'),
    sa.ForeignKeyConstraint(['post_id'], [u'posts.id'], name=u'post_categories_post_id_fkey'),
    sa.PrimaryKeyConstraint(u'id', name=u'post_categories_pkey')
    )
    op.drop_table('post_tags')
    op.drop_table('question_history')
    op.drop_table('question_comment_notifications')
    op.drop_table('question_comments')
    op.drop_table('questions')
    op.drop_table('groups')
    op.drop_table('tags')
    ### end Alembic commands ###
