107 lines
3.5 KiB
TypeScript
107 lines
3.5 KiB
TypeScript
import { getQuery } from 'h3'
|
|
import { db, sql } from '../../utils/database'
|
|
import { requirePermission } from '../../utils/rbac'
|
|
|
|
const SORTABLE_COLUMNS = ['display_name', 'email', 'status', 'created', 'last_login'] as const
|
|
type SortColumn = typeof SORTABLE_COLUMNS[number]
|
|
|
|
// Lifecycle order used by the "status" sort. Active first, then not-verified
|
|
// self-registrations, then pending invites, then expired ones — keeps healthy
|
|
// accounts at the top under default desc and surfaces stale invites under asc.
|
|
const STATUS_ORDER_SQL = `CASE
|
|
WHEN users.verified = true THEN 0
|
|
WHEN users.password IS NOT NULL THEN 1
|
|
WHEN users.token_expires_at > now() THEN 2
|
|
ELSE 3
|
|
END`
|
|
|
|
export default defineEventHandler(async (event) => {
|
|
await requirePermission(event, 'users.view')
|
|
|
|
const query = getQuery(event)
|
|
|
|
const pageRaw = Number(query.page ?? 1)
|
|
const pageSizeRaw = Number(query.pageSize ?? 50)
|
|
const page = Number.isFinite(pageRaw) && pageRaw >= 1 ? Math.floor(pageRaw) : 1
|
|
const pageSize = Number.isFinite(pageSizeRaw) && pageSizeRaw >= 1 && pageSizeRaw <= 200
|
|
? Math.floor(pageSizeRaw)
|
|
: 50
|
|
|
|
const q = typeof query.q === 'string' ? query.q.trim() : ''
|
|
|
|
const sortParam = typeof query.sort === 'string' ? query.sort : 'created'
|
|
const sort: SortColumn = (SORTABLE_COLUMNS as readonly string[]).includes(sortParam)
|
|
? sortParam as SortColumn
|
|
: 'created'
|
|
const dir: 'asc' | 'desc' = query.dir === 'asc' ? 'asc' : 'desc'
|
|
|
|
const pattern = q ? `%${q}%` : null
|
|
|
|
const totalRow = await db
|
|
.selectFrom('users')
|
|
.select(eb => eb.fn.countAll<string>().as('count'))
|
|
.$if(!!pattern, qb => qb.where(eb => eb.or([
|
|
eb('users.display_name', 'ilike', pattern!),
|
|
eb('users.email', 'ilike', pattern!)
|
|
])))
|
|
.executeTakeFirst()
|
|
const total = Number(totalRow?.count ?? 0)
|
|
|
|
const orderExpr = sort === 'last_login'
|
|
? sql`last_login ${sql.raw(dir)} nulls last`
|
|
: sort === 'display_name'
|
|
? sql`users.display_name ${sql.raw(dir)}`
|
|
: sort === 'email'
|
|
? sql`users.email ${sql.raw(dir)}`
|
|
: sort === 'status'
|
|
? sql`${sql.raw(STATUS_ORDER_SQL)} ${sql.raw(dir)}`
|
|
: sql`users.created ${sql.raw(dir)}`
|
|
|
|
const rows = await db
|
|
.selectFrom('users')
|
|
.select([
|
|
'users.id',
|
|
'users.display_name',
|
|
'users.email',
|
|
'users.verified',
|
|
'users.created',
|
|
'users.roles',
|
|
'users.password',
|
|
'users.token_expires_at',
|
|
eb => eb
|
|
.selectFrom('activity_logs')
|
|
.select(eb2 => eb2.fn.max('activity_logs.timestamp').as('last_login'))
|
|
.where('activity_logs.event_type', '=', 'LOGIN')
|
|
.whereRef('activity_logs.user_id', '=', 'users.id')
|
|
.as('last_login')
|
|
])
|
|
.$if(!!pattern, qb => qb.where(eb => eb.or([
|
|
eb('users.display_name', 'ilike', pattern!),
|
|
eb('users.email', 'ilike', pattern!)
|
|
])))
|
|
.orderBy(orderExpr)
|
|
.limit(pageSize)
|
|
.offset((page - 1) * pageSize)
|
|
.execute()
|
|
|
|
const now = Date.now()
|
|
const decorated = rows.map(({ password, token_expires_at, ...rest }) => {
|
|
let status: 'active' | 'not_verified' | 'pending_invite' | 'expired_invite'
|
|
if (rest.verified) {
|
|
status = 'active'
|
|
} else if (password !== null) {
|
|
status = 'not_verified'
|
|
} else {
|
|
const expiresMs = token_expires_at ? new Date(token_expires_at).getTime() : 0
|
|
status = expiresMs > now ? 'pending_invite' : 'expired_invite'
|
|
}
|
|
return { ...rest, status }
|
|
})
|
|
|
|
return {
|
|
rows: decorated,
|
|
total,
|
|
page,
|
|
pageSize
|
|
}
|
|
})
|