DeutschLernen/docs/database/initial-database-schema.sql

-- Enable UUID extension (optional, but useful for unique IDs)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

---
--- **1. Users and Authentication**
---

-- Users table
CREATE TABLE Users (
    Id SERIAL PRIMARY KEY,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    Role VARCHAR(20) DEFAULT 'user' CHECK (Role IN ('user', 'admin')),
    LastLoginAt TIMESTAMP,
    CreatedAt TIMESTAMP DEFAULT NOW()
);

---
--- **2. Learning Content: Levels, Lessons, and Dependencies**
---

-- Levels table
CREATE TABLE Levels (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(10) UNIQUE NOT NULL,  -- e.g., "A1", "A2"
    Description TEXT,
    Order INT UNIQUE NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE
);

-- Lessons table
CREATE TABLE Lessons (
    Id SERIAL PRIMARY KEY,
    LevelId INT REFERENCES Levels(Id) ON DELETE CASCADE,
    Title VARCHAR(100) NOT NULL,
    Description TEXT,
    Order INT NOT NULL,
    EstimatedMinutes INT,
    UNIQUE(LevelId, Order)
);

---
--- **3. Vocabulary and Word Details**
---

-- Vocabulary table (core words)
CREATE TABLE Vocabulary (
    Id SERIAL PRIMARY KEY,
    LessonId INT REFERENCES Lessons(Id) ON DELETE CASCADE,
    BaseWord VARCHAR(50) NOT NULL,  -- e.g., "gehen", "Buch"
    UNIQUE(LessonId, BaseWord)
);

-- WordMetadata table (metadata for vocabulary, e.g., frequency, part of speech)
CREATE TABLE WordMetadata (
    Id SERIAL PRIMARY KEY,
    VocabularyId INT REFERENCES Vocabulary(Id) ON DELETE CASCADE,
    Translation VARCHAR(100) NOT NULL,
    WordClass VARCHAR(20) NOT NULL CHECK (WordClass IN ('noun', 'verb', 'adjective', 'adverb', 'preposition', 'conjunction', 'pronoun', 'article', 'other')),
    Frequency INT CHECK (Frequency BETWEEN 1 AND 5),  -- 1 = rare, 5 = very common
    AudioUrl VARCHAR(255) NOT NULL,
    ImageUrl VARCHAR(255)
);

-- WordForms table (inflections, plurals, conjugations, etc.)
CREATE TABLE WordForms (
    Id SERIAL PRIMARY KEY,
    VocabularyId INT REFERENCES Vocabulary(Id) ON DELETE CASCADE,
    FormType VARCHAR(20) NOT NULL CHECK (FormType IN ('plural', 'singular', 'infinitive', 'past_tense', 'past_participle', 'present_participle', 'conjugation_ich', 'conjugation_du', 'conjugation_er', 'conjugation_wir', 'conjugation_ihr', 'conjugation_sie', 'dative', 'accusative', 'genitive', 'nominative')),
    Form VARCHAR(50) NOT NULL,
    Article VARCHAR(10) CHECK (Article IN ('der', 'die', 'das', '')),  -- Only for nouns
    AudioUrl VARCHAR(255),
    UNIQUE(VocabularyId, FormType, Form)
);

---
--- **4. Story Segments**
---

-- StorySegments table
CREATE TABLE StorySegments (
    Id SERIAL PRIMARY KEY,
    LevelId INT REFERENCES Levels(Id) ON DELETE CASCADE,
    LessonId INT REFERENCES Lessons(Id) ON DELETE CASCADE NOT NULL,  -- Required
    Title VARCHAR(100) NOT NULL,
    Content TEXT NOT NULL,
    AudioUrl VARCHAR(255) NOT NULL,
    Order INT NOT NULL,
    IsUnlocked BOOLEAN DEFAULT FALSE,  -- Managed by the app logic (unlocked via lesson completion)
    UNIQUE(LevelId, Order)
);

---
--- **5. Quizzes and Questions**
---

-- Quizzes table
CREATE TABLE Quizzes (
    Id SERIAL PRIMARY KEY,
    LessonId INT REFERENCES Lessons(Id) ON DELETE CASCADE,
    Title VARCHAR(100) NOT NULL,
    Description TEXT,
    PassingScore INT DEFAULT 80
);

-- Questions table
CREATE TABLE Questions (
    Id SERIAL PRIMARY KEY,
    QuizId INT REFERENCES Quizzes(Id) ON DELETE CASCADE,
    Type VARCHAR(20) NOT NULL CHECK (Type IN ('mcq', 'fill_in', 'listening', 'matching')),
    Content TEXT NOT NULL,
    CorrectAnswer TEXT NOT NULL,
    AudioUrl VARCHAR(255),
    AudioScript TEXT,  -- For listening questions: the text of the audio
    Explanation TEXT,
    Points INT DEFAULT 1,
    Difficulty INT CHECK (Difficulty BETWEEN 1 AND 5)
);

-- QuestionOptions table (normalized for MCQ)
CREATE TABLE QuestionOptions (
    Id SERIAL PRIMARY KEY,
    QuestionId INT REFERENCES Questions(Id) ON DELETE CASCADE,
    OptionText TEXT NOT NULL,
    Order INT NOT NULL,
    UNIQUE(QuestionId, Order)
);

---
--- **6. User Progress and Quiz Attempts**
---

-- UserProgress table
CREATE TABLE UserProgress (
    Id SERIAL PRIMARY KEY,
    UserId INT REFERENCES Users(Id) ON DELETE CASCADE,
    LessonId INT REFERENCES Lessons(Id) ON DELETE CASCADE,
    IsCompleted BOOLEAN DEFAULT FALSE,
    CurrentLevelId INT REFERENCES Levels(Id),  -- Moved from Users
    Streak INT DEFAULT 0,
    TotalPoints INT DEFAULT 0,
    TimeSpentMinutes INT DEFAULT 0,
    LastVisitedAt TIMESTAMP DEFAULT NOW(),
    UNIQUE(UserId, LessonId)
);

-- QuizAttempts table (tracks all attempts)
CREATE TABLE QuizAttempts (
    Id SERIAL PRIMARY KEY,
    UserId INT REFERENCES Users(Id) ON DELETE CASCADE,
    QuizId INT REFERENCES Quizzes(Id) ON DELETE CASCADE,
    Score INT NOT NULL,
    TotalPoints INT NOT NULL,
    Passed BOOLEAN NOT NULL,
    AttemptDate TIMESTAMP DEFAULT NOW(),
    TimeSpentSeconds INT
);

---
--- **7. Gamification**
---

-- Badges table
CREATE TABLE Badges (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description TEXT,
    ImageUrl VARCHAR(255),
    PointsRequired INT,
    Category VARCHAR(50),
    UNIQUE(Name)
);

-- UserBadges table
CREATE TABLE UserBadges (
    UserId INT REFERENCES Users(Id) ON DELETE CASCADE,
    BadgeId INT REFERENCES Badges(Id) ON DELETE CASCADE,
    EarnedDate TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (UserId, BadgeId)
);

---
--- **8. Practice Sessions**
---

-- PracticeSessions table
CREATE TABLE PracticeSessions (
    Id SERIAL PRIMARY KEY,
    UserId INT REFERENCES Users(Id) ON DELETE CASCADE,
    Type VARCHAR(20) NOT NULL CHECK (Type IN ('speaking', 'writing')),
    Prompt TEXT NOT NULL,
    UserResponse TEXT,
    Feedback TEXT,
    Score INT,
    TimeSpentSeconds INT,
    Date TIMESTAMP DEFAULT NOW()
);

---
--- **9. User Settings**
---

-- UserSettings table
CREATE TABLE UserSettings (
    UserId INT PRIMARY KEY REFERENCES Users(Id) ON DELETE CASCADE,
    AudioAutoplay BOOLEAN DEFAULT TRUE,
    Theme VARCHAR(20) DEFAULT 'light' CHECK (Theme IN ('light', 'dark')),
    NativeLanguage VARCHAR(50) DEFAULT 'en'
);

---
--- **10. Feedback**
---

-- Feedback table
CREATE TABLE Feedback (
    Id SERIAL PRIMARY KEY,
    UserId INT REFERENCES Users(Id) ON DELETE CASCADE,
    LessonId INT REFERENCES Lessons(Id) ON DELETE SET NULL,
    QuizId INT REFERENCES Quizzes(Id) ON DELETE SET NULL,
    Rating INT CHECK (Rating BETWEEN 1 AND 5),
    Comment TEXT,
    CreatedAt TIMESTAMP DEFAULT NOW(),
    CHECK (LessonId IS NOT NULL OR QuizId IS NOT NULL)
);

---
--- **11. Indexes for Performance**
---

CREATE INDEX idx_userprogress_userid ON UserProgress(UserId);
CREATE INDEX idx_userprogress_lessonid ON UserProgress(LessonId);
CREATE INDEX idx_vocabulary_lessonid ON Vocabulary(LessonId);
CREATE INDEX idx_wordmetadata_vocabularyid ON WordMetadata(VocabularyId);
CREATE INDEX idx_wordforms_vocabularyid ON WordForms(VocabularyId);
CREATE INDEX idx_questions_quizid ON Questions(QuizId);
CREATE INDEX idx_questionoptions_questionid ON QuestionOptions(QuestionId);
CREATE INDEX idx_quizattempts_userid ON QuizAttempts(UserId);
CREATE INDEX idx_quizattempts_quizid ON QuizAttempts(QuizId);
CREATE INDEX idx_storysegments_levelid ON StorySegments(LevelId);
CREATE INDEX idx_storysegments_lessonid ON StorySegments(LessonId);