-- ============================================================================
-- schema.sql · Schulbibliothek · Musterlösung
-- ----------------------------------------------------------------------------
-- Reihenfolge der Tabellen folgt den Fremdschlüssel-Abhängigkeiten:
--   Klasse  →  Schueler
--   Buch    →  Buch_Genre  ←  Genre
--   Schueler + Buch  →  Ausleihe
-- ============================================================================

PRAGMA foreign_keys = ON;          -- SQLite muss FKs explizit aktivieren

-- ---------------------------------------------------------------------------
-- Klasse  (Eltern-Tabelle, wird von Schueler referenziert)
-- ---------------------------------------------------------------------------
CREATE TABLE Klasse (
  id            INTEGER PRIMARY KEY,
  bezeichnung   VARCHAR(8)   NOT NULL UNIQUE,        -- z.B. '3a', '3b'
  stufe         INTEGER      NOT NULL
                  CHECK (stufe BETWEEN 1 AND 6)
);

-- ---------------------------------------------------------------------------
-- Schueler  (Kind von Klasse — beim Löschen einer Klasse wird klasse_id NULL)
-- ---------------------------------------------------------------------------
CREATE TABLE Schueler (
  id            INTEGER PRIMARY KEY,
  vorname       VARCHAR(60)  NOT NULL,
  nachname      VARCHAR(60)  NOT NULL,
  klasse_id     INTEGER
                  REFERENCES Klasse(id)
                  ON DELETE SET NULL,
  geburtsdatum  DATE         NOT NULL
                  CHECK (geburtsdatum >= '2005-01-01'
                         AND geburtsdatum <= '2020-12-31')
);

-- ---------------------------------------------------------------------------
-- Buch  (ISBN als natürlicher Primärschlüssel)
-- ---------------------------------------------------------------------------
CREATE TABLE Buch (
  isbn          VARCHAR(17)  PRIMARY KEY,            -- z.B. '978-3-446-26684-2'
  titel         VARCHAR(200) NOT NULL,
  autor         VARCHAR(100) NOT NULL,
  jahr          INTEGER
                  CHECK (jahr BETWEEN 1500 AND 2100),
  exemplare     INTEGER      NOT NULL DEFAULT 1
                  CHECK (exemplare >= 0)
);

-- ---------------------------------------------------------------------------
-- Genre
-- ---------------------------------------------------------------------------
CREATE TABLE Genre (
  id            INTEGER PRIMARY KEY,
  name          VARCHAR(40)  NOT NULL UNIQUE
);

-- ---------------------------------------------------------------------------
-- Buch_Genre  (n:m-Verbindungstabelle Buch ↔ Genre)
-- Bei Löschen eines Buches oder Genres verschwinden die Zuordnungen mit.
-- ---------------------------------------------------------------------------
CREATE TABLE Buch_Genre (
  isbn          VARCHAR(17)  NOT NULL
                  REFERENCES Buch(isbn) ON DELETE CASCADE,
  genre_id      INTEGER      NOT NULL
                  REFERENCES Genre(id) ON DELETE CASCADE,
  PRIMARY KEY (isbn, genre_id)
);

-- ---------------------------------------------------------------------------
-- Ausleihe  (n:m-Beziehung Schueler ↔ Buch über die Zeit hinweg)
-- ---------------------------------------------------------------------------
-- Statt eines zusammengesetzten PKs aus (schueler_id, isbn, ausleih_datum)
-- nutzen wir eine künstliche id — sonst könnte derselbe Schüler ein Buch nicht
-- mehrfach am gleichen Tag ausleihen (Spezialfall, aber sauberer so).
CREATE TABLE Ausleihe (
  id              INTEGER PRIMARY KEY,
  schueler_id     INTEGER  NOT NULL
                    REFERENCES Schueler(id) ON DELETE CASCADE,
  isbn            VARCHAR(17) NOT NULL
                    REFERENCES Buch(isbn),
  ausleih_datum   DATE     NOT NULL,
  rueckgabe_datum DATE
                    CHECK (rueckgabe_datum IS NULL
                           OR rueckgabe_datum >= ausleih_datum)
);

-- ---------------------------------------------------------------------------
-- INDEX  (Performance-Hinweis: alle Use-Case-Queries filtern auf schueler_id)
-- ---------------------------------------------------------------------------
CREATE INDEX idx_ausleihe_schueler ON Ausleihe (schueler_id);
CREATE INDEX idx_ausleihe_isbn     ON Ausleihe (isbn);

-- ---------------------------------------------------------------------------
-- VIEW  (Komfort-Sicht auf aktuell laufende Ausleihen)
-- ---------------------------------------------------------------------------
CREATE VIEW AktiveAusleihen AS
SELECT  a.id            AS ausleih_id,
        s.vorname || ' ' || s.nachname AS schueler,
        k.bezeichnung   AS klasse,
        b.titel,
        b.autor,
        a.ausleih_datum
FROM    Ausleihe a
  JOIN  Schueler s ON s.id = a.schueler_id
  LEFT JOIN Klasse k ON k.id = s.klasse_id
  JOIN  Buch     b ON b.isbn = a.isbn
WHERE   a.rueckgabe_datum IS NULL;
