pangolin/server/internal/admin/store.go

package admin

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"
)

// ErrAdminNotFound is returned when no admin row matches a lookup.
var ErrAdminNotFound = errors.New("admin: not found")

// Store is the data-access surface the admin backend needs. It is an interface
// so handlers and the authenticator can be unit-tested with a fake; the
// production implementation (DBStore) is backed by MySQL.
type Store interface {
	// Admin identity.
	GetAdminByUsername(ctx context.Context, username string) (*Admin, error)
	CreateAdmin(ctx context.Context, username, pwHash string, totpSecretEnc []byte) (int64, error)
	UpdateLastLogin(ctx context.Context, id int64, at time.Time) error

	// Node catalogue (read-only here; mutations go through the #5/#14 services).
	ListNodes(ctx context.Context, eventsPerNode int) ([]NodeRow, error)
	GetNode(ctx context.Context, id int64) (*NodeRow, error)

	// Audit & events.
	WriteAudit(ctx context.Context, actor, action, target, metaJSON string) error
	QueryAudit(ctx context.Context, f AuditFilter) ([]AuditEntry, int, error)
	QueryNodeEvents(ctx context.Context, nodeID int64, limit int) ([]NodeEvent, error)
}

// DBStore implements Store over MySQL.
type DBStore struct {
	db *sql.DB
}

// NewDBStore creates a DBStore.
func NewDBStore(db *sql.DB) *DBStore { return &DBStore{db: db} }

// GetAdminByUsername loads an admin by username.
func (s *DBStore) GetAdminByUsername(ctx context.Context, username string) (*Admin, error) {
	row := s.db.QueryRowContext(ctx,
		`SELECT id, username, pw_hash, totp_secret, status, created_at, last_login_at
		   FROM admins WHERE username = ?`, username)
	var a Admin
	var last sql.NullTime
	err := row.Scan(&a.ID, &a.Username, &a.PwHash, &a.TOTPSecretEnc, &a.Status, &a.CreatedAt, &last)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrAdminNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("admin.GetAdminByUsername: %w", err)
	}
	if last.Valid {
		a.LastLoginAt = &last.Time
	}
	return &a, nil
}

// CreateAdmin inserts a new admin and returns its id.
func (s *DBStore) CreateAdmin(ctx context.Context, username, pwHash string, totpSecretEnc []byte) (int64, error) {
	res, err := s.db.ExecContext(ctx,
		`INSERT INTO admins (username, pw_hash, totp_secret, status, created_at)
		 VALUES (?, ?, ?, 'active', ?)`,
		username, pwHash, totpSecretEnc, time.Now().UTC())
	if err != nil {
		return 0, fmt.Errorf("admin.CreateAdmin: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("admin.CreateAdmin last id: %w", err)
	}
	return id, nil
}

// UpdateLastLogin records a successful login time.
func (s *DBStore) UpdateLastLogin(ctx context.Context, id int64, at time.Time) error {
	_, err := s.db.ExecContext(ctx,
		`UPDATE admins SET last_login_at = ? WHERE id = ?`, at.UTC(), id)
	if err != nil {
		return fmt.Errorf("admin.UpdateLastLogin: %w", err)
	}
	return nil
}

// ListNodes returns all nodes with up to eventsPerNode recent events each.
func (s *DBStore) ListNodes(ctx context.Context, eventsPerNode int) ([]NodeRow, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT n.id, n.uuid, n.region, n.name_zh, n.name_en, n.role, n.tier,
		        n.endpoint, n.status, n.weight, pr.name
		   FROM nodes n
		   JOIN providers pr ON pr.id = n.provider_id
		  ORDER BY n.id`)
	if err != nil {
		return nil, fmt.Errorf("admin.ListNodes: %w", err)
	}
	defer rows.Close()

	var out []NodeRow
	for rows.Next() {
		var n NodeRow
		if err := rows.Scan(&n.ID, &n.UUID, &n.Region, &n.NameZH, &n.NameEN, &n.Role,
			&n.Tier, &n.Endpoint, &n.Status, &n.Weight, &n.Provider); err != nil {
			return nil, fmt.Errorf("admin.ListNodes scan: %w", err)
		}
		out = append(out, n)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if eventsPerNode > 0 {
		for i := range out {
			ev, err := s.QueryNodeEvents(ctx, out[i].ID, eventsPerNode)
			if err != nil {
				return nil, err
			}
			out[i].RecentEvents = ev
		}
	}
	return out, nil
}

// GetNode loads a single node by id.
func (s *DBStore) GetNode(ctx context.Context, id int64) (*NodeRow, error) {
	row := s.db.QueryRowContext(ctx,
		`SELECT n.id, n.uuid, n.region, n.name_zh, n.name_en, n.role, n.tier,
		        n.endpoint, n.status, n.weight, pr.name
		   FROM nodes n
		   JOIN providers pr ON pr.id = n.provider_id
		  WHERE n.id = ?`, id)
	var n NodeRow
	err := row.Scan(&n.ID, &n.UUID, &n.Region, &n.NameZH, &n.NameEN, &n.Role,
		&n.Tier, &n.Endpoint, &n.Status, &n.Weight, &n.Provider)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("admin.GetNode: %w", err)
	}
	return &n, nil
}

// WriteAudit inserts an audit_log row.
func (s *DBStore) WriteAudit(ctx context.Context, actor, action, target, metaJSON string) error {
	if metaJSON == "" {
		metaJSON = "null"
	}
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO audit_log (actor, action, target, meta, at)
		 VALUES (?, ?, ?, ?, ?)`,
		actor, action, target, metaJSON, time.Now().UTC())
	if err != nil {
		return fmt.Errorf("admin.WriteAudit: %w", err)
	}
	return nil
}

// QueryAudit returns filtered audit_log entries plus the total match count.
func (s *DBStore) QueryAudit(ctx context.Context, f AuditFilter) ([]AuditEntry, int, error) {
	where := []string{"1=1"}
	args := []any{}
	if f.Actor != "" {
		where = append(where, "actor = ?")
		args = append(args, f.Actor)
	}
	if f.Action != "" {
		where = append(where, "action = ?")
		args = append(args, f.Action)
	}
	if f.Target != "" {
		where = append(where, "target LIKE ?")
		args = append(args, "%"+f.Target+"%")
	}
	if f.From != nil {
		where = append(where, "at >= ?")
		args = append(args, f.From.UTC())
	}
	if f.To != nil {
		where = append(where, "at <= ?")
		args = append(args, f.To.UTC())
	}
	clause := strings.Join(where, " AND ")

	var total int
	if err := s.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM audit_log WHERE `+clause, args...).Scan(&total); err != nil {
		return nil, 0, fmt.Errorf("admin.QueryAudit count: %w", err)
	}

	limit := f.Limit
	if limit <= 0 || limit > 500 {
		limit = 50
	}
	q := `SELECT id, actor, action, target, COALESCE(meta, ''), at
	        FROM audit_log WHERE ` + clause + ` ORDER BY at DESC, id DESC LIMIT ? OFFSET ?`
	args = append(args, limit, f.Offset)
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("admin.QueryAudit: %w", err)
	}
	defer rows.Close()

	var out []AuditEntry
	for rows.Next() {
		var e AuditEntry
		var meta []byte
		if err := rows.Scan(&e.ID, &e.Actor, &e.Action, &e.Target, &meta, &e.At); err != nil {
			return nil, 0, fmt.Errorf("admin.QueryAudit scan: %w", err)
		}
		e.Meta = string(meta)
		out = append(out, e)
	}
	return out, total, rows.Err()
}

// QueryNodeEvents returns the most recent events for a node.
func (s *DBStore) QueryNodeEvents(ctx context.Context, nodeID int64, limit int) ([]NodeEvent, error) {
	if limit <= 0 || limit > 200 {
		limit = 20
	}
	rows, err := s.db.QueryContext(ctx,
		`SELECT id, node_id, event, COALESCE(detail, ''), at
		   FROM node_events WHERE node_id = ? ORDER BY at DESC, id DESC LIMIT ?`,
		nodeID, limit)
	if err != nil {
		return nil, fmt.Errorf("admin.QueryNodeEvents: %w", err)
	}
	defer rows.Close()

	var out []NodeEvent
	for rows.Next() {
		var e NodeEvent
		var detail []byte
		if err := rows.Scan(&e.ID, &e.NodeID, &e.Event, &detail, &e.At); err != nil {
			return nil, fmt.Errorf("admin.QueryNodeEvents scan: %w", err)
		}
		e.Detail = string(detail)
		out = append(out, e)
	}
	return out, rows.Err()
}