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

package sqlite

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

	"inbox/internal/domain/topic"
)

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

	if _, err := s.db.ExecContext(ctx, `
		INSERT INTO topics(id, workspace_id, slug, title, space, status, summary, created_at, updated_at, closed_at)
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`, value.ID, value.WorkspaceID, value.Slug, value.Title, string(value.Space), value.Status, value.Summary, value.CreatedAt, value.UpdatedAt, nullableString(value.ClosedAt)); err != nil {
		return topic.Record{}, fmt.Errorf("create topic: %w", err)
	}
	return value, nil
}

func (s *Store) ListTopics(ctx context.Context, workspaceID string) ([]topic.Record, error) {
	var (
		rows *sql.Rows
		err  error
	)
	if strings.TrimSpace(workspaceID) == "" {
		rows, err = s.db.QueryContext(ctx, `
			SELECT id, workspace_id, slug, title, space, status, summary, created_at, updated_at, closed_at
			FROM topics
			ORDER BY updated_at DESC, created_at DESC, slug
		`)
	} else {
		rows, err = s.db.QueryContext(ctx, `
			SELECT id, workspace_id, slug, title, space, status, summary, created_at, updated_at, closed_at
			FROM topics
			WHERE workspace_id = ?
			ORDER BY updated_at DESC, created_at DESC, slug
		`, workspaceID)
	}
	if err != nil {
		return nil, fmt.Errorf("list topics: %w", err)
	}
	defer rows.Close()

	var out []topic.Record
	for rows.Next() {
		item, err := scanTopic(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate topics: %w", err)
	}
	return out, nil
}

func (s *Store) ListTopicsBySpace(ctx context.Context, workspaceID string, space topic.Space) ([]topic.Record, error) {
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, workspace_id, slug, title, space, status, summary, created_at, updated_at, closed_at
		FROM topics
		WHERE workspace_id = ? AND space = ?
		ORDER BY updated_at DESC, created_at DESC, slug
	`, workspaceID, string(space))
	if err != nil {
		return nil, fmt.Errorf("list topics by space: %w", err)
	}
	defer rows.Close()

	var out []topic.Record
	for rows.Next() {
		item, err := scanTopic(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate topics by space: %w", err)
	}
	return out, nil
}

func (s *Store) GetTopic(ctx context.Context, topicID string) (topic.Record, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, workspace_id, slug, title, space, status, summary, created_at, updated_at, closed_at
		FROM topics
		WHERE id = ?
	`, topicID)
	return scanTopic(row)
}

func (s *Store) GetTopicBySlugOrTitle(ctx context.Context, workspaceID, value string, spaces ...topic.Space) (topic.Record, error) {
	args := []any{workspaceID, value, value}
	query := `
		SELECT id, workspace_id, slug, title, space, status, summary, created_at, updated_at, closed_at
		FROM topics
		WHERE workspace_id = ? AND (slug = ? OR title = ?)
	`
	if len(spaces) > 0 {
		query += " AND space IN (" + placeholders(len(spaces)) + ")"
		for _, space := range spaces {
			args = append(args, string(space))
		}
	}
	query += `
		ORDER BY CASE WHEN slug = ? THEN 0 ELSE 1 END, updated_at DESC, created_at DESC
		LIMIT 1
	`
	args = append(args, value)
	row := s.db.QueryRowContext(ctx, query, args...)
	return scanTopic(row)
}

func (s *Store) UpdateTopic(ctx context.Context, value topic.Record) (topic.Record, error) {
	if value.ID == "" {
		return topic.Record{}, fmt.Errorf("topic id is required")
	}
	current, err := s.GetTopic(ctx, value.ID)
	if err != nil {
		return topic.Record{}, err
	}
	value.CreatedAt = current.CreatedAt
	value.UpdatedAt = s.now()
	if err := value.Validate(); err != nil {
		return topic.Record{}, err
	}
	result, err := s.db.ExecContext(ctx, `
		UPDATE topics
		SET workspace_id = ?, slug = ?, title = ?, space = ?, status = ?, summary = ?, updated_at = ?, closed_at = ?
		WHERE id = ?
	`, value.WorkspaceID, value.Slug, value.Title, string(value.Space), value.Status, value.Summary, value.UpdatedAt, nullableString(value.ClosedAt), value.ID)
	if err != nil {
		return topic.Record{}, fmt.Errorf("update topic: %w", err)
	}
	if err := ensureAffected(result, sql.ErrNoRows); err != nil {
		return topic.Record{}, err
	}
	return s.GetTopic(ctx, value.ID)
}

func (s *Store) DeleteTopic(ctx context.Context, topicID string) error {
	result, err := s.db.ExecContext(ctx, `DELETE FROM topics WHERE id = ?`, topicID)
	if err != nil {
		return fmt.Errorf("delete topic: %w", err)
	}
	return ensureAffected(result, sql.ErrNoRows)
}

func scanTopic(s scanner) (topic.Record, error) {
	var item topic.Record
	var space string
	var closedAt sql.NullString
	if err := s.Scan(&item.ID, &item.WorkspaceID, &item.Slug, &item.Title, &space, &item.Status, &item.Summary, &item.CreatedAt, &item.UpdatedAt, &closedAt); err != nil {
		return topic.Record{}, err
	}
	item.Space = topic.Space(space)
	if closedAt.Valid {
		item.ClosedAt = closedAt.String
	}
	return item, nil
}