feat(tasks): add Projects CRUD store methods

1 files changed, 67 insertions, 0 deletions

diff --git a/internal/store/projects.go b/internal/store/projects.go
new file mode 100644
index 0000000..a05656f
--- /dev/null
+++ b/internal/store/projects.go
@@ -0,0 +1,67 @@
+package store
+
+import (
+	"database/sql"
+
+	"task-dashboard/internal/models"
+)
+
+// CreateProject inserts a new project and returns it.
+func (s *Store) CreateProject(name, color string) (*models.Project, error) {
+	id := newTaskID()
+	if _, err := s.db.Exec(`INSERT INTO projects (id, name, color) VALUES (?, ?, ?)`, id, name, color); err != nil {
+		return nil, err
+	}
+	return s.GetProjectByID(id)
+}
+
+// GetProjects returns all non-archived projects, alphabetically by name.
+func (s *Store) GetProjects() ([]models.Project, error) {
+	rows, err := s.db.Query(`
+		SELECT id, name, color, created_at, archived FROM projects WHERE archived = 0 ORDER BY name ASC
+	`)
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = rows.Close() }()
+
+	var projects []models.Project
+	for rows.Next() {
+		var p models.Project
+		if err := rows.Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived); err != nil {
+			return nil, err
+		}
+		projects = append(projects, p)
+	}
+	return projects, rows.Err()
+}
+
+// GetProjectByID returns a single project by id, or ErrNativeTaskNotFound.
+func (s *Store) GetProjectByID(id string) (*models.Project, error) {
+	var p models.Project
+	err := s.db.QueryRow(`
+		SELECT id, name, color, created_at, archived FROM projects WHERE id = ?
+	`, id).Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived)
+	if err == sql.ErrNoRows {
+		return nil, ErrNativeTaskNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &p, nil
+}
+
+// SetTaskProject sets or clears a task's project (projectID = "" clears
+// it). Returns ErrNativeTaskNotFound if id doesn't match any task row --
+// deliberately does NOT validate that projectID itself exists, mirroring
+// this codebase's existing tolerance for a stale/empty foreign reference
+// (e.g. RecurrenceSeriesID is never validated against another table either).
+func (s *Store) SetTaskProject(id, projectID string) error {
+	result, err := s.db.Exec(`
+		UPDATE native_tasks SET project_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
+	`, projectID, id)
+	if err != nil {
+		return err
+	}
+	return checkRowsAffected(result)
+}