game.js
12.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
import { randomUUID } from 'node:crypto'
import { levelForXp, weekStart } from './level.js'
import { insertEvent } from './tasks.js'
const EARNING_REASONS = Object.freeze(['vk_task_payout', 'vk_relay_share'])
const DAY_MS = 86400_000
/**
* 结算后的成长结算，在结算事务里调用：收款人升级、接力称号、公会周目标。
* @param {import('knex').Knex} trx
* @param {{ id: string, guild_id: string }} task
* @param {string[]} recipient_agent_ids 本次结算的全部收款人（多于 1 人即发生了接力）
* @param {Date} now
*/
export async function growAfterSettle(trx, task, recipient_agent_ids, now) {
for (const agent_id of recipient_agent_ids) {
const agent = await trx('vk_agents').where({ id: agent_id }).first('xp', 'level')
const level = levelForXp(agent.xp)
if (level > agent.level) {
await trx('vk_agents').where({ id: agent_id }).update({ level })
await insertEvent(trx, task, 'agent.level_up', agent_id, now)
}
if (recipient_agent_ids.length > 1) await awardTitle(trx, task, agent_id, 'first_relay', '', now)
}
const guild = await trx('vk_guilds').where({ id: task.guild_id }).forUpdate().first('weekly_goal', 'goal_reached_week')
const week = weekStart(now).toISOString().slice(0, 10)
if (guild.weekly_goal > 0 && guild.goal_reached_week !== week && (await weeklyProgress(trx, task.guild_id, now)) >= guild.weekly_goal) {
await trx('vk_guilds').where({ id: task.guild_id }).update({ goal_reached_week: week, updated_at: now }).increment('level', 1)
await insertEvent(trx, task, 'guild.goal_progress', null, now)
}
}
/**
* 发称号；同一 Agent 同一称号同一周期只发一次（UNIQUE 裁决），重复时不再发事件。
* @param {import('knex').Knex} trx
* @param {{ id: string|null, guild_id: string }} task 事件挂靠的任务；与具体任务无关的称号 id 为 null
* @param {string} agent_id
* @param {'first_relay'|'streak_7'|'weekly_mvp'} title
* @param {string} period_key 一次性称号为空串；周称号为该周周一的日期
* @param {Date} now
*/
async function awardTitle(trx, task, agent_id, title, period_key, now) {
if ((await trx('vk_agent_titles').where({ agent_id, title, period_key }).first('id')) !== undefined) return
await trx('vk_agent_titles').insert({ id: `ti_${randomUUID()}`, agent_id, title, period_key, awarded_at: now })
await insertEvent(trx, task, 'agent.title_awarded', agent_id, now)
}
/**
* 公会本周已结算任务的赏金之和。
* @param {import('knex').Knex} runner
* @param {string} guild_id
* @param {Date} now
* @returns {Promise<number>}
*/
async function weeklyProgress(runner, guild_id, now) {
const row = await runner('vk_tasks').where({ guild_id, status: 'settled' }).where('settled_at', '>=', weekStart(now)).sum({ points: 'reward' }).first()
return Number(row.points)
}
/**
* 游戏层的只读视图与每日结算：排行榜、公会城状态与动态、称号、日报数据。全部来自业务表与事件表，不另存一份。
* @param {{ db: import('knex').Knex }} deps
*/
export function createGame({ db }) {
if (typeof db !== 'function') throw new Error('createGame: db is required')
/**
* 各 Agent 在时间段内靠任务赚到的积分（任务收入 + 接力分成），与账本流水一致。
* @param {{ guild_id: string|null, since: Date|null, until: Date|null }} filter
*/
function earnings({ guild_id, since, until }) {
const query = db('billing_ledger as l')
.join('vk_agents as a', 'a.wallet_user_id', 'l.user_id')
.join('vk_owner_handles as h', 'h.user_id', 'a.owner_user_id')
.where('l.source', 'voko')
.whereIn('l.reason', EARNING_REASONS)
.groupBy('a.id', 'a.name', 'h.handle', 'a.level', 'a.guild_id')
.select('a.id as agent_id', db.raw("CONCAT(a.name, '@', h.handle) as name"), 'a.level', 'a.guild_id', db.raw('SUM(l.amount) as points'))
if (guild_id !== null) query.where('a.guild_id', guild_id)
if (since !== null) query.where('l.created_at', '>=', since)
if (until !== null) query.where('l.created_at', '<', until)
return query
}
/**
* @param {object} _ctx
* @param {{ agent: import('./agents.js').Agent|null, scope: 'guild'|'world', period: 'week'|'all' }} input 公开小镇页没有 agent，只能看世界榜
* @returns {Promise<Array<{ rank: number, agent_id: string, name: string, level: number, points: number }>>}
*/
async function leaderboard(_ctx, { agent, scope, period }) {
if (scope === 'guild' && agent === null) throw new Error('leaderboard: guild scope requires an agent')
const rows = await earnings({ guild_id: agent !== null && scope === 'guild' ? agent.guild_id : null, since: period === 'week' ? weekStart(new Date()) : null, until: null }).orderBy([{ column: 'points', order: 'desc' }, { column: 'a.id', order: 'asc' }]).limit(50)
return rows.map((row, index) => ({ rank: index + 1, agent_id: row.agent_id, name: row.name, level: row.level, points: Number(row.points) }))
}
/**
* 公会城当前状态：各 Agent 的状态（求助中 / 工作中 / 空闲）与称号；指定公会时还有公会等级与本周目标进度。
* `guild_id` 为 null 表示整个小镇（全部公会，公开页面用）。
* @param {object} _ctx
* @param {{ guild_id: string|null }} input
*/
async function worldState(_ctx, { guild_id }) {
const now = new Date()
const inGuild = (/** @type {import('knex').Knex.QueryBuilder} */ query, /** @type {string} */ column) => (guild_id === null ? query : query.where(column, guild_id))
const [guild, agents, working, asking, titles] = await Promise.all([
guild_id === null ? null : db('vk_guilds').where({ id: guild_id }).first('id', 'name', 'level', 'weekly_goal'),
inGuild(db('vk_agents as a').join('vk_guilds as g', 'g.id', 'a.guild_id').join('vk_owner_handles as h', 'h.user_id', 'a.owner_user_id').where('a.status', 'active'), 'a.guild_id').orderBy('a.created_at', 'asc').select('a.id', db.raw("CONCAT(a.name, '@', h.handle) as name"), 'a.role', 'a.level', 'a.xp', 'g.name as guild_name'),
inGuild(db('vk_tasks'), 'guild_id').whereIn('status', ['claimed', 'rework']).select('holder_agent_id'),
inGuild(db('vk_help_requests as h').join('vk_tasks as t', 't.id', 'h.task_id').where('h.status', 'open'), 't.guild_id').select('h.agent_id'),
inGuild(db('vk_agent_titles as ti').join('vk_agents as a', 'a.id', 'ti.agent_id'), 'a.guild_id').select('ti.agent_id', 'ti.title'),
])
if (guild === undefined) throw new Error(`vk_guilds row missing, id: ${guild_id}`)
const busy = new Set(working.map((row) => row.holder_agent_id))
const stuck = new Set(asking.map((row) => row.agent_id))
return {
guild: guild === null ? null : { id: guild.id, name: guild.name, level: guild.level, weekly_goal: guild.weekly_goal, weekly_progress: await weeklyProgress(db, /** @type {string} */ (guild_id), now) },
agents: agents.map((agent) => ({
...agent,
state: stuck.has(agent.id) ? 'help' : busy.has(agent.id) ? 'working' : 'idle',
titles: [...new Set(titles.filter((row) => row.agent_id === agent.id).map((row) => row.title))],
})),
}
}
/**
* 动态流：同一时刻发给多方的同一事件只出现一次；带任务标题与当事 Agent，供前端翻译成动画。
* `guild_id` 为 null 表示整个小镇。
* @param {object} _ctx
* @param {{ guild_id: string|null, since: number }} input
*/
async function worldFeed(_ctx, { guild_id, since }) {
const query = db('vk_events as e')
.leftJoin('vk_tasks as t', 't.id', 'e.task_id')
.where('e.id', '>', since)
.groupBy('e.type', 'e.task_id', 'e.created_at', 't.title', 't.reward', 't.rarity', 't.holder_agent_id', 't.publisher_agent_id')
.orderBy('id', 'asc')
.limit(100)
.select(db.raw('MIN(e.id) as id'), 'e.type', 'e.task_id', 'e.created_at', db.raw('MIN(e.target_agent_id) as target_agent_id'), 't.title', 't.reward', 't.rarity', 't.holder_agent_id', 't.publisher_agent_id')
if (guild_id !== null) query.where('e.guild_id', guild_id)
return (await query).map((event) => ({ ...event, id: Number(event.id) }))
}
/**
* 每日称号结算：连续 7 天有任务结算（streak_7）；上一周各公会赚得最多的 Agent（weekly_mvp）。重复执行无害。
* @param {object} _ctx
* @returns {Promise<{ streak_7: number, weekly_mvp: number }>}
*/
async function runDaily(_ctx) {
const now = new Date()
const today = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()))
const streakers = await db('vk_escrow_payouts as p')
.join('vk_agents as a', 'a.wallet_user_id', 'p.recipient_wallet')
.whereIn('p.kind', ['winner', 'relay_share'])
.where('p.created_at', '>=', new Date(today.getTime() - 6 * DAY_MS))
.groupBy('a.id', 'a.guild_id')
.havingRaw('COUNT(DISTINCT DATE(p.created_at)) >= 7')
.select('a.id as agent_id', 'a.guild_id')
const thisWeek = weekStart(now)
const lastWeek = new Date(thisWeek.getTime() - 7 * DAY_MS)
const earned = await earnings({ guild_id: null, since: lastWeek, until: thisWeek }).orderBy([{ column: 'points', order: 'desc' }, { column: 'a.id', order: 'asc' }])
const mvps = new Map()
for (const row of earned) if (!mvps.has(row.guild_id)) mvps.set(row.guild_id, row)
await db.transaction(async (trx) => {
for (const row of streakers) await awardTitle(trx, { id: null, guild_id: row.guild_id }, row.agent_id, 'streak_7', '', now)
for (const row of mvps.values()) await awardTitle(trx, { id: null, guild_id: row.guild_id }, row.agent_id, 'weekly_mvp', lastWeek.toISOString().slice(0, 10), now)
})
return { streak_7: streakers.length, weekly_mvp: mvps.size }
}
/**
* 日报数据：主人名下各 Agent 自 `since` 以来的结算数与赚取积分、当前在手任务数、等你回复的求助数。
* @param {object} _ctx
* @param {{ owner_user_id: string, since: Date }} input
*/
async function digestFor(_ctx, { owner_user_id, since }) {
const agents = await db('vk_agents').where({ owner_user_id }).whereNot({ status: 'pending_claim' }).select('id', 'name', 'wallet_user_id', 'level')
const [earned, active, help] = await Promise.all([
db('billing_ledger').whereIn('user_id', agents.map((agent) => agent.wallet_user_id)).where({ source: 'voko' }).whereIn('reason', EARNING_REASONS).where('created_at', '>=', since).groupBy('user_id').select('user_id', db.raw('SUM(amount) as points'), db.raw('COUNT(*) as settled')),
db('vk_tasks').whereIn('holder_agent_id', agents.map((agent) => agent.id)).whereIn('status', ['claimed', 'rework', 'submitted']).groupBy('holder_agent_id').select('holder_agent_id', db.raw('COUNT(*) as n')),
db('vk_help_requests').where({ owner_user_id, status: 'open' }).count({ n: '*' }).first(),
])
return {
open_help: Number(help.n),
agents: agents.map((agent) => {
const income = earned.find((row) => row.user_id === agent.wallet_user_id)
const holding = active.find((row) => row.holder_agent_id === agent.id)
return { name: agent.name, level: agent.level, settled: income === undefined ? 0 : Number(income.settled), points: income === undefined ? 0 : Number(income.points), active_tasks: holding === undefined ? 0 : Number(holding.n) }
}),
}
}
/**
* 数据看板（PRD 指标）：完成任务数、赚取积分、平均交付周期、一次通过率，以及当前在途与争议数。
* @param {object} _ctx
*/
async function dashboard(_ctx) {
const [settled, earned, statuses, agents, disputes] = await Promise.all([
db('vk_tasks').where({ status: 'settled' }).select(db.raw('COUNT(*) as n'), db.raw('SUM(rework_count = 0) as first_pass'), db.raw('AVG(TIMESTAMPDIFF(SECOND, created_at, settled_at)) as avg_cycle_sec')).first(),
db('billing_ledger').where({ source: 'voko' }).whereIn('reason', EARNING_REASONS).sum({ points: 'amount' }).first(),
db('vk_tasks').groupBy('status').select('status', db.raw('COUNT(*) as n')),
db('vk_agents').groupBy('status').select('status', db.raw('COUNT(*) as n')),
db('vk_disputes').whereNot({ status: 'resolved' }).count({ n: '*' }).first(),
])
const settledCount = Number(settled.n)
return {
tasks_settled: settledCount,
points_earned: Number(earned.points),
avg_delivery_hours: settledCount === 0 ? null : Math.round(Number(settled.avg_cycle_sec) / 360) / 10,
first_pass_rate: settledCount === 0 ? null : Math.round((Number(settled.first_pass) / settledCount) * 1000) / 1000,
open_disputes: Number(disputes.n),
tasks_by_status: Object.fromEntries(statuses.map((row) => [row.status, Number(row.n)])),
agents_by_status: Object.fromEntries(agents.map((row) => [row.status, Number(row.n)])),
}
}
return { leaderboard, worldState, worldFeed, runDaily, digestFor, dashboard }
}