# barox bugs

Webanwendung zur Verfolgung der Bugs und Feature-Requests, die unser Lieferant
(RY) als Excel-Liste liefert. Jede neue Ausgabe wird hochgeladen und gegen den
bestehenden Datenbestand abgeglichen: neue Tickets kommen hinzu, bekannte werden
aktualisiert, und jede Änderung an Status, Zieltermin oder Bemerkung bleibt
nachvollziehbar.

- **Domain:** https://bugs.barox.io
- **Serverpfad:** `/var/www/html/barox-bugs`
- **Stack:** Apache2 + PHP 8.2+ + MySQL/MariaDB, TLS über nginx-Reverse-Proxy
- **Anmeldung:** Microsoft 365 / Entra ID (OIDC Authorization Code Flow)
- **Sprachen:** Deutsch und Englisch, pro Benutzer umschaltbar
- **Datenbank:** `barox_bugs`, Benutzer `barox`

Bewusst **ohne Composer** gebaut – eigener Autoloader, eigener OIDC-Client,
eigener XLSX-Leser (analog zu barox-cve und barox-transfer).

---

## 1. Wie die Lieferantendatei gelesen wird

Die Datei ist eine gepflegte Excel-Liste, kein Datenbankexport. Bedeutung steckt
an drei Stellen, die alle ausgewertet werden:

| Quelle | Bedeutung in der Anwendung |
|---|---|
| Ticketnummer-Präfix | `25702` = Bug · `S…` = Feature-Request · `W…` = Änderung |
| Spalte `Status/ETD` | Kette der Zieldaten; `=>` = Terminverschiebung, `+` = zusätzliches Release. Der letzte Eintrag ist der aktuelle Termin. |
| **Schriftfarbe** | Legende im Tabellenblatt: grün = erledigt, rot = verschoben, blau = in dieser Ausgabe aktualisiert |

Die Farbe ist deshalb wichtig: ob eine Firmware freigegeben ist, steht in der
Datei **nur** als grüne Schrift, nicht als Text. Gelesen werden beide Ebenen –
die Farbe des Zellenformats und die Farben einzelner Textabschnitte
(Rich-Text-Runs), damit z. B. die neueste blau markierte Remark-Zeile erkannt
wird.

Aus `Status/ETD` und den Farben leitet die Anwendung einen Status ab:
Freigegeben · Termin geplant · Termin verschoben · Termin offen ·
Abgeschlossen · Zurückgezogen.

Die Spalte `Remark` wird in Einzelmeldungen zerlegt (`8/27: …`). Das in der Datei
fehlende Jahr wird aus dem Stand der Ausgabe ergänzt. Diese Meldungen sind
**append-only**: sie bleiben erhalten, auch wenn der Lieferant die Historie in
einer späteren Ausgabe kürzt.

Der **Stand der Ausgabe** kommt aus dem Dateinamen
(`Barox Tickets_20260827.xlsx` → 27.08.2026) und bestimmt die Reihenfolge der
Ausgaben – nicht der Upload-Zeitpunkt.

## 2. Was beim Einlesen passiert

| Fall | Verhalten |
|---|---|
| Unbekannte Ticketnummer | neues Ticket |
| Bekanntes Ticket, verändert | aktualisiert, jede geänderte Angabe wird protokolliert |
| Bekanntes Ticket, unverändert | nur „zuletzt bestätigt“ wird nachgezogen |
| Ticket fehlt in der Ausgabe | als „nicht mehr gelistet“ markiert – **nie gelöscht** |
| Ticket kommt wieder vor | wieder aufgenommen, mit Vermerk |

Zwei Sonderfälle werden abgefangen, statt Daten zu beschädigen:

- **Bereits eingelesene Datei** (gleicher SHA-256): wird abgewiesen; ein
  erneutes Einlesen ist nach Bestätigung möglich.
- **Ältere Ausgabe** als der aktuelle Datenstand: wird abgewiesen, weil sie
  neuere Angaben überschreiben würde. Nach Bestätigung wird sie **nur ergänzend**
  eingelesen – neue Tickets und Bemerkungen kommen hinzu, bestehende Angaben
  bleiben unverändert.

Der Import läuft in einer Transaktion; bricht etwas ab, bleibt der alte Stand
vollständig erhalten.

---

## 3. Zweisprachigkeit (DE / EN)

Die gesamte Oberfläche liegt in Deutsch und Englisch vor. Umgeschaltet wird
oben rechts – auf der Anmeldeseite ebenso wie im angemeldeten Bereich. Die Wahl
gilt sofort und wird in der Session **und** in einem Cookie (1 Jahr) gemerkt,
damit sie eine abgelaufene Sitzung überlebt.

Reihenfolge der Sprachwahl: Auswahl des Benutzers › Cookie › Browsersprache
(`Accept-Language`) › `APP_DEFAULT_LANG` aus `config/.env`.

Übersetzt sind auch die Statusbezeichnungen, die Feldnamen im
Änderungsverlauf, Fehler- und Importmeldungen sowie die Kopfzeile des
CSV-Exports. Das Datumsformat folgt der Sprache (`27.08.2026` / `27 Aug 2026`).

Die Texte stehen in `src/Lang/de.php` und `src/Lang/en.php` – gleiche
Schlüssel, Platzhalter im `sprintf`-Format. Neue Texte immer in **beide**
Dateien aufnehmen; ein fehlender Schlüssel wird als Schlüsselname ausgegeben
und fällt damit sofort auf. Bewusst **nicht** übersetzt sind die Meldungen im
Server-Log, das Audit-Log und die CLI-Skripte in `bin/` (Administration).

---

## 4. Installation auf dem Server

### 4.1 Dateien und Rechte

```bash
sudo mkdir -p /var/www/html/barox-bugs
# Projekt hochladen (VS Code SFTP, remotePath ist bereits gesetzt)

cd /var/www/html/barox-bugs
sudo chown -R www-data:www-data storage
sudo chmod -R 750 storage
```

Benötigte PHP-Erweiterungen: `pdo_mysql`, `curl`, `openssl`, `mbstring`,
`simplexml`. `php-zip` ist **nicht** nötig – der XLSX-Leser bringt einen eigenen
ZIP-Leser mit.

```bash
sudo apt install php-mysql php-curl php-mbstring php-xml
```

### 4.2 Datenbank

```bash
sudo mysql -e "CREATE DATABASE IF NOT EXISTS barox_bugs
                 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
sudo mysql -e "CREATE USER IF NOT EXISTS 'barox'@'localhost' IDENTIFIED BY '<PASSWORT>';"
sudo mysql -e "GRANT SELECT, INSERT, UPDATE, DELETE ON barox_bugs.* TO 'barox'@'localhost';"
sudo mysql -e "FLUSH PRIVILEGES;"
```

Für das Anlegen der Tabellen braucht der Benutzer einmalig `CREATE`, oder das
Schema wird als root eingelesen:

```bash
sudo mysql barox_bugs < db/schema.sql
# alternativ mit den Zugangsdaten aus config/.env:
php bin/migrate.php
```

### 4.3 Konfiguration

```bash
cp config/.env.example config/.env
php -r "echo bin2hex(random_bytes(32)), PHP_EOL;"   # Wert für APP_SECRET
nano config/.env
sudo chown root:www-data config/.env
sudo chmod 640 config/.env
```

### 4.4 Entra-App-Registrierung (Microsoft 365)

1. https://entra.microsoft.com → **App-Registrierungen** → **Neue Registrierung**
   - Name: `barox bugs`, Kontotyp: **nur eigenes Verzeichnis** (Single-Tenant)
   - Umleitungs-URI (Typ **Web**): `https://bugs.barox.io/auth/callback`
2. **Zertifikate & Geheimnisse** → neues Client-Secret → Wert notieren
3. In `config/.env` eintragen: `MS_TENANT_ID` (Verzeichnis-ID),
   `MS_CLIENT_ID` (Anwendungs-ID), `MS_CLIENT_SECRET`

Zusätzliche API-Berechtigungen sind nicht nötig (nur `openid email profile`).
Wer sich anmelden darf, steuert `AUTH_ALLOWED_DOMAINS` (Standard: `barox.ch`,
Konten werden beim ersten Login automatisch angelegt). Der erste Benutzer wird
Administrator. Bei leerem Wert gilt eine strikte Allowlist:

```bash
php bin/add-user.php max.muster@barox.ch "Max Muster" editor
```

Rollen: `viewer` (nur lesen) · `editor` (Ausgaben einlesen) · `admin`.

### 4.5 Apache und nginx

```bash
sudo cp deploy/apache-bugs.barox.io.conf /etc/apache2/sites-available/
sudo a2enmod rewrite headers expires remoteip
sudo a2ensite apache-bugs.barox.io
# Falls Port 8081 noch nicht lauscht: "Listen 8081" in /etc/apache2/ports.conf
sudo apache2ctl configtest && sudo systemctl reload apache2

sudo cp deploy/nginx-bugs.barox.io.conf /etc/nginx/sites-available/bugs.barox.io
sudo ln -s /etc/nginx/sites-available/bugs.barox.io /etc/nginx/sites-enabled/
sudo certbot --nginx -d bugs.barox.io
sudo nginx -t && sudo systemctl reload nginx
```

Wichtig am Reverse Proxy: `proxy_set_header X-Forwarded-Proto https;` muss
gesetzt sein. Ohne diesen Header leitet die Anwendung endlos auf HTTPS um und
setzt keine `Secure`-Cookies.

Upload-Grenzen müssen zusammenpassen: `UPLOAD_MAX_MB` (.env) ≤
`upload_max_filesize`/`post_max_size` (PHP) ≤ `client_max_body_size` (nginx).

### 4.6 Erste Befüllung

Ältere Ausgaben lassen sich der Reihe nach (ältester Stand zuerst) über die
Kommandozeile einlesen – dann entsteht die Historie gleich mit:

```bash
php bin/import.php "/pfad/Barox Tickets_20260715.xlsx"
php bin/import.php "/pfad/Barox Tickets_20260827.xlsx"
```

---

## 5. Aufbau

```
public/           Webroot (DocumentRoot) – nur index.php und Assets
  index.php       Front-Controller mit der Routentabelle
src/
  bootstrap.php   Autoloader, .env, Fehlerbehandlung, Security-Header
  helpers.php     Escaping, Datums-/Label-Helfer, Badges
  Core/           Env, Session, Csrf, Auth, OidcClient, Database, View, AuditLog, Lang
  Lang/           de.php, en.php – alle Oberflaechentexte
  Excel/          Zip (eigener ZIP-Leser), XlsxReader, Cell (Text + Farben)
  Import/         RowParser (Zeile → Datensatz), Importer (Abgleich)
  Repo/           TicketRepo (Abfragen, Kennzahlen, Historie)
  Controllers/    Auth, Ticket, Import
templates/        PHP-Templates (layout/, pages/, errors/)
db/schema.sql     Datenbankschema (idempotent)
bin/              migrate.php, add-user.php, import.php
deploy/           Apache-vHost und nginx-Konfiguration
storage/          Logs, OIDC-Cache, eingelesene Dateien (nicht im Webroot)
docs/testdaten/   Skript für eine simulierte Folgeausgabe (Test des Updates)
```

### Datenmodell

| Tabelle | Inhalt |
|---|---|
| `tickets` | aktueller Stand je Ticket, `ticket_key` = normalisierte Ticketnummer |
| `imports` | eine Zeile je eingelesener Ausgabe, mit Zählern |
| `ticket_changes` | feldgenaue Änderungen (alt → neu) je Ausgabe |
| `ticket_revisions` | vollständige Momentaufnahme je Ticket und Ausgabe |
| `remark_entries` | Bemerkungen des Lieferanten als Einzelmeldungen (append-only) |
| `users`, `audit_log` | Benutzer und Protokoll der Anmeldungen/Importe |

## 6. Sicherheit

- Anmeldung nur über Microsoft 365; `id_token` wird gegen die JWKS geprüft
  (RS256, `iss`/`aud`/`nonce`/`exp`/`tid`).
- Session-Cookie `HttpOnly`, `Secure`, **`SameSite=Lax`** – bei `Strict` bricht
  der Rücksprung vom Microsoft-Login (Login-Loop).
- CSRF-Token auf allen POST-Routen; Session- und Token-Rotation nach Login.
- Ausschliesslich Prepared Statements, `ATTR_EMULATE_PREPARES = false`;
  Sortier- und Filterwerte gegen feste Allowlisten geprüft.
- Content-Security-Policy ohne `unsafe-inline` (keine inline-Styles/Skripte),
  dazu HSTS, `X-Frame-Options`, `nosniff`, Referrer-Policy.
- Upload: nur `.xlsx`, Grössenlimit, XML-Parsing mit `LIBXML_NONET`
  (keine externen Entitäten).
- Eingelesene Dateien liegen unter `storage/uploads` ausserhalb des Webroots.

## 7. Test der Update-Logik

`docs/testdaten/make-testfile.py` erzeugt aus der echten Ausgabe eine
simulierte Folgeausgabe vom 03.09.2026 (ein Ticket wird freigegeben, eines
erhält einen Termin, eines wird erneut verschoben, zwei kommen hinzu, eines
verschwindet). Nach dem Hochladen beider Dateien in dieser Reihenfolge zeigt
`/imports/detail`, ob der Abgleich wie erwartet arbeitet.