package organization

import (
	"context"
	"fmt"
	"strings"

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

type activeOrganizationMember struct {
	MemberID             string
	UserID               string
	Source               string
	DefaultWorkspaceRole string
	SyncMode             string
}

func (txs TxStore) resolveOrganizationTeam(ctx context.Context, queryer queryRower, organizationID string, teamID string) (string, string, error) {
	trimmedTeamID := strings.TrimSpace(teamID)
	if trimmedTeamID == "" {
		row := queryer.QueryRow(ctx, `
SELECT id, name
FROM organization_teams
WHERE organization_id = $1 AND is_default = TRUE
`, organizationID)
		var resolvedTeamID string
		var resolvedTeamName string
		if err := row.Scan(&resolvedTeamID, &resolvedTeamName); err != nil {
			if coredb.IsNotFound(err) {
				return "", "", ErrNotFound
			}
			return "", "", fmt.Errorf("load default organization team: %w", err)
		}
		return resolvedTeamID, resolvedTeamName, nil
	}

	row := queryer.QueryRow(ctx, `
SELECT id, name
FROM organization_teams
WHERE organization_id = $1 AND id = $2
`, organizationID, trimmedTeamID)
	var resolvedTeamID string
	var resolvedTeamName string
	if err := row.Scan(&resolvedTeamID, &resolvedTeamName); err != nil {
		if coredb.IsNotFound(err) {
			return "", "", ErrNotFound
		}
		return "", "", fmt.Errorf("load organization team: %w", err)
	}
	return resolvedTeamID, resolvedTeamName, nil
}

func (txs TxStore) loadOrganizationWorkspace(ctx context.Context, queryer queryRower, organizationID string, workspaceID string) (OrganizationWorkspaceView, error) {
	row := queryer.QueryRow(ctx, `
SELECT w.id,
       w.slug,
       w.name,
       w.created_at,
       COALESCE(os.default_workspace_id = w.id, FALSE)
FROM workspaces w
LEFT JOIN organization_settings os ON os.organization_id = w.organization_id
WHERE w.organization_id = $1 AND w.id = $2
`, organizationID, workspaceID)

	var workspace OrganizationWorkspaceView
	if err := row.Scan(&workspace.ID, &workspace.Slug, &workspace.Name, &workspace.BoundAt, &workspace.IsDefault); err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationWorkspaceView{}, ErrNotFound
		}
		return OrganizationWorkspaceView{}, fmt.Errorf("load organization workspace: %w", err)
	}
	return workspace, nil
}

func (txs TxStore) loadActiveOrganizationMember(ctx context.Context, queryer queryRower, organizationID string, memberID string) (activeOrganizationMember, error) {
	row := queryer.QueryRow(ctx, `
SELECT id, user_id, source, default_workspace_role, sync_mode
FROM organization_members
WHERE organization_id = $1
  AND id = $2
  AND status = 'active'
`, organizationID, memberID)

	var member activeOrganizationMember
	if err := row.Scan(&member.MemberID, &member.UserID, &member.Source, &member.DefaultWorkspaceRole, &member.SyncMode); err != nil {
		if coredb.IsNotFound(err) {
			return activeOrganizationMember{}, ErrNotFound
		}
		return activeOrganizationMember{}, fmt.Errorf("load organization member: %w", err)
	}
	return member, nil
}

func (txs TxStore) loadOrganizationProvider(ctx context.Context, queryer queryRower, organizationID string, providerID string) (OrganizationProviderView, error) {
	row := queryer.QueryRow(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 AND id = $2
`, organizationID, providerID)
	provider, err := scanOrganizationProviderView(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationProviderView{}, ErrNotFound
		}
		return OrganizationProviderView{}, fmt.Errorf("load organization provider: %w", err)
	}
	modelsByProviderID, err := txs.listOrganizationRouteModelsByProvider(ctx, organizationID, []string{provider.ID})
	if err != nil {
		return OrganizationProviderView{}, err
	}
	provider.Models = applyOrganizationProviderModelNames(
		modelsByProviderID[strings.TrimSpace(provider.ID)],
		provider.Models,
	)
	return provider, nil
}

func (txs TxStore) loadOrganizationProviderRoute(ctx context.Context, queryer queryRower, organizationID string, routeID string) (OrganizationProviderRouteView, error) {
	row := queryer.QueryRow(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 AND id = $2
`, organizationID, routeID)
	route, err := scanOrganizationProviderRouteView(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationProviderRouteView{}, ErrNotFound
		}
		return OrganizationProviderRouteView{}, fmt.Errorf("load organization provider route: %w", err)
	}
	return route, nil
}

func (txs TxStore) loadOrganizationProviderRouteGroup(ctx context.Context, queryer queryRower, organizationID string, routeID string, groupID string) (OrganizationProviderRouteGroupView, error) {
	row := queryer.QueryRow(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 AND id = $3
`, organizationID, routeID, groupID)
	group, err := scanOrganizationProviderRouteGroupView(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationProviderRouteGroupView{}, ErrNotFound
		}
		return OrganizationProviderRouteGroupView{}, fmt.Errorf("load organization provider route group: %w", err)
	}
	return group, nil
}

func (txs TxStore) organizationProviderRouteHasManagedGroup(ctx context.Context, queryer queryRower, organizationID string, routeID string) (bool, error) {
	var exists int
	if err := queryer.QueryRow(ctx, `
SELECT 1
FROM organization_provider_route_groups
WHERE organization_id = $1
  AND route_id = $2
  AND managed_by_system = TRUE
LIMIT 1
`, organizationID, routeID).Scan(&exists); err != nil {
		if coredb.IsNotFound(err) {
			return false, nil
		}
		return false, fmt.Errorf("check organization provider route managed groups: %w", err)
	}
	return true, nil
}