FM / routes

/opt/canhelp/apps/api/src/routes

Name	Size	Mode	Actions
admin.ts	95057	0644	edit dl rm
auto-match.ts	7406	0644	edit dl rm
auto-response.ts	8111	0644	edit dl rm
availability.ts	7294	0644	edit dl rm
categories.ts	1867	0644	edit dl rm
chat.ts	9377	0644	edit dl rm
dashboard.ts	5142	0644	edit dl rm
favorites.ts	4441	0644	edit dl rm
fomo.ts	6844	0644	edit dl rm
google.ts	4872	0644	edit dl rm
health.ts	1214	0644	edit dl rm
i18n.ts	167086	0644	edit dl rm
locations.ts	1781	0644	edit dl rm
notifications.ts	2862	0644	edit dl rm
offers.ts	10047	0644	edit dl rm
payments.ts	21167	0644	edit dl rm
plans.ts	1352	0644	edit dl rm
portfolio.ts	6658	0644	edit dl rm
price-list.ts	14417	0644	edit dl rm
reports.ts	2007	0644	edit dl rm
reviews.ts	2783	0644	edit dl rm
skill-suggestions.ts	1035	0644	edit dl rm
specialist-cards.ts	18641	0644	edit dl rm
stats.ts	1093	0644	edit dl rm
statuses.ts	3731	0644	edit dl rm
support.ts	8452	0644	edit dl rm
tasks.ts	30018	0644	edit dl rm
uploads.ts	1526	0644	edit dl rm
users.ts	37157	0644	edit dl rm

Edit: /opt/canhelp/apps/api/src/routes/users.ts (37157B)

import { Hono, type Context } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { eq, and, ne, count, desc, or, ilike, avg, asc, inArray, sql, gte, countDistinct } from 'drizzle-orm'
import { createHash } from 'crypto'
import { db } from '../db.js'
import { users, reviews, specialistCards, plans, specialistAvailability, referralRewards, userStatuses, portfolioItems, offers, tasks, sitePageViews, siteContactLogs, planHistory, pushTokens } from '@canhelp/db'
import { requireAuth, optionalAuth, type AuthVariables } from '../middleware/auth.js'
import { generateReferralCode, applyReferral } from '../lib/referral.js'
import { translateString } from '../translate.js'
import {
  enrichUserWithHelperCapability,
  getHelperCapability,
  isAdminRole,
  toClientRole,
} from '../lib/helper-capability.js'

const app = new Hono<{ Variables: AuthVariables }>()

type AppLocale = 'el' | 'en' | 'ru' | 'uk'

function normalizeLocale(input?: string | null): AppLocale {
  if (input === 'el' || input === 'en' || input === 'ru' || input === 'uk') return input
  return 'el'
}

function pickLocalizedBio(user: any, locale?: string | null): string | null {
  const l = normalizeLocale(locale)
  const byLocale = {
    el: user.bioEl,
    en: user.bioEn,
    ru: user.bioRu,
    uk: user.bioUk,
  } as const
  return byLocale[l] ?? user.bio ?? null
}

// GET /users/me
app.get('/me', requireAuth, async (c) => {
  const authUser = c.get('user')
  const [dbUser] = await db.select().from(users).where(eq(users.id, authUser.id))
  const user = dbUser ? { ...dbUser, bio: pickLocalizedBio(dbUser, dbUser.locale) } : null
  if (!user) return c.json({ error: 'Not found' }, 404)

let plan: typeof plans.$inferSelect | null = null
  if (user.planId) {
    const [p] = await db.select().from(plans).where(eq(plans.id, user.planId))
    plan = p ?? null
  }

const enriched = await enrichUserWithHelperCapability(user)
  const [{ total: offersCount }] = await db
    .select({ total: count() })
    .from(specialistCards)
    .where(eq(specialistCards.specialistId, user.id))
  return c.json({ ...enriched, plan, hasOffers: (offersCount ?? 0) > 0 })
})

app.post(
  '/me/push-token',
  requireAuth,
  zValidator(
    'json',
    z.object({
      token: z.string().min(10),
      platform: z.enum(['android', 'ios', 'web', 'unknown']).optional(),
      locale: z.enum(['el', 'en', 'ru', 'uk']).optional(),
    }),
  ),
  async (c) => {
    const authUser = c.get('user')
    const { token, platform, locale } = c.req.valid('json')

const [existing] = await db
      .select({ id: pushTokens.id })
      .from(pushTokens)
      .where(eq(pushTokens.token, token))

if (existing) {
      await db
        .update(pushTokens)
        .set({
          userId: authUser.id,
          platform: platform ?? 'unknown',
          locale,
          updatedAt: new Date(),
        })
        .where(eq(pushTokens.id, existing.id))
    } else {
      await db.insert(pushTokens).values({
        userId: authUser.id,
        token,
        platform: platform ?? 'unknown',
        locale,
      })
    }

return c.json({ ok: true })
  },
)

app.post(
  '/me/push-token/remove',
  requireAuth,
  zValidator('json', z.object({ token: z.string().min(10) })),
  async (c) => {
    const authUser = c.get('user')
    const { token } = c.req.valid('json')
    await db
      .delete(pushTokens)
      .where(and(eq(pushTokens.userId, authUser.id), eq(pushTokens.token, token)))
    return c.json({ ok: true })
  },
)

// POST /users/me/referral/apply — apply a referral code for the current user
// Used after Google OAuth when refCode couldn't be passed through the OAuth flow
app.post('/me/referral/apply', requireAuth, zValidator('json', z.object({ refCode: z.string().min(1).max(20) })), async (c) => {
  const authUser = c.get('user')
  const { refCode } = c.req.valid('json')
  // Check if reward already applied for this user
  const existing = await db
    .select({ id: referralRewards.id })
    .from(referralRewards)
    .where(eq(referralRewards.refereeId, authUser.id))
    .limit(1)
  if (existing.length) return c.json({ ok: true, skipped: true })
  await applyReferral(authUser.id, refCode)
  return c.json({ ok: true })
})

// GET /users/me/referral — get referral info (code + stats)
app.get('/me/referral', requireAuth, async (c) => {
  const authUser = c.get('user')
  let [user] = await db
    .select({ referralCode: users.referralCode })
    .from(users)
    .where(eq(users.id, authUser.id))
  if (!user) return c.json({ error: 'Not found' }, 404)

// Auto-generate code for existing users that were created before referral system
  if (!user.referralCode) {
    let code: string | null = null
    for (let i = 0; i < 5; i++) {
      const candidate = generateReferralCode()
      const existing = await db.select({ id: users.id }).from(users).where(eq(users.referralCode, candidate))
      if (existing.length === 0) { code = candidate; break }
    }
    if (code) {
      await db.update(users).set({ referralCode: code }).where(eq(users.id, authUser.id))
      user = { referralCode: code }
    }
  }

const rewarded = await db
    .select({ count: count() })
    .from(referralRewards)
    .where(eq(referralRewards.referrerId, authUser.id))

const webUrl = process.env.WEB_URL ?? 'https://canhelp.gr'
  return c.json({
    referralCode: user.referralCode,
    referralCount: Number(rewarded[0]?.count ?? 0),
    referralUrl: user.referralCode
      ? `${webUrl}/register?ref=${user.referralCode}`
      : null,
  })
})

// GET /users/me/plan-history — plan change history for current user
app.get('/me/plan-history', requireAuth, async (c) => {
  const user = c.get('user')
  const limit = Math.min(Number(c.req.query('limit') ?? '50'), 100)
  const rows = await db
    .select()
    .from(planHistory)
    .where(eq(planHistory.userId, user.id))
    .orderBy(desc(planHistory.createdAt))
    .limit(limit)
  return c.json(rows)
})

app.patch(
  '/me',
  requireAuth,
  zValidator(
    'json',
    z.object({
      firstName: z.string().min(1).optional(),
      lastName: z.string().min(1).optional(),
      phone: z.string().optional(),
      bio: z.string().max(1000).optional(),
      bioLocale: z.enum(['el', 'en', 'ru', 'uk']).optional(),
      autoTranslateBio: z.boolean().optional(),
      image: z.string().url().optional().nullable(),
      skills: z.array(z.string()).optional(),
      specialistCategories: z.array(z.string()).optional(),
      specialistLocations: z.array(z.string()).optional(),
      locale: z.enum(['el', 'en', 'ru', 'uk']).optional(),
      notifyNewTasks: z.boolean().optional(),
      notifMessages: z.boolean().optional(),
      languages: z.array(z.string().max(50)).max(20).optional(),
      showContactInfo: z.boolean().optional(),
      personalSiteSlug: z.string().max(50).regex(/^[a-z0-9_-]+$/).optional().nullable(),
      siteSettings: z.object({
        showBio: z.boolean().optional(),
        showRating: z.boolean().optional(),
        showServices: z.boolean().optional(),
        showPriceList: z.boolean().optional(),
        showPortfolio: z.boolean().optional(),
        showReviews: z.boolean().optional(),
        showPhone: z.boolean().optional(),
        showStatus: z.boolean().optional(),
        showFullLastName: z.boolean().optional(),
        siteTheme: z.enum(['dark', 'light']).optional(),
        profileBackgroundImage: z.string().url().optional().nullable(),
      }).optional(),
    }),
  ),
  async (c) => {
    const authUser = c.get('user')
    const body = c.req.valid('json')

const updates: Partial<typeof users.$inferInsert> = {}
    if (body.firstName !== undefined) updates.firstName = body.firstName
    if (body.lastName !== undefined) updates.lastName = body.lastName
    if (body.phone !== undefined) updates.phone = body.phone
    if (body.bio !== undefined) {
      const [currentUser] = await db
        .select({ locale: users.locale })
        .from(users)
        .where(eq(users.id, authUser.id))
      const sourceLocale = normalizeLocale(body.bioLocale ?? body.locale ?? currentUser?.locale)
      updates.bio = body.bio
      if (sourceLocale === 'el') updates.bioEl = body.bio
      if (sourceLocale === 'en') updates.bioEn = body.bio
      if (sourceLocale === 'ru') updates.bioRu = body.bio
      if (sourceLocale === 'uk') updates.bioUk = body.bio
    }
    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.locale !== undefined) updates.locale = body.locale
    if (body.image !== undefined) updates.image = body.image
    if (body.notifyNewTasks !== undefined) updates.notifyNewTasks = body.notifyNewTasks
    if (body.notifMessages !== undefined) updates.notifMessages = body.notifMessages
    if (body.languages !== undefined) updates.languages = body.languages
    if (body.showContactInfo !== undefined) {
      // Check if user's plan allows showing contact info
      const [dbUser] = await db.select({ planId: users.planId }).from(users).where(eq(users.id, authUser.id))
      if (body.showContactInfo && dbUser?.planId) {
        const [plan] = await db.select().from(plans).where(eq(plans.id, dbUser.planId))
        if (!plan?.canShowContactInfo) {
          return c.json({ error: 'Your plan does not allow showing contact info', code: 'PLAN_FEATURE_RESTRICTED' }, 403)
        }
      }
      updates.showContactInfo = body.showContactInfo
    }
    if (body.personalSiteSlug !== undefined) {
      // Check if user's plan allows personal site and read current slug for legacy validation.
      const [dbUser] = await db
        .select({ planId: users.planId, personalSiteSlug: users.personalSiteSlug })
        .from(users)
        .where(eq(users.id, authUser.id))

// Legacy compatibility: allow 1-2 char slug only if user keeps their existing slug unchanged.
      if (body.personalSiteSlug && body.personalSiteSlug.length < 3 && body.personalSiteSlug !== dbUser?.personalSiteSlug) {
        return c.json({ error: 'Personal site URL must be at least 3 characters', code: 'SLUG_TOO_SHORT' }, 400)
      }

if (body.personalSiteSlug && dbUser?.planId) {
        const [plan] = await db.select().from(plans).where(eq(plans.id, dbUser.planId))
        if (!plan?.hasPersonalSite) {
          return c.json({ error: 'Your plan does not include a personal site', code: 'PLAN_FEATURE_RESTRICTED' }, 403)
        }
      }
      // Check slug uniqueness
      if (body.personalSiteSlug) {
        const [taken] = await db
          .select({ id: users.id })
          .from(users)
          .where(and(eq(users.personalSiteSlug, body.personalSiteSlug), ne(users.id, authUser.id)))
        if (taken) {
          return c.json({ error: 'This URL is already taken', code: 'SLUG_TAKEN' }, 409)
        }
      }
      updates.personalSiteSlug = body.personalSiteSlug
    }
    if (body.siteSettings !== undefined) {
      updates.siteSettings = body.siteSettings
    }

if (Object.keys(updates).length === 0) {
      return c.json({ error: 'No fields to update' }, 400)
    }

updates.updatedAt = new Date()
    const [updated] = await db
      .update(users)
      .set(updates)
      .where(eq(users.id, authUser.id))
      .returning()

if (body.bio && (body.autoTranslateBio ?? true)) {
      const sourceLocale = normalizeLocale(body.bioLocale ?? body.locale ?? updated.locale)
      const targets = (['el', 'en', 'ru', 'uk'] as const).filter((l) => l !== sourceLocale)
      Promise.allSettled(
        targets.map((to) =>
          translateString(body.bio as string, sourceLocale, to).then(async (translated) => {
            if (!translated) return
            const field = to === 'el' ? 'bioEl' : to === 'en' ? 'bioEn' : to === 'ru' ? 'bioRu' : 'bioUk'
            await db
              .update(users)
              .set({ [field]: translated, updatedAt: new Date() })
              .where(eq(users.id, authUser.id))
          }),
        ),
      ).catch((e) => console.error('[bio-translate]', e))
    }

let plan: typeof plans.$inferSelect | null = null
    if (updated.planId) {
      const [p] = await db.select().from(plans).where(eq(plans.id, updated.planId))
      plan = p ?? null
    }

const enriched = await enrichUserWithHelperCapability({ ...updated, bio: pickLocalizedBio(updated, updated.locale) })
    return c.json({ ...enriched, plan })
  },
)

async function enableHelperMode(c: Context<{ Variables: AuthVariables }>) {
  const authUser = c.get('user')
  const [user] = await db.select().from(users).where(eq(users.id, authUser.id))
  if (!user) return c.json({ error: 'Not found' }, 404)

const [updated] = await db
    .update(users)
    .set({ updatedAt: new Date() })
    .where(eq(users.id, authUser.id))
    .returning()

const enriched = await enrichUserWithHelperCapability(updated)
  return c.json(enriched)
}

// POST /users/me/offer-help
app.post('/me/offer-help', requireAuth, enableHelperMode)

// POST /users/me/become-specialist (legacy alias)
app.post('/me/become-specialist', requireAuth, enableHelperMode)

// GET /users/specialists (public)
app.get('/specialists', async (c) => {
  const page = Math.max(1, 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 filterCategory = c.req.query('category') || ''
  const filterLocation = c.req.query('location') || ''
  const filterLanguage = c.req.query('language') || ''
  const locale = normalizeLocale(c.req.query('locale'))

// Support multiple languages via repeated language[]=el&language[]=en or comma-separated
  const rawLanguages = c.req.queries('language') ?? (filterLanguage ? [filterLanguage] : [])
  const filterLanguages = rawLanguages.flatMap((l) => l.split(',').map((s) => s.trim())).filter(Boolean)

// If filtering by category, location, or language, find matching specialistIds via cards
  let filteredIds: string[] | null = null
  if (filterCategory || filterLocation) {
    const cardConditions: any[] = [eq(specialistCards.isActive, true), eq(specialistCards.publicationStatus, 'active')]
    if (filterCategory) {
      cardConditions.push(sql`${specialistCards.categories} @> ARRAY[${filterCategory}]::text[]`)
    }
    if (filterLocation) {
      cardConditions.push(sql`${specialistCards.locations} @> ARRAY[${filterLocation}]::text[]`)
    }
    const matching = await db
      .selectDistinct({ specialistId: specialistCards.specialistId })
      .from(specialistCards)
      .where(and(...cardConditions))
    filteredIds = matching.map((r) => r.specialistId)
    if (filteredIds.length === 0) {
      return c.json({ data: [], total: 0, page, limit })
    }
  }

const helperReadyCondition = sql<boolean>`
    (
      (${users.bio} IS NOT NULL AND btrim(${users.bio}) <> '')
      AND EXISTS (
        SELECT 1
        FROM specialist_cards sc
        WHERE sc.specialist_id = ${users.id}
          AND sc.is_active = true
          AND sc.publication_status = 'active'
          AND cardinality(coalesce(sc.categories, ARRAY[]::text[])) > 0
          AND cardinality(coalesce(sc.locations, ARRAY[]::text[])) > 0
      )
    )
  `
  const conditions = [eq(users.isActive, true), helperReadyCondition]
  if (q) {
    const nameCondition = or(
      ilike(users.name, `%${q}%`),
      ilike(users.firstName, `%${q}%`),
      ilike(users.lastName, `%${q}%`),
    )!
    // Also search in active portfolio cards: title, description, skills
    const cardMatches = await db
      .selectDistinct({ specialistId: specialistCards.specialistId })
      .from(specialistCards)
      .where(
        and(
          eq(specialistCards.isActive, true),
          eq(specialistCards.publicationStatus, 'active'),
          or(
            ilike(specialistCards.title, `%${q}%`),
            ilike(specialistCards.description, `%${q}%`),
            sql`EXISTS (SELECT 1 FROM unnest(${specialistCards.skills}) AS s WHERE s ILIKE ${`%${q}%`})`,
          )!,
        ),
      )
    const cardMatchIds = cardMatches.map((r) => r.specialistId)
    conditions.push(
      cardMatchIds.length > 0
        ? or(nameCondition, inArray(users.id, cardMatchIds))!
        : nameCondition,
    )
  }
  if (filteredIds !== null) {
    conditions.push(inArray(users.id, filteredIds))
  }
  if (filterLanguages.length > 0) {
    // Specialist must speak AT LEAST ONE of the selected languages (OR semantics)
    conditions.push(
      or(...filterLanguages.map((lang) => sql`${users.languages} @> ARRAY[${lang}]::text[]`))!,
    )
  }

const where = and(...conditions)

const [rows, [{ total }]] = await Promise.all([
    db
      .select({
        id: users.id,
        role: users.role,
        name: users.name,
        firstName: users.firstName,
        lastName: users.lastName,
        siteSettings: users.siteSettings,
        image: users.image,
        bio: users.bio,
        bioEl: users.bioEl,
        bioEn: users.bioEn,
        bioRu: users.bioRu,
        bioUk: users.bioUk,
        skills: users.skills,
        createdAt: users.createdAt,
        lastSeenAt: users.lastSeenAt,
        personalSiteSlug: users.personalSiteSlug,
        searchBoost: sql<number>`COALESCE(${plans.searchBoost}, 0)`.as('search_boost'),
      })
      .from(users)
      .leftJoin(plans, eq(users.planId, plans.id))
      .where(where)
      .orderBy(sql`COALESCE(${plans.searchBoost}, 0) DESC`, desc(users.createdAt))
      .limit(limit)
      .offset(offset),
    db.select({ total: count() }).from(users).where(where),
  ])

// Fetch ratings for all specialists in the page
  const ids = rows.map((r) => r.id)
  const ratings =
    ids.length > 0
      ? await Promise.all(
          ids.map(async (id) => {
            const [result] = await db
              .select({ avg: avg(reviews.rating), total: count(), positive: count(sql`CASE WHEN ${reviews.rating} >= 4 THEN 1 END`) })
              .from(reviews)
              .where(eq(reviews.targetId, id))
            return { id, avg: result?.avg ?? null, total: result?.total ?? 0, positive: result?.positive ?? 0 }
          }),
        )
      : []
  const ratingMap = Object.fromEntries(ratings.map((r) => [r.id, r]))

const data = rows.map((u) => {
    const r = ratingMap[u.id]
    const positivePercent = r && r.total > 0 ? Math.round((r.positive / r.total) * 100) : null
    const localizedBio = pickLocalizedBio(u, locale)
    const showFullLastName = (u.siteSettings?.showFullLastName ?? false) === true
    const profileBackgroundImage = (u.siteSettings as any)?.profileBackgroundImage ?? null
    const maskedLastName = showFullLastName
      ? u.lastName
      : (u.lastName ? `${u.lastName[0]}.` : u.lastName)
    return {
      ...u,
      lastName: maskedLastName,
      bio: localizedBio,
      rating: r?.avg ?? null,
      reviewCount: r?.total ?? 0,
      positivePercent,
      // Keep only the background image setting public for list cards.
      siteSettings: profileBackgroundImage ? { profileBackgroundImage } : undefined,
    }
  }).map(({ bioEl, bioEn, bioRu, bioUk, ...u }) => u)

// Fetch active specialist cards for all specialists in the page
  const allCards =
    ids.length > 0
      ? await db
          .select({
            id: specialistCards.id,
            specialistId: specialistCards.specialistId,
            title: specialistCards.title,
            skills: specialistCards.skills,
            categories: specialistCards.categories,
            locations: specialistCards.locations,
            order: specialistCards.order,
          })
          .from(specialistCards)
          .where(and(
            eq(specialistCards.isActive, true),
            eq(specialistCards.publicationStatus, 'active'),
            inArray(specialistCards.specialistId, ids),
          ))
          .orderBy(asc(specialistCards.order), asc(specialistCards.createdAt))
      : []

// Attach card summary to each specialist
  const cardsBySpecialist = new Map<string, typeof allCards>()
  for (const c of allCards) {
    const arr = cardsBySpecialist.get(c.specialistId) ?? []
    arr.push(c)
    cardsBySpecialist.set(c.specialistId, arr)
  }

// Fetch today's availability for all specialists
  const today = new Date().toISOString().slice(0, 10)
  const todayAvailabilityRows =
    ids.length > 0
      ? await db
          .select({ specialistId: specialistAvailability.specialistId, status: specialistAvailability.status })
          .from(specialistAvailability)
          .where(and(inArray(specialistAvailability.specialistId, ids), eq(specialistAvailability.date, today)))
      : []
  const availabilityMap = Object.fromEntries(todayAvailabilityRows.map((r) => [r.specialistId, r.status]))

// Fetch active user statuses (canhelp_now / need_help) and verified badges
  const now = new Date()
  const [activeStatuses, userPlans] = await Promise.all([
    ids.length > 0
      ? db
          .select({
            userId: userStatuses.userId,
            statusType: userStatuses.statusType,
            expiresAt: userStatuses.expiresAt,
          })
          .from(userStatuses)
          .where(and(inArray(userStatuses.userId, ids), eq(userStatuses.isActive, true)))
      : Promise.resolve([]),
    ids.length > 0
      ? db
          .select({
            id: users.id,
            planId: users.planId,
          })
          .from(users)
          .where(inArray(users.id, ids))
      : Promise.resolve([]),
  ])

// Build status map
  const statusMap = new Map<string, string[]>()
  for (const s of activeStatuses) {
    if (s.expiresAt && s.expiresAt < now) continue
    const arr = statusMap.get(s.userId) ?? []
    arr.push(s.statusType)
    statusMap.set(s.userId, arr)
  }

// Build badge map (hasVerifiedBadge from plan)
  const planIds = [...new Set(userPlans.map((u) => u.planId).filter(Boolean))] as string[]
  const planRows = planIds.length > 0
    ? await db.select({ id: plans.id, hasVerifiedBadge: plans.hasVerifiedBadge, tier: plans.tier, searchBoost: plans.searchBoost, highlightedReviews: plans.highlightedReviews }).from(plans).where(inArray(plans.id, planIds))
    : []
  const planMap = Object.fromEntries(planRows.map((p) => [p.id, p]))
  const userPlanMap = Object.fromEntries(userPlans.map((u) => [u.id, u.planId]))

const enrichedData = data.map((u) => {
    const cards = (cardsBySpecialist.get(u.id) ?? []).filter((c) => ids.includes(c.specialistId))
    const allSkills = [...new Set(cards.flatMap((c) => c.skills ?? []))]
    const allCategories = [...new Set(cards.flatMap((c) => c.categories ?? []))]
    const allLocations = [...new Set(cards.flatMap((c) => c.locations ?? []))]
    const uPlanId = userPlanMap[u.id]
    const uPlan = uPlanId ? planMap[uPlanId] : null
    return {
      ...u,
      role: toClientRole((u as any).role ?? 'user', true),
      isHelperReady: true,
      legacyMarketRole: 'specialist',
      todayStatus: (availabilityMap[u.id] ?? null) as 'available' | 'busy' | null,
      cards: cards.map((c) => ({ id: c.id, title: c.title })),
      cardSkills: allSkills,
      cardCategories: allCategories,
      cardLocations: allLocations,
      activeStatuses: statusMap.get(u.id) ?? [],
      hasVerifiedBadge: !!uPlan?.hasVerifiedBadge,
      highlightedReviews: !!uPlan?.highlightedReviews,
      planTier: uPlan?.tier ?? 'free',
    }
  })

return c.json({ data: enrichedData, total, page, limit })
})

// GET /users/me/site-stats — site analytics for the current user (last 30 days)
app.get('/me/site-stats', requireAuth, async (c) => {
  const authUser = c.get('user')
  const since = new Date()
  since.setDate(since.getDate() - 29)
  const sinceDate = since.toISOString().slice(0, 10)

// Daily views + unique visitors
  const viewRows = await db
    .select({
      date: sitePageViews.date,
      views: count(),
      unique: countDistinct(sitePageViews.visitorFingerprint),
    })
    .from(sitePageViews)
    .where(and(eq(sitePageViews.siteOwnerId, authUser.id), gte(sitePageViews.date, sinceDate)))
    .groupBy(sitePageViews.date)
    .orderBy(asc(sitePageViews.date))

// Daily messages
  const msgRows = await db
    .select({
      date: siteContactLogs.date,
      messages: count(),
    })
    .from(siteContactLogs)
    .where(and(eq(siteContactLogs.siteOwnerId, authUser.id), gte(siteContactLogs.date, sinceDate)))
    .groupBy(siteContactLogs.date)
    .orderBy(asc(siteContactLogs.date))

// Build full 30-day array
  const msgMap = Object.fromEntries(msgRows.map((r) => [r.date, Number(r.messages)]))
  const days: { date: string; views: number; unique: number; messages: number }[] = []
  for (let i = 0; i < 30; i++) {
    const d = new Date(since)
    d.setDate(d.getDate() + i)
    const dateStr = d.toISOString().slice(0, 10)
    const vr = viewRows.find((r) => r.date === dateStr)
    days.push({
      date: dateStr,
      views: vr ? Number(vr.views) : 0,
      unique: vr ? Number(vr.unique) : 0,
      messages: msgMap[dateStr] ?? 0,
    })
  }

return c.json({ days })
})

// GET /users/site/:slug — public personal site for specialists with Ultimate plan
app.get('/site/:slug', async (c) => {
  const slug = c.req.param('slug')
  const [user] = await db
    .select()
    .from(users)
    .where(and(eq(users.personalSiteSlug, slug), eq(users.isActive, true)))
  if (!user) return c.json({ error: 'Not found' }, 404)

// Verify the user's plan includes personal site
  let planTier = 'free'
  let hasVerifiedBadge = false
  let hasPersonalSite = false
  if (user.planId) {
    const [plan] = await db
      .select({ tier: plans.tier, hasVerifiedBadge: plans.hasVerifiedBadge, hasPersonalSite: plans.hasPersonalSite })
      .from(plans)
      .where(eq(plans.id, user.planId))
    if (plan) {
      planTier = plan.tier ?? 'free'
      hasVerifiedBadge = !!plan.hasVerifiedBadge
      hasPersonalSite = !!plan.hasPersonalSite
    }
  }

if (!hasPersonalSite) return c.json({ error: 'Not found' }, 404)

// Track page view (fire & forget — don't block response)
  const ip = c.req.header('x-forwarded-for')?.split(',')[0]?.trim() ?? c.req.header('x-real-ip') ?? 'unknown'
  const ua = c.req.header('user-agent') ?? ''
  const fingerprint = createHash('sha256').update(ip + ua).digest('hex').slice(0, 16)
  const today = new Date().toISOString().slice(0, 10)
  db.insert(sitePageViews).values({ siteOwnerId: user.id, visitorFingerprint: fingerprint, date: today }).execute().catch(() => {})

// Load specialist cards
  const cards = await db
    .select({
      id: specialistCards.id,
      title: specialistCards.title,
      description: specialistCards.description,
      descriptionEl: specialistCards.descriptionEl,
      descriptionEn: specialistCards.descriptionEn,
      descriptionRu: specialistCards.descriptionRu,
      descriptionUk: specialistCards.descriptionUk,
      skills: specialistCards.skills,
      categories: specialistCards.categories,
      locations: specialistCards.locations,
    })
    .from(specialistCards)
    .where(and(eq(specialistCards.specialistId, user.id), eq(specialistCards.isActive, true)))
    .orderBy(asc(specialistCards.order))

// Load portfolio
  const portfolio = await db
    .select({
      id: portfolioItems.id,
      imageUrl: portfolioItems.imageUrl,
      title: portfolioItems.title,
      description: portfolioItems.description,
    })
    .from(portfolioItems)
    .where(eq(portfolioItems.specialistId, user.id))
    .orderBy(asc(portfolioItems.order))

// Load reviews
  const reviewRows = await db
    .select({
      id: reviews.id,
      rating: reviews.rating,
      comment: reviews.comment,
      createdAt: reviews.createdAt,
      authorName: users.name,
      authorFirstName: users.firstName,
      authorImage: users.image,
    })
    .from(reviews)
    .leftJoin(users, eq(reviews.authorId, users.id))
    .where(eq(reviews.targetId, user.id))
    .orderBy(desc(reviews.createdAt))
    .limit(20)

// Rating summary
  const [ratingAgg] = await db
    .select({ avg: avg(reviews.rating), total: count() })
    .from(reviews)
    .where(eq(reviews.targetId, user.id))

// Active statuses
  const now = new Date()
  const statusRows = await db
    .select({ statusType: userStatuses.statusType, expiresAt: userStatuses.expiresAt })
    .from(userStatuses)
    .where(and(eq(userStatuses.userId, user.id), eq(userStatuses.isActive, true)))
  const activeStatuses = statusRows
    .filter((s) => !s.expiresAt || s.expiresAt > now)
    .map((s) => s.statusType)

// Count completed tasks (where specialist's offer was accepted)
  const [completedAgg] = await db
    .select({ total: count() })
    .from(offers)
    .innerJoin(tasks, eq(offers.taskId, tasks.id))
    .where(and(eq(offers.specialistId, user.id), eq(offers.status, 'accepted'), eq(tasks.status, 'completed')))

const ss = (user.siteSettings ?? {}) as Record<string, boolean | undefined>
  const show = (key: string) => ss[key] !== false  // undefined → show by default
  const showServices = show('showServices')
  const showPriceList = show('showPriceList')

return c.json({
    id: user.id,
    name: user.name,
    firstName: user.firstName,
    lastName: show('showFullLastName') ? user.lastName : (user.lastName ? user.lastName[0] + '.' : null),
    image: user.image,
    bio: show('showBio') ? user.bio : undefined,
    slug: user.personalSiteSlug,
    phone: show('showPhone') ? user.phone : undefined,
    siteSettings: ss,
    planTier,
    hasVerifiedBadge,
    activeStatuses: show('showStatus') ? activeStatuses : [],
    cards: showServices || showPriceList ? cards : [],
    portfolio: show('showPortfolio') ? portfolio : [],
    reviews: show('showReviews') ? reviewRows : [],
    rating: show('showRating') ? (ratingAgg?.avg ? Number(ratingAgg.avg) : null) : null,
    reviewCount: show('showRating') ? (ratingAgg?.total ?? 0) : 0,
    languages: user.languages ?? [],
    memberSince: user.createdAt,
    completedTasks: completedAgg?.total ?? 0,
  })
})

// POST /users/site/:slug/contact — contact form from personal site
app.post('/site/:slug/contact', zValidator('json', z.object({
  name: z.string().min(1).max(200),
  email: z.string().email().max(200).optional().or(z.literal('')),
  message: z.string().min(1).max(2000),
})), async (c) => {
  const slug = c.req.param('slug')
  const { name, email, message } = c.req.valid('json')

const [user] = await db
    .select({ id: users.id, email: users.email, firstName: users.firstName, personalSiteSlug: users.personalSiteSlug })
    .from(users)
    .where(and(eq(users.personalSiteSlug, slug), eq(users.isActive, true)))
  if (!user) return c.json({ error: 'Not found' }, 404)

// Send notification email to the specialist
  try {
    const { emailSiteContact } = await import('../lib/email.js')
    await emailSiteContact({
      to: user.email,
      specialistName: user.firstName ?? '',
      senderName: name,
      senderEmail: email || undefined,
      message,
    })
  } catch (err) {
    console.error('[Site Contact] email error:', err)
  }

// Track contact message (fire & forget)
  const msgDate = new Date().toISOString().slice(0, 10)
  db.insert(siteContactLogs).values({ siteOwnerId: user.id, date: msgDate }).execute().catch(() => {})

return c.json({ ok: true })
})

// POST /users/site/:slug/track-message — track direct message sent from personal site (authenticated)
app.post('/site/:slug/track-message', async (c) => {
  const slug = c.req.param('slug')
  const [user] = await db
    .select({ id: users.id })
    .from(users)
    .where(and(eq(users.personalSiteSlug, slug), eq(users.isActive, true)))
  if (!user) return c.json({ ok: false }, 404)
  const date = new Date().toISOString().slice(0, 10)
  db.insert(siteContactLogs).values({ siteOwnerId: user.id, date }).execute().catch(() => {})
  return c.json({ ok: true })
})

// GET /users/:id/profile (public, with optional auth for phone visibility)
app.get('/:id/profile', optionalAuth, async (c) => {
  const [dbUser] = await db.select().from(users).where(eq(users.id, c.req.param('id')))
  const viewer = c.get('user') as any
  const profileLocale = normalizeLocale(c.req.query('locale') ?? viewer?.locale ?? dbUser?.locale)
  const user = dbUser ? { ...dbUser, bio: pickLocalizedBio(dbUser, profileLocale) } : null
  if (!user || !user.isActive) return c.json({ error: 'Not found' }, 404)

// Strip sensitive fields
  const {
    balance,
    referralCode,
    referredBy,
    notifyNewTasks,
    notifMessages,
    planExpiresAt,
    ...profile
  } = user

// Phone visibility: only show if user opted in AND viewer's plan allows it
  let showPhone = false
  
  if (viewer) {
    if (viewer.id === user.id || viewer.role === 'admin') {
      showPhone = true
    } else if (user.showContactInfo && user.phone) {
      // Check viewer's plan canViewPhone
      const [viewerUser] = await db.select({ planId: users.planId }).from(users).where(eq(users.id, viewer.id))
      if (viewerUser?.planId) {
        const [viewerPlan] = await db.select().from(plans).where(eq(plans.id, viewerUser.planId))
        showPhone = !!viewerPlan?.canViewPhone
      }
    }
  }

// Fetch verified badge + statuses
  let hasVerifiedBadge = false
  let planTier = 'free'
  if (user.planId) {
    const [plan] = await db
      .select({ hasVerifiedBadge: plans.hasVerifiedBadge, tier: plans.tier })
      .from(plans)
      .where(eq(plans.id, user.planId))
    if (plan) {
      hasVerifiedBadge = !!plan.hasVerifiedBadge
      planTier = plan.tier ?? 'free'
    }
  }

const now = new Date()
  const statusRows = await db
    .select({ statusType: userStatuses.statusType, expiresAt: userStatuses.expiresAt })
    .from(userStatuses)
    .where(and(eq(userStatuses.userId, user.id), eq(userStatuses.isActive, true)))
  const activeStatuses = statusRows
    .filter((s) => !s.expiresAt || s.expiresAt > now)
    .map((s) => s.statusType)

const isHelperReady = await getHelperCapability(user.id)
  return c.json({
    ...profile,
    role: toClientRole(profile.role, isHelperReady),
    isHelperReady,
    legacyMarketRole: isHelperReady ? 'specialist' : 'customer',
    email: viewer?.id === user.id || viewer?.role === 'admin' ? profile.email : undefined,
    phone: showPhone ? profile.phone : undefined,
    hasVerifiedBadge,
    planTier,
    activeStatuses,
  })
})

// ─── POST /users/me/site-seo ─────────────────────────────────────────────
app.post(
  '/me/site-seo',
  requireAuth,
  zValidator('json', z.object({
    seoTitle: z.string().max(200).optional(),
    seoDescription: z.string().max(500).optional(),
    locale: z.enum(['el', 'en', 'ru', 'uk']),
  })),
  async (c) => {
    const authUser = c.get('user')
    const { seoTitle, seoDescription, locale } = c.req.valid('json')

const [dbUser] = await db.select({ siteSettings: users.siteSettings }).from(users).where(eq(users.id, authUser.id))
    const cap = locale.charAt(0).toUpperCase() + locale.slice(1)
    const current = (dbUser?.siteSettings ?? {}) as Record<string, any>
    const updated: Record<string, any> = { ...current }
    if (seoTitle !== undefined) updated[`seoTitle${cap}`] = seoTitle
    if (seoDescription !== undefined) updated[`seoDesc${cap}`] = seoDescription

await db.update(users).set({ siteSettings: updated }).where(eq(users.id, authUser.id))

// Background translation to other locales
    const targets = (['el', 'en', 'ru', 'uk'] as const).filter((l) => l !== locale)
    Promise.allSettled(
      targets.flatMap((to) => {
        const toCap = to.charAt(0).toUpperCase() + to.slice(1)
        const jobs: Promise<void>[] = []
        if (seoTitle) {
          jobs.push(
            translateString(seoTitle, locale, to).then(async (translated) => {
              if (!translated) return
              const [cur] = await db.select({ siteSettings: users.siteSettings }).from(users).where(eq(users.id, authUser.id))
              const merged = { ...(cur?.siteSettings ?? {}), [`seoTitle${toCap}`]: translated }
              await db.update(users).set({ siteSettings: merged }).where(eq(users.id, authUser.id))
            })
          )
        }
        if (seoDescription) {
          jobs.push(
            translateString(seoDescription, locale, to).then(async (translated) => {
              if (!translated) return
              const [cur] = await db.select({ siteSettings: users.siteSettings }).from(users).where(eq(users.id, authUser.id))
              const merged = { ...(cur?.siteSettings ?? {}), [`seoDesc${toCap}`]: translated }
              await db.update(users).set({ siteSettings: merged }).where(eq(users.id, authUser.id))
            })
          )
        }
        return jobs
      }),
    ).catch((e) => console.error('[seo-translate]', e))

return c.json({ ok: true })
  },
)

export default app

cmd: