1 files changed, 110 insertions, 0 deletions
diff --git a/internal/channel.go b/internal/channel.go
index da051ac..c07c4f1 100644
--- a/internal/channel.go
+++ b/internal/channel.go
@@ -1,14 +1,17 @@
 package stats
 
 import (
+	"database/sql"
 	"log"
 	"slices"
+	"strings"
 	"time"
 
 	"github.com/gempir/go-twitch-irc"
 )
 
 type Channel struct {
+	dbid   int
 	id     string
 	name   string
 	emotes map[string][]Emote
@@ -16,7 +19,18 @@ type Channel struct {
 }
 
 func NewChannel(id string, name string, db *DatabaseConnection) Channel {
+	// channel
+	var dbid int
+
+	if err := db.QueryRow(
+		"SELECT id FROM channels WHERE alias_id = ?",
+		id,
+	).Scan(&dbid); err != nil {
+		log.Panicf("Failed to get channel: %v\n", err)
+	}
+
 	c := Channel{
+		dbid:   dbid,
 		id:     id,
 		name:   name,
 		emotes: make(map[string][]Emote),
@@ -45,6 +59,102 @@ func (c *Channel) updateEmotes() (err error) {
 }
 
 func (c *Channel) HandleMessageEvent(sender twitch.User, message twitch.Message) {
+	err := c.db.Ping()
+	if err != nil {
+		log.Panicf("Failed to ping a database connection: %v\n", err)
+	}
+
+	// user
+	var userId int64
+	var userName string
+	err = c.db.QueryRow(
+		"SELECT id, alias_name FROM users WHERE alias_id = ?",
+		sender.UserID,
+	).Scan(&userId, &userName)
+	if err == sql.ErrNoRows {
+		res, err := c.db.Exec("INSERT INTO users(alias_id, alias_name) VALUES (?, ?)", sender.UserID, sender.Username)
+		if err != nil {
+			log.Panicf("Error creating a new user: %v\n", err)
+		}
+
+		userId, _ = res.LastInsertId()
+		userName = sender.Username
+	} else if err != nil {
+		log.Panicf("Error getting user: %v\n", err)
+	}
+
+	if userName != sender.Username {
+		_, err = c.db.Exec("UPDATE users SET alias_name = ? WHERE id = ?", sender.Username, userId)
+		if err != nil {
+			log.Panicf("Failed to update username: %v\n", err)
+		}
+	}
+
+	parts := strings.Split(message.Text, " ")
+	for _, part := range parts {
+		part = strings.TrimSpace(part)
+		if len(part) == 0 {
+			continue
+		}
+
+		// word
+		var wordId int64
+		err = c.db.QueryRow(
+			"SELECT id FROM words WHERE name = ?",
+			part,
+		).Scan(&wordId)
+		if err == sql.ErrNoRows {
+			res, err := c.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 = c.db.QueryRow(
+			"SELECT id, usage_count FROM channel_words WHERE word_id = ? AND user_id = ? AND channel_id = ?",
+			wordId, userId, c.dbid,
+		).Scan(&cwordId, &usageCount)
+		if err == sql.ErrNoRows {
+			res, err := c.db.Exec("INSERT INTO channel_words(word_id, user_id, channel_id) VALUES (?, ?, ?)",
+				wordId,
+				userId,
+				c.dbid,
+			)
+			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)
+		}
+
+		_, err := c.db.Exec(
+			`INSERT INTO channel_words(word_id, user_id, channel_id)
+			VALUES (?, ?, ?)
+			ON DUPLICATE KEY UPDATE
+				usage_count = usage_count + 1,
+				last_used_at = UTC_TIMESTAMP()`,
+			wordId,
+			userId,
+			c.dbid,
+		)
+		if err != nil {
+			log.Panicf("Error creating a new channel word: %vn", err)
+		}
+	}
+}
+
+func (c *Channel) Name() string {
+	return c.name
 }
 
 func JoinChannels(channels *[]Channel, client *twitch.Client, db *DatabaseConnection) {