[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_107":108,"_109":110},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","kubeniz","name","apexGemTracker","path","site.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":58,"_59":32,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/kubeniz/apexGemTracker/code/site.ts","extension","id","c279e172-73b2-40c9-af83-f2d5e498d969","fileId","ef09917e-9d89-11f1-8a53-1607ee4eb77e","parentId","branchId","3439b816-bce6-43b4-8219-2a89c70ea329","branchVersion",202,"latest",true,"createdAt",["D",1787585086322],"version",26,"fileType","http","content","// site.ts — Apex Scout: live dashboard + JSON API for apexGemTracker.\n// Serves the UI at / and reads the listings/retired tables at /api/*.\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\nimport PAGE from \"./page.ts\";\n\nconst GRADES = [\n \"D-\", \"D\", \"D+\", \"C-\", \"C\", \"C+\", \"B-\", \"B\", \"B+\", \"A-\", \"A\", \"A+\", \"S-\", \"S\", \"S+\"\n];\nconst AMINUS = GRADES.indexOf(\"A-\");\nconst SMINUS = GRADES.indexOf(\"S-\");\nconst ELITE = GRADES.slice(AMINUS).map((g) => `'${g}'`).join(\",\");\nconst SUPER_ELITE = GRADES.slice(SMINUS).map((g) => `'${g}'`).join(\",\");\n\n// Fast unowned check on listings\nconst LISTINGS_UNOWNED = `(\n player_json IS NULL \n OR json_extract(player_json, '$.previousTeams') IS NULL \n OR json_array_length(json_extract(player_json, '$.previousTeams')) = 0\n)`;\n\n// Fast correlated NOT EXISTS check for retired unowned players\nconst RETIRED_UNOWNED = `NOT EXISTS (\n SELECT 1 FROM listings l_prev \n WHERE l_prev.player_id = r.player_id \n AND l_prev.player_json IS NOT NULL \n AND json_extract(l_prev.player_json, '$.previousTeams') IS NOT NULL \n AND json_array_length(json_extract(l_prev.player_json, '$.previousTeams')) > 0\n)`;\n\nconst gradeCase = (col: string) =>\n \"CASE \" + col + \" \" + GRADES.map((g, i) =>\n `WHEN '${g}' THEN ${i}`\n ).join(\" \") + \" ELSE -1 END\";\n\nasync function ensureSchema() {\n try {\n await sqlite.execute(`ALTER TABLE listings ADD COLUMN end_at INTEGER`);\n } catch (_e) {\n // Column already exists\n }\n}\n\nasync function q(sql: string, args: any[] = []): Promise {\n const res: any = await sqlite.execute(args.length ? { sql, args } : sql);\n const cols: string[] = res.columns || [];\n return (res.rows as any[]).map((row) =>\n Array.isArray(row)\n ? Object.fromEntries(cols.map((c, i) => [c, row[i]]))\n : row\n );\n}\n\nconst json = (o: any, status = 200) =>\n new Response(JSON.stringify(o), {\n status,\n headers: { \"content-type\": \"application/json\" },\n });\n\nasync function apiStats(): Promise {\n await ensureSchema();\n const now = Date.now();\n const r = (await q(`SELECT\n (SELECT COUNT(*) FROM listings WHERE ${LISTINGS_UNOWNED}) AS captured,\n (SELECT COUNT(*) FROM listings WHERE left_at IS NULL AND (end_at IS NULL OR end_at > ${now}) AND ${LISTINGS_UNOWNED}) AS live,\n (SELECT COUNT(*) FROM retired r WHERE ${RETIRED_UNOWNED}) AS resolved,\n (SELECT COUNT(*) FROM retired r WHERE r.retired_potential IN (${ELITE}) AND ${RETIRED_UNOWNED}) AS hits,\n (SELECT COUNT(*) FROM retired r WHERE r.retired_potential IN (${SUPER_ELITE}) AND ${RETIRED_UNOWNED}) AS s_hits`))[\n 0\n ];\n const hitRate = r.resolved ? Math.round((100 * r.hits) / r.resolved) : 0;\n const sHitRate = r.resolved ? Math.round((100 * r.s_hits) / r.resolved) : 0;\n return json({\n captured: r.captured,\n live: r.live,\n resolved: r.resolved,\n hitRate,\n sHitRate,\n });\n}\n\nasync function apiLive(): Promise {\n await ensureSchema();\n const now = Date.now();\n const rows = await q(\n `SELECT player_id, name, role, age, est_rank, est_potential, intel, fog_pct,\n salary, starting_bid, current_bid, growth_class, first_seen_at, end_at\n FROM listings \n WHERE left_at IS NULL \n AND (end_at IS NULL OR end_at > ?)\n AND ${LISTINGS_UNOWNED}\n ORDER BY end_at ASC, first_seen_at DESC`,\n [now]\n );\n return json(rows);\n}\n\nasync function apiRetired(url: URL): Promise {\n await ensureSchema();\n const limitParam = parseInt(url.searchParams.get(\"limit\") || \"50\") || 50;\n const pageSize = Math.min(100, Math.max(1, limitParam));\n const page = Math.max(1, parseInt(url.searchParams.get(\"page\") || \"1\") || 1);\n const term = (url.searchParams.get(\"q\") || \"\").trim();\n const sortKey = url.searchParams.get(\"sort\") || \"r_pot\";\n const sortDir = url.searchParams.get(\"dir\") === \"1\" ? \"ASC\" : \"DESC\";\n\n const mRank = (url.searchParams.get(\"m_rank\") || \"\").trim();\n const mPot = (url.searchParams.get(\"m_pot\") || \"\").trim();\n const rRank = (url.searchParams.get(\"r_rank\") || \"\").trim();\n const rPot = (url.searchParams.get(\"r_pot\") || \"\").trim();\n const growthClass = (url.searchParams.get(\"growth_class\") || \"\").trim();\n const minAge = url.searchParams.get(\"min_age\");\n const maxAge = url.searchParams.get(\"max_age\");\n const minFog = url.searchParams.get(\"min_fog\");\n const maxFog = url.searchParams.get(\"max_fog\");\n const minSal = url.searchParams.get(\"min_sal\");\n const maxSal = url.searchParams.get(\"max_sal\");\n const minDelta = url.searchParams.get(\"min_delta\");\n const maxDelta = url.searchParams.get(\"max_delta\");\n\n const where = [\"r.retired_potential IS NOT NULL\", RETIRED_UNOWNED];\n const args: any[] = [];\n\n if (term) {\n where.push(\"r.name LIKE ?\");\n args.push(\"%\" + term + \"%\");\n }\n\n const addGradeFilter = (paramVal: string, colExpr: string) => {\n if (!paramVal || paramVal === \"all\") return;\n const grades = paramVal.split(\",\").map((g) => g.trim()).filter((g) =>\n GRADES.includes(g)\n );\n if (grades.length > 0) {\n const inList = grades.map((g) => `'${g}'`).join(\",\");\n where.push(`${colExpr} IN (${inList})`);\n }\n };\n\n addGradeFilter(mRank, \"COALESCE(r.market_est_rank, l.est_rank)\");\n addGradeFilter(mPot, \"COALESCE(r.market_est_potential, l.est_potential)\");\n addGradeFilter(rRank, \"r.retired_rank\");\n addGradeFilter(rPot, \"r.retired_potential\");\n\n if (growthClass && growthClass !== \"all\") {\n where.push(\"LOWER(l.growth_class) = LOWER(?)\");\n args.push(growthClass);\n }\n\n if (minAge !== null && minAge !== \"\" && !isNaN(Number(minAge))) {\n where.push(\"COALESCE(r.market_age, l.age) >= ?\");\n args.push(Number(minAge));\n }\n if (maxAge !== null && maxAge !== \"\" && !isNaN(Number(maxAge))) {\n where.push(\"COALESCE(r.market_age, l.age) <= ?\");\n args.push(Number(maxAge));\n }\n if (minFog !== null && minFog !== \"\" && !isNaN(Number(minFog))) {\n where.push(\"COALESCE(r.market_fog_pct, l.fog_pct) >= ?\");\n args.push(Number(minFog));\n }\n if (maxFog !== null && maxFog !== \"\" && !isNaN(Number(maxFog))) {\n where.push(\"COALESCE(r.market_fog_pct, l.fog_pct) <= ?\");\n args.push(Number(maxFog));\n }\n if (minSal !== null && minSal !== \"\" && !isNaN(Number(minSal))) {\n where.push(\"COALESCE(r.market_salary, l.salary) >= ?\");\n args.push(Number(minSal));\n }\n if (maxSal !== null && maxSal !== \"\" && !isNaN(Number(maxSal))) {\n where.push(\"COALESCE(r.market_salary, l.salary) <= ?\");\n args.push(Number(maxSal));\n }\n if (minDelta !== null && minDelta !== \"\" && !isNaN(Number(minDelta))) {\n where.push(\n `(${gradeCase(\"r.retired_potential\")} - ${\n gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")\n }) >= ?`,\n );\n args.push(Number(minDelta));\n }\n if (maxDelta !== null && maxDelta !== \"\" && !isNaN(Number(maxDelta))) {\n where.push(\n `(${gradeCase(\"r.retired_potential\")} - ${\n gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")\n }) <= ?`,\n );\n args.push(Number(maxDelta));\n }\n\n let orderSql = `${gradeCase(\"r.retired_potential\")} DESC, r.retired_at DESC`;\n if (sortKey === \"name\") orderSql = `r.name ${sortDir}`;\n else if (sortKey === \"growth\") {\n orderSql = `l.growth_class ${sortDir}`;\n } else if (sortKey === \"age\") {\n orderSql = `COALESCE(r.market_age, l.age) ${sortDir}`;\n } else if (sortKey === \"fog\") {\n orderSql = `COALESCE(r.market_fog_pct, l.fog_pct) ${sortDir}`;\n } else if (sortKey === \"salary\") {\n orderSql = `COALESCE(r.market_salary, l.salary) ${sortDir}`;\n } else if (sortKey === \"m_rank\") {\n orderSql = `${gradeCase(\"COALESCE(r.market_est_rank, l.est_rank)\")} ${sortDir}`;\n } else if (sortKey === \"m_pot\") {\n orderSql = `${gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")} ${sortDir}`;\n } else if (sortKey === \"r_rank\") {\n orderSql = `${gradeCase(\"r.retired_rank\")} ${sortDir}`;\n } else if (sortKey === \"r_pot\") {\n orderSql = `${gradeCase(\"r.retired_potential\")} ${sortDir}, r.retired_at DESC`;\n } else if (sortKey === \"delta\") {\n orderSql = `(${gradeCase(\"r.retired_potential\")} - ${\n gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")\n }) ${sortDir}`;\n } else if (sortKey === \"retired_at\") {\n orderSql = `r.retired_at ${sortDir}`;\n }\n\n const fromClause = `\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n `;\n\n const W = \"WHERE \" + where.join(\" AND \");\n const countSql = `SELECT COUNT(*) c ${fromClause} ${W}`;\n const total = (await q(countSql, args))[0].c;\n const pages = Math.max(1, Math.ceil(total / pageSize));\n const p = Math.min(page, pages);\n\n const querySql = `\n SELECT \n r.name, \n l.growth_class,\n COALESCE(r.market_age, l.age) AS age,\n COALESCE(r.market_fog_pct, l.fog_pct) AS fog_pct,\n COALESCE(r.market_salary, l.salary) AS salary,\n COALESCE(r.market_est_rank, l.est_rank) AS market_est_rank,\n COALESCE(r.market_est_potential, l.est_potential) AS market_est_potential,\n r.retired_rank, \n r.retired_potential, \n r.true_rating, \n r.potential_rating, \n r.retired_at, \n r.card_url\n ${fromClause}\n ${W}\n ORDER BY ${orderSql}\n LIMIT ${pageSize} OFFSET ${(p - 1) * pageSize}\n `;\n\n const rows = await q(querySql, args);\n return json({ rows, total, page: p, pages, limit: pageSize });\n}\n\nasync function apiAnalytics(): Promise {\n await ensureSchema();\n const potCase = gradeCase(\"r.retired_potential\");\n const bandCase =\n `CASE WHEN COALESCE(r.market_fog_pct, l.fog_pct)<20 THEN '0-20' \n WHEN COALESCE(r.market_fog_pct, l.fog_pct)<40 THEN '20-40' \n WHEN COALESCE(r.market_fog_pct, l.fog_pct)<60 THEN '40-60' \n WHEN COALESCE(r.market_fog_pct, l.fog_pct)<80 THEN '60-80' \n ELSE '80+' END`;\n\n // 0. A+ / S-Tier Hit Rate Matrix\n const aPlusMatrix = await q(`\n SELECT \n COALESCE(r.market_est_rank, l.est_rank) || ' / ' || COALESCE(r.market_est_potential, l.est_potential) AS grade_pair,\n COUNT(*) AS total_players,\n ROUND(AVG(COALESCE(r.market_fog_pct, l.fog_pct)), 1) AS avg_fog,\n ROUND(AVG(COALESCE(r.market_salary, l.salary)), 0) AS avg_salary,\n SUM(CASE WHEN r.retired_potential IN ('A+', 'S-', 'S', 'S+') THEN 1 ELSE 0 END) AS a_plus_or_better,\n ROUND(100.0 * SUM(CASE WHEN r.retired_potential IN ('A+', 'S-', 'S', 'S+') THEN 1 ELSE 0 END) / COUNT(*), 1) AS a_plus_rate_pct,\n SUM(CASE WHEN r.retired_potential IN ('S-', 'S', 'S+') THEN 1 ELSE 0 END) AS s_tier_count,\n ROUND(AVG(r.true_rating), 1) AS avg_revealed_true,\n ROUND(AVG(r.potential_rating), 1) AS avg_revealed_pot\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.retired_potential IS NOT NULL \n AND COALESCE(r.market_est_potential, l.est_potential) IS NOT NULL\n AND COALESCE(r.market_fog_pct, l.fog_pct) BETWEEN 45 AND 85\n AND ${RETIRED_UNOWNED}\n GROUP BY COALESCE(r.market_est_rank, l.est_rank), COALESCE(r.market_est_potential, l.est_potential)\n HAVING COUNT(*) >= 25\n ORDER BY a_plus_rate_pct DESC\n `);\n\n // 1. Fog Bands\n const fb = await q(`\n SELECT ${bandCase} AS band, COUNT(*) n,\n SUM(CASE WHEN ${potCase}>=9 THEN 1 ELSE 0 END) t9,\n SUM(CASE WHEN ${potCase}>=10 THEN 1 ELSE 0 END) t10,\n SUM(CASE WHEN ${potCase}>=11 THEN 1 ELSE 0 END) t11,\n SUM(CASE WHEN ${potCase}>=12 THEN 1 ELSE 0 END) t12,\n SUM(CASE WHEN ${potCase}>=13 THEN 1 ELSE 0 END) t13\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.retired_potential IS NOT NULL \n AND COALESCE(r.market_fog_pct, l.fog_pct) IS NOT NULL \n AND ${RETIRED_UNOWNED}\n GROUP BY band\n `);\n\n const order = [\"0-20\", \"20-40\", \"40-60\", \"60-80\", \"80+\"];\n const fogbands = order.map((b) =>\n fb.find((x) => x.band === b) ||\n { band: b, n: 0, t9: 0, t10: 0, t11: 0, t12: 0, t13: 0 }\n );\n\n // 2. Salary vs Revealed Potential Rating\n const scatter = await q(`\n SELECT COALESCE(r.market_salary, l.salary) s, r.potential_rating p, r.true_rating r, r.retired_potential g\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.potential_rating IS NOT NULL \n AND COALESCE(r.market_salary, l.salary) IS NOT NULL \n AND ${RETIRED_UNOWNED}\n `);\n\n // 3. Age Decay Curve (% S- or above across age brackets)\n const ageBrackets = await q(`\n SELECT \n CASE \n WHEN COALESCE(r.market_age, l.age) < 20 THEN '<20'\n WHEN COALESCE(r.market_age, l.age) < 24 THEN '20-23'\n WHEN COALESCE(r.market_age, l.age) < 28 THEN '24-27'\n WHEN COALESCE(r.market_age, l.age) < 32 THEN '28-31'\n ELSE '32+' \n END AS bracket,\n COUNT(*) AS total,\n SUM(CASE WHEN ${potCase} >= 12 THEN 1 ELSE 0 END) AS s_count,\n SUM(CASE WHEN ${potCase} >= 9 THEN 1 ELSE 0 END) AS a_count\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.retired_potential IS NOT NULL \n AND COALESCE(r.market_age, l.age) IS NOT NULL \n AND ${RETIRED_UNOWNED}\n GROUP BY bracket\n `);\n\n // 4. Ceiling Breakdown by Growth Class\n const cRows = await q(`\n SELECT est_potential pot, growth_class g, COUNT(*) n \n FROM listings\n WHERE est_potential IS NOT NULL \n AND growth_class IS NOT NULL \n AND ${LISTINGS_UNOWNED}\n GROUP BY est_potential, growth_class\n `);\n\n const labels = Array.from(new Set(cRows.map((r) => r.pot))).sort((a, b) =>\n GRADES.indexOf(a) - GRADES.indexOf(b)\n );\n const matrix: any = { novice: {}, amateur: {}, veteran: {}, prodigy: {} };\n for (const r of cRows) {\n (matrix[r.g] = matrix[r.g] || {})[r.pot] = r.n;\n }\n\n return json({ aPlusMatrix, fogbands, scatter, ageBrackets, ceiling: { labels, matrix } });\n}\n\n/* ---- Roster Development API ---- */\n\nconst STAT_COLS = [\n \"aim\", \"mechanics\", \"clutch\", \"consistency\",\n \"awareness\", \"communication\", \"temperament\", \"cognition\"\n];\n\nasync function apiRosterPlayers(): Promise {\n // Latest snapshot per player + lead counts — exclude departed players\n const rows = await q(`\n SELECT s.*, \n (SELECT COUNT(*) FROM development_leads d WHERE d.player_id = s.player_id) AS lead_count,\n (SELECT COUNT(*) FROM development_leads d WHERE d.player_id = s.player_id AND d.completed = 1) AS leads_done\n FROM roster_snapshots s\n INNER JOIN (\n SELECT player_id, MAX(snapshot_at) AS max_at\n FROM roster_snapshots GROUP BY player_id\n ) latest ON s.player_id = latest.player_id AND s.snapshot_at = latest.max_at\n WHERE s.player_id NOT IN (\n SELECT rc.player_id FROM roster_changes rc\n WHERE rc.change_type = 'departed'\n AND rc.changed_at = (SELECT MAX(rc2.changed_at) FROM roster_changes rc2 WHERE rc2.player_id = rc.player_id AND rc2.change_type = 'departed')\n AND NOT EXISTS (\n SELECT 1 FROM roster_snapshots s2\n WHERE s2.player_id = rc.player_id AND s2.snapshot_at > rc.changed_at\n )\n )\n ORDER BY s.roster_group ASC, s.name ASC\n `);\n\n // For each player, get their previous snapshot to compute trend (stats + fog)\n const withTrend = [];\n for (const row of rows) {\n const prev = await q(`\n SELECT ${STAT_COLS.join(\",\")}, fog_pct FROM roster_snapshots\n WHERE player_id = ? AND snapshot_at < ?\n ORDER BY snapshot_at DESC LIMIT 1\n `, [row.player_id, row.snapshot_at]);\n\n const trend: Record = {};\n if (prev.length) {\n for (const col of [...STAT_COLS, \"fog_pct\"]) {\n if (row[col] != null && prev[0][col] != null) {\n const d = row[col] - prev[0][col];\n if (d !== 0) trend[col] = d;\n }\n }\n }\n withTrend.push({ ...row, trend });\n }\n return json(withTrend);\n}\n\nasync function apiRosterSnapshots(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n if (!playerId) return json({ error: \"player_id required\" }, 400);\n const limit = Math.min(500, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"200\") || 200));\n\n const rows = await q(`\n SELECT * FROM roster_snapshots\n WHERE player_id = ?\n ORDER BY snapshot_at ASC\n LIMIT ?\n `, [playerId, limit]);\n return json(rows);\n}\n\nasync function apiRosterEvents(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n const limit = Math.min(500, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"200\") || 200));\n\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n args.push(limit);\n\n const rows = await q(`\n SELECT * FROM roster_events\n WHERE ${where.join(\" AND \")}\n ORDER BY detected_at DESC\n LIMIT ?\n `, args);\n return json(rows);\n}\n\nasync function apiRosterChanges(): Promise {\n const rows = await q(`\n SELECT * FROM roster_changes\n ORDER BY changed_at DESC\n LIMIT 200\n `);\n return json(rows);\n}\n\nasync function apiRosterLeads(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n\n const rows = await q(`\n SELECT * FROM development_leads\n WHERE ${where.join(\" AND \")}\n ORDER BY completed ASC, first_seen_at DESC\n `, args);\n return json(rows);\n}\n\nasync function apiRosterLeadToggle(req: Request): Promise {\n const body = await req.json();\n const clueId = body.clue_id;\n if (!clueId) return json({ error: \"clue_id required\" }, 400);\n\n // Toggle: flip the completed flag\n const now = Date.now();\n await sqlite.execute({\n sql: `UPDATE development_leads\n SET completed = CASE WHEN completed = 0 THEN 1 ELSE 0 END,\n completed_at = CASE WHEN completed = 0 THEN ? ELSE NULL END\n WHERE clue_id = ?`,\n args: [now, clueId],\n });\n\n const rows = await q(`SELECT * FROM development_leads WHERE clue_id = ?`, [clueId]);\n return json(rows[0] || { error: \"not found\" });\n}\n\nasync function apiRosterHistory(url: URL): Promise {\n // Now serves activity sessions instead of development_history\n const playerId = url.searchParams.get(\"player_id\");\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n\n const rows = await q(`\n SELECT * FROM activity_sessions\n WHERE ${where.join(\" AND \")}\n ORDER BY started_at DESC\n LIMIT 200\n `, args);\n return json(rows);\n}\n\nasync function apiRosterTurningPoints(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n\n const rows = await q(`\n SELECT * FROM turning_points\n WHERE ${where.join(\" AND \")}\n ORDER BY detected_at DESC\n LIMIT 100\n `, args);\n return json(rows);\n}\n\nasync function apiRosterDaily(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n if (!playerId) return json({ error: \"player_id required\" }, 400);\n const limit = Math.min(60, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"30\") || 30));\n\n // Get one snapshot per day (latest snapshot each day) for this player\n const rows = await q(`\n SELECT s.* FROM roster_snapshots s\n INNER JOIN (\n SELECT MAX(id) AS max_id\n FROM roster_snapshots\n WHERE player_id = ?\n GROUP BY date(snapshot_at/1000, 'unixepoch')\n ) daily ON s.id = daily.max_id\n ORDER BY s.snapshot_at DESC\n LIMIT ?\n `, [playerId, limit]);\n\n // Also get the very first snapshot for total progress calculation\n const first = await q(`\n SELECT * FROM roster_snapshots\n WHERE player_id = ?\n ORDER BY snapshot_at ASC\n LIMIT 1\n `, [playerId]);\n\n return json({ snapshots: rows, first: first[0] || null });\n}\n\nasync function apiRosterEffectiveness(): Promise {\n // Aggregate stat gains by activity type\n const rows = await q(`\n SELECT attributed_activity,\n stat_name,\n COUNT(*) AS occurrences,\n SUM(delta) AS total_delta,\n ROUND(AVG(delta), 2) AS avg_delta\n FROM roster_events\n WHERE attributed_activity IS NOT NULL\n GROUP BY attributed_activity, stat_name\n ORDER BY attributed_activity, total_delta DESC\n `);\n\n // Also get totals per activity\n const totals = await q(`\n SELECT attributed_activity,\n COUNT(*) AS total_changes,\n SUM(delta) AS total_points\n FROM roster_events\n WHERE attributed_activity IS NOT NULL\n GROUP BY attributed_activity\n ORDER BY total_points DESC\n `);\n\n return json({ byStatAndActivity: rows, totals });\n}\n\nexport default async function (req: Request): Promise {\n await initDb();\n const url = new URL(req.url);\n const p = url.pathname.replace(/\\/+$/, \"\") || \"/\";\n try {\n if (p === \"/api/stats\") return await apiStats();\n if (p === \"/api/live\") return await apiLive();\n if (p === \"/api/retired\") return await apiRetired(url);\n if (p === \"/api/analytics\") return await apiAnalytics();\n\n // Roster Development endpoints\n if (p === \"/api/roster/players\") return await apiRosterPlayers();\n if (p === \"/api/roster/snapshots\") return await apiRosterSnapshots(url);\n if (p === \"/api/roster/events\") return await apiRosterEvents(url);\n if (p === \"/api/roster/changes\") return await apiRosterChanges();\n if (p === \"/api/roster/leads\") return await apiRosterLeads(url);\n if (p === \"/api/roster/leads/toggle\" && req.method === \"POST\") return await apiRosterLeadToggle(req);\n if (p === \"/api/roster/history\") return await apiRosterHistory(url);\n if (p === \"/api/roster/effectiveness\") return await apiRosterEffectiveness();\n if (p === \"/api/roster/turning-points\") return await apiRosterTurningPoints(url);\n if (p === \"/api/roster/daily\") return await apiRosterDaily(url);\n } catch (e) {\n return json({ error: String(e) }, 500);\n }\n return new Response(PAGE, {\n headers: { \"content-type\": \"text/html; charset=utf-8\" },\n });\n}","deleted",false,"label",[14],"b4ea5955-0efb-41c6-a963-10779cb76bf7",["D",1787146151215],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Tracks market players to find fog-hidden A-+ gems on retirement","imageUrl","userId","b71e33f8-d99a-4cff-b095-38f58c0a5236","pinnedFileId","f1a1657c-9bd3-11f1-8fbb-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019fd3a0-5b29-72d9-8429-c2783b92627c","tursoName","b4ea59550efb41c6a96310779cb76bf7","tursoHostname","b4ea59550efb41c6a96310779cb76bf7-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789491713377],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_106":102},"org",{"_9":10,"_99":100,"_101":102,"_55":56,"_103":104,"_105":42},"branch",{"_35":89,"_11":90,"_22":28,"_91":45,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":12,"_98":10},"isOwned",215,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Valentino Rodrigues","avatar","https://lh3.googleusercontent.com/a/ACg8ocLUTO6MzdqeXpcs4pmDUIuBGqLrhsGH0xN-uIFC_LP0kPooCA=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_82":-5,"_140":141,"_142":143,"_144":145,"_146":42,"_147":42,"_148":42,"_149":32,"_150":-5,"_151":-7,"_152":-7,"_153":63,"_154":42,"_155":42,"_156":-5,"_157":42,"_158":42,"_159":42,"_160":-5},"ENV",{"_111":112,"_113":114,"_115":90,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]