set role mc;

SELECT organisation.name                                            as "Organisation",
       g."name"                                                     as "Group",
       p."name"                                                     as "Privilege",
       st."name"                                                    as "Subject type",
       prg.name                                                     as "Program",
       penct.name                                                   as "Program encounter type",
       enct.name                                                    as "Encounter type",
       ''                                                           as "Checklist details",
       ''                                                           as "Allow Access",
       CONCAT('"', string_agg(group_privilege.id::text, ', '), '"') as "GP Ids"
FROM group_privilege
         join "groups" g on g.id = group_privilege.group_id
         left join subject_type st on st.id = group_privilege.subject_type_id
         left join program prg on prg.id = group_privilege.program_id
         left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id
         left join encounter_type enct on enct.id = group_privilege.encounter_type_id
         join organisation on group_privilege.organisation_id = organisation.id
         join privilege p on p.id = group_privilege.privilege_id
WHERE group_privilege.is_voided = false
GROUP BY organisation.name,
         group_id,
         g."name",
         privilege_id,
         p."name",
         subject_type_id,
         st."name",
         program_id,
         prg.name,
         program_encounter_type_id,
         penct.name,
         encounter_type_id,
         enct.name
HAVING COUNT(allow) > 1; --299

UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    allow                   = false,
    last_modified_by_id     = 5516
WHERE is_voided = true
  and allow = true;

SELECT organisation.name                                            as "Organisation",
       g."name"                                                     as "Group",
       p."name"                                                     as "Privilege",
       st."name"                                                    as "Subject type",
       prg.name                                                     as "Program",
       penct.name                                                   as "Program encounter type",
       enct.name                                                    as "Encounter type",
       ''                                                           as "Checklist details",
       ''                                                           as "Allow Access",
       CONCAT('"', string_agg(group_privilege.id::text, ', '), '"') as "GP Ids"
FROM group_privilege
         join "groups" g on g.id = group_privilege.group_id
         left join subject_type st on st.id = group_privilege.subject_type_id
         left join program prg on prg.id = group_privilege.program_id
         left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id
         left join encounter_type enct on enct.id = group_privilege.encounter_type_id
         join organisation on group_privilege.organisation_id = organisation.id
         join privilege p on p.id = group_privilege.privilege_id
WHERE organisation.db_user in
      (
          'mc'
          )
  and group_privilege.is_voided = false
GROUP BY organisation.name,
         group_id,
         g."name",
         privilege_id,
         p."name",
         subject_type_id,
         st."name",
         program_id,
         prg.name,
         program_encounter_type_id,
         penct.name,
         encounter_type_id,
         enct.name
HAVING COUNT(distinct allow) > 1;


with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id
                      JOIN public.encounter_type et ON et.id = gp.encounter_type_id
             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Creche Supervisor'
               AND priv.name = 'Schedule visit'
               AND st.name = 'School Going'
               AND et.name = 'Daily morning attendance for school going'
) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id
                      JOIN public.encounter_type et ON et.id = gp.encounter_type_id
             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Creche Supervisor'
               AND priv.name = 'Approve Encounter'
               AND st.name = 'Creche'
               AND et.name = 'Daily morning attendance for creche'
) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id
                      JOIN public.encounter_type et ON et.id = gp.encounter_type_id
             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Creche Supervisor'
               AND priv.name = 'Reject Encounter'
               AND st.name = 'Creche'
               AND et.name = 'Daily morning attendance for creche'
) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id
                      JOIN public.encounter_type et ON et.id = gp.encounter_type_id
             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Creche Supervisor'
               AND priv.name = 'Void visit'
               AND st.name = 'Creche'
               AND et.name = 'Daily morning attendance for creche'
) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id

             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Program team'
               AND priv.name = 'Approve Subject'
               AND st.name = 'Balwadi'

) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id
                      JOIN public.encounter_type et ON et.id = gp.encounter_type_id
             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Program team'
               AND priv.name = 'Approve Encounter'
               AND st.name = 'Balwadi'
               AND et.name = 'Daily morning attendance for balwadi'
) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

with cte as (select gp.id gpid,
                    RANK() OVER ( ORDER BY gp.id DESC) AS rnk
             from group_privilege gp
                      JOIN public.organisation o ON o.id = gp.organisation_id
                      JOIN public.groups g ON g.id = gp.group_id
                      JOIN public.privilege priv ON priv.id = gp.privilege_id
                      JOIN public.subject_type st ON st.id = gp.subject_type_id
                      JOIN public.encounter_type et ON et.id = gp.encounter_type_id
             WHERE o.name = 'Mobile Creches'
               AND g.name = 'Program team'
               AND priv.name = 'Approve Encounter'
               AND st.name = 'Balwadi'
               AND et.name = 'Daily evening attendance for balwadi'
) UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id = 5516,
    is_voided =  cte.rnk > 1,
    allow = FALSE
from cte
WHERE id = cte.gpid;

SELECT organisation.name                                            as "Organisation",
       g."name"                                                     as "Group",
       p."name"                                                     as "Privilege",
       st."name"                                                    as "Subject type",
       prg.name                                                     as "Program",
       penct.name                                                   as "Program encounter type",
       enct.name                                                    as "Encounter type",
       ''                                                           as "Checklist details",
       ''                                                           as "Allow Access",
       CONCAT('"', string_agg(group_privilege.id::text, ', '), '"') as "GP Ids"
FROM group_privilege
         join "groups" g on g.id = group_privilege.group_id
         left join subject_type st on st.id = group_privilege.subject_type_id
         left join program prg on prg.id = group_privilege.program_id
         left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id
         left join encounter_type enct on enct.id = group_privilege.encounter_type_id
         join organisation on group_privilege.organisation_id = organisation.id
         join privilege p on p.id = group_privilege.privilege_id
WHERE group_privilege.is_voided = false
GROUP BY organisation.name,
         group_id,
         g."name",
         privilege_id,
         p."name",
         subject_type_id,
         st."name",
         program_id,
         prg.name,
         program_encounter_type_id,
         penct.name,
         encounter_type_id,
         enct.name
HAVING COUNT(allow) > 1;

with aggregate_gp_ids_data as (
    SELECT max(group_privilege.id)               max_gp_ids,
           unnest(array_agg(group_privilege.id)) duplicate_gp_ids,
           group_id,
           g."name",
           privilege_id,
           p."name",
           subject_type_id,
           st."name",
           program_id,
           prg.name,
           program_encounter_type_id,
           group_privilege.checklist_detail_id,
           encounter_type_id,
           group_privilege.is_voided,
           organisation.name as                  ogname
    FROM group_privilege
             join "groups" g on g.id = group_privilege.group_id
             left join subject_type st on st.id = group_privilege.subject_type_id
             left join program prg on prg.id = group_privilege.program_id
             join organisation on group_privilege.organisation_id = organisation.id
             join privilege p on p.id = group_privilege.privilege_id
    where group_privilege.is_voided = false
    GROUP BY organisation.name,
             group_id,
             g."name",
             privilege_id,
             p."name",
             subject_type_id,
             st."name",
             program_id,
             prg.name,
             program_encounter_type_id,
             group_privilege.checklist_detail_id,
             encounter_type_id,
             group_privilege.is_voided
    HAVING COUNT(allow) > 1
)
select distinct duplicate_gp_ids
from aggregate_gp_ids_data
EXCEPT
select distinct max_gp_ids
from aggregate_gp_ids_data;

Select SUM(duplicate_gp_ids - 1)
from (
         SELECT (count(group_privilege.id)) duplicate_gp_ids
         FROM group_privilege
                  join "groups" g on g.id = group_privilege.group_id
                  left join subject_type st on st.id = group_privilege.subject_type_id
                  left join program prg on prg.id = group_privilege.program_id
                  join organisation on group_privilege.organisation_id = organisation.id
                  join privilege p on p.id = group_privilege.privilege_id
         where group_privilege.is_voided = false
         GROUP BY group_id,
                  g."name",
                  privilege_id,
                  p."name",
                  subject_type_id,
                  st."name",
                  program_id,
                  prg.name,
                  program_encounter_type_id,
                  group_privilege.checklist_detail_id,
                  encounter_type_id,
                  group_privilege.is_voided,
                  organisation.name
         HAVING COUNT(allow) > 1) aggreg;


UPDATE group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id     = 5516,
    is_voided               = true,
    allow                   = FALSE
WHERE id in (with aggregate_gp_ids_data as (
    SELECT max(group_privilege.id)               max_gp_ids,
           unnest(array_agg(group_privilege.id)) duplicate_gp_ids,
           group_id,
           g."name",
           privilege_id,
           p."name",
           subject_type_id,
           st."name",
           program_id,
           prg.name,
           program_encounter_type_id,
           group_privilege.checklist_detail_id,
           encounter_type_id,
           group_privilege.is_voided,
           organisation.name as                  ogname
    FROM group_privilege
             join "groups" g on g.id = group_privilege.group_id
             left join subject_type st on st.id = group_privilege.subject_type_id
             left join program prg on prg.id = group_privilege.program_id
             join organisation on group_privilege.organisation_id = organisation.id
             join privilege p on p.id = group_privilege.privilege_id
    where group_privilege.is_voided = false
    GROUP BY organisation.name,
             group_id,
             g."name",
             privilege_id,
             p."name",
             subject_type_id,
             st."name",
             program_id,
             prg.name,
             program_encounter_type_id,
             group_privilege.checklist_detail_id,
             encounter_type_id,
             group_privilege.is_voided
    HAVING COUNT(allow) > 1
)
             select distinct duplicate_gp_ids
             from aggregate_gp_ids_data
             EXCEPT
             select distinct max_gp_ids
             from aggregate_gp_ids_data);

SELECT case when count(orgName) > 0 then 'Duplicates exist' else 'No more dupes' end FROM (
                                                                                              SELECT organisation.name orgName
                                                                                              FROM group_privilege
                                                                                                       join "groups" g on g.id = group_privilege.group_id
                                                                                                       left join subject_type st on st.id = group_privilege.subject_type_id
                                                                                                       left join program prg on prg.id = group_privilege.program_id
                                                                                                       left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id
                                                                                                       left join encounter_type enct on enct.id = group_privilege.encounter_type_id
                                                                                                       join organisation on group_privilege.organisation_id = organisation.id
                                                                                                       join privilege p on p.id = group_privilege.privilege_id
                                                                                              WHERE group_privilege.is_voided = false
                                                                                              GROUP BY organisation.name,
                                                                                                       group_id,
                                                                                                       g."name",
                                                                                                       privilege_id,
                                                                                                       p."name",
                                                                                                       subject_type_id,
                                                                                                       st."name",
                                                                                                       program_id,
                                                                                                       prg.name,
                                                                                                       program_encounter_type_id,
                                                                                                       penct.name,
                                                                                                       encounter_type_id,
                                                                                                       enct.name
                                                                                              HAVING COUNT(allow) > 1) as duplicatePrivOrgs;