package registry

import (
	"context"
	"fmt"
	"strings"

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

// LatestInstallableMetadata is the latest app version a user may install for a requested app.

// BuildLoadLatestInstallableMetadataQuery returns a portable query for resolving installable app metadata.
func BuildLoadLatestInstallableMetadataQuery(appIDs []string) (string, []any, error) {
	requestedApps := UniqueTrimmedStrings(appIDs)
	if len(requestedApps) == 0 {
		return "", nil, nil
	}

	args := make([]any, 0, len(requestedApps))

	requestedAppRows := make([]string, 0, len(requestedApps))
	for idx, appID := range requestedApps {
		args = append(args, appID)
		requestedAppRows = append(requestedAppRows, fmt.Sprintf("SELECT $%d AS app_id", idx+1))
	}

	query := fmt.Sprintf(`
WITH requested_apps AS (
%s
)
SELECT
    av.service_definition_id,
    av.version,
    av.manifest
FROM requested_apps requested
JOIN service_definitions a ON a.id = requested.app_id
JOIN extension_definition_versions av
  ON av.service_definition_id = requested.app_id
 AND av.version = a.latest_version
ORDER BY av.service_definition_id ASC, av.created_at DESC, av.id DESC
`, strings.Join(requestedAppRows, "\nUNION ALL\n"))

	return query, args, nil
}

// LoadLatestInstallableMetadataByAppID resolves installable app metadata keyed by app ID.
func LoadLatestInstallableMetadataByAppID(ctx context.Context, queryer coredb.Queryer, appIDs []string) (map[string]model.LatestInstallableAppMetadata, error) {
	query, args, err := BuildLoadLatestInstallableMetadataQuery(appIDs)
	if err != nil {
		return nil, err
	}
	if strings.TrimSpace(query) == "" {
		return map[string]model.LatestInstallableAppMetadata{}, nil
	}

	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load latest installable app metadata: %w", err)
	}
	defer rows.Close()

	metadataByAppID := make(map[string]model.LatestInstallableAppMetadata, len(appIDs))
	for rows.Next() {
		var (
			appID        string
			version      string
			manifestJSON []byte
		)
		if err := rows.Scan(&appID, &version, &manifestJSON); err != nil {
			return nil, fmt.Errorf("scan latest installable app metadata: %w", err)
		}
		appID = strings.TrimSpace(appID)
		if appID == "" {
			continue
		}
		if _, exists := metadataByAppID[appID]; exists {
			continue
		}
		metadataByAppID[appID] = model.LatestInstallableAppMetadata{
			Version:      strings.TrimSpace(version),
			ManifestJSON: append([]byte(nil), manifestJSON...),
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate latest installable app metadata: %w", err)
	}

	return metadataByAppID, nil
}