bri-sandbox-development-platform/control-plane/internal/store/sandboxes.go

package store

import (
	"database/sql"
	"errors"
	"time"
)

// Sandbox is a named deployment context. It owns a single gateway branch
// and a set of microservice branches, each of which can be marked
// "use_ocp" (i.e. leave the routing alone) or pointed at a local
// stand-in.
type Sandbox struct {
	ID              string           `json:"id"`
	Name            string           `json:"name"`
	GatewayBranch   string           `json:"gatewayBranch,omitempty"`
	GatewayEnvID    string           `json:"gatewayEnvId,omitempty"`
	GatewayHostPort int              `json:"gatewayHostPort,omitempty"`
	CreatedAt       int64            `json:"createdAt"`
	UpdatedAt       int64            `json:"updatedAt"`
	Services        []SandboxService `json:"services"`
}

// SandboxService is one microservice entry inside a sandbox.
type SandboxService struct {
	ID        string `json:"id"`
	SandboxID string `json:"sandboxId"`
	Repo      string `json:"repo"`
	Branch    string `json:"branch,omitempty"`
	EnvID     string `json:"envId,omitempty"`
	HostPort  int    `json:"hostPort,omitempty"`
	UseOCP    bool   `json:"useOcp"`
}

var ErrNotFound = errors.New("not found")

// CreateSandbox persists a sandbox and its services. The caller assigns
// IDs; this function does not generate them. The whole insert is in one
// transaction.
func (s *Store) CreateSandbox(sb Sandbox) error {
	now := time.Now().UnixMilli()
	if sb.CreatedAt == 0 {
		sb.CreatedAt = now
	}
	sb.UpdatedAt = now
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.Exec(
		`INSERT INTO sandboxes(id, name, gateway_branch, gateway_env_id, gateway_host_port, created_at, updated_at)
		 VALUES(?,?,?,?,?,?,?)`,
		sb.ID, sb.Name, sb.GatewayBranch, nullString(sb.GatewayEnvID), nullInt(sb.GatewayHostPort),
		sb.CreatedAt, sb.UpdatedAt,
	); err != nil {
		return err
	}
	for i := range sb.Services {
		svc := &sb.Services[i]
		if svc.ID == "" {
			svc.ID = sb.ID + "-" + svc.Repo
			svc.SandboxID = sb.ID
		}
		if err := insertSandboxService(tx, *svc); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// UpdateSandbox replaces the sandbox's mutable fields. Services are
// replaced wholesale — the caller passes the full desired list.
func (s *Store) UpdateSandbox(sb Sandbox) error {
	now := time.Now().UnixMilli()
	sb.UpdatedAt = now
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	res, err := tx.Exec(
		`UPDATE sandboxes SET name=?, gateway_branch=?, gateway_env_id=?, gateway_host_port=?, updated_at=? WHERE id=?`,
		sb.Name, sb.GatewayBranch, nullString(sb.GatewayEnvID), nullInt(sb.GatewayHostPort),
		sb.UpdatedAt, sb.ID,
	)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return ErrNotFound
	}
	if _, err := tx.Exec(`DELETE FROM sandbox_services WHERE sandbox_id=?`, sb.ID); err != nil {
		return err
	}
	for i := range sb.Services {
		svc := &sb.Services[i]
		svc.SandboxID = sb.ID
		if svc.ID == "" {
			svc.ID = sb.ID + "-" + svc.Repo
		}
		if err := insertSandboxService(tx, *svc); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// GetSandbox returns one sandbox with its services. ErrNotFound if missing.
func (s *Store) GetSandbox(id string) (*Sandbox, error) {
	row := s.db.QueryRow(
		`SELECT id, name, COALESCE(gateway_branch,''), COALESCE(gateway_env_id,''),
		        COALESCE(gateway_host_port,0), created_at, updated_at
		 FROM sandboxes WHERE id=?`, id)
	var sb Sandbox
	err := row.Scan(&sb.ID, &sb.Name, &sb.GatewayBranch, &sb.GatewayEnvID,
		&sb.GatewayHostPort, &sb.CreatedAt, &sb.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	if err := s.loadSandboxServices(&sb); err != nil {
		return nil, err
	}
	return &sb, nil
}

// ListSandboxes returns all sandboxes, newest first.
func (s *Store) ListSandboxes() ([]Sandbox, error) {
	rows, err := s.db.Query(
		`SELECT id, name, COALESCE(gateway_branch,''), COALESCE(gateway_env_id,''),
		        COALESCE(gateway_host_port,0), created_at, updated_at
		 FROM sandboxes ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Sandbox
	for rows.Next() {
		var sb Sandbox
		if err := rows.Scan(&sb.ID, &sb.Name, &sb.GatewayBranch, &sb.GatewayEnvID,
			&sb.GatewayHostPort, &sb.CreatedAt, &sb.UpdatedAt); err != nil {
			return nil, err
		}
		out = append(out, sb)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	for i := range out {
		if err := s.loadSandboxServices(&out[i]); err != nil {
			return nil, err
		}
	}
	return out, nil
}

// DeleteSandbox removes the sandbox row and its services. Active
// deployments are NOT stopped — the caller is responsible for that.
func (s *Store) DeleteSandbox(id string) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.Exec(`DELETE FROM sandbox_services WHERE sandbox_id=?`, id); err != nil {
		return err
	}
	res, err := tx.Exec(`DELETE FROM sandboxes WHERE id=?`, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return ErrNotFound
	}
	return tx.Commit()
}

func (s *Store) loadSandboxServices(sb *Sandbox) error {
	rows, err := s.db.Query(
		`SELECT id, sandbox_id, repo, COALESCE(branch,''), COALESCE(env_id,''),
		        COALESCE(host_port,0), COALESCE(use_ocp,0)
		 FROM sandbox_services WHERE sandbox_id=? ORDER BY repo`, sb.ID)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var svc SandboxService
		var useOCP int
		if err := rows.Scan(&svc.ID, &svc.SandboxID, &svc.Repo, &svc.Branch,
			&svc.EnvID, &svc.HostPort, &useOCP); err != nil {
			return err
		}
		svc.UseOCP = useOCP == 1
		sb.Services = append(sb.Services, svc)
	}
	return rows.Err()
}

func insertSandboxService(tx *sql.Tx, svc SandboxService) error {
	_, err := tx.Exec(
		`INSERT INTO sandbox_services(id, sandbox_id, repo, branch, env_id, host_port, use_ocp)
		 VALUES(?,?,?,?,?,?,?)`,
		svc.ID, svc.SandboxID, svc.Repo, svc.Branch, nullString(svc.EnvID),
		nullInt(svc.HostPort), boolInt(svc.UseOCP),
	)
	return err
}

func nullString(s string) interface{} {
	if s == "" {
		return nil
	}
	return s
}

func nullInt(n int) interface{} {
	if n == 0 {
		return nil
	}
	return n
}

func boolInt(b bool) int {
	if b {
		return 1
	}
	return 0
}