add views, expand api, add windows opening thing overridemain

5 files changed, 92 insertions, 1 deletions

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