Prism/internal/store/store.go

package store

import (
	"context"
	"database/sql"
	"embed"
	"fmt"
	"os"
	"path/filepath"
	"time"

	"github.com/pressly/goose/v3"
	_ "modernc.org/sqlite"
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

type Store struct {
	db *sql.DB
}

func Open(dataDir string) (*Store, error) {
	if err := os.MkdirAll(dataDir, 0o755); err != nil {
		return nil, err
	}
	dbPath := filepath.Join(dataDir, "prism.db")
	db, err := sql.Open("sqlite", dbPath+"?_pragma=foreign_keys(1)&_pragma=journal_mode(WAL)")
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	s := &Store{db: db}
	if err := s.migrate(); err != nil {
		db.Close()
		return nil, err
	}
	return s, nil
}

func (s *Store) DB() *sql.DB { return s.db }

func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	goose.SetBaseFS(migrationsFS)
	if err := goose.SetDialect("sqlite3"); err != nil {
		return err
	}
	return goose.Up(s.db, "migrations")
}

func (s *Store) GetSetting(ctx context.Context, key string) (string, error) {
	var value string
	err := s.db.QueryRowContext(ctx, `SELECT value FROM settings WHERE key = ?`, key).Scan(&value)
	return value, err
}

func (s *Store) GetSettings(ctx context.Context) (map[string]string, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT key, value FROM settings`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]string)
	for rows.Next() {
		var k, v string
		if err := rows.Scan(&k, &v); err != nil {
			return nil, err
		}
		out[k] = v
	}
	return out, rows.Err()
}

func (s *Store) SetSettings(ctx context.Context, settings map[string]string) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for k, v := range settings {
		if _, err := tx.ExecContext(ctx, `INSERT INTO settings(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value`, k, v); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *Store) AddAuditLog(ctx context.Context, action, resource, detail string) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO audit_logs(action,resource,detail) VALUES(?,?,?)`, action, resource, detail)
	return err
}

func (s *Store) CountAuditLogs(ctx context.Context) (int, error) {
	var n int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM audit_logs`).Scan(&n)
	return n, err
}

func (s *Store) ListAuditLogs(ctx context.Context, limit, offset int) ([]AuditLog, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id,action,resource,detail,created_at FROM audit_logs ORDER BY id DESC LIMIT ? OFFSET ?`, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []AuditLog
	for rows.Next() {
		var l AuditLog
		var created string
		if err := rows.Scan(&l.ID, &l.Action, &l.Resource, &l.Detail, &created); err != nil {
			return nil, err
		}
		l.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", created)
		out = append(out, l)
	}
	return out, rows.Err()
}

func (s *Store) AddRequestLog(ctx context.Context, level, source, message string) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO request_logs(level,source,message) VALUES(?,?,?)`, level, source, message)
	return err
}

func (s *Store) ListRequestLogs(ctx context.Context, level, keyword string, limit, offset int) ([]RequestLog, int, error) {
	query := `SELECT id,level,source,message,created_at FROM request_logs WHERE 1=1`
	countQuery := `SELECT COUNT(*) FROM request_logs WHERE 1=1`
	args := []any{}
	if level != "" {
		query += ` AND level = ?`
		countQuery += ` AND level = ?`
		args = append(args, level)
	}
	if keyword != "" {
		query += ` AND message LIKE ?`
		countQuery += ` AND message LIKE ?`
		args = append(args, "%"+keyword+"%")
	}
	var total int
	if err := s.db.QueryRowContext(ctx, countQuery, args...).Scan(&total); err != nil {
		return nil, 0, err
	}
	query += ` ORDER BY id DESC LIMIT ? OFFSET ?`
	args = append(args, limit, offset)
	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var out []RequestLog
	for rows.Next() {
		var l RequestLog
		var created string
		if err := rows.Scan(&l.ID, &l.Level, &l.Source, &l.Message, &created); err != nil {
			return nil, 0, err
		}
		l.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", created)
		out = append(out, l)
	}
	return out, total, rows.Err()
}

func (s *Store) PruneRequestLogs(ctx context.Context, keep int) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM request_logs WHERE id NOT IN (SELECT id FROM request_logs ORDER BY id DESC LIMIT ?)`, keep)
	return err
}

func (s *Store) AddTrafficSnapshot(ctx context.Context, up, down, conns int64) error {
	_, err := s.db.ExecContext(ctx, `INSERT INTO traffic_snapshots(upload_rate,download_rate,connections) VALUES(?,?,?)`, up, down, conns)
	return err
}

func (s *Store) ListTrafficSnapshots(ctx context.Context, limit int) ([]map[string]any, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT upload_rate,download_rate,connections,created_at FROM traffic_snapshots ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []map[string]any
	for rows.Next() {
		var up, down, conns int64
		var created string
		if err := rows.Scan(&up, &down, &conns, &created); err != nil {
			return nil, err
		}
		out = append(out, map[string]any{
			"upload_rate":    up,
			"download_rate":  down,
			"connections":    conns,
			"created_at":     created,
		})
	}
	return out, rows.Err()
}

func parseTimePtr(s sql.NullString) *time.Time {
	if !s.Valid || s.String == "" {
		return nil
	}
	t, err := time.Parse("2006-01-02 15:04:05", s.String)
	if err != nil {
		return nil
	}
	return &t
}

func parseTime(s string) time.Time {
	t, _ := time.Parse("2006-01-02 15:04:05", s)
	return t
}

func boolFromInt(v int) bool { return v != 0 }
func intFromBool(v bool) int {
	if v {
		return 1
	}
	return 0
}

func RuntimeName(prefix string, id int64, name string) string {
	return fmt.Sprintf("%s%d_%s", prefix, id, sanitizeName(name))
}

func sanitizeName(name string) string {
	out := make([]rune, 0, len(name))
	for _, r := range name {
		switch {
		case r >= 'a' && r <= 'z', r >= 'A' && r <= 'Z', r >= '0' && r <= '9', r == '_', r == '-':
			out = append(out, r)
		default:
			out = append(out, '_')
		}
	}
	if len(out) == 0 {
		return "node"
	}
	return string(out)
}