<!\\[CDATA\\[(.*?)\\]\\]><\\/title>/)?.[1] || inner.match(/<title>(.*?)<\\/title>/)?.[1] || \"\";\n const link = inner.match(/<link>(.*?)<\\/link>/)?.[1] || \"\";\n const pubDate = inner.match(/<pubDate>(.*?)<\\/pubDate>/)?.[1] || \"\";\n const source = inner.match(/<source[^>]*>(.*?)<\\/source>/)?.[1] || \"\";\n const desc = inner.match(/<description><!\\[CDATA\\[(.*?)\\]\\]><\\/description>/)?.[1] || inner.match(/<description>(.*?)<\\/description>/)?.[1] || \"\";\n if (title && link) {\n items.push({\n title: title.trim(),\n url: link.trim(),\n source_name: source.trim() || \"Google News\",\n description: desc.trim(),\n published_at: pubDate ? Math.floor(new Date(pubDate).getTime() / 1000) : null,\n source_type: \"mainstream_news\",\n });\n }\n }\n return items.slice(0, 20);\n } catch {\n return [];\n }\n}\n\nasync function fetchNewsApi(brand: string): Promise<any[]> {\n const apiKey = Deno.env.get(\"NEWSAPI_KEY\");\n if (!apiKey) return [];\n try {\n const q = encodeURIComponent(brand);\n const url = `https://newsapi.org/v2/everything?q=${q}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${apiKey}`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.articles || []).map((a: any) => ({\n title: a.title || \"\",\n url: a.url || \"\",\n source_name: a.source?.name || \"NewsAPI\",\n description: a.description || \"\",\n published_at: a.publishedAt ? Math.floor(new Date(a.publishedAt).getTime() / 1000) : null,\n source_type: \"mainstream_news\",\n }));\n } catch {\n return [];\n }\n}\n\nasync function fetchHackerNews(brand: string): Promise<any[]> {\n try {\n const q = encodeURIComponent(brand);\n const url = `https://hn.algolia.com/api/v1/search_by_date?query=${q}&tags=story&hitsPerPage=20`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.hits || []).map((h: any) => ({\n title: h.title || \"\",\n url: h.url || `https://news.ycombinator.com/item?id=${h.objectID}`,\n source_name: \"Hacker News\",\n description: \"\",\n published_at: h.created_at ? Math.floor(new Date(h.created_at).getTime() / 1000) : null,\n source_type: \"tech_community\",\n }));\n } catch {\n return [];\n }\n}\n\n// ============================================================\n// COLLECTION PIPELINE\n// ============================================================\nasync function collectForBrand(brandId: number, brandName: string): Promise<{\n collected: number;\n filtered_domains: number;\n filtered_relevance: number;\n fetched: number;\n sources: Record<string, number>;\n}> {\n const stats = { collected: 0, filtered_domains: 0, filtered_relevance: 0, fetched: 0, sources: {} as Record<string, number> };\n\n // Step 1: Fetch from all sources\n const [googleArticles, newsApiArticles, hnArticles] = await Promise.all([\n fetchGoogleNews(brandName),\n fetchNewsApi(brandName),\n fetchHackerNews(brandName),\n ]);\n\n const allFetched = [\n ...googleArticles.map(a => ({ ...a, _source: \"google_news\" })),\n ...newsApiArticles.map(a => ({ ...a, _source: \"newsapi\" })),\n ...hnArticles.map(a => ({ ...a, _source: \"hacker_news\" })),\n ];\n\n stats.fetched = allFetched.length;\n\n // Step 2: Domain blocklist filter\n const domainPassed: typeof allFetched = [];\n for (const a of allFetched) {\n if (isBlockedDomain(a.url)) {\n stats.filtered_domains++;\n } else {\n domainPassed.push(a);\n }\n }\n\n // Step 3: Relevance filter (parallel, batched)\n const relevanceResults = await Promise.all(\n domainPassed.map(async a => {\n const score = await checkRelevance(brandName, a.title, a.description || \"\");\n return { article: a, score };\n })\n );\n\n const relevancePassed = relevanceResults.filter(r => r.score >= 60);\n stats.filtered_relevance = relevanceResults.length - relevancePassed.length;\n\n // Step 4: Sentiment + tier for remaining articles\n const analyzed = await Promise.all(\n relevancePassed.map(async ({ article, score }) => {\n const { sentiment, score: sentScore } = await analyzeSentiment(article.title, article.description || \"\");\n const tier = classifyTier(article.url);\n return { article, relevanceScore: score, sentiment, sentScore, tier };\n })\n );\n\n // Step 5: Insert into DB\n const sourceCounters: Record<string, number> = { google_news: 0, newsapi: 0, hacker_news: 0 };\n\n for (const { article, relevanceScore, sentiment, sentScore, tier } of analyzed) {\n try {\n const result = await sqlite.execute(\n `INSERT OR IGNORE INTO ${DB_NAME}_articles\n (brand_id, title, url, source_name, published_at, collected_at, sentiment, sentiment_score, source_type, publication_tier, relevance_score)\n VALUES (?, ?, ?, ?, ?, unixepoch(), ?, ?, ?, ?, ?)`,\n [brandId, article.title, article.url, article.source_name,\n article.published_at, sentiment, sentScore, article.source_type, tier, relevanceScore]\n );\n if ((result as any).rowsAffected > 0) {\n stats.collected++;\n const src = article._source;\n if (src in sourceCounters) sourceCounters[src]++;\n }\n } catch (_) {}\n }\n\n stats.sources = sourceCounters;\n\n // Update last_collected_at\n await sqlite.execute(\n `UPDATE ${DB_NAME}_brands SET last_collected_at = unixepoch() WHERE id = ?`,\n [brandId]\n );\n\n return stats;\n}\n\n// ============================================================\n// REPORT HELPERS\n// ============================================================\nfunction periodToCutoff(period: string): number {\n const now = Math.floor(Date.now() / 1000);\n switch (period) {\n case \"day\": return now - 86400;\n case \"month\": return now - 30 * 86400;\n default: return now - 7 * 86400; // week\n }\n}\n\n// ============================================================\n// HTTP HANDLER\n// ============================================================\nexport default async function handler(req: Request): Promise<Response> {\n const url = new URL(req.url);\n const path = url.pathname;\n const method = req.method;\n\n const cors = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, DELETE, OPTIONS\",\n \"Access-Control-Allow-Headers\": \"Content-Type\",\n };\n\n if (method === \"OPTIONS\") return new Response(null, { status: 204, headers: cors });\n\n const json = (data: any, status = 200) =>\n new Response(JSON.stringify(data), {\n status,\n headers: { ...cors, \"Content-Type\": \"application/json\" },\n });\n\n try {\n await initDb();\n\n // GET /api/brands\n if (path === \"/api/brands\" && method === \"GET\") {\n const result = await sqlite.execute(`\n SELECT b.id, b.name, b.created_at, b.last_collected_at,\n (SELECT COUNT(*) FROM ${DB_NAME}_articles WHERE brand_id = b.id) as article_count\n FROM ${DB_NAME}_brands b\n ORDER BY b.name ASC\n `);\n return json(result.rows.map(r => ({\n id: r[0], name: r[1], created_at: r[2], last_collected_at: r[3], article_count: r[4]\n })));\n }\n\n // POST /api/brands\n if (path === \"/api/brands\" && method === \"POST\") {\n const body = await req.json();\n const name = (body.name || \"\").trim();\n if (!name) return json({ error: \"Name required\" }, 400);\n const result = await sqlite.execute(\n `INSERT INTO ${DB_NAME}_brands (name) VALUES (?) RETURNING id, name, created_at`,\n [name]\n );\n const row = result.rows[0];\n return json({ id: row[0], name: row[1], created_at: row[2] }, 201);\n }\n\n // DELETE /api/brands/:id\n const brandDeleteMatch = path.match(/^\\/api\\/brands\\/(\\d+)$/);\n if (brandDeleteMatch && method === \"DELETE\") {\n const id = parseInt(brandDeleteMatch[1]);\n await sqlite.execute(`DELETE FROM ${DB_NAME}_articles WHERE brand_id = ?`, [id]);\n await sqlite.execute(`DELETE FROM ${DB_NAME}_brands WHERE id = ?`, [id]);\n return json({ deleted: true });\n }\n\n // POST /api/collect\n if (path === \"/api/collect\" && method === \"POST\") {\n const body = await req.json();\n const brandId = parseInt(body.brand_id);\n if (!brandId) return json({ error: \"brand_id required\" }, 400);\n const brandResult = await sqlite.execute(\n `SELECT id, name FROM ${DB_NAME}_brands WHERE id = ?`, [brandId]\n );\n if (!brandResult.rows.length) return json({ error: \"Brand not found\" }, 404);\n const brandName = brandResult.rows[0][1] as string;\n const stats = await collectForBrand(brandId, brandName);\n return json({\n collected: stats.collected,\n fetched: stats.fetched,\n filtered_domains: stats.filtered_domains,\n filtered_relevance: stats.filtered_relevance,\n sources: stats.sources,\n });\n }\n\n // GET /api/articles\n if (path === \"/api/articles\" && method === \"GET\") {\n const brandId = parseInt(url.searchParams.get(\"brand_id\") || \"0\");\n const limit = Math.min(parseInt(url.searchParams.get(\"limit\") || \"50\"), 200);\n if (!brandId) return json({ error: \"brand_id required\" }, 400);\n const result = await sqlite.execute(\n `SELECT id, title, url, source_name, published_at, sentiment, sentiment_score,\n source_type, publication_tier, collected_at\n FROM ${DB_NAME}_articles WHERE brand_id = ?\n ORDER BY collected_at DESC LIMIT ?`,\n [brandId, limit]\n );\n return json(result.rows.map(r => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3], published_at: r[4],\n sentiment: r[5], sentiment_score: r[6], source_type: r[7],\n publication_tier: r[8], collected_at: r[9],\n })));\n }\n\n // GET /api/report\n if (path === \"/api/report\" && method === \"GET\") {\n const brandId = parseInt(url.searchParams.get(\"brand_id\") || \"0\");\n const period = url.searchParams.get(\"period\") || \"week\";\n if (!brandId) return json({ error: \"brand_id required\" }, 400);\n\n const cutoff = periodToCutoff(period);\n\n const brandResult = await sqlite.execute(\n `SELECT id, name, last_collected_at FROM ${DB_NAME}_brands WHERE id = ?`, [brandId]\n );\n if (!brandResult.rows.length) return json({ error: \"Brand not found\" }, 404);\n const brand = brandResult.rows[0];\n\n const totalResult = await sqlite.execute(\n `SELECT COUNT(*) FROM ${DB_NAME}_articles WHERE brand_id = ? AND collected_at >= ?`,\n [brandId, cutoff]\n );\n const total = totalResult.rows[0][0] as number;\n\n const sentResult = await sqlite.execute(\n `SELECT sentiment, COUNT(*) FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY sentiment`,\n [brandId, cutoff]\n );\n const sentMap: Record<string, number> = { positive: 0, neutral: 0, negative: 0 };\n for (const r of sentResult.rows) sentMap[r[0] as string] = r[1] as number;\n\n const srcResult = await sqlite.execute(\n `SELECT source_name, COUNT(*) as cnt FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY source_name ORDER BY cnt DESC LIMIT 10`,\n [brandId, cutoff]\n );\n\n const srcTypeResult = await sqlite.execute(\n `SELECT source_type, COUNT(*) FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY source_type`,\n [brandId, cutoff]\n );\n const srcTypeMap: Record<string, number> = {};\n for (const r of srcTypeResult.rows) srcTypeMap[r[0] as string] = r[1] as number;\n\n const tierResult = await sqlite.execute(\n `SELECT publication_tier, COUNT(*) FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY publication_tier`,\n [brandId, cutoff]\n );\n const tierMap: Record<number, number> = {};\n for (const r of tierResult.rows) tierMap[r[0] as number] = r[1] as number;\n\n // daily_counts — bucket by collected_at date\n const dailyCutoff = Math.floor(Date.now() / 1000) - 14 * 86400;\n const dailyResult = await sqlite.execute(\n `SELECT date(collected_at, 'unixepoch') as day, COUNT(*) as cnt\n FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY day ORDER BY day ASC`,\n [brandId, dailyCutoff]\n );\n const dailyCounts = dailyResult.rows.map(r => ({ date: r[0] as string, count: r[1] as number }));\n\n const articlesResult = await sqlite.execute(\n `SELECT id, title, url, source_name, published_at, sentiment, sentiment_score,\n source_type, publication_tier, collected_at\n FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n ORDER BY collected_at DESC LIMIT 50`,\n [brandId, cutoff]\n );\n\n return json({\n brand: { id: brand[0], name: brand[1], last_collected_at: brand[2] },\n period,\n source_filter: \"mainstream_news\",\n total_articles: total,\n total_all_sources: total,\n sentiment_breakdown: sentMap,\n top_sources: srcResult.rows.map(r => ({ name: r[0], count: r[1] })),\n sources_breakdown: {\n google_news: srcTypeMap[\"google_news\"] || 0,\n newsapi: srcTypeMap[\"mainstream_news\"] || 0,\n hacker_news: srcTypeMap[\"tech_community\"] || 0,\n reddit: srcTypeMap[\"social\"] || 0,\n mainstream_news: srcTypeMap[\"mainstream_news\"] || 0,\n tech_community: srcTypeMap[\"tech_community\"] || 0,\n social: srcTypeMap[\"social\"] || 0,\n },\n tier_breakdown: {\n tier_1: tierMap[1] || 0,\n tier_2: tierMap[2] || 0,\n tier_3: tierMap[3] || 0,\n },\n daily_counts: dailyCounts,\n recent_articles: articlesResult.rows.map(r => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3],\n published_at: r[4], sentiment: r[5], sentiment_score: r[6],\n source_type: r[7], publication_tier: r[8], collected_at: r[9],\n })),\n });\n }\n\n return json({ error: \"Not found\", path }, 404);\n } catch (err: any) {\n console.error(\"Handler error:\", err);\n return json({ error: String(err?.message || err) }, 500);\n }\n}\n","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nexport default async function handler(req: Request): Promise<Response> {\n const cors = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Content-Type\": \"application/json\",\n };\n \n try {\n // List all tables\n const tables = await sqlite.execute(\n \"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\"\n );\n \n // Get info about each table\n const tableInfo: any[] = [];\n for (const row of tables.rows) {\n const name = row[0] as string;\n const count = await sqlite.execute(`SELECT COUNT(*) FROM \"${name}\"`);\n tableInfo.push({ name, count: count.rows[0][0] });\n }\n \n return new Response(JSON.stringify({ tables: tableInfo }), { headers: cors });\n } catch (err: any) {\n return new Response(JSON.stringify({ error: String(err) }), { status: 500, headers: cors });\n }\n}\n","createdAt",["D",1783291471036],"branchVersion","branchId","41a11757-2dbb-47d5-a624-ca5832847750","id","02b93d4c-8e45-4618-935a-44aaa786ed62","prMonitorApi",["D",1783289313194],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","PR monitoring API backend","imageUrl","019cdef0-5cd0-7261-bddf-67d3a4efcd7a","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019cdef0-5cd0-7261-bddf-6971337b2068","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_95":94,"_102":98},"org",{"_95":94,"_96":-5,"_97":98,"_18":55,"_99":100,"_101":25},"branch",{"_13":84,"_32":85,"_43":42,"_86":44,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":45,"_93":94},"isOwned",26,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","lobsterman","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBb2xGb1hUcU8ydWYzR0FNVzhFRDBaQk5SWCJ9","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_32":45,"_38":172,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":173,"_79":174,"_81":175,"_83":25},"tree",[153,155],"files",[153,154,155],"counts",{"_142":61,"_143":138,"_144":61,"_145":146,"_147":148},"contents",[126,127],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_43":135,"_32":136,"_30":137,"_129":-5,"_41":42,"_40":138,"_130":121,"_38":139,"_13":61,"_33":140,"_35":-5,"_24":25,"_133":-5,"_27":141},{"_43":128,"_32":28,"_30":31,"_129":-5,"_41":42,"_40":14,"_130":25,"_38":131,"_13":132,"_33":34,"_35":37,"_24":25,"_133":-5,"_27":134},"5a44d297-30d2-419c-82d5-d5081deae3ed","parentId","latest",["D",1783291471036],6,"label",[28],"4a6c5e85-b952-46dd-aaa1-9a41fcfb2f6e","backend","e767cc15-b91e-430a-9ff5-bbe470e7e661",1,["D",1783289363906],"directory",[136],"pulls","branches","forks","envVars",{"_149":151,"_152":61},"connections",{"_149":61,"_150":61},"count","unfilledCount",3,"emptyCount",{"_43":135,"_32":136,"_30":137,"_129":-5,"_41":42,"_40":138,"_130":121,"_38":169,"_13":61,"_33":140,"_35":-5,"_24":25,"_133":-5,"_157":170,"_27":171},{"_43":160,"_32":161,"_30":162,"_129":137,"_41":42,"_40":163,"_130":121,"_38":164,"_13":165,"_33":34,"_35":166,"_24":25,"_133":-5,"_157":167,"_27":168},{"_43":128,"_32":28,"_30":31,"_129":-5,"_41":42,"_40":14,"_130":25,"_38":156,"_13":132,"_33":34,"_35":37,"_24":25,"_133":-5,"_157":158,"_27":159},["D",1783291471036],"children",[],[28],"63ec8fca-157f-4969-b0ce-df63cc09c844","main.tsx","2e305658-78be-11f1-9027-1607ee4eb77e",9,["D",1783290840618],7,"/* Open in Val Town: https://www.val.town/x/lobsterman/prMonitorApi/code/backend/main.tsx */\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// Initialize tables - Phase 1 tables + Phase 2 additions\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS brands (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL UNIQUE,\n keywords TEXT DEFAULT '[]',\n created_at INTEGER DEFAULT (unixepoch())\n)`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS articles (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n brand_id INTEGER NOT NULL,\n title TEXT NOT NULL,\n url TEXT UNIQUE NOT NULL,\n source_name TEXT,\n published_at INTEGER,\n sentiment TEXT,\n sentiment_score REAL DEFAULT 0,\n source_type TEXT DEFAULT 'mainstream_news',\n publication_tier INTEGER DEFAULT 3,\n collected_at INTEGER DEFAULT (unixepoch())\n)`);\n\n// Add Phase 2 columns to existing articles table if they don't exist\ntry {\n await sqlite.execute(`ALTER TABLE articles ADD COLUMN source_type TEXT DEFAULT 'mainstream_news'`);\n} catch (_e) { /* column already exists */ }\ntry {\n await sqlite.execute(`ALTER TABLE articles ADD COLUMN publication_tier INTEGER DEFAULT 3`);\n} catch (_e) { /* column already exists */ }\n\n// Data migration: fix source_type for known community sources\nawait sqlite.execute(\n `UPDATE articles SET source_type = 'tech_community' WHERE source_name = 'Hacker News' AND source_type != 'tech_community'`\n);\nawait sqlite.execute(\n `UPDATE articles SET source_type = 'social' WHERE source_name LIKE 'Reddit%' AND source_type != 'social'`\n);\n// Fix publication_tier for known Tier 1/2 outlets (existing data)\nawait sqlite.execute(`UPDATE articles SET publication_tier = 1 WHERE source_name IN (\n 'Reuters','Bloomberg','BBC','CNN','NBC News','ABC News','CBS News','Fox News',\n 'NPR','USA Today','The Guardian','The Atlantic','Fortune','Time','Newsweek',\n 'Vogue','Wired','TechCrunch','The Verge','Engadget','Politico','Axios',\n 'CNBC','MarketWatch','Forbes','Business Insider','Yahoo Finance','Yahoo News'\n) AND publication_tier != 1`);\nawait sqlite.execute(`UPDATE articles SET publication_tier = 2 WHERE source_name IN (\n 'Slashdot','Ars Technica','Notebookcheck','Futurism','Electrek','ZDNet','CNET',\n 'PCMag','TechRadar','Gizmodo','9to5Mac','Slashdot.org','Notebookcheck.net',\n 'Futurism','Futurism.com','Electrek.co'\n) AND publication_tier NOT IN (1,2)`);\n// Fix Yahoo variants that appear in the DB\nawait sqlite.execute(`UPDATE articles SET publication_tier = 1 WHERE source_name LIKE 'Yahoo%' AND publication_tier != 1`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS daily_metrics (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n brand_id INTEGER NOT NULL,\n date TEXT NOT NULL,\n article_count INTEGER DEFAULT 0,\n positive_count INTEGER DEFAULT 0,\n neutral_count INTEGER DEFAULT 0,\n negative_count INTEGER DEFAULT 0,\n UNIQUE(brand_id, date)\n)`);\n\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, DELETE, OPTIONS\",\n \"Access-Control-Allow-Headers\": \"Content-Type\",\n};\n\nfunction jsonResponse(data: unknown, status = 200): Response {\n return new Response(JSON.stringify(data), {\n status,\n headers: { ...corsHeaders, \"Content-Type\": \"application/json\" },\n });\n}\n\n// ─── AI Helper: Generate Keywords ───────────────────────────────────────────\n\nasync function generateKeywords(brandName: string): Promise<string[]> {\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_API_KEY\");\n const keywordPrompt =\n `For a PR/media monitoring tool, generate 4-6 search keywords and phrases for the brand \"${brandName}\".\nInclude: product names, key people associated with the brand, common industry terms, and controversy topics if applicable.\nRespond with a JSON array of strings only. Example: [\"Tesla\", \"Elon Musk\", \"Model Y\", \"EV recall\", \"Cybertruck\"]`;\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: keywordPrompt }],\n max_tokens: 200,\n }),\n });\n if (!resp.ok) return [brandName];\n const result = await resp.json();\n const content = result.choices?.[0]?.message?.content || \"[]\";\n // Strip markdown code blocks if present\n const cleaned = content.replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const keywords = JSON.parse(cleaned);\n if (Array.isArray(keywords) && keywords.length > 0) return keywords;\n } catch (_e) {\n // Fall through\n }\n return [brandName];\n}\n\n// ─── AI Helper: Publication Tier Classification ──────────────────────────────\n\nasync function classifyPublicationTier(\n sourceName: string,\n): Promise<{ tier: number; source_type: string }> {\n // Known Tier 1 outlets (major national/international media)\n const tier1Outlets = [\n \"nytimes.com\", \"new york times\", \"wsj.com\", \"wall street journal\",\n \"washingtonpost.com\", \"washington post\", \"reuters.com\", \"reuters\",\n \"apnews.com\", \"associated press\", \"ap news\", \"bloomberg.com\", \"bloomberg\",\n \"ft.com\", \"financial times\", \"bbc.com\", \"bbc.co.uk\", \"bbc news\",\n \"cnn.com\", \"cnn\", \"nbcnews.com\", \"nbc news\", \"abcnews.go.com\", \"abc news\",\n \"cbsnews.com\", \"cbs news\", \"foxnews.com\", \"fox news\", \"npr.org\", \"npr\",\n \"usatoday.com\", \"usa today\", \"theguardian.com\", \"the guardian\",\n \"theatlantic.com\", \"the atlantic\", \"fortune.com\", \"fortune\", \"time.com\",\n \"newsweek.com\", \"newsweek\", \"vogue.com\", \"vogue\", \"wired.com\", \"wired\",\n \"techcrunch.com\", \"techcrunch\", \"theverge.com\", \"the verge\",\n \"engadget.com\", \"engadget\", \"newyorker.com\", \"new yorker\",\n \"politico.com\", \"politico\", \"axios.com\", \"axios\", \"thehill.com\", \"the hill\",\n \"cnbc.com\", \"cnbc\", \"marketwatch.com\", \"marketwatch\",\n \"businessinsider.com\", \"business insider\", \"yahoo finance\", \"yahoo news\",\n \"forbes.com\", \"forbes\",\n ];\n // Known Tier 2 outlets (industry trades, regional papers)\n const tier2Outlets = [\n \"adweek.com\", \"adweek\", \"adage.com\", \"ad age\", \"prweek.com\", \"pr week\",\n \"slashdot.org\", \"slashdot\", \"arstechnica.com\", \"ars technica\",\n \"notebookcheck.net\", \"notebookcheck\", \"9to5mac.com\", \"9to5mac\",\n \"electrek.co\", \"electrek\", \"theinformation.com\", \"the information\",\n \"zdnet.com\", \"zdnet\", \"cnet.com\", \"cnet\", \"pcmag.com\", \"pcmag\",\n \"techradar.com\", \"techradar\", \"gizmodo.com\", \"gizmodo\",\n \"futurism.com\", \"futurism\", \"redmondpie.com\", \"redmondpie\",\n ];\n // Social/community platforms\n const socialPatterns = [\"reddit\", \"r/\"];\n const techCommunityPatterns = [\"hacker news\", \"hackernews\", \"hn.algolia\", \"lobste.rs\", \"dev.to\"];\n const lc = sourceName.toLowerCase();\n\n if (techCommunityPatterns.some((p) => lc.includes(p))) {\n return { tier: 3, source_type: \"tech_community\" };\n }\n if (socialPatterns.some((p) => lc.includes(p))) {\n return { tier: 3, source_type: \"social\" };\n }\n if (tier1Outlets.some((p) => lc.includes(p))) {\n return { tier: 1, source_type: \"mainstream_news\" };\n }\n if (tier2Outlets.some((p) => lc.includes(p))) {\n return { tier: 2, source_type: \"mainstream_news\" };\n }\n // Use AI for unknown sources\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_API_KEY\");\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{\n role: \"user\",\n content:\n `Classify this publication for a PR professional: \"${sourceName}\"\\n\\nRespond with JSON only:\\n{\"tier\": 1|2|3, \"source_type\": \"mainstream_news\"|\"tech_community\"|\"social\"}\\n\\nTier 1 = major national outlets (NYT, WSJ, Vogue, CNN, Reuters)\\nTier 2 = industry trades or regional papers\\nTier 3 = blogs, minor sites, aggregators`,\n }],\n max_tokens: 60,\n }),\n });\n if (resp.ok) {\n const data = await resp.json();\n const content = data.choices?.[0]?.message?.content || \"{}\";\n const cleaned = content.replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(cleaned);\n if (parsed.tier && parsed.source_type) {\n return { tier: parsed.tier, source_type: parsed.source_type };\n }\n }\n } catch (_e) {\n // Fall through to default\n }\n return { tier: 3, source_type: \"mainstream_news\" };\n}\n\n// ─── AI Helper: Sentiment ────────────────────────────────────────────────────\n\nasync function getSentiment(title: string): Promise<{ sentiment: string; score: number }> {\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_API_KEY\");\n try {\n const aiResp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [\n {\n role: \"system\",\n content:\n 'Classify the sentiment of this news headline as exactly one word: positive, neutral, or negative. Then give a score from -1.0 to 1.0. Respond as JSON: {\"sentiment\": \"...\", \"score\": 0.0}',\n },\n { role: \"user\", content: title },\n ],\n max_tokens: 50,\n }),\n });\n if (aiResp.ok) {\n const aiData = await aiResp.json();\n const content = aiData.choices?.[0]?.message?.content || \"{}\";\n const parsed = JSON.parse(content);\n const sentiment = parsed.sentiment || \"neutral\";\n const score = parsed.score || 0.0;\n if ([\"positive\", \"neutral\", \"negative\"].includes(sentiment)) {\n return { sentiment, score };\n }\n }\n } catch (_e) {\n // Fall through to default\n }\n return { sentiment: \"neutral\", score: 0.0 };\n}\n\n// ─── Data Sources ────────────────────────────────────────────────────────────\n\nasync function fetchFromGoogleNews(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n const rssUrl =\n `https://news.google.com/rss/search?q=${encodeURIComponent(query)}&hl=en-US&gl=US&ceid=US:en`;\n try {\n const rssResp = await fetch(rssUrl, {\n headers: { \"User-Agent\": \"Mozilla/5.0 (compatible; PRMonitor/1.0)\" },\n });\n if (!rssResp.ok) return [];\n const xml = await rssResp.text();\n const itemRegex = /<item>([\\s\\S]*?)<\\/item>/g;\n const articles: Array<{ title: string; url: string; source: string; pubDate: number }> = [];\n let itemMatch;\n while ((itemMatch = itemRegex.exec(xml)) !== null) {\n const item = itemMatch[1];\n const titleMatch = item.match(/<title><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/title>/) ||\n item.match(/<title>([^<]+)<\\/title>/);\n const linkMatch = item.match(/<link>([^<]+)<\\/link>/);\n const sourceMatch = item.match(/<source[^>]*><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/source>/) ||\n item.match(/<source[^>]*>([^<]+)<\\/source>/);\n const pubDateMatch = item.match(/<pubDate>([^<]+)<\\/pubDate>/);\n if (titleMatch && linkMatch) {\n const pubDate = pubDateMatch\n ? Math.floor(new Date(pubDateMatch[1]).getTime() / 1000)\n : Math.floor(Date.now() / 1000);\n articles.push({\n title: titleMatch[1].trim(),\n url: linkMatch[1].trim(),\n source: sourceMatch ? sourceMatch[1].trim() : \"Google News\",\n pubDate,\n });\n }\n }\n return articles;\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchFromNewsApi(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n const newsApiKey = Deno.env.get(\"NEWS_API_KEY\");\n if (!newsApiKey) return [];\n try {\n const url =\n `https://newsapi.org/v2/everything?q=${encodeURIComponent(query)}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${newsApiKey}`;\n const resp = await fetch(url, {\n headers: { \"User-Agent\": \"PRMonitor/1.0\" },\n });\n if (!resp.ok) return [];\n const data = await resp.json();\n if (!data.articles || !Array.isArray(data.articles)) return [];\n return data.articles.map((a: {\n title?: string;\n url?: string;\n source?: { name?: string };\n publishedAt?: string;\n }) => ({\n title: a.title || \"Untitled\",\n url: a.url || \"\",\n source: a.source?.name || \"NewsAPI\",\n pubDate: a.publishedAt\n ? Math.floor(new Date(a.publishedAt).getTime() / 1000)\n : Math.floor(Date.now() / 1000),\n })).filter((a: { url: string }) => a.url);\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchHackerNews(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n try {\n const url =\n `https://hn.algolia.com/api/v1/search?query=${encodeURIComponent(query)}&tags=story&hitsPerPage=20`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.hits || []).map((hit: {\n title?: string;\n url?: string;\n objectID?: string;\n created_at_i?: number;\n }) => ({\n title: hit.title || \"Untitled\",\n url: hit.url || `https://news.ycombinator.com/item?id=${hit.objectID}`,\n source: \"Hacker News\",\n pubDate: Math.floor(hit.created_at_i || Date.now() / 1000),\n }));\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchReddit(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n try {\n const url =\n `https://www.reddit.com/search.json?q=${encodeURIComponent(query)}&sort=new&limit=20&t=week`;\n const resp = await fetch(url, { headers: { \"User-Agent\": \"PRMonitor/1.0\" } });\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.data?.children || []).map((post: {\n data: { title?: string; permalink?: string; subreddit?: string; created_utc?: number };\n }) => ({\n title: post.data.title || \"Untitled\",\n url: `https://reddit.com${post.data.permalink}`,\n source: `Reddit r/${post.data.subreddit}`,\n pubDate: Math.floor(post.data.created_utc || Date.now() / 1000),\n }));\n } catch (_e) {\n return [];\n }\n}\n\n// ─── Core Collection Logic ───────────────────────────────────────────────────\n\nasync function collectArticles(\n brandId: number,\n): Promise<{ collected: number; sources: Record<string, number> }> {\n const brandRows = await sqlite.execute({\n sql: `SELECT name, keywords FROM brands WHERE id = ?`,\n args: [brandId],\n });\n if (!brandRows.rows.length) return { collected: 0, sources: {} };\n\n const brandName = brandRows.rows[0][0] as string;\n const keywordsRaw = brandRows.rows[0][1] as string;\n let keywords: string[] = [brandName];\n try {\n const parsed = JSON.parse(keywordsRaw || \"[]\");\n if (Array.isArray(parsed) && parsed.length > 0) keywords = parsed;\n } catch (_e) {\n keywords = [brandName];\n }\n\n // Fetch mainstream sources for each keyword\n const sourceCounters: Record<string, number> = {\n google_news: 0,\n newsapi: 0,\n hacker_news: 0,\n reddit: 0,\n };\n\n const allRaw: Array<\n {\n title: string;\n url: string;\n source: string;\n pubDate: number;\n sourceType: string;\n tier: number;\n }\n > = [];\n\n // For Google News + NewsAPI: use all keywords\n const googleFetches = keywords.map((kw) => fetchFromGoogleNews(kw));\n const newsFetches = keywords.map((kw) => fetchFromNewsApi(kw));\n const [googleResults, newsResults] = await Promise.all([\n Promise.all(googleFetches),\n Promise.all(newsFetches),\n ]);\n\n for (const articles of googleResults) {\n for (const a of articles) allRaw.push({ ...a, sourceType: \"mainstream_news\", tier: 3 });\n }\n for (const articles of newsResults) {\n for (const a of articles) allRaw.push({ ...a, sourceType: \"mainstream_news\", tier: 3 });\n }\n\n // HN + Reddit: brand name only, marked as community sources\n const [hnArticles, redditArticles] = await Promise.all([\n fetchHackerNews(brandName),\n fetchReddit(brandName),\n ]);\n for (const a of hnArticles) allRaw.push({ ...a, sourceType: \"tech_community\", tier: 3 });\n for (const a of redditArticles) allRaw.push({ ...a, sourceType: \"social\", tier: 3 });\n\n // Deduplicate by URL\n const seen = new Set<string>();\n const deduped: typeof allRaw = [];\n for (const article of allRaw) {\n if (article.url && !seen.has(article.url)) {\n seen.add(article.url);\n deduped.push(article);\n }\n }\n\n // Cap at 50 per collection run\n const toProcess = deduped.slice(0, 50);\n\n let collected = 0;\n const today = new Date().toISOString().split(\"T\")[0];\n let posCount = 0, neuCount = 0, negCount = 0;\n\n // Classify publication tiers + get sentiment for each article\n // Process in batches to avoid overwhelming the AI proxy\n for (const article of toProcess) {\n // For community sources, use the pre-assigned type (don't run through classifyPublicationTier)\n // For mainstream sources, classify publication tier\n let tier: number;\n let source_type: string;\n if (article.sourceType === \"tech_community\" || article.sourceType === \"social\") {\n tier = 3;\n source_type = article.sourceType;\n } else {\n const classified = await classifyPublicationTier(article.source);\n tier = classified.tier;\n source_type = classified.source_type;\n }\n\n // Get sentiment\n const { sentiment, score } = await getSentiment(article.title);\n\n // Track source counts\n if (article.sourceType === \"tech_community\") {\n sourceCounters.hacker_news++;\n } else if (article.sourceType === \"social\") {\n sourceCounters.reddit++;\n } else {\n // For mainstream, check if it came from Google or NewsAPI by source name\n const isNewsApi = !article.source.toLowerCase().includes(\"google\");\n if (isNewsApi) {\n sourceCounters.newsapi++;\n } else {\n sourceCounters.google_news++;\n }\n }\n\n try {\n const insertResult = await sqlite.execute({\n sql:\n `INSERT OR IGNORE INTO articles (brand_id, title, url, source_name, published_at, sentiment, sentiment_score, source_type, publication_tier) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n brandId,\n article.title,\n article.url,\n article.source,\n article.pubDate,\n sentiment,\n score,\n source_type,\n tier,\n ],\n });\n if (insertResult.rowsAffected > 0) {\n collected++;\n if (sentiment === \"positive\") posCount++;\n else if (sentiment === \"negative\") negCount++;\n else neuCount++;\n }\n } catch (_e) {\n // Skip on error (URL already exists)\n }\n }\n\n // Update daily metrics\n if (collected > 0) {\n await sqlite.execute({\n sql: `INSERT INTO daily_metrics (brand_id, date, article_count, positive_count, neutral_count, negative_count)\n VALUES (?, ?, ?, ?, ?, ?)\n ON CONFLICT(brand_id, date) DO UPDATE SET\n article_count = article_count + excluded.article_count,\n positive_count = positive_count + excluded.positive_count,\n neutral_count = neutral_count + excluded.neutral_count,\n negative_count = negative_count + excluded.negative_count`,\n args: [brandId, today, collected, posCount, neuCount, negCount],\n });\n }\n\n return { collected, sources: sourceCounters };\n}\n\n// ─── HTTP Handler ────────────────────────────────────────────────────────────\n\nexport default async function handler(req: Request): Promise<Response> {\n const url = new URL(req.url);\n const path = url.pathname;\n\n if (req.method === \"OPTIONS\") {\n return new Response(null, { headers: corsHeaders });\n }\n\n // GET /api/brands\n if (req.method === \"GET\" && path === \"/api/brands\") {\n const rows = await sqlite.execute(\n `SELECT b.id, b.name, b.created_at, COUNT(a.id) as article_count\n FROM brands b LEFT JOIN articles a ON b.id = a.brand_id\n GROUP BY b.id ORDER BY b.name`,\n );\n const brands = rows.rows.map((r) => ({\n id: r[0],\n name: r[1],\n created_at: r[2],\n article_count: r[3],\n }));\n return jsonResponse(brands);\n }\n\n // POST /api/brands\n if (req.method === \"POST\" && path === \"/api/brands\") {\n const body = await req.json();\n if (!body.name) return jsonResponse({ error: \"name required\" }, 400);\n try {\n const existing = await sqlite.execute({\n sql: `SELECT id, name FROM brands WHERE name = ?`,\n args: [body.name],\n });\n if (existing.rows.length > 0) {\n return jsonResponse({ error: \"Brand already exists\" }, 409);\n }\n await sqlite.execute({\n sql: `INSERT INTO brands (name) VALUES (?)`,\n args: [body.name],\n });\n const newBrand = await sqlite.execute({\n sql: `SELECT id FROM brands WHERE name = ?`,\n args: [body.name],\n });\n const brandId = newBrand.rows[0][0] as number;\n\n // Generate keywords via AI\n const keywords = await generateKeywords(body.name);\n await sqlite.execute({\n sql: `UPDATE brands SET keywords = ? WHERE id = ?`,\n args: [JSON.stringify(keywords), brandId],\n });\n\n return jsonResponse({ id: brandId, name: body.name, keywords }, 201);\n } catch (e) {\n const msg = e instanceof Error ? e.message : String(e);\n if (msg.includes(\"UNIQUE\") || msg.includes(\"unique\")) {\n return jsonResponse({ error: \"Brand already exists\" }, 409);\n }\n return jsonResponse({ error: msg }, 500);\n }\n }\n\n // DELETE /api/brands/:id\n const brandDeleteMatch = path.match(/^\\/api\\/brands\\/(\\d+)$/);\n if (req.method === \"DELETE\" && brandDeleteMatch) {\n const id = parseInt(brandDeleteMatch[1]);\n await sqlite.execute({ sql: `DELETE FROM articles WHERE brand_id = ?`, args: [id] });\n await sqlite.execute({ sql: `DELETE FROM daily_metrics WHERE brand_id = ?`, args: [id] });\n await sqlite.execute({ sql: `DELETE FROM brands WHERE id = ?`, args: [id] });\n return jsonResponse({ deleted: true });\n }\n\n // GET /api/brands/:id/keywords\n const keywordsMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/keywords$/);\n if (req.method === \"GET\" && keywordsMatch) {\n const id = parseInt(keywordsMatch[1]);\n const rows = await sqlite.execute({\n sql: `SELECT id, name, keywords FROM brands WHERE id = ?`,\n args: [id],\n });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brand = rows.rows[0];\n let keywords: string[] = [];\n try {\n keywords = JSON.parse((brand[2] as string) || \"[]\");\n } catch (_e) {\n keywords = [];\n }\n return jsonResponse({ id: brand[0], name: brand[1], keywords });\n }\n\n // POST /api/brands/:id/keywords/refresh\n const keywordsRefreshMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/keywords\\/refresh$/);\n if (req.method === \"POST\" && keywordsRefreshMatch) {\n const id = parseInt(keywordsRefreshMatch[1]);\n const rows = await sqlite.execute({\n sql: `SELECT id, name FROM brands WHERE id = ?`,\n args: [id],\n });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brandName = rows.rows[0][1] as string;\n const keywords = await generateKeywords(brandName);\n await sqlite.execute({\n sql: `UPDATE brands SET keywords = ? WHERE id = ?`,\n args: [JSON.stringify(keywords), id],\n });\n return jsonResponse({ id, name: brandName, keywords });\n }\n\n // GET /api/articles?brand_id=1&days=7&source_type=mainstream_news\n if (req.method === \"GET\" && path === \"/api/articles\") {\n const brandId = url.searchParams.get(\"brand_id\");\n const days = parseInt(url.searchParams.get(\"days\") || \"7\");\n const sourceTypeFilter = url.searchParams.get(\"source_type\") || null;\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n let sql =\n `SELECT id, title, url, source_name, published_at, sentiment, sentiment_score, source_type, publication_tier, collected_at\n FROM articles WHERE brand_id = ? AND collected_at >= ?`;\n const args: (string | number)[] = [parseInt(brandId), cutoff];\n if (sourceTypeFilter) {\n sql += ` AND source_type = ?`;\n args.push(sourceTypeFilter);\n }\n sql += ` ORDER BY collected_at DESC LIMIT 100`;\n const rows = await sqlite.execute({ sql, args });\n const articles = rows.rows.map((r) => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3],\n published_at: r[4], sentiment: r[5], sentiment_score: r[6],\n source_type: r[7], publication_tier: r[8], collected_at: r[9],\n }));\n return jsonResponse(articles);\n }\n\n // GET /api/report?brand_id=1&period=week&source_type=mainstream_news\n if (req.method === \"GET\" && path === \"/api/report\") {\n const brandId = url.searchParams.get(\"brand_id\");\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const period = url.searchParams.get(\"period\") || \"week\";\n // Default to mainstream_news only (like a PR professional expects)\n const sourceTypeFilter = url.searchParams.get(\"source_type\") || \"mainstream_news\";\n const days = period === \"month\" ? 30 : period === \"day\" ? 1 : 7;\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n\n const brandRows = await sqlite.execute({\n sql: `SELECT id, name FROM brands WHERE id = ?`,\n args: [parseInt(brandId)],\n });\n if (!brandRows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brand = { id: brandRows.rows[0][0], name: brandRows.rows[0][1] };\n\n // All articles (for source breakdown counts)\n const allArticleRows = await sqlite.execute({\n sql:\n `SELECT id, source_type, publication_tier FROM articles WHERE brand_id = ? AND collected_at >= ?`,\n args: [parseInt(brandId), cutoff],\n });\n\n const sources_breakdown = {\n google_news: 0,\n newsapi: 0,\n hacker_news: 0,\n reddit: 0,\n mainstream_news: 0,\n tech_community: 0,\n social: 0,\n };\n const tier_breakdown = { tier_1: 0, tier_2: 0, tier_3: 0 };\n\n for (const r of allArticleRows.rows) {\n const st = r[1] as string;\n const tier = r[2] as number;\n if (st === \"mainstream_news\") sources_breakdown.mainstream_news++;\n else if (st === \"tech_community\") sources_breakdown.tech_community++;\n else if (st === \"social\") sources_breakdown.social++;\n if (tier === 1) tier_breakdown.tier_1++;\n else if (tier === 2) tier_breakdown.tier_2++;\n else tier_breakdown.tier_3++;\n }\n\n // Filtered articles for the report body\n let sql =\n `SELECT id, title, url, source_name, sentiment, sentiment_score, source_type, publication_tier, collected_at\n FROM articles WHERE brand_id = ? AND collected_at >= ?`;\n const args: (string | number)[] = [parseInt(brandId), cutoff];\n if (sourceTypeFilter && sourceTypeFilter !== \"all\") {\n sql += ` AND source_type = ?`;\n args.push(sourceTypeFilter);\n }\n sql += ` ORDER BY publication_tier ASC, collected_at DESC`;\n const articleRows = await sqlite.execute({ sql, args });\n\n const articles = articleRows.rows.map((r) => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3],\n sentiment: r[4], sentiment_score: r[5],\n source_type: r[6], publication_tier: r[7], collected_at: r[8],\n }));\n\n const sentimentBreakdown = { positive: 0, neutral: 0, negative: 0 };\n const sourceMap: Record<string, number> = {};\n\n for (const a of articles) {\n const s = a.sentiment as string;\n if (s in sentimentBreakdown) sentimentBreakdown[s as keyof typeof sentimentBreakdown]++;\n const src = (a.source_name as string) || \"Unknown\";\n sourceMap[src] = (sourceMap[src] || 0) + 1;\n }\n\n const topSources = Object.entries(sourceMap)\n .sort((a, b) => b[1] - a[1])\n .slice(0, 10)\n .map(([name, count]) => ({ name, count }));\n\n return jsonResponse({\n brand,\n period,\n source_filter: sourceTypeFilter,\n total_articles: articles.length,\n total_all_sources: allArticleRows.rows.length,\n sentiment_breakdown: sentimentBreakdown,\n top_sources: topSources,\n sources_breakdown,\n tier_breakdown,\n recent_articles: articles.slice(0, 20),\n });\n }\n\n // POST /api/collect\n if (req.method === \"POST\" && path === \"/api/collect\") {\n const body = await req.json();\n if (!body.brand_id) return jsonResponse({ error: \"brand_id required\" }, 400);\n const result = await collectArticles(body.brand_id);\n return jsonResponse(result);\n }\n\n return jsonResponse({ error: \"Not found\", path }, 404);\n}\n",[],[136,161],["D",1783289363906],[154],[136],["D",1783289313194],{"_95":94,"_102":98},{"_95":94,"_96":-5,"_97":98,"_18":55,"_99":100,"_101":25},{"_13":84,"_32":85,"_43":42,"_86":44,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":45,"_93":94},"ctx",{"_77":-5,"_123":209,"_210":211,"_212":213,"_214":25,"_215":25,"_216":25,"_217":121,"_218":-5,"_219":-7,"_220":-7,"_221":61,"_222":25,"_223":25,"_224":-5,"_225":25,"_226":25,"_227":25,"_228":-5},"ENV",{"_180":181,"_182":183,"_184":85,"_185":186,"_187":188,"_189":190,"_191":192,"_193":194,"_195":196,"_197":198,"_199":200,"_201":202,"_203":204,"_205":206,"_207":208},"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"] </article></body></html>

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_176":177,"_178":179},"routes/_app.x.$handle.$name",{"_95":94,"_32":45,"_103":85,"_27":104,"_13":14,"_105":-5,"_11":106,"_107":108,"_109":110,"_111":112,"_113":114,"_20":-5,"_115":-5,"_116":-5,"_117":25,"_118":119,"_120":121,"_122":-5,"_123":-5,"_124":125},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":78,"_79":80,"_81":82,"_83":25},"version",17,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","index.tsx","latestPath","fileId","6529976a-78c2-11f1-b86f-1607ee4eb77e","name","fileType","http","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nconst DB_NAME = \"prmoni\";\n\n// ============================================================\n// DOMAIN BLOCKLIST — skip articles from these domains\n// ============================================================\nconst NON_NEWS_DOMAINS = [\n \"bringatrailer.com\", \"ebay.com\", \"amazon.com\", \"etsy.com\",\n \"reddit.com\", \"facebook.com\", \"twitter.com\", \"instagram.com\",\n \"youtube.com\", \"tiktok.com\", \"linkedin.com\", \"pinterest.com\",\n \"craigslist.org\", \"autotrader.com\", \"cars.com\", \"carmax.com\",\n \"dealertrack.com\", \"manheim.com\", \"copart.com\",\n \"pypi.org\", \"npmjs.com\", \"github.com\", \"stackoverflow.com\",\n];\n\n// ============================================================\n// TIER CLASSIFICATION\n// ============================================================\nconst TIER1_DOMAINS = [\n \"nytimes.com\", \"wsj.com\", \"washingtonpost.com\", \"ft.com\", \"bloomberg.com\",\n \"reuters.com\", \"apnews.com\", \"bbc.com\", \"bbc.co.uk\", \"cnn.com\", \"foxnews.com\",\n \"nbcnews.com\", \"abcnews.go.com\", \"cbsnews.com\", \"usatoday.com\", \"latimes.com\",\n \"chicagotribune.com\", \"nypost.com\", \"thedailybeast.com\", \"theguardian.com\",\n \"forbes.com\", \"fortune.com\", \"businessinsider.com\", \"axios.com\",\n \"politico.com\", \"thehill.com\", \"vogue.com\", \"people.com\", \"variety.com\",\n \"hollywoodreporter.com\", \"wired.com\", \"techcrunch.com\", \"theverge.com\",\n \"engadget.com\", \"arstechnica.com\", \"marketwatch.com\", \"cnbc.com\",\n \"yahoo.com\", \"finance.yahoo.com\", \"apnews.com\", \"economist.com\",\n];\n\nfunction classifyTier(url: string): number {\n try {\n const domain = new URL(url).hostname.replace(/^www\\./, \"\");\n if (TIER1_DOMAINS.some(d => domain.includes(d))) return 1;\n if (domain.match(/blog\\.|blogspot\\.|wordpress\\.|medium\\.com|substack\\./)) return 3;\n return 2;\n } catch {\n return 3;\n }\n}\n\nfunction isBlockedDomain(url: string): boolean {\n try {\n const domain = new URL(url).hostname.replace(/^www\\./, \"\");\n return NON_NEWS_DOMAINS.some(d => domain.includes(d));\n } catch {\n return false;\n }\n}\n\n// ============================================================\n// DB SETUP\n// ============================================================\nasync function initDb() {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${DB_NAME}_brands (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL UNIQUE,\n created_at INTEGER DEFAULT (unixepoch()),\n last_collected_at INTEGER\n )\n `);\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${DB_NAME}_articles (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n brand_id INTEGER NOT NULL,\n title TEXT,\n url TEXT NOT NULL,\n source_name TEXT,\n published_at INTEGER,\n collected_at INTEGER DEFAULT (unixepoch()),\n sentiment TEXT DEFAULT 'neutral',\n sentiment_score REAL DEFAULT 0,\n source_type TEXT DEFAULT 'mainstream_news',\n publication_tier INTEGER DEFAULT 2,\n relevance_score INTEGER DEFAULT 100,\n UNIQUE(brand_id, url)\n )\n `);\n // Add missing columns if upgrading from earlier schema\n const cols = [\n `ALTER TABLE ${DB_NAME}_articles ADD COLUMN IF NOT EXISTS publication_tier INTEGER DEFAULT 2`,\n `ALTER TABLE ${DB_NAME}_articles ADD COLUMN IF NOT EXISTS relevance_score INTEGER DEFAULT 100`,\n ];\n for (const sql of cols) {\n try { await sqlite.execute(sql); } catch (_) {}\n }\n}\n\n// ============================================================\n// AI HELPERS — sentiment & relevance via OpenAI proxy\n// ============================================================\nasync function analyzeSentiment(title: string, description: string): Promise<{ sentiment: string; score: number }> {\n try {\n const prompt = `Rate the sentiment of this news headline about a company. Reply with exactly one word: positive, negative, or neutral, followed by a comma and a decimal score from -1.0 to 1.0.\n\nTitle: \"${title}\"\n${description ? `Description: \"${description.slice(0, 200)}\"` : \"\"}\n\nReply format: positive,0.8`;\n\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${Deno.env.get(\"valtown\")}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: prompt }],\n max_tokens: 20,\n }),\n });\n const data = await resp.json();\n const text = data?.choices?.[0]?.message?.content?.trim() || \"neutral,0\";\n const parts = text.split(\",\");\n const sentiment = [\"positive\", \"negative\", \"neutral\"].includes(parts[0]) ? parts[0] : \"neutral\";\n const score = parseFloat(parts[1] || \"0\") || 0;\n return { sentiment, score };\n } catch {\n return { sentiment: \"neutral\", score: 0 };\n }\n}\n\nasync function checkRelevance(brand: string, title: string, description: string): Promise {\n try {\n const prompt = `You are a media monitoring filter for a PR agency.\n\nRate how relevant this article is to \"${brand}\" as a COMPANY (not just any product or generic mention).\n\nAn article is HIGHLY RELEVANT (80-100) if it is primarily ABOUT the company: their business decisions, executive news, product launches, controversies, financial results, campaigns, or reputation.\n\nAn article is LOW RELEVANCE (0-40) if it merely mentions the brand incidentally: a car listing, a product review snippet, a marketplace post, a passing reference in an unrelated story, or content about an entirely different topic that happens to share a word.\n\nTitle: \"${title}\"\n${description ? `Description: \"${description.slice(0, 300)}\"` : \"\"}\n\nRespond with only a number 0-100.`;\n\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${Deno.env.get(\"valtown\")}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: prompt }],\n max_tokens: 10,\n }),\n });\n const data = await resp.json();\n const text = data?.choices?.[0]?.message?.content?.trim() || \"0\";\n return parseInt(text) || 0;\n } catch {\n return 50; // default to borderline so we don't silently drop on error\n }\n}\n\n// ============================================================\n// NEWS FETCHING\n// ============================================================\nasync function fetchGoogleNews(brand: string): Promise {\n try {\n const q = encodeURIComponent(`\"${brand}\"`);\n const url = `https://news.google.com/rss/search?q=${q}&hl=en-US&gl=US&ceid=US:en`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const xml = await resp.text();\n const items: any[] = [];\n const itemMatches = xml.matchAll(/([\\s\\S]*?)<\\/item>/g);\n for (const m of itemMatches) {\n const inner = m[1];\n const title = inner.match(/<!\\[CDATA\\[(.*?)\\]\\]><\\/title>/)?.[1] || inner.match(/<title>(.*?)<\\/title>/)?.[1] || \"\";\n const link = inner.match(/<link>(.*?)<\\/link>/)?.[1] || \"\";\n const pubDate = inner.match(/<pubDate>(.*?)<\\/pubDate>/)?.[1] || \"\";\n const source = inner.match(/<source[^>]*>(.*?)<\\/source>/)?.[1] || \"\";\n const desc = inner.match(/<description><!\\[CDATA\\[(.*?)\\]\\]><\\/description>/)?.[1] || inner.match(/<description>(.*?)<\\/description>/)?.[1] || \"\";\n if (title && link) {\n items.push({\n title: title.trim(),\n url: link.trim(),\n source_name: source.trim() || \"Google News\",\n description: desc.trim(),\n published_at: pubDate ? Math.floor(new Date(pubDate).getTime() / 1000) : null,\n source_type: \"mainstream_news\",\n });\n }\n }\n return items.slice(0, 20);\n } catch {\n return [];\n }\n}\n\nasync function fetchNewsApi(brand: string): Promise<any[]> {\n const apiKey = Deno.env.get(\"NEWSAPI_KEY\");\n if (!apiKey) return [];\n try {\n const q = encodeURIComponent(brand);\n const url = `https://newsapi.org/v2/everything?q=${q}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${apiKey}`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.articles || []).map((a: any) => ({\n title: a.title || \"\",\n url: a.url || \"\",\n source_name: a.source?.name || \"NewsAPI\",\n description: a.description || \"\",\n published_at: a.publishedAt ? Math.floor(new Date(a.publishedAt).getTime() / 1000) : null,\n source_type: \"mainstream_news\",\n }));\n } catch {\n return [];\n }\n}\n\nasync function fetchHackerNews(brand: string): Promise<any[]> {\n try {\n const q = encodeURIComponent(brand);\n const url = `https://hn.algolia.com/api/v1/search_by_date?query=${q}&tags=story&hitsPerPage=20`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.hits || []).map((h: any) => ({\n title: h.title || \"\",\n url: h.url || `https://news.ycombinator.com/item?id=${h.objectID}`,\n source_name: \"Hacker News\",\n description: \"\",\n published_at: h.created_at ? Math.floor(new Date(h.created_at).getTime() / 1000) : null,\n source_type: \"tech_community\",\n }));\n } catch {\n return [];\n }\n}\n\n// ============================================================\n// COLLECTION PIPELINE\n// ============================================================\nasync function collectForBrand(brandId: number, brandName: string): Promise<{\n collected: number;\n filtered_domains: number;\n filtered_relevance: number;\n fetched: number;\n sources: Record<string, number>;\n}> {\n const stats = { collected: 0, filtered_domains: 0, filtered_relevance: 0, fetched: 0, sources: {} as Record<string, number> };\n\n // Step 1: Fetch from all sources\n const [googleArticles, newsApiArticles, hnArticles] = await Promise.all([\n fetchGoogleNews(brandName),\n fetchNewsApi(brandName),\n fetchHackerNews(brandName),\n ]);\n\n const allFetched = [\n ...googleArticles.map(a => ({ ...a, _source: \"google_news\" })),\n ...newsApiArticles.map(a => ({ ...a, _source: \"newsapi\" })),\n ...hnArticles.map(a => ({ ...a, _source: \"hacker_news\" })),\n ];\n\n stats.fetched = allFetched.length;\n\n // Step 2: Domain blocklist filter\n const domainPassed: typeof allFetched = [];\n for (const a of allFetched) {\n if (isBlockedDomain(a.url)) {\n stats.filtered_domains++;\n } else {\n domainPassed.push(a);\n }\n }\n\n // Step 3: Relevance filter (parallel, batched)\n const relevanceResults = await Promise.all(\n domainPassed.map(async a => {\n const score = await checkRelevance(brandName, a.title, a.description || \"\");\n return { article: a, score };\n })\n );\n\n const relevancePassed = relevanceResults.filter(r => r.score >= 60);\n stats.filtered_relevance = relevanceResults.length - relevancePassed.length;\n\n // Step 4: Sentiment + tier for remaining articles\n const analyzed = await Promise.all(\n relevancePassed.map(async ({ article, score }) => {\n const { sentiment, score: sentScore } = await analyzeSentiment(article.title, article.description || \"\");\n const tier = classifyTier(article.url);\n return { article, relevanceScore: score, sentiment, sentScore, tier };\n })\n );\n\n // Step 5: Insert into DB\n const sourceCounters: Record<string, number> = { google_news: 0, newsapi: 0, hacker_news: 0 };\n\n for (const { article, relevanceScore, sentiment, sentScore, tier } of analyzed) {\n try {\n const result = await sqlite.execute(\n `INSERT OR IGNORE INTO ${DB_NAME}_articles\n (brand_id, title, url, source_name, published_at, collected_at, sentiment, sentiment_score, source_type, publication_tier, relevance_score)\n VALUES (?, ?, ?, ?, ?, unixepoch(), ?, ?, ?, ?, ?)`,\n [brandId, article.title, article.url, article.source_name,\n article.published_at, sentiment, sentScore, article.source_type, tier, relevanceScore]\n );\n if ((result as any).rowsAffected > 0) {\n stats.collected++;\n const src = article._source;\n if (src in sourceCounters) sourceCounters[src]++;\n }\n } catch (_) {}\n }\n\n stats.sources = sourceCounters;\n\n // Update last_collected_at\n await sqlite.execute(\n `UPDATE ${DB_NAME}_brands SET last_collected_at = unixepoch() WHERE id = ?`,\n [brandId]\n );\n\n return stats;\n}\n\n// ============================================================\n// REPORT HELPERS\n// ============================================================\nfunction periodToCutoff(period: string): number {\n const now = Math.floor(Date.now() / 1000);\n switch (period) {\n case \"day\": return now - 86400;\n case \"month\": return now - 30 * 86400;\n default: return now - 7 * 86400; // week\n }\n}\n\n// ============================================================\n// HTTP HANDLER\n// ============================================================\nexport default async function handler(req: Request): Promise<Response> {\n const url = new URL(req.url);\n const path = url.pathname;\n const method = req.method;\n\n const cors = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, DELETE, OPTIONS\",\n \"Access-Control-Allow-Headers\": \"Content-Type\",\n };\n\n if (method === \"OPTIONS\") return new Response(null, { status: 204, headers: cors });\n\n const json = (data: any, status = 200) =>\n new Response(JSON.stringify(data), {\n status,\n headers: { ...cors, \"Content-Type\": \"application/json\" },\n });\n\n try {\n await initDb();\n\n // GET /api/brands\n if (path === \"/api/brands\" && method === \"GET\") {\n const result = await sqlite.execute(`\n SELECT b.id, b.name, b.created_at, b.last_collected_at,\n (SELECT COUNT(*) FROM ${DB_NAME}_articles WHERE brand_id = b.id) as article_count\n FROM ${DB_NAME}_brands b\n ORDER BY b.name ASC\n `);\n return json(result.rows.map(r => ({\n id: r[0], name: r[1], created_at: r[2], last_collected_at: r[3], article_count: r[4]\n })));\n }\n\n // POST /api/brands\n if (path === \"/api/brands\" && method === \"POST\") {\n const body = await req.json();\n const name = (body.name || \"\").trim();\n if (!name) return json({ error: \"Name required\" }, 400);\n const result = await sqlite.execute(\n `INSERT INTO ${DB_NAME}_brands (name) VALUES (?) RETURNING id, name, created_at`,\n [name]\n );\n const row = result.rows[0];\n return json({ id: row[0], name: row[1], created_at: row[2] }, 201);\n }\n\n // DELETE /api/brands/:id\n const brandDeleteMatch = path.match(/^\\/api\\/brands\\/(\\d+)$/);\n if (brandDeleteMatch && method === \"DELETE\") {\n const id = parseInt(brandDeleteMatch[1]);\n await sqlite.execute(`DELETE FROM ${DB_NAME}_articles WHERE brand_id = ?`, [id]);\n await sqlite.execute(`DELETE FROM ${DB_NAME}_brands WHERE id = ?`, [id]);\n return json({ deleted: true });\n }\n\n // POST /api/collect\n if (path === \"/api/collect\" && method === \"POST\") {\n const body = await req.json();\n const brandId = parseInt(body.brand_id);\n if (!brandId) return json({ error: \"brand_id required\" }, 400);\n const brandResult = await sqlite.execute(\n `SELECT id, name FROM ${DB_NAME}_brands WHERE id = ?`, [brandId]\n );\n if (!brandResult.rows.length) return json({ error: \"Brand not found\" }, 404);\n const brandName = brandResult.rows[0][1] as string;\n const stats = await collectForBrand(brandId, brandName);\n return json({\n collected: stats.collected,\n fetched: stats.fetched,\n filtered_domains: stats.filtered_domains,\n filtered_relevance: stats.filtered_relevance,\n sources: stats.sources,\n });\n }\n\n // GET /api/articles\n if (path === \"/api/articles\" && method === \"GET\") {\n const brandId = parseInt(url.searchParams.get(\"brand_id\") || \"0\");\n const limit = Math.min(parseInt(url.searchParams.get(\"limit\") || \"50\"), 200);\n if (!brandId) return json({ error: \"brand_id required\" }, 400);\n const result = await sqlite.execute(\n `SELECT id, title, url, source_name, published_at, sentiment, sentiment_score,\n source_type, publication_tier, collected_at\n FROM ${DB_NAME}_articles WHERE brand_id = ?\n ORDER BY collected_at DESC LIMIT ?`,\n [brandId, limit]\n );\n return json(result.rows.map(r => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3], published_at: r[4],\n sentiment: r[5], sentiment_score: r[6], source_type: r[7],\n publication_tier: r[8], collected_at: r[9],\n })));\n }\n\n // GET /api/report\n if (path === \"/api/report\" && method === \"GET\") {\n const brandId = parseInt(url.searchParams.get(\"brand_id\") || \"0\");\n const period = url.searchParams.get(\"period\") || \"week\";\n if (!brandId) return json({ error: \"brand_id required\" }, 400);\n\n const cutoff = periodToCutoff(period);\n\n const brandResult = await sqlite.execute(\n `SELECT id, name, last_collected_at FROM ${DB_NAME}_brands WHERE id = ?`, [brandId]\n );\n if (!brandResult.rows.length) return json({ error: \"Brand not found\" }, 404);\n const brand = brandResult.rows[0];\n\n const totalResult = await sqlite.execute(\n `SELECT COUNT(*) FROM ${DB_NAME}_articles WHERE brand_id = ? AND collected_at >= ?`,\n [brandId, cutoff]\n );\n const total = totalResult.rows[0][0] as number;\n\n const sentResult = await sqlite.execute(\n `SELECT sentiment, COUNT(*) FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY sentiment`,\n [brandId, cutoff]\n );\n const sentMap: Record<string, number> = { positive: 0, neutral: 0, negative: 0 };\n for (const r of sentResult.rows) sentMap[r[0] as string] = r[1] as number;\n\n const srcResult = await sqlite.execute(\n `SELECT source_name, COUNT(*) as cnt FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY source_name ORDER BY cnt DESC LIMIT 10`,\n [brandId, cutoff]\n );\n\n const srcTypeResult = await sqlite.execute(\n `SELECT source_type, COUNT(*) FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY source_type`,\n [brandId, cutoff]\n );\n const srcTypeMap: Record<string, number> = {};\n for (const r of srcTypeResult.rows) srcTypeMap[r[0] as string] = r[1] as number;\n\n const tierResult = await sqlite.execute(\n `SELECT publication_tier, COUNT(*) FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY publication_tier`,\n [brandId, cutoff]\n );\n const tierMap: Record<number, number> = {};\n for (const r of tierResult.rows) tierMap[r[0] as number] = r[1] as number;\n\n // daily_counts — bucket by collected_at date\n const dailyCutoff = Math.floor(Date.now() / 1000) - 14 * 86400;\n const dailyResult = await sqlite.execute(\n `SELECT date(collected_at, 'unixepoch') as day, COUNT(*) as cnt\n FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n GROUP BY day ORDER BY day ASC`,\n [brandId, dailyCutoff]\n );\n const dailyCounts = dailyResult.rows.map(r => ({ date: r[0] as string, count: r[1] as number }));\n\n const articlesResult = await sqlite.execute(\n `SELECT id, title, url, source_name, published_at, sentiment, sentiment_score,\n source_type, publication_tier, collected_at\n FROM ${DB_NAME}_articles\n WHERE brand_id = ? AND collected_at >= ?\n ORDER BY collected_at DESC LIMIT 50`,\n [brandId, cutoff]\n );\n\n return json({\n brand: { id: brand[0], name: brand[1], last_collected_at: brand[2] },\n period,\n source_filter: \"mainstream_news\",\n total_articles: total,\n total_all_sources: total,\n sentiment_breakdown: sentMap,\n top_sources: srcResult.rows.map(r => ({ name: r[0], count: r[1] })),\n sources_breakdown: {\n google_news: srcTypeMap[\"google_news\"] || 0,\n newsapi: srcTypeMap[\"mainstream_news\"] || 0,\n hacker_news: srcTypeMap[\"tech_community\"] || 0,\n reddit: srcTypeMap[\"social\"] || 0,\n mainstream_news: srcTypeMap[\"mainstream_news\"] || 0,\n tech_community: srcTypeMap[\"tech_community\"] || 0,\n social: srcTypeMap[\"social\"] || 0,\n },\n tier_breakdown: {\n tier_1: tierMap[1] || 0,\n tier_2: tierMap[2] || 0,\n tier_3: tierMap[3] || 0,\n },\n daily_counts: dailyCounts,\n recent_articles: articlesResult.rows.map(r => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3],\n published_at: r[4], sentiment: r[5], sentiment_score: r[6],\n source_type: r[7], publication_tier: r[8], collected_at: r[9],\n })),\n });\n }\n\n return json({ error: \"Not found\", path }, 404);\n } catch (err: any) {\n console.error(\"Handler error:\", err);\n return json({ error: String(err?.message || err) }, 500);\n }\n}\n","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nexport default async function handler(req: Request): Promise<Response> {\n const cors = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Content-Type\": \"application/json\",\n };\n \n try {\n // List all tables\n const tables = await sqlite.execute(\n \"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name\"\n );\n \n // Get info about each table\n const tableInfo: any[] = [];\n for (const row of tables.rows) {\n const name = row[0] as string;\n const count = await sqlite.execute(`SELECT COUNT(*) FROM \"${name}\"`);\n tableInfo.push({ name, count: count.rows[0][0] });\n }\n \n return new Response(JSON.stringify({ tables: tableInfo }), { headers: cors });\n } catch (err: any) {\n return new Response(JSON.stringify({ error: String(err) }), { status: 500, headers: cors });\n }\n}\n","createdAt",["D",1783291471036],"branchVersion","branchId","41a11757-2dbb-47d5-a624-ca5832847750","id","02b93d4c-8e45-4618-935a-44aaa786ed62","prMonitorApi",["D",1783289313194],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","PR monitoring API backend","imageUrl","019cdef0-5cd0-7261-bddf-67d3a4efcd7a","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019cdef0-5cd0-7261-bddf-6971337b2068","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_95":94,"_102":98},"org",{"_95":94,"_96":-5,"_97":98,"_18":55,"_99":100,"_101":25},"branch",{"_13":84,"_32":85,"_43":42,"_86":44,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":45,"_93":94},"isOwned",26,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","lobsterman","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBb2xGb1hUcU8ydWYzR0FNVzhFRDBaQk5SWCJ9","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_32":45,"_38":172,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":173,"_79":174,"_81":175,"_83":25},"tree",[153,155],"files",[153,154,155],"counts",{"_142":61,"_143":138,"_144":61,"_145":146,"_147":148},"contents",[126,127],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_43":135,"_32":136,"_30":137,"_129":-5,"_41":42,"_40":138,"_130":121,"_38":139,"_13":61,"_33":140,"_35":-5,"_24":25,"_133":-5,"_27":141},{"_43":128,"_32":28,"_30":31,"_129":-5,"_41":42,"_40":14,"_130":25,"_38":131,"_13":132,"_33":34,"_35":37,"_24":25,"_133":-5,"_27":134},"5a44d297-30d2-419c-82d5-d5081deae3ed","parentId","latest",["D",1783291471036],6,"label",[28],"4a6c5e85-b952-46dd-aaa1-9a41fcfb2f6e","backend","e767cc15-b91e-430a-9ff5-bbe470e7e661",1,["D",1783289363906],"directory",[136],"pulls","branches","forks","envVars",{"_149":151,"_152":61},"connections",{"_149":61,"_150":61},"count","unfilledCount",3,"emptyCount",{"_43":135,"_32":136,"_30":137,"_129":-5,"_41":42,"_40":138,"_130":121,"_38":169,"_13":61,"_33":140,"_35":-5,"_24":25,"_133":-5,"_157":170,"_27":171},{"_43":160,"_32":161,"_30":162,"_129":137,"_41":42,"_40":163,"_130":121,"_38":164,"_13":165,"_33":34,"_35":166,"_24":25,"_133":-5,"_157":167,"_27":168},{"_43":128,"_32":28,"_30":31,"_129":-5,"_41":42,"_40":14,"_130":25,"_38":156,"_13":132,"_33":34,"_35":37,"_24":25,"_133":-5,"_157":158,"_27":159},["D",1783291471036],"children",[],[28],"63ec8fca-157f-4969-b0ce-df63cc09c844","main.tsx","2e305658-78be-11f1-9027-1607ee4eb77e",9,["D",1783290840618],7,"/* Open in Val Town: https://www.val.town/x/lobsterman/prMonitorApi/code/backend/main.tsx */\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// Initialize tables - Phase 1 tables + Phase 2 additions\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS brands (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL UNIQUE,\n keywords TEXT DEFAULT '[]',\n created_at INTEGER DEFAULT (unixepoch())\n)`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS articles (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n brand_id INTEGER NOT NULL,\n title TEXT NOT NULL,\n url TEXT UNIQUE NOT NULL,\n source_name TEXT,\n published_at INTEGER,\n sentiment TEXT,\n sentiment_score REAL DEFAULT 0,\n source_type TEXT DEFAULT 'mainstream_news',\n publication_tier INTEGER DEFAULT 3,\n collected_at INTEGER DEFAULT (unixepoch())\n)`);\n\n// Add Phase 2 columns to existing articles table if they don't exist\ntry {\n await sqlite.execute(`ALTER TABLE articles ADD COLUMN source_type TEXT DEFAULT 'mainstream_news'`);\n} catch (_e) { /* column already exists */ }\ntry {\n await sqlite.execute(`ALTER TABLE articles ADD COLUMN publication_tier INTEGER DEFAULT 3`);\n} catch (_e) { /* column already exists */ }\n\n// Data migration: fix source_type for known community sources\nawait sqlite.execute(\n `UPDATE articles SET source_type = 'tech_community' WHERE source_name = 'Hacker News' AND source_type != 'tech_community'`\n);\nawait sqlite.execute(\n `UPDATE articles SET source_type = 'social' WHERE source_name LIKE 'Reddit%' AND source_type != 'social'`\n);\n// Fix publication_tier for known Tier 1/2 outlets (existing data)\nawait sqlite.execute(`UPDATE articles SET publication_tier = 1 WHERE source_name IN (\n 'Reuters','Bloomberg','BBC','CNN','NBC News','ABC News','CBS News','Fox News',\n 'NPR','USA Today','The Guardian','The Atlantic','Fortune','Time','Newsweek',\n 'Vogue','Wired','TechCrunch','The Verge','Engadget','Politico','Axios',\n 'CNBC','MarketWatch','Forbes','Business Insider','Yahoo Finance','Yahoo News'\n) AND publication_tier != 1`);\nawait sqlite.execute(`UPDATE articles SET publication_tier = 2 WHERE source_name IN (\n 'Slashdot','Ars Technica','Notebookcheck','Futurism','Electrek','ZDNet','CNET',\n 'PCMag','TechRadar','Gizmodo','9to5Mac','Slashdot.org','Notebookcheck.net',\n 'Futurism','Futurism.com','Electrek.co'\n) AND publication_tier NOT IN (1,2)`);\n// Fix Yahoo variants that appear in the DB\nawait sqlite.execute(`UPDATE articles SET publication_tier = 1 WHERE source_name LIKE 'Yahoo%' AND publication_tier != 1`);\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS daily_metrics (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n brand_id INTEGER NOT NULL,\n date TEXT NOT NULL,\n article_count INTEGER DEFAULT 0,\n positive_count INTEGER DEFAULT 0,\n neutral_count INTEGER DEFAULT 0,\n negative_count INTEGER DEFAULT 0,\n UNIQUE(brand_id, date)\n)`);\n\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, DELETE, OPTIONS\",\n \"Access-Control-Allow-Headers\": \"Content-Type\",\n};\n\nfunction jsonResponse(data: unknown, status = 200): Response {\n return new Response(JSON.stringify(data), {\n status,\n headers: { ...corsHeaders, \"Content-Type\": \"application/json\" },\n });\n}\n\n// ─── AI Helper: Generate Keywords ───────────────────────────────────────────\n\nasync function generateKeywords(brandName: string): Promise<string[]> {\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_API_KEY\");\n const keywordPrompt =\n `For a PR/media monitoring tool, generate 4-6 search keywords and phrases for the brand \"${brandName}\".\nInclude: product names, key people associated with the brand, common industry terms, and controversy topics if applicable.\nRespond with a JSON array of strings only. Example: [\"Tesla\", \"Elon Musk\", \"Model Y\", \"EV recall\", \"Cybertruck\"]`;\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: keywordPrompt }],\n max_tokens: 200,\n }),\n });\n if (!resp.ok) return [brandName];\n const result = await resp.json();\n const content = result.choices?.[0]?.message?.content || \"[]\";\n // Strip markdown code blocks if present\n const cleaned = content.replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const keywords = JSON.parse(cleaned);\n if (Array.isArray(keywords) && keywords.length > 0) return keywords;\n } catch (_e) {\n // Fall through\n }\n return [brandName];\n}\n\n// ─── AI Helper: Publication Tier Classification ──────────────────────────────\n\nasync function classifyPublicationTier(\n sourceName: string,\n): Promise<{ tier: number; source_type: string }> {\n // Known Tier 1 outlets (major national/international media)\n const tier1Outlets = [\n \"nytimes.com\", \"new york times\", \"wsj.com\", \"wall street journal\",\n \"washingtonpost.com\", \"washington post\", \"reuters.com\", \"reuters\",\n \"apnews.com\", \"associated press\", \"ap news\", \"bloomberg.com\", \"bloomberg\",\n \"ft.com\", \"financial times\", \"bbc.com\", \"bbc.co.uk\", \"bbc news\",\n \"cnn.com\", \"cnn\", \"nbcnews.com\", \"nbc news\", \"abcnews.go.com\", \"abc news\",\n \"cbsnews.com\", \"cbs news\", \"foxnews.com\", \"fox news\", \"npr.org\", \"npr\",\n \"usatoday.com\", \"usa today\", \"theguardian.com\", \"the guardian\",\n \"theatlantic.com\", \"the atlantic\", \"fortune.com\", \"fortune\", \"time.com\",\n \"newsweek.com\", \"newsweek\", \"vogue.com\", \"vogue\", \"wired.com\", \"wired\",\n \"techcrunch.com\", \"techcrunch\", \"theverge.com\", \"the verge\",\n \"engadget.com\", \"engadget\", \"newyorker.com\", \"new yorker\",\n \"politico.com\", \"politico\", \"axios.com\", \"axios\", \"thehill.com\", \"the hill\",\n \"cnbc.com\", \"cnbc\", \"marketwatch.com\", \"marketwatch\",\n \"businessinsider.com\", \"business insider\", \"yahoo finance\", \"yahoo news\",\n \"forbes.com\", \"forbes\",\n ];\n // Known Tier 2 outlets (industry trades, regional papers)\n const tier2Outlets = [\n \"adweek.com\", \"adweek\", \"adage.com\", \"ad age\", \"prweek.com\", \"pr week\",\n \"slashdot.org\", \"slashdot\", \"arstechnica.com\", \"ars technica\",\n \"notebookcheck.net\", \"notebookcheck\", \"9to5mac.com\", \"9to5mac\",\n \"electrek.co\", \"electrek\", \"theinformation.com\", \"the information\",\n \"zdnet.com\", \"zdnet\", \"cnet.com\", \"cnet\", \"pcmag.com\", \"pcmag\",\n \"techradar.com\", \"techradar\", \"gizmodo.com\", \"gizmodo\",\n \"futurism.com\", \"futurism\", \"redmondpie.com\", \"redmondpie\",\n ];\n // Social/community platforms\n const socialPatterns = [\"reddit\", \"r/\"];\n const techCommunityPatterns = [\"hacker news\", \"hackernews\", \"hn.algolia\", \"lobste.rs\", \"dev.to\"];\n const lc = sourceName.toLowerCase();\n\n if (techCommunityPatterns.some((p) => lc.includes(p))) {\n return { tier: 3, source_type: \"tech_community\" };\n }\n if (socialPatterns.some((p) => lc.includes(p))) {\n return { tier: 3, source_type: \"social\" };\n }\n if (tier1Outlets.some((p) => lc.includes(p))) {\n return { tier: 1, source_type: \"mainstream_news\" };\n }\n if (tier2Outlets.some((p) => lc.includes(p))) {\n return { tier: 2, source_type: \"mainstream_news\" };\n }\n // Use AI for unknown sources\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_API_KEY\");\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{\n role: \"user\",\n content:\n `Classify this publication for a PR professional: \"${sourceName}\"\\n\\nRespond with JSON only:\\n{\"tier\": 1|2|3, \"source_type\": \"mainstream_news\"|\"tech_community\"|\"social\"}\\n\\nTier 1 = major national outlets (NYT, WSJ, Vogue, CNN, Reuters)\\nTier 2 = industry trades or regional papers\\nTier 3 = blogs, minor sites, aggregators`,\n }],\n max_tokens: 60,\n }),\n });\n if (resp.ok) {\n const data = await resp.json();\n const content = data.choices?.[0]?.message?.content || \"{}\";\n const cleaned = content.replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(cleaned);\n if (parsed.tier && parsed.source_type) {\n return { tier: parsed.tier, source_type: parsed.source_type };\n }\n }\n } catch (_e) {\n // Fall through to default\n }\n return { tier: 3, source_type: \"mainstream_news\" };\n}\n\n// ─── AI Helper: Sentiment ────────────────────────────────────────────────────\n\nasync function getSentiment(title: string): Promise<{ sentiment: string; score: number }> {\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_API_KEY\");\n try {\n const aiResp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${apiKey}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [\n {\n role: \"system\",\n content:\n 'Classify the sentiment of this news headline as exactly one word: positive, neutral, or negative. Then give a score from -1.0 to 1.0. Respond as JSON: {\"sentiment\": \"...\", \"score\": 0.0}',\n },\n { role: \"user\", content: title },\n ],\n max_tokens: 50,\n }),\n });\n if (aiResp.ok) {\n const aiData = await aiResp.json();\n const content = aiData.choices?.[0]?.message?.content || \"{}\";\n const parsed = JSON.parse(content);\n const sentiment = parsed.sentiment || \"neutral\";\n const score = parsed.score || 0.0;\n if ([\"positive\", \"neutral\", \"negative\"].includes(sentiment)) {\n return { sentiment, score };\n }\n }\n } catch (_e) {\n // Fall through to default\n }\n return { sentiment: \"neutral\", score: 0.0 };\n}\n\n// ─── Data Sources ────────────────────────────────────────────────────────────\n\nasync function fetchFromGoogleNews(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n const rssUrl =\n `https://news.google.com/rss/search?q=${encodeURIComponent(query)}&hl=en-US&gl=US&ceid=US:en`;\n try {\n const rssResp = await fetch(rssUrl, {\n headers: { \"User-Agent\": \"Mozilla/5.0 (compatible; PRMonitor/1.0)\" },\n });\n if (!rssResp.ok) return [];\n const xml = await rssResp.text();\n const itemRegex = /<item>([\\s\\S]*?)<\\/item>/g;\n const articles: Array<{ title: string; url: string; source: string; pubDate: number }> = [];\n let itemMatch;\n while ((itemMatch = itemRegex.exec(xml)) !== null) {\n const item = itemMatch[1];\n const titleMatch = item.match(/<title><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/title>/) ||\n item.match(/<title>([^<]+)<\\/title>/);\n const linkMatch = item.match(/<link>([^<]+)<\\/link>/);\n const sourceMatch = item.match(/<source[^>]*><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/source>/) ||\n item.match(/<source[^>]*>([^<]+)<\\/source>/);\n const pubDateMatch = item.match(/<pubDate>([^<]+)<\\/pubDate>/);\n if (titleMatch && linkMatch) {\n const pubDate = pubDateMatch\n ? Math.floor(new Date(pubDateMatch[1]).getTime() / 1000)\n : Math.floor(Date.now() / 1000);\n articles.push({\n title: titleMatch[1].trim(),\n url: linkMatch[1].trim(),\n source: sourceMatch ? sourceMatch[1].trim() : \"Google News\",\n pubDate,\n });\n }\n }\n return articles;\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchFromNewsApi(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n const newsApiKey = Deno.env.get(\"NEWS_API_KEY\");\n if (!newsApiKey) return [];\n try {\n const url =\n `https://newsapi.org/v2/everything?q=${encodeURIComponent(query)}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${newsApiKey}`;\n const resp = await fetch(url, {\n headers: { \"User-Agent\": \"PRMonitor/1.0\" },\n });\n if (!resp.ok) return [];\n const data = await resp.json();\n if (!data.articles || !Array.isArray(data.articles)) return [];\n return data.articles.map((a: {\n title?: string;\n url?: string;\n source?: { name?: string };\n publishedAt?: string;\n }) => ({\n title: a.title || \"Untitled\",\n url: a.url || \"\",\n source: a.source?.name || \"NewsAPI\",\n pubDate: a.publishedAt\n ? Math.floor(new Date(a.publishedAt).getTime() / 1000)\n : Math.floor(Date.now() / 1000),\n })).filter((a: { url: string }) => a.url);\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchHackerNews(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n try {\n const url =\n `https://hn.algolia.com/api/v1/search?query=${encodeURIComponent(query)}&tags=story&hitsPerPage=20`;\n const resp = await fetch(url);\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.hits || []).map((hit: {\n title?: string;\n url?: string;\n objectID?: string;\n created_at_i?: number;\n }) => ({\n title: hit.title || \"Untitled\",\n url: hit.url || `https://news.ycombinator.com/item?id=${hit.objectID}`,\n source: \"Hacker News\",\n pubDate: Math.floor(hit.created_at_i || Date.now() / 1000),\n }));\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchReddit(\n query: string,\n): Promise<Array<{ title: string; url: string; source: string; pubDate: number }>> {\n try {\n const url =\n `https://www.reddit.com/search.json?q=${encodeURIComponent(query)}&sort=new&limit=20&t=week`;\n const resp = await fetch(url, { headers: { \"User-Agent\": \"PRMonitor/1.0\" } });\n if (!resp.ok) return [];\n const data = await resp.json();\n return (data.data?.children || []).map((post: {\n data: { title?: string; permalink?: string; subreddit?: string; created_utc?: number };\n }) => ({\n title: post.data.title || \"Untitled\",\n url: `https://reddit.com${post.data.permalink}`,\n source: `Reddit r/${post.data.subreddit}`,\n pubDate: Math.floor(post.data.created_utc || Date.now() / 1000),\n }));\n } catch (_e) {\n return [];\n }\n}\n\n// ─── Core Collection Logic ───────────────────────────────────────────────────\n\nasync function collectArticles(\n brandId: number,\n): Promise<{ collected: number; sources: Record<string, number> }> {\n const brandRows = await sqlite.execute({\n sql: `SELECT name, keywords FROM brands WHERE id = ?`,\n args: [brandId],\n });\n if (!brandRows.rows.length) return { collected: 0, sources: {} };\n\n const brandName = brandRows.rows[0][0] as string;\n const keywordsRaw = brandRows.rows[0][1] as string;\n let keywords: string[] = [brandName];\n try {\n const parsed = JSON.parse(keywordsRaw || \"[]\");\n if (Array.isArray(parsed) && parsed.length > 0) keywords = parsed;\n } catch (_e) {\n keywords = [brandName];\n }\n\n // Fetch mainstream sources for each keyword\n const sourceCounters: Record<string, number> = {\n google_news: 0,\n newsapi: 0,\n hacker_news: 0,\n reddit: 0,\n };\n\n const allRaw: Array<\n {\n title: string;\n url: string;\n source: string;\n pubDate: number;\n sourceType: string;\n tier: number;\n }\n > = [];\n\n // For Google News + NewsAPI: use all keywords\n const googleFetches = keywords.map((kw) => fetchFromGoogleNews(kw));\n const newsFetches = keywords.map((kw) => fetchFromNewsApi(kw));\n const [googleResults, newsResults] = await Promise.all([\n Promise.all(googleFetches),\n Promise.all(newsFetches),\n ]);\n\n for (const articles of googleResults) {\n for (const a of articles) allRaw.push({ ...a, sourceType: \"mainstream_news\", tier: 3 });\n }\n for (const articles of newsResults) {\n for (const a of articles) allRaw.push({ ...a, sourceType: \"mainstream_news\", tier: 3 });\n }\n\n // HN + Reddit: brand name only, marked as community sources\n const [hnArticles, redditArticles] = await Promise.all([\n fetchHackerNews(brandName),\n fetchReddit(brandName),\n ]);\n for (const a of hnArticles) allRaw.push({ ...a, sourceType: \"tech_community\", tier: 3 });\n for (const a of redditArticles) allRaw.push({ ...a, sourceType: \"social\", tier: 3 });\n\n // Deduplicate by URL\n const seen = new Set<string>();\n const deduped: typeof allRaw = [];\n for (const article of allRaw) {\n if (article.url && !seen.has(article.url)) {\n seen.add(article.url);\n deduped.push(article);\n }\n }\n\n // Cap at 50 per collection run\n const toProcess = deduped.slice(0, 50);\n\n let collected = 0;\n const today = new Date().toISOString().split(\"T\")[0];\n let posCount = 0, neuCount = 0, negCount = 0;\n\n // Classify publication tiers + get sentiment for each article\n // Process in batches to avoid overwhelming the AI proxy\n for (const article of toProcess) {\n // For community sources, use the pre-assigned type (don't run through classifyPublicationTier)\n // For mainstream sources, classify publication tier\n let tier: number;\n let source_type: string;\n if (article.sourceType === \"tech_community\" || article.sourceType === \"social\") {\n tier = 3;\n source_type = article.sourceType;\n } else {\n const classified = await classifyPublicationTier(article.source);\n tier = classified.tier;\n source_type = classified.source_type;\n }\n\n // Get sentiment\n const { sentiment, score } = await getSentiment(article.title);\n\n // Track source counts\n if (article.sourceType === \"tech_community\") {\n sourceCounters.hacker_news++;\n } else if (article.sourceType === \"social\") {\n sourceCounters.reddit++;\n } else {\n // For mainstream, check if it came from Google or NewsAPI by source name\n const isNewsApi = !article.source.toLowerCase().includes(\"google\");\n if (isNewsApi) {\n sourceCounters.newsapi++;\n } else {\n sourceCounters.google_news++;\n }\n }\n\n try {\n const insertResult = await sqlite.execute({\n sql:\n `INSERT OR IGNORE INTO articles (brand_id, title, url, source_name, published_at, sentiment, sentiment_score, source_type, publication_tier) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n brandId,\n article.title,\n article.url,\n article.source,\n article.pubDate,\n sentiment,\n score,\n source_type,\n tier,\n ],\n });\n if (insertResult.rowsAffected > 0) {\n collected++;\n if (sentiment === \"positive\") posCount++;\n else if (sentiment === \"negative\") negCount++;\n else neuCount++;\n }\n } catch (_e) {\n // Skip on error (URL already exists)\n }\n }\n\n // Update daily metrics\n if (collected > 0) {\n await sqlite.execute({\n sql: `INSERT INTO daily_metrics (brand_id, date, article_count, positive_count, neutral_count, negative_count)\n VALUES (?, ?, ?, ?, ?, ?)\n ON CONFLICT(brand_id, date) DO UPDATE SET\n article_count = article_count + excluded.article_count,\n positive_count = positive_count + excluded.positive_count,\n neutral_count = neutral_count + excluded.neutral_count,\n negative_count = negative_count + excluded.negative_count`,\n args: [brandId, today, collected, posCount, neuCount, negCount],\n });\n }\n\n return { collected, sources: sourceCounters };\n}\n\n// ─── HTTP Handler ────────────────────────────────────────────────────────────\n\nexport default async function handler(req: Request): Promise<Response> {\n const url = new URL(req.url);\n const path = url.pathname;\n\n if (req.method === \"OPTIONS\") {\n return new Response(null, { headers: corsHeaders });\n }\n\n // GET /api/brands\n if (req.method === \"GET\" && path === \"/api/brands\") {\n const rows = await sqlite.execute(\n `SELECT b.id, b.name, b.created_at, COUNT(a.id) as article_count\n FROM brands b LEFT JOIN articles a ON b.id = a.brand_id\n GROUP BY b.id ORDER BY b.name`,\n );\n const brands = rows.rows.map((r) => ({\n id: r[0],\n name: r[1],\n created_at: r[2],\n article_count: r[3],\n }));\n return jsonResponse(brands);\n }\n\n // POST /api/brands\n if (req.method === \"POST\" && path === \"/api/brands\") {\n const body = await req.json();\n if (!body.name) return jsonResponse({ error: \"name required\" }, 400);\n try {\n const existing = await sqlite.execute({\n sql: `SELECT id, name FROM brands WHERE name = ?`,\n args: [body.name],\n });\n if (existing.rows.length > 0) {\n return jsonResponse({ error: \"Brand already exists\" }, 409);\n }\n await sqlite.execute({\n sql: `INSERT INTO brands (name) VALUES (?)`,\n args: [body.name],\n });\n const newBrand = await sqlite.execute({\n sql: `SELECT id FROM brands WHERE name = ?`,\n args: [body.name],\n });\n const brandId = newBrand.rows[0][0] as number;\n\n // Generate keywords via AI\n const keywords = await generateKeywords(body.name);\n await sqlite.execute({\n sql: `UPDATE brands SET keywords = ? WHERE id = ?`,\n args: [JSON.stringify(keywords), brandId],\n });\n\n return jsonResponse({ id: brandId, name: body.name, keywords }, 201);\n } catch (e) {\n const msg = e instanceof Error ? e.message : String(e);\n if (msg.includes(\"UNIQUE\") || msg.includes(\"unique\")) {\n return jsonResponse({ error: \"Brand already exists\" }, 409);\n }\n return jsonResponse({ error: msg }, 500);\n }\n }\n\n // DELETE /api/brands/:id\n const brandDeleteMatch = path.match(/^\\/api\\/brands\\/(\\d+)$/);\n if (req.method === \"DELETE\" && brandDeleteMatch) {\n const id = parseInt(brandDeleteMatch[1]);\n await sqlite.execute({ sql: `DELETE FROM articles WHERE brand_id = ?`, args: [id] });\n await sqlite.execute({ sql: `DELETE FROM daily_metrics WHERE brand_id = ?`, args: [id] });\n await sqlite.execute({ sql: `DELETE FROM brands WHERE id = ?`, args: [id] });\n return jsonResponse({ deleted: true });\n }\n\n // GET /api/brands/:id/keywords\n const keywordsMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/keywords$/);\n if (req.method === \"GET\" && keywordsMatch) {\n const id = parseInt(keywordsMatch[1]);\n const rows = await sqlite.execute({\n sql: `SELECT id, name, keywords FROM brands WHERE id = ?`,\n args: [id],\n });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brand = rows.rows[0];\n let keywords: string[] = [];\n try {\n keywords = JSON.parse((brand[2] as string) || \"[]\");\n } catch (_e) {\n keywords = [];\n }\n return jsonResponse({ id: brand[0], name: brand[1], keywords });\n }\n\n // POST /api/brands/:id/keywords/refresh\n const keywordsRefreshMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/keywords\\/refresh$/);\n if (req.method === \"POST\" && keywordsRefreshMatch) {\n const id = parseInt(keywordsRefreshMatch[1]);\n const rows = await sqlite.execute({\n sql: `SELECT id, name FROM brands WHERE id = ?`,\n args: [id],\n });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brandName = rows.rows[0][1] as string;\n const keywords = await generateKeywords(brandName);\n await sqlite.execute({\n sql: `UPDATE brands SET keywords = ? WHERE id = ?`,\n args: [JSON.stringify(keywords), id],\n });\n return jsonResponse({ id, name: brandName, keywords });\n }\n\n // GET /api/articles?brand_id=1&days=7&source_type=mainstream_news\n if (req.method === \"GET\" && path === \"/api/articles\") {\n const brandId = url.searchParams.get(\"brand_id\");\n const days = parseInt(url.searchParams.get(\"days\") || \"7\");\n const sourceTypeFilter = url.searchParams.get(\"source_type\") || null;\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n let sql =\n `SELECT id, title, url, source_name, published_at, sentiment, sentiment_score, source_type, publication_tier, collected_at\n FROM articles WHERE brand_id = ? AND collected_at >= ?`;\n const args: (string | number)[] = [parseInt(brandId), cutoff];\n if (sourceTypeFilter) {\n sql += ` AND source_type = ?`;\n args.push(sourceTypeFilter);\n }\n sql += ` ORDER BY collected_at DESC LIMIT 100`;\n const rows = await sqlite.execute({ sql, args });\n const articles = rows.rows.map((r) => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3],\n published_at: r[4], sentiment: r[5], sentiment_score: r[6],\n source_type: r[7], publication_tier: r[8], collected_at: r[9],\n }));\n return jsonResponse(articles);\n }\n\n // GET /api/report?brand_id=1&period=week&source_type=mainstream_news\n if (req.method === \"GET\" && path === \"/api/report\") {\n const brandId = url.searchParams.get(\"brand_id\");\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const period = url.searchParams.get(\"period\") || \"week\";\n // Default to mainstream_news only (like a PR professional expects)\n const sourceTypeFilter = url.searchParams.get(\"source_type\") || \"mainstream_news\";\n const days = period === \"month\" ? 30 : period === \"day\" ? 1 : 7;\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n\n const brandRows = await sqlite.execute({\n sql: `SELECT id, name FROM brands WHERE id = ?`,\n args: [parseInt(brandId)],\n });\n if (!brandRows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brand = { id: brandRows.rows[0][0], name: brandRows.rows[0][1] };\n\n // All articles (for source breakdown counts)\n const allArticleRows = await sqlite.execute({\n sql:\n `SELECT id, source_type, publication_tier FROM articles WHERE brand_id = ? AND collected_at >= ?`,\n args: [parseInt(brandId), cutoff],\n });\n\n const sources_breakdown = {\n google_news: 0,\n newsapi: 0,\n hacker_news: 0,\n reddit: 0,\n mainstream_news: 0,\n tech_community: 0,\n social: 0,\n };\n const tier_breakdown = { tier_1: 0, tier_2: 0, tier_3: 0 };\n\n for (const r of allArticleRows.rows) {\n const st = r[1] as string;\n const tier = r[2] as number;\n if (st === \"mainstream_news\") sources_breakdown.mainstream_news++;\n else if (st === \"tech_community\") sources_breakdown.tech_community++;\n else if (st === \"social\") sources_breakdown.social++;\n if (tier === 1) tier_breakdown.tier_1++;\n else if (tier === 2) tier_breakdown.tier_2++;\n else tier_breakdown.tier_3++;\n }\n\n // Filtered articles for the report body\n let sql =\n `SELECT id, title, url, source_name, sentiment, sentiment_score, source_type, publication_tier, collected_at\n FROM articles WHERE brand_id = ? AND collected_at >= ?`;\n const args: (string | number)[] = [parseInt(brandId), cutoff];\n if (sourceTypeFilter && sourceTypeFilter !== \"all\") {\n sql += ` AND source_type = ?`;\n args.push(sourceTypeFilter);\n }\n sql += ` ORDER BY publication_tier ASC, collected_at DESC`;\n const articleRows = await sqlite.execute({ sql, args });\n\n const articles = articleRows.rows.map((r) => ({\n id: r[0], title: r[1], url: r[2], source_name: r[3],\n sentiment: r[4], sentiment_score: r[5],\n source_type: r[6], publication_tier: r[7], collected_at: r[8],\n }));\n\n const sentimentBreakdown = { positive: 0, neutral: 0, negative: 0 };\n const sourceMap: Record<string, number> = {};\n\n for (const a of articles) {\n const s = a.sentiment as string;\n if (s in sentimentBreakdown) sentimentBreakdown[s as keyof typeof sentimentBreakdown]++;\n const src = (a.source_name as string) || \"Unknown\";\n sourceMap[src] = (sourceMap[src] || 0) + 1;\n }\n\n const topSources = Object.entries(sourceMap)\n .sort((a, b) => b[1] - a[1])\n .slice(0, 10)\n .map(([name, count]) => ({ name, count }));\n\n return jsonResponse({\n brand,\n period,\n source_filter: sourceTypeFilter,\n total_articles: articles.length,\n total_all_sources: allArticleRows.rows.length,\n sentiment_breakdown: sentimentBreakdown,\n top_sources: topSources,\n sources_breakdown,\n tier_breakdown,\n recent_articles: articles.slice(0, 20),\n });\n }\n\n // POST /api/collect\n if (req.method === \"POST\" && path === \"/api/collect\") {\n const body = await req.json();\n if (!body.brand_id) return jsonResponse({ error: \"brand_id required\" }, 400);\n const result = await collectArticles(body.brand_id);\n return jsonResponse(result);\n }\n\n return jsonResponse({ error: \"Not found\", path }, 404);\n}\n",[],[136,161],["D",1783289363906],[154],[136],["D",1783289313194],{"_95":94,"_102":98},{"_95":94,"_96":-5,"_97":98,"_18":55,"_99":100,"_101":25},{"_13":84,"_32":85,"_43":42,"_86":44,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":45,"_93":94},"ctx",{"_77":-5,"_123":209,"_210":211,"_212":213,"_214":25,"_215":25,"_216":25,"_217":121,"_218":-5,"_219":-7,"_220":-7,"_221":61,"_222":25,"_223":25,"_224":-5,"_225":25,"_226":25,"_227":25,"_228":-5},"ENV",{"_180":181,"_182":183,"_184":85,"_185":186,"_187":188,"_189":190,"_191":192,"_193":194,"_195":196,"_197":198,"_199":200,"_201":202,"_203":204,"_205":206,"_207":208},"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"] </article></body></html>

Diff · v17