tracker/internal/store/store.go

package store

import (
	"database/sql"
	"errors"

	_ "modernc.org/sqlite"
)

var (
	ErrNotFound = errors.New("not found")
)

type Project struct {
	ID          int64  `json:"id"`
	Name        string `json:"name"`
	Description string `json:"description"`
	CreatedAt   string `json:"created_at"`
}

type Note struct {
	ID        int64  `json:"id"`
	ProjectID int64  `json:"project_id"`
	Body      string `json:"body"`
	CreatedAt string `json:"created_at"`
}

type Task struct {
	ID        int64  `json:"id"`
	ProjectID int64  `json:"project_id"`
	Title     string `json:"title"`
	Status    string `json:"status"`
	CreatedAt string `json:"created_at"`
}

type Hit struct {
	Kind      string `json:"kind"`
	ProjectID int64  `json:"project_id"`
	RefID     int64  `json:"ref_id"`
	Title     string `json:"title"`
	Snippet   string `json:"snippet"`
}

type Store struct {
	db *sql.DB
}

func Open(path string) (*Store, error) {
	db, err := sql.Open("sqlite", path+"?_pragma=foreign_keys(1)&_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
	if err != nil {
		return nil, err
	}
	s := &Store{db: db}
	if err := s.migrate(); err != nil {
		db.Close()
		return nil, err
	}
	return s, nil
}

func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	_, err := s.db.Exec(`
CREATE TABLE IF NOT EXISTS projects (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	description TEXT NOT NULL DEFAULT '',
	created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS notes (
	id INTEGER PRIMARY KEY,
	project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
	body TEXT NOT NULL,
	created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS tasks (
	id INTEGER PRIMARY KEY,
	project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
	title TEXT NOT NULL,
	status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo','doing','done')),
	created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
CREATE INDEX IF NOT EXISTS idx_notes_project ON notes(project_id);
CREATE INDEX IF NOT EXISTS idx_tasks_project ON tasks(project_id);
CREATE TABLE IF NOT EXISTS users (
	id INTEGER PRIMARY KEY,
	username TEXT NOT NULL UNIQUE,
	password_hash TEXT NOT NULL,
	created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS api_keys (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	key_hash TEXT NOT NULL UNIQUE,
	created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
	last_used TEXT
);
CREATE TABLE IF NOT EXISTS sessions (
	token_hash TEXT PRIMARY KEY,
	username TEXT NOT NULL,
	created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
	expires_at TEXT NOT NULL
);
`)
	return err
}

// --- projects ---

func (s *Store) Projects() ([]Project, error) {
	rows, err := s.db.Query(`SELECT id, name, description, created_at FROM projects ORDER BY name COLLATE NOCASE`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanProjects(rows)
}

func (s *Store) Project(id int64) (Project, error) {
	var p Project
	err := s.db.QueryRow(`SELECT id, name, description, created_at FROM projects WHERE id = ?`, id).
		Scan(&p.ID, &p.Name, &p.Description, &p.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return p, ErrNotFound
	}
	return p, err
}

func (s *Store) CreateProject(name, description string) (int64, error) {
	res, err := s.db.Exec(`INSERT INTO projects (name, description) VALUES (?, ?)`, name, description)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (s *Store) UpdateProject(id int64, name, description string) error {
	res, err := s.db.Exec(`UPDATE projects SET name = ?, description = ? WHERE id = ?`, name, description, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

func (s *Store) DeleteProject(id int64) error {
	res, err := s.db.Exec(`DELETE FROM projects WHERE id = ?`, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

// --- notes ---

func (s *Store) Notes(projectID int64) ([]Note, error) {
	rows, err := s.db.Query(`SELECT id, project_id, body, created_at FROM notes WHERE project_id = ? ORDER BY id DESC`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var notes []Note
	for rows.Next() {
		var n Note
		if err := rows.Scan(&n.ID, &n.ProjectID, &n.Body, &n.CreatedAt); err != nil {
			return nil, err
		}
		notes = append(notes, n)
	}
	return notes, rows.Err()
}

func (s *Store) Note(id int64) (Note, error) {
	var n Note
	err := s.db.QueryRow(`SELECT id, project_id, body, created_at FROM notes WHERE id = ?`, id).
		Scan(&n.ID, &n.ProjectID, &n.Body, &n.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return n, ErrNotFound
	}
	return n, err
}

func (s *Store) CreateNote(projectID int64, body string) (int64, error) {
	res, err := s.db.Exec(`INSERT INTO notes (project_id, body) VALUES (?, ?)`, projectID, body)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (s *Store) DeleteNote(id int64) error {
	res, err := s.db.Exec(`DELETE FROM notes WHERE id = ?`, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

func (s *Store) UpdateNote(id int64, body string) error {
	res, err := s.db.Exec(`UPDATE notes SET body = ? WHERE id = ?`, body, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

// --- tasks ---

func (s *Store) Tasks(projectID int64) ([]Task, error) {
	rows, err := s.db.Query(`SELECT id, project_id, title, status, created_at FROM tasks WHERE project_id = ? ORDER BY id`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var tasks []Task
	for rows.Next() {
		var t Task
		if err := rows.Scan(&t.ID, &t.ProjectID, &t.Title, &t.Status, &t.CreatedAt); err != nil {
			return nil, err
		}
		tasks = append(tasks, t)
	}
	return tasks, rows.Err()
}

func (s *Store) Task(id int64) (Task, error) {
	var t Task
	err := s.db.QueryRow(`SELECT id, project_id, title, status, created_at FROM tasks WHERE id = ?`, id).
		Scan(&t.ID, &t.ProjectID, &t.Title, &t.Status, &t.CreatedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return t, ErrNotFound
	}
	return t, err
}

func (s *Store) CreateTask(projectID int64, title string) (int64, error) {
	res, err := s.db.Exec(`INSERT INTO tasks (project_id, title) VALUES (?, ?)`, projectID, title)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

// CycleTask advances status todo -> doing -> done -> todo.
func (s *Store) CycleTask(id int64) error {
	_, err := s.db.Exec(`UPDATE tasks SET status = CASE status WHEN 'todo' THEN 'doing' WHEN 'doing' THEN 'done' ELSE 'todo' END WHERE id = ?`, id)
	return err
}

func (s *Store) SetTaskStatus(id int64, status string) error {
	res, err := s.db.Exec(`UPDATE tasks SET status = ? WHERE id = ?`, status, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

func (s *Store) UpdateTaskTitle(id int64, title string) error {
	res, err := s.db.Exec(`UPDATE tasks SET title = ? WHERE id = ?`, title, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

func (s *Store) DeleteTask(id int64) error {
	res, err := s.db.Exec(`DELETE FROM tasks WHERE id = ?`, id)
	if err != nil {
		return err
	}
	return checkAffected(res)
}

// --- search ---

func (s *Store) Search(q string) ([]Hit, error) {
	like := "%" + q + "%"
	var hits []Hit

	rows, err := s.db.Query(`SELECT id, name, description FROM projects WHERE name LIKE ?1 OR description LIKE ?1 ORDER BY name COLLATE NOCASE`, like)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var id int64
		var name, desc string
		if err := rows.Scan(&id, &name, &desc); err != nil {
			rows.Close()
			return nil, err
		}
		title, snip := name, desc
		if snip == "" {
			snip = name
		}
		hits = append(hits, Hit{Kind: "project", ProjectID: id, RefID: id, Title: title, Snippet: snip})
	}
	rows.Close()

	rows, err = s.db.Query(`SELECT n.id, n.project_id, n.body, p.name FROM notes n JOIN projects p ON p.id = n.project_id WHERE n.body LIKE ? ORDER BY n.id DESC`, like)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var id, pid int64
		var body, pname string
		if err := rows.Scan(&id, &pid, &body, &pname); err != nil {
			rows.Close()
			return nil, err
		}
		hits = append(hits, Hit{Kind: "note", ProjectID: pid, RefID: id, Title: pname, Snippet: snippet(body, q)})
	}
	rows.Close()

	rows, err = s.db.Query(`SELECT t.id, t.project_id, t.title, p.name FROM tasks t JOIN projects p ON p.id = t.project_id WHERE t.title LIKE ? ORDER BY t.id DESC`, like)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var id, pid int64
		var title, pname string
		if err := rows.Scan(&id, &pid, &title, &pname); err != nil {
			rows.Close()
			return nil, err
		}
		hits = append(hits, Hit{Kind: "task", ProjectID: pid, RefID: id, Title: title, Snippet: pname})
	}
	rows.Close()

	return hits, nil
}

// --- stats ---

type Stats struct {
	Projects int
	Tasks    struct {
		Todo, Doing, Done int
	}
	Notes int
}

func (s *Store) Stats() (Stats, error) {
	var st Stats
	if err := s.db.QueryRow(`SELECT COUNT(*) FROM projects`).Scan(&st.Projects); err != nil {
		return st, err
	}
	if err := s.db.QueryRow(`SELECT COUNT(*) FROM notes`).Scan(&st.Notes); err != nil {
		return st, err
	}
	rows, err := s.db.Query(`SELECT status, COUNT(*) FROM tasks GROUP BY status`)
	if err != nil {
		return st, err
	}
	defer rows.Close()
	for rows.Next() {
		var status string
		var n int
		if err := rows.Scan(&status, &n); err != nil {
			return st, err
		}
		switch status {
		case "todo":
			st.Tasks.Todo = n
		case "doing":
			st.Tasks.Doing = n
		case "done":
			st.Tasks.Done = n
		}
	}
	return st, rows.Err()
}

// --- helpers ---

func scanProjects(rows *sql.Rows) ([]Project, error) {
	var projects []Project
	for rows.Next() {
		var p Project
		if err := rows.Scan(&p.ID, &p.Name, &p.Description, &p.CreatedAt); err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}

func checkAffected(res sql.Result) error {
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// snippet returns body trimmed to a window around the first match of q.
func snippet(body, q string) string {
	lb, lq := lower(body), lower(q)
	i := index(lb, lq)
	if i < 0 {
		if len(body) > 160 {
			return body[:160] + "…"
		}
		return body
	}
	start := i - 40
	if start < 0 {
		start = 0
	}
	end := i + len(q) + 80
	if end > len(body) {
		end = len(body)
	}
	out := body[start:end]
	if start > 0 {
		out = "…" + out
	}
	if end < len(body) {
		out += "…"
	}
	return out
}

// ASCII-only helpers to avoid pulling unicode/strings machinery into hot path readability.
func lower(s string) string {
	b := []byte(s)
	for i := range b {
		if b[i] >= 'A' && b[i] <= 'Z' {
			b[i] += 'a' - 'A'
		}
	}
	return string(b)
}

func index(s, sub string) int {
	for i := 0; i+len(sub) <= len(s); i++ {
		if s[i:i+len(sub)] == sub {
			return i
		}
	}
	return -1
}