package organization

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

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

func organizationWorkspaceGrantSource(grantSource string) string {
	switch grantSource {
	case "direct":
		return "manual_admin_grant"
	default:
		return strings.TrimSpace(grantSource)
	}
}

func (txs TxStore) ListOrganizationWorkspaceGrantsForUser(ctx context.Context, userID string, organizationID string, workspaceID string) ([]OrganizationWorkspaceGrantView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if err := txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, workspaceID); err != nil {
		return nil, err
	}

	rows, err := txs.db.Query(ctx, `
SELECT owag.workspace_id,
       owag.organization_member_id,
       om.user_id,
       COALESCE(owmp.member_id, ''),
       owag.workspace_role,
       owag.source
FROM organization_workspace_member_grants owag
JOIN organization_settings os
  ON os.organization_id = owag.organization_id
 AND os.status = 'active'
JOIN organization_members om
  ON om.organization_id = owag.organization_id
 AND om.id = owag.organization_member_id
 AND om.status = 'active'
LEFT JOIN organization_workspace_member_projections owmp
  ON owmp.organization_id = owag.organization_id
 AND owmp.workspace_id = owag.workspace_id
 AND owmp.organization_member_id = owag.organization_member_id
WHERE owag.organization_id = $1
  AND owag.workspace_id = $2
  AND owag.status = 'active'
ORDER BY owag.created_at ASC, owag.organization_member_id ASC
`, organizationID, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("list organization workspace grants: %w", err)
	}
	defer rows.Close()

	grants := make([]OrganizationWorkspaceGrantView, 0)
	for rows.Next() {
		var grant OrganizationWorkspaceGrantView
		var grantSource string
		if err := rows.Scan(
			&grant.WorkspaceID,
			&grant.MemberID,
			&grant.UserID,
			&grant.WorkspaceMemberID,
			&grant.Role,
			&grantSource,
		); err != nil {
			return nil, fmt.Errorf("scan organization workspace grant: %w", err)
		}
		grant.Source = organizationWorkspaceGrantSource(grantSource)
		grants = append(grants, grant)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization workspace grants: %w", err)
	}
	return grants, nil
}

func (txs TxStore) GrantOrganizationMemberWorkspaceAccessForUser(ctx context.Context, userID string, organizationID string, workspaceID string, accountID string, role string) (OrganizationWorkspaceGrantView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationWorkspaceGrantView{}, err
	}
	if err := txs.core.RequireWorkspaceMemberManagementForUser(ctx, userID, workspaceID); err != nil {
		return OrganizationWorkspaceGrantView{}, err
	}

	return withTxValue(ctx, txs.db, func(tx coredb.Tx) (OrganizationWorkspaceGrantView, error) {
		account, err := txs.loadActiveOrganizationMember(ctx, tx, organizationID, accountID)
		if err != nil {
			return OrganizationWorkspaceGrantView{}, err
		}
		if err := txs.requireOrganizationWorkspaceBinding(ctx, tx, organizationID, workspaceID); err != nil {
			return OrganizationWorkspaceGrantView{}, err
		}
		resolvedWorkspaceRole, err := resolveOrganizationWorkspaceRole(role, account.DefaultWorkspaceRole)
		if err != nil {
			return OrganizationWorkspaceGrantView{}, err
		}

		grantID, err := identifiers.NewEntityID("ogg")
		if err != nil {
			return OrganizationWorkspaceGrantView{}, fmt.Errorf("generate organization grant id: %w", err)
		}
		now := time.Now().UTC()
		if err := execPortableUpdateThenInsert(
			ctx,
			tx,
			`
SELECT 1
FROM organization_workspace_member_grants
WHERE workspace_id = $1
  AND organization_member_id = $2
`,
			[]any{workspaceID, accountID},
			`
UPDATE organization_workspace_member_grants
SET workspace_role = $3,
    status = 'active',
    granted_by_user_id = $4,
    updated_at = $5
WHERE workspace_id = $1
  AND organization_member_id = $2
`,
			[]any{workspaceID, accountID, resolvedWorkspaceRole, userID, now},
			`
INSERT INTO organization_workspace_member_grants (
    id,
    organization_id,
    workspace_id,
    organization_member_id,
    workspace_role,
    status,
    source,
    granted_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, 'active', 'direct', $6, $7, $7)
`,
			[]any{grantID, organizationID, workspaceID, accountID, resolvedWorkspaceRole, userID, now},
		); err != nil {
			return OrganizationWorkspaceGrantView{}, fmt.Errorf("upsert organization workspace member grant: %w", err)
		}

		memberID, effectiveRole, _, err := txs.reconcileOrganizationMemberWorkspaceProjection(ctx, tx, organizationID, workspaceID, accountID)
		if err != nil {
			return OrganizationWorkspaceGrantView{}, err
		}

		return OrganizationWorkspaceGrantView{
			WorkspaceID:       workspaceID,
			MemberID:          accountID,
			UserID:            account.UserID,
			WorkspaceMemberID: memberID,
			Role:              effectiveRole,
			Source:            "manual_admin_grant",
		}, nil
	})
}

func (txs TxStore) ListOrganizationProvidersForUser(ctx context.Context, userID string, organizationID string) ([]OrganizationProviderView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}

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

	providers := make([]OrganizationProviderView, 0)
	for rows.Next() {
		provider, err := scanOrganizationProviderView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan organization provider: %w", err)
		}
		providers = append(providers, provider)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization providers: %w", err)
	}
	providerIDs := make([]string, 0, len(providers))
	providerIndexByID := make(map[string]int, len(providers))
	for i := range providers {
		providerIDs = append(providerIDs, providers[i].ID)
		providerIndexByID[strings.TrimSpace(providers[i].ID)] = i
	}
	modelsByProviderID, err := txs.listOrganizationRouteModelsByProvider(ctx, organizationID, providerIDs)
	if err != nil {
		return nil, err
	}
	for providerID, index := range providerIndexByID {
		providers[index].Models = applyOrganizationProviderModelNames(
			modelsByProviderID[providerID],
			providers[index].Models,
		)
	}
	return providers, nil
}

func (txs TxStore) ListOrganizationProviderRoutesForUser(ctx context.Context, userID string, organizationID string) ([]OrganizationProviderRouteView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}

	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 organization_id = $1
ORDER BY created_at ASC, id ASC
`, organizationID)
	if err != nil {
		return nil, fmt.Errorf("list organization provider routes: %w", err)
	}
	defer rows.Close()

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

func (txs TxStore) ListOrganizationProviderRouteGroupsForUser(ctx context.Context, userID string, organizationID string, routeID string) ([]OrganizationProviderRouteGroupView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if _, err := txs.loadOrganizationProviderRoute(ctx, txs.db, organizationID, routeID); err != nil {
		return nil, err
	}

	rows, err := txs.db.Query(ctx, `
SELECT id, organization_id, route_id, models_json, provider_refs_json, strategy, priority, managed_by_system, status, created_at, updated_at
FROM organization_provider_route_groups
WHERE organization_id = $1
  AND route_id = $2
ORDER BY priority ASC, created_at ASC, id ASC
`, organizationID, routeID)
	if err != nil {
		return nil, fmt.Errorf("list organization provider route groups: %w", err)
	}
	defer rows.Close()

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

func (txs TxStore) GetOrganizationProviderForUser(ctx context.Context, userID string, organizationID string, providerID string) (OrganizationProviderView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderView{}, err
	}
	return txs.loadOrganizationProvider(ctx, txs.db, organizationID, providerID)
}

func (txs TxStore) CreateOrganizationProviderForUser(ctx context.Context, userID string, organizationID string, params CreateOrganizationProviderParams) (OrganizationProviderView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderView{}, err
	}

	name := strings.TrimSpace(params.Name)
	if name == "" {
		return OrganizationProviderView{}, badRequest("name is required")
	}
	driverKind := resolveOrganizationProviderDriverKind(params.DriverKind)
	endpoints := normalizeProviderEndpoints(params.Endpoints)
	endpointsJSON, err := encodeProviderEndpoints(endpoints)
	if err != nil {
		return OrganizationProviderView{}, err
	}
	models := normalizeOrganizationProviderModels(params.Models)
	capabilities := inferProviderCapabilities(driverKind, params.BaseURL, endpoints, models, params.Capabilities)
	if !hasProviderCapabilities(capabilities) {
		return OrganizationProviderView{}, badRequest("at least one provider capability is required")
	}
	capabilitiesJSON, err := encodeProviderCapabilities(capabilities)
	if err != nil {
		return OrganizationProviderView{}, err
	}
	providerID, err := identifiers.NewEntityID("opvd")
	if err != nil {
		return OrganizationProviderView{}, fmt.Errorf("generate organization provider id: %w", err)
	}
	now := time.Now().UTC()
	tx := txs.tx

	if _, err := tx.Exec(ctx, `
INSERT INTO organization_providers (
    id,
    organization_id,
    name,
    description,
    driver_kind,
    base_url,
    endpoints_json,
    capabilities_json,
    status,
    created_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, CAST(NULLIF($7, '') AS JSON), CAST(NULLIF($8, '') AS JSON), 'active', $9, $10, $10)
	`, providerID, organizationID, name, strings.TrimSpace(params.Description), driverKind, strings.TrimSpace(params.BaseURL), endpointsJSON, capabilitiesJSON, userID, now); err != nil {
		return OrganizationProviderView{}, fmt.Errorf("insert organization provider: %w", err)
	}
	if err := txs.upsertDefaultOrganizationProviderRouteAndGroup(ctx, tx, organizationID, providerID, name, capabilities, models, now); err != nil {
		return OrganizationProviderView{}, err
	}
	return txs.loadOrganizationProvider(ctx, tx, organizationID, providerID)
}

func (txs TxStore) UpdateOrganizationProviderForUser(ctx context.Context, userID string, organizationID string, providerID string, params UpdateOrganizationProviderParams) (OrganizationProviderView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderView{}, err
	}

	current, err := txs.loadOrganizationProvider(ctx, txs.db, organizationID, providerID)
	if err != nil {
		return OrganizationProviderView{}, err
	}

	name := current.Name
	if params.Name != nil {
		name = strings.TrimSpace(*params.Name)
		if name == "" {
			return OrganizationProviderView{}, badRequest("name is required")
		}
	}
	description := current.Description
	if params.Description != nil {
		description = strings.TrimSpace(*params.Description)
	}
	driverKind := current.DriverKind
	if params.DriverKind != nil {
		driverKind = resolveOrganizationProviderDriverKind(*params.DriverKind)
	}
	baseURL := current.BaseURL
	if params.BaseURL != nil {
		baseURL = strings.TrimSpace(*params.BaseURL)
	}
	endpoints := current.Endpoints
	if params.Endpoints != nil {
		endpoints = normalizeProviderEndpoints(*params.Endpoints)
	}
	endpointsJSON, err := encodeProviderEndpoints(endpoints)
	if err != nil {
		return OrganizationProviderView{}, err
	}
	models := current.Models
	if params.Models != nil {
		models = normalizeOrganizationProviderModels(*params.Models)
	}
	capabilities := current.Capabilities
	if params.Capabilities != nil {
		capabilities = inferProviderCapabilities(driverKind, baseURL, endpoints, models, *params.Capabilities)
	}
	if !hasProviderCapabilities(capabilities) {
		return OrganizationProviderView{}, badRequest("at least one provider capability is required")
	}
	capabilitiesJSON, err := encodeProviderCapabilities(capabilities)
	if err != nil {
		return OrganizationProviderView{}, err
	}
	status := current.Status
	if params.Status != nil {
		status, err = resolveOrganizationProviderStatus(*params.Status)
		if err != nil {
			return OrganizationProviderView{}, err
		}
	}

	now := time.Now().UTC()
	tx := txs.tx

	if _, err := tx.Exec(ctx, `
UPDATE organization_providers
SET name = $3,
    description = $4,
    driver_kind = $5,
    base_url = $6,
    endpoints_json = CAST(NULLIF($7, '') AS JSON),
    capabilities_json = CAST(NULLIF($8, '') AS JSON),
    status = $9,
    updated_at = $10
WHERE organization_id = $1
  AND id = $2
`, organizationID, providerID, name, description, driverKind, baseURL, endpointsJSON, capabilitiesJSON, status, now); err != nil {
		return OrganizationProviderView{}, fmt.Errorf("update organization provider: %w", err)
	}
	if err := txs.upsertDefaultOrganizationProviderRouteAndGroup(ctx, tx, organizationID, providerID, name, capabilities, models, now); err != nil {
		return OrganizationProviderView{}, err
	}
	return txs.loadOrganizationProvider(ctx, tx, organizationID, providerID)
}

func (txs TxStore) DeleteOrganizationProviderForUser(ctx context.Context, userID string, organizationID string, providerID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	tx := txs.tx
	if _, err := tx.Exec(ctx, `
DELETE FROM organization_provider_route_group_provider_refs
WHERE organization_id = $1 AND provider_id = $2
`, organizationID, providerID); err != nil {
		return fmt.Errorf("delete organization provider route group provider refs: %w", err)
	}
	commandTag, err := tx.Exec(ctx, `
DELETE FROM organization_providers
WHERE organization_id = $1 AND id = $2
`, organizationID, providerID)
	if err != nil {
		return fmt.Errorf("delete organization provider: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) CreateOrganizationProviderRouteForUser(ctx context.Context, userID string, organizationID string, params CreateOrganizationProviderRouteParams) (OrganizationProviderRouteView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderRouteView{}, err
	}

	vendor := strings.ToLower(strings.TrimSpace(params.Vendor))
	if vendor == "" {
		return OrganizationProviderRouteView{}, badRequest("vendor is required")
	}
	displayName := strings.TrimSpace(params.DisplayName)
	if displayName == "" {
		displayName = vendor
	}
	models := normalizeOrganizationProviderModels(params.Models)
	modelsJSON, err := encodeOrganizationProviderModels(models)
	if err != nil {
		return OrganizationProviderRouteView{}, err
	}

	routeID, err := identifiers.NewEntityID("oprt")
	if err != nil {
		return OrganizationProviderRouteView{}, fmt.Errorf("generate organization provider route id: %w", err)
	}
	now := time.Now().UTC()
	if _, err := txs.db.Exec(ctx, `
INSERT INTO organization_provider_routes (
    id,
    organization_id,
    vendor,
    display_name,
    models_json,
    status,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, CAST(NULLIF($5, '') AS JSON), 'active', $6, $6)
`, routeID, organizationID, vendor, displayName, modelsJSON, now); err != nil {
		return OrganizationProviderRouteView{}, fmt.Errorf("insert organization provider route: %w", err)
	}
	return txs.loadOrganizationProviderRoute(ctx, txs.db, organizationID, routeID)
}

func (txs TxStore) UpdateOrganizationProviderRouteForUser(ctx context.Context, userID string, organizationID string, routeID string, params UpdateOrganizationProviderRouteParams) (OrganizationProviderRouteView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderRouteView{}, err
	}
	current, err := txs.loadOrganizationProviderRoute(ctx, txs.db, organizationID, routeID)
	if err != nil {
		return OrganizationProviderRouteView{}, err
	}

	vendor := current.Vendor
	if params.Vendor != nil {
		vendor = strings.ToLower(strings.TrimSpace(*params.Vendor))
		if vendor == "" {
			return OrganizationProviderRouteView{}, badRequest("vendor is required")
		}
	}

	displayName := current.DisplayName
	if params.DisplayName != nil {
		displayName = strings.TrimSpace(*params.DisplayName)
		if displayName == "" {
			displayName = vendor
		}
	}
	if displayName == "" {
		displayName = vendor
	}

	models := current.Models
	if params.Models != nil {
		models = normalizeOrganizationProviderModels(*params.Models)
	}
	modelsJSON, err := encodeOrganizationProviderModels(models)
	if err != nil {
		return OrganizationProviderRouteView{}, err
	}

	now := time.Now().UTC()
	if _, err := txs.db.Exec(ctx, `
UPDATE organization_provider_routes
SET vendor = $3,
    display_name = $4,
    models_json = CAST(NULLIF($5, '') AS JSON),
    updated_at = $6
WHERE organization_id = $1
  AND id = $2
`, organizationID, routeID, vendor, displayName, modelsJSON, now); err != nil {
		return OrganizationProviderRouteView{}, fmt.Errorf("update organization provider route: %w", err)
	}
	return txs.loadOrganizationProviderRoute(ctx, txs.db, organizationID, routeID)
}

func (txs TxStore) DeleteOrganizationProviderRouteForUser(ctx context.Context, userID string, organizationID string, routeID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	rows, err := txs.db.Query(ctx, `
SELECT id, organization_id, route_id, models_json, provider_refs_json, strategy, priority, managed_by_system, status, created_at, updated_at
FROM organization_provider_route_groups
WHERE organization_id = $1
  AND route_id = $2
`, organizationID, routeID)
	if err != nil {
		return fmt.Errorf("list organization provider route groups before delete: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		_, scanErr := scanOrganizationProviderRouteGroupView(rows)
		if scanErr != nil {
			return fmt.Errorf("scan organization provider route group before delete: %w", scanErr)
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate organization provider route groups before delete: %w", err)
	}

	commandTag, err := txs.db.Exec(ctx, `
DELETE FROM organization_provider_routes
WHERE organization_id = $1
  AND id = $2
`, organizationID, routeID)
	if err != nil {
		return fmt.Errorf("delete organization provider route: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) CreateOrganizationProviderRouteGroupForUser(ctx context.Context, userID string, organizationID string, routeID string, params CreateOrganizationProviderRouteGroupParams) (OrganizationProviderRouteGroupView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	if _, err := txs.loadOrganizationProviderRoute(ctx, txs.db, organizationID, routeID); err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}

	modelsJSON, err := encodeJSONStringList(params.Models)
	if err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	providerRefsJSON, err := encodeJSONStringList(params.ProviderRefs)
	if err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	if strings.TrimSpace(providerRefsJSON) == "" || providerRefsJSON == "[]" {
		return OrganizationProviderRouteGroupView{}, badRequest("providerRefs is required")
	}

	strategy := strings.TrimSpace(params.Strategy)
	if strategy == "" {
		strategy = "round_robin"
	}
	priority := params.Priority
	if priority <= 0 {
		priority = 100
	}
	groupID, err := identifiers.NewEntityID("oprg")
	if err != nil {
		return OrganizationProviderRouteGroupView{}, fmt.Errorf("generate organization provider route group id: %w", err)
	}
	now := time.Now().UTC()
	tx := txs.tx
	if _, err := tx.Exec(ctx, `
INSERT INTO organization_provider_route_groups (
    id,
    organization_id,
    route_id,
    models_json,
    provider_refs_json,
    strategy,
    priority,
    managed_by_system,
    status,
    created_at,
    updated_at
) VALUES ($1, $2, $3, CAST(NULLIF($4, '') AS JSON), CAST(NULLIF($5, '') AS JSON), $6, $7, FALSE, 'active', $8, $8)
`, groupID, organizationID, routeID, modelsJSON, providerRefsJSON, strategy, priority, now); err != nil {
		return OrganizationProviderRouteGroupView{}, fmt.Errorf("insert organization provider route group: %w", err)
	}
	if err := replaceOrganizationProviderRouteGroupRefs(ctx, tx, organizationID, routeID, groupID, params.Models, params.ProviderRefs, now); err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	return txs.loadOrganizationProviderRouteGroup(ctx, tx, organizationID, routeID, groupID)
}

func (txs TxStore) UpdateOrganizationProviderRouteGroupForUser(ctx context.Context, userID string, organizationID string, routeID string, groupID string, params UpdateOrganizationProviderRouteGroupParams) (OrganizationProviderRouteGroupView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	group, err := txs.loadOrganizationProviderRouteGroup(ctx, txs.db, organizationID, routeID, groupID)
	if err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}

	models := group.Models
	if params.Models != nil {
		models = *params.Models
	}
	modelsJSON, err := encodeJSONStringList(models)
	if err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}

	providerRefs := group.ProviderRefs
	if params.ProviderRefs != nil {
		providerRefs = *params.ProviderRefs
	}
	providerRefsJSON, err := encodeJSONStringList(providerRefs)
	if err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	if strings.TrimSpace(providerRefsJSON) == "" || providerRefsJSON == "[]" {
		return OrganizationProviderRouteGroupView{}, badRequest("providerRefs is required")
	}

	strategy := group.Strategy
	if params.Strategy != nil {
		strategy = strings.TrimSpace(*params.Strategy)
		if strategy == "" {
			strategy = "round_robin"
		}
	}

	priority := group.Priority
	if params.Priority != nil {
		priority = *params.Priority
		if priority <= 0 {
			priority = 100
		}
	}

	now := time.Now().UTC()
	tx := txs.tx
	if _, err := tx.Exec(ctx, `
UPDATE organization_provider_route_groups
SET models_json = CAST(NULLIF($4, '') AS JSON),
    provider_refs_json = CAST(NULLIF($5, '') AS JSON),
    strategy = $6,
    priority = $7,
    updated_at = $8
WHERE organization_id = $1
  AND route_id = $2
  AND id = $3
`, organizationID, routeID, groupID, modelsJSON, providerRefsJSON, strategy, priority, now); err != nil {
		return OrganizationProviderRouteGroupView{}, fmt.Errorf("update organization provider route group: %w", err)
	}
	if err := replaceOrganizationProviderRouteGroupRefs(ctx, tx, organizationID, routeID, groupID, models, providerRefs, now); err != nil {
		return OrganizationProviderRouteGroupView{}, err
	}
	return txs.loadOrganizationProviderRouteGroup(ctx, tx, organizationID, routeID, groupID)
}

func (txs TxStore) DeleteOrganizationProviderRouteGroupForUser(ctx context.Context, userID string, organizationID string, routeID string, groupID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	if _, err := txs.loadOrganizationProviderRouteGroup(ctx, txs.db, organizationID, routeID, groupID); err != nil {
		return err
	}
	tx := txs.tx
	if err := deleteOrganizationProviderRouteGroupRefs(ctx, tx, groupID); err != nil {
		return err
	}
	commandTag, err := tx.Exec(ctx, `
DELETE FROM organization_provider_route_groups
WHERE organization_id = $1 AND route_id = $2 AND id = $3
`, organizationID, routeID, groupID)
	if err != nil {
		return fmt.Errorf("delete organization provider route group: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) ListOrganizationWorkspaceProviderGrantsForUser(ctx context.Context, userID string, organizationID string, workspaceID string) ([]OrganizationWorkspaceProviderGrantView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if err := txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, workspaceID); err != nil {
		return nil, err
	}

	rows, err := txs.db.Query(ctx, `
SELECT owpg.workspace_id,
       op.id,
       op.name,
       op.driver_kind,
       op.base_url,
       owpg.status,
       owpg.created_at,
       owpg.updated_at
FROM organization_workspace_provider_grants owpg
JOIN organization_providers op
  ON op.organization_id = owpg.organization_id
 AND op.id = owpg.organization_provider_id
WHERE owpg.organization_id = $1
  AND owpg.workspace_id = $2
ORDER BY owpg.created_at ASC, owpg.id ASC
`, organizationID, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("list organization workspace provider grants: %w", err)
	}
	defer rows.Close()

	grants := make([]OrganizationWorkspaceProviderGrantView, 0)
	grantedProviderIDs := make([]string, 0)
	for rows.Next() {
		grant, err := scanOrganizationWorkspaceProviderGrantView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan organization workspace provider grant: %w", err)
		}
		if trimmedProviderID := strings.TrimSpace(grant.ProviderID); trimmedProviderID != "" {
			grantedProviderIDs = append(grantedProviderIDs, trimmedProviderID)
		}
		grants = append(grants, grant)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization workspace provider grants: %w", err)
	}
	modelsByProviderID, err := txs.listWorkspaceRouteModelsByProvider(ctx, workspaceID, grantedProviderIDs)
	if err != nil {
		return nil, err
	}
	for i := range grants {
		grants[i].Models = append([]OrganizationProviderModel(nil), modelsByProviderID[strings.TrimSpace(grants[i].ProviderID)]...)
	}
	return grants, nil
}

func (txs TxStore) GrantOrganizationWorkspaceProviderForUser(ctx context.Context, userID string, organizationID string, workspaceID string, providerID string) (OrganizationWorkspaceProviderGrantView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationWorkspaceProviderGrantView{}, err
	}

	tx := txs.tx

	if err := txs.requireOrganizationWorkspaceBinding(ctx, tx, organizationID, workspaceID); err != nil {
		return OrganizationWorkspaceProviderGrantView{}, err
	}
	provider, err := txs.loadOrganizationProvider(ctx, tx, organizationID, providerID)
	if err != nil {
		return OrganizationWorkspaceProviderGrantView{}, err
	}
	grantID, err := identifiers.NewEntityID("owpg")
	if err != nil {
		return OrganizationWorkspaceProviderGrantView{}, fmt.Errorf("generate organization provider grant id: %w", err)
	}
	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
INSERT INTO organization_workspace_provider_grants (
    id,
    organization_id,
    workspace_id,
    organization_provider_id,
    status,
    granted_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, 'active', $5, $6, $6)
ON DUPLICATE KEY UPDATE
    status = 'active',
    granted_by_user_id = VALUES(granted_by_user_id),
    updated_at = VALUES(updated_at)
`, grantID, organizationID, workspaceID, providerID, userID, now); err != nil {
		return OrganizationWorkspaceProviderGrantView{}, fmt.Errorf("upsert organization provider grant: %w", err)
	}
	return OrganizationWorkspaceProviderGrantView{
		WorkspaceID:   workspaceID,
		ProviderID:    provider.ID,
		ProviderName:  provider.Name,
		DriverKind:    provider.DriverKind,
		ProviderType:  inferOrganizationProviderRouteVendor(provider.Capabilities),
		BaseURL:       provider.BaseURL,
		Models:        provider.Models,
		Status:        organizationProviderStatusActive,
		GrantedAt:     now,
		LastUpdatedAt: now,
	}, nil
}

func (txs TxStore) RevokeOrganizationWorkspaceProviderForUser(ctx context.Context, userID string, organizationID string, workspaceID string, providerID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	if err := txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, workspaceID); err != nil {
		return err
	}
	commandTag, err := txs.db.Exec(ctx, `
DELETE FROM organization_workspace_provider_grants
WHERE organization_id = $1
  AND workspace_id = $2
  AND organization_provider_id = $3
`, organizationID, workspaceID, providerID)
	if err != nil {
		return fmt.Errorf("delete organization provider grant: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) ListOrganizationWorkspaceRouteGrantsForUser(ctx context.Context, userID string, organizationID string, workspaceID string) ([]OrganizationWorkspaceRouteGrantView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if err := txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, workspaceID); err != nil {
		return nil, err
	}

	rows, err := txs.db.Query(ctx, `
SELECT owrg.workspace_id,
       opr.id,
       opr.vendor,
       opr.display_name,
       opr.models_json,
       owrg.status,
       owrg.created_at,
       owrg.updated_at
FROM organization_workspace_route_grants owrg
JOIN organization_provider_routes opr
  ON opr.organization_id = owrg.organization_id
 AND opr.id = owrg.organization_route_id
WHERE owrg.organization_id = $1
  AND owrg.workspace_id = $2
ORDER BY owrg.created_at ASC, owrg.id ASC
`, organizationID, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("list organization workspace route grants: %w", err)
	}
	defer rows.Close()

	grants := make([]OrganizationWorkspaceRouteGrantView, 0)
	for rows.Next() {
		grant, err := scanOrganizationWorkspaceRouteGrantView(rows)
		if err != nil {
			return nil, fmt.Errorf("scan organization workspace route grant: %w", err)
		}
		grants = append(grants, grant)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization workspace route grants: %w", err)
	}
	return grants, nil
}

func (txs TxStore) GrantOrganizationWorkspaceRouteForUser(ctx context.Context, userID string, organizationID string, workspaceID string, routeID string) (OrganizationWorkspaceRouteGrantView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationWorkspaceRouteGrantView{}, err
	}

	tx := txs.tx

	if err := txs.requireOrganizationWorkspaceBinding(ctx, tx, organizationID, workspaceID); err != nil {
		return OrganizationWorkspaceRouteGrantView{}, err
	}
	route, err := txs.loadOrganizationProviderRoute(ctx, tx, organizationID, routeID)
	if err != nil {
		return OrganizationWorkspaceRouteGrantView{}, err
	}
	grantID, err := identifiers.NewEntityID("owrg")
	if err != nil {
		return OrganizationWorkspaceRouteGrantView{}, fmt.Errorf("generate organization route grant id: %w", err)
	}
	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
INSERT INTO organization_workspace_route_grants (
    id,
    organization_id,
    workspace_id,
    organization_route_id,
    status,
    granted_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, 'active', $5, $6, $6)
ON DUPLICATE KEY UPDATE
    status = 'active',
    granted_by_user_id = VALUES(granted_by_user_id),
    updated_at = VALUES(updated_at)
`, grantID, organizationID, workspaceID, routeID, userID, now); err != nil {
		return OrganizationWorkspaceRouteGrantView{}, fmt.Errorf("upsert organization route grant: %w", err)
	}
	return OrganizationWorkspaceRouteGrantView{
		WorkspaceID:   workspaceID,
		RouteID:       route.ID,
		Vendor:        route.Vendor,
		DisplayName:   route.DisplayName,
		Models:        route.Models,
		Status:        organizationProviderStatusActive,
		GrantedAt:     now,
		LastUpdatedAt: now,
	}, nil
}

func (txs TxStore) RevokeOrganizationWorkspaceRouteForUser(ctx context.Context, userID string, organizationID string, workspaceID string, routeID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	if err := txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, workspaceID); err != nil {
		return err
	}
	commandTag, err := txs.db.Exec(ctx, `
DELETE FROM organization_workspace_route_grants
WHERE organization_id = $1
  AND workspace_id = $2
  AND organization_route_id = $3
`, organizationID, workspaceID, routeID)
	if err != nil {
		return fmt.Errorf("delete organization route grant: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) upsertDefaultOrganizationProviderRouteAndGroup(ctx context.Context, queryer queryExecerRower, organizationID string, providerID string, name string, capabilities OrganizationProviderCapabilities, models []OrganizationProviderModel, now time.Time) error {
	vendor := slugifyOrganizationName(name)
	if vendor == "" {
		vendor = inferOrganizationProviderRouteVendor(capabilities)
	}
	if vendor == "" {
		vendor = "provider"
	}

	routeID := ""
	row := queryer.QueryRow(ctx, `
SELECT opr.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 = oprg.organization_id
 AND opr.id = oprg.route_id
WHERE oprgp.organization_id = $1
  AND oprgp.provider_id = $2
  AND oprg.managed_by_system = TRUE
ORDER BY oprg.created_at ASC, oprg.id ASC
LIMIT 1
`, organizationID, providerID)
	switch err := row.Scan(&routeID); {
	case err == nil:
	case coredb.IsNotFound(err):
		newRouteID, idErr := identifiers.NewEntityID("oprt")
		if idErr != nil {
			return fmt.Errorf("generate organization provider route id: %w", idErr)
		}
		if _, execErr := queryer.Exec(ctx, `
INSERT INTO organization_provider_routes (
    id,
    organization_id,
    vendor,
    display_name,
    status,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $6)
`, newRouteID, organizationID, vendor, name, organizationProviderStatusActive, now); execErr != nil {
			return fmt.Errorf("insert organization provider route: %w", execErr)
		}
		routeID = newRouteID
	default:
		return fmt.Errorf("load organization provider route: %w", err)
	}

	routeModelsJSON, err := encodeOrganizationProviderModels(models)
	if err != nil {
		return err
	}

	if _, err := queryer.Exec(ctx, `
UPDATE organization_provider_routes
SET vendor = $3,
    display_name = $4,
    models_json = CAST(NULLIF($5, '') AS JSON),
    status = $6,
    updated_at = $7
WHERE organization_id = $1
  AND id = $2
`, organizationID, routeID, vendor, name, routeModelsJSON, organizationProviderStatusActive, now); err != nil {
		return fmt.Errorf("update organization provider route: %w", err)
	}

	modelsJSON, err := encodeJSONStringList(providerModelIDs(models))
	if err != nil {
		return err
	}
	providerRefsJSON, err := encodeJSONStringList([]string{providerID})
	if err != nil {
		return err
	}

	groupID := ""
	groupRow := queryer.QueryRow(ctx, `
SELECT id
FROM organization_provider_route_groups
WHERE organization_id = $1
  AND route_id = $2
  AND managed_by_system = TRUE
LIMIT 1
`, organizationID, routeID)
	switch err := groupRow.Scan(&groupID); {
	case err == nil:
		_, err = queryer.Exec(ctx, `
UPDATE organization_provider_route_groups
SET models_json = CAST(NULLIF($4, '') AS JSON),
    provider_refs_json = CAST(NULLIF($5, '') AS JSON),
    strategy = 'round_robin',
    priority = 100,
    status = $6,
    updated_at = $7
WHERE organization_id = $1
  AND route_id = $2
  AND id = $3
`, organizationID, routeID, groupID, modelsJSON, providerRefsJSON, organizationProviderStatusActive, now)
		if err != nil {
			return fmt.Errorf("update organization provider route group: %w", err)
		}
		if err := replaceOrganizationProviderRouteGroupRefs(ctx, queryer, organizationID, routeID, groupID, providerModelIDs(models), []string{providerID}, now); err != nil {
			return err
		}
	case coredb.IsNotFound(err):
		groupID, err = identifiers.NewEntityID("oprg")
		if err != nil {
			return fmt.Errorf("generate organization provider route group id: %w", err)
		}
		if _, err := queryer.Exec(ctx, `
INSERT INTO organization_provider_route_groups (
    id,
    organization_id,
    route_id,
    models_json,
    provider_refs_json,
    strategy,
    priority,
    managed_by_system,
    status,
    created_at,
    updated_at
) VALUES ($1, $2, $3, CAST(NULLIF($4, '') AS JSON), CAST(NULLIF($5, '') AS JSON), 'round_robin', 100, TRUE, $6, $7, $7)
`, groupID, organizationID, routeID, modelsJSON, providerRefsJSON, organizationProviderStatusActive, now); err != nil {
			return fmt.Errorf("insert organization provider route group: %w", err)
		}
		if err := replaceOrganizationProviderRouteGroupRefs(ctx, queryer, organizationID, routeID, groupID, providerModelIDs(models), []string{providerID}, now); err != nil {
			return err
		}
	default:
		return fmt.Errorf("load organization provider route group: %w", err)
	}
	return nil
}