package automation

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

func (s *Store) RegisterRunnerWithToken(ctx context.Context, runner AutomationRunner, token AutomationRunnerToken, now time.Time) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	runner.WorkspaceID = strings.TrimSpace(runner.WorkspaceID)
	runner.RunnerID = strings.TrimSpace(runner.RunnerID)
	token.WorkspaceID = runner.WorkspaceID
	token.RunnerID = runner.RunnerID
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Model(&AutomationRunnerToken{}).
			Where("workspace_id = ? AND runner_id = ? AND token_kind = ? AND revoked_at IS NULL AND used_at IS NULL", runner.WorkspaceID, runner.RunnerID, AutomationRunnerTokenKindRegistration).
			Updates(map[string]any{"revoked_at": now, "updated_at": now}).Error; err != nil {
			return fmt.Errorf("revoke previous automation runner registration tokens: %w", err)
		}
		if err := tx.Model(&AutomationRunnerToken{}).
			Where("workspace_id = ? AND runner_id = ? AND token_kind = ? AND revoked_at IS NULL", runner.WorkspaceID, runner.RunnerID, AutomationRunnerTokenKindSession).
			Updates(map[string]any{"revoked_at": now, "updated_at": now}).Error; err != nil {
			return fmt.Errorf("revoke previous automation runner session tokens: %w", err)
		}
		if err := tx.Model(&AutomationRunnerSession{}).
			Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", runner.WorkspaceID, runner.RunnerID, AutomationRunnerSessionStatusActive).
			Updates(map[string]any{"status": AutomationRunnerSessionStatusRevoked, "revoked_at": now, "updated_at": now}).Error; err != nil {
			return fmt.Errorf("revoke previous automation runner sessions: %w", err)
		}
		if err := tx.Model(&AutomationActionsRunnerCredential{}).
			Where("workspace_id = ? AND runner_id = ? AND revoked_at IS NULL", runner.WorkspaceID, runner.RunnerID).
			Updates(map[string]any{"revoked_at": now, "updated_at": now}).Error; err != nil {
			return fmt.Errorf("revoke previous actions runner credentials: %w", err)
		}
		var existing AutomationRunner
		err := tx.Where("workspace_id = ? AND runner_id = ?", runner.WorkspaceID, runner.RunnerID).First(&existing).Error
		switch {
		case err == nil:
			if err := tx.Model(&AutomationRunner{}).Where("workspace_id = ? AND runner_id = ?", runner.WorkspaceID, runner.RunnerID).Updates(map[string]any{
				"name":          runner.Name,
				"runner_group":  runner.RunnerGroup,
				"labels_json":   runner.LabelsJSON,
				"status":        AutomationRunnerStatusActive,
				"last_seen_at":  nil,
				"revoked_at":    nil,
				"registered_at": runner.RegisteredAt,
				"updated_at":    runner.UpdatedAt,
			}).Error; err != nil {
				return fmt.Errorf("update automation runner: %w", err)
			}
		case errors.Is(err, gorm.ErrRecordNotFound):
			if err := tx.Create(&runner).Error; err != nil {
				return fmt.Errorf("create automation runner: %w", err)
			}
		default:
			return fmt.Errorf("get automation runner: %w", err)
		}
		if err := tx.Create(&token).Error; err != nil {
			return fmt.Errorf("create automation runner registration token: %w", err)
		}
		return nil
	})
}

// GetActiveRunnerRegistrationByTokenHash resolves an unused registration token
// to its active runner row.
func (s *Store) GetActiveRunnerRegistrationByTokenHash(ctx context.Context, registrationTokenHash string, now time.Time) (AutomationRunner, AutomationRunnerToken, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunner{}, AutomationRunnerToken{}, err
	}
	registrationTokenHash = strings.TrimSpace(registrationTokenHash)
	var token AutomationRunnerToken
	if err := s.db.WithContext(ctx).
		Where("token_hash = ? AND token_kind = ? AND revoked_at IS NULL AND used_at IS NULL AND expires_at > ?", registrationTokenHash, AutomationRunnerTokenKindRegistration, now).
		First(&token).Error; err != nil {
		return AutomationRunner{}, AutomationRunnerToken{}, err
	}
	var runner AutomationRunner
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", token.WorkspaceID, token.RunnerID, AutomationRunnerStatusActive).
		First(&runner).Error; err != nil {
		return AutomationRunner{}, AutomationRunnerToken{}, err
	}
	return runner, token, nil
}

func (s *Store) CreateRunnerSessionWithToken(ctx context.Context, registrationTokenHash string, session AutomationRunnerSession, sessionToken AutomationRunnerToken, now time.Time) (AutomationRunnerSession, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunnerSession{}, err
	}
	registrationTokenHash = strings.TrimSpace(registrationTokenHash)
	session.WorkspaceID = strings.TrimSpace(session.WorkspaceID)
	session.RunnerID = strings.TrimSpace(session.RunnerID)
	session.SessionID = strings.TrimSpace(session.SessionID)
	sessionToken.WorkspaceID = session.WorkspaceID
	sessionToken.RunnerID = session.RunnerID
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var token AutomationRunnerToken
		if err := tx.Where("token_hash = ? AND token_kind = ? AND workspace_id = ? AND runner_id = ? AND revoked_at IS NULL AND used_at IS NULL AND expires_at > ?", registrationTokenHash, AutomationRunnerTokenKindRegistration, session.WorkspaceID, session.RunnerID, now).First(&token).Error; err != nil {
			return err
		}
		var runner AutomationRunner
		if err := tx.Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", session.WorkspaceID, session.RunnerID, AutomationRunnerStatusActive).First(&runner).Error; err != nil {
			return err
		}
		usedAt := now
		consume := tx.Model(&AutomationRunnerToken{}).Where("token_hash = ? AND used_at IS NULL", token.TokenHash).Updates(map[string]any{"used_at": usedAt, "updated_at": now})
		if consume.Error != nil {
			return fmt.Errorf("consume automation runner registration token: %w", consume.Error)
		}
		if consume.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		if err := createRunnerSessionForRunnerTx(tx, &session, &sessionToken, runner, now); err != nil {
			return err
		}
		return nil
	})
	if err != nil {
		return AutomationRunnerSession{}, err
	}
	return session, nil
}

// CreateRunnerSessionForRunner creates a session for an already-authenticated
// active runner without consuming registration-token state.
func (s *Store) CreateRunnerSessionForRunner(ctx context.Context, session AutomationRunnerSession, sessionToken AutomationRunnerToken, now time.Time) (AutomationRunnerSession, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunnerSession{}, err
	}
	session.WorkspaceID = strings.TrimSpace(session.WorkspaceID)
	session.RunnerID = strings.TrimSpace(session.RunnerID)
	session.SessionID = strings.TrimSpace(session.SessionID)
	sessionToken.WorkspaceID = session.WorkspaceID
	sessionToken.RunnerID = session.RunnerID
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var runner AutomationRunner
		if err := tx.Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", session.WorkspaceID, session.RunnerID, AutomationRunnerStatusActive).First(&runner).Error; err != nil {
			return err
		}
		return createRunnerSessionForRunnerTx(tx, &session, &sessionToken, runner, now)
	})
	if err != nil {
		return AutomationRunnerSession{}, err
	}
	return session, nil
}

// GetActiveRunnerSessionForRunner returns the current active session and native
// session-token row for one active runner.
func (s *Store) GetActiveRunnerSessionForRunner(ctx context.Context, workspaceID string, runnerID string, now time.Time) (AutomationRunnerSession, AutomationRunnerToken, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunnerSession{}, AutomationRunnerToken{}, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	runnerID = strings.TrimSpace(runnerID)
	var session AutomationRunnerSession
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL AND expires_at > ?", workspaceID, runnerID, AutomationRunnerSessionStatusActive, now).
		Where("EXISTS (SELECT 1 FROM automation_runners WHERE automation_runners.workspace_id = automation_runner_sessions.workspace_id AND automation_runners.runner_id = automation_runner_sessions.runner_id AND automation_runners.status = ? AND automation_runners.revoked_at IS NULL)", AutomationRunnerStatusActive).
		Order("updated_at DESC").
		First(&session).Error; err != nil {
		return AutomationRunnerSession{}, AutomationRunnerToken{}, err
	}
	var token AutomationRunnerToken
	loadToken := func(sessionID string) error {
		return s.db.WithContext(ctx).
			Where("workspace_id = ? AND runner_id = ? AND token_kind = ? AND revoked_at IS NULL AND expires_at > ?", workspaceID, runnerID, AutomationRunnerTokenKindSession, now).
			Where("session_id = ?", sessionID).
			Order("updated_at DESC").
			First(&token).Error
	}
	if err := loadToken(session.SessionID); err != nil {
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return AutomationRunnerSession{}, AutomationRunnerToken{}, err
		}
		if err := loadToken(""); err != nil {
			return AutomationRunnerSession{}, AutomationRunnerToken{}, err
		}
	}
	return session, token, nil
}

func createRunnerSessionForRunnerTx(tx *gorm.DB, session *AutomationRunnerSession, sessionToken *AutomationRunnerToken, runner AutomationRunner, now time.Time) error {
	if err := tx.Model(&AutomationRunnerToken{}).
		Where("workspace_id = ? AND runner_id = ? AND token_kind = ? AND revoked_at IS NULL", session.WorkspaceID, session.RunnerID, AutomationRunnerTokenKindSession).
		Updates(map[string]any{"revoked_at": now, "updated_at": now}).Error; err != nil {
		return fmt.Errorf("revoke old automation runner session tokens: %w", err)
	}
	if err := tx.Model(&AutomationRunnerSession{}).
		Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", session.WorkspaceID, session.RunnerID, AutomationRunnerSessionStatusActive).
		Updates(map[string]any{"status": AutomationRunnerSessionStatusRevoked, "revoked_at": now, "updated_at": now}).Error; err != nil {
		return fmt.Errorf("revoke old automation runner sessions: %w", err)
	}
	session.RunnerGroup = runner.RunnerGroup
	session.LabelsJSON = runner.LabelsJSON
	if err := tx.Save(session).Error; err != nil {
		return fmt.Errorf("create automation runner session: %w", err)
	}
	if err := tx.Create(sessionToken).Error; err != nil {
		return fmt.Errorf("create automation runner session token: %w", err)
	}
	if err := tx.Model(&AutomationRunner{}).Where("workspace_id = ? AND runner_id = ?", session.WorkspaceID, session.RunnerID).Updates(map[string]any{
		"last_seen_at": now,
		"updated_at":   now,
	}).Error; err != nil {
		return fmt.Errorf("mark automation runner seen: %w", err)
	}
	return nil
}

// UpsertActionsRunnerCredentialWithRegistrationTokenHash consumes one active
// registration token and stores official runner OAuth client metadata in the
// same transaction.
func (s *Store) UpsertActionsRunnerCredentialWithRegistrationTokenHash(ctx context.Context, registrationTokenHash string, credential AutomationActionsRunnerCredential, now time.Time) (AutomationActionsRunnerCredential, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationActionsRunnerCredential{}, err
	}
	registrationTokenHash = strings.TrimSpace(registrationTokenHash)
	credential = normalizeActionsRunnerCredential(credential, now)
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var runner AutomationRunner
		if err := tx.Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", credential.WorkspaceID, credential.RunnerID, AutomationRunnerStatusActive).First(&runner).Error; err != nil {
			return err
		}
		usedAt := now
		consume := tx.Model(&AutomationRunnerToken{}).
			Where("token_hash = ? AND token_kind = ? AND workspace_id = ? AND runner_id = ? AND revoked_at IS NULL AND used_at IS NULL AND expires_at > ?", registrationTokenHash, AutomationRunnerTokenKindRegistration, credential.WorkspaceID, credential.RunnerID, now).
			Updates(map[string]any{"used_at": usedAt, "updated_at": now})
		if consume.Error != nil {
			return fmt.Errorf("consume automation runner registration token: %w", consume.Error)
		}
		if consume.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		return upsertActionsRunnerCredentialTx(tx, credential, now)
	})
	if err != nil {
		return AutomationActionsRunnerCredential{}, err
	}
	return credential, nil
}

func normalizeActionsRunnerCredential(credential AutomationActionsRunnerCredential, now time.Time) AutomationActionsRunnerCredential {
	credential.ClientID = strings.TrimSpace(credential.ClientID)
	credential.WorkspaceID = strings.TrimSpace(credential.WorkspaceID)
	credential.RunnerID = strings.TrimSpace(credential.RunnerID)
	credential.PublicKey = strings.TrimSpace(credential.PublicKey)
	credential.CreatedAt = now
	credential.UpdatedAt = now
	credential.RevokedAt = nil
	return credential
}

func upsertActionsRunnerCredentialTx(tx *gorm.DB, credential AutomationActionsRunnerCredential, now time.Time) error {
	if err := tx.Model(&AutomationActionsRunnerCredential{}).
		Where("workspace_id = ? AND runner_id = ? AND client_id <> ? AND revoked_at IS NULL", credential.WorkspaceID, credential.RunnerID, credential.ClientID).
		Updates(map[string]any{"revoked_at": now, "updated_at": now}).Error; err != nil {
		return fmt.Errorf("revoke previous actions runner credentials: %w", err)
	}
	if err := tx.Clauses(clause.OnConflict{
		Columns: []clause.Column{{Name: "client_id"}},
		DoUpdates: clause.Assignments(map[string]any{
			"workspace_id":       credential.WorkspaceID,
			"runner_id":          credential.RunnerID,
			"official_runner_id": credential.OfficialRunnerID,
			"public_key":         credential.PublicKey,
			"updated_at":         now,
			"revoked_at":         nil,
		}),
	}).Create(&credential).Error; err != nil {
		return fmt.Errorf("upsert actions runner credential: %w", err)
	}
	return nil
}

// CreateActionsRunnerAccessToken stores one official-runner OAuth access token
// hash for later broker/run-service authentication.
func (s *Store) CreateActionsRunnerAccessToken(ctx context.Context, token AutomationRunnerToken) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	token.TokenHash = strings.TrimSpace(token.TokenHash)
	token.WorkspaceID = strings.TrimSpace(token.WorkspaceID)
	token.RunnerID = strings.TrimSpace(token.RunnerID)
	token.TokenScope = strings.TrimSpace(token.TokenScope)
	token.SessionID = strings.TrimSpace(token.SessionID)
	token.SessionTokenHash = strings.TrimSpace(token.SessionTokenHash)
	token.TokenKind = AutomationRunnerTokenKindActionsAccess
	return s.db.WithContext(ctx).Create(&token).Error
}

// GetActiveActionsRunnerAccessTokenByHash resolves one unexpired official
// access token by its full token hash.
func (s *Store) GetActiveActionsRunnerAccessTokenByHash(ctx context.Context, tokenHash string, now time.Time) (AutomationRunnerToken, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunnerToken{}, err
	}
	tokenHash = strings.TrimSpace(tokenHash)
	var token AutomationRunnerToken
	if err := s.db.WithContext(ctx).
		Where("token_hash = ? AND token_kind = ? AND revoked_at IS NULL AND expires_at > ?", tokenHash, AutomationRunnerTokenKindActionsAccess, now).
		First(&token).Error; err != nil {
		return AutomationRunnerToken{}, err
	}
	return token, nil
}

// GetActionsRunnerCredentialByClientID loads an active official runner OAuth
// client and verifies that its MeshX runner is still active.
func (s *Store) GetActionsRunnerCredentialByClientID(ctx context.Context, clientID string) (AutomationActionsRunnerCredential, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationActionsRunnerCredential{}, err
	}
	clientID = strings.TrimSpace(clientID)
	var credential AutomationActionsRunnerCredential
	if err := s.db.WithContext(ctx).
		Where("client_id = ? AND revoked_at IS NULL", clientID).
		Where("EXISTS (SELECT 1 FROM automation_runners WHERE automation_runners.workspace_id = automation_actions_runner_credentials.workspace_id AND automation_runners.runner_id = automation_actions_runner_credentials.runner_id AND automation_runners.status = ? AND automation_runners.revoked_at IS NULL)", AutomationRunnerStatusActive).
		First(&credential).Error; err != nil {
		return AutomationActionsRunnerCredential{}, err
	}
	return credential, nil
}

func (s *Store) GetActiveRunnerSessionByToken(ctx context.Context, sessionTokenHash string, workspaceID string, runnerID string, sessionID string, now time.Time) (AutomationRunnerSession, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunnerSession{}, err
	}
	sessionTokenHash = strings.TrimSpace(sessionTokenHash)
	workspaceID = strings.TrimSpace(workspaceID)
	runnerID = strings.TrimSpace(runnerID)
	sessionID = strings.TrimSpace(sessionID)
	var token AutomationRunnerToken
	if err := s.db.WithContext(ctx).
		Where("token_hash = ? AND token_kind = ? AND workspace_id = ? AND runner_id = ? AND revoked_at IS NULL AND expires_at > ?", sessionTokenHash, AutomationRunnerTokenKindSession, workspaceID, runnerID, now).
		Where("(session_id = ? OR session_id = '')", sessionID).
		First(&token).Error; err != nil {
		return AutomationRunnerSession{}, err
	}
	var session AutomationRunnerSession
	if err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND runner_id = ? AND session_id = ? AND status = ? AND revoked_at IS NULL AND expires_at > ?", workspaceID, runnerID, sessionID, AutomationRunnerSessionStatusActive, now).
		Where("EXISTS (SELECT 1 FROM automation_runners WHERE automation_runners.workspace_id = automation_runner_sessions.workspace_id AND automation_runners.runner_id = automation_runner_sessions.runner_id AND automation_runners.status = ? AND automation_runners.revoked_at IS NULL)", AutomationRunnerStatusActive).
		First(&session).Error; err != nil {
		return AutomationRunnerSession{}, err
	}
	return session, nil
}

func (s *Store) HeartbeatRunnerSession(ctx context.Context, sessionTokenHash string, workspaceID string, runnerID string, sessionID string, now time.Time, expiresAt time.Time) (AutomationRunnerSession, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationRunnerSession{}, err
	}
	sessionTokenHash = strings.TrimSpace(sessionTokenHash)
	workspaceID = strings.TrimSpace(workspaceID)
	runnerID = strings.TrimSpace(runnerID)
	sessionID = strings.TrimSpace(sessionID)
	var session AutomationRunnerSession
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		sessionUpdate := tx.Model(&AutomationRunnerSession{}).
			Where("workspace_id = ? AND runner_id = ? AND session_id = ? AND status = ? AND revoked_at IS NULL AND expires_at > ?", workspaceID, runnerID, sessionID, AutomationRunnerSessionStatusActive, now).
			Updates(map[string]any{"heartbeat_at": now, "expires_at": expiresAt, "updated_at": now})
		if sessionUpdate.Error != nil {
			return fmt.Errorf("heartbeat automation runner session: %w", sessionUpdate.Error)
		}
		if sessionUpdate.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		tokenUpdate := tx.Model(&AutomationRunnerToken{}).
			Where("token_hash = ? AND token_kind = ? AND workspace_id = ? AND runner_id = ? AND revoked_at IS NULL AND expires_at > ?", sessionTokenHash, AutomationRunnerTokenKindSession, workspaceID, runnerID, now).
			Updates(map[string]any{"expires_at": expiresAt, "updated_at": now})
		if tokenUpdate.Error != nil {
			return fmt.Errorf("renew automation runner session token: %w", tokenUpdate.Error)
		}
		if tokenUpdate.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		if err := tx.Model(&AutomationRunner{}).Where("workspace_id = ? AND runner_id = ? AND status = ? AND revoked_at IS NULL", workspaceID, runnerID, AutomationRunnerStatusActive).Updates(map[string]any{
			"last_seen_at": now,
			"updated_at":   now,
		}).Error; err != nil {
			return fmt.Errorf("mark automation runner heartbeat: %w", err)
		}
		if err := tx.Where("workspace_id = ? AND runner_id = ? AND session_id = ?", workspaceID, runnerID, sessionID).First(&session).Error; err != nil {
			return err
		}
		return nil
	})
	if err != nil {
		return AutomationRunnerSession{}, err
	}
	return session, nil
}

// RevokeRunnerSession revokes one active runner session and its session token.
func (s *Store) RevokeRunnerSession(ctx context.Context, sessionTokenHash string, workspaceID string, runnerID string, sessionID string, now time.Time) error {
	if err := s.ensureDB(); err != nil {
		return err
	}
	sessionTokenHash = strings.TrimSpace(sessionTokenHash)
	workspaceID = strings.TrimSpace(workspaceID)
	runnerID = strings.TrimSpace(runnerID)
	sessionID = strings.TrimSpace(sessionID)
	return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		sessionUpdate := tx.Model(&AutomationRunnerSession{}).
			Where("workspace_id = ? AND runner_id = ? AND session_id = ? AND status = ? AND revoked_at IS NULL", workspaceID, runnerID, sessionID, AutomationRunnerSessionStatusActive).
			Updates(map[string]any{"status": AutomationRunnerSessionStatusRevoked, "revoked_at": now, "updated_at": now})
		if sessionUpdate.Error != nil {
			return fmt.Errorf("revoke automation runner session: %w", sessionUpdate.Error)
		}
		if sessionUpdate.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		tokenUpdate := tx.Model(&AutomationRunnerToken{}).
			Where("token_hash = ? AND token_kind = ? AND workspace_id = ? AND runner_id = ? AND revoked_at IS NULL", sessionTokenHash, AutomationRunnerTokenKindSession, workspaceID, runnerID).
			Updates(map[string]any{"revoked_at": now, "updated_at": now})
		if tokenUpdate.Error != nil {
			return fmt.Errorf("revoke automation runner session token: %w", tokenUpdate.Error)
		}
		if tokenUpdate.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		return nil
	})
}

func (s *Store) RenewLeasedJob(ctx context.Context, workspaceID string, jobID int64, leaseOwner string, now time.Time, leaseUntil time.Time) (AutomationJob, error) {
	if err := s.ensureDB(); err != nil {
		return AutomationJob{}, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	leaseOwner = strings.TrimSpace(leaseOwner)
	var job AutomationJob
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		if err := tx.Where("id = ? AND workspace_id = ? AND status = ? AND lease_owner = ? AND lease_until > ?", jobID, workspaceID, AutomationJobStatusLeased, leaseOwner, now).First(&job).Error; err != nil {
			return err
		}
		update := tx.Model(&AutomationJob{}).Where("id = ? AND workspace_id = ? AND status = ? AND lease_owner = ?", job.ID, workspaceID, AutomationJobStatusLeased, leaseOwner).Updates(map[string]any{
			"lease_until": leaseUntil,
			"updated_at":  now,
		})
		if update.Error != nil {
			return fmt.Errorf("renew automation job lease: %w", update.Error)
		}
		if update.RowsAffected == 0 {
			var matched AutomationJob
			if err := tx.Select("id").
				Where("id = ? AND workspace_id = ? AND status = ? AND lease_owner = ? AND lease_until > ?", job.ID, workspaceID, AutomationJobStatusLeased, leaseOwner, now).
				First(&matched).Error; err != nil {
				return err
			}
		}
		if err := tx.Model(&AutomationJobRun{}).Where("workspace_id = ? AND id = ? AND lease_owner = ?", job.WorkspaceID, job.JobRunID, leaseOwner).Updates(map[string]any{
			"lease_until": leaseUntil,
			"updated_at":  now,
		}).Error; err != nil {
			return fmt.Errorf("renew automation job run lease: %w", err)
		}
		job.LeaseUntil = &leaseUntil
		job.UpdatedAt = now
		return nil
	})
	if err != nil {
		return AutomationJob{}, err
	}
	return job, nil
}

func (s *Store) RunnerJobCancelRequested(ctx context.Context, workspaceID string, queueJobID int64, leaseOwner string) (bool, error) {
	if err := s.ensureDB(); err != nil {
		return false, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	leaseOwner = strings.TrimSpace(leaseOwner)
	if queueJobID <= 0 || workspaceID == "" || leaseOwner == "" {
		return false, nil
	}
	var status struct {
		JobStatus    string
		JobRunStatus string
	}
	err := s.db.WithContext(ctx).
		Table("automation_jobs AS jobs").
		Select("jobs.status AS job_status, COALESCE(job_runs.status, '') AS job_run_status").
		Joins("LEFT JOIN automation_job_runs AS job_runs ON job_runs.workspace_id = jobs.workspace_id AND job_runs.id = jobs.job_run_id").
		Where("jobs.id = ? AND jobs.workspace_id = ? AND jobs.lease_owner = ?", queueJobID, workspaceID, leaseOwner).
		Take(&status).Error
	if err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return false, nil
		}
		return false, fmt.Errorf("get automation runner cancel state: %w", err)
	}
	return status.JobStatus == AutomationJobStatusCanceled || status.JobRunStatus == AutomationJobRunStatusCanceled, nil
}