"""recently_viewed_projects_to_recent_search

Revision ID: b0fd9f4bf611
Revises: 5356915b3df4
Create Date: 2023-02-27 13:31:32.698671

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from migrations.utils import get_table

# revision identifiers, used by Alembic.
revision = 'b0fd9f4bf611'
down_revision = 'e0835a6aa5b9'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    op.drop_constraint(op.f('ck_RecentSearchItem_type'), 'RecentSearchItem', type_="check")
    op.create_check_constraint(
        op.f("ck_RecentSearchItem_type"), "RecentSearchItem",
        f"(artist_id is not null AND type = 0) OR (project_id is not null AND type = 1) OR (campaign_id is not null AND type = 2) OR (project_id is not null AND type = 4)"
    )

    op.drop_constraint('uq_RecentSearchItem_project_id_user_id', 'RecentSearchItem', type_='unique')
    op.create_unique_constraint('uq_RecentSearchItem_project_id_user_id_type', 'RecentSearchItem',
                                ['project_id', 'user_id', 'type'])

    recent_search_item_table = get_table(bind, "RecentSearchItem")

    recently_viewed_projects_history_items = bind.execute(
        '''
            SELECT "ProjectHistoryItem".project_id, "ProjectHistoryItem".user_id, max("ProjectHistoryItem".created_at)
            FROM "ProjectHistoryItem"
            WHERE action_id = 43
            GROUP BY "ProjectHistoryItem".project_id, "ProjectHistoryItem".user_id;
        '''
    ).fetchall()

    recently_viewed_items = []
    for project_id, user_id, created_at in recently_viewed_projects_history_items:
        recently_viewed_items.append(
            {
                "type": 4,
                "user_id": user_id,
                "project_id": project_id,
                "created_at": created_at
            }
        )
    op.bulk_insert(recent_search_item_table, recently_viewed_items)

    bind.execute("""DELETE FROM "ProjectHistoryItem" WHERE action_id = 43 """)
    bind.execute("""DELETE FROM "ProjectHistoryAction" WHERE id = 43 """)


def downgrade():
    bind = op.get_bind()
    bind.execute(""" INSERT INTO "ProjectHistoryAction"(id, type) VALUES (43, 'open_project') """)

    project_history_item_table = get_table(bind, "ProjectHistoryItem")

    recently_viewed_projects_items = bind.execute(
        '''
            SELECT "RecentSearchItem".project_id, "RecentSearchItem".user_id, "RecentSearchItem".searched_at
            FROM "RecentSearchItem"
            WHERE "RecentSearchItem".type = 4
        '''
    ).fetchall()

    recently_viewed_items = []
    for project_id, user_id, searched_at in recently_viewed_projects_items:
        recently_viewed_items.append(
            {
                "action_id": 43,
                "user_id": user_id,
                "project_id": project_id,
                "created_at": searched_at
            }
        )
    op.bulk_insert(project_history_item_table, recently_viewed_items)

    bind.execute("""DELETE FROM "RecentSearchItem" WHERE type = 4 """)

    op.drop_constraint(op.f('ck_RecentSearchItem_type'), 'RecentSearchItem', type_="check")
    op.create_check_constraint(
        op.f("ck_RecentSearchItem_type"), "RecentSearchItem",
        f"(artist_id is not null AND type = 0) OR (project_id is not null AND type = 1) OR (campaign_id is not null AND type = 2)"
    )

    op.drop_constraint('uq_RecentSearchItem_project_id_user_id_type', 'RecentSearchItem', type_='unique')
    op.create_unique_constraint('uq_RecentSearchItem_project_id_user_id', 'RecentSearchItem',
                                ['project_id', 'user_id'])