Fix duplicate privileges in webapp

[1t5j0y]

Issue:

• In some organisations, when a privilege is removed from UserGroups it is not retained on refresh. This is because of duplicate group privileges for the same entities with different values.
• Currently UAT and prod orgs have different UUIDs for the privileges.

AC:

• Introduce new column impl_version for group_privilege - defaults to 1
• Fixing bundle upload:
  • Via bundle upload, instead of updating privilege based on uuid, update it based on combination of entities' UUIDs(group, privilege, subject type, program, encounterType, programEncounterType, checklistDetail). Update voided and allow fields if we find the combination of entities.
  • Only impl_version 1, group privileges need to be exported and imported.
  • If the above combination not present then create new entry in the group_privilege table.
  • Make sure performance of bundle import doesn't get impacted. To avoid the same, query and keep the different entities in memory instead of fetching each time for each privilege from DB
• Add db constraint for groupPrivilege table(for group, privilege, subject type, program, encounterType, programEncounterType, checklistDetail) when impl_version=1. Don't include voided field in the constraint since we need only one entry in the db - voided or not.
• Add query to update the impl_version of all voided(independent of allow is true or false) to 0 and non-voided to 1

Old: Ignore

Context:

Via #683 and maybe other routes, it is possible for duplicate non-voided group_privilege records for the same entity to be created in the database. This leads to confusing behaviour.

In https://avni.freshdesk.com/a/tickets/3760 we saw that there are duplicate records for a few entities, some with allow as false and the other as true. On webapp user groups -> permissions screen, the true records were not shown. On client, since there is a record with allow as true, the privilege is allowed and sync tries to pull entities that the user does not have privileges to and is causing association errors during sync.

Further analysis might be required.

[1t5j0y]

This affects almost all orgs currently. Query to check impact:

with gp_counts as (select id, organisation_id, allow,
                          row_number()
                          over (partition by group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id order by id) as row_num
                   from group_privilege
                   where is_voided = false)
-- select * from gp_counts where row_num > 1;
select o.name, count(*) from gp_counts join  organisation o on o.id = organisation_id where row_num > 1 group by o.name order by o.name ;

[1t5j0y]

Queries in #746 to be executed (or alternate queries to remove dupes depending on environment) before deploying this.

[petmongrels]

Please add query to the release task if not added.

[AchalaBelokar]

• [ ] After uploading the bundle on AchalaB@achalacatch it is giving me error

errors-achalaissuebundle.csv

achalaissuebundle.zip