CREATE TABLE profile (id BIGINT AUTO_INCREMENT, sf_guard_user_id INT, voornaam VARCHAR(255), achternaam VARCHAR(255), adres VARCHAR(255), postcode VARCHAR(255), woonplaats VARCHAR(255), email VARCHAR(255), is_doof TINYINT(1) DEFAULT '0', INDEX sf_guard_user_id_idx (sf_guard_user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE categories (id BIGINT AUTO_INCREMENT, name VARCHAR(10) NOT NULL, description VARCHAR(50), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE courses (id BIGINT AUTO_INCREMENT, code VARCHAR(10), naam VARCHAR(100), omschrijving VARCHAR(255), level_id BIGINT, basistekst VARCHAR(100), image VARCHAR(100), INDEX level_id_idx (level_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dictionaries (id MEDIUMINT AUTO_INCREMENT, woord VARCHAR(50) DEFAULT '' NOT NULL, spelling VARCHAR(250) DEFAULT '' NOT NULL, zinnen VARCHAR(250) DEFAULT '' NOT NULL, lrgrp VARCHAR(20) DEFAULT '' NOT NULL, klemtoon VARCHAR(20) DEFAULT '' NOT NULL, les CHAR(2) DEFAULT '' NOT NULL, klem_pic VARCHAR(25) DEFAULT '' NOT NULL, woord_pic VARCHAR(50) DEFAULT '' NOT NULL, wav VARCHAR(50) DEFAULT '' NOT NULL, hits MEDIUMINT DEFAULT '0' NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE file_uploads (id BIGINT UNSIGNED AUTO_INCREMENT, file_name VARCHAR(50) DEFAULT '' NOT NULL, mime_type VARCHAR(32) NOT NULL, file_size VARCHAR(50) NOT NULL, subdir VARCHAR(25) NOT NULL, extra_field VARCHAR(50), created DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, fmodified DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE lessons (id BIGINT AUTO_INCREMENT, course_id BIGINT NOT NULL, level_id BIGINT NOT NULL, categorie_id BIGINT NOT NULL, lestekst_geluid VARCHAR(255), code VARCHAR(10), instructie LONGTEXT NOT NULL, vraag LONGTEXT, toets TINYINT(1) DEFAULT '0', volgnummer BIGINT, type_id BIGINT NOT NULL, bijlage VARCHAR(255), video VARCHAR(255), INDEX course_id_idx (course_id), INDEX level_id_idx (level_id), INDEX categorie_id_idx (categorie_id), INDEX type_id_idx (type_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE levels (id BIGINT AUTO_INCREMENT, levelnaam VARCHAR(50) NOT NULL, omschrijving VARCHAR(255) DEFAULT '' NOT NULL, kleur VARCHAR(10) DEFAULT '' NOT NULL, code CHAR(2), for_deaf TINYINT(1) DEFAULT '0', PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE messages (id BIGINT AUTO_INCREMENT, from_user INT NOT NULL, to_user INT NOT NULL, message text NOT NULL, is_read TINYINT(1) DEFAULT '0', file_name VARCHAR(255), file_type VARCHAR(30), INDEX from_user_idx (from_user), INDEX to_user_idx (to_user), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE questions (id BIGINT AUTO_INCREMENT, vraag LONGTEXT, lesson_id BIGINT NOT NULL, geluid VARCHAR(255), media VARCHAR(255), voorbeeld TINYINT(1) DEFAULT '0', voorbeeldvolgnummer BIGINT, toets TINYINT(1) DEFAULT '0', code1 VARCHAR(10), code2 VARCHAR(10), code3 VARCHAR(10), vraagnummer BIGINT, antwoord1 VARCHAR(255), antwoord2 VARCHAR(255), antwoord3 VARCHAR(255), antwoord4 VARCHAR(255), antwoord5 VARCHAR(255), correct BIGINT, INDEX lesson_id_idx (lesson_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE sf_guard_group (id INT AUTO_INCREMENT, name VARCHAR(255) UNIQUE, description TEXT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE sf_guard_group_permission (group_id INT, permission_id INT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(group_id, permission_id)) ENGINE = INNODB;
CREATE TABLE sf_guard_permission (id INT AUTO_INCREMENT, name VARCHAR(255) UNIQUE, description TEXT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE sf_guard_remember_key (id INT AUTO_INCREMENT, user_id INT, remember_key VARCHAR(32), ip_address VARCHAR(50), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX user_id_idx (user_id), PRIMARY KEY(id, ip_address)) ENGINE = INNODB;
CREATE TABLE sf_guard_user (id INT AUTO_INCREMENT, username VARCHAR(128) NOT NULL UNIQUE, algorithm VARCHAR(128) DEFAULT 'sha1' NOT NULL, salt VARCHAR(128), password VARCHAR(128), is_active TINYINT(1) DEFAULT '1', is_super_admin TINYINT(1) DEFAULT '0', last_login DATETIME, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX is_active_idx_idx (is_active), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE sf_guard_user_group (user_id INT, group_id INT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(user_id, group_id)) ENGINE = INNODB;
CREATE TABLE sf_guard_user_permission (user_id INT, permission_id INT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(user_id, permission_id)) ENGINE = INNODB;
CREATE TABLE types (id BIGINT AUTO_INCREMENT, naam VARCHAR(50) DEFAULT '' NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE userlogs (id BIGINT AUTO_INCREMENT, user_id INT NOT NULL, lesson_id BIGINT NOT NULL, lesson_code VARCHAR(10) NOT NULL, score BIGINT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX lesson_id_idx (lesson_id), INDEX user_id_idx (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE profile ADD CONSTRAINT profile_sf_guard_user_id_sf_guard_user_id FOREIGN KEY (sf_guard_user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE courses ADD CONSTRAINT courses_level_id_levels_id FOREIGN KEY (level_id) REFERENCES levels(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE lessons ADD CONSTRAINT lessons_type_id_types_id FOREIGN KEY (type_id) REFERENCES types(id);
ALTER TABLE lessons ADD CONSTRAINT lessons_level_id_levels_id FOREIGN KEY (level_id) REFERENCES levels(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE lessons ADD CONSTRAINT lessons_course_id_courses_id FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE lessons ADD CONSTRAINT lessons_categorie_id_categories_id FOREIGN KEY (categorie_id) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE messages ADD CONSTRAINT messages_to_user_sf_guard_user_id FOREIGN KEY (to_user) REFERENCES sf_guard_user(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE messages ADD CONSTRAINT messages_from_user_sf_guard_user_id FOREIGN KEY (from_user) REFERENCES sf_guard_user(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE questions ADD CONSTRAINT questions_lesson_id_lessons_id FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE sf_guard_group_permission ADD CONSTRAINT sf_guard_group_permission_permission_id_sf_guard_permission_id FOREIGN KEY (permission_id) REFERENCES sf_guard_permission(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_group_permission ADD CONSTRAINT sf_guard_group_permission_group_id_sf_guard_group_id FOREIGN KEY (group_id) REFERENCES sf_guard_group(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_remember_key ADD CONSTRAINT sf_guard_remember_key_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_group ADD CONSTRAINT sf_guard_user_group_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_group ADD CONSTRAINT sf_guard_user_group_group_id_sf_guard_group_id FOREIGN KEY (group_id) REFERENCES sf_guard_group(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_permission ADD CONSTRAINT sf_guard_user_permission_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE;
ALTER TABLE sf_guard_user_permission ADD CONSTRAINT sf_guard_user_permission_permission_id_sf_guard_permission_id FOREIGN KEY (permission_id) REFERENCES sf_guard_permission(id) ON DELETE CASCADE;
ALTER TABLE userlogs ADD CONSTRAINT userlogs_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE userlogs ADD CONSTRAINT userlogs_lesson_id_lessons_id FOREIGN KEY (lesson_id) REFERENCES lessons(id);
