rony-chat-bot/internal/portfolio/conversations.go

package portfolio

import (
	"context"
	"crypto/rand"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"time"
)

// Conversation is a thread of messages between one user and the bot.
type Conversation struct {
	ID        string    `json:"id"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
	Messages  []Message `json:"messages"`
}

// Message is a single turn in a conversation.
type Message struct {
	ID        int64     `json:"id"`
	Role      string    `json:"role"`   // "user" | "assistant" | "system"
	Content   string    `json:"content"`
	Sources   []string  `json:"sources,omitempty"`
	CreatedAt time.Time `json:"created_at"`
}

// ConversationSummary is the lightweight listing shape (no messages).
type ConversationSummary struct {
	ID        string    `json:"id"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
	Preview   string    `json:"preview"` // first ~80 chars of the first user message
}

const conversationSchema = `
CREATE TABLE IF NOT EXISTS conversations (
    id         TEXT PRIMARY KEY,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS messages (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    conversation_id TEXT    NOT NULL,
    role            TEXT    NOT NULL,
    content         TEXT    NOT NULL,
    sources         TEXT,                -- JSON array, nullable
    created_at      INTEGER NOT NULL,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_messages_conv ON messages(conversation_id, id);
`

// ErrConversationNotFound is returned when a conversation ID doesn't exist.
var ErrConversationNotFound = errors.New("conversation not found")

// CreateConversation makes a new empty conversation and returns its ID.
// The ID is a UUID-ish hex string (crypto/rand based) — unguessable in
// practice, so it can serve as the access token for the GET endpoint.
func (s *Store) CreateConversation(ctx context.Context) (string, error) {
	id, err := newConvID()
	if err != nil {
		return "", err
	}
	now := time.Now().Unix()
	if _, err := s.db.ExecContext(ctx,
		`INSERT INTO conversations (id, created_at, updated_at) VALUES (?, ?, ?)`,
		id, now, now); err != nil {
		return "", fmt.Errorf("create conversation: %w", err)
	}
	return id, nil
}

// TouchConversation updates the updated_at timestamp. Called after every
// message so ListConversations can sort by recency.
func (s *Store) TouchConversation(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx,
		`UPDATE conversations SET updated_at = ? WHERE id = ?`,
		time.Now().Unix(), id)
	return err
}

// SaveMessage appends a message to a conversation and bumps updated_at.
// The conversation must exist (use CreateConversation first or pass an
// existing ID). Sources may be nil.
func (s *Store) SaveMessage(ctx context.Context, convID, role, content string, sources []string) error {
	if convID == "" {
		return errors.New("convID required")
	}
	var sourcesJSON sql.NullString
	if len(sources) > 0 {
		b, err := json.Marshal(sources)
		if err != nil {
			return fmt.Errorf("marshal sources: %w", err)
		}
		sourcesJSON = sql.NullString{String: string(b), Valid: true}
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.ExecContext(ctx,
		`INSERT INTO messages (conversation_id, role, content, sources, created_at) VALUES (?, ?, ?, ?, ?)`,
		convID, role, content, sourcesJSON, time.Now().Unix()); err != nil {
		return fmt.Errorf("insert message: %w", err)
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE conversations SET updated_at = ? WHERE id = ?`,
		time.Now().Unix(), convID); err != nil {
		return fmt.Errorf("touch conversation: %w", err)
	}
	return tx.Commit()
}

// GetConversation returns a conversation with all its messages in
// chronological order. Returns ErrConversationNotFound if the ID is unknown.
func (s *Store) GetConversation(ctx context.Context, id string) (*Conversation, error) {
	var c Conversation
	var createdUnix, updatedUnix int64
	err := s.db.QueryRowContext(ctx,
		`SELECT id, created_at, updated_at FROM conversations WHERE id = ?`, id,
	).Scan(&c.ID, &createdUnix, &updatedUnix)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrConversationNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("select conversation: %w", err)
	}
	c.CreatedAt = time.Unix(createdUnix, 0).UTC()
	c.UpdatedAt = time.Unix(updatedUnix, 0).UTC()

	rows, err := s.db.QueryContext(ctx,
		`SELECT id, role, content, sources, created_at FROM messages WHERE conversation_id = ? ORDER BY id ASC`, id)
	if err != nil {
		return nil, fmt.Errorf("select messages: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var m Message
		var sourcesStr sql.NullString
		var createdUnix int64
		if err := rows.Scan(&m.ID, &m.Role, &m.Content, &sourcesStr, &createdUnix); err != nil {
			return nil, err
		}
		if sourcesStr.Valid {
			if err := json.Unmarshal([]byte(sourcesStr.String), &m.Sources); err != nil {
				return nil, fmt.Errorf("unmarshal sources: %w", err)
			}
		}
		m.CreatedAt = time.Unix(createdUnix, 0).UTC()
		c.Messages = append(c.Messages, m)
	}
	return &c, rows.Err()
}

// ListConversations returns the most recent conversations, newest first.
// Useful for a "show my chats" UI. Limit caps the result; pass 0 for default
// (50). Each entry includes a short preview from the first user message.
func (s *Store) ListConversations(ctx context.Context, limit int) ([]ConversationSummary, error) {
	if limit <= 0 {
		limit = 50
	}
	rows, err := s.db.QueryContext(ctx, `
		SELECT c.id, c.created_at, c.updated_at,
		       (SELECT content FROM messages m
		         WHERE m.conversation_id = c.id AND m.role = 'user'
		         ORDER BY m.id ASC LIMIT 1) AS preview
		FROM conversations c
		ORDER BY c.updated_at DESC
		LIMIT ?`, limit)
	if err != nil {
		return nil, fmt.Errorf("list conversations: %w", err)
	}
	defer rows.Close()

	var out []ConversationSummary
	for rows.Next() {
		var cs ConversationSummary
		var createdUnix, updatedUnix int64
		var preview sql.NullString
		if err := rows.Scan(&cs.ID, &createdUnix, &updatedUnix, &preview); err != nil {
			return nil, err
		}
		cs.CreatedAt = time.Unix(createdUnix, 0).UTC()
		cs.UpdatedAt = time.Unix(updatedUnix, 0).UTC()
		if preview.Valid {
			cs.Preview = truncateRunes(preview.String, 80)
		}
		out = append(out, cs)
	}
	return out, rows.Err()
}

// DeleteConversation removes a conversation and all its messages (cascade).
// Returns ErrConversationNotFound if the ID didn't exist.
func (s *Store) DeleteConversation(ctx context.Context, id string) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM conversations WHERE id = ?`, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return ErrConversationNotFound
	}
	return nil
}

func truncateRunes(s string, n int) string {
	if len([]rune(s)) <= n {
		return s
	}
	r := []rune(s)
	return string(r[:n]) + "…"
}

// newConvID returns a 16-byte random hex string (32 chars). Unguessable
// in practice; doubles as the access token for GET /api/conversations/:id.
func newConvID() (string, error) {
	var b [16]byte
	if _, err := rand.Read(b[:]); err != nil {
		return "", fmt.Errorf("rand: %w", err)
	}
	return hex.EncodeToString(b[:]), nil
}