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)
		}
	}
}