path: root/internal/store/sqlite.go

package store

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"sort"
	"time"

	_ "github.com/mattn/go-sqlite3"

	"task-dashboard/internal/config"
	"task-dashboard/internal/models"
)

// Cache key constants
const (
	CacheKeyTodoistTasks   = "todoist_tasks"
	CacheKeyTrelloBoards   = "trello_boards"
	CacheKeyPlanToEatMeals = "plantoeat_meals"
)

type Store struct {
	db           *sql.DB
	migrationDir string
}

// New creates a new Store instance and runs migrations
func New(dbPath, migrationDir string) (*Store, error) {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// Enable foreign keys
	if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
	}

	// Enable WAL mode for better concurrency
	if _, err := db.Exec("PRAGMA journal_mode = WAL"); err != nil {
		return nil, fmt.Errorf("failed to enable WAL mode: %w", err)
	}

	// Configure connection pool for SQLite with WAL mode
	// WAL allows concurrent reads, but writes still need serialization
	db.SetMaxOpenConns(config.SQLiteMaxOpenConns)
	db.SetMaxIdleConns(config.SQLiteMaxIdleConns)
	db.SetConnMaxLifetime(config.SQLiteConnMaxLifetime)

	store := &Store{db: db, migrationDir: migrationDir}

	// Run migrations
	if err := store.runMigrations(); err != nil {
		return nil, fmt.Errorf("failed to run migrations: %w", err)
	}

	return store, nil
}

// Close closes the database connection
func (s *Store) Close() error {
	return s.db.Close()
}

// DB returns the underlying database connection
func (s *Store) DB() *sql.DB {
	return s.db
}

// runMigrations executes all migration files in order
func (s *Store) runMigrations() error {
	// Get migration files from configured directory
	pattern := filepath.Join(s.migrationDir, "*.sql")
	migrationFiles, err := filepath.Glob(pattern)
	if err != nil {
		return fmt.Errorf("failed to read migration files: %w", err)
	}

	// Sort migrations by filename
	sort.Strings(migrationFiles)

	// Execute each migration
	for _, file := range migrationFiles {
		content, err := os.ReadFile(file)
		if err != nil {
			return fmt.Errorf("failed to read migration %s: %w", file, err)
		}

		if _, err := s.db.Exec(string(content)); err != nil {
			return fmt.Errorf("failed to execute migration %s: %w", file, err)
		}
	}

	return nil
}

// Tasks operations

// scanTask scans a single task row from the database
func scanTask(rows *sql.Rows) (models.Task, error) {
	var task models.Task
	var labelsJSON string
	var dueDate sql.NullTime

	err := rows.Scan(
		&task.ID,
		&task.Content,
		&task.Description,
		&task.ProjectID,
		&task.ProjectName,
		&dueDate,
		&task.Priority,
		&task.Completed,
		&labelsJSON,
		&task.URL,
		&task.CreatedAt,
	)
	if err != nil {
		return task, err
	}

	if dueDate.Valid {
		task.DueDate = &dueDate.Time
	}

	if err := json.Unmarshal([]byte(labelsJSON), &task.Labels); err != nil {
		log.Printf("Warning: failed to unmarshal labels for task %s: %v", task.ID, err)
		task.Labels = []string{}
	}
	return task, nil
}

// scanTasks scans multiple task rows from the database
func scanTasks(rows *sql.Rows) ([]models.Task, error) {
	var tasks []models.Task
	for rows.Next() {
		task, err := scanTask(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, task)
	}
	return tasks, rows.Err()
}

// SaveTasks saves multiple tasks to the database
func (s *Store) SaveTasks(tasks []models.Task) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	// Clear existing tasks first to remove stale data
	if _, err := tx.Exec(`DELETE FROM tasks`); err != nil {
		return err
	}

	stmt, err := tx.Prepare(`
		INSERT OR REPLACE INTO tasks
		(id, content, description, project_id, project_name, due_date, priority, completed, labels, url, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
	`)
	if err != nil {
		return err
	}
	defer func() { _ = stmt.Close() }()

	for _, task := range tasks {
		labelsJSON, err := json.Marshal(task.Labels)
		if err != nil {
			log.Printf("Warning: failed to marshal labels for task %s: %v", task.ID, err)
			labelsJSON = []byte("[]")
		}
		_, err = stmt.Exec(
			task.ID,
			task.Content,
			task.Description,
			task.ProjectID,
			task.ProjectName,
			task.DueDate,
			task.Priority,
			task.Completed,
			string(labelsJSON),
			task.URL,
			task.CreatedAt,
		)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// GetTasks retrieves all tasks from the database
func (s *Store) GetTasks() ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_id, project_name, due_date, priority, completed, labels, url, created_at
		FROM tasks
		ORDER BY completed ASC, CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC, priority DESC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanTasks(rows)
}

// DeleteTask removes a task from the cache by ID
func (s *Store) DeleteTask(id string) error {
	_, err := s.db.Exec(`DELETE FROM tasks WHERE id = ?`, id)
	return err
}

// UpsertTask inserts or updates a single task
func (s *Store) UpsertTask(task models.Task) error {
	labelsJSON, err := json.Marshal(task.Labels)
	if err != nil {
		log.Printf("Warning: failed to marshal labels for task %s: %v", task.ID, err)
		labelsJSON = []byte("[]")
	}
	_, err = s.db.Exec(`
		INSERT OR REPLACE INTO tasks
		(id, content, description, project_id, project_name, due_date, priority, completed, labels, url, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
	`,
		task.ID,
		task.Content,
		task.Description,
		task.ProjectID,
		task.ProjectName,
		task.DueDate,
		task.Priority,
		task.Completed,
		string(labelsJSON),
		task.URL,
		task.CreatedAt,
	)
	return err
}

// DeleteTasksByIDs removes multiple tasks by ID
func (s *Store) DeleteTasksByIDs(ids []string) error {
	if len(ids) == 0 {
		return nil
	}
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	stmt, err := tx.Prepare(`DELETE FROM tasks WHERE id = ?`)
	if err != nil {
		return err
	}
	defer func() { _ = stmt.Close() }()

	for _, id := range ids {
		if _, err := stmt.Exec(id); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// Meals operations

// SaveMeals saves multiple meals to the database
func (s *Store) SaveMeals(meals []models.Meal) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	stmt, err := tx.Prepare(`
		INSERT OR REPLACE INTO meals
		(id, recipe_name, date, meal_type, recipe_url, updated_at)
		VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
	`)
	if err != nil {
		return err
	}
	defer func() { _ = stmt.Close() }()

	for _, meal := range meals {
		_, err := stmt.Exec(
			meal.ID,
			meal.RecipeName,
			meal.Date,
			meal.MealType,
			meal.RecipeURL,
		)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// GetMeals retrieves meals from the database
func (s *Store) GetMeals(startDate, endDate time.Time) ([]models.Meal, error) {
	rows, err := s.db.Query(`
		SELECT id, recipe_name, date, meal_type, recipe_url
		FROM meals
		WHERE date BETWEEN ? AND ?
		ORDER BY date ASC,
			CASE meal_type
				WHEN 'breakfast' THEN 1
				WHEN 'lunch' THEN 2
				WHEN 'dinner' THEN 3
				ELSE 4
			END
	`, startDate, endDate)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var meals []models.Meal
	for rows.Next() {
		var meal models.Meal
		err := rows.Scan(
			&meal.ID,
			&meal.RecipeName,
			&meal.Date,
			&meal.MealType,
			&meal.RecipeURL,
		)
		if err != nil {
			return nil, err
		}
		meals = append(meals, meal)
	}

	return meals, rows.Err()
}

// Cache metadata operations

// GetCacheMetadata retrieves cache metadata for a key
func (s *Store) GetCacheMetadata(key string) (*models.CacheMetadata, error) {
	var cm models.CacheMetadata
	err := s.db.QueryRow(`
		SELECT key, last_fetch, ttl_minutes
		FROM cache_metadata
		WHERE key = ?
	`, key).Scan(&cm.Key, &cm.LastFetch, &cm.TTLMinutes)

	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	return &cm, nil
}

// UpdateCacheMetadata updates the last fetch time for a cache key
func (s *Store) UpdateCacheMetadata(key string, ttlMinutes int) error {
	_, err := s.db.Exec(`
		INSERT OR REPLACE INTO cache_metadata (key, last_fetch, ttl_minutes, updated_at)
		VALUES (?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP)
	`, key, ttlMinutes)
	return err
}

// IsCacheValid checks if the cache for a given key is still valid
func (s *Store) IsCacheValid(key string) (bool, error) {
	cm, err := s.GetCacheMetadata(key)
	if err != nil {
		return false, err
	}
	if cm == nil {
		return false, nil
	}

	return cm.IsCacheValid(), nil
}

// InvalidateCache removes the cache metadata for a given key, forcing a refresh on next fetch
func (s *Store) InvalidateCache(key string) error {
	_, err := s.db.Exec(`DELETE FROM cache_metadata WHERE key = ?`, key)
	return err
}

// Boards operations

// SaveBoards saves multiple boards to the database
// This clears existing data and replaces it with the new data
func (s *Store) SaveBoards(boards []models.Board) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	// Clear existing data first (cards must be deleted before boards due to foreign key)
	if _, err := tx.Exec(`DELETE FROM cards`); err != nil {
		return err
	}
	if _, err := tx.Exec(`DELETE FROM boards`); err != nil {
		return err
	}

	// Save boards
	boardStmt, err := tx.Prepare(`
		INSERT OR REPLACE INTO boards (id, name, updated_at)
		VALUES (?, ?, CURRENT_TIMESTAMP)
	`)
	if err != nil {
		return err
	}
	defer func() { _ = boardStmt.Close() }()

	// Save cards
	cardStmt, err := tx.Prepare(`
		INSERT OR REPLACE INTO cards
		(id, name, board_id, list_id, list_name, due_date, url, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
	`)
	if err != nil {
		return err
	}
	defer func() { _ = cardStmt.Close() }()

	savedBoards := 0
	savedCards := 0
	for _, board := range boards {
		_, err := boardStmt.Exec(board.ID, board.Name)
		if err != nil {
			log.Printf("Error saving board %s: %v", board.Name, err)
			return err
		}
		savedBoards++

		// Save all cards for this board
		for _, card := range board.Cards {
			_, err := cardStmt.Exec(
				card.ID,
				card.Name,
				board.ID,
				card.ListID,
				card.ListName,
				card.DueDate,
				card.URL,
			)
			if err != nil {
				log.Printf("Error saving card %s for board %s: %v", card.Name, board.Name, err)
				return err
			}
			savedCards++
		}
	}

	log.Printf("SaveBoards: Saved %d boards and %d cards to database", savedBoards, savedCards)
	return tx.Commit()
}

// GetBoards retrieves all boards with their cards from the database
func (s *Store) GetBoards() ([]models.Board, error) {
	// Fetch boards, sorted by: non-empty boards first, newest card activity, then alphabetical
	// Trello card IDs are chronologically sortable (newer IDs > older IDs)
	boardRows, err := s.db.Query(`
		SELECT b.id, b.name
		FROM boards b
		LEFT JOIN cards c ON c.board_id = b.id
		GROUP BY b.id, b.name
		ORDER BY
			CASE WHEN COUNT(c.id) > 0 THEN 0 ELSE 1 END,
			MAX(c.id) DESC,
			b.name ASC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = boardRows.Close() }()

	var boards []models.Board
	boardMap := make(map[string]int) // Store index, not pointer

	for boardRows.Next() {
		var board models.Board
		err := boardRows.Scan(&board.ID, &board.Name)
		if err != nil {
			return nil, err
		}
		board.Cards = []models.Card{}
		boardMap[board.ID] = len(boards) // Store index before append
		boards = append(boards, board)
	}

	if err := boardRows.Err(); err != nil {
		return nil, err
	}

	// Fetch cards
	cardRows, err := s.db.Query(`
		SELECT id, name, board_id, list_id, list_name, due_date, url
		FROM cards
		ORDER BY board_id, list_name, name
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = cardRows.Close() }()

	for cardRows.Next() {
		var card models.Card
		var boardID string
		var dueDate sql.NullTime

		err := cardRows.Scan(
			&card.ID,
			&card.Name,
			&boardID,
			&card.ListID,
			&card.ListName,
			&dueDate,
			&card.URL,
		)
		if err != nil {
			return nil, err
		}

		if dueDate.Valid {
			card.DueDate = &dueDate.Time
		}

		// Add card to the appropriate board
		if idx, ok := boardMap[boardID]; ok {
			boards[idx].Cards = append(boards[idx].Cards, card)
		}
	}

	totalCards := 0
	for _, b := range boards {
		totalCards += len(b.Cards)
	}
	log.Printf("GetBoards: Retrieved %d boards with %d total cards from database", len(boards), totalCards)

	return boards, cardRows.Err()
}

// DeleteCard removes a card from the cache by ID
func (s *Store) DeleteCard(id string) error {
	_, err := s.db.Exec(`DELETE FROM cards WHERE id = ?`, id)
	return err
}

// Sync token operations

// GetSyncToken retrieves the sync token for a service
func (s *Store) GetSyncToken(service string) (string, error) {
	var token string
	err := s.db.QueryRow(`SELECT token FROM sync_tokens WHERE service = ?`, service).Scan(&token)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return token, nil
}

// SetSyncToken saves the sync token for a service
func (s *Store) SetSyncToken(service, token string) error {
	_, err := s.db.Exec(`
		INSERT OR REPLACE INTO sync_tokens (service, token, updated_at)
		VALUES (?, ?, CURRENT_TIMESTAMP)
	`, service, token)
	return err
}

// ClearSyncToken removes the sync token for a service
func (s *Store) ClearSyncToken(service string) error {
	_, err := s.db.Exec(`DELETE FROM sync_tokens WHERE service = ?`, service)
	return err
}

// Bug represents a user-reported bug
type Bug struct {
	ID          int64
	Description string
	CreatedAt   time.Time
	ResolvedAt  *time.Time
}

// SaveBug saves a new bug report
func (s *Store) SaveBug(description string) error {
	_, err := s.db.Exec(`INSERT INTO bugs (description) VALUES (?)`, description)
	return err
}

// GetBugs retrieves all bugs, newest first
func (s *Store) GetBugs() ([]Bug, error) {
	rows, err := s.db.Query(`SELECT id, description, created_at, resolved_at FROM bugs ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var bugs []Bug
	for rows.Next() {
		var b Bug
		var resolvedAt sql.NullTime
		if err := rows.Scan(&b.ID, &b.Description, &b.CreatedAt, &resolvedAt); err != nil {
			return nil, err
		}
		if resolvedAt.Valid {
			b.ResolvedAt = &resolvedAt.Time
		}
		bugs = append(bugs, b)
	}
	return bugs, rows.Err()
}

// GetUnresolvedBugs retrieves bugs that haven't been resolved yet
func (s *Store) GetUnresolvedBugs() ([]Bug, error) {
	rows, err := s.db.Query(`SELECT id, description, created_at FROM bugs WHERE resolved_at IS NULL ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var bugs []Bug
	for rows.Next() {
		var b Bug
		if err := rows.Scan(&b.ID, &b.Description, &b.CreatedAt); err != nil {
			return nil, err
		}
		bugs = append(bugs, b)
	}
	return bugs, rows.Err()
}

// ResolveBug marks a bug as resolved
func (s *Store) ResolveBug(id int64) error {
	_, err := s.db.Exec(`UPDATE bugs SET resolved_at = CURRENT_TIMESTAMP WHERE id = ?`, id)
	return err
}

// UnresolveBug marks a bug as unresolved (reopens it)
func (s *Store) UnresolveBug(id int64) error {
	_, err := s.db.Exec(`UPDATE bugs SET resolved_at = NULL WHERE id = ?`, id)
	return err
}

// UserShoppingItem represents a user-added shopping item
type UserShoppingItem struct {
	ID        int64
	Name      string
	Store     string
	Checked   bool
	CreatedAt time.Time
}

// SaveUserShoppingItem saves a new user shopping item
func (s *Store) SaveUserShoppingItem(name, store string) error {
	_, err := s.db.Exec(`INSERT INTO user_shopping_items (name, store) VALUES (?, ?)`, name, store)
	return err
}

// GetUserShoppingItems retrieves all user shopping items
func (s *Store) GetUserShoppingItems() ([]UserShoppingItem, error) {
	rows, err := s.db.Query(`SELECT id, name, store, checked, created_at FROM user_shopping_items ORDER BY store, created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var items []UserShoppingItem
	for rows.Next() {
		var item UserShoppingItem
		if err := rows.Scan(&item.ID, &item.Name, &item.Store, &item.Checked, &item.CreatedAt); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

// ToggleUserShoppingItem toggles the checked state of a user shopping item
func (s *Store) ToggleUserShoppingItem(id int64, checked bool) error {
	_, err := s.db.Exec(`UPDATE user_shopping_items SET checked = ? WHERE id = ?`, checked, id)
	return err
}

// DeleteUserShoppingItem removes a user shopping item
func (s *Store) DeleteUserShoppingItem(id int64) error {
	_, err := s.db.Exec(`DELETE FROM user_shopping_items WHERE id = ?`, id)
	return err
}

// SetShoppingItemChecked sets the checked state for an external shopping item
func (s *Store) SetShoppingItemChecked(source, itemID string, checked bool) error {
	checkedInt := 0
	if checked {
		checkedInt = 1
	}
	_, err := s.db.Exec(`
		INSERT INTO shopping_item_checks (source, item_id, checked, updated_at)
		VALUES (?, ?, ?, CURRENT_TIMESTAMP)
		ON CONFLICT(source, item_id) DO UPDATE SET checked = ?, updated_at = CURRENT_TIMESTAMP
	`, source, itemID, checkedInt, checkedInt)
	return err
}

// GetShoppingItemChecks returns a map of item_id -> checked for a given source
func (s *Store) GetShoppingItemChecks(source string) (map[string]bool, error) {
	rows, err := s.db.Query(`SELECT item_id, checked FROM shopping_item_checks WHERE source = ?`, source)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	checks := make(map[string]bool)
	for rows.Next() {
		var itemID string
		var checked int
		if err := rows.Scan(&itemID, &checked); err != nil {
			return nil, err
		}
		checks[itemID] = checked == 1
	}
	return checks, rows.Err()
}

// GetTasksByDateRange retrieves tasks due within a specific date range
func (s *Store) GetTasksByDateRange(start, end time.Time) ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_id, project_name, due_date, priority, completed, labels, url, created_at
		FROM tasks
		WHERE due_date BETWEEN ? AND ?
		ORDER BY due_date ASC, priority DESC
	`, start, end)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanTasks(rows)
}

// GetMealsByDateRange retrieves meals within a specific date range
func (s *Store) GetMealsByDateRange(start, end time.Time) ([]models.Meal, error) {
	return s.GetMeals(start, end)
}

// GetCardsByDateRange retrieves cards due within a specific date range
func (s *Store) GetCardsByDateRange(start, end time.Time) ([]models.Card, error) {
	rows, err := s.db.Query(`
		SELECT c.id, c.name, b.name, c.list_id, c.list_name, c.due_date, c.url
		FROM cards c
		JOIN boards b ON c.board_id = b.id
		WHERE c.due_date BETWEEN ? AND ?
		ORDER BY c.due_date ASC
	`, start, end)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var cards []models.Card
	for rows.Next() {
		var card models.Card
		var dueDate sql.NullTime

		err := rows.Scan(
			&card.ID,
			&card.Name,
			&card.BoardName,
			&card.ListID,
			&card.ListName,
			&dueDate,
			&card.URL,
		)
		if err != nil {
			return nil, err
		}

		if dueDate.Valid {
			card.DueDate = &dueDate.Time
		}

		cards = append(cards, card)
	}

	return cards, rows.Err()
}

// Agent operations

// CreateAgentSession creates a new pending agent session
func (s *Store) CreateAgentSession(session *models.AgentSession) error {
	result, err := s.db.Exec(`
		INSERT INTO agent_sessions (request_token, agent_name, agent_id, status, expires_at)
		VALUES (?, ?, ?, 'pending', ?)
	`, session.RequestToken, session.AgentName, session.AgentID, session.ExpiresAt)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	session.ID = id
	return nil
}

// GetAgentSessionByRequestToken retrieves a session by request token
func (s *Store) GetAgentSessionByRequestToken(token string) (*models.AgentSession, error) {
	var session models.AgentSession
	var sessionToken sql.NullString
	var sessionExpiresAt sql.NullTime

	err := s.db.QueryRow(`
		SELECT id, request_token, agent_name, agent_id, status, created_at, expires_at, session_token, session_expires_at
		FROM agent_sessions
		WHERE request_token = ?
	`, token).Scan(
		&session.ID,
		&session.RequestToken,
		&session.AgentName,
		&session.AgentID,
		&session.Status,
		&session.CreatedAt,
		&session.ExpiresAt,
		&sessionToken,
		&sessionExpiresAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	if sessionToken.Valid {
		session.SessionToken = sessionToken.String
	}
	if sessionExpiresAt.Valid {
		session.SessionExpiresAt = &sessionExpiresAt.Time
	}
	return &session, nil
}

// GetPendingAgentSessionByAgentID retrieves an existing pending session for an agent
func (s *Store) GetPendingAgentSessionByAgentID(agentID string) (*models.AgentSession, error) {
	var session models.AgentSession

	err := s.db.QueryRow(`
		SELECT id, request_token, agent_name, agent_id, status, created_at, expires_at
		FROM agent_sessions
		WHERE agent_id = ? AND status = 'pending' AND expires_at > datetime('now', 'localtime')
		ORDER BY created_at DESC
		LIMIT 1
	`, agentID).Scan(
		&session.ID,
		&session.RequestToken,
		&session.AgentName,
		&session.AgentID,
		&session.Status,
		&session.CreatedAt,
		&session.ExpiresAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	return &session, nil
}

// GetAgentSessionBySessionToken retrieves a session by session token
func (s *Store) GetAgentSessionBySessionToken(token string) (*models.AgentSession, error) {
	var session models.AgentSession
	var sessionToken sql.NullString
	var sessionExpiresAt sql.NullTime

	err := s.db.QueryRow(`
		SELECT id, request_token, agent_name, agent_id, status, created_at, expires_at, session_token, session_expires_at
		FROM agent_sessions
		WHERE session_token = ? AND status = 'approved'
	`, token).Scan(
		&session.ID,
		&session.RequestToken,
		&session.AgentName,
		&session.AgentID,
		&session.Status,
		&session.CreatedAt,
		&session.ExpiresAt,
		&sessionToken,
		&sessionExpiresAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	if sessionToken.Valid {
		session.SessionToken = sessionToken.String
	}
	if sessionExpiresAt.Valid {
		session.SessionExpiresAt = &sessionExpiresAt.Time
	}
	return &session, nil
}

// ApproveAgentSession approves a pending session
func (s *Store) ApproveAgentSession(requestToken, sessionToken string, sessionExpiresAt time.Time) error {
	result, err := s.db.Exec(`
		UPDATE agent_sessions
		SET status = 'approved', session_token = ?, session_expires_at = ?
		WHERE request_token = ? AND status = 'pending'
	`, sessionToken, sessionExpiresAt, requestToken)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected == 0 {
		return errors.New("session not found or already processed")
	}
	return nil
}

// DenyAgentSession denies a pending session
func (s *Store) DenyAgentSession(requestToken string) error {
	result, err := s.db.Exec(`
		UPDATE agent_sessions
		SET status = 'denied'
		WHERE request_token = ? AND status = 'pending'
	`, requestToken)
	if err != nil {
		return err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected == 0 {
		return errors.New("session not found or already processed")
	}
	return nil
}

// GetPendingAgentSessions retrieves all unexpired pending sessions
func (s *Store) GetPendingAgentSessions() ([]models.AgentSession, error) {
	rows, err := s.db.Query(`
		SELECT id, request_token, agent_name, agent_id, status, created_at, expires_at
		FROM agent_sessions
		WHERE status = 'pending' AND expires_at > datetime('now', 'localtime')
		ORDER BY created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var sessions []models.AgentSession
	for rows.Next() {
		var session models.AgentSession
		if err := rows.Scan(
			&session.ID,
			&session.RequestToken,
			&session.AgentName,
			&session.AgentID,
			&session.Status,
			&session.CreatedAt,
			&session.ExpiresAt,
		); err != nil {
			return nil, err
		}
		sessions = append(sessions, session)
	}
	return sessions, rows.Err()
}

// InvalidatePreviousAgentSessions marks previous sessions for an agent as expired
func (s *Store) InvalidatePreviousAgentSessions(agentID string) error {
	_, err := s.db.Exec(`
		UPDATE agent_sessions
		SET status = 'expired'
		WHERE agent_id = ? AND status IN ('pending', 'approved')
	`, agentID)
	return err
}

// GetAgentByAgentID retrieves an agent by their agent_id (UUID)
func (s *Store) GetAgentByAgentID(agentID string) (*models.Agent, error) {
	var agent models.Agent
	var lastSeen sql.NullTime

	err := s.db.QueryRow(`
		SELECT id, name, agent_id, created_at, last_seen, trusted
		FROM agents
		WHERE agent_id = ?
	`, agentID).Scan(
		&agent.ID,
		&agent.Name,
		&agent.AgentID,
		&agent.CreatedAt,
		&lastSeen,
		&agent.Trusted,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	if lastSeen.Valid {
		agent.LastSeen = &lastSeen.Time
	}
	return &agent, nil
}

// GetAgentByName retrieves an agent by name
func (s *Store) GetAgentByName(name string) (*models.Agent, error) {
	var agent models.Agent
	var lastSeen sql.NullTime

	err := s.db.QueryRow(`
		SELECT id, name, agent_id, created_at, last_seen, trusted
		FROM agents
		WHERE name = ?
	`, name).Scan(
		&agent.ID,
		&agent.Name,
		&agent.AgentID,
		&agent.CreatedAt,
		&lastSeen,
		&agent.Trusted,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	if lastSeen.Valid {
		agent.LastSeen = &lastSeen.Time
	}
	return &agent, nil
}

// CreateOrUpdateAgent creates or updates an agent record
func (s *Store) CreateOrUpdateAgent(name, agentID string) error {
	_, err := s.db.Exec(`
		INSERT INTO agents (name, agent_id, last_seen, trusted)
		VALUES (?, ?, datetime('now'), 1)
		ON CONFLICT(agent_id) DO UPDATE SET
			name = excluded.name,
			last_seen = datetime('now')
	`, name, agentID)
	return err
}

// UpdateAgentLastSeen updates the last_seen timestamp for an agent
func (s *Store) UpdateAgentLastSeen(agentID string) error {
	_, err := s.db.Exec(`
		UPDATE agents SET last_seen = datetime('now')
		WHERE agent_id = ?
	`, agentID)
	return err
}

// GetAllAgents retrieves all agents
func (s *Store) GetAllAgents() ([]models.Agent, error) {
	rows, err := s.db.Query(`
		SELECT id, name, agent_id, created_at, last_seen, trusted
		FROM agents
		ORDER BY last_seen DESC NULLS LAST
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var agents []models.Agent
	for rows.Next() {
		var agent models.Agent
		var lastSeen sql.NullTime
		if err := rows.Scan(
			&agent.ID,
			&agent.Name,
			&agent.AgentID,
			&agent.CreatedAt,
			&lastSeen,
			&agent.Trusted,
		); err != nil {
			return nil, err
		}
		if lastSeen.Valid {
			agent.LastSeen = &lastSeen.Time
		}
		agents = append(agents, agent)
	}
	return agents, rows.Err()
}

// RevokeAgent sets trusted=false for an agent
func (s *Store) RevokeAgent(agentID string) error {
	_, err := s.db.Exec(`UPDATE agents SET trusted = 0 WHERE agent_id = ?`, agentID)
	return err
}

// CheckAgentTrust determines trust level for an agent request
func (s *Store) CheckAgentTrust(name, agentID string) (models.AgentTrustLevel, error) {
	// Check if this exact agent_id is known
	existingByID, err := s.GetAgentByAgentID(agentID)
	if err != nil {
		return "", err
	}

	// Check if this name is known with a different ID
	existingByName, err := s.GetAgentByName(name)
	if err != nil {
		return "", err
	}

	if existingByID != nil && existingByID.Name == name && existingByID.Trusted {
		return models.AgentTrustRecognized, nil
	}

	if existingByName != nil && existingByName.AgentID != agentID {
		return models.AgentTrustSuspicious, nil
	}

	return models.AgentTrustNew, nil
}