feat(user): leave gorm

6 files changed, 112 insertions, 62 deletions

diff --git a/commands/stats.go b/commands/stats.go
index 28c0bcc..c9562dc 100644
--- a/commands/stats.go
+++ b/commands/stats.go
@@ -237,7 +237,7 @@ func generatePlot(ctx context.Context, dg bot.Session, i *interaction.Applicatio
 
 	cnt := 0
 	for in, c := range copaings {
-		m, err := guild.GetMember(i.GuildID, c.DiscordID).Do(ctx)
+		m, err := guild.GetMember(i.GuildID, c.ID).Do(ctx)
 		if err != nil {
 			bot.Logger(ctx).Error("fetching guild member", "error", err)
 			return nil, err
diff --git a/commands/top.go b/commands/top.go
index 82183a1..db44dbd 100644
--- a/commands/top.go
+++ b/commands/top.go
@@ -59,7 +59,7 @@ func Top(ctx context.Context, dg bot.Session, i *interaction.ApplicationCommand)
 func genTopsMessage(tops []user.CopaingCached) string {
 	msg := ""
 	for i, c := range tops {
-		msg += fmt.Sprintf("%d. **<@%d>** - niveau %d", i+1, c.DiscordID, exp.Level(c.XP))
+		msg += fmt.Sprintf("%d. **<@%d>** - niveau %d", i+1, c.ID, exp.Level(c.XP))
 		if i != len(tops)-1 {
 			msg += "\n"
 		}
diff --git a/migrations/000-leave-gorm.sql b/migrations/000-leave-gorm.sql
index f80555a..360e2e3 100644
--- a/migrations/000-leave-gorm.sql
+++ b/migrations/000-leave-gorm.sql
@@ -9,13 +9,14 @@ CREATE TABLE IF NOT EXISTS guilds (
 );
 
 CREATE TABLE IF NOT EXISTS copaings (
-    id snowflake PRIMARY KEY,
-    guild_id snowflake REFERENCES guilds(id) NOT NULL
+    id snowflake UNIQUE,
+    guild_id snowflake REFERENCES guilds(id) ON DELETE CASCADE,
+    PRIMARY KEY(id, guild_id)
 );
 
 CREATE TABLE IF NOT EXISTS copaing_xps (
-    copaing_id snowflake REFERENCES copaings(id) NOT NULL,
-    guild_id snowflake REFERENCES copaings(id) NOT NULL,
+    copaing_id snowflake REFERENCES copaings(id) ON DELETE CASCADE,
+    guild_id snowflake REFERENCES copaings(id) ON DELETE CASCADE,
     xp uint DEFAULT 0,
     created_at TIMESTAMP DEFAULT now(),
     PRIMARY KEY(copaing_id, guild_id, created_at)
@@ -24,7 +25,7 @@ CREATE TABLE IF NOT EXISTS copaing_xps (
 CREATE TABLE IF NOT EXISTS xp_roles (
     xp uint,
     role snowflake,
-    guild_id snowflake REFERENCES guilds(id),
+    guild_id snowflake REFERENCES guilds(id) ON DELETE CASCADE,
     PRIMARY KEY(xp, role, guild_id)
 );
 
@@ -32,13 +33,13 @@ CREATE TABLE IF NOT EXISTS role_react_messages (
     id SERIAL PRIMARY KEY,
     message_id snowflake,
     channel_id snowflake,
-    guild_id snowflake REFERENCES guilds(id),
+    guild_id snowflake REFERENCES guilds(id) ON DELETE CASCADE,
     note TEXT
 );
 
 CREATE TABLE IF NOT EXISTS role_reacts (
     role snowflake,
-    message_id INTEGER REFERENCES role_react_messages(id),
+    message_id INTEGER REFERENCES role_react_messages(id) ON DELETE CASCADE,
     reaction TEXT,
     PRIMARY KEY(role, message_id, reaction)
 );
diff --git a/user/level.go b/user/level.go
index 012f253..26cf03f 100644
--- a/user/level.go
+++ b/user/level.go
@@ -6,6 +6,7 @@ import (
 	"sync"
 	"time"
 
+	"git.anhgelus.world/anhgelus/les-copaings-bot/common"
 	"git.anhgelus.world/anhgelus/les-copaings-bot/config"
 	"git.anhgelus.world/anhgelus/les-copaings-bot/exp"
 	"github.com/nyttikord/gokord/bot"
@@ -40,9 +41,9 @@ func onNewLevel(ctx context.Context, dg bot.Session, m *user.Member, level uint)
 }
 
 func (c *CopaingCached) onNewLevel(ctx context.Context, dg bot.Session, level uint) {
-	m, err := guild.GetMember(c.GuildID, c.DiscordID).Do(ctx)
+	m, err := guild.GetMember(c.GuildID, c.ID).Do(ctx)
 	if err != nil {
-		bot.Logger(ctx).Error("getting member for new level", "error", err, "user", c.DiscordID, "guild", c.GuildID)
+		bot.Logger(ctx).Error("getting member for new level", "error", err, "user", c.ID, "guild", c.GuildID)
 		return
 	}
 	onNewLevel(ctx, dg, m, level)
@@ -59,17 +60,18 @@ func PeriodicReducer(ctx context.Context, dg bot.Session) {
 	n := 0
 	var wg sync.WaitGroup
 	for _, g := range dg.GuildState().ListGuilds() {
-		/*cfg := config.GetGuildConfig(ctx, g)
-		res := common.GetDB(ctx).
-			Model(&CopaingXP{}).
-			Where("guild_id = ? and created_at < ?", g, exp.TimeStampNDaysBefore(ctx, cfg.DaysXPRemains)).
-			Delete(&CopaingXP{})
-		if res.Error != nil {
-			log.Error("removing old xp", "error", res.Error, "guild", g)
+		cfg := config.GetGuildConfig(ctx, g)
+		res, err := common.GetDB(ctx).ExecContext(
+			ctx,
+			`DELETE FROM copaing_xps WHERE guild_id = ? and created_at < ?`,
+			g, exp.TimeStampNDaysBefore(ctx, cfg.DaysXPRemains),
+		)
+		if err != nil {
+			log.Error("removing old xp", "error", err, "guild", g)
 			continue
 		}
 		n++
-		log.Debug("guild cleaned", "guild", g, "rows affected", res.RowsAffected)*/
+		log.Debug("guild cleaned", "guild", g, "rows affected", res.RowsAffected)
 
 		wg.Go(func() {
 			syncCopaings(ctx, dg, state.Copaings(g))
diff --git a/user/member.go b/user/member.go
index e22e00c..1e25617 100644
--- a/user/member.go
+++ b/user/member.go
@@ -2,20 +2,23 @@ package user
 
 import (
 	"context"
+	"database/sql"
+	"errors"
 	"time"
+
+	"git.anhgelus.world/anhgelus/les-copaings-bot/common"
 )
 
 type Copaing struct {
-	ID         uint        `gorm:"primarykey"`
-	DiscordID  uint64      `gorm:"not null"`
+	ID         uint64
 	CopaingXPs []CopaingXP `gorm:"constraint:OnDelete:SET NULL;"`
 	GuildID    uint64      `gorm:"not null"`
+	lastSaved  int
 }
 
 type CopaingXP struct {
-	ID        uint `gorm:"primarykey"`
 	XP        uint `gorm:"default:0"`
-	CopaingID uint
+	CopaingID uint64
 	GuildID   uint64 `gorm:"not null;"`
 	CreatedAt time.Time
 }
@@ -29,7 +32,7 @@ func GetCopaing(ctx context.Context, discordID, guildID uint64) *CopaingCached {
 	state := GetState(ctx)
 	cc, err := state.Copaing(guildID, discordID)
 	if err != nil {
-		c := Copaing{DiscordID: discordID, GuildID: guildID}
+		c := Copaing{ID: discordID, GuildID: guildID}
 		if err := c.load(ctx); err != nil {
 			panic(err)
 		}
@@ -39,30 +42,77 @@ func GetCopaing(ctx context.Context, discordID, guildID uint64) *CopaingCached {
 }
 
 func (c *Copaing) load(ctx context.Context) error {
-	return nil
-	/*err := common.GetDB(ctx).
-		Where("discord_id = ? and guild_id = ?", c.DiscordID, c.GuildID).
-		Preload("CopaingXPs").
-		FirstOrCreate(c).
-		Error
+	db := common.GetDB(ctx)
+	row := db.QueryRowContext(
+		ctx,
+		`SELECT id, guild_id FROM copaings WHERE id = ? AND guild_id = ?`,
+		c.ID, c.GuildID,
+	)
+	c.CopaingXPs = make([]CopaingXP, 0)
+	c.lastSaved = 0
+	err := row.Err()
 	if err != nil {
+		if !errors.Is(err, sql.ErrNoRows) {
+			return err
+		}
+		_, err = db.ExecContext(
+			ctx,
+			`INSERT INTO copaings (id, guild_id) VALUES (?, ?)`,
+			c.ID, c.GuildID,
+		)
 		return err
 	}
-	return err*/
+	rows, err := db.QueryContext(
+		ctx,
+		`SELECT xp, created_at FROM copaing_xps WHERE copaing_id = ? AND guild_id = ?`,
+		c.ID, c.GuildID,
+	)
+	if err != nil {
+		if !errors.Is(err, sql.ErrNoRows) {
+			return err
+		}
+		return nil
+	}
+	for rows.Next() {
+		var xp CopaingXP
+		err = rows.Scan(&xp.XP, &xp.CreatedAt)
+		if err != nil {
+			return err
+		}
+		xp.CopaingID = c.ID
+		xp.GuildID = c.GuildID
+		c.CopaingXPs = append(c.CopaingXPs, xp)
+	}
+	c.lastSaved = len(c.CopaingXPs)
+	return nil
 }
 
 func (c *Copaing) Save(ctx context.Context) error {
-	return nil //common.GetDB(ctx).Save(c).Error
+	if c.lastSaved == len(c.CopaingXPs) {
+		return nil
+	}
+	db := common.GetDB(ctx)
+	for _, xp := range c.CopaingXPs[c.lastSaved:] {
+		_, err := db.ExecContext(
+			ctx,
+			`INSERT INTO copaing_xps (copaing_id, guild_id, xp) VALUES (?, ?, ?)`,
+			c.ID, c.GuildID, xp.XP,
+		)
+		if err != nil {
+			return err
+		}
+		c.lastSaved++
+	}
+	return nil
 }
 
 func (c *Copaing) Delete(ctx context.Context) error {
-	/*db := common.GetDB(ctx)
-	err := db.
-		Where("copaing_id = ? and guild_id = ?", c.ID, c.GuildID).
-		Delete(&CopaingXP{}).
-		Error
-	if err != nil {
-		return err
-	}*/
-	return nil // db.Where("guild_id = ? AND discord_id = ?", c.GuildID, c.DiscordID).Delete(c).Error
+	_, err := common.GetDB(ctx).ExecContext(
+		ctx,
+		`DELETE FROM copaings WHERE copaing_id = ? AND guild_id = ?`,
+		c.ID, c.GuildID,
+	)
+	c.lastSaved = 0
+	c.CopaingXPs = nil
+	return err
 }
diff --git a/user/state.go b/user/state.go
index f2bfa5c..5e5df87 100644
--- a/user/state.go
+++ b/user/state.go
@@ -22,12 +22,11 @@ type XPCached struct {
 }
 
 type CopaingCached struct {
-	ID        uint
-	DiscordID uint64
-	GuildID   uint64
-	XP        uint
-	XPs       []XPCached
-	XPToAdd   uint
+	ID      uint64
+	GuildID uint64
+	XP      uint
+	XPs     []XPCached
+	XPToAdd uint
 }
 
 // copaing turns a CopaingCached into a Copaing.
@@ -37,7 +36,7 @@ type CopaingCached struct {
 // - sync the copaing cached, use CopaingCached.Sync for that.
 // TL;DR: don't use this method, unless you know what are you doing.
 func (cc *CopaingCached) copaing(ctx context.Context) *Copaing {
-	c := &Copaing{DiscordID: cc.DiscordID, GuildID: cc.GuildID}
+	c := &Copaing{ID: cc.ID, GuildID: cc.GuildID}
 	if err := c.load(ctx); err != nil {
 		panic(err)
 	}
@@ -65,7 +64,7 @@ func (cc *CopaingCached) Save(ctx context.Context) error {
 	state.mu.Lock()
 	defer state.mu.Unlock()
 
-	return state.storage.Write(KeyCopaingCachedRaw(cc.GuildID, cc.DiscordID), *cc)
+	return state.storage.Write(KeyCopaingCachedRaw(cc.GuildID, cc.ID), *cc)
 }
 
 func (cc *CopaingCached) SaveInDB(ctx context.Context) error {
@@ -116,19 +115,18 @@ func saveStateInDB(ctx context.Context) error {
 
 func FromCopaing(c *Copaing) *CopaingCached {
 	return &CopaingCached{
-		ID:        c.ID,
-		DiscordID: c.DiscordID,
-		GuildID:   c.GuildID,
-		XP:        calcXP(c),
-		XPs:       generateXPs(c),
-		XPToAdd:   0,
+		ID:      c.ID,
+		GuildID: c.GuildID,
+		XP:      calcXP(c),
+		XPs:     generateXPs(c),
+		XPToAdd: 0,
 	}
 }
 
 const KeyCopaingCachedPrefix = "cc:"
 
 func KeyCopaingCached(c *Copaing) string {
-	return KeyCopaingCachedRaw(c.GuildID, c.DiscordID)
+	return KeyCopaingCachedRaw(c.GuildID, c.ID)
 }
 
 func KeyCopaingCachedRaw(guildID, copaingID uint64) string {
@@ -166,12 +164,11 @@ func SetState(ctx context.Context, state *State) context.Context {
 
 func deepCopy(src CopaingCached) CopaingCached {
 	res := CopaingCached{
-		ID:        src.ID,
-		DiscordID: src.DiscordID,
-		GuildID:   src.GuildID,
-		XP:        src.XP,
-		XPToAdd:   src.XPToAdd,
-		XPs:       make([]XPCached, len(src.XPs)),
+		ID:      src.ID,
+		GuildID: src.GuildID,
+		XP:      src.XP,
+		XPToAdd: src.XPToAdd,
+		XPs:     make([]XPCached, len(src.XPs)),
 	}
 	copy(res.XPs, src.XPs)
 	return res