"""Update All Tasks filter to display tasks authorized for current logged in user
and update applicationId label to Submission Id

Revision ID: 78e2529b7c39
Revises: d833f3edc621
Create Date: 2024-03-21 15:37:58.949691

"""
from alembic import op
import sqlalchemy as sa
import json

# revision identifiers, used by Alembic.
revision = '78e2529b7c39'
down_revision = 'd833f3edc621'
branch_labels = None
depends_on = None



def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()

    filter_entry = conn.execute(
                sa.text("""SELECT * FROM public.Filter 
                WHERE name='All Tasks' AND criteria::text = '{}' AND status='active' AND tenant is null""")).fetchone()
    if filter_entry:
        filter = filter_entry._asdict()

        # Update applicationId label to Submission Id
        # Include all existing variables & rename applicationId label
        applicationId = {"name": "applicationId", "label": "Submission Id"}
        variables = [variable for variable in filter['variables'] if variable.get("name") != "applicationId"]
        variables.append(applicationId)
        
        # variables field is of type ARRAY of JSON. Construct the json array elements 
        array_elements = [f"'{json.dumps(var)}'::json" for var in variables]
        criteria = """'{"candidateGroupsExpression": "${currentUserGroups()}", "includeAssignedTasks": true}'"""

        # Construct the UPDATE statement with the ARRAY
        query = f"""
            UPDATE public.Filter
            SET criteria = {criteria},
            variables = ARRAY[{', '.join(array_elements)}]
            WHERE id = :filter_id
        """
        
        stmt = sa.text(query)
        conn.execute(stmt, {"filter_id": filter["id"]})
        
    # ### end Alembic commands ###


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