UPDATE IDN_OAUTH2_ACCESS_TOKEN
SET TOKEN_SCOPE_HASH=md5(random()::text || current_timestamp::text)::VARCHAR(32)
WHERE TOKEN_ID IN (
	SELECT DISTINCT a.TOKEN_ID
	FROM IDN_OAUTH2_ACCESS_TOKEN a
	JOIN (
		SELECT CONSUMER_KEY_ID, AUTHZ_USER, USER_DOMAIN, TENANT_ID, TOKEN_SCOPE_HASH, USER_TYPE, TOKEN_STATE, COUNT(*)
		FROM IDN_OAUTH2_ACCESS_TOKEN
		GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, USER_DOMAIN, TENANT_ID, TOKEN_SCOPE_HASH, USER_TYPE, TOKEN_STATE
		HAVING COUNT(*)>1 AND TOKEN_STATE='ACTIVE'
	) b
	ON a.CONSUMER_KEY_ID = b.CONSUMER_KEY_ID
	AND a.AUTHZ_USER = b.AUTHZ_USER
	AND a.USER_DOMAIN = b.USER_DOMAIN
	AND a.TENANT_ID = b.TENANT_ID
	AND a.TOKEN_SCOPE_HASH = b.TOKEN_SCOPE_HASH
	AND a.USER_TYPE = b.USER_TYPE
	AND a.TOKEN_STATE = b.TOKEN_STATE
);

ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN DROP CONSTRAINT IF EXISTS CON_APP_KEY;
ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ADD CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH, TOKEN_STATE,TOKEN_STATE_ID,IDP_ID);
