path: root/internal/store/sqlite.go

package store

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

	_ "github.com/mattn/go-sqlite3"
	"task-dashboard/internal/models"
)

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

type Store struct {
	db *sql.DB
}

// New creates a new Store instance and runs migrations
func New(dbPath 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)
	}

	// Serialize writes to prevent "database is locked" errors
	db.SetMaxOpenConns(1)

	store := &Store{db: db}

	// 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()
}

// runMigrations executes all migration files in order
func (s *Store) runMigrations() error {
	// Get migration files
	migrationFiles, err := filepath.Glob("migrations/*.sql")
	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

// 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 tx.Rollback()

	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 stmt.Close()

	for _, task := range tasks {
		labelsJSON, _ := json.Marshal(task.Labels)
		_, 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 rows.Close()

	var tasks []models.Task
	for rows.Next() {
		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 nil, err
		}

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

		json.Unmarshal([]byte(labelsJSON), &task.Labels)
		tasks = append(tasks, task)
	}

	return tasks, rows.Err()
}

// 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
}

// Notes operations

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

	stmt, err := tx.Prepare(`
		INSERT OR REPLACE INTO notes
		(filename, title, content, modified, path, tags, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	for _, note := range notes {
		tagsJSON, _ := json.Marshal(note.Tags)
		_, err := stmt.Exec(
			note.Filename,
			note.Title,
			note.Content,
			note.Modified,
			note.Path,
			string(tagsJSON),
		)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// GetNotes retrieves all notes from the database
func (s *Store) GetNotes(limit int) ([]models.Note, error) {
	query := `
		SELECT filename, title, content, modified, path, tags
		FROM notes
		ORDER BY modified DESC
	`
	var args []interface{}
	if limit > 0 {
		query += " LIMIT ?"
		args = append(args, limit)
	}

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

	var notes []models.Note
	for rows.Next() {
		var note models.Note
		var tagsJSON string

		err := rows.Scan(
			&note.Filename,
			&note.Title,
			&note.Content,
			&note.Modified,
			&note.Path,
			&tagsJSON,
		)
		if err != nil {
			return nil, err
		}

		json.Unmarshal([]byte(tagsJSON), &note.Tags)
		notes = append(notes, note)
	}

	return notes, rows.Err()
}

// SearchNotes searches notes by title or content
func (s *Store) SearchNotes(query string) ([]models.Note, error) {
	searchPattern := "%" + query + "%"
	rows, err := s.db.Query(`
		SELECT filename, title, content, modified, path, tags
		FROM notes
		WHERE title LIKE ? OR content LIKE ?
		ORDER BY modified DESC
	`, searchPattern, searchPattern)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var notes []models.Note
	for rows.Next() {
		var note models.Note
		var tagsJSON string

		err := rows.Scan(
			&note.Filename,
			&note.Title,
			&note.Content,
			&note.Modified,
			&note.Path,
			&tagsJSON,
		)
		if err != nil {
			return nil, err
		}

		json.Unmarshal([]byte(tagsJSON), &note.Tags)
		notes = append(notes, note)
	}

	return notes, rows.Err()
}

// 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 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 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 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 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 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 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 cardStmt.Close()

	for _, board := range boards {
		_, err := boardStmt.Exec(board.ID, board.Name)
		if err != nil {
			return err
		}

		// 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 {
				return err
			}
		}
	}

	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 boardRows.Close()

	var boards []models.Board
	boardMap := make(map[string]*models.Board)

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

	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 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 board, ok := boardMap[boardID]; ok {
			board.Cards = append(board.Cards, card)
		}
	}

	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
}