cmd/marabot: start importing discord data

Signed-off-by: Xe Iaso <me@xeiaso.net>

2 files changed, 165 insertions, 46 deletions

diff --git a/cmd/marabot/main.go b/cmd/marabot/main.go
index 2d94bc5..547194c 100644
--- a/cmd/marabot/main.go
+++ b/cmd/marabot/main.go
@@ -5,6 +5,7 @@ import (
 	"database/sql"
 	_ "embed"
 	"flag"
+	"fmt"
 	"os"
 	"os/signal"
 	"syscall"
@@ -12,6 +13,7 @@ import (
 
 	"github.com/bwmarrin/discordgo"
 	_ "modernc.org/sqlite"
+	"tailscale.com/hostinfo"
 	"within.website/ln"
 	"within.website/ln/opname"
 	"within.website/x/internal"
@@ -24,6 +26,8 @@ var (
 	revoltToken           = flag.String("revolt-token", "", "Revolt bot token")
 	revoltAPIServer       = flag.String("revolt-api-server", "https://api.revolt.chat", "API server for Revolt")
 	revoltWebsocketServer = flag.String("revolt-ws-server", "wss://ws.revolt.chat", "Websocket server for Revolt")
+	tsAuthkey             = flag.String("ts-authkey", "", "Tailscale authkey")
+	tsHostname            = flag.String("ts-hostname", "", "Tailscale hostname")
 
 	//go:embed schema.sql
 	dbSchema string
@@ -37,16 +41,29 @@ const (
 func main() {
 	internal.HandleStartup()
 
+	hostinfo.SetApp("within.website/x/cmd/marabot")
+
 	ctx, cancel := context.WithCancel(opname.With(context.Background(), "marabot"))
 	defer cancel()
 
 	ln.Log(ctx, ln.Action("starting up"))
 
+	db, err := sql.Open("sqlite", *dbFile)
+	if err != nil {
+		ln.FatalErr(ctx, err, ln.Action("opening sqlite database"))
+	}
+	defer db.Close()
+
+	if _, err := db.ExecContext(ctx, dbSchema); err != nil {
+		ln.FatalErr(ctx, err, ln.Action("running database schema"))
+	}
+
 	// Init a new client.
 	client := revolt.NewWithEndpoint(*revoltToken, *revoltAPIServer, *revoltWebsocketServer)
 
 	mr := &MaraRevolt{
 		cli: client,
+		db:  db,
 	}
 
 	client.Connect(ctx, mr)
@@ -56,52 +73,48 @@ func main() {
 		ln.FatalErr(ctx, err, ln.Action("creating discord client"))
 	}
 
+	dg.AddHandler(mr.DiscordMessageCreate)
+	dg.AddHandler(mr.DiscordMessageDelete)
+	dg.AddHandler(mr.DiscordMessageEdit)
+
 	if err := dg.Open(); err != nil {
 		ln.FatalErr(ctx, err, ln.Action("opening discord client"))
 	}
 	defer dg.Close()
 
-	db, err := sql.Open("sqlite", *dbFile)
+	channels, err := dg.GuildChannels(furryholeDiscord)
+	if err == nil {
+		for _, ch := range channels {
+			if _, err := mr.db.Exec("INSERT INTO discord_channels (id, guild_id, name, topic, nsfw) VALUES (?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name, topic = EXCLUDED.topic, nsfw = EXCLUDED.nsfw", ch.ID, ch.GuildID, ch.Name, ch.Topic, ch.NSFW); err != nil {
+				ln.Error(ctx, err, ln.F{"channel_name": ch.Name, "channel_id": ch.ID})
+				continue
+			}
+		}
+	}
+
+	roles, err := dg.GuildRoles(furryholeDiscord)
 	if err != nil {
-		ln.FatalErr(ctx, err, ln.Action("opening sqlite database"))
+		ln.FatalErr(ctx, err, ln.Action("getting guild roles"))
 	}
-	defer db.Close()
 
-	if _, err := db.ExecContext(ctx, dbSchema); err != nil {
-		ln.FatalErr(ctx, err, ln.Action("running database schema"))
+	for _, role := range roles {
+		if _, err := db.ExecContext(ctx, "INSERT INTO discord_roles (guild_id, id, name, color, hoist, position) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name, color = EXCLUDED.color, position = EXCLUDED.position", furryholeDiscord, role.ID, role.Name, fmt.Sprintf("#%06x", role.Color), role.Hoist, role.Position); err != nil {
+			ln.Error(ctx, err, ln.Action("inserting role"))
+			continue
+		}
 	}
 
-	// roles, err := dg.GuildRoles(furryholeDiscord)
-	// if err != nil {
-	// 	ln.FatalErr(ctx, err, ln.Action("getting guild roles"))
-	// }
-
-	// for _, role := range roles {
-	// 	if role.Name == "@everyone" {
-	// 		continue
-	// 	}
-	// 	ln.Log(ctx, ln.Info("role"), ln.F{"name": role.Name, "id": role.ID, "color": fmt.Sprintf("#%06x", role.Color)})
-
-	// 	id, err := client.ServerCreateRole(ctx, furryholeRevolt, role.Name)
-	// 	if err != nil {
-	// 		ln.Error(ctx, err, ln.Action("creating role"))
-	// 		continue
-	// 	}
-
-	// 	if err := client.ServerEditRole(ctx, furryholeRevolt, id, &revolt.EditRole{
-	// 		Color: fmt.Sprintf("#%06x", role.Color),
-	// 		Hoist: role.Hoist,
-	// 		Rank:  250 - role.Position,
-	// 	}); err != nil {
-	// 		ln.Error(ctx, err, ln.Action("editing role"))
-	// 		continue
-	// 	}
-
-	// 	if _, err := db.ExecContext(ctx, "INSERT INTO roles (discord_server, discord_id, revolt_server, revolt_id, name, color, hoist) VALUES (?, ?, ?, ?, ?, ?, ?)", furryholeDiscord, role.ID, furryholeRevolt, id, role.Name, fmt.Sprintf("#%06x", role.Color), role.Hoist); err != nil {
-	// 		ln.Error(ctx, err, ln.Action("inserting role"))
-	// 		continue
-	// 	}
-	// }
+	// https://cdn.discordapp.com/emojis/664686615616290816.webp?size=240&quality=lossless
+	emoji, err := dg.GuildEmojis(furryholeDiscord)
+	if err != nil {
+		ln.FatalErr(ctx, err, ln.Action("getting guild emoji"))
+	}
+	for _, emoji := range emoji {
+		if _, err := db.ExecContext(ctx, "INSERT INTO discord_emoji (id, guild_id, name, url) VALUES (?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name, url = EXCLUDED.url", emoji.ID, furryholeDiscord, emoji.Name, fmt.Sprintf("https://cdn.discordapp.com/emojis/%s.webp?size=240&quality=lossless", emoji.ID)); err != nil {
+			ln.Error(ctx, err, ln.Action("inserting emoji"))
+			continue
+		}
+	}
 
 	// Wait for close.
 	sc := make(chan os.Signal, 1)
@@ -125,12 +138,67 @@ func main() {
 	}
 }
 
+func (mr *MaraRevolt) DiscordMessageDelete(s *discordgo.Session, m *discordgo.MessageDelete) {
+	if _, err := mr.db.Exec("DELETE FROM discord_messages WHERE id = ?", m.ID); err != nil {
+		ln.Error(context.Background(), err)
+	}
+}
+
+func (mr *MaraRevolt) DiscordMessageEdit(s *discordgo.Session, m *discordgo.MessageUpdate) {
+	if _, err := mr.db.Exec("UPDATE discord_messages SET content = ?, edited_at = ? WHERE id = ?", m.Content, time.Now().Format(time.RFC3339), m.ID); err != nil {
+		ln.Error(context.Background(), err)
+	}
+}
+
+func (mr *MaraRevolt) DiscordMessageCreate(s *discordgo.Session, m *discordgo.MessageCreate) {
+	if err := mr.discordMessageCreate(s, m); err != nil {
+		ln.Error(context.Background(), err)
+	}
+}
+
+func (mr *MaraRevolt) discordMessageCreate(s *discordgo.Session, m *discordgo.MessageCreate) error {
+	if _, err := mr.db.Exec(`INSERT INTO discord_users (id, username, avatar_url, accent_color)
+VALUES (?, ?, ?, ?)
+ON CONFLICT(id)
+DO UPDATE SET username = EXCLUDED.username, avatar_url = EXCLUDED.avatar_url, accent_color = EXCLUDED.accent_color`, m.Author.ID, m.Author.Username, m.Author.AvatarURL(""), m.Author.AccentColor); err != nil {
+		return err
+	}
+
+	if _, err := mr.db.Exec(`INSERT INTO discord_messages (id, guild_id, channel_id, author_id, content, created_at, edited_at, webhook_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, m.ID, m.GuildID, m.ChannelID, m.Author.ID, m.Content, m.Timestamp.Format(time.RFC3339), m.EditedTimestamp, m.WebhookID); err != nil {
+		return err
+	}
+
+	for _, att := range m.Attachments {
+		if _, err := mr.db.Exec(`INSERT INTO discord_attachments (id, message_id, url, proxy_url, filename, content_type, width, height, size) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`, att.ID, m.ID, att.URL, att.ProxyURL, att.Filename, att.ContentType, att.Width, att.Height, att.Size); err != nil {
+			return err
+		}
+	}
+
+	ch, err := s.Channel(m.ChannelID)
+	if err != nil {
+		return err
+	}
+
+	if _, err := mr.db.Exec("INSERT INTO discord_channels (id, guild_id, name, topic, nsfw) VALUES (?, ?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name, topic = EXCLUDED.topic, nsfw = EXCLUDED.nsfw", ch.ID, ch.GuildID, ch.Name, ch.Topic, ch.NSFW); err != nil {
+		return err
+	}
+
+	for _, emoji := range m.GetCustomEmojis() {
+		if _, err := mr.db.Exec("INSERT INTO discord_emoji (id, guild_id, name, url) VALUES (?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET name = EXCLUDED.name, url = EXCLUDED.url", emoji.ID, furryholeDiscord, emoji.Name, fmt.Sprintf("https://cdn.discordapp.com/emojis/%s.webp?size=240&quality=lossless", emoji.ID)); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
 type MaraRevolt struct {
 	cli *revolt.Client
+	db  *sql.DB
 	revolt.NullHandler
 }
 
-func (m *MaraRevolt) MessageCreate(ctx context.Context, msg *revolt.Message) error {
+func (mr *MaraRevolt) MessageCreate(ctx context.Context, msg *revolt.Message) error {
 	if msg.Content == "!ping" {
 		sendMsg := &revolt.SendMessage{
 			Masquerade: &revolt.Masquerade{
@@ -140,7 +208,7 @@ func (m *MaraRevolt) MessageCreate(ctx context.Context, msg *revolt.Message) err
 		}
 		sendMsg.SetContent("🏓 Pong!")
 
-		if _, err := m.cli.MessageReply(ctx, msg.ChannelId, msg.ID, true, sendMsg); err != nil {
+		if _, err := mr.cli.MessageReply(ctx, msg.ChannelId, msg.ID, true, sendMsg); err != nil {
 			return err
 		}
 	}
diff --git a/cmd/marabot/schema.sql b/cmd/marabot/schema.sql
index 7d4046f..b95c4c7 100644
--- a/cmd/marabot/schema.sql
+++ b/cmd/marabot/schema.sql
@@ -1,10 +1,61 @@
-CREATE TABLE IF NOT EXISTS roles (
-    id SERIAL PRIMARY KEY,
-    discord_server TEXT NOT NULL,
-    discord_id TEXT NOT NULL,
-    revolt_server TEXT NOT NULL,
-    revolt_id TEXT NOT NULL,
+CREATE TABLE IF NOT EXISTS discord_roles (
+    id TEXT PRIMARY KEY,
+    guild_id TEXT NOT NULL,
     name TEXT NOT NULL,
     color TEXT NOT NULL,
-    hoist BOOLEAN NOT NULL
-);
\ No newline at end of file
+    hoist BOOLEAN NOT NULL,
+    position INTEGER NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS discord_users (
+    id TEXT PRIMARY KEY,
+    username TEXT NOT NULL,
+    avatar_url TEXT NOT NULL,
+    accent_color INTEGER NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS discord_messages (
+    id TEXT PRIMARY KEY,
+    guild_id TEXT NOT NULL,
+    channel_id TEXT NOT NULL,
+    author_id TEXT NOT NULL,
+    content TEXT,
+    created_at TEXT NOT NULL,
+    edited_at TEXT,
+    webhook_id TEXT
+);
+
+CREATE TABLE IF NOT EXISTS discord_attachments (
+    id TEXT PRIMARY KEY,
+    message_id TEXT NOT NULL,
+    url TEXT NOT NULL,
+    proxy_url TEXT NOT NULL,
+    filename TEXT NOT NULL,
+    content_type TEXT NOT NULL,
+    width INTEGER NOT NULL,
+    height INTEGER NOT NULL,
+    "size" INTEGER NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS discord_channels (
+    id TEXT PRIMARY KEY,
+    guild_id TEXT NOT NULL,
+    name TEXT NOT NULL,
+    topic TEXT NOT NULL,
+    nsfw BOOLEAN NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS discord_guilds (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    description TEXT NOT NULL,
+    icon_url TEXT NOT NULL,
+    banner_url TEXT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS discord_emoji (
+    id TEXT PRIMARY KEY,
+    guild_id TEXT NOT NULL,
+    name TEXT NOT NULL,
+    url TEXT NOT NULL
+);