3 files changed, 253 insertions, 0 deletions
diff --git a/backend/storage/db.go b/backend/storage/db.go
new file mode 100644
index 0000000..0ee50da
--- /dev/null
+++ b/backend/storage/db.go
@@ -0,0 +1,74 @@
+package storage
+
+import (
+	"context"
+	"database/sql"
+	"embed"
+	"fmt"
+	"log/slog"
+	"regexp"
+	"slices"
+	"strconv"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+const DBKey = "db"
+
+//go:embed migrations
+var migrations embed.FS
+
+var nameReg = regexp.MustCompile(`(\d{3})_[a-zA-Z_-]+.sql`)
+
+func ConnectDatabase(file string) *sql.DB {
+	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?cache=shared", file))
+	if err != nil {
+		panic(err)
+	}
+	return db
+}
+
+func RunMigration(ctx context.Context, db *sql.DB) error {
+	entries, err := migrations.ReadDir("migrations")
+	if err != nil {
+		return err
+	}
+	type runMig struct {
+		val string
+		n   int
+	}
+	var toRun []runMig
+	for _, e := range entries {
+		rawId := nameReg.FindStringSubmatch(e.Name())
+		id, err := strconv.Atoi(rawId[1])
+		if err != nil {
+			return err
+		}
+		b, err := migrations.ReadFile("migrations/" + e.Name())
+		if err != nil {
+			return err
+		}
+		slog.Debug("loading migration", "n", id, "file", e.Name(), "content", string(b))
+		toRun = append(toRun, runMig{
+			val: string(b), n: id,
+		})
+	}
+	if len(toRun) == 0 {
+		return nil
+	}
+	slices.SortFunc(toRun, func(a, b runMig) int {
+		return a.n - b.n
+	})
+	for _, m := range toRun {
+		slog.Info("migrating", "n", m.n)
+		_, err := db.ExecContext(ctx, m.val)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+func getDB(ctx context.Context) *sql.DB {
+	return ctx.Value(DBKey).(*sql.DB)
+}
diff --git a/backend/storage/migrations/000_init.sql b/backend/storage/migrations/000_init.sql
new file mode 100644
index 0000000..6dd385c
--- /dev/null
+++ b/backend/storage/migrations/000_init.sql
@@ -0,0 +1,6 @@
+CREATE TABLE IF NOT EXISTS stats(
+    id INTEGER PRIMARY KEY,
+    visit INTEGER NOT NULL,
+    origin TEXT NOT NULL,
+    target TEXT NOT NULL
+);
diff --git a/backend/storage/stats.go b/backend/storage/stats.go
new file mode 100644
index 0000000..b30cb8c
--- /dev/null
+++ b/backend/storage/stats.go
@@ -0,0 +1,173 @@
+package storage
+
+import (
+	"context"
+	"fmt"
+	"log/slog"
+	"net/http"
+	"net/url"
+	"slices"
+	"strings"
+	"sync"
+	"time"
+)
+
+type loaded struct {
+	data map[string]struct{}
+	mu   *sync.RWMutex
+}
+
+func (l *loaded) Has(k string) bool {
+	l.mu.RLock()
+	defer l.mu.RUnlock()
+	_, ok := l.data[k]
+	return ok
+}
+
+func (l *loaded) Add(k string) {
+	l.mu.Lock()
+	defer l.mu.Unlock()
+	l.data[k] = struct{}{}
+}
+
+func (l *loaded) Remove(k string) {
+	l.mu.Lock()
+	defer l.mu.Unlock()
+	delete(l.data, k)
+}
+
+func newLoaded() *loaded {
+	return &loaded{
+		data: make(map[string]struct{}),
+		mu:   new(sync.RWMutex),
+	}
+}
+
+var load = newLoaded()
+
+func UpdateStats(ctx context.Context, r *http.Request, domain string) error {
+	target := r.URL.Path
+	if strings.HasPrefix(target, "/static") || strings.HasPrefix(target, "/admin") {
+		return nil
+	}
+	ref := r.Header.Get("Referer")
+	if ref == "" {
+		return nil
+	}
+	refUrl, err := url.Parse(ref)
+	if err != nil {
+		return nil
+	}
+	ref = refUrl.Host
+	if ref == domain || ref == fmt.Sprintf("localhost:%d", 8000) {
+		ref = refUrl.Path
+		if ref == target || strings.HasPrefix(ref, "/admin") || ref == "/favicon.ico" {
+			return nil
+		}
+	}
+	// using /assets/styles.css to detect if a page is loaded → majority of bots will not load this
+	if target == "/assets/styles.css" {
+		target = ref
+		ref = "?"
+	}
+	if load.Has(target) {
+		return nil
+	}
+	db := getDB(ctx)
+	rows, err := db.QueryContext(ctx, "SELECT id, visit FROM stats WHERE origin = ? AND target = ?", ref, target)
+	if err != nil {
+		return err
+	}
+	defer func() {
+		if err == nil {
+			slog.Debug("stats updated")
+			load.Add(target)
+			go func(target string) {
+				time.Sleep(5 * time.Second)
+				load.Remove(target)
+			}(target)
+		}
+	}()
+	if !rows.Next() {
+		_, err = db.ExecContext(ctx, "INSERT INTO stats (origin, target, visit) VALUES (?, ?, 1)", ref, target)
+		return err
+	}
+	var id uint
+	var nb uint
+	err = rows.Scan(&id, &nb)
+	if err != nil {
+		return err
+	}
+	err = rows.Close()
+	if err != nil {
+		return err
+	}
+	_, err = db.ExecContext(ctx, "UPDATE stats SET visit = ? WHERE id = ?", nb+1, id)
+	return err
+}
+
+type StatsRow struct {
+	Origin string
+	Target string
+	Visit  uint
+}
+
+const StatsPerPage = 25
+
+func GetStatsRows(ctx context.Context, page uint) ([]StatsRow, error) {
+	rows, err := getDB(ctx).QueryContext(
+		ctx,
+		"SELECT origin, target, visit FROM stats ORDER BY visit DESC LIMIT ? OFFSET ?",
+		StatsPerPage, (page-1)*StatsPerPage,
+	)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	statRows := make([]StatsRow, StatsPerPage)
+	var i uint8
+	for i = 0; rows.Next(); i++ {
+		var stat StatsRow
+		err = rows.Scan(&stat.Origin, &stat.Target, &stat.Visit)
+		if err != nil {
+			return nil, err
+		}
+		statRows[i] = stat
+	}
+	if i == 0 {
+		return nil, nil
+	}
+	return statRows[:i], nil
+}
+
+func GetUnionStatsRows(ctx context.Context) ([]StatsRow, error) {
+	rows, err := getDB(ctx).QueryContext(ctx, "SELECT target, visit FROM stats ORDER BY visit DESC")
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	data := make(map[string]uint)
+	for rows.Next() {
+		var stat StatsRow
+		err = rows.Scan(&stat.Target, &stat.Visit)
+		if err != nil {
+			return nil, err
+		}
+		if _, ok := data[stat.Target]; !ok {
+			data[stat.Target] = stat.Visit
+		} else {
+			data[stat.Target] += stat.Visit
+		}
+	}
+	var statRows []StatsRow
+	for k, v := range data {
+		statRows = append(statRows, StatsRow{
+			Target: k,
+			Visit:  v,
+		})
+	}
+	slices.SortFunc(statRows, func(a, b StatsRow) int {
+		return int(b.Visit) - int(a.Visit)
+	})
+	return statRows, nil
+}