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

package sqlite

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

	"inbox/internal/domain/role"
)

func (s *Store) GetRole(ctx context.Context, name string) (role.Definition, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT name, title, executor_kind, description, is_enabled, is_builtin, sort_order, created_at, updated_at
		FROM roles
		WHERE name = ?
	`, name)
	return scanRole(row)
}

func (s *Store) ListRoles(ctx context.Context) ([]role.Definition, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT name, title, executor_kind, description, is_enabled, is_builtin, sort_order, created_at, updated_at
		FROM roles
		ORDER BY sort_order, name
	`)
	if err != nil {
		return nil, fmt.Errorf("list roles: %w", err)
	}
	defer rows.Close()

	var out []role.Definition
	for rows.Next() {
		item, err := scanRole(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate roles: %w", err)
	}
	return out, nil
}

func (s *Store) UpsertRole(ctx context.Context, value role.Definition, changedBy string) (role.Definition, error) {
	_ = changedBy
	value = role.NormalizeDefinition(value)
	if err := value.Validate(); err != nil {
		return role.Definition{}, err
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return role.Definition{}, fmt.Errorf("begin upsert role: %w", err)
	}
	defer tx.Rollback()

	before, err := getRoleTx(ctx, tx, value.Name)
	if err != nil && !errors.Is(err, sql.ErrNoRows) {
		return role.Definition{}, err
	}
	now := s.now()
	if errors.Is(err, sql.ErrNoRows) {
		value.CreatedAt = now
	} else {
		value.CreatedAt = before.CreatedAt
	}
	value.UpdatedAt = now

	if _, err := tx.ExecContext(ctx, `
		INSERT INTO roles(name, title, executor_kind, description, is_enabled, is_builtin, sort_order, created_at, updated_at)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(name) DO UPDATE SET
			title = excluded.title,
			executor_kind = excluded.executor_kind,
			description = excluded.description,
			is_enabled = excluded.is_enabled,
			is_builtin = excluded.is_builtin,
			sort_order = excluded.sort_order,
			updated_at = excluded.updated_at
	`,
		value.Name,
		value.Title,
		string(value.ExecutorKind),
		value.Description,
		boolToInt(value.IsEnabled),
		boolToInt(value.IsBuiltin),
		value.SortOrder,
		value.CreatedAt,
		value.UpdatedAt,
	); err != nil {
		return role.Definition{}, fmt.Errorf("upsert role %q: %w", value.Name, err)
	}
	after, err := getRoleTx(ctx, tx, value.Name)
	if err != nil {
		return role.Definition{}, err
	}
	if err := tx.Commit(); err != nil {
		return role.Definition{}, fmt.Errorf("commit upsert role: %w", err)
	}
	return after, nil
}

func (s *Store) UpsertRolePrompt(ctx context.Context, value role.Prompt, changedBy string) (role.Prompt, error) {
	if err := value.Validate(); err != nil {
		return role.Prompt{}, err
	}
	return upsertVersionedConfig(ctx, s, value, changedBy, versionedConfigUpsertSpec[role.Prompt]{
		entityName: "role prompt",
		idKind:     "role-prompt",
		loadTx: func(ctx context.Context, tx *sql.Tx) (role.Prompt, error) {
			return getRolePromptTx(ctx, tx, value.RoleName, value.WorkspaceID, value.PromptKind)
		},
		current: func(item role.Prompt) versionedConfigCurrent {
			return versionedConfigCurrent{
				ID:        item.ID,
				Version:   item.Version,
				CreatedAt: item.CreatedAt,
			}
		},
		applyMetadata: func(item *role.Prompt, meta versionedConfigMetadata) {
			item.ID = meta.ID
			item.Version = meta.Version
			item.CreatedAt = meta.CreatedAt
			item.UpdatedAt = meta.UpdatedAt
		},
		writeTx: func(ctx context.Context, tx *sql.Tx, item role.Prompt) error {
			_, err := tx.ExecContext(ctx, `
				INSERT INTO role_prompts(id, role_name, workspace_id, prompt_kind, content_markdown, version, updated_by, created_at, updated_at)
				VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
				ON CONFLICT(id) DO UPDATE SET
					content_markdown = excluded.content_markdown,
					version = excluded.version,
					updated_by = excluded.updated_by,
					updated_at = excluded.updated_at
			`,
				item.ID,
				item.RoleName,
				nullableString(item.WorkspaceID),
				string(item.PromptKind),
				item.ContentMarkdown,
				item.Version,
				coalesceString(changedBy, item.UpdatedBy),
				item.CreatedAt,
				item.UpdatedAt,
			)
			return err
		},
	})
}

func (s *Store) ListRolePrompts(ctx context.Context, roleName string) ([]role.Prompt, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, role_name, workspace_id, prompt_kind, content_markdown, version, updated_by, created_at, updated_at
		FROM role_prompts
		WHERE role_name = ?
		ORDER BY prompt_kind, workspace_id, updated_at
	`, roleName)
	if err != nil {
		return nil, fmt.Errorf("list role prompts: %w", err)
	}
	defer rows.Close()

	var out []role.Prompt
	for rows.Next() {
		item, err := scanRolePrompt(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate role prompts: %w", err)
	}
	return out, nil
}

func (s *Store) UpsertRoleConfig(ctx context.Context, value role.Config, changedBy string) (role.Config, error) {
	_ = changedBy
	value = role.NormalizeConfig(value)
	if err := value.Validate(); err != nil {
		return role.Config{}, err
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return role.Config{}, fmt.Errorf("begin upsert role config: %w", err)
	}
	defer tx.Rollback()

	result, err := tx.ExecContext(ctx, `
		UPDATE roles
		SET config_toml = ?, auth_json = ?, updated_at = ?
		WHERE name = ?
	`, value.ConfigTOML, value.AuthJSON, s.now(), value.RoleName)
	if err != nil {
		return role.Config{}, fmt.Errorf("upsert role config: %w", err)
	}
	if err := ensureAffected(result, sql.ErrNoRows); err != nil {
		return role.Config{}, err
	}
	after, err := getRoleConfigTx(ctx, tx, value.RoleName)
	if err != nil {
		return role.Config{}, err
	}
	if err := tx.Commit(); err != nil {
		return role.Config{}, fmt.Errorf("commit upsert role config: %w", err)
	}
	return after, nil
}

func (s *Store) GetRoleConfig(ctx context.Context, roleName string) (role.Config, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT name, config_toml, auth_json
		FROM roles
		WHERE name = ?
	`, roleName)
	return scanRoleConfig(row)
}

func (s *Store) UpsertRoleSkillBinding(ctx context.Context, value role.SkillBinding, changedBy string) (role.SkillBinding, error) {
	if err := value.Validate(); err != nil {
		return role.SkillBinding{}, err
	}
	return upsertVersionedConfig(ctx, s, value, changedBy, versionedConfigUpsertSpec[role.SkillBinding]{
		entityName: "role skill binding",
		idKind:     "role-skill-binding",
		loadTx: func(ctx context.Context, tx *sql.Tx) (role.SkillBinding, error) {
			return getRoleSkillBindingTx(ctx, tx, value.RoleName, value.WorkspaceID, value.SkillID)
		},
		current: func(item role.SkillBinding) versionedConfigCurrent {
			return versionedConfigCurrent{
				ID:        item.ID,
				Version:   item.Version,
				CreatedAt: item.CreatedAt,
			}
		},
		applyMetadata: func(item *role.SkillBinding, meta versionedConfigMetadata) {
			item.ID = meta.ID
			item.Version = meta.Version
			item.CreatedAt = meta.CreatedAt
			item.UpdatedAt = meta.UpdatedAt
		},
		writeTx: func(ctx context.Context, tx *sql.Tx, item role.SkillBinding) error {
			configJSON, err := marshalJSONMapAny(item.Config)
			if err != nil {
				return err
			}

			_, err = tx.ExecContext(ctx, `
				INSERT INTO role_skill_bindings(id, role_name, workspace_id, skill_id, is_enabled, sort_order, config_json, version, updated_by, created_at, updated_at)
				VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				ON CONFLICT(id) DO UPDATE SET
					is_enabled = excluded.is_enabled,
					sort_order = excluded.sort_order,
					config_json = excluded.config_json,
					version = excluded.version,
					updated_by = excluded.updated_by,
					updated_at = excluded.updated_at
			`,
				item.ID,
				item.RoleName,
				nullableString(item.WorkspaceID),
				item.SkillID,
				boolToInt(item.IsEnabled),
				item.SortOrder,
				configJSON,
				item.Version,
				coalesceString(changedBy, item.UpdatedBy),
				item.CreatedAt,
				item.UpdatedAt,
			)
			return err
		},
	})
}

func (s *Store) ListRoleSkillBindings(ctx context.Context, roleName string) ([]role.SkillBinding, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, role_name, workspace_id, skill_id, is_enabled, sort_order, config_json, version, updated_by, created_at, updated_at
		FROM role_skill_bindings
		WHERE role_name = ?
		ORDER BY sort_order, skill_id, workspace_id
	`, roleName)
	if err != nil {
		return nil, fmt.Errorf("list role skill bindings: %w", err)
	}
	defer rows.Close()

	var out []role.SkillBinding
	for rows.Next() {
		item, err := scanRoleSkillBinding(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate role skill bindings: %w", err)
	}
	return out, nil
}

func getRoleTx(ctx context.Context, tx *sql.Tx, name string) (role.Definition, error) {
	row := tx.QueryRowContext(ctx, `
		SELECT name, title, executor_kind, description, is_enabled, is_builtin, sort_order, created_at, updated_at
		FROM roles
		WHERE name = ?
	`, name)
	return scanRole(row)
}

func getRolePromptTx(ctx context.Context, tx *sql.Tx, roleName, workspaceID string, kind role.PromptKind) (role.Prompt, error) {
	var row *sql.Row
	if workspaceID == "" {
		row = tx.QueryRowContext(ctx, `
			SELECT id, role_name, workspace_id, prompt_kind, content_markdown, version, updated_by, created_at, updated_at
			FROM role_prompts
			WHERE role_name = ? AND prompt_kind = ? AND workspace_id IS NULL
		`, roleName, string(kind))
	} else {
		row = tx.QueryRowContext(ctx, `
			SELECT id, role_name, workspace_id, prompt_kind, content_markdown, version, updated_by, created_at, updated_at
			FROM role_prompts
			WHERE role_name = ? AND prompt_kind = ? AND workspace_id = ?
		`, roleName, string(kind), workspaceID)
	}
	return scanRolePrompt(row)
}

func getRoleConfigTx(ctx context.Context, tx *sql.Tx, roleName string) (role.Config, error) {
	row := tx.QueryRowContext(ctx, `
		SELECT name, config_toml, auth_json
		FROM roles
		WHERE name = ?
	`, roleName)
	return scanRoleConfig(row)
}

func getRoleSkillBindingTx(ctx context.Context, tx *sql.Tx, roleName, workspaceID, skillID string) (role.SkillBinding, error) {
	var row *sql.Row
	if workspaceID == "" {
		row = tx.QueryRowContext(ctx, `
			SELECT id, role_name, workspace_id, skill_id, is_enabled, sort_order, config_json, version, updated_by, created_at, updated_at
			FROM role_skill_bindings
			WHERE role_name = ? AND skill_id = ? AND workspace_id IS NULL
		`, roleName, skillID)
	} else {
		row = tx.QueryRowContext(ctx, `
			SELECT id, role_name, workspace_id, skill_id, is_enabled, sort_order, config_json, version, updated_by, created_at, updated_at
			FROM role_skill_bindings
			WHERE role_name = ? AND skill_id = ? AND workspace_id = ?
		`, roleName, skillID, workspaceID)
	}
	return scanRoleSkillBinding(row)
}

func scanRole(s scanner) (role.Definition, error) {
	var item role.Definition
	var executorKind string
	if err := s.Scan(&item.Name, &item.Title, &executorKind, &item.Description, &item.IsEnabled, &item.IsBuiltin, &item.SortOrder, &item.CreatedAt, &item.UpdatedAt); err != nil {
		return role.Definition{}, err
	}
	item.ExecutorKind = role.ExecutorKind(executorKind)
	item = role.NormalizeDefinition(item)
	return item, nil
}

func scanRolePrompt(s scanner) (role.Prompt, error) {
	var item role.Prompt
	var workspaceID sql.NullString
	var kind string
	if err := s.Scan(&item.ID, &item.RoleName, &workspaceID, &kind, &item.ContentMarkdown, &item.Version, &item.UpdatedBy, &item.CreatedAt, &item.UpdatedAt); err != nil {
		return role.Prompt{}, err
	}
	if workspaceID.Valid {
		item.WorkspaceID = workspaceID.String
	}
	item.PromptKind = role.PromptKind(kind)
	return item, nil
}

func scanRoleConfig(s scanner) (role.Config, error) {
	var item role.Config
	if err := s.Scan(&item.RoleName, &item.ConfigTOML, &item.AuthJSON); err != nil {
		return role.Config{}, err
	}
	return role.NormalizeConfig(item), nil
}

func scanRoleSkillBinding(s scanner) (role.SkillBinding, error) {
	var item role.SkillBinding
	var workspaceID sql.NullString
	var configJSON string
	if err := s.Scan(&item.ID, &item.RoleName, &workspaceID, &item.SkillID, &item.IsEnabled, &item.SortOrder, &configJSON, &item.Version, &item.UpdatedBy, &item.CreatedAt, &item.UpdatedAt); err != nil {
		return role.SkillBinding{}, err
	}
	if workspaceID.Valid {
		item.WorkspaceID = workspaceID.String
	}
	if err := unmarshalJSON(configJSON, &item.Config); err != nil {
		return role.SkillBinding{}, fmt.Errorf("decode role skill binding config: %w", err)
	}
	return item, nil
}