compass_teams_nhs/internal/store/calendar.go

package store

import (
	"context"
	"database/sql"
	"fmt"
	"time"
)

// This file holds the Phase 6 calendar helpers (AGENTS.md §4 FR-8, §11 Phase 6):
// the events that are worth writing to Sam's Home Assistant calendar, the mark
// that records the Home Assistant event id on the item, and the append-only
// push log that makes a failed write loud (FR-12).

// calendarSelect extends the shared item column list with the Home Assistant
// columns, so a candidate carries its own push state.
const calendarSelect = `SELECT i.id, i.child_id, COALESCE(ch.name, ''), i.source, i.type,
        COALESCE(i.subject, ''), COALESCE(i.teacher, ''), i.title,
        COALESCE(i.body, ''), i.due_at, COALESCE(i.url, ''),
        COALESCE(i.source_page, ''), COALESCE(i.importance, ''), COALESCE(i.external_ref, ''),
        COALESCE(i.ha_event_uid, ''), i.ha_pushed_at
   FROM items i LEFT JOIN children ch ON ch.id = i.child_id`

// CalendarItem is one item plus its Home Assistant push state.
type CalendarItem struct {
	Item
	// HAEventUID is the Home Assistant event id once the item is in the
	// calendar ("" until then).
	HAEventUID string
	// HAPushedAt is when the item was pushed, or nil.
	HAPushedAt *time.Time
}

// CalendarCandidates returns the important school events that belong in the
// calendar: Compass events marked "high" by the Phase 3 classifier, starting at
// or after from, newest-first excluded. childID and itemID narrow the set, and
// limit caps it (0 means no cap).
//
// An item that already carries a Home Assistant event id is not a candidate:
// the write is idempotent, so a re-run must not duplicate it.
func (s *Store) CalendarCandidates(ctx context.Context, childID, itemID *int64, from time.Time, limit int) ([]CalendarItem, error) {
	q := calendarSelect + `
        WHERE i.type = 'event'
          AND i.source = 'compass'
          AND i.importance = 'high'
          AND COALESCE(i.ha_event_uid, '') = ''
          AND i.due_at IS NOT NULL
          AND i.due_at >= ?`
	args := []any{from.UTC().Format(time.RFC3339)}
	q, args = narrowCalendarQuery(q, args, childID, itemID)
	q += ` ORDER BY i.due_at ASC, i.id ASC`
	q, args = capCalendarQuery(q, args, limit)
	return s.queryCalendarItems(ctx, q, args)
}

// CalendarPeriodCandidates returns the per-child periods for one day that are
// not in the calendar yet. They come from the child's own day widget
// (source_page child-profile / learning-tasks), never from the school-wide week
// calendar (FR-16). A period is a routine lesson, so it carries importance
// "normal"; school events are written by CalendarCandidates.
func (s *Store) CalendarPeriodCandidates(ctx context.Context, childID, itemID *int64, dayStart, dayEnd time.Time, limit int) ([]CalendarItem, error) {
	q := calendarSelect + `
        WHERE i.type = 'event'
          AND i.source = 'compass'
          AND i.importance = 'normal'
          AND COALESCE(i.ha_event_uid, '') = ''
          AND i.source_page IN ('child-profile', 'learning-tasks')
          AND i.due_at IS NOT NULL
          AND i.due_at >= ? AND i.due_at < ?`
	args := []any{dayStart.UTC().Format(time.RFC3339), dayEnd.UTC().Format(time.RFC3339)}
	q, args = narrowCalendarQuery(q, args, childID, itemID)
	q += ` ORDER BY i.due_at ASC, i.id ASC`
	q, args = capCalendarQuery(q, args, limit)
	return s.queryCalendarItems(ctx, q, args)
}

// CalendarTaskCandidates returns the outstanding work that carries a due date
// and is not in the calendar yet: the work due in the future and the work
// overdue (FR-8). Submitted work is filtered by the caller, which holds the
// body-status rule.
func (s *Store) CalendarTaskCandidates(ctx context.Context, childID, itemID *int64, limit int) ([]CalendarItem, error) {
	q := calendarSelect + `
        WHERE i.type = 'task'
          AND i.source = 'compass'
          AND COALESCE(i.ha_event_uid, '') = ''
          AND i.due_at IS NOT NULL`
	args := []any{}
	q, args = narrowCalendarQuery(q, args, childID, itemID)
	q += ` ORDER BY i.due_at ASC, i.id ASC`
	q, args = capCalendarQuery(q, args, limit)
	return s.queryCalendarItems(ctx, q, args)
}

// narrowCalendarQuery appends the optional child and item filters.
func narrowCalendarQuery(q string, args []any, childID, itemID *int64) (string, []any) {
	if childID != nil {
		q += ` AND i.child_id = ?`
		args = append(args, *childID)
	}
	if itemID != nil {
		q += ` AND i.id = ?`
		args = append(args, *itemID)
	}
	return q, args
}

func capCalendarQuery(q string, args []any, limit int) (string, []any) {
	if limit > 0 {
		q += ` LIMIT ?`
		args = append(args, limit)
	}
	return q, args
}

// queryCalendarItems runs a candidate query and reads every row.
func (s *Store) queryCalendarItems(ctx context.Context, q string, args []any) ([]CalendarItem, error) {
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("calendar candidates: %w", err)
	}
	defer rows.Close()

	var out []CalendarItem
	for rows.Next() {
		var it CalendarItem
		var due, pushed sql.NullString
		if err := rows.Scan(&it.ID, &it.ChildID, &it.Child, &it.Source, &it.Type,
			&it.Subject, &it.Teacher, &it.Title, &it.Body, &due, &it.URL,
			&it.SourcePage, &it.Importance, &it.ExternalRef,
			&it.HAEventUID, &pushed); err != nil {
			return nil, fmt.Errorf("scan calendar item: %w", err)
		}
		if due.Valid {
			t := parseTime(due.String)
			it.DueAt = &t
		}
		if pushed.Valid {
			t := parseTime(pushed.String)
			it.HAPushedAt = &t
		}
		out = append(out, it)
	}
	return out, rows.Err()
}

// CalendarItemByID returns one item with its Home Assistant push state, or nil
// when it does not exist. The item screen uses it to show the calendar status.
func (s *Store) CalendarItemByID(ctx context.Context, id int64) (*CalendarItem, error) {
	row := s.db.QueryRowContext(ctx, calendarSelect+` WHERE i.id = ?`, id)

	var it CalendarItem
	var due, pushed sql.NullString
	err := row.Scan(&it.ID, &it.ChildID, &it.Child, &it.Source, &it.Type,
		&it.Subject, &it.Teacher, &it.Title, &it.Body, &due, &it.URL,
		&it.SourcePage, &it.Importance, &it.ExternalRef,
		&it.HAEventUID, &pushed)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("item %d: %w", id, err)
	}
	if due.Valid {
		t := parseTime(due.String)
		it.DueAt = &t
	}
	if pushed.Valid {
		t := parseTime(pushed.String)
		it.HAPushedAt = &t
	}
	return &it, nil
}

// DeleteChildRoutineCalendarEvents removes a child's routine class periods that
// came from the school-wide week calendar (source_page "calendar", importance
// "normal"). The week calendar's lesson grid belongs to the eldest child, so
// storing it against a younger child is a leak (FR-16); the child's own day
// widget is the per-child source. Real school events (importance "high") were
// written with the same page and are kept.
func (s *Store) DeleteChildRoutineCalendarEvents(ctx context.Context, childID int64) (int64, error) {
	res, err := s.db.ExecContext(ctx,
		`DELETE FROM items
		 WHERE child_id = ? AND type = 'event' AND source_page = 'calendar' AND importance = 'normal'`,
		childID)
	if err != nil {
		return 0, fmt.Errorf("delete routine calendar events: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return n, nil
}

// DeleteChildDayWidgetEvents removes a child's previously parsed day-widget
// periods so a re-parse replaces the snapshot instead of accumulating stale
// days or wrong times. It only touches events from the child's own pages.
func (s *Store) DeleteChildDayWidgetEvents(ctx context.Context, childID int64) (int64, error) {
	res, err := s.db.ExecContext(ctx,
		`DELETE FROM items
		 WHERE child_id = ? AND type = 'event' AND source_page IN ('child-profile', 'learning-tasks')`,
		childID)
	if err != nil {
		return 0, fmt.Errorf("delete day-widget events: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return n, nil
}

// MarkItemCalendarSynced records the Home Assistant event id and the push time
// on one item. After this the item is no longer a calendar candidate.
func (s *Store) MarkItemCalendarSynced(ctx context.Context, itemID int64, uid string, at time.Time) error {
	if _, err := s.db.ExecContext(ctx,
		`UPDATE items SET ha_event_uid = ?, ha_pushed_at = ?, updated_at = datetime('now') WHERE id = ?`,
		uid, at.UTC().Format(time.RFC3339), itemID); err != nil {
		return fmt.Errorf("mark item %d calendar-synced: %w", itemID, err)
	}
	return nil
}

// CalendarPush is one row of the append-only calendar push log.
type CalendarPush struct {
	ID        int64     `json:"id"`
	ItemID    int64     `json:"item_id"`
	EntityID  string    `json:"entity_id"`
	UID       string    `json:"uid,omitempty"`
	OK        bool      `json:"ok"`
	Error     string    `json:"error,omitempty"`
	CreatedAt time.Time `json:"created_at"`
}

// CalendarPushStatus summarises the push log for the dashboard. A failed push is
// shown as an explicit failure and is never swallowed (FR-12).
type CalendarPushStatus struct {
	// EverRun is false until the first push is recorded.
	EverRun bool `json:"ever_run"`
	// Total is the number of push rows.
	Total int `json:"total"`
	// Failed is the number of rows with ok = 0.
	Failed int `json:"failed"`
	// Last is the most recent push, when one exists.
	Last *CalendarPush `json:"last,omitempty"`
}

// RecordCalendarPush appends one push attempt. ok is false with an error message
// for a failed write, which the dashboard then shows.
func (s *Store) RecordCalendarPush(ctx context.Context, itemID int64, entityID, uid string, ok bool, errMsg string) (int64, error) {
	var errVal any
	if errMsg != "" {
		errVal = errMsg
	}
	res, err := s.db.ExecContext(ctx,
		`INSERT INTO calendar_pushes (item_id, entity_id, uid, ok, error) VALUES (?, ?, ?, ?, ?)`,
		itemID, entityID, uid, ok, errVal)
	if err != nil {
		return 0, fmt.Errorf("record calendar push for item %d: %w", itemID, err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("record calendar push for item %d: %w", itemID, err)
	}
	return id, nil
}

// LastCalendarPush returns the most recent push row, or nil when none exists.
func (s *Store) LastCalendarPush(ctx context.Context) (*CalendarPush, error) {
	row := s.db.QueryRowContext(ctx,
		`SELECT id, item_id, entity_id, COALESCE(uid, ''), ok, COALESCE(error, ''), created_at
		 FROM calendar_pushes ORDER BY id DESC LIMIT 1`)
	p, err := scanCalendarPush(row)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

// CalendarPushSummary summarises the push log (FR-12).
func (s *Store) CalendarPushSummary(ctx context.Context) (CalendarPushStatus, error) {
	var out CalendarPushStatus
	row := s.db.QueryRowContext(ctx,
		`SELECT COUNT(*), COALESCE(SUM(CASE WHEN ok = 0 THEN 1 ELSE 0 END), 0) FROM calendar_pushes`)
	if err := row.Scan(&out.Total, &out.Failed); err != nil {
		return out, fmt.Errorf("calendar push summary: %w", err)
	}
	out.EverRun = out.Total > 0
	last, err := s.LastCalendarPush(ctx)
	if err != nil {
		return out, err
	}
	out.Last = last
	return out, nil
}

func scanCalendarPush(r rowScanner) (CalendarPush, error) {
	var p CalendarPush
	var created string
	var uid, errMsg sql.NullString
	var ok int
	if err := r.Scan(&p.ID, &p.ItemID, &p.EntityID, &uid, &ok, &errMsg, &created); err != nil {
		return p, err
	}
	p.UID = uid.String
	p.OK = ok != 0
	p.Error = errMsg.String
	p.CreatedAt = parseTime(created)
	return p, nil
}