go-bananas/internal/store/repositories.go

package store

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

	"github.com/urpi/go-bananas/internal/domain"
)

// ------------------------------------------------------------------- users --

// UpsertUser records a successful login. Roles are overwritten from the
// identity provider on every login rather than merged, so that a user who is
// removed from a group actually loses access.
func (s *Store) UpsertUser(ctx context.Context, identity *domain.Identity) error {
	if identity == nil || identity.Subject == "" {
		return fmt.Errorf("%w: identity subject must not be empty", domain.ErrInvalid)
	}
	roles, err := json.Marshal(identity.Roles)
	if err != nil {
		return fmt.Errorf("encode roles: %w", err)
	}

	now := s.timestamp()
	_, err = s.db.ExecContext(ctx, `
		INSERT INTO users (subject, email, display_name, roles, method, first_seen, last_login)
		VALUES (?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(subject) DO UPDATE SET
			email        = excluded.email,
			display_name = excluded.display_name,
			roles        = excluded.roles,
			method       = excluded.method,
			last_login   = excluded.last_login`,
		identity.Subject, identity.Email, identity.Name, string(roles), identity.Method, now, now,
	)
	return translateError(err)
}

// User is a stored account projection.
type User struct {
	Subject   string    `json:"subject"`
	Email     string    `json:"email,omitempty"`
	Name      string    `json:"name,omitempty"`
	Roles     []string  `json:"roles"`
	Method    string    `json:"method"`
	FirstSeen time.Time `json:"firstSeen"`
	LastLogin time.Time `json:"lastLogin"`
}

// ListUsers returns every known account, most recently seen first.
func (s *Store) ListUsers(ctx context.Context, limit int) ([]User, error) {
	if limit <= 0 || limit > 500 {
		limit = 100
	}
	rows, err := s.db.QueryContext(ctx, `
		SELECT subject, email, display_name, roles, method, first_seen, last_login
		FROM users ORDER BY last_login DESC LIMIT ?`, limit)
	if err != nil {
		return nil, fmt.Errorf("list users: %w", translateError(err))
	}
	defer rows.Close()

	out := make([]User, 0, limit)
	for rows.Next() {
		var (
			u         User
			rolesJSON string
			firstSeen string
			lastLogin string
		)
		if err := rows.Scan(&u.Subject, &u.Email, &u.Name, &rolesJSON, &u.Method, &firstSeen, &lastLogin); err != nil {
			return nil, fmt.Errorf("scan user: %w", err)
		}
		// Deliberately degraded, not accidentally ignored: a malformed roles blob
		// yields a user with no roles, which the authorisation layer treats as
		// read-only. Failing the whole listing because one row has a corrupt column
		// would be worse than degrading that single user, and it would mean one bad
		// row hides every other user from the admin.
		//
		//nolint:errcheck // see above
		_ = json.Unmarshal([]byte(rolesJSON), &u.Roles)
		if u.Roles == nil {
			u.Roles = []string{}
		}
		if u.FirstSeen, err = parseTime(firstSeen); err != nil {
			return nil, err
		}
		if u.LastLogin, err = parseTime(lastLogin); err != nil {
			return nil, err
		}
		out = append(out, u)
	}
	return out, rows.Err()
}

// UserCount returns the number of known accounts.
func (s *Store) UserCount(ctx context.Context) (int, error) {
	var count int
	if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count); err != nil {
		return 0, fmt.Errorf("count users: %w", err)
	}
	return count, nil
}

// ---------------------------------------------------------------- settings --

// Setting returns a setting value, or fallback when it is absent.
func (s *Store) Setting(ctx context.Context, key, fallback string) (string, error) {
	var value string
	err := s.db.QueryRowContext(ctx, `SELECT value FROM settings WHERE key = ?`, key).Scan(&value)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return fallback, nil
	case err != nil:
		return "", fmt.Errorf("read setting %s: %w", key, err)
	default:
		return value, nil
	}
}

// SetSetting writes a setting.
func (s *Store) SetSetting(ctx context.Context, key, value, actor string) error {
	_, err := s.db.ExecContext(ctx, `
		INSERT INTO settings (key, value, updated_at, updated_by) VALUES (?, ?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET
			value = excluded.value, updated_at = excluded.updated_at, updated_by = excluded.updated_by`,
		key, value, s.timestamp(), actor)
	return translateError(err)
}

// AllSettings returns every setting.
func (s *Store) AllSettings(ctx context.Context) (map[string]domain.Setting, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT key, value, updated_at, updated_by FROM settings`)
	if err != nil {
		return nil, fmt.Errorf("list settings: %w", translateError(err))
	}
	defer rows.Close()

	out := make(map[string]domain.Setting)
	for rows.Next() {
		var (
			setting   domain.Setting
			updatedAt string
		)
		if err := rows.Scan(&setting.Key, &setting.Value, &updatedAt, &setting.UpdatedBy); err != nil {
			return nil, fmt.Errorf("scan setting: %w", err)
		}
		if setting.UpdatedAt, err = parseTime(updatedAt); err != nil {
			return nil, err
		}
		out[setting.Key] = setting
	}
	return out, rows.Err()
}

// ------------------------------------------------------------------- audit --

// AuditFilter narrows the activity log.
type AuditFilter struct {
	Actor      string
	Action     string
	EntityType string
	EntityID   string
	Since      *time.Time
	Limit      int
	Offset     int
}

// AppendAudit writes one audit entry. Failures are returned but callers on the
// request path generally log and continue: losing an audit row is bad, failing
// a user's publish because of it is worse.
func (s *Store) AppendAudit(ctx context.Context, entry *domain.AuditEntry) error {
	if entry == nil {
		return fmt.Errorf("%w: audit entry must not be nil", domain.ErrInvalid)
	}
	if entry.Action == "" {
		return fmt.Errorf("%w: audit entry requires an action", domain.ErrInvalid)
	}
	if entry.At.IsZero() {
		entry.At = s.now().UTC()
	}

	detail, err := json.Marshal(entry.Detail)
	if err != nil {
		detail = []byte("{}")
	}

	_, err = s.db.ExecContext(ctx, `
		INSERT INTO audit_log (at, actor, actor_email, action, entity_type, entity_id, summary, detail_json, remote_ip)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		entry.At.UTC().Format(domain.TimeFormat), entry.Actor, entry.ActorEmail,
		string(entry.Action), entry.EntityType, entry.EntityID, entry.Summary,
		string(detail), entry.RemoteIP,
	)
	return translateError(err)
}

// ListAudit returns audit entries newest first.
func (s *Store) ListAudit(ctx context.Context, filter AuditFilter) ([]domain.AuditEntry, int, error) {
	var (
		where []string
		args  []any
	)
	if filter.Actor != "" {
		where = append(where, `actor_email LIKE ?`)
		args = append(args, "%"+filter.Actor+"%")
	}
	if filter.Action != "" {
		where = append(where, `action = ?`)
		args = append(args, filter.Action)
	}
	if filter.EntityType != "" {
		where = append(where, `entity_type = ?`)
		args = append(args, filter.EntityType)
	}
	if filter.EntityID != "" {
		where = append(where, `entity_id = ?`)
		args = append(args, filter.EntityID)
	}
	if filter.Since != nil {
		where = append(where, `at >= ?`)
		args = append(args, filter.Since.UTC().Format(domain.TimeFormat))
	}

	clause := ""
	if len(where) > 0 {
		clause = " WHERE " + strings.Join(where, " AND ")
	}

	limit := filter.Limit
	if limit <= 0 || limit > 500 {
		limit = 50
	}
	offset := filter.Offset
	if offset < 0 {
		offset = 0
	}

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

	rows, err := s.db.QueryContext(ctx,
		`SELECT id, at, actor, actor_email, action, entity_type, entity_id, summary, detail_json, remote_ip
		 FROM audit_log`+clause+` ORDER BY id DESC LIMIT ? OFFSET ?`,
		append(append([]any{}, args...), limit, offset)...)
	if err != nil {
		return nil, 0, fmt.Errorf("list audit entries: %w", translateError(err))
	}
	defer rows.Close()

	out := make([]domain.AuditEntry, 0, limit)
	for rows.Next() {
		var (
			entry      domain.AuditEntry
			action     string
			at         string
			detailJSON string
		)
		if err := rows.Scan(&entry.ID, &at, &entry.Actor, &entry.ActorEmail, &action,
			&entry.EntityType, &entry.EntityID, &entry.Summary, &detailJSON, &entry.RemoteIP); err != nil {
			return nil, 0, fmt.Errorf("scan audit entry: %w", err)
		}
		entry.Action = domain.AuditAction(action)
		if entry.At, err = parseTime(at); err != nil {
			return nil, 0, err
		}
		if detailJSON != "" {
			// Same reasoning as the roles column: an audit entry whose detail cannot
			// be decoded is still worth returning, because the point of the audit log
			// is that the record of the action exists.
			//
			//nolint:errcheck // see above
			_ = json.Unmarshal([]byte(detailJSON), &entry.Detail)
		}
		out = append(out, entry)
	}
	return out, total, rows.Err()
}

// AuditRetention returns the age of the oldest audit entry, used by the
// retention job.
func (s *Store) AuditRetention(ctx context.Context) (time.Time, error) {
	var oldest sql.NullString
	if err := s.db.QueryRowContext(ctx, `SELECT MIN(at) FROM audit_log`).Scan(&oldest); err != nil {
		return time.Time{}, fmt.Errorf("audit retention: %w", err)
	}
	if !oldest.Valid {
		return s.now().UTC(), nil
	}
	return parseTime(oldest.String)
}

// PurgeAudit removes entries older than the cutoff and returns the count.
func (s *Store) PurgeAudit(ctx context.Context, before time.Time) (int64, error) {
	result, err := s.db.ExecContext(ctx,
		`DELETE FROM audit_log WHERE at < ?`, before.UTC().Format(domain.TimeFormat))
	if err != nil {
		return 0, fmt.Errorf("purge audit: %w", translateError(err))
	}
	return result.RowsAffected()
}

// Stats summarises the instance for the admin dashboard.
type Stats struct {
	Themes          int            `json:"themes"`
	ThemesByStatus  map[string]int `json:"themesByStatus"`
	Users           int            `json:"users"`
	AuditEntries    int            `json:"auditEntries"`
	DatabaseSize    int64          `json:"databaseSizeBytes"`
	VersionsRetaind int            `json:"versionsRetained"`
}

// Stats gathers dashboard counters in a single pass.
func (s *Store) Stats(ctx context.Context) (Stats, error) {
	stats := Stats{ThemesByStatus: map[string]int{}}

	rows, err := s.db.QueryContext(ctx, `SELECT status, COUNT(*) FROM themes GROUP BY status`)
	if err != nil {
		return stats, fmt.Errorf("stats: %w", translateError(err))
	}
	defer rows.Close()
	for rows.Next() {
		var (
			status string
			count  int
		)
		if err := rows.Scan(&status, &count); err != nil {
			return stats, fmt.Errorf("stats: %w", err)
		}
		stats.ThemesByStatus[status] = count
		stats.Themes += count
	}
	// rows.Next() returning false means either no rows or an error. Checking Err
	// distinguishes them, and without it a failure part way through the result set
	// reports success with silently incomplete counters.
	if err := rows.Err(); err != nil {
		return stats, fmt.Errorf("stats: %w", err)
	}

	for _, query := range []struct {
		sql  string
		dest *int
	}{
		{`SELECT COUNT(*) FROM users`, &stats.Users},
		{`SELECT COUNT(*) FROM audit_log`, &stats.AuditEntries},
		{`SELECT COUNT(*) FROM theme_versions`, &stats.VersionsRetaind},
	} {
		if err := s.db.QueryRowContext(ctx, query.sql).Scan(query.dest); err != nil {
			return stats, fmt.Errorf("stats: %w", translateError(err))
		}
	}

	// page_count * page_size is the on-disk size of the main database file. It
	// is a cheap approximation, which is all a dashboard needs.
	var pageCount, pageSize int64
	if err := s.db.QueryRowContext(ctx, `PRAGMA page_count`).Scan(&pageCount); err == nil {
		if err := s.db.QueryRowContext(ctx, `PRAGMA page_size`).Scan(&pageSize); err == nil {
			stats.DatabaseSize = pageCount * pageSize
		}
	}
	return stats, nil
}