package schedule

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/core/runeffect"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

const (
	defaultRunListLimit = 100

	schedulerRoutableWorkspacePlacementStateActive   = "active"
	schedulerRoutableWorkspacePlacementStateDraining = "draining"
	schedulerRoutableWorkspacePlacementStateMoving   = "moving"
)

// AutomationRun records one reserved schedule occurrence and its final effect.
type AutomationRun struct {
	ID                        string
	WorkspaceID               string
	AutomationKind            string
	AutomationID              string
	IdempotencyKey            string
	SourceOccurrenceRef       string
	SourceKind                string
	SourceType                string
	SourcePayload             []byte
	Status                    string
	ScheduledFor              *time.Time
	OccurredAt                *time.Time
	AttemptCount              int
	AttemptedAt               *time.Time
	CompletedAt               *time.Time
	NextRetryAt               *time.Time
	EffectKind                string
	EffectRef                 string
	MessageID                 string
	ErrorCode                 string
	ErrorDetail               string
	LeaseOwner                string
	LeaseExpiresAt            *time.Time
	TargetKind                string
	TargetRef                 string
	DeliveryScopeID           string
	PermissionCheckScopeID    string
	TargetSnapshot            []byte
	OwnerMemberIDSnapshot     string
	OwnerUserIDSnapshot       string
	PrincipalMemberIDSnapshot string
	AuthorMemberIDSnapshot    string
	AutomationNameSnapshot    string
	NotificationModeSnapshot  string
	MessageTemplateSnapshot   string
	RenderedContentPreview    string
	Metadata                  []byte
	CreatedAt                 time.Time
	UpdatedAt                 time.Time
}

// ReservedRun is the immutable work item produced by reserving a due schedule.
type ReservedRun struct {
	Definition                     Definition
	Run                            AutomationRun
	CompleteDefinitionAfterSuccess bool
}

// AutomationRunListQuery bounds automation run list queries. CursorAttemptedAt
// is the effective ordering timestamp: attempted_at when present, otherwise
// created_at. The same effective key is exposed by the management view and
// encoded by the Agent API cursor.
type AutomationRunListQuery struct {
	WorkspaceID       string
	AutomationKind    string
	AutomationID      string
	Status            string
	Statuses          []string
	Limit             int
	CursorAttemptedAt *time.Time
	CursorID          string
}

type AutomationRunPage struct {
	Runs    []AutomationRun
	HasMore bool
}

// DueDefinitionListQuery bounds due schedule definition scans for the scheduler.
type DueDefinitionListQuery struct {
	Now   time.Time
	Limit int
}

// AutomationRunCompletion records the terminal effect or error for a reserved run.
type AutomationRunCompletion struct {
	WorkspaceID     string
	ID              string
	Status          runeffect.Status
	Effect          runeffect.Effect
	Error           runeffect.Error
	BlockDefinition bool
	Now             time.Time
}

// AutomationRunRetryUpdate moves a run back to retry-waiting after a failed attempt.
type AutomationRunRetryUpdate struct {
	WorkspaceID string
	ID          string
	NextRetryAt time.Time
	ErrorCode   string
	ErrorDetail string
	Now         time.Time
}

// ListDueDefinitionIDs returns active schedule definitions with due occurrences.
func (store *Store) ListDueDefinitionIDs(ctx context.Context, params DueDefinitionListQuery) ([]string, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listDueDefinitionIDs(ctx, queryer, params.Now, params.Limit, store.schedulerWorkspaceGroupID())
}

// ReserveDueAutomationRun idempotently reserves the current due occurrence for one schedule definition.
func (store *Store) ReserveDueAutomationRun(ctx context.Context, definitionID string, now time.Time) (ReservedRun, bool, error) {
	if store == nil || store.db == nil {
		return ReservedRun{}, false, fmt.Errorf("schedule store database is not configured")
	}
	var reserved ReservedRun
	var ok bool
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		reserved, ok, err = reserveDueAutomationRun(ctx, tx, definitionID, now)
		return err
	})
	return reserved, ok, err
}

// ListRetryRunIDs returns retry-waiting schedule automation run ids that are ready to claim.
func (store *Store) ListRetryRunIDs(ctx context.Context, now time.Time, limit int) ([]string, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listRetryRunIDs(ctx, queryer, now, limit, store.schedulerWorkspaceGroupID())
}

// ClaimRetryAutomationRun moves a retry-waiting run back to running.
func (store *Store) ClaimRetryAutomationRun(ctx context.Context, runID string, now time.Time) (ReservedRun, bool, error) {
	if store == nil || store.db == nil {
		return ReservedRun{}, false, fmt.Errorf("schedule store database is not configured")
	}
	var reserved ReservedRun
	var ok bool
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		reserved, ok, err = claimRetryAutomationRun(ctx, tx, runID, now)
		return err
	})
	return reserved, ok, err
}

// AbandonInactiveRetryRuns marks ready retry-waiting runs terminal when their schedule can no longer execute.
func (store *Store) AbandonInactiveRetryRuns(ctx context.Context, now time.Time, limit int) (int, error) {
	if store == nil || store.db == nil {
		return 0, fmt.Errorf("schedule store database is not configured")
	}
	runIDs, err := listInactiveRetryRunIDs(ctx, store.db, now, limit, store.schedulerWorkspaceGroupID())
	if err != nil {
		return 0, err
	}
	abandoned := 0
	for _, runID := range runIDs {
		var ok bool
		err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
			var err error
			ok, err = abandonInactiveRetryRun(ctx, tx, runID, now)
			return err
		})
		if err != nil {
			return abandoned, err
		}
		if ok {
			abandoned++
		}
	}
	return abandoned, nil
}

// MarkAutomationRunRetryWaiting records an attempt failure that should be retried later.
func (store *Store) MarkAutomationRunRetryWaiting(ctx context.Context, params AutomationRunRetryUpdate) (AutomationRun, error) {
	if store == nil || store.db == nil {
		return AutomationRun{}, fmt.Errorf("schedule store database is not configured")
	}
	var run AutomationRun
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		run, err = markAutomationRunRetryWaiting(ctx, tx, params)
		return err
	})
	return run, err
}

// CompleteAutomationRun records a terminal effect or failure and updates the owning definition summary.
func (store *Store) CompleteAutomationRun(ctx context.Context, params AutomationRunCompletion) (AutomationRun, error) {
	if store == nil || store.db == nil {
		return AutomationRun{}, fmt.Errorf("schedule store database is not configured")
	}
	var run AutomationRun
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		run, err = completeAutomationRun(ctx, tx, params)
		return err
	})
	return run, err
}

// LoadAutomationRun loads one automation run by id.
func (store *Store) LoadAutomationRun(ctx context.Context, workspaceID string, runID string) (AutomationRun, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return AutomationRun{}, err
	}
	return loadAutomationRun(ctx, queryer, workspaceID, runID, false)
}

// ListAutomationRunPage lists one bounded, stable page of automation runs.
func (store *Store) ListAutomationRunPage(ctx context.Context, params AutomationRunListQuery) (AutomationRunPage, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return AutomationRunPage{}, err
	}
	return listAutomationRunPage(ctx, queryer, params)
}

// ListAutomationRuns lists schedule automation runs. It remains a slice API
// for internal scheduler callers; public collection endpoints use the page API.
func (store *Store) ListAutomationRuns(ctx context.Context, params AutomationRunListQuery) ([]AutomationRun, error) {
	page, err := store.ListAutomationRunPage(ctx, params)
	if err != nil {
		return nil, err
	}
	return page.Runs, nil
}

// ListStaleRunningRunIDs returns running run ids older than the caller's recovery threshold.
func (store *Store) ListStaleRunningRunIDs(ctx context.Context, recoveredBefore time.Time, limit int) ([]string, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listStaleRunningRunIDs(ctx, queryer, recoveredBefore, limit, store.schedulerWorkspaceGroupID())
}

// RecoverStaleRunningRun requeues or fails a stale running run without creating a second effect.
func (store *Store) RecoverStaleRunningRun(ctx context.Context, runID string, recoveredBefore time.Time, now time.Time, maxAttempts int) (bool, error) {
	if store == nil || store.db == nil {
		return false, fmt.Errorf("schedule store database is not configured")
	}
	var recovered bool
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		recovered, err = recoverStaleRunningRun(ctx, tx, runID, recoveredBefore, now, maxAttempts)
		return err
	})
	return recovered, err
}

func listDueDefinitionIDs(ctx context.Context, queryer coredb.Queryer, now time.Time, limit int, workspaceGroupID string) ([]string, error) {
	limit = normalizeRunListLimit(limit)
	workspaceGroupID = trim(workspaceGroupID)
	if workspaceGroupID != "" {
		rows, err := queryer.Query(ctx, `
SELECT definition.id
FROM schedule_definitions definition
JOIN workspace_placements placement ON placement.workspace_id = definition.workspace_id
WHERE definition.deleted_at IS NULL
  AND definition.status = $1
  AND definition.source_kind = $2
  AND definition.next_occurrence_at IS NOT NULL
  AND definition.next_occurrence_at <= $3
  AND placement.workspace_group_id = $4
  AND placement.state IN ($5, $6, $7)
ORDER BY definition.next_occurrence_at ASC, definition.created_at ASC, definition.id ASC
LIMIT $8
`, DefinitionStatusActive, SourceKindTime, utcOrNow(now), workspaceGroupID, schedulerRoutableWorkspacePlacementStateActive, schedulerRoutableWorkspacePlacementStateDraining, schedulerRoutableWorkspacePlacementStateMoving, limit)
		return scanRunIDRows(rows, err, "list due schedule definitions", "due schedule definition")
	}
	rows, err := queryer.Query(ctx, `
SELECT id
FROM schedule_definitions
WHERE deleted_at IS NULL
  AND status = $1
  AND source_kind = $2
  AND next_occurrence_at IS NOT NULL
  AND next_occurrence_at <= $3
ORDER BY next_occurrence_at ASC, created_at ASC, id ASC
LIMIT $4
`, DefinitionStatusActive, SourceKindTime, utcOrNow(now), limit)
	return scanRunIDRows(rows, err, "list due schedule definitions", "due schedule definition")
}

func scanRunIDRows(rows coredb.Rows, err error, queryLabel string, rowLabel string) ([]string, error) {
	if err != nil {
		return nil, fmt.Errorf("%s: %w", queryLabel, err)
	}
	defer rows.Close()
	ids := make([]string, 0)
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("scan %s id: %w", rowLabel, err)
		}
		ids = append(ids, trim(id))
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate %s ids: %w", rowLabel, err)
	}
	return ids, nil
}

func reserveDueAutomationRun(ctx context.Context, execer coredb.QueryExecer, definitionID string, now time.Time) (ReservedRun, bool, error) {
	now = utcOrNow(now)
	definition, err := loadDefinitionForUpdateByID(ctx, execer, definitionID)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			return ReservedRun{}, false, nil
		}
		return ReservedRun{}, false, err
	}
	if definition.DeletedAt != nil || definition.Status != DefinitionStatusActive || definition.NextOccurrenceAt == nil {
		return ReservedRun{}, false, nil
	}
	if definition.NextOccurrenceAt.After(now) {
		return ReservedRun{}, false, nil
	}
	if definition.SourceKind != SourceKindTime {
		if err := updateDefinitionAfterFailure(ctx, execer, definition.ID, now, FailureCodeSourceKindNotEnabled, "schedule source kind is not enabled", true); err != nil {
			return ReservedRun{}, false, err
		}
		return ReservedRun{}, false, nil
	}
	occurrence := definition.NextOccurrenceAt.UTC()
	nextOccurrenceAt, err := nextOccurrenceAfterDefinition(definition, occurrence)
	if err != nil {
		if updateErr := updateDefinitionAfterFailure(ctx, execer, definition.ID, now, FailureCodeInvalidSchedule, err.Error(), true); updateErr != nil {
			return ReservedRun{}, false, updateErr
		}
		return ReservedRun{}, false, nil
	}
	runID, err := identifiers.NewEntityID("arun")
	if err != nil {
		return ReservedRun{}, false, fmt.Errorf("generate automation run id: %w", err)
	}
	run := automationRunFromDefinition(definition, runID, occurrence, now)
	if err := insertAutomationRun(ctx, execer, run); err != nil {
		if coredb.IsUniqueViolation(err) {
			return ReservedRun{}, false, nil
		}
		return ReservedRun{}, false, err
	}
	if err := updateDefinitionNextOccurrence(ctx, execer, definition.ID, nextOccurrenceAt, now); err != nil {
		return ReservedRun{}, false, err
	}
	definition.NextOccurrenceAt = nextOccurrenceAt
	return ReservedRun{Definition: definition, Run: run, CompleteDefinitionAfterSuccess: isOneShotSource(definition.SourceType)}, true, nil
}

func loadDefinitionForUpdateByID(ctx context.Context, queryer coredb.QueryRower, definitionID string) (Definition, error) {
	trimmedID := trim(definitionID)
	if trimmedID == "" {
		return Definition{}, badRequest("scheduleDefinitionId is required")
	}
	query := definitionSelectSQL() + `
WHERE id = $1
  AND deleted_at IS NULL
FOR UPDATE
`
	definition, err := scanDefinition(queryer.QueryRow(ctx, query, trimmedID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return Definition{}, notFound("schedule definition not found")
		}
		return Definition{}, fmt.Errorf("load schedule definition: %w", err)
	}
	return definition, nil
}

func automationRunFromDefinition(definition Definition, runID string, occurrence time.Time, now time.Time) AutomationRun {
	occurrence = occurrence.UTC()
	return AutomationRun{
		ID:                        runID,
		WorkspaceID:               definition.WorkspaceID,
		AutomationKind:            AutomationKindSchedule,
		AutomationID:              definition.ID,
		IdempotencyKey:            buildAutomationRunIdempotencyKey(definition.ID, occurrence),
		SourceOccurrenceRef:       occurrence.Format(time.RFC3339Nano),
		SourceKind:                definition.SourceKind,
		SourceType:                definition.SourceType,
		Status:                    string(runeffect.StatusRunning),
		ScheduledFor:              &occurrence,
		OccurredAt:                &occurrence,
		AttemptCount:              1,
		AttemptedAt:               &now,
		EffectKind:                string(runeffect.EffectKindNone),
		TargetKind:                definition.TargetKind,
		TargetRef:                 definition.TargetRef,
		DeliveryScopeID:           definition.DeliveryScopeID,
		PermissionCheckScopeID:    definition.PermissionCheckScopeID,
		TargetSnapshot:            definition.TargetSnapshot,
		OwnerMemberIDSnapshot:     definition.OwnerMemberID,
		PrincipalMemberIDSnapshot: definition.PrincipalMemberID,
		AuthorMemberIDSnapshot:    definition.PrincipalMemberID,
		AutomationNameSnapshot:    definition.Name,
		NotificationModeSnapshot:  definition.NotificationMode,
		MessageTemplateSnapshot:   definition.MessageTemplate,
		RenderedContentPreview:    definition.MessageTemplate,
		Metadata:                  definition.Metadata,
		CreatedAt:                 now,
		UpdatedAt:                 now,
	}
}

func buildAutomationRunIdempotencyKey(definitionID string, occurrence time.Time) string {
	return "schedule:" + trim(definitionID) + ":" + occurrence.UTC().Format(time.RFC3339Nano)
}

func insertAutomationRun(ctx context.Context, execer coredb.QueryExecer, run AutomationRun) error {
	_, err := execer.Exec(ctx, `
INSERT INTO automation_runs (
    id, workspace_id, automation_kind, automation_id, idempotency_key,
    source_occurrence_ref, source_kind, source_type, source_payload,
    status, scheduled_for, occurred_at, attempt_count, attempted_at,
    completed_at, next_retry_at, effect_kind, effect_ref, message_id,
    error_code, error_detail, lease_owner, lease_expires_at,
    target_kind, target_ref, delivery_scope_id, permission_check_scope_id,
    target_snapshot, owner_member_id_snapshot, owner_user_id_snapshot, principal_member_id_snapshot,
    author_member_id_snapshot, automation_name_snapshot,
    notification_mode_snapshot, message_template_snapshot,
    rendered_content_preview, metadata, created_at, updated_at
) VALUES (
    $1, $2, $3, $4, $5,
    $6, $7, $8, $9,
    $10, $11, $12, $13, $14,
    $15, $16, $17, $18, $19,
    $20, $21, $22, $23,
    $24, $25, $26, $27,
    $28, $29, $30, $31,
    $32, $33,
    $34, $35,
    $36, $37, $38, $39
)
`, run.ID, run.WorkspaceID, run.AutomationKind, run.AutomationID, run.IdempotencyKey,
		run.SourceOccurrenceRef, run.SourceKind, run.SourceType, nullableBytes(run.SourcePayload),
		run.Status, run.ScheduledFor, run.OccurredAt, run.AttemptCount, run.AttemptedAt,
		run.CompletedAt, run.NextRetryAt, run.EffectKind, run.EffectRef, nullableString(run.MessageID),
		run.ErrorCode, run.ErrorDetail, run.LeaseOwner, run.LeaseExpiresAt,
		run.TargetKind, run.TargetRef, nullableString(run.DeliveryScopeID), nullableString(run.PermissionCheckScopeID),
		nullableBytes(run.TargetSnapshot), run.OwnerMemberIDSnapshot, run.OwnerUserIDSnapshot, run.PrincipalMemberIDSnapshot,
		run.AuthorMemberIDSnapshot, run.AutomationNameSnapshot,
		run.NotificationModeSnapshot, run.MessageTemplateSnapshot,
		run.RenderedContentPreview, nullableBytes(run.Metadata), run.CreatedAt, run.UpdatedAt)
	if err != nil {
		return fmt.Errorf("insert automation run: %w", err)
	}
	return nil
}

func listRetryRunIDs(ctx context.Context, queryer coredb.Queryer, now time.Time, limit int, workspaceGroupID string) ([]string, error) {
	limit = normalizeRunListLimit(limit)
	workspaceGroupID = trim(workspaceGroupID)
	if workspaceGroupID != "" {
		rows, err := queryer.Query(ctx, `
SELECT run.id
FROM automation_runs run
JOIN schedule_definitions definition ON definition.id = run.automation_id
JOIN workspace_placements placement ON placement.workspace_id = run.workspace_id
WHERE run.automation_kind = $1
  AND run.status = $2
  AND run.next_retry_at IS NOT NULL
  AND run.next_retry_at <= $3
  AND run.source_kind = $4
  AND definition.deleted_at IS NULL
  AND definition.status = $5
  AND definition.source_kind = $4
  AND placement.workspace_group_id = $6
  AND placement.state IN ($7, $8, $9)
ORDER BY run.next_retry_at ASC, run.attempted_at ASC, run.id ASC
LIMIT $10
`, AutomationKindSchedule, string(runeffect.StatusRetryWaiting), utcOrNow(now), SourceKindTime, DefinitionStatusActive, workspaceGroupID, schedulerRoutableWorkspacePlacementStateActive, schedulerRoutableWorkspacePlacementStateDraining, schedulerRoutableWorkspacePlacementStateMoving, limit)
		return scanRunIDRows(rows, err, "list retry schedule automation runs", "retry schedule automation run")
	}
	rows, err := queryer.Query(ctx, `
SELECT run.id
FROM automation_runs run
JOIN schedule_definitions definition ON definition.id = run.automation_id
WHERE run.automation_kind = $1
  AND run.status = $2
  AND run.next_retry_at IS NOT NULL
  AND run.next_retry_at <= $3
  AND run.source_kind = $4
  AND definition.deleted_at IS NULL
  AND definition.status = $5
  AND definition.source_kind = $4
ORDER BY run.next_retry_at ASC, run.attempted_at ASC, run.id ASC
LIMIT $6
`, AutomationKindSchedule, string(runeffect.StatusRetryWaiting), utcOrNow(now), SourceKindTime, DefinitionStatusActive, limit)
	return scanRunIDRows(rows, err, "list retry schedule automation runs", "retry schedule automation run")
}

func listInactiveRetryRunIDs(ctx context.Context, queryer coredb.Queryer, now time.Time, limit int, workspaceGroupID string) ([]string, error) {
	limit = normalizeRunListLimit(limit)
	workspaceGroupID = trim(workspaceGroupID)
	if workspaceGroupID != "" {
		rows, err := queryer.Query(ctx, `
SELECT run.id
FROM automation_runs run
LEFT JOIN schedule_definitions definition ON definition.id = run.automation_id
JOIN workspace_placements placement ON placement.workspace_id = run.workspace_id
WHERE run.automation_kind = $1
  AND run.status = $2
  AND run.next_retry_at IS NOT NULL
  AND run.next_retry_at <= $3
  AND run.source_kind = $4
  AND (
    definition.id IS NULL
    OR definition.deleted_at IS NOT NULL
    OR definition.status IN ($5, $6)
    OR definition.source_kind <> $4
  )
  AND placement.workspace_group_id = $7
  AND placement.state IN ($8, $9, $10)
ORDER BY run.next_retry_at ASC, run.attempted_at ASC, run.id ASC
LIMIT $11
`, AutomationKindSchedule, string(runeffect.StatusRetryWaiting), utcOrNow(now), SourceKindTime, DefinitionStatusBlocked, DefinitionStatusCompleted, workspaceGroupID, schedulerRoutableWorkspacePlacementStateActive, schedulerRoutableWorkspacePlacementStateDraining, schedulerRoutableWorkspacePlacementStateMoving, limit)
		return scanRunIDRows(rows, err, "list inactive retry schedule automation runs", "inactive retry schedule automation run")
	}
	rows, err := queryer.Query(ctx, `
SELECT run.id
FROM automation_runs run
LEFT JOIN schedule_definitions definition ON definition.id = run.automation_id
WHERE run.automation_kind = $1
  AND run.status = $2
  AND run.next_retry_at IS NOT NULL
  AND run.next_retry_at <= $3
  AND run.source_kind = $4
  AND (
    definition.id IS NULL
    OR definition.deleted_at IS NOT NULL
    OR definition.status IN ($5, $6)
    OR definition.source_kind <> $4
  )
ORDER BY run.next_retry_at ASC, run.attempted_at ASC, run.id ASC
LIMIT $7
`, AutomationKindSchedule, string(runeffect.StatusRetryWaiting), utcOrNow(now), SourceKindTime, DefinitionStatusBlocked, DefinitionStatusCompleted, limit)
	return scanRunIDRows(rows, err, "list inactive retry schedule automation runs", "inactive retry schedule automation run")
}

func abandonInactiveRetryRun(ctx context.Context, execer coredb.QueryExecer, runID string, now time.Time) (bool, error) {
	now = utcOrNow(now)
	run, err := loadAutomationRun(ctx, execer, "", runID, true)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			return false, nil
		}
		return false, err
	}
	if run.AutomationKind != AutomationKindSchedule || run.Status != string(runeffect.StatusRetryWaiting) || run.NextRetryAt == nil || run.NextRetryAt.After(now) || run.SourceKind != SourceKindTime {
		return false, nil
	}
	definition, err := loadDefinitionForUpdateByID(ctx, execer, run.AutomationID)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			_, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{
				ID:              run.ID,
				Status:          runeffect.StatusFailed,
				Error:           runeffect.Error{Code: FailureCodeScheduleNotActive, Detail: "schedule definition is missing or deleted; abandoning retrying run"},
				BlockDefinition: false,
				Now:             now,
			})
			return completeErr == nil, completeErr
		}
		return false, err
	}
	if definition.Status == DefinitionStatusActive && definition.SourceKind == SourceKindTime {
		return false, nil
	}
	if definition.Status == DefinitionStatusPaused && definition.SourceKind == SourceKindTime {
		return false, nil
	}
	if definition.SourceKind != SourceKindTime {
		_, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{
			ID:              run.ID,
			Status:          runeffect.StatusFailed,
			Error:           runeffect.Error{Code: FailureCodeSourceKindNotEnabled, Detail: "schedule source kind is not enabled; abandoning retrying run"},
			BlockDefinition: true,
			Now:             now,
		})
		return completeErr == nil, completeErr
	}
	_, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{
		ID:              run.ID,
		Status:          runeffect.StatusFailed,
		Error:           runeffect.Error{Code: FailureCodeScheduleNotActive, Detail: "schedule is no longer executable; abandoning retrying run"},
		BlockDefinition: false,
		Now:             now,
	})
	return completeErr == nil, completeErr
}

func claimRetryAutomationRun(ctx context.Context, execer coredb.QueryExecer, runID string, now time.Time) (ReservedRun, bool, error) {
	now = utcOrNow(now)
	run, err := loadAutomationRun(ctx, execer, "", runID, true)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			return ReservedRun{}, false, nil
		}
		return ReservedRun{}, false, err
	}
	if run.AutomationKind != AutomationKindSchedule || run.Status != string(runeffect.StatusRetryWaiting) || run.NextRetryAt == nil || run.NextRetryAt.After(now) || run.SourceKind != SourceKindTime {
		return ReservedRun{}, false, nil
	}
	definition, err := loadDefinitionForUpdateByID(ctx, execer, run.AutomationID)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			if _, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{ID: run.ID, Status: runeffect.StatusFailed, Error: runeffect.Error{Code: FailureCodeScheduleNotActive, Detail: "schedule definition is missing; abandoning retrying run"}, Now: now}); completeErr != nil {
				return ReservedRun{}, false, completeErr
			}
			return ReservedRun{}, false, nil
		}
		return ReservedRun{}, false, err
	}
	if definition.Status == DefinitionStatusPaused && definition.SourceKind == SourceKindTime {
		return ReservedRun{}, false, nil
	}
	if definition.DeletedAt != nil || definition.Status != DefinitionStatusActive || definition.SourceKind != SourceKindTime {
		if _, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{ID: run.ID, Status: runeffect.StatusFailed, Error: runeffect.Error{Code: FailureCodeScheduleNotActive, Detail: "schedule is no longer active; abandoning retrying run"}, Now: now}); completeErr != nil {
			return ReservedRun{}, false, completeErr
		}
		return ReservedRun{}, false, nil
	}
	if err := updateAutomationRunClaimRetry(ctx, execer, run.ID, now); err != nil {
		return ReservedRun{}, false, err
	}
	run.Status = string(runeffect.StatusRunning)
	run.AttemptCount++
	run.AttemptedAt = &now
	run.NextRetryAt = nil
	run.UpdatedAt = now
	return ReservedRun{Definition: definition, Run: run, CompleteDefinitionAfterSuccess: isOneShotSource(definition.SourceType)}, true, nil
}

func updateAutomationRunClaimRetry(ctx context.Context, execer coredb.QueryExecer, runID string, now time.Time) error {
	result, err := execer.Exec(ctx, `
UPDATE automation_runs
SET status = $2,
    attempt_count = attempt_count + 1,
    attempted_at = $3,
    next_retry_at = NULL,
    effect_kind = $4,
    effect_ref = '',
    lease_owner = '',
    lease_expires_at = NULL,
    updated_at = $3
WHERE id = $1
`, trim(runID), string(runeffect.StatusRunning), now, string(runeffect.EffectKindNone))
	if err != nil {
		return fmt.Errorf("claim schedule automation run retry: %w", err)
	}
	if result.RowsAffected() == 0 {
		return notFound("automation run not found")
	}
	return nil
}

func markAutomationRunRetryWaiting(ctx context.Context, execer coredb.QueryExecer, params AutomationRunRetryUpdate) (AutomationRun, error) {
	runID := trim(params.ID)
	if runID == "" {
		return AutomationRun{}, badRequest("automationRunId is required")
	}
	now := utcOrNow(params.Now)
	nextRetryAt := params.NextRetryAt.UTC()
	if nextRetryAt.IsZero() {
		return AutomationRun{}, badRequest("nextRetryAt is required")
	}
	run, err := loadAutomationRun(ctx, execer, params.WorkspaceID, runID, true)
	if err != nil {
		return AutomationRun{}, err
	}
	result, err := execer.Exec(ctx, `
UPDATE automation_runs
SET status = $2,
    next_retry_at = $3,
    error_code = $4,
    error_detail = $5,
    lease_owner = '',
    lease_expires_at = NULL,
    updated_at = $6
WHERE id = $1
`, run.ID, string(runeffect.StatusRetryWaiting), nextRetryAt, trim(params.ErrorCode), trim(params.ErrorDetail), now)
	if err != nil {
		return AutomationRun{}, fmt.Errorf("mark schedule automation run retry waiting: %w", err)
	}
	if result.RowsAffected() == 0 {
		return AutomationRun{}, notFound("automation run not found")
	}
	if err := updateDefinitionAttemptFailure(ctx, execer, run.AutomationID, now, trim(params.ErrorCode), trim(params.ErrorDetail)); err != nil {
		return AutomationRun{}, err
	}
	return loadAutomationRun(ctx, execer, run.WorkspaceID, run.ID, true)
}

func completeAutomationRun(ctx context.Context, execer coredb.QueryExecer, params AutomationRunCompletion) (AutomationRun, error) {
	runID := trim(params.ID)
	if runID == "" {
		return AutomationRun{}, badRequest("automationRunId is required")
	}
	now := utcOrNow(params.Now)
	status := params.Status
	if status == "" {
		status = runeffect.StatusSucceeded
	}
	status, err := runeffect.NormalizeStatus(string(status))
	if err != nil {
		return AutomationRun{}, badRequest(err.Error())
	}
	run, err := loadAutomationRun(ctx, execer, params.WorkspaceID, runID, true)
	if err != nil {
		return AutomationRun{}, err
	}
	effect, err := params.Effect.Normalize()
	if err != nil {
		return AutomationRun{}, badRequest(err.Error())
	}
	failure := params.Error.Normalize()
	contract := runeffect.Contract{
		Status:         status,
		IdempotencyKey: run.IdempotencyKey,
		AttemptCount:   run.AttemptCount,
		AttemptedAt:    run.AttemptedAt,
		CompletedAt:    &now,
		Effect:         effect,
		Error:          failure,
	}
	if err := contract.Validate(); err != nil {
		return AutomationRun{}, badRequest(err.Error())
	}
	result, err := execer.Exec(ctx, `
UPDATE automation_runs
SET status = $2,
    completed_at = $3,
    effect_kind = $4,
    effect_ref = $5,
    message_id = $6,
    error_code = $7,
    error_detail = $8,
    next_retry_at = NULL,
    lease_owner = '',
    lease_expires_at = NULL,
    updated_at = $3
WHERE id = $1
`, run.ID, string(status), now, string(effect.Kind), trim(effect.Ref), nullableString(effect.MessageID), failure.Code, failure.Detail)
	if err != nil {
		return AutomationRun{}, fmt.Errorf("complete schedule automation run: %w", err)
	}
	if result.RowsAffected() == 0 {
		return AutomationRun{}, notFound("automation run not found")
	}
	if status == runeffect.StatusSucceeded {
		if err := updateDefinitionAfterSuccess(ctx, execer, run.AutomationID, isOneShotSource(run.SourceType), now); err != nil {
			return AutomationRun{}, err
		}
	} else if status == runeffect.StatusFailed {
		if err := updateDefinitionAfterFailure(ctx, execer, run.AutomationID, now, failure.Code, failure.Detail, params.BlockDefinition); err != nil {
			if !errors.Is(err, ErrNotFound) {
				return AutomationRun{}, err
			}
		}
	}
	return loadAutomationRun(ctx, execer, run.WorkspaceID, run.ID, true)
}

func listAutomationRunPage(ctx context.Context, queryer coredb.Queryer, params AutomationRunListQuery) (AutomationRunPage, error) {
	workspaceID := trim(params.WorkspaceID)
	if workspaceID == "" {
		return AutomationRunPage{}, badRequest("workspaceId is required")
	}
	cursorID := trim(params.CursorID)
	if (params.CursorAttemptedAt == nil) != (cursorID == "") {
		return AutomationRunPage{}, badRequest("invalid automation run cursor")
	}
	conditions := []string{"workspace_id = $1"}
	args := []any{workspaceID}
	if trim(params.AutomationKind) != "" {
		args = append(args, trim(params.AutomationKind))
		conditions = append(conditions, fmt.Sprintf("automation_kind = $%d", len(args)))
	}
	if trim(params.AutomationID) != "" {
		args = append(args, trim(params.AutomationID))
		conditions = append(conditions, fmt.Sprintf("automation_id = $%d", len(args)))
	}
	if trim(params.Status) != "" {
		args = append(args, trim(params.Status))
		conditions = append(conditions, fmt.Sprintf("status = $%d", len(args)))
	} else if len(params.Statuses) > 0 {
		conditions = append(conditions, stringInCondition("status", params.Statuses, &args))
	}
	if params.CursorAttemptedAt != nil {
		if params.CursorAttemptedAt.IsZero() {
			return AutomationRunPage{}, badRequest("invalid automation run cursor")
		}
		if err := validateAutomationRunCursor(ctx, queryer, params, cursorID); err != nil {
			return AutomationRunPage{}, err
		}
		attemptedAtArg := len(args) + 1
		args = append(args, params.CursorAttemptedAt.UTC())
		idArg := len(args) + 1
		args = append(args, cursorID)
		// CursorAttemptedAt is the effective key used by both ORDER BY and the
		// continuation predicate. In particular, NULL attempted_at rows use
		// their non-NULL created_at value instead of matching every continuation
		// page.
		effectiveAt := "COALESCE(attempted_at, created_at)"
		conditions = append(conditions, fmt.Sprintf("(%s < $%d OR (%s = $%d AND id < $%d))", effectiveAt, attemptedAtArg, effectiveAt, attemptedAtArg, idArg))
	}
	limit := normalizeRunListLimit(params.Limit)
	args = append(args, limit+1)
	query := automationRunSelectSQL() + `
WHERE ` + strings.Join(conditions, " AND ") + `
ORDER BY COALESCE(attempted_at, created_at) DESC, id DESC
`
	query += fmt.Sprintf("LIMIT $%d\n", len(args))
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return AutomationRunPage{}, fmt.Errorf("list automation runs: %w", err)
	}
	defer rows.Close()
	runs := make([]AutomationRun, 0, limit+1)
	for rows.Next() {
		run, err := scanAutomationRun(rows)
		if err != nil {
			return AutomationRunPage{}, fmt.Errorf("scan automation run: %w", err)
		}
		runs = append(runs, run)
	}
	if err := rows.Err(); err != nil {
		return AutomationRunPage{}, fmt.Errorf("iterate automation runs: %w", err)
	}
	hasMore := len(runs) > limit
	if hasMore {
		runs = runs[:limit]
	}
	return AutomationRunPage{Runs: runs, HasMore: hasMore}, nil
}

func validateAutomationRunCursor(ctx context.Context, queryer coredb.Queryer, params AutomationRunListQuery, cursorID string) error {
	run, err := loadAutomationRun(ctx, queryer, params.WorkspaceID, cursorID, false)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			return badRequest("invalid automation run cursor")
		}
		return err
	}
	if kind := trim(params.AutomationKind); kind != "" && run.AutomationKind != kind {
		return badRequest("invalid automation run cursor")
	}
	if automationID := trim(params.AutomationID); automationID != "" && run.AutomationID != automationID {
		return badRequest("invalid automation run cursor")
	}
	if status := trim(params.Status); status != "" && run.Status != status {
		return badRequest("invalid automation run cursor")
	}
	if len(params.Statuses) > 0 {
		matched := false
		for _, status := range params.Statuses {
			if run.Status == trim(status) {
				matched = true
				break
			}
		}
		if !matched {
			return badRequest("invalid automation run cursor")
		}
	}
	if params.CursorAttemptedAt == nil || !automationRunCursorAt(run).Equal(params.CursorAttemptedAt.UTC()) {
		return badRequest("invalid automation run cursor")
	}
	return nil
}

func automationRunCursorAt(run AutomationRun) time.Time {
	if run.AttemptedAt != nil {
		return run.AttemptedAt.UTC()
	}
	return run.CreatedAt.UTC()
}

func listAutomationRuns(ctx context.Context, queryer coredb.Queryer, params AutomationRunListQuery) ([]AutomationRun, error) {
	page, err := listAutomationRunPage(ctx, queryer, params)
	if err != nil {
		return nil, err
	}
	return page.Runs, nil
}

func loadAutomationRun(ctx context.Context, queryer coredb.QueryRower, workspaceID string, runID string, forUpdate bool) (AutomationRun, error) {
	trimmedID := trim(runID)
	if trimmedID == "" {
		return AutomationRun{}, badRequest("automationRunId is required")
	}
	conditions := []string{"id = $1"}
	args := []any{trimmedID}
	if trim(workspaceID) != "" {
		args = append(args, trim(workspaceID))
		conditions = append(conditions, fmt.Sprintf("workspace_id = $%d", len(args)))
	}
	query := automationRunSelectSQL() + `
WHERE ` + strings.Join(conditions, " AND ") + "\n"
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	run, err := scanAutomationRun(queryer.QueryRow(ctx, query, args...))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AutomationRun{}, notFound("automation run not found")
		}
		return AutomationRun{}, fmt.Errorf("load automation run: %w", err)
	}
	return run, nil
}

func listStaleRunningRunIDs(ctx context.Context, queryer coredb.Queryer, recoveredBefore time.Time, limit int, workspaceGroupID string) ([]string, error) {
	limit = normalizeRunListLimit(limit)
	workspaceGroupID = trim(workspaceGroupID)
	if workspaceGroupID != "" {
		rows, err := queryer.Query(ctx, `
SELECT run.id
FROM automation_runs run
JOIN workspace_placements placement ON placement.workspace_id = run.workspace_id
WHERE run.automation_kind = $1
  AND run.status = $2
  AND run.completed_at IS NULL
  AND run.attempted_at < $3
  AND run.source_kind = $4
  AND placement.workspace_group_id = $5
  AND placement.state IN ($6, $7, $8)
ORDER BY run.attempted_at ASC, run.id ASC
LIMIT $9
`, AutomationKindSchedule, string(runeffect.StatusRunning), recoveredBefore.UTC(), SourceKindTime, workspaceGroupID, schedulerRoutableWorkspacePlacementStateActive, schedulerRoutableWorkspacePlacementStateDraining, schedulerRoutableWorkspacePlacementStateMoving, limit)
		return scanRunIDRows(rows, err, "list stale running schedule automation runs", "stale running schedule automation run")
	}
	rows, err := queryer.Query(ctx, `
SELECT id
FROM automation_runs
WHERE automation_kind = $1
  AND status = $2
  AND completed_at IS NULL
  AND attempted_at < $3
  AND source_kind = $4
ORDER BY attempted_at ASC, id ASC
LIMIT $5
`, AutomationKindSchedule, string(runeffect.StatusRunning), recoveredBefore.UTC(), SourceKindTime, limit)
	return scanRunIDRows(rows, err, "list stale running schedule automation runs", "stale running schedule automation run")
}

func recoverStaleRunningRun(ctx context.Context, execer coredb.QueryExecer, runID string, recoveredBefore time.Time, now time.Time, maxAttempts int) (bool, error) {
	now = utcOrNow(now)
	run, err := loadAutomationRun(ctx, execer, "", runID, true)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			return false, nil
		}
		return false, err
	}
	if run.AutomationKind != AutomationKindSchedule || run.Status != string(runeffect.StatusRunning) || run.CompletedAt != nil || run.AttemptedAt == nil || !run.AttemptedAt.Before(recoveredBefore.UTC()) || run.SourceKind != SourceKindTime {
		return false, nil
	}
	definition, err := loadDefinitionForUpdateByID(ctx, execer, run.AutomationID)
	if err != nil {
		if coredb.IsNotFound(err) || errors.Is(err, ErrNotFound) {
			_, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{
				ID:              run.ID,
				Status:          runeffect.StatusFailed,
				Error:           runeffect.Error{Code: FailureCodeScheduleNotActive, Detail: "schedule definition is missing; abandoning stale running run"},
				BlockDefinition: false,
				Now:             now,
			})
			return completeErr == nil, completeErr
		}
		return false, err
	}
	if definition.Status != DefinitionStatusActive && definition.Status != DefinitionStatusPaused {
		_, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{
			ID:              run.ID,
			Status:          runeffect.StatusFailed,
			Error:           runeffect.Error{Code: FailureCodeScheduleNotActive, Detail: "schedule is no longer active; abandoning stale running run"},
			BlockDefinition: false,
			Now:             now,
		})
		return completeErr == nil, completeErr
	}
	if definition.SourceKind != SourceKindTime {
		_, completeErr := completeAutomationRun(ctx, execer, AutomationRunCompletion{
			ID:              run.ID,
			Status:          runeffect.StatusFailed,
			Error:           runeffect.Error{Code: FailureCodeSourceKindNotEnabled, Detail: "schedule source kind is not enabled; abandoning stale running run"},
			BlockDefinition: true,
			Now:             now,
		})
		return completeErr == nil, completeErr
	}
	if maxAttempts <= 0 {
		maxAttempts = 1
	}
	if run.AttemptCount >= maxAttempts {
		detail := trim(run.ErrorDetail)
		if detail == "" {
			detail = "retry exhausted after recovering stale running schedule automation run"
		}
		_, err := completeAutomationRun(ctx, execer, AutomationRunCompletion{
			ID:              run.ID,
			Status:          runeffect.StatusFailed,
			Error:           runeffect.Error{Code: FailureCodeTransientWriteError, Detail: detail},
			BlockDefinition: false,
			Now:             now,
		})
		return err == nil, err
	}
	_, err = markAutomationRunRetryWaiting(ctx, execer, AutomationRunRetryUpdate{
		ID:          run.ID,
		NextRetryAt: now,
		ErrorCode:   FailureCodeTransientWriteError,
		ErrorDetail: "recovered stale running schedule automation run",
		Now:         now,
	})
	return err == nil, err
}

func updateDefinitionNextOccurrence(ctx context.Context, execer coredb.QueryExecer, definitionID string, nextOccurrenceAt *time.Time, updatedAt time.Time) error {
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET next_occurrence_at = $2,
    updated_at = $3
WHERE id = $1
`, trim(definitionID), nextOccurrenceAt, updatedAt)
	if err != nil {
		return fmt.Errorf("update schedule next occurrence: %w", err)
	}
	if result.RowsAffected() == 0 {
		return notFound("schedule definition not found")
	}
	return nil
}

func updateDefinitionAfterSuccess(ctx context.Context, execer coredb.QueryExecer, definitionID string, complete bool, updatedAt time.Time) error {
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET last_run_at = $2,
    last_success_at = $2,
    last_failure_code = '',
    last_failure_detail = '',
    status = CASE
        WHEN $3 = TRUE THEN $4
        ELSE status
    END,
    next_occurrence_at = CASE
        WHEN $3 = TRUE THEN NULL
        ELSE next_occurrence_at
    END,
    updated_at = $2
WHERE id = $1
`, trim(definitionID), updatedAt, complete, DefinitionStatusCompleted)
	if err != nil {
		return fmt.Errorf("update schedule definition after success: %w", err)
	}
	if result.RowsAffected() == 0 {
		return notFound("schedule definition not found")
	}
	return nil
}

func updateDefinitionAfterFailure(ctx context.Context, execer coredb.QueryExecer, definitionID string, updatedAt time.Time, code string, detail string, blockDefinition bool) error {
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET last_run_at = $2,
    last_failure_at = $2,
    last_failure_code = $3,
    last_failure_detail = $4,
    status = CASE
        WHEN $5 = TRUE AND status = $6 THEN $7
        ELSE status
    END,
    next_occurrence_at = CASE
        WHEN $5 = TRUE AND status = $6 THEN NULL
        ELSE next_occurrence_at
    END,
    updated_at = $2
WHERE id = $1
`, trim(definitionID), updatedAt, trim(code), trim(detail), blockDefinition, DefinitionStatusActive, DefinitionStatusBlocked)
	if err != nil {
		return fmt.Errorf("update schedule definition after failure: %w", err)
	}
	if result.RowsAffected() == 0 {
		return notFound("schedule definition not found")
	}
	return nil
}

func updateDefinitionAttemptFailure(ctx context.Context, execer coredb.QueryExecer, definitionID string, updatedAt time.Time, code string, detail string) error {
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET last_run_at = $2,
    last_failure_at = $2,
    last_failure_code = $3,
    last_failure_detail = $4,
    updated_at = $2
WHERE id = $1
`, trim(definitionID), updatedAt, trim(code), trim(detail))
	if err != nil {
		return fmt.Errorf("update schedule definition attempt failure: %w", err)
	}
	if result.RowsAffected() == 0 {
		return notFound("schedule definition not found")
	}
	return nil
}

func automationRunSelectSQL() string {
	return `
SELECT id, workspace_id, automation_kind, automation_id, idempotency_key,
       source_occurrence_ref, source_kind, source_type, source_payload,
       status, scheduled_for, occurred_at, attempt_count, attempted_at,
       completed_at, next_retry_at, effect_kind, effect_ref,
       COALESCE(message_id, ''), error_code, error_detail, lease_owner,
       lease_expires_at, target_kind, target_ref, COALESCE(delivery_scope_id, ''),
       COALESCE(permission_check_scope_id, ''), target_snapshot, owner_member_id_snapshot,
       COALESCE(owner_user_id_snapshot, ''), principal_member_id_snapshot, author_member_id_snapshot,
       automation_name_snapshot, notification_mode_snapshot,
       message_template_snapshot, rendered_content_preview, metadata,
       created_at, updated_at
FROM automation_runs`
}

func scanAutomationRun(row coredb.Row) (AutomationRun, error) {
	var run AutomationRun
	var sourcePayload, targetSnapshot, metadata []byte
	var scheduledFor, occurredAt, attemptedAt, completedAt, nextRetryAt, leaseExpiresAt sql.NullTime
	var attemptCount int64
	if err := row.Scan(
		&run.ID,
		&run.WorkspaceID,
		&run.AutomationKind,
		&run.AutomationID,
		&run.IdempotencyKey,
		&run.SourceOccurrenceRef,
		&run.SourceKind,
		&run.SourceType,
		&sourcePayload,
		&run.Status,
		&scheduledFor,
		&occurredAt,
		&attemptCount,
		&attemptedAt,
		&completedAt,
		&nextRetryAt,
		&run.EffectKind,
		&run.EffectRef,
		&run.MessageID,
		&run.ErrorCode,
		&run.ErrorDetail,
		&run.LeaseOwner,
		&leaseExpiresAt,
		&run.TargetKind,
		&run.TargetRef,
		&run.DeliveryScopeID,
		&run.PermissionCheckScopeID,
		&targetSnapshot,
		&run.OwnerMemberIDSnapshot,
		&run.OwnerUserIDSnapshot,
		&run.PrincipalMemberIDSnapshot,
		&run.AuthorMemberIDSnapshot,
		&run.AutomationNameSnapshot,
		&run.NotificationModeSnapshot,
		&run.MessageTemplateSnapshot,
		&run.RenderedContentPreview,
		&metadata,
		&run.CreatedAt,
		&run.UpdatedAt,
	); err != nil {
		return AutomationRun{}, err
	}
	run.ID = trim(run.ID)
	run.WorkspaceID = trim(run.WorkspaceID)
	run.AutomationKind = trim(run.AutomationKind)
	run.AutomationID = trim(run.AutomationID)
	run.IdempotencyKey = trim(run.IdempotencyKey)
	run.SourceOccurrenceRef = trim(run.SourceOccurrenceRef)
	run.SourceKind = trim(run.SourceKind)
	run.SourceType = trim(run.SourceType)
	run.Status = trim(run.Status)
	run.EffectKind = trim(run.EffectKind)
	run.EffectRef = trim(run.EffectRef)
	run.MessageID = trim(run.MessageID)
	run.OwnerMemberIDSnapshot = trim(run.OwnerMemberIDSnapshot)
	run.OwnerUserIDSnapshot = trim(run.OwnerUserIDSnapshot)
	run.PrincipalMemberIDSnapshot = trim(run.PrincipalMemberIDSnapshot)
	run.AuthorMemberIDSnapshot = trim(run.AuthorMemberIDSnapshot)
	run.AttemptCount = int(attemptCount)
	run.ScheduledFor = ptrFromNullTime(scheduledFor)
	run.OccurredAt = ptrFromNullTime(occurredAt)
	run.AttemptedAt = ptrFromNullTime(attemptedAt)
	run.CompletedAt = ptrFromNullTime(completedAt)
	run.NextRetryAt = ptrFromNullTime(nextRetryAt)
	run.LeaseExpiresAt = ptrFromNullTime(leaseExpiresAt)
	run.SourcePayload = sourcePayload
	run.TargetSnapshot = targetSnapshot
	run.Metadata = metadata
	return run, nil
}

func normalizeRunListLimit(limit int) int {
	if limit <= 0 {
		return defaultRunListLimit
	}
	if limit > 1000 {
		return 1000
	}
	return limit
}