/**
 * Script that deactivates users who have not logged in within the last 6 months.
 * See https://www.redradishtech.com/pages/viewpage.action?pageId=11796495 
 * Loosely based on Adaptavist's sample at https://www.adaptavist.com/doco/display/SFJ/Automatically+deactivate+inactive+JIRA+users
 * Adaptavist's script has a bug where if a user has *never* logged in, they will never be deactivated. We fix this by checking the user creation date too.
 *
 * Note: I suggest using the SQL variant (deactivate-inactive-jira-users.groovy) of this script in production.
 *
 * jeff@redradishtech.com, 5/Jun/19
 * v1.0
*/
import com.atlassian.crowd.embedded.api.User
import com.atlassian.crowd.embedded.api.CrowdService
import com.atlassian.crowd.embedded.api.UserWithAttributes
import com.atlassian.crowd.embedded.impl.ImmutableUser
import com.atlassian.crowd.embedded.api.SearchRestriction
import com.atlassian.jira.bc.user.UserService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.user.ApplicationUsers
import com.atlassian.crowd.search.query.entity.restriction.constants.UserTermKeys
import com.atlassian.crowd.search.query.entity.restriction.constants.DirectoryTermKeys
import com.atlassian.crowd.search.builder.Restriction
import com.atlassian.crowd.search.builder.QueryBuilder
import com.atlassian.crowd.search.query.entity.EntityQuery
import com.atlassian.crowd.search.EntityDescriptor
import com.atlassian.jira.crowd.embedded.ofbiz.OfBizUser

import org.joda.time.DateTime;
import org.joda.time.Period;

CrowdService crowdService = ComponentAccessor.crowdService

// In a perfect world Jira would let us find exactly the users we want to deactivate with CQL expression 'lastLogin > -6m OR (!lastLogin AND createdDate<-6m)'. Sadly 'lastLogin.lastLoginMillis' is considered a 'secondary' property which Crowd CQL doesn't support (https://developer.atlassian.com/server/crowd/crowd-query-language/). Crowd CQL also doesn't support relative dates like '-6m'. Nor does it support finding users from a particular directory (some of ours may be read-only).
// 
// So instead we search for all active users, and manually check the lastLogin/create date.
// 
// First we search for active users. We don't use UserUtil.getUsers() (unlike every other example on the web), as that returns ApplicationUsers for which it is impossible to get the underlying Ofbiz object, which we need to get the created_date. Instead we use CrowdService.search(), which returns OfBizUsers (https://docs.atlassian.com/software/jira/docs/api/7.2.0/com/atlassian/jira/crowd/embedded/ofbiz/OfBizUser.html).
// QueryBuilder has excellent Javadocs at https://docs.atlassian.com/atlassian-crowd/3.2.3/com/atlassian/crowd/search/builder/QueryBuilder.html
// This returns an iterable of OfBizUsers (https://docs.atlassian.com/software/jira/docs/api/7.2.0/com/atlassian/jira/crowd/embedded/ofbiz/OfBizUser.html) actually
def SearchRestriction active = Restriction.on(UserTermKeys.ACTIVE).exactlyMatching(Boolean.TRUE)
def foundUsers = crowdService.search(
        QueryBuilder.queryFor(User.class, EntityDescriptor.user()).with(active).returningAtMost(EntityQuery.ALL_RESULTS)
        ) as ArrayList<OfBizUser>;

log.info "Checking ${foundUsers.size()} active users for possible deactivation-due-to-inactivity"

def shouldDeactivate(User user, DateTime lastUsed) {
        def INACTIVITY_PERIOD = Period.parse("P1Y") // Period of inactivity after which user is deactivated. The format is https://en.wikipedia.org/wiki/ISO_8601#Durations
        // JodaTime 'time ago' calculation: https://stackoverflow.com/a/3859313/7538322
        def expiryDate = lastUsed.plus(INACTIVITY_PERIOD);
        log.info "User ${user.name} will be deactivated after ${expiryDate}";
        return expiryDate.isBeforeNow();
}

def deactivate(User user) {
        UserService userService = ComponentAccessor.getComponent(UserService)
        ApplicationUser updateUser = ApplicationUsers.from(ImmutableUser.newUser(user).active(false).toUser());
        UserService.UpdateUserValidationResult updateUserValidationResult = userService.validateUpdateUser(updateUser);
        if (updateUserValidationResult.isValid()) {
                // Comment out this line to do a dry run:
                //userService.updateUser(updateUserValidationResult)
                return true
        } else {
                log.error "Update of ${user.name} failed: ${updateUserValidationResult.getErrorCollection().getErrors().entrySet().join(',')}";
                return false
        }
}

long count = 0
// Restrict to our Internal directory, with ID 1, otherwise we'll get errors trying to modify read-only LDAP users.
foundUsers.findAll { ofbizUser -> ofbizUser.directoryId == 1 }.each { ofbizUser ->
        def UserWithAttributes user = crowdService.getUserWithAttributes(ofbizUser.getName());
	// FIXME: also need to consider 'lastAuthenticated' if Confluence or an external client uses Jira for auth, and you want these authentications to be considered 'activity'
        String lastLoginMillis = user.getValue('login.lastLoginMillis');
        if (lastLoginMillis?.isNumber()) {
                DateTime lastLogin = new DateTime(Long.parseLong(lastLoginMillis));
                if (shouldDeactivate(user, lastLogin) && deactivate(user)) {
                        log.warn "Deactivated ${user.name}, who was last active on ${lastLogin}";
                        count++
                }
        } else if (!lastLoginMillis) {
                DateTime created = new DateTime(ofbizUser.getCreatedDate());
                if (shouldDeactivate(user, created) && deactivate(user)) {
                        log.warn "Deactivated ${user.name}, who has never logged in and was created on ${created}";
                        count++;
                }
        }
}

"${count} inactive users automatically deactivated.\n"

-- Creates a queries.inactive_users view in a Jira database, listing inactive user accounts that might be deactivated by deactivate-inactive-jira-users.groovy
--
-- Last updated: 14/Oct/24
-- See https://www.redradishtech.com/display/KB/Automatically+deactivating+inactive+Jira+users

-- @provides queries.inactive_users_all
drop view if exists queries.inactive_users_all cascade;
create schema if not exists queries;
create view queries.inactive_users_all AS
WITH userlogins AS (
        SELECT DISTINCT ON (user_name) -- If LDAP is used there will be 2 directories ('LDAP' and 'Jira Internal Directory'), each with a duplicate set of cwd_user rows. The "DISTINCT ON (user_name) ... ORDER BY user_name, cwd_directory.directory_position ASC" gets us only the first cwd_user record by directory 'position', i.e. the one actually authenticated against that will have up-to-date lastLogin stats.
		cwd_user.directory_id
        , user_name
        , email_address
        , cwd_user.created_date
        , timestamp with time zone 'epoch'+lastlogins.attribute_value::numeric/1000 * INTERVAL '1 second' AS lastlogin
        , timestamp with time zone 'epoch'+lastauths.attribute_value::numeric/1000 * INTERVAL '1 second' AS lastauth   -- REST queries count as authentications, not logins
        FROM
        cwd_user
        JOIN (select * from cwd_directory WHERE active=1) as cwd_directory ON cwd_user.directory_id = cwd_directory.id
        JOIN cwd_membership ON (cwd_membership.lower_child_name=cwd_user.lower_user_name and cwd_membership.directory_id=cwd_directory.id)
        JOIN (
                select * from globalpermissionentry WHERE permission IN ('USE', 'ADMINISTER')
             ) AS globalpermissionentry ON cwd_membership.lower_parent_name=globalpermissionentry.group_id
             LEFT JOIN LATERAL (select * from cwd_user_attributes WHERE directory_id=cwd_directory.id AND attribute_name in ('login.lastLoginMillis')) lastlogins ON lastlogins.user_id=cwd_user.id
             LEFT JOIN LATERAL (select * from cwd_user_attributes WHERE directory_id=cwd_directory.id AND attribute_name in ('lastAuthenticated')) lastauths ON lastauths.user_id=cwd_user.id
        WHERE cwd_user.active=1 
		-- Note that we cannot have any further WHERE clauses, e.g. limiting email_address to a certain pattern. Say user 'jjsmith' Internal user has email jjsmith@foo.com, and is shadowed by 'jjsmith' in LDAP, with email jjsmith@bar.com. If we limit to 'not like '%@bar.com' then only the inactive foo.com username's attributes would be found. Instead our caller must do the filtering.
		-- Specific exceptions can be added to the 'never-deactivate' group.
		ORDER BY user_name, cwd_directory.directory_position ASC
)
, lastassigns AS (
        SELECT DISTINCT
        newvalue AS user_name
        , max(created) AS lastassign
        FROM changegroup cg
        JOIN changeitem ci ON cg.id = ci.groupid
        WHERE field='assignee' group by 1
)
, lastwatch AS (
	SELECT cwd_user.user_name
		   , max(userassociation.created) AS lastwatch
	FROM app_user
	LEFT JOIN userassociation ON userassociation.source_name=app_user.user_key
	JOIN cwd_user USING (lower_user_name)
	WHERE association_type='WatchIssue'
	GROUP BY user_name )
, lastreactivate AS (
	-- Check the audit log for account reactivations.
	-- If an admin recently reactivated a dormant account, we don't want to deactivate it due to the user's inactivity
	select
		app_user.lower_user_name AS user_name
		,max(to_timestamp("ENTITY_TIMESTAMP"/1000)::date) AS lastreactivate
	 from "AO_C77861_AUDIT_ENTITY" JOIN app_user ON app_user.user_key="PRIMARY_RESOURCE_ID" where "PRIMARY_RESOURCE_TYPE"='USER' AND "CHANGE_VALUES" ~ '"from":"Inactive","to":"Active"}]$'
	group by user_name
)
, neverdeactivate AS (
	select cwd_user.user_name from cwd_user JOIN cwd_membership ON cwd_user.id=cwd_membership.child_id JOIN cwd_group ON cwd_membership.parent_id=cwd_group.id WHERE cwd_group.group_name='never-deactivate'
)
SELECT distinct
	user_name
	, directory_id
	, email_address
	, to_char(created_date, 'YYYY-MM-DD') AS created
	, to_char(lastlogin, 'YYYY-MM-DD') AS lastlogin
	, to_char(lastauth, 'YYYY-MM-DD') AS lastauth
	, to_char(lastassign, 'YYYY-MM-DD') AS lastassign
	, to_char(lastwatch, 'YYYY-MM-DD') AS lastwatch
	, to_char(lastreactivate, 'YYYY-MM-DD') AS lastreactivate
	, (select count(*) from jiraissue where assignee=userlogins.user_name) AS assigneecount
FROM userlogins
LEFT JOIN lastassigns USING (user_name)
LEFT JOIN lastreactivate USING (user_name)
LEFT JOIN lastwatch USING (user_name)
 WHERE
	(created_date < now() - '3 months'::interval)
	AND ((lastlogin < now() - '3 months'::interval) OR lastlogin is null) 
	AND ((lastauth < now() - '3 months'::interval) OR lastauth is null) 
	AND ((lastassign < now() - '3 months'::interval) OR lastassign is null)
	AND ((lastreactivate < now() - '3 months'::interval) OR lastreactivate is null)
	-- Note that we don't filter on 'lastwatch' (although we include it), as it is not a good sign of user liveness - at least, no better than lastlogin (people need to be logged in to watch issues). lastwatch is displayed for informational purposes - if the date is later than lastlogin, it means someone other than user_name added user_name as a watcher after they last logged in.
	AND NOT EXISTS (select * from neverdeactivate where user_name=userlogins.user_name)
ORDER BY lastlogin desc nulls last ;
GRANT select on queries.inactive_users to jira_ro;

-- @provides queries.inactive_users
-- Inactive customer accounts
drop view if exists queries.inactive_users;

CREATE VIEW queries.inactive_users AS
SELECT user_name,
       email_address,
       created,
       lastlogin,
       lastauth,
       lastassign,
       lastwatch,
       lastreactivate
FROM queries.inactive_users_all
WHERE email_address not like '%@mycompany.com'
  AND directory_id=1;

/**
 * Script that deactivates users who have not logged in within the last X months, based on a SQL query.
 * See See https://www.redradishtech.com/pages/viewpage.action?pageId=11796495 
 * 
 * Loosely based on Adaptavist's sample at https://www.adaptavist.com/doco/display/SFJ/Automatically+deactivate+inactive+JIRA+users
 * 
 * Instead of trying to figure out which users to deactivate in code, we instead rely on a queries.inactive_users table or view being defined in the Jira database. The SQL can then be as fancy or customized as needed: e.g. we might want to avoid deactivating role accounts which are assigned issues but never log in. The only requirement for our table or view is that a 'user_name' column must exist.
 *
 * jeff@redradishtech.com, 19/Dec/2019
 * v1.0
*/
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.user.ApplicationUsers
import com.atlassian.jira.bc.user.UserService
import com.atlassian.crowd.embedded.api.User
import com.atlassian.crowd.embedded.api.UserWithAttributes
import com.atlassian.crowd.embedded.api.CrowdService
import com.atlassian.crowd.embedded.impl.ImmutableUser


/** Deactivate a user.
 * @return null on success, or a String error message.
 */
def String deactivate(String user_name) {
        CrowdService crowdService = ComponentAccessor.crowdService
        def UserWithAttributes user = crowdService.getUserWithAttributes(user_name);
        if (!user.active) return "Already inactive";
        UserService userService = ComponentAccessor.getComponent(UserService)
        ApplicationUser updateUser = ApplicationUsers.from(ImmutableUser.newUser(user).active(false).toUser());
        UserService.UpdateUserValidationResult updateUserValidationResult = userService.validateUpdateUser(updateUser);
        if (updateUserValidationResult.isValid()) {
                // Comment out this line to do a dry run:
                userService.updateUser(updateUserValidationResult)
                return null
        } else {
                return updateUserValidationResult.getErrorCollection().getErrors().entrySet().join(',')
        }
}

// https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html
import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """select * from queries.inactive_users;"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

log.warn "Beginning inactive user deactivation run"
long count = 0
try {
    sql.eachRow(sqlStmt) {
    // https://stackoverflow.com/questions/50041526/how-to-read-each-row-in-a-groovy-sql-statement
        def errmsg = deactivate(it['user_name'] as String);
        if (!errmsg) {
                log.warn "Deactivated ${it['user_name']}: ${it}";
                count++
        } else {
                log.error "Failed to deactivate ${it['user_name']}: ${errmsg}";
        }
    }
}
finally {
    sql.close()
}
"${count} inactive users automatically deactivated.\n"

# curl --silent --get -u cli:cli http://jira.localhost/rest/usermanagement/1/search -d 'entity-type=user' --data-urlencode 'restriction=active=true and email=jeff@redradishtech.com and createdDate>2013-09-02'   --header 'Accept: application/json'  | jq .
{
  "expand": "user",
  "users": [
    {
      "link": {
        "href": "http://jira.localhost/rest/usermanagement/1/user?username=jturner",
        "rel": "self"
      },
      "name": "jturner"
    }
  ]
}