/opt/canhelp/apps/api/src/routes
Edit: /opt/canhelp/apps/api/src/routes/admin.ts (95057B)
import { Hono } from 'hono'
import { eq, count, countDistinct, desc, or, ilike, and, inArray, asc, gte, lte, sql, isNull } from 'drizzle-orm'
import { db } from '../db.js'
import { users, tasks, offers, reviews, reports, categories, categorySuggestions, locations, aiLogs, plans, activityLogs, settings as settingsTable, emailTemplates, notifications, balanceTransactions, specialistCards, skillSuggestions, referralRewards, pushTokens } from '@canhelp/db'
import { requireAdmin, type AuthVariables } from '../middleware/auth.js'
import { redis } from '../redis.js'
import { logActivity } from '../lib/activity.js'
import { invalidateEmailCache } from '../lib/email.js'
import { translateTask, translateString, translateStringsBatch } from '../translate.js'
import { applyBalanceChange } from '../lib/balance.js'
import { sendPushToUser } from '../lib/push.js'
import { notifySpecialistCardApproved, notifySpecialistCardRejected } from '../lib/notif.js'
import { execFile, spawn } from 'node:child_process'
import { promisify } from 'node:util'
import zlib from 'node:zlib'
import fs from 'node:fs'
import path from 'node:path'
import { fileURLToPath } from 'node:url'
const CATEGORIES_CACHE_KEY = 'categories:tree:v2'
const LOCATIONS_CACHE_KEY = 'locations:tree:v2'
const app = new Hono<{ Variables: AuthVariables }>()
// GET /admin/stats
app.get('/stats', requireAdmin, async (c) => {
const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
const [
[{ userCount }],
[{ specialistCount }],
[{ taskCount }],
[{ offerCount }],
[{ reviewCount }],
tasksByStatus,
tasksByDay,
usersByDay,
specialistsByDay,
tokenTotals,
tokensByMonth,
] = await Promise.all([
db.select({ userCount: count() }).from(users),
db.select({ specialistCount: countDistinct(specialistCards.specialistId) }).from(specialistCards).where(eq(specialistCards.isActive, true)),
db.select({ taskCount: count() }).from(tasks),
db.select({ offerCount: count() }).from(offers),
db.select({ reviewCount: count() }).from(reviews),
db.select({ status: tasks.status, count: count() }).from(tasks).groupBy(tasks.status),
db
.select({
day: sql
`DATE_TRUNC('day', ${tasks.createdAt})::date::text`,
count: count(),
})
.from(tasks)
.where(gte(tasks.createdAt, thirtyDaysAgo))
.groupBy(sql`DATE_TRUNC('day', ${tasks.createdAt})`)
.orderBy(sql`DATE_TRUNC('day', ${tasks.createdAt})`),
db
.select({
day: sql`DATE_TRUNC('day', ${users.createdAt})::date::text`,
count: count(),
})
.from(users)
.where(gte(users.createdAt, thirtyDaysAgo))
.groupBy(sql`DATE_TRUNC('day', ${users.createdAt})`)
.orderBy(sql`DATE_TRUNC('day', ${users.createdAt})`),
db
.select({
day: sql`DATE_TRUNC('day', ${specialistCards.createdAt})::date::text`,
count: countDistinct(specialistCards.specialistId),
})
.from(specialistCards)
.where(gte(specialistCards.createdAt, thirtyDaysAgo))
.groupBy(sql`DATE_TRUNC('day', ${specialistCards.createdAt})`)
.orderBy(sql`DATE_TRUNC('day', ${specialistCards.createdAt})`),
db
.select({
totalInput: sql`COALESCE(SUM(${aiLogs.inputTokens}), 0)`,
totalOutput: sql`COALESCE(SUM(${aiLogs.outputTokens}), 0)`,
totalCost: sql`COALESCE(SUM(${aiLogs.costUsd}), 0)`,
})
.from(aiLogs),
db
.select({
month: sql`DATE_TRUNC('month', ${aiLogs.createdAt})::date::text`,
input: sql`COALESCE(SUM(${aiLogs.inputTokens}), 0)`,
output: sql`COALESCE(SUM(${aiLogs.outputTokens}), 0)`,
})
.from(aiLogs)
.groupBy(sql`DATE_TRUNC('month', ${aiLogs.createdAt})`)
.orderBy(sql`DATE_TRUNC('month', ${aiLogs.createdAt})`),
])
return c.json({
userCount,
specialistCount,
taskCount,
offerCount,
reviewCount,
tasksByStatus,
tasksByDay,
usersByDay,
specialistsByDay,
totalInputTokens: Number(tokenTotals[0]?.totalInput ?? 0),
totalOutputTokens: Number(tokenTotals[0]?.totalOutput ?? 0),
totalCostUsd: Number(tokenTotals[0]?.totalCost ?? 0),
tokensByMonth,
})
})
// GET /admin/users?page=&q=&role=
app.get('/users', requireAdmin, async (c) => {
const page = Number(c.req.query('page') || 1)
const limit = Math.min(Number(c.req.query('limit') || 20), 100)
const offset = (page - 1) * limit
const q = c.req.query('q')
const role = c.req.query('role')
const conditions = []
if (q) conditions.push(or(ilike(users.name, `%${q}%`), ilike(users.email, `%${q}%`)))
if (role && ['customer', 'specialist', 'admin'].includes(role))
conditions.push(eq(users.role, role as 'customer' | 'specialist' | 'admin'))
const where = conditions.length > 0 ? and(...conditions) : undefined
const [rows, [{ total }]] = await Promise.all([
db.select().from(users).where(where).orderBy(desc(users.createdAt)).limit(limit).offset(offset),
db.select({ total: count() }).from(users).where(where),
])
return c.json({ data: rows, total, page, limit })
})
// GET /admin/users/:id — full single user record
app.get('/users/:id', requireAdmin, async (c) => {
const [user] = await db
.select()
.from(users)
.where(eq(users.id, c.req.param('id')))
.limit(1)
if (!user) return c.json({ error: 'Not found' }, 404)
return c.json(user)
})
// GET /admin/users/:id/specialist-cards — all cards for a user (incl. inactive)
app.get('/users/:id/specialist-cards', requireAdmin, async (c) => {
const cards = await db
.select()
.from(specialistCards)
.where(eq(specialistCards.specialistId, c.req.param('id')))
.orderBy(asc(specialistCards.order))
return c.json(cards)
})
// GET /admin/specialist-cards — specialist cards for moderation
app.get('/specialist-cards', requireAdmin, async (c) => {
const page = Number(c.req.query('page') || 1)
const limit = Math.min(Number(c.req.query('limit') || 20), 100)
const offset = (page - 1) * limit
const status = c.req.query('status')
const whereClause =
status === 'active'
? eq(specialistCards.publicationStatus, 'active')
: status === 'pending'
? eq(specialistCards.publicationStatus, 'pending')
: status === 'review'
? sql`${specialistCards.publicationStatus} IN ('pending', 'active')`
: undefined
const baseQuery = db
.select({
id: specialistCards.id,
specialistId: specialistCards.specialistId,
title: specialistCards.title,
description: specialistCards.description,
skills: specialistCards.skills,
categories: specialistCards.categories,
locations: specialistCards.locations,
publicationStatus: specialistCards.publicationStatus,
isActive: specialistCards.isActive,
order: specialistCards.order,
createdAt: specialistCards.createdAt,
updatedAt: specialistCards.updatedAt,
})
.from(specialistCards)
const rows = await (whereClause ? baseQuery.where(whereClause) : baseQuery)
.orderBy(desc(specialistCards.createdAt))
.limit(limit)
.offset(offset)
const totalQuery = db.select({ total: count() }).from(specialistCards)
const [{ total }] = await (whereClause ? totalQuery.where(whereClause) : totalQuery)
const specialistIds = [...new Set(rows.map((row) => row.specialistId))]
const specialists = specialistIds.length > 0
? await db.select({ id: users.id, name: users.name, email: users.email }).from(users).where(inArray(users.id, specialistIds))
: []
const specialistMap = Object.fromEntries(specialists.map((u) => [u.id, u]))
const pendingSuggestions = rows.length === 0
? []
: await db.select().from(categorySuggestions).where(and(
inArray(categorySuggestions.cardId, rows.map((row) => row.id)),
eq(categorySuggestions.status, 'pending'),
))
const suggestionsByCard: Record = {}
for (const suggestion of pendingSuggestions) {
(suggestionsByCard[suggestion.cardId] ??= []).push(suggestion)
}
const data = rows.map((row) => ({
...row,
specialistName: specialistMap[row.specialistId]?.name ?? null,
specialistEmail: specialistMap[row.specialistId]?.email ?? null,
categorySuggestions: suggestionsByCard[row.id] ?? [],
}))
return c.json({ data, total, page, limit })
})
// PATCH /admin/specialist-cards/:id — admin update of a specialist card
app.patch('/specialist-cards/:id', requireAdmin, async (c) => {
const admin = c.get('user')
const cardId = c.req.param('id')
const body = await c.req.json<{
title?: string; description?: string | null
skills?: string[]; categories?: string[]; locations?: string[]
publicationStatus?: 'pending' | 'active' | 'inactive'
isActive?: boolean; order?: number
adminComment?: string
}>()
const [card] = await db.select().from(specialistCards).where(eq(specialistCards.id, cardId)).limit(1)
if (!card) return c.json({ error: 'Not found' }, 404)
const [specialist] = await db.select({ id: users.id, name: users.name, email: users.email }).from(users).where(eq(users.id, card.specialistId)).limit(1)
const updates: Record = { updatedAt: new Date() }
if (body.title !== undefined) updates.title = body.title
if (body.description !== undefined) updates.description = body.description ?? null
if (body.skills !== undefined) updates.skills = body.skills
if (body.categories !== undefined) updates.categories = body.categories
if (body.locations !== undefined) updates.locations = body.locations
if (body.publicationStatus !== undefined) {
updates.publicationStatus = body.publicationStatus
updates.isActive = body.publicationStatus === 'active'
}
if (body.isActive !== undefined) updates.isActive = body.isActive
if (body.isActive !== undefined && body.publicationStatus === undefined) {
updates.publicationStatus = body.isActive ? 'active' : 'inactive'
}
if (body.order !== undefined) updates.order = body.order
const [updated] = await db.update(specialistCards).set(updates).where(eq(specialistCards.id, cardId)).returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
if (specialist && updated.publicationStatus !== card.publicationStatus) {
if (updated.publicationStatus === 'active') {
notifySpecialistCardApproved(specialist.id, updated.title, updated.id).catch((err) => {
console.error('[admin] specialist card approved notify error:', err)
})
} else if (updated.publicationStatus === 'inactive') {
notifySpecialistCardRejected(specialist.id, updated.title, updated.id, body.adminComment?.trim() || undefined).catch((err) => {
console.error('[admin] specialist card rejected notify error:', err)
})
}
}
logActivity({ userId: admin?.id, event: 'admin.specialist_card.updated', details: { cardId, specialistId: card.specialistId, adminComment: body.adminComment?.trim() || undefined } })
return c.json(updated)
})
app.patch('/specialist-cards/:cardId/category-suggestions/:suggestionId', requireAdmin, async (c) => {
const admin = c.get('user')
const { cardId, suggestionId } = c.req.param()
const body = await c.req.json<{
action: 'map' | 'create' | 'reject'
categoryId?: string
slug?: string
parentId?: string | null
icon?: string
}>()
if (!['map', 'create', 'reject'].includes(body.action)) return c.json({ error: 'Invalid action' }, 400)
const [suggestion] = await db.select().from(categorySuggestions).where(and(
eq(categorySuggestions.id, suggestionId),
eq(categorySuggestions.cardId, cardId),
eq(categorySuggestions.status, 'pending'),
))
if (!suggestion) return c.json({ error: 'Suggestion not found' }, 404)
let category: typeof categories.$inferSelect | undefined
if (body.action === 'map') {
if (!body.categoryId) return c.json({ error: 'Category is required' }, 400)
;[category] = await db.select().from(categories).where(eq(categories.id, body.categoryId))
if (!category) return c.json({ error: 'Category not found' }, 404)
} else if (body.action === 'create') {
const slug = (body.slug ?? suggestion.name)
.toLowerCase()
.trim()
.replace(/[^a-z0-9]+/g, '-')
.replace(/^-|-$/g, '')
if (!slug) return c.json({ error: 'A Latin slug is required' }, 400)
const [duplicate] = await db.select({ id: categories.id }).from(categories).where(eq(categories.slug, slug))
if (duplicate) return c.json({ error: 'Category slug already exists' }, 409)
;[category] = await db.insert(categories).values({
slug,
icon: body.icon ?? '📦',
parentId: body.parentId ?? null,
namesEl: suggestion.name,
namesEn: suggestion.name,
namesRu: suggestion.name,
namesUk: suggestion.name,
}).returning()
await redis.del(CATEGORIES_CACHE_KEY).catch(() => {})
}
const [card] = await db.select().from(specialistCards).where(eq(specialistCards.id, cardId))
if (!card) return c.json({ error: 'Card not found' }, 404)
if (category) {
await db.update(specialistCards).set({
categories: [...new Set([...(card.categories ?? []), category.slug])],
updatedAt: new Date(),
}).where(eq(specialistCards.id, cardId))
}
const [updated] = await db.update(categorySuggestions).set({
status: body.action === 'map' ? 'mapped' : body.action === 'create' ? 'created' : 'rejected',
mappedCategoryId: category?.id ?? null,
reviewedByAdminId: admin.id,
updatedAt: new Date(),
}).where(eq(categorySuggestions.id, suggestionId)).returning()
logActivity({ userId: admin.id, event: 'admin.category_suggestion.resolved', details: { cardId, suggestionId, action: body.action } })
return c.json(updated)
})
// PATCH /admin/users/:id/deactivate
app.patch('/users/:id/deactivate', requireAdmin, async (c) => {
const admin = c.get('user')
const [updated] = await db
.update(users)
.set({ isActive: false, updatedAt: new Date() })
.where(eq(users.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.user.deactivate', details: { targetUserId: updated.id, targetEmail: updated.email } })
return c.json(updated)
})
// PATCH /admin/users/:id/verify-email
app.patch('/users/:id/verify-email', requireAdmin, async (c) => {
const admin = c.get('user')
const [updated] = await db
.update(users)
.set({ emailVerified: true, updatedAt: new Date() })
.where(eq(users.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.user.email_verified', details: { targetUserId: updated.id, targetEmail: updated.email } })
return c.json(updated)
})
// PATCH /admin/users/:id/activate
app.patch('/users/:id/activate', requireAdmin, async (c) => {
const admin = c.get('user')
const [updated] = await db
.update(users)
.set({ isActive: true, updatedAt: new Date() })
.where(eq(users.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.user.activate', details: { targetUserId: updated.id, targetEmail: updated.email } })
return c.json(updated)
})
// PATCH /admin/users/:id/role
app.patch('/users/:id/role', requireAdmin, async (c) => {
const admin = c.get('user')
const { role } = await c.req.json<{ role: 'customer' | 'specialist' | 'admin' }>()
if (!['customer', 'specialist', 'admin'].includes(role))
return c.json({ error: 'Invalid role' }, 400)
const [updated] = await db
.update(users)
.set({ role, updatedAt: new Date() })
.where(eq(users.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.user.role_changed', details: { targetUserId: updated.id, targetEmail: updated.email, newRole: role } })
return c.json(updated)
})
// PATCH /admin/users/:id — general profile update
app.patch('/users/:id', requireAdmin, async (c) => {
const admin = c.get('user')
const body = await c.req.json<{
firstName?: string
lastName?: string
phone?: string | null
bio?: string | null
role?: 'customer' | 'specialist' | 'admin'
planId?: string | null
isActive?: boolean
locale?: string
languages?: string[]
personalSiteSlug?: string | null
showContactInfo?: boolean
notifyNewTasks?: boolean
notifMessages?: boolean
skills?: string[]
specialistCategories?: string[]
specialistLocations?: string[]
siteSettings?: Record
}>()
const targetUserId = c.req.param('id')
const [currentUser] = await db
.select({ id: users.id, email: users.email })
.from(users)
.where(eq(users.id, targetUserId))
.limit(1)
if (!currentUser) return c.json({ error: 'Not found' }, 404)
const changedFields = Object.keys(body)
const updated = await db.transaction(async (tx) => {
const updates: Record = { updatedAt: new Date() }
if (body.firstName !== undefined) updates.firstName = body.firstName
if (body.lastName !== undefined) updates.lastName = body.lastName
if (body.phone !== undefined) updates.phone = body.phone ?? null
if (body.bio !== undefined) updates.bio = body.bio ?? null
if (body.role !== undefined && ['customer', 'specialist', 'admin'].includes(body.role))
updates.role = body.role
if (body.planId !== undefined) updates.planId = body.planId ?? null
if (body.isActive !== undefined) updates.isActive = body.isActive
if (body.locale !== undefined) updates.locale = body.locale
if (body.languages !== undefined) updates.languages = body.languages
if (body.personalSiteSlug !== undefined) updates.personalSiteSlug = body.personalSiteSlug ?? null
if (body.showContactInfo !== undefined) updates.showContactInfo = body.showContactInfo
if (body.notifyNewTasks !== undefined) updates.notifyNewTasks = body.notifyNewTasks
if (body.notifMessages !== undefined) updates.notifMessages = body.notifMessages
if (body.skills !== undefined) updates.skills = body.skills
if (body.specialistCategories !== undefined) updates.specialistCategories = body.specialistCategories
if (body.specialistLocations !== undefined) updates.specialistLocations = body.specialistLocations
if (body.siteSettings !== undefined) updates.siteSettings = body.siteSettings
const [nextUser] = await tx
.update(users)
.set(updates)
.where(eq(users.id, targetUserId))
.returning()
if (!nextUser) return null
return nextUser
})
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.user.updated', details: { targetUserId: updated.id, targetEmail: updated.email, changes: changedFields } })
return c.json(updated)
})
app.post('/users/:id/balance/topup', requireAdmin, async (c) => {
const admin = c.get('user')
const targetUserId = c.req.param('id')
const body: { amount?: number | string; description?: string } = await c.req
.json<{ amount?: number | string; description?: string }>()
.catch(() => ({}))
const amount = Number(body.amount)
if (!Number.isFinite(amount) || amount <= 0) {
return c.json({ error: 'Amount must be greater than 0' }, 400)
}
const [targetUser] = await db
.select({ id: users.id, email: users.email })
.from(users)
.where(eq(users.id, targetUserId))
.limit(1)
if (!targetUser) return c.json({ error: 'Not found' }, 404)
const result = await db.transaction(async (tx) => {
const balanceChange = await applyBalanceChange(tx, {
userId: targetUserId,
amount,
direction: 'credit',
kind: 'admin_topup',
currency: 'EUR',
adminUserId: admin?.id ?? null,
description: body.description?.trim() || 'Admin top-up',
metadata: {
source: 'admin-panel',
},
})
const [updatedUser] = await tx
.select()
.from(users)
.where(eq(users.id, targetUserId))
.limit(1)
return { updatedUser, transaction: balanceChange.transaction }
})
logActivity({
userId: admin?.id,
event: 'admin.user.balance_topped_up',
details: {
targetUserId,
targetEmail: targetUser.email,
amount: amount.toFixed(2),
description: body.description?.trim() || null,
},
})
return c.json({ user: result.updatedUser, transaction: result.transaction })
})
app.post('/users/:id/balance/withdraw', requireAdmin, async (c) => {
const admin = c.get('user')
const targetUserId = c.req.param('id')
const body: { amount?: number | string; description?: string } = await c.req
.json<{ amount?: number | string; description?: string }>()
.catch(() => ({}))
const amount = Number(body.amount)
if (!Number.isFinite(amount) || amount <= 0) {
return c.json({ error: 'Amount must be greater than 0' }, 400)
}
const [targetUser] = await db
.select({ id: users.id, email: users.email })
.from(users)
.where(eq(users.id, targetUserId))
.limit(1)
if (!targetUser) return c.json({ error: 'Not found' }, 404)
try {
const result = await db.transaction(async (tx) => {
const balanceChange = await applyBalanceChange(tx, {
userId: targetUserId,
amount,
direction: 'debit',
kind: 'admin_adjustment',
currency: 'EUR',
adminUserId: admin?.id ?? null,
description: body.description?.trim() || 'Admin withdrawal',
metadata: {
source: 'admin-panel',
action: 'withdraw',
},
})
const [updatedUser] = await tx
.select()
.from(users)
.where(eq(users.id, targetUserId))
.limit(1)
return { updatedUser, transaction: balanceChange.transaction }
})
logActivity({
userId: admin?.id,
event: 'admin.user.balance_withdrawn',
details: {
targetUserId,
targetEmail: targetUser.email,
amount: amount.toFixed(2),
description: body.description?.trim() || null,
},
})
return c.json({ user: result.updatedUser, transaction: result.transaction })
} catch (error) {
const message = error instanceof Error ? error.message : 'Failed to withdraw balance'
return c.json({ error: message }, message === 'Insufficient balance' ? 400 : 500)
}
})
app.get('/balance-history', requireAdmin, async (c) => {
const page = Math.max(1, Number(c.req.query('page') || 1))
const limit = Math.min(Number(c.req.query('limit') || 50), 200)
const offset = (page - 1) * limit
const userId = c.req.query('userId')
const kind = c.req.query('kind')
const conditions = []
if (userId) conditions.push(eq(balanceTransactions.userId, userId))
if (kind && ['topup', 'plan_purchase', 'admin_topup', 'admin_adjustment'].includes(kind)) {
conditions.push(eq(balanceTransactions.kind, kind as any))
}
const where = conditions.length > 0 ? and(...conditions) : undefined
const [rows, [{ total }]] = await Promise.all([
db
.select({
id: balanceTransactions.id,
kind: balanceTransactions.kind,
direction: balanceTransactions.direction,
amount: balanceTransactions.amount,
balanceBefore: balanceTransactions.balanceBefore,
balanceAfter: balanceTransactions.balanceAfter,
currency: balanceTransactions.currency,
description: balanceTransactions.description,
metadata: balanceTransactions.metadata,
createdAt: balanceTransactions.createdAt,
userId: balanceTransactions.userId,
adminUserId: balanceTransactions.adminUserId,
paymentId: balanceTransactions.paymentId,
userEmail: users.email,
userName: users.name,
})
.from(balanceTransactions)
.leftJoin(users, eq(balanceTransactions.userId, users.id))
.where(where)
.orderBy(desc(balanceTransactions.createdAt))
.limit(limit)
.offset(offset),
db.select({ total: count() }).from(balanceTransactions).where(where),
])
return c.json({ data: rows, total, page, limit })
})
// POST /admin/push/user/:id
app.post('/push/user/:id', requireAdmin, async (c) => {
const admin = c.get('user')
const targetUserId = c.req.param('id')
type AdminPushBody = {
title?: string
body?: string | null
type?: string
referenceId?: string
data?: Record
}
const body: AdminPushBody = await c.req
.json()
.catch(() => ({} as AdminPushBody))
const title = body.title?.trim()
if (!title) return c.json({ error: 'title is required' }, 400)
const [targetUser] = await db
.select({ id: users.id, email: users.email })
.from(users)
.where(eq(users.id, targetUserId))
.limit(1)
if (!targetUser) return c.json({ error: 'Not found' }, 404)
const data: Record = { ...(body.data ?? {}) }
if (body.type?.trim()) data.type = body.type.trim()
if (body.referenceId?.trim()) data.referenceId = body.referenceId.trim()
const result = await sendPushToUser(targetUserId, {
title,
body: body.body?.trim() || undefined,
data,
})
logActivity({
userId: admin?.id,
event: 'admin.push.user',
details: {
targetUserId,
targetEmail: targetUser.email,
title,
type: data.type ?? null,
referenceId: data.referenceId ?? null,
sent: result.sent,
invalidTokens: result.invalidTokens.length,
tokensFound: result.tokensFound,
reason: result.reason ?? null,
failureKey: result.failureKey ?? null,
failureMessage: result.failureMessage ?? null,
},
})
return c.json({
ok: true,
targetUserId,
sent: result.sent,
invalidTokens: result.invalidTokens.length,
tokensFound: result.tokensFound,
reason: result.reason ?? null,
failureKey: result.failureKey ?? null,
failureMessage: result.failureMessage ?? null,
})
})
// POST /admin/push/broadcast
app.post('/push/broadcast', requireAdmin, async (c) => {
const admin = c.get('user')
type AdminBroadcastBody = {
title?: string
body?: string | null
type?: string
referenceId?: string
data?: Record
onlyActive?: boolean
}
const body: AdminBroadcastBody = await c.req
.json()
.catch(() => ({} as AdminBroadcastBody))
const title = body.title?.trim()
if (!title) return c.json({ error: 'title is required' }, 400)
const data: Record = { ...(body.data ?? {}) }
if (body.type?.trim()) data.type = body.type.trim()
if (body.referenceId?.trim()) data.referenceId = body.referenceId.trim()
const tokenUsersRows = await db
.select({ userId: pushTokens.userId })
.from(pushTokens)
.groupBy(pushTokens.userId)
const tokenUserIds = tokenUsersRows.map((r) => r.userId)
if (tokenUserIds.length === 0) {
return c.json({
ok: true,
usersTotal: 0,
usersWithSent: 0,
pushesSent: 0,
invalidTokens: 0,
failedUsers: [],
})
}
let targetUserIds = tokenUserIds
if (body.onlyActive !== false) {
const activeRows = await db
.select({ id: users.id })
.from(users)
.where(and(inArray(users.id, tokenUserIds), eq(users.isActive, true)))
targetUserIds = activeRows.map((r) => r.id)
}
let pushesSent = 0
let usersWithSent = 0
let invalidTokens = 0
let usersWithoutTokens = 0
let usersConfigBlocked = 0
let usersAuthFailed = 0
let usersAllFailed = 0
const failedReasonStats: Record = {}
let failedReasonSample: string | null = null
const failedUsers: string[] = []
for (const userId of targetUserIds) {
try {
const result = await sendPushToUser(userId, {
title,
body: body.body?.trim() || undefined,
data,
})
pushesSent += result.sent
invalidTokens += result.invalidTokens.length
if (result.sent > 0) usersWithSent += 1
if (result.reason === 'no_tokens') usersWithoutTokens += 1
if (result.reason === 'fcm_not_configured') usersConfigBlocked += 1
if (result.reason === 'fcm_auth_failed') usersAuthFailed += 1
if (result.reason === 'fcm_send_failed') {
usersAllFailed += 1
const key = result.failureKey ?? 'UNKNOWN_FCM_SEND_FAILURE'
failedReasonStats[key] = (failedReasonStats[key] ?? 0) + 1
if (!failedReasonSample && result.failureMessage) {
failedReasonSample = result.failureMessage
}
}
} catch {
failedUsers.push(userId)
}
}
logActivity({
userId: admin?.id,
event: 'admin.push.broadcast',
details: {
title,
type: data.type ?? null,
referenceId: data.referenceId ?? null,
onlyActive: body.onlyActive !== false,
usersTotal: targetUserIds.length,
usersWithSent,
pushesSent,
invalidTokens,
usersWithoutTokens,
usersConfigBlocked,
usersAuthFailed,
usersAllFailed,
failedReasonStats,
failedReasonSample,
failedUsers: failedUsers.length,
},
})
return c.json({
ok: true,
usersTotal: targetUserIds.length,
usersWithSent,
pushesSent,
invalidTokens,
usersWithoutTokens,
usersConfigBlocked,
usersAuthFailed,
usersAllFailed,
failedReasonStats,
failedReasonSample,
failedUsers,
})
})
// GET /admin/tasks?page=&q=&status=
app.get('/tasks', requireAdmin, async (c) => {
const page = Number(c.req.query('page') || 1)
const limit = Math.min(Number(c.req.query('limit') || 20), 100)
const offset = (page - 1) * limit
const q = c.req.query('q')
const status = c.req.query('status')
const conditions = []
if (q) conditions.push(ilike(tasks.title, `%${q}%`))
if (status && ['draft', 'open', 'in_progress', 'completed', 'cancelled'].includes(status))
conditions.push(eq(tasks.status, status as any))
const where = conditions.length > 0 ? and(...conditions) : undefined
const [rows, [{ total }]] = await Promise.all([
db
.select({ task: tasks, customer: { id: users.id, name: users.name, email: users.email } })
.from(tasks)
.leftJoin(users, eq(tasks.customerId, users.id))
.where(where)
.orderBy(desc(tasks.createdAt))
.limit(limit)
.offset(offset),
db.select({ total: count() }).from(tasks).where(where),
])
return c.json({ data: rows, total, page, limit })
})
// PATCH /admin/tasks/:id/cancel
app.patch('/tasks/:id/cancel', requireAdmin, async (c) => {
const admin = c.get('user')
const [updated] = await db
.update(tasks)
.set({ status: 'cancelled', updatedAt: new Date() })
.where(eq(tasks.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.task.cancelled', details: { taskId: updated.id, taskTitle: updated.title } })
return c.json({ task: updated })
})
// DELETE /admin/tasks/:id
app.delete('/tasks/:id', requireAdmin, async (c) => {
const admin = c.get('user')
const [deleted] = await db
.delete(tasks)
.where(eq(tasks.id, c.req.param('id')))
.returning()
if (!deleted) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: 'admin.task.deleted', details: { taskId: deleted.id, taskTitle: deleted.title } })
return c.json({ ok: true })
})
// GET /admin/reports
app.get('/reports', requireAdmin, async (c) => {
const page = Number(c.req.query('page') || 1)
const limit = Math.min(Number(c.req.query('limit') || 20), 100)
const offset = (page - 1) * limit
const status = c.req.query('status')
const whereClause = status ? eq(reports.status, status as any) : undefined
const rowsQuery = db.select().from(reports)
const totalQuery = db.select({ total: count() }).from(reports)
const [rows, [{ total }]] = await Promise.all([
(whereClause ? rowsQuery.where(whereClause) : rowsQuery).orderBy(desc(reports.createdAt)).limit(limit).offset(offset),
whereClause ? totalQuery.where(whereClause) : totalQuery,
])
if (rows.length === 0) return c.json({ data: [], total, page, limit })
// Batch-fetch reporters and targets
const reporterIds = [...new Set(rows.map((r) => r.reporterId))]
const taskTargetIds = [...new Set(rows.filter((r) => r.targetType === 'task').map((r) => r.targetId))]
const userTargetIds = [...new Set(rows.filter((r) => r.targetType === 'user').map((r) => r.targetId))]
const [reporters, targetTasks, targetUsers] = await Promise.all([
db.select({ id: users.id, name: users.name }).from(users).where(inArray(users.id, reporterIds)),
taskTargetIds.length > 0
? db.select({ id: tasks.id, title: tasks.title }).from(tasks).where(inArray(tasks.id, taskTargetIds))
: Promise.resolve([]),
userTargetIds.length > 0
? db.select({ id: users.id, name: users.name }).from(users).where(inArray(users.id, userTargetIds))
: Promise.resolve([]),
])
const reporterMap = Object.fromEntries(reporters.map((u) => [u.id, u.name]))
const taskMap = Object.fromEntries(targetTasks.map((t) => [t.id, t.title]))
const userMap = Object.fromEntries(targetUsers.map((u) => [u.id, u.name]))
const enriched = rows.map((r) => ({
...r,
reporterName: reporterMap[r.reporterId] ?? null,
targetTitle: r.targetType === 'task' ? (taskMap[r.targetId] ?? null) : (userMap[r.targetId] ?? null),
}))
return c.json({ data: enriched, total, page, limit })
})
// PATCH /admin/reports/:id/status
app.patch('/reports/:id/status', requireAdmin, async (c) => {
const admin = c.get('user')
const { status } = await c.req.json<{ status: 'reviewed' | 'dismissed' }>()
const [updated] = await db
.update(reports)
.set({ status })
.where(eq(reports.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
logActivity({ userId: admin?.id, event: `admin.report.${status}`, details: { reportId: updated.id, reason: updated.reason } })
return c.json(updated)
})
// GET /admin/activity-logs
app.get('/activity-logs', requireAdmin, async (c) => {
const page = Math.max(1, Number(c.req.query('page') || 1))
const limit = Math.min(Number(c.req.query('limit') || 50), 200)
const offset = (page - 1) * limit
const event = c.req.query('event')
const userId = c.req.query('userId')
const dateFrom = c.req.query('dateFrom')
const dateTo = c.req.query('dateTo')
const conditions = []
if (event) conditions.push(eq(activityLogs.event, event))
if (userId) conditions.push(eq(activityLogs.userId, userId))
if (dateFrom) conditions.push(gte(activityLogs.createdAt, new Date(dateFrom)))
if (dateTo) {
const to = new Date(dateTo)
to.setHours(23, 59, 59, 999)
conditions.push(lte(activityLogs.createdAt, to))
}
const where = conditions.length > 0 ? and(...conditions) : undefined
const [rows, [{ total }]] = await Promise.all([
db
.select()
.from(activityLogs)
.where(where)
.orderBy(desc(activityLogs.createdAt))
.limit(limit)
.offset(offset),
db.select({ total: count() }).from(activityLogs).where(where),
])
// Return distinct event types for filter dropdown
const eventTypes = await db
.selectDistinct({ event: activityLogs.event })
.from(activityLogs)
.orderBy(asc(activityLogs.event))
return c.json({ data: rows, total, page, limit, eventTypes: eventTypes.map((r) => r.event) })
})
// GET /admin/mail-logs
app.get('/mail-logs', requireAdmin, async (c) => {
const page = Math.max(1, Number(c.req.query('page') || 1))
const limit = Math.min(Number(c.req.query('limit') || 50), 200)
const offset = (page - 1) * limit
const event = c.req.query('event')
const dateFrom = c.req.query('dateFrom')
const dateTo = c.req.query('dateTo')
const mailBaseCondition = ilike(activityLogs.event, 'email.%')
const conditions: any[] = [mailBaseCondition]
if (event) conditions.push(eq(activityLogs.event, event))
if (dateFrom) conditions.push(gte(activityLogs.createdAt, new Date(dateFrom)))
if (dateTo) {
const to = new Date(dateTo)
to.setHours(23, 59, 59, 999)
conditions.push(lte(activityLogs.createdAt, to))
}
const where = and(...conditions)
const [rows, [{ total }], eventTypes] = await Promise.all([
db
.select()
.from(activityLogs)
.where(where)
.orderBy(desc(activityLogs.createdAt))
.limit(limit)
.offset(offset),
db.select({ total: count() }).from(activityLogs).where(where),
db
.selectDistinct({ event: activityLogs.event })
.from(activityLogs)
.where(mailBaseCondition)
.orderBy(asc(activityLogs.event)),
])
return c.json({ data: rows, total, page, limit, eventTypes: eventTypes.map((r) => r.event) })
})
// ─── Categories CRUD ───────────────────────────────────────────────────────
// GET /admin/categories — flat list with all (including inactive)
app.get('/categories', requireAdmin, async (c) => {
const rows = await db
.select()
.from(categories)
.orderBy(asc(categories.order), asc(categories.namesEl))
return c.json(rows)
})
// POST /admin/categories
app.post('/categories', requireAdmin, async (c) => {
const body = await c.req.json<{
slug: string
icon?: string
namesEl: string
namesEn: string
namesRu: string
namesUk?: string
parentId?: string | null
order?: number
}>()
const [cat] = await db
.insert(categories)
.values({
slug: body.slug,
icon: body.icon ?? '📦',
namesEl: body.namesEl,
namesEn: body.namesEn,
namesRu: body.namesRu,
namesUk: body.namesUk ?? null,
parentId: body.parentId ?? null,
order: body.order ?? 0,
})
.returning()
await redis.del(CATEGORIES_CACHE_KEY).catch(() => {})
return c.json(cat, 201)
})
// PATCH /admin/categories/:id
app.patch('/categories/:id', requireAdmin, async (c) => {
const body = await c.req.json<{
slug?: string
icon?: string
namesEl?: string
namesEn?: string
namesRu?: string
namesUk?: string
parentId?: string | null
order?: number
isActive?: boolean
}>()
const updates: Partial = { updatedAt: new Date() }
if (body.slug !== undefined) updates.slug = body.slug
if (body.icon !== undefined) updates.icon = body.icon
if (body.namesEl !== undefined) updates.namesEl = body.namesEl
if (body.namesEn !== undefined) updates.namesEn = body.namesEn
if (body.namesRu !== undefined) updates.namesRu = body.namesRu
if (body.namesUk !== undefined) updates.namesUk = body.namesUk
if (body.parentId !== undefined) updates.parentId = body.parentId
if (body.order !== undefined) updates.order = body.order
if (body.isActive !== undefined) updates.isActive = body.isActive
const [updated] = await db
.update(categories)
.set(updates)
.where(eq(categories.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
await redis.del(CATEGORIES_CACHE_KEY).catch(() => {})
return c.json(updated)
})
// DELETE /admin/categories/:id
app.delete('/categories/:id', requireAdmin, async (c) => {
const [deleted] = await db
.delete(categories)
.where(eq(categories.id, c.req.param('id')))
.returning()
if (!deleted) return c.json({ error: 'Not found' }, 404)
await redis.del(CATEGORIES_CACHE_KEY).catch(() => {})
return c.json({ ok: true })
})
// ─── Skills (skill_suggestions) CRUD ───────────────────────────────────────
// GET /admin/skills?categorySlug=xxx
app.get('/skills', requireAdmin, async (c) => {
const categorySlug = c.req.query('categorySlug')
const rows = await db
.select()
.from(skillSuggestions)
.where(categorySlug ? eq(skillSuggestions.categorySlug, categorySlug) : undefined)
.orderBy(asc(skillSuggestions.order), asc(skillSuggestions.nameEl))
return c.json(rows)
})
// POST /admin/skills
app.post('/skills', requireAdmin, async (c) => {
const body = await c.req.json<{
categorySlug: string
nameEl: string
nameEn: string
nameRu: string
nameUk?: string
order?: number
}>()
if (!body.categorySlug || !body.nameEl) return c.json({ error: 'categorySlug and nameEl required' }, 400)
const [row] = await db
.insert(skillSuggestions)
.values({
categorySlug: body.categorySlug,
nameEl: body.nameEl,
nameEn: body.nameEn || body.nameEl,
nameRu: body.nameRu || body.nameEl,
nameUk: body.nameUk || null,
order: body.order ?? 0,
})
.returning()
return c.json(row, 201)
})
// PATCH /admin/skills/:id
app.patch('/skills/:id', requireAdmin, async (c) => {
const body = await c.req.json<{
nameEl?: string
nameEn?: string
nameRu?: string
nameUk?: string
order?: number
categorySlug?: string
}>()
const updates: any = {}
if (body.nameEl !== undefined) updates.nameEl = body.nameEl
if (body.nameEn !== undefined) updates.nameEn = body.nameEn
if (body.nameRu !== undefined) updates.nameRu = body.nameRu
if (body.nameUk !== undefined) updates.nameUk = body.nameUk
if (body.order !== undefined) updates.order = body.order
if (body.categorySlug !== undefined) updates.categorySlug = body.categorySlug
if (Object.keys(updates).length === 0) return c.json({ error: 'Nothing to update' }, 400)
const [row] = await db
.update(skillSuggestions)
.set(updates)
.where(eq(skillSuggestions.id, c.req.param('id')))
.returning()
if (!row) return c.json({ error: 'Not found' }, 404)
return c.json(row)
})
// DELETE /admin/skills/:id
app.delete('/skills/:id', requireAdmin, async (c) => {
const [deleted] = await db
.delete(skillSuggestions)
.where(eq(skillSuggestions.id, c.req.param('id')))
.returning()
if (!deleted) return c.json({ error: 'Not found' }, 404)
return c.json({ ok: true })
})
// GET /admin/skills/dedup?categorySlug=xxx — returns groups of duplicate skill names (same normalized nameEl per category)
app.get('/skills/dedup', requireAdmin, async (c) => {
const categorySlug = c.req.query('categorySlug')
const rows = await db
.select()
.from(skillSuggestions)
.where(categorySlug ? eq(skillSuggestions.categorySlug, categorySlug) : undefined)
.orderBy(asc(skillSuggestions.order), asc(skillSuggestions.nameEl))
// Group by normalized (lowercase + trim) nameEl per category
const map = new Map()
for (const row of rows) {
const key = `${row.categorySlug}::${row.nameEl.toLowerCase().trim()}`
const g = map.get(key) ?? []
g.push(row)
map.set(key, g)
}
const duplicates: Array> = []
for (const [, group] of map) {
if (group.length < 2) continue
const withUsage = await Promise.all(group.map(async (s) => {
const [{ cardCount }] = await db
.select({ cardCount: count() })
.from(specialistCards)
.where(sql`${s.nameEl} = ANY(${specialistCards.skills})`)
const [{ userCount }] = await db
.select({ userCount: count() })
.from(users)
.where(sql`${s.nameEl} = ANY(${users.skills})`)
return { ...s, usageCount: Number(cardCount ?? 0) + Number(userCount ?? 0) }
}))
duplicates.push(withUsage)
}
return c.json(duplicates)
})
// POST /admin/skills/dedup/merge — keeps one skill, replaces name references and deletes the rest
app.post('/skills/dedup/merge', requireAdmin, async (c) => {
const { keepId, deleteIds } = await c.req.json<{ keepId: string; deleteIds: string[] }>()
if (!keepId || !deleteIds?.length) return c.json({ error: 'keepId and deleteIds required' }, 400)
const [keeper] = await db.select().from(skillSuggestions).where(eq(skillSuggestions.id, keepId))
if (!keeper) return c.json({ error: 'Keeper skill not found' }, 404)
const deletees = await db.select().from(skillSuggestions).where(inArray(skillSuggestions.id, deleteIds))
for (const deletee of deletees) {
if (deletee.nameEl !== keeper.nameEl) {
await db
.update(specialistCards)
.set({ skills: sql`array_replace(${specialistCards.skills}, ${deletee.nameEl}, ${keeper.nameEl})` })
.where(sql`${deletee.nameEl} = ANY(${specialistCards.skills})`)
await db
.update(users)
.set({ skills: sql`array_replace(${users.skills}, ${deletee.nameEl}, ${keeper.nameEl})` })
.where(sql`${deletee.nameEl} = ANY(${users.skills})`)
}
await db.delete(skillSuggestions).where(eq(skillSuggestions.id, deletee.id))
}
return c.json({ ok: true, merged: deleteIds.length })
})
// ─── Locations CRUD ────────────────────────────────────────────────────────
// GET /admin/locations
app.get('/locations', requireAdmin, async (c) => {
const rows = await db
.select()
.from(locations)
.orderBy(asc(locations.order), asc(locations.nameEl))
return c.json(rows)
})
// POST /admin/locations
app.post('/locations', requireAdmin, async (c) => {
const body = await c.req.json<{
slug: string
nameEl: string; nameEn: string; nameRu: string; nameUk?: string
parentId?: string | null
order?: number
}>()
const [loc] = await db
.insert(locations)
.values({
slug: body.slug,
nameEl: body.nameEl,
nameEn: body.nameEn,
nameRu: body.nameRu,
nameUk: body.nameUk ?? null,
parentId: body.parentId ?? null,
order: body.order ?? 0,
})
.returning()
await redis.del(LOCATIONS_CACHE_KEY).catch(() => {})
return c.json(loc, 201)
})
// PATCH /admin/locations/:id
app.patch('/locations/:id', requireAdmin, async (c) => {
const body = await c.req.json<{
slug?: string
nameEl?: string; nameEn?: string; nameRu?: string; nameUk?: string
parentId?: string | null
order?: number
isActive?: boolean
}>()
const updates: Partial = { updatedAt: new Date() }
if (body.slug !== undefined) updates.slug = body.slug
if (body.nameEl !== undefined) updates.nameEl = body.nameEl
if (body.nameEn !== undefined) updates.nameEn = body.nameEn
if (body.nameRu !== undefined) updates.nameRu = body.nameRu
if (body.nameUk !== undefined) updates.nameUk = body.nameUk
if (body.parentId !== undefined) updates.parentId = body.parentId
if (body.order !== undefined) updates.order = body.order
if (body.isActive !== undefined) updates.isActive = body.isActive
const [updated] = await db
.update(locations)
.set(updates)
.where(eq(locations.id, c.req.param('id')))
.returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
await redis.del(LOCATIONS_CACHE_KEY).catch(() => {})
return c.json(updated)
})
// DELETE /admin/locations/:id
app.delete('/locations/:id', requireAdmin, async (c) => {
const [deleted] = await db
.delete(locations)
.where(eq(locations.id, c.req.param('id')))
.returning()
if (!deleted) return c.json({ error: 'Not found' }, 404)
await redis.del(LOCATIONS_CACHE_KEY).catch(() => {})
return c.json({ ok: true })
})
// GET /admin/ai-logs
app.get('/ai-logs', requireAdmin, async (c) => {
const page = Number(c.req.query('page') || 1)
const limit = Math.min(Number(c.req.query('limit') || 50), 200)
const offset = (page - 1) * limit
const [rows, [{ total }]] = await Promise.all([
db.select().from(aiLogs).orderBy(desc(aiLogs.createdAt)).limit(limit).offset(offset),
db.select({ total: count() }).from(aiLogs),
])
return c.json({ data: rows, total, page, limit })
})
// GET /admin/notification-logs
app.get('/notification-logs', requireAdmin, async (c) => {
const page = Number(c.req.query('page') || 1)
const limit = Math.min(Number(c.req.query('limit') || 50), 200)
const offset = (page - 1) * limit
const type = c.req.query('type') || ''
const dateFrom = c.req.query('dateFrom') || ''
const dateTo = c.req.query('dateTo') || ''
const filters = [
type ? eq(notifications.type, type as any) : undefined,
dateFrom ? gte(notifications.createdAt, new Date(dateFrom)) : undefined,
dateTo ? lte(notifications.createdAt, new Date(dateTo + 'T23:59:59')) : undefined,
].filter(Boolean) as any[]
const where = filters.length ? and(...filters) : undefined
const [rows, [{ total }], types] = await Promise.all([
db.select({ n: notifications, recipientEmail: users.email, recipientName: users.name })
.from(notifications)
.leftJoin(users, eq(notifications.recipientId, users.id))
.where(where)
.orderBy(desc(notifications.createdAt))
.limit(limit)
.offset(offset),
db.select({ total: count() }).from(notifications).where(where),
db.selectDistinct({ type: notifications.type }).from(notifications),
])
return c.json({
data: rows.map((r) => ({ ...r.n, recipientEmail: r.recipientEmail, recipientName: r.recipientName })),
total,
page,
limit,
notifTypes: types.map((t) => t.type),
})
})
// GET /admin/notification-stats — per-day counts for chart (last 30 days)
app.get('/notification-stats', requireAdmin, async (c) => {
const rows = await db
.select({
day: sql`to_char(${notifications.createdAt}, 'YYYY-MM-DD')`,
count: sql`cast(count(*) as int)`,
})
.from(notifications)
.where(gte(notifications.createdAt, new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)))
.groupBy(sql`to_char(${notifications.createdAt}, 'YYYY-MM-DD')`)
.orderBy(sql`to_char(${notifications.createdAt}, 'YYYY-MM-DD')`)
return c.json(rows)
})
// GET /admin/mail-stats — per-day email activity counts for chart (last 30 days)
app.get('/mail-stats', requireAdmin, async (c) => {
const rows = await db
.select({
day: sql`to_char(${activityLogs.createdAt}, 'YYYY-MM-DD')`,
count: sql`cast(count(*) as int)`,
})
.from(activityLogs)
.where(and(
ilike(activityLogs.event, 'email.%'),
gte(activityLogs.createdAt, new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)),
))
.groupBy(sql`to_char(${activityLogs.createdAt}, 'YYYY-MM-DD')`)
.orderBy(sql`to_char(${activityLogs.createdAt}, 'YYYY-MM-DD')`)
return c.json(rows)
})
// ─── Plans CRUD ────────────────────────────────────────────────────────────
// GET /admin/plans
app.get('/plans', requireAdmin, async (c) => {
const rows = await db.select().from(plans).orderBy(asc(plans.order), asc(plans.name))
return c.json(rows)
})
// POST /admin/plans
app.post('/plans', requireAdmin, async (c) => {
const body = await c.req.json<{
name: string
description?: string
role?: string
tier?: string
price?: string
oldPrice?: string | null
currency?: string
maxTasks?: number | null
maxOffers?: number | null
maxCards?: number | null
maxMessagesPerDay?: number | null
maxOrdersPerDay?: number | null
maxSkills?: number | null
maxProfiles?: number | null
maxPortfolioItems?: number | null
durationDays?: number | null
notifyNewTasks?: boolean
searchBoost?: number
offersMultiplier?: string
canContactFreePlan?: boolean
canContactProPlan?: boolean
canContactAll?: boolean
canShowContactInfo?: boolean
canViewPhone?: boolean
canUploadVideo?: boolean
hasFavorites?: boolean
hasGoogleCalendar?: boolean
hasVerifiedBadge?: boolean
highlightedReviews?: boolean
hasPersonalSite?: boolean
hasCanHelpNowStatus?: boolean
hasNeedHelpStatus?: boolean
hasAutoResponse?: boolean
hasAutoMatch?: boolean
hasPriceList?: boolean
receiveInstantDispatchFree?: boolean
receiveInstantDispatchPro?: boolean
features?: string[]
isDefault?: boolean
isActive?: boolean
order?: number
}>()
// If marking as default — unset previous default
if (body.isDefault) {
await db.update(plans).set({ isDefault: false }).where(eq(plans.isDefault, true))
}
const [plan] = await db.insert(plans).values({
name: body.name,
description: body.description ?? null,
role: (body.role as any) ?? 'customer',
tier: (body.tier as any) ?? 'free',
price: body.price ?? '0',
oldPrice: body.oldPrice ?? null,
currency: body.currency ?? 'EUR',
maxTasks: body.maxTasks ?? null,
maxOffers: body.maxOffers ?? null,
maxCards: body.maxCards ?? null,
maxMessagesPerDay: body.maxMessagesPerDay ?? null,
maxOrdersPerDay: body.maxOrdersPerDay ?? null,
maxSkills: body.maxSkills ?? null,
maxProfiles: body.maxProfiles ?? null,
maxPortfolioItems: body.maxPortfolioItems ?? null,
durationDays: body.durationDays ?? null,
notifyNewTasks: body.notifyNewTasks ?? false,
searchBoost: body.searchBoost ?? 0,
offersMultiplier: body.offersMultiplier ?? '1.00',
canContactFreePlan: body.canContactFreePlan ?? false,
canContactProPlan: body.canContactProPlan ?? true,
canContactAll: body.canContactAll ?? false,
canShowContactInfo: body.canShowContactInfo ?? false,
canViewPhone: body.canViewPhone ?? false,
canUploadVideo: body.canUploadVideo ?? false,
hasFavorites: body.hasFavorites ?? false,
hasGoogleCalendar: body.hasGoogleCalendar ?? false,
hasVerifiedBadge: body.hasVerifiedBadge ?? false,
highlightedReviews: body.highlightedReviews ?? false,
hasPersonalSite: body.hasPersonalSite ?? false,
hasCanHelpNowStatus: body.hasCanHelpNowStatus ?? false,
hasNeedHelpStatus: body.hasNeedHelpStatus ?? false,
hasAutoResponse: body.hasAutoResponse ?? false,
hasAutoMatch: body.hasAutoMatch ?? false,
hasPriceList: body.hasPriceList ?? false,
receiveInstantDispatchFree: body.receiveInstantDispatchFree ?? false,
receiveInstantDispatchPro: body.receiveInstantDispatchPro ?? false,
features: body.features ?? [],
isDefault: body.isDefault ?? false,
isActive: body.isActive ?? true,
order: body.order ?? 0,
}).returning()
return c.json(plan, 201)
})
// PATCH /admin/plans/:id
app.patch('/plans/:id', requireAdmin, async (c) => {
const body = await c.req.json<{
name?: string
description?: string
role?: string
tier?: string
price?: string
oldPrice?: string | null
currency?: string
maxTasks?: number | null
maxOffers?: number | null
maxCards?: number | null
maxMessagesPerDay?: number | null
maxOrdersPerDay?: number | null
maxSkills?: number | null
maxProfiles?: number | null
maxPortfolioItems?: number | null
durationDays?: number | null
notifyNewTasks?: boolean
searchBoost?: number
offersMultiplier?: string
canContactFreePlan?: boolean
canContactProPlan?: boolean
canContactAll?: boolean
canShowContactInfo?: boolean
canViewPhone?: boolean
canUploadVideo?: boolean
hasFavorites?: boolean
hasGoogleCalendar?: boolean
hasVerifiedBadge?: boolean
highlightedReviews?: boolean
hasPersonalSite?: boolean
hasCanHelpNowStatus?: boolean
hasNeedHelpStatus?: boolean
hasAutoResponse?: boolean
hasAutoMatch?: boolean
hasPriceList?: boolean
receiveInstantDispatchFree?: boolean
receiveInstantDispatchPro?: boolean
features?: string[]
isDefault?: boolean
isActive?: boolean
order?: number
}>()
if (body.isDefault) {
await db.update(plans).set({ isDefault: false }).where(eq(plans.isDefault, true))
}
const updates: Partial = { updatedAt: new Date() }
if (body.name !== undefined) updates.name = body.name
if (body.description !== undefined) updates.description = body.description
if (body.role !== undefined) updates.role = body.role as any
if (body.tier !== undefined) updates.tier = body.tier as any
if (body.price !== undefined) updates.price = body.price
if (body.oldPrice !== undefined) updates.oldPrice = body.oldPrice
if (body.currency !== undefined) updates.currency = body.currency
if (body.maxTasks !== undefined) updates.maxTasks = body.maxTasks
if (body.maxOffers !== undefined) updates.maxOffers = body.maxOffers
if (body.maxCards !== undefined) updates.maxCards = body.maxCards
if (body.maxMessagesPerDay !== undefined) updates.maxMessagesPerDay = body.maxMessagesPerDay
if (body.maxOrdersPerDay !== undefined) updates.maxOrdersPerDay = body.maxOrdersPerDay
if (body.maxSkills !== undefined) updates.maxSkills = body.maxSkills
if (body.maxProfiles !== undefined) updates.maxProfiles = body.maxProfiles
if (body.maxPortfolioItems !== undefined) updates.maxPortfolioItems = body.maxPortfolioItems
if (body.durationDays !== undefined) updates.durationDays = body.durationDays
if (body.notifyNewTasks !== undefined) updates.notifyNewTasks = body.notifyNewTasks
if (body.searchBoost !== undefined) updates.searchBoost = body.searchBoost
if (body.offersMultiplier !== undefined) updates.offersMultiplier = body.offersMultiplier
if (body.canContactFreePlan !== undefined) updates.canContactFreePlan = body.canContactFreePlan
if (body.canContactProPlan !== undefined) updates.canContactProPlan = body.canContactProPlan
if (body.canContactAll !== undefined) updates.canContactAll = body.canContactAll
if (body.canShowContactInfo !== undefined) updates.canShowContactInfo = body.canShowContactInfo
if (body.canViewPhone !== undefined) updates.canViewPhone = body.canViewPhone
if (body.canUploadVideo !== undefined) updates.canUploadVideo = body.canUploadVideo
if (body.hasFavorites !== undefined) updates.hasFavorites = body.hasFavorites
if (body.hasGoogleCalendar !== undefined) updates.hasGoogleCalendar = body.hasGoogleCalendar
if (body.hasVerifiedBadge !== undefined) updates.hasVerifiedBadge = body.hasVerifiedBadge
if (body.highlightedReviews !== undefined) updates.highlightedReviews = body.highlightedReviews
if (body.hasPersonalSite !== undefined) updates.hasPersonalSite = body.hasPersonalSite
if (body.hasCanHelpNowStatus !== undefined) updates.hasCanHelpNowStatus = body.hasCanHelpNowStatus
if (body.hasNeedHelpStatus !== undefined) updates.hasNeedHelpStatus = body.hasNeedHelpStatus
if (body.hasAutoResponse !== undefined) updates.hasAutoResponse = body.hasAutoResponse
if (body.hasAutoMatch !== undefined) updates.hasAutoMatch = body.hasAutoMatch
if (body.hasPriceList !== undefined) updates.hasPriceList = body.hasPriceList
if (body.receiveInstantDispatchFree !== undefined) updates.receiveInstantDispatchFree = body.receiveInstantDispatchFree
if (body.receiveInstantDispatchPro !== undefined) updates.receiveInstantDispatchPro = body.receiveInstantDispatchPro
if (body.features !== undefined) updates.features = body.features
if (body.isDefault !== undefined) updates.isDefault = body.isDefault
if (body.isActive !== undefined) updates.isActive = body.isActive
if (body.order !== undefined) updates.order = body.order
const [updated] = await db.update(plans).set(updates).where(eq(plans.id, c.req.param('id'))).returning()
if (!updated) return c.json({ error: 'Not found' }, 404)
return c.json(updated)
})
// DELETE /admin/plans/:id
app.delete('/plans/:id', requireAdmin, async (c) => {
const [deleted] = await db.delete(plans).where(eq(plans.id, c.req.param('id'))).returning()
if (!deleted) return c.json({ error: 'Not found' }, 404)
return c.json({ ok: true })
})
// ─── Settings ─────────────────────────────────────────────────────────────────
// GET /admin/settings — return all settings as a flat key→value object
app.get('/settings', requireAdmin, async (c) => {
const rows = await db.select().from(settingsTable)
const kv: Record = {}
for (const r of rows) kv[r.key] = r.value
return c.json(kv)
})
// PUT /admin/settings — upsert multiple settings at once
// Body: { "smtp.host": "smtp.gmail.com", "general.siteName": "CanHelp", ... }
app.put('/settings', requireAdmin, async (c) => {
const body = await c.req.json>()
if (typeof body !== 'object' || body === null) return c.json({ error: 'Invalid body' }, 400)
const ALLOWED_PREFIXES = ['general.', 'smtp.', 'payment.', 'seo.', 'social.', 'telegram.']
const ALLOWED_EXACT = ['referral_enabled', 'referral_plan_id', 'referral_days', 'referral_customer_plan_id', 'referral_customer_days', 'referral_specialist_plan_id', 'referral_specialist_days']
const entries = Object.entries(body).filter(([k]) =>
ALLOWED_PREFIXES.some((p) => k.startsWith(p)) || ALLOWED_EXACT.includes(k),
)
if (entries.length === 0) return c.json({ ok: true })
await Promise.all(
entries.map(([key, value]) =>
db
.insert(settingsTable)
.values({ key, value, updatedAt: new Date() })
.onConflictDoUpdate({ target: settingsTable.key, set: { value, updatedAt: new Date() } }),
),
)
invalidateEmailCache()
// Invalidate Telegram cache if any telegram.* keys were changed
if (entries.some(([k]) => k.startsWith('telegram.'))) {
const { invalidateTelegramCache } = await import('../lib/telegram.js')
invalidateTelegramCache()
}
return c.json({ ok: true })
})
// GET /admin/public-settings — safe, client-visible feature flags only.
app.get('/public-settings', async (c) => {
const [showPricing] = await db
.select({ value: settingsTable.value })
.from(settingsTable)
.where(eq(settingsTable.key, 'general.showPricing'))
return c.json({ showPricing: showPricing?.value !== 'false' })
})
// GET /admin/settings/templates — return all email templates
app.get('/settings/templates', requireAdmin, async (c) => {
const rows = await db.select().from(emailTemplates)
return c.json(rows)
})
// PUT /admin/settings/templates/:key — upsert individual template
app.put('/settings/templates/:key', requireAdmin, async (c) => {
const key = c.req.param('key')
const BASE_KEYS = [
'email_verification', 'registration_success', 'password_reset',
'new_offer', 'new_task', 'offer_accepted', 'new_message',
'offer_declined', 'offer_other_accepted', 'task_updated',
'deadline_reminder', 'task_archived', 'referral_reward',
// Plan lifecycle
'plan_activated', 'plan_upgraded', 'plan_downgrade_scheduled',
'plan_downgrade_applied', 'plan_renewal_reminder', 'plan_insufficient_funds',
]
const VALID_KEYS = [...BASE_KEYS, ...BASE_KEYS.flatMap((k) => ['el', 'en', 'ru', 'uk'].map((l) => `${k}_${l}`))]
if (!VALID_KEYS.includes(key)) return c.json({ error: 'Unknown template key' }, 400)
const { subject, body: bodyText } = await c.req.json<{ subject?: string; body?: string }>()
await db
.insert(emailTemplates)
.values({ key, subject: subject ?? '', body: bodyText ?? '', updatedAt: new Date() })
.onConflictDoUpdate({
target: emailTemplates.key,
set: { subject: subject ?? '', body: bodyText ?? '', updatedAt: new Date() },
})
const [updated] = await db.select().from(emailTemplates).where(eq(emailTemplates.key, key))
return c.json(updated)
})
// POST /admin/settings/templates/translate — translate subject+body to other locales
app.post('/settings/templates/translate', requireAdmin, async (c) => {
const { subject, body: bodyText, fromLocale } = await c.req.json<{ subject: string; body: string; fromLocale: string }>()
if (!subject && !bodyText) return c.json({ error: 'subject or body required' }, 400)
if (!['el', 'en', 'ru', 'uk'].includes(fromLocale)) return c.json({ error: 'invalid locale' }, 400)
const targets = (['el', 'en', 'ru', 'uk'] as const).filter((l) => l !== fromLocale)
const results: Record = {}
await Promise.all(targets.map(async (loc) => {
const subjectResult = subject ? await translateTask(subject, '', fromLocale, loc) : null
const bodyResult = bodyText ? await translateTask('', bodyText, fromLocale, loc) : null
results[loc] = {
subject: subjectResult?.title ?? subject,
body: bodyResult?.description ?? bodyText,
}
}))
return c.json(results)
})
// POST /admin/settings/test-email — send a test email to the current admin
app.post('/settings/test-email', requireAdmin, async (c) => {
const admin = c.get('user') as any
const to = admin?.email
if (!to) return c.json({ error: 'No admin email' }, 400)
try {
const { emailWelcome } = await import('../lib/email.js')
await emailWelcome({ to, name: admin.firstName || admin.name || 'Admin', role: 'customer', locale: admin.locale || 'el' })
await logActivity({ userId: admin.id, event: 'admin.email.test.sent', details: { to } })
return c.json({ ok: true, to })
} catch (err) {
await logActivity({
userId: admin.id,
event: 'admin.email.test.failed',
details: { to, error: err instanceof Error ? err.message : String(err) },
})
return c.json({ error: err instanceof Error ? err.message : 'Failed to send test email' }, 500)
}
})
// POST /admin/settings/test-telegram — test Telegram bot connection
app.post('/settings/test-telegram', requireAdmin, async (c) => {
const admin = c.get('user') as any
const body = await c.req.json().catch(() => ({})) as { botToken?: string; chatId?: string; locale?: string }
if (!body.botToken || !body.chatId) return c.json({ ok: false, error: 'botToken and chatId are required' })
try {
const { testTelegramConnection } = await import('../lib/telegram.js')
const result = await testTelegramConnection(body.botToken, body.chatId)
if (result.ok) {
await logActivity({ userId: admin.id, event: 'admin.telegram.test.ok', details: { chatId: body.chatId, locale: body.locale, bot: result.botName } })
return c.json({ ok: true, botName: result.botName })
} else {
return c.json({ ok: false, error: result.error })
}
} catch (err) {
return c.json({ ok: false, error: err instanceof Error ? err.message : String(err) })
}
})
// POST /admin/settings/verify-telegram-token — verify bot token via getMe (no chatId needed)
app.post('/settings/verify-telegram-token', requireAdmin, async (c) => {
const body = await c.req.json().catch(() => ({})) as { botToken?: string }
if (!body.botToken) return c.json({ ok: false, error: 'botToken is required' })
try {
const res = await fetch(`https://api.telegram.org/bot${body.botToken}/getMe`)
const data = await res.json() as { ok: boolean; result?: { username?: string; first_name?: string }; description?: string }
if (!data.ok) return c.json({ ok: false, error: data.description ?? 'Invalid token' })
const botName = data.result?.username || data.result?.first_name || 'Bot'
return c.json({ ok: true, botName })
} catch (err) {
return c.json({ ok: false, error: err instanceof Error ? err.message : String(err) })
}
})
// GET /admin/seo-public — public, no auth — returns only seo.* settings for Next.js layout/middleware
app.get('/seo-public', async (c) => {
const rows = await db.select().from(settingsTable)
const kv: Record = {}
for (const r of rows) {
if (r.key.startsWith('seo.')) kv[r.key] = r.value
}
return c.json(kv)
})
// GET /admin/social-public — public, no auth — returns social.* settings for Footer
app.get('/social-public', async (c) => {
const rows = await db.select().from(settingsTable)
const kv: Record = {}
for (const r of rows) {
if (r.key.startsWith('social.') && r.value) kv[r.key] = r.value
}
return c.json(kv)
})
// ─── Backup ────────────────────────────────────────────────────────────────────
const execFileAsync = promisify(execFile)
const _backupDir = process.env.BACKUP_DIR
? path.resolve(process.env.BACKUP_DIR)
: path.resolve(path.dirname(fileURLToPath(import.meta.url)), '../../backups')
if (!fs.existsSync(_backupDir)) {
fs.mkdirSync(_backupDir, { recursive: true })
}
function _parseDatabaseUrl(url: string) {
const parsed = new URL(url)
return {
host: parsed.hostname || 'localhost',
port: parsed.port || '5432',
user: parsed.username,
// DB_PASSWORD env var overrides URL-parsed password to avoid issues
// with special characters (+, =, @) in passwords when using new URL()
password: process.env.DB_PASSWORD || decodeURIComponent(parsed.password),
database: parsed.pathname.replace(/^\//, ''),
}
}
async function _pgDump(tableName?: string): Promise {
const dbUrl = process.env.DATABASE_URL || ''
const { host, port, user, password, database } = _parseDatabaseUrl(dbUrl)
const args = [
'-h', host, '-p', port, '-U', user, '-d', database,
'--no-password', '--clean', '--if-exists',
]
if (tableName) args.push('-t', tableName)
const { stdout } = await execFileAsync('pg_dump', args, {
env: { ...process.env, PGPASSWORD: password },
maxBuffer: 200 * 1024 * 1024,
})
return new Promise((resolve, reject) =>
zlib.gzip(Buffer.from(stdout), (err, buf) => (err ? reject(err) : resolve(buf))),
)
}
async function _psqlRestore(sqlGzBuffer: Buffer): Promise {
const dbUrl = process.env.DATABASE_URL || ''
const { host, port, user, password, database } = _parseDatabaseUrl(dbUrl)
const sql = await new Promise((resolve, reject) =>
zlib.gunzip(sqlGzBuffer, (err, buf) => (err ? reject(err) : resolve(buf))),
)
await new Promise((resolve, reject) => {
const child = spawn('psql', ['-h', host, '-p', port, '-U', user, '-d', database, '--no-password'], {
env: { ...process.env, PGPASSWORD: password },
})
let stderr = ''
child.stderr.on('data', (d: Buffer) => { stderr += d.toString() })
child.on('close', (code) => {
if (code !== 0) reject(new Error(`psql exited ${code}: ${stderr}`))
else resolve()
})
child.on('error', reject)
child.stdin.write(sql)
child.stdin.end()
})
}
// GET /admin/backup/tables — list all public tables
app.get('/backup/tables', requireAdmin, async (c) => {
const result = await db.execute(
sql`SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name`,
)
const tables = (result.rows as any[]).map((r: any) => r.table_name as string)
return c.json({ tables })
})
// GET /admin/backup/settings
app.get('/backup/settings', requireAdmin, async (c) => {
const rows = await db.select().from(settingsTable).where(
sql`${settingsTable.key} LIKE 'backup.%'`,
)
const kv: Record = {}
for (const r of rows) kv[r.key] = r.value
return c.json({
autoEnabled: kv['backup.autoEnabled'] === 'true',
lastBackup: kv['backup.lastBackup'] || null,
})
})
// PUT /admin/backup/settings
app.put('/backup/settings', requireAdmin, async (c) => {
const body = await c.req.json<{ autoEnabled?: boolean }>()
if (body.autoEnabled !== undefined) {
await db
.insert(settingsTable)
.values({ key: 'backup.autoEnabled', value: String(body.autoEnabled), updatedAt: new Date() })
.onConflictDoUpdate({ target: settingsTable.key, set: { value: String(body.autoEnabled), updatedAt: new Date() } })
}
return c.json({ ok: true })
})
// GET /admin/backup/list — list stored backup files (today / yesterday)
app.get('/backup/list', requireAdmin, async (c) => {
const names = ['backup_today', 'backup_yesterday']
const files = names
.map((name) => {
const fp = path.join(_backupDir, `${name}.sql.gz`)
if (!fs.existsSync(fp)) return null
const stat = fs.statSync(fp)
return { name, size: stat.size, date: stat.mtime.toISOString() }
})
.filter(Boolean)
return c.json({ files })
})
// GET /admin/backup/export — full DB export as .sql.gz
app.get('/backup/export', requireAdmin, async (c) => {
try {
const buf = await _pgDump()
return new Response(buf, {
headers: {
'Content-Type': 'application/gzip',
'Content-Disposition': `attachment; filename="backup_${new Date().toISOString().slice(0, 10)}.sql.gz"`,
'Content-Length': String(buf.length),
},
})
} catch (err) {
console.error('[BackupExport] error', err)
return c.json({ error: err instanceof Error ? err.message : 'Export failed' }, 500)
}
})
// GET /admin/backup/export/:table — single table export
app.get('/backup/export/:table', requireAdmin, async (c) => {
const table = c.req.param('table')
// Validate table name (alphanumeric + underscore only)
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(table)) {
return c.json({ error: 'Invalid table name' }, 400)
}
try {
const buf = await _pgDump(table)
return new Response(buf, {
headers: {
'Content-Type': 'application/gzip',
'Content-Disposition': `attachment; filename="${table}_${new Date().toISOString().slice(0, 10)}.sql.gz"`,
'Content-Length': String(buf.length),
},
})
} catch (err) {
console.error('[BackupExport] table error', err)
return c.json({ error: err instanceof Error ? err.message : 'Export failed' }, 500)
}
})
// POST /admin/backup/import — full DB import from uploaded .sql.gz
app.post('/backup/import', requireAdmin, async (c) => {
try {
const formData = await c.req.formData()
const file = formData.get('file') as File | null
if (!file) return c.json({ error: 'No file provided' }, 400)
const buf = Buffer.from(await file.arrayBuffer())
await _psqlRestore(buf)
return c.json({ ok: true })
} catch (err) {
console.error('[BackupImport] error', err)
return c.json({ error: err instanceof Error ? err.message : 'Import failed' }, 500)
}
})
// POST /admin/backup/import/:table — single table import
app.post('/backup/import/:table', requireAdmin, async (c) => {
const table = c.req.param('table')
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(table)) {
return c.json({ error: 'Invalid table name' }, 400)
}
try {
const formData = await c.req.formData()
const file = formData.get('file') as File | null
if (!file) return c.json({ error: 'No file provided' }, 400)
const buf = Buffer.from(await file.arrayBuffer())
await _psqlRestore(buf)
return c.json({ ok: true })
} catch (err) {
console.error('[BackupImport] table error', err)
return c.json({ error: err instanceof Error ? err.message : 'Import failed' }, 500)
}
})
// POST /admin/backup/restore — restore from stored backup file (today | yesterday)
app.post('/backup/restore', requireAdmin, async (c) => {
const { name } = await c.req.json<{ name: string }>()
if (!['backup_today', 'backup_yesterday'].includes(name)) {
return c.json({ error: 'Invalid backup name' }, 400)
}
const fp = path.join(_backupDir, `${name}.sql.gz`)
if (!fs.existsSync(fp)) return c.json({ error: 'Backup file not found' }, 404)
try {
const buf = fs.readFileSync(fp)
await _psqlRestore(buf)
return c.json({ ok: true })
} catch (err) {
console.error('[BackupRestore] error', err)
return c.json({ error: err instanceof Error ? err.message : 'Restore failed' }, 500)
}
})
// ─── Translations ───────────────────────────────────────────────────────────
const TRANSLATION_LANGS = ['el', 'en', 'ru', 'uk'] as const
type TranslationLang = (typeof TRANSLATION_LANGS)[number]
const TABLE_CONFIG = {
categories: {
table: categories,
fields: { el: 'namesEl', en: 'namesEn', ru: 'namesRu', uk: 'namesUk' } as Record,
displayField: 'namesEl' as const,
},
locations: {
table: locations,
fields: { el: 'nameEl', en: 'nameEn', ru: 'nameRu', uk: 'nameUk' } as Record,
displayField: 'nameEl' as const,
},
skill_suggestions: {
table: skillSuggestions,
fields: { el: 'nameEl', en: 'nameEn', ru: 'nameRu', uk: 'nameUk' } as Record,
displayField: 'nameEl' as const,
},
specialist_cards: {
table: specialistCards,
fields: { el: 'descriptionEl', en: 'descriptionEn', ru: 'descriptionRu', uk: 'descriptionUk' } as Record,
displayField: 'descriptionEl' as const,
},
} as const
// GET /admin/translations/stats
app.get('/translations/stats', requireAdmin, async (c) => {
const [catStats] = await db.select({
total: count(),
missingEl: sql`COUNT(*) FILTER (WHERE names_el IS NULL OR names_el = '')`,
missingEn: sql`COUNT(*) FILTER (WHERE names_en IS NULL OR names_en = '')`,
missingRu: sql`COUNT(*) FILTER (WHERE names_ru IS NULL OR names_ru = '')`,
missingUk: sql`COUNT(*) FILTER (WHERE names_uk IS NULL OR names_uk = '')`,
}).from(categories)
const [locStats] = await db.select({
total: count(),
missingEl: sql`COUNT(*) FILTER (WHERE name_el IS NULL OR name_el = '')`,
missingEn: sql`COUNT(*) FILTER (WHERE name_en IS NULL OR name_en = '')`,
missingRu: sql`COUNT(*) FILTER (WHERE name_ru IS NULL OR name_ru = '')`,
missingUk: sql`COUNT(*) FILTER (WHERE name_uk IS NULL OR name_uk = '')`,
}).from(locations)
const [skillStats] = await db.select({
total: count(),
missingEl: sql`COUNT(*) FILTER (WHERE name_el IS NULL OR name_el = '')`,
missingEn: sql`COUNT(*) FILTER (WHERE name_en IS NULL OR name_en = '')`,
missingRu: sql`COUNT(*) FILTER (WHERE name_ru IS NULL OR name_ru = '')`,
missingUk: sql`COUNT(*) FILTER (WHERE name_uk IS NULL OR name_uk = '')`,
}).from(skillSuggestions)
const [cardStats] = await db.select({
total: count(),
// A locale is "missing" only when the locale field is empty AND it's not the original locale
// (the original locale field may also be empty if description is filled instead — handled below)
missingEl: sql`COUNT(*) FILTER (WHERE (description_el IS NULL OR description_el = '') AND (original_locale != 'el' OR (description IS NULL OR description = '')))`,
missingEn: sql`COUNT(*) FILTER (WHERE (description_en IS NULL OR description_en = '') AND original_locale != 'en')`,
missingRu: sql`COUNT(*) FILTER (WHERE (description_ru IS NULL OR description_ru = '') AND original_locale != 'ru')`,
missingUk: sql`COUNT(*) FILTER (WHERE (description_uk IS NULL OR description_uk = '') AND original_locale != 'uk')`,
}).from(specialistCards)
const [taskStats] = await db.select({
total: count(),
missingEl: sql`COUNT(*) FILTER (WHERE title_el IS NULL OR title_el = '')`,
missingEn: sql`COUNT(*) FILTER (WHERE title_en IS NULL OR title_en = '')`,
missingRu: sql`COUNT(*) FILTER (WHERE title_ru IS NULL OR title_ru = '')`,
missingUk: sql`COUNT(*) FILTER (WHERE title_uk IS NULL OR title_uk = '')`,
}).from(tasks)
const EMAIL_BASE_KEYS_STATS = [
'email_verification', 'registration_success', 'password_reset',
'new_offer', 'new_task', 'offer_accepted', 'new_message',
'offer_declined', 'offer_other_accepted', 'task_updated',
'deadline_reminder', 'task_archived', 'referral_reward',
'plan_activated', 'plan_upgraded', 'plan_downgrade_scheduled',
'plan_downgrade_applied', 'plan_renewal_reminder', 'plan_insufficient_funds',
]
const emailRows = await db.select({ key: emailTemplates.key }).from(emailTemplates)
const emailKeySet = new Set(emailRows.map((r) => r.key))
const emailTotal = EMAIL_BASE_KEYS_STATS.length
const emailMissingEl = EMAIL_BASE_KEYS_STATS.filter((k) => !emailKeySet.has(`${k}_el`)).length
const emailMissingEn = EMAIL_BASE_KEYS_STATS.filter((k) => !emailKeySet.has(`${k}_en`)).length
const emailMissingRu = EMAIL_BASE_KEYS_STATS.filter((k) => !emailKeySet.has(`${k}_ru`)).length
const emailMissingUk = EMAIL_BASE_KEYS_STATS.filter((k) => !emailKeySet.has(`${k}_uk`)).length
return c.json({
tables: [
{
name: 'categories',
label: 'Categories',
total: Number(catStats.total),
missing: { el: Number(catStats.missingEl), en: Number(catStats.missingEn), ru: Number(catStats.missingRu), uk: Number(catStats.missingUk) },
},
{
name: 'locations',
label: 'Locations',
total: Number(locStats.total),
missing: { el: Number(locStats.missingEl), en: Number(locStats.missingEn), ru: Number(locStats.missingRu), uk: Number(locStats.missingUk) },
},
{
name: 'skill_suggestions',
label: 'Skills',
total: Number(skillStats.total),
missing: { el: Number(skillStats.missingEl), en: Number(skillStats.missingEn), ru: Number(skillStats.missingRu), uk: Number(skillStats.missingUk) },
},
{
name: 'tasks',
label: 'Tasks',
total: Number(taskStats.total),
missing: { el: Number(taskStats.missingEl), en: Number(taskStats.missingEn), ru: Number(taskStats.missingRu), uk: Number(taskStats.missingUk) },
},
{
name: 'email_templates',
label: 'Email Templates',
total: emailTotal,
missing: { el: emailMissingEl, en: emailMissingEn, ru: emailMissingRu, uk: emailMissingUk },
},
{
name: 'specialist_cards',
label: 'Portfolio (Card descriptions)',
total: Number(cardStats.total),
missing: { el: Number(cardStats.missingEl), en: Number(cardStats.missingEn), ru: Number(cardStats.missingRu), uk: Number(cardStats.missingUk) },
},
],
})
})
// POST /admin/translations/auto-translate
app.post('/translations/auto-translate', requireAdmin, async (c) => {
const { table: tableName, missingLang, fromLang, batchSize = 20 } = await c.req.json<{
table: string
missingLang: TranslationLang
fromLang: TranslationLang
batchSize?: number
}>()
if (!TRANSLATION_LANGS.includes(missingLang) || !TRANSLATION_LANGS.includes(fromLang)) {
return c.json({ error: 'Invalid language' }, 400)
}
const cfg = TABLE_CONFIG[tableName as keyof typeof TABLE_CONFIG]
if (!cfg) return c.json({ error: 'Unknown table' }, 400)
const missingFieldName = cfg.fields[missingLang]
const fromFieldName = cfg.fields[fromLang]
const missingCol = (cfg.table as any)[missingFieldName]
// Fetch rows with missing translation
const rows = await db
.select()
.from(cfg.table as any)
.where(or(isNull(missingCol), eq(missingCol, '')))
.limit(Math.min(batchSize, 500))
const rowsWithText = rows
.map((row) => {
// For specialist_cards, fall back to the base `description` field if the
// requested source locale field is empty (legacy cards filled before locale fields existed)
const localeText = (row as any)[fromFieldName] as string | null | undefined
const fallbackText = tableName === 'specialist_cards' ? (row as any).description as string | null | undefined : undefined
return { row, text: (localeText || fallbackText || '') }
})
.filter((r) => r.text)
const translations = await translateStringsBatch(rowsWithText.map((r) => r.text), fromLang, missingLang)
let translated = 0
let failed = rows.length - rowsWithText.length
await Promise.all(rowsWithText.map(async ({ row }, i) => {
const result = translations[i]
if (!result) { failed++; return }
await db
.update(cfg.table as any)
.set({ [missingFieldName]: result } as any)
.where(eq((cfg.table as any).id, row.id))
translated++
}))
// Invalidate caches so frontends pick up new translations
if (tableName === 'categories') await redis.del(CATEGORIES_CACHE_KEY).catch(() => {})
if (tableName === 'locations') await redis.del(LOCATIONS_CACHE_KEY).catch(() => {})
return c.json({ translated, failed, total: rows.length })
})
// GET /admin/translations/tasks-preview?lang=el&limit=50
app.get('/translations/tasks-preview', requireAdmin, async (c) => {
const lang = c.req.query('lang') as string | undefined
const limit = Math.min(Number(c.req.query('limit') ?? 50), 200)
if (!lang || !TRANSLATION_LANGS.includes(lang as any)) {
return c.json({ error: 'Invalid lang' }, 400)
}
const cap = (s: string) => s.charAt(0).toUpperCase() + s.slice(1)
const titleField = tasks[`title${cap(lang)}` as keyof typeof tasks] as any
const rows = await db
.select({
id: tasks.id,
title: tasks.title,
originalLocale: tasks.originalLocale,
titleEl: tasks.titleEl,
titleEn: tasks.titleEn,
titleRu: tasks.titleRu,
titleUk: tasks.titleUk,
})
.from(tasks)
.where(or(isNull(titleField), eq(titleField, '')))
.orderBy(tasks.id)
.limit(limit)
return c.json({ lang, rows })
})
// POST /admin/translations/auto-translate-tasks
// Translates tasks from their originalLocale to ALL missing language fields
app.post('/translations/auto-translate-tasks', requireAdmin, async (c) => {
const { batchSize = 500 } = await c.req.json<{ batchSize?: number }>()
const cap = (s: string) => s.charAt(0).toUpperCase() + s.slice(1)
const langs: TranslationLang[] = ['el', 'en', 'ru', 'uk']
// Fetch tasks that have any missing translation
const rows = await db
.select()
.from(tasks)
.where(or(
isNull(tasks.titleEl), eq(tasks.titleEl, ''),
isNull(tasks.titleEn), eq(tasks.titleEn, ''),
isNull(tasks.titleRu), eq(tasks.titleRu, ''),
isNull(tasks.titleUk), eq(tasks.titleUk, ''),
))
.limit(Math.min(batchSize, 500))
const translatedTaskIds = new Set()
let failed = 0
// For each target language: collect tasks missing it, group by originalLocale, batch translate
for (const toLang of langs) {
const titleField = `title${cap(toLang)}`
const descField = `description${cap(toLang)}`
const needLang = rows.filter((r) => {
const val = (r as any)[titleField]
return !val || val === ''
})
if (needLang.length === 0) continue
// Tasks whose original content IS this language — just copy title/description directly
const directCopy = needLang.filter((r) => (r.originalLocale ?? 'el') === toLang)
if (directCopy.length > 0) {
await Promise.all(directCopy.map(async (row) => {
await db
.update(tasks)
.set({ [titleField]: row.title, [descField]: row.description } as any)
.where(eq(tasks.id, row.id))
translatedTaskIds.add(row.id)
}))
}
// Tasks that need actual translation (different source locale)
const needTranslation = needLang.filter((r) => (r.originalLocale ?? 'el') !== toLang)
if (needTranslation.length === 0) continue
const byLocale = new Map()
for (const row of needTranslation) {
const loc = row.originalLocale ?? 'el'
if (!byLocale.has(loc)) byLocale.set(loc, [])
byLocale.get(loc)!.push(row)
}
for (const [fromLocale, group] of byLocale.entries()) {
const titles = group.map((r) => r.title)
const descriptions = group.map((r) => r.description)
const [translatedTitles, translatedDescs] = await Promise.all([
translateStringsBatch(titles, fromLocale, toLang),
translateStringsBatch(descriptions, fromLocale, toLang),
])
await Promise.all(group.map(async (row, i) => {
const t = translatedTitles[i]
const d = translatedDescs[i]
if (!t || !d) { failed++; return }
await db
.update(tasks)
.set({ [titleField]: t, [descField]: d } as any)
.where(eq(tasks.id, row.id))
translatedTaskIds.add(row.id)
}))
}
}
return c.json({ translated: translatedTaskIds.size, failed, total: rows.length })
})
// POST /admin/translations/auto-translate-templates
// Bulk translate email templates to a missing locale
app.post('/translations/auto-translate-templates', requireAdmin, async (c) => {
const { missingLang, fromLang = 'el' } = await c.req.json<{
missingLang: TranslationLang
fromLang?: TranslationLang
}>()
if (!TRANSLATION_LANGS.includes(missingLang)) return c.json({ error: 'Invalid language' }, 400)
if (!TRANSLATION_LANGS.includes(fromLang as TranslationLang)) return c.json({ error: 'Invalid fromLang' }, 400)
const EMAIL_BASE_KEYS_TRANS = [
'email_verification', 'registration_success', 'password_reset',
'new_offer', 'new_task', 'offer_accepted', 'new_message',
'offer_declined', 'offer_other_accepted', 'task_updated',
'deadline_reminder', 'task_archived', 'referral_reward',
'plan_activated', 'plan_upgraded', 'plan_downgrade_scheduled',
'plan_downgrade_applied', 'plan_renewal_reminder', 'plan_insufficient_funds',
]
const existingRows = await db.select().from(emailTemplates)
const existingMap = new Map(existingRows.map((r) => [r.key, r]))
const keysToTranslate = EMAIL_BASE_KEYS_TRANS.filter((k) => !existingMap.has(`${k}_${missingLang}`))
// Build list of source rows in order
const sourceEntries = keysToTranslate
.map((k) => ({ key: k, row: existingMap.get(`${k}_${fromLang}`) ?? existingMap.get(k) }))
.filter((e): e is { key: string; row: NonNullable } => !!e.row && !!(e.row.subject || e.row.body))
const subjects = sourceEntries.map((e) => e.row.subject ?? '')
const bodies = sourceEntries.map((e) => e.row.body ?? '')
const [translatedSubjects, translatedBodies] = await Promise.all([
translateStringsBatch(subjects.map((s) => s || ' '), fromLang, missingLang),
translateStringsBatch(bodies.map((b) => b || ' '), fromLang, missingLang),
])
let translated = 0
let failed = keysToTranslate.length - sourceEntries.length
await Promise.all(sourceEntries.map(async ({ key, row }, i) => {
const subjectResult = translatedSubjects[i] ?? row.subject ?? ''
const bodyResult = translatedBodies[i] ?? row.body ?? ''
try {
const targetKey = `${key}_${missingLang}`
await db
.insert(emailTemplates)
.values({ key: targetKey, subject: subjectResult, body: bodyResult, updatedAt: new Date() })
.onConflictDoUpdate({
target: emailTemplates.key,
set: { subject: subjectResult, body: bodyResult, updatedAt: new Date() },
})
translated++
} catch {
failed++
}
}))
return c.json({ translated, failed, total: keysToTranslate.length })
})
// GET /admin/translations/:table
app.get('/translations/:table', requireAdmin, async (c) => {
const tableName = c.req.param('table')
const missingLang = (c.req.query('missingLang') ?? 'uk') as TranslationLang
const page = Math.max(1, parseInt(c.req.query('page') ?? '1', 10))
const limit = Math.min(100, parseInt(c.req.query('limit') ?? '50', 10))
const offset = (page - 1) * limit
if (!TRANSLATION_LANGS.includes(missingLang)) return c.json({ error: 'Invalid language' }, 400)
const cfg = TABLE_CONFIG[tableName as keyof typeof TABLE_CONFIG]
if (!cfg) return c.json({ error: 'Unknown table' }, 400)
const missingFieldName = cfg.fields[missingLang]
const missingCol = (cfg.table as any)[missingFieldName]
const [rows, [{ total }]] = await Promise.all([
db
.select()
.from(cfg.table as any)
.where(or(isNull(missingCol), eq(missingCol, '')))
.orderBy((cfg.table as any)[cfg.displayField])
.limit(limit)
.offset(offset),
db
.select({ total: count() })
.from(cfg.table as any)
.where(or(isNull(missingCol), eq(missingCol, ''))),
])
return c.json({ rows, total: Number(total), page, limit })
})
// POST /admin/translations/:table/save
app.post('/translations/:table/save', requireAdmin, async (c) => {
const tableName = c.req.param('table')
const cfg = TABLE_CONFIG[tableName as keyof typeof TABLE_CONFIG]
if (!cfg) return c.json({ error: 'Unknown table' }, 400)
const { items } = await c.req.json<{
items: Array<{ id: string; lang: TranslationLang; value: string }>
}>()
if (!Array.isArray(items) || items.length === 0) return c.json({ error: 'No items' }, 400)
const ALLOWED_FIELDS = new Set(Object.values(cfg.fields))
let saved = 0
for (const item of items) {
if (!item.id || !item.lang || !item.value?.trim()) continue
const fieldName = cfg.fields[item.lang]
if (!fieldName || !ALLOWED_FIELDS.has(fieldName)) continue
await db
.update(cfg.table as any)
.set({ [fieldName]: item.value.trim() } as any)
.where(eq((cfg.table as any).id, item.id))
saved++
}
// Invalidate caches so frontends pick up new translations
if (saved > 0) {
if (tableName === 'categories') await redis.del(CATEGORIES_CACHE_KEY).catch(() => {})
if (tableName === 'locations') await redis.del(LOCATIONS_CACHE_KEY).catch(() => {})
}
return c.json({ saved })
})
// GET /admin/referrals — referral tree with stats
app.get('/referrals', requireAdmin, async (c) => {
const rewards = await db
.select()
.from(referralRewards)
.orderBy(desc(referralRewards.createdAt))
if (rewards.length === 0) {
return c.json({ totalReferrers: 0, totalReferrals: 0, activeRewards: 0, tree: [] })
}
const userIds = [...new Set([...rewards.map((r) => r.referrerId), ...rewards.map((r) => r.refereeId)])]
const usersData = await db
.select({ id: users.id, firstName: users.firstName, lastName: users.lastName, email: users.email, referralCode: users.referralCode })
.from(users)
.where(inArray(users.id, userIds))
const userMap = Object.fromEntries(usersData.map((u) => [u.id, u]))
const now = new Date()
const activeRewards = rewards.filter((r) => r.expiresAt > now).length
const grouped = new Map()
for (const r of rewards) {
if (!grouped.has(r.referrerId)) {
grouped.set(r.referrerId, { referrer: userMap[r.referrerId], referees: [] })
}
grouped.get(r.referrerId)!.referees.push({
...userMap[r.refereeId],
planId: r.planId,
daysAdded: r.daysAdded,
expiresAt: r.expiresAt,
createdAt: r.createdAt,
})
}
const tree = [...grouped.values()].sort((a, b) => b.referees.length - a.referees.length)
return c.json({ totalReferrers: grouped.size, totalReferrals: rewards.length, activeRewards, tree })
})
export { _backupDir, _pgDump }
export default app