"""fix_campaigns_history_search

Revision ID: 8d2a0916a511
Revises: 33960d440b60
Create Date: 2021-06-08 09:37:25.818906

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

# revision identifiers, used by Alembic.
revision = '8d2a0916a511'
down_revision = '51369b83c7fa'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    gras_user_id = bind.execute(f'''SELECT id from "User" WHERE name = 'GRAS' AND is_service_account is true''').fetchone()[0]
    bind.execute(f'''UPDATE "ProjectHistoryItem" set user_id = {gras_user_id} where user_id is null''')

    bind.execute(f'''
        with keywords as (
            SELECT
                id as item_id,
                json_array_elements(new_value)->>'name' as value
            FROM "ProjectHistoryItem"
            WHERE action_id IN (19, 20, 23)
        ),
        grouped_by_project as (
            SELECT
                item_id,
                array_agg(keywords.value) as value
            FROM keywords
            GROUP BY item_id
        )
        UPDATE "ProjectHistoryItem"
        SET keywords = array_to_string(grouped_by_project.value, ' ')
        FROM grouped_by_project
        WHERE grouped_by_project.item_id = "ProjectHistoryItem".id AND action_id IN (19, 20, 23)
    ''')

    bind.execute(f'''
        with keywords as (
            SELECT
                id as item_id,
                json_array_elements(old_value)->>'name' as value
            FROM "ProjectHistoryItem"
            WHERE action_id IN (21, 22, 24)),
        grouped_by_project as (
            SELECT
                item_id,
                array_agg(keywords.value) as value
            FROM keywords
            GROUP BY item_id
        )

        UPDATE "ProjectHistoryItem"
        SET keywords = array_to_string(grouped_by_project.value, ' ')
        FROM grouped_by_project
        WHERE grouped_by_project.item_id = "ProjectHistoryItem".id AND action_id IN (21, 22, 24)
    ''')

    bind.execute('''
        UPDATE "ProjectHistoryItem" set old_value = '[]'::json WHERE old_value::jsonb = 'null'::jsonb AND action_id in (5, 11);
        UPDATE "ProjectHistoryItem" set new_value = '[]'::json WHERE new_value::jsonb = 'null'::jsonb AND action_id in (5, 11);
    ''')


def downgrade():
    pass