"""Merge duplicate auth dashboard entires

Revision ID: 76ee53eb640a
Revises: 91f9cd061108
Create Date: 2024-02-09 14:51:11.176619

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '76ee53eb640a'
down_revision = '91f9cd061108'
branch_labels = None
depends_on = None


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

    duplicate_ids = conn.execute(
        sa.text(
            """SELECT resource_id FROM public.authorization 
                    WHERE auth_type = 'DASHBOARD' GROUP BY  resource_id HAVING COUNT(*) > 1;"""
        )
    ).fetchall()

    if duplicate_ids:
        for duplicate_id in duplicate_ids:
            duplicate_rows = conn.execute(
                sa.text(
                    """SELECT * FROM public.authorization  
                        WHERE auth_type = 'DASHBOARD' and resource_id = :resource_id"""
                ),
                {"resource_id": duplicate_id[0]},
            ).fetchall()
            merged_row = duplicate_rows[0]._asdict()
            for duplicate_row in duplicate_rows[1:]:
                row = duplicate_row._asdict()
                merged_row["roles"] += row["roles"]
                conn.execute(
                    sa.text(
                    """DELETE FROM public.authorization WHERE id = :id"""
                    ),
                    {"id": row["id"]},
                )
            merge_rows = list(set(merged_row["roles"]))
            # Update the first row with the merged values
            conn.execute(
                sa.text(
                    """UPDATE public.authorization SET roles = :roles 
                        WHERE auth_type = 'DASHBOARD' AND id = :id"""
                ),
                {"roles": merge_rows, "id": merged_row["id"]},
            )
        # ### end Alembic commands ###



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