"""empty message

Revision ID: 16571f73a1f3
Revises: df697361aa25
Create Date: 2022-12-02 09:51:38.433424

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

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

interests = [{"id": "ae355041-e39e-40fa-85e7-5b719ab83b45", "en": "Acting", "de": ""}, {"id": "dd2d3fb1-a287-4337-a78e-37d580d0a36d", "en": "Activism", "de": ""}, {"id": "b6ddf04b-8913-4321-9519-fc5339fb4e68", "en": "Anime, Manga", "de": ""}, {"id": "8f6c993c-28e9-4fff-a7b3-a7d929c4414f", "en": "Art", "de": ""}, {"id": "bdc85f94-247c-4cde-b7e0-77d040acadcb", "en": "Baking and cooking", "de": ""}, {"id": "4209b980-2eb4-455f-996a-979ea14d99ed", "en": "Blogging", "de": ""}, {"id": "4ff586c3-43c3-447d-924d-fef62304c918", "en": "Cars, Motor sport", "de": ""}, {"id": "40d42c29-6116-4b3f-8743-7f64a6772705", "en": "Clubs, bars + parties", "de": ""}, {"id": "899812d6-ab02-42ea-88be-e852daad14b5", "en": "Comedy", "de": ""}, {"id": "9d8fc29c-eb3b-4ce4-8263-dd3efd162e2c", "en": "Cosplaying, LARPing", "de": ""}, {"id": "2b912cef-97dd-4c59-96d1-44f656614192", "en": "Craft, DIY", "de": ""}, {"id": "7b26a8ed-7d2b-4bae-a021-40575967b051", "en": "Electronics", "de": ""}, {"id": "209a7eb8-15c9-4b7f-ba09-12e73987561d", "en": "Fashion, Makeup, Beauty", "de": ""}, {"id": "891c59d2-280b-4199-8682-b957dce9dbac", "en": "Gaming", "de": ""}, {"id": "9271b2b0-1848-4423-8ded-f73a9725ca59", "en": "Mindset, Meditation, Minimalism", "de": ""}, {"id": "75e8e63f-c9e3-418b-91dd-374f37bc1e64", "en": "Movies, Documentaries", "de": ""}, {"id": "6e21e722-4fc9-4503-ba68-ed99fa0ea010", "en": "Music, Singing, Songwriting", "de": ""}, {"id": "65382f8d-3576-4bd3-8d62-6435fa88cc2a", "en": "Pets", "de": ""}, {"id": "7f063acf-80b0-413b-bfd6-1005630a42f0", "en": "Photography, Filmmaking", "de": ""}, {"id": "187c0a1a-411e-41e0-8d61-d2cedfda35cb", "en": "Plants, Gardening", "de": ""}, {"id": "525c19a5-afe3-4cef-b632-52cff020fcb3", "en": "Programming", "de": ""}, {"id": "4cb93b91-2ba4-432a-9d4f-45dfab071598", "en": "Reading, Writing, Poetry", "de": ""}, {"id": "27482987-f190-4633-a61c-4b456efaa65b", "en": "Social media, Streaming, Podcasts", "de": ""}, {"id": "feb51738-fa7b-4680-b895-d67ee1543fb0", "en": "Sustainability", "de": ""}, {"id": "a9e3c38f-829f-4349-a07d-3f30c1d34c4a", "en": "Tabletop games", "de": ""}, {"id": "fa1a67e3-55b6-4a0b-96fc-e893a299c940", "en": "Tattoos", "de": ""}, {"id": "0b90b969-2797-4aff-8d71-f043780d1d0d", "en": "Travel", "de": ""}, {"id": "372839f6-f8d1-4b43-969d-24750d53595a", "en": "Air sports", "de": ""}, {"id": "e2cf6d2f-cef4-4fc5-a01e-09d0f9d3c7cb", "en": "American Football", "de": ""}, {"id": "5ca66897-6b8d-4597-82de-43b8ed724582", "en": "Badminton", "de": ""}, {"id": "f0b40f7e-d9f0-459c-80b2-95d6a458840c", "en": "Baseball", "de": ""}, {"id": "99bbaa5a-e199-4b77-b83f-81e385abc53c", "en": "Basketball", "de": ""}, {"id": "204758cc-b786-4396-8b46-233669851bef", "en": "Climbing, Hiking", "de": ""}, {"id": "8f045781-2feb-4900-bb7f-9c38ca07563b", "en": "Cycling, Mountain biking, BMX", "de": ""}, {"id": "df8b0153-26c6-4bb2-8a86-3f334cdc97fa", "en": "Dance", "de": ""}, {"id": "47fd1773-21fe-4675-a790-20bd742a2dda", "en": "Fitness, Bodybuilding, Yoga", "de": ""}, {"id": "0afc518d-6f26-4d70-b628-d5d9b4010b49", "en": "Gymnastics", "de": ""}, {"id": "4a608654-4436-41a0-b973-cd582ca811c4", "en": "Handball", "de": ""}, {"id": "20acd1f4-c4b3-4764-a8c9-6a24f651b07d", "en": "Horseback riding", "de": ""}, {"id": "9724b464-e48d-43a8-8ffd-a3463af86f0c", "en": "Ice skating, Inline skating", "de": ""}, {"id": "31131799-704a-4dda-b553-c3a6ca984971", "en": "Martial arts", "de": ""}, {"id": "e4be42c7-e848-44dc-b957-09f8f16ef45d", "en": "Jogging, Running", "de": ""}, {"id": "9bed4975-7229-473a-b9f1-d5b3985c8fce", "en": "Skateboarding, Longboarding", "de": ""}, {"id": "040ab785-b0ab-4577-abf5-59062be1eccd", "en": "Soccer", "de": ""}, {"id": "1e2585f3-0880-430b-bd06-5e40c69b9713", "en": "Table tennis", "de": ""}, {"id": "f4bdf881-142e-43e2-b1c9-c94971ddfb70", "en": "Tennis", "de": ""}, {"id": "a8c28dc4-5681-47c8-a722-cc8b2f994fa8", "en": "Water sports", "de": ""}, {"id": "f30d80d8-fef1-4319-8de2-c63750cd83c7", "en": "Winter sports", "de": ""}]


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('interest',
                    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.create_table('user_interests',
                    sa.Column('user_id', postgresql.UUID(as_uuid=True), nullable=True),
                    sa.Column('interest_id', postgresql.UUID(as_uuid=True), nullable=True),
                    sa.ForeignKeyConstraint(['interest_id'], ['interest.id'], ),
                    sa.ForeignKeyConstraint(['user_id'], ['user.id'], )
                    )
    # ### end Alembic commands ###

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

    users = conn.execute("SELECT user_id, interest_list FROM personal_information WHERE interest_list IS NOT NULL OR interest_list != '{}';")
    for user in users:
        for interest in user[1]:
            for i in interests:
                if i['en'] == interest:
                    conn.execute(f"INSERT INTO user_interests (user_id, interest_id) VALUES ('{str(user[0])}', '{i['id']}')")
                    break

    op.drop_column('personal_information', 'interest_list')


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('personal_information',
                  sa.Column('interest_list', postgresql.ARRAY(sa.VARCHAR()), autoincrement=False, nullable=True))
    op.drop_table('user_interests')
    op.drop_table('interest')
    # ### end Alembic commands ###
