[[..:start|zurück]]

<code sql> /* =========================================================
   Bewerbungsdatenbank (MariaDB)
   - deutsch benannte Tabellen und Spalten (ohne Umlaute)
   - inkl. Status-Historie, Aktivitaeten, Dokumenten-Nachweisen
   - Views fuer Nachweis-Export + Monatsreport
   - Trigger: Statusaenderung -> Historie
   ========================================================= */

/* 1) Datenbank komplett neu */
DROP DATABASE IF EXISTS bewerbungen;

CREATE DATABASE bewerbungen
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE bewerbungen;

/* =========================================================
   2) Tabellen
   ========================================================= */

CREATE TABLE firma (
  firma_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
  name          VARCHAR(255) NOT NULL,
  ort           VARCHAR(120),
  webseite      VARCHAR(255),
  notizen       TEXT,
  erstellt_am   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ansprechpartner (
  ansprechpartner_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  firma_id           BIGINT NOT NULL,
  vollname           VARCHAR(255),
  rolle              VARCHAR(255),
  email              VARCHAR(255),
  telefon            VARCHAR(60),
  notizen            TEXT,
  erstellt_am        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ansprechpartner_firma
    FOREIGN KEY (firma_id) REFERENCES firma(firma_id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE stelle (
  stelle_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  firma_id         BIGINT NOT NULL,
  titel            VARCHAR(255) NOT NULL,
  referenznummer   VARCHAR(120),
  stellen_url      TEXT,
  quelle_plattform VARCHAR(120),   -- z.B. StepStone, Indeed, LinkedIn, Firmenwebsite
  ort_text         VARCHAR(255),
  arbeitsmodell    VARCHAR(60),    -- vor Ort, hybrid, remote
  beschaeftigung   VARCHAR(60),    -- Vollzeit, Teilzeit, ...
  notizen          TEXT,
  erstellt_am      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_stelle_firma
    FOREIGN KEY (firma_id) REFERENCES firma(firma_id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE bewerbung (
  bewerbung_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  stelle_id           BIGINT NOT NULL,
  bewerbungsdatum     DATE NOT NULL,
  kanal               VARCHAR(60) NOT NULL,      -- Email, Portal, Post, Recruiter, Telefon
  aktueller_status    VARCHAR(60) NOT NULL DEFAULT 'offen',
  naechstes_nachfassen DATE NULL,
  betreff             VARCHAR(255),
  notizen             TEXT,
  erstellt_am         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bewerbung_stelle
    FOREIGN KEY (stelle_id) REFERENCES stelle(stelle_id)
    ON DELETE CASCADE,
  INDEX idx_bewerbung_datum (bewerbungsdatum),
  INDEX idx_bewerbung_status (aktueller_status),
  INDEX idx_bewerbung_nachfassen (naechstes_nachfassen)
) ENGINE=InnoDB;

CREATE TABLE status_verlauf (
  status_verlauf_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  bewerbung_id      BIGINT NOT NULL,
  status            VARCHAR(60) NOT NULL,  -- offen, absage, einladung, angebot, ...
  status_datum      DATE NOT NULL,
  quelle            VARCHAR(60),           -- Email, Portal, Telefon, Brief, system
  notizen           TEXT,
  erstellt_am       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_statusverlauf_bewerbung
    FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id)
    ON DELETE CASCADE,
  INDEX idx_statusverlauf_bewerbung_datum (bewerbung_id, status_datum)
) ENGINE=InnoDB;

CREATE TABLE aktivitaet (
  aktivitaet_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
  bewerbung_id      BIGINT NOT NULL,
  typ               VARCHAR(60) NOT NULL,  -- Nachfassen, Telefonat, Interview, Mail, Test, ...
  zeitpunkt         DATETIME NOT NULL,
  ergebnis          VARCHAR(120),
  notizen           TEXT,
  erstellt_am       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_aktivitaet_bewerbung
    FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id)
    ON DELETE CASCADE,
  INDEX idx_aktivitaet_bewerbung_zeitpunkt (bewerbung_id, zeitpunkt)
) ENGINE=InnoDB;

CREATE TABLE dokument (
  dokument_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
  bewerbung_id  BIGINT NOT NULL,
  typ           VARCHAR(60) NOT NULL,  -- Anschreiben, Lebenslauf, Stellenanzeige, Bestaetigung, Absage, ...
  dateipfad     TEXT,                 -- z.B. /srv/bewerbungen/docs/....
  url           TEXT,
  erstellt_am_datum DATE NOT NULL,
  notizen       TEXT,
  erstellt_am   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dokument_bewerbung
    FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id)
    ON DELETE CASCADE,
  INDEX idx_dokument_bewerbung_typ (bewerbung_id, typ)
) ENGINE=InnoDB;

/* =========================================================
   3) Views (Export / Nachweis / Report)
   ========================================================= */

CREATE OR REPLACE VIEW v_nachweis_bewerbungen AS
SELECT
  b.bewerbung_id,
  b.bewerbungsdatum,
  f.name AS firma,
  s.titel AS stelle,
  b.kanal,
  b.aktueller_status AS status,
  b.naechstes_nachfassen,
  s.quelle_plattform AS quelle,
  s.referenznummer,
  s.stellen_url
FROM bewerbung b
JOIN stelle s ON s.stelle_id = b.stelle_id
JOIN firma f  ON f.firma_id  = s.firma_id;

CREATE OR REPLACE VIEW v_nachweis_bewerbungen_mit_belegen AS
SELECT
  b.bewerbung_id,
  b.bewerbungsdatum,
  f.name AS firma,
  s.titel AS stelle,
  b.kanal,
  b.aktueller_status AS status,
  b.naechstes_nachfassen,
  s.quelle_plattform AS quelle,
  s.referenznummer,
  COUNT(d.dokument_id) AS anzahl_belege
FROM bewerbung b
JOIN stelle s ON s.stelle_id = b.stelle_id
JOIN firma f  ON f.firma_id  = s.firma_id
LEFT JOIN dokument d ON d.bewerbung_id = b.bewerbung_id
GROUP BY
  b.bewerbung_id, b.bewerbungsdatum, f.name, s.titel, b.kanal,
  b.aktueller_status, b.naechstes_nachfassen, s.quelle_plattform, s.referenznummer;

CREATE OR REPLACE VIEW v_report_monat AS
SELECT
  DATE_FORMAT(bewerbungsdatum, '%Y-%m') AS monat,
  COUNT(*) AS bewerbungen_total,
  SUM(aktueller_status='offen') AS offen,
  SUM(aktueller_status='nachfassen_geplant') AS nachfassen_geplant,
  SUM(aktueller_status='rueckmeldung') AS rueckmeldung,
  SUM(aktueller_status='einladung') AS einladung,
  SUM(aktueller_status='absage') AS absage,
  SUM(aktueller_status='angebot') AS angebot,
  SUM(aktueller_status='zurueckgezogen') AS zurueckgezogen,
  SUM(aktueller_status='keine_antwort') AS keine_antwort
FROM bewerbung
GROUP BY DATE_FORMAT(bewerbungsdatum, '%Y-%m')
ORDER BY monat DESC;

</code>

<WRAP center round box 60%>
**Hinweise zur DDL
**
  * RESTRICT bei FKs: verhindert versehentliche Loeschungen (amtstauglich = kein Datenverlust).
  * aktueller_status ist bewusst redundant (Performance + einfache Auswertungen); Historie liegt in status_verlauf.
  * dokument.dateipfad und dokument.url koennen optional sein; mindestens eines sollte befuellt sein (Validierung macht PHP).
</WRAP>