add views, expand api, add windows opening thing overridemain

15 files changed, 216 insertions, 35 deletions

diff --git a/.pik/build.sh b/.pik/build.sh
index 63a9c0c..5c62fe9 100644
--- a/.pik/build.sh
+++ b/.pik/build.sh
@@ -1,2 +1,2 @@
 #!/usr/bin/env bash
-go build "$@" .
\ No newline at end of file
+go build -ldflags "-s -w" "$@" .
\ No newline at end of file
diff --git a/api/filter.go b/api/filter.go
index 1130c7c..5fbe6a7 100644
--- a/api/filter.go
+++ b/api/filter.go
@@ -2,8 +2,44 @@
 
 package api
 
-type Filter struct {
-	Win      *bool
-	ActName  *string
-	ActIndex *int
+import (
+	"fmt"
+	"reflect"
+	"strings"
+	"sts2stats/spool"
+)
+
+type Filter interface {
+	WhereQuery() string
+}
+
+type RunFilter struct {
+	Win       *bool
+	Character *string
+	Ascension *int
+	Version   *string
+}
+
+func Query(f any) string {
+	var parts []string
+	for field, value := range reflect.ValueOf(f).Fields() {
+		if !value.IsNil() {
+			elem := value.Elem()
+
+			switch elem.Kind() {
+			case reflect.String:
+				parts = append(parts, fmt.Sprintf("%v = '%v'", field.Name, elem.String()))
+			case reflect.Int:
+				parts = append(parts, fmt.Sprintf("%v = %v", field.Name, elem.Int()))
+			case reflect.Bool:
+				parts = append(parts, fmt.Sprintf("%v = %v", field.Name, elem.Bool()))
+			default:
+				spool.Warn("unsupported filter field kind: %v", elem.Kind())
+			}
+		}
+	}
+	if len(parts) == 0 {
+		return ""
+	}
+	return fmt.Sprintf("WHERE %s", strings.Join(parts, " AND "))
 }
diff --git a/api/filter_test.go b/api/filter_test.go
new file mode 100644
index 0000000..dcfa5df
--- /dev/null
+++ b/api/filter_test.go
@@ -0,0 +1,19 @@
+//go:build api
+
+package api
+
+import "testing"
+
+func ptr[T any](obj T) *T {
+	return &obj
+}
+
+func TestFilter(t *testing.T) {
+	f := RunFilter{
+		Win:       ptr(true),
+		Character: ptr("guy"),
+		Ascension: ptr(3),
+		Version:   ptr("0.333.2"),
+	}
+	Query(f)
+}
diff --git a/api/request.go b/api/request.go
new file mode 100644
index 0000000..0df902a
--- /dev/null
+++ b/api/request.go
@@ -0,0 +1,10 @@
+//go:build api
+
+package api
+
+type Request struct {
+}
+
+type CardRequest struct {
+	Request
+}
diff --git a/main.go b/main.go
index c3f60ac..a1ae13f 100644
--- a/main.go
+++ b/main.go
@@ -50,7 +50,10 @@ func main() {
 		if err != nil {
 			spool.Warn("ui: %v\n", err)
 		}
-		exec.Command("xdg-open", "http://localhost:4213/").Run()
+		err = exec.Command(opener, "http://localhost:4213/").Run()
+		if err != nil {
+			spool.Warn("ui: %v\n", err)
+		}
 	}
 
 	c := make(chan os.Signal, 1)
diff --git a/model/save.go b/model/save.go
index 4127828..b1dfdd2 100644
--- a/model/save.go
+++ b/model/save.go
@@ -77,30 +77,32 @@ type RunSave struct {
 			TurnsTaken int    `json:"turns_taken"`
 		} `json:"rooms"`
 	} `json:"map_point_history"`
-	Modifiers    []interface{} `json:"modifiers"`
-	PlatformType string        `json:"platform_type"`
-	Players      []struct {
-		Character string `json:"character"`
-		Deck      []struct {
-			FloorAddedToDeck int    `json:"floor_added_to_deck"`
-			ID               string `json:"id"`
-			Enchantment      struct {
-				Amount int    `json:"amount"`
-				ID     string `json:"id"`
-			} `json:"enchantment,omitempty"`
-		} `json:"deck"`
-		ID                 int           `json:"id"`
-		MaxPotionSlotCount int           `json:"max_potion_slot_count"`
-		Potions            []interface{} `json:"potions"`
-		Relics             []struct {
-			FloorAddedToDeck int    `json:"floor_added_to_deck"`
-			ID               string `json:"id"`
-		} `json:"relics"`
-	} `json:"players"`
-	RunTime       int    `json:"run_time"`
-	SchemaVersion int    `json:"schema_version"`
-	Seed          string `json:"seed"`
-	StartTime     int    `json:"start_time"`
-	WasAbandoned  bool   `json:"was_abandoned"`
-	Win           bool   `json:"win"`
+	Modifiers     []interface{} `json:"modifiers"`
+	PlatformType  string        `json:"platform_type"`
+	Players       []Player      `json:"players"`
+	RunTime       int           `json:"run_time"`
+	SchemaVersion int           `json:"schema_version"`
+	Seed          string        `json:"seed"`
+	StartTime     int           `json:"start_time"`
+	WasAbandoned  bool          `json:"was_abandoned"`
+	Win           bool          `json:"win"`
+}
+
+type Player struct {
+	Character string `json:"character"`
+	Deck      []struct {
+		FloorAddedToDeck int    `json:"floor_added_to_deck"`
+		ID               string `json:"id"`
+		Enchantment      struct {
+			Amount int    `json:"amount"`
+			ID     string `json:"id"`
+		} `json:"enchantment,omitempty"`
+	} `json:"deck"`
+	ID                 int           `json:"id"`
+	MaxPotionSlotCount int           `json:"max_potion_slot_count"`
+	Potions            []interface{} `json:"potions"`
+	Relics             []struct {
+		FloorAddedToDeck int    `json:"floor_added_to_deck"`
+		ID               string `json:"id"`
+	} `json:"relics"`
 }
diff --git a/open_linux.go b/open_linux.go
new file mode 100644
index 0000000..985a753
--- /dev/null
+++ b/open_linux.go
@@ -0,0 +1,3 @@
+package main
+
+const opener = "xdg-open"
diff --git a/open_windows.go b/open_windows.go
new file mode 100644
index 0000000..155a9a5
--- /dev/null
+++ b/open_windows.go
@@ -0,0 +1,3 @@
+package main
+
+const opener = "explorer"
diff --git a/stats/db.go b/stats/db.go
index 327373f..397b5e9 100644
--- a/stats/db.go
+++ b/stats/db.go
@@ -14,6 +14,7 @@ type RunStat struct {
 	FloorsClimbed int
 	Abandoned     bool
 	InProgress    bool
+	Character     string
 }
 
 type RoomStat struct {
@@ -21,7 +22,16 @@ type RoomStat struct {
 	Floor int
 }
 
-func NewRunStat(run model.Run) RunStat {
+func NewRunStat(run model.Run, steamid int) RunStat {
+	var player *model.Player
+	for _, p := range run.Players {
+		if p.ID == steamid {
+			player = &p
+		}
+	}
+	if player == nil {
+		player = &run.Players[0]
+	}
 	st := RunStat{
 		Version:       run.BuildID,
 		StartTime:     time.Unix(int64(run.StartTime), 0),
@@ -31,6 +41,7 @@ func NewRunStat(run model.Run) RunStat {
 		Abandoned:     run.WasAbandoned,
 		FloorsClimbed: runLen(run),
 		InProgress:    run.KilledByEncounter != "" || run.KilledByEvent != "" || run.Win != true,
+		Character:     player.Character,
 	}
 	return st
 }
diff --git a/stats/enrich.go b/stats/enrich.go
index 40e91ec..192729f 100644
--- a/stats/enrich.go
+++ b/stats/enrich.go
@@ -1,6 +1,7 @@
 package stats
 
 import (
+	"github.com/spf13/pflag"
 	"sts2stats/model"
 	"sts2stats/spool"
 	"sts2stats/storage"
@@ -36,11 +37,13 @@ var Enrichers = map[string]Enricher{
 	"card choice":    EnrichWrap(EnrichCardChoice),
 }
 
+var SteamId = pflag.IntP("steamid", "s", 0, "steamid to match players to")
+
 func Enrich(run model.Run) error {
 	startTime := time.Now()
 	id := run.RunId[:4]
 	wg := sync.WaitGroup{}
-	st := NewRunStat(run)
+	st := NewRunStat(run, *SteamId)
 	for k, e := range Enrichers {
 		wg.Go(func() {
 			spool.Debug("[%v] Starting %v enrichment\n", id, k)
diff --git a/storage/storage.go b/storage/storage.go
index 253a4c2..ddb466d 100644
--- a/storage/storage.go
+++ b/storage/storage.go
@@ -54,7 +54,12 @@ func Init(items ...any) error {
 		return err
 	}
 
-	return dbmap.CreateTablesIfNotExists()
+	err = dbmap.CreateTablesIfNotExists()
+	if err != nil {
+		return err
+	}
+
+	return SetupViews()
 }
 
 func register(item ...any) error {
diff --git a/storage/views.go b/storage/views.go
new file mode 100644
index 0000000..6808f11
--- /dev/null
+++ b/storage/views.go
@@ -0,0 +1,41 @@
+package storage
+
+import (
+	"embed"
+	_ "embed"
+	"fmt"
+	"io/fs"
+	"path/filepath"
+	"strings"
+	"sts2stats/spool"
+)
+
+//go:embed views
+var data embed.FS
+
+func SetupViews() error {
+	err := fs.WalkDir(data, "views", func(path string, d fs.DirEntry, err error) error {
+		if d.IsDir() {
+			return nil
+		}
+		ext := filepath.Ext(path)
+		name := strings.TrimSuffix(strings.Join(strings.Split(path, "/")[1:], "_"), ext)
+		content, err := fs.ReadFile(data, path)
+		if err != nil {
+			spool.Warn("view %s: %s\n", name, err)
+			return nil
+		}
+		err = AddView(name, string(content))
+		if err != nil {
+			spool.Warn("view %s: %s\n", name, err)
+			return nil
+		}
+		return nil
+	})
+	return err
+}
+
+func AddView(name string, selectQuery string) error {
+	_, err := conn.Exec(fmt.Sprintf("CREATE OR REPLACE VIEW _%s AS %s;", name, selectQuery))
+	return err
+}
diff --git a/storage/views/Ancients/Best.gosql b/storage/views/Ancients/Best.gosql
new file mode 100644
index 0000000..bd87658
--- /dev/null
+++ b/storage/views/Ancients/Best.gosql
@@ -0,0 +1,18 @@
+SELECT
+    AVG(ActIndex) + 1 AS Act,
+    Character,
+    Key,
+    Count(*) AS Amount,
+    Sum(Win) AS Wins,
+    Amount - Sum(Win) AS Losses,
+    ROUND(Wins / Amount, 2) as Winrate,
+FROM
+    AncientChoice
+WHERE
+    Chosen = TRUE,
+GROUP BY
+    Character,
+    Key,
+    ActName,
+ORDER BY
+    Wins
\ No newline at end of file
diff --git a/storage/views/Ancients/BestSkips.gosql b/storage/views/Ancients/BestSkips.gosql
new file mode 100644
index 0000000..0b926c1
--- /dev/null
+++ b/storage/views/Ancients/BestSkips.gosql
@@ -0,0 +1,17 @@
+SELECT
+    AVG(ActIndex) + 1 AS Act,
+    Character,
+    Key,
+    Sum(Win) as Wins,
+    Count(*) - Sum(Win) as Losses,
+    ROUND(Wins / Count(*), 2) as Winrate,
+FROM
+    AncientChoice
+WHERE
+    Chosen = FALSE
+GROUP BY
+    Character,
+    Key,
+    ActName
+ORDER BY
+    Wins * Winrate DESC
\ No newline at end of file
diff --git a/storage/views/Cards/Best.gosql b/storage/views/Cards/Best.gosql
new file mode 100644
index 0000000..73e3b31
--- /dev/null
+++ b/storage/views/Cards/Best.gosql
@@ -0,0 +1,10 @@
+SELECT
+    Card,
+    ActIndex + 1 AS Act,
+    Sum(Win) as Wins,
+    Count(*) - Sum(Win) as Losses,
+    ROUND(Wins / Count(*), 2) as Winrate,
+FROM
+    CardChoice
+WHERE
+    Picked = TRUE
\ No newline at end of file