# API: Database-backed data loading

This document lists the REST-style endpoints and SQL needed to load application data from the database schema exported to `.github/database.md`. Paths follow the convention:

- `/{resource}`
- `/{resource}/{id}`
- `/{resource}/{id}/subitem`

Each endpoint includes example SQL (SELECT for reads, INSERT/UPDATE where appropriate). SQL uses parameter placeholders (`:id`, `:team_id`, `:leg_id`, etc.) — adapt to your query library's parameter style.

**Categories**
- GET `/categories` : List all categories
  - SQL: `SELECT id, slug, name, border_color, created_at, modified_at FROM categories ORDER BY name;`
- GET `/categories/{id}` : Get a single category
  - SQL: `SELECT id, slug, name, border_color, created_at, modified_at FROM categories WHERE id = :id;`

**Events & Courses**
- GET `/events` : List events
  - SQL: `SELECT id, slug, name, start_time, description, race_start_location_id, created_at FROM events ORDER BY start_time;`
- GET `/events/{id}` : Single event
  - SQL: `SELECT * FROM events WHERE id = :id;`
- GET `/courses` : List courses
  - SQL: `SELECT id, slug, name, distance, event_id, created_at FROM courses ORDER BY name;`
- GET `/courses/{id}` : Single course
  - SQL: `SELECT * FROM courses WHERE id = :id;`

**Legs**
- GET `/legs` : All legs (optionally filtered by `course_id`)
  - SQL: `SELECT id, slug, course_id, name, type, total_checkpoints, order_no, route_gpx, color, distance_text, altitude_gain, start_location_id, end_location_id FROM legs` +
    `WHERE (:course_id IS NULL OR course_id = :course_id) ORDER BY course_id, order_no;`
- GET `/legs/{id}` : Single leg
  - SQL: `SELECT * FROM legs WHERE id = :id;`
- GET `/legs/{id}/checkpoints` : Checkpoints for a leg (ordered)
  - SQL: `SELECT lc.id, lc.leg_id, lc.location_id, lc.order_no, l.name, l.lat, l.lng FROM leg_checkpoints lc` +
    `JOIN locations l ON l.id = lc.location_id WHERE lc.leg_id = :id ORDER BY lc.order_no;`
- GET `/legs/{id}/special_checkpoints` : Leg special checkpoints
  - SQL: `SELECT id, leg_id, name, description, created_at FROM leg_special_checkpoints WHERE leg_id = :id ORDER BY id;`

**Locations**
- GET `/locations` : List locations (optionally by bounding box or type)
  - SQL: `SELECT id, slug, name, short_name, type, lat, lng, images FROM locations WHERE (:type IS NULL OR type = :type);`
- GET `/locations/{id}` : Single location
  - SQL: `SELECT * FROM locations WHERE id = :id;`

**Teams**
- GET `/teams` : List teams (optionally filter by `event_id`, `course_id`, `category_id`)
  - SQL: `SELECT id, slug, name, bib_number, category, course_id, event_id, withdrawn, lat, lng, number, biography, category_id FROM teams` +
    `WHERE (:event_id IS NULL OR event_id = :event_id) AND (:course_id IS NULL OR course_id = :course_id) AND (:category_id IS NULL OR category_id = :category_id) ORDER BY number;`
- GET `/teams/{id}` : Team details
  - SQL: `SELECT * FROM teams WHERE id = :id;`
- GET `/teams/{id}/members` : Team members
  - SQL: `SELECT id, slug, team_id, name, dropped_out, created_at FROM team_members WHERE team_id = :id ORDER BY id;`
- GET `/teams/{id}/messages` : Messages for a team
  - SQL: `SELECT id, team_id, sender, text, timestamp, created_at FROM team_messages WHERE team_id = :id ORDER BY created_at;`

**Team progress & checkpoints**
- GET `/teams/{id}/progress` : All progress rows for a team (per leg)
  - SQL: `SELECT tp.id, tp.team_id, tp.leg_id, tp.checkpoints_collected, tp.finish_time, tp.transition_in, tp.transition_out, tp.meta, tp.created_at` +
    `FROM team_progress tp WHERE tp.team_id = :id ORDER BY tp.leg_id;`
- GET `/team_progress/{id}` : Single team progress entry
  - SQL: `SELECT * FROM team_progress WHERE id = :id;`
- GET `/team_progress/{id}/special_events` : Special checkpoint events for a team_progress row
  - SQL: `SELECT id, team_progress_id, name, occurred_at, checkpoints_before, created_at FROM special_checkpoint_events WHERE team_progress_id = :id ORDER BY occurred_at;`

**Transition times**
- GET `/transition_times` : Optionally filter by `team_id` or `location_id`
  - SQL: `SELECT id, team_id, name, location_id, time, created_at FROM transition_times WHERE (:team_id IS NULL OR team_id = :team_id) AND (:location_id IS NULL OR location_id = :location_id) ORDER BY time;`
- GET `/transition_times/{id}` : Single transition time record
  - SQL: `SELECT * FROM transition_times WHERE id = :id;`

**Team messaging (create/read)**
- POST `/teams/{id}/messages` : Add a message for a team
  - SQL (INSERT): `INSERT INTO team_messages (team_id, sender, text, timestamp, created_at) VALUES (:team_id, :sender, :text, :timestamp, NOW());`
  - SQL (return newly created): `SELECT * FROM team_messages WHERE id = LAST_INSERT_ID();`

**Creating/updating Team progress or transitions**
- POST `/teams/{id}/progress` : Create or update a team progress row
  - Upsert example (MySQL style):
    - SQL (INSERT):
      `INSERT INTO team_progress (team_id, leg_id, checkpoints_collected, finish_time, transition_in, transition_out, meta, created_at, modified_at)`
      `VALUES (:team_id, :leg_id, :checkpoints_collected, :finish_time, :transition_in, :transition_out, :meta, NOW(), NOW())`
      `ON DUPLICATE KEY UPDATE checkpoints_collected = VALUES(checkpoints_collected), finish_time = VALUES(finish_time), transition_in = VALUES(transition_in), transition_out = VALUES(transition_out), meta = VALUES(meta), modified_at = NOW();`
- POST `/teams/{id}/transition_times` : Add a transition time
  - SQL (INSERT): `INSERT INTO transition_times (team_id, name, location_id, time, created_at) VALUES (:team_id, :name, :location_id, :time, NOW());`

**Leg route GPX**
- GET `/legs/{id}/gpx` : Return raw GPX for a leg if present
  - SQL: `SELECT route_gpx FROM legs WHERE id = :id;`

**Useful joins / lookups for map UI**
- GET `/race/overview` : A single query to fetch minimal data for the map (teams, legs, locations)
  - SQL (example, split into three queries for simplicity):
    - Teams: `SELECT id, name, lat, lng, number, category_id FROM teams WHERE withdrawn = 0;`
    - Legs: `SELECT id, course_id, name, order_no, color FROM legs ORDER BY course_id, order_no;`
    - Locations: `SELECT id, name, lat, lng, type FROM locations WHERE lat IS NOT NULL AND lng IS NOT NULL;`

Notes and mappings
- JSON fields: Columns such as `legs.special_checkpoints`, `locations.images`, `team_progress.meta` are stored as JSON; use your driver to JSON-parse them after retrieval.
- Legacy IDs: `slug` fields exist for many tables to map older datasets. Keep them when importing or exporting.
- Indexes: Use the provided indexes in `database.md` (e.g., `idx_teams_slug`, `idx_leg_checkpoints_leg_id`) when writing pagination and filter queries.

Adapt these SQL snippets to your backend (parameter style, prepared statements, pagination). If you want, I can also generate TypeScript/Node.js data-access functions that implement these queries directly using your preferred DB library (e.g., `knex`, `pg`, `mysql2`).

# API definitions and SQL

-- Notes
- All queries assume the database tables shown in the schema: `events`, `courses`, `legs`, `leg_checkpoints`, `leg_special_checkpoints`, `locations`, `teams`, `team_members`, `team_progress`, `special_checkpoint_events`, `transition_times`, `team_messages`, `categories`.
- Use proper indexing (the schema already includes common indexes). Use `LIMIT` + pagination parameters for list endpoints where needed (not shown in every query). by default return 250 items if no limits are shown

---

**Events**
- Path: `GET /events`
  - SQL: SELECT all events (most recent first)
    ```sql
    SELECT `id`, `slug`, `name`, `start_time`, `description`, `race_start_location_id`, `created_at`, `modified_at`
    FROM `events`
    ORDER BY `start_time` DESC;
    ```

- Path: `GET /events/{id}`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `start_time`, `description`, `race_start_location_id`, `created_at`, `modified_at`
    FROM `events`
    WHERE `id` = :id;
    ```

- Path: `GET /events/{id}/courses`  (courses belonging to an event)
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `distance`, `event_id`, `created_at`, `modified_at`
    FROM `courses`
    WHERE `event_id` = :id
    ORDER BY `id`;
    ```

- Path: `GET /events/{id}/teams`  (teams registered for an event)
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `bib_number`, `category`, `course_id`, `event_id`, `withdrawn`, `created_at`, `modified_at`, `lat`, `lng`, `number`, `biography`, `category_id`
    FROM `teams`
    WHERE `event_id` = :id
    ORDER BY `bib_number`;
    ```

---

**Courses**
- Path: `GET /courses`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `distance`, `event_id`, `created_at`, `modified_at`
    FROM `courses`
    ORDER BY `name`;
    ```

- Path: `GET /courses/{id}`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `distance`, `event_id`, `created_at`, `modified_at`
    FROM `courses`
    WHERE `id` = :id;
    ```

- Path: `GET /courses/{id}/legs`
  - SQL: retrieve ordered legs for a course
    ```sql
    SELECT `id`, `slug`, `course_id`, `name`, `type`, `total_checkpoints`, `order_no` AS `order`, `ends_at`, `special_checkpoints`, `route_gpx`, `color`, `distance_text`, `altitude_gain`, `start_location_id`, `end_location_id`, `created_at`, `modified_at`
    FROM `legs`
    WHERE `course_id` = :id
    ORDER BY `order_no` ASC;
    ```

---

**Legs**
- Path: `GET /legs/{id}`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `course_id`, `name`, `type`, `total_checkpoints`, `order_no` AS `order`, `ends_at`, `special_checkpoints`, `route_gpx`, `color`, `distance_text`, `altitude_gain`, `start_location_id`, `end_location_id`, `created_at`, `modified_at`
    FROM `legs`
    WHERE `id` = :id;
    ```

- Path: `GET /legs/{id}/checkpoints`
  - SQL: join to `locations` to include place details
    ```sql
    SELECT lc.`id`, lc.`leg_id`, lc.`location_id`, lc.`order_no`, l.`name` AS `location_name`, l.`short_name`, l.`type` AS `location_type`, l.`lat`, l.`lng`, l.`images`
    FROM `leg_checkpoints` lc
    JOIN `locations` l ON lc.`location_id` = l.`id`
    WHERE lc.`leg_id` = :id
    ORDER BY lc.`order_no` ASC;
    ```

- Path: `GET /legs/{id}/special_checkpoints`
  - SQL:
    ```sql
    SELECT `id`, `leg_id`, `name`, `description`, `created_at`, `modified_at`
    FROM `leg_special_checkpoints`
    WHERE `leg_id` = :id
    ORDER BY `name`;
    ```

---

**Locations**
- Path: `GET /locations`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `short_name`, `type`, `lat`, `lng`, `images`, `created_at`, `modified_at`
    FROM `locations`
    ORDER BY `name`;
    ```

- Path: `GET /locations/{id}`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `short_name`, `type`, `lat`, `lng`, `images`, `created_at`, `modified_at`
    FROM `locations`
    WHERE `id` = :id;
    ```

---

**Categories**
- Path: `GET /categories`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `border_color`, `created_at`, `modified_at`
    FROM `categories`
    ORDER BY `name`;
    ```

- Path: `GET /categories/{id}`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `border_color`, `created_at`, `modified_at`
    FROM `categories`
    WHERE `id` = :id;
    ```

---

**Teams**
- Path: `GET /teams` (optionally filter by `eventId`, `courseId`, `category`, `withdrawn`)
  - SQL (basic):
    ```sql
    SELECT `id`, `slug`, `name`, `bib_number`, `category`, `course_id`, `event_id`, `withdrawn`, `created_at`, `modified_at`, `lat`, `lng`, `number`, `biography`, `category_id`
    FROM `teams`
    /* Add WHERE clauses when filters are provided, e.g.: WHERE `event_id` = :eventId */
    ORDER BY `bib_number`;
    ```

- Path: `GET /teams/{id}`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `name`, `bib_number`, `category`, `course_id`, `event_id`, `withdrawn`, `created_at`, `modified_at`, `lat`, `lng`, `number`, `biography`, `category_id`
    FROM `teams`
    WHERE `id` = :id;
    ```

- Path: `GET /teams/{id}/members`
  - SQL:
    ```sql
    SELECT `id`, `slug`, `team_id`, `name`, `dropped_out`, `created_at`, `modified_at`
    FROM `team_members`
    WHERE `team_id` = :id
    ORDER BY `id`;
    ```

- Path: `GET /teams/{id}/messages`
  - SQL:
    ```sql
    SELECT `id`, `team_id`, `sender`, `text`, `timestamp`, `created_at`
    FROM `team_messages`
    WHERE `team_id` = :id
    ORDER BY `created_at` ASC;
    ```

- Path: `GET /teams/{id}/transitions`
  - SQL:
    ```sql
    SELECT `id`, `team_id`, `name`, `location_id`, `time`, `created_at`, `modified_at`
    FROM `transition_times`
    WHERE `team_id` = :id
    ORDER BY `time` ASC;
    ```

---

**Team Progress**
- Path: `GET /team_progress` (global list, filterable)
  - SQL (example: filter by `team_id` or `leg_id`):
    ```sql
    SELECT `id`, `team_id`, `leg_id`, `checkpoints_collected`, `finish_time`, `transition_in`, `transition_out`, `meta`, `created_at`, `modified_at`
    FROM `team_progress`
    /* WHERE `team_id` = :teamId OR `leg_id` = :legId */
    ORDER BY `team_id`, `leg_id`;
    ```

- Path: `GET /team_progress/{id}`
  - SQL:
    ```sql
    SELECT `id`, `team_id`, `leg_id`, `checkpoints_collected`, `finish_time`, `transition_in`, `transition_out`, `meta`, `created_at`, `modified_at`
    FROM `team_progress`
    WHERE `id` = :id;
    ```

- Path: `GET /teams/{teamId}/progress`  (progress entries for a team, ordered by leg order)
  - SQL: join to `legs` to order and include leg metadata
    ```sql
    SELECT tp.`id`, tp.`team_id`, tp.`leg_id`, l.`name` AS `leg_name`, l.`order_no` AS `leg_order`, tp.`checkpoints_collected`, tp.`finish_time`, tp.`transition_in`, tp.`transition_out`, tp.`meta`, tp.`created_at`, tp.`modified_at`
    FROM `team_progress` tp
    JOIN `legs` l ON tp.`leg_id` = l.`id`
    WHERE tp.`team_id` = :teamId
    ORDER BY l.`order_no` ASC;
    ```

- Path: `GET /team_progress/{id}/special_checkpoint_events`
  - SQL:
    ```sql
    SELECT `id`, `team_progress_id`, `name`, `occurred_at`, `checkpoints_before`, `created_at`, `modified_at`
    FROM `special_checkpoint_events`
    WHERE `team_progress_id` = :id
    ORDER BY `occurred_at` ASC;
    ```

---

**Misc / Utilities**
- Path: `GET /courses/{id}/summary` (aggregate course-level info such as total legs and total checkpoints)
  - SQL (example aggregate):
    ```sql
    SELECT c.`id` AS `course_id`, c.`name` AS `course_name`, COUNT(l.`id`) AS `legs_count`, COALESCE(SUM(l.`total_checkpoints`),0) AS `total_checkpoints`
    FROM `courses` c
    LEFT JOIN `legs` l ON c.`id` = l.`course_id`
    WHERE c.`id` = :id
    GROUP BY c.`id`;
    ```

- Path: `GET /teams/{id}/status` (computed current status — example outline)
  - Implementation: server composes data from `team_progress` (latest leg/finish_time), `transition_times` (last transition), and `teams` (withdrawn flag). Example queries combine the above; exact SQL depends on desired status logic.

---

Performance & security notes
- Use prepared statements for all parameterized queries.
- Add pagination (`LIMIT` + `OFFSET`) for list endpoints to avoid returning huge result sets.
- Use the existing indexes (e.g. `idx_*` in the schema) and add additional indexes if you plan to heavily filter by fields that are not currently indexed.
- For APIs returning JSON fields (e.g., `legs.special_checkpoints`, `locations.images`, `team_progress.meta`) parse them in application code; MariaDB supports JSON functions if using JSON typed columns.

If you want, I can also add example request/response payloads for each endpoint (matching `src/types/mockData.ts`) or generate a small OpenAPI (Swagger) YAML based on these queries.