internet_use_control/internal/store/alerts.go

// Alerts (AGENTS.md §4 FR-13, §7 retention, §11 Phase 9).
//
// An alert is a message the Control Service decided Sam should see: a device
// hit its limit (a blocking window turned on), or a device has been offline too
// long. The engine (internal/alert) raises them and delivers them through
// Apprise; this file only records what happened.
//
// `alerts` is append-only except for the delivery outcome, so an operator can
// always tell what fired and whether it reached Sam. `alert_state` is the small
// per-device dedup memory that turns a transition into exactly one alert.
package store

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

// Alert kinds. 'limit' is a device that became blocked, 'offline' is a device
// unreachable for too long, 'tamper' is a kids' client that reported its own
// enforcement was bypassed (FR-17), 'notify' is an accepted notification
// delivered on demand.
const (
	AlertLimit   = "limit"
	AlertOffline = "offline"
	AlertTamper  = "tamper"
	AlertNotify  = "notify"
)

// Alert is one recorded alert.
type Alert struct {
	ID          int64
	DeviceID    *int64
	DeviceName  string
	Kind        string
	Message     string
	CreatedAt   time.Time
	Delivered   bool
	DeliveredAt *time.Time
	Error       string
}

// AlertState is the last observed condition for one device. It exists only to
// suppress duplicate alerts while a condition stays true.
type AlertState struct {
	Locked  bool
	Offline bool
}

// CreateAlert records one alert and appends it to the audit log in the same
// transaction (FR-15). deviceID may be nil for a notification with no device.
func (s *Store) CreateAlert(ctx context.Context, deviceID *int64, kind, message string) (*Alert, error) {
	switch kind {
	case AlertLimit, AlertOffline, AlertTamper, AlertNotify:
	default:
		return nil, fmt.Errorf("create alert: unknown kind %q", kind)
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, fmt.Errorf("create alert: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	var idArg any
	if deviceID != nil {
		idArg = *deviceID
	}
	res, err := tx.ExecContext(ctx,
		`INSERT INTO alerts (device_id, kind, message) VALUES (?, ?, ?)`,
		idArg, kind, message)
	if err != nil {
		return nil, fmt.Errorf("create alert: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("create alert id: %w", err)
	}
	if err := insertAudit(ctx, tx, "alert", "alert."+kind, deviceID, message); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, fmt.Errorf("commit alert: %w", err)
	}
	return s.GetAlert(ctx, id)
}

// MarkAlertDelivered records the delivery outcome. An empty errMsg means Apprise
// accepted the notification.
func (s *Store) MarkAlertDelivered(ctx context.Context, id int64, errMsg string) error {
	delivered := 0
	if errMsg == "" {
		delivered = 1
	}
	if _, err := s.db.ExecContext(ctx, `
		UPDATE alerts SET delivered = ?, delivered_at = datetime('now'), error = ?
		WHERE id = ?`, delivered, nullString(errMsg), id); err != nil {
		return fmt.Errorf("mark alert delivered: %w", err)
	}
	return nil
}

// GetAlert returns one alert, or ErrNotFound.
func (s *Store) GetAlert(ctx context.Context, id int64) (*Alert, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT a.id, a.device_id, COALESCE(d.name, ''), a.kind, a.message,
		       a.created_at, a.delivered, a.delivered_at, COALESCE(a.error, '')
		FROM alerts a
		LEFT JOIN devices d ON d.id = a.device_id
		WHERE a.id = ?`, id)
	a, err := scanAlert(row)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get alert: %w", err)
	}
	return a, nil
}

// ListAlerts returns the newest alerts first, for the Alerts screen and for
// verification. A non-positive or oversized limit is clamped.
func (s *Store) ListAlerts(ctx context.Context, limit int) ([]Alert, error) {
	if limit <= 0 || limit > 500 {
		limit = 50
	}
	rows, err := s.db.QueryContext(ctx, `
		SELECT a.id, a.device_id, COALESCE(d.name, ''), a.kind, a.message,
		       a.created_at, a.delivered, a.delivered_at, COALESCE(a.error, '')
		FROM alerts a
		LEFT JOIN devices d ON d.id = a.device_id
		ORDER BY a.id DESC
		LIMIT ?`, limit)
	if err != nil {
		return nil, fmt.Errorf("list alerts: %w", err)
	}
	defer rows.Close()

	out := make([]Alert, 0, limit)
	for rows.Next() {
		a, err := scanAlert(rows)
		if err != nil {
			return nil, fmt.Errorf("scan alert: %w", err)
		}
		out = append(out, *a)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("list alerts: %w", err)
	}
	return out, nil
}

// AlertStates returns the dedup state for every device that has one. A device
// with no row is a device the engine has not seen yet.
func (s *Store) AlertStates(ctx context.Context) (map[int64]AlertState, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT device_id, locked, offline FROM alert_state`)
	if err != nil {
		return nil, fmt.Errorf("alert states: %w", err)
	}
	defer rows.Close()

	out := make(map[int64]AlertState)
	for rows.Next() {
		var (
			id            int64
			locked, offln int
		)
		if err := rows.Scan(&id, &locked, &offln); err != nil {
			return nil, fmt.Errorf("scan alert state: %w", err)
		}
		out[id] = AlertState{Locked: locked != 0, Offline: offln != 0}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("alert states: %w", err)
	}
	return out, nil
}

// SetAlertState records the last observed condition for one device.
func (s *Store) SetAlertState(ctx context.Context, deviceID int64, locked, offline bool) error {
	if _, err := s.db.ExecContext(ctx, `
		INSERT INTO alert_state (device_id, locked, offline, updated_at)
		VALUES (?, ?, ?, datetime('now'))
		ON CONFLICT(device_id) DO UPDATE SET
			locked = excluded.locked,
			offline = excluded.offline,
			updated_at = datetime('now')`,
		deviceID, boolToInt(locked), boolToInt(offline)); err != nil {
		return fmt.Errorf("set alert state: %w", err)
	}
	return nil
}

func scanAlert(row interface {
	Scan(dest ...any) error
}) (*Alert, error) {
	var (
		a           Alert
		deviceID    sql.NullInt64
		created     sql.NullString
		delivered   int
		deliveredAt sql.NullString
	)
	if err := row.Scan(&a.ID, &deviceID, &a.DeviceName, &a.Kind, &a.Message,
		&created, &delivered, &deliveredAt, &a.Error); err != nil {
		return nil, err
	}
	if deviceID.Valid {
		id := deviceID.Int64
		a.DeviceID = &id
	}
	a.CreatedAt = parseTime(created.String)
	a.Delivered = delivered != 0
	a.DeliveredAt = parseTimePtr(deliveredAt)
	return &a, nil
}

// nullString maps an empty message to SQL NULL, so an undelivered-without-error
// row is distinguishable from a delivered one.
func nullString(s string) any {
	if s == "" {
		return nil
	}
	return s
}