====== LU17 – CRUD mit Express & MySQL ======

===== Lernziele =====
  * Sie können Ihre **MySQL-Datenbank** aus einem Express-Server heraus ansprechen.
  * Sie können die CRUD-Routen aus LU16 (''GET'', ''POST'', ''PUT'', ''DELETE'') so erweitern, dass sie mit einer **echten Tabelle ''posts''** arbeiten.
  * Sie setzen einfache **Validierung** ein (Pflichtfelder prüfen).
  * Sie verwenden passende **HTTP-Statuscodes** (200, 201, 400, 404, 500).
  * Sie testen alle Operationen mit **Postman**.

===== Rückblick auf LU16 =====
In LU16 haben Sie eine API für Social-Media-Posts gebaut – aber noch ohne Datenbank:

  * Daten waren in einer **JavaScript-Liste ''posts''** gespeichert (In-Memory).
  * Ihre Routen waren z.B.:
    * ''GET /api/posts''
    * ''GET /api/posts/:id''
    * ''POST /api/posts''

In LU17 ersetzen Sie diese In-Memory-Liste durch eine echte Datenbank:

  * Die Daten kommen aus der **MySQL-Tabelle ''posts''**.
  * Ihr Express-Server ist die **Brücke** zwischen Client (Postman) und Datenbank.
  * Die gleiche Idee bleibt: **HTTP-Anfrage → Route → Logik/SQL → HTTP-Antwort**.


{{:modul:m290_guko:learningunits:lu16:theorie:client_server_http_req_response_kitchen.png?direct&900|}}

===== HTTP-Statuscodes =====
Wenn Sie mit Postman testen, sehen Sie immer einen Statuscode. Dieser Code ist Ihr „Kurzfazit“, ob der Request geklappt hat.

{{ :modul:m290_guko:learningunits:lu17:theorie:http_statuscodes.png?direct&1100 | HTTP-Statuscodes }}

<WRAP center box round 80%>
^ Situation ^ Typischer Statuscode ^ Bedeutung ^
| Erfolgreich Daten gelesen (z.B. GET) | **200** | OK (Daten kommen zurück) |
| Erfolgreich Datensatz erstellt (POST) | **201** | Created (Neuer Datensatz wurde erstellt) |
| Erfolgreich aktualisiert (PUT) | **200** | OK (Update hat geklappt) |
| Erfolgreich gelöscht (DELETE) | **200** | OK (Löschen hat geklappt; Sie können zur Kontrolle z.B. das gelöschte Objekt oder eine Message zurückgeben) |
| Pflichtfelder fehlen / ungültige Eingabe | **400** | Bad Request (Client hat falsche/fehlende Daten geschickt) |
| ID existiert nicht | **404** | Not Found (Datensatz wurde nicht gefunden) |
| Datenbank-/Serverfehler | **500** | Internal Server Error (Problem auf Server/DB-Seite) |
</WRAP>

===== Vorbereitung: MySQL-Datenbank "social_media" =====
Für die Verbindung mit MySQL benötigen wir eine bestehende Datenbank. Wir arbeiten mit der Datenbank "social_media", welche die Tabellen users und posts beinhaltet.

<WRAP center round download 60%>
Laden Sie hier den SQL-Dump herunter und lassen Sie das Skript in Webstorm laufen via //Run SQL Script...// (eine Anleitung, wie das prinzipiell geht finden Sie in LU09).
{{ :modul:m290_guko:learningunits:lu17:theorie:social_media_db-dump.sql.zip |SQL-Dump einer fiktiven Social-Media-Datenbank}}
</WRAP>

Das ERD der Datenbank sieht so aus:
{{ :modul:m290_guko:learningunits:lu17:theorie:social_media_crowsfoot_ids_updated.drawio.png?direct&900 | Crow's Foot Diagramm Social Media DB}}

Eine mögliche Tabellenstruktur für die Tabelle "posts":

<WRAP center box round 80%>
^ Spalte ^ Datentyp ^ Beschreibung ^
| ''id'' | INT, PRIMARY KEY, AUTO_INCREMENT | Primärschlüssel |
| ''user_id'' | INT | Referenz auf User (FK) |
| ''title'' | VARCHAR(255) | Titel des Posts |
| ''image_url'' | VARCHAR(512) | Bild-URL |
| ''description'' | TEXT | Beschreibung |
| ''likes'' | INT | Anzahl Likes |
</WRAP>

<WRAP center round important 60%>
Achtung: Die Primär-Spalte heisst neu ''id'' und nicht mehr ''post_id'' wie in LU16.
</WRAP>

===== Schritt 1: mysql2 installieren =====
Damit Node.js mit MySQL sprechen kann, verwenden wir ''mysql2''.

<WRAP box round center 80%>
<code bash>
npm install mysql2
</code>
</WRAP>

===== Schritt 2: connect.js erstellen (DB-Verbindung auslagern) =====
Damit ''index.js'' übersichtlich bleibt, erstellen Sie eine eigene Datei ''connect.js''.
Diese Datei erstellt **eine Verbindung** und exportiert sie, damit Sie sie in Ihren Routes mit ''db.query(...)'' verwenden können.

<WRAP box round center 80%>
<code javascript>
import mysql from 'mysql2';

// Hinweis: Verwenden Sie hier Ihren AppUser (nicht root).
// Port 3306 ist der Standard-Port für MySQL auf localhost.
const db = mysql.createConnection({
  host: 'localhost',
  user: 'app_user',
  password: 'YOUR_PASSWORD_HERE',
  port: 3306,
  database: 'social_media',
});

// connect() öffnet die Verbindung und gibt bei Erfolg/Fehler eine Meldung aus.
db.connect((err) => {
  if (err) {
    console.error('DB-Verbindung fehlgeschlagen:', err);
    return;
  }
  console.log('Mit MySQL verbunden');
});

export { db };
</code>
</WRAP>

**Was passiert hier?**
  * ''db'' ist die offene Verbindung und kann in ''index.js'' importiert werden (z.B. ''import { db } from "./connect.js";'').
  * In der LB03 sollen Sie **nicht** als ''root'' arbeiten → verwenden Sie einen AppUser.


===== Schritt 3: index.js vorbereiten (Express + DB verwenden) =====
Sie bauen auf dem Setup aus LU16 auf:

  * ''app.use(express.json())'' bleibt wichtig (für ''req.body'').
  * Sie importieren ''db'' aus ''connect.js''. Die Verbindung wird in ''connect.js'' bereits aufgebaut (''db.connect(...)'').
  * __Löschen__ Sie die Test-Tabelle aus LU16 ''let posts = [{ post_id: 1, ... }];''



<WRAP box round center 80%>
<code javascript>
import express from 'express';
import { db } from './connect.js';

const app = express();
const port = 3000;

app.use(express.json());

// Test-Route: schnell prüfen, ob der Server läuft
app.get('/', (req, res) => {
  res.send('API mit Datenbank ist online');
});

// Optional: kleine Test-Route für DB (zeigt Serverzeit aus MySQL)
app.get('/api/db-test', (req, res) => {
  db.query('SELECT NOW() AS server_time', (err, results) => {
    if (err) {
      console.error('DB-Fehler bei /api/db-test:', err);
      return res.status(500).send('DB-Test fehlgeschlagen');
    }
    res.status(200).json(results[0]);
  });
});

// Server starten
app.listen(port, () => {
  console.log(`API läuft auf http://localhost:${port}`);
});
</code>
</WRAP>

**Was passiert hier?**
  * ''db'' kommt aus ''connect.js'' und ist die MySQL-Verbindung.
  * Ihre Routen können direkt ''db.query(...)'' verwenden.
  * Mit ''/api/db-test'' prüfen Sie schnell, ob die DB erreichbar ist (Status ''200'' + JSON), ohne schon CRUD zu programmieren.


**Server starten**
Nicht alle Lernenden haben ''nodemon'' installiert. Beides ist möglich:

<WRAP box round center 80%>
<code bash>
node index.js
</code>
</WRAP>

oder (falls Sie ein dev-Script mit nodemon haben):

<WRAP box round center 80%>
<code bash>
npm run dev
</code>
</WRAP>

===== CRUD-Routen: posts jetzt mit echter MySQL-Tabelle =====
Ab hier ersetzen Sie die In-Memory-Liste aus LU16 durch SQL.

<WRAP box round center 80%>
**Konvention:** Wir bleiben wie in LU16 bei ''/api/posts'' (statt nur ''/posts''), damit Ihre API klar erkennbar ist.
</WRAP>

==== READ: alle Posts (GET /api/posts) ====

<WRAP box round center 80%>
<code javascript>
// READ – alle Posts aus der DB
// Route: GET http://localhost:3000/api/posts
app.get('/api/posts', (req, res) => {

  // SQL-Abfrage: alle Spalten, die wir zurückgeben möchten
  const sql = `
    SELECT id, user_id, title, image_url, description, likes
    FROM posts
  `;

  // db.query(...) führt die SQL-Abfrage aus
  db.query(sql, (err, results) => {

    // Falls die DB einen Fehler liefert -> 500
    if (err) {
      console.error('DB-Fehler bei GET /api/posts:', err);
      return res.status(500).send('Serverfehler bei der Post-Abfrage');
    }

    // results ist ein Array mit Zeilen (Rows)
    res.status(200).json(results);
  });
});
</code>
</WRAP>

=== Test in Postman ===
  * Methode: ''GET''
  * URL: ''http://localhost:3000/api/posts''
  * Erwartung: ''200'' + JSON-Liste


==== READ: einzelner Post (GET /api/posts/:id) ====

<WRAP box round center 80%>
<code javascript>
// READ – einzelner Post nach ID aus der DB
// Route: GET http://localhost:3000/api/posts/1
app.get('/api/posts/:id', (req, res) => {

  // Route-Parameter :id kommt immer als String -> Number(...) macht eine Zahl daraus
  const id = Number(req.params.id);

  // Einfache Validierung: ist id überhaupt eine Zahl?
  if (Number.isNaN(id)) {
    return res.status(400).send('Ungültige ID (muss eine Zahl sein)');
  }

  const sql = `
    SELECT id, user_id, title, image_url, description, likes
    FROM posts
    WHERE id = ?
  `;

  // Platzhalter ? wird durch id ersetzt -> Schutz vor SQL-Injection
  db.query(sql, [id], (err, results) => {

    if (err) {
      console.error('DB-Fehler bei GET /api/posts/:id:', err);
      return res.status(500).send('Serverfehler bei der Post-Abfrage');
    }

    // Wenn keine Zeile gefunden -> 404
    if (results.length === 0) {
      return res.status(404).send('Post nicht gefunden');
    }

    // results[0] ist der erste (und hier einzige) Treffer
    res.status(200).json(results[0]);
  });
});
</code>
</WRAP>


==== CREATE: neuen Post erstellen (POST /api/posts) ====

<WRAP box round center 80%>
<code javascript>
// CREATE – neuen Post in der DB anlegen
// Route: POST http://localhost:3000/api/posts
app.post('/api/posts', (req, res) => {

  // Daten kommen aus dem Request-Body (Postman: Body -> raw -> JSON)
  const user_id = req.body.user_id;
  const title = req.body.title;
  const image_url = req.body.image_url;
  const description = req.body.description; 

  // Validierung: Pflichtfelder
  if (user_id === undefined || user_id === null || title === undefined || title === null || title === '') {
    return res.status(400).send('Bitte mindestens user_id und title angeben.');
  }

  const sql = `
    INSERT INTO posts (user_id, title, image_url, description, likes)
    VALUES (?, ?, ?, ?, 0)
  `;

  const values = [
    user_id,
    title,
    image_url || '',
    description || ''
  ];

  // INSERT ausführen
  db.query(sql, values, (err, result) => {

    if (err) {
      console.error('DB-Fehler bei POST /api/posts:', err);
      return res.status(500).send('Serverfehler beim Erstellen des Posts');
    }

    // insertId kommt von MySQL AUTO_INCREMENT
    const newPost = {
      id: result.insertId,
      user_id: user_id,
      title: title,
      image_url: image_url || '',
      description: description || '',
      likes: 0
    };

    res.status(201).json(newPost);
  });
});
</code>
</WRAP>

=== Test in Postman ===

**Test-Body (Postman → Body → raw → JSON)**
<WRAP box round center 80%>
<code json>
{
  "user_id": 1,
  "title": "Neuer DB-Post",
  "image_url": "https://example.com/post.jpg",
  "description": "Dieser Post wurde in MySQL gespeichert."
}
</code>
</WRAP>


==== UPDATE: Post ändern (PUT /api/posts/:id) ====

In LU16b haben Sie einen Post so aktualisiert: Nur die Felder, die im Body wirklich mitgeschickt werden, werden geändert.
Genau diese Logik übernehmen wir jetzt – der Unterschied ist nur: statt im Array ändern wir jetzt die MySQL-Tabelle ''posts''.


<WRAP box round center 80%>
<code javascript>
// UPDATE – Post vollständig ersetzen (PUT)
// Route: PUT http://localhost:3000/api/posts/1
app.put('/api/posts/:id', (req, res) => {

  const id = Number(req.params.id);

  if (Number.isNaN(id)) {
    return res.status(400).send('Ungültige ID (muss eine Zahl sein)');
  }

  // Alle Felder werden erwartet (PUT ersetzt alles)
  const user_id = req.body.user_id;
  const title = req.body.title;
  const image_url = req.body.image_url;
  const description = req.body.description;
  const likes = req.body.likes;

  // Validierung: fehlen Felder?
  // likes kann 0 sein -> deshalb auf undefined prüfen
  if (
    user_id === undefined ||
    title === undefined ||
    image_url === undefined ||
    description === undefined ||
    likes === undefined
  ) {
    return res.status(400).send('Bitte user_id, title, image_url, description und likes mitsenden (PUT ersetzt alles).');
  }

  const sql = `
    UPDATE posts
    SET user_id = ?, title = ?, image_url = ?, description = ?, likes = ?
    WHERE id = ?
  `;

  const values = [user_id, title, image_url, description, likes, id];

  db.query(sql, values, (err, result) => {
    if (err) {
      console.error('DB-Fehler bei PUT /api/posts/:id:', err);
      return res.status(500).send('Serverfehler beim Aktualisieren des Posts');
    }

    if (result.affectedRows === 0) {
      return res.status(404).send('Post nicht gefunden');
    }

    // 200 OK + das "neue" Objekt zurückgeben
    res.status(200).json({
      id: id,
      user_id: user_id,
      title: title,
      image_url: image_url,
      description: description,
      likes: likes
    });
  });
});
</code>
</WRAP>

=== Test in Postman ===

**Test-Body**
<WRAP box round center 80%>
<code json>
{
  "user_id": 1,
  "title": "Titel (replaced)",
  "image_url": "https://example.com/new.jpg",
  "description": "Dieser Post wurde komplett ersetzt.",
  "likes": 5
}
</code>
</WRAP>

Erwartung:
  * Status ''200 OK''
  * JSON-Objekt des aktualisierten Posts (inkl. neuem ''title'' und ''likes'')


==== DELETE: Post löschen (DELETE /api/posts/:id) ====
<WRAP box round center 80%>
<code javascript>
// DELETE – Post löschen
app.delete('/api/posts/:id', (req, res) => {

  const id = Number(req.params.id);

  if (Number.isNaN(id)) {
    return res.status(400).send('Ungültige ID (muss eine Zahl sein)');
  }

  const sql = 'DELETE FROM posts WHERE id = ?';

  db.query(sql, [id], (err, result) => {
    if (err) {
      console.error('DB-Fehler bei DELETE /api/posts/:id:', err);
      return res.status(500).send('Serverfehler beim Löschen des Posts');
    }

    if (result.affectedRows === 0) {
      return res.status(404).send('Post nicht gefunden');
    }

    // Antwort: 200 + Message
    res.status(200).json({ message: `Post mit id=${id} wurde gelöscht.` });

  });
});
</code>
</WRAP>

=== Test mit Postman ===

  * Methode: ''DELETE''
  * URL: ''http://localhost:3000/api/posts/1''

Erwartung:
  * Status ''200 OK''
  * JSON-Objekt des gelöschten Posts
  * Danach ''GET /api/posts/1'' → ''404 Not Found''


===== Typische Fehlerquellen (aus LU16, jetzt noch wichtiger) =====
  * ''req.params.id'' ist immer ein String → mit ''Number(...)'' umwandeln (und ''Number.isNaN(...)'' prüfen).
  * Ohne ''app.use(express.json())'' ist ''req.body'' leer.
  * SQL immer mit Platzhaltern ''?'' schreiben (Prepared Statements).
  * Denken Sie bei ''db.query(...)'' immer an die drei Fälle:
    * Eingaben ok? → sonst ''400''
    * Datensatz gefunden? → sonst ''404'' (''results.length === 0'' oder ''affectedRows === 0'')
    * DB-Fehler? → ''500''


===== Transfer auf Ihr Projekt (LB03) =====
Für Ihr Projekt ersetzen Sie ''posts'' durch Ihre eigenen Tabellen (z.B. ''serie'', ''actor'', ''serie_actor''):

  * Ressourcen-Route: z.B. ''/api/serien''
  * CRUD: ''GET'', ''POST'', ''PUT'', ''DELETE''
  * Zusätzlich:
    * mindestens **eine JOIN-Route** (z.B. Serien inkl. Schauspieler:innen)
    * mindestens **eine Aggregat-Route** (z.B. Durchschnittsbewertung pro Genre)