1 files changed, 146 insertions, 0 deletions
diff --git a/internal/controlplane/queries.go b/internal/controlplane/queries.go
new file mode 100644
index 0000000..732cb46
--- /dev/null
+++ b/internal/controlplane/queries.go
@@ -0,0 +1,146 @@
+package controlplane
+
+import (
+	"context"
+	"encoding/json"
+	"fmt"
+)
+
+func (s *Store) Overview(ctx context.Context) (Overview, error) {
+	var result Overview
+	err := s.db.QueryRow(ctx, `
+		SELECT
+			(SELECT generation FROM control_state WHERE singleton = TRUE),
+			(SELECT count(*) FROM tenants WHERE status = 'active'),
+			(SELECT count(*) FROM projects WHERE status = 'active'),
+			(SELECT count(*) FROM api_keys WHERE status = 'active'),
+			(SELECT count(*) FROM providers WHERE enabled = TRUE),
+			(SELECT count(*) FROM models WHERE enabled = TRUE)`,
+	).Scan(&result.Generation, &result.Tenants, &result.Projects, &result.APIKeys, &result.Providers, &result.Models)
+	if err != nil {
+		return Overview{}, fmt.Errorf("query control-plane overview: %w", err)
+	}
+	return result, nil
+}
+
+func (s *Store) ListTenants(ctx context.Context) ([]Tenant, error) {
+	rows, err := s.db.Query(ctx, `SELECT id::text, slug, name, status, created_at FROM tenants ORDER BY created_at DESC`)
+	if err != nil {
+		return nil, fmt.Errorf("query tenants: %w", err)
+	}
+	defer rows.Close()
+	result := make([]Tenant, 0)
+	for rows.Next() {
+		var item Tenant
+		if err := rows.Scan(&item.ID, &item.Slug, &item.Name, &item.Status, &item.CreatedAt); err != nil {
+			return nil, fmt.Errorf("scan tenant: %w", err)
+		}
+		result = append(result, item)
+	}
+	return result, rows.Err()
+}
+
+func (s *Store) ListProjects(ctx context.Context) ([]Project, error) {
+	rows, err := s.db.Query(ctx, `SELECT id::text, tenant_id::text, slug, name, status, created_at FROM projects ORDER BY created_at DESC`)
+	if err != nil {
+		return nil, fmt.Errorf("query projects: %w", err)
+	}
+	defer rows.Close()
+	result := make([]Project, 0)
+	for rows.Next() {
+		var item Project
+		if err := rows.Scan(&item.ID, &item.TenantID, &item.Slug, &item.Name, &item.Status, &item.CreatedAt); err != nil {
+			return nil, fmt.Errorf("scan project: %w", err)
+		}
+		result = append(result, item)
+	}
+	return result, rows.Err()
+}
+
+func (s *Store) ListAPIKeys(ctx context.Context) ([]APIKey, error) {
+	rows, err := s.db.Query(ctx, `
+		SELECT id::text, tenant_id::text, project_id::text, name, key_prefix, scopes, status, created_at
+		FROM api_keys ORDER BY created_at DESC`)
+	if err != nil {
+		return nil, fmt.Errorf("query API keys: %w", err)
+	}
+	defer rows.Close()
+	result := make([]APIKey, 0)
+	for rows.Next() {
+		var item APIKey
+		var scopesJSON []byte
+		if err := rows.Scan(&item.ID, &item.TenantID, &item.ProjectID, &item.Name, &item.KeyPrefix, &scopesJSON, &item.Status, &item.CreatedAt); err != nil {
+			return nil, fmt.Errorf("scan API key: %w", err)
+		}
+		if err := json.Unmarshal(scopesJSON, &item.Scopes); err != nil {
+			return nil, fmt.Errorf("decode API key scopes: %w", err)
+		}
+		result = append(result, item)
+	}
+	return result, rows.Err()
+}
+
+func (s *Store) ListProviders(ctx context.Context) ([]Provider, error) {
+	rows, err := s.db.Query(ctx, `
+		SELECT p.id::text, p.name, p.protocol, p.base_url, p.enabled, count(r.id), p.created_at
+		FROM providers p LEFT JOIN model_routes r ON r.provider_id = p.id
+		GROUP BY p.id ORDER BY p.created_at DESC`)
+	if err != nil {
+		return nil, fmt.Errorf("query providers: %w", err)
+	}
+	defer rows.Close()
+	result := make([]Provider, 0)
+	for rows.Next() {
+		var item Provider
+		if err := rows.Scan(&item.ID, &item.Name, &item.Protocol, &item.BaseURL, &item.Enabled, &item.RouteCount, &item.CreatedAt); err != nil {
+			return nil, fmt.Errorf("scan provider: %w", err)
+		}
+		result = append(result, item)
+	}
+	return result, rows.Err()
+}
+
+func (s *Store) ListModels(ctx context.Context) ([]Model, error) {
+	rows, err := s.db.Query(ctx, `SELECT id::text, public_id, owned_by, enabled, created_at FROM models ORDER BY public_id`)
+	if err != nil {
+		return nil, fmt.Errorf("query models: %w", err)
+	}
+	models := make([]Model, 0)
+	positions := make(map[string]int)
+	for rows.Next() {
+		var item Model
+		if err := rows.Scan(&item.ID, &item.PublicID, &item.OwnedBy, &item.Enabled, &item.CreatedAt); err != nil {
+			rows.Close()
+			return nil, fmt.Errorf("scan model: %w", err)
+		}
+		item.Routes = []Route{}
+		positions[item.ID] = len(models)
+		models = append(models, item)
+	}
+	if err := rows.Err(); err != nil {
+		rows.Close()
+		return nil, err
+	}
+	rows.Close()
+
+	routeRows, err := s.db.Query(ctx, `
+		SELECT r.id::text, r.model_id::text, r.provider_id::text, p.name, p.protocol,
+		       r.upstream_model, r.priority, r.weight, r.enabled
+		FROM model_routes r JOIN providers p ON p.id = r.provider_id
+		ORDER BY r.priority, r.created_at`)
+	if err != nil {
+		return nil, fmt.Errorf("query model routes: %w", err)
+	}
+	defer routeRows.Close()
+	for routeRows.Next() {
+		var route Route
+		var modelID string
+		if err := routeRows.Scan(&route.ID, &modelID, &route.ProviderID, &route.ProviderName, &route.Protocol, &route.UpstreamModel, &route.Priority, &route.Weight, &route.Enabled); err != nil {
+			return nil, fmt.Errorf("scan model route: %w", err)
+		}
+		if position, ok := positions[modelID]; ok {
+			models[position].Routes = append(models[position].Routes, route)
+		}
+	}
+	return models, routeRows.Err()
+}