orchard

  1package main
  2
  3// Every aggregation here logs its database error and returns a zero value, so
  4// one failing panel leaves a blank panel rather than a 500 page. Time arithmetic
  5// is unix milliseconds throughout, matching events.created_at.
  6
  7import (
  8	"context"
  9	"database/sql"
 10	"fmt"
 11	"log/slog"
 12	"math"
 13	"sort"
 14	"strconv"
 15	"strings"
 16	"time"
 17
 18	"github.com/google/uuid"
 19)
 20
 21// The events the collector emits on its own; everything else is a custom event.
 22var builtInEvents = []string{"session_start", "page_view", "page_leave", "click", "scroll"}
 23
 24// Page-leave timings outside this band are a double fire or a tab left open.
 25const (
 26	timeOnPageMinS = 1.0
 27	timeOnPageMaxS = 30.0 * 60.0
 28)
 29
 30// LabelCount is one row of any "top N by something" breakdown.
 31type LabelCount struct {
 32	Label string `json:"label"`
 33	Count int64  `json:"count"`
 34}
 35
 36// GraphPoint is one bucket of the time series. Label is preformatted because
 37// only the query knows which bucket width it produced.
 38type GraphPoint struct {
 39	Label string `json:"label"`
 40	Count int64  `json:"count"`
 41}
 42
 43// EventCard is one metric tile. Value is a string because two tiles carry their
 44// units in it.
 45type EventCard struct {
 46	Name          string `json:"name"`
 47	Value         string `json:"value"`
 48	PercentChange int64  `json:"percent_change"`
 49	HelpText      string `json:"help_text,omitempty"`
 50}
 51
 52// CustomEventDescriptor is one row of the "which custom events exist, and
 53// which are pinned" list behind the dashboard's card picker.
 54type CustomEventDescriptor struct {
 55	Event  string `json:"event"`
 56	Active bool   `json:"active"`
 57}
 58
 59// BotTraffic is the bot panel, read from the separate bot_events table.
 60type BotTraffic struct {
 61	Total    int64        `json:"total"`
 62	TopBots  []LabelCount `json:"top_bots"`
 63	TopPages []LabelCount `json:"top_pages"`
 64}
 65
 66// EventCounts is the five headline counts, gathered in one pass.
 67type EventCounts struct {
 68	SessionStart int64
 69	PageView     int64
 70	Click        int64
 71	Scroll       int64
 72	Total        int64
 73}
 74
 75// pctChange is the period-over-period delta on each card. A zero previous
 76// returns zero, not infinity, which would put "+100%" on every first-week card.
 77func pctChange(current, previous float64) int64 {
 78	if previous == 0 {
 79		return 0
 80	}
 81	return int64(math.Round((current - previous) / previous * 100))
 82}
 83
 84// filterClause returns SQL to append and the argument to bind with it, so a
 85// visitor-supplied URL is never interpolated.
 86func filterClause(filterURL string) (string, []any) {
 87	if filterURL == "" {
 88		return "", nil
 89	}
 90	return " AND url = ?", []any{filterURL}
 91}
 92
 93// placeholders renders "?,?,?" for an IN clause of n items.
 94func placeholders(n int) string {
 95	return strings.TrimSuffix(strings.Repeat("?,", n), ",")
 96}
 97
 98func logQuery(what string, err error) {
 99	if err != nil && err != sql.ErrNoRows {
100		slog.Info(fmt.Sprintf("query %s: %v", what, err))
101	}
102}
103
104// totalLiveUsers counts distinct visitors seen in the last thirty minutes. It
105// ignores the dashboard's date range, since "live" means now.
106func totalLiveUsers(ctx context.Context, db *sql.DB, propertyID uuid.UUID) int64 {
107	cutoff := time.Now().Add(-30 * time.Minute).UnixMilli()
108	var n int64
109	err := db.QueryRowContext(ctx,
110		`SELECT COUNT(DISTINCT user_id) FROM events
111		 WHERE property_id = ? AND created_at >= ? AND user_id IS NOT NULL`,
112		propertyID[:], cutoff).Scan(&n)
113	logQuery("total_live_users", err)
114	return n
115}
116
117func eventCounts(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string) EventCounts {
118	extraSQL, extraArgs := filterClause(filterURL)
119	query := `SELECT
120	    SUM(CASE WHEN event = 'session_start' THEN 1 ELSE 0 END),
121	    SUM(CASE WHEN event = 'page_view'     THEN 1 ELSE 0 END),
122	    SUM(CASE WHEN event = 'click'         THEN 1 ELSE 0 END),
123	    SUM(CASE WHEN event = 'scroll'        THEN 1 ELSE 0 END),
124	    COUNT(*)
125	  FROM events
126	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?` + extraSQL
127
128	args := append([]any{propertyID[:], startMS, endMS}, extraArgs...)
129
130	// SUM over no rows is NULL, not 0, so these scan through nullables.
131	var ss, pv, cl, sc sql.NullInt64
132	var total int64
133	err := db.QueryRowContext(ctx, query, args...).Scan(&ss, &pv, &cl, &sc, &total)
134	logQuery("event_counts", err)
135	return EventCounts{
136		SessionStart: ss.Int64,
137		PageView:     pv.Int64,
138		Click:        cl.Int64,
139		Scroll:       sc.Int64,
140		Total:        total,
141	}
142}
143
144// engagedUsers is the share of visitors with more than ten events, as a
145// percentage of session starts.
146func engagedUsers(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string, sessionStarts int64) float64 {
147	if sessionStarts == 0 {
148		return 0
149	}
150	extraSQL, extraArgs := filterClause(filterURL)
151	query := `SELECT COUNT(*) FROM (
152	    SELECT user_id FROM events
153	    WHERE property_id = ? AND created_at >= ? AND created_at <= ?
154	          AND user_id IS NOT NULL` + extraSQL + `
155	    GROUP BY user_id HAVING COUNT(*) >= 10
156	  )`
157	args := append([]any{propertyID[:], startMS, endMS}, extraArgs...)
158
159	var engaged int64
160	err := db.QueryRowContext(ctx, query, args...).Scan(&engaged)
161	logQuery("engaged_users", err)
162	return math.Round(float64(engaged)/float64(sessionStarts)*100*100) / 100
163}
164
165func avgTimeOnPage(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string) float64 {
166	extraSQL, extraArgs := filterClause(filterURL)
167	query := `SELECT AVG(time_on_page_ms / 1000.0) FROM events
168	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
169	        AND event = 'page_leave'
170	        AND time_on_page_ms IS NOT NULL
171	        AND time_on_page_ms / 1000.0 BETWEEN ? AND ?` + extraSQL
172	args := append([]any{propertyID[:], startMS, endMS, timeOnPageMinS, timeOnPageMaxS}, extraArgs...)
173
174	var avg sql.NullFloat64
175	err := db.QueryRowContext(ctx, query, args...).Scan(&avg)
176	logQuery("avg_time_on_page", err)
177	return math.Round(avg.Float64*100) / 100
178}
179
180// standardEventCards builds the seven tiles every property gets, each with its
181// change against the immediately preceding period of the same length.
182func standardEventCards(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS, prevStartMS, prevEndMS int64, filterURL string) []EventCard {
183	cur := eventCounts(ctx, db, propertyID, startMS, endMS, filterURL)
184	prev := eventCounts(ctx, db, propertyID, prevStartMS, prevEndMS, filterURL)
185
186	cards := []EventCard{
187		{
188			Name:          "Total session starts",
189			Value:         fmt.Sprintf("%d", cur.SessionStart),
190			PercentChange: pctChange(float64(cur.SessionStart), float64(prev.SessionStart)),
191			HelpText:      "Unique users visiting your site for your selected date range.",
192		},
193		{
194			Name:          "Total page views",
195			Value:         fmt.Sprintf("%d", cur.PageView),
196			PercentChange: pctChange(float64(cur.PageView), float64(prev.PageView)),
197			HelpText:      "Total pages viewed for your selected date range.",
198		},
199		{
200			Name:          "Total clicks",
201			Value:         fmt.Sprintf("%d", cur.Click),
202			PercentChange: pctChange(float64(cur.Click), float64(prev.Click)),
203			HelpText:      "Total clicks users made on all your pages for your selected date range.",
204		},
205		{
206			Name:          "Total scrolls",
207			Value:         fmt.Sprintf("%d", cur.Scroll),
208			PercentChange: pctChange(float64(cur.Scroll), float64(prev.Scroll)),
209			HelpText:      "Total scrolls users made on all your pages for your selected date range.",
210		},
211		{
212			Name:          "Total events",
213			Value:         fmt.Sprintf("%d", cur.Total),
214			PercentChange: pctChange(float64(cur.Total), float64(prev.Total)),
215			HelpText:      "All events for your selected date range, including custom events.",
216		},
217	}
218
219	engCur := engagedUsers(ctx, db, propertyID, startMS, endMS, filterURL, cur.SessionStart)
220	engPrev := engagedUsers(ctx, db, propertyID, prevStartMS, prevEndMS, filterURL, prev.SessionStart)
221	cards = append(cards, EventCard{
222		Name:          "Total user engagement",
223		Value:         trimFloat(engCur) + "%",
224		PercentChange: pctChange(engCur, engPrev),
225		HelpText:      "An engaged user is a user with more than 10 events collected for your selected date range.",
226	})
227
228	tCur := avgTimeOnPage(ctx, db, propertyID, startMS, endMS, filterURL)
229	tPrev := avgTimeOnPage(ctx, db, propertyID, prevStartMS, prevEndMS, filterURL)
230	cards = append(cards, EventCard{
231		Name:          "Avg. time on page",
232		Value:         trimFloat(tCur) + "s",
233		PercentChange: pctChange(tCur, tPrev),
234		HelpText:      "Average time a user spends on each page. Sessions over 30 minutes are excluded as idle.",
235	})
236
237	return cards
238}
239
240// trimFloat uses 'f' with precision -1 rather than %v, which is %g underneath
241// and flips to scientific notation at the top of the range.
242func trimFloat(v float64) string {
243	return strconv.FormatFloat(v, 'f', -1, 64)
244}
245
246// customEventCards returns the pinned custom-event tiles and, separately,
247// every custom event this property has ever recorded so the picker can list
248// them.
249func customEventCards(ctx context.Context, db *sql.DB, propertyID uuid.UUID, cards []CustomCard, startMS, endMS, prevStartMS, prevEndMS int64, filterURL string) ([]EventCard, []CustomEventDescriptor) {
250	// Unbounded by the date range, so the picker still lists a stale event.
251	query := `SELECT DISTINCT event FROM events
252	  WHERE property_id = ? AND event NOT IN (` + placeholders(len(builtInEvents)) + `)
253	  ORDER BY event`
254	args := []any{propertyID[:]}
255	for _, b := range builtInEvents {
256		args = append(args, b)
257	}
258
259	var names []string
260	rows, err := db.QueryContext(ctx, query, args...)
261	logQuery("custom_event_names", err)
262	if err == nil {
263		for rows.Next() {
264			var n string
265			if rows.Scan(&n) == nil {
266				names = append(names, n)
267			}
268		}
269		rows.Close()
270	}
271
272	active := make(map[string]bool, len(cards))
273	for _, c := range cards {
274		if c.Value {
275			active[c.Event] = true
276		}
277	}
278
279	descriptors := make([]CustomEventDescriptor, 0, len(names))
280	for _, n := range names {
281		descriptors = append(descriptors, CustomEventDescriptor{Event: n, Active: active[n]})
282	}
283
284	if len(active) == 0 {
285		return nil, descriptors
286	}
287
288	// Iterate names, not the map, so the tile order is the picker's rather
289	// than Go's randomised one.
290	activeNames := make([]string, 0, len(active))
291	for _, n := range names {
292		if active[n] {
293			activeNames = append(activeNames, n)
294		}
295	}
296	if len(activeNames) == 0 {
297		return nil, descriptors
298	}
299
300	countFor := func(periodStart, periodEnd int64) map[string]int64 {
301		extraSQL, extraArgs := filterClause(filterURL)
302		q := `SELECT event, COUNT(*) FROM events
303		  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
304		        AND event IN (` + placeholders(len(activeNames)) + `)` + extraSQL + `
305		  GROUP BY event`
306		a := []any{propertyID[:], periodStart, periodEnd}
307		for _, n := range activeNames {
308			a = append(a, n)
309		}
310		a = append(a, extraArgs...)
311
312		out := make(map[string]int64, len(activeNames))
313		r, err := db.QueryContext(ctx, q, a...)
314		logQuery("custom_event_counts", err)
315		if err != nil {
316			return out
317		}
318		defer r.Close()
319		for r.Next() {
320			var name string
321			var c int64
322			if r.Scan(&name, &c) == nil {
323				out[name] = c
324			}
325		}
326		return out
327	}
328
329	curMap := countFor(startMS, endMS)
330	prevMap := countFor(prevStartMS, prevEndMS)
331
332	out := make([]EventCard, 0, len(activeNames))
333	for _, name := range activeNames {
334		v := curMap[name]
335		p := prevMap[name]
336		out = append(out, EventCard{
337			Name:          name,
338			Value:         fmt.Sprintf("%d", v),
339			PercentChange: pctChange(float64(v), float64(p)),
340		})
341	}
342	return out, descriptors
343}
344
345// eventsGraph is the time series, bucketed by day, week or month and stepping
346// back from endDate. Stepping back from today instead would chart a historical
347// range as a row of zeros beside real metric cards.
348func eventsGraph(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string, endDate time.Time, rangeDays int64) []GraphPoint {
349	extraSQL, extraArgs := filterClause(filterURL)
350	query := `SELECT date(created_at / 1000, 'unixepoch') AS day, COUNT(*)
351	  FROM events
352	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?` + extraSQL + `
353	  GROUP BY day`
354	args := append([]any{propertyID[:], startMS, endMS}, extraArgs...)
355
356	byDay := map[string]int64{}
357	rows, err := db.QueryContext(ctx, query, args...)
358	logQuery("events_graph", err)
359	if err == nil {
360		for rows.Next() {
361			var day string
362			var c int64
363			if rows.Scan(&day, &c) == nil {
364				byDay[day] = c
365			}
366		}
367		rows.Close()
368	}
369
370	key := func(t time.Time) string { return t.Format("2006-01-02") }
371	bucketSum := func(start time.Time, days int) int64 {
372		var sum int64
373		for j := 0; j < days; j++ {
374			sum += byDay[key(start.AddDate(0, 0, j))]
375		}
376		return sum
377	}
378
379	type point struct {
380		date  time.Time
381		count int64
382	}
383	var points []point
384
385	switch {
386	case rangeDays <= 28:
387		for i := int64(0); i < rangeDays; i++ {
388			d := endDate.AddDate(0, 0, -int(i))
389			points = append(points, point{d, byDay[key(d)]})
390		}
391	case rangeDays <= 6*28:
392		weeks := rangeDays / 7
393		for w := int64(0); w < weeks; w++ {
394			d := endDate.AddDate(0, 0, -int(7*w))
395			points = append(points, point{d, bucketSum(d, 7)})
396		}
397	default:
398		months := rangeDays / 28
399		for m := int64(0); m < months; m++ {
400			d := endDate.AddDate(0, 0, -int(28*m))
401			points = append(points, point{d, bucketSum(d, 28)})
402		}
403	}
404
405	sort.Slice(points, func(i, j int) bool { return points[i].date.Before(points[j].date) })
406
407	out := make([]GraphPoint, 0, len(points))
408	for _, p := range points {
409		out = append(out, GraphPoint{Label: formatGraphLabel(p.date), Count: p.count})
410	}
411	return out
412}
413
414// formatGraphLabel renders "Jan 5"; Go has no unpadded day verb, so the padded
415// form is trimmed.
416func formatGraphLabel(t time.Time) string {
417	return t.Format("Jan") + " " + strings.TrimPrefix(t.Format("02"), "0")
418}
419
420// topByColumn groups by one column and takes the top N. column and countExpr are
421// interpolated into the SQL, which is safe only while every caller passes a
422// literal; never pass one from a request.
423func topByColumn(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL, column, event string, limit int64, distinctUsers bool) []LabelCount {
424	countExpr := "COUNT(*)"
425	if distinctUsers {
426		countExpr = "COUNT(DISTINCT user_id)"
427	}
428
429	var sb strings.Builder
430	fmt.Fprintf(&sb, `SELECT %s, %s FROM events
431	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
432	        AND %s IS NOT NULL AND %s != ''`, column, countExpr, column, column)
433
434	args := []any{propertyID[:], startMS, endMS}
435	if distinctUsers {
436		sb.WriteString(" AND user_id IS NOT NULL")
437	}
438	if event != "" {
439		sb.WriteString(" AND event = ?")
440		args = append(args, event)
441	}
442	extraSQL, extraArgs := filterClause(filterURL)
443	sb.WriteString(extraSQL)
444	args = append(args, extraArgs...)
445	fmt.Fprintf(&sb, " GROUP BY %s ORDER BY %s DESC LIMIT ?", column, countExpr)
446	args = append(args, limit)
447
448	return scanLabelCounts(ctx, db, "top_by_"+column, sb.String(), args...)
449}
450
451func scanLabelCounts(ctx context.Context, db *sql.DB, what, query string, args ...any) []LabelCount {
452	rows, err := db.QueryContext(ctx, query, args...)
453	logQuery(what, err)
454	if err != nil {
455		return nil
456	}
457	defer rows.Close()
458
459	var out []LabelCount
460	for rows.Next() {
461		var lc LabelCount
462		if rows.Scan(&lc.Label, &lc.Count) == nil {
463			out = append(out, lc)
464		}
465	}
466	return out
467}
468
469// eventsByScreenSize counts distinct visitors per screen size. It filters on
470// page_view, like the three breakdowns below it, because the collector's user-id
471// cookie suppresses session_start after a visitor's first visit.
472func eventsByScreenSize(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string, limit int64) []LabelCount {
473	extraSQL, extraArgs := filterClause(filterURL)
474	query := `SELECT screen_width, screen_height, COUNT(DISTINCT user_id) FROM events
475	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
476	        AND event = 'page_view'
477	        AND screen_width IS NOT NULL
478	        AND user_id IS NOT NULL` + extraSQL + `
479	  GROUP BY screen_width, screen_height
480	  ORDER BY COUNT(DISTINCT user_id) DESC LIMIT ?`
481	args := append([]any{propertyID[:], startMS, endMS}, extraArgs...)
482	args = append(args, limit)
483
484	rows, err := db.QueryContext(ctx, query, args...)
485	logQuery("events_by_screen_size", err)
486	if err != nil {
487		return nil
488	}
489	defer rows.Close()
490
491	var out []LabelCount
492	for rows.Next() {
493		var w, h sql.NullInt64
494		var c int64
495		if rows.Scan(&w, &h, &c) == nil {
496			out = append(out, LabelCount{
497				Label: fmt.Sprintf("%dx%d", w.Int64, h.Int64),
498				Count: c,
499			})
500		}
501	}
502	return out
503}
504
505func eventsByDevice(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f string, limit int64) []LabelCount {
506	return topByColumn(ctx, db, id, s, e, f, "device", "page_view", limit, true)
507}
508
509func eventsByBrowser(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f string, limit int64) []LabelCount {
510	return topByColumn(ctx, db, id, s, e, f, "browser", "page_view", limit, true)
511}
512
513func eventsByPlatform(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f string, limit int64) []LabelCount {
514	return topByColumn(ctx, db, id, s, e, f, "platform", "page_view", limit, true)
515}
516
517func eventsByPageURL(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f string, limit int64) []LabelCount {
518	return topByColumn(ctx, db, id, s, e, f, "url", "", limit, false)
519}
520
521func pageViewsByPageURL(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f string, limit int64) []LabelCount {
522	return topByColumn(ctx, db, id, s, e, f, "url", "page_view", limit, false)
523}
524
525func sessionStartsByReferrer(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f string, limit int64) []LabelCount {
526	return topByColumn(ctx, db, id, s, e, f, "referrer", "session_start", limit, false)
527}
528
529// pageViewsByUTM maps a campaign field to its column, which is what keeps a
530// request-supplied field out of topByColumn's interpolation.
531func pageViewsByUTM(ctx context.Context, db *sql.DB, id uuid.UUID, s, e int64, f, field string, limit int64) []LabelCount {
532	column, ok := map[string]string{
533		"source":   "utm_source",
534		"medium":   "utm_medium",
535		"campaign": "utm_campaign",
536		"term":     "utm_term",
537		"content":  "utm_content",
538	}[field]
539	if !ok {
540		return nil
541	}
542	return topByColumn(ctx, db, id, s, e, f, column, "page_view", limit, false)
543}
544
545func eventsByCustomEvent(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string, limit int64) []LabelCount {
546	extraSQL, extraArgs := filterClause(filterURL)
547	query := `SELECT event, COUNT(*) FROM events
548	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
549	        AND event NOT IN (` + placeholders(len(builtInEvents)) + `)` + extraSQL + `
550	  GROUP BY event ORDER BY COUNT(*) DESC LIMIT ?`
551	args := []any{propertyID[:], startMS, endMS}
552	for _, b := range builtInEvents {
553		args = append(args, b)
554	}
555	args = append(args, extraArgs...)
556	args = append(args, limit)
557
558	return scanLabelCounts(ctx, db, "events_by_custom_event", query, args...)
559}
560
561// sessionStartsByCountry feeds the world map, keyed by ISO country code.
562func sessionStartsByCountry(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string) map[string]int64 {
563	extraSQL, extraArgs := filterClause(filterURL)
564	query := `SELECT country, COUNT(*) FROM events
565	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
566	        AND event = 'session_start' AND country IS NOT NULL` + extraSQL + `
567	  GROUP BY country`
568	args := append([]any{propertyID[:], startMS, endMS}, extraArgs...)
569
570	out := map[string]int64{}
571	rows, err := db.QueryContext(ctx, query, args...)
572	logQuery("session_starts_by_country", err)
573	if err != nil {
574		return out
575	}
576	defer rows.Close()
577	for rows.Next() {
578		var country string
579		var c int64
580		if rows.Scan(&country, &c) == nil {
581			out[country] = c
582		}
583	}
584	return out
585}
586
587// sessionStartsByCountryRegion feeds the map's admin-1 drill-down.
588func sessionStartsByCountryRegion(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS int64, filterURL string) map[string]map[string]int64 {
589	extraSQL, extraArgs := filterClause(filterURL)
590	query := `SELECT country, region, COUNT(*) FROM events
591	  WHERE property_id = ? AND created_at >= ? AND created_at <= ?
592	        AND event = 'session_start'
593	        AND country IS NOT NULL AND region IS NOT NULL` + extraSQL + `
594	  GROUP BY country, region`
595	args := append([]any{propertyID[:], startMS, endMS}, extraArgs...)
596
597	out := map[string]map[string]int64{}
598	rows, err := db.QueryContext(ctx, query, args...)
599	logQuery("session_starts_by_country_region", err)
600	if err != nil {
601		return out
602	}
603	defer rows.Close()
604	for rows.Next() {
605		var country, region string
606		var c int64
607		if rows.Scan(&country, &region, &c) != nil {
608			continue
609		}
610		if out[country] == nil {
611			out[country] = map[string]int64{}
612		}
613		out[country][region] = c
614	}
615	return out
616}
617
618func botTraffic(ctx context.Context, db *sql.DB, propertyID uuid.UUID, startMS, endMS, limit int64) BotTraffic {
619	var total int64
620	err := db.QueryRowContext(ctx,
621		`SELECT COUNT(*) FROM bot_events
622		 WHERE property_id = ? AND created_at >= ? AND created_at <= ?`,
623		propertyID[:], startMS, endMS).Scan(&total)
624	logQuery("bot_traffic_total", err)
625	if total == 0 {
626		return BotTraffic{}
627	}
628
629	return BotTraffic{
630		Total: total,
631		TopBots: scanLabelCounts(ctx, db, "bot_traffic_bots",
632			`SELECT bot_name, COUNT(*) FROM bot_events
633			 WHERE property_id = ? AND created_at >= ? AND created_at <= ?
634			       AND bot_name IS NOT NULL AND bot_name != ''
635			 GROUP BY bot_name ORDER BY COUNT(*) DESC LIMIT ?`,
636			propertyID[:], startMS, endMS, limit),
637		TopPages: scanLabelCounts(ctx, db, "bot_traffic_pages",
638			`SELECT url, COUNT(*) FROM bot_events
639			 WHERE property_id = ? AND created_at >= ? AND created_at <= ?
640			       AND url IS NOT NULL AND url != ''
641			 GROUP BY url ORDER BY COUNT(*) DESC LIMIT ?`,
642			propertyID[:], startMS, endMS, limit),
643	}
644}
645
646// parseDateToMS turns a "YYYY-MM-DD" query parameter into a unix-ms bound. The
647// boundary resolves in local time, since the operator means their own days.
648func parseDateToMS(date string, endOfDay bool) (int64, bool) {
649	d, err := time.ParseInLocation("2006-01-02", date, time.Local)
650	if err != nil {
651		return 0, false
652	}
653	if endOfDay {
654		d = d.Add(23*time.Hour + 59*time.Minute + 59*time.Second)
655	}
656	return d.UnixMilli(), true
657}