package content

import (
	"context"
	"fmt"
	"strings"
)

const MaxRetentionReviewBatch = 100

// ListRetentionReviewCandidates returns a bounded oldest-first set that is
// neither head nor explicitly pinned and whose commit event is delivered.
// These rows are not deletion-authorized: a caller must still establish a
// product-reference fence before removing any version.
func (s *Store) ListRetentionReviewCandidates(ctx context.Context, workspaceID, contentID string, limit int) ([]ContentVersion, error) {
	if err := s.ensureDB(); err != nil {
		return nil, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	contentID = strings.TrimSpace(contentID)
	if workspaceID == "" || contentID == "" || limit <= 0 || limit > MaxRetentionReviewBatch {
		return nil, fmt.Errorf("content retention review request is invalid")
	}
	var content VersionedContent
	if err := s.db.WithContext(ctx).Select("id", "head_version_id").
		Where("workspace_id = ? AND id = ?", workspaceID, contentID).First(&content).Error; err != nil {
		return nil, err
	}
	var versions []ContentVersion
	err := s.db.WithContext(ctx).Model(&ContentVersion{}).
		Where("workspace_id = ? AND content_id = ? AND id <> ?", workspaceID, contentID, content.HeadVersionID).
		Where("EXISTS (SELECT 1 FROM content_outbox WHERE content_outbox.workspace_id = content_versions.workspace_id AND content_outbox.content_id = content_versions.content_id AND content_outbox.version_id = content_versions.id AND content_outbox.status = ?)", OutboxStatusDelivered).
		Where("NOT EXISTS (SELECT 1 FROM content_version_pins WHERE content_version_pins.workspace_id = content_versions.workspace_id AND content_version_pins.content_id = content_versions.content_id AND content_version_pins.version_id = content_versions.id)").
		Order("created_at ASC, id ASC").Limit(limit).Find(&versions).Error
	return versions, err
}