[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_110":111,"_112":113},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","dominik1","name","co-w-zamosciu","path","backend/db.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":-5,"_58":59,"_60":61,"_62":34,"_63":-5,"_64":-5,"_65":66,"_67":68,"_69":70,"_71":72,"_73":74,"_75":-5,"_76":77,"_78":77,"_79":80,"_81":80,"_82":80,"_83":77,"_84":77,"_85":86,"_87":88,"_89":90,"_91":44},"file",{"_22":23,"_11":24,"_25":26,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_45":-5,"_13":46},"permalink","https://www.val.town/x/dominik1/co-w-zamosciu/code/backend/db.ts","extension","id","510fe941-41a2-41b1-8c21-4542ae196eba","db.ts","fileId","196d739e-afbf-11f1-bca7-1607ee4eb77e","parentId","bee349a4-71cb-4813-87f2-05f9d2043e4c","branchId","255405a6-055f-4761-b8d5-94d1ffe681e3","branchVersion",117,"latest",true,"createdAt",["D",1789372482721],"version",10,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { type EventItem, isCategory, type ScrapedEvent, type SourceStatus } from \"../shared/types.ts\";\nimport { addDays, dedupKey, similarTitles, warsawToday } from \"./normalize.ts\";\n\nconst EVENTS = \"cwz_events\";\nconst SOURCES = \"cwz_sources\";\nconst PUSH = \"cwz_push\";\nconst LLM_CACHE = \"cwz_llm_cache\";\nconst LOGIN_FAILS = \"cwz_login_fails\";\nconst META = \"cwz_meta\";\n\nconst DAY_MS = 24 * 60 * 60 * 1000;\n\nexport async function initDb() {\n await sqlite.batch([\n {\n sql: `CREATE TABLE IF NOT EXISTS ${EVENTS} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n dedup_key TEXT NOT NULL UNIQUE,\n title TEXT NOT NULL,\n date TEXT NOT NULL,\n end_date TEXT,\n time TEXT,\n place TEXT,\n town TEXT NOT NULL,\n category TEXT NOT NULL,\n price TEXT,\n image_url TEXT,\n url TEXT,\n description TEXT,\n showtimes TEXT,\n sources TEXT NOT NULL DEFAULT '[]',\n priority INTEGER NOT NULL DEFAULT 0,\n manual INTEGER NOT NULL DEFAULT 0,\n hidden INTEGER NOT NULL DEFAULT 0,\n first_seen INTEGER NOT NULL,\n last_seen INTEGER NOT NULL\n )`,\n args: [],\n },\n { sql: `CREATE INDEX IF NOT EXISTS cwz_events_date ON ${EVENTS} (date)`, args: [] },\n {\n sql: `CREATE TABLE IF NOT EXISTS ${SOURCES} (\n id TEXT PRIMARY KEY,\n name TEXT NOT NULL,\n last_run INTEGER,\n last_ok INTEGER,\n last_error TEXT,\n last_count INTEGER NOT NULL DEFAULT 0,\n prev_count INTEGER NOT NULL DEFAULT 0\n )`,\n args: [],\n },\n {\n sql: `CREATE TABLE IF NOT EXISTS ${PUSH} (\n endpoint TEXT PRIMARY KEY,\n p256dh TEXT NOT NULL,\n auth TEXT NOT NULL,\n favorites TEXT NOT NULL DEFAULT '[]',\n created_at INTEGER NOT NULL\n )`,\n args: [],\n },\n {\n sql: `CREATE TABLE IF NOT EXISTS ${LLM_CACHE} (hash TEXT PRIMARY KEY, result TEXT NOT NULL, created_at INTEGER NOT NULL)`,\n args: [],\n },\n { sql: `CREATE TABLE IF NOT EXISTS ${LOGIN_FAILS} (ip TEXT NOT NULL, at INTEGER NOT NULL)`, args: [] },\n { sql: `CREATE TABLE IF NOT EXISTS ${META} (key TEXT PRIMARY KEY, value TEXT)`, args: [] },\n ]);\n}\n\n// ---------- events ----------\n\nconst COLUMNS =\n \"id, title, date, end_date, time, place, town, category, price, image_url, url, description, showtimes, sources, manual\";\n\nfunction parseJson(value: unknown, fallback: T): T {\n try {\n return value == null ? fallback : JSON.parse(String(value));\n } catch {\n return fallback;\n }\n}\n\nfunction toEvent(r: Record): EventItem {\n const str = (v: unknown) => (v == null ? null : String(v));\n return {\n id: Number(r.id),\n title: String(r.title),\n date: String(r.date),\n endDate: str(r.end_date),\n time: str(r.time),\n place: str(r.place),\n town: String(r.town),\n category: isCategory(r.category) ? r.category : \"inne\",\n price: str(r.price),\n imageUrl: str(r.image_url),\n url: str(r.url),\n description: str(r.description),\n sources: parseJson(r.sources, []),\n manual: Number(r.manual) === 1,\n showtimes: parseJson(r.showtimes, null),\n };\n}\n\n// Events happening on any day in [from, to], including multi-day ones that started earlier\nexport async function listEvents(from: string, to: string): Promise {\n const { rows } = await sqlite.execute({\n sql: `SELECT ${COLUMNS} FROM ${EVENTS}\n WHERE hidden = 0 AND date <= ? AND COALESCE(end_date, date) >= ?\n ORDER BY date, COALESCE(time, '00:00'), title`,\n args: [to, from],\n });\n return rows.map(toEvent);\n}\n\nexport async function getEvent(id: number): Promise {\n const { rows } = await sqlite.execute({ sql: `SELECT ${COLUMNS} FROM ${EVENTS} WHERE id = ?`, args: [id] });\n return rows[0] ? toEvent(rows[0]) : null;\n}\n\nexport async function getEventsByIds(ids: number[]): Promise {\n if (ids.length === 0) return [];\n const { rows } = await sqlite.execute({\n sql: `SELECT ${COLUMNS} FROM ${EVENTS} WHERE hidden = 0 AND id IN (${ids.map(() => \"?\").join(\", \")})`,\n args: ids,\n });\n return rows.map(toEvent);\n}\n\ntype FieldRow = Record;\n\nconst FIELDS: [keyof ScrapedEvent, string][] = [\n [\"title\", \"title\"],\n [\"endDate\", \"end_date\"],\n [\"time\", \"time\"],\n [\"place\", \"place\"],\n [\"town\", \"town\"],\n [\"category\", \"category\"],\n [\"price\", \"price\"],\n [\"imageUrl\", \"image_url\"],\n [\"url\", \"url\"],\n [\"description\", \"description\"],\n [\"showtimes\", \"showtimes\"],\n];\n\nconst dbValue = (v: unknown) => (v == null ? null : typeof v === \"object\" ? JSON.stringify(v) : v);\n\n/**\n * Stores events from one source. The same event seen on several sites is merged:\n * a source with higher priority overwrites fields, a lower one only fills gaps.\n */\nexport async function upsertEvents(sourceId: string, priority: number, events: ScrapedEvent[], manual = false) {\n const unique = new Map();\n for (const e of events) {\n const key = dedupKey(e.title, e.date);\n // the same film plays in several cinemas on the same day; only listings with showtimes are kept per cinema\n if (e.category === \"kino\" && e.showtimes?.length) {\n unique.set(`${key}@${(e.place ?? \"\").toLowerCase()}`, e);\n continue;\n }\n // a site sometimes lists one event twice under slightly different titles\n const twin = [...unique.values()].some((u) =>\n u.category !== \"kino\" && u.date === e.date && u.town === e.town &&\n (u.time == null || e.time == null || u.time === e.time) &&\n similarTitles(u.title, e.title)\n );\n if (!twin || unique.has(key)) unique.set(key, e);\n }\n if (unique.size === 0) return 0;\n\n const keys = [...unique.keys()];\n const existing = new Map();\n for (let i = 0; i < keys.length; i += 200) {\n const chunk = keys.slice(i, i + 200);\n const { rows } = await sqlite.execute({\n sql: `SELECT * FROM ${EVENTS} WHERE dedup_key IN (${chunk.map(() => \"?\").join(\", \")})`,\n args: chunk,\n });\n for (const r of rows) existing.set(String(r.dedup_key), r);\n }\n\n // The same event worded differently on another site: same day, compatible time, mostly the same words\n const missing = [...unique].filter(([key]) => !existing.has(key));\n if (missing.length) {\n const dates = [...new Set(missing.map(([, e]) => e.date))];\n const { rows } = await sqlite.execute({\n sql: `SELECT * FROM ${EVENTS} WHERE date IN (${dates.map(() => \"?\").join(\", \")})`,\n args: dates,\n });\n for (const [key, e] of missing) {\n const match = rows.find((r) =>\n r.date === e.date && r.town === e.town &&\n // two cinema listings never merge (different screenings); a film announcement without showtimes may\n !(r.showtimes != null && e.showtimes?.length) &&\n (r.time == null || e.time == null || r.time === e.time) &&\n similarTitles(String(r.title), e.title)\n );\n if (match) existing.set(key, match);\n }\n }\n\n const now = Date.now();\n const statements = [];\n for (const [key, e] of unique) {\n const row = existing.get(key);\n if (!row) {\n statements.push({\n sql: `INSERT INTO ${EVENTS} (dedup_key, date, sources, priority, manual, first_seen, last_seen,\n ${FIELDS.map(([, c]) => c).join(\", \")})\n VALUES (?, ?, ?, ?, ?, ?, ?, ${FIELDS.map(() => \"?\").join(\", \")})`,\n args: [key, e.date, JSON.stringify([sourceId]), priority, manual ? 1 : 0, now, now,\n ...FIELDS.map(([f]) => dbValue(e[f]))],\n });\n continue;\n }\n const wins = priority >= Number(row.priority);\n const sets: string[] = [];\n const args: unknown[] = [];\n for (const [field, column] of FIELDS) {\n const value = dbValue(e[field]);\n if (value == null) continue;\n // a category found by the classifier is better than a site's \"other\"\n if (column === \"category\" && value === \"inne\" && row.category != null) continue;\n if (wins || row[column] == null) {\n sets.push(`${column} = ?`);\n args.push(value);\n }\n }\n const sources = parseJson(row.sources, []);\n if (!sources.includes(sourceId)) sources.push(sourceId);\n sets.push(\"sources = ?\", \"last_seen = ?\", \"priority = ?\");\n args.push(JSON.stringify(sources), now, Math.max(priority, Number(row.priority)));\n statements.push({ sql: `UPDATE ${EVENTS} SET ${sets.join(\", \")} WHERE id = ?`, args: [...args, row.id] });\n }\n\n for (let i = 0; i < statements.length; i += 50) {\n await sqlite.batch(statements.slice(i, i + 50) as { sql: string; args: never[] }[]);\n }\n return unique.size;\n}\n\n// Upcoming events still in \"other\", for the LLM classifier\nexport async function listUncategorized(limit: number) {\n const { rows } = await sqlite.execute({\n sql: `SELECT id, title, description FROM ${EVENTS}\n WHERE category = 'inne' AND hidden = 0 AND date >= ? ORDER BY date LIMIT ?`,\n args: [warsawToday(), limit],\n });\n return rows.map((r) => ({\n id: Number(r.id),\n title: String(r.title),\n description: r.description == null ? null : String(r.description),\n }));\n}\n\nexport async function setCategories(updates: { id: number; category: string }[]) {\n if (updates.length === 0) return;\n await sqlite.batch(updates.map((u) => ({ sql: `UPDATE ${EVENTS} SET category = ? WHERE id = ?`, args: [u.category, u.id] })));\n}\n\nexport async function hideEvent(id: number) {\n await sqlite.execute({ sql: `UPDATE ${EVENTS} SET hidden = 1 WHERE id = ?`, args: [id] });\n}\n\n// Past events are of no use; multi-day ones stay until their last day has passed\nexport async function pruneOldEvents() {\n const cutoff = addDays(warsawToday(), -2);\n await sqlite.execute({ sql: `DELETE FROM ${EVENTS} WHERE COALESCE(end_date, date) < ?`, args: [cutoff] });\n}\n\n// Seen by the source before, but gone from it for a while: cancelled or moved\nexport async function dropVanished(sourceId: string, runStartedAt: number) {\n const today = warsawToday();\n await sqlite.execute({\n sql: `DELETE FROM ${EVENTS}\n WHERE manual = 0 AND sources = ? AND last_seen < ? AND date >= ?`,\n args: [JSON.stringify([sourceId]), runStartedAt - 2 * DAY_MS, today],\n });\n}\n\n// URLs already known for a source, so detail pages are fetched only once\nexport async function knownUrls(prefix: string): Promise> {\n const { rows } = await sqlite.execute({\n sql: `SELECT url FROM ${EVENTS} WHERE url LIKE ? AND time IS NOT NULL`,\n args: [`${prefix}%`],\n });\n return new Set(rows.map((r) => String(r.url)));\n}\n\n// ---------- sources ----------\n\nexport async function recordSourceRun(id: string, name: string, result: { count: number } | { error: string }) {\n const now = Date.now();\n if (\"error\" in result) {\n await sqlite.execute({\n sql: `INSERT INTO ${SOURCES} (id, name, last_run, last_error) VALUES (?, ?, ?, ?)\n ON CONFLICT(id) DO UPDATE SET name = excluded.name, last_run = excluded.last_run, last_error = excluded.last_error`,\n args: [id, name, now, result.error.slice(0, 500)],\n });\n } else {\n await sqlite.execute({\n sql: `INSERT INTO ${SOURCES} (id, name, last_run, last_ok, last_error, last_count, prev_count)\n VALUES (?, ?, ?, ?, NULL, ?, 0)\n ON CONFLICT(id) DO UPDATE SET name = excluded.name, last_run = excluded.last_run, last_ok = excluded.last_ok,\n last_error = NULL, prev_count = ${SOURCES}.last_count, last_count = excluded.last_count`,\n args: [id, name, now, now, result.count],\n });\n }\n}\n\nexport async function listSources(): Promise {\n const { rows } = await sqlite.execute(`SELECT * FROM ${SOURCES} ORDER BY id`);\n return rows.map((r) => ({\n id: String(r.id),\n name: String(r.name),\n lastRun: r.last_run == null ? null : Number(r.last_run),\n lastOk: r.last_ok == null ? null : Number(r.last_ok),\n lastError: r.last_error == null ? null : String(r.last_error),\n lastCount: Number(r.last_count),\n prevCount: Number(r.prev_count),\n }));\n}\n\n// ---------- meta ----------\n\nexport async function setMeta(key: string, value: string) {\n await sqlite.execute({\n sql: `INSERT INTO ${META} (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value`,\n args: [key, value],\n });\n}\n\nexport async function getMeta(key: string): Promise {\n const { rows } = await sqlite.execute({ sql: `SELECT value FROM ${META} WHERE key = ?`, args: [key] });\n return rows[0]?.value == null ? null : String(rows[0].value);\n}\n\n// ---------- LLM cache ----------\n\nexport async function getCached(hash: string): Promise {\n const { rows } = await sqlite.execute({ sql: `SELECT result FROM ${LLM_CACHE} WHERE hash = ?`, args: [hash] });\n return rows[0] ? String(rows[0].result) : null;\n}\n\nexport async function setCached(hash: string, result: string) {\n const now = Date.now();\n await sqlite.batch([\n { sql: `DELETE FROM ${LLM_CACHE} WHERE created_at < ?`, args: [now - 30 * DAY_MS] },\n {\n sql: `INSERT INTO ${LLM_CACHE} (hash, result, created_at) VALUES (?, ?, ?)\n ON CONFLICT(hash) DO UPDATE SET result = excluded.result, created_at = excluded.created_at`,\n args: [hash, result, now],\n },\n ]);\n}\n\n// ---------- push subscriptions ----------\n\nexport interface PushRow {\n endpoint: string;\n p256dh: string;\n auth: string;\n favorites: number[];\n}\n\nexport async function saveSubscription(endpoint: string, p256dh: string, auth: string, favorites: number[]) {\n await sqlite.execute({\n sql: `INSERT INTO ${PUSH} (endpoint, p256dh, auth, favorites, created_at) VALUES (?, ?, ?, ?, ?)\n ON CONFLICT(endpoint) DO UPDATE SET p256dh = excluded.p256dh, auth = excluded.auth, favorites = excluded.favorites`,\n args: [endpoint, p256dh, auth, JSON.stringify(favorites), Date.now()],\n });\n}\n\nexport async function deleteSubscription(endpoint: string) {\n await sqlite.execute({ sql: `DELETE FROM ${PUSH} WHERE endpoint = ?`, args: [endpoint] });\n}\n\nexport async function allSubscriptions(): Promise {\n const { rows } = await sqlite.execute(`SELECT endpoint, p256dh, auth, favorites FROM ${PUSH}`);\n return rows.map((r) => ({\n endpoint: String(r.endpoint),\n p256dh: String(r.p256dh),\n auth: String(r.auth),\n favorites: parseJson(r.favorites, []),\n }));\n}\n\n// ---------- PIN attempts ----------\n\nexport async function countLoginFails(ip: string, since: number) {\n const { rows } = await sqlite.execute({\n sql: `SELECT COUNT(*) AS total, COALESCE(SUM(CASE WHEN ip = ? THEN 1 ELSE 0 END), 0) AS mine\n FROM ${LOGIN_FAILS} WHERE at >= ?`,\n args: [ip, since],\n });\n return { total: Number(rows[0]?.total ?? 0), mine: Number(rows[0]?.mine ?? 0) };\n}\n\nexport async function recordLoginFail(ip: string) {\n const now = Date.now();\n await sqlite.batch([\n { sql: `DELETE FROM ${LOGIN_FAILS} WHERE at < ?`, args: [now - DAY_MS] },\n { sql: `INSERT INTO ${LOGIN_FAILS} (ip, at) VALUES (?, ?)`, args: [ip, now] },\n ]);\n}\n","deleted",false,"label",[47,24],"backend","25f66c95-84fd-4431-a007-b1f4e010323a",["D",1789336184443],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Co dziś w Zamościu i na Roztoczu — koncerty, kino, wydarzenia","imageUrl","userId","5dd5cdfa-b36a-42e1-a911-81ba86e3b64c","pinnedFileId","01a09cbf-66da-7600-85d1-7a5d4b41e0de","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","01a09aab-d118-725d-b44f-c12d328bfdc6","tursoName","25f66c9584fd4431a007b1f4e010323a","tursoHostname","25f66c9584fd4431a007b1f4e010323a-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789519256886],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_109":105},"org",{"_9":10,"_102":103,"_104":105,"_58":59,"_106":107,"_108":44},"branch",{"_37":92,"_11":93,"_22":30,"_94":48,"_95":-5,"_96":-5,"_97":-5,"_98":-5,"_99":-5,"_100":12,"_101":10},"isOwned",150,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Dominik","avatar","","tier","free","canRestrictVals","profileImageUrl","ctx",{"_85":-5,"_143":144,"_145":146,"_147":148,"_149":44,"_150":44,"_151":44,"_152":34,"_153":-5,"_154":-7,"_155":-7,"_156":66,"_157":44,"_158":44,"_159":-5,"_160":44,"_161":44,"_162":44,"_163":-5},"ENV",{"_114":115,"_116":117,"_118":93,"_119":120,"_121":122,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138,"_139":140,"_141":142},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","811aa22498c9b0b5a47e8d778de579cdb990f332","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]