"""empty message

Revision ID: 09bbbdb4d2a5
Revises: 16571f73a1f3
Create Date: 2022-12-13 16:13:46.961861

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

# revision identifiers, used by Alembic.
revision = '09bbbdb4d2a5'
down_revision = '16571f73a1f3'
branch_labels = None
depends_on = None

_types = [{"id": "66ce9d79-305d-4f6a-9fad-889aa8c4d96f", "en": "University", "de": ""},
          {"id": "b8057652-8a8a-45cb-b5b3-3d95f1e9d5f5", "en": "Hobby", "de": ""},
          {"id": "c49d9f25-f244-4a1e-af35-67c235c62bfa", "en": "other", "de": ""}]


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('project_types',
                    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
                    sa.Column('en', sa.String(), nullable=False),
                    sa.Column('de', sa.String(), nullable=False),
                    sa.PrimaryKeyConstraint('id')
                    )
    op.add_column('project', sa.Column('project_type_id', postgresql.UUID(as_uuid=True), nullable=True))
    op.create_foreign_key(None, 'project', 'project_types', ['project_type_id'], ['id'])

    conn = op.get_bind()
    for i in _types:
        conn.execute(f"INSERT INTO project_types (id, en, de) VALUES ('{i['id']}', '{i['en']}', '{i['de']}');")

    projects = conn.execute(
        "SELECT id, project_type FROM project WHERE project.project_type IS NOT NULL;")
    for project in projects:
        for t in _types:
            if t['en'] == project[1]:
                conn.execute(
                    f"UPDATE project SET project_type_id = '{t['id']}' WHERE id = '{project[0]}'")
                break

    op.drop_column('project', 'project_type')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('project', sa.Column('project_type', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.drop_constraint('project_project_type_id_fkey', 'project', type_='foreignkey')
    op.drop_column('project', 'project_type_id')
    op.drop_table('project_types')
    # ### end Alembic commands ###
