<!\\[CDATA\\[([^\\]]+)\\]\\]><\\/title>/) || item.match(/<title>([^<]+)<\\/title>/);\n const linkMatch = item.match(/<link>([^<]+)<\\/link>/);\n const sourceMatch = item.match(/<source[^>]*><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/source>/) || item.match(/<source[^>]*>([^<]+)<\\/source>/);\n const pubDateMatch = item.match(/<pubDate>([^<]+)<\\/pubDate>/);\n const descMatch = item.match(/<description><!\\[CDATA\\[([^\\]]*)\\]\\]><\\/description>/) || item.match(/<description>([^<]*)<\\/description>/);\n if (titleMatch && linkMatch) {\n const pubDate = pubDateMatch ? Math.floor(new Date(pubDateMatch[1]).getTime() / 1000) : Math.floor(Date.now() / 1000);\n const snippet = descMatch ? descMatch[1].replace(/<[^>]+>/g, \"\").trim().slice(0, 200) : \"\";\n articles.push({ title: titleMatch[1].trim(), url: linkMatch[1].trim(), source: sourceMatch ? sourceMatch[1].trim() : \"Google News\", pubDate, snippet });\n }\n }\n return articles;\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchFromNewsApi(query: string): Promise<Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }>> {\n const newsApiKey = Deno.env.get(\"NEWS_API_KEY\") || \"960ad67b7cd6404995c74781be087fee\";\n try {\n const url = `https://newsapi.org/v2/everything?q=${encodeURIComponent(query)}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${newsApiKey}`;\n const resp = await fetch(url, { headers: { \"User-Agent\": \"PRMonitor/2.0\" } });\n if (!resp.ok) return [];\n const data = await resp.json();\n if (!data.articles) return [];\n return data.articles.map((a: { title?: string; url?: string; source?: { name?: string }; publishedAt?: string; description?: string }) => ({\n title: a.title || \"Untitled\",\n url: a.url || \"\",\n source: a.source?.name || \"NewsAPI\",\n pubDate: a.publishedAt ? Math.floor(new Date(a.publishedAt).getTime() / 1000) : Math.floor(Date.now() / 1000),\n snippet: (a.description || \"\").slice(0, 200),\n })).filter((a: { url: string }) => a.url);\n } catch (_e) {\n return [];\n }\n}\n\n// ─── Core Collection ──────────────────────────────────────────────────────\n\nasync function collectArticles(brandId: number): Promise<{ collected: number; rejected: number; total_fetched: number; sources: Record<string, number> }> {\n const brandRows = await sqlite.execute({ sql: `SELECT name, search_query, industry, parent_company, relevance_definition FROM brands WHERE id = ?`, args: [brandId] });\n if (!brandRows.rows.length) return { collected: 0, rejected: 0, total_fetched: 0, sources: {} };\n\n const brandName = brandRows.rows[0][0] as string;\n const searchQuery = (brandRows.rows[0][1] as string) || `\"${brandName}\"`;\n const industry = (brandRows.rows[0][2] as string) || \"\";\n const parentCompany = (brandRows.rows[0][3] as string) || \"\";\n const relevanceDefinition = (brandRows.rows[0][4] as string) || `Article must be primarily about ${brandName} as a company.`;\n\n const [googleArticles, newsApiArticles] = await Promise.all([\n fetchFromGoogleNews(searchQuery),\n fetchFromNewsApi(brandName),\n ]);\n\n const allRaw = [...googleArticles, ...newsApiArticles];\n\n const seenUrls = new Set<string>();\n const deduped = allRaw.filter((a) => {\n if (!a.url || seenUrls.has(a.url)) return false;\n seenUrls.add(a.url);\n return true;\n });\n\n const afterBlocklist = deduped.filter((a) => !isBlockedSource(a.source, a.url, a.title));\n\n let collected = 0;\n let rejected = 0;\n const sourceCounters: Record<string, number> = { google_news: 0, newsapi: 0 };\n const today = new Date().toISOString().split(\"T\")[0];\n let posCount = 0, neuCount = 0, negCount = 0;\n\n const existingHashes = await sqlite.execute({ sql: `SELECT story_hash FROM articles WHERE brand_id = ? AND collected_at >= ? AND story_hash != ''`, args: [brandId, Math.floor(Date.now() / 1000) - 86400 * 3] });\n const thisRunHashes = new Set<string>(existingHashes.rows.map((r) => r[0] as string));\n\n const toProcess = afterBlocklist.slice(0, 60);\n\n for (const article of toProcess) {\n const snippet = article.snippet || \"\";\n\n const { score: relevanceScore, reason: relevanceReason } = await scoreRelevance(article.title, snippet, article.source, brandName, industry, parentCompany, relevanceDefinition);\n\n if (relevanceScore < 65) {\n rejected++;\n continue;\n }\n\n const storyHash = computeStoryHash(article.title);\n if (thisRunHashes.has(storyHash)) {\n rejected++;\n continue;\n }\n thisRunHashes.add(storyHash);\n\n const { sentiment, score: sentimentScore, reason: sentimentReason } = await getSentiment(article.title, snippet, brandName);\n const { tier, source_type } = classifyTierSync(article.source);\n\n let sourceDomain = \"\";\n try { sourceDomain = new URL(article.url).hostname.replace(\"www.\", \"\"); } catch (_) {}\n\n const isFromGoogle = googleArticles.some((a) => a.url === article.url);\n if (isFromGoogle) sourceCounters.google_news++;\n else sourceCounters.newsapi++;\n\n try {\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO articles (brand_id, title, url, source_name, source_domain, published_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, story_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [brandId, article.title, article.url, article.source, sourceDomain, article.pubDate, relevanceScore, relevanceReason, sentiment, sentimentScore, sentimentReason, source_type, tier, storyHash],\n });\n if (result.rowsAffected > 0) {\n collected++;\n if (sentiment === \"positive\") posCount++;\n else if (sentiment === \"negative\") negCount++;\n else neuCount++;\n }\n } catch (_e) {}\n }\n\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) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(brand_id, date) DO UPDATE SET article_count = article_count + excluded.article_count, positive_count = positive_count + excluded.positive_count, neutral_count = neutral_count + excluded.neutral_count, negative_count = negative_count + excluded.negative_count`,\n args: [brandId, today, collected, posCount, neuCount, negCount],\n });\n }\n\n return { collected, rejected, total_fetched: deduped.length, 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\") return new Response(null, { headers: corsHeaders });\n\n // GET /api/brands\n if (req.method === \"GET\" && path === \"/api/brands\") {\n const rows = await sqlite.execute(`SELECT b.id, b.name, b.industry, b.parent_company, b.search_query, b.created_at, COUNT(a.id) as article_count, MAX(a.collected_at) as last_collected_at FROM brands b LEFT JOIN articles a ON b.id = a.brand_id AND a.relevance_score >= 65 GROUP BY b.id ORDER BY b.name`);\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], name: r[1], industry: r[2], parent_company: r[3], search_query: r[4], created_at: r[5], article_count: r[6], last_collected_at: r[7] })));\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({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n if (existing.rows.length > 0) return jsonResponse({ error: \"Brand already exists\" }, 409);\n await sqlite.execute({ sql: `INSERT INTO brands (name) VALUES (?)`, args: [body.name] });\n const newBrand = await sqlite.execute({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n const brandId = newBrand.rows[0][0] as number;\n const profile = await generateBrandProfile(body.name);\n await sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE id=?`,\n args: [profile.industry, profile.parent_company, JSON.stringify(profile.aliases), JSON.stringify(profile.key_products), JSON.stringify(profile.competitors), profile.relevance_definition, profile.search_query, brandId],\n });\n return jsonResponse({ id: brandId, name: body.name, ...profile }, 201);\n } catch (e) {\n const msg = e instanceof Error ? e.message : String(e);\n if (msg.includes(\"UNIQUE\") || msg.includes(\"unique\")) return jsonResponse({ error: \"Brand already exists\" }, 409);\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/profile\n const brandIdMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/profile$/);\n if (req.method === \"GET\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const rows = await sqlite.execute({ sql: `SELECT name, industry, parent_company, aliases, key_products, competitors, relevance_definition, search_query FROM brands WHERE id = ?`, args: [id] });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const r = rows.rows[0];\n return jsonResponse({ id, name: r[0], industry: r[1], parent_company: r[2], aliases: JSON.parse(r[3] as string || \"[]\"), key_products: JSON.parse(r[4] as string || \"[]\"), competitors: JSON.parse(r[5] as string || \"[]\"), relevance_definition: r[6], search_query: r[7] });\n }\n\n // PUT /api/brands/:id/profile\n if (req.method === \"PUT\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const body = await req.json();\n await sqlite.execute({\n sql: `UPDATE brands SET industry=COALESCE(?,industry), parent_company=COALESCE(?,parent_company), aliases=COALESCE(?,aliases), key_products=COALESCE(?,key_products), competitors=COALESCE(?,competitors), relevance_definition=COALESCE(?,relevance_definition), search_query=COALESCE(?,search_query), updated_at=unixepoch() WHERE id=?`,\n args: [body.industry||null, body.parent_company||null, body.aliases?JSON.stringify(body.aliases):null, body.key_products?JSON.stringify(body.key_products):null, body.competitors?JSON.stringify(body.competitors):null, body.relevance_definition||null, body.search_query||null, id],\n });\n return jsonResponse({ updated: true });\n }\n\n // DELETE /api/brands/:id/articles\n const brandArticlesMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/articles$/);\n if (req.method === \"DELETE\" && brandArticlesMatch) {\n const id = parseInt(brandArticlesMatch[1]);\n const result = 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 return jsonResponse({ deleted: result.rowsAffected });\n }\n\n // GET /api/articles\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 minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const rows = await sqlite.execute({\n sql: `SELECT id, title, url, source_name, source_domain, published_at, collected_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC LIMIT 100`,\n args: [parseInt(brandId), cutoff, minRelevance],\n });\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], source_domain: r[4], published_at: r[5], collected_at: r[6], relevance_score: r[7], relevance_reason: r[8], sentiment: r[9], sentiment_score: r[10], sentiment_reason: r[11], source_type: r[12], publication_tier: r[13] })));\n }\n\n // GET /api/report\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 const minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n const days = period === \"month\" ? 30 : period === \"day\" ? 1 : 7;\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const brandRows = await sqlite.execute({ sql: `SELECT id, name FROM brands WHERE id = ?`, args: [parseInt(brandId)] });\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 const articleRows = await sqlite.execute({ sql: `SELECT id, title, url, source_name, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, collected_at, relevance_score FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC`, args: [parseInt(brandId), cutoff, minRelevance] });\n const articles = articleRows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], sentiment: r[4], sentiment_score: r[5], sentiment_reason: r[6], source_type: r[7], publication_tier: r[8], collected_at: r[9], relevance_score: r[10] }));\n const sentimentBreakdown = { positive: 0, neutral: 0, negative: 0 };\n const tierBreakdown = { tier_1: 0, tier_2: 0, tier_3: 0 };\n const sourceMap: Record<string, number> = {};\n for (const a of articles) {\n const s = a.sentiment as string;\n if (s === \"positive\") sentimentBreakdown.positive++;\n else if (s === \"negative\") sentimentBreakdown.negative++;\n else sentimentBreakdown.neutral++;\n if (a.publication_tier === 1) tierBreakdown.tier_1++;\n else if (a.publication_tier === 2) tierBreakdown.tier_2++;\n else tierBreakdown.tier_3++;\n const src = (a.source_name as string) || \"Unknown\";\n sourceMap[src] = (sourceMap[src] || 0) + 1;\n }\n const topSources = Object.entries(sourceMap).sort((a, b) => b[1] - a[1]).slice(0, 10).map(([name, count]) => ({ name, count }));\n return jsonResponse({ brand, period, total_articles: articles.length, sentiment_breakdown: sentimentBreakdown, tier_breakdown: tierBreakdown, top_sources: topSources, recent_articles: articles.slice(0, 30) });\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","deleted",false,"label",[37],"e1c94eff-59a2-4307-9dee-e974c01c7677","index.tsx","6529976a-78c2-11f1-b86f-1607ee4eb77e",26,["D",1783339002574],14,"http","import { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// ─── Schema Setup ──────────────────────────────────────────────────────────\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS brands (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL UNIQUE,\n industry TEXT DEFAULT '',\n parent_company TEXT DEFAULT '',\n aliases TEXT DEFAULT '[]',\n key_products TEXT DEFAULT '[]',\n competitors TEXT DEFAULT '[]',\n relevance_definition TEXT DEFAULT '',\n search_query TEXT DEFAULT '',\n created_at INTEGER DEFAULT (unixepoch()),\n updated_at INTEGER DEFAULT (unixepoch())\n)`);\n\nconst brandCols = [\n ['industry', 'TEXT DEFAULT \"\"'],\n ['parent_company', 'TEXT DEFAULT \"\"'],\n ['aliases', 'TEXT DEFAULT \"[]\"'],\n ['key_products', 'TEXT DEFAULT \"[]\"'],\n ['competitors', 'TEXT DEFAULT \"[]\"'],\n ['relevance_definition', 'TEXT DEFAULT \"\"'],\n ['search_query', 'TEXT DEFAULT \"\"'],\n ['updated_at', 'INTEGER DEFAULT 0'],\n];\nfor (const [col, def] of brandCols) {\n try { await sqlite.execute(`ALTER TABLE brands ADD COLUMN ${col} ${def}`); } catch (_) {}\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 DEFAULT '',\n source_domain TEXT DEFAULT '',\n published_at INTEGER DEFAULT 0,\n collected_at INTEGER DEFAULT (unixepoch()),\n relevance_score INTEGER DEFAULT 0,\n relevance_reason TEXT DEFAULT '',\n sentiment TEXT DEFAULT 'neutral',\n sentiment_score REAL DEFAULT 0,\n sentiment_reason TEXT DEFAULT '',\n source_type TEXT DEFAULT 'mainstream_news',\n publication_tier INTEGER DEFAULT 3,\n story_hash TEXT DEFAULT ''\n)`);\n\nconst articleCols = [\n ['source_domain', 'TEXT DEFAULT \"\"'],\n ['relevance_score', 'INTEGER DEFAULT 0'],\n ['relevance_reason', 'TEXT DEFAULT \"\"'],\n ['sentiment_reason', 'TEXT DEFAULT \"\"'],\n ['story_hash', 'TEXT DEFAULT \"\"'],\n];\nfor (const [col, def] of articleCols) {\n try { await sqlite.execute(`ALTER TABLE articles ADD COLUMN ${col} ${def}`); } catch (_) {}\n}\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\n// Seed Behr profile\nawait sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE name='Behr'`,\n args: [\n 'Paint / Home Improvement',\n 'Masco Corporation (Home Depot subsidiary)',\n '[\"BEHR\",\"BEHR Paint\",\"Behr Process Corporation\",\"BEHR Premium\",\"Behr Paint Company\"]',\n '[\"BEHR Premium Plus\",\"Marquee Interior Paint\",\"BEHR PRO\",\"DeckOver\",\"ECOMIX Plant-Based Paint\"]',\n '[\"Sherwin-Williams\",\"Benjamin Moore\",\"Valspar\",\"PPG Paints\"]',\n 'Article must be primarily about Behr Paint Company as a business entity — product launches, retail partnerships, marketing campaigns, company news, lawsuits involving the company, or financial results. NOT: articles mentioning Behr as a German word, a surname, or a person unrelated to the paint company.',\n '\"Behr Paint\" OR \"BEHR Premium\" OR \"Behr Paint Company\"',\n ]\n});\n\nawait sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE name='Tesla'`,\n args: [\n 'Electric Vehicles / Automotive Technology',\n '',\n '[\"Tesla Motors\",\"TSLA\"]',\n '[\"Model S\",\"Model 3\",\"Model X\",\"Model Y\",\"Cybertruck\",\"Powerwall\",\"Full Self-Driving\"]',\n '[\"Ford\",\"GM\",\"Rivian\",\"Lucid\",\"BYD\",\"Volkswagen\"]',\n 'Article must be primarily about Tesla as a company — vehicle launches, recalls, Autopilot/FSD, financial results, Elon Musk in Tesla CEO role, manufacturing, or major partnerships. NOT: generic EV industry articles that mention Tesla in a list.',\n '\"Tesla\" electric vehicle OR \"Tesla Motors\" OR TSLA automotive',\n ]\n});\n\n// ─── CORS ─────────────────────────────────────────────────────────────────\n\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, PUT, 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// ─── Helpers ──────────────────────────────────────────────────────────────\n\nfunction isBlockedSource(sourceName: string, url: string, title: string): boolean {\n const combined = (sourceName + \" \" + url).toLowerCase();\n const titleLower = title.toLowerCase();\n const blockedPatterns = [\n 'legacy.com', 'dignitymemorial.com', 'findagrave.com', 'tributes.com',\n 'newcomerdayton', 'bolock', 'ratterman', 'hodapp', 'sunset options',\n 'funeral home', 'mortuary', 'cremation service', 'funeral services',\n 'chase parkway memorial', 'buffalospree.com',\n 'zillow.com', 'realtor.com', 'apartments.com', 'indeed.com', 'glassdoor.com',\n 'reddit.com', 'twitter.com', 'instagram.com', 'tiktok.com', 'pinterest.com',\n 'ycombinator.com', 'hacker news',\n ];\n for (const p of blockedPatterns) {\n if (combined.includes(p)) return true;\n }\n if (titleLower.includes('obituary') || titleLower.includes('obituaries')) return true;\n // Block celebrity-named-Behr patterns when source is entertainment\n const entertainmentSources = ['people.com', 'tmz', 'us weekly', 'entertainment tonight', 'celebrity'];\n if (entertainmentSources.some(e => combined.includes(e))) return true;\n return false;\n}\n\nfunction classifyTierSync(sourceName: string): { tier: number; source_type: string } {\n const lc = sourceName.toLowerCase();\n const tier1 = ['reuters', 'bloomberg', 'new york times', 'nytimes', 'wall street journal', 'wsj',\n 'washington post', 'bbc', 'cnn', 'nbc news', 'abc news', 'cbs news', 'fox news', 'npr',\n 'usa today', 'guardian', 'the atlantic', 'fortune', 'time', 'newsweek', 'cnbc', 'marketwatch',\n 'forbes', 'business insider', 'yahoo finance', 'yahoo news', 'yahoo', 'associated press',\n 'ap news', 'financial times', 'the verge', 'techcrunch', 'wired', 'axios', 'politico',\n 'the hill', 'new yorker', 'vogue', 'variety', 'rolling stone', 'engadget', 'cbsnews'];\n const tier2 = ['adweek', 'ad age', 'adage', 'prweek', 'pr week', 'chief marketer',\n 'coatings world', 'coatingsworld', 'paint square', 'paintsquare', 'hardware retailing',\n 'hardwareretailing', 'chemanalyst', 'home accents', 'architectural digest',\n 'good housekeeping', 'better homes', 'southern living', 'real simple', 'homes and gardens',\n 'realtor.com', 'the information', 'slashdot', 'ars technica', 'arstechnica',\n 'zdnet', 'cnet', 'pcmag', 'techradar', 'gizmodo', 'electrek', '9to5',\n 'notebookcheck', 'futurism', 'business journals', 'bizjournals'];\n if (tier1.some(p => lc.includes(p))) return { tier: 1, source_type: 'mainstream_news' };\n if (tier2.some(p => lc.includes(p))) return { tier: 2, source_type: 'mainstream_news' };\n return { tier: 3, source_type: 'mainstream_news' };\n}\n\nasync function classifyPublicationTier(sourceName: string): Promise<{ tier: number; source_type: string }> {\n const syncResult = classifyTierSync(sourceName);\n // Only use AI for tier 3 unknowns if we want (skip to save API calls)\n return syncResult;\n}\n\n// Heuristic relevance check when AI is unavailable\n// Returns a score 0-100 based on keyword signals in title\nfunction heuristicRelevance(title: string, snippet: string, brandName: string, aliases: string[]): { score: number; reason: string } {\n const titleLower = title.toLowerCase();\n const combined = (title + \" \" + snippet).toLowerCase();\n const brandLower = brandName.toLowerCase();\n const allNames = [brandLower, ...aliases.map(a => a.toLowerCase())];\n\n // Check if brand name appears in title at all\n const brandInTitle = allNames.some(n => titleLower.includes(n));\n const brandInContent = allNames.some(n => combined.includes(n));\n\n if (!brandInTitle && !brandInContent) return { score: 10, reason: \"Brand not mentioned\" };\n if (!brandInTitle) return { score: 40, reason: \"Brand only in snippet, not title\" };\n\n // Brand IS in title. Now check for false-positive patterns:\n // \"Brand\" as a generic word appearing in a listicle of many topics\n const listArticlePattern = titleLower.split(',').length >= 4; // \"Topic A, Topic B, Topic C, Topic D\"\n if (listArticlePattern && !allNames.some(n => titleLower.startsWith(n))) {\n return { score: 20, reason: \"Appears to be a roundup not primarily about brand\" };\n }\n\n // Strong positive signals: brand name is the subject\n const startsWithBrand = allNames.some(n => titleLower.startsWith(n) || titleLower.startsWith(\"the \" + n));\n const brandPossessive = allNames.some(n => titleLower.includes(n + \"'s\") || titleLower.includes(n + \" paint\") || titleLower.includes(n + \" names\") || titleLower.includes(n + \" unveils\") || titleLower.includes(n + \" launches\") || titleLower.includes(n + \" announces\") || titleLower.includes(n + \" is \") || titleLower.includes(n + \" just \") || titleLower.includes(n + \" and \") || titleLower.includes(n + \" vs \"));\n\n if (startsWithBrand || brandPossessive) return { score: 82, reason: \"Brand is primary subject of title\" };\n\n // Medium: brand appears in title but not as subject\n return { score: 68, reason: \"Brand mentioned in title\" };\n}\n\nasync function scoreRelevance(\n title: string, snippet: string, sourceName: string,\n brandName: string, industry: string, parentCompany: string, relevanceDefinition: string,\n aliases: string[] = []\n): Promise<{ score: number; reason: string }> {\n const apiKey = Deno.env.get(\"valtown\");\n if (!apiKey) return heuristicRelevance(title, snippet, brandName, aliases);\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `You are evaluating whether a news article belongs in a PR monitoring feed.\n\nBrand: ${brandName}\nIndustry: ${industry}\nParent company: ${parentCompany || \"independent\"}\nWhat counts as relevant: ${relevanceDefinition}\n\nScore 0-100:\n- 80-100: Article is PRIMARILY about ${brandName} as a company (product launch, campaign, partnership, news, controversy, analyst coverage)\n- 60-79: ${brandName} is a significant subject (not just mentioned)\n- 40-59: ${brandName} is mentioned but not the main focus\n- 0-39: \"${brandName}\" refers to something else (a person's surname, a foreign word, an unrelated entity), OR it is an obituary, celebrity gossip, job listing, or real estate listing\n\nArticle title: ${title}\nSnippet: ${snippet || \"(no snippet)\"}\nSource: ${sourceName}\n\nRespond with JSON only: {\"score\": N, \"reason\": \"one sentence\"}` }],\n max_tokens: 100,\n }),\n });\n if (!resp.ok) return heuristicRelevance(title, snippet, brandName, aliases);\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n const score = parseInt(parsed.score);\n if (isNaN(score)) return heuristicRelevance(title, snippet, brandName, aliases);\n return { score, reason: parsed.reason || \"\" };\n } catch (_e) {\n return heuristicRelevance(title, snippet, brandName, aliases);\n }\n}\n\nasync function getSentiment(title: string, snippet: string, brandName: string): Promise<{ sentiment: string; score: number; reason: string }> {\n const apiKey = Deno.env.get(\"valtown\");\n if (!apiKey) return { sentiment: \"neutral\", score: 0, reason: \"\" };\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `Is this article positive, neutral, or negative for ${brandName}'s public image?\\nTitle: ${title}\\nSnippet: ${snippet || \"\"}\\nRespond JSON: {\"sentiment\": \"positive\"|\"neutral\"|\"negative\", \"score\": -1.0 to 1.0, \"reason\": \"one sentence\"}` }],\n max_tokens: 100,\n }),\n });\n if (!resp.ok) return { sentiment: \"neutral\", score: 0, reason: \"\" };\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n const sentiment = [\"positive\", \"neutral\", \"negative\"].includes(parsed.sentiment) ? parsed.sentiment : \"neutral\";\n return { sentiment, score: parsed.score || 0, reason: parsed.reason || \"\" };\n } catch (_e) {\n return { sentiment: \"neutral\", score: 0, reason: \"\" };\n }\n}\n\nasync function generateBrandProfile(brandName: string): Promise<{\n industry: string; parent_company: string; aliases: string[];\n key_products: string[]; competitors: string[]; relevance_definition: string; search_query: string;\n}> {\n const def = { industry: \"\", parent_company: \"\", aliases: [brandName], key_products: [] as string[], competitors: [] as string[], relevance_definition: `Article must be primarily about ${brandName} as a company.`, search_query: `\"${brandName}\"` };\n // Use Anthropic Claude API for reliable brand profile generation\n const anthropicKey = \"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\";\n try {\n const resp = await fetch(\"https://api.anthropic.com/v1/messages\", {\n method: \"POST\",\n headers: {\n \"x-api-key\": anthropicKey,\n \"anthropic-version\": \"2023-06-01\",\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"claude-haiku-4-5\",\n max_tokens: 600,\n messages: [{\n role: \"user\",\n content: `Generate a brand profile for PR monitoring for the company: \"${brandName}\"\n\nReturn a JSON object with exactly these fields:\n{\n \"industry\": \"Primary industry (e.g. Paint / Home Improvement)\",\n \"parent_company\": \"Parent company name, or empty string if independent\",\n \"aliases\": [\"array of brand name variants and alternate spellings\"],\n \"key_products\": [\"top 3-5 flagship products or product lines\"],\n \"competitors\": [\"top 3-5 direct competitors\"],\n \"relevance_definition\": \"2-3 sentences defining what articles ARE relevant (e.g. product launches, partnerships, financial news) and explicitly what to EXCLUDE (e.g. articles where the brand name refers to a person's surname, foreign word, or unrelated entity)\",\n \"search_query\": \"Google News search query using quoted phrases and OR operators for precision, e.g. \\\\\"Brand Name Product\\\\\" OR \\\\\"Brand Inc\\\\\" industry\"\n}\n\nRespond with JSON only, no markdown code blocks.`\n }],\n }),\n });\n if (!resp.ok) {\n // Fall back to OpenAI proxy if Anthropic fails\n const valApiKey = Deno.env.get(\"valtown\");\n if (valApiKey) {\n const oaiResp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${valApiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `Generate a brand profile for PR monitoring for: \"${brandName}\"\\n\\nReturn JSON:\\n{\"industry\":\"Primary industry\",\"parent_company\":\"Parent company or empty string\",\"aliases\":[\"name variants\"],\"key_products\":[\"top 3-5 products\"],\"competitors\":[\"top 3-5 competitors\"],\"relevance_definition\":\"2-3 sentences defining relevant articles and what to exclude\",\"search_query\":\"search query with industry qualifiers, use quoted phrases for precision\"}` }],\n max_tokens: 400,\n }),\n });\n if (oaiResp.ok) {\n const oaiData = await oaiResp.json();\n const oaiContent = (oaiData.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n return { ...def, ...JSON.parse(oaiContent) };\n }\n }\n return def;\n }\n const data = await resp.json();\n const content = (data.content?.[0]?.text || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n return { ...def, ...parsed };\n } catch (_e) {\n return def;\n }\n}\n\nfunction computeStoryHash(title: string): string {\n return title.toLowerCase().replace(/[^a-z0-9 ]/g, \"\").replace(/\\b(the|a|an|is|are|has|have|of|in|on|for|to|and|or|but|with|its|was)\\b/g, \"\").replace(/\\s+/g, \" \").trim().slice(0, 80);\n}\n\n// ─── Data Sources ──────────────────────────────────────────────────────────\n\nasync function fetchFromGoogleNews(query: string): Promise<Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }>> {\n const rssUrl = `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, { headers: { \"User-Agent\": \"Mozilla/5.0 (compatible; PRMonitor/2.0)\" } });\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; snippet: string }> = [];\n let itemMatch;\n while ((itemMatch = itemRegex.exec(xml)) !== null) {\n const item = itemMatch[1];\n const titleMatch = item.match(/<title><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/title>/) || item.match(/<title>([^<]+)<\\/title>/);\n const linkMatch = item.match(/<link>([^<]+)<\\/link>/);\n const sourceMatch = item.match(/<source[^>]*><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/source>/) || item.match(/<source[^>]*>([^<]+)<\\/source>/);\n const pubDateMatch = item.match(/<pubDate>([^<]+)<\\/pubDate>/);\n const descMatch = item.match(/<description><!\\[CDATA\\[([^\\]]*)\\]\\]><\\/description>/) || item.match(/<description>([^<]*)<\\/description>/);\n if (titleMatch && linkMatch) {\n const pubDate = pubDateMatch ? Math.floor(new Date(pubDateMatch[1]).getTime() / 1000) : Math.floor(Date.now() / 1000);\n const snippet = descMatch ? descMatch[1].replace(/<[^>]+>/g, \"\").trim().slice(0, 200) : \"\";\n articles.push({ title: titleMatch[1].trim(), url: linkMatch[1].trim(), source: sourceMatch ? sourceMatch[1].trim() : \"Google News\", pubDate, snippet });\n }\n }\n return articles;\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchFromNewsApi(query: string): Promise<Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }>> {\n const newsApiKey = Deno.env.get(\"NEWS_API_KEY\") || \"960ad67b7cd6404995c74781be087fee\";\n try {\n const url = `https://newsapi.org/v2/everything?q=${encodeURIComponent(query)}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${newsApiKey}`;\n const resp = await fetch(url, { headers: { \"User-Agent\": \"PRMonitor/2.0\" } });\n if (!resp.ok) return [];\n const data = await resp.json();\n if (!data.articles) return [];\n return data.articles.map((a: { title?: string; url?: string; source?: { name?: string }; publishedAt?: string; description?: string }) => ({\n title: a.title || \"Untitled\",\n url: a.url || \"\",\n source: a.source?.name || \"NewsAPI\",\n pubDate: a.publishedAt ? Math.floor(new Date(a.publishedAt).getTime() / 1000) : Math.floor(Date.now() / 1000),\n snippet: (a.description || \"\").slice(0, 200),\n })).filter((a: { url: string }) => a.url);\n } catch (_e) {\n return [];\n }\n}\n\n// ─── Core Collection ──────────────────────────────────────────────────────\n\nasync function collectArticles(brandId: number): Promise<{ collected: number; rejected: number; total_fetched: number; sources: Record<string, number> }> {\n const brandRows = await sqlite.execute({ sql: `SELECT name, search_query, industry, parent_company, relevance_definition, aliases FROM brands WHERE id = ?`, args: [brandId] });\n if (!brandRows.rows.length) return { collected: 0, rejected: 0, total_fetched: 0, sources: {} };\n\n const brandName = brandRows.rows[0][0] as string;\n const searchQuery = (brandRows.rows[0][1] as string) || `\"${brandName}\"`;\n const industry = (brandRows.rows[0][2] as string) || \"\";\n const parentCompany = (brandRows.rows[0][3] as string) || \"\";\n const relevanceDefinition = (brandRows.rows[0][4] as string) || `Article must be primarily about ${brandName} as a company.`;\n let aliases: string[] = [];\n try { aliases = JSON.parse((brandRows.rows[0][5] as string) || \"[]\"); } catch (_) {}\n\n const [googleArticles, newsApiArticles] = await Promise.all([\n fetchFromGoogleNews(searchQuery),\n fetchFromNewsApi(searchQuery),\n ]);\n\n const allRaw = [...googleArticles, ...newsApiArticles];\n\n const seenUrls = new Set<string>();\n const deduped = allRaw.filter((a) => {\n if (!a.url || seenUrls.has(a.url)) return false;\n seenUrls.add(a.url);\n return true;\n });\n\n const afterBlocklist = deduped.filter((a) => !isBlockedSource(a.source, a.url, a.title));\n\n let collected = 0;\n let rejected = 0;\n const sourceCounters: Record<string, number> = { google_news: 0, newsapi: 0 };\n const today = new Date().toISOString().split(\"T\")[0];\n let posCount = 0, neuCount = 0, negCount = 0;\n\n const existingHashes = await sqlite.execute({ sql: `SELECT story_hash FROM articles WHERE brand_id = ? AND collected_at >= ? AND story_hash != ''`, args: [brandId, Math.floor(Date.now() / 1000) - 86400 * 3] });\n const thisRunHashes = new Set<string>(existingHashes.rows.map((r) => r[0] as string));\n\n const toProcess = afterBlocklist.slice(0, 60);\n\n for (const article of toProcess) {\n const snippet = article.snippet || \"\";\n\n const { score: relevanceScore, reason: relevanceReason } = await scoreRelevance(article.title, snippet, article.source, brandName, industry, parentCompany, relevanceDefinition, aliases);\n\n if (relevanceScore < 65) {\n rejected++;\n continue;\n }\n\n const storyHash = computeStoryHash(article.title);\n if (thisRunHashes.has(storyHash)) {\n rejected++;\n continue;\n }\n thisRunHashes.add(storyHash);\n\n const { sentiment, score: sentimentScore, reason: sentimentReason } = await getSentiment(article.title, snippet, brandName);\n const { tier, source_type } = classifyTierSync(article.source);\n\n let sourceDomain = \"\";\n try { sourceDomain = new URL(article.url).hostname.replace(\"www.\", \"\"); } catch (_) {}\n\n const isFromGoogle = googleArticles.some((a) => a.url === article.url);\n if (isFromGoogle) sourceCounters.google_news++;\n else sourceCounters.newsapi++;\n\n try {\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO articles (brand_id, title, url, source_name, source_domain, published_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, story_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [brandId, article.title, article.url, article.source, sourceDomain, article.pubDate, relevanceScore, relevanceReason, sentiment, sentimentScore, sentimentReason, source_type, tier, storyHash],\n });\n if (result.rowsAffected > 0) {\n collected++;\n if (sentiment === \"positive\") posCount++;\n else if (sentiment === \"negative\") negCount++;\n else neuCount++;\n }\n } catch (_e) {}\n }\n\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) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(brand_id, date) DO UPDATE SET article_count = article_count + excluded.article_count, positive_count = positive_count + excluded.positive_count, neutral_count = neutral_count + excluded.neutral_count, negative_count = negative_count + excluded.negative_count`,\n args: [brandId, today, collected, posCount, neuCount, negCount],\n });\n }\n\n return { collected, rejected, total_fetched: deduped.length, 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\") return new Response(null, { headers: corsHeaders });\n\n // GET /api/brands\n if (req.method === \"GET\" && path === \"/api/brands\") {\n const rows = await sqlite.execute(`SELECT b.id, b.name, b.industry, b.parent_company, b.search_query, b.created_at, COUNT(a.id) as article_count, MAX(a.collected_at) as last_collected_at FROM brands b LEFT JOIN articles a ON b.id = a.brand_id AND a.relevance_score >= 65 GROUP BY b.id ORDER BY b.name`);\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], name: r[1], industry: r[2], parent_company: r[3], search_query: r[4], created_at: r[5], article_count: r[6], last_collected_at: r[7] })));\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({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n if (existing.rows.length > 0) return jsonResponse({ error: \"Brand already exists\" }, 409);\n await sqlite.execute({ sql: `INSERT INTO brands (name) VALUES (?)`, args: [body.name] });\n const newBrand = await sqlite.execute({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n const brandId = newBrand.rows[0][0] as number;\n const profile = await generateBrandProfile(body.name);\n await sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE id=?`,\n args: [profile.industry, profile.parent_company, JSON.stringify(profile.aliases), JSON.stringify(profile.key_products), JSON.stringify(profile.competitors), profile.relevance_definition, profile.search_query, brandId],\n });\n return jsonResponse({ id: brandId, name: body.name, ...profile }, 201);\n } catch (e) {\n const msg = e instanceof Error ? e.message : String(e);\n if (msg.includes(\"UNIQUE\") || msg.includes(\"unique\")) return jsonResponse({ error: \"Brand already exists\" }, 409);\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/profile\n const brandIdMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/profile$/);\n if (req.method === \"GET\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const rows = await sqlite.execute({ sql: `SELECT name, industry, parent_company, aliases, key_products, competitors, relevance_definition, search_query FROM brands WHERE id = ?`, args: [id] });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const r = rows.rows[0];\n return jsonResponse({ id, name: r[0], industry: r[1], parent_company: r[2], aliases: JSON.parse(r[3] as string || \"[]\"), key_products: JSON.parse(r[4] as string || \"[]\"), competitors: JSON.parse(r[5] as string || \"[]\"), relevance_definition: r[6], search_query: r[7] });\n }\n\n // PUT /api/brands/:id/profile\n if (req.method === \"PUT\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const body = await req.json();\n await sqlite.execute({\n sql: `UPDATE brands SET industry=COALESCE(?,industry), parent_company=COALESCE(?,parent_company), aliases=COALESCE(?,aliases), key_products=COALESCE(?,key_products), competitors=COALESCE(?,competitors), relevance_definition=COALESCE(?,relevance_definition), search_query=COALESCE(?,search_query), updated_at=unixepoch() WHERE id=?`,\n args: [body.industry||null, body.parent_company||null, body.aliases?JSON.stringify(body.aliases):null, body.key_products?JSON.stringify(body.key_products):null, body.competitors?JSON.stringify(body.competitors):null, body.relevance_definition||null, body.search_query||null, id],\n });\n return jsonResponse({ updated: true });\n }\n\n // DELETE /api/brands/:id/articles\n const brandArticlesMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/articles$/);\n if (req.method === \"DELETE\" && brandArticlesMatch) {\n const id = parseInt(brandArticlesMatch[1]);\n const result = 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 return jsonResponse({ deleted: result.rowsAffected });\n }\n\n // GET /api/articles\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 minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const rows = await sqlite.execute({\n sql: `SELECT id, title, url, source_name, source_domain, published_at, collected_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC LIMIT 100`,\n args: [parseInt(brandId), cutoff, minRelevance],\n });\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], source_domain: r[4], published_at: r[5], collected_at: r[6], relevance_score: r[7], relevance_reason: r[8], sentiment: r[9], sentiment_score: r[10], sentiment_reason: r[11], source_type: r[12], publication_tier: r[13] })));\n }\n\n // GET /api/report\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 const minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n const days = period === \"month\" ? 30 : period === \"day\" ? 1 : 7;\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const brandRows = await sqlite.execute({ sql: `SELECT id, name FROM brands WHERE id = ?`, args: [parseInt(brandId)] });\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 const articleRows = await sqlite.execute({ sql: `SELECT id, title, url, source_name, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, collected_at, relevance_score FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC`, args: [parseInt(brandId), cutoff, minRelevance] });\n const articles = articleRows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], sentiment: r[4], sentiment_score: r[5], sentiment_reason: r[6], source_type: r[7], publication_tier: r[8], collected_at: r[9], relevance_score: r[10] }));\n const sentimentBreakdown = { positive: 0, neutral: 0, negative: 0 };\n const tierBreakdown = { tier_1: 0, tier_2: 0, tier_3: 0 };\n const sourceMap: Record<string, number> = {};\n for (const a of articles) {\n const s = a.sentiment as string;\n if (s === \"positive\") sentimentBreakdown.positive++;\n else if (s === \"negative\") sentimentBreakdown.negative++;\n else sentimentBreakdown.neutral++;\n if (a.publication_tier === 1) tierBreakdown.tier_1++;\n else if (a.publication_tier === 2) tierBreakdown.tier_2++;\n else tierBreakdown.tier_3++;\n const src = (a.source_name as string) || \"Unknown\";\n sourceMap[src] = (sourceMap[src] || 0) + 1;\n }\n const topSources = Object.entries(sourceMap).sort((a, b) => b[1] - a[1]).slice(0, 10).map(([name, count]) => ({ name, count }));\n return jsonResponse({ brand, period, total_articles: articles.length, sentiment_breakdown: sentimentBreakdown, tier_breakdown: tierBreakdown, top_sources: topSources, recent_articles: articles.slice(0, 30) });\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 // POST /api/debug-collect\n if (req.method === \"POST\" && path === \"/api/debug-collect\") {\n const body = await req.json();\n if (!body.brand_id) return jsonResponse({ error: \"brand_id required\" }, 400);\n const brandId = body.brand_id;\n const brandRows = await sqlite.execute({ sql: `SELECT name, search_query, industry, parent_company, relevance_definition FROM brands WHERE id = ?`, args: [brandId] });\n if (!brandRows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brandName = brandRows.rows[0][0] as string;\n const searchQuery = (brandRows.rows[0][1] as string) || `\"${brandName}\"`;\n const industry = (brandRows.rows[0][2] as string) || \"\";\n const parentCompany = (brandRows.rows[0][3] as string) || \"\";\n const relevanceDefinition = (brandRows.rows[0][4] as string) || \"\";\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_TOKEN\");\n const [googleArticles, newsApiArticles] = await Promise.all([\n fetchFromGoogleNews(searchQuery),\n fetchFromNewsApi(searchQuery),\n ]);\n const sampleGoogle = googleArticles.slice(0, 3).map(a => ({ title: a.title, url: a.url.slice(0, 60), source: a.source }));\n const sampleNews = newsApiArticles.slice(0, 3).map(a => ({ title: a.title, url: a.url.slice(0, 60), source: a.source }));\n const testArticle = googleArticles[0];\n let testScore = { score: 0, reason: \"no articles\" };\n if (testArticle) {\n testScore = await scoreRelevance(testArticle.title, testArticle.snippet, testArticle.source, brandName, industry, parentCompany, relevanceDefinition);\n }\n const existingHashes = await sqlite.execute({ sql: `SELECT COUNT(*) FROM articles WHERE brand_id = ? AND collected_at >= ? AND story_hash != ''`, args: [brandId, Math.floor(Date.now() / 1000) - 86400 * 3] });\n return jsonResponse({\n brand: brandName,\n search_query: searchQuery,\n api_key_available: !!apiKey,\n google_count: googleArticles.length,\n newsapi_count: newsApiArticles.length,\n sample_google: sampleGoogle,\n sample_newsapi: sampleNews,\n test_score: testScore,\n existing_hashes: existingHashes.rows[0][0],\n });\n }\n\n return jsonResponse({ error: \"Not found\", path }, 404);\n}\n",[58],"4a6c5e85-b952-46dd-aaa1-9a41fcfb2f6e","backend","e767cc15-b91e-430a-9ff5-bbe470e7e661",1,["D",1783289363906],"directory",[67],{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":83,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_77":84,"_18":85},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":80,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_77":81,"_18":82},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":76,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_77":78,"_18":79},["D",1783298708366],"children",[],[37],["D",1783339002574],[],[58],["D",1783289363906],[86],[67],{"_35":87,"_13":88,"_38":89,"_40":68,"_41":42,"_43":90,"_45":46,"_47":91,"_17":92,"_50":63,"_51":93,"_53":54,"_55":-5,"_77":94,"_18":95},"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",[],[67,88],"02b93d4c-8e45-4618-935a-44aaa786ed62",["D",1783289313194],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","PR monitoring API backend","imageUrl","userId","019cdef0-5cd0-7261-bddf-67d3a4efcd7a","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","orgId","019cdef0-5cd0-7261-bddf-6971337b2068","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_11":12,"_149":145},"org",{"_11":12,"_143":-5,"_144":145,"_106":107,"_146":147,"_148":54},"branch",{"_17":60,"_13":16,"_35":42,"_135":96,"_136":-5,"_137":-5,"_138":-5,"_139":-5,"_140":-5,"_141":14,"_142":12},"isOwned","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBb2xGb1hUcU8ydWYzR0FNVzhFRDBaQk5SWCJ9","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_35":96,"_13":14,"_47":196,"_98":-5,"_99":54,"_100":101,"_102":101,"_103":104,"_105":-5,"_106":107,"_108":-5,"_109":54,"_110":-5,"_111":-5,"_112":49,"_113":114,"_115":-5,"_116":-5,"_117":-5,"_118":-5,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_128":197,"_130":198,"_132":199,"_134":54},[184,186,187],"files",[184,185,186,187],"counts",{"_174":49,"_175":69,"_176":49,"_31":177,"_178":179},[168,169,170],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":70,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_18":173},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":61,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_18":172},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":48,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_18":171},[37],[58],[67],"pulls","branches","forks",{"_180":182,"_183":49},"connections",{"_180":49,"_181":49},"count","unfilledCount",3,"emptyCount",{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":83,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_77":194,"_18":195},{"_35":87,"_13":88,"_38":89,"_40":68,"_41":42,"_43":90,"_45":46,"_47":91,"_17":92,"_50":63,"_51":93,"_53":54,"_55":-5,"_77":192,"_18":193},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":80,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_77":190,"_18":191},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":76,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_77":188,"_18":189},[],[37],[],[58],[],[67,88],[185],[67],["D",1783289313194],{"_11":12,"_149":145},{"_11":12,"_143":-5,"_144":145,"_106":107,"_146":147,"_148":54},{"_17":60,"_13":16,"_35":42,"_135":96,"_136":-5,"_137":-5,"_138":-5,"_139":-5,"_140":-5,"_141":14,"_142":12},"ctx",{"_128":-5,"_165":233,"_234":235,"_236":237,"_238":54,"_239":54,"_240":54,"_241":46,"_242":-5,"_243":-7,"_244":-7,"_245":49,"_246":54,"_247":54,"_248":-5,"_249":54,"_250":54,"_251":54,"_252":-5},"ENV",{"_204":205,"_206":207,"_208":16,"_209":210,"_211":212,"_213":214,"_215":216,"_217":218,"_219":220,"_221":222,"_223":224,"_225":226,"_227":228,"_229":230,"_231":232},"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",{"_200":201,"_202":203},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":-7,"_18":19,"_17":-7,"_150":-5,"_20":151,"_22":152,"_153":154,"_155":156,"_25":157,"_24":-5,"_158":-5,"_159":-5,"_160":54,"_161":162,"_163":46,"_164":-5,"_165":-5,"_166":167},"routes/_app.x.$handle.$name.code.$",{"_11":12,"_13":14,"_15":16,"_17":-7,"_18":19,"_20":21,"_22":23,"_24":-7,"_25":26,"_27":-5,"_28":-5,"_29":-5,"_30":-5,"_31":-5},"handle","lobsterman","name","prMonitorApi","branchName","main","version","path","","project",{"_35":96,"_13":14,"_47":97,"_98":-5,"_99":54,"_100":101,"_102":101,"_103":104,"_105":-5,"_106":107,"_108":-5,"_109":54,"_110":-5,"_111":-5,"_112":49,"_113":114,"_115":-5,"_116":-5,"_117":-5,"_118":-5,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_128":129,"_130":131,"_132":133,"_134":54},"tree",[73,74,75],"file","contents",[32,33,34],"upstream","pull","ahead","behind","envVars",{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":70,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_18":72},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":61,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_18":65},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":48,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_18":56},"id","7510911f-6f29-47ab-95fb-6914b32a0eda","main.ts","fileId","efec12a4-78d3-11f1-8b41-1607ee4eb77e","parentId","branchId","41a11757-2dbb-47d5-a624-ca5832847750","branchVersion",20,"latest",true,"createdAt",["D",1783298708366],0,"fileType","content","import { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// ─── Schema Setup ──────────────────────────────────────────────────────────\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS brands (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL UNIQUE,\n industry TEXT DEFAULT '',\n parent_company TEXT DEFAULT '',\n aliases TEXT DEFAULT '[]',\n key_products TEXT DEFAULT '[]',\n competitors TEXT DEFAULT '[]',\n relevance_definition TEXT DEFAULT '',\n search_query TEXT DEFAULT '',\n created_at INTEGER DEFAULT (unixepoch()),\n updated_at INTEGER DEFAULT (unixepoch())\n)`);\n\nconst brandCols = [\n ['industry', 'TEXT DEFAULT \"\"'],\n ['parent_company', 'TEXT DEFAULT \"\"'],\n ['aliases', 'TEXT DEFAULT \"[]\"'],\n ['key_products', 'TEXT DEFAULT \"[]\"'],\n ['competitors', 'TEXT DEFAULT \"[]\"'],\n ['relevance_definition', 'TEXT DEFAULT \"\"'],\n ['search_query', 'TEXT DEFAULT \"\"'],\n ['updated_at', 'INTEGER DEFAULT 0'],\n];\nfor (const [col, def] of brandCols) {\n try { await sqlite.execute(`ALTER TABLE brands ADD COLUMN ${col} ${def}`); } catch (_) {}\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 DEFAULT '',\n source_domain TEXT DEFAULT '',\n published_at INTEGER DEFAULT 0,\n collected_at INTEGER DEFAULT (unixepoch()),\n relevance_score INTEGER DEFAULT 0,\n relevance_reason TEXT DEFAULT '',\n sentiment TEXT DEFAULT 'neutral',\n sentiment_score REAL DEFAULT 0,\n sentiment_reason TEXT DEFAULT '',\n source_type TEXT DEFAULT 'mainstream_news',\n publication_tier INTEGER DEFAULT 3,\n story_hash TEXT DEFAULT ''\n)`);\n\nconst articleCols = [\n ['source_domain', 'TEXT DEFAULT \"\"'],\n ['relevance_score', 'INTEGER DEFAULT 0'],\n ['relevance_reason', 'TEXT DEFAULT \"\"'],\n ['sentiment_reason', 'TEXT DEFAULT \"\"'],\n ['story_hash', 'TEXT DEFAULT \"\"'],\n];\nfor (const [col, def] of articleCols) {\n try { await sqlite.execute(`ALTER TABLE articles ADD COLUMN ${col} ${def}`); } catch (_) {}\n}\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\n// Seed Behr profile\nawait sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE name='Behr'`,\n args: [\n 'Paint / Home Improvement',\n 'Masco Corporation (Home Depot subsidiary)',\n '[\"BEHR\",\"BEHR Paint\",\"Behr Process Corporation\",\"BEHR Premium\",\"Behr Paint Company\"]',\n '[\"BEHR Premium Plus\",\"Marquee Interior Paint\",\"BEHR PRO\",\"DeckOver\",\"ECOMIX Plant-Based Paint\"]',\n '[\"Sherwin-Williams\",\"Benjamin Moore\",\"Valspar\",\"PPG Paints\"]',\n 'Article must be primarily about Behr Paint Company as a business entity — product launches, retail partnerships, marketing campaigns, company news, lawsuits involving the company, or financial results. NOT: articles mentioning Behr as a German word, a surname, or a person unrelated to the paint company.',\n '\"Behr Paint\" OR \"BEHR Premium\" OR \"Behr Paint Company\"',\n ]\n});\n\nawait sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE name='Tesla'`,\n args: [\n 'Electric Vehicles / Automotive Technology',\n '',\n '[\"Tesla Motors\",\"TSLA\"]',\n '[\"Model S\",\"Model 3\",\"Model X\",\"Model Y\",\"Cybertruck\",\"Powerwall\",\"Full Self-Driving\"]',\n '[\"Ford\",\"GM\",\"Rivian\",\"Lucid\",\"BYD\",\"Volkswagen\"]',\n 'Article must be primarily about Tesla as a company — vehicle launches, recalls, Autopilot/FSD, financial results, Elon Musk in Tesla CEO role, manufacturing, or major partnerships. NOT: generic EV industry articles that mention Tesla in a list.',\n '\"Tesla\" electric vehicle OR \"Tesla Motors\" OR TSLA automotive',\n ]\n});\n\n// ─── CORS ─────────────────────────────────────────────────────────────────\n\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, PUT, 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// ─── Helpers ──────────────────────────────────────────────────────────────\n\nfunction isBlockedSource(sourceName: string, url: string, title: string): boolean {\n const combined = (sourceName + \" \" + url).toLowerCase();\n const titleLower = title.toLowerCase();\n const blockedPatterns = [\n 'legacy.com', 'dignitymemorial.com', 'findagrave.com', 'tributes.com',\n 'newcomerdayton', 'bolock', 'ratterman', 'hodapp', 'sunset options',\n 'funeral home', 'mortuary', 'cremation service', 'funeral services',\n 'chase parkway memorial', 'buffalospree.com',\n 'zillow.com', 'realtor.com', 'apartments.com', 'indeed.com', 'glassdoor.com',\n 'reddit.com', 'twitter.com', 'instagram.com', 'tiktok.com', 'pinterest.com',\n 'ycombinator.com', 'hacker news',\n ];\n for (const p of blockedPatterns) {\n if (combined.includes(p)) return true;\n }\n if (titleLower.includes('obituary') || titleLower.includes('obituaries')) return true;\n // Block celebrity-named-Behr patterns when source is entertainment\n const entertainmentSources = ['people.com', 'tmz', 'us weekly', 'entertainment tonight', 'celebrity'];\n if (entertainmentSources.some(e => combined.includes(e))) return true;\n return false;\n}\n\nfunction classifyTierSync(sourceName: string): { tier: number; source_type: string } {\n const lc = sourceName.toLowerCase();\n const tier1 = ['reuters', 'bloomberg', 'new york times', 'nytimes', 'wall street journal', 'wsj',\n 'washington post', 'bbc', 'cnn', 'nbc news', 'abc news', 'cbs news', 'fox news', 'npr',\n 'usa today', 'guardian', 'the atlantic', 'fortune', 'time', 'newsweek', 'cnbc', 'marketwatch',\n 'forbes', 'business insider', 'yahoo finance', 'yahoo news', 'yahoo', 'associated press',\n 'ap news', 'financial times', 'the verge', 'techcrunch', 'wired', 'axios', 'politico',\n 'the hill', 'new yorker', 'vogue', 'variety', 'rolling stone', 'engadget', 'cbsnews'];\n const tier2 = ['adweek', 'ad age', 'adage', 'prweek', 'pr week', 'chief marketer',\n 'coatings world', 'coatingsworld', 'paint square', 'paintsquare', 'hardware retailing',\n 'hardwareretailing', 'chemanalyst', 'home accents', 'architectural digest',\n 'good housekeeping', 'better homes', 'southern living', 'real simple', 'homes and gardens',\n 'realtor.com', 'the information', 'slashdot', 'ars technica', 'arstechnica',\n 'zdnet', 'cnet', 'pcmag', 'techradar', 'gizmodo', 'electrek', '9to5',\n 'notebookcheck', 'futurism', 'business journals', 'bizjournals'];\n if (tier1.some(p => lc.includes(p))) return { tier: 1, source_type: 'mainstream_news' };\n if (tier2.some(p => lc.includes(p))) return { tier: 2, source_type: 'mainstream_news' };\n return { tier: 3, source_type: 'mainstream_news' };\n}\n\nasync function classifyPublicationTier(sourceName: string): Promise<{ tier: number; source_type: string }> {\n const syncResult = classifyTierSync(sourceName);\n // Only use AI for tier 3 unknowns if we want (skip to save API calls)\n return syncResult;\n}\n\nasync function scoreRelevance(\n title: string, snippet: string, sourceName: string,\n brandName: string, industry: string, parentCompany: string, relevanceDefinition: string\n): Promise<{ score: number; reason: string }> {\n const apiKey = Deno.env.get(\"valtown\");\n if (!apiKey) return { score: 50, reason: \"No API key\" };\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `You are evaluating whether a news article belongs in a PR monitoring feed.\n\nBrand: ${brandName}\nIndustry: ${industry}\nParent company: ${parentCompany || \"independent\"}\nWhat counts as relevant: ${relevanceDefinition}\n\nScore 0-100:\n- 80-100: Article is PRIMARILY about ${brandName} as a company (product launch, campaign, partnership, news, controversy, analyst coverage)\n- 60-79: ${brandName} is a significant subject (not just mentioned)\n- 40-59: ${brandName} is mentioned but not the main focus\n- 0-39: \"${brandName}\" refers to something else (a person's surname, a foreign word, an unrelated entity), OR it is an obituary, celebrity gossip, job listing, or real estate listing\n\nArticle title: ${title}\nSnippet: ${snippet || \"(no snippet)\"}\nSource: ${sourceName}\n\nRespond with JSON only: {\"score\": N, \"reason\": \"one sentence\"}` }],\n max_tokens: 100,\n }),\n });\n if (!resp.ok) return { score: 50, reason: \"API error\" };\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n return { score: parseInt(parsed.score) || 0, reason: parsed.reason || \"\" };\n } catch (_e) {\n return { score: 50, reason: \"Parse error\" };\n }\n}\n\nasync function getSentiment(title: string, snippet: string, brandName: string): Promise<{ sentiment: string; score: number; reason: string }> {\n const apiKey = Deno.env.get(\"valtown\");\n if (!apiKey) return { sentiment: \"neutral\", score: 0, reason: \"\" };\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `Is this article positive, neutral, or negative for ${brandName}'s public image?\\nTitle: ${title}\\nSnippet: ${snippet || \"\"}\\nRespond JSON: {\"sentiment\": \"positive\"|\"neutral\"|\"negative\", \"score\": -1.0 to 1.0, \"reason\": \"one sentence\"}` }],\n max_tokens: 100,\n }),\n });\n if (!resp.ok) return { sentiment: \"neutral\", score: 0, reason: \"\" };\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n const sentiment = [\"positive\", \"neutral\", \"negative\"].includes(parsed.sentiment) ? parsed.sentiment : \"neutral\";\n return { sentiment, score: parsed.score || 0, reason: parsed.reason || \"\" };\n } catch (_e) {\n return { sentiment: \"neutral\", score: 0, reason: \"\" };\n }\n}\n\nasync function generateBrandProfile(brandName: string): Promise<{\n industry: string; parent_company: string; aliases: string[];\n key_products: string[]; competitors: string[]; relevance_definition: string; search_query: string;\n}> {\n const apiKey = Deno.env.get(\"valtown\");\n const def = { industry: \"\", parent_company: \"\", aliases: [brandName], key_products: [], competitors: [], relevance_definition: `Article must be primarily about ${brandName} as a company.`, search_query: `\"${brandName}\"` };\n if (!apiKey) return def;\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `Generate a brand profile for PR monitoring for: \"${brandName}\"\\n\\nReturn JSON:\\n{\"industry\":\"Primary industry\",\"parent_company\":\"Parent company or empty string\",\"aliases\":[\"name variants\"],\"key_products\":[\"top 3-5 products\"],\"competitors\":[\"top 3-5 competitors\"],\"relevance_definition\":\"2-3 sentences defining relevant articles and what to exclude\",\"search_query\":\"search query with industry qualifiers, use quoted phrases for precision\"}` }],\n max_tokens: 400,\n }),\n });\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n return { ...def, ...JSON.parse(content) };\n } catch (_e) {\n return def;\n }\n}\n\nfunction computeStoryHash(title: string): string {\n return title.toLowerCase().replace(/[^a-z0-9 ]/g, \"\").replace(/\\b(the|a|an|is|are|has|have|of|in|on|for|to|and|or|but|with|its|was)\\b/g, \"\").replace(/\\s+/g, \" \").trim().slice(0, 80);\n}\n\n// ─── Data Sources ──────────────────────────────────────────────────────────\n\nasync function fetchFromGoogleNews(query: string): Promise> {\n const rssUrl = `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, { headers: { \"User-Agent\": \"Mozilla/5.0 (compatible; PRMonitor/2.0)\" } });\n if (!rssResp.ok) return [];\n const xml = await rssResp.text();\n const itemRegex = /([\\s\\S]*?)<\\/item>/g;\n const articles: Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }> = [];\n let itemMatch;\n while ((itemMatch = itemRegex.exec(xml)) !== null) {\n const item = itemMatch[1];\n const titleMatch = item.match(/<!\\[CDATA\\[([^\\]]+)\\]\\]><\\/title>/) || item.match(/<title>([^<]+)<\\/title>/);\n const linkMatch = item.match(/<link>([^<]+)<\\/link>/);\n const sourceMatch = item.match(/<source[^>]*><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/source>/) || item.match(/<source[^>]*>([^<]+)<\\/source>/);\n const pubDateMatch = item.match(/<pubDate>([^<]+)<\\/pubDate>/);\n const descMatch = item.match(/<description><!\\[CDATA\\[([^\\]]*)\\]\\]><\\/description>/) || item.match(/<description>([^<]*)<\\/description>/);\n if (titleMatch && linkMatch) {\n const pubDate = pubDateMatch ? Math.floor(new Date(pubDateMatch[1]).getTime() / 1000) : Math.floor(Date.now() / 1000);\n const snippet = descMatch ? descMatch[1].replace(/<[^>]+>/g, \"\").trim().slice(0, 200) : \"\";\n articles.push({ title: titleMatch[1].trim(), url: linkMatch[1].trim(), source: sourceMatch ? sourceMatch[1].trim() : \"Google News\", pubDate, snippet });\n }\n }\n return articles;\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchFromNewsApi(query: string): Promise<Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }>> {\n const newsApiKey = Deno.env.get(\"NEWS_API_KEY\") || \"960ad67b7cd6404995c74781be087fee\";\n try {\n const url = `https://newsapi.org/v2/everything?q=${encodeURIComponent(query)}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${newsApiKey}`;\n const resp = await fetch(url, { headers: { \"User-Agent\": \"PRMonitor/2.0\" } });\n if (!resp.ok) return [];\n const data = await resp.json();\n if (!data.articles) return [];\n return data.articles.map((a: { title?: string; url?: string; source?: { name?: string }; publishedAt?: string; description?: string }) => ({\n title: a.title || \"Untitled\",\n url: a.url || \"\",\n source: a.source?.name || \"NewsAPI\",\n pubDate: a.publishedAt ? Math.floor(new Date(a.publishedAt).getTime() / 1000) : Math.floor(Date.now() / 1000),\n snippet: (a.description || \"\").slice(0, 200),\n })).filter((a: { url: string }) => a.url);\n } catch (_e) {\n return [];\n }\n}\n\n// ─── Core Collection ──────────────────────────────────────────────────────\n\nasync function collectArticles(brandId: number): Promise<{ collected: number; rejected: number; total_fetched: number; sources: Record<string, number> }> {\n const brandRows = await sqlite.execute({ sql: `SELECT name, search_query, industry, parent_company, relevance_definition FROM brands WHERE id = ?`, args: [brandId] });\n if (!brandRows.rows.length) return { collected: 0, rejected: 0, total_fetched: 0, sources: {} };\n\n const brandName = brandRows.rows[0][0] as string;\n const searchQuery = (brandRows.rows[0][1] as string) || `\"${brandName}\"`;\n const industry = (brandRows.rows[0][2] as string) || \"\";\n const parentCompany = (brandRows.rows[0][3] as string) || \"\";\n const relevanceDefinition = (brandRows.rows[0][4] as string) || `Article must be primarily about ${brandName} as a company.`;\n\n const [googleArticles, newsApiArticles] = await Promise.all([\n fetchFromGoogleNews(searchQuery),\n fetchFromNewsApi(brandName),\n ]);\n\n const allRaw = [...googleArticles, ...newsApiArticles];\n\n const seenUrls = new Set<string>();\n const deduped = allRaw.filter((a) => {\n if (!a.url || seenUrls.has(a.url)) return false;\n seenUrls.add(a.url);\n return true;\n });\n\n const afterBlocklist = deduped.filter((a) => !isBlockedSource(a.source, a.url, a.title));\n\n let collected = 0;\n let rejected = 0;\n const sourceCounters: Record<string, number> = { google_news: 0, newsapi: 0 };\n const today = new Date().toISOString().split(\"T\")[0];\n let posCount = 0, neuCount = 0, negCount = 0;\n\n const existingHashes = await sqlite.execute({ sql: `SELECT story_hash FROM articles WHERE brand_id = ? AND collected_at >= ? AND story_hash != ''`, args: [brandId, Math.floor(Date.now() / 1000) - 86400 * 3] });\n const thisRunHashes = new Set<string>(existingHashes.rows.map((r) => r[0] as string));\n\n const toProcess = afterBlocklist.slice(0, 60);\n\n for (const article of toProcess) {\n const snippet = article.snippet || \"\";\n\n const { score: relevanceScore, reason: relevanceReason } = await scoreRelevance(article.title, snippet, article.source, brandName, industry, parentCompany, relevanceDefinition);\n\n if (relevanceScore < 65) {\n rejected++;\n continue;\n }\n\n const storyHash = computeStoryHash(article.title);\n if (thisRunHashes.has(storyHash)) {\n rejected++;\n continue;\n }\n thisRunHashes.add(storyHash);\n\n const { sentiment, score: sentimentScore, reason: sentimentReason } = await getSentiment(article.title, snippet, brandName);\n const { tier, source_type } = classifyTierSync(article.source);\n\n let sourceDomain = \"\";\n try { sourceDomain = new URL(article.url).hostname.replace(\"www.\", \"\"); } catch (_) {}\n\n const isFromGoogle = googleArticles.some((a) => a.url === article.url);\n if (isFromGoogle) sourceCounters.google_news++;\n else sourceCounters.newsapi++;\n\n try {\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO articles (brand_id, title, url, source_name, source_domain, published_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, story_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [brandId, article.title, article.url, article.source, sourceDomain, article.pubDate, relevanceScore, relevanceReason, sentiment, sentimentScore, sentimentReason, source_type, tier, storyHash],\n });\n if (result.rowsAffected > 0) {\n collected++;\n if (sentiment === \"positive\") posCount++;\n else if (sentiment === \"negative\") negCount++;\n else neuCount++;\n }\n } catch (_e) {}\n }\n\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) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(brand_id, date) DO UPDATE SET article_count = article_count + excluded.article_count, positive_count = positive_count + excluded.positive_count, neutral_count = neutral_count + excluded.neutral_count, negative_count = negative_count + excluded.negative_count`,\n args: [brandId, today, collected, posCount, neuCount, negCount],\n });\n }\n\n return { collected, rejected, total_fetched: deduped.length, 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\") return new Response(null, { headers: corsHeaders });\n\n // GET /api/brands\n if (req.method === \"GET\" && path === \"/api/brands\") {\n const rows = await sqlite.execute(`SELECT b.id, b.name, b.industry, b.parent_company, b.search_query, b.created_at, COUNT(a.id) as article_count, MAX(a.collected_at) as last_collected_at FROM brands b LEFT JOIN articles a ON b.id = a.brand_id AND a.relevance_score >= 65 GROUP BY b.id ORDER BY b.name`);\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], name: r[1], industry: r[2], parent_company: r[3], search_query: r[4], created_at: r[5], article_count: r[6], last_collected_at: r[7] })));\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({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n if (existing.rows.length > 0) return jsonResponse({ error: \"Brand already exists\" }, 409);\n await sqlite.execute({ sql: `INSERT INTO brands (name) VALUES (?)`, args: [body.name] });\n const newBrand = await sqlite.execute({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n const brandId = newBrand.rows[0][0] as number;\n const profile = await generateBrandProfile(body.name);\n await sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE id=?`,\n args: [profile.industry, profile.parent_company, JSON.stringify(profile.aliases), JSON.stringify(profile.key_products), JSON.stringify(profile.competitors), profile.relevance_definition, profile.search_query, brandId],\n });\n return jsonResponse({ id: brandId, name: body.name, ...profile }, 201);\n } catch (e) {\n const msg = e instanceof Error ? e.message : String(e);\n if (msg.includes(\"UNIQUE\") || msg.includes(\"unique\")) return jsonResponse({ error: \"Brand already exists\" }, 409);\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/profile\n const brandIdMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/profile$/);\n if (req.method === \"GET\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const rows = await sqlite.execute({ sql: `SELECT name, industry, parent_company, aliases, key_products, competitors, relevance_definition, search_query FROM brands WHERE id = ?`, args: [id] });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const r = rows.rows[0];\n return jsonResponse({ id, name: r[0], industry: r[1], parent_company: r[2], aliases: JSON.parse(r[3] as string || \"[]\"), key_products: JSON.parse(r[4] as string || \"[]\"), competitors: JSON.parse(r[5] as string || \"[]\"), relevance_definition: r[6], search_query: r[7] });\n }\n\n // PUT /api/brands/:id/profile\n if (req.method === \"PUT\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const body = await req.json();\n await sqlite.execute({\n sql: `UPDATE brands SET industry=COALESCE(?,industry), parent_company=COALESCE(?,parent_company), aliases=COALESCE(?,aliases), key_products=COALESCE(?,key_products), competitors=COALESCE(?,competitors), relevance_definition=COALESCE(?,relevance_definition), search_query=COALESCE(?,search_query), updated_at=unixepoch() WHERE id=?`,\n args: [body.industry||null, body.parent_company||null, body.aliases?JSON.stringify(body.aliases):null, body.key_products?JSON.stringify(body.key_products):null, body.competitors?JSON.stringify(body.competitors):null, body.relevance_definition||null, body.search_query||null, id],\n });\n return jsonResponse({ updated: true });\n }\n\n // DELETE /api/brands/:id/articles\n const brandArticlesMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/articles$/);\n if (req.method === \"DELETE\" && brandArticlesMatch) {\n const id = parseInt(brandArticlesMatch[1]);\n const result = 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 return jsonResponse({ deleted: result.rowsAffected });\n }\n\n // GET /api/articles\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 minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const rows = await sqlite.execute({\n sql: `SELECT id, title, url, source_name, source_domain, published_at, collected_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC LIMIT 100`,\n args: [parseInt(brandId), cutoff, minRelevance],\n });\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], source_domain: r[4], published_at: r[5], collected_at: r[6], relevance_score: r[7], relevance_reason: r[8], sentiment: r[9], sentiment_score: r[10], sentiment_reason: r[11], source_type: r[12], publication_tier: r[13] })));\n }\n\n // GET /api/report\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 const minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n const days = period === \"month\" ? 30 : period === \"day\" ? 1 : 7;\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const brandRows = await sqlite.execute({ sql: `SELECT id, name FROM brands WHERE id = ?`, args: [parseInt(brandId)] });\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 const articleRows = await sqlite.execute({ sql: `SELECT id, title, url, source_name, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, collected_at, relevance_score FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC`, args: [parseInt(brandId), cutoff, minRelevance] });\n const articles = articleRows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], sentiment: r[4], sentiment_score: r[5], sentiment_reason: r[6], source_type: r[7], publication_tier: r[8], collected_at: r[9], relevance_score: r[10] }));\n const sentimentBreakdown = { positive: 0, neutral: 0, negative: 0 };\n const tierBreakdown = { tier_1: 0, tier_2: 0, tier_3: 0 };\n const sourceMap: Record<string, number> = {};\n for (const a of articles) {\n const s = a.sentiment as string;\n if (s === \"positive\") sentimentBreakdown.positive++;\n else if (s === \"negative\") sentimentBreakdown.negative++;\n else sentimentBreakdown.neutral++;\n if (a.publication_tier === 1) tierBreakdown.tier_1++;\n else if (a.publication_tier === 2) tierBreakdown.tier_2++;\n else tierBreakdown.tier_3++;\n const src = (a.source_name as string) || \"Unknown\";\n sourceMap[src] = (sourceMap[src] || 0) + 1;\n }\n const topSources = Object.entries(sourceMap).sort((a, b) => b[1] - a[1]).slice(0, 10).map(([name, count]) => ({ name, count }));\n return jsonResponse({ brand, period, total_articles: articles.length, sentiment_breakdown: sentimentBreakdown, tier_breakdown: tierBreakdown, top_sources: topSources, recent_articles: articles.slice(0, 30) });\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","deleted",false,"label",[37],"e1c94eff-59a2-4307-9dee-e974c01c7677","index.tsx","6529976a-78c2-11f1-b86f-1607ee4eb77e",26,["D",1783339002574],14,"http","import { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// ─── Schema Setup ──────────────────────────────────────────────────────────\n\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS brands (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL UNIQUE,\n industry TEXT DEFAULT '',\n parent_company TEXT DEFAULT '',\n aliases TEXT DEFAULT '[]',\n key_products TEXT DEFAULT '[]',\n competitors TEXT DEFAULT '[]',\n relevance_definition TEXT DEFAULT '',\n search_query TEXT DEFAULT '',\n created_at INTEGER DEFAULT (unixepoch()),\n updated_at INTEGER DEFAULT (unixepoch())\n)`);\n\nconst brandCols = [\n ['industry', 'TEXT DEFAULT \"\"'],\n ['parent_company', 'TEXT DEFAULT \"\"'],\n ['aliases', 'TEXT DEFAULT \"[]\"'],\n ['key_products', 'TEXT DEFAULT \"[]\"'],\n ['competitors', 'TEXT DEFAULT \"[]\"'],\n ['relevance_definition', 'TEXT DEFAULT \"\"'],\n ['search_query', 'TEXT DEFAULT \"\"'],\n ['updated_at', 'INTEGER DEFAULT 0'],\n];\nfor (const [col, def] of brandCols) {\n try { await sqlite.execute(`ALTER TABLE brands ADD COLUMN ${col} ${def}`); } catch (_) {}\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 DEFAULT '',\n source_domain TEXT DEFAULT '',\n published_at INTEGER DEFAULT 0,\n collected_at INTEGER DEFAULT (unixepoch()),\n relevance_score INTEGER DEFAULT 0,\n relevance_reason TEXT DEFAULT '',\n sentiment TEXT DEFAULT 'neutral',\n sentiment_score REAL DEFAULT 0,\n sentiment_reason TEXT DEFAULT '',\n source_type TEXT DEFAULT 'mainstream_news',\n publication_tier INTEGER DEFAULT 3,\n story_hash TEXT DEFAULT ''\n)`);\n\nconst articleCols = [\n ['source_domain', 'TEXT DEFAULT \"\"'],\n ['relevance_score', 'INTEGER DEFAULT 0'],\n ['relevance_reason', 'TEXT DEFAULT \"\"'],\n ['sentiment_reason', 'TEXT DEFAULT \"\"'],\n ['story_hash', 'TEXT DEFAULT \"\"'],\n];\nfor (const [col, def] of articleCols) {\n try { await sqlite.execute(`ALTER TABLE articles ADD COLUMN ${col} ${def}`); } catch (_) {}\n}\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\n// Seed Behr profile\nawait sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE name='Behr'`,\n args: [\n 'Paint / Home Improvement',\n 'Masco Corporation (Home Depot subsidiary)',\n '[\"BEHR\",\"BEHR Paint\",\"Behr Process Corporation\",\"BEHR Premium\",\"Behr Paint Company\"]',\n '[\"BEHR Premium Plus\",\"Marquee Interior Paint\",\"BEHR PRO\",\"DeckOver\",\"ECOMIX Plant-Based Paint\"]',\n '[\"Sherwin-Williams\",\"Benjamin Moore\",\"Valspar\",\"PPG Paints\"]',\n 'Article must be primarily about Behr Paint Company as a business entity — product launches, retail partnerships, marketing campaigns, company news, lawsuits involving the company, or financial results. NOT: articles mentioning Behr as a German word, a surname, or a person unrelated to the paint company.',\n '\"Behr Paint\" OR \"BEHR Premium\" OR \"Behr Paint Company\"',\n ]\n});\n\nawait sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE name='Tesla'`,\n args: [\n 'Electric Vehicles / Automotive Technology',\n '',\n '[\"Tesla Motors\",\"TSLA\"]',\n '[\"Model S\",\"Model 3\",\"Model X\",\"Model Y\",\"Cybertruck\",\"Powerwall\",\"Full Self-Driving\"]',\n '[\"Ford\",\"GM\",\"Rivian\",\"Lucid\",\"BYD\",\"Volkswagen\"]',\n 'Article must be primarily about Tesla as a company — vehicle launches, recalls, Autopilot/FSD, financial results, Elon Musk in Tesla CEO role, manufacturing, or major partnerships. NOT: generic EV industry articles that mention Tesla in a list.',\n '\"Tesla\" electric vehicle OR \"Tesla Motors\" OR TSLA automotive',\n ]\n});\n\n// ─── CORS ─────────────────────────────────────────────────────────────────\n\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET, POST, PUT, 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// ─── Helpers ──────────────────────────────────────────────────────────────\n\nfunction isBlockedSource(sourceName: string, url: string, title: string): boolean {\n const combined = (sourceName + \" \" + url).toLowerCase();\n const titleLower = title.toLowerCase();\n const blockedPatterns = [\n 'legacy.com', 'dignitymemorial.com', 'findagrave.com', 'tributes.com',\n 'newcomerdayton', 'bolock', 'ratterman', 'hodapp', 'sunset options',\n 'funeral home', 'mortuary', 'cremation service', 'funeral services',\n 'chase parkway memorial', 'buffalospree.com',\n 'zillow.com', 'realtor.com', 'apartments.com', 'indeed.com', 'glassdoor.com',\n 'reddit.com', 'twitter.com', 'instagram.com', 'tiktok.com', 'pinterest.com',\n 'ycombinator.com', 'hacker news',\n ];\n for (const p of blockedPatterns) {\n if (combined.includes(p)) return true;\n }\n if (titleLower.includes('obituary') || titleLower.includes('obituaries')) return true;\n // Block celebrity-named-Behr patterns when source is entertainment\n const entertainmentSources = ['people.com', 'tmz', 'us weekly', 'entertainment tonight', 'celebrity'];\n if (entertainmentSources.some(e => combined.includes(e))) return true;\n return false;\n}\n\nfunction classifyTierSync(sourceName: string): { tier: number; source_type: string } {\n const lc = sourceName.toLowerCase();\n const tier1 = ['reuters', 'bloomberg', 'new york times', 'nytimes', 'wall street journal', 'wsj',\n 'washington post', 'bbc', 'cnn', 'nbc news', 'abc news', 'cbs news', 'fox news', 'npr',\n 'usa today', 'guardian', 'the atlantic', 'fortune', 'time', 'newsweek', 'cnbc', 'marketwatch',\n 'forbes', 'business insider', 'yahoo finance', 'yahoo news', 'yahoo', 'associated press',\n 'ap news', 'financial times', 'the verge', 'techcrunch', 'wired', 'axios', 'politico',\n 'the hill', 'new yorker', 'vogue', 'variety', 'rolling stone', 'engadget', 'cbsnews'];\n const tier2 = ['adweek', 'ad age', 'adage', 'prweek', 'pr week', 'chief marketer',\n 'coatings world', 'coatingsworld', 'paint square', 'paintsquare', 'hardware retailing',\n 'hardwareretailing', 'chemanalyst', 'home accents', 'architectural digest',\n 'good housekeeping', 'better homes', 'southern living', 'real simple', 'homes and gardens',\n 'realtor.com', 'the information', 'slashdot', 'ars technica', 'arstechnica',\n 'zdnet', 'cnet', 'pcmag', 'techradar', 'gizmodo', 'electrek', '9to5',\n 'notebookcheck', 'futurism', 'business journals', 'bizjournals'];\n if (tier1.some(p => lc.includes(p))) return { tier: 1, source_type: 'mainstream_news' };\n if (tier2.some(p => lc.includes(p))) return { tier: 2, source_type: 'mainstream_news' };\n return { tier: 3, source_type: 'mainstream_news' };\n}\n\nasync function classifyPublicationTier(sourceName: string): Promise<{ tier: number; source_type: string }> {\n const syncResult = classifyTierSync(sourceName);\n // Only use AI for tier 3 unknowns if we want (skip to save API calls)\n return syncResult;\n}\n\n// Heuristic relevance check when AI is unavailable\n// Returns a score 0-100 based on keyword signals in title\nfunction heuristicRelevance(title: string, snippet: string, brandName: string, aliases: string[]): { score: number; reason: string } {\n const titleLower = title.toLowerCase();\n const combined = (title + \" \" + snippet).toLowerCase();\n const brandLower = brandName.toLowerCase();\n const allNames = [brandLower, ...aliases.map(a => a.toLowerCase())];\n\n // Check if brand name appears in title at all\n const brandInTitle = allNames.some(n => titleLower.includes(n));\n const brandInContent = allNames.some(n => combined.includes(n));\n\n if (!brandInTitle && !brandInContent) return { score: 10, reason: \"Brand not mentioned\" };\n if (!brandInTitle) return { score: 40, reason: \"Brand only in snippet, not title\" };\n\n // Brand IS in title. Now check for false-positive patterns:\n // \"Brand\" as a generic word appearing in a listicle of many topics\n const listArticlePattern = titleLower.split(',').length >= 4; // \"Topic A, Topic B, Topic C, Topic D\"\n if (listArticlePattern && !allNames.some(n => titleLower.startsWith(n))) {\n return { score: 20, reason: \"Appears to be a roundup not primarily about brand\" };\n }\n\n // Strong positive signals: brand name is the subject\n const startsWithBrand = allNames.some(n => titleLower.startsWith(n) || titleLower.startsWith(\"the \" + n));\n const brandPossessive = allNames.some(n => titleLower.includes(n + \"'s\") || titleLower.includes(n + \" paint\") || titleLower.includes(n + \" names\") || titleLower.includes(n + \" unveils\") || titleLower.includes(n + \" launches\") || titleLower.includes(n + \" announces\") || titleLower.includes(n + \" is \") || titleLower.includes(n + \" just \") || titleLower.includes(n + \" and \") || titleLower.includes(n + \" vs \"));\n\n if (startsWithBrand || brandPossessive) return { score: 82, reason: \"Brand is primary subject of title\" };\n\n // Medium: brand appears in title but not as subject\n return { score: 68, reason: \"Brand mentioned in title\" };\n}\n\nasync function scoreRelevance(\n title: string, snippet: string, sourceName: string,\n brandName: string, industry: string, parentCompany: string, relevanceDefinition: string,\n aliases: string[] = []\n): Promise<{ score: number; reason: string }> {\n const apiKey = Deno.env.get(\"valtown\");\n if (!apiKey) return heuristicRelevance(title, snippet, brandName, aliases);\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `You are evaluating whether a news article belongs in a PR monitoring feed.\n\nBrand: ${brandName}\nIndustry: ${industry}\nParent company: ${parentCompany || \"independent\"}\nWhat counts as relevant: ${relevanceDefinition}\n\nScore 0-100:\n- 80-100: Article is PRIMARILY about ${brandName} as a company (product launch, campaign, partnership, news, controversy, analyst coverage)\n- 60-79: ${brandName} is a significant subject (not just mentioned)\n- 40-59: ${brandName} is mentioned but not the main focus\n- 0-39: \"${brandName}\" refers to something else (a person's surname, a foreign word, an unrelated entity), OR it is an obituary, celebrity gossip, job listing, or real estate listing\n\nArticle title: ${title}\nSnippet: ${snippet || \"(no snippet)\"}\nSource: ${sourceName}\n\nRespond with JSON only: {\"score\": N, \"reason\": \"one sentence\"}` }],\n max_tokens: 100,\n }),\n });\n if (!resp.ok) return heuristicRelevance(title, snippet, brandName, aliases);\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n const score = parseInt(parsed.score);\n if (isNaN(score)) return heuristicRelevance(title, snippet, brandName, aliases);\n return { score, reason: parsed.reason || \"\" };\n } catch (_e) {\n return heuristicRelevance(title, snippet, brandName, aliases);\n }\n}\n\nasync function getSentiment(title: string, snippet: string, brandName: string): Promise<{ sentiment: string; score: number; reason: string }> {\n const apiKey = Deno.env.get(\"valtown\");\n if (!apiKey) return { sentiment: \"neutral\", score: 0, reason: \"\" };\n try {\n const resp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${apiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `Is this article positive, neutral, or negative for ${brandName}'s public image?\\nTitle: ${title}\\nSnippet: ${snippet || \"\"}\\nRespond JSON: {\"sentiment\": \"positive\"|\"neutral\"|\"negative\", \"score\": -1.0 to 1.0, \"reason\": \"one sentence\"}` }],\n max_tokens: 100,\n }),\n });\n if (!resp.ok) return { sentiment: \"neutral\", score: 0, reason: \"\" };\n const data = await resp.json();\n const content = (data.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n const sentiment = [\"positive\", \"neutral\", \"negative\"].includes(parsed.sentiment) ? parsed.sentiment : \"neutral\";\n return { sentiment, score: parsed.score || 0, reason: parsed.reason || \"\" };\n } catch (_e) {\n return { sentiment: \"neutral\", score: 0, reason: \"\" };\n }\n}\n\nasync function generateBrandProfile(brandName: string): Promise<{\n industry: string; parent_company: string; aliases: string[];\n key_products: string[]; competitors: string[]; relevance_definition: string; search_query: string;\n}> {\n const def = { industry: \"\", parent_company: \"\", aliases: [brandName], key_products: [] as string[], competitors: [] as string[], relevance_definition: `Article must be primarily about ${brandName} as a company.`, search_query: `\"${brandName}\"` };\n // Use Anthropic Claude API for reliable brand profile generation\n const anthropicKey = \"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\";\n try {\n const resp = await fetch(\"https://api.anthropic.com/v1/messages\", {\n method: \"POST\",\n headers: {\n \"x-api-key\": anthropicKey,\n \"anthropic-version\": \"2023-06-01\",\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n model: \"claude-haiku-4-5\",\n max_tokens: 600,\n messages: [{\n role: \"user\",\n content: `Generate a brand profile for PR monitoring for the company: \"${brandName}\"\n\nReturn a JSON object with exactly these fields:\n{\n \"industry\": \"Primary industry (e.g. Paint / Home Improvement)\",\n \"parent_company\": \"Parent company name, or empty string if independent\",\n \"aliases\": [\"array of brand name variants and alternate spellings\"],\n \"key_products\": [\"top 3-5 flagship products or product lines\"],\n \"competitors\": [\"top 3-5 direct competitors\"],\n \"relevance_definition\": \"2-3 sentences defining what articles ARE relevant (e.g. product launches, partnerships, financial news) and explicitly what to EXCLUDE (e.g. articles where the brand name refers to a person's surname, foreign word, or unrelated entity)\",\n \"search_query\": \"Google News search query using quoted phrases and OR operators for precision, e.g. \\\\\"Brand Name Product\\\\\" OR \\\\\"Brand Inc\\\\\" industry\"\n}\n\nRespond with JSON only, no markdown code blocks.`\n }],\n }),\n });\n if (!resp.ok) {\n // Fall back to OpenAI proxy if Anthropic fails\n const valApiKey = Deno.env.get(\"valtown\");\n if (valApiKey) {\n const oaiResp = await fetch(\"https://std-openaiproxy.web.val.run/v1/chat/completions\", {\n method: \"POST\",\n headers: { \"Authorization\": `Bearer ${valApiKey}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n model: \"gpt-4o-mini\",\n messages: [{ role: \"user\", content: `Generate a brand profile for PR monitoring for: \"${brandName}\"\\n\\nReturn JSON:\\n{\"industry\":\"Primary industry\",\"parent_company\":\"Parent company or empty string\",\"aliases\":[\"name variants\"],\"key_products\":[\"top 3-5 products\"],\"competitors\":[\"top 3-5 competitors\"],\"relevance_definition\":\"2-3 sentences defining relevant articles and what to exclude\",\"search_query\":\"search query with industry qualifiers, use quoted phrases for precision\"}` }],\n max_tokens: 400,\n }),\n });\n if (oaiResp.ok) {\n const oaiData = await oaiResp.json();\n const oaiContent = (oaiData.choices?.[0]?.message?.content || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n return { ...def, ...JSON.parse(oaiContent) };\n }\n }\n return def;\n }\n const data = await resp.json();\n const content = (data.content?.[0]?.text || \"{}\").replace(/```json\\s*/g, \"\").replace(/```\\s*/g, \"\").trim();\n const parsed = JSON.parse(content);\n return { ...def, ...parsed };\n } catch (_e) {\n return def;\n }\n}\n\nfunction computeStoryHash(title: string): string {\n return title.toLowerCase().replace(/[^a-z0-9 ]/g, \"\").replace(/\\b(the|a|an|is|are|has|have|of|in|on|for|to|and|or|but|with|its|was)\\b/g, \"\").replace(/\\s+/g, \" \").trim().slice(0, 80);\n}\n\n// ─── Data Sources ──────────────────────────────────────────────────────────\n\nasync function fetchFromGoogleNews(query: string): Promise<Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }>> {\n const rssUrl = `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, { headers: { \"User-Agent\": \"Mozilla/5.0 (compatible; PRMonitor/2.0)\" } });\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; snippet: string }> = [];\n let itemMatch;\n while ((itemMatch = itemRegex.exec(xml)) !== null) {\n const item = itemMatch[1];\n const titleMatch = item.match(/<title><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/title>/) || item.match(/<title>([^<]+)<\\/title>/);\n const linkMatch = item.match(/<link>([^<]+)<\\/link>/);\n const sourceMatch = item.match(/<source[^>]*><!\\[CDATA\\[([^\\]]+)\\]\\]><\\/source>/) || item.match(/<source[^>]*>([^<]+)<\\/source>/);\n const pubDateMatch = item.match(/<pubDate>([^<]+)<\\/pubDate>/);\n const descMatch = item.match(/<description><!\\[CDATA\\[([^\\]]*)\\]\\]><\\/description>/) || item.match(/<description>([^<]*)<\\/description>/);\n if (titleMatch && linkMatch) {\n const pubDate = pubDateMatch ? Math.floor(new Date(pubDateMatch[1]).getTime() / 1000) : Math.floor(Date.now() / 1000);\n const snippet = descMatch ? descMatch[1].replace(/<[^>]+>/g, \"\").trim().slice(0, 200) : \"\";\n articles.push({ title: titleMatch[1].trim(), url: linkMatch[1].trim(), source: sourceMatch ? sourceMatch[1].trim() : \"Google News\", pubDate, snippet });\n }\n }\n return articles;\n } catch (_e) {\n return [];\n }\n}\n\nasync function fetchFromNewsApi(query: string): Promise<Array<{ title: string; url: string; source: string; pubDate: number; snippet: string }>> {\n const newsApiKey = Deno.env.get(\"NEWS_API_KEY\") || \"960ad67b7cd6404995c74781be087fee\";\n try {\n const url = `https://newsapi.org/v2/everything?q=${encodeURIComponent(query)}&language=en&sortBy=publishedAt&pageSize=20&apiKey=${newsApiKey}`;\n const resp = await fetch(url, { headers: { \"User-Agent\": \"PRMonitor/2.0\" } });\n if (!resp.ok) return [];\n const data = await resp.json();\n if (!data.articles) return [];\n return data.articles.map((a: { title?: string; url?: string; source?: { name?: string }; publishedAt?: string; description?: string }) => ({\n title: a.title || \"Untitled\",\n url: a.url || \"\",\n source: a.source?.name || \"NewsAPI\",\n pubDate: a.publishedAt ? Math.floor(new Date(a.publishedAt).getTime() / 1000) : Math.floor(Date.now() / 1000),\n snippet: (a.description || \"\").slice(0, 200),\n })).filter((a: { url: string }) => a.url);\n } catch (_e) {\n return [];\n }\n}\n\n// ─── Core Collection ──────────────────────────────────────────────────────\n\nasync function collectArticles(brandId: number): Promise<{ collected: number; rejected: number; total_fetched: number; sources: Record<string, number> }> {\n const brandRows = await sqlite.execute({ sql: `SELECT name, search_query, industry, parent_company, relevance_definition, aliases FROM brands WHERE id = ?`, args: [brandId] });\n if (!brandRows.rows.length) return { collected: 0, rejected: 0, total_fetched: 0, sources: {} };\n\n const brandName = brandRows.rows[0][0] as string;\n const searchQuery = (brandRows.rows[0][1] as string) || `\"${brandName}\"`;\n const industry = (brandRows.rows[0][2] as string) || \"\";\n const parentCompany = (brandRows.rows[0][3] as string) || \"\";\n const relevanceDefinition = (brandRows.rows[0][4] as string) || `Article must be primarily about ${brandName} as a company.`;\n let aliases: string[] = [];\n try { aliases = JSON.parse((brandRows.rows[0][5] as string) || \"[]\"); } catch (_) {}\n\n const [googleArticles, newsApiArticles] = await Promise.all([\n fetchFromGoogleNews(searchQuery),\n fetchFromNewsApi(searchQuery),\n ]);\n\n const allRaw = [...googleArticles, ...newsApiArticles];\n\n const seenUrls = new Set<string>();\n const deduped = allRaw.filter((a) => {\n if (!a.url || seenUrls.has(a.url)) return false;\n seenUrls.add(a.url);\n return true;\n });\n\n const afterBlocklist = deduped.filter((a) => !isBlockedSource(a.source, a.url, a.title));\n\n let collected = 0;\n let rejected = 0;\n const sourceCounters: Record<string, number> = { google_news: 0, newsapi: 0 };\n const today = new Date().toISOString().split(\"T\")[0];\n let posCount = 0, neuCount = 0, negCount = 0;\n\n const existingHashes = await sqlite.execute({ sql: `SELECT story_hash FROM articles WHERE brand_id = ? AND collected_at >= ? AND story_hash != ''`, args: [brandId, Math.floor(Date.now() / 1000) - 86400 * 3] });\n const thisRunHashes = new Set<string>(existingHashes.rows.map((r) => r[0] as string));\n\n const toProcess = afterBlocklist.slice(0, 60);\n\n for (const article of toProcess) {\n const snippet = article.snippet || \"\";\n\n const { score: relevanceScore, reason: relevanceReason } = await scoreRelevance(article.title, snippet, article.source, brandName, industry, parentCompany, relevanceDefinition, aliases);\n\n if (relevanceScore < 65) {\n rejected++;\n continue;\n }\n\n const storyHash = computeStoryHash(article.title);\n if (thisRunHashes.has(storyHash)) {\n rejected++;\n continue;\n }\n thisRunHashes.add(storyHash);\n\n const { sentiment, score: sentimentScore, reason: sentimentReason } = await getSentiment(article.title, snippet, brandName);\n const { tier, source_type } = classifyTierSync(article.source);\n\n let sourceDomain = \"\";\n try { sourceDomain = new URL(article.url).hostname.replace(\"www.\", \"\"); } catch (_) {}\n\n const isFromGoogle = googleArticles.some((a) => a.url === article.url);\n if (isFromGoogle) sourceCounters.google_news++;\n else sourceCounters.newsapi++;\n\n try {\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO articles (brand_id, title, url, source_name, source_domain, published_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, story_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [brandId, article.title, article.url, article.source, sourceDomain, article.pubDate, relevanceScore, relevanceReason, sentiment, sentimentScore, sentimentReason, source_type, tier, storyHash],\n });\n if (result.rowsAffected > 0) {\n collected++;\n if (sentiment === \"positive\") posCount++;\n else if (sentiment === \"negative\") negCount++;\n else neuCount++;\n }\n } catch (_e) {}\n }\n\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) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(brand_id, date) DO UPDATE SET article_count = article_count + excluded.article_count, positive_count = positive_count + excluded.positive_count, neutral_count = neutral_count + excluded.neutral_count, negative_count = negative_count + excluded.negative_count`,\n args: [brandId, today, collected, posCount, neuCount, negCount],\n });\n }\n\n return { collected, rejected, total_fetched: deduped.length, 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\") return new Response(null, { headers: corsHeaders });\n\n // GET /api/brands\n if (req.method === \"GET\" && path === \"/api/brands\") {\n const rows = await sqlite.execute(`SELECT b.id, b.name, b.industry, b.parent_company, b.search_query, b.created_at, COUNT(a.id) as article_count, MAX(a.collected_at) as last_collected_at FROM brands b LEFT JOIN articles a ON b.id = a.brand_id AND a.relevance_score >= 65 GROUP BY b.id ORDER BY b.name`);\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], name: r[1], industry: r[2], parent_company: r[3], search_query: r[4], created_at: r[5], article_count: r[6], last_collected_at: r[7] })));\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({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n if (existing.rows.length > 0) return jsonResponse({ error: \"Brand already exists\" }, 409);\n await sqlite.execute({ sql: `INSERT INTO brands (name) VALUES (?)`, args: [body.name] });\n const newBrand = await sqlite.execute({ sql: `SELECT id FROM brands WHERE name = ?`, args: [body.name] });\n const brandId = newBrand.rows[0][0] as number;\n const profile = await generateBrandProfile(body.name);\n await sqlite.execute({\n sql: `UPDATE brands SET industry=?, parent_company=?, aliases=?, key_products=?, competitors=?, relevance_definition=?, search_query=?, updated_at=unixepoch() WHERE id=?`,\n args: [profile.industry, profile.parent_company, JSON.stringify(profile.aliases), JSON.stringify(profile.key_products), JSON.stringify(profile.competitors), profile.relevance_definition, profile.search_query, brandId],\n });\n return jsonResponse({ id: brandId, name: body.name, ...profile }, 201);\n } catch (e) {\n const msg = e instanceof Error ? e.message : String(e);\n if (msg.includes(\"UNIQUE\") || msg.includes(\"unique\")) return jsonResponse({ error: \"Brand already exists\" }, 409);\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/profile\n const brandIdMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/profile$/);\n if (req.method === \"GET\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const rows = await sqlite.execute({ sql: `SELECT name, industry, parent_company, aliases, key_products, competitors, relevance_definition, search_query FROM brands WHERE id = ?`, args: [id] });\n if (!rows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const r = rows.rows[0];\n return jsonResponse({ id, name: r[0], industry: r[1], parent_company: r[2], aliases: JSON.parse(r[3] as string || \"[]\"), key_products: JSON.parse(r[4] as string || \"[]\"), competitors: JSON.parse(r[5] as string || \"[]\"), relevance_definition: r[6], search_query: r[7] });\n }\n\n // PUT /api/brands/:id/profile\n if (req.method === \"PUT\" && brandIdMatch) {\n const id = parseInt(brandIdMatch[1]);\n const body = await req.json();\n await sqlite.execute({\n sql: `UPDATE brands SET industry=COALESCE(?,industry), parent_company=COALESCE(?,parent_company), aliases=COALESCE(?,aliases), key_products=COALESCE(?,key_products), competitors=COALESCE(?,competitors), relevance_definition=COALESCE(?,relevance_definition), search_query=COALESCE(?,search_query), updated_at=unixepoch() WHERE id=?`,\n args: [body.industry||null, body.parent_company||null, body.aliases?JSON.stringify(body.aliases):null, body.key_products?JSON.stringify(body.key_products):null, body.competitors?JSON.stringify(body.competitors):null, body.relevance_definition||null, body.search_query||null, id],\n });\n return jsonResponse({ updated: true });\n }\n\n // DELETE /api/brands/:id/articles\n const brandArticlesMatch = path.match(/^\\/api\\/brands\\/(\\d+)\\/articles$/);\n if (req.method === \"DELETE\" && brandArticlesMatch) {\n const id = parseInt(brandArticlesMatch[1]);\n const result = 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 return jsonResponse({ deleted: result.rowsAffected });\n }\n\n // GET /api/articles\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 minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n if (!brandId) return jsonResponse({ error: \"brand_id required\" }, 400);\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const rows = await sqlite.execute({\n sql: `SELECT id, title, url, source_name, source_domain, published_at, collected_at, relevance_score, relevance_reason, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC LIMIT 100`,\n args: [parseInt(brandId), cutoff, minRelevance],\n });\n return jsonResponse(rows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], source_domain: r[4], published_at: r[5], collected_at: r[6], relevance_score: r[7], relevance_reason: r[8], sentiment: r[9], sentiment_score: r[10], sentiment_reason: r[11], source_type: r[12], publication_tier: r[13] })));\n }\n\n // GET /api/report\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 const minRelevance = parseInt(url.searchParams.get(\"min_relevance\") || \"65\");\n const days = period === \"month\" ? 30 : period === \"day\" ? 1 : 7;\n const cutoff = Math.floor(Date.now() / 1000) - days * 86400;\n const brandRows = await sqlite.execute({ sql: `SELECT id, name FROM brands WHERE id = ?`, args: [parseInt(brandId)] });\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 const articleRows = await sqlite.execute({ sql: `SELECT id, title, url, source_name, sentiment, sentiment_score, sentiment_reason, source_type, publication_tier, collected_at, relevance_score FROM articles WHERE brand_id = ? AND collected_at >= ? AND relevance_score >= ? ORDER BY publication_tier ASC, collected_at DESC`, args: [parseInt(brandId), cutoff, minRelevance] });\n const articles = articleRows.rows.map((r) => ({ id: r[0], title: r[1], url: r[2], source_name: r[3], sentiment: r[4], sentiment_score: r[5], sentiment_reason: r[6], source_type: r[7], publication_tier: r[8], collected_at: r[9], relevance_score: r[10] }));\n const sentimentBreakdown = { positive: 0, neutral: 0, negative: 0 };\n const tierBreakdown = { tier_1: 0, tier_2: 0, tier_3: 0 };\n const sourceMap: Record<string, number> = {};\n for (const a of articles) {\n const s = a.sentiment as string;\n if (s === \"positive\") sentimentBreakdown.positive++;\n else if (s === \"negative\") sentimentBreakdown.negative++;\n else sentimentBreakdown.neutral++;\n if (a.publication_tier === 1) tierBreakdown.tier_1++;\n else if (a.publication_tier === 2) tierBreakdown.tier_2++;\n else tierBreakdown.tier_3++;\n const src = (a.source_name as string) || \"Unknown\";\n sourceMap[src] = (sourceMap[src] || 0) + 1;\n }\n const topSources = Object.entries(sourceMap).sort((a, b) => b[1] - a[1]).slice(0, 10).map(([name, count]) => ({ name, count }));\n return jsonResponse({ brand, period, total_articles: articles.length, sentiment_breakdown: sentimentBreakdown, tier_breakdown: tierBreakdown, top_sources: topSources, recent_articles: articles.slice(0, 30) });\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 // POST /api/debug-collect\n if (req.method === \"POST\" && path === \"/api/debug-collect\") {\n const body = await req.json();\n if (!body.brand_id) return jsonResponse({ error: \"brand_id required\" }, 400);\n const brandId = body.brand_id;\n const brandRows = await sqlite.execute({ sql: `SELECT name, search_query, industry, parent_company, relevance_definition FROM brands WHERE id = ?`, args: [brandId] });\n if (!brandRows.rows.length) return jsonResponse({ error: \"Brand not found\" }, 404);\n const brandName = brandRows.rows[0][0] as string;\n const searchQuery = (brandRows.rows[0][1] as string) || `\"${brandName}\"`;\n const industry = (brandRows.rows[0][2] as string) || \"\";\n const parentCompany = (brandRows.rows[0][3] as string) || \"\";\n const relevanceDefinition = (brandRows.rows[0][4] as string) || \"\";\n const apiKey = Deno.env.get(\"valtown\") || Deno.env.get(\"VALTOWN_TOKEN\");\n const [googleArticles, newsApiArticles] = await Promise.all([\n fetchFromGoogleNews(searchQuery),\n fetchFromNewsApi(searchQuery),\n ]);\n const sampleGoogle = googleArticles.slice(0, 3).map(a => ({ title: a.title, url: a.url.slice(0, 60), source: a.source }));\n const sampleNews = newsApiArticles.slice(0, 3).map(a => ({ title: a.title, url: a.url.slice(0, 60), source: a.source }));\n const testArticle = googleArticles[0];\n let testScore = { score: 0, reason: \"no articles\" };\n if (testArticle) {\n testScore = await scoreRelevance(testArticle.title, testArticle.snippet, testArticle.source, brandName, industry, parentCompany, relevanceDefinition);\n }\n const existingHashes = await sqlite.execute({ sql: `SELECT COUNT(*) FROM articles WHERE brand_id = ? AND collected_at >= ? AND story_hash != ''`, args: [brandId, Math.floor(Date.now() / 1000) - 86400 * 3] });\n return jsonResponse({\n brand: brandName,\n search_query: searchQuery,\n api_key_available: !!apiKey,\n google_count: googleArticles.length,\n newsapi_count: newsApiArticles.length,\n sample_google: sampleGoogle,\n sample_newsapi: sampleNews,\n test_score: testScore,\n existing_hashes: existingHashes.rows[0][0],\n });\n }\n\n return jsonResponse({ error: \"Not found\", path }, 404);\n}\n",[58],"4a6c5e85-b952-46dd-aaa1-9a41fcfb2f6e","backend","e767cc15-b91e-430a-9ff5-bbe470e7e661",1,["D",1783289363906],"directory",[67],{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":83,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_77":84,"_18":85},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":80,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_77":81,"_18":82},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":76,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_77":78,"_18":79},["D",1783298708366],"children",[],[37],["D",1783339002574],[],[58],["D",1783289363906],[86],[67],{"_35":87,"_13":88,"_38":89,"_40":68,"_41":42,"_43":90,"_45":46,"_47":91,"_17":92,"_50":63,"_51":93,"_53":54,"_55":-5,"_77":94,"_18":95},"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",[],[67,88],"02b93d4c-8e45-4618-935a-44aaa786ed62",["D",1783289313194],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","PR monitoring API backend","imageUrl","userId","019cdef0-5cd0-7261-bddf-67d3a4efcd7a","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","orgId","019cdef0-5cd0-7261-bddf-6971337b2068","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_11":12,"_149":145},"org",{"_11":12,"_143":-5,"_144":145,"_106":107,"_146":147,"_148":54},"branch",{"_17":60,"_13":16,"_35":42,"_135":96,"_136":-5,"_137":-5,"_138":-5,"_139":-5,"_140":-5,"_141":14,"_142":12},"isOwned","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBb2xGb1hUcU8ydWYzR0FNVzhFRDBaQk5SWCJ9","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_35":96,"_13":14,"_47":196,"_98":-5,"_99":54,"_100":101,"_102":101,"_103":104,"_105":-5,"_106":107,"_108":-5,"_109":54,"_110":-5,"_111":-5,"_112":49,"_113":114,"_115":-5,"_116":-5,"_117":-5,"_118":-5,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_128":197,"_130":198,"_132":199,"_134":54},[184,186,187],"files",[184,185,186,187],"counts",{"_174":49,"_175":69,"_176":49,"_31":177,"_178":179},[168,169,170],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":70,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_18":173},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":61,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_18":172},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":48,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_18":171},[37],[58],[67],"pulls","branches","forks",{"_180":182,"_183":49},"connections",{"_180":49,"_181":49},"count","unfilledCount",3,"emptyCount",{"_35":66,"_13":67,"_38":68,"_40":-5,"_41":42,"_43":69,"_45":46,"_47":83,"_17":49,"_50":71,"_51":-5,"_53":54,"_55":-5,"_77":194,"_18":195},{"_35":87,"_13":88,"_38":89,"_40":68,"_41":42,"_43":90,"_45":46,"_47":91,"_17":92,"_50":63,"_51":93,"_53":54,"_55":-5,"_77":192,"_18":193},{"_35":57,"_13":58,"_38":59,"_40":-5,"_41":42,"_43":60,"_45":46,"_47":80,"_17":62,"_50":63,"_51":64,"_53":54,"_55":-5,"_77":190,"_18":191},{"_35":36,"_13":37,"_38":39,"_40":-5,"_41":42,"_43":44,"_45":46,"_47":76,"_17":49,"_50":24,"_51":52,"_53":54,"_55":-5,"_77":188,"_18":189},[],[37],[],[58],[],[67,88],[185],[67],["D",1783289313194],{"_11":12,"_149":145},{"_11":12,"_143":-5,"_144":145,"_106":107,"_146":147,"_148":54},{"_17":60,"_13":16,"_35":42,"_135":96,"_136":-5,"_137":-5,"_138":-5,"_139":-5,"_140":-5,"_141":14,"_142":12},"ctx",{"_128":-5,"_165":233,"_234":235,"_236":237,"_238":54,"_239":54,"_240":54,"_241":46,"_242":-5,"_243":-7,"_244":-7,"_245":49,"_246":54,"_247":54,"_248":-5,"_249":54,"_250":54,"_251":54,"_252":-5},"ENV",{"_204":205,"_206":207,"_208":16,"_209":210,"_211":212,"_213":214,"_215":216,"_217":218,"_219":220,"_221":222,"_223":224,"_225":226,"_227":228,"_229":230,"_231":232},"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>

Code