ccrm/server/internal/db/sessions.go

package db

import (
	"database/sql"
	"time"
)

// Session represents a session row in the database.
type Session struct {
	ID              string     `json:"id"`
	MachineID       string     `json:"machine_id"`
	Name            string     `json:"name"`
	TmuxSession     string     `json:"tmux_session"`
	ProjectPath     string     `json:"project_path"`
	ClaudeSessionID string     `json:"claude_session_id"`
	Status          string     `json:"status"`
	IsWorktree      bool       `json:"is_worktree"`
	WorktreeBranch  string     `json:"worktree_branch"`
	CreatedAt       time.Time  `json:"created_at"`
	EndedAt         *time.Time `json:"ended_at,omitempty"`
}

// UpsertSession creates or updates a session.
func (db *DB) UpsertSession(s *Session) error {
	_, err := db.Exec(`
		INSERT INTO sessions (id, machine_id, name, tmux_session, project_path, claude_session_id, status, is_worktree, worktree_branch, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(machine_id, name) DO UPDATE SET
			status = excluded.status,
			claude_session_id = COALESCE(NULLIF(excluded.claude_session_id, ''), sessions.claude_session_id),
			tmux_session = excluded.tmux_session,
			project_path = excluded.project_path
	`, s.ID, s.MachineID, s.Name, s.TmuxSession, s.ProjectPath, s.ClaudeSessionID, s.Status, s.IsWorktree, s.WorktreeBranch, s.CreatedAt)
	return err
}

// UpdateSessionStatus updates a session's status.
func (db *DB) UpdateSessionStatus(name, machineID, status string) error {
	query := "UPDATE sessions SET status = ?"
	args := []any{status}

	if status == "stopped" {
		query += ", ended_at = ?"
		args = append(args, time.Now().UTC())
	}

	query += " WHERE name = ? AND machine_id = ?"
	args = append(args, name, machineID)

	_, err := db.Exec(query, args...)
	return err
}

// GetSession returns a session by ID.
func (db *DB) GetSession(id string) (*Session, error) {
	s := &Session{}
	var endedAt sql.NullTime
	err := db.QueryRow(`
		SELECT id, machine_id, name, tmux_session, project_path, claude_session_id, status, is_worktree, worktree_branch, created_at, ended_at
		FROM sessions WHERE id = ?
	`, id).Scan(&s.ID, &s.MachineID, &s.Name, &s.TmuxSession, &s.ProjectPath, &s.ClaudeSessionID, &s.Status, &s.IsWorktree, &s.WorktreeBranch, &s.CreatedAt, &endedAt)
	if err != nil {
		return nil, err
	}
	if endedAt.Valid {
		s.EndedAt = &endedAt.Time
	}
	return s, nil
}

// GetSessionByName returns a session by name and machine ID.
func (db *DB) GetSessionByName(name, machineID string) (*Session, error) {
	s := &Session{}
	var endedAt sql.NullTime
	err := db.QueryRow(`
		SELECT id, machine_id, name, tmux_session, project_path, claude_session_id, status, is_worktree, worktree_branch, created_at, ended_at
		FROM sessions WHERE name = ? AND machine_id = ?
	`, name, machineID).Scan(&s.ID, &s.MachineID, &s.Name, &s.TmuxSession, &s.ProjectPath, &s.ClaudeSessionID, &s.Status, &s.IsWorktree, &s.WorktreeBranch, &s.CreatedAt, &endedAt)
	if err != nil {
		return nil, err
	}
	if endedAt.Valid {
		s.EndedAt = &endedAt.Time
	}
	return s, nil
}

// ReconcileSessions marks any active sessions for a machine as stopped
// if they are not in the provided list of live session names. This is
// called on heartbeat to clean up dangling sessions.
func (db *DB) ReconcileSessions(machineID string, liveNames []string) error {
	if len(liveNames) == 0 {
		// No live sessions — stop all active sessions for this machine
		_, err := db.Exec(`
			UPDATE sessions SET status = 'stopped', ended_at = ?
			WHERE machine_id = ? AND status != 'stopped'
		`, time.Now().UTC(), machineID)
		return err
	}

	// Build placeholders for the IN clause
	placeholders := ""
	args := []any{time.Now().UTC(), machineID}
	for i, name := range liveNames {
		if i > 0 {
			placeholders += ","
		}
		placeholders += "?"
		args = append(args, name)
	}

	_, err := db.Exec(`
		UPDATE sessions SET status = 'stopped', ended_at = ?
		WHERE machine_id = ? AND status != 'stopped' AND name NOT IN (`+placeholders+`)
	`, args...)
	return err
}

// ListSessions returns all sessions, optionally filtered by status.
func (db *DB) ListSessions(activeOnly bool) ([]*Session, error) {
	query := `SELECT id, machine_id, name, tmux_session, project_path, claude_session_id, status, is_worktree, worktree_branch, created_at, ended_at FROM sessions`
	if activeOnly {
		query += ` WHERE status != 'stopped'`
	}
	query += ` ORDER BY created_at DESC`

	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var sessions []*Session
	for rows.Next() {
		s := &Session{}
		var endedAt sql.NullTime
		if err := rows.Scan(&s.ID, &s.MachineID, &s.Name, &s.TmuxSession, &s.ProjectPath, &s.ClaudeSessionID, &s.Status, &s.IsWorktree, &s.WorktreeBranch, &s.CreatedAt, &endedAt); err != nil {
			return nil, err
		}
		if endedAt.Valid {
			s.EndedAt = &endedAt.Time
		}
		sessions = append(sessions, s)
	}
	return sessions, nil
}