3d.lehrstun.de/database.py

"""SQLite-Schema und Init fuer 3d.lehrstun.de.

Wird beim ersten Start aufgerufen via:
    python -c "from database import init_db; init_db()"

Migrationen kommen separat in migrations/, idempotent + datiert,
analog zu arduino.lehrstun.de.

Vollstaendige Tabellen-Doku: docs/datenmodell.md
Entscheidungen E-002 (Profil primaer), E-013 (Onboarding-Status),
E-014 (Projekt-Lebenszyklus), E-016 (Files in BLOB), E-020 (V7 Lockout).
"""
import sqlite3
from pathlib import Path

DB_PFAD = Path(__file__).parent / "3dag.db"


def get_db():
    """Standard-Verbindung mit Row-Factory + Foreign-Keys an."""
    conn = sqlite3.connect(DB_PFAD)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


SCHEMA = """
-- ===========================================================================
-- AG-Mitgliedschaft (statt Trimester-Kurs — E-012, AG ist offen + jahrelang)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS ag_mitgliedschaft (
    id TEXT PRIMARY KEY,                -- z.B. 'lessing-3d-ag'
    name TEXT NOT NULL,
    start_datum DATE,
    status TEXT DEFAULT 'aktiv'         -- 'aktiv'|'archiviert'
);

-- ===========================================================================
-- SuS-Profile (Primaer-Objekt — E-002)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS profil (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vorname TEXT NOT NULL,              -- Klarname (E-003)
    tier TEXT NOT NULL,                 -- Tier-Slug fuer Icon
    pin_hash TEXT NOT NULL,             -- bcrypt
    ag_id TEXT NOT NULL,                -- FK ag_mitgliedschaft
    aktiv INTEGER DEFAULT 1,
    -- Lehrer schaltet SuS manuell zur Drucker-Reservierung frei (ersetzt
    -- den geplanten L1-Gate-Automatismus, weil manche SuS zuhause schon
    -- drucken oder schneller durch L1 sind als die Doku festhalten kann).
    reservieren_freigeschaltet INTEGER DEFAULT 0,
    -- Rolle: 'schueler' (Default) | 'power'. Power-User sind fortgeschrittene
    -- SuS (oder Herb selbst): duerfen Drucker/Software verwalten + Slots fuer
    -- andere + mehrere Demo-Projekte, aber KEINE SuS-Verwaltung/Freigaben.
    -- Kein Tier-Zwang, kein Check-In.
    rolle TEXT DEFAULT 'schueler',
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(ag_id, vorname, tier),
    FOREIGN KEY (ag_id) REFERENCES ag_mitgliedschaft(id)
);

-- Persistenter Login-Lockout (V7, verbessert ggue Arduino-Kurs)
CREATE TABLE IF NOT EXISTS login_lockout (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profil_id INTEGER NOT NULL,
    versuche_count INTEGER DEFAULT 1,
    sperre_bis DATETIME,                -- NULL = nur Zaehler, kein aktives Sperren
    letzter_versuch_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_lockout_profil ON login_lockout(profil_id);

-- 2er-Paarungen (optional)
CREATE TABLE IF NOT EXISTS paarung (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profil_a INTEGER NOT NULL,
    profil_b INTEGER NOT NULL,
    aktiv INTEGER DEFAULT 1,
    gebildet_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profil_a) REFERENCES profil(id) ON DELETE CASCADE,
    FOREIGN KEY (profil_b) REFERENCES profil(id) ON DELETE CASCADE,
    CHECK (profil_a < profil_b)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_paarung_aktiv
    ON paarung(profil_a, profil_b) WHERE aktiv = 1;

-- ===========================================================================
-- Onboarding-Pfad-Fortschritt (E-013, E-019 selbst-zertifiziert)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS profil_lektion_status (
    profil_id INTEGER NOT NULL,
    lektion_nummer INTEGER NOT NULL,
    status TEXT DEFAULT 'offen',        -- 'offen'|'laeuft'|'fertig'
    geaendert_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (profil_id, lektion_nummer),
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE
);

-- ===========================================================================
-- Projekte (mit vollem Lebenszyklus — E-014)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS projekt (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profil_id INTEGER,                  -- entweder dies ODER paarung_id
    paarung_id INTEGER,
    titel TEXT NOT NULL,
    beschreibung_md TEXT,
    -- Lebenszyklus
    status_lebenszyklus TEXT DEFAULT 'in_arbeit',  -- 'in_arbeit'|'abgeschlossen'
    abgeschlossen_am DATETIME,
    selbsteinschaetzung TEXT,           -- 'zufrieden'|'mittel'|'gelernt' (E-015) — Gesamt
    selbsteinschaetzung_planung TEXT,   -- 'gut'|'ok'|'schwierig' — wie war die Planung?
    selbsteinschaetzung_ausfuehrung TEXT, -- 'gut'|'ok'|'schwierig' — wie war die Umsetzung?
    was_gelernt TEXT,
    was_klappte_nicht TEXT,             -- optionaler Freitext, hilft Lehrer beim Review
    cover_blob BLOB,                    -- Cover-Foto (Crop-Tool, 400x400 JPEG)
    cover_mime TEXT,
    -- Werkstatt-Erfassung (Etappe 6 Phase D, mit Herb 2026-05-25)
    modellier_software_slug TEXT,       -- FK auf software_seite.slug (welche Hauptsoftware)
    drucker_id_used INTEGER,            -- FK auf drucker.id (welcher Drucker wurde benutzt)
    makerworld_url TEXT,                -- optional: SuS hat Projekt zusaetzlich auf MakerWorld veroeffentlicht                   -- optionaler Freitext
    -- Freigabe-Workflow (E-017)
    freigabe_status TEXT DEFAULT 'in_arbeit', -- 'in_arbeit'|'wartet_auf_freigabe'|'freigegeben'|'rueckfrage'
    freigegeben_am DATETIME,
    freigegeben_von INTEGER,            -- Lehrer-Profil-ID
    -- Sichtbarkeit
    sichtbar_in_ag INTEGER DEFAULT 1,
    -- Vernetzung
    vorlage_projekt_id INTEGER,         -- "ich mache sowas wie Mia"
    geaendert_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE,
    FOREIGN KEY (paarung_id) REFERENCES paarung(id) ON DELETE CASCADE,
    FOREIGN KEY (vorlage_projekt_id) REFERENCES projekt(id) ON DELETE SET NULL,
    FOREIGN KEY (freigegeben_von) REFERENCES profil(id) ON DELETE SET NULL,
    CHECK ((profil_id IS NOT NULL) <> (paarung_id IS NOT NULL))
);
CREATE INDEX IF NOT EXISTS idx_projekt_freigabe ON projekt(freigabe_status);
CREATE INDEX IF NOT EXISTS idx_projekt_sichtbar ON projekt(sichtbar_in_ag, freigabe_status);

-- Projekt-Dateien (E-016, 30 MB-Limit als App-Logik)
CREATE TABLE IF NOT EXISTS projekt_datei (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    projekt_id INTEGER NOT NULL,
    dateiname TEXT NOT NULL,            -- Anzeigename (auch fuer Links)
    mime_type TEXT,
    groesse_bytes INTEGER,
    inhalt BLOB,                        -- NULL bei externen Links
    extern_url TEXT,                    -- gesetzt = externer Link (GitHub/MakerWorld/...), inhalt dann NULL
    art TEXT NOT NULL,                  -- 'cover_foto'|'stl'|'3mf'|'foto'|'link'|'sonstiges'
    hochgeladen_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    hochgeladen_von INTEGER,
    FOREIGN KEY (projekt_id) REFERENCES projekt(id) ON DELETE CASCADE,
    FOREIGN KEY (hochgeladen_von) REFERENCES profil(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_datei_projekt ON projekt_datei(projekt_id);

-- Projekt-Tags (fuer Suche und Vernetzung)
CREATE TABLE IF NOT EXISTS projekt_tag (
    projekt_id INTEGER NOT NULL,
    tag TEXT NOT NULL,
    PRIMARY KEY (projekt_id, tag),
    FOREIGN KEY (projekt_id) REFERENCES projekt(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_tag ON projekt_tag(tag);

-- Lehrer-Rueckfragen pro Projekt (V6)
CREATE TABLE IF NOT EXISTS projekt_rueckfrage (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    projekt_id INTEGER NOT NULL,
    von_profil_id INTEGER NOT NULL,
    text TEXT NOT NULL,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'offen',        -- 'offen'|'erledigt'
    erledigt_am DATETIME,
    FOREIGN KEY (projekt_id) REFERENCES projekt(id) ON DELETE CASCADE,
    FOREIGN KEY (von_profil_id) REFERENCES profil(id) ON DELETE SET NULL
);

-- ===========================================================================
-- Lehrer-Notizen (allgemein, an ein SuS-Cockpit gerichtet — V5-Quick-Action)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS lehrer_notiz (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profil_id INTEGER NOT NULL,         -- Adressat
    von_profil_id INTEGER,              -- NULL = System
    text TEXT NOT NULL,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    gelesen_am DATETIME,                -- NULL = ungelesen
    typ TEXT DEFAULT 'info',            -- 'info'|'rueckfrage'|'erinnerung'
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE
);

-- ===========================================================================
-- Stunden-Beginn-Abfrage (E-021 mit "weiter wie bisher"-Default)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS check_in (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profil_id INTEGER NOT NULL,
    besuch_datum DATE NOT NULL,
    modus TEXT NOT NULL,                -- siehe docs/datenmodell.md
    freitext TEXT,                      -- optional
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(profil_id, besuch_datum),
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE
);

-- ===========================================================================
-- Drucker (Stamm-Daten, vom Admin gepflegt) + Live-Status
-- ===========================================================================
CREATE TABLE IF NOT EXISTS drucker (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    modell TEXT NOT NULL,               -- 'P1S'|'H2S'|'A1 mini' (Bestand 2026-05-25)
    serial_nr TEXT,                     -- fuer Cloud-API
    geschlossenes_gehaeuse INTEGER DEFAULT 1,  -- A1 mini = 0
    aktiv INTEGER DEFAULT 1,
    notiz TEXT,
    -- Freigabe-Steuerung (Etappe 5-vorgezogen): Lehrer kann Drucker
    -- voruebergehend von Reservierung ausschliessen (Reparatur, Wartung).
    freigegeben INTEGER DEFAULT 1,      -- 1 = SuS koennen reservieren, 0 = gesperrt
    gesperrt_grund TEXT,                -- nur relevant wenn freigegeben=0
    -- Optional: Drucker-spezifische URL-Overrides ueber dem Modell-Default
    manual_url TEXT,
    troubleshooting_url TEXT
);

CREATE TABLE IF NOT EXISTS drucker_status (
    drucker_id INTEGER PRIMARY KEY,
    status TEXT,                        -- 'idle'|'printing'|'paused'|'finished'|'failed'|'offline'
    job_name TEXT,
    progress_pct REAL,
    restzeit_min INTEGER,
    bett_temp REAL,
    duese_temp REAL,
    fehlermeldung TEXT,
    gepollt_am DATETIME,
    manuell_gesetzt INTEGER DEFAULT 0,  -- 1 = Lehrer hat Override gesetzt
    FOREIGN KEY (drucker_id) REFERENCES drucker(id) ON DELETE CASCADE
);

-- ===========================================================================
-- Druck-Auftraege + Slots (Etappe 7)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS druckauftrag (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profil_id INTEGER NOT NULL,
    projekt_id INTEGER,
    titel TEXT NOT NULL,
    geschaetzte_dauer_min INTEGER,
    farbe TEXT,                         -- 'PLA rot', 'PLA+ schwarz' etc.
    notiz TEXT,
    status TEXT DEFAULT 'geplant',      -- 'geplant'|'reserviert'|'druckt'|'fertig'|'abgeholt'|'fehlgeschlagen'|'abgebrochen'
    drucker_id INTEGER,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE,
    FOREIGN KEY (projekt_id) REFERENCES projekt(id) ON DELETE SET NULL,
    FOREIGN KEY (drucker_id) REFERENCES drucker(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS slot (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    drucker_id INTEGER NOT NULL,
    profil_id INTEGER NOT NULL,
    druckauftrag_id INTEGER,
    start_zeit DATETIME NOT NULL,
    ende_zeit DATETIME NOT NULL,
    prioritaet INTEGER DEFAULT 0,
    status TEXT DEFAULT 'reserviert',   -- 'reserviert'|'aktiv'|'beendet'|'storniert'
    notiz TEXT,
    erstellt_am DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (drucker_id) REFERENCES drucker(id) ON DELETE CASCADE,
    FOREIGN KEY (profil_id) REFERENCES profil(id) ON DELETE CASCADE,
    FOREIGN KEY (druckauftrag_id) REFERENCES druckauftrag(id) ON DELETE SET NULL
);

-- ===========================================================================
-- Lektionen (Onboarding-Pfad + freie Module)
-- ===========================================================================
CREATE TABLE IF NOT EXISTS lektion (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nummer INTEGER UNIQUE NOT NULL,
    titel TEXT NOT NULL,
    onboarding INTEGER DEFAULT 0,       -- 1 = Pflicht fuer neue SuS (Onboarding-Pfad)
    aufgabe_md TEXT NOT NULL,
    tipp_md TEXT,
    extern_link TEXT,
    schaltplan_bild BLOB,
    schaltplan_mime TEXT,
    schwierigkeit TEXT DEFAULT 'leicht',
    aktiv INTEGER DEFAULT 1
);

-- ===========================================================================
-- Software-Sektion (Etappe 3 — Karten mit Bild + Kurzbeschreibung + Links)
-- ===========================================================================
-- Wir halten uns absichtlich raus: SuS haben die Software ohnehin auf den
-- Schul-PCs / im Browser und machen eigene Erfahrungen. Wir geben nur die
-- Auswahl vor (Karten-Liste mit Hauptlink + Tutorial-Link, ggf. Store-Links
-- mit QR-Code). Keine Detail-Seite.
CREATE TABLE IF NOT EXISTS software_seite (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug TEXT UNIQUE NOT NULL,             -- 'bambu-studio', 'gridfinity', ...
    titel TEXT NOT NULL,
    kurzbeschreibung TEXT NOT NULL,        -- 1-2 Saetze, sichtbar auf der Karte
    url TEXT NOT NULL,                     -- Hauptlink (Startseite / Download)
    tutorial_url TEXT,                     -- optionaler deutscher Einstieg
    plattform TEXT,                        -- Klartext: 'Browser', 'Windows/macOS/Linux', ...
    app_store_url TEXT,                    -- nur bei Apps
    play_store_url TEXT,                   -- nur bei Apps
    bild_pfad TEXT,                        -- alt: relativ zu static/img/software/, optional Fallback
    bild_blob BLOB,                        -- per Crop-Tool hochgeladen (Default-Quelle)
    bild_mime TEXT,                        -- z.B. 'image/jpeg'
    kategorie TEXT NOT NULL DEFAULT 'software',  -- 'software'|'app'|'generator'
    sortierung INTEGER DEFAULT 100,
    aktiv INTEGER DEFAULT 1
);

-- ===========================================================================
-- Einstellungen (Inline-Edit-Konfig) + Audit-Log
-- ===========================================================================
CREATE TABLE IF NOT EXISTS einstellung (
    schluessel TEXT PRIMARY KEY,
    wert TEXT NOT NULL,
    typ TEXT DEFAULT 'text',
    beschreibung TEXT,
    geaendert_am DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS aenderung_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tabelle TEXT NOT NULL,
    datensatz_id INTEGER,
    feld TEXT,
    alter_wert TEXT,
    neuer_wert TEXT,
    durch_profil_id INTEGER,            -- Lehrer (NULL = system/admin)
    geaendert_am DATETIME DEFAULT CURRENT_TIMESTAMP
);
"""


def init_db():
    """Legt alle Tabellen an (idempotent)."""
    conn = get_db()
    conn.executescript(SCHEMA)
    conn.commit()
    conn.close()
    print(f"Schema initialisiert in {DB_PFAD}")


if __name__ == "__main__":
    init_db()