Apex Scout — market intel

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_261":262,"_263":264},"routes/_app.x.$handle.$name",{"_98":97,"_32":44,"_107":88,"_27":108,"_13":14,"_109":-5,"_11":110,"_111":112,"_113":114,"_115":116,"_117":118,"_20":-5,"_119":-5,"_120":-5,"_121":48,"_122":123,"_124":25,"_125":-5,"_126":-5,"_127":128},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_42":43,"_32":44,"_37":45,"_46":-5,"_47":48,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":48,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":69,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":81,"_82":83,"_84":85,"_86":48},"version",2,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":36,"_37":38,"_39":14,"_40":41,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",true,"deletedHere","path","tmp_recheck_active.ts","latestPath","fileId","01a06c3e-3531-7518-8403-c72f26436da4","name","fileType","script","content","export default async function (req: Request): Promise {\n const token = Deno.env.get(\"APEX_TOKEN\");\n const resp = await fetch(\"https://apexmanager.rootsxo.studio/api/auctions\", {\n headers: { Authorization: `Bearer ${token}` },\n });\n const data = await resp.json();\n const auctions = Array.isArray(data)\n ? data\n : (data.auctions ?? data.items ?? []);\n\n // Return the first auction object to see all its top-level properties\n const sample = auctions[0] ?? { error: \"No active auctions found\" };\n\n return new Response(JSON.stringify(sample, null, 2), {\n headers: { \"content-type\": \"application/json\" },\n });\n}","createdAt",["D",1788522470041],"branchVersion","branchId","63086719-d96c-41ff-8e16-4e84b4540a2c","id","3e806cc7-ffa7-4f5a-8f6f-a23db6d75082","centuriaVillageDashboard",["D",1788522411148],"deletedAt","legacyEnvVars",false,"privacy","public","httpPrivacy","description","Live village dashboard for Centuria b15 (village 8low)","imageUrl","b71e33f8-d99a-4cff-b095-38f58c0a5236","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019fd3a0-5b29-72d9-8429-c2783b92627c","tursoName","3e806cc7ffa74f5a8f6fa23db6d75082","tursoHostname","3e806cc7ffa74f5a8f6fa23db6d75082-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789477862254],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_98":97,"_106":102},"org",{"_98":97,"_99":100,"_101":102,"_18":55,"_103":104,"_105":48},"branch",{"_13":87,"_32":88,"_42":41,"_89":43,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":44,"_96":97},"isOwned",19,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","kubeniz","handle","displayName","Valentino Rodrigues","avatar","https://lh3.googleusercontent.com/a/ACg8ocLUTO6MzdqeXpcs4pmDUIuBGqLrhsGH0xN-uIFC_LP0kPooCA=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_42":43,"_32":44,"_37":256,"_46":-5,"_47":48,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":48,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":257,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":258,"_82":259,"_84":260,"_86":48},"tree",[215,216,217,218,219,220,221,222,223,224],"files",[215,216,217,218,219,220,221,222,223,224],"counts",{"_205":61,"_206":143,"_207":61,"_208":209,"_210":211},"contents",[129,130,131,132,133,134,135,136,137,138],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_42":199,"_32":200,"_30":201,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":202,"_13":61,"_33":20,"_35":203,"_24":48,"_148":-5,"_27":204},{"_42":193,"_32":194,"_30":195,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":196,"_13":61,"_33":160,"_35":197,"_24":48,"_148":-5,"_27":198},{"_42":187,"_32":188,"_30":189,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":190,"_13":61,"_33":160,"_35":191,"_24":48,"_148":-5,"_27":192},{"_42":181,"_32":182,"_30":183,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":184,"_13":61,"_33":34,"_35":185,"_24":48,"_148":-5,"_27":186},{"_42":175,"_32":176,"_30":177,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":178,"_13":61,"_33":34,"_35":179,"_24":48,"_148":-5,"_27":180},{"_42":169,"_32":170,"_30":171,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":172,"_13":61,"_33":34,"_35":173,"_24":48,"_148":-5,"_27":174},{"_42":163,"_32":164,"_30":165,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":166,"_13":61,"_33":146,"_35":167,"_24":48,"_148":-5,"_27":168},{"_42":156,"_32":157,"_30":158,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":159,"_13":61,"_33":160,"_35":161,"_24":48,"_148":-5,"_27":162},{"_42":150,"_32":151,"_30":152,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":153,"_13":61,"_33":34,"_35":154,"_24":48,"_148":-5,"_27":155},{"_42":139,"_32":140,"_30":141,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":145,"_13":61,"_33":146,"_35":147,"_24":48,"_148":-5,"_27":149},"6e9ea7f3-c0f6-4d92-8455-ddf8dba57387","site.ts","01a06c3e-3531-7518-8403-c2f126f56030","parentId",1,"latest",["D",1788522411148],"http","// 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}","label",[140],"8992c0b5-c282-4be8-b5e9-dc383b72f930","seed_test.ts","01a06c3e-3531-7518-8403-bca0da09a6da",["D",1788522411148],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb, isGem, isBelowAMinus } from \"./db.ts\";\n\nawait initDb();\nconst TOKEN = Deno.env.get(\"APEX_TOKEN\");\n\nasync function api(url: string) {\n const r = await fetch(url, { headers: { Authorization: `Bearer ${TOKEN}` } });\n return r.ok ? await r.json() : null;\n}\n\nconst wl = await api(\"https://apexmanager.rootsxo.studio/api/watchlist\");\nconst arr = Array.isArray(wl) ? wl : (wl.items ?? wl.players ?? []);\n\n// Prefer a retired player who displayed below A- on the market but has A-+ potential (a real gem).\nconst candidates = arr.filter(\n (p: any) => (p.isRetired || p.trueRating != null) && p.potentialRating >= 1800 && isBelowAMinus(p.estimatedPotential),\n);\nconst pick = (candidates[0] as any) ?? (arr.find((p: any) => p.isRetired || p.trueRating != null) as any);\nif (!pick) {\n console.log(\"No retired player found to seed.\");\n} else {\n const c = await api(`https://apexmanager.rootsxo.studio/api/players/${pick.id}`);\n if (!c) {\n console.log(\"Could not fetch player card.\");\n } else {\n const intel = c.fogConfidence ?? 0;\n const fogPct = Math.round((1 - intel) * 100);\n const gem = isGem(c.estimatedPotential, c.estimatedPotential); // market sourced from card here\n const pid = \"test_\" + c.id; // prefix marks this as a manual test row\n await sqlite.execute(\n `INSERT INTO retired\n (player_id, name, market_est_rank, market_est_potential, market_intel,\n market_fog_pct, market_salary, true_rating, potential_rating, peak_rating,\n retired_rank, retired_potential, retired_at, checked_at, is_gem, card_url)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n ON CONFLICT(player_id) DO UPDATE SET\n market_est_potential=excluded.market_est_potential,\n true_rating=excluded.true_rating,\n potential_rating=excluded.potential_rating,\n retired_rank=excluded.retired_rank,\n retired_potential=excluded.retired_potential,\n is_gem=excluded.is_gem,\n card_url=excluded.card_url`,\n [\n pid, c.name, c.estimatedRank, c.estimatedPotential, intel,\n fogPct, c.salary ?? null, c.trueRating ?? null, c.potentialRating ?? null, c.peakRating ?? null,\n c.estimatedRank, c.estimatedPotential,\n Date.now(), Date.now(), gem ? 1 : 0,\n `https://apexmanager.rootsxo.studio/player/${c.id}`,\n ],\n );\n console.log(`Seeded test row: ${c.name} | market ${c.estimatedRank}/${c.estimatedPotential} (fog ${fogPct}%) -> potential ${c.potentialRating} | gem=${gem}`);\n }\n}\n",[151],"4d2ebcab-8ff0-43fd-ae8c-44d4d8febbe8","roster_collect.ts","01a06c3e-3531-7518-8403-b93d715e6b85",["D",1788522411148],"interval","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\nconst TOKEN = Deno.env.get(\"APEX_TOKEN\");\nconst API = \"https://apexmanager.rootsxo.studio/api\";\n\nconst STAT_KEYS = [\n \"aim\", \"mechanics\", \"clutch\", \"consistency\",\n \"awareness\", \"communication\", \"temperament\",\n] as const;\n\nasync function api(path: string) {\n const r = await fetch(`${API}${path}`, {\n headers: { Authorization: `Bearer ${TOKEN}` },\n });\n if (!r.ok) {\n console.error(`API ${path} → ${r.status}`);\n return null;\n }\n return r.json();\n}\n\nexport default async function (_interval: Interval) {\n await initDb();\n const now = Date.now();\n\n // ── 1. Fetch roster + team-house notifications ──\n const roster = await api(\"/roster\");\n if (!roster) {\n console.error(\"Failed to fetch roster, skipping this cycle.\");\n return;\n }\n\n // Fetch team-house notifications for Sport Psych fog reductions & cognition training\n const thNotifs = await api(\"/team-house/notifications\") ?? [];\n // Build lookup: player_id → latest fog reduction / cognition training notification\n const fogReductions = new Map();\n const cogTrainings = new Map();\n for (const n of thNotifs) {\n if (n.category === \"staff\" && n.title?.startsWith(\"Fog Reduction:\") && n.refId) {\n const existing = fogReductions.get(n.refId);\n if (!existing || n.createdAt > existing.createdAt) {\n fogReductions.set(n.refId, { createdAt: n.createdAt, body: n.body });\n }\n }\n if (n.category === \"training\" && n.title?.startsWith(\"Cognition Training:\") && n.refId) {\n const existing = cogTrainings.get(n.refId);\n if (!existing || n.createdAt > existing.createdAt) {\n cogTrainings.set(n.refId, { createdAt: n.createdAt, body: n.body });\n }\n }\n }\n\n // Fetch regular notifications for turning points (player_story) and scrim results\n const regNotifs = await api(\"/notifications\") ?? [];\n const turningPoints = new Map();\n const scrimResults = new Map();\n for (const n of regNotifs) {\n if (n.type === \"player_story\" && n.title?.includes(\"turning point\") && n.metadata?.playerId) {\n const pid = n.metadata.playerId;\n const existing = turningPoints.get(pid);\n if (!existing || n.createdAt > existing.createdAt) {\n turningPoints.set(pid, { createdAt: n.createdAt, body: n.body ?? \"\", arcKey: n.metadata.arcKey ?? \"\" });\n }\n }\n if (n.type === \"scrim_result\" && n.refId) {\n if (!scrimResults.has(n.refId)) scrimResults.set(n.refId, []);\n scrimResults.get(n.refId)!.push({ createdAt: n.createdAt, body: n.body ?? \"\" });\n }\n }\n\n const groups: Record = {\n active: roster.active ?? [],\n reserve: roster.reserve ?? [],\n bench: roster.bench ?? [],\n other: roster.other ?? [],\n };\n\n const allPlayers: { player: any; group: string }[] = [];\n for (const [group, players] of Object.entries(groups)) {\n for (const p of players) {\n allPlayers.push({ player: p, group });\n }\n }\n\n // ── 2. Get previous snapshot for each player (for change detection) ──\n const prevSnapshots = new Map();\n const prevRows = await sqlite.execute(\n `SELECT * FROM roster_snapshots\n WHERE id IN (\n SELECT MAX(id) FROM roster_snapshots GROUP BY player_id\n )`,\n );\n for (const row of prevRows.rows) {\n prevSnapshots.set(row.player_id as string, row);\n }\n\n // ── 3. Get current roster player IDs to detect departures ──\n const currentPlayerIds = new Set(allPlayers.map(({ player }) => player.id));\n\n // ── 4. Insert snapshots and detect changes ──\n for (const { player: p, group } of allPlayers) {\n // Coerce all values: libsql rejects undefined and raw booleans\n const v = (x: any) => x === undefined ? null : (typeof x === \"boolean\" ? (x ? 1 : 0) : x);\n const cognition = v(p.attributes?.cognition);\n const cognitionCap = v(p.attributeCaps?.cognition);\n\n const fogPct = v(p.fogPct);\n const estRank = v(p.estimatedRank);\n const estPotential = v(p.estimatedPotential);\n\n await sqlite.execute({\n sql: `INSERT INTO roster_snapshots (\n player_id, name, roster_group, role, secondary_role, growth_class, age,\n aim, mechanics, clutch, consistency, awareness, communication, temperament, cognition,\n aim_cap, mechanics_cap, clutch_cap, consistency_cap,\n awareness_cap, communication_cap, temperament_cap, cognition_cap,\n activity, practice_focus, fatigue, scrims_today, streams_today,\n committed_to_tournament, active_tournament_id, lockout_until, salary, fog_pct,\n estimated_rank, estimated_potential, snapshot_at\n ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n args: [\n p.id, p.name, group, v(p.role), v(p.secondaryRole), v(p.growthClass), v(p.age),\n v(p.attributes?.aim), v(p.attributes?.mechanics), v(p.attributes?.clutch),\n v(p.attributes?.consistency), v(p.attributes?.awareness),\n v(p.attributes?.communication), v(p.attributes?.temperament), cognition,\n v(p.attributeCaps?.aim), v(p.attributeCaps?.mechanics), v(p.attributeCaps?.clutch),\n v(p.attributeCaps?.consistency), v(p.attributeCaps?.awareness),\n v(p.attributeCaps?.communication), v(p.attributeCaps?.temperament), cognitionCap,\n v(p.activity), v(p.practiceFocus), v(p.fatigue), v(p.scrimsToday), v(p.streamsToday),\n v(p.committedToTournament), v(p.activeTournamentId), v(p.lockoutUntil),\n v(p.salary), fogPct, estRank, estPotential, now,\n ],\n });\n\n const prev = prevSnapshots.get(p.id);\n\n // ── Detect stat & rating changes ──\n if (prev) {\n const statsToCheck = [...STAT_KEYS, \"cognition\"] as const;\n\n const oldFog = prev.fog_pct as number | null;\n const fogChanged = oldFog != null && fogPct != null && oldFog !== fogPct;\n const fogIncreased = fogChanged && (fogPct as number) > (oldFog as number); // fog clearing\n\n // Detect estimated_rank changes\n const oldRank = prev.estimated_rank as string | null;\n if (oldRank != null && estRank != null && oldRank !== estRank) {\n await sqlite.execute({\n sql: `INSERT INTO roster_events\n (player_id, name, stat_name, old_value, new_value, delta, attributed_activity, tournament_id, detected_at, details)\n VALUES (?,?,?,?,?,?,?,?,?,?)`,\n args: [\n p.id, p.name, \"estimated_rank\", null, null, null,\n (prev.activity as string) || \"idle\", prev.active_tournament_id as string | null, now,\n oldRank + \" → \" + estRank,\n ],\n });\n }\n // Detect estimated_potential changes\n const oldPot = prev.estimated_potential as string | null;\n if (oldPot != null && estPotential != null && oldPot !== estPotential) {\n await sqlite.execute({\n sql: `INSERT INTO roster_events\n (player_id, name, stat_name, old_value, new_value, delta, attributed_activity, tournament_id, detected_at, details)\n VALUES (?,?,?,?,?,?,?,?,?,?)`,\n args: [\n p.id, p.name, \"estimated_potential\", null, null, null,\n (prev.activity as string) || \"idle\", prev.active_tournament_id as string | null, now,\n oldPot + \" → \" + estPotential,\n ],\n });\n }\n\n // Check notifications for this player since the last snapshot\n const prevSnapshotTime = prev.snapshot_at as number;\n const prevSnapshotDate = new Date(prevSnapshotTime).toISOString();\n const fogRedNotif = fogReductions.get(p.id);\n const hasFogReduction = fogRedNotif != null && fogRedNotif.createdAt > prevSnapshotDate;\n const cogNotif = cogTrainings.get(p.id);\n const hasCogTraining = cogNotif != null && cogNotif.createdAt > prevSnapshotDate;\n const tpNotif = turningPoints.get(p.id);\n const hasTurningPoint = tpNotif != null && tpNotif.createdAt > prevSnapshotDate;\n\n // ── Store turning point in dedicated table ──\n if (hasTurningPoint) {\n const existingTp = await sqlite.execute({\n sql: `SELECT id FROM turning_points WHERE player_id = ? AND arc_key = ?`,\n args: [p.id, tpNotif!.arcKey || \"\"],\n });\n if (existingTp.rows.length === 0) {\n await sqlite.execute({\n sql: `INSERT INTO turning_points (player_id, player_name, description, arc_key, detected_at)\n VALUES (?,?,?,?,?)`,\n args: [p.id, p.name, tpNotif!.body, tpNotif!.arcKey || null, now],\n });\n }\n }\n\n // Count how many stats changed this cycle\n let statChangeCount = 0;\n for (const s of statsToCheck) {\n const ov = prev[s] as number | null;\n const nv = s === \"cognition\" ? cognition : (p.attributes?.[s] ?? null);\n if (ov != null && nv != null && ov !== nv) statChangeCount++;\n }\n const prevActivity = prev.activity as string || \"unknown\";\n const wasIdle = prevActivity === \"idle\";\n const wasOnHoliday = prevActivity === \"holiday\";\n const playerHasFog = (fogPct != null && (fogPct as number) < 100) || (oldFog != null && oldFog < 100);\n\n // Fog noise: idle + fog changed OR idle + player has fog and 3+ stats shifted\n const likelyFogNoise = wasIdle\n && (fogChanged || (playerHasFog && statChangeCount >= 3));\n\n for (const stat of statsToCheck) {\n const oldVal = prev[stat] as number | null;\n const newVal = stat === \"cognition\" ? cognition : (p.attributes?.[stat] ?? null);\n if (oldVal != null && newVal != null && oldVal !== newVal) {\n // Holiday doesn't reveal attributes — skip stat changes during holiday (just fog noise)\n if (wasOnHoliday) continue;\n\n let attributed = prevActivity;\n const tournamentId = prev.active_tournament_id as string | null;\n const delta = newVal - oldVal;\n\n let details: string | null = null;\n\n if (stat === \"cognition\" && wasIdle) {\n // Check if Sport Psych ran cognition training on this player\n if (delta > 0 && hasCogTraining) {\n attributed = \"cognition_training\";\n details = cogNotif!.body;\n } else {\n // No cognition training notification → fog noise\n attributed = \"fog_reveal\";\n }\n }\n // Turning point confirmed by notification\n else if (wasIdle && hasTurningPoint && !likelyFogNoise) {\n attributed = \"turning_point\";\n details = tpNotif!.body;\n }\n // Other stats while idle: fog noise takes precedence\n else if (wasIdle && likelyFogNoise) {\n attributed = \"fog_reveal\";\n }\n else if (wasIdle && !prev.committed_to_tournament) {\n // Idle stat change with no turning point notification and no fog noise\n attributed = hasTurningPoint ? \"turning_point\" : \"fog_reveal\";\n if (hasTurningPoint) details = tpNotif!.body;\n }\n // Non-idle: attribute to the activity (scrim/tournament/stream)\n\n await sqlite.execute({\n sql: `INSERT INTO roster_events\n (player_id, name, stat_name, old_value, new_value, delta, attributed_activity, tournament_id, detected_at, details)\n VALUES (?,?,?,?,?,?,?,?,?,?)`,\n args: [\n p.id, p.name, stat, oldVal, newVal, delta,\n attributed, tournamentId, now, details,\n ],\n });\n }\n }\n\n // ── Detect fog_pct changes (skip holiday — no reveals during holiday) ──\n if (fogChanged && !wasOnHoliday) {\n const tournamentId = prev.active_tournament_id as string | null;\n let attributed: string;\n let fogDetails: string | null = null;\n if (wasIdle && fogIncreased && hasFogReduction) {\n // Confirmed Sport Psych fog reduction via team-house notification\n attributed = \"sport_psych\";\n fogDetails = fogRedNotif!.body;\n } else if (wasIdle && fogIncreased) {\n // Fog clearing while idle but no notification — still likely sport psych\n attributed = \"sport_psych\";\n } else if (wasIdle) {\n attributed = \"fog_reveal\";\n } else {\n // Fog changed during activity — attribute to that activity\n attributed = prevActivity;\n }\n await sqlite.execute({\n sql: `INSERT INTO roster_events\n (player_id, name, stat_name, old_value, new_value, delta, attributed_activity, tournament_id, detected_at, details)\n VALUES (?,?,?,?,?,?,?,?,?,?)`,\n args: [\n p.id, p.name, \"fog_pct\", oldFog, fogPct, (fogPct as number) - (oldFog as number),\n attributed, tournamentId, now, fogDetails,\n ],\n });\n }\n\n // ── Detect roster group change ──\n if (prev.roster_group !== group) {\n await sqlite.execute({\n sql: `INSERT INTO roster_changes (player_id, name, change_type, from_group, to_group, details, changed_at)\n VALUES (?,?,?,?,?,?,?)`,\n args: [p.id, p.name, \"moved\", prev.roster_group, group, null, now],\n });\n }\n } else {\n // New player — never seen before\n await sqlite.execute({\n sql: `INSERT INTO roster_changes (player_id, name, change_type, from_group, to_group, details, changed_at)\n VALUES (?,?,?,?,?,?,?)`,\n args: [p.id, p.name, \"signed\", null, group, null, now],\n });\n }\n }\n\n // ── 5. Track activity sessions (all non-idle activities) ──\n for (const { player: p } of allPlayers) {\n const activity = p.activity as string;\n const lockout = p.lockoutUntil as string | null;\n\n // Track every non-idle activity\n if (activity && activity !== \"idle\") {\n // Map game activity names to friendlier labels\n const activityLabel = activity === \"practicing\" ? \"Scrim\"\n : activity === \"streaming\" ? \"Stream\"\n : activity === \"competing\" ? \"Tournament\"\n : activity === \"holiday\" ? \"Holiday\"\n : activity.charAt(0).toUpperCase() + activity.slice(1);\n\n // Deduplication: if lockoutUntil is set, use it as unique key (same lockout = same session)\n // Otherwise, check if the player was already in this same activity in the previous snapshot\n let isDuplicate = false;\n if (lockout) {\n const existing = await sqlite.execute({\n sql: `SELECT id FROM activity_sessions\n WHERE player_id = ? AND lockout_until = ?`,\n args: [p.id, lockout],\n });\n isDuplicate = existing.rows.length > 0;\n } else {\n // No lockout (e.g. holiday) — check if previous snapshot had the same activity\n // AND we already have a session recorded for it\n const prev = prevSnapshots.get(p.id);\n if (prev && prev.activity === activity) {\n // Only treat as duplicate if we already recorded a session for this ongoing activity\n const recentSession = await sqlite.execute({\n sql: `SELECT id FROM activity_sessions\n WHERE player_id = ? AND activity = ? AND started_at > ?`,\n args: [p.id, activityLabel, now - 7 * 24 * 60 * 60 * 1000], // within last 7 days\n });\n isDuplicate = recentSession.rows.length > 0;\n }\n }\n\n if (!isDuplicate) {\n await sqlite.execute({\n sql: `INSERT INTO activity_sessions\n (player_id, player_name, activity, started_at, lockout_until, detected_at)\n VALUES (?,?,?,?,?,?)`,\n args: [p.id, p.name, activityLabel, now, lockout, now],\n });\n }\n }\n }\n\n // ── 6. Detect departures ──\n for (const [playerId, prev] of prevSnapshots) {\n if (!currentPlayerIds.has(playerId)) {\n // Check if we already logged a departure for this player recently (within 2h)\n const existing = await sqlite.execute({\n sql: `SELECT id FROM roster_changes\n WHERE player_id = ? AND change_type = 'departed' AND changed_at > ?`,\n args: [playerId, now - 2 * 60 * 60 * 1000],\n });\n if (existing.rows.length === 0) {\n const depDetails = JSON.stringify({\n lastGroup: prev.roster_group,\n estimatedRank: prev.estimated_rank ?? null,\n estimatedPotential: prev.estimated_potential ?? null,\n });\n await sqlite.execute({\n sql: `INSERT INTO roster_changes (player_id, name, change_type, from_group, to_group, details, changed_at)\n VALUES (?,?,?,?,?,?,?)`,\n args: [playerId, prev.name, \"departed\", prev.roster_group, null, depDetails, now],\n });\n }\n }\n }\n\n // ── 7. Collect development leads from Knowledge API ──\n const knowledge = await api(\"/knowledge/status\");\n if (knowledge?.dossiers) {\n for (const dossier of knowledge.dossiers) {\n const player = dossier.player;\n for (const clue of dossier.clues ?? []) {\n if (clue.kind === \"hint\") {\n await sqlite.execute({\n sql: `INSERT INTO development_leads (clue_id, player_id, player_name, clue_text, first_seen_at)\n VALUES (?,?,?,?,?)\n ON CONFLICT(clue_id) DO UPDATE SET\n player_name = excluded.player_name,\n clue_text = excluded.clue_text`,\n args: [clue.id, player.id, player.name, clue.text, now],\n });\n }\n // development_history collection removed — we track activity sessions ourselves\n }\n }\n }\n\n // ── Summary log ──\n const snapshotCount = allPlayers.length;\n const eventCount = await sqlite.execute(\n `SELECT COUNT(*) as c FROM roster_events WHERE detected_at = ?`,\n [now],\n );\n const changeCount = await sqlite.execute(\n `SELECT COUNT(*) as c FROM roster_changes WHERE changed_at = ?`,\n [now],\n );\n console.log(\n `roster_collect: ${snapshotCount} players snapshotted, ` +\n `${(eventCount.rows[0] as any)?.c ?? 0} stat changes detected, ` +\n `${(changeCount.rows[0] as any)?.c ?? 0} roster changes, ` +\n `${fogReductions.size} fog reductions, ${cogTrainings.size} cog trainings tracked`\n );\n}\n",[157],"f32197f2-93b0-4e94-9d92-80dc7eb01940","report.ts","01a06c3e-3531-7518-8403-b5be32f2adb2",["D",1788522411148],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\n// Turns a header string + array-of-rows into CSV text, quoting values that\n// contain commas, quotes, or newlines (so names/urls with commas stay intact).\nfunction toCsv(header: string, rows: any[][]): string {\n const esc = (v: any) => {\n if (v === null || v === undefined) return \"\";\n const s = String(v);\n return /[\",\\n]/.test(s) ? `\"${s.replace(/\"/g, '\"\"')}\"` : s;\n };\n return [header, ...rows.map((r) => r.map(esc).join(\",\"))].join(\"\\n\");\n}\n\nexport default async function handler(req: Request): Promise {\n await initDb();\n const url = new URL(req.url);\n\n // Resolved players: market display vs revealed grade, gems flagged.\n const retiredRows = await sqlite.execute(\n `SELECT name, market_est_rank, market_est_potential, market_fog_pct, market_age, market_salary,\n retired_rank, retired_potential, true_rating, potential_rating, peak_rating,\n is_gem, card_url, retired_at\n FROM retired\n ORDER BY is_gem DESC, potential_rating DESC`,\n );\n const retired = retiredRows.rows as any[];\n\n // Active market listings currently up for auction (not yet resolved).\n const listingRows = await sqlite.execute(\n `SELECT name, role, age, growth_class, archetype, est_rank, est_potential,\n fog_pct, salary, starting_bid, current_bid, first_seen_at, last_seen_at\n FROM listings\n WHERE left_at IS NULL\n ORDER BY fog_pct DESC, first_seen_at ASC`,\n );\n const listings = listingRows.rows as any[];\n\n // Every listing ever seen, active or not. left_at is NULL while a listing\n // is still active; it gets set once collect.ts hasn't seen it in 45+ min.\n const allListingRows = await sqlite.execute(\n `SELECT name, role, age, growth_class, archetype, est_rank, est_potential,\n fog_pct, salary, starting_bid, current_bid, first_seen_at, last_seen_at, left_at\n FROM listings\n ORDER BY (left_at IS NULL) DESC, first_seen_at DESC`,\n );\n const allListings = allListingRows.rows as any[];\n\n if (url.pathname.endsWith(\"/csv\") || url.pathname.endsWith(\"/csv/gems\")) {\n const header =\n \"name,market_rank,market_potential,market_fog_pct,market_age,market_salary,retired_rank,retired_potential,true_rating,potential_rating,peak_rating,is_gem,card_url,retired_at\";\n const body = toCsv(\n header,\n retired.map((r) => [\n r.name,\n r.market_est_rank,\n r.market_est_potential,\n r.market_fog_pct,\n r.market_age,\n r.market_salary,\n r.retired_rank,\n r.retired_potential,\n r.true_rating,\n r.potential_rating,\n r.peak_rating,\n r.is_gem,\n r.card_url,\n r.retired_at ? new Date(r.retired_at).toISOString() : \"\",\n ]),\n );\n return new Response(body, {\n headers: { \"Content-Type\": \"text/csv; charset=utf-8\" },\n });\n }\n\n if (url.pathname.endsWith(\"/csv/listings/all\")) {\n const header =\n \"name,role,age,growth_class,archetype,market_rank,market_potential,fog_pct,salary,starting_bid,current_bid,status,first_seen_at,last_seen_at,left_at\";\n const body = toCsv(\n header,\n allListings.map((r) => [\n r.name,\n r.role,\n r.age,\n r.growth_class,\n r.archetype,\n r.est_rank,\n r.est_potential,\n r.fog_pct,\n r.salary,\n r.starting_bid,\n r.current_bid,\n r.left_at ? \"left\" : \"active\",\n r.first_seen_at ? new Date(r.first_seen_at).toISOString() : \"\",\n r.last_seen_at ? new Date(r.last_seen_at).toISOString() : \"\",\n r.left_at ? new Date(r.left_at).toISOString() : \"\",\n ]),\n );\n return new Response(body, {\n headers: { \"Content-Type\": \"text/csv; charset=utf-8\" },\n });\n }\n\n if (url.pathname.endsWith(\"/csv/listings\")) {\n const header =\n \"name,role,age,growth_class,archetype,market_rank,market_potential,fog_pct,salary,starting_bid,current_bid,first_seen_at,last_seen_at\";\n const body = toCsv(\n header,\n listings.map((r) => [\n r.name,\n r.role,\n r.age,\n r.growth_class,\n r.archetype,\n r.est_rank,\n r.est_potential,\n r.fog_pct,\n r.salary,\n r.starting_bid,\n r.current_bid,\n r.first_seen_at ? new Date(r.first_seen_at).toISOString() : \"\",\n r.last_seen_at ? new Date(r.last_seen_at).toISOString() : \"\",\n ]),\n );\n return new Response(body, {\n headers: { \"Content-Type\": \"text/csv; charset=utf-8\" },\n });\n }\n\n if (url.pathname.endsWith(\"/listings/all\")) {\n return Response.json(allListings);\n }\n\n if (url.pathname.endsWith(\"/listings\")) {\n return Response.json(listings);\n }\n\n return Response.json(retired);\n}",[164],"a0fb96d9-8625-45b3-88e1-091ba2ca87b3","page.ts","01a06c3e-3531-7518-8403-b35c09d098e2",["D",1788522411148],"export default `\n\n\n\n\nApex Scout — market intel\n\n\n\n\n\n\n\n

APEX SCOUT

\n 🔒 UNLISTED\n \n \n

\n \n 100%\n \n

\n\n

\n \n

live

\n \n

\n \n \n \n \n \n \n \n \n \n \n

Player	Growth	Role	Age	Rank	Ceiling	Fog	Salary	Buy now	Time Left

\n\n

\n \n

\n \n \n \n \n \n \n \n \n \n \n \n \n

Player	\n Growth▾ \n \n Growth Class \n \n ★ Prodigy\n Veteran\n Amateur\n Novice\n \n \n \n	\n Age▾ \n \n Filter Age \n \n \n \n \n \n \n	\n Market Rank▾ \n \n Market Rank \n All Clear \n \n \n \n	\n Market Pot▾ \n \n Market Potential \n All Clear \n \n \n \n	\n Fog▾ \n \n Filter Fog % \n \n \n \n \n \n \n	\n Ret Rank▾ \n \n Retired Rank \n All Clear \n \n \n \n	\n Ret Pot▾ \n \n Retired Potential \n All Clear \n \n \n \n	\n Salary▾ \n \n Filter Salary \n \n \n \n \n \n \n	\n Pot Δ▾ \n \n Potential Tier Change \n \n \n \n \n \n \n	Resolved

\n \n

\n\n

Advanced Signal Analysis

\n \n

A+ / S-Tier Potential Hit Rate Matrix

Market grade pairs (45%–85% Fog, Sample ≥ 25) ranked by likelihood of revealing ≥ A+ potential.

\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n

Grade Pair (Rank / Pot)	Sample (N)	Avg Fog	Avg Salary	≥ A+ Hits	≥ A+ Rate	S-Tier (S-/S/S+)	Avg Rev. True	Avg Rev. Pot

\n\n \n

Reached ≥ A- potential, by fog band

Percentage of resolved players in each fog band that reached or exceeded the selected grade threshold.

\n \n \n

Age Cohort vs. Upside Decay

Conversion probability of hitting A- and S- tier ceilings across player age brackets.

\n\n \n

Salary vs Revealed Rating

Market salary vs revealed rating — highlighting hidden S-tier players under 6,000 cr/wk.

\n\n \n

Ceiling Grade Distribution

Displayed potential grades split by player growth archetype.

\n\n \n

Roster Development Tracker

\n\n \n\n

\n \n

\n\n\n\n`;",[170],"f9f5c7ca-0e45-46fa-ba5a-05062df523e4","main.ts","01a06c3e-3531-7518-8403-ace0bfa30c05",["D",1788522411148],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\n// Run this once (or any time) to initialize the schema and see current state.\nawait initDb();\n\nconst l = await sqlite.execute(`SELECT COUNT(*) AS c FROM listings`);\nconst r = await sqlite.execute(`SELECT COUNT(*) AS c FROM retired`);\nconst g = await sqlite.execute(`SELECT COUNT(*) AS c FROM retired WHERE is_gem = 1`);\nconst p = await sqlite.execute(`SELECT COUNT(*) AS c FROM player_checks`);\n\nconsole.log(`apexGemTracker status`);\nconsole.log(` listings: ${l.rows[0]?.c ?? 0}`);\nconsole.log(` retired: ${r.rows[0]?.c ?? 0}`);\nconsole.log(` gems found: ${g.rows[0]?.c ?? 0}`);\nconsole.log(` players checked: ${p.rows[0]?.c ?? 0}`);\n",[176],"5ef75bac-bd7c-48d6-b170-d2988a2b9482","db.ts","01a06c3e-3531-7518-8403-a875e7bf0d24",["D",1788522411148],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// A \"gem\" = a player who displayed BELOW A- potential on the market, but whose\n// revealed (retired) potential grade is A- or higher. Comparison is by letter\n// grade only — the game's numeric true/potential rating does NOT map to a fixed\n// grade scale we can rely on, so we never infer a grade from a number.\nconst A_MINUS_INDEX = 9;\n\nconst GRADE_INDEX: Record = {\n \"D-\": 0,\n \"D\": 1,\n \"D+\": 2,\n \"C-\": 3,\n \"C\": 4,\n \"C+\": 5,\n \"B-\": 6,\n \"B\": 7,\n \"B+\": 8,\n \"A-\": 9,\n \"A\": 10,\n \"A+\": 11,\n \"S-\": 12,\n \"S\": 13,\n \"S+\": 14,\n};\n\nexport function gradeIndex(letter?: string | null): number | null {\n if (!letter) return null;\n return GRADE_INDEX[letter] ?? null;\n}\n\n// Was the displayed potential grade strictly below A- ? (i.e. fog hid an A-+ player)\nexport function isBelowAMinus(letter?: string | null): boolean {\n const i = gradeIndex(letter);\n return i !== null && i < A_MINUS_INDEX;\n}\n\nexport function isGem(\n marketPotential: string | null,\n retiredPotential: string | null,\n): boolean {\n return isBelowAMinus(marketPotential) && !isBelowAMinus(retiredPotential);\n}\n\nexport async function initDb() {\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS listings (\n auction_id TEXT PRIMARY KEY,\n player_id TEXT NOT NULL,\n name TEXT,\n role TEXT,\n age REAL,\n growth_class TEXT,\n archetype TEXT,\n est_rank TEXT,\n est_potential TEXT,\n intel REAL,\n fog_pct INTEGER,\n salary INTEGER,\n starting_bid INTEGER,\n current_bid INTEGER,\n first_seen_at INTEGER,\n last_seen_at INTEGER,\n left_at INTEGER,\n player_json TEXT\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS retired (\n player_id TEXT PRIMARY KEY,\n name TEXT,\n market_est_rank TEXT,\n market_est_potential TEXT,\n market_intel REAL,\n market_fog_pct INTEGER,\n market_salary INTEGER,\n true_rating INTEGER,\n potential_rating INTEGER,\n peak_rating INTEGER,\n retired_at INTEGER,\n checked_at INTEGER,\n is_gem INTEGER DEFAULT 0\n )`,\n );\n try {\n await sqlite.execute(`ALTER TABLE retired ADD COLUMN card_url TEXT`);\n } catch { /* already exists */ }\n try {\n await sqlite.execute(`ALTER TABLE retired ADD COLUMN retired_rank TEXT`);\n } catch { /* already exists */ }\n try {\n await sqlite.execute(\n `ALTER TABLE retired ADD COLUMN retired_potential TEXT`,\n );\n } catch { /* already exists */ }\n try {\n await sqlite.execute(`ALTER TABLE retired ADD COLUMN market_age REAL`);\n } catch { /* already exists */ }\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS player_checks (\n player_id TEXT PRIMARY KEY,\n name TEXT,\n last_checked_at INTEGER,\n retired INTEGER DEFAULT 0\n )`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_listings_player ON listings(player_id)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_listings_left ON listings(left_at)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_listings_first ON listings(first_seen_at)`,\n );\n\n // ── Roster Development Tracker tables ──\n\n // Time-series: one row per player per 30-min snapshot\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS roster_snapshots (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n player_id TEXT NOT NULL,\n name TEXT,\n roster_group TEXT,\n role TEXT,\n secondary_role TEXT,\n growth_class TEXT,\n age REAL,\n aim INTEGER, mechanics INTEGER, clutch INTEGER, consistency INTEGER,\n awareness INTEGER, communication INTEGER, temperament INTEGER, cognition INTEGER,\n aim_cap INTEGER, mechanics_cap INTEGER, clutch_cap INTEGER, consistency_cap INTEGER,\n awareness_cap INTEGER, communication_cap INTEGER, temperament_cap INTEGER, cognition_cap INTEGER,\n activity TEXT,\n practice_focus TEXT,\n fatigue INTEGER,\n scrims_today INTEGER,\n streams_today INTEGER,\n committed_to_tournament INTEGER DEFAULT 0,\n active_tournament_id TEXT,\n lockout_until TEXT,\n salary INTEGER,\n snapshot_at INTEGER NOT NULL\n )`,\n );\n\n // Detected stat changes with activity attribution\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS roster_events (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n player_id TEXT NOT NULL,\n name TEXT,\n stat_name TEXT NOT NULL,\n old_value INTEGER,\n new_value INTEGER,\n delta INTEGER,\n attributed_activity TEXT,\n tournament_id TEXT,\n detected_at INTEGER NOT NULL\n )`,\n );\n\n // Player joins, departures, group moves\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS roster_changes (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n player_id TEXT NOT NULL,\n name TEXT,\n change_type TEXT NOT NULL,\n from_group TEXT,\n to_group TEXT,\n details TEXT,\n changed_at INTEGER NOT NULL\n )`,\n );\n\n // Development leads (hint clues from knowledge dossiers)\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS development_leads (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n clue_id TEXT UNIQUE NOT NULL,\n player_id TEXT NOT NULL,\n player_name TEXT,\n clue_text TEXT NOT NULL,\n completed INTEGER DEFAULT 0,\n first_seen_at INTEGER NOT NULL,\n completed_at INTEGER\n )`,\n );\n\n // Development history (history clues — turning points, contracts, etc.)\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS development_history (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n clue_id TEXT UNIQUE NOT NULL,\n player_id TEXT NOT NULL,\n player_name TEXT,\n clue_text TEXT NOT NULL,\n activity_key TEXT,\n activity_label TEXT,\n changes_json TEXT,\n occurred_at TEXT,\n first_seen_at INTEGER NOT NULL\n )`,\n );\n\n // Turning points — key story events for players\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS turning_points (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n player_id TEXT NOT NULL,\n player_name TEXT,\n description TEXT,\n arc_key TEXT,\n detected_at INTEGER NOT NULL\n )`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_tp_player ON turning_points(player_id)`,\n );\n\n // Activity sessions — one row per activity session (scrim/stream/tournament)\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS activity_sessions (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n player_id TEXT NOT NULL,\n player_name TEXT,\n activity TEXT NOT NULL,\n started_at INTEGER NOT NULL,\n lockout_until TEXT,\n detected_at INTEGER NOT NULL\n )`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_actsess_player ON activity_sessions(player_id)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_actsess_lockout ON activity_sessions(player_id, lockout_until)`,\n );\n\n // Add fog_pct to roster_snapshots (migration for existing table)\n try {\n await sqlite.execute(`ALTER TABLE roster_snapshots ADD COLUMN fog_pct INTEGER`);\n } catch { /* already exists */ }\n\n // Add estimated_rank and estimated_potential to roster_snapshots\n try {\n await sqlite.execute(`ALTER TABLE roster_snapshots ADD COLUMN estimated_rank TEXT`);\n } catch { /* already exists */ }\n try {\n await sqlite.execute(`ALTER TABLE roster_snapshots ADD COLUMN estimated_potential TEXT`);\n } catch { /* already exists */ }\n\n // Add details column to roster_events for turning point reasons, psych info, etc.\n try {\n await sqlite.execute(`ALTER TABLE roster_events ADD COLUMN details TEXT`);\n } catch { /* already exists */ }\n\n // Indexes for roster queries\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_snapshots_player ON roster_snapshots(player_id)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_snapshots_at ON roster_snapshots(snapshot_at)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_events_player ON roster_events(player_id)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_changes_player ON roster_changes(player_id)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_leads_player ON development_leads(player_id)`,\n );\n}\n",[182],"0dadd897-9ee4-4bf9-8469-e7fa4f668343","collect.ts","01a06c3e-3531-7518-8403-a70bcae040f2",["D",1788522411148],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\nconst API = \"https://apexmanager.rootsxo.studio/api/auctions\";\n\nexport default async function collect(_interval: Interval) {\n await initDb();\n\n // Ensure end_at column exists in listings table\n try {\n await sqlite.execute(`ALTER TABLE listings ADD COLUMN end_at INTEGER`);\n } catch (_e) {\n // Column already exists\n }\n\n const token = Deno.env.get(\"APEX_TOKEN\");\n if (!token) throw new Error(\"Missing APEX_TOKEN\");\n\n const resp = await fetch(API, { headers: { Authorization: `Bearer ${token}` } });\n if (!resp.ok) throw new Error(`API error: ${resp.status} ${resp.statusText}`);\n const data = await resp.json();\n const auctions = Array.isArray(data) ? data : (data.auctions ?? data.items ?? []);\n const now = Date.now();\n\n const activeAuctionIds: string[] = [];\n\n for (const a of auctions) {\n if (a.kind !== \"player\" || !a.player) continue;\n activeAuctionIds.push(a.id);\n const p = a.player;\n const intel = p.fogConfidence ?? 0;\n const fogPct = Math.round((1 - intel) * 100);\n const endAtMs = a.endAt ? new Date(a.endAt).getTime() : null;\n\n await sqlite.execute(\n `INSERT INTO listings\n (auction_id, player_id, name, role, age, growth_class, archetype,\n est_rank, est_potential, intel, fog_pct, salary, starting_bid, current_bid,\n first_seen_at, last_seen_at, end_at, left_at, player_json)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)\n ON CONFLICT(auction_id) DO UPDATE SET\n last_seen_at = excluded.last_seen_at,\n end_at = COALESCE(excluded.end_at, listings.end_at),\n current_bid = excluded.current_bid,\n left_at = NULL,\n player_json = excluded.player_json`,\n [\n a.id, p.id, p.name, p.role, p.age, p.growthClass, p.archetype ?? null,\n p.estimatedRank, p.estimatedPotential, intel, fogPct, p.salary ?? null,\n a.startingBid ?? null, a.currentBid ?? null,\n now, now, endAtMs, JSON.stringify(p),\n ],\n );\n }\n\n // If a listing wasn't in the active auctions response, mark it as left the market immediately\n if (activeAuctionIds.length > 0) {\n const placeholders = activeAuctionIds.map(() => \"?\").join(\",\");\n await sqlite.execute(\n `UPDATE listings SET left_at = ? WHERE left_at IS NULL AND auction_id NOT IN (${placeholders})`,\n [now, ...activeAuctionIds],\n );\n }\n\n const n = await sqlite.execute(`SELECT COUNT(*) AS c FROM listings`);\n const c = n.rows[0]?.c ?? 0;\n console.log(`Collect: ${auctions.length} auctions, ${c} tracked listings.`);\n}",[188],"b35fed05-01a0-4847-b833-9e9705ff0a1c","check.ts","01a06c3e-3531-7518-8403-a327a260c85f",["D",1788522411148],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb, isGem } from \"./db.ts\";\n\nconst API = \"https://apexmanager.rootsxo.studio/api/players/\";\nconst RE_CHECK_MS = 10 * 60 * 1000; // Retry unresolved/active players every 10 minutes\n\nasync function getCard(id: string): Promise {\n const token = Deno.env.get(\"APEX_TOKEN\");\n try {\n const r = await fetch(API + id, {\n headers: { Authorization: `Bearer ${token}` },\n });\n return r.ok ? await r.json() : null;\n } catch {\n return null;\n }\n}\n\nexport default async function check(_interval: Interval) {\n await initDb();\n const token = Deno.env.get(\"APEX_TOKEN\");\n if (!token) throw new Error(\"Missing APEX_TOKEN\");\n\n const now = Date.now();\n\n // Query ONLY players whose listings have definitively left the market (left_at IS NOT NULL)\n // regardless of whether their auction lasted 15 minutes, 6 hours, or 24 hours.\n const rows = await sqlite.execute(\n `SELECT l.player_id, l.name, MAX(l.left_at) AS left_market_at\n FROM listings l\n LEFT JOIN player_checks c ON c.player_id = l.player_id\n WHERE l.left_at IS NOT NULL\n AND (c.player_id IS NULL OR c.retired = 0)\n AND (c.player_id IS NULL OR c.last_checked_at < ?)\n GROUP BY l.player_id\n ORDER BY left_market_at ASC\n LIMIT 100`,\n [now - RE_CHECK_MS],\n );\n\n let checked = 0;\n let retiredFound = 0;\n\n for (const r of rows.rows as any[]) {\n const pid = r.player_id;\n const card = await getCard(pid);\n if (!card) continue;\n\n checked++;\n if (card.isRetired || card.trueRating != null) {\n // Retrieve earliest market display listing for this player\n const m = await sqlite.execute(\n `SELECT est_rank, est_potential, intel, fog_pct, salary, age\n FROM listings \n WHERE player_id = ? \n ORDER BY first_seen_at ASC \n LIMIT 1`,\n [pid],\n );\n const d = m.rows[0] as any;\n const gem = isGem(\n d?.est_potential ?? null,\n card.estimatedPotential ?? null,\n );\n\n await sqlite.execute(\n `INSERT INTO retired\n (player_id, name, market_est_rank, market_est_potential, market_intel,\n market_fog_pct, market_age, market_salary, true_rating, potential_rating, peak_rating,\n retired_rank, retired_potential, retired_at, checked_at, is_gem, card_url)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n ON CONFLICT(player_id) DO UPDATE SET\n true_rating = excluded.true_rating,\n potential_rating = excluded.potential_rating,\n peak_rating = excluded.peak_rating,\n retired_rank = excluded.retired_rank,\n retired_potential = excluded.retired_potential,\n checked_at = excluded.checked_at,\n is_gem = excluded.is_gem,\n card_url = excluded.card_url`,\n [\n pid,\n card.name ?? r.name,\n d?.est_rank ?? null,\n d?.est_potential ?? null,\n d?.intel ?? null,\n d?.fog_pct ?? null,\n d?.age ?? null,\n d?.salary ?? null,\n card.trueRating ?? null,\n card.potentialRating ?? null,\n card.peakRating ?? null,\n card.estimatedRank ?? null,\n card.estimatedPotential ?? null,\n now,\n now,\n gem ? 1 : 0,\n `https://apexmanager.rootsxo.studio/player/${pid}`,\n ],\n );\n\n await sqlite.execute(\n `INSERT INTO player_checks (player_id, name, last_checked_at, retired)\n VALUES (?,?,?,1)\n ON CONFLICT(player_id) DO UPDATE SET retired = 1, last_checked_at = excluded.last_checked_at`,\n [pid, card.name ?? r.name, now],\n );\n retiredFound++;\n } else {\n await sqlite.execute(\n `INSERT INTO player_checks (player_id, name, last_checked_at, retired)\n VALUES (?,?,?,0)\n ON CONFLICT(player_id) DO UPDATE SET last_checked_at = excluded.last_checked_at`,\n [pid, card.name ?? r.name, now],\n );\n }\n }\n\n console.log(\n `Check: ${checked} cards checked, ${retiredFound} retired/resolved.`,\n );\n}",[194],"c97e2158-3782-4d64-8145-241447a4bc5c","AGENTS.md","01a06c3e-3531-7518-8403-9ebcc109d10f",["D",1788522411148],"# apexGemTracker — Core Architecture & Context\n\nThis Val tracks Apex Manager market listings to find \"hidden gems\": players who displayed BELOW A- potential on the market but actually retired with A- or higher potential (i.e., fog hid their true quality)[cite: 1]. This is strictly a data collection and comparison pipeline with a web dashboard, entirely separate from notification/alert Vals[cite: 1].\n\n## ⚠️ Core Business Logic\n\n1. **The Gem Rule (`db.ts`)**\n `is_gem = isBelowAMinus(marketPotential) && !isBelowAMinus(retiredPotential)`[cite: 1, 4]\n *CRITICAL:* Always compare LETTER GRADES (`estimatedRank` / `estimatedPotential`), never the numeric `trueRating` or `potentialRating`[cite: 1]. The game's numeric ratings do not map to a reliable letter grade scale (e.g., a potential rating of 1986 might retire as a C/B, not an A)[cite: 1]. The authoritative \"true\" grade is the revealed letter grade on the retired card at 100% confidence[cite: 1].\n\n2. **Fog Semantics**\n `fogConfidence` (API) = INTEL[cite: 1]. Displayed \"Fog %\" = 100 - (fogConfidence * 100)[cite: 3]. High intel (low fog) = well-scouted; low intel (high fog) = obscured/lottery[cite: 1].\n\n## Data Model (SQLite)\n\n* **`listings`**: Snapshot of every market listing (keyed by `auction_id`)[cite: 1]. Captures the first-seen state (est rank/potential, intel, fog, salary, full `player_json`)[cite: 1, 3]. Upserted every run[cite: 1]. `left_at` is set when a listing stops appearing in the active auctions API[cite: 1, 3].\n* **`retired`**: Resolved players[cite: 1]. Compares market display vs. retired revealed grade (`retired_rank` / `retired_potential`)[cite: 1]. Stores market age/salary snapshot + true numeric ratings + `is_gem` flag + `card_url`[cite: 1, 2].\n* **`player_checks`**: Dedupe and backoff tracking table (checked every 10 minutes) so we don't spam the API for unresolved players[cite: 1, 2].\n\n## Scheduled Jobs (Cron)\n\n* **`collect.ts` (*/15 mins)**: Snapshots all `/api/auctions` listings[cite: 1]. Inserts new listings and updates `left_at` for expired auctions[cite: 3].\n* **`check.ts` (Hourly)**: Looks at listings that left the market >=1h ago[cite: 1]. Fetches `/api/players/{id}`[cite: 1]. If retired, calculates gem status and moves to `retired` table[cite: 1, 2]. If still active, backs off and checks again later[cite: 1]. \n\n## Web & API Endpoints (HTTP)\n\n* **`site.ts`**: The main frontend UI and API router[cite: 9]. Serves a rich HTML/JS dashboard at the root (`/`) with tabs for Live Market, Retired Players, and Advanced Analytics charts[cite: 6, 9]. Serves JSON endpoints at `/api/stats`, `/api/live`, `/api/retired`, and `/api/analytics`[cite: 9].\n* **`report.ts`**: Spreadsheet export router[cite: 1, 7]. Serves raw JSON and CSVs (e.g., `/csv` for gems, `/csv/listings` for active market)[cite: 1, 7]. Designed to be ingested directly by Google Sheets using the `IMPORTDATA()` function[cite: 1].\n* **`main.ts`**: A utility script to initialize the DB schema and print current table row counts[cite: 1, 5]. \n\n## Environment\n\n* `APEX_TOKEN`: The bearer token required to authenticate against the Apex Manager API[cite: 1]. All scripts will fail if this is missing or expired[cite: 1, 2, 3].",[200],"pulls","branches","forks","envVars",{"_212":61,"_214":61},"connections",{"_212":61,"_213":61},"count","unfilledCount","emptyCount",{"_42":199,"_32":200,"_30":201,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":253,"_13":61,"_33":20,"_35":203,"_24":48,"_148":-5,"_226":254,"_27":255},{"_42":193,"_32":194,"_30":195,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":250,"_13":61,"_33":160,"_35":197,"_24":48,"_148":-5,"_226":251,"_27":252},{"_42":187,"_32":188,"_30":189,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":247,"_13":61,"_33":160,"_35":191,"_24":48,"_148":-5,"_226":248,"_27":249},{"_42":181,"_32":182,"_30":183,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":244,"_13":61,"_33":34,"_35":185,"_24":48,"_148":-5,"_226":245,"_27":246},{"_42":175,"_32":176,"_30":177,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":241,"_13":61,"_33":34,"_35":179,"_24":48,"_148":-5,"_226":242,"_27":243},{"_42":169,"_32":170,"_30":171,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":238,"_13":61,"_33":34,"_35":173,"_24":48,"_148":-5,"_226":239,"_27":240},{"_42":163,"_32":164,"_30":165,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":235,"_13":61,"_33":146,"_35":167,"_24":48,"_148":-5,"_226":236,"_27":237},{"_42":156,"_32":157,"_30":158,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":232,"_13":61,"_33":160,"_35":161,"_24":48,"_148":-5,"_226":233,"_27":234},{"_42":150,"_32":151,"_30":152,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":229,"_13":61,"_33":34,"_35":154,"_24":48,"_148":-5,"_226":230,"_27":231},{"_42":139,"_32":140,"_30":141,"_142":-5,"_40":41,"_39":143,"_144":48,"_37":225,"_13":61,"_33":146,"_35":147,"_24":48,"_148":-5,"_226":227,"_27":228},["D",1788522411148],"children",[],[140],["D",1788522411148],[],[151],["D",1788522411148],[],[157],["D",1788522411148],[],[164],["D",1788522411148],[],[170],["D",1788522411148],[],[176],["D",1788522411148],[],[182],["D",1788522411148],[],[188],["D",1788522411148],[],[194],["D",1788522411148],[],[200],["D",1788522411148],["D",1789477862254],{"_98":97,"_106":102},{"_98":97,"_99":100,"_101":102,"_18":55,"_103":104,"_105":48},{"_13":87,"_32":88,"_42":41,"_89":43,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":44,"_96":97},"ctx",{"_80":-5,"_126":294,"_295":296,"_297":298,"_299":48,"_300":48,"_301":48,"_302":25,"_303":-5,"_304":-7,"_305":-7,"_306":61,"_307":48,"_308":48,"_309":-5,"_310":48,"_311":48,"_312":48,"_313":-5},"ENV",{"_265":266,"_267":268,"_269":88,"_270":271,"_272":273,"_274":275,"_276":277,"_278":279,"_280":281,"_282":283,"_284":285,"_286":287,"_288":289,"_290":291,"_292":293},"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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v2