SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS lams_activity_category;


CREATE TABLE lams_activity_category (
  activity_category_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (activity_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_activity_evaluation;


CREATE TABLE lams_activity_evaluation (
  activity_id bigint NOT NULL,
  tool_output_definition varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  weight tinyint DEFAULT NULL,
  PRIMARY KEY (activity_id),
  KEY activity_id (activity_id),
  CONSTRAINT FK_lams_activity_evaluation_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_auth_method_type;


CREATE TABLE lams_auth_method_type (
  authentication_method_type_id int NOT NULL,
  `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (authentication_method_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_authentication_method;


CREATE TABLE lams_authentication_method (
  authentication_method_id bigint NOT NULL,
  authentication_method_type_id int NOT NULL DEFAULT '0',
  authentication_method_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (authentication_method_id),
  UNIQUE KEY UQ_lams_authentication_method_1 (authentication_method_name),
  KEY authentication_method_type_id (authentication_method_type_id),
  CONSTRAINT FK_lams_authorization_method_1 FOREIGN KEY (authentication_method_type_id) REFERENCES lams_auth_method_type (authentication_method_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_branch_activity_entry;


CREATE TABLE lams_branch_activity_entry (
  entry_id bigint NOT NULL AUTO_INCREMENT,
  entry_ui_id int DEFAULT NULL,
  group_id bigint DEFAULT NULL,
  sequence_activity_id bigint DEFAULT NULL,
  branch_activity_id bigint NOT NULL,
  condition_id bigint DEFAULT NULL,
  open_gate tinyint(1) DEFAULT NULL,
  PRIMARY KEY (entry_id),
  KEY group_id (group_id),
  KEY sequence_activity_id (sequence_activity_id),
  KEY branch_activity_id (branch_activity_id),
  KEY condition_id (condition_id),
  CONSTRAINT FK_lams_branch_activity_entry_4 FOREIGN KEY (condition_id) REFERENCES lams_branch_condition (condition_id),
  CONSTRAINT FK_lams_branch_map_branch FOREIGN KEY (branch_activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT FK_lams_branch_map_sequence FOREIGN KEY (sequence_activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT FK_lams_group_activity_1 FOREIGN KEY (group_id) REFERENCES lams_group (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_branch_condition;


CREATE TABLE lams_branch_condition (
  condition_id bigint NOT NULL AUTO_INCREMENT,
  condition_ui_id int DEFAULT NULL,
  order_id int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  start_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  end_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  exact_match_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (condition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_comment;


CREATE TABLE lams_comment (
  uid bigint NOT NULL AUTO_INCREMENT,
  session_id bigint NOT NULL,
  body mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  create_by bigint DEFAULT NULL,
  create_date datetime DEFAULT NULL,
  update_date datetime DEFAULT NULL,
  update_by bigint DEFAULT NULL,
  last_modified datetime DEFAULT NULL,
  last_reply_date datetime DEFAULT NULL,
  reply_number int DEFAULT NULL,
  hide_flag smallint DEFAULT NULL,
  parent_uid bigint DEFAULT NULL,
  root_comment_uid bigint DEFAULT NULL,
  comment_level smallint DEFAULT NULL,
  thread_comment_uid bigint DEFAULT NULL,
  sticky smallint DEFAULT '0',
  monitor smallint DEFAULT '0',
  anonymous smallint DEFAULT '0',
  PRIMARY KEY (uid),
  KEY FK_comment_session (session_id),
  KEY FK_comment_create (create_by),
  KEY FK_comment_modify (update_by),
  KEY FK_comment_parent (parent_uid),
  KEY FK_comment_root (root_comment_uid),
  KEY FK_comment_thread (thread_comment_uid),
  KEY IX_comment_level_sticky (comment_level,sticky),
  CONSTRAINT FK_comment_create FOREIGN KEY (create_by) REFERENCES lams_user (user_id),
  CONSTRAINT FK_comment_modify FOREIGN KEY (update_by) REFERENCES lams_user (user_id),
  CONSTRAINT FK_comment_parent FOREIGN KEY (parent_uid) REFERENCES lams_comment (uid),
  CONSTRAINT FK_comment_root FOREIGN KEY (root_comment_uid) REFERENCES lams_comment (uid),
  CONSTRAINT FK_comment_session FOREIGN KEY (session_id) REFERENCES lams_comment_session (uid),
  CONSTRAINT FK_comment_thread FOREIGN KEY (thread_comment_uid) REFERENCES lams_comment (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_comment_likes;


CREATE TABLE lams_comment_likes (
  uid bigint NOT NULL AUTO_INCREMENT,
  comment_uid bigint NOT NULL,
  user_id bigint NOT NULL,
  vote tinyint(1) DEFAULT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY comment_like_unique (comment_uid,user_id),
  KEY FK_commentlike_comment (comment_uid),
  KEY FK_commentlike_user (user_id),
  CONSTRAINT FK_commentlike_comment FOREIGN KEY (comment_uid) REFERENCES lams_comment (uid),
  CONSTRAINT FK_commentlike_user FOREIGN KEY (user_id) REFERENCES lams_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_comment_session;


CREATE TABLE lams_comment_session (
  uid bigint NOT NULL AUTO_INCREMENT,
  external_id bigint DEFAULT NULL,
  external_id_type int DEFAULT NULL,
  external_signature varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  external_secondary_id bigint DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY comment_ext_sig (external_id,external_signature)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_competence;


CREATE TABLE lams_competence (
  competence_id bigint NOT NULL AUTO_INCREMENT,
  learning_design_id bigint DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  title varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (competence_id),
  UNIQUE KEY competence_id (competence_id),
  UNIQUE KEY learning_design_id (learning_design_id,title),
  CONSTRAINT LearningDesignCompetenceMap FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_competence_mapping;


CREATE TABLE lams_competence_mapping (
  competence_mapping_id bigint NOT NULL AUTO_INCREMENT,
  competence_id bigint DEFAULT NULL,
  activity_id bigint DEFAULT NULL,
  PRIMARY KEY (competence_mapping_id),
  UNIQUE KEY competence_mapping_id (competence_mapping_id),
  UNIQUE KEY competence_id (competence_id,activity_id),
  KEY activity_id (activity_id),
  CONSTRAINT FK_lams_competence_mapping_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_competence_mapping_2 FOREIGN KEY (competence_id) REFERENCES lams_competence (competence_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_configuration;


CREATE TABLE lams_configuration (
  config_key varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  config_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  description_key varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  header_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `format` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  required tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_copy_type;


CREATE TABLE lams_copy_type (
  copy_type_id tinyint NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (copy_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_cr_credential;


CREATE TABLE lams_cr_credential (
  credential_id bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (credential_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_cr_node;


DROP TABLE IF EXISTS `lams_cr_node`;

CREATE TABLE `lams_cr_node` (
  `node_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `portrait_uuid` BINARY(16),
  `workspace_id` bigint(20) unsigned NOT NULL,
  `path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_date_time` datetime NOT NULL,
  `next_version_id` bigint(20) unsigned NOT NULL DEFAULT '1',
  `parent_nv_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`node_id`),
  UNIQUE INDEX IDX_portrait_uuid (portrait_uuid),
  KEY `workspace_id` (`workspace_id`),
  CONSTRAINT `FK_lams_cr_node_1` FOREIGN KEY (`workspace_id`) REFERENCES `lams_cr_workspace` (`workspace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_cr_node_version;


CREATE TABLE lams_cr_node_version (
  nv_id bigint unsigned NOT NULL AUTO_INCREMENT,
  node_id bigint unsigned NOT NULL,
  version_id bigint unsigned NOT NULL,
  created_date_time datetime NOT NULL,
  user_id bigint NOT NULL,
  PRIMARY KEY (nv_id),
  KEY node_id (node_id),
  CONSTRAINT FK_lams_cr_node_version_2 FOREIGN KEY (node_id) REFERENCES lams_cr_node (node_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_cr_node_version_property;


CREATE TABLE lams_cr_node_version_property (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  nv_id bigint unsigned NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` tinyint NOT NULL,
  PRIMARY KEY (id),
  KEY nv_id (nv_id),
  CONSTRAINT FK_lams_cr_node_version_property_1 FOREIGN KEY (nv_id) REFERENCES lams_cr_node_version (nv_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_cr_workspace;


CREATE TABLE lams_cr_workspace (
  workspace_id bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (workspace_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_cr_workspace_credential;


CREATE TABLE lams_cr_workspace_credential (
  wc_id bigint unsigned NOT NULL AUTO_INCREMENT,
  workspace_id bigint unsigned NOT NULL,
  credential_id bigint unsigned NOT NULL,
  PRIMARY KEY (wc_id),
  KEY workspace_id (workspace_id),
  KEY credential_id (credential_id),
  CONSTRAINT FK_lams_cr_workspace_credential_1 FOREIGN KEY (workspace_id) REFERENCES lams_cr_workspace (workspace_id),
  CONSTRAINT FK_lams_cr_workspace_credential_2 FOREIGN KEY (credential_id) REFERENCES lams_cr_credential (credential_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_data_flow;


CREATE TABLE lams_data_flow (
  data_flow_object_id bigint NOT NULL AUTO_INCREMENT,
  transition_id bigint NOT NULL,
  order_id int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  tool_assigment_id int DEFAULT NULL,
  PRIMARY KEY (data_flow_object_id),
  KEY FK_lams_learning_transition_1 (transition_id),
  CONSTRAINT FK_lams_learning_transition_1 FOREIGN KEY (transition_id) REFERENCES lams_learning_transition (transition_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_email_notification_archive;


CREATE TABLE lams_email_notification_archive (
  uid bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint DEFAULT NULL,
  lesson_id bigint DEFAULT NULL,
  search_type tinyint NOT NULL,
  sent_on datetime NOT NULL,
  body mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (uid),
  KEY FK_lams_email_notification_archive_1 (organisation_id),
  KEY FK_lams_email_notification_archive_2 (lesson_id),
  CONSTRAINT FK_lams_email_notification_archive_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_email_notification_archive_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_email_notification_recipient_archive;


CREATE TABLE lams_email_notification_recipient_archive (
  email_notification_uid bigint NOT NULL,
  user_id bigint NOT NULL,
  KEY FK_lams_email_notification_recipient_archive_1 (email_notification_uid),
  KEY FK_lams_email_notification_recipient_archive_2 (user_id),
  CONSTRAINT FK_lams_email_notification_recipient_archive_1 FOREIGN KEY (email_notification_uid) REFERENCES lams_email_notification_archive (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_email_notification_recipient_archive_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_ext_course_class_map;


CREATE TABLE lams_ext_course_class_map (
  sid int NOT NULL AUTO_INCREMENT,
  courseid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  classid bigint NOT NULL,
  ext_server_org_map_id int NOT NULL,
  PRIMARY KEY (sid),
  KEY classid (classid),
  KEY ext_server_org_map_id (ext_server_org_map_id),
  CONSTRAINT lams_ext_course_class_map_fk FOREIGN KEY (classid) REFERENCES lams_organisation (organisation_id),
  CONSTRAINT lams_ext_course_class_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_ext_server_lesson_map;


CREATE TABLE lams_ext_server_lesson_map (
  uid bigint NOT NULL AUTO_INCREMENT,
  lesson_id bigint NOT NULL,
  ext_server_org_map_id int NOT NULL,
  resource_link_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY lesson_id (lesson_id),
  KEY lams_ext_server_lesson_map_fk1 (ext_server_org_map_id),
  CONSTRAINT lams_ext_server_lesson_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT lams_ext_server_lesson_map_fk2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_ext_server_org_map;


DROP TABLE IF EXISTS `lams_ext_server_org_map`;

CREATE TABLE `lams_ext_server_org_map` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `serverid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `serverkey` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `servername` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `serverdesc` mediumtext COLLATE utf8mb4_unicode_ci,
  `prefix` varchar(11) COLLATE utf8mb4_unicode_ci NOT NULL,
  `userinfo_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `lesson_finish_url` text COLLATE utf8mb4_unicode_ci,
  `logout_url` TEXT,
  `disabled` bit(1) NOT NULL,
  `time_to_live_login_request` int(11) DEFAULT '80',
  `time_to_live_login_request_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `ext_groups_url` text COLLATE utf8mb4_unicode_ci,
  `server_type_id` int(11) NOT NULL DEFAULT '1',
  `lti_consumer_monitor_roles` text COLLATE utf8mb4_unicode_ci,
  `learner_presence_avail` tinyint(1) DEFAULT '0',
  `learner_im_avail` tinyint(1) DEFAULT '0',
  `live_edit_enabled` tinyint(1) DEFAULT '1',
  `enable_lesson_notifications` tinyint(1) DEFAULT '1',
  `force_restart` tinyint(1) DEFAULT '0',
  `allow_restart` tinyint(1) DEFAULT '0',
  `gradebook_on_complete` tinyint(1) DEFAULT '1',
  membership_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  user_id_parameter_name varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`sid`),
  UNIQUE KEY `serverid` (`serverid`),
  UNIQUE KEY `prefix` (`prefix`),
  KEY `FK_lams_ext_server_type` (`server_type_id`),
  CONSTRAINT `FK_lams_ext_server_type` FOREIGN KEY (`server_type_id`) REFERENCES `lams_ext_server_type` (`server_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_ext_server_tool_map;


CREATE TABLE lams_ext_server_tool_map (
  uid bigint NOT NULL AUTO_INCREMENT,
  tool_id bigint NOT NULL,
  ext_server_org_map_id int NOT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY unique_adapter_map (ext_server_org_map_id,tool_id),
  KEY lams_ext_server_tool_map_fk2 (tool_id),
  CONSTRAINT lams_ext_server_tool_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT lams_ext_server_tool_map_fk2 FOREIGN KEY (tool_id) REFERENCES lams_tool (tool_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_ext_server_type;


CREATE TABLE lams_ext_server_type (
  server_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (server_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_ext_user_userid_map;


CREATE TABLE lams_ext_user_userid_map (
  sid int NOT NULL AUTO_INCREMENT,
  external_username varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  user_id bigint NOT NULL,
  ext_server_org_map_id int NOT NULL,
  tc_gradebook_id text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (sid),
  KEY user_id (user_id),
  KEY ext_server_org_map_id (ext_server_org_map_id),
  CONSTRAINT lams_ext_user_userid_map_fk FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT lams_ext_user_userid_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_favorite_organisation;


CREATE TABLE lams_favorite_organisation (
  favorite_organisation_id bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  user_id bigint NOT NULL,
  PRIMARY KEY (favorite_organisation_id),
  KEY organisation_id (organisation_id),
  KEY user_id (user_id),
  CONSTRAINT FK_lams_favorite_organisation_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id),
  CONSTRAINT FK_lams_favorite_organisation_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_gate_activity_level;


CREATE TABLE lams_gate_activity_level (
  gate_activity_level_id int NOT NULL,
  `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (gate_activity_level_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_gate_allowed_learners;


CREATE TABLE lams_gate_allowed_learners (
  user_id bigint NOT NULL,
  activity_id bigint NOT NULL,
  KEY user_id (user_id),
  KEY activity_id (activity_id),
  CONSTRAINT FK_TABLE_32_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id),
  CONSTRAINT FK_TABLE_32_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_gradebook_user_activity;


CREATE TABLE lams_gradebook_user_activity (
  uid bigint NOT NULL AUTO_INCREMENT,
  activity_id bigint NOT NULL,
  user_id bigint NOT NULL,
  mark double DEFAULT NULL,
  feedback mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  marked_in_gradebook tinyint(1) NOT NULL DEFAULT '0',
  update_date datetime DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY activity_id (activity_id,user_id),
  KEY FK_lams_gradebook_user_activity_2 (user_id),
  CONSTRAINT FK_lams_gradebook_user_activity_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_gradebook_user_activity_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_gradebook_user_activity_archive;


CREATE TABLE lams_gradebook_user_activity_archive (
  uid bigint NOT NULL,
  activity_id bigint NOT NULL,
  user_id bigint NOT NULL,
  mark double DEFAULT NULL,
  feedback mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  marked_in_gradebook tinyint(1) NOT NULL DEFAULT '0',
  update_date datetime DEFAULT NULL,
  archive_date datetime DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY activity_id (activity_id,user_id),
  KEY FK_lams_gradebook_user_activity_archive_2 (user_id),
  CONSTRAINT FK_lams_gradebook_user_activity_archive_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_gradebook_user_activity_archive_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_gradebook_user_lesson;


DROP TABLE IF EXISTS `lams_gradebook_user_lesson`;

CREATE TABLE `lams_gradebook_user_lesson` (
  `uid` bigint(20) NOT NULL AUTO_INCREMENT,
  `lesson_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `mark` double DEFAULT NULL,
  `feedback` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `lesson_id` (`lesson_id`,`user_id`),
  KEY `FK_lams_gradebook_user_lesson_2` (`user_id`),
  CONSTRAINT `FK_lams_gradebook_user_lesson_1` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`),
  CONSTRAINT `FK_lams_gradebook_user_lesson_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_gradebook_user_lesson_archive;


CREATE TABLE lams_gradebook_user_lesson_archive (
  uid bigint NOT NULL,
  lesson_id bigint NOT NULL,
  user_id bigint NOT NULL,
  mark double DEFAULT NULL,
  feedback mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  archive_date datetime DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY lesson_id (lesson_id,user_id),
  KEY FK_lams_gradebook_user_lesson_archive_2 (user_id),
  CONSTRAINT FK_lams_gradebook_user_lesson_archive_1 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_gradebook_user_lesson_archive_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_group;


CREATE TABLE lams_group (
  group_id bigint NOT NULL AUTO_INCREMENT,
  group_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  grouping_id bigint NOT NULL,
  order_id int NOT NULL DEFAULT '1',
  group_ui_id int DEFAULT NULL,
  PRIMARY KEY (group_id),
  UNIQUE KEY UQ_lams_group_1 (grouping_id,order_id),
  KEY grouping_id (grouping_id),
  CONSTRAINT FK_lams_learning_group_1 FOREIGN KEY (grouping_id) REFERENCES lams_grouping (grouping_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_grouping;


CREATE TABLE lams_grouping (
  grouping_id bigint NOT NULL AUTO_INCREMENT,
  grouping_ui_id int DEFAULT NULL,
  grouping_type_id int NOT NULL,
  number_of_groups int DEFAULT NULL,
  learners_per_group int DEFAULT NULL,
  staff_group_id bigint DEFAULT '0',
  max_number_of_groups int DEFAULT NULL,
  equal_number_of_learners_per_group tinyint(1) DEFAULT '0',
  view_students_before_selection tinyint(1) DEFAULT '0',
  PRIMARY KEY (grouping_id),
  KEY grouping_type_id (grouping_type_id),
  CONSTRAINT FK_lams_learning_grouping_1 FOREIGN KEY (grouping_type_id) REFERENCES lams_grouping_type (grouping_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_grouping_support_type;


CREATE TABLE lams_grouping_support_type (
  grouping_support_type_id int NOT NULL,
  `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (grouping_support_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_grouping_type;


CREATE TABLE lams_grouping_type (
  grouping_type_id int NOT NULL,
  `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (grouping_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_input_activity;


CREATE TABLE lams_input_activity (
  activity_id bigint NOT NULL,
  input_activity_id bigint NOT NULL,
  UNIQUE KEY UQ_lams_input_activity_1 (activity_id,input_activity_id),
  KEY activity_id (activity_id),
  CONSTRAINT FK_lams_input_activity_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT FK_lams_input_activity_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive;


CREATE TABLE lams_kumalive (
  kumalive_id bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  created_by bigint DEFAULT NULL,
  finished tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (kumalive_id),
  KEY FK_lams_kumalive_1 (organisation_id),
  KEY FK_lams_kumalive_2 (created_by),
  CONSTRAINT FK_lams_kumalive_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_kumalive_2 FOREIGN KEY (created_by) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive_log;


CREATE TABLE lams_kumalive_log (
  log_id bigint NOT NULL AUTO_INCREMENT,
  kumalive_id bigint NOT NULL,
  user_id bigint DEFAULT NULL,
  log_date datetime NOT NULL,
  log_type tinyint DEFAULT NULL,
  PRIMARY KEY (log_id),
  KEY FK_lams_kumalive_log_1 (kumalive_id),
  KEY FK_lams_kumalive_log_2 (user_id),
  CONSTRAINT FK_lams_kumalive_log_1 FOREIGN KEY (kumalive_id) REFERENCES lams_kumalive (kumalive_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_kumalive_log_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive_poll;


CREATE TABLE lams_kumalive_poll (
  poll_id bigint NOT NULL AUTO_INCREMENT,
  kumalive_id bigint NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  votes_released tinyint(1) DEFAULT '0',
  voters_released tinyint(1) DEFAULT '0',
  start_date datetime NOT NULL,
  finish_date datetime DEFAULT NULL,
  PRIMARY KEY (poll_id),
  KEY FK_lams_kumalive_poll_1 (kumalive_id),
  CONSTRAINT FK_lams_kumalive_poll_1 FOREIGN KEY (kumalive_id) REFERENCES lams_kumalive (kumalive_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive_poll_answer;


CREATE TABLE lams_kumalive_poll_answer (
  answer_id bigint NOT NULL AUTO_INCREMENT,
  poll_id bigint NOT NULL,
  order_id tinyint NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (answer_id),
  KEY FK_lams_kumalive_poll_answer_1 (poll_id),
  CONSTRAINT FK_lams_kumalive_poll_answer_1 FOREIGN KEY (poll_id) REFERENCES lams_kumalive_poll (poll_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive_poll_vote;


CREATE TABLE lams_kumalive_poll_vote (
  answer_id bigint NOT NULL,
  user_id bigint NOT NULL,
  vote_date datetime DEFAULT NULL,
  PRIMARY KEY (answer_id,user_id),
  KEY FK_lams_kumalive_poll_vote_2 (user_id),
  CONSTRAINT FK_lams_kumalive_poll_vote_1 FOREIGN KEY (answer_id) REFERENCES lams_kumalive_poll_answer (answer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_kumalive_poll_vote_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive_rubric;


CREATE TABLE lams_kumalive_rubric (
  rubric_id bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  kumalive_id bigint DEFAULT NULL,
  order_id tinyint NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (rubric_id),
  KEY FK_lams_kumalive_rubric_1 (organisation_id),
  KEY FK_lams_kumalive_rubric_2 (kumalive_id),
  CONSTRAINT FK_lams_kumalive_rubric_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_kumalive_rubric_2 FOREIGN KEY (kumalive_id) REFERENCES lams_kumalive (kumalive_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_kumalive_score;


CREATE TABLE lams_kumalive_score (
  score_id bigint NOT NULL AUTO_INCREMENT,
  rubric_id bigint NOT NULL,
  user_id bigint DEFAULT NULL,
  batch bigint DEFAULT NULL,
  score tinyint DEFAULT NULL,
  PRIMARY KEY (score_id),
  KEY FK_lams_kumalive_score_1 (rubric_id),
  KEY FK_lams_kumalive_score_2 (user_id),
  CONSTRAINT FK_lams_kumalive_score_1 FOREIGN KEY (rubric_id) REFERENCES lams_kumalive_rubric (rubric_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_kumalive_score_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learner_progress;


CREATE TABLE lams_learner_progress (
  learner_progress_id bigint NOT NULL AUTO_INCREMENT,
  user_id bigint NOT NULL,
  lesson_id bigint NOT NULL,
  lesson_completed_flag tinyint(1) NOT NULL DEFAULT '0',
  waiting_flag tinyint NOT NULL,
  start_date_time datetime NOT NULL,
  finish_date_time datetime DEFAULT NULL,
  current_activity_id bigint DEFAULT NULL,
  next_activity_id bigint DEFAULT NULL,
  previous_activity_id bigint DEFAULT NULL,
  requires_restart_flag tinyint(1) NOT NULL,
  PRIMARY KEY (learner_progress_id),
  UNIQUE KEY IX_lams_learner_progress_1 (user_id,lesson_id),
  KEY user_id (user_id),
  KEY lesson_id (lesson_id),
  KEY current_activity_id (current_activity_id),
  KEY next_activity_id (next_activity_id),
  KEY previous_activity_id (previous_activity_id),
  CONSTRAINT FK_lams_learner_progress_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id),
  CONSTRAINT FK_lams_learner_progress_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id),
  CONSTRAINT FK_lams_learner_progress_3 FOREIGN KEY (current_activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT FK_lams_learner_progress_4 FOREIGN KEY (next_activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT FK_lams_learner_progress_5 FOREIGN KEY (previous_activity_id) REFERENCES lams_learning_activity (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learner_progress_archive;


CREATE TABLE lams_learner_progress_archive (
  learner_progress_id bigint NOT NULL AUTO_INCREMENT,
  user_id bigint NOT NULL,
  lesson_id bigint NOT NULL,
  attempt_id tinyint NOT NULL DEFAULT '1',
  lesson_completed_flag tinyint(1) NOT NULL DEFAULT '0',
  start_date_time datetime NOT NULL,
  finish_date_time datetime DEFAULT NULL,
  current_activity_id bigint DEFAULT NULL,
  archive_date datetime DEFAULT NULL,
  PRIMARY KEY (learner_progress_id),
  UNIQUE KEY IX_lams_learner_progress_archive_1 (user_id,lesson_id,attempt_id),
  KEY FK_lams_learner_progress_archive_2 (lesson_id),
  KEY FK_lams_learner_progress_archive_3 (current_activity_id),
  CONSTRAINT FK_lams_learner_progress_archive_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_learner_progress_archive_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_learner_progress_archive_3 FOREIGN KEY (current_activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_activity;


CREATE TABLE lams_learning_activity (
  activity_id bigint NOT NULL AUTO_INCREMENT,
  activity_ui_id int DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  xcoord int DEFAULT NULL,
  ycoord int DEFAULT NULL,
  parent_activity_id bigint DEFAULT NULL,
  parent_ui_id int DEFAULT NULL,
  learning_activity_type_id int NOT NULL DEFAULT '0',
  grouping_support_type_id int NOT NULL,
  apply_grouping_flag tinyint(1) NOT NULL,
  grouping_id bigint DEFAULT NULL,
  grouping_ui_id int DEFAULT NULL,
  order_id int DEFAULT NULL,
  learning_design_id bigint DEFAULT NULL,
  learning_library_id bigint DEFAULT NULL,
  create_date_time datetime NOT NULL,
  max_number_of_options int DEFAULT NULL,
  min_number_of_options int DEFAULT NULL,
  options_instructions mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  tool_id bigint DEFAULT NULL,
  tool_content_id bigint DEFAULT NULL,
  activity_category_id int,
  gate_activity_level_id int DEFAULT NULL,
  gate_open_flag tinyint(1) DEFAULT NULL,
  gate_open_user bigint DEFAULT NULL,
  gate_open_time datetime DEFAULT NULL,
  gate_start_time_offset bigint DEFAULT NULL,
  gate_end_time_offset bigint DEFAULT NULL,
  gate_activity_completion_based tinyint(1) DEFAULT NULL,
  gate_password varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  library_activity_ui_image varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  create_grouping_id bigint DEFAULT NULL,
  create_grouping_ui_id int DEFAULT NULL,
  library_activity_id bigint DEFAULT NULL,
  language_file varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  system_tool_id bigint DEFAULT NULL,
  `read_only` tinyint(1) DEFAULT '0',
  `initialised` tinyint(1) DEFAULT '0',
  `default_activity_id` bigint(20) DEFAULT NULL,
  `start_xcoord` int(11) DEFAULT NULL,
  `start_ycoord` int(11) DEFAULT NULL,
  `end_xcoord` int(11) DEFAULT NULL,
  `end_ycoord` int(11) DEFAULT NULL,
  `stop_after_activity` tinyint(1) NOT NULL DEFAULT '0',
  `transition_to_id` bigint(20) DEFAULT NULL,
  `transition_from_id` bigint(20) DEFAULT NULL,
  `branching_ordered_asc` TINYINT(1),
  PRIMARY KEY (`activity_id`),
  KEY `lams_learning_activity_tool_content_id` (`tool_content_id`),
  KEY `learning_library_id` (`learning_library_id`),
  KEY `learning_design_id` (`learning_design_id`),
  KEY `parent_activity_id` (`parent_activity_id`),
  KEY `learning_activity_type_id` (`learning_activity_type_id`),
  KEY `grouping_id` (`grouping_id`),
  KEY `tool_id` (`tool_id`),
  KEY `gate_activity_level_id` (`gate_activity_level_id`),
  KEY `create_grouping_id` (`create_grouping_id`),
  KEY `library_activity_id` (`library_activity_id`),
  KEY `activity_category_id` (`activity_category_id`),
  KEY `grouping_support_type_id` (`grouping_support_type_id`),
  KEY `system_tool_id` (`system_tool_id`),
  KEY `FK_lams_learning_activity_15` (`transition_to_id`),
  KEY `FK_lams_learning_activity_16` (`transition_from_id`),
  KEY `FK_lams_learning_activity_17` (`gate_open_user`),
  CONSTRAINT `FK_lams_learning_activity_10` FOREIGN KEY (`gate_activity_level_id`) REFERENCES `lams_gate_activity_level` (`gate_activity_level_id`),
  CONSTRAINT `FK_lams_learning_activity_11` FOREIGN KEY (`library_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`),
  CONSTRAINT `FK_lams_learning_activity_12` FOREIGN KEY (`activity_category_id`) REFERENCES `lams_activity_category` (`activity_category_id`),
  CONSTRAINT `FK_lams_learning_activity_13` FOREIGN KEY (`grouping_support_type_id`) REFERENCES `lams_grouping_support_type` (`grouping_support_type_id`),
  CONSTRAINT `FK_lams_learning_activity_14` FOREIGN KEY (`system_tool_id`) REFERENCES `lams_system_tool` (`system_tool_id`),
  CONSTRAINT `FK_lams_learning_activity_15` FOREIGN KEY (`transition_to_id`) REFERENCES `lams_learning_transition` (`transition_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_lams_learning_activity_16` FOREIGN KEY (`transition_from_id`) REFERENCES `lams_learning_transition` (`transition_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_lams_learning_activity_17` FOREIGN KEY (`gate_open_user`) REFERENCES `lams_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `FK_lams_learning_activity_6` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_lams_learning_activity_7` FOREIGN KEY (`learning_library_id`) REFERENCES `lams_learning_library` (`learning_library_id`),
  CONSTRAINT `FK_lams_learning_activity_8` FOREIGN KEY (`tool_id`) REFERENCES `lams_tool` (`tool_id`),
  CONSTRAINT `FK_lams_learning_activity_9` FOREIGN KEY (`create_grouping_id`) REFERENCES `lams_grouping` (`grouping_id`),
  CONSTRAINT `FK_learning_activity_2` FOREIGN KEY (`parent_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`),
  CONSTRAINT `FK_learning_activity_3` FOREIGN KEY (`learning_activity_type_id`) REFERENCES `lams_learning_activity_type` (`learning_activity_type_id`),
  CONSTRAINT `FK_learning_activity_6` FOREIGN KEY (`grouping_id`) REFERENCES `lams_grouping` (`grouping_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_activity_type;

CREATE TABLE lams_learning_activity_type (
  learning_activity_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (learning_activity_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_command;


CREATE TABLE lams_learning_command (
  uid bigint NOT NULL AUTO_INCREMENT,
  lesson_id bigint DEFAULT NULL,
  user_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  create_date datetime NOT NULL,
  command_TEXT text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (uid),
  KEY idx_lesson_id (lesson_id),
  KEY idx_user_name (user_name),
  KEY idx_create_date (create_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_design;


CREATE TABLE lams_learning_design (
  learning_design_id bigint NOT NULL AUTO_INCREMENT,
  learning_design_ui_id int DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  first_activity_id bigint DEFAULT NULL,
  floating_activity_id bigint DEFAULT NULL,
  max_id int DEFAULT NULL,
  valid_design_flag tinyint(1) NOT NULL,
  read_only_flag tinyint(1) NOT NULL,
  date_read_only datetime DEFAULT NULL,
  user_id bigint NOT NULL,
  original_user_id bigint NOT NULL,
  help_text mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  copy_type_id tinyint NOT NULL,
  create_date_time datetime NOT NULL,
  version varchar(56) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  original_learning_design_id bigint DEFAULT NULL,
  workspace_folder_id bigint DEFAULT NULL,
  duration bigint DEFAULT NULL,
  license_id bigint DEFAULT NULL,
  license_TEXT mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  last_modified_date_time datetime DEFAULT NULL,
  content_folder_id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  edit_override_lock tinyint(1) DEFAULT '0',
  edit_override_user_id bigint DEFAULT NULL,
  design_version int DEFAULT '1',
  removed tinyint(1) NOT NULL DEFAULT '0',
  design_type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (learning_design_id),
  KEY user_id (user_id),
  KEY workspace_folder_id (workspace_folder_id),
  KEY license_id (license_id),
  KEY copy_type_id (copy_type_id),
  KEY edit_override_user_id (edit_override_user_id),
  KEY idx_design_parent_id (original_learning_design_id),
  KEY idx_design_first_act (first_activity_id),
  KEY idx_design_floating_act (floating_activity_id),
  CONSTRAINT FK_lams_learning_design_3 FOREIGN KEY (user_id) REFERENCES lams_user (user_id),
  CONSTRAINT FK_lams_learning_design_4 FOREIGN KEY (workspace_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id),
  CONSTRAINT FK_lams_learning_design_5 FOREIGN KEY (license_id) REFERENCES lams_license (license_id),
  CONSTRAINT FK_lams_learning_design_6 FOREIGN KEY (copy_type_id) REFERENCES lams_copy_type (copy_type_id),
  CONSTRAINT FK_lams_learning_design_7 FOREIGN KEY (edit_override_user_id) REFERENCES lams_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_design_access;


CREATE TABLE lams_learning_design_access (
  learning_design_id bigint NOT NULL,
  user_id bigint NOT NULL,
  access_date datetime DEFAULT NULL,
  PRIMARY KEY (learning_design_id,user_id),
  KEY FK_lams_learning_design_access_2 (user_id),
  CONSTRAINT FK_lams_learning_design_access_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_learning_design_access_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_design_annotation;


CREATE TABLE lams_learning_design_annotation (
  uid bigint NOT NULL AUTO_INCREMENT,
  learning_design_id bigint NOT NULL,
  ui_id int DEFAULT NULL,
  title varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  xcoord int DEFAULT NULL,
  ycoord int DEFAULT NULL,
  end_xcoord int DEFAULT NULL,
  end_ycoord int DEFAULT NULL,
  color char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  size tinyint DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY FK_lams_learning_design_annotation_1 (learning_design_id),
  CONSTRAINT FK_lams_learning_design_annotation_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_library;


CREATE TABLE lams_learning_library (
  learning_library_id bigint NOT NULL AUTO_INCREMENT,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  valid_flag tinyint(1) NOT NULL DEFAULT '1',
  create_date_time datetime NOT NULL,
  PRIMARY KEY (learning_library_id)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_library_group;


CREATE TABLE lams_learning_library_group (
  group_id int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_library_to_group;


CREATE TABLE lams_learning_library_to_group (
  group_id int NOT NULL,
  learning_library_id bigint NOT NULL,
  PRIMARY KEY (group_id,learning_library_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_learning_transition;


CREATE TABLE lams_learning_transition (
  transition_id bigint NOT NULL AUTO_INCREMENT,
  transition_ui_id int DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  to_activity_id bigint NOT NULL,
  from_activity_id bigint NOT NULL,
  learning_design_id bigint DEFAULT NULL,
  create_date_time datetime NOT NULL,
  to_ui_id int DEFAULT NULL,
  from_ui_id int DEFAULT NULL,
  transition_type tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (transition_id),
  KEY from_activity_id (from_activity_id),
  KEY to_activity_id (to_activity_id),
  KEY learning_design_id (learning_design_id),
  CONSTRAINT FK_learning_transition_2 FOREIGN KEY (to_activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT FK_learning_transition_3 FOREIGN KEY (from_activity_id) REFERENCES lams_learning_activity (activity_id),
  CONSTRAINT lddefn_transition_ibfk_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_lesson;


DROP TABLE IF EXISTS `lams_lesson`;

CREATE TABLE `lams_lesson` (
  `lesson_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `learning_design_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` mediumtext COLLATE utf8mb4_unicode_ci,
  `create_date_time` datetime NOT NULL,
  `organisation_id` bigint(20) DEFAULT NULL,
  `class_grouping_id` bigint(20) DEFAULT NULL,
  `lesson_state_id` int(3) NOT NULL,
  `start_date_time` datetime DEFAULT NULL,
  `scheduled_number_days_to_lesson_finish` int(3) DEFAULT NULL,
  `schedule_start_date_time` datetime DEFAULT NULL,
  `end_date_time` datetime DEFAULT NULL,
  `schedule_end_date_time` datetime DEFAULT NULL,
  `previous_state_id` int(3) DEFAULT NULL,
  `learner_presence_avail` TINYINT(1) NOT NULL DEFAULT 0,
  `learner_im_avail` TINYINT(1) NOT NULL DEFAULT 0,
  `live_edit_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `enable_lesson_notifications` TINYINT(1) NOT NULL DEFAULT 0,
  `locked_for_edit` TINYINT(1) NOT NULL DEFAULT 0,
  `marks_released` TINYINT(1) NOT NULL DEFAULT 0,
  `enable_lesson_INTro` TINYINT(1) NOT NULL DEFAULT 0,
  `display_design_image` TINYINT(1) NOT NULL DEFAULT 0,
  `force_restart` TINYINT(1) NOT NULL DEFAULT 0,
  `allow_restart` TINYINT(1) NOT NULL DEFAULT 0,
  `gradebook_on_complete` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`lesson_id`),
  KEY `learning_design_id` (`learning_design_id`),
  KEY `user_id` (`user_id`),
  KEY `organisation_id` (`organisation_id`),
  KEY `lesson_state_id` (`lesson_state_id`),
  KEY `class_grouping_id` (`class_grouping_id`),
  CONSTRAINT `FK_lams_lesson_1_1` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`),
  CONSTRAINT `FK_lams_lesson_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`),
  CONSTRAINT `FK_lams_lesson_3` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`),
  CONSTRAINT `FK_lams_lesson_4` FOREIGN KEY (`lesson_state_id`) REFERENCES `lams_lesson_state` (`lesson_state_id`),
  CONSTRAINT `FK_lams_lesson_5` FOREIGN KEY (`class_grouping_id`) REFERENCES `lams_grouping` (`grouping_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_lesson_dependency;


CREATE TABLE lams_lesson_dependency (
  lesson_id bigint NOT NULL,
  preceding_lesson_id bigint NOT NULL,
  PRIMARY KEY (lesson_id,preceding_lesson_id),
  KEY FK_lams_lesson_dependency_2 (preceding_lesson_id),
  CONSTRAINT FK_lams_lesson_dependency_1 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_lesson_dependency_2 FOREIGN KEY (preceding_lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_lesson_state;


CREATE TABLE lams_lesson_state (
  lesson_state_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (lesson_state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_license;


CREATE TABLE lams_license (
  license_id bigint NOT NULL,
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  url varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  default_flag tinyint(1) NOT NULL DEFAULT '0',
  picture_url varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  order_id tinyint DEFAULT '0',
  PRIMARY KEY (license_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_log_event;


DROP TABLE IF EXISTS `lams_log_event`;

CREATE TABLE `lams_log_event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `log_event_type_id` int(5) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `occurred_date_time` datetime NOT NULL,
  `lesson_id` bigint(20) DEFAULT NULL,
  `activity_id` bigint(20) DEFAULT NULL,
  `target_user_id` bigint(20) DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `event_log_occurred_date_time` (`occurred_date_time`),
  KEY `FK_event_log_event_type_idx` (`log_event_type_id`),
  KEY `event_log_date_and_type` (`occurred_date_time`, `log_event_type_id`),
  CONSTRAINT `FK_event_log_event_type` FOREIGN KEY (`log_event_type_id`) REFERENCES `lams_log_event_type` (`log_event_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_log_event_type;


CREATE TABLE lams_log_event_type (
  log_event_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  area varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (log_event_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_notebook_entry;


CREATE TABLE lams_notebook_entry (
  uid bigint NOT NULL AUTO_INCREMENT,
  external_id bigint DEFAULT NULL,
  external_id_type int DEFAULT NULL,
  external_signature varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  user_id int DEFAULT NULL,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  entry mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  create_date datetime DEFAULT NULL,
  last_modified datetime DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY ext_sig_user (external_id,external_id_type,external_signature,user_id),
  KEY idx_create_date (create_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_notification_event;


CREATE TABLE lams_notification_event (
  uid bigint NOT NULL AUTO_INCREMENT,
  scope varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  event_session_id bigint DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  message mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  fail_time datetime DEFAULT NULL,
  html_format tinyint(1) DEFAULT '0',
  PRIMARY KEY (uid),
  KEY scope (scope,`name`,event_session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_notification_subscription;


CREATE TABLE lams_notification_subscription (
  uid bigint NOT NULL AUTO_INCREMENT,
  user_id bigint DEFAULT NULL,
  event_uid bigint DEFAULT NULL,
  delivery_method_id tinyint unsigned DEFAULT NULL,
  last_operation_message mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (uid),
  KEY EventSubscriptionsToUsers (user_id),
  KEY event_uid (event_uid),
  CONSTRAINT EventSubscriptionsToEvent FOREIGN KEY (event_uid) REFERENCES lams_notification_event (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT EventSubscriptionsToUsers FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_organisation;


CREATE TABLE lams_organisation (
  organisation_id bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  parent_organisation_id bigint DEFAULT NULL,
  organisation_type_id int NOT NULL DEFAULT '0',
  create_date datetime NOT NULL,
  created_by bigint NOT NULL,
  organisation_state_id int NOT NULL,
  admin_add_new_users tinyint(1) NOT NULL DEFAULT '0',
  admin_browse_all_users tinyint(1) NOT NULL DEFAULT '0',
  admin_change_status tinyint(1) NOT NULL DEFAULT '0',
  admin_create_guest tinyint(1) NOT NULL DEFAULT '0',
  enable_course_notifications tinyint(1) NOT NULL DEFAULT '0',
  enable_learner_gradebook tinyint(1) NOT NULL DEFAULT '0',
  enable_single_activity_lessons tinyint(1) NOT NULL DEFAULT '1',
  enable_live_edit tinyint(1) NOT NULL DEFAULT '1',
  enable_kumalive tinyint(1) NOT NULL DEFAULT '0',
  archived_date datetime DEFAULT NULL,
  ordered_lesson_ids text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (organisation_id),
  KEY organisation_type_id (organisation_type_id),
  KEY parent_organisation_id (parent_organisation_id),
  KEY organisation_state_id (organisation_state_id),
  CONSTRAINT FK_lams_organisation_1 FOREIGN KEY (organisation_type_id) REFERENCES lams_organisation_type (organisation_type_id),
  CONSTRAINT FK_lams_organisation_3 FOREIGN KEY (parent_organisation_id) REFERENCES lams_organisation (organisation_id),
  CONSTRAINT FK_lams_organisation_4 FOREIGN KEY (organisation_state_id) REFERENCES lams_organisation_state (organisation_state_id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_organisation_group;


CREATE TABLE lams_organisation_group (
  group_id bigint NOT NULL AUTO_INCREMENT,
  grouping_id bigint NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (group_id),
  KEY FK_lams_organisation_group_1 (grouping_id),
  CONSTRAINT FK_lams_organisation_group_1 FOREIGN KEY (grouping_id) REFERENCES lams_organisation_grouping (grouping_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_organisation_grouping;


CREATE TABLE lams_organisation_grouping (
  grouping_id bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (grouping_id),
  KEY FK_lams_organisation_grouping_1 (organisation_id),
  CONSTRAINT FK_lams_organisation_grouping_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_organisation_state;


CREATE TABLE lams_organisation_state (
  organisation_state_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (organisation_state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_organisation_type;


CREATE TABLE lams_organisation_type (
  organisation_type_id int NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (organisation_type_id),
  UNIQUE KEY UQ_lams_organisation_type_name (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_outcome;


DROP TABLE IF EXISTS `lams_outcome`;

CREATE TABLE `lams_outcome` (
  `outcome_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `scale_id` mediumint(9) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `create_by` bigint(20) DEFAULT NULL,
  `create_date_time` datetime NOT NULL,
  PRIMARY KEY (`outcome_id`),
  KEY `name` (`name`),
  KEY `code` (`code`),
  KEY `FK_lams_outcome_2` (`scale_id`),
  CONSTRAINT `FK_lams_outcome_2` FOREIGN KEY (`scale_id`) REFERENCES `lams_outcome_scale` (`scale_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_outcome_mapping;


DROP TABLE IF EXISTS `lams_outcome_mapping`;

CREATE TABLE `lams_outcome_mapping` (
  `mapping_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `outcome_id` mediumint(9) NOT NULL,
  `lesson_id` bigint(20) DEFAULT NULL,
  `tool_content_id` bigint(20) DEFAULT NULL,
  `item_id` bigint(20) DEFAULT NULL,
  qb_question_id INT,
  PRIMARY KEY (`mapping_id`),
  KEY `FK_lams_outcome_mapping_1` (`outcome_id`),
  KEY `FK_lams_outcome_mapping_2` (`lesson_id`),
  KEY `FK_lams_outcome_mapping_3` (`tool_content_id`),
  CONSTRAINT `FK_lams_outcome_mapping_1` FOREIGN KEY (`outcome_id`) REFERENCES `lams_outcome` (`outcome_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `FK_lams_outcome_mapping_2` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_lams_outcome_mapping_3` FOREIGN KEY (`tool_content_id`) REFERENCES `lams_tool_content` (`tool_content_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_outcome_result;


CREATE TABLE lams_outcome_result (
  result_id bigint NOT NULL AUTO_INCREMENT,
  mapping_id bigint NOT NULL,
  user_id bigint DEFAULT NULL,
  `value` tinyint DEFAULT NULL,
  create_by bigint DEFAULT NULL,
  create_date_time datetime NOT NULL,
  PRIMARY KEY (result_id),
  KEY FK_lams_outcome_result_1 (mapping_id),
  KEY FK_lams_outcome_result_2 (user_id),
  KEY FK_lams_outcome_result_3 (create_by),
  CONSTRAINT FK_lams_outcome_result_1 FOREIGN KEY (mapping_id) REFERENCES lams_outcome_mapping (mapping_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_outcome_result_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_outcome_result_3 FOREIGN KEY (create_by) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_outcome_scale;


DROP TABLE IF EXISTS `lams_outcome_scale`;
			 
CREATE TABLE `lams_outcome_scale` (
  `scale_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `create_by` bigint(20) DEFAULT NULL,
  `create_date_time` datetime NOT NULL,
  PRIMARY KEY (`scale_id`),
  KEY `name` (`name`),
  KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_outcome_scale_item;


CREATE TABLE lams_outcome_scale_item (
  item_id int NOT NULL AUTO_INCREMENT,
  scale_id mediumint DEFAULT NULL,
  `value` tinyint DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (item_id),
  KEY FK_lams_outcome_scale_item_1 (scale_id),
  CONSTRAINT FK_lams_outcome_scale_item_1 FOREIGN KEY (scale_id) REFERENCES lams_outcome_scale (scale_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_password_request;


CREATE TABLE lams_password_request (
  request_id bigint NOT NULL AUTO_INCREMENT,
  user_id bigint NOT NULL,
  request_key char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  request_date datetime NOT NULL,
  PRIMARY KEY (request_id),
  UNIQUE KEY IX_lams_psswd_rqst_key (request_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_planner_activity_metadata;


CREATE TABLE lams_planner_activity_metadata (
  activity_id bigint NOT NULL,
  collapsed tinyint(1) DEFAULT '0',
  expanded tinyint(1) DEFAULT '0',
  hidden tinyint(1) DEFAULT '0',
  editing_advice varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY FK_lams_planner_metadata_primary (activity_id),
  CONSTRAINT FK_lams_planner_metadata_primary FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_planner_node_role;


CREATE TABLE lams_planner_node_role (
  uid bigint NOT NULL AUTO_INCREMENT,
  node_uid bigint NOT NULL,
  user_id bigint NOT NULL,
  role_id int NOT NULL,
  PRIMARY KEY (uid),
  KEY FK_planner_node_role_user (user_id),
  KEY FK_planner_node_role_node (node_uid),
  KEY FK_planner_node_role_role (role_id),
  CONSTRAINT FK_planner_node_role_node FOREIGN KEY (node_uid) REFERENCES lams_planner_nodes (uid) ON DELETE CASCADE,
  CONSTRAINT FK_planner_node_role_role FOREIGN KEY (role_id) REFERENCES lams_role (role_id) ON DELETE CASCADE,
  CONSTRAINT FK_planner_node_role_user FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_planner_nodes;


CREATE TABLE lams_planner_nodes (
  uid bigint NOT NULL AUTO_INCREMENT,
  parent_uid bigint DEFAULT NULL,
  order_id tinyint unsigned NOT NULL,
  `locked` tinyint(1) NOT NULL DEFAULT '0',
  content_folder_id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  brief_desc mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  full_desc mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  ld_id bigint DEFAULT NULL,
  user_id bigint DEFAULT NULL,
  permissions int DEFAULT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY parent_uid (parent_uid,order_id),
  KEY FK_lams_planner_node_user (user_id),
  CONSTRAINT FK_lams_planner_node_parent FOREIGN KEY (parent_uid) REFERENCES lams_planner_nodes (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_planner_node_user FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_planner_recent_learning_designs;


CREATE TABLE lams_planner_recent_learning_designs (
  user_id bigint NOT NULL,
  learning_design_id bigint NOT NULL,
  last_modified_date timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id,learning_design_id),
  KEY FK_lams_planner_recent_learning_designs_2 (learning_design_id),
  CONSTRAINT FK_lams_planner_recent_learning_designs_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_planner_recent_learning_designs_2 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_policy;


CREATE TABLE lams_policy (
  uid bigint NOT NULL AUTO_INCREMENT,
  policy_id bigint DEFAULT NULL,
  created_by bigint NOT NULL,
  policy_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  version mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  summary text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  full_policy text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  last_modified datetime NOT NULL,
  policy_state_id int NOT NULL,
  policy_type_id int NOT NULL,
  PRIMARY KEY (uid),
  KEY created_by (created_by),
  KEY policy_state_id (policy_state_id),
  KEY policy_type_id (policy_type_id),
  CONSTRAINT FK_lams_lesson_1 FOREIGN KEY (created_by) REFERENCES lams_user (user_id),
  CONSTRAINT FK_lams_policy_2 FOREIGN KEY (policy_state_id) REFERENCES lams_policy_state (policy_state_id),
  CONSTRAINT FK_lams_policy_3 FOREIGN KEY (policy_type_id) REFERENCES lams_policy_type (policy_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_policy_consent;


CREATE TABLE lams_policy_consent (
  uid bigint NOT NULL AUTO_INCREMENT,
  date_agreed_on datetime NOT NULL,
  policy_uid bigint NOT NULL,
  user_id bigint NOT NULL,
  PRIMARY KEY (uid),
  KEY policy_uid (policy_uid),
  KEY user_id (user_id),
  CONSTRAINT FK_lams_consent_1_1 FOREIGN KEY (policy_uid) REFERENCES lams_policy (uid),
  CONSTRAINT FK_lams_consent_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_policy_state;


CREATE TABLE lams_policy_state (
  policy_state_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (policy_state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_policy_type;


CREATE TABLE lams_policy_type (
  policy_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (policy_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_presence_chat_msgs;


CREATE TABLE lams_presence_chat_msgs (
  uid bigint NOT NULL AUTO_INCREMENT,
  lesson_id bigint DEFAULT NULL,
  from_user varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  to_user varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  date_sent datetime DEFAULT NULL,
  message varchar(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY FK_lams_presence_chat_msgs_lesson (lesson_id),
  KEY idx_lams_presence_chat_msgs_from (from_user),
  KEY idx_lams_presence_chat_msgs_to (to_user),
  CONSTRAINT FK_lams_presence_chat_msgs_lesson FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_presence_user;


CREATE TABLE lams_presence_user (
  nickname varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  lesson_id bigint NOT NULL,
  last_presence datetime DEFAULT NULL,
  PRIMARY KEY (nickname,lesson_id),
  KEY FK_lams_presence_user_lesson (lesson_id),
  CONSTRAINT FK_lams_presence_user_lesson FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_progress_attempted;


CREATE TABLE lams_progress_attempted (
  learner_progress_id bigint NOT NULL,
  activity_id bigint NOT NULL,
  start_date_time datetime DEFAULT NULL,
  PRIMARY KEY (learner_progress_id,activity_id),
  KEY learner_progress_id (learner_progress_id),
  KEY activity_id (activity_id),
  CONSTRAINT FK_lams_progress_current_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_progress_current_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_progress_attempted_archive;


CREATE TABLE lams_progress_attempted_archive (
  learner_progress_id bigint NOT NULL,
  activity_id bigint NOT NULL,
  start_date_time datetime DEFAULT NULL,
  PRIMARY KEY (learner_progress_id,activity_id),
  KEY FK_lams_progress_current_archive_2 (activity_id),
  CONSTRAINT FK_lams_progress_current_archive_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress_archive (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_progress_current_archive_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_progress_completed;


CREATE TABLE lams_progress_completed (
  learner_progress_id bigint NOT NULL,
  activity_id bigint NOT NULL,
  completed_date_time datetime DEFAULT NULL,
  start_date_time datetime DEFAULT NULL,
  PRIMARY KEY (learner_progress_id,activity_id),
  KEY learner_progress_id (learner_progress_id),
  KEY activity_id (activity_id),
  CONSTRAINT FK_lams_progress_completed_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_progress_completed_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_progress_completed_archive;


CREATE TABLE lams_progress_completed_archive (
  learner_progress_id bigint NOT NULL,
  activity_id bigint NOT NULL,
  completed_date_time datetime DEFAULT NULL,
  start_date_time datetime DEFAULT NULL,
  PRIMARY KEY (learner_progress_id,activity_id),
  KEY FK_lams_progress_completed_archive_2 (activity_id),
  CONSTRAINT FK_lams_progress_completed_archive_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress_archive (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_progress_completed_archive_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_rating;


CREATE TABLE lams_rating (
  uid bigint NOT NULL AUTO_INCREMENT,
  rating_criteria_id bigint NOT NULL,
  item_id bigint DEFAULT NULL,
  user_id bigint NOT NULL,
  rating float DEFAULT NULL,
  tool_session_id bigint DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY rating_criteria_id (rating_criteria_id),
  KEY user_id (user_id),
  KEY FK_lams_rating_3 (tool_session_id),
  CONSTRAINT FK_lams_rating_1 FOREIGN KEY (rating_criteria_id) REFERENCES lams_rating_criteria (rating_criteria_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_rating_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_rating_comment;


CREATE TABLE lams_rating_comment (
  uid bigint NOT NULL AUTO_INCREMENT,
  rating_criteria_id bigint NOT NULL,
  item_id bigint DEFAULT NULL,
  user_id bigint NOT NULL,
  `comment` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  posted_date datetime DEFAULT NULL,
  tool_session_id bigint DEFAULT NULL,
  PRIMARY KEY (uid),
  KEY rating_criteria_id (rating_criteria_id),
  KEY user_id (user_id),
  KEY FK_lams_rating_comment_3 (tool_session_id),
  CONSTRAINT FK_lams_rating_comment_1 FOREIGN KEY (rating_criteria_id) REFERENCES lams_rating_criteria (rating_criteria_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_rating_comment_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_rating_criteria;


CREATE TABLE lams_rating_criteria (
  rating_criteria_id bigint NOT NULL AUTO_INCREMENT,
  title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  rating_criteria_type_id int NOT NULL DEFAULT '0',
  comments_enabled tinyint(1) NOT NULL DEFAULT '0',
  comments_min_words_limit int DEFAULT '0',
  order_id int NOT NULL,
  tool_content_id bigint DEFAULT NULL,
  item_id bigint DEFAULT NULL,
  lesson_id bigint DEFAULT NULL,
  rating_style bigint NOT NULL DEFAULT '1',
  max_rating bigint NOT NULL DEFAULT '5',
  minimum_rates int DEFAULT '0',
  maximum_rates int DEFAULT '0',
  PRIMARY KEY (rating_criteria_id),
  KEY rating_criteria_type_id (rating_criteria_type_id),
  KEY tool_content_id (tool_content_id),
  KEY lesson_id (lesson_id),
  CONSTRAINT FK_lams_rating_criteria_1 FOREIGN KEY (rating_criteria_type_id) REFERENCES lams_rating_criteria_type (rating_criteria_type_id),
  CONSTRAINT FK_lams_rating_criteria_2 FOREIGN KEY (tool_content_id) REFERENCES lams_tool_content (tool_content_id),
  CONSTRAINT FK_lams_rating_criteria_3 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_rating_criteria_type;


CREATE TABLE lams_rating_criteria_type (
  rating_criteria_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (rating_criteria_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_role;


CREATE TABLE lams_role (
  role_id int NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  create_date datetime NOT NULL,
  PRIMARY KEY (role_id),
  KEY gname (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_signup_organisation;


CREATE TABLE lams_signup_organisation (
  signup_organisation_id bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  add_to_lessons tinyint(1) DEFAULT '1',
  add_as_staff tinyint(1) DEFAULT '0',
  add_with_author tinyint(1) DEFAULT '0',
  add_with_monitor tinyint(1) DEFAULT '0',
  email_verify tinyint(1) DEFAULT '0',
  course_key varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  blurb text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  create_date datetime DEFAULT NULL,
  disabled tinyint(1) DEFAULT '0',
  `conTEXT` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  login_tab_active tinyint(1) DEFAULT '0',
  PRIMARY KEY (signup_organisation_id),
  UNIQUE KEY `conTEXT` (`conTEXT`),
  KEY organisation_id (organisation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_supported_locale;


CREATE TABLE lams_supported_locale (
  locale_id int NOT NULL AUTO_INCREMENT,
  language_iso_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  country_iso_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  direction varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  fckeditor_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (locale_id)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_system_tool;


CREATE TABLE lams_system_tool (
  system_tool_id bigint NOT NULL AUTO_INCREMENT,
  learning_activity_type_id int NOT NULL,
  tool_display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  learner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  learner_preview_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  learner_progress_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  monitor_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  contribute_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  help_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  create_date_time datetime NOT NULL,
  admin_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  pedagogical_planner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (system_tool_id),
  UNIQUE KEY UQ_systool_activity_type (learning_activity_type_id),
  CONSTRAINT FK_lams_system_tool FOREIGN KEY (learning_activity_type_id) REFERENCES lams_learning_activity_type (learning_activity_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_text_search_condition;


CREATE TABLE lams_text_search_condition (
  condition_id bigint NOT NULL,
  text_search_all_words text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  text_search_phrase text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  text_search_any_words text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  text_search_excluded_words text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (condition_id),
  CONSTRAINT TextSearchConditionInheritance FOREIGN KEY (condition_id) REFERENCES lams_branch_condition (condition_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_theme;


CREATE TABLE lams_theme (
  theme_id bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  image_directory varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (theme_id),
  UNIQUE KEY UQ_name (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_timezone;


CREATE TABLE lams_timezone (
  id bigint NOT NULL AUTO_INCREMENT,
  timezone_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  server_timezone tinyint(1) DEFAULT '0',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_tool;


CREATE TABLE lams_tool (
  tool_id bigint NOT NULL AUTO_INCREMENT,
  tool_signature varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  service_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  tool_display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  tool_identifier varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  tool_version varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  learning_library_id bigint DEFAULT NULL,
  default_tool_content_id bigint DEFAULT NULL,
  valid_flag tinyint(1) NOT NULL DEFAULT '1',
  grouping_support_type_id int NOT NULL,
  learner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  learner_preview_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  learner_progress_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  author_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  monitor_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  pedagogical_planner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  help_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  create_date_time datetime NOT NULL,
  language_file varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  modified_date_time datetime DEFAULT NULL,
  admin_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  supports_outputs tinyint(1) DEFAULT '0',
  ext_lms_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (tool_id),
  UNIQUE KEY UQ_lams_tool_sig (tool_signature),
  UNIQUE KEY UQ_lams_tool_class_name (service_name),
  KEY learning_library_id (learning_library_id),
  KEY grouping_support_type_id (grouping_support_type_id),
  CONSTRAINT FK_lams_tool_1 FOREIGN KEY (learning_library_id) REFERENCES lams_learning_library (learning_library_id),
  CONSTRAINT FK_lams_tool_2 FOREIGN KEY (grouping_support_type_id) REFERENCES lams_grouping_support_type (grouping_support_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_tool_content;


CREATE TABLE lams_tool_content (
  tool_content_id bigint NOT NULL AUTO_INCREMENT,
  tool_id bigint NOT NULL,
  PRIMARY KEY (tool_content_id),
  KEY tool_id (tool_id),
  CONSTRAINT FK_lams_tool_content_1 FOREIGN KEY (tool_id) REFERENCES lams_tool (tool_id)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_tool_session;


CREATE TABLE lams_tool_session (
  tool_session_id bigint NOT NULL AUTO_INCREMENT,
  tool_session_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  tool_session_type_id int NOT NULL,
  lesson_id bigint NOT NULL,
  activity_id bigint NOT NULL,
  tool_session_state_id int NOT NULL,
  create_date_time datetime NOT NULL,
  group_id bigint DEFAULT NULL,
  user_id bigint DEFAULT NULL,
  unique_key varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (tool_session_id),
  UNIQUE KEY UQ_lams_tool_session_1 (unique_key),
  KEY tool_session_state_id (tool_session_state_id),
  KEY user_id (user_id),
  KEY tool_session_type_id (tool_session_type_id),
  KEY activity_id (activity_id),
  KEY group_id (group_id),
  CONSTRAINT FK_lams_tool_session_1 FOREIGN KEY (group_id) REFERENCES lams_group (group_id),
  CONSTRAINT FK_lams_tool_session_4 FOREIGN KEY (tool_session_state_id) REFERENCES lams_tool_session_state (tool_session_state_id),
  CONSTRAINT FK_lams_tool_session_5 FOREIGN KEY (user_id) REFERENCES lams_user (user_id),
  CONSTRAINT FK_lams_tool_session_7 FOREIGN KEY (tool_session_type_id) REFERENCES lams_tool_session_type (tool_session_type_id),
  CONSTRAINT FK_lams_tool_session_8 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_tool_session_state;


CREATE TABLE lams_tool_session_state (
  tool_session_state_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (tool_session_state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_tool_session_type;


CREATE TABLE lams_tool_session_type (
  tool_session_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (tool_session_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_user;


DROP TABLE IF EXISTS `lams_user`;

CREATE TABLE `lams_user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `login` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `salt` char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `two_factor_auth_enabled` tinyint(1) DEFAULT '0',
  `two_factor_auth_secret` char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_line_1` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_line_2` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_line_3` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `day_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `evening_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fax` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_verified` tinyint(1) DEFAULT '1',
  `disabled_flag` tinyint(1) NOT NULL DEFAULT '0',
  `create_date` datetime NOT NULL,
  `authentication_method_id` bigint(20) NOT NULL DEFAULT '0',
  `workspace_folder_id` bigint(20) DEFAULT NULL,
  `theme_id` bigint(20) DEFAULT NULL,
  `locale_id` int(11) DEFAULT NULL,
  `portrait_uuid` BINARY(16),
  `change_password` tinyint(1) DEFAULT '0',
  `timezone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_login` tinyint(1) DEFAULT '1',
  `modified_date` datetime DEFAULT NULL,
  `last_visited_organisation_id` bigint(20) DEFAULT NULL,
  `failed_attempts` tinyint(4) DEFAULT NULL,
  `lock_out_time` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `UQ_lams_user_login` (`login`),
  KEY `authentication_method_id` (`authentication_method_id`),
  KEY `workspace_folder_id` (`workspace_folder_id`),
  KEY `theme_id` (`theme_id`),
  KEY `locale_id` (`locale_id`),
  KEY `FK_lams_user_7` (`last_visited_organisation_id`),
  KEY `email` (`email`),
  CONSTRAINT `FK_lams_user_1` FOREIGN KEY (`authentication_method_id`) REFERENCES `lams_authentication_method` (`authentication_method_id`),
  CONSTRAINT `FK_lams_user_2` FOREIGN KEY (`workspace_folder_id`) REFERENCES `lams_workspace_folder` (`workspace_folder_id`),
  CONSTRAINT `FK_lams_user_5` FOREIGN KEY (`theme_id`) REFERENCES `lams_theme` (`theme_id`),
  CONSTRAINT `FK_lams_user_6` FOREIGN KEY (`locale_id`) REFERENCES `lams_supported_locale` (`locale_id`),
  CONSTRAINT `FK_lams_user_7` FOREIGN KEY (`last_visited_organisation_id`) REFERENCES `lams_organisation` (`organisation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_user_group;


CREATE TABLE lams_user_group (
  user_id bigint NOT NULL,
  group_id bigint NOT NULL,
  scheduled_lesson_end_date datetime DEFAULT NULL,
  PRIMARY KEY (user_id,group_id),
  KEY user_id (user_id),
  KEY group_id (group_id),
  CONSTRAINT FK_lams_user_group_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id),
  CONSTRAINT FK_lams_user_group_2 FOREIGN KEY (group_id) REFERENCES lams_group (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_user_organisation;


CREATE TABLE lams_user_organisation (
  user_organisation_id bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  user_id bigint NOT NULL,
  PRIMARY KEY (user_organisation_id),
  KEY organisation_id (organisation_id),
  KEY user_id (user_id),
  CONSTRAINT FK_lams_user_organisation_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id),
  CONSTRAINT FK_lams_user_organisation_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_user_organisation_collapsed;


CREATE TABLE lams_user_organisation_collapsed (
  uid bigint NOT NULL AUTO_INCREMENT,
  organisation_id bigint NOT NULL,
  user_id bigint NOT NULL,
  collapsed tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (uid),
  KEY organisation_id (organisation_id),
  KEY user_id (user_id),
  CONSTRAINT FK_lams_user_organisation_collapsed_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id),
  CONSTRAINT FK_lams_user_organisation_collapsed_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_user_organisation_group;


CREATE TABLE lams_user_organisation_group (
  group_id bigint NOT NULL,
  user_id bigint NOT NULL,
  PRIMARY KEY (group_id,user_id),
  KEY FK_lams_user_organisation_group_2 (user_id),
  CONSTRAINT FK_lams_user_organisation_group_1 FOREIGN KEY (group_id) REFERENCES lams_organisation_group (group_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_user_organisation_group_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_user_organisation_role;


CREATE TABLE lams_user_organisation_role (
  user_organisation_role_id bigint NOT NULL AUTO_INCREMENT,
  user_organisation_id bigint NOT NULL,
  role_id int NOT NULL,
  PRIMARY KEY (user_organisation_role_id),
  KEY role_id (role_id),
  KEY user_organisation_id (user_organisation_id),
  CONSTRAINT FK_lams_user_organisation_role_2 FOREIGN KEY (role_id) REFERENCES lams_role (role_id),
  CONSTRAINT FK_lams_user_organisation_role_3 FOREIGN KEY (user_organisation_id) REFERENCES lams_user_organisation (user_organisation_id)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_workspace_folder;


CREATE TABLE lams_workspace_folder (
  workspace_folder_id bigint NOT NULL AUTO_INCREMENT,
  parent_folder_id bigint DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  user_id bigint NOT NULL,
  organisation_id bigint DEFAULT NULL,
  create_date_time datetime NOT NULL,
  last_modified_date_time datetime DEFAULT NULL,
  lams_workspace_folder_type_id int NOT NULL,
  PRIMARY KEY (workspace_folder_id),
  KEY parent_folder_id (parent_folder_id),
  KEY lams_workspace_folder_type_id (lams_workspace_folder_type_id),
  CONSTRAINT FK_lams_workspace_folder_2 FOREIGN KEY (parent_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id),
  CONSTRAINT FK_lams_workspace_folder_4 FOREIGN KEY (lams_workspace_folder_type_id) REFERENCES lams_workspace_folder_type (lams_workspace_folder_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_workspace_folder_content;


CREATE TABLE lams_workspace_folder_content (
  folder_content_id bigint NOT NULL AUTO_INCREMENT,
  content_type_id int NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  create_date_time datetime NOT NULL,
  last_modified_date datetime NOT NULL,
  workspace_folder_id bigint NOT NULL,
  uuid bigint DEFAULT NULL,
  version_id bigint DEFAULT NULL,
  mime_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (folder_content_id),
  UNIQUE KEY unique_content_name (`name`,workspace_folder_id,mime_type),
  UNIQUE KEY unique_node_version (workspace_folder_id,uuid,version_id),
  KEY content_type_id (content_type_id),
  KEY workspace_folder_id (workspace_folder_id),
  CONSTRAINT FK_lams_workspace_folder_content_1 FOREIGN KEY (workspace_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS lams_workspace_folder_type;


CREATE TABLE lams_workspace_folder_type (
  lams_workspace_folder_type_id int NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (lams_workspace_folder_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS patches;


CREATE TABLE patches (
  system_name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  patch_level int NOT NULL,
  patch_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  patch_in_progress char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'F',
  PRIMARY KEY (system_name,patch_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_qb_question`
--

DROP TABLE IF EXISTS `lams_qb_question`;

CREATE TABLE `lams_qb_question` (
  `uid` BIGINT AUTO_INCREMENT,
  `uuid` BINARY(16) NOT NULL,
  `type` TINYINT NOT NULL,
  `question_id` INT NOT NULL,
  `version` SMALLINT NOT NULL DEFAULT 1,
  `create_date` DATETIME NOT NULL DEFAULT NOW(),
  `content_folder_id` char(36) NOT NULL,
  `name` TEXT,
  `description` MEDIUMTEXT,
  `max_mark` INT,
  `feedback` TEXT,
  `penalty_factor` float DEFAULT 0,
  `answer_required` TINYINT(1) DEFAULT 0,
  `multiple_answers_allowed` TINYINT(1) DEFAULT 0,
  `incorrect_answer_nullifies_mark` TINYINT(1) DEFAULT 0,
  `feedback_on_correct` TEXT,
  `feedback_on_partially_correct` TEXT,
  `feedback_on_incorrect` TEXT,
  `shuffle` TINYINT(1) DEFAULT 0,
  `prefix_answers_with_letters` TINYINT(1) DEFAULT 0,
  `case_sensitive` TINYINT(1) DEFAULT 0,
  `correct_answer` TINYINT(1) DEFAULT 0,
  `allow_rich_editor` TINYINT(1) DEFAULT 0,
  `max_words_limit` int(11) DEFAULT 0,
  `min_words_limit` int(11) DEFAULT 0,
  `hedging_justification_enabled` TINYINT(1) DEFAULT 0,
  `autocomplete_enabled` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (uid),
  CONSTRAINT UQ_question_version UNIQUE INDEX (question_id, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
                               
DROP TRIGGER IF EXISTS before_insert_qb_question;
			
CREATE TRIGGER before_insert_qb_question
  BEFORE INSERT ON lams_qb_question
  FOR EACH ROW
  SET new.uuid = IF(new.uuid IS NULL, UUID_TO_BIN(UUID()), new.uuid);


--
-- Table structure for table `lams_qb_tool_question`
--

DROP TABLE IF EXISTS `lams_qb_tool_question`;

CREATE TABLE lams_qb_tool_question (
  `tool_question_uid` BIGINT AUTO_INCREMENT,
  `qb_question_uid` BIGINT NOT NULL,
  `tool_content_id` BIGINT NOT NULL,
  `display_order` TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (tool_question_uid),
  INDEX (tool_content_id),
  CONSTRAINT FK_lams_qb_tool_question_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_qb_option`
--

DROP TABLE IF EXISTS `lams_qb_option`;

CREATE TABLE lams_qb_option (
  `uid` BIGINT AUTO_INCREMENT,
  `qb_question_uid` BIGINT NOT NULL,
  `display_order` TINYINT NOT NULL DEFAULT 1,
  `name` TEXT,
 `matching_pair` TEXT,
  `numerical_option` float DEFAULT 0,
  `max_mark` float DEFAULT 0,
  `accepted_error` float DEFAULT 0,
  `feedback` TEXT,
  PRIMARY KEY (uid),
  INDEX (display_order),
  CONSTRAINT FK_lams_qb_option_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_qb_question_unit`
--

DROP TABLE IF EXISTS `lams_qb_question_unit`;

CREATE TABLE lams_qb_question_unit (
  `uid` BIGINT AUTO_INCREMENT,
  `qb_question_uid` BIGINT NOT NULL,
  `display_order` TINYINT NOT NULL DEFAULT 1,
  `multiplier` float DEFAULT 0,
  `name` varchar(255),
  PRIMARY KEY (uid),
  CONSTRAINT FK_lams_qb_question_unit_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_qb_tool_answer`
--

DROP TABLE IF EXISTS `lams_qb_tool_answer`;

CREATE TABLE lams_qb_tool_answer (
  `answer_uid` BIGINT AUTO_INCREMENT,
  `tool_question_uid` BIGINT NOT NULL,
  `qb_option_uid` BIGINT DEFAULT NULL,
  `answer` MEDIUMTEXT,
  PRIMARY KEY (answer_uid),
  CONSTRAINT FK_lams_qb_tool_answer_1 FOREIGN KEY (tool_question_uid) REFERENCES lams_qb_tool_question (tool_question_uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_qb_tool_answer_2 FOREIGN KEY (qb_option_uid) REFERENCES lams_qb_option (uid)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_sequence_generator`
--

DROP TABLE IF EXISTS `lams_sequence_generator`;

CREATE TABLE lams_sequence_generator (
  lams_qb_question_question_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE UNIQUE INDEX IDX_lams_qb_question_question_id ON lams_sequence_generator(lams_qb_question_question_id);


--
-- Table structure for table `lams_qb_collection`
--

DROP TABLE IF EXISTS `lams_qb_collection`;

CREATE TABLE lams_qb_collection (
  `uid` BIGINT AUTO_INCREMENT, 
  `name` VARCHAR(255),
  `user_id` BIGINT,
  `personal` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (uid),
  INDEX (personal),
  CONSTRAINT FK_lams_qb_collection_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_qb_collection_question`
--

DROP TABLE IF EXISTS `lams_qb_collection_question`;

CREATE TABLE lams_qb_collection_question (
  `collection_uid`  BIGINT NOT NULL,
  `qb_question_id` INT NOT NULL,
  CONSTRAINT FK_lams_qb_collection_question_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_qb_collection_question_2 FOREIGN KEY (qb_question_id) REFERENCES lams_qb_question (question_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_qb_collection_organisation`
--

DROP TABLE IF EXISTS `lams_qb_collection_organisation`;

CREATE TABLE lams_qb_collection_organisation (
  `collection_uid`  BIGINT NOT NULL,
  `organisation_id` BIGINT NOT NULL,
  CONSTRAINT FK_lams_qb_collection_share_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_lams_qb_collection_share_2 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


--
-- Table structure for table `lams_user_organisation_collapsed`
--

DROP TABLE IF EXISTS `lams_user_organisation_collapsed`;

CREATE TABLE `lams_user_organisation_collapsed` (
  `uid` bigint(20) NOT NULL AUTO_INCREMENT,
  `organisation_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `collapsed` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`uid`),
  KEY `organisation_id` (`organisation_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `FK_lams_user_organisation_collapsed_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`),
  CONSTRAINT `FK_lams_user_organisation_collapsed_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS=1;