"""access_id in user_projects

Revision ID: 987edda096f5
Revises: 38581a8fbae2
Create Date: 2016-07-05 18:21:14.771273

"""

# revision identifiers, used by Alembic.
revision = '987edda096f5'
down_revision = '38581a8fbae2'

from alembic import op
import sqlalchemy as sa


def upgrade():
    ''' Add a foreign key in user_projects and projects_groups
    table for access_levels
    '''

    op.add_column(
        'user_projects',
        sa.Column(
            'access',
            sa.String(255),
            sa.ForeignKey(
                'access_levels.access',
                onupdate='CASCADE',
                ondelete='CASCADE',
            ),
            nullable=True,
        ),
    )
    op.execute('UPDATE "user_projects" SET access=\'admin\'')
    op.alter_column(
        'user_projects',
        'access',
        nullable=False,
        existing_nullable=True,
    )

    # for groups
    op.add_column(
        'projects_groups',
        sa.Column(
            'access',
            sa.String(255),
            sa.ForeignKey(
                'access_levels.access',
                onupdate='CASCADE',
                ondelete='CASCADE',
            ),
            nullable=True,
        ),
    )
    op.execute('UPDATE "projects_groups" SET access=\'admin\'')
    op.alter_column(
        'projects_groups',
        'access',
        nullable=False,
        existing_nullable=True,
    )

    # alter the constraints
    op.drop_constraint('user_projects_project_id_key', 'user_projects')
    op.create_unique_constraint(
            None,
            'user_projects',
            ["project_id", "user_id", "access"]
    )

    op.drop_constraint('projects_groups_pkey', 'projects_groups')
    op.create_primary_key(
            None,
            'projects_groups',
            ['project_id', 'group_id', 'access'],
    )



def downgrade():
    ''' Remove column access_id from user_projects and projects_groups '''

    # this removes the current constraints as well.
    op.drop_column('user_projects', 'access')
    op.drop_column('projects_groups', 'access')

    # recreate the previous constraints
    op.create_unique_constraint(
            None,
            'user_projects',
            ['project_id', 'user_id'],
    )
    op.create_primary_key(
            None,
            'projects_groups',
            ['project_id', 'group_id'],
    )