/opt/canhelp/apps/api/src/lib
Edit: /opt/canhelp/apps/api/src/lib/fomo.ts (11366B)
import { eq, and, sql, count, avg, gte } from 'drizzle-orm'
import { db } from '../db.js'
import {
taskViews,
taskStats,
missedOrders,
dailySpecialistStats,
users,
plans,
offers,
tasks,
specialistCards,
} from '@canhelp/db'
/** Get today's date in YYYY-MM-DD (Europe/Athens timezone) */
function todayDate(): string {
return new Date().toLocaleDateString('sv-SE', { timeZone: 'Europe/Athens' })
}
// ─── Task View Tracking ────────────────────────────────────────────────────
/** Record a task view by a user. Updates task_stats. */
export async function recordTaskView(taskId: string, userId: string): Promise<{ totalViews: number; proViews: number }> {
// Get viewer's plan tier
const [viewer] = await db
.select({ planId: users.planId })
.from(users)
.where(eq(users.id, userId))
let tier: string | null = null
if (viewer?.planId) {
const [plan] = await db.select({ tier: plans.tier }).from(plans).where(eq(plans.id, viewer.planId))
tier = plan?.tier ?? null
}
// Upsert task view (ignore duplicate)
await db
.insert(taskViews)
.values({ taskId, userId, userPlanTier: tier ?? 'free' })
.onConflictDoNothing()
// Update task_stats atomically
const isPro = tier === 'pro' || tier === 'ultimate'
// Upsert task_stats
await db.execute(sql`
INSERT INTO task_stats (id, task_id, total_views, pro_views, total_offers, updated_at)
VALUES (gen_random_uuid(), ${taskId}, 1, ${isPro ? 1 : 0}, 0, now())
ON CONFLICT (task_id) DO UPDATE SET
total_views = (SELECT count(*) FROM task_views WHERE task_id = ${taskId}),
pro_views = (SELECT count(*) FROM task_views WHERE task_id = ${taskId} AND user_plan_tier IN ('pro', 'ultimate')),
updated_at = now()
`)
// Return current counts
const [stats] = await db
.select({ totalViews: taskStats.totalViews, proViews: taskStats.proViews })
.from(taskStats)
.where(eq(taskStats.taskId, taskId))
return { totalViews: stats?.totalViews ?? 1, proViews: stats?.proViews ?? 0 }
}
// ─── Task Stats ────────────────────────────────────────────────────────────
/** Get full stats for a task (for the customer view) */
export async function getTaskStats(taskId: string) {
const [stats] = await db.select().from(taskStats).where(eq(taskStats.taskId, taskId))
// Count offers
const [offerCount] = await db
.select({ count: count() })
.from(offers)
.where(eq(offers.taskId, taskId))
// Average response time in minutes (time from task creation to offer creation)
const [task] = await db
.select({ createdAt: tasks.createdAt })
.from(tasks)
.where(eq(tasks.id, taskId))
let avgResponseMin: number | null = null
if (task) {
const [result] = await db
.select({
avg: sql
`EXTRACT(EPOCH FROM AVG(${offers.createdAt} - ${sql`${task.createdAt}::timestamp`})) / 60`,
})
.from(offers)
.where(eq(offers.taskId, taskId))
avgResponseMin = result?.avg ? Math.round(result.avg) : null
}
// Update stats with latest offer count
if (stats) {
await db
.update(taskStats)
.set({
totalOffers: offerCount?.count ?? 0,
avgResponseMin,
updatedAt: new Date(),
})
.where(eq(taskStats.taskId, taskId))
}
return {
totalViews: stats?.totalViews ?? 0,
proViews: stats?.proViews ?? 0,
totalOffers: offerCount?.count ?? 0,
avgResponseMin,
}
}
// ─── Missed Orders Tracking ────────────────────────────────────────────────
/**
* When a task gets an accepted offer, record missed orders for specialists
* who were relevant but didn't respond (or viewed but didn't offer).
*/
export async function recordMissedOrders(
taskId: string,
acceptedSpecialistId: string,
): Promise {
const date = todayDate()
const [task] = await db
.select({
id: tasks.id,
budget: tasks.budget,
category: tasks.category,
location: tasks.location,
})
.from(tasks)
.where(eq(tasks.id, taskId))
if (!task) return
// Estimate value from budget
const estimatedValue = task.budget ?? null
// Was the winner a PRO specialist?
const [winnerUser] = await db
.select({ planId: users.planId })
.from(users)
.where(eq(users.id, acceptedSpecialistId))
let takenByPro = false
if (winnerUser?.planId) {
const [plan] = await db
.select({ tier: plans.tier })
.from(plans)
.where(eq(plans.id, winnerUser.planId))
takenByPro = plan?.tier === 'pro' || plan?.tier === 'ultimate'
}
// Find relevant specialists who matched this task's category but didn't win
const categoryCondition = task.category
? sql`${specialistCards.categories} @> ARRAY[${task.category}]::text[]`
: sql`true`
const relevantSpecs = await db
.selectDistinct({ specialistId: specialistCards.specialistId })
.from(specialistCards)
.where(and(eq(specialistCards.isActive, true), categoryCondition))
const relevantIds = relevantSpecs
.map((r) => r.specialistId)
.filter((id) => id !== acceptedSpecialistId)
if (relevantIds.length === 0) return
// Check who viewed but didn't offer
const viewedSet = new Set()
if (relevantIds.length > 0) {
const views = await db
.select({ userId: taskViews.userId })
.from(taskViews)
.where(eq(taskViews.taskId, taskId))
views.forEach((v) => viewedSet.add(v.userId))
}
// Check who already made offers
const offeredSet = new Set()
const existingOffers = await db
.select({ specialistId: offers.specialistId })
.from(offers)
.where(eq(offers.taskId, taskId))
existingOffers.forEach((o) => offeredSet.add(o.specialistId))
// Record missed orders (batch insert)
const missedValues = relevantIds
.filter((id) => !offeredSet.has(id)) // didn't make an offer
.map((specialistId) => ({
specialistId,
taskId,
reason: viewedSet.has(specialistId) ? 'viewed_no_offer' : 'not_viewed',
estimatedValue,
takenByPro,
date,
}))
if (missedValues.length > 0) {
await db.insert(missedOrders).values(missedValues).onConflictDoNothing()
}
// Update daily specialist stats for each missed specialist
for (const m of missedValues) {
await db.execute(sql`
INSERT INTO daily_specialist_stats (id, specialist_id, date, missed_total, missed_by_pro, missed_revenue, hot_orders_missed)
VALUES (gen_random_uuid(), ${m.specialistId}, ${date}, 1, ${m.takenByPro ? 1 : 0}, ${Number(m.estimatedValue ?? 0)}, 0)
ON CONFLICT (specialist_id, date) DO UPDATE SET
missed_total = daily_specialist_stats.missed_total + 1,
missed_by_pro = daily_specialist_stats.missed_by_pro + ${m.takenByPro ? 1 : 0},
missed_revenue = daily_specialist_stats.missed_revenue + ${Number(m.estimatedValue ?? 0)}
`)
}
// Update daily stats for winner
await db.execute(sql`
INSERT INTO daily_specialist_stats (id, specialist_id, date, received_orders)
VALUES (gen_random_uuid(), ${acceptedSpecialistId}, ${date}, 1)
ON CONFLICT (specialist_id, date) DO UPDATE SET
received_orders = daily_specialist_stats.received_orders + 1
`)
}
// ─── Specialist FOMO Dashboard ─────────────────────────────────────────────
export interface FomoDashboard {
today: {
missedTotal: number
missedByPro: number
missedRevenue: string
receivedOrders: number
totalAvailable: number
hotOrdersMissed: number
coveragePercent: number // % of available orders received
}
week: {
missedTotal: number
missedRevenue: string
receivedOrders: number
}
recentMissed: Array<{
taskId: string
reason: string | null
estimatedValue: string | null
takenByPro: boolean
date: string
}>
}
export async function getSpecialistFomoDashboard(specialistId: string): Promise {
const date = todayDate()
// Today's stats
const [todayStats] = await db
.select()
.from(dailySpecialistStats)
.where(and(eq(dailySpecialistStats.specialistId, specialistId), eq(dailySpecialistStats.date, date)))
// Week stats (last 7 days)
const weekAgo = new Date()
weekAgo.setDate(weekAgo.getDate() - 7)
const weekDate = weekAgo.toLocaleDateString('sv-SE', { timeZone: 'Europe/Athens' })
const [weekAgg] = await db
.select({
missedTotal: sql`COALESCE(SUM(${dailySpecialistStats.missedTotal}), 0)`,
missedRevenue: sql`COALESCE(SUM(${dailySpecialistStats.missedRevenue}), 0)`,
receivedOrders: sql`COALESCE(SUM(${dailySpecialistStats.receivedOrders}), 0)`,
})
.from(dailySpecialistStats)
.where(
and(
eq(dailySpecialistStats.specialistId, specialistId),
gte(dailySpecialistStats.date, weekDate),
),
)
// Recent missed orders (last 10)
const recentMissed = await db
.select({
taskId: missedOrders.taskId,
reason: missedOrders.reason,
estimatedValue: missedOrders.estimatedValue,
takenByPro: missedOrders.takenByPro,
date: missedOrders.date,
})
.from(missedOrders)
.where(eq(missedOrders.specialistId, specialistId))
.orderBy(sql`${missedOrders.createdAt} DESC`)
.limit(10)
const missed = todayStats?.missedTotal ?? 0
const received = todayStats?.receivedOrders ?? 0
const total = missed + received
const coveragePercent = total > 0 ? Math.round((received / total) * 100) : 100
return {
today: {
missedTotal: missed,
missedByPro: todayStats?.missedByPro ?? 0,
missedRevenue: todayStats?.missedRevenue ?? '0',
receivedOrders: received,
totalAvailable: total,
hotOrdersMissed: todayStats?.hotOrdersMissed ?? 0,
coveragePercent,
},
week: {
missedTotal: Number(weekAgg?.missedTotal ?? 0),
missedRevenue: String(weekAgg?.missedRevenue ?? '0'),
receivedOrders: Number(weekAgg?.receivedOrders ?? 0),
},
recentMissed,
}
}
// ─── Customer Conversion Dashboard ─────────────────────────────────────────
export interface CustomerDashboard {
activeTasks: Array<{
taskId: string
title: string
totalViews: number
proViews: number
totalOffers: number
avgResponseMin: number | null
}>
}
export async function getCustomerDashboard(customerId: string): Promise {
// Get active tasks
const activeTasks = await db
.select({ id: tasks.id, title: tasks.title })
.from(tasks)
.where(and(eq(tasks.customerId, customerId), eq(tasks.status, 'open')))
.orderBy(sql`${tasks.createdAt} DESC`)
.limit(20)
const result = await Promise.all(
activeTasks.map(async (t) => {
const stats = await getTaskStats(t.id)
return {
taskId: t.id,
title: t.title,
...stats,
}
}),
)
return { activeTasks: result }
}