"""associate startdate, finishdate to user_position

Revision ID: 6e7d50e2ade9
Revises: 576094efbb9c
Create Date: 2020-12-23 14:42:01.558949

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6e7d50e2ade9'
down_revision = '576094efbb9c'
branch_labels = None
depends_on = None


def upgrade(engine_name):
    globals()["upgrade_%s" % engine_name]()


def downgrade(engine_name):
    globals()["downgrade_%s" % engine_name]()





def upgrade_():
    # ### commands auto generated by Alembic - please adjust! ###
    user_position_dates = op.create_table('user_position_dates',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('interest_id', sa.Integer(), nullable=True),
    sa.Column('user_id', sa.Integer(), nullable=True),
    sa.Column('position_id', sa.Integer(), nullable=True),
    sa.Column('startdate', sa.Date(), nullable=True),
    sa.Column('finishdate', sa.Date(), nullable=True),
    sa.Column('version_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['interest_id'], ['localinterest.id'], ),
    sa.ForeignKeyConstraint(['position_id'], ['position.id'], ),
    sa.ForeignKeyConstraint(['user_id'], ['localuser.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###

    # copy data from user_position table to user_position_dates table (user_position table is now obsolete)
    from sqlalchemy.sql import table, column
    userposition_table = table('user_position',
                               column('user_id', sa.Integer),
                               column('position_id', sa.Integer)
                               )
    from datetime import date
    conn = op.get_bind()
    select = userposition_table.select()
    # default startdate to day of banquet, good for officers, but most positions will need modification
    # hard code interest_id to 1 to mean primary interest
    startdate = date(2019,1,19)
    userpositions = [
        {
            'interest_id': 1,
            'user_id': up.user_id,
            'position_id': up.position_id,
            'startdate': startdate
        }
        for up in conn.execute(select)
    ]
    op.bulk_insert(user_position_dates, userpositions)

def downgrade_():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('user_position_dates')
    # ### end Alembic commands ###


def upgrade_users():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade_users():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

