-- Tabellenerstellung für die Lerncodes
CREATE TABLE IF NOT EXISTS material_lerncodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    original_text_id INT NULL,
    lerncode VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    FOREIGN KEY (original_text_id) REFERENCES original_texts(id) ON DELETE SET NULL,
    UNIQUE KEY (lerncode),
    UNIQUE KEY (material_id, original_text_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hinweis: Führen Sie dieses Skript aus, um die Datenbank zu aktualisieren:
-- mysql -u[USERNAME] -p[PASSWORD] [DATENBANKNAME] < create_lerncode_table.sql