package attachment

import (
	"context"
	"fmt"
	"time"

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

const defaultUnclaimedAttachmentDeleteLimit = 100
const attachmentGCDeleteTimeout = 250 * time.Millisecond

// DeleteUnclaimedAttachmentsBefore removes uploaded attachments that were never
// claimed by a message and are older than the provided cutoff.
func (txs TxStore) DeleteUnclaimedAttachmentsBefore(ctx context.Context, cutoff time.Time, limit int) (int64, error) {
	if limit <= 0 {
		limit = defaultUnclaimedAttachmentDeleteLimit
	}

	rows, err := txs.tx.Query(ctx, BuildSelectUnclaimedAttachmentIDsForGCQuery(), cutoff.UTC(), limit)
	if err != nil {
		return 0, fmt.Errorf("select unclaimed attachments: %w", err)
	}
	defer rows.Close()

	type gcTarget struct {
		AttachmentID  string
		WorkspaceID   string
		StorageDriver string
		ObjectKey     string
	}
	attachmentTargets := make([]gcTarget, 0, limit)
	attachmentIDs := make([]string, 0, limit)
	for rows.Next() {
		var target gcTarget
		if err := rows.Scan(&target.AttachmentID, &target.WorkspaceID, &target.StorageDriver, &target.ObjectKey); err != nil {
			return 0, fmt.Errorf("scan unclaimed attachment id: %w", err)
		}
		attachmentTargets = append(attachmentTargets, target)
		attachmentIDs = append(attachmentIDs, target.AttachmentID)
	}
	if err := rows.Err(); err != nil {
		return 0, fmt.Errorf("iterate unclaimed attachments: %w", err)
	}
	if len(attachmentTargets) == 0 {
		return 0, nil
	}

	deleteQuery, deleteArgs, err := BuildDeleteUnclaimedAttachmentsForGCQuery(attachmentIDs)
	if err != nil {
		return 0, fmt.Errorf("delete unclaimed attachments: %w", err)
	}
	deleteCtx, cancel := context.WithTimeout(ctx, attachmentGCDeleteTimeout)
	result, err := txs.tx.Exec(deleteCtx, deleteQuery, deleteArgs...)
	cancel()
	if err != nil {
		if deleteCtx.Err() == context.DeadlineExceeded {
			return 0, nil
		}
		return 0, fmt.Errorf("delete unclaimed attachments: %w", err)
	}
	deletedCount := result.RowsAffected()
	if deletedCount == 0 {
		return 0, nil
	}

	deletedTargets := attachmentTargets
	if deletedCount != int64(len(attachmentTargets)) {
		remainingIDs, err := txs.loadExistingAttachmentIDSet(ctx, attachmentIDs)
		if err != nil {
			return deletedCount, err
		}
		deletedTargets = make([]gcTarget, 0, int(deletedCount))
		for _, target := range attachmentTargets {
			if _, found := remainingIDs[target.AttachmentID]; !found {
				deletedTargets = append(deletedTargets, target)
			}
		}
	}
	for _, target := range deletedTargets {
		txs.deleteMediaObjectBestEffort(ctx, target.StorageDriver, target.ObjectKey)
		// Best-effort clean any generated thumbnail derivative for this
		// attachment (no-op when none was generated). Message-deleted
		// attachments rely on the OSS-prefix lifecycle + a future orphan sweep.
		for _, variant := range SupportedThumbnailVersions {
			txs.deleteMediaObjectBestEffort(ctx, txs.mediaStorageDriver(), derivativeObjectKey(target.WorkspaceID, target.AttachmentID, variant))
		}
	}
	return deletedCount, nil
}

func (txs TxStore) loadExistingAttachmentIDSet(ctx context.Context, attachmentIDs []string) (map[string]struct{}, error) {
	query, args, err := BuildSelectExistingAttachmentsForGCQuery(attachmentIDs)
	if err != nil {
		return nil, fmt.Errorf("load existing attachments after GC: %w", err)
	}
	rows, err := txs.tx.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load existing attachments after GC: %w", err)
	}
	defer rows.Close()

	ids := make(map[string]struct{}, len(attachmentIDs))
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("scan existing attachment after GC: %w", err)
		}
		ids[id] = struct{}{}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate existing attachments after GC: %w", err)
	}
	return ids, nil
}

func BuildSelectUnclaimedAttachmentIDsForGCQuery() string {
	return `
SELECT id, workspace_id, COALESCE(storage_driver, ''), COALESCE(object_key, '')
FROM attachments
WHERE message_id IS NULL
  AND created_at < $1
ORDER BY created_at ASC, id ASC
LIMIT $2
`
}

func BuildDeleteUnclaimedAttachmentsForGCQuery(attachmentIDs []string) (string, []any, error) {
	cte, args, _, err := coredb.BuildStringValuesCTE("attachment_ids", "attachment_id", normalizeNonEmptyStrings(attachmentIDs), 1)
	if err != nil {
		return "", nil, err
	}
	return fmt.Sprintf(`
WITH %s
DELETE FROM attachments
WHERE EXISTS (
    SELECT 1
    FROM attachment_ids
    WHERE attachment_ids.attachment_id = attachments.id
  )
  AND message_id IS NULL
`, cte), args, nil
}

func BuildSelectExistingAttachmentsForGCQuery(attachmentIDs []string) (string, []any, error) {
	cte, args, _, err := coredb.BuildStringValuesCTE("attachment_ids", "attachment_id", normalizeNonEmptyStrings(attachmentIDs), 1)
	if err != nil {
		return "", nil, err
	}
	return fmt.Sprintf(`
WITH %s
SELECT a.id
FROM attachments AS a
JOIN attachment_ids ON attachment_ids.attachment_id = a.id
`, cte), args, nil
}