# Event History: Website Data Contract

Use this reference for model/query/statistics changes. The database is already deployed; do not add DDL to the website task.

## Boundary

`event_history` and `event_history_players` are standalone CMS/MySQL snapshots. Completed pages must never read `EntitiesModel`, `GuildsModel`, `.ini`, `.bin`, game tables, or current names/memberships.

```mermaid
flowchart LR
    H[event_history] -->|id = event_id| P[event_history_players]
    P --> S[Website snapshot model]
    S --> E[Event list]
    S --> G[Guild/player drill-down]
    S --> C[Guild CSV]
    L[Live player/guild data] -. forbidden .-> S
```

## Tables Used

### `event_history`

| Column | Website use |
| --- | --- |
| `id` | Required navigation/cache identity |
| `event_type` | Available event metadata |
| `event_variant` | Available event metadata |
| `event_name` | Display/filename context |
| `event_hour` | Server-local displayed event date/time |
| `expected_end_at` | Not required by the page |
| `status` | `in_progress` or `completed`; controls access |
| `started_at`, `completed_at` | Available event metadata |
| `created_at`, `updated_at` | Not displayed/exported |

### `event_history_players`

| Group | Columns |
| --- | --- |
| Identity | `id`, `event_id`, `player_id`, `player_name`, `guild_id`, `guild_name` |
| Time | `event_map_seconds` |
| Kills | `kills_same_guild`, `kills_enemy_guild`, `kills_allied_guild`, `kills_neutral` |
| Deaths | `deaths_same_guild`, `deaths_enemy_guild`, `deaths_allied_guild`, `deaths_neutral`, `deaths_non_player` |
| Revives | `manual_revives`, `water_revives_given`, `water_revives_received` |
| Audit | `created_at`, `updated_at` |

Website consumers identify a snapshot with `event_id + player_id`; never depend on player-row `id`, which may change after server `REPLACE`.

Jail time is absent from the schema. Do not infer, derive, display, export, or reserve a fake field for it.

## Derived Values

Compute the same aliases in every player-returning model query:

```text
kills = kills_same_guild
      + kills_enemy_guild
      + kills_allied_guild
      + kills_neutral

deaths = deaths_same_guild
       + deaths_enemy_guild
       + deaths_allied_guild
       + deaths_neutral
       + deaths_non_player

times_revived = manual_revives
              + water_revives_received

revives_given = water_revives_given
```

`manual_revives` describes the player's manual self-revive. `water_revives_received` contributes to times the player was revived. `water_revives_given` remains a separate activity total.

```mermaid
flowchart TD
    K1[4 raw kill columns] --> K[Kills]
    D1[5 raw death columns] --> D[Deaths]
    M[manual_revives] --> R[Times Revived]
    WR[water_revives_received] --> R
    WG[water_revives_given] --> RG[Revives Given]
```

## Grouping and Ordering

- Events: `event_hour DESC, id DESC`.
- Guild identity: `event_id + guild_id`; retain snapshot name and count players.
- Guild order: named guilds by `guild_name ASC, guild_id ASC`, then `guild_id=0`.
- Players: `kills DESC, deaths ASC, player_name ASC, player_id ASC`.
- `guild_id=0` plus empty name is valid and presented as `No Guild`; do not discard those players.
- A completed event may validly contain zero players.

Indexes already supporting the page:

- `event_history`: primary `id`, date index, status/lookup indexes.
- `event_history_players`: unique `(event_id, player_id)` and index `(event_id, guild_id)`.

## Immutability

Completed snapshots are permanent. Perpetual CSV caching relies on the server contract that completed event/player records are not changed or deleted. If that contract changes later, add explicit cache versioning/invalidation before allowing edits.