From 552c9f527a778443a593d11e5ce991a8513a18c1 Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Mon, 25 Dec 2023 07:52:46 +0100
Subject: Tag search

---
 main.go | 128 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 127 insertions(+), 1 deletion(-)

(limited to 'main.go')

diff --git a/main.go b/main.go
index 53c7e22..8179446 100644
--- a/main.go
+++ b/main.go
@@ -314,7 +314,7 @@ func getSubentries(tx *sql.Tx, parent int64) (entries []webEntry, err error) {
 	entries = []webEntry{}
 	for rows.Next() {
 		var e webEntry
-		if err := rows.Scan(
+		if err = rows.Scan(
 			&e.SHA1, &e.Name, &e.Modified, &e.ThumbW, &e.ThumbH); err != nil {
 			return nil, err
 		}
@@ -899,6 +899,131 @@ func handleAPISimilar(w http.ResponseWriter, r *http.Request) {
 
 // - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
+// NOTE: AND will mean MULTIPLY(IFNULL(ta.weight, 0)) per SHA1.
+const searchCTE = `WITH
+	matches(sha1, thumbw, thumbh, score) AS (
+		SELECT i.sha1, i.thumbw, i.thumbh, ta.weight AS score
+		FROM tag_assignment AS ta
+		JOIN image AS i ON i.sha1 = ta.sha1
+		WHERE ta.tag = ?
+	),
+	supertags(tag) AS (
+		SELECT DISTINCT ta.tag
+		FROM tag_assignment AS ta
+		JOIN matches AS m ON m.sha1 = ta.sha1
+	),
+	scoredtags(tag, score) AS (
+		SELECT st.tag, AVG(IFNULL(ta.weight, 0)) AS score
+		FROM supertags AS st, matches AS m
+		LEFT JOIN tag_assignment AS ta
+		ON ta.sha1 = m.sha1 AND ta.tag = st.tag
+		GROUP BY st.tag
+	)
+`
+
+type webTagMatch struct {
+	SHA1   string  `json:"sha1"`
+	ThumbW int64   `json:"thumbW"`
+	ThumbH int64   `json:"thumbH"`
+	Score  float32 `json:"score"`
+}
+
+func getTagMatches(tag int64) (matches []webTagMatch, err error) {
+	rows, err := db.Query(searchCTE+`
+		SELECT sha1, IFNULL(thumbw, 0), IFNULL(thumbh, 0), score
+		FROM matches`, tag)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	matches = []webTagMatch{}
+	for rows.Next() {
+		var match webTagMatch
+		if err = rows.Scan(&match.SHA1,
+			&match.ThumbW, &match.ThumbH, &match.Score); err != nil {
+			return nil, err
+		}
+		matches = append(matches, match)
+	}
+	return matches, rows.Err()
+}
+
+type webTagRelated struct {
+	Tag   string  `json:"tag"`
+	Score float32 `json:"score"`
+}
+
+func getTagRelated(tag int64) (result map[string][]webTagRelated, err error) {
+	rows, err := db.Query(searchCTE+`
+		SELECT ts.name, t.name, st.score FROM scoredtags AS st
+		JOIN tag AS t ON st.tag = t.id
+		JOIN tag_space AS ts ON ts.id = t.space
+		ORDER BY st.score DESC`, tag)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	result = make(map[string][]webTagRelated)
+	for rows.Next() {
+		var (
+			space string
+			r     webTagRelated
+		)
+		if err = rows.Scan(&space, &r.Tag, &r.Score); err != nil {
+			return nil, err
+		}
+		result[space] = append(result[space], r)
+	}
+	return result, rows.Err()
+}
+
+func handleAPISearch(w http.ResponseWriter, r *http.Request) {
+	var params struct {
+		Query string
+	}
+	if err := json.NewDecoder(r.Body).Decode(&params); err != nil {
+		http.Error(w, err.Error(), http.StatusBadRequest)
+		return
+	}
+
+	var result struct {
+		Matches []webTagMatch              `json:"matches"`
+		Related map[string][]webTagRelated `json:"related"`
+	}
+
+	space, tag, _ := strings.Cut(params.Query, ":")
+
+	var tagID int64
+	err := db.QueryRow(`
+		SELECT t.id FROM tag AS t
+		JOIN tag_space AS ts ON t.space = ts.id
+		WHERE ts.name = ? AND t.name = ?`, space, tag).Scan(&tagID)
+	if errors.Is(err, sql.ErrNoRows) {
+		http.Error(w, err.Error(), http.StatusNotFound)
+		return
+	} else if err != nil {
+		http.Error(w, err.Error(), http.StatusInternalServerError)
+		return
+	}
+
+	if result.Matches, err = getTagMatches(tagID); err != nil {
+		http.Error(w, err.Error(), http.StatusInternalServerError)
+		return
+	}
+	if result.Related, err = getTagRelated(tagID); err != nil {
+		http.Error(w, err.Error(), http.StatusInternalServerError)
+		return
+	}
+
+	if err := json.NewEncoder(w).Encode(result); err != nil {
+		log.Println(err)
+	}
+}
+
+// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+
 // cmdWeb runs a web UI against GD on ADDRESS.
 func cmdWeb(args []string) error {
 	if len(args) != 2 {
@@ -923,6 +1048,7 @@ func cmdWeb(args []string) error {
 	http.HandleFunc("/api/orphans", handleAPIOrphans)
 	http.HandleFunc("/api/info", handleAPIInfo)
 	http.HandleFunc("/api/similar", handleAPISimilar)
+	http.HandleFunc("/api/search", handleAPISearch)
 
 	host, port, err := net.SplitHostPort(address)
 	if err != nil {
-- 
cgit v1.2.3-70-g09d2