velox-bot/internal/db/repos/securityrepo/repo.go

// Package securityrepo stores the anti-raid and anti-nuke settings, the
// trusted list, quarantines, locked channels, the event history and the
// dashboard's requests. Discord IDs are int64; 0 means "not set".
package securityrepo

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"strconv"
	"strings"
	"time"
)

type Repo struct {
	db *sql.DB
}

func NewRepo(db *sql.DB) *Repo {
	return &Repo{db: db}
}

// Filter actions.
const (
	ActionOff     = "off"
	ActionAlert   = "alert" // raid mode only: alert staff, touch nobody
	ActionTimeout = "timeout"
	ActionKick    = "kick"
	ActionBan     = "ban"
)

// Anti-nuke limit keys.
const (
	LimitChannelDelete = "channel_delete"
	LimitChannelCreate = "channel_create"
	LimitRoleDelete    = "role_delete"
	LimitRoleCreate    = "role_create"
	LimitBan           = "ban"
	LimitKick          = "kick"
	LimitWebhookCreate = "webhook_create"
)

// DefaultLimits mirrors the column default in the migration.
func DefaultLimits() map[string]int {
	return map[string]int{
		LimitChannelDelete: 2, LimitChannelCreate: 5, LimitRoleDelete: 2, LimitRoleCreate: 5,
		LimitBan: 3, LimitKick: 5, LimitWebhookCreate: 3,
	}
}

type Settings struct {
	GuildID        int64
	AlertChannelID int64
	AlertRoleID    int64
	AlertDM        bool

	JoinGateEnabled       bool
	NoAvatar              string
	NewAccount            string
	MinAccountAgeDays     int
	AdvertisingName       string
	UsernameFilter        string
	UsernamePatterns      []string
	UnverifiedBot         string
	UnauthorizedBot       string
	AntiRaidEnabled       bool
	RaidJoinCount         int
	RaidJoinSeconds       int
	RaidAction            string
	RaidPauseInvites      bool
	RaidQuietMinutes      int
	AntiNukeEnabled       bool
	AntiNukeWindowSeconds int
	AntiNukeLimits        map[string]int
	AntiNukeLockServer    bool
	QuarantineRoleID      int64
	AntiNukeAutoRestore   bool
	HeatEnabled           bool
	HeatStrictness        string
	HeatAction            string
	HeatTimeoutMinutes    int
	HeatEscalate          bool
	HeatBlockedWords      []string
	HeatPanicMembers      int
	HeatLockdownMentions  int
	JoinLock              string

	// Configured is false when the guild has no row yet; the fields then
	// hold the defaults, with every system off.
	Configured bool
}

func Defaults(guildID int64) *Settings {
	return &Settings{
		GuildID:  guildID,
		AlertDM:  true,
		NoAvatar: ActionOff, NewAccount: ActionOff, MinAccountAgeDays: 7, AdvertisingName: ActionOff,
		UsernameFilter: ActionOff, UnverifiedBot: ActionOff, UnauthorizedBot: ActionKick,
		RaidJoinCount: 10, RaidJoinSeconds: 10, RaidAction: ActionKick, RaidPauseInvites: true, RaidQuietMinutes: 10,
		AntiNukeWindowSeconds: 10, AntiNukeLimits: DefaultLimits(),
		HeatStrictness: "medium", HeatAction: ActionTimeout, HeatTimeoutMinutes: 10, HeatEscalate: true, HeatPanicMembers: 3,
		JoinLock: ActionOff,
	}
}

func nullID(v int64) sql.NullInt64 { return sql.NullInt64{Int64: v, Valid: v != 0} }

func (r *Repo) GetSettings(ctx context.Context, guildID int64) (*Settings, error) {
	const q = `
		SELECT alert_channel_id, alert_role_id, alert_dm,
			joingate_enabled, joingate_no_avatar, joingate_new_account, joingate_min_account_age_days,
			joingate_advertising_name, joingate_username_filter, to_json(joingate_username_patterns),
			joingate_unverified_bot, joingate_unauthorized_bot,
			antiraid_enabled, raid_join_count, raid_join_seconds, raid_action, raid_pause_invites, raid_quiet_minutes,
			antinuke_enabled, antinuke_window_seconds, antinuke_limits, antinuke_lock_server, quarantine_role_id,
			antinuke_auto_restore, heat_enabled, heat_strictness, heat_action, heat_timeout_minutes, heat_escalate,
			to_json(heat_blocked_words), heat_panic_members, heat_lockdown_mentions,
			join_lock
		FROM security_settings WHERE guild_id = $1`
	s := &Settings{GuildID: guildID, Configured: true}
	var alertCh, alertRole, qRole sql.NullInt64
	var patterns, words []byte
	var limits []byte
	err := r.db.QueryRowContext(ctx, q, guildID).Scan(
		&alertCh, &alertRole, &s.AlertDM,
		&s.JoinGateEnabled, &s.NoAvatar, &s.NewAccount, &s.MinAccountAgeDays,
		&s.AdvertisingName, &s.UsernameFilter, &patterns,
		&s.UnverifiedBot, &s.UnauthorizedBot,
		&s.AntiRaidEnabled, &s.RaidJoinCount, &s.RaidJoinSeconds, &s.RaidAction, &s.RaidPauseInvites, &s.RaidQuietMinutes,
		&s.AntiNukeEnabled, &s.AntiNukeWindowSeconds, &limits, &s.AntiNukeLockServer, &qRole,
		&s.AntiNukeAutoRestore, &s.HeatEnabled, &s.HeatStrictness, &s.HeatAction, &s.HeatTimeoutMinutes, &s.HeatEscalate,
		&words, &s.HeatPanicMembers, &s.HeatLockdownMentions,
		&s.JoinLock,
	)
	if errors.Is(err, sql.ErrNoRows) {
		return Defaults(guildID), nil
	}
	if err != nil {
		return nil, err
	}
	s.AlertChannelID, s.AlertRoleID, s.QuarantineRoleID = alertCh.Int64, alertRole.Int64, qRole.Int64
	_ = json.Unmarshal(patterns, &s.UsernamePatterns)
	_ = json.Unmarshal(words, &s.HeatBlockedWords)
	s.AntiNukeLimits = DefaultLimits()
	if len(limits) > 0 {
		// Saved values override the defaults key by key, so a limit added
		// in a later version still has a value for older rows.
		var saved map[string]int
		if err := json.Unmarshal(limits, &saved); err == nil {
			for k, v := range saved {
				s.AntiNukeLimits[k] = v
			}
		}
	}
	return s, nil
}

// ensureRow makes sure the guild has a settings row so single-column updates
// below can be plain UPDATEs.
func (r *Repo) ensureRow(ctx context.Context, guildID int64) error {
	_, err := r.db.ExecContext(ctx, `INSERT INTO security_settings (guild_id) VALUES ($1) ON CONFLICT DO NOTHING`, guildID)
	return err
}

func (r *Repo) update(ctx context.Context, guildID int64, set string, args ...any) error {
	if err := r.ensureRow(ctx, guildID); err != nil {
		return err
	}
	_, err := r.db.ExecContext(ctx, `UPDATE security_settings SET `+set+` WHERE guild_id = $1`, append([]any{guildID}, args...)...)
	return err
}

// Feature names accepted by SetEnabled.
const (
	FeatureJoinGate = "joingate"
	FeatureAntiRaid = "antiraid"
	FeatureAntiNuke = "antinuke"
	FeatureHeat     = "heat"
)

func (r *Repo) SetEnabled(ctx context.Context, guildID int64, feature string, on bool) error {
	col := map[string]string{FeatureJoinGate: "joingate_enabled", FeatureAntiRaid: "antiraid_enabled", FeatureAntiNuke: "antinuke_enabled", FeatureHeat: "heat_enabled"}[feature]
	if col == "" {
		return errors.New("unknown feature " + feature)
	}
	return r.update(ctx, guildID, col+` = $2`, on)
}

func (r *Repo) SetAlerts(ctx context.Context, guildID, channelID, roleID int64) error {
	return r.update(ctx, guildID, `alert_channel_id = $2, alert_role_id = $3`, nullID(channelID), nullID(roleID))
}

func (r *Repo) SetQuarantineRole(ctx context.Context, guildID, roleID int64) error {
	return r.update(ctx, guildID, `quarantine_role_id = $2`, nullID(roleID))
}

func (r *Repo) SetJoinLock(ctx context.Context, guildID int64, mode string) error {
	return r.update(ctx, guildID, `join_lock = $2`, mode)
}

// Tuning is a partial update of a server's thresholds, filters and actions.
// A nil field is left as it is. The security package validates it before it
// gets here; the column constraints are the last line of defence.
type Tuning struct {
	AlertDM *bool

	NoAvatar          *string
	NewAccount        *string
	MinAccountAgeDays *int
	AdvertisingName   *string
	UsernameFilter    *string
	UsernamePatterns  *[]string
	UnverifiedBot     *string
	UnauthorizedBot   *string

	RaidJoinCount    *int
	RaidJoinSeconds  *int
	RaidAction       *string
	RaidPauseInvites *bool
	RaidQuietMinutes *int

	AntiNukeWindowSeconds *int
	AntiNukeLimits        map[string]int // only the keys present change
	AntiNukeLockServer    *bool
	AntiNukeAutoRestore   *bool

	HeatStrictness       *string
	HeatAction           *string
	HeatTimeoutMinutes   *int
	HeatEscalate         *bool
	HeatBlockedWords     *[]string
	HeatPanicMembers     *int
	HeatLockdownMentions *int
}

// UpdateTuning applies the fields that are set, in one statement.
func (r *Repo) UpdateTuning(ctx context.Context, guildID int64, t Tuning) error {
	var sets []string
	args := []any{}
	add := func(col string, v any) {
		args = append(args, v)
		sets = append(sets, col+` = $`+strconv.Itoa(len(args)+1)) // $1 is the guild
	}
	addList := func(col string, v []string) {
		b, _ := json.Marshal(v)
		args = append(args, string(b))
		sets = append(sets, col+` = ARRAY(SELECT jsonb_array_elements_text($`+strconv.Itoa(len(args)+1)+`::jsonb))`)
	}
	if t.AlertDM != nil {
		add("alert_dm", *t.AlertDM)
	}
	if t.NoAvatar != nil {
		add("joingate_no_avatar", *t.NoAvatar)
	}
	if t.NewAccount != nil {
		add("joingate_new_account", *t.NewAccount)
	}
	if t.MinAccountAgeDays != nil {
		add("joingate_min_account_age_days", *t.MinAccountAgeDays)
	}
	if t.AdvertisingName != nil {
		add("joingate_advertising_name", *t.AdvertisingName)
	}
	if t.UsernameFilter != nil {
		add("joingate_username_filter", *t.UsernameFilter)
	}
	if t.UsernamePatterns != nil {
		addList("joingate_username_patterns", *t.UsernamePatterns)
	}
	if t.UnverifiedBot != nil {
		add("joingate_unverified_bot", *t.UnverifiedBot)
	}
	if t.UnauthorizedBot != nil {
		add("joingate_unauthorized_bot", *t.UnauthorizedBot)
	}
	if t.RaidJoinCount != nil {
		add("raid_join_count", *t.RaidJoinCount)
	}
	if t.RaidJoinSeconds != nil {
		add("raid_join_seconds", *t.RaidJoinSeconds)
	}
	if t.RaidAction != nil {
		add("raid_action", *t.RaidAction)
	}
	if t.RaidPauseInvites != nil {
		add("raid_pause_invites", *t.RaidPauseInvites)
	}
	if t.RaidQuietMinutes != nil {
		add("raid_quiet_minutes", *t.RaidQuietMinutes)
	}
	if t.AntiNukeWindowSeconds != nil {
		add("antinuke_window_seconds", *t.AntiNukeWindowSeconds)
	}
	if len(t.AntiNukeLimits) > 0 {
		b, _ := json.Marshal(t.AntiNukeLimits)
		args = append(args, string(b))
		// Merge into what is saved, so setting one limit keeps the others.
		sets = append(sets, `antinuke_limits = antinuke_limits || $`+strconv.Itoa(len(args)+1)+`::jsonb`)
	}
	if t.AntiNukeLockServer != nil {
		add("antinuke_lock_server", *t.AntiNukeLockServer)
	}
	if t.AntiNukeAutoRestore != nil {
		add("antinuke_auto_restore", *t.AntiNukeAutoRestore)
	}
	if t.HeatStrictness != nil {
		add("heat_strictness", *t.HeatStrictness)
	}
	if t.HeatAction != nil {
		add("heat_action", *t.HeatAction)
	}
	if t.HeatTimeoutMinutes != nil {
		add("heat_timeout_minutes", *t.HeatTimeoutMinutes)
	}
	if t.HeatEscalate != nil {
		add("heat_escalate", *t.HeatEscalate)
	}
	if t.HeatBlockedWords != nil {
		addList("heat_blocked_words", *t.HeatBlockedWords)
	}
	if t.HeatPanicMembers != nil {
		add("heat_panic_members", *t.HeatPanicMembers)
	}
	if t.HeatLockdownMentions != nil {
		add("heat_lockdown_mentions", *t.HeatLockdownMentions)
	}
	if len(sets) == 0 {
		return nil
	}
	return r.update(ctx, guildID, strings.Join(sets, ", "), args...)
}

// ---- Trusted ----

type Trusted struct {
	TargetID int64
	Kind     string // "user" or "role"
	AddedBy  int64
}

func (r *Repo) ListTrusted(ctx context.Context, guildID int64) ([]Trusted, error) {
	rows, err := r.db.QueryContext(ctx, `SELECT target_id, kind, added_by FROM security_trusted WHERE guild_id = $1 ORDER BY created_at`, guildID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Trusted
	for rows.Next() {
		var t Trusted
		if err := rows.Scan(&t.TargetID, &t.Kind, &t.AddedBy); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

func (r *Repo) AddTrusted(ctx context.Context, guildID int64, t Trusted) error {
	_, err := r.db.ExecContext(ctx, `
		INSERT INTO security_trusted (guild_id, target_id, kind, added_by) VALUES ($1, $2, $3, $4)
		ON CONFLICT (guild_id, target_id) DO UPDATE SET kind = EXCLUDED.kind`, guildID, t.TargetID, t.Kind, t.AddedBy)
	return err
}

func (r *Repo) RemoveTrusted(ctx context.Context, guildID, targetID int64) (bool, error) {
	res, err := r.db.ExecContext(ctx, `DELETE FROM security_trusted WHERE guild_id = $1 AND target_id = $2`, guildID, targetID)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// ---- Quarantine ----

type Quarantine struct {
	GuildID         int64
	UserID          int64
	PreviousRoleIDs []int64
	Reason          string
	CreatedAt       time.Time
}

// SaveQuarantine records a quarantine. If the member is already quarantined
// the first record is kept, since its roles are the ones to give back.
func (r *Repo) SaveQuarantine(ctx context.Context, q Quarantine) error {
	_, err := r.db.ExecContext(ctx, `
		INSERT INTO security_quarantines (guild_id, user_id, previous_role_ids, reason)
		VALUES ($1, $2, COALESCE($3::bigint[], '{}'), $4)
		ON CONFLICT (guild_id, user_id) DO NOTHING`, q.GuildID, q.UserID, q.PreviousRoleIDs, q.Reason)
	return err
}

func scanQuarantine(row interface{ Scan(...any) error }) (Quarantine, error) {
	var q Quarantine
	var roles []byte
	err := row.Scan(&q.GuildID, &q.UserID, &roles, &q.Reason, &q.CreatedAt)
	if err == nil {
		err = json.Unmarshal(roles, &q.PreviousRoleIDs)
	}
	return q, err
}

// GetQuarantine returns nil when the member isn't quarantined.
func (r *Repo) GetQuarantine(ctx context.Context, guildID, userID int64) (*Quarantine, error) {
	q, err := scanQuarantine(r.db.QueryRowContext(ctx, `
		SELECT guild_id, user_id, to_json(previous_role_ids), reason, created_at FROM security_quarantines
		WHERE guild_id = $1 AND user_id = $2`, guildID, userID))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &q, nil
}

func (r *Repo) ListQuarantines(ctx context.Context, guildID int64) ([]Quarantine, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT guild_id, user_id, to_json(previous_role_ids), reason, created_at FROM security_quarantines
		WHERE guild_id = $1 ORDER BY created_at DESC`, guildID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Quarantine
	for rows.Next() {
		q, err := scanQuarantine(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, q)
	}
	return out, rows.Err()
}

func (r *Repo) DeleteQuarantine(ctx context.Context, guildID, userID int64) error {
	_, err := r.db.ExecContext(ctx, `DELETE FROM security_quarantines WHERE guild_id = $1 AND user_id = $2`, guildID, userID)
	return err
}

// ---- Lockdowns ----

type Lockdown struct {
	GuildID       int64
	ChannelID     int64
	HadOverwrite  bool
	PreviousAllow int64
	PreviousDeny  int64
	ServerWide    bool
	LockedBy      int64
}

// SaveLockdown remembers a channel's @everyone overwrite from before it was
// locked. A channel that is already locked keeps its first record, which is
// the state to go back to.
func (r *Repo) SaveLockdown(ctx context.Context, l Lockdown) error {
	_, err := r.db.ExecContext(ctx, `
		INSERT INTO security_lockdowns (guild_id, channel_id, had_overwrite, previous_allow, previous_deny, server_wide, locked_by)
		VALUES ($1, $2, $3, $4, $5, $6, $7)
		ON CONFLICT (guild_id, channel_id) DO NOTHING`,
		l.GuildID, l.ChannelID, l.HadOverwrite, l.PreviousAllow, l.PreviousDeny, l.ServerWide, l.LockedBy)
	return err
}

// GetLockdown returns nil when the channel isn't locked by the bot.
func (r *Repo) GetLockdown(ctx context.Context, guildID, channelID int64) (*Lockdown, error) {
	var l Lockdown
	err := r.db.QueryRowContext(ctx, `
		SELECT guild_id, channel_id, had_overwrite, previous_allow, previous_deny, server_wide, locked_by
		FROM security_lockdowns WHERE guild_id = $1 AND channel_id = $2`, guildID, channelID).
		Scan(&l.GuildID, &l.ChannelID, &l.HadOverwrite, &l.PreviousAllow, &l.PreviousDeny, &l.ServerWide, &l.LockedBy)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &l, nil
}

// ListLockdowns lists locked channels; serverWideOnly limits it to the ones a
// server lockdown locked.
func (r *Repo) ListLockdowns(ctx context.Context, guildID int64, serverWideOnly bool) ([]Lockdown, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT guild_id, channel_id, had_overwrite, previous_allow, previous_deny, server_wide, locked_by
		FROM security_lockdowns WHERE guild_id = $1 AND (NOT $2 OR server_wide) ORDER BY locked_at`, guildID, serverWideOnly)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Lockdown
	for rows.Next() {
		var l Lockdown
		if err := rows.Scan(&l.GuildID, &l.ChannelID, &l.HadOverwrite, &l.PreviousAllow, &l.PreviousDeny, &l.ServerWide, &l.LockedBy); err != nil {
			return nil, err
		}
		out = append(out, l)
	}
	return out, rows.Err()
}

func (r *Repo) DeleteLockdown(ctx context.Context, guildID, channelID int64) error {
	_, err := r.db.ExecContext(ctx, `DELETE FROM security_lockdowns WHERE guild_id = $1 AND channel_id = $2`, guildID, channelID)
	return err
}

// ---- Events ----

type Event struct {
	GuildID   int64
	Kind      string
	ActorID   int64
	TargetID  int64
	Detail    string
	CreatedAt time.Time // set when read
}

// Event kinds.
const (
	EventJoinGate        = "joingate"
	EventRaidStarted     = "raid_started"
	EventRaidEnded       = "raid_ended"
	EventNuke            = "nuke"
	EventQuarantined     = "quarantined"
	EventReleased        = "released"
	EventUnauthorizedBot = "unauthorized_bot"
	EventLocked          = "locked"
	EventUnlocked        = "unlocked"
	EventHeat            = "heat"
	EventHeatPanic       = "heat_panic"
	EventSnapshot        = "snapshot"
	EventRestored        = "restored"
)

func (r *Repo) AddEvent(ctx context.Context, e Event) error {
	_, err := r.db.ExecContext(ctx, `
		INSERT INTO security_events (guild_id, kind, actor_id, target_id, detail) VALUES ($1, $2, $3, $4, $5)`,
		e.GuildID, e.Kind, nullID(e.ActorID), nullID(e.TargetID), e.Detail)
	return err
}

// ---- Dashboard commands ----

type Command struct {
	ID          int64
	GuildID     int64
	CommandType string
	Payload     []byte
	RequestedBy int64
}

func (r *Repo) ListUnprocessedCommands(ctx context.Context) ([]Command, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id, guild_id, command_type, COALESCE(payload, '{}'::jsonb), requested_by
		FROM security_commands WHERE processed_at IS NULL ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Command
	for rows.Next() {
		var c Command
		if err := rows.Scan(&c.ID, &c.GuildID, &c.CommandType, &c.Payload, &c.RequestedBy); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

func (r *Repo) MarkCommandProcessed(ctx context.Context, id int64) error {
	_, err := r.db.ExecContext(ctx, `UPDATE security_commands SET processed_at = NOW() WHERE id = $1`, id)
	return err
}

// ---- Raids in progress ----

type Raid struct {
	GuildID       int64
	StartedAt     time.Time
	LastJoinAt    time.Time
	InvitesPaused bool
	CaughtTotal   int
}

func (r *Repo) SaveRaid(ctx context.Context, x Raid) error {
	_, err := r.db.ExecContext(ctx, `
		INSERT INTO security_raids (guild_id, started_at, last_join_at, invites_paused, caught_total)
		VALUES ($1, $2, $3, $4, $5)
		ON CONFLICT (guild_id) DO UPDATE SET
			last_join_at = EXCLUDED.last_join_at, invites_paused = EXCLUDED.invites_paused, caught_total = EXCLUDED.caught_total`,
		x.GuildID, x.StartedAt, x.LastJoinAt, x.InvitesPaused, x.CaughtTotal)
	return err
}

func (r *Repo) ListRaids(ctx context.Context) ([]Raid, error) {
	rows, err := r.db.QueryContext(ctx, `SELECT guild_id, started_at, last_join_at, invites_paused, caught_total FROM security_raids`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Raid
	for rows.Next() {
		var x Raid
		if err := rows.Scan(&x.GuildID, &x.StartedAt, &x.LastJoinAt, &x.InvitesPaused, &x.CaughtTotal); err != nil {
			return nil, err
		}
		out = append(out, x)
	}
	return out, rows.Err()
}

// DeleteRaid ends a raid's record and its caught list.
func (r *Repo) DeleteRaid(ctx context.Context, guildID int64) error {
	if _, err := r.db.ExecContext(ctx, `DELETE FROM security_raid_caught WHERE guild_id = $1`, guildID); err != nil {
		return err
	}
	_, err := r.db.ExecContext(ctx, `DELETE FROM security_raids WHERE guild_id = $1`, guildID)
	return err
}

type Caught struct {
	UserID int64
	Action string
	Name   string
}

func (r *Repo) AddRaidCaught(ctx context.Context, guildID int64, c Caught) error {
	_, err := r.db.ExecContext(ctx, `
		INSERT INTO security_raid_caught (guild_id, user_id, action, name) VALUES ($1, $2, $3, $4)
		ON CONFLICT (guild_id, user_id) DO NOTHING`, guildID, c.UserID, c.Action, c.Name)
	return err
}

func (r *Repo) ListRaidCaught(ctx context.Context, guildID int64, limit int) ([]Caught, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT user_id, action, name FROM security_raid_caught WHERE guild_id = $1 ORDER BY caught_at LIMIT $2`, guildID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Caught
	for rows.Next() {
		var c Caught
		if err := rows.Scan(&c.UserID, &c.Action, &c.Name); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// ---- Snapshots ----

const keepSnapshots = 3

type Snapshot struct {
	ID        int64
	GuildID   int64
	Data      []byte
	Roles     int
	Channels  int
	CreatedAt time.Time
}

// SaveSnapshot stores a snapshot and drops all but the newest three.
func (r *Repo) SaveSnapshot(ctx context.Context, s Snapshot) error {
	if _, err := r.db.ExecContext(ctx, `
		INSERT INTO security_snapshots (guild_id, data, roles, channels) VALUES ($1, $2, $3, $4)`,
		s.GuildID, s.Data, s.Roles, s.Channels); err != nil {
		return err
	}
	_, err := r.db.ExecContext(ctx, `
		DELETE FROM security_snapshots WHERE guild_id = $1 AND id NOT IN (
			SELECT id FROM security_snapshots WHERE guild_id = $1 ORDER BY created_at DESC, id DESC LIMIT $2)`,
		s.GuildID, keepSnapshots)
	return err
}

// LatestSnapshot returns nil when the guild has none.
func (r *Repo) LatestSnapshot(ctx context.Context, guildID int64) (*Snapshot, error) {
	var s Snapshot
	err := r.db.QueryRowContext(ctx, `
		SELECT id, guild_id, data, roles, channels, created_at FROM security_snapshots
		WHERE guild_id = $1 ORDER BY created_at DESC, id DESC LIMIT 1`, guildID).
		Scan(&s.ID, &s.GuildID, &s.Data, &s.Roles, &s.Channels, &s.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &s, nil
}

// GuildsDueForSnapshot lists guilds with anti-nuke on whose newest snapshot
// is older than maxAge (or that have none).
func (r *Repo) GuildsDueForSnapshot(ctx context.Context, maxAge time.Duration) ([]int64, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT s.guild_id FROM security_settings s
		WHERE s.antinuke_enabled AND NOT EXISTS (
			SELECT 1 FROM security_snapshots p WHERE p.guild_id = s.guild_id AND p.created_at > NOW() - make_interval(secs => $1))`,
		maxAge.Seconds())
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []int64
	for rows.Next() {
		var g int64
		if err := rows.Scan(&g); err != nil {
			return nil, err
		}
		out = append(out, g)
	}
	return out, rows.Err()
}

// ListEvents returns a server's most recent security events, newest first.
func (r *Repo) ListEvents(ctx context.Context, guildID int64, limit int) ([]Event, error) {
	if limit < 1 || limit > 100 {
		limit = 20
	}
	rows, err := r.db.QueryContext(ctx, `
		SELECT kind, COALESCE(actor_id, 0), COALESCE(target_id, 0), detail, created_at
		FROM security_events WHERE guild_id = $1 ORDER BY id DESC LIMIT $2`, guildID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Event
	for rows.Next() {
		e := Event{GuildID: guildID}
		if err := rows.Scan(&e.Kind, &e.ActorID, &e.TargetID, &e.Detail, &e.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, e)
	}
	return out, rows.Err()
}