package organization

import (
	"context"
	"fmt"
	"strings"
	"time"

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

func replaceOrganizationProviderRouteGroupRefs(ctx context.Context, queryer coredb.QueryExecer, organizationID string, routeID string, groupID string, models []string, providerRefs []string, now time.Time) error {
	if err := deleteOrganizationProviderRouteGroupRefs(ctx, queryer, groupID); err != nil {
		return err
	}

	normalizedProviderRefs := normalizeRouteGroupStringRefs(providerRefs)
	if len(normalizedProviderRefs) > 0 {
		providerRefsCTE, providerRefArgs, _, err := coredb.BuildIndexedStringValuesCTE("provider_refs", "provider_id", "ordinality", normalizedProviderRefs, 5)
		if err != nil {
			return fmt.Errorf("build organization provider route group provider refs: %w", err)
		}
		args := append([]any{organizationID, routeID, groupID, now}, providerRefArgs...)
		if _, err := queryer.Exec(ctx, fmt.Sprintf(`
INSERT INTO organization_provider_route_group_provider_refs (
    organization_id,
    route_id,
    route_group_id,
    provider_id,
    ref_order,
    created_at
)
WITH %s
SELECT
    $1,
    $2,
    $3,
    provider_refs.provider_id,
    provider_refs.ordinality - 1,
    $4
FROM provider_refs
ORDER BY provider_refs.ordinality ASC
`, providerRefsCTE), args...); err != nil {
			return fmt.Errorf("insert organization provider route group provider ref: %w", err)
		}
	}

	normalizedModels := normalizeRouteGroupStringRefs(models)
	if len(normalizedModels) > 0 {
		modelsCTE, modelArgs, _, err := coredb.BuildIndexedStringValuesCTE("models", "model_id", "ordinality", normalizedModels, 5)
		if err != nil {
			return fmt.Errorf("build organization provider route group model refs: %w", err)
		}
		args := append([]any{organizationID, routeID, groupID, now}, modelArgs...)
		if _, err := queryer.Exec(ctx, fmt.Sprintf(`
INSERT INTO organization_provider_route_group_model_refs (
    organization_id,
    route_id,
    route_group_id,
    model_id,
    ref_order,
    created_at
)
WITH %s
SELECT
    $1,
    $2,
    $3,
    models.model_id,
    models.ordinality - 1,
    $4
FROM models
ORDER BY models.ordinality ASC
`, modelsCTE), args...); err != nil {
			return fmt.Errorf("insert organization provider route group model ref: %w", err)
		}
	}

	return nil
}

func deleteOrganizationProviderRouteGroupRefs(ctx context.Context, queryer coredb.QueryExecer, groupID string) error {
	if _, err := queryer.Exec(ctx, `
DELETE FROM organization_provider_route_group_provider_refs
WHERE route_group_id = $1
`, groupID); err != nil {
		return fmt.Errorf("delete organization provider route group provider refs: %w", err)
	}
	if _, err := queryer.Exec(ctx, `
DELETE FROM organization_provider_route_group_model_refs
WHERE route_group_id = $1
`, groupID); err != nil {
		return fmt.Errorf("delete organization provider route group model refs: %w", err)
	}
	return nil
}

func normalizeRouteGroupStringRefs(items []string) []string {
	normalized := make([]string, 0, len(items))
	seen := make(map[string]struct{}, len(items))
	for _, item := range items {
		trimmed := strings.TrimSpace(item)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	return normalized
}