package files

import (
	"context"
	"database/sql"
	"strings"
	"time"
)

// UploadOperationMetrics is a lightweight snapshot of `_System_FileUploadOperation`
// health for observability and future controlplane repair hooks.
type UploadOperationMetrics struct {
	Total      int64 `json:"total"`
	Prepared   int64 `json:"prepared"`
	BlobStored int64 `json:"blob_stored"`
	Completed  int64 `json:"completed"`
	Failed     int64 `json:"failed"`
	Expired    int64 `json:"expired"`
}

// CleanupExpiredUploadOperations removes only expired COMPLETED receipts.
// Nonterminal rows may still own blobs, so their lifecycle belongs to the
// scheduled/timed repair worker rather than this receipt cleanup primitive.
func CleanupExpiredUploadOperations(ctx context.Context, db *sql.DB, now time.Time) (int64, error) {
	if db == nil {
		return 0, nil
	}
	res, err := db.ExecContext(
		ctx,
		"DELETE FROM `_System_FileUploadOperation` WHERE `State` = 'COMPLETED' AND `ExpiresAt` IS NOT NULL AND `ExpiresAt` < ?",
		now,
	)
	if err != nil {
		return 0, err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return affected, nil
}

// QueryUploadOperationMetrics returns a compact state snapshot for sentry and
// other lightweight observability endpoints.
func QueryUploadOperationMetrics(ctx context.Context, db *sql.DB, now time.Time) (UploadOperationMetrics, error) {
	if db == nil {
		return UploadOperationMetrics{}, nil
	}

	metrics := UploadOperationMetrics{}

	var total int64
	if err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM `_System_FileUploadOperation`").Scan(&total); err != nil {
		return UploadOperationMetrics{}, err
	}
	metrics.Total = total

	rows, err := db.QueryContext(ctx, "SELECT `State`, COUNT(*) FROM `_System_FileUploadOperation` GROUP BY `State`")
	if err != nil {
		return metrics, err
	}
	defer rows.Close()
	for rows.Next() {
		var state string
		var count int64
		if err := rows.Scan(&state, &count); err != nil {
			return metrics, err
		}
		switch strings.ToUpper(strings.TrimSpace(state)) {
		case uploadStatePrepared:
			metrics.Prepared = count
		case uploadStateBlobStored:
			metrics.BlobStored = count
		case uploadStateCompleted:
			metrics.Completed = count
		case uploadStateFailed:
			metrics.Failed = count
		}
	}
	if err := rows.Err(); err != nil {
		return metrics, err
	}

	if err := db.QueryRowContext(
		ctx,
		"SELECT COUNT(*) FROM `_System_FileUploadOperation` WHERE `ExpiresAt` IS NOT NULL AND `ExpiresAt` < ?",
		now,
	).Scan(&metrics.Expired); err != nil {
		return metrics, err
	}

	return metrics, nil
}