housecarl/internal/store/bans.go

package store

import (
	"context"
	"database/sql"
	"errors"
	"time"

	"housecarl/internal/model"
)

const banCols = `id, list_id, address, protocol, port, expires_at, created_at, who, source, reason`

func scanBan(sc interface{ Scan(...any) error }, now time.Time) (*model.Ban, error) {
	b := &model.Ban{}
	var expires sql.NullString
	var created string
	if err := sc.Scan(&b.ID, &b.ListID, &b.Address, &b.Protocol, &b.Port, &expires, &created, &b.Who, &b.Source, &b.Reason); err != nil {
		return nil, err
	}
	b.Created = parseTS(created)
	b.Expires = parseTSPtr(expires)
	if b.Expires != nil {
		b.Timeout = max(int(b.Expires.Sub(now).Round(time.Second)/time.Second), 1)
	}
	return b, nil
}

// PutBan saves a ban, or renews the same ban (list, address, protocol
// and port) with its new end, who and why (an empty source or reason
// keeps the old). existing tells which; b gets
// its ID either way.
func (s *Store) PutBan(ctx context.Context, b *model.Ban) (existing bool, err error) {
	now := time.Now()
	err = s.db.QueryRowContext(ctx, `SELECT id FROM bans WHERE list_id = ? AND address = ? AND protocol = ? AND port = ?
		AND (expires_at IS NULL OR expires_at > ?)`, b.ListID, b.Address, b.Protocol, b.Port, ts(now)).Scan(&b.ID)
	existing = err == nil
	if err != nil && !errors.Is(err, sql.ErrNoRows) {
		return false, err
	}
	b.Created = now
	err = s.db.QueryRowContext(ctx, `INSERT INTO bans (list_id, address, protocol, port, expires_at, created_at, who, source, reason)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT (list_id, address, protocol, port) DO UPDATE SET expires_at = excluded.expires_at,
			who = excluded.who, source = COALESCE(NULLIF(excluded.source, ''), bans.source),
			reason = COALESCE(NULLIF(excluded.reason, ''), bans.reason),
			created_at = CASE WHEN bans.expires_at IS NULL OR bans.expires_at > excluded.created_at THEN bans.created_at ELSE excluded.created_at END
		RETURNING id, created_at`,
		b.ListID, b.Address, b.Protocol, b.Port, tsPtr(b.Expires), ts(now), b.Who, b.Source, b.Reason).Scan(&b.ID, new(string))
	return existing, err
}

// ListBans lists the active bans of a list (0: of all lists), oldest
// first.
func (s *Store) ListBans(ctx context.Context, listID int64) ([]*model.Ban, error) {
	now := time.Now()
	q := `SELECT ` + banCols + ` FROM bans WHERE (expires_at IS NULL OR expires_at > ?)`
	args := []any{ts(now)}
	if listID != 0 {
		q += ` AND list_id = ?`
		args = append(args, listID)
	}
	rows, err := s.db.QueryContext(ctx, q+` ORDER BY id`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []*model.Ban{}
	for rows.Next() {
		b, err := scanBan(rows, now)
		if err != nil {
			return nil, err
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

// GetBan loads an active ban.
func (s *Store) GetBan(ctx context.Context, id int64) (*model.Ban, error) {
	now := time.Now()
	b, err := scanBan(s.db.QueryRowContext(ctx, `SELECT `+banCols+` FROM bans WHERE id = ? AND (expires_at IS NULL OR expires_at > ?)`, id, ts(now)), now)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return b, err
}

// DeleteBan removes a ban.
func (s *Store) DeleteBan(ctx context.Context, id int64) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM bans WHERE id = ?`, id)
	return err
}

// CountBans counts a list's active bans.
func (s *Store) CountBans(ctx context.Context, listID int64) (int, error) {
	var n int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM bans WHERE list_id = ? AND (expires_at IS NULL OR expires_at > ?)`, listID, ts(time.Now())).Scan(&n)
	return n, err
}

// PruneBans deletes the bans that ended, and those of lists that no
// longer exist (lists names the ones that do).
func (s *Store) PruneBans(ctx context.Context, lists []int64) (int64, error) {
	res, err := s.db.ExecContext(ctx, `DELETE FROM bans WHERE expires_at <= ?`, ts(time.Now()))
	if err != nil {
		return 0, err
	}
	n, _ := res.RowsAffected()
	rows, err := s.db.QueryContext(ctx, `SELECT DISTINCT list_id FROM bans`)
	if err != nil {
		return n, err
	}
	var gone []int64
	keep := map[int64]bool{}
	for _, id := range lists {
		keep[id] = true
	}
	for rows.Next() {
		var id int64
		if rows.Scan(&id) == nil && !keep[id] {
			gone = append(gone, id)
		}
	}
	rows.Close()
	for _, id := range gone {
		res, err := s.db.ExecContext(ctx, `DELETE FROM bans WHERE list_id = ?`, id)
		if err != nil {
			return n, err
		}
		m, _ := res.RowsAffected()
		n += m
	}
	return n, nil
}