OrchestrAD/backend/internal/repository/rules.go

// Package repository - Rules repository
package repository

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

	"github.com/Grace-Solutions/OrchestrAD/internal/models"
	"github.com/google/uuid"
)

// RuleRepository handles rule database operations
type RuleRepository struct {
	db *sql.DB
}

// NewRuleRepository creates a new RuleRepository
func NewRuleRepository(db *sql.DB) *RuleRepository {
	return &RuleRepository{db: db}
}

// Create creates a new rule
func (r *RuleRepository) Create(rule *models.Rule) error {
	if rule.ID == "" {
		rule.ID = uuid.New().String()
	}
	now := time.Now().UTC()
	rule.CreatedUTC = now
	rule.UpdatedUTC = now

	_, err := r.db.Exec(`
		INSERT INTO rules (
			id, name, description, is_enabled, ad_connection_id,
			object_type, base_dn_override, search_scope_override,
			schedule_id, execution_mode, group_join_operator,
			max_parallelism, stop_on_error, created_utc, updated_utc
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`,
		rule.ID, rule.Name, rule.Description, boolToInt(rule.IsEnabled),
		rule.ADConnectionID, rule.ObjectType, rule.BaseDNOverride,
		rule.SearchScopeOverride, rule.ScheduleID, rule.ExecutionMode,
		rule.GroupJoinOperator, rule.MaxParallelism, boolToInt(rule.StopOnError),
		formatTime(rule.CreatedUTC), formatTime(rule.UpdatedUTC),
	)
	return err
}

// GetByID retrieves a rule by ID with its condition groups and actions
func (r *RuleRepository) GetByID(id string) (*models.Rule, error) {
	rule := &models.Rule{}
	var isEnabled, stopOnError int
	var lastRun, deleted sql.NullString
	var createdStr, updatedStr string

	err := r.db.QueryRow(`
		SELECT id, name, description, is_enabled, ad_connection_id,
			object_type, base_dn_override, search_scope_override,
			schedule_id, execution_mode, group_join_operator,
			max_parallelism, stop_on_error, last_run_utc, last_run_result,
			created_utc, updated_utc, deleted_utc
		FROM rules WHERE id = ? AND deleted_utc IS NULL
	`, id).Scan(
		&rule.ID, &rule.Name, &rule.Description, &isEnabled,
		&rule.ADConnectionID, &rule.ObjectType, &rule.BaseDNOverride,
		&rule.SearchScopeOverride, &rule.ScheduleID, &rule.ExecutionMode,
		&rule.GroupJoinOperator, &rule.MaxParallelism, &stopOnError,
		&lastRun, &rule.LastRunResult,
		&createdStr, &updatedStr, &deleted,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	rule.IsEnabled = intToBool(isEnabled)
	rule.StopOnError = intToBool(stopOnError)
	rule.LastRunUTC = parseNullTime(lastRun)
	rule.CreatedUTC = parseTimeOrZero(createdStr)
	rule.UpdatedUTC = parseTimeOrZero(updatedStr)
	rule.DeletedUTC = parseNullTime(deleted)

	// Load condition groups
	rule.ConditionGroups, err = r.getConditionGroups(id)
	if err != nil {
		return nil, err
	}

	// Load actions
	rule.Actions, err = r.getActions(id)
	if err != nil {
		return nil, err
	}

	return rule, nil
}

func (r *RuleRepository) getConditionGroups(ruleID string) ([]models.RuleConditionGroup, error) {
	// Read all groups first and close the cursor before loading conditions.
	// Loading conditions per-group while this cursor is still open would hold
	// one pooled connection and acquire a second, which deadlocks when the
	// pool is small; a single bulk conditions query avoids that entirely.
	rows, err := r.db.Query(`
		SELECT id, rule_id, name, is_enabled, join_operator,
			sort_order, negate, created_utc, updated_utc
		FROM rule_condition_groups
		WHERE rule_id = ? AND deleted_utc IS NULL
		ORDER BY sort_order
	`, ruleID)
	if err != nil {
		return nil, err
	}

	var groups []models.RuleConditionGroup
	groupIDs := []string{}
	for rows.Next() {
		var g models.RuleConditionGroup
		var isEnabled, negate int
		var createdStr, updatedStr string
		if err := rows.Scan(
			&g.ID, &g.RuleID, &g.Name, &isEnabled, &g.JoinOperator,
			&g.SortOrder, &negate, &createdStr, &updatedStr,
		); err != nil {
			rows.Close()
			return nil, err
		}
		g.IsEnabled = intToBool(isEnabled)
		g.Negate = intToBool(negate)
		g.CreatedUTC = parseTimeOrZero(createdStr)
		g.UpdatedUTC = parseTimeOrZero(updatedStr)
		groups = append(groups, g)
		groupIDs = append(groupIDs, g.ID)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return nil, err
	}
	rows.Close()

	byGroup, err := r.getConditionsForGroups(groupIDs)
	if err != nil {
		return nil, err
	}
	for i := range groups {
		groups[i].Conditions = byGroup[groups[i].ID]
	}
	return groups, nil
}

// getConditionsForGroups loads the conditions for every group in one query and
// returns them keyed by condition_group_id, preserving sort order.
func (r *RuleRepository) getConditionsForGroups(groupIDs []string) (map[string][]models.RuleCondition, error) {
	out := make(map[string][]models.RuleCondition, len(groupIDs))
	if len(groupIDs) == 0 {
		return out, nil
	}

	placeholders := make([]string, len(groupIDs))
	args := make([]any, len(groupIDs))
	for i, id := range groupIDs {
		placeholders[i] = "?"
		args[i] = id
	}
	query := `
		SELECT id, condition_group_id, is_enabled, attribute_name,
			operator, value_type, comparison_value, custom_ldap_expression,
			negate, sort_order, case_sensitive, created_utc, updated_utc
		FROM rule_conditions
		WHERE condition_group_id IN (` + strings.Join(placeholders, ",") + `) AND deleted_utc IS NULL
		ORDER BY sort_order`

	rows, err := r.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var c models.RuleCondition
		var isEnabled, negate, caseSensitive int
		var createdStr, updatedStr string
		if err := rows.Scan(
			&c.ID, &c.ConditionGroupID, &isEnabled, &c.AttributeName,
			&c.Operator, &c.ValueType, &c.ComparisonValue, &c.CustomLdapExpression,
			&negate, &c.SortOrder, &caseSensitive, &createdStr, &updatedStr,
		); err != nil {
			return nil, err
		}
		c.IsEnabled = intToBool(isEnabled)
		c.Negate = intToBool(negate)
		c.CaseSensitive = intToBool(caseSensitive)
		c.CreatedUTC = parseTimeOrZero(createdStr)
		c.UpdatedUTC = parseTimeOrZero(updatedStr)
		out[c.ConditionGroupID] = append(out[c.ConditionGroupID], c)
	}

	return out, rows.Err()
}

// List retrieves rules with summary counts and without nested detail.
func (r *RuleRepository) List(offset, limit int) ([]models.Rule, int, error) {
	var total int
	if err := r.db.QueryRow(`SELECT COUNT(*) FROM rules WHERE deleted_utc IS NULL`).Scan(&total); err != nil {
		return nil, 0, err
	}

	rows, err := r.db.Query(`
		SELECT id, name, description, is_enabled, ad_connection_id,
			object_type, base_dn_override, search_scope_override,
			schedule_id, execution_mode, group_join_operator,
			max_parallelism, stop_on_error, last_run_utc, last_run_result,
			created_utc, updated_utc
		FROM rules
		WHERE deleted_utc IS NULL
		ORDER BY created_utc DESC
		LIMIT ? OFFSET ?
	`, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var rules []models.Rule
	for rows.Next() {
		var rule models.Rule
		var isEnabled, stopOnError int
		var lastRun sql.NullString
		var createdStr, updatedStr string
		if err := rows.Scan(
			&rule.ID, &rule.Name, &rule.Description, &isEnabled,
			&rule.ADConnectionID, &rule.ObjectType, &rule.BaseDNOverride,
			&rule.SearchScopeOverride, &rule.ScheduleID, &rule.ExecutionMode,
			&rule.GroupJoinOperator, &rule.MaxParallelism, &stopOnError,
			&lastRun, &rule.LastRunResult,
			&createdStr, &updatedStr,
		); err != nil {
			return nil, 0, err
		}
		rule.IsEnabled = intToBool(isEnabled)
		rule.StopOnError = intToBool(stopOnError)
		rule.LastRunUTC = parseNullTime(lastRun)
		rule.CreatedUTC = parseTimeOrZero(createdStr)
		rule.UpdatedUTC = parseTimeOrZero(updatedStr)
		rules = append(rules, rule)
	}

	return rules, total, rows.Err()
}

// Update persists top-level rule fields. Nested groups/conditions/actions are
// managed through their own methods.
func (r *RuleRepository) Update(rule *models.Rule) error {
	rule.UpdatedUTC = time.Now().UTC()
	_, err := r.db.Exec(`
		UPDATE rules SET
			name = ?, description = ?, is_enabled = ?, ad_connection_id = ?,
			object_type = ?, base_dn_override = ?, search_scope_override = ?,
			schedule_id = ?, execution_mode = ?, group_join_operator = ?,
			max_parallelism = ?, stop_on_error = ?, updated_utc = ?
		WHERE id = ? AND deleted_utc IS NULL
	`,
		rule.Name, rule.Description, boolToInt(rule.IsEnabled), rule.ADConnectionID,
		rule.ObjectType, rule.BaseDNOverride, rule.SearchScopeOverride,
		rule.ScheduleID, rule.ExecutionMode, rule.GroupJoinOperator,
		rule.MaxParallelism, boolToInt(rule.StopOnError),
		formatTime(rule.UpdatedUTC), rule.ID,
	)
	return err
}

// UpdateLastRun records the outcome of the most recent execution on the rule
// row, so the rules list can show when it last ran and how it went.
func (r *RuleRepository) UpdateLastRun(id string, runUTC time.Time, result string) error {
	_, err := r.db.Exec(`
		UPDATE rules SET last_run_utc = ?, last_run_result = ? WHERE id = ?
	`, formatTime(runUTC), result, id)
	return err
}

// UpdateEnabled toggles the enabled flag on a rule.
func (r *RuleRepository) UpdateEnabled(id string, enabled bool) error {
	now := time.Now().UTC()
	_, err := r.db.Exec(`
		UPDATE rules SET is_enabled = ?, updated_utc = ? WHERE id = ? AND deleted_utc IS NULL
	`, boolToInt(enabled), formatTime(now), id)
	return err
}

// SoftDelete marks a rule as deleted.
func (r *RuleRepository) SoftDelete(id string) error {
	now := time.Now().UTC()
	_, err := r.db.Exec(`
		UPDATE rules SET deleted_utc = ?, updated_utc = ? WHERE id = ?
	`, formatTime(now), formatTime(now), id)
	return err
}

// CountConditionsForRule returns the total enabled condition count across all
// condition groups for a rule, useful for list summaries.
func (r *RuleRepository) CountConditionsForRule(ruleID string) (int, error) {
	var n int
	err := r.db.QueryRow(`
		SELECT COUNT(*) FROM rule_conditions c
		JOIN rule_condition_groups g ON g.id = c.condition_group_id
		WHERE g.rule_id = ? AND c.deleted_utc IS NULL AND g.deleted_utc IS NULL
	`, ruleID).Scan(&n)
	return n, err
}

// CountActionsForRule returns the total action count for a rule.
func (r *RuleRepository) CountActionsForRule(ruleID string) (int, error) {
	var n int
	err := r.db.QueryRow(`
		SELECT COUNT(*) FROM rule_actions WHERE rule_id = ? AND deleted_utc IS NULL
	`, ruleID).Scan(&n)
	return n, err
}

func (r *RuleRepository) getActions(ruleID string) ([]models.RuleAction, error) {
	rows, err := r.db.Query(`
		SELECT id, rule_id, action_type, is_enabled, sort_order,
			configuration_json, rollback_mode, created_utc, updated_utc
		FROM rule_actions
		WHERE rule_id = ? AND deleted_utc IS NULL
		ORDER BY sort_order
	`, ruleID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var actions []models.RuleAction
	for rows.Next() {
		var a models.RuleAction
		var isEnabled int
		var createdStr, updatedStr string
		if err := rows.Scan(
			&a.ID, &a.RuleID, &a.ActionType, &isEnabled, &a.SortOrder,
			&a.ConfigurationJSON, &a.RollbackMode, &createdStr, &updatedStr,
		); err != nil {
			return nil, err
		}
		a.IsEnabled = intToBool(isEnabled)
		a.CreatedUTC = parseTimeOrZero(createdStr)
		a.UpdatedUTC = parseTimeOrZero(updatedStr)
		actions = append(actions, a)
	}

	return actions, rows.Err()
}