ai-workflow/inbox/internal/store/sqlite/projects_workspaces.go

package sqlite

import (
	"context"
	"database/sql"
	"fmt"

	"inbox/internal/domain/workspace"
)

func (s *Store) CreateProject(ctx context.Context, value workspace.Project) (workspace.Project, error) {
	if err := value.Validate(); err != nil {
		return workspace.Project{}, err
	}
	if value.ID == "" {
		id, err := s.newID("project")
		if err != nil {
			return workspace.Project{}, err
		}
		value.ID = id
	}
	now := s.now()
	value.CreatedAt = coalesceString(value.CreatedAt, now)
	value.UpdatedAt = now

	if _, err := s.db.ExecContext(ctx, `
		INSERT INTO projects(id, slug, name, root_path, default_branch, status, created_at, updated_at)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?)
	`, value.ID, value.Slug, value.Name, value.RootPath, value.DefaultBranch, value.Status, value.CreatedAt, value.UpdatedAt); err != nil {
		return workspace.Project{}, fmt.Errorf("create project: %w", err)
	}
	return value, nil
}

func (s *Store) ListProjects(ctx context.Context) ([]workspace.Project, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, slug, name, root_path, default_branch, status, created_at, updated_at
		FROM projects
		ORDER BY created_at, slug
	`)
	if err != nil {
		return nil, fmt.Errorf("list projects: %w", err)
	}
	defer rows.Close()

	var out []workspace.Project
	for rows.Next() {
		item, err := scanProject(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate projects: %w", err)
	}
	return out, nil
}

func (s *Store) GetProject(ctx context.Context, projectID string) (workspace.Project, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, slug, name, root_path, default_branch, status, created_at, updated_at
		FROM projects
		WHERE id = ?
	`, projectID)
	return scanProject(row)
}

func (s *Store) GetProjectByRootPath(ctx context.Context, rootPath string) (workspace.Project, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, slug, name, root_path, default_branch, status, created_at, updated_at
		FROM projects
		WHERE root_path = ?
		ORDER BY created_at DESC
		LIMIT 1
	`, rootPath)
	return scanProject(row)
}

func (s *Store) GetProjectBySlug(ctx context.Context, slug string) (workspace.Project, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, slug, name, root_path, default_branch, status, created_at, updated_at
		FROM projects
		WHERE slug = ?
		ORDER BY created_at DESC
		LIMIT 1
	`, slug)
	return scanProject(row)
}

func (s *Store) CreateWorkspace(ctx context.Context, value workspace.Workspace) (workspace.Workspace, error) {
	value.ProvisionState = coalesceString(value.ProvisionState, "pending")
	if err := value.Validate(); err != nil {
		return workspace.Workspace{}, err
	}
	if value.ID == "" {
		id, err := s.newID("workspace")
		if err != nil {
			return workspace.Workspace{}, err
		}
		value.ID = id
	}
	now := s.now()
	value.CreatedAt = coalesceString(value.CreatedAt, now)
	value.UpdatedAt = now

	if _, err := s.db.ExecContext(ctx, `
		INSERT INTO workspaces(id, project_id, slug, name, root_path, base_branch, worktree_branch, runtime_backend, status, provision_state, provision_error, last_provisioned_at, created_at, updated_at)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`, value.ID, value.ProjectID, value.Slug, value.Name, value.RootPath, value.BaseBranch, value.WorktreeBranch, value.RuntimeBackend, value.Status, value.ProvisionState, value.ProvisionError, nullableString(value.LastProvisionedAt), value.CreatedAt, value.UpdatedAt); err != nil {
		return workspace.Workspace{}, fmt.Errorf("create workspace: %w", err)
	}
	return value, nil
}

func (s *Store) ListWorkspaces(ctx context.Context, projectID string) ([]workspace.Workspace, error) {
	var (
		rows *sql.Rows
		err  error
	)
	if projectID == "" {
		rows, err = s.db.QueryContext(ctx, `
			SELECT id, project_id, slug, name, root_path, base_branch, worktree_branch, runtime_backend, status, provision_state, provision_error, last_provisioned_at, created_at, updated_at
			FROM workspaces
			ORDER BY created_at, slug
		`)
	} else {
		rows, err = s.db.QueryContext(ctx, `
			SELECT id, project_id, slug, name, root_path, base_branch, worktree_branch, runtime_backend, status, provision_state, provision_error, last_provisioned_at, created_at, updated_at
			FROM workspaces
			WHERE project_id = ?
			ORDER BY created_at, slug
		`, projectID)
	}
	if err != nil {
		return nil, fmt.Errorf("list workspaces: %w", err)
	}
	defer rows.Close()

	var out []workspace.Workspace
	for rows.Next() {
		item, err := scanWorkspace(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspaces: %w", err)
	}
	return out, nil
}

func (s *Store) GetWorkspace(ctx context.Context, workspaceID string) (workspace.Workspace, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, project_id, slug, name, root_path, base_branch, worktree_branch, runtime_backend, status, provision_state, provision_error, last_provisioned_at, created_at, updated_at
		FROM workspaces
		WHERE id = ?
	`, workspaceID)
	return scanWorkspace(row)
}

func (s *Store) GetWorkspaceBySlugOrName(ctx context.Context, value string) (workspace.Workspace, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, project_id, slug, name, root_path, base_branch, worktree_branch, runtime_backend, status, provision_state, provision_error, last_provisioned_at, created_at, updated_at
		FROM workspaces
		WHERE slug = ? OR name = ?
		ORDER BY CASE WHEN slug = ? THEN 0 ELSE 1 END, created_at DESC
		LIMIT 1
	`, value, value, value)
	return scanWorkspace(row)
}

func (s *Store) GetWorkspaceByProjectAndSlug(ctx context.Context, projectID, slug string) (workspace.Workspace, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, project_id, slug, name, root_path, base_branch, worktree_branch, runtime_backend, status, provision_state, provision_error, last_provisioned_at, created_at, updated_at
		FROM workspaces
		WHERE project_id = ? AND slug = ?
		ORDER BY created_at DESC
		LIMIT 1
	`, projectID, slug)
	return scanWorkspace(row)
}

func (s *Store) UpdateProjectDefaultBranch(ctx context.Context, projectID, defaultBranch string) error {
	if _, err := s.db.ExecContext(ctx, `
		UPDATE projects
		SET default_branch = ?, updated_at = ?
		WHERE id = ?
	`, defaultBranch, s.now(), projectID); err != nil {
		return fmt.Errorf("update project default branch: %w", err)
	}
	return nil
}

func (s *Store) UpdateWorkspace(ctx context.Context, value workspace.Workspace) error {
	value.UpdatedAt = s.now()
	if _, err := s.db.ExecContext(ctx, `
		UPDATE workspaces
		SET root_path = ?,
		    base_branch = ?,
		    worktree_branch = ?,
		    runtime_backend = ?,
		    status = ?,
		    provision_state = ?,
		    provision_error = ?,
		    last_provisioned_at = ?,
		    updated_at = ?
		WHERE id = ?
	`, value.RootPath, value.BaseBranch, value.WorktreeBranch, value.RuntimeBackend, value.Status, value.ProvisionState, value.ProvisionError, nullableString(value.LastProvisionedAt), value.UpdatedAt, value.ID); err != nil {
		return fmt.Errorf("update workspace: %w", err)
	}
	return nil
}

func scanProject(s scanner) (workspace.Project, error) {
	var item workspace.Project
	if err := s.Scan(&item.ID, &item.Slug, &item.Name, &item.RootPath, &item.DefaultBranch, &item.Status, &item.CreatedAt, &item.UpdatedAt); err != nil {
		return workspace.Project{}, err
	}
	return item, nil
}

func scanWorkspace(s scanner) (workspace.Workspace, error) {
	var item workspace.Workspace
	var lastProvisionedAt sql.NullString
	if err := s.Scan(&item.ID, &item.ProjectID, &item.Slug, &item.Name, &item.RootPath, &item.BaseBranch, &item.WorktreeBranch, &item.RuntimeBackend, &item.Status, &item.ProvisionState, &item.ProvisionError, &lastProvisionedAt, &item.CreatedAt, &item.UpdatedAt); err != nil {
		return workspace.Workspace{}, err
	}
	if lastProvisionedAt.Valid {
		item.LastProvisionedAt = lastProvisionedAt.String
	}
	return item, nil
}