package organization

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

func (txs TxStore) ListVisibleOrganizationProvidersForWorkspace(ctx context.Context, workspaceID string) ([]OrganizationProviderView, error) {
	rows, err := txs.db.Query(ctx, `
SELECT
    op.id,
    op.organization_id,
    op.name,
    op.description,
    op.driver_kind,
    op.base_url,
    op.endpoints_json,
    op.capabilities_json,
    CAST('[]' AS JSON) AS models_json,
    op.status,
    op.created_at,
    op.updated_at
FROM organization_providers op
JOIN (
    SELECT
        oprgp.organization_id,
        oprgp.provider_id
    FROM organization_workspace_route_grants owrg
    JOIN workspaces w
      ON w.id = owrg.workspace_id
     AND w.organization_id = owrg.organization_id
    JOIN organization_provider_route_group_provider_refs oprgp
      ON oprgp.organization_id = owrg.organization_id
     AND oprgp.route_id = owrg.organization_route_id
    JOIN organization_provider_route_groups oprg
      ON oprg.organization_id = oprgp.organization_id
     AND oprg.route_id = oprgp.route_id
     AND oprg.id = oprgp.route_group_id
    JOIN organization_provider_routes opr
      ON opr.organization_id = oprgp.organization_id
     AND opr.id = oprgp.route_id
    WHERE owrg.workspace_id = $1
      AND owrg.status = $2
      AND opr.status = $2
      AND oprg.status = $2
    GROUP BY oprgp.organization_id, oprgp.provider_id
) visible_provider
  ON visible_provider.organization_id = op.organization_id
 AND visible_provider.provider_id = op.id
WHERE op.status = $2
ORDER BY op.name ASC, op.id ASC
`, workspaceID, organizationProviderStatusActive)
	if err != nil {
		return nil, fmt.Errorf("list visible organization providers: %w", err)
	}
	defer rows.Close()

	providers := make([]OrganizationProviderView, 0)
	providerIDs := make([]string, 0)
	for rows.Next() {
		provider, err := scanOrganizationProviderView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan visible organization provider: %w", err)
		}
		providerIDs = append(providerIDs, strings.TrimSpace(provider.ID))
		providers = append(providers, provider)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate visible organization providers: %w", err)
	}
	modelsByProviderID, err := txs.listWorkspaceRouteModelsByProvider(ctx, workspaceID, providerIDs)
	if err != nil {
		return nil, err
	}
	for i := range providers {
		providers[i].Models = append([]OrganizationProviderModel(nil), modelsByProviderID[strings.TrimSpace(providers[i].ID)]...)
		if providers[i].Models == nil {
			providers[i].Models = []OrganizationProviderModel{}
		}
	}
	return providers, nil
}

func (txs TxStore) listOrganizationRouteModelsByProvider(ctx context.Context, organizationID string, allowedProviderIDs []string) (map[string][]OrganizationProviderModel, error) {
	allowed := normalizeOrganizationProviderIDSet(allowedProviderIDs)
	modelsByProviderID := make(map[string][]OrganizationProviderModel)
	for providerID := range allowed {
		modelsByProviderID[providerID] = []OrganizationProviderModel{}
	}
	if len(allowed) == 0 {
		return modelsByProviderID, nil
	}

	args := []any{organizationID, organizationProviderStatusActive}
	providerFilter, args := organizationProviderIDFilter("oprgp.provider_id", allowed, args)
	rows, err := txs.db.Query(ctx, `
SELECT
    oprgp.provider_id,
    oprgm.model_id,
    opr.id,
    opr.models_json
FROM organization_provider_route_group_provider_refs oprgp
JOIN organization_provider_route_groups oprg
  ON oprg.organization_id = oprgp.organization_id
 AND oprg.route_id = oprgp.route_id
 AND oprg.id = oprgp.route_group_id
JOIN organization_provider_routes opr
  ON opr.organization_id = oprg.organization_id
 AND opr.id = oprg.route_id
JOIN organization_provider_route_group_model_refs oprgm
  ON oprgm.organization_id = oprgp.organization_id
 AND oprgm.route_id = oprgp.route_id
 AND oprgm.route_group_id = oprgp.route_group_id
WHERE oprgp.organization_id = $1
  AND opr.status = $2
  AND oprg.status = $2
`+providerFilter+`
ORDER BY oprgp.provider_id ASC, oprg.priority ASC, oprg.created_at ASC, oprg.id ASC, oprgm.ref_order ASC, oprgm.model_id ASC
`, args...)
	if err != nil {
		return nil, fmt.Errorf("list organization route group models by provider: %w", err)
	}
	defer rows.Close()

	modelsByProviderID, err = scanRouteModelRowsByProvider(rows, modelsByProviderID)
	if err != nil {
		return nil, fmt.Errorf("scan organization route group models by provider: %w", err)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization route group models by provider: %w", err)
	}
	return modelsByProviderID, nil
}

func (txs TxStore) listWorkspaceRouteModelsByProvider(ctx context.Context, workspaceID string, allowedProviderIDs []string) (map[string][]OrganizationProviderModel, error) {
	allowed := normalizeOrganizationProviderIDSet(allowedProviderIDs)
	modelsByProviderID := make(map[string][]OrganizationProviderModel)
	for providerID := range allowed {
		modelsByProviderID[providerID] = []OrganizationProviderModel{}
	}
	if len(allowed) == 0 {
		return modelsByProviderID, nil
	}

	args := []any{workspaceID, organizationProviderStatusActive}
	providerFilter, args := organizationProviderIDFilter("oprgp.provider_id", allowed, args)
	rows, err := txs.db.Query(ctx, `
SELECT
    oprgp.provider_id,
    oprgm.model_id,
    opr.id,
    opr.models_json
FROM organization_workspace_route_grants owrg
JOIN organization_provider_route_group_provider_refs oprgp
  ON oprgp.organization_id = owrg.organization_id
 AND oprgp.route_id = owrg.organization_route_id
JOIN organization_provider_route_groups oprg
  ON oprg.organization_id = oprgp.organization_id
 AND oprg.route_id = oprgp.route_id
 AND oprg.id = oprgp.route_group_id
JOIN organization_provider_routes opr
  ON opr.organization_id = oprg.organization_id
 AND opr.id = oprg.route_id
JOIN organization_provider_route_group_model_refs oprgm
  ON oprgm.organization_id = oprgp.organization_id
 AND oprgm.route_id = oprgp.route_id
 AND oprgm.route_group_id = oprgp.route_group_id
WHERE owrg.workspace_id = $1
  AND owrg.status = $2
  AND oprg.status = $2
  AND opr.status = $2
`+providerFilter+`
ORDER BY oprgp.provider_id ASC, oprg.priority ASC, oprg.created_at ASC, oprg.id ASC, oprgm.ref_order ASC, oprgm.model_id ASC
`, args...)
	if err != nil {
		return nil, fmt.Errorf("list visible route groups for workspace providers: %w", err)
	}
	defer rows.Close()

	modelsByProviderID, err = scanRouteModelRowsByProvider(rows, modelsByProviderID)
	if err != nil {
		return nil, fmt.Errorf("scan visible route group for workspace providers: %w", err)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate visible route groups for workspace providers: %w", err)
	}
	return modelsByProviderID, nil
}

type routeModelRows interface {
	Next() bool
	Scan(...any) error
}

func normalizeOrganizationProviderIDSet(providerIDs []string) map[string]struct{} {
	normalized := make(map[string]struct{}, len(providerIDs))
	for _, providerID := range providerIDs {
		if trimmedProviderID := strings.TrimSpace(providerID); trimmedProviderID != "" {
			normalized[trimmedProviderID] = struct{}{}
		}
	}
	return normalized
}

func organizationProviderIDFilter(column string, providerIDs map[string]struct{}, args []any) (string, []any) {
	if len(providerIDs) == 0 {
		return "", args
	}
	orderedIDs := make([]string, 0, len(providerIDs))
	for providerID := range providerIDs {
		orderedIDs = append(orderedIDs, providerID)
	}
	sort.Strings(orderedIDs)

	placeholders := make([]string, 0, len(orderedIDs))
	for _, providerID := range orderedIDs {
		args = append(args, providerID)
		placeholders = append(placeholders, fmt.Sprintf("$%d", len(args)))
	}
	return "\n  AND " + column + " IN (" + strings.Join(placeholders, ", ") + ")", args
}

func scanRouteModelRowsByProvider(rows routeModelRows, modelsByProviderID map[string][]OrganizationProviderModel) (map[string][]OrganizationProviderModel, error) {
	indexByProviderModelID := make(map[string]map[string]int, len(modelsByProviderID))
	nameByRouteModelID := make(map[string]map[string]string)
	for rows.Next() {
		var providerID string
		var modelID string
		var routeID string
		var routeModelsRaw []byte
		if err := rows.Scan(&providerID, &modelID, &routeID, &routeModelsRaw); err != nil {
			return nil, err
		}
		providerID = strings.TrimSpace(providerID)
		modelID = strings.TrimSpace(modelID)
		routeID = strings.TrimSpace(routeID)
		if providerID == "" || modelID == "" {
			continue
		}

		nameByID := nameByRouteModelID[routeID]
		if nameByID == nil {
			routeModels, err := decodeOrganizationProviderModels(routeModelsRaw)
			if err != nil {
				return nil, err
			}
			nameByID = make(map[string]string, len(routeModels))
			for _, routeModel := range routeModels {
				routeModelID := strings.TrimSpace(routeModel.ID)
				if routeModelID == "" {
					continue
				}
				routeModelName := strings.TrimSpace(routeModel.Name)
				if routeModelName == "" {
					routeModelName = routeModelID
				}
				nameByID[routeModelID] = routeModelName
			}
			nameByRouteModelID[routeID] = nameByID
		}

		modelName := strings.TrimSpace(nameByID[modelID])
		if modelName == "" {
			modelName = modelID
		}
		indices := indexByProviderModelID[providerID]
		if indices == nil {
			indices = make(map[string]int)
			indexByProviderModelID[providerID] = indices
		}
		if existingIndex, ok := indices[modelID]; ok {
			existing := modelsByProviderID[providerID][existingIndex]
			if strings.TrimSpace(existing.Name) == modelID && modelName != modelID {
				modelsByProviderID[providerID][existingIndex].Name = modelName
			}
			continue
		}
		indices[modelID] = len(modelsByProviderID[providerID])
		modelsByProviderID[providerID] = append(modelsByProviderID[providerID], OrganizationProviderModel{
			Name: modelName,
			ID:   modelID,
		})
	}
	return modelsByProviderID, nil
}

func (txs TxStore) ListActiveOrganizationProvidersForLLM(ctx context.Context) ([]OrganizationProviderView, error) {
	rows, err := txs.db.Query(ctx, `
SELECT DISTINCT
    op.id,
    op.organization_id,
    op.name,
    op.description,
    op.driver_kind,
    op.base_url,
    op.endpoints_json,
    op.capabilities_json,
    CAST('[]' AS JSON) AS models_json,
    op.status,
    op.created_at,
    op.updated_at
FROM organization_providers op
WHERE op.status = $1
ORDER BY op.name ASC, op.id ASC
`, organizationProviderStatusActive)
	if err != nil {
		return nil, fmt.Errorf("list active organization providers for llm: %w", err)
	}
	defer rows.Close()

	providers := make([]OrganizationProviderView, 0)
	for rows.Next() {
		provider, err := scanOrganizationProviderView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan active organization provider for llm: %w", err)
		}
		providers = append(providers, provider)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization providers for llm: %w", err)
	}
	return providers, nil
}

func (txs TxStore) ListActiveOrganizationProviderRoutesForLLM(ctx context.Context) ([]OrganizationProviderRouteView, error) {
	rows, err := txs.db.Query(ctx, `
SELECT id, organization_id, vendor, display_name, models_json, source_kind, status, created_at, updated_at
FROM organization_provider_routes
WHERE status = $1
ORDER BY created_at ASC, id ASC
`, organizationProviderStatusActive)
	if err != nil {
		return nil, fmt.Errorf("list active organization provider routes for llm: %w", err)
	}
	defer rows.Close()

	routes := make([]OrganizationProviderRouteView, 0)
	for rows.Next() {
		route, err := scanOrganizationProviderRouteView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan active organization provider route for llm: %w", err)
		}
		routes = append(routes, route)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization provider routes for llm: %w", err)
	}
	return routes, nil
}

func (txs TxStore) ListActiveOrganizationProviderRouteGroupsForLLM(ctx context.Context) ([]OrganizationProviderRouteGroupView, error) {
	rows, err := txs.db.Query(ctx, `
SELECT id, organization_id, route_id, CAST('[]' AS JSON) AS models_json, CAST('[]' AS JSON) AS provider_refs_json, strategy, priority, managed_by_system, status, created_at, updated_at
FROM organization_provider_route_groups
WHERE status = $1
ORDER BY priority ASC, id ASC
`, organizationProviderStatusActive)
	if err != nil {
		return nil, fmt.Errorf("list active organization provider route groups for llm: %w", err)
	}
	defer rows.Close()

	groups := make([]OrganizationProviderRouteGroupView, 0)
	for rows.Next() {
		group, err := scanOrganizationProviderRouteGroupView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan active organization provider route group for llm: %w", err)
		}
		groups = append(groups, group)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization provider route groups for llm: %w", err)
	}
	return txs.hydrateActiveOrganizationProviderRouteGroupRefs(ctx, groups)
}

func (txs TxStore) hydrateActiveOrganizationProviderRouteGroupRefs(ctx context.Context, groups []OrganizationProviderRouteGroupView) ([]OrganizationProviderRouteGroupView, error) {
	if len(groups) == 0 {
		return groups, nil
	}
	groupIndexByID := make(map[string]int, len(groups))
	for i := range groups {
		groups[i].Models = []string{}
		groups[i].ProviderRefs = []string{}
		if groupID := strings.TrimSpace(groups[i].ID); groupID != "" {
			groupIndexByID[groupID] = i
		}
	}

	providerRows, err := txs.db.Query(ctx, `
SELECT
    oprgp.route_group_id,
    oprgp.provider_id
FROM organization_provider_route_group_provider_refs oprgp
JOIN organization_provider_route_groups oprg
  ON oprg.organization_id = oprgp.organization_id
 AND oprg.route_id = oprgp.route_id
 AND oprg.id = oprgp.route_group_id
JOIN organization_provider_routes opr
  ON opr.organization_id = oprgp.organization_id
 AND opr.id = oprgp.route_id
JOIN organization_providers op
  ON op.organization_id = oprgp.organization_id
 AND op.id = oprgp.provider_id
WHERE oprg.status = $1
  AND opr.status = $1
  AND op.status = $1
ORDER BY oprg.priority ASC, oprg.id ASC, oprgp.ref_order ASC, oprgp.provider_id ASC
`, organizationProviderStatusActive)
	if err != nil {
		return nil, fmt.Errorf("list active organization provider route group provider refs for llm: %w", err)
	}
	defer providerRows.Close()

	for providerRows.Next() {
		var groupID string
		var providerID string
		if err := providerRows.Scan(&groupID, &providerID); err != nil {
			return nil, fmt.Errorf("scan active organization provider route group provider ref for llm: %w", err)
		}
		index, ok := groupIndexByID[strings.TrimSpace(groupID)]
		if !ok {
			continue
		}
		if trimmedProviderID := strings.TrimSpace(providerID); trimmedProviderID != "" {
			groups[index].ProviderRefs = append(groups[index].ProviderRefs, trimmedProviderID)
		}
	}
	if err := providerRows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization provider route group provider refs for llm: %w", err)
	}

	modelRows, err := txs.db.Query(ctx, `
SELECT
    oprgm.route_group_id,
    oprgm.model_id
FROM organization_provider_route_group_model_refs oprgm
JOIN organization_provider_route_groups oprg
  ON oprg.organization_id = oprgm.organization_id
 AND oprg.route_id = oprgm.route_id
 AND oprg.id = oprgm.route_group_id
JOIN organization_provider_routes opr
  ON opr.organization_id = oprgm.organization_id
 AND opr.id = oprgm.route_id
WHERE oprg.status = $1
  AND opr.status = $1
ORDER BY oprg.priority ASC, oprg.id ASC, oprgm.ref_order ASC, oprgm.model_id ASC
`, organizationProviderStatusActive)
	if err != nil {
		return nil, fmt.Errorf("list active organization provider route group model refs for llm: %w", err)
	}
	defer modelRows.Close()

	for modelRows.Next() {
		var groupID string
		var modelID string
		if err := modelRows.Scan(&groupID, &modelID); err != nil {
			return nil, fmt.Errorf("scan active organization provider route group model ref for llm: %w", err)
		}
		index, ok := groupIndexByID[strings.TrimSpace(groupID)]
		if !ok {
			continue
		}
		if trimmedModelID := strings.TrimSpace(modelID); trimmedModelID != "" {
			groups[index].Models = append(groups[index].Models, trimmedModelID)
		}
	}
	if err := modelRows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization provider route group model refs for llm: %w", err)
	}
	return groups, nil
}