package schedule

import (
	"context"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

func TestScheduleAutomationRunStoreReservesAndCompletesDueSchedule(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	fixture := seedScheduleFixture(t, db, "run_success", time.Date(2026, 5, 30, 8, 0, 0, 0, time.UTC))
	store := New(db)

	dueAt := time.Date(2026, 5, 30, 8, 5, 0, 0, time.UTC)
	created, reused, err := store.CreateDefinition(ctx, DefinitionCreate{
		ID:                     "sch_run_success",
		WorkspaceID:            fixture.WorkspaceID,
		OwnerMemberID:          fixture.HumanMemberID,
		PrincipalMemberID:      fixture.HumanMemberID,
		Name:                   "Run success",
		SourceType:             SourceTypeOneShotAbsolute,
		ScheduledAt:            &dueAt,
		TargetKind:             TargetKindChannel,
		TargetRef:              "chn_run_success",
		DeliveryScopeID:        fixture.ScopeID,
		PermissionCheckScopeID: fixture.ScopeID,
		MessageTemplate:        "fire once",
		CreatedByMemberID:      fixture.HumanMemberID,
		CreatedByActorType:     "human",
		CreatedByActorID:       fixture.UserID,
		CreatedVia:             "api",
		Now:                    time.Date(2026, 5, 30, 8, 0, 0, 0, time.UTC),
	})
	require.NoError(t, err)
	require.False(t, reused)
	require.Equal(t, dueAt, *created.NextOccurrenceAt)

	dueIDs, err := store.ListDueDefinitionIDs(ctx, DueDefinitionListQuery{Now: dueAt.Add(-time.Second), Limit: 10})
	require.NoError(t, err)
	require.Empty(t, dueIDs)
	dueIDs, err = store.ListDueDefinitionIDs(ctx, DueDefinitionListQuery{Now: dueAt, Limit: 10})
	require.NoError(t, err)
	require.Equal(t, []string{created.ID}, dueIDs)

	reserved, ok, err := store.ReserveDueAutomationRun(ctx, created.ID, dueAt)
	require.NoError(t, err)
	require.True(t, ok)
	require.Equal(t, AutomationKindSchedule, reserved.Run.AutomationKind)
	require.Equal(t, created.ID, reserved.Run.AutomationID)
	require.Equal(t, SourceKindTime, reserved.Run.SourceKind)
	require.Equal(t, SourceTypeOneShotAbsolute, reserved.Run.SourceType)
	require.Equal(t, string(runeffect.StatusRunning), reserved.Run.Status)
	require.Equal(t, 1, reserved.Run.AttemptCount)
	require.Equal(t, "fire once", reserved.Run.MessageTemplateSnapshot)
	require.Equal(t, "fire once", reserved.Run.RenderedContentPreview)
	require.Equal(t, fixture.HumanMemberID, reserved.Run.OwnerMemberIDSnapshot)
	require.Equal(t, fixture.HumanMemberID, reserved.Run.PrincipalMemberIDSnapshot)
	require.True(t, reserved.CompleteDefinitionAfterSuccess)

	afterReserve, err := store.LoadDefinition(ctx, fixture.WorkspaceID, created.ID)
	require.NoError(t, err)
	require.Nil(t, afterReserve.NextOccurrenceAt)

	messageID := "msg_schedule_run_success"
	seedScheduleMessage(t, db, fixture, messageID, dueAt.Add(time.Second))
	var bound AutomationRun
	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		var bindErr error
		bound, bindErr = store.InTx(tx).BindAutomationRunMessage(ctx, AutomationRunMessageBinding{
			WorkspaceID: fixture.WorkspaceID,
			ID:          reserved.Run.ID,
			MessageID:   messageID,
			Now:         dueAt.Add(time.Second),
		})
		return bindErr
	})
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusRunning), bound.Status)
	require.Equal(t, string(runeffect.EffectKindNone), bound.EffectKind)
	require.Equal(t, messageID, bound.MessageID)
	require.Nil(t, bound.CompletedAt)

	completed, err := store.CompleteAutomationRun(ctx, AutomationRunCompletion{
		WorkspaceID: fixture.WorkspaceID,
		ID:          reserved.Run.ID,
		Status:      runeffect.StatusSucceeded,
		Effect:      runeffect.MessageEffect(messageID),
		Now:         dueAt.Add(2 * time.Second),
	})
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusSucceeded), completed.Status)
	require.Equal(t, string(runeffect.EffectKindMessage), completed.EffectKind)
	require.Equal(t, messageID, completed.EffectRef)
	require.Equal(t, messageID, completed.MessageID)
	require.NotNil(t, completed.CompletedAt)

	finalDefinition, err := store.LoadDefinition(ctx, fixture.WorkspaceID, created.ID)
	require.NoError(t, err)
	require.Equal(t, DefinitionStatusCompleted, finalDefinition.Status)
	require.NotNil(t, finalDefinition.LastRunAt)
	require.NotNil(t, finalDefinition.LastSuccessAt)
	require.Empty(t, finalDefinition.LastFailureCode)
}

func TestScheduleAutomationRunPageUsesEffectiveCursorForUnattemptedRows(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	now := time.Date(2026, 5, 30, 8, 0, 0, 0, time.UTC)
	fixture := seedScheduleFixture(t, db, "run_page_effective_cursor", now)
	store := New(db)
	const automationID = "sch_run_page_effective_cursor"

	attemptedAt := func(value time.Time) *time.Time {
		value = value.UTC()
		return &value
	}
	runs := []AutomationRun{
		{ID: "arun_page_1", AttemptedAt: attemptedAt(now.Add(10 * time.Hour)), CreatedAt: now.Add(1 * time.Hour)},
		{ID: "arun_page_2", AttemptedAt: nil, CreatedAt: now.Add(9 * time.Hour)},
		{ID: "arun_page_3", AttemptedAt: attemptedAt(now.Add(8 * time.Hour)), CreatedAt: now.Add(2 * time.Hour)},
		{ID: "arun_page_4", AttemptedAt: nil, CreatedAt: now.Add(7 * time.Hour)},
		{ID: "arun_page_5", AttemptedAt: nil, CreatedAt: now.Add(6 * time.Hour)},
	}
	for index := range runs {
		run := &runs[index]
		run.WorkspaceID = fixture.WorkspaceID
		run.AutomationKind = AutomationKindSchedule
		run.AutomationID = automationID
		run.IdempotencyKey = "schedule-page-effective-cursor:" + run.ID
		run.SourceOccurrenceRef = "occurrence:" + run.ID
		run.SourceKind = SourceKindTime
		run.SourceType = SourceTypeOneShotAbsolute
		run.Status = string(runeffect.StatusSucceeded)
		run.EffectKind = string(runeffect.EffectKindNone)
		run.TargetKind = string(TargetKindChannel)
		run.TargetRef = fixture.ScopeID
		run.OwnerMemberIDSnapshot = fixture.HumanMemberID
		run.PrincipalMemberIDSnapshot = fixture.HumanMemberID
		run.AuthorMemberIDSnapshot = fixture.HumanMemberID
		run.AutomationNameSnapshot = "Effective cursor schedule"
		run.MessageTemplateSnapshot = "page"
		run.RenderedContentPreview = "page"
		run.UpdatedAt = run.CreatedAt
		require.NoError(t, insertAutomationRun(ctx, db, *run))
	}

	list := func(cursorRun AutomationRun) AutomationRunPage {
		cursorAt := automationRunCursorAt(cursorRun)
		page, err := store.ListAutomationRunPage(ctx, AutomationRunListQuery{
			WorkspaceID: fixture.WorkspaceID, AutomationKind: AutomationKindSchedule,
			AutomationID: automationID, Limit: 2, CursorAttemptedAt: &cursorAt, CursorID: cursorRun.ID,
		})
		require.NoError(t, err)
		return page
	}

	first, err := store.ListAutomationRunPage(ctx, AutomationRunListQuery{
		WorkspaceID: fixture.WorkspaceID, AutomationKind: AutomationKindSchedule,
		AutomationID: automationID, Limit: 2,
	})
	require.NoError(t, err)
	require.True(t, first.HasMore)
	require.Equal(t, []string{"arun_page_1", "arun_page_2"}, runIDs(first.Runs))

	second := list(first.Runs[len(first.Runs)-1])
	require.True(t, second.HasMore)
	require.Equal(t, []string{"arun_page_3", "arun_page_4"}, runIDs(second.Runs))

	third := list(second.Runs[len(second.Runs)-1])
	require.False(t, third.HasMore)
	require.Equal(t, []string{"arun_page_5"}, runIDs(third.Runs))

	// A client that follows the terminal tuple gets a stable empty page rather
	// than seeing the NULL attempted_at rows repeated forever.
	terminal := list(third.Runs[len(third.Runs)-1])
	require.False(t, terminal.HasMore)
	require.Empty(t, terminal.Runs)

	seen := append(append(append([]string{}, runIDs(first.Runs)...), runIDs(second.Runs)...), runIDs(third.Runs)...)
	require.Equal(t, []string{"arun_page_1", "arun_page_2", "arun_page_3", "arun_page_4", "arun_page_5"}, seen)

	cursorAt := automationRunCursorAt(third.Runs[0])
	_, err = store.ListAutomationRunPage(ctx, AutomationRunListQuery{
		WorkspaceID: fixture.WorkspaceID, AutomationKind: AutomationKindSchedule,
		AutomationID: automationID, Limit: 2, CursorAttemptedAt: &cursorAt, CursorID: "arun_missing",
	})
	require.ErrorIs(t, err, ErrBadRequest)

	staleAt := cursorAt.Add(time.Second)
	_, err = store.ListAutomationRunPage(ctx, AutomationRunListQuery{
		WorkspaceID: fixture.WorkspaceID, AutomationKind: AutomationKindSchedule,
		AutomationID: automationID, Limit: 2, CursorAttemptedAt: &staleAt, CursorID: third.Runs[0].ID,
	})
	require.ErrorIs(t, err, ErrBadRequest)
}

func runIDs(runs []AutomationRun) []string {
	ids := make([]string, 0, len(runs))
	for _, run := range runs {
		ids = append(ids, run.ID)
	}
	return ids
}

func TestScheduleAutomationRunStoreFiltersSchedulerListsByWorkspaceGroup(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	now := time.Date(2026, 5, 30, 13, 0, 0, 0, time.UTC)
	fixtureA := seedScheduleFixture(t, db, "scheduler_group_a", now)
	fixtureB := seedScheduleFixture(t, db, "scheduler_group_b", now)
	seedWorkspacePlacement(t, db, fixtureA, "schedule-group-a", "active", now)
	seedWorkspacePlacement(t, db, fixtureB, "schedule-group-b", "active", now)

	allStore := New(db)
	groupAStore := NewWithOptions(db, StoreOptions{WorkspaceGroupID: " schedule-group-a "})
	dueAt := now.Add(5 * time.Minute)
	createdA := createOneShotScheduleDefinition(t, ctx, allStore, fixtureA, "scheduler_group_a", dueAt, now)
	createdB := createOneShotScheduleDefinition(t, ctx, allStore, fixtureB, "scheduler_group_b", dueAt, now)

	dueIDs, err := groupAStore.ListDueDefinitionIDs(ctx, DueDefinitionListQuery{Now: dueAt, Limit: 10})
	require.NoError(t, err)
	require.Equal(t, []string{createdA.ID}, dueIDs)

	reservedA, ok, err := allStore.ReserveDueAutomationRun(ctx, createdA.ID, dueAt)
	require.NoError(t, err)
	require.True(t, ok)
	reservedB, ok, err := allStore.ReserveDueAutomationRun(ctx, createdB.ID, dueAt)
	require.NoError(t, err)
	require.True(t, ok)

	staleIDs, err := groupAStore.ListStaleRunningRunIDs(ctx, dueAt.Add(time.Second), 10)
	require.NoError(t, err)
	require.Equal(t, []string{reservedA.Run.ID}, staleIDs)

	nextRetryAt := dueAt.Add(2 * time.Second)
	_, err = allStore.MarkAutomationRunRetryWaiting(ctx, AutomationRunRetryUpdate{
		WorkspaceID: fixtureA.WorkspaceID,
		ID:          reservedA.Run.ID,
		NextRetryAt: nextRetryAt,
		ErrorCode:   FailureCodeTransientWriteError,
		ErrorDetail: "temporary failure in group a",
		Now:         dueAt.Add(time.Second),
	})
	require.NoError(t, err)
	_, err = allStore.MarkAutomationRunRetryWaiting(ctx, AutomationRunRetryUpdate{
		WorkspaceID: fixtureB.WorkspaceID,
		ID:          reservedB.Run.ID,
		NextRetryAt: nextRetryAt,
		ErrorCode:   FailureCodeTransientWriteError,
		ErrorDetail: "temporary failure in group b",
		Now:         dueAt.Add(time.Second),
	})
	require.NoError(t, err)

	retryIDs, err := groupAStore.ListRetryRunIDs(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Equal(t, []string{reservedA.Run.ID}, retryIDs)

	_, err = allStore.DeleteDefinition(ctx, fixtureA.WorkspaceID, createdA.ID, nextRetryAt.Add(time.Second))
	require.NoError(t, err)
	_, err = allStore.DeleteDefinition(ctx, fixtureB.WorkspaceID, createdB.ID, nextRetryAt.Add(time.Second))
	require.NoError(t, err)

	abandonedCount, err := groupAStore.AbandonInactiveRetryRuns(ctx, nextRetryAt.Add(2*time.Second), 10)
	require.NoError(t, err)
	require.Equal(t, 1, abandonedCount)

	abandonedA, err := allStore.LoadAutomationRun(ctx, fixtureA.WorkspaceID, reservedA.Run.ID)
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusFailed), abandonedA.Status)
	stillWaitingB, err := allStore.LoadAutomationRun(ctx, fixtureB.WorkspaceID, reservedB.Run.ID)
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusRetryWaiting), stillWaitingB.Status)
}

func TestScheduleAutomationRunStoreSchedulerListsUseRoutableWorkspacePlacements(t *testing.T) {
	t.Parallel()

	cases := []struct {
		state    string
		routable bool
	}{
		{state: "active", routable: true},
		{state: "draining", routable: true},
		{state: "moving", routable: true},
		{state: "disabled", routable: false},
	}

	for _, tc := range cases {
		t.Run(tc.state, func(t *testing.T) {
			ctx := context.Background()
			db := startScheduleRollbackDB(t, ctx)
			now := time.Date(2026, 5, 30, 14, 0, 0, 0, time.UTC)
			suffix := "scheduler_routable_" + tc.state
			fixture := seedScheduleFixture(t, db, suffix, now)
			seedWorkspacePlacement(t, db, fixture, "schedule-routable-group", tc.state, now)

			allStore := New(db)
			groupStore := NewWithOptions(db, StoreOptions{WorkspaceGroupID: "schedule-routable-group"})
			dueAt := now.Add(5 * time.Minute)
			created := createOneShotScheduleDefinition(t, ctx, allStore, fixture, suffix, dueAt, now)

			dueIDs, err := groupStore.ListDueDefinitionIDs(ctx, DueDefinitionListQuery{Now: dueAt, Limit: 10})
			require.NoError(t, err)
			if tc.routable {
				require.Equal(t, []string{created.ID}, dueIDs)
			} else {
				require.Empty(t, dueIDs)
			}

			reserved, ok, err := allStore.ReserveDueAutomationRun(ctx, created.ID, dueAt)
			require.NoError(t, err)
			require.True(t, ok)

			staleIDs, err := groupStore.ListStaleRunningRunIDs(ctx, dueAt.Add(time.Second), 10)
			require.NoError(t, err)
			if tc.routable {
				require.Equal(t, []string{reserved.Run.ID}, staleIDs)
			} else {
				require.Empty(t, staleIDs)
			}

			nextRetryAt := dueAt.Add(2 * time.Second)
			_, err = allStore.MarkAutomationRunRetryWaiting(ctx, AutomationRunRetryUpdate{
				WorkspaceID: fixture.WorkspaceID,
				ID:          reserved.Run.ID,
				NextRetryAt: nextRetryAt,
				ErrorCode:   FailureCodeTransientWriteError,
				ErrorDetail: "temporary failure in " + tc.state,
				Now:         dueAt.Add(time.Second),
			})
			require.NoError(t, err)

			retryIDs, err := groupStore.ListRetryRunIDs(ctx, nextRetryAt, 10)
			require.NoError(t, err)
			if tc.routable {
				require.Equal(t, []string{reserved.Run.ID}, retryIDs)
			} else {
				require.Empty(t, retryIDs)
			}

			_, err = allStore.DeleteDefinition(ctx, fixture.WorkspaceID, created.ID, nextRetryAt.Add(time.Second))
			require.NoError(t, err)

			abandonedCount, err := groupStore.AbandonInactiveRetryRuns(ctx, nextRetryAt.Add(2*time.Second), 10)
			require.NoError(t, err)
			if tc.routable {
				require.Equal(t, 1, abandonedCount)
			} else {
				require.Zero(t, abandonedCount)
			}

			updatedRun, err := allStore.LoadAutomationRun(ctx, fixture.WorkspaceID, reserved.Run.ID)
			require.NoError(t, err)
			if tc.routable {
				require.Equal(t, string(runeffect.StatusFailed), updatedRun.Status)
			} else {
				require.Equal(t, string(runeffect.StatusRetryWaiting), updatedRun.Status)
			}
		})
	}
}

func createOneShotScheduleDefinition(t testing.TB, ctx context.Context, store *Store, fixture scheduleFixture, suffix string, dueAt time.Time, now time.Time) Definition {
	t.Helper()
	created, reused, err := store.CreateDefinition(ctx, DefinitionCreate{
		ID:                     "sch_" + suffix,
		WorkspaceID:            fixture.WorkspaceID,
		OwnerMemberID:          fixture.HumanMemberID,
		PrincipalMemberID:      fixture.HumanMemberID,
		Name:                   "Schedule " + suffix,
		SourceType:             SourceTypeOneShotAbsolute,
		ScheduledAt:            &dueAt,
		TargetKind:             TargetKindChannel,
		TargetRef:              "chn_" + suffix,
		DeliveryScopeID:        fixture.ScopeID,
		PermissionCheckScopeID: fixture.ScopeID,
		MessageTemplate:        "fire " + suffix,
		CreatedByMemberID:      fixture.HumanMemberID,
		CreatedByActorType:     "human",
		CreatedByActorID:       fixture.UserID,
		CreatedVia:             "api",
		Now:                    now,
	})
	require.NoError(t, err)
	require.False(t, reused)
	return created
}

func TestScheduleAutomationRunStoreRetriesAndBlocksOnTerminalFailure(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	fixture := seedScheduleFixture(t, db, "run_retry", time.Date(2026, 5, 30, 9, 0, 0, 0, time.UTC))
	store := New(db)

	anchorAt := time.Date(2026, 5, 30, 9, 5, 0, 0, time.UTC)
	intervalSeconds := int64(300)
	created, _, err := store.CreateDefinition(ctx, DefinitionCreate{
		ID:                     "sch_run_retry",
		WorkspaceID:            fixture.WorkspaceID,
		OwnerMemberID:          fixture.HumanMemberID,
		PrincipalMemberID:      fixture.HumanMemberID,
		Name:                   "Retrying schedule",
		SourceType:             SourceTypeInterval,
		AnchorAt:               &anchorAt,
		IntervalSeconds:        &intervalSeconds,
		TargetKind:             TargetKindChannel,
		TargetRef:              "chn_run_retry",
		DeliveryScopeID:        fixture.ScopeID,
		PermissionCheckScopeID: fixture.ScopeID,
		MessageTemplate:        "retry me",
		CreatedByMemberID:      fixture.HumanMemberID,
		CreatedByActorType:     "human",
		CreatedByActorID:       fixture.UserID,
		CreatedVia:             "api",
		Now:                    time.Date(2026, 5, 30, 9, 0, 0, 0, time.UTC),
	})
	require.NoError(t, err)
	require.Equal(t, anchorAt, *created.NextOccurrenceAt)

	reserved, ok, err := store.ReserveDueAutomationRun(ctx, created.ID, anchorAt)
	require.NoError(t, err)
	require.True(t, ok)
	require.False(t, reserved.CompleteDefinitionAfterSuccess)
	afterReserve, err := store.LoadDefinition(ctx, fixture.WorkspaceID, created.ID)
	require.NoError(t, err)
	require.Equal(t, anchorAt.Add(5*time.Minute), *afterReserve.NextOccurrenceAt)
	messageID := "msg_schedule_run_retry"
	seedScheduleMessage(t, db, fixture, messageID, anchorAt.Add(time.Second))
	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		_, bindErr := store.InTx(tx).BindAutomationRunMessage(ctx, AutomationRunMessageBinding{
			WorkspaceID: fixture.WorkspaceID,
			ID:          reserved.Run.ID,
			MessageID:   messageID,
			Now:         anchorAt.Add(time.Second),
		})
		return bindErr
	})
	require.NoError(t, err)

	nextRetryAt := anchorAt.Add(30 * time.Second)
	retryWaiting, err := store.MarkAutomationRunRetryWaiting(ctx, AutomationRunRetryUpdate{
		WorkspaceID: fixture.WorkspaceID,
		ID:          reserved.Run.ID,
		NextRetryAt: nextRetryAt,
		ErrorCode:   FailureCodeTransientWriteError,
		ErrorDetail: "temporary writer failure",
		Now:         anchorAt.Add(10 * time.Second),
	})
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusRetryWaiting), retryWaiting.Status)
	require.Equal(t, FailureCodeTransientWriteError, retryWaiting.ErrorCode)
	require.Equal(t, messageID, retryWaiting.MessageID)

	retryIDs, err := store.ListRetryRunIDs(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Equal(t, []string{reserved.Run.ID}, retryIDs)

	claimed, ok, err := store.ClaimRetryAutomationRun(ctx, reserved.Run.ID, nextRetryAt)
	require.NoError(t, err)
	require.True(t, ok)
	require.Equal(t, 2, claimed.Run.AttemptCount)
	require.Equal(t, string(runeffect.StatusRunning), claimed.Run.Status)
	require.Equal(t, messageID, claimed.Run.MessageID)
	require.Nil(t, claimed.Run.NextRetryAt)

	failed, err := store.CompleteAutomationRun(ctx, AutomationRunCompletion{
		WorkspaceID:     fixture.WorkspaceID,
		ID:              claimed.Run.ID,
		Status:          runeffect.StatusFailed,
		Error:           runeffect.Error{Code: FailureCodeForbiddenCurrentPerms, Detail: "target permission revoked"},
		BlockDefinition: true,
		Now:             nextRetryAt.Add(time.Second),
	})
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusFailed), failed.Status)
	require.Equal(t, FailureCodeForbiddenCurrentPerms, failed.ErrorCode)

	blocked, err := store.LoadDefinition(ctx, fixture.WorkspaceID, created.ID)
	require.NoError(t, err)
	require.Equal(t, DefinitionStatusBlocked, blocked.Status)
	require.Nil(t, blocked.NextOccurrenceAt)
	require.Equal(t, FailureCodeForbiddenCurrentPerms, blocked.LastFailureCode)
	require.Equal(t, "target permission revoked", blocked.LastFailureDetail)
}

func TestScheduleAutomationRunStoreAbandonsRetryWhenDefinitionIsDeleted(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	fixture := seedScheduleFixture(t, db, "run_retry_deleted", time.Date(2026, 5, 30, 11, 0, 0, 0, time.UTC))
	store := New(db)

	dueAt := time.Date(2026, 5, 30, 11, 5, 0, 0, time.UTC)
	created, _, err := store.CreateDefinition(ctx, DefinitionCreate{
		ID:                     "sch_run_retry_deleted",
		WorkspaceID:            fixture.WorkspaceID,
		OwnerMemberID:          fixture.HumanMemberID,
		PrincipalMemberID:      fixture.HumanMemberID,
		Name:                   "Retry deleted schedule",
		SourceType:             SourceTypeOneShotAbsolute,
		ScheduledAt:            &dueAt,
		TargetKind:             TargetKindChannel,
		TargetRef:              "chn_run_retry_deleted",
		DeliveryScopeID:        fixture.ScopeID,
		PermissionCheckScopeID: fixture.ScopeID,
		MessageTemplate:        "retry after delete",
		CreatedByMemberID:      fixture.HumanMemberID,
		CreatedByActorType:     "human",
		CreatedByActorID:       fixture.UserID,
		CreatedVia:             "api",
		Now:                    time.Date(2026, 5, 30, 11, 0, 0, 0, time.UTC),
	})
	require.NoError(t, err)
	reserved, ok, err := store.ReserveDueAutomationRun(ctx, created.ID, dueAt)
	require.NoError(t, err)
	require.True(t, ok)

	nextRetryAt := dueAt.Add(time.Minute)
	_, err = store.MarkAutomationRunRetryWaiting(ctx, AutomationRunRetryUpdate{
		WorkspaceID: fixture.WorkspaceID,
		ID:          reserved.Run.ID,
		NextRetryAt: nextRetryAt,
		ErrorCode:   FailureCodeTransientWriteError,
		ErrorDetail: "temporary failure before deletion",
		Now:         dueAt.Add(time.Second),
	})
	require.NoError(t, err)
	_, err = store.DeleteDefinition(ctx, fixture.WorkspaceID, created.ID, dueAt.Add(2*time.Second))
	require.NoError(t, err)

	retryIDs, err := store.ListRetryRunIDs(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Empty(t, retryIDs)

	abandonedCount, err := store.AbandonInactiveRetryRuns(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Equal(t, 1, abandonedCount)

	abandoned, err := store.LoadAutomationRun(ctx, fixture.WorkspaceID, reserved.Run.ID)
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusFailed), abandoned.Status)
	require.Equal(t, FailureCodeScheduleNotActive, abandoned.ErrorCode)
	require.NotNil(t, abandoned.CompletedAt)
}

func TestScheduleAutomationRunStoreKeepsPausedRetryWaitingForResume(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	fixture := seedScheduleFixture(t, db, "run_retry_paused", time.Date(2026, 5, 30, 11, 30, 0, 0, time.UTC))
	store := New(db)

	dueAt := time.Date(2026, 5, 30, 11, 35, 0, 0, time.UTC)
	intervalSeconds := int64(300)
	created, _, err := store.CreateDefinition(ctx, DefinitionCreate{
		ID:                     "sch_run_retry_paused",
		WorkspaceID:            fixture.WorkspaceID,
		OwnerMemberID:          fixture.HumanMemberID,
		PrincipalMemberID:      fixture.HumanMemberID,
		Name:                   "Retry paused schedule",
		SourceType:             SourceTypeInterval,
		AnchorAt:               &dueAt,
		IntervalSeconds:        &intervalSeconds,
		TargetKind:             TargetKindChannel,
		TargetRef:              "chn_run_retry_paused",
		DeliveryScopeID:        fixture.ScopeID,
		PermissionCheckScopeID: fixture.ScopeID,
		MessageTemplate:        "retry after pause",
		CreatedByMemberID:      fixture.HumanMemberID,
		CreatedByActorType:     "human",
		CreatedByActorID:       fixture.UserID,
		CreatedVia:             "api",
		Now:                    time.Date(2026, 5, 30, 11, 30, 0, 0, time.UTC),
	})
	require.NoError(t, err)
	reserved, ok, err := store.ReserveDueAutomationRun(ctx, created.ID, dueAt)
	require.NoError(t, err)
	require.True(t, ok)

	nextRetryAt := dueAt.Add(time.Minute)
	_, err = store.MarkAutomationRunRetryWaiting(ctx, AutomationRunRetryUpdate{
		WorkspaceID: fixture.WorkspaceID,
		ID:          reserved.Run.ID,
		NextRetryAt: nextRetryAt,
		ErrorCode:   FailureCodeTransientWriteError,
		ErrorDetail: "temporary failure before pause",
		Now:         dueAt.Add(time.Second),
	})
	require.NoError(t, err)
	_, err = store.SetDefinitionStatus(ctx, DefinitionStatusUpdate{
		WorkspaceID: fixture.WorkspaceID,
		ID:          created.ID,
		Status:      DefinitionStatusPaused,
		Reason:      "user_request",
		Now:         dueAt.Add(2 * time.Second),
	})
	require.NoError(t, err)

	abandonedCount, err := store.AbandonInactiveRetryRuns(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Zero(t, abandonedCount)
	retryIDs, err := store.ListRetryRunIDs(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Empty(t, retryIDs)

	_, err = store.SetDefinitionStatus(ctx, DefinitionStatusUpdate{
		WorkspaceID: fixture.WorkspaceID,
		ID:          created.ID,
		Status:      DefinitionStatusActive,
		Now:         dueAt.Add(3 * time.Second),
	})
	require.NoError(t, err)
	retryIDs, err = store.ListRetryRunIDs(ctx, nextRetryAt, 10)
	require.NoError(t, err)
	require.Equal(t, []string{reserved.Run.ID}, retryIDs)
}

func TestScheduleAutomationRunStoreRecoversDeletedStaleRunningRun(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startScheduleRollbackDB(t, ctx)
	fixture := seedScheduleFixture(t, db, "run_stale_deleted", time.Date(2026, 5, 30, 12, 0, 0, 0, time.UTC))
	store := New(db)

	dueAt := time.Date(2026, 5, 30, 12, 5, 0, 0, time.UTC)
	created, _, err := store.CreateDefinition(ctx, DefinitionCreate{
		ID:                     "sch_run_stale_deleted",
		WorkspaceID:            fixture.WorkspaceID,
		OwnerMemberID:          fixture.HumanMemberID,
		PrincipalMemberID:      fixture.HumanMemberID,
		Name:                   "Stale deleted schedule",
		SourceType:             SourceTypeOneShotAbsolute,
		ScheduledAt:            &dueAt,
		TargetKind:             TargetKindChannel,
		TargetRef:              "chn_run_stale_deleted",
		DeliveryScopeID:        fixture.ScopeID,
		PermissionCheckScopeID: fixture.ScopeID,
		MessageTemplate:        "stale after delete",
		CreatedByMemberID:      fixture.HumanMemberID,
		CreatedByActorType:     "human",
		CreatedByActorID:       fixture.UserID,
		CreatedVia:             "api",
		Now:                    time.Date(2026, 5, 30, 12, 0, 0, 0, time.UTC),
	})
	require.NoError(t, err)
	reserved, ok, err := store.ReserveDueAutomationRun(ctx, created.ID, dueAt)
	require.NoError(t, err)
	require.True(t, ok)
	_, err = store.DeleteDefinition(ctx, fixture.WorkspaceID, created.ID, dueAt.Add(time.Second))
	require.NoError(t, err)

	recovered, err := store.RecoverStaleRunningRun(ctx, reserved.Run.ID, dueAt.Add(time.Second), dueAt.Add(2*time.Second), 3)
	require.NoError(t, err)
	require.True(t, recovered)

	abandoned, err := store.LoadAutomationRun(ctx, fixture.WorkspaceID, reserved.Run.ID)
	require.NoError(t, err)
	require.Equal(t, string(runeffect.StatusFailed), abandoned.Status)
	require.Equal(t, FailureCodeScheduleNotActive, abandoned.ErrorCode)
	require.NotNil(t, abandoned.CompletedAt)
}

func seedScheduleMessage(t testing.TB, db coredb.QueryExecer, fixture scheduleFixture, messageID string, now time.Time) {
	t.Helper()
	mustExec(t, db, `
INSERT INTO scope_messages (
  id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id,
  author_type, client_message_id, message_type, content, metadata, created_at, updated_at
) VALUES ($1, $2, $3, 1, 1, $4, 'human', $5, '', 'scheduled output', '{}', $6, $6)
`, messageID, fixture.WorkspaceID, fixture.ScopeID, fixture.HumanMemberID, "schedule-client-"+messageID, now)
}