compass_teams_nhs/internal/store/codes.go

package store

import (
	"context"
	"fmt"
	"strings"
)

// This file owns the editable decode map (AGENTS.md §4 FR-17, §7): the raw
// Compass subject, room, teacher and period codes and the plain-English names
// they render as. The map is data because the codes change between years and
// new subjects appear each term; the seed below only fills gaps, so a
// correction Sam makes in the dashboard is never overwritten.

// Code kinds stored in the codes table.
const (
	CodeSubject = "subject"
	CodeRoom    = "room"
	CodeTeacher = "teacher"
	CodePeriod  = "period"
)

// CodeMap is the whole decode table, grouped by kind. A missing code is not an
// error: the renderer falls back to the raw code (never blank).
type CodeMap struct {
	Subject map[string]string
	Room    map[string]string
	Teacher map[string]string
	Period  map[string]string
}

// Lookup returns the mapped name for one kind and code, or "" when the code is
// not mapped.
func (m CodeMap) Lookup(kind, code string) string {
	code = strings.TrimSpace(code)
	switch kind {
	case CodeSubject:
		return m.Subject[code]
	case CodeRoom:
		return m.Room[code]
	case CodeTeacher:
		return m.Teacher[code]
	case CodePeriod:
		return m.Period[code]
	default:
		return ""
	}
}

// seedCodes are the mappings the project ships with. Subjects use the school's
// own names; rooms are "Room <code>"; teacher entries are placeholders because
// Compass only supplies initials, so Sam replaces them with surnames.
var seedCodes = []struct {
	Kind string
	Code string
	Name string
}{
	// Subjects seen in the Phase 2/4 captures (AGENTS.md §7 seed table plus the
	// real captures).
	{CodeSubject, "7SCF", "Year 7 Science"},
	{CodeSubject, "7DESF", "Year 7 Design"},
	{CodeSubject, "7TUTF", "Year 7 Tutorial"},
	{CodeSubject, "7ENF", "Year 7 English"},
	{CodeSubject, "7PEF", "Year 7 Physical Education"},
	{CodeSubject, "7DRAF", "Year 7 Drama"},
	{CodeSubject, "7HU", "Year 7 Humanities"},
	{CodeSubject, "7EN", "Year 7 English"},
	{CodeSubject, "7PE", "Year 7 Physical Education"},
	{CodeSubject, "7CHI", "Year 7 Chinese"},
	{CodeSubject, "11MGMAC2", "Year 11 General Maths"},
	{CodeSubject, "11XMUSA6", "Year 11 Music"},
	{CodeSubject, "11SBIOA1", "Year 11 Biology"},
	{CodeSubject, "11HLEGB5", "Year 11 Legal Studies"},
	{CodeSubject, "11AACPA4", "Year 11 Art Creative Practice"},
	{CodeSubject, "11EENGB3", "Year 11 English"},
	{CodeSubject, "11TUTB", "Year 11 Tutorial"},
	{CodeSubject, "11MGMA", "Year 11 General Maths"},
	{CodeSubject, "11XMUS", "Year 11 Music"},
	{CodeSubject, "11SBIO", "Year 11 Biology"},
	{CodeSubject, "11HLEG", "Year 11 Legal Studies"},
	{CodeSubject, "11EENG", "Year 11 English"},
	{CodeSubject, "11AACP", "Year 11 Art Creative Practice"},

	// Rooms seen in the captures (AGENTS.md §7: B11 / M6 / A1 read as
	// "Room B11" / "Room M6" / "Room A1").
	{CodeRoom, "H14LAB", "Room H14LAB"},
	{CodeRoom, "B11", "Room B11"},
	{CodeRoom, "M6", "Room M6"},
	{CodeRoom, "A1", "Room A1"},
	{CodeRoom, "G7", "Room G7"},
	{CodeRoom, "B1", "Room B1"},
	{CodeRoom, "C1", "Room C1"},
	{CodeRoom, "H3", "Room H3"},
	{CodeRoom, "M4", "Room M4"},
	{CodeRoom, "D8", "Room D8"},
	{CodeRoom, "IM1", "Room IM1"},
	{CodeRoom, "IM3", "Room IM3"},
	{CodeRoom, "IM5", "Room IM5"},
	{CodeRoom, "B13", "Room B13"},
	{CodeRoom, "GYM4", "Gym 4"},
	{CodeRoom, "THEATRE", "Theatre"},
	{CodeRoom, "HALL", "Hall"},
	{CodeRoom, "ENS", "Ensemble Room"},

	// Teacher initials. Compass gives only initials, so these are placeholders
	// in the AGENTS.md "Mr GT" form; Sam replaces them with surnames.
	{CodeTeacher, "KNI", "Mr KNI"},
	{CodeTeacher, "PSI", "Mr PSI"},
	{CodeTeacher, "KDI", "Mr KDI"},
	{CodeTeacher, "AFR", "Mr AFR"},
	{CodeTeacher, "LHT", "Mr LHT"},
	{CodeTeacher, "RLE", "Mr RLE"},
	{CodeTeacher, "JBR", "Mr JBR"},
	{CodeTeacher, "GT", "Mr GT"},
	{CodeTeacher, "ANE", "Mr ANE"},
	{CodeTeacher, "BDR", "Mr BDR"},
	{CodeTeacher, "CHA", "Mr CHA"},
	{CodeTeacher, "HZH", "Mr HZH"},
	{CodeTeacher, "SLW", "Mr SLW"},
	{CodeTeacher, "KWO", "Mr KWO"},
	{CodeTeacher, "HHL", "Mr HHL"},
	{CodeTeacher, "OFG", "Mr OFG"},
	{CodeTeacher, "DCA", "Mr DCA"},
	{CodeTeacher, "LTA", "Mr LTA"},
	{CodeTeacher, "KSH", "Mr KSH"},
	{CodeTeacher, "CFR", "Mr CFR"},
	{CodeTeacher, "CMA", "Mr CMA"},
	{CodeTeacher, "KSM", "Mr KSM"},

	// Period labels that are words rather than numbers (AGENTS.md §7: C and
	// "RLE GROUP D" are tutorials, Connect or assembly). A number is rendered
	// as "Period N" without a table entry; anything else falls back to raw.
	{CodePeriod, "C", "Connect"},
	{CodePeriod, "RLE GROUP D", "RLE Group D"},
}

// SeedCodes inserts the shipped mappings once, when the map is still empty. It
// never overwrites a value, so Sam's corrections survive every start and every
// scan — and because it is a one-time bootstrap, a mapping Sam deletes in the
// dashboard stays deleted instead of reappearing on the next read.
func (s *Store) SeedCodes(ctx context.Context) error {
	var n int
	if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM codes`).Scan(&n); err != nil {
		return fmt.Errorf("seed codes: count: %w", err)
	}
	if n > 0 {
		return nil
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("seed codes: begin: %w", err)
	}
	defer func() { _ = tx.Rollback() }()
	for _, c := range seedCodes {
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO codes (kind, code, name) VALUES (?, ?, ?)
			 ON CONFLICT(kind, code) DO NOTHING`,
			c.Kind, c.Code, c.Name); err != nil {
			return fmt.Errorf("seed code %s %q: %w", c.Kind, c.Code, err)
		}
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("seed codes: commit: %w", err)
	}
	return nil
}

// Codes returns the whole decode map. It seeds the shipped defaults first (once)
// so a fresh database renders the known captures, and a corrected or deleted row
// is kept.
func (s *Store) Codes(ctx context.Context) (CodeMap, error) {
	if err := s.SeedCodes(ctx); err != nil {
		return CodeMap{}, err
	}
	rows, err := s.db.QueryContext(ctx, `SELECT kind, code, name FROM codes`)
	if err != nil {
		return CodeMap{}, fmt.Errorf("codes: %w", err)
	}
	defer rows.Close()

	out := CodeMap{
		Subject: map[string]string{},
		Room:    map[string]string{},
		Teacher: map[string]string{},
		Period:  map[string]string{},
	}
	for rows.Next() {
		var kind, code, name string
		if err := rows.Scan(&kind, &code, &name); err != nil {
			return CodeMap{}, fmt.Errorf("scan code: %w", err)
		}
		switch kind {
		case CodeSubject:
			out.Subject[code] = name
		case CodeRoom:
			out.Room[code] = name
		case CodeTeacher:
			out.Teacher[code] = name
		case CodePeriod:
			out.Period[code] = name
		}
	}
	return out, rows.Err()
}

// Code is one row of the decode map, for the `school codes` CLI.
type Code struct {
	Kind string `json:"kind"`
	Code string `json:"code"`
	Name string `json:"name"`
}

// ListCodes returns every mapping, ordered by kind then code.
func (s *Store) ListCodes(ctx context.Context) ([]Code, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT kind, code, name FROM codes ORDER BY kind, code`)
	if err != nil {
		return nil, fmt.Errorf("list codes: %w", err)
	}
	defer rows.Close()

	var out []Code
	for rows.Next() {
		var c Code
		if err := rows.Scan(&c.Kind, &c.Code, &c.Name); err != nil {
			return nil, fmt.Errorf("scan code: %w", err)
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// SetCode creates or corrects one mapping and returns the row. It is how Sam
// edits the decode map (FR-17).
func (s *Store) SetCode(ctx context.Context, kind, code, name string) (Code, error) {
	kind = strings.ToLower(strings.TrimSpace(kind))
	code = strings.TrimSpace(code)
	name = strings.TrimSpace(name)
	switch kind {
	case CodeSubject, CodeRoom, CodeTeacher, CodePeriod:
	default:
		return Code{}, fmt.Errorf("unknown code kind %q (want subject, room, teacher or period)", kind)
	}
	if code == "" {
		return Code{}, fmt.Errorf("code is required")
	}
	if name == "" {
		return Code{}, fmt.Errorf("name is required (an empty mapping renders blank)")
	}
	if _, err := s.db.ExecContext(ctx,
		`INSERT INTO codes (kind, code, name, updated_at) VALUES (?, ?, ?, datetime('now'))
		 ON CONFLICT(kind, code) DO UPDATE SET name = excluded.name, updated_at = datetime('now')`,
		kind, code, name); err != nil {
		return Code{}, fmt.Errorf("set code %s %q: %w", kind, code, err)
	}
	return Code{Kind: kind, Code: code, Name: name}, nil
}

// DeleteCode removes one mapping, so the code falls back to raw. It reports
// whether a row existed.
func (s *Store) DeleteCode(ctx context.Context, kind, code string) (bool, error) {
	res, err := s.db.ExecContext(ctx, `DELETE FROM codes WHERE kind = ? AND code = ?`,
		strings.ToLower(strings.TrimSpace(kind)), strings.TrimSpace(code))
	if err != nil {
		return false, fmt.Errorf("delete code: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return false, err
	}
	return n > 0, nil
}