package kb

import (
	"context"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"time"

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

const (
	GenerationKindWiki          = "wiki"
	GenerationDecisionApproved  = "approved"
	GenerationDecisionRejected  = "rejected"
	GenerationDecisionWithdrawn = "withdrawn"

	GenerationRequestStatusPendingApproval = "pending_approval"
	GenerationRequestStatusQueued          = "queued"
	GenerationRequestStatusRunning         = "running"
	GenerationRequestStatusReady           = "ready"
	GenerationRequestStatusFailed          = "failed"
	GenerationRequestStatusRejected        = "rejected"
	GenerationRequestStatusWithdrawn       = "withdrawn"
	GenerationRequestStatusStale           = "stale"

	GenerationNotificationStatePending   = "pending"
	GenerationNotificationStateProjected = "projected"
)

var (
	ErrGenerationRequestAlreadyActive  = errors.New("generation request already active")
	ErrGenerationRequestReplayConflict = errors.New("generation request replay conflict")
	ErrGenerationSourceRevisionChanged = errors.New("generation source revision changed")
	ErrGenerationArtifactAlreadyReady  = errors.New("generation artifact already ready")
)

type CreateWikiGenerationParams struct {
	Request KnowledgeBaseGenerationRequest
	Job     *Job
	Now     time.Time
}

// CreateWikiGeneration atomically freezes the current Source revision and
// creates either a pending Approval-backed request or a direct queued request.
// The source-row lock serializes both paths with Source revision advancement.
func (s *Store) CreateWikiGeneration(ctx context.Context, params CreateWikiGenerationParams) (KnowledgeBaseGenerationRequest, bool, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, false, err
	}
	request := params.Request
	if err := validateInitialWikiGeneration(request, params.Job, params.Now); err != nil {
		return KnowledgeBaseGenerationRequest{}, false, err
	}
	var stored KnowledgeBaseGenerationRequest
	created := false
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		store := s.WithDB(tx)
		var source Source
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("workspace_id = ? AND knowledge_base_id = ? AND id = ? AND deleted_at IS NULL", request.WorkspaceID, request.KnowledgeBaseID, request.SourceID).
			First(&source).Error; err != nil {
			return err
		}

		var replay KnowledgeBaseGenerationRequest
		err := tx.Where(
			"workspace_id = ? AND requester_id = ? AND idempotency_key = ?",
			request.WorkspaceID, request.RequesterID, request.IdempotencyKey,
		).First(&replay).Error
		if err == nil {
			if !sameGenerationRequest(replay, request) {
				return ErrGenerationRequestReplayConflict
			}
			stored = replay
			return nil
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}

		var current SourceRevision
		if err := tx.Where(
			"workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?",
			request.WorkspaceID, request.KnowledgeBaseID, request.SourceID, true,
		).Order("updated_at DESC, id DESC").First(&current).Error; err != nil {
			return err
		}
		if current.ID != request.SourceRevisionID || strings.TrimSpace(current.SourceCommitSHA) != strings.TrimSpace(request.SourceCommitSHA) {
			return ErrGenerationSourceRevisionChanged
		}
		if strings.TrimSpace(current.PublishedWikiRevision) != "" {
			return ErrGenerationArtifactAlreadyReady
		}

		var active KnowledgeBaseGenerationRequest
		err = tx.Where(
			"workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND kind = ? AND status IN ?",
			request.WorkspaceID, request.KnowledgeBaseID, request.SourceID, GenerationKindWiki,
			[]string{GenerationRequestStatusPendingApproval, GenerationRequestStatusQueued, GenerationRequestStatusRunning},
		).Order("created_at DESC, id DESC").First(&active).Error
		if err == nil {
			return ErrGenerationRequestAlreadyActive
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}

		if err := tx.Create(&request).Error; err != nil {
			return fmt.Errorf("create wiki generation request: %w", err)
		}
		if params.Job != nil {
			if _, err := store.CreateJob(ctx, *params.Job); err != nil {
				return err
			}
		}
		artifact := KnowledgeBaseSetupSourceArtifact{
			ID: request.ID + "_disposition", WorkspaceID: request.WorkspaceID,
			KnowledgeBaseID: request.KnowledgeBaseID, SourceID: request.SourceID,
			Kind: GenerationKindWiki, State: "not_requested", UpdatedBy: request.RequesterID,
			CreatedAt: params.Now, UpdatedAt: params.Now,
		}
		if _, err := store.UpsertKnowledgeBaseSetupSourceArtifact(ctx, artifact); err != nil {
			return err
		}
		stored, created = request, true
		return nil
	})
	if err != nil {
		return KnowledgeBaseGenerationRequest{}, false, err
	}
	return stored, created, nil
}

func validateInitialWikiGeneration(request KnowledgeBaseGenerationRequest, job *Job, now time.Time) error {
	if strings.TrimSpace(request.ID) == "" || strings.TrimSpace(request.WorkspaceID) == "" ||
		strings.TrimSpace(request.KnowledgeBaseID) == "" || strings.TrimSpace(request.SourceID) == "" ||
		strings.TrimSpace(request.SourceRevisionID) == "" || strings.TrimSpace(request.RequesterID) == "" ||
		strings.TrimSpace(request.IdempotencyKey) == "" || request.Kind != GenerationKindWiki || now.IsZero() {
		return fmt.Errorf("create wiki generation request: scoped request identities are required")
	}
	if len(request.PayloadDigest) != 64 {
		return fmt.Errorf("create wiki generation request: payload digest is invalid")
	}
	if _, err := hex.DecodeString(request.PayloadDigest); err != nil {
		return fmt.Errorf("create wiki generation request: payload digest is invalid")
	}
	if request.Decision != "" || request.DecisionID != "" || request.DecisionInputReason != "" ||
		request.DecisionReason != "" || request.DecidedBy != "" || request.DecidedAt != nil {
		return fmt.Errorf("create wiki generation request: initial decision must be empty")
	}
	switch request.Status {
	case GenerationRequestStatusPendingApproval:
		if strings.TrimSpace(request.ApprovalID) == "" || request.ApprovalVersion <= 0 || job != nil || strings.TrimSpace(request.JobID) != "" {
			return fmt.Errorf("create wiki generation request: pending request requires versioned Approval without job")
		}
	case GenerationRequestStatusQueued:
		if job == nil || strings.TrimSpace(job.ID) == "" || job.ID != request.JobID || strings.TrimSpace(request.ApprovalID) != "" || request.ApprovalVersion != 0 {
			return fmt.Errorf("create wiki generation request: direct request requires matching job without Approval")
		}
		if job.WorkspaceID != request.WorkspaceID || job.KnowledgeBaseID != request.KnowledgeBaseID || job.SourceID != request.SourceID {
			return fmt.Errorf("create wiki generation request: job scope does not match request")
		}
	default:
		return fmt.Errorf("create wiki generation request: initial status is invalid")
	}
	return nil
}

func sameGenerationRequest(left, right KnowledgeBaseGenerationRequest) bool {
	return left.WorkspaceID == right.WorkspaceID &&
		left.KnowledgeBaseID == right.KnowledgeBaseID &&
		left.SourceID == right.SourceID &&
		left.Kind == right.Kind &&
		left.SourceRevisionID == right.SourceRevisionID &&
		left.SourceCommitSHA == right.SourceCommitSHA &&
		left.Scope == right.Scope && left.Language == right.Language &&
		left.Reason == right.Reason && left.RequesterID == right.RequesterID &&
		left.ApprovalID == right.ApprovalID && left.ApprovalVersion == right.ApprovalVersion &&
		left.PayloadDigest == right.PayloadDigest
}

type ResolveWikiGenerationApprovalParams struct {
	WorkspaceID      string
	ApprovalID       string
	ApprovalVersion  int64
	ActorID          string
	ClientDecisionID string
	Decision         string
	Reason           string
	Job              *Job
	AuditEvent       AuditEvent
	Now              time.Time
}

// ResolveWikiGenerationApproval applies one generic Approval outcome to the
// MeshX request. Approved work is enqueued in the same transaction. A stale or
// deleted Source closes the request without creating a job.
func (s *Store) ResolveWikiGenerationApproval(ctx context.Context, params ResolveWikiGenerationApprovalParams) (KnowledgeBaseGenerationRequest, bool, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, false, err
	}
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.ApprovalID = strings.TrimSpace(params.ApprovalID)
	params.ActorID = strings.TrimSpace(params.ActorID)
	params.ClientDecisionID = strings.TrimSpace(params.ClientDecisionID)
	params.Decision = strings.TrimSpace(params.Decision)
	params.Reason = strings.TrimSpace(params.Reason)
	if params.WorkspaceID == "" || params.ApprovalID == "" || params.ApprovalVersion <= 0 || params.ActorID == "" || params.ClientDecisionID == "" {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("resolve wiki generation Approval: identities are required")
	}
	if params.Decision != GenerationDecisionApproved && params.Decision != GenerationDecisionRejected && params.Decision != GenerationDecisionWithdrawn {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("resolve wiki generation Approval: decision is invalid")
	}
	if params.Decision == GenerationDecisionRejected && params.Reason == "" {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("resolve wiki generation Approval: rejection reason is required")
	}
	if params.Now.IsZero() {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("resolve wiki generation Approval: decision time is required")
	}
	if params.Decision == GenerationDecisionApproved {
		if params.Job == nil || strings.TrimSpace(params.Job.ID) == "" || params.Job.Status != "queued" || strings.TrimSpace(params.Job.JobType) == "" {
			return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("resolve wiki generation Approval: approved decision requires queued job")
		}
	} else if params.Job != nil {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("resolve wiki generation Approval: terminal decision must not include job")
	}

	var resolved KnowledgeBaseGenerationRequest
	applied := false
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var request KnowledgeBaseGenerationRequest
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("workspace_id = ? AND approval_id = ?", params.WorkspaceID, params.ApprovalID).
			First(&request).Error; err != nil {
			return err
		}
		if request.DecisionID != "" {
			if request.DecisionID != params.ClientDecisionID || request.Decision != params.Decision ||
				request.DecidedBy != params.ActorID || request.DecisionInputReason != params.Reason ||
				request.ApprovalVersion != params.ApprovalVersion {
				return ErrGenerationRequestReplayConflict
			}
			resolved = request
			return nil
		}
		if request.Status != GenerationRequestStatusPendingApproval {
			return ErrGenerationRequestReplayConflict
		}
		if params.ApprovalVersion != request.ApprovalVersion+1 {
			return ErrGenerationRequestReplayConflict
		}
		if params.Job != nil && (params.Job.WorkspaceID != request.WorkspaceID ||
			params.Job.KnowledgeBaseID != request.KnowledgeBaseID || params.Job.SourceID != request.SourceID) {
			return fmt.Errorf("resolve wiki generation Approval: job scope does not match request")
		}

		status, decision, decisionReason := GenerationRequestStatusRejected, params.Decision, params.Reason
		jobID := ""
		if params.Decision == GenerationDecisionWithdrawn {
			status = GenerationRequestStatusWithdrawn
		} else if params.Decision == GenerationDecisionApproved {
			stale, err := wikiGenerationSourceChanged(tx, request)
			if err != nil {
				return err
			}
			if stale {
				status, decisionReason = GenerationRequestStatusStale, "source_revision_changed"
			} else {
				status, jobID = GenerationRequestStatusQueued, params.Job.ID
				if _, err := s.WithDB(tx).CreateJob(ctx, *params.Job); err != nil {
					return err
				}
			}
		}

		updates := map[string]any{
			"status": status, "job_id": jobID, "approval_version": params.ApprovalVersion,
			"decision":    decision,
			"decision_id": params.ClientDecisionID, "decision_input_reason": params.Reason,
			"decision_reason": decisionReason,
			"decided_by":      params.ActorID, "decided_at": params.Now,
			"updated_at": params.Now, "version": gorm.Expr("version + 1"),
		}
		result := tx.Model(&KnowledgeBaseGenerationRequest{}).
			Where("workspace_id = ? AND id = ? AND status = ? AND decision_id = ''", request.WorkspaceID, request.ID, GenerationRequestStatusPendingApproval).
			Updates(updates)
		if result.Error != nil {
			return fmt.Errorf("resolve wiki generation Approval: %w", result.Error)
		}
		if result.RowsAffected != 1 {
			return ErrGenerationRequestReplayConflict
		}
		if strings.TrimSpace(params.AuditEvent.ID) != "" {
			if _, err := s.WithDB(tx).CreateAuditEvent(ctx, params.AuditEvent); err != nil {
				return err
			}
		}
		if err := tx.Where("workspace_id = ? AND id = ?", request.WorkspaceID, request.ID).First(&resolved).Error; err != nil {
			return err
		}
		applied = true
		return nil
	})
	return resolved, applied, err
}

func wikiGenerationSourceChanged(tx *gorm.DB, request KnowledgeBaseGenerationRequest) (bool, error) {
	var source Source
	if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
		Where("workspace_id = ? AND knowledge_base_id = ? AND id = ? AND deleted_at IS NULL", request.WorkspaceID, request.KnowledgeBaseID, request.SourceID).
		First(&source).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return true, nil
		}
		return false, err
	}
	var revision SourceRevision
	if err := tx.Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?", request.WorkspaceID, request.KnowledgeBaseID, request.SourceID, true).
		Order("updated_at DESC, id DESC").First(&revision).Error; err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return true, nil
		}
		return false, err
	}
	return revision.ID != request.SourceRevisionID || revision.SourceCommitSHA != request.SourceCommitSHA || strings.TrimSpace(revision.PublishedWikiRevision) != "", nil
}

// ListLatestGenerationRequestsBySource returns at most one request per Source
// for one explicitly bounded Source set. Historical attempts stay off setup
// first paint.
func (s *Store) ListLatestGenerationRequestsBySource(ctx context.Context, workspaceID, kbID, kind string, sourceIDs []string) ([]KnowledgeBaseGenerationRequest, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	sourceIDs = uniqueTrimmedStrings(sourceIDs)
	if len(sourceIDs) == 0 {
		return []KnowledgeBaseGenerationRequest{}, nil
	}
	var rows []KnowledgeBaseGenerationRequest
	err := s.db.WithContext(ctx).
		Table("knowledge_base_generation_requests AS requests").
		Where("requests.workspace_id = ? AND requests.knowledge_base_id = ? AND requests.kind = ? AND requests.source_id IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(kbID), strings.TrimSpace(kind), sourceIDs).
		Where(`requests.id = (
			SELECT latest.id
			FROM knowledge_base_generation_requests AS latest
			WHERE latest.workspace_id = requests.workspace_id
			  AND latest.knowledge_base_id = requests.knowledge_base_id
			  AND latest.source_id = requests.source_id
			  AND latest.kind = requests.kind
			ORDER BY latest.created_at DESC, latest.id DESC
			LIMIT 1
		)`).
		Order("requests.source_id ASC").
		Scan(&rows).Error
	if err != nil {
		return nil, fmt.Errorf("list latest generation requests by source: %w", err)
	}
	return rows, nil
}

func (s *Store) GetGenerationRequest(ctx context.Context, workspaceID, requestID string) (KnowledgeBaseGenerationRequest, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, err
	}
	var request KnowledgeBaseGenerationRequest
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(requestID)).
		First(&request).Error
	return request, err
}

func (s *Store) GetGenerationRequestByApproval(ctx context.Context, workspaceID, approvalID string) (KnowledgeBaseGenerationRequest, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, err
	}
	var request KnowledgeBaseGenerationRequest
	err := s.db.WithContext(ctx).
		Where("workspace_id = ? AND approval_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(approvalID)).
		First(&request).Error
	return request, err
}

// ListPendingWikiGenerationTerminalNotifications returns a bounded durable
// projection queue. Workspace scope is applied before LIMIT so one process
// never consumes another workspace group's work.
func (s *Store) ListPendingWikiGenerationTerminalNotifications(ctx context.Context, limit int, workspaceScope ...[]string) ([]KnowledgeBaseGenerationRequest, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	if limit <= 0 {
		limit = 1
	}
	query := s.db.WithContext(ctx).
		Where("kind = ? AND notification_state = ?", GenerationKindWiki, GenerationNotificationStatePending)
	query = applyWorkspaceScope(query, "workspace_id", workspaceScope)
	var requests []KnowledgeBaseGenerationRequest
	if err := query.Order("updated_at ASC").Order("id ASC").Limit(limit).Find(&requests).Error; err != nil {
		return nil, fmt.Errorf("list pending Wiki generation terminal notifications: %w", err)
	}
	return requests, nil
}

// MarkWikiGenerationTerminalNotified acknowledges one exact domain version.
// Notification metadata does not advance the domain version, otherwise the
// projection fence could never converge.
func (s *Store) MarkWikiGenerationTerminalNotified(ctx context.Context, workspaceID, requestID string, expectedVersion int64, notifiedAt time.Time) (bool, error) {
	if err := s.ensureDB(); err != nil {
		return false, err
	}
	workspaceID, requestID = strings.TrimSpace(workspaceID), strings.TrimSpace(requestID)
	if workspaceID == "" || requestID == "" || expectedVersion <= 0 || notifiedAt.IsZero() {
		return false, fmt.Errorf("mark Wiki generation terminal notified: scoped version and time are required")
	}
	result := s.db.WithContext(ctx).Model(&KnowledgeBaseGenerationRequest{}).
		Where("workspace_id = ? AND id = ? AND kind = ? AND status IN ? AND version = ? AND notification_version < ?",
			workspaceID, requestID, GenerationKindWiki, []string{
				GenerationRequestStatusReady,
				GenerationRequestStatusFailed,
				GenerationRequestStatusStale,
			}, expectedVersion, expectedVersion).
		Where("notification_state = ?", GenerationNotificationStatePending).
		Updates(map[string]any{
			"notification_state":   GenerationNotificationStateProjected,
			"notification_version": expectedVersion,
			"notified_at":          notifiedAt,
		})
	if result.Error != nil {
		return false, fmt.Errorf("mark Wiki generation terminal notified: %w", result.Error)
	}
	return result.RowsAffected == 1, nil
}

// FindGenerationRequestByJob resolves the durable request from the claimed job
// identity instead of trusting the mutable job progress payload.
func (s *Store) FindGenerationRequestByJob(ctx context.Context, workspaceID, jobID string) (KnowledgeBaseGenerationRequest, bool, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, false, err
	}
	var request KnowledgeBaseGenerationRequest
	result := s.db.WithContext(ctx).
		Where("workspace_id = ? AND job_id = ?", strings.TrimSpace(workspaceID), strings.TrimSpace(jobID)).
		Limit(1).
		Find(&request)
	if result.Error != nil {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("find generation request by job: %w", result.Error)
	}
	return request, result.RowsAffected == 1, nil
}

// MarkWikiGenerationRunning binds one claimed job attempt to its durable
// request. Reclaimed jobs may replay this transition while the request is
// already running.
func (s *Store) MarkWikiGenerationRunning(ctx context.Context, workspaceID, requestID, jobID string, startedAt time.Time) (KnowledgeBaseGenerationRequest, bool, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, false, err
	}
	result := s.db.WithContext(ctx).Model(&KnowledgeBaseGenerationRequest{}).
		Where("workspace_id = ? AND id = ? AND job_id = ? AND status IN ?", strings.TrimSpace(workspaceID), strings.TrimSpace(requestID), strings.TrimSpace(jobID), []string{GenerationRequestStatusQueued, GenerationRequestStatusRunning}).
		Updates(map[string]any{
			"status":     GenerationRequestStatusRunning,
			"started_at": startedAt,
			"updated_at": startedAt,
			"version":    gorm.Expr("version + 1"),
		})
	if result.Error != nil {
		return KnowledgeBaseGenerationRequest{}, false, fmt.Errorf("mark wiki generation running: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return KnowledgeBaseGenerationRequest{}, false, nil
	}
	request, err := s.GetGenerationRequest(ctx, workspaceID, requestID)
	return request, true, err
}

type FinishWikiGenerationParams struct {
	WorkspaceID         string
	RequestID           string
	JobID               string
	LeaseOwner          string
	AttemptCount        int
	InsightsCommitSHA   string
	PublishedWikiCommit string
	Failure             error
	StaleReason         string
	AuditEvent          AuditEvent
	Now                 time.Time
}

// FinishWikiGeneration atomically fences publication against the current
// Source revision, updates the request and leased job, and records the success
// audit. A changed Source revision makes the request stale and never advances
// the reader-visible Wiki pointer.
func (s *Store) FinishWikiGeneration(ctx context.Context, params FinishWikiGenerationParams) (KnowledgeBaseGenerationRequest, error) {
	if err := s.ensureDB(); err != nil {
		return KnowledgeBaseGenerationRequest{}, err
	}
	var finished KnowledgeBaseGenerationRequest
	err := s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var request KnowledgeBaseGenerationRequest
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("workspace_id = ? AND id = ? AND job_id = ?", strings.TrimSpace(params.WorkspaceID), strings.TrimSpace(params.RequestID), strings.TrimSpace(params.JobID)).
			First(&request).Error; err != nil {
			return err
		}
		if request.Status != GenerationRequestStatusRunning {
			finished = request
			return nil
		}
		status := GenerationRequestStatusFailed
		jobStatus := "failed"
		artifactRevision := ""
		decisionReason := "generation_failed"
		if strings.TrimSpace(params.StaleReason) != "" {
			status, jobStatus, decisionReason = GenerationRequestStatusStale, "cancelled", strings.TrimSpace(params.StaleReason)
		} else if params.Failure == nil {
			var source Source
			if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
				Where("workspace_id = ? AND knowledge_base_id = ? AND id = ?", request.WorkspaceID, request.KnowledgeBaseID, request.SourceID).
				First(&source).Error; err != nil {
				return err
			}
			var revision SourceRevision
			err := tx.Where("workspace_id = ? AND knowledge_base_id = ? AND source_id = ? AND is_current = ?", request.WorkspaceID, request.KnowledgeBaseID, request.SourceID, true).
				Order("updated_at DESC, id DESC").First(&revision).Error
			if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
				return err
			}
			if errors.Is(err, gorm.ErrRecordNotFound) || revision.ID != request.SourceRevisionID || revision.SourceCommitSHA != request.SourceCommitSHA {
				status, jobStatus, decisionReason = GenerationRequestStatusStale, "cancelled", "source_revision_changed"
			} else {
				result := tx.Model(&SourceRevision{}).
					Where("workspace_id = ? AND id = ? AND is_current = ? AND source_commit_sha = ?", request.WorkspaceID, request.SourceRevisionID, true, request.SourceCommitSHA).
					Updates(map[string]any{
						"insights_commit_sha":     strings.TrimSpace(params.InsightsCommitSHA),
						"published_wiki_revision": strings.TrimSpace(params.PublishedWikiCommit),
						"updated_at":              params.Now,
					})
				if result.Error != nil {
					return fmt.Errorf("publish generated wiki revision: %w", result.Error)
				}
				if result.RowsAffected != 1 {
					status, jobStatus, decisionReason = GenerationRequestStatusStale, "cancelled", "source_revision_changed"
				} else {
					status, jobStatus, artifactRevision, decisionReason = GenerationRequestStatusReady, "succeeded", strings.TrimSpace(params.PublishedWikiCommit), ""
					if _, err := s.WithDB(tx).CreateAuditEvent(ctx, params.AuditEvent); err != nil {
						return err
					}
				}
			}
		}
		result := tx.Model(&KnowledgeBaseGenerationRequest{}).
			Where("workspace_id = ? AND id = ? AND status = ?", request.WorkspaceID, request.ID, GenerationRequestStatusRunning).
			Updates(map[string]any{
				"status": status, "artifact_revision": artifactRevision,
				"decision_reason": decisionReason, "finished_at": params.Now,
				"notification_state": GenerationNotificationStatePending,
				"updated_at":         params.Now, "version": gorm.Expr("version + 1"),
			})
		if result.Error != nil || result.RowsAffected != 1 {
			if result.Error != nil {
				return fmt.Errorf("finish wiki generation request: %w", result.Error)
			}
			return fmt.Errorf("finish wiki generation request: request fence was lost")
		}
		jobResult := tx.Model(&Job{}).
			Where("workspace_id = ? AND id = ? AND status = ? AND lease_owner = ? AND attempt_count = ?", request.WorkspaceID, request.JobID, "running", strings.TrimSpace(params.LeaseOwner), params.AttemptCount).
			Updates(map[string]any{
				"status": jobStatus, "error": generationFailureSummary(params.Failure),
				"lease_owner": "", "lease_until": nil, "finished_at": params.Now, "updated_at": params.Now,
			})
		if jobResult.Error != nil || jobResult.RowsAffected != 1 {
			if jobResult.Error != nil {
				return fmt.Errorf("finish wiki generation job: %w", jobResult.Error)
			}
			return fmt.Errorf("finish wiki generation job: lease fence was lost")
		}
		return tx.Where("workspace_id = ? AND id = ?", request.WorkspaceID, request.ID).First(&finished).Error
	})
	return finished, err
}

func generationFailureSummary(err error) string {
	if err == nil {
		return ""
	}
	// Worker diagnostics remain server-side and intentionally exclude raw
	// subprocess output, local paths, credentials, or provider responses.
	return "wiki generation failed"
}