====== Fix validity data on business roles ======

Release 15.17.0 fixed a situation where sub roles shared by multiple automatic (business) roles sometimes did not update their validity after a contract change. See [[https://redmine.bcvsolutions.eu/issues/40570|redmine #40570]].

  * **Warning:** the fix prevents new inconsistencies from being created, but it does not repair existing incorrect validities of sub roles in the data.
  * Use the detection SQL to determine the impact on the project, and use the repair SQL (PostgreSQL) to fix it. Run the repair repeatedly until it updates 0 rows.

===== Detection =====

Number of affected users + logins:

<code sql>
select count(distinct i.username), string_agg(distinct i.username, ',') as loginy
from idm_identity_role ir
join idm_identity_contract ic on ic.id = ir.identity_contract_id
join idm_identity i on i.id = ic.identity_id
join idm_identity_role direct on direct.id = ir.direct_role_id
where ir.direct_role_id is not null
  and (
    (ir.valid_till != direct.valid_till or ir.valid_from != direct.valid_from)
    or (ir.valid_till is not null and direct.valid_till is null)
    or (ir.valid_from is not null and direct.valid_from is null)
    or (ir.valid_till is null and direct.valid_till is not null)
    or (ir.valid_from is null and direct.valid_from is not null)
  );
</code>

===== Repair =====

Repeat until 0 rows are updated:

<code sql>
update idm_identity_role as ir
set valid_from = direct.valid_from,
    valid_till = direct.valid_till,
    modified = now()
from idm_identity_role as direct
where direct.id = ir.direct_role_id
  and ir.direct_role_id is not null
  and (
    (ir.valid_till != direct.valid_till or ir.valid_from != direct.valid_from)
    or (ir.valid_till is not null and direct.valid_till is null)
    or (ir.valid_from is not null and direct.valid_from is null)
    or (ir.valid_till is null and direct.valid_till is not null)
    or (ir.valid_from is null and direct.valid_from is not null)
  );
</code>