1 files changed, 192 insertions, 0 deletions
diff --git a/internal/store/buckets.go b/internal/store/buckets.go
new file mode 100644
index 0000000..8bbcca9
--- /dev/null
+++ b/internal/store/buckets.go
@@ -0,0 +1,192 @@
+package store
+
+import (
+	"database/sql"
+	"time"
+
+	"task-dashboard/internal/config"
+	"task-dashboard/internal/models"
+)
+
+// CreateBucket inserts a new maintenance bucket and returns it.
+func (s *Store) CreateBucket(name string, cycleDays, pickN int) (*models.MaintenanceBucket, error) {
+	id := newTaskID()
+	if _, err := s.db.Exec(`
+		INSERT INTO maintenance_buckets (id, name, cycle_days, pick_n) VALUES (?, ?, ?, ?)
+	`, id, name, cycleDays, pickN); err != nil {
+		return nil, err
+	}
+	return &models.MaintenanceBucket{ID: id, Name: name, CycleDays: cycleDays, PickN: pickN}, nil
+}
+
+// GetBuckets returns every maintenance bucket, alphabetically by name.
+func (s *Store) GetBuckets() ([]models.MaintenanceBucket, error) {
+	rows, err := s.db.Query(`
+		SELECT id, name, cycle_days, pick_n, last_cycle_at, created_at FROM maintenance_buckets ORDER BY name ASC
+	`)
+	if err != nil {
+		return nil, err
+	}
+	defer func() { _ = rows.Close() }()
+
+	var buckets []models.MaintenanceBucket
+	for rows.Next() {
+		var b models.MaintenanceBucket
+		var lastCycleAt sql.NullTime
+		if err := rows.Scan(&b.ID, &b.Name, &b.CycleDays, &b.PickN, &lastCycleAt, &b.CreatedAt); err != nil {
+			return nil, err
+		}
+		if lastCycleAt.Valid {
+			b.LastCycleAt = &lastCycleAt.Time
+		}
+		buckets = append(buckets, b)
+	}
+	return buckets, rows.Err()
+}
+
+// GetBucketByID returns a single bucket by id, or ErrNativeTaskNotFound.
+func (s *Store) GetBucketByID(id string) (*models.MaintenanceBucket, error) {
+	var b models.MaintenanceBucket
+	var lastCycleAt sql.NullTime
+	err := s.db.QueryRow(`
+		SELECT id, name, cycle_days, pick_n, last_cycle_at, created_at FROM maintenance_buckets WHERE id = ?
+	`, id).Scan(&b.ID, &b.Name, &b.CycleDays, &b.PickN, &lastCycleAt, &b.CreatedAt)
+	if err == sql.ErrNoRows {
+		return nil, ErrNativeTaskNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	if lastCycleAt.Valid {
+		b.LastCycleAt = &lastCycleAt.Time
+	}
+	return &b, nil
+}
+
+// AddBucketItem assigns an existing task to a bucket's pool: sets bucket_id
+// and bucket_state = 'dormant', clearing any due date (dormant items are
+// invisible to date-based views, per the design). Returns
+// ErrNativeTaskNotFound if taskID doesn't match any row.
+func (s *Store) AddBucketItem(bucketID, taskID string) error {
+	result, err := s.db.Exec(`
+		UPDATE native_tasks SET bucket_id = ?, bucket_state = 'dormant', due_date = NULL, updated_at = ? WHERE id = ?
+	`, bucketID, config.Now(), taskID)
+	if err != nil {
+		return err
+	}
+	return checkRowsAffected(result)
+}
+
+// RemoveBucketItem clears a task's bucket membership entirely. Returns
+// ErrNativeTaskNotFound if taskID doesn't match any row.
+func (s *Store) RemoveBucketItem(taskID string) error {
+	result, err := s.db.Exec(`
+		UPDATE native_tasks SET bucket_id = '', bucket_state = '', bucket_last_active_at = NULL, updated_at = ? WHERE id = ?
+	`, config.Now(), taskID)
+	if err != nil {
+		return err
+	}
+	return checkRowsAffected(result)
+}
+
+// selectBucketCycle activates the top pick_n dormant items in the bucket's
+// pool, scored by staleness (never-activated items first, then oldest
+// bucket_last_active_at) with task priority as a tiebreaker. Activated items
+// get due_date = now + cycle_days and bucket_last_active_at = now. Always
+// updates the bucket's last_cycle_at, even if nothing was activated (an
+// empty pool shouldn't cause every subsequent tick to re-scan it). Returns
+// the number of items activated.
+func (s *Store) selectBucketCycle(bucketID string, now time.Time) (int, error) {
+	bucket, err := s.GetBucketByID(bucketID)
+	if err != nil {
+		return 0, err
+	}
+
+	rows, err := s.db.Query(`
+		SELECT id FROM native_tasks
+		WHERE bucket_id = ? AND bucket_state = 'dormant'
+		ORDER BY (bucket_last_active_at IS NULL) DESC, bucket_last_active_at ASC, priority DESC
+		LIMIT ?
+	`, bucketID, bucket.PickN)
+	if err != nil {
+		return 0, err
+	}
+	var ids []string
+	for rows.Next() {
+		var id string
+		if err := rows.Scan(&id); err != nil {
+			_ = rows.Close()
+			return 0, err
+		}
+		ids = append(ids, id)
+	}
+	if err := rows.Err(); err != nil {
+		return 0, err
+	}
+	_ = rows.Close()
+
+	dueDate := now.AddDate(0, 0, bucket.CycleDays)
+	for _, id := range ids {
+		if _, err := s.db.Exec(`
+			UPDATE native_tasks SET bucket_state = 'active', due_date = ?, bucket_last_active_at = ?, updated_at = ? WHERE id = ?
+		`, dueDate, now, now, id); err != nil {
+			return 0, err
+		}
+	}
+
+	if _, err := s.db.Exec(`UPDATE maintenance_buckets SET last_cycle_at = ? WHERE id = ?`, now, bucketID); err != nil {
+		return 0, err
+	}
+	return len(ids), nil
+}
+
+// RunBucketCycles runs selectBucketCycle for every bucket whose cycle is
+// due (last_cycle_at is unset, or at least cycle_days old). Mirrors
+// AdvanceDueRecurringTasks's shape as the scheduler's entry point. Returns
+// the total number of items activated across all due buckets.
+func (s *Store) RunBucketCycles(now time.Time) (int, error) {
+	buckets, err := s.GetBuckets()
+	if err != nil {
+		return 0, err
+	}
+	total := 0
+	for _, b := range buckets {
+		due := b.LastCycleAt == nil || !b.LastCycleAt.AddDate(0, 0, b.CycleDays).After(now)
+		if !due {
+			continue
+		}
+		n, err := s.selectBucketCycle(b.ID, now)
+		if err != nil {
+			return total, err
+		}
+		total += n
+	}
+	return total, nil
+}
+
+// DeferNativeTask returns an active bucket item to the pool without
+// crediting it as done: bucket_last_active_at is left at its prior value
+// (still relatively stale, likely to be reselected soon) -- unlike
+// completing it, which stamps bucket_last_active_at = now via
+// CompleteNativeTask. Immediately triggers a fresh selection for the
+// task's bucket to backfill the freed slot. Returns ErrNativeTaskNotFound
+// if id doesn't match any row, or if the task isn't an active bucket item.
+func (s *Store) DeferNativeTask(id string) error {
+	task, err := s.GetNativeTaskByID(id)
+	if err != nil {
+		return err
+	}
+	if task.BucketID == "" || task.BucketState != "active" {
+		return ErrNativeTaskNotFound
+	}
+
+	now := config.Now()
+	if _, err := s.db.Exec(`
+		UPDATE native_tasks SET bucket_state = 'dormant', due_date = NULL, updated_at = ? WHERE id = ?
+	`, now, id); err != nil {
+		return err
+	}
+
+	_, err = s.selectBucketCycle(task.BucketID, now)
+	return err
+}