feat: save words into the database

2 files changed, 122 insertions, 0 deletions

diff --git a/internal/database.go b/internal/database.go
index 353c117..82a1255 100644
--- a/internal/database.go
+++ b/internal/database.go
@@ -53,6 +53,18 @@ func (d *DatabaseConnection) Query(query string, args ...any) ([]map[string]stri
 	return data, nil
 }
 
+func (d *DatabaseConnection) Exec(query string, args ...any) (sql.Result, error) {
+	return d.db.Exec(query, args...)
+}
+
+func (d *DatabaseConnection) QueryRow(query string, args ...any) *sql.Row {
+	return d.db.QueryRow(query, args...)
+}
+
+func (d *DatabaseConnection) Ping() error {
+	return d.db.Ping()
+}
+
 func (d *DatabaseConnection) Close() error {
 	return d.db.Close()
 }
diff --git a/internal/handlers.go b/internal/handlers.go
new file mode 100644
index 0000000..9c0ade4
--- /dev/null
+++ b/internal/handlers.go
@@ -0,0 +1,110 @@
+package stats
+
+import (
+	"database/sql"
+	"log"
+	"strings"
+
+	"github.com/gempir/go-twitch-irc"
+)
+
+func HandleMessageEvent(channel string, user twitch.User, message twitch.Message, db *DatabaseConnection) {
+	err := db.Ping()
+	if err != nil {
+		log.Panicf("Failed to ping a database connection: %v\n", err)
+	}
+
+	// channel
+	var channelId int
+	var channelName string
+	err = db.QueryRow(
+		"SELECT id, alias_name FROM channels WHERE alias_id = ?",
+		message.Tags["room-id"],
+	).Scan(&channelId, &channelName)
+	if err != nil {
+		log.Panicf("Failed to get channel: %v\n", err)
+	}
+
+	if channelName != channel {
+		_, err = db.Exec("UPDATE channels SET alias_name = ? WHERE id = ?", channel, channelId)
+		if err != nil {
+			log.Panicf("Failed to update channel name: %v\n", err)
+		}
+	}
+
+	// user
+	var userId int64
+	var userName string
+	err = db.QueryRow(
+		"SELECT id, alias_name FROM users WHERE alias_id = ?",
+		user.UserID,
+	).Scan(&userId, &userName)
+	if err == sql.ErrNoRows {
+		res, err := db.Exec("INSERT INTO users(alias_id, alias_name) VALUES (?, ?)", user.UserID, user.Username)
+		if err != nil {
+			log.Panicf("Error creating a new user: %v\n", err)
+		}
+
+		userId, _ = res.LastInsertId()
+		userName = user.Username
+	} else if err != nil {
+		log.Panicf("Error getting user: %v\n", err)
+	}
+
+	if userName != user.Username {
+		_, err = db.Exec("UPDATE channels SET alias_name = ? WHERE id = ?", user.Username, userId)
+		if err != nil {
+			log.Panicf("Failed to update channel name: %v\n", err)
+		}
+	}
+
+	parts := strings.Split(message.Text, " ")
+	for _, part := range parts {
+		// word
+		var wordId int64
+		err = db.QueryRow(
+			"SELECT id FROM words WHERE name = ?",
+			part,
+		).Scan(&wordId)
+		if err == sql.ErrNoRows {
+			res, err := db.Exec("INSERT INTO words(name) VALUES (?)", part)
+			if err != nil {
+				log.Panicf("Error creating a new word: %v\n", err)
+			}
+
+			wordId, _ = res.LastInsertId()
+		} else if err != nil {
+			log.Panicf("Error getting word: %v\n", err)
+		}
+
+		// channel word
+		var cwordId int64
+		var usageCount int
+		err = db.QueryRow(
+			"SELECT id, usage_count FROM channel_words WHERE word_id = ? AND user_id = ? AND channel_id = ?",
+			wordId, userId, channelId,
+		).Scan(&cwordId, &usageCount)
+		if err == sql.ErrNoRows {
+			res, err := db.Exec("INSERT INTO channel_words(word_id, user_id, channel_id) VALUES (?, ?, ?)",
+				wordId,
+				userId,
+				channelId,
+			)
+			if err != nil {
+				log.Panicf("Error creating a new channel word: %v\n", err)
+			}
+
+			cwordId, _ = res.LastInsertId()
+			usageCount = 0
+		} else if err != nil {
+			log.Panicf("Error getting channel word: %v\n", err)
+		}
+
+		usageCount++
+		_, err = db.Exec("UPDATE channel_words SET last_used_at = UTC_TIMESTAMP, usage_count = ? WHERE id = ?", usageCount, cwordId)
+
+		if err != nil {
+			log.Panicf("Failed to update channel word: %v\n", err)
+		}
+	}
+}