feat: save words into the database

4 files changed, 158 insertions, 2 deletions

diff --git a/cmd/statsbot/main.go b/cmd/statsbot/main.go
index e7fd098..35e8f28 100644
--- a/cmd/statsbot/main.go
+++ b/cmd/statsbot/main.go
@@ -6,12 +6,14 @@ import (
 	"github.com/gempir/go-twitch-irc"
 
 	stats "ilotterytea/tinystats/internal"
+
+	_ "github.com/go-sql-driver/mysql"
 )
 
 func main() {
 	db, err := stats.NewDatabaseConnection("mysql", "kochan:kochan@/stats")
 	if err != nil {
-		log.Panicf("Failed to establish database connection: %v\n", err)
+		log.Panicf("Failed to establish a database connection: %v\n", err)
 	}
 	defer db.Close()
 
@@ -22,7 +24,7 @@ func main() {
 	})
 
 	client.OnNewMessage(func(channel string, user twitch.User, message twitch.Message) {
-		log.Printf("Message: %s\n", message.Text)
+		go stats.HandleMessageEvent(channel, user, message, db)
 	})
 
 	channels, _ := db.Query("SELECT alias_name FROM channels WHERE opted_out_at IS NULL")
diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..e453239
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,32 @@
+CREATE TABLE IF NOT EXISTS channels (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  alias_id INTEGER NOT NULL UNIQUE,
+  alias_name TEXT NOT NULL UNIQUE,
+  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+  opted_out_at TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS users (
+  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+  alias_id INTEGER NOT NULL UNIQUE,
+  alias_name TEXT NOT NULL UNIQUE,
+  joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+  opted_out_at TIMESTAMP
+);
+
+CREATE TABLE IF NOT EXISTS words (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    name TEXT NOT NULL UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS channel_words (
+    id BIGINT PRIMARY KEY AUTO_INCREMENT,
+    channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    word_id BIGINT NOT NULL REFERENCES words(id) ON DELETE CASCADE,
+    usage_count BIGINT NOT NULL DEFAULT 0,
+    first_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+    last_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
+
+    UNIQUE (channel_id, user_id, word_id)
+);
\ No newline at end of file
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)
+		}
+	}
+}