dogfather-universe/cloudflare-worker/migrations/0013_webdesign_aufgaben.sql

-- =====================================================================
-- 0013_webdesign_aufgaben.sql
--
-- Zwei Erweiterungen, bewusst in EINER Migration — beide brauchen
-- denselben Neustart, und zwei Deploy-Runden für zusammengehörende
-- Funktionen sind vermeidbare Arbeit für Filipe.
--
--   1. Aufgabenliste je Projekt ("was ist schon gemacht von dem, was im
--      Plan war")
--   2. Nachrichten OHNE Projektbezug ("Kunden sollen mir auch
--      Kleinigkeiten schreiben können")
--
-- Wunsch vom 22.08.2026.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. AUFGABEN JE PROJEKT
--
-- Heute sieht der Kunde nur die Phase ("Design · 3/7"). Er sieht NICHT,
-- was innerhalb dieser Phase schon erledigt ist — und genau daraus
-- entstehen die meisten Rückfragen ("wie weit seid ihr denn?").
--
-- Drei Entscheidungen, die hier drinstecken:
--
--   * "wer_dran" ist eine eigene Spalte, kein Textfeld. Manche Punkte
--     warten auf den KUNDEN ("Texte liefern"), und genau die müssen ihm
--     ins Auge springen. Ohne diese Unterscheidung liest er die Liste
--     als reinen Fortschrittsbericht und übersieht seinen eigenen Teil.
--
--   * "nicht_enthalten" bildet ab, was NICHT zum Umfang gehört. Das ist
--     die häufigste Streitfrage in jedem Projekt ("ich dachte, das ist
--     dabei"). Sichtbar aufgeschrieben, bevor es strittig wird, kostet
--     es nichts — hinterher kostet es Geld oder den Kunden.
--
--   * "kategorie" nutzt dieselben vier Abschnitte wie die Farben im
--     Portal (Start / Gestaltung / Umsetzung / Abschluss). Damit
--     bedeutet eine Farbe überall dasselbe, statt nur hübsch zu sein.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wd_aufgaben (
  id              TEXT PRIMARY KEY,
  projekt_id      TEXT NOT NULL,
  kunde_id        TEXT NOT NULL,

  titel           TEXT NOT NULL,
  beschreibung    TEXT,

  -- start | gestaltung | umsetzung | abschluss  (die vier Prozessfarben)
  kategorie       TEXT NOT NULL DEFAULT 'umsetzung',

  -- offen | in_arbeit | erledigt | entfaellt
  status          TEXT NOT NULL DEFAULT 'offen',

  -- dogfather | kunde  — wer diesen Punkt erledigen muss
  wer_dran        TEXT NOT NULL DEFAULT 'dogfather',

  /* Punkte, die ausdrücklich NICHT zum Umfang gehören. Sie stehen
     trotzdem in der Liste, aber deutlich als "nicht enthalten"
     gekennzeichnet — mit einem Hinweis, was es kosten würde. */
  nicht_enthalten INTEGER NOT NULL DEFAULT 0,
  hinweis         TEXT,

  -- Reihenfolge in der Anzeige. Lücken (10, 20, 30) lassen Platz zum
  -- Einschieben, ohne alle folgenden Zeilen neu zu nummerieren.
  reihenfolge     INTEGER NOT NULL DEFAULT 100,

  erledigt_am     TEXT,
  erstellt_am     TEXT NOT NULL,
  aktualisiert_am TEXT,

  FOREIGN KEY (projekt_id) REFERENCES wd_projekte(id) ON DELETE CASCADE,
  FOREIGN KEY (kunde_id)   REFERENCES wd_kunden(id)   ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_wd_aufgaben_projekt ON wd_aufgaben(projekt_id, reihenfolge);
CREATE INDEX IF NOT EXISTS idx_wd_aufgaben_status  ON wd_aufgaben(projekt_id, status);
CREATE INDEX IF NOT EXISTS idx_wd_aufgaben_kunde   ON wd_aufgaben(kunde_id, wer_dran, status);

-- ---------------------------------------------------------------------
-- 2. NACHRICHTEN OHNE PROJEKTBEZUG
--
-- wd_nachrichten hat projekt_id als NOT NULL. Das war richtig, solange
-- jede Nachricht zu einem Projekt gehörte — es verhindert verwaiste
-- Einträge. Es bedeutet aber auch: Wer noch kein Projekt hat, kann gar
-- nicht schreiben. Genau das ist aufgefallen.
--
-- SQLite kann eine NOT-NULL-Beschränkung nicht einfach lockern; dafür
-- müsste die ganze Tabelle kopiert werden. Das wäre bei einer Tabelle
-- mit echten Kundennachrichten ein unnötiges Risiko.
--
-- Deshalb eine EIGENE Tabelle für den allgemeinen Kanal. Sie hat
-- ohnehin andere Anforderungen: Anhänge, Lesestatus in beide Richtungen,
-- und optional ein Bezug auf eine Aufgabe ("zu diesem Punkt habe ich
-- eine Frage"). Zwei klar getrennte Tabellen sind hier ehrlicher als
-- eine, die beides halb kann.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wd_postfach (
  id              TEXT PRIMARY KEY,
  kunde_id        TEXT NOT NULL,

  -- kunde | dogfather | vanvan
  autor           TEXT NOT NULL,
  text            TEXT NOT NULL,

  /* Optionaler Bezug. Eine Nachricht kann sich auf ein Projekt ODER auf
     einen einzelnen Aufgabenpunkt beziehen — muss aber nicht. Beides
     darf leer sein, das ist der ganze Zweck dieser Tabelle. */
  projekt_id      TEXT,
  aufgabe_id      TEXT,

  /* Anhang: "Kleinigkeiten" sind fast immer Screenshots. Die Datei
     selbst liegt neben der Datenbank, hier steht nur der Verweis —
     gleiches Muster wie bei wd_dateien. */
  datei_id        TEXT,

  /* Lesestatus getrennt für beide Seiten. Eine einzige "gelesen"-Spalte
     könnte nicht beantworten, ob der KUNDE die Antwort schon gesehen
     hat — und genau das will man wissen, bevor man nachhakt. */
  gelesen_kunde   INTEGER NOT NULL DEFAULT 0,
  gelesen_admin   INTEGER NOT NULL DEFAULT 0,

  /* Interne Bemerkungen im selben Verlauf. Die Trennung ist heikel:
     eine versehentlich sichtbare interne Notiz ist der peinlichste
     Fehler in so einem System. Deshalb Vorgabe 0 (= sichtbar) und die
     Abfragen im Portal filtern immer ausdrücklich auf intern = 0. */
  intern          INTEGER NOT NULL DEFAULT 0,

  erstellt_am     TEXT NOT NULL,

  FOREIGN KEY (kunde_id)   REFERENCES wd_kunden(id)   ON DELETE CASCADE,
  FOREIGN KEY (projekt_id) REFERENCES wd_projekte(id) ON DELETE SET NULL,
  FOREIGN KEY (aufgabe_id) REFERENCES wd_aufgaben(id) ON DELETE SET NULL,
  FOREIGN KEY (datei_id)   REFERENCES wd_dateien(id)  ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_wd_postfach_kunde ON wd_postfach(kunde_id, erstellt_am DESC);
CREATE INDEX IF NOT EXISTS idx_wd_postfach_offen ON wd_postfach(gelesen_admin, erstellt_am DESC);

-- ---------------------------------------------------------------------
-- 3. VORLAGEN FÜR AUFGABENLISTEN
--
-- Bei jedem neuen Projekt dieselben fünfzehn Punkte von Hand einzutippen
-- führt zuverlässig dazu, dass es irgendwann niemand mehr macht — und
-- dann steht der Kunde wieder vor einer leeren Liste.
--
-- Deshalb: Vorlagen je Paket, die beim Anlegen eines Projekts
-- automatisch übernommen werden. Sie sind änderbar, damit sie mit der
-- Erfahrung wachsen können, statt im Code zu versteinern.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wd_aufgaben_vorlagen (
  id            TEXT PRIMARY KEY,
  paket         TEXT NOT NULL,          -- onepager | website | shop | verwaltung | betreuung
  titel         TEXT NOT NULL,
  beschreibung  TEXT,
  kategorie     TEXT NOT NULL DEFAULT 'umsetzung',
  wer_dran      TEXT NOT NULL DEFAULT 'dogfather',
  reihenfolge   INTEGER NOT NULL DEFAULT 100,
  aktiv         INTEGER NOT NULL DEFAULT 1,
  erstellt_am   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_wd_vorlagen_paket ON wd_aufgaben_vorlagen(paket, reihenfolge);