🐚 WEB SHELL ACTIVATED

📁 File Browser

Current directory: /home/klas4s23/domains/585455.klas4s23.mid-ica.nl/public_html/Gastenboek/uploads

📄 Viewing: ./../../l7-pro-2-e-learning-2023-Epolleke/docs/../database/schema.sql

-- English Vocabulary Learning Database Schema
-- Dit script maakt alle benodigde tabellen aan

-- Database aanmaken (indien nog niet bestaat)
CREATE DATABASE IF NOT EXISTS english_learning_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE english_learning_app;

-- Tabel 1: Users (Gebruikers accounts)
CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel 2: Categories (Categorieën voor woorden)
CREATE TABLE IF NOT EXISTS categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category_name (category_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel 3: Words (Engelse woorden met vertalingen)
CREATE TABLE IF NOT EXISTS words (
    word_id INT AUTO_INCREMENT PRIMARY KEY,
    english_word VARCHAR(100) NOT NULL,
    dutch_translation VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    difficulty_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
    INDEX idx_english_word (english_word),
    INDEX idx_difficulty (difficulty_level),
    INDEX idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel 4: User Progress (Voortgang per woord per gebruiker)
CREATE TABLE IF NOT EXISTS user_progress (
    progress_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    session_id VARCHAR(100) NOT NULL,
    word_id INT NOT NULL,
    attempts INT DEFAULT 0,
    correct INT DEFAULT 0,
    last_attempt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_mastered BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (word_id) REFERENCES words(word_id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_session (session_id),
    INDEX idx_word (word_id),
    INDEX idx_mastered (is_mastered)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel 5: Practice Sessions (Overzicht van oefensessies)
CREATE TABLE IF NOT EXISTS practice_sessions (
    session_log_id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(100) NOT NULL UNIQUE,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL,
    total_words INT DEFAULT 0,
    correct_answers INT DEFAULT 0,
    score_percentage DECIMAL(5,2) DEFAULT 0.00,
    INDEX idx_session_id (session_id),
    INDEX idx_start_time (start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel 6: Word Lists (Aangepaste woordenlijsten van gebruikers)
CREATE TABLE IF NOT EXISTS word_lists (
    list_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    list_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_public (is_public),
    INDEX idx_name (list_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel 7: List Words (Woorden in aangepaste lijsten)
CREATE TABLE IF NOT EXISTS list_words (
    list_word_id INT AUTO_INCREMENT PRIMARY KEY,
    list_id INT NOT NULL,
    english_word VARCHAR(100) NOT NULL,
    dutch_translation VARCHAR(100) NOT NULL,
    difficulty_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (list_id) REFERENCES word_lists(list_id) ON DELETE CASCADE,
    INDEX idx_list (list_id),
    INDEX idx_difficulty (difficulty_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Voorbeelddata invoegen
-- Categorieën
INSERT INTO categories (category_name, description) VALUES
('Animals', 'Words related to animals and pets'),
('Food', 'Words about food and drinks'),
('Verbs', 'Common English verbs'),
('Colors', 'Names of colors'),
('Numbers', 'Numbers and counting'),
('Family', 'Family members and relationships'),
('Body', 'Parts of the body'),
('Weather', 'Weather-related vocabulary');

-- Woorden - Animals
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('cat', 'kat', 1, 'beginner'),
('dog', 'hond', 1, 'beginner'),
('bird', 'vogel', 1, 'beginner'),
('fish', 'vis', 1, 'beginner'),
('horse', 'paard', 1, 'beginner'),
('elephant', 'olifant', 1, 'intermediate'),
('butterfly', 'vlinder', 1, 'intermediate'),
('dolphin', 'dolfijn', 1, 'intermediate');

-- Woorden - Food
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('apple', 'appel', 2, 'beginner'),
('bread', 'brood', 2, 'beginner'),
('cheese', 'kaas', 2, 'beginner'),
('water', 'water', 2, 'beginner'),
('chicken', 'kip', 2, 'beginner'),
('strawberry', 'aardbei', 2, 'intermediate'),
('cucumber', 'komkommer', 2, 'intermediate'),
('pineapple', 'ananas', 2, 'intermediate');

-- Woorden - Verbs
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('walk', 'lopen', 3, 'beginner'),
('run', 'rennen', 3, 'beginner'),
('eat', 'eten', 3, 'beginner'),
('drink', 'drinken', 3, 'beginner'),
('sleep', 'slapen', 3, 'beginner'),
('think', 'denken', 3, 'intermediate'),
('understand', 'begrijpen', 3, 'intermediate'),
('remember', 'herinneren', 3, 'advanced');

-- Woorden - Colors
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('red', 'rood', 4, 'beginner'),
('blue', 'blauw', 4, 'beginner'),
('green', 'groen', 4, 'beginner'),
('yellow', 'geel', 4, 'beginner'),
('purple', 'paars', 4, 'beginner'),
('orange', 'oranje', 4, 'beginner'),
('pink', 'roze', 4, 'beginner'),
('brown', 'bruin', 4, 'beginner');

-- Woorden - Numbers
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('one', 'een', 5, 'beginner'),
('two', 'twee', 5, 'beginner'),
('three', 'drie', 5, 'beginner'),
('four', 'vier', 5, 'beginner'),
('five', 'vijf', 5, 'beginner'),
('ten', 'tien', 5, 'beginner'),
('twenty', 'twintig', 5, 'intermediate'),
('hundred', 'honderd', 5, 'intermediate');

-- Woorden - Family
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('mother', 'moeder', 6, 'beginner'),
('father', 'vader', 6, 'beginner'),
('sister', 'zus', 6, 'beginner'),
('brother', 'broer', 6, 'beginner'),
('grandmother', 'oma', 6, 'beginner'),
('grandfather', 'opa', 6, 'beginner'),
('aunt', 'tante', 6, 'intermediate'),
('uncle', 'oom', 6, 'intermediate');

-- Woorden - Body
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('head', 'hoofd', 7, 'beginner'),
('hand', 'hand', 7, 'beginner'),
('foot', 'voet', 7, 'beginner'),
('eye', 'oog', 7, 'beginner'),
('ear', 'oor', 7, 'beginner'),
('nose', 'neus', 7, 'beginner'),
('mouth', 'mond', 7, 'beginner'),
('finger', 'vinger', 7, 'beginner');

-- Woorden - Weather
INSERT INTO words (english_word, dutch_translation, category_id, difficulty_level) VALUES
('sun', 'zon', 8, 'beginner'),
('rain', 'regen', 8, 'beginner'),
('wind', 'wind', 8, 'beginner'),
('snow', 'sneeuw', 8, 'beginner'),
('cloud', 'wolk', 8, 'beginner'),
('storm', 'storm', 8, 'intermediate'),
('lightning', 'bliksem', 8, 'intermediate'),
('thunder', 'donder', 8, 'intermediate');

-- Demo gebruiker toevoegen (username: demo, password: demo123)
INSERT INTO users (username, email, password_hash, full_name) VALUES
('demo', 'demo@example.com', '$2y$10$LpsqLo0QF.qm7kW23lwoLue50R2gALevpzdQ49VHmEQRRuERfT68C', 'Demo User');
-- Note: password is 'demo123'

🎯 Available Actions