OrchestrAD/backend/internal/services/activity_service.go

// Package services - activity intelligence aggregation service.
//
// The activity service turns the raw rule_run_actions ledger into operator-
// friendly intelligence: how many syncs, operations and removals happened, to
// how many objects, and a drill-in feed answering what action ran, what
// happened, to which object, triggered by whom, and when.
package services

import (
	"database/sql"
	"strings"
	"time"

	"github.com/Grace-Solutions/OrchestrAD/internal/logging"
)

// Activity categories group the individual AD action types into the three
// buckets operators reason about.
const (
	CategorySync      = "Sync"      // membership additions (AddToGroup / AddGroupToGroup)
	CategoryOperation = "Operation" // structural changes (MoveToOu / EnsureGroupExists)
	CategoryRemoval   = "Removal"   // membership removals (RemoveFromGroupIfNoLongerMatched)
)

// syncActionTypes / removalActionTypes drive both the SQL categorisation and
// the Go-side helpers so the two never drift apart.
var (
	syncActionTypes    = []string{"AddToGroup", "AddGroupToGroup"}
	removalActionTypes = []string{"RemoveFromGroupIfNoLongerMatched"}
)

// categorize maps an action type to its activity category.
func categorize(actionType string) string {
	for _, a := range syncActionTypes {
		if a == actionType {
			return CategorySync
		}
	}
	for _, a := range removalActionTypes {
		if a == actionType {
			return CategoryRemoval
		}
	}
	return CategoryOperation
}

// ActivityWindow summarises action activity over a time window.
type ActivityWindow struct {
	Key             string `json:"key"`
	Label           string `json:"label"`
	Total           int    `json:"total"`
	Success         int    `json:"success"`
	Failed          int    `json:"failed"`
	Syncs           int    `json:"syncs"`
	Operations      int    `json:"operations"`
	Removals        int    `json:"removals"`
	ObjectsAffected int    `json:"objectsAffected"`
}

// ActionTypeCount is an all-time roll-up for a single action type.
type ActionTypeCount struct {
	ActionType string `json:"actionType"`
	Category   string `json:"category"`
	Total      int    `json:"total"`
	Success    int    `json:"success"`
	Failed     int    `json:"failed"`
}

// RuleActivityCount ranks rules by how much activity they generated.
type RuleActivityCount struct {
	RuleID   string `json:"ruleId"`
	RuleName string `json:"ruleName"`
	Total    int    `json:"total"`
	Failed   int    `json:"failed"`
}

// ActivitySummary is the composite response for the intelligence page header.
type ActivitySummary struct {
	Windows      []ActivityWindow    `json:"windows"`
	ByActionType []ActionTypeCount   `json:"byActionType"`
	TopRules     []RuleActivityCount `json:"topRules"`
	GeneratedUTC string              `json:"generatedUtc"`
}

// ActivityRecord is one row of the drill-in feed.
type ActivityRecord struct {
	ID           string  `json:"id"`
	RuleRunID    string  `json:"ruleRunId"`
	RuleID       string  `json:"ruleId"`
	RuleName     string  `json:"ruleName"`
	ObjectDN     string  `json:"objectDn"`
	ActionType   string  `json:"actionType"`
	Category     string  `json:"category"`
	Status       string  `json:"status"`
	DetailsJSON  *string `json:"detailsJson,omitempty"`
	ErrorMessage *string `json:"errorMessage,omitempty"`
	DurationMS   *int    `json:"durationMs,omitempty"`
	TriggeredBy  *string `json:"triggeredBy,omitempty"`
	CreatedUTC   string  `json:"createdUtc"`
}

// ActivityFilter narrows the drill-in feed.
type ActivityFilter struct {
	Category   string
	ActionType string
	Status     string
	RuleID     string
	Search     string
	Offset     int
	Limit      int
}

// ActivityService aggregates read-only intelligence from the action ledger.
type ActivityService struct {
	db     *sql.DB
	logger *logging.Logger
}

// NewActivityService creates a new ActivityService.
func NewActivityService(db *sql.DB, logger *logging.Logger) *ActivityService {
	return &ActivityService{db: db, logger: logger}
}

// caseSum builds a "COALESCE(SUM(CASE WHEN <cond> THEN 1 ELSE 0 END),0)" fragment.
func caseSum(cond string) string {
	return "COALESCE(SUM(CASE WHEN " + cond + " THEN 1 ELSE 0 END),0)"
}

// inList renders a quoted SQL IN(...) list from action-type constants. The
// values are compile-time constants, never user input, so inlining is safe.
func inList(values []string) string {
	quoted := make([]string, len(values))
	for i, v := range values {
		quoted[i] = "'" + v + "'"
	}
	return "(" + strings.Join(quoted, ",") + ")"
}

// Summary returns the composite intelligence payload.
func (s *ActivityService) Summary() (*ActivitySummary, error) {
	now := time.Now().UTC()
	out := &ActivitySummary{GeneratedUTC: now.Format(time.RFC3339)}

	windows := []struct {
		key, label string
		since      *time.Time
	}{
		{"24h", "Last 24 hours", ptrTime(now.Add(-24 * time.Hour))},
		{"7d", "Last 7 days", ptrTime(now.Add(-7 * 24 * time.Hour))},
		{"all", "All time", nil},
	}
	for _, w := range windows {
		win, err := s.loadWindow(w.key, w.label, w.since)
		if err != nil {
			return nil, err
		}
		out.Windows = append(out.Windows, win)
	}

	byType, err := s.loadByActionType()
	if err != nil {
		return nil, err
	}
	out.ByActionType = byType

	topRules, err := s.loadTopRules(8)
	if err != nil {
		return nil, err
	}
	out.TopRules = topRules

	return out, nil
}

func (s *ActivityService) loadWindow(key, label string, since *time.Time) (ActivityWindow, error) {
	win := ActivityWindow{Key: key, Label: label}
	query := `
		SELECT COUNT(*),
			` + caseSum("ra.status = 'Succeeded'") + `,
			` + caseSum("ra.status = 'Failed'") + `,
			` + caseSum("ra.action_type IN "+inList(syncActionTypes)) + `,
			` + caseSum("ra.action_type IN "+inList(removalActionTypes)) + `,
			COUNT(DISTINCT ra.object_dn)
		FROM rule_run_actions ra`
	var args []any
	if since != nil {
		query += ` WHERE ra.created_utc >= ?`
		args = append(args, since.Format(time.RFC3339))
	}
	var syncs, removals int
	if err := s.db.QueryRow(query, args...).Scan(
		&win.Total, &win.Success, &win.Failed, &syncs, &removals, &win.ObjectsAffected,
	); err != nil {
		return win, err
	}
	win.Syncs = syncs
	win.Removals = removals
	win.Operations = win.Total - syncs - removals
	return win, nil
}

func (s *ActivityService) loadByActionType() ([]ActionTypeCount, error) {
	rows, err := s.db.Query(`
		SELECT ra.action_type, COUNT(*),
			` + caseSum("ra.status = 'Succeeded'") + `,
			` + caseSum("ra.status = 'Failed'") + `
		FROM rule_run_actions ra
		GROUP BY ra.action_type
		ORDER BY COUNT(*) DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make([]ActionTypeCount, 0)
	for rows.Next() {
		var c ActionTypeCount
		if err := rows.Scan(&c.ActionType, &c.Total, &c.Success, &c.Failed); err != nil {
			return nil, err
		}
		c.Category = categorize(c.ActionType)
		out = append(out, c)
	}
	return out, rows.Err()
}

func (s *ActivityService) loadTopRules(limit int) ([]RuleActivityCount, error) {
	rows, err := s.db.Query(`
		SELECT r.id, r.name, COUNT(*),
			`+caseSum("ra.status = 'Failed'")+`
		FROM rule_run_actions ra
		JOIN rule_runs rr ON rr.id = ra.rule_run_id
		JOIN rules r ON r.id = rr.rule_id
		GROUP BY r.id, r.name
		ORDER BY COUNT(*) DESC
		LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	out := make([]RuleActivityCount, 0)
	for rows.Next() {
		var c RuleActivityCount
		if err := rows.Scan(&c.RuleID, &c.RuleName, &c.Total, &c.Failed); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// ListActions returns a filtered, paginated slice of the action feed plus the
// total matching count.
func (s *ActivityService) ListActions(f ActivityFilter) ([]ActivityRecord, int, error) {
	where := []string{"1=1"}
	var args []any

	switch f.Category {
	case CategorySync:
		where = append(where, "ra.action_type IN "+inList(syncActionTypes))
	case CategoryRemoval:
		where = append(where, "ra.action_type IN "+inList(removalActionTypes))
	case CategoryOperation:
		where = append(where, "ra.action_type NOT IN "+inList(append(append([]string{}, syncActionTypes...), removalActionTypes...)))
	}
	if f.ActionType != "" {
		where = append(where, "ra.action_type = ?")
		args = append(args, f.ActionType)
	}
	if f.Status != "" {
		where = append(where, "ra.status = ?")
		args = append(args, f.Status)
	}
	if f.RuleID != "" {
		where = append(where, "rr.rule_id = ?")
		args = append(args, f.RuleID)
	}
	if f.Search != "" {
		where = append(where, "ra.object_dn LIKE ?")
		args = append(args, "%"+f.Search+"%")
	}
	clause := strings.Join(where, " AND ")

	var total int
	countQuery := `
		SELECT COUNT(*)
		FROM rule_run_actions ra
		JOIN rule_runs rr ON rr.id = ra.rule_run_id
		JOIN rules r ON r.id = rr.rule_id
		WHERE ` + clause
	if err := s.db.QueryRow(countQuery, args...).Scan(&total); err != nil {
		return nil, 0, err
	}

	limit := f.Limit
	if limit <= 0 {
		limit = 50
	}
	listArgs := append(append([]any{}, args...), limit, f.Offset)
	rows, err := s.db.Query(`
		SELECT ra.id, ra.rule_run_id, rr.rule_id, r.name, ra.object_dn,
			ra.action_type, ra.status, ra.details_json, ra.error_message,
			ra.duration_ms, rr.triggered_by, ra.created_utc
		FROM rule_run_actions ra
		JOIN rule_runs rr ON rr.id = ra.rule_run_id
		JOIN rules r ON r.id = rr.rule_id
		WHERE `+clause+`
		ORDER BY ra.created_utc DESC
		LIMIT ? OFFSET ?`, listArgs...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]ActivityRecord, 0, limit)
	for rows.Next() {
		var rec ActivityRecord
		var details, errMsg, triggeredBy sql.NullString
		var duration sql.NullInt64
		if err := rows.Scan(
			&rec.ID, &rec.RuleRunID, &rec.RuleID, &rec.RuleName, &rec.ObjectDN,
			&rec.ActionType, &rec.Status, &details, &errMsg,
			&duration, &triggeredBy, &rec.CreatedUTC,
		); err != nil {
			return nil, 0, err
		}
		rec.Category = categorize(rec.ActionType)
		if details.Valid {
			v := details.String
			rec.DetailsJSON = &v
		}
		if errMsg.Valid {
			v := errMsg.String
			rec.ErrorMessage = &v
		}
		if duration.Valid {
			v := int(duration.Int64)
			rec.DurationMS = &v
		}
		if triggeredBy.Valid {
			v := triggeredBy.String
			rec.TriggeredBy = &v
		}
		out = append(out, rec)
	}
	return out, total, rows.Err()
}

func ptrTime(t time.Time) *time.Time { return &t }