apex-public/internal/followup/followup.go

package followup

import (
	"database/sql"
	"fmt"
	"sort"
	"strings"
	"time"
)

var Cadence = map[string]time.Duration{
	"Applied":   7 * 24 * time.Hour,
	"Responded": 14 * 24 * time.Hour,
	"Interview": 3 * 24 * time.Hour,
}

type PendingFollowUp struct {
	ApplicationID int64
	Company       string
	Title         string
	Status        string
	StatusSince   time.Time
	DueAt         time.Time
	IsOverdue     bool
}

// Pending returns applications that need follow-up based on cadence.
// Excludes apps with terminal statuses or those already contacted.
func Pending(db *sql.DB) ([]PendingFollowUp, error) {
	now := time.Now()

	rows, err := db.Query(`
		SELECT
			a.id,
			j.company,
			j.title,
			a.status,
			a.updated_at,
			fu.contacted_at
		FROM applications a
		JOIN jobs j ON a.job_id = j.id
		LEFT JOIN follow_ups fu ON a.id = fu.application_id
		WHERE a.status IN ('Applied', 'Responded', 'Interview')
			AND fu.contacted_at IS NULL
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []PendingFollowUp
	for rows.Next() {
		var appID int64
		var company, title, status string
		var updatedAt time.Time
		var contacted sql.NullTime

		if err := rows.Scan(&appID, &company, &title, &status, &updatedAt, &contacted); err != nil {
			return nil, err
		}

		// Skip if already contacted
		if contacted.Valid {
			continue
		}

		cadence, ok := Cadence[status]
		if !ok {
			continue
		}

		dueAt := updatedAt.Add(cadence)
		isOverdue := now.After(dueAt)

		items = append(items, PendingFollowUp{
			ApplicationID: appID,
			Company:       company,
			Title:         title,
			Status:        status,
			StatusSince:   updatedAt,
			DueAt:         dueAt,
			IsOverdue:     isOverdue,
		})
	}

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

	// Sort: overdue first, then by due date
	sort.Slice(items, func(i, j int) bool {
		if items[i].IsOverdue != items[j].IsOverdue {
			return items[i].IsOverdue
		}
		return items[i].DueAt.Before(items[j].DueAt)
	})

	return items, nil
}

// MarkContacted records that a follow-up was sent for an application.
func MarkContacted(db *sql.DB, applicationID int64, notes string) error {
	var exists int
	if err := db.QueryRow(`SELECT 1 FROM applications WHERE id = ?`, applicationID).Scan(&exists); err != nil {
		if err == sql.ErrNoRows {
			return fmt.Errorf("application %d not found", applicationID)
		}
		return fmt.Errorf("check application: %w", err)
	}
	res, err := db.Exec(`
		UPDATE follow_ups
		SET contacted_at = CURRENT_TIMESTAMP, notes = ?
		WHERE application_id = ?
	`, notes, applicationID)
	if err != nil {
		return fmt.Errorf("update follow_ups: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return fmt.Errorf("no follow_up row for application %d", applicationID)
	}
	return nil
}

// Format returns a plain-text summary of pending follow-ups for CLI output.
func Format(items []PendingFollowUp) string {
	if len(items) == 0 {
		return "No pending follow-ups.\n"
	}

	var b strings.Builder
	fmt.Fprintf(&b, "Pending Follow-ups (%d):\n\n", len(items))

	var overdue []PendingFollowUp
	var dueSoon []PendingFollowUp

	for _, item := range items {
		if item.IsOverdue {
			overdue = append(overdue, item)
		} else {
			dueSoon = append(dueSoon, item)
		}
	}

	if len(overdue) > 0 {
		b.WriteString("OVERDUE:\n")
		for _, item := range overdue {
			statusDate := item.StatusSince.Format("2006-01-02")
			dueDate := item.DueAt.Format("2006-01-02")
			fmt.Fprintf(&b, "  %s — %s (%s %s, was due %s)\n",
				item.Company, item.Title, item.Status, statusDate, dueDate)
		}
		b.WriteString("\n")
	}

	if len(dueSoon) > 0 {
		b.WriteString("DUE SOON:\n")
		for _, item := range dueSoon {
			statusDate := item.StatusSince.Format("2006-01-02")
			dueDate := item.DueAt.Format("2006-01-02")
			fmt.Fprintf(&b, "  %s — %s (%s %s, due %s)\n",
				item.Company, item.Title, item.Status, statusDate, dueDate)
		}
	}

	return b.String()
}