-- 
-- eFront v3.6.0 database creation file
--

--
-- Table 'benchmark'
--
-- This is a table used for creating a usage log. An entry is created on each call.
-- id (primary key): A numerical identifier
-- url: The url of the call
-- init_time: The time used for script initialization
-- script_time: The time used by the script, excluding initialization and smarty
-- database_time: The script time used in database queries
-- smarty_time: The script time used by smarty
-- total_time: The total script time
-- memory_usage: The peak memory usage of the script
-- total_queries: The total queries in this page
-- max_query: Details for the slower query
-- timestamp: The time of the page execution 
CREATE TABLE benchmark (
  id mediumint(8) unsigned NOT NULL auto_increment,
  url text,
  init_time float,
  script_time float,
  database_time float,
  smarty_time float,
  total_time float,
  memory_usage float,
  total_queries mediumint(8) unsigned,
  max_query text,
  timestamp int(10) unsigned NOT NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'bookmarks'
--
-- This table represents a bookmark. A bookmark is a link to a page that a student can save
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that this bookmark belongs to, matching the 'login' field at the 'users' table
-- name: The stored page title, for example 'Control Panel'
-- url: The url of the stored page, for example 'http://efront.example.com/student.php?ctg=control_panel'
CREATE TABLE bookmarks (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  lessons_ID mediumint(8) unsigned,
  name text,
  url text,
  PRIMARY KEY  (id),
  KEY users_LOGIN (users_LOGIN)
) DEFAULT CHARSET=utf8;

--
-- Table 'cache'
--
-- This table is used for caching. Each cached entity is identified by a sha256 key, which is produced
-- by the concatenation of the entity name and the entity id, for example: 'test:12'
-- Fields:
-- cache_key (primary key): The sha256 unique cached entity id
-- value: The cached entity
-- timestamp: A 10-digit number representing the event's date and time
CREATE TABLE cache ( 
  cache_key char(64) NOT NULL,
  value longtext,
  timestamp int(10) unsigned NOT NULL,
  timeout int(10) unsigned default null,
  PRIMARY KEY  (cache_key)
) DEFAULT CHARSET=utf8;

--
-- Table 'calendar'
--
-- This table represents a calendar event. 
-- Fields:
-- id (primary key): A numerical identifier
-- data: The event itself
-- timestamp: A 10-digit number representing the event's date and time
-- active: Whether this event should appear. Valid values are 0 (not active) and 1 (active)
-- private: Whether this calendar event is private (appears only in submitting user)
-- users_LOGIN: The user that posted this event, matching the 'login' field at the 'users' table
-- foreign_ID: The id of the entity that the event is related to. The value matches the 'id' field in the respective entity table (defined at the 'type' field). If it's 0, it means that this event is not associated to any entity, making it in effect 'global' (if it's not set as private of course) 
-- type: The entity that this event is related to. see calendar class for possible values 
CREATE TABLE calendar (
  id mediumint(8) unsigned NOT NULL auto_increment,
  data text,
  timestamp int(10) unsigned NOT NULL,
  active tinyint(1) NOT NULL default '1',
-- private tinyint(1) NOT NULL default '0',
  users_LOGIN varchar(100) NOT NULL,
  foreign_ID mediumint(8) unsigned default '0',
  type varchar(50) NOT NULL default '',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'comments'
--
-- This table represent a comment attached to a content unit
-- Fields:
-- id (primary key): A numerical identifier
-- data: The comment body
-- users_LOGIN: The user that created the comment
-- content_ID: The id of the unit this comment was appended to, corresponding to the 'id' field of the 'content' table. 
-- timestamp: A 10-digit number representing the time that the comment was posted
-- active: Whether this comment should appear.
-- private: Whether this comment is private (appears only in submitting user)
CREATE TABLE comments (
  id mediumint(8) unsigned NOT NULL auto_increment,
  data text NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  content_ID mediumint(8) unsigned NOT NULL default '0',
  timestamp int(10) unsigned NOT NULL,
  active tinyint(1) NOT NULL default '0',
  private tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'configuration'
--
-- This table holds the system's configuration options
-- Fields:
-- name (primary key): A word representing the option name
-- value: A string holding the option value
CREATE TABLE configuration (
  name varchar(100) NOT NULL,
  value text NOT NULL,
  PRIMARY KEY  (name)
) DEFAULT CHARSET=utf8;

--
-- Table 'content'
--
-- This table represent a content unit
-- Fields:
-- id (primary key): A numerical identifier
-- name: The unit name
-- data: The unit body
-- parent_content_ID: An id corresponding to a unit that is parent of this unit. If it's 0, then the unit has no parents (it is a 'root node')
-- lessons_ID: The id of the lesson that this unit is associated to, corresponding to the 'id' field of the 'lessons' table. 
-- timestamp: A 10-digit number representing the time that the unit was created
-- ctg_type: The type of the unit. Can be one of: 'theory', 'examples', 'tests', 'scorm', 'scorm_test'
-- active: Whether this unit is active or not. Possible values are 0 (not active) and 1 (active)
-- previous_content_ID: An id corresponding to a unit that comes before this unit. If it's 0, then the unit is the first one
-- options: A string holding a serialized array with unit options
-- metadata: A string holding a serialized array with unit metadata
-- publish: Whether the unit created should be visible to students (ie 'published') 
CREATE TABLE content (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  data longtext,
  parent_content_ID mediumint(8) unsigned default '0',
  lessons_ID mediumint(8) unsigned default '0',
  timestamp int(10) unsigned NOT NULL,
  ctg_type varchar(255) NOT NULL,
  active tinyint(1) default '1',
  previous_content_ID mediumint(8) unsigned default '0',
  options text default NULL,
  metadata text default NULL,
  scorm_version varchar(50) default NULL,
  publish tinyint(1) default '1',
  identifier varchar(255) NOT NULL default '', 
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'courses'
--
-- This table represent a course
-- Fields:
-- id (primary key): A numerical identifier
-- name: The course name
-- active: Whether this course is active or not. Possible values are 0 (not active) and 1 (active)
-- archive: A timestamp indicating when this entity was archived. If 0, then the entity is not archived. Archived entities MUST have active = 0 also
-- created: Course creation timestamp
-- start_date: The time when the course starts. Must be a db field in order to sort/filter by this
-- end_date: The time when the course ends. Must be a db field in order to sort/filter by this
-- options: A serialized array that holds the course options
-- metadata: A string holding a serialized array with unit metadata
-- description: A textual description of this entity
-- info: A serialized array holding course information
-- price: The price of the course, or 0 if it's free
-- show_catalog: Whether to show this course on the course catalog list
-- publish: Whether the course created should be visible to students (ie 'published')
-- directions_ID: The id of the direction (category) that this course belongs to, corresponding to the 'id' field of the 'directions' table
-- languages_NAME: The language that this course supports. It's value corresponds to the 'name' field of the 'languages' table
-- reset: Whether course is reset when certificate expires
-- certificate_expiration: The time duration until the certificate expires(from being issued)
-- max_users: The maximum number of users a lesson is allowed to have
-- rules: A serialized array that corresponds to the succession rules applied among lessons in the course
-- instance_source: If set, this course is an instance, and the field points to the original course id
-- supervisor_LOGIN: A user that is responsible for authorizing course assignements
-- depends_on: A course that this course depends on, in order to be accessed.
-- ceu: A simple integer representing Continuing Educational Units associated with this course
-- creator_LOGIN: The creator of the course
CREATE TABLE courses (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(150) NOT NULL,
  active tinyint(1) NOT NULL default '1',
  archive int(10) unsigned default 0,
  created int(10) unsigned default NULL,
  start_date int(10) unsigned default NULL,
  end_date int(10) unsigned default NULL,
  options text,
  metadata text,
  description text,
  info text,
  price float default '0',
  show_catalog tinyint(1) NOT NULL default '1',
  publish tinyint(1) default '1',
  directions_ID mediumint(8) unsigned default NULL,
  languages_NAME varchar(50) NOT NULL,
  reset tinyint(1) NOT NULL default '0',
  certificate_expiration int(10) unsigned default NULL,
  reset_interval int(10) unsigned default NULL,
  max_users int(10) unsigned default NULL,
  rules text,
  instance_source mediumint(8) unsigned default '0',
  supervisor_LOGIN varchar(100) default NULL,
  depends_on  mediumint(8) unsigned default '0',
  ceu int(10) unsigned default NULL,
  creator_LOGIN varchar(100) default NULL,
--  FOREIGN KEY (directions_ID) REFERENCES directions (id),
--  FOREIGN KEY (languages_ID) REFERENCES languages (id),
  PRIMARY KEY  (id),
  index(instance_source)
) DEFAULT CHARSET=utf8;
 

--
-- Table 'users_to_courses'
--
-- This table represents the relation between a user and a course
-- Fields:
-- users_LOGIN: user's login that is assiciated with the course
-- courses_ID: id of the course
-- active: if 0, then the user cannot access the course, even though he/she has it
-- archive: A timestamp indicating whether the user relation to the lesson is considered active. If 0, then it is active.
-- from_timestamp: Time when user enrolled to course. If it's 0, the user has the course but has yet to be activated by the admin
-- user_type: Type that user enrolled to course as (student, professor, etc)
-- completed: shows if course is completed by user (for student types)
-- score: shows the score that user has for the course
-- issued_certificate: stores data about the certificate that user takes for the course 
-- comments: stores comments from professor or admininstrator when user completes course
-- to_timestamp: time that user completes course  
CREATE TABLE users_to_courses (
  users_LOGIN varchar(100) NOT NULL,
  courses_ID mediumint(8) unsigned NOT NULL default '0',
  active tinyint(1) NOT NULL default '0',
  archive int(10) unsigned default 0,
  from_timestamp int(10) unsigned default NULL,
  user_type varchar(50) default NULL,
  completed tinyint(1) NOT NULL default '0',
  score int(11) NOT NULL default '0',
  issued_certificate text,
  comments text,
  to_timestamp int(10) unsigned default NULL,
--  FOREIGN KEY (users_ID) REFERENCES users (id),
--  FOREIGN KEY (courses_ID) REFERENCES courses (id),
  primary key (users_LOGIN, courses_ID),
  index(archive),
  index(users_LOGIN),
  index(courses_ID)
) DEFAULT CHARSET=utf8;


-- 
-- Table 'lessons_to_courses'
--
-- This table represents the relationship between lessons and courses
-- Fields:
-- courses_ID: The course id
-- lessons_ID: The lesson id
-- previous_lessons_ID: The id of the previous lesson. This defines the succession of lessons in the course
CREATE TABLE lessons_to_courses (
  courses_ID mediumint(8) unsigned NOT NULL,
  lessons_ID mediumint(8) unsigned NOT NULL,
  previous_lessons_ID mediumint(8) unsigned default 0,
  start_date int(10) unsigned default NULL,
  end_date int(10) unsigned default NULL,
--  FOREIGN KEY (courses_ID) REFERENCES courses (id),
--  FOREIGN KEY (lessons_ID) REFERENCES lessons (id),
--  FOREIGN KEY (previous_lessons_ID) REFERENCES lessons (id),
  PRIMARY KEY  (lessons_ID, courses_ID)
) DEFAULT CHARSET=utf8;



-- 
-- Table 'directions'
--
-- This table represents a system category (direction)
-- Fields:
-- id (primary key): A numerical identifier
-- name: The category name
-- active: Whether the category is active. Possible values are 0 (non active) and 1 (active)
-- parent_direction_ID The id of the category's parent category. If it's 0, then the category is top-most 
CREATE TABLE directions (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(150) NOT NULL,
  active tinyint(1) NOT NULL default '1',
  parent_direction_ID mediumint(8) unsigned default 0,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'done_questions'
--
-- This table holds data for test questions completed by users
-- Fields:
-- id (primary key): A numerical identifier
-- done_tests_ID: The id of the done test that the question is part of, corresponding to the 'id' field of the 'done_tests' table
-- questions_ID: The id of the question whos result is stored here, corresponding to the 'id' field of the 'questions' table
-- answer: The answer that the user gave, represented as a serialized string
-- score: The user's score in this question. If score is < 0, then it corresponds to a question which will be manually corrected 
-- timestamp: The time that the question was completed
CREATE TABLE done_questions (
  id mediumint(8) unsigned NOT NULL auto_increment,
  done_tests_ID mediumint(8) unsigned NOT NULL default '0',
  questions_ID mediumint(8) unsigned NOT NULL default '0',
  answer text,
  score float default '0',
  timestamp int(10) unsigned NOT NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'done_tests'
--
-- This table holds data for tests completed by users
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that completed the test, matching the 'login' field at the 'users' table
-- tests_ID: The id of the test whos result is stored here, corresponding to the 'id' field of the 'tests' table
-- timestamp: The time that the test was completed
-- score: The user's score in the test.
-- comments: Comments that the professor may have attached to the user
-- duration: The time (in seconds) that the user spent on the test  
CREATE TABLE done_tests (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  tests_ID mediumint(8) unsigned NOT NULL default '0',
  timestamp int(10) unsigned NOT NULL,
  score float default '0',
  comments text,
  duration mediumint(10) unsigned NOT NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_forums'
--
-- This table holds data for forums
-- Fields:
-- id (primary key): A numerical identifier
-- title: The forum's title
-- lessons_ID: The id of the lesson that this forum is attached to. If it's 0, then the forum is not associated to a lesson
-- parent_id: The id of the forum's parent forum. If it's 0, then this forum is a 'root' forum
-- status: May be 1 for 'public' (default), 2 for 'locked' or 3 for 'invisible' and corresponds to the forum's status 
-- users_LOGIN: The forum's creator, matching the 'login' field at the 'users' table
-- comments: Comments related to this forum 
CREATE TABLE f_forums (
  id mediumint(8) unsigned NOT NULL auto_increment,
  title varchar(255) NOT NULL,
  lessons_ID mediumint(8) unsigned NOT NULL default '0',
  parent_id mediumint(8) unsigned NOT NULL default '0',
  status tinyint(1) NOT NULL default '1',
  users_LOGIN varchar(100) NOT NULL,
  comments text,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_configuration'
--
-- This table holds the forum's configuration options
-- Fields:
-- name (primary key): A word representing the option name
-- value: A string holding the option value
CREATE TABLE f_configuration (
  name varchar(100) NOT NULL,
  value varchar(150) NOT NULL,
  PRIMARY KEY  (name)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_folders'
--
-- This table holds the virtual folders for personal messages
-- Fields:
-- id (primary key): A numerical identifier
-- name: The folder's name
-- users_LOGIN: The folder's owner, corresponding to the 'login' field at the 'users' table
-- parent_id: The folder's parent folder, or 0 if it's a root folder
CREATE TABLE f_folders (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(150) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  parent_id mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE(name, users_LOGIN)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_messages'
--
-- This table holds the messages posted to the forum
-- Fields:
-- id (primary key): A numerical identifier
-- f_topics_ID: The id of the topic that this message was posted in
-- title: The message's (optional) title
-- body: The message body
-- timestamp: The time that the message was posted
-- users_LOGIN: The user that posted the message, corresponding to the 'login' field at the 'users' table
-- replyto: Whether this message is a reply to another message. If so, then its value is the id of the original message, otherwise it's 0 (deprecated)
-- rank: deprecated
CREATE TABLE f_messages (
  id mediumint(8) unsigned NOT NULL auto_increment,
  f_topics_ID mediumint(8) unsigned NOT NULL default '0',
  title varchar(255) NOT NULL,
  body text NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  replyto mediumint(8) unsigned NOT NULL default '0',
  rank tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_personal_messages'
--
-- This table holds the personal messages sent among users
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that the message belongs to, corresponding to the 'login' field at the 'users' table
-- recipient: The recipent(s) of the message, a comma-seprated list of user logins
-- sender: The sender of the message, corresponding to the 'login' field at the 'users' table
-- timestamp: The time that the message was sent
-- attachments: If the message has an attachment, this field holds its identifier (an id corresponding to the 'files' table, or a full file path)  
-- title: The message's title
-- body: The message body
-- f_folders_ID: The folder that this message displays in, corresponding to the 'id' field in the 'f_folders' table
-- viewed: Boolean value where 1 means the user has read the message, and 0 he hasn't 
-- priority: A number indicating the priority of the message, where higher values mean higher priority
CREATE TABLE f_personal_messages (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  recipient text,
  sender varchar(100) NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  attachments text,
  title varchar(255) NOT NULL,
  body text NOT NULL,
  bcc tinyint(1) NOT NULL default '0',
  f_folders_ID mediumint(8) unsigned NOT NULL default '1',
  viewed tinyint(1) NOT NULL default '0',
  priority tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_poll'
--
-- This table holds the forum polls
-- Fields:
-- id (primary key): A numerical identifier
-- title: The poll title
-- question: The questions that the poll has
-- options: Poll options
-- timestamp_created: The time that this poll was created
-- users_LOGIN: The user that created the poll
-- f_forums_ID: The id of the forum that the poll belongs to
-- timestamp_start: The time that the poll will be available from
-- timestamp_end: The time that the poll will be available until
-- views: How many people have seen this poll
-- sticky: Whether this poll is "sticky" i.e. it is always on top, can be either 0 or 1
-- comments: Comments that display next to the poll
CREATE TABLE f_poll (
  id mediumint(8) unsigned NOT NULL auto_increment,
  title varchar(255) NOT NULL,
  question text NOT NULL,
  options text NOT NULL,
  timestamp_created int(10) unsigned NOT NULL,
  users_LOGIN varchar(100) NOT NULL,  
  f_forums_ID mediumint(8) unsigned NOT NULL default '0',
  timestamp_start int(10) unsigned NOT NULL,
  timestamp_end int(10) unsigned NOT NULL,
  views mediumint(8) unsigned NOT NULL default '0',
  sticky tinyint(1) NOT NULL default '0',
  comments text,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_topic'
--
-- This table holds the forum topics
-- Fields:
-- id (primary key): A numerical identifier
-- f_forums_ID: The id of the forum that the topic belongs to
-- timestamp: The time that this topic was created
-- title: The topic title
-- users_LOGIN: The user that created the topic
-- views: How many people have seen this topic
-- viewed_by: A list of user logins that have seen this topic
-- status: May be 1 for 'public' (default), 2 for 'locked' or 3 for 'invisible'
-- sticky: Whether this topic is "sticky" i.e. it is always on top, can be either 0 or 1
-- comments: Comments that display next to the topic
CREATE TABLE f_topics (
  id mediumint(8) unsigned NOT NULL auto_increment,
  f_forums_ID mediumint(8) unsigned NOT NULL default '0',
  timestamp int(10) unsigned NOT NULL,
  title varchar(255) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  views mediumint(8) unsigned default '0',
  viewed_by text,
  status tinyint(1) NOT NULL default '1',
  sticky tinyint(1) default '0',
  comments text,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'f_users_to_polls'
--
-- This table holds people's votes to a poll
-- Fields:
-- f_poll_ID: The id of the poll that the vote is for
-- users_LOGIN: The user that submited the vote
-- vote: The vote for the poll
-- timestamp: The time that this vote was submited
CREATE TABLE f_users_to_polls (
  f_poll_ID mediumint(8) unsigned NOT NULL default '0',
  users_LOGIN varchar(100) NOT NULL,
  vote tinyint(4) unsigned NOT NULL default '0',
  timestamp int(10) unsigned NOT NULL,
  primary key (f_poll_ID, users_LOGIN)
) DEFAULT CHARSET=utf8;


--
-- Table 'glossary'
--
-- This table holds glossary words
-- Fields:
-- id (primary key): A numerical identifier
-- name: The word in the glossary
-- lessons_ID: The id of the lesson that the word is for
-- info: Description of the word
-- type: The word type, for example 'general'
-- active: Whether this word should be hightlighted, can be either 1 (active) or 0
CREATE TABLE glossary (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  lessons_ID mediumint(8) unsigned NOT NULL default '0',
  info text,
  type varchar(20) NOT NULL default 'general',
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'languages'
--
-- Fields:
-- id (primary key): A numerical identifier
-- name: The language name, must be unique
-- active: Whether this language is available, can be either 1 (active) or 0
-- translation: The language name in its native language
-- rtl: Whether this is a Right-To-Left written language, can be either 1 (rtl) or 0
CREATE TABLE languages (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(50) NOT NULL,
  active tinyint(1) NOT NULL default '1',
  translation varchar(50),
  rtl tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE (name)
) DEFAULT CHARSET=utf8;


--
-- Table 'lessons'
--
-- Fields:
-- id (primary key): A numerical identifier
-- name: The lesson name
-- directions_ID: The id of the direction that this lesson belongs to, corresponds to the 'id' field of the 'directions' table
-- info: A serialized array that corresponds to the lesson information. Once unserialized, this information can instantiate an EfrontInformation object
-- price: A number corresponding to the lesson price
-- active: Whether this lesson is active. Can be either 0 (inactive) or 1 (active)
-- show_catalog: Whether to show this course on the course catalog list
-- options: A serialized array that holds the lesson options
-- languages_NAME: The lesson's language, corresponds to the 'name' field in the 'languages' table
-- metadata: The lesson metadata, in a serialized array, that can be instantiated to an EfrontInformation object
-- course_only: A flag that sets whether this lesson will be accessed through a course (1) or not (0)
-- certificate: A certificate that can be issued for this lesson
-- from_timestamp: The time that the lesson will be available from
-- to_timestamp: The time that the lesson will be available until
-- shift: Whether this lesson supports shifting based on user registration date  
-- publish: Whether the lesson created should be visible to students (ie 'published')
-- created: Lesson creation timestamp
-- max_users: The maximum number of users a lesson is allowed to have
-- archive: A timestamp indicating when this entity was archived. If 0, then the entity is not archived. Archived entities MUST have active = 0 also
-- creator_LOGIN: The creator of the lesson
CREATE TABLE lessons (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(150) NOT NULL,
  directions_ID mediumint(8) unsigned default '0',
  info text,
  price float default '0',
  active tinyint(1) NOT NULL default '1',
  show_catalog tinyint(1) NOT NULL default '1',
  duration int(10) default 0,
  access_limit int(10) default 0,
  options text,
  languages_NAME varchar(50) NOT NULL,
  metadata text,
  course_only tinyint(1) default '0',
  certificate text,
  from_timestamp int(10) unsigned default NULL,
  to_timestamp int(10) unsigned default NULL,
  shift tinyint(1) default '0',
  publish tinyint(1) default '1',
  share_folder int(10) default 0,
  created int(10) unsigned default NULL,
  max_users int(10) unsigned default NULL,
  archive int(10) unsigned default 0,
  instance_source mediumint(8) unsigned default '0',
  originating_course mediumint(8) unsigned default '0',
  creator_LOGIN varchar(100) default NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'users_to_lessons'
--
-- This table represents the relation between a user and a lesson
-- Fields:
-- users_LOGIN: user's login that is assiciated with the lesson
-- lessons_ID: id of the lesson
-- active: if 0, then the user cannot access the lesson, even though he/she has it
-- archive: A timestamp indicating whether the user relation to the lesson is considered active. If 0, then it is active.
-- from_timestamp: Time when user enrolled to lesson. If it's 0, the user has the lesson but has yet to be activated by the admin
-- user_type: Type that user enrolled to lesson as (student, professor, etc)
-- completed: shows if lesson is completed by user (for student types)
-- score: shows the score that user has for the lesson
-- issued_certificate: stores data about the certificate that user takes for the lesson 
-- comments: stores comments from professor or admininstrator when user completes lesson
-- to_timestamp: time that user completes lesson
CREATE TABLE users_to_lessons (
  users_LOGIN varchar(100) NOT NULL,
  lessons_ID mediumint(8) unsigned NOT NULL default '0',
  active tinyint(1) NOT NULL default '0',
  archive int(10) unsigned default 0,
  from_timestamp int(10) unsigned default NULL,
  user_type varchar(50) default NULL,
  positions text,
  done_content text,
  current_unit mediumint(8) unsigned default 0,
  completed tinyint(1) NOT NULL default '0',
  score tinyint(3) unsigned NOT NULL default '0',
  issued_certificate blob,
  comments text,
  to_timestamp int(10) unsigned default NULL,
  access_counter int(10) default 0,
  primary key (users_LOGIN, lessons_ID),
  index(users_LOGIN),
  index(lessons_ID),
  index(from_timestamp),
  index(archive)
) DEFAULT CHARSET=utf8;

--
-- Table 'lesson_conditions'
--
-- Fields:
-- id (primary key): A numerical identifier
-- lessons_ID: The id of the lesson that this condition refers to, corresponds to the 'id' field of the 'lessons' table
-- type: The type of the condition, can be 'all_units', 'percentage_units', 'specific_unit', 'all_tests', 'specific_test'
-- options: Options that comprise the condition, depending on its type, for example the percentage or the unit id
-- relation: The condition's relation to other conditions of the same lesson, can be 'and' or 'or'
CREATE TABLE lesson_conditions (
  id mediumint(8) unsigned NOT NULL auto_increment,
  lessons_ID mediumint(8) unsigned NOT NULL,
  type varchar(255) NOT NULL,
  options text,
  relation varchar(255) NOT NULL default 'and',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'logs'
--
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that this log entry refers to
-- timestamp: The time that this log entry was triggered
-- action: The action that triggered the entry, for example 'login', 'logout', 'lesson', 'tests', 'content', 'personal', 'lastmove'
-- comments: Special data referring to the action, for example the session id, or the content id
-- session_ip: The ip of the user that initiated the log entry
-- lessons_ID: The id of the lesson that may has to do with the log entry
CREATE TABLE logs (
  id int(11) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  action varchar(255) NOT NULL,
  comments varchar(32) NOT NULL default '0',
  session_ip char(8) NOT NULL default '0',
  lessons_ID mediumint(8) unsigned default '0',
  PRIMARY KEY  (id),
  index(timestamp),
  index(users_LOGIN),
  index(action)
) DEFAULT CHARSET=utf8;


CREATE TABLE modules ( 
  className varchar(150) NOT NULL,
  db_file varchar(255),
  name varchar(150) NOT NULL,
  active tinyint(1) NOT NULL,
  title varchar(150) NOT NULL,
  author varchar(100) default NULL,
  version varchar(10) default NULL,
  description text,
  position varchar(150) NOT NULL,
  menu varchar(255) default NULL,
  mandatory varchar(255) default NULL,
  permissions varchar(32) NOT NULL default 'administrator',
  PRIMARY KEY  (className)
) DEFAULT CHARSET=utf8;

--
-- Table 'news'
--
-- Fields:
-- id (primary key): A numerical identifier
-- title: The announcement's title
-- data: The annoucnement itself
-- timestamp: The time that the announcement will be valid from (unix timestamp)
-- expire: The time that the announcement will be valid until (unix timestamp)
-- lessons_ID: The id of the lesson that this announcement concerns. If it's 0, it's a system announcement
-- users_LOGIN: The user that posted the announcement  
CREATE TABLE news (
  id mediumint(8) unsigned NOT NULL auto_increment,
  title varchar(255) default NULL,
  data text,
  timestamp int(10) unsigned default 0,
  expire int(10) unsigned default 0,
  lessons_ID mediumint(8) unsigned default NULL,
  users_LOGIN varchar(100) NOT NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'periods' DEPRECATED
--
-- Fields:
-- id (primary key): A numerical identifier
-- name: The period name
-- from_timestamp: The time that the period starts
-- to_timestamp: The time that the period ends
-- lessons_ID: The lesson that this period is for
CREATE TABLE periods (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(150) NOT NULL,
  from_timestamp int(10) unsigned NOT NULL,
  to_timestamp int(10) unsigned NOT NULL,
  lessons_ID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'projects'
--
-- Fields:
-- id (primary key): A numerical identifier
-- title: The project name
-- data: The complete description for this project
-- deadline: The timestamp indicating when this project expires
-- creator_LOGIN: The user who created the project
-- lessons_ID: The lesson that this project belongs to
-- auto_assign: Whether to automatically assign this project to new users
-- metadata: The project metadata, in a serialized array, that can be instantiated to an EfrontInformation object
CREATE TABLE projects (
  id mediumint(8) unsigned NOT NULL auto_increment,
  title varchar(150) default NULL,
  data text,
  deadline int(10) unsigned default NULL,
  creator_LOGIN varchar(100) NOT NULL,
  lessons_ID mediumint(8) unsigned default NULL,
  auto_assign tinyint(1) NOT NULL default '0',
  metadata text,
  PRIMARY KEY  (id),
  KEY creator_LOGIN (creator_LOGIN),
  KEY deadline (deadline)
) DEFAULT CHARSET=utf8;

--
-- Table 'questions'
--
-- Fields:
-- id (primary key): A numerical identifier
-- text: The question text
-- type: The question type, may be one out of: 'raw_text', 'multiple_one', 'multiple_many', 'match', 'true_false', 'empty_spaces' 
-- content_ID: The unit that this question is associated to. Corresponds to the 'id' field of the 'content' table. If 0, then the questions is not associated to any unit  
-- difficulty: The difficulty of the question, may be one out of: 'easy', 'medium', 'hard'
-- options: A serialized array with the question options, for example if it's a multiple questions, the different options
-- answer: The answer(s) of the question, usually in a serialized array
-- explanation: A default explanation added by the professor that displays when the question is completed 
-- answers_explanation: Explanations per answer
-- estimate: A number depicting the estimated time (in seconds) a user will need to complete the question  
-- settings: A serialized array with the other question options
CREATE TABLE questions (
  id mediumint(8) unsigned NOT NULL auto_increment,
  text text NOT NULL,
  type varchar(255) NOT NULL,
  content_ID mediumint(8) unsigned NOT NULL default '0',
  lessons_ID mediumint(8) unsigned NOT NULL default '0',
  difficulty varchar(255) NOT NULL,
  options text,
  answer text,
  explanation text,
  answers_explanation text default NULL,
  estimate int(10) unsigned default NULL,
  settings text,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'themes'
--
-- Fields:
-- id (primary key): A numerical identifier
-- name: The theme name
-- title: The theme title
-- author: The author of the theme
-- version: The version of the theme
-- description: An extended description of the theme
-- options: The options of the theme, a serialized array
-- layout: Layout settings for the current theme
-- path: The path to this theme, can be a local or remote folder
CREATE TABLE themes (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(100) NOT NULL,
  title varchar(100) default NULL,
  author varchar(100) default NULL,
  version varchar(10) default NULL,
  description text,
  options text,
  layout text,
  path text NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE (name)
) DEFAULT CHARSET=utf8;


--
-- Table 'rules'
--
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: student that rule is refered to
-- content_ID: The unit that rule is refered to
-- rule_type: type of the rule may be one out of: 'always','hasnot_seen'
-- rule_content_ID: The unit that rule depends on
-- rule_option: deprecated
-- lessons_ID: The lesson that the rule belongs to
CREATE TABLE rules (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  content_ID mediumint(8) unsigned NOT NULL default '0',
  rule_type varchar(255) NOT NULL,
  rule_content_ID mediumint(8) unsigned default '0',
  rule_option float default '0',
  lessons_ID mediumint(8) unsigned default '0',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE scorm_data (
  id mediumint(8) unsigned NOT NULL auto_increment,
  content_ID mediumint(8) unsigned NOT NULL default '0',
  users_LOGIN varchar(100) default NULL,
  timestamp int(10) unsigned default NULL,
  lesson_location text,
  maxtimeallowed varchar(255) default NULL,
  timelimitaction varchar(255) default NULL,
  masteryscore varchar(255) default NULL,
  datafromlms text,
  entry varchar(255) NOT NULL default '',
  total_time varchar(255) default NULL,
  comments varchar(255) default NULL,
  comments_from_lms text,
  lesson_status varchar(255) default NULL,
  score varchar(255) default NULL,
  scorm_exit varchar(255) default NULL,
  minscore varchar(255) default NULL,
  maxscore varchar(255) default NULL,
  suspend_data text,
  completion_threshold varchar(255) default NULL,
  completion_status varchar(255) default NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE search_keywords (
  keyword mediumint(8) unsigned default NULL,
  foreign_ID mediumint(8) unsigned NOT NULL default '0',
  table_name tinyint(1) NOT NULL,
  position tinyint(1) NOT NULL default '1',
  key keyword (keyword),
  key foreign_ID (foreign_ID),
  key table_name (table_name),
  key position (position)
) DEFAULT CHARSET=utf8;

CREATE TABLE search_invertedindex (
  id mediumint(8) unsigned NOT NULL auto_increment,
  keyword varchar(150) NOT NULL,
  key keyword (keyword),
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;



--
-- Table 'tests'
--
-- Fields:
-- id (primary key): A numerical identifier
-- active: Whether this lesson is active. Can be either 0 (inactive) or 1 (active)
-- content_ID: The unit that this test is associated to. Corresponds to the 'id' field of the 'content' table
-- lessons_ID: The id of the lesson that this test is associated to, or 0 if it's not associated to any lesson
-- name: The name of the test
-- test_active: Whether the test is active  
-- description: A textual description of the test
-- mastery_score: A number 0-100 that sets the score above which the test is regarded as "passed"
-- options: A serialized array of options
-- publish: Whether the lesson created should be visible to students (ie 'published')
CREATE TABLE tests (
  id mediumint(8) unsigned NOT NULL auto_increment,
  active tinyint(1) NOT NULL default '1',
  content_ID mediumint(8) unsigned NOT NULL default '0',
  lessons_ID mediumint(8) unsigned NOT NULL default '0',
  name varchar(255) NOT NULL default '',
  mastery_score tinyint(4) unsigned NOT NULL default '0',
  description text,
  options text,
  publish tinyint(1) default '1',
  keep_best tinyint(1) default '0',
  key lessons_ID (lessons_ID),
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE tests_to_questions (
  tests_ID mediumint(8) unsigned NOT NULL default '0',
  questions_ID mediumint(8) unsigned NOT NULL default '0',
  weight tinyint(1) unsigned NOT NULL default '1',
  previous_question_ID mediumint(8) unsigned NOT NULL default '0',
  primary key (tests_ID, questions_ID)
) DEFAULT CHARSET=utf8;

--
-- Table 'completed_tests'
--
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that has done this test, matching the 'login' field at the 'users' table
-- tests_ID: The id of the test that this instance corresponds to. If 0, the corresponding test no longer exists
-- test: The serialized EfrontCompletedTest object
-- status: The completed test status, can be '', 'incomplete', 'failed', 'passed', 'completed', 'deleted'
-- timestamp: The time that the test started
-- archive: A boolean value that sets whether this completed test is archive. Archived tests are passed tests, and may be accessed only through the corresponding sections
-- time_start: The time that the test started
-- time_end: The time that the test ended
-- time_spent: The total time spent in this test (can be different than time_end-time_start, due to pauses)
-- score: The user's score in this test
-- pending: Whether the test is pending
CREATE TABLE completed_tests (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) default NULL,
  tests_ID mediumint(8) unsigned NOT NULL default '0',
  test longblob,
  status varchar(255),
  timestamp int(10) unsigned NOT NULL default 0,
  archive tinyint(1) NOT NULL default 0,
  time_start int(10) unsigned,
  time_end int(10) unsigned, 
  time_spent int(10) unsigned,
  score float,
  pending tinyint(1) NOT NULL default 0,
  key users_login (users_login),
  key tests_ID (tests_ID),
  key status (status),
  key timestamp (timestamp),
  key archive (archive),
  key score (score),
  key pending (pending),
  primary key (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'users'
--
-- This table represents a user entity
-- Fields:
-- id (key): A numerical identifier
-- login (primary key): The user identification string, for example 'jdoe'
-- password: The user's password for the system, for example 'a4807fe70ffc6466c7b9bcdeb084d5df'
-- email: The user's email, for example 'jdoe@example.com'
-- languages_NAME: A string representing the user's language. This matches the key field 'name' in the 'languages' table. For example, 'english'
-- timezone: a string denoting this user's time zone - since 3.6.0
-- name: The user's first name, for example 'John' 
-- surname: The user's last name, for example 'Doe'
-- active: Whether this user is active. Possible values are 0 (inactive) and 1 (active)
-- comments: Any comments that have to do with the user
-- user_type: The basic user type. Valid values are: 'student', 'professor', 'administrator'
-- timestamp: A 10-digit number representing the time that the user registered to the system
-- avatar: A file path or id (corresponding to the 'files' table) that represents a user image
-- pending: Whether this user has registered to the system, but his registration is not confirmed yet by the administrator. Possible values are 0 (normal) and 1 (pending)
-- user_types_ID: An id corresponding to the 'user_types' table, that represents a special user type (custom user types based on the predefined 'student', 'professor', 'administrator')
-- additional_accounts: A serialized list of account logins, that the user may automatically switch to.
-- status: the eFront social status-mood text 
-- short_description: the eFront social short description - CV text
-- balance: The credit left to the user for getting lessons
-- archive: A timestamp indicating when this entity was archived. If 0, then the entity is not archived. Archived entities MUST have active = 0 also
-- dashboard_positions: The positions of the elements on the user's dashboard
-- need_mod_init: A special flag used by the jfusion plugin
-- autologin: A special url for logging automatically
-- need_pwd_change: If it's 1, then this user must change his/her password on next login
CREATE TABLE users (
  id mediumint(8) unsigned NOT NULL auto_increment,
  login varchar(100) NOT NULL,
  password char(32) NOT NULL,
  email varchar(150) NOT NULL,
  languages_NAME varchar(50) NOT NULL,
  timezone varchar(100) default "",
  name varchar(100) NOT NULL,
  surname varchar(100) NOT NULL,
  active tinyint(1) NOT NULL default '1',
  comments text,
  user_type varchar(50) NOT NULL default 'student',
  timestamp int(10) unsigned NOT NULL,
  avatar varchar(255) default NULL,
  pending tinyint(1) NOT NULL default '0',
  user_types_ID mediumint(8) default '0',
  additional_accounts text,
  viewed_license tinyint(1) default '0',
  status varchar(255) default '',
  short_description text,
  balance float default 0,
  archive int(10) unsigned default 0,
  dashboard_positions text,
  need_mod_init tinyint(1) default '0',
  autologin char(32),
  need_pwd_change tinyint(1) default '0',
  last_login int(10) unsigned NOT NULL,
  PRIMARY KEY  (login),
  KEY (id),
  index(active)
) DEFAULT CHARSET=utf8;


CREATE TABLE users_to_projects (
  users_LOGIN varchar(100) NOT NULL,
  projects_ID mediumint(8) unsigned NOT NULL default '0',
  status tinyint(1) NOT NULL default '0',
  comments text,
  grade float default NULL,
  filename varchar(255) default NULL,
  upload_timestamp int(10) unsigned default NULL,
  last_comment varchar(100) DEFAULT NULL,
  primary key (users_LOGIN, projects_ID)
) DEFAULT CHARSET=utf8;


CREATE TABLE user_types (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(50) NOT NULL,
  basic_user_type varchar(50) NOT NULL,
  core_access text,
  modules_access text,
  active tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE tokens (
  token char(30) NOT NULL,
  status text  NOT NULL,
  users_LOGIN varchar(100),
  create_timestamp int(10) unsigned NOT NULL,
  expired tinyint(1) NOT NULL ,
  PRIMARY KEY  (token)
) DEFAULT CHARSET=utf8;

CREATE TABLE files (
  id mediumint(8) unsigned NOT NULL auto_increment,
  path text NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  description text,
  groups_ID mediumint(8) unsigned not null default 0,
  access smallint(3) unsigned not null default 755,
  shared mediumint(8) unsigned default 0,
  metadata text,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;




--
-- Table 'groups'
--
-- This table represent a group of users
-- Fields:
-- id (primary key): A numerical identifier
-- name: The group name
-- description: A description for the group
-- active: Whether the group is active
-- dynamic: Whether this group is dynamic. Dynamic groups are created for mass operations and are not visible in group lists. The 'active' flag for dynamic groups is not considered
-- created: A timestamp indicating when this group was created
-- user_types_ID: The custom user type of the group
-- languages_NAME: The language of the group
-- users_active: Whether the members of this group should be set to 'active' or 'inactive'
-- assign_profile_to_new: Whether to automatically assign the group profile to new users
-- unique_key: A key identifying the group, that a user can use in order to automatically get the group's courses and lessons
-- is_default: Whether this is the default group, meaning that new users are assigned to it
-- key_max_usage: How many times a key may be used
-- key_current_usage: How many times the key has been used so far
-- self_enroll: Whether to allow users to enroll to the group themselves
CREATE TABLE groups (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(150) NOT NULL,
  description text,
  active tinyint(1) NOT NULL default '1',
  dynamic tinyint(1) NOT NULL default '0',
  created int(10) unsigned default NULL,
  user_types_ID varchar(50) default '0',
  languages_NAME varchar(50) default NULL,
  users_active tinyint(1) default 0,
  assign_profile_to_new tinyint(1) default 0,
  unique_key varchar(255) default "",
  is_default tinyint(1) default 0,
  self_enroll tinyint(1) default 0,
  key_max_usage mediumint(8) unsigned default 0,
  key_current_usage mediumint(8) unsigned default 0,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

--
-- Table 'lessons_to_groups'
--
-- This table represents the relation of lessons to groups
-- Fields:
-- lessons_ID: The id of the lesson
-- user_type: The default user type that users are assigned as to the lesson
-- groups_ID: The id of the group
CREATE TABLE lessons_to_groups (
  lessons_ID mediumint(8) unsigned NOT NULL,
  user_type varchar(50) default 'student',
  groups_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY(lessons_ID, groups_ID)
) DEFAULT CHARSET=utf8;

--
-- Table 'courses_to_groups'
--
-- This table represents the relation of courses to groups
-- Fields:
-- lessons_ID: The id of the course
-- user_type: The default user type that users are assigned as to the course
-- groups_ID: The id of the group
CREATE TABLE courses_to_groups (
  courses_ID mediumint(8) unsigned NOT NULL,
  user_type varchar(50) default 'student',
  groups_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY(courses_ID, groups_ID)
) DEFAULT CHARSET=utf8;

--
-- Table 'users_to_groups'
--
-- This table represents the relation of users to groups
-- Fields:
-- groups_ID: The id of the group
-- users_LOGIN: The login of the user
CREATE TABLE users_to_groups (
  groups_ID mediumint(8) unsigned NOT NULL,
  users_LOGIN varchar(100)  NOT NULL,
  PRIMARY KEY  (groups_ID,users_LOGIN)
) DEFAULT CHARSET=utf8;


--
-- Table 'user_times'
--
-- This table is used to store times for users inside the system, per session
-- Fields:
-- id (primary key): A numerical identifier
-- session_timestamp: The time when this session started
-- session_id: The id of the session
-- session_custom_identifier: A custom identifier for the user's session, which is reset everytime he/she logs in
-- session_expired: Whether this session has expired
-- users_LOGIN: The login of the user
-- timestamp_now: The timestamp of the latest action of the user. It is compared to autologout_time limit, and if it's less, the user is automatically logged out
-- time: The total time the user spent on the specific entity, during this session
-- lessons_ID: The lesson id, if any
-- courses_ID: The courses id, if any
-- entity: A string representing the place the user spent some time. For example, 'unit', 'lesson', 'system'
-- entity_id: if applicable, the id of the entity the user spent time in, for example it can be a unit id 
CREATE TABLE user_times (
  id mediumint(8) unsigned NOT NULL auto_increment,
  session_timestamp int(10) unsigned NOT NULL,
  session_id varchar(255) NOT NULL,
  session_custom_identifier char(40) NOT NULL default '',
  session_expired tinyint(1) NOT NULL default 0,
  users_LOGIN varchar(100) NOT NULL,
  timestamp_now int(10) unsigned NOT NULL,
  time int(10) unsigned NOT NULL,
  lessons_ID mediumint(8) unsigned default NULL,
  courses_ID mediumint(8) unsigned default NULL,
  entity varchar(100) NOT NULL,
  entity_id mediumint(8) default 0,    
  PRIMARY KEY  (id),
  key(users_LOGIN),
  key(session_expired),
  key(entity),  
  key(lessons_ID)  
) DEFAULT CHARSET=utf8;







CREATE TABLE questions_to_skills (
 questions_id mediumint(8) unsigned NOT NULL,
 skills_ID mediumint(8) unsigned not null,
 relevance int(1) default 1,
 KEY  (questions_id, skills_ID)
) DEFAULT CHARSET=utf8;




CREATE TABLE events (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  users_name varchar(255) NOT NULL,
  users_surname varchar(255) NOT NULL,
  timestamp int(10) NOT NULL,
  type int(11) NOT NULL,
  lessons_ID varchar(255) default NULL,
  lessons_name varchar(255) default NULL,
  entity_ID varchar(255) default NULL,
  entity_name varchar(255) default NULL,
  PRIMARY KEY  (id),
  index(users_LOGIN),
  index(timestamp)
) DEFAULT CHARSET=utf8;

CREATE TABLE profile_comments (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  authors_LOGIN varchar(100) NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  data text NOT NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;



-- 
-- Table 'notifications'
--
-- timestamp of date for the email to be sent
-- send_interval should the email be sent periodically? if so this is the period
-- send_conditions to which category should the email be sent?
-- type_entity the type of the event . "_" . the ID of the involved entity from the list in events.class.php OR NULl if we have a non-event notification
-- recipient if IS NOT NULL then this is the single recipient's login of the email
-- html_message: 0 text/plain message body, 1 text/html message body
-- subject/message: self-explanatory
--
CREATE TABLE notifications (
  id mediumint(8) unsigned NOT NULL auto_increment,
  timestamp int(10) NOT NULL,
  send_interval varchar(10) NOT NULL default 0,
  send_conditions text,  
  id_type_entity varchar(255) default NULL, 
  recipient varchar(100), 
  subject varchar(255) NOT NULL,
  message text,
  active tinyint(1) default 1,
  html_message tinyint(1) default 0,
  PRIMARY KEY(id),
  index(recipient)
) DEFAULT CHARSET=utf8;

-- 
-- Table 'event_notifications'
--
-- event_type the type of the event from the list in events.class.php
-- after_time how long after the event triggering should the notification be send
-- send_conditions should the notification apply to a particular lesson, test etc
-- send_recipients: 0 user triggering the event, 1: all users of same lesson
-- html_message: 0 text/plain message body, 1 text/html message body
-- subject, message, active: self-explanatory
--
CREATE TABLE event_notifications (
  id mediumint(8) unsigned NOT NULL auto_increment,
  event_type int(11) NOT NULL,
  after_time int(10) NOT NULL default '0',  
  send_conditions text,
  send_recipients int(1) default 1,
  subject varchar(255) NOT NULL, 
  message text,
  active tinyint(1) default 1,
  html_message tinyint(1) default 0,
  send_immediately tinyint(1) default 0,
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;

CREATE TABLE sent_notifications (
  id mediumint(8) unsigned NOT NULL auto_increment,
  timestamp int(10) NOT NULL,
  recipient varchar(255),
  subject varchar(255) NOT NULL, 
  body text,
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;



-- 
-- Table 'users_to_content'
--
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that this bookmark belongs to, matching the 'login' field at the 'users' table
-- content_ID: The id of the unit this comment was appended to, corresponding to the 'id' field of the 'content' table. 
-- success_status: The status of the user for this unit, can be either 'passed', 'failed' 'incomplete', or 'unknown'
-- timestamp: The last time that the unit's status changed
-- score: The user's score in the unit.
-- entry: The status of the learner in this unit, can be either 'ab-initio', 'resume' or ''
-- total_time: The total time the learner has spent in this unit
-- suspend_data: Any data that the content might want to store, such as the whole test dump
-- archive: A boolean value that sets whether this occurence is archive
-- time_start: The time that the unit started
-- time_end: The time that the unit ended
-- pending: Whether the unit is pending
CREATE TABLE users_to_content (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  content_ID mediumint(8) unsigned NOT NULL,
  lessons_ID mediumint(8) unsigned NOT NULL,
  success_status varchar(15) DEFAULT 'unknown',
  timestamp int(10) unsigned,
  score float default '0',
  entry varchar(15) DEFAULT '',
  total_time int(10) unsigned NOT NULL default 0,
  suspend_data longtext,
  archive tinyint(1) NOT NULL default 0,
  time_start int(10) unsigned,
  time_end int(10) unsigned, 
  pending tinyint(1) NOT NULL default 0,
  primary key (id),
  unique(users_LOGIN, content_ID, lessons_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE lessons_timeline_topics (
  id mediumint(8) unsigned NOT NULL auto_increment,
  lessons_ID mediumint(8) unsigned NOT NULL,
  title varchar(255) NOT NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE lessons_timeline_topics_data (
  id mediumint(8) unsigned NOT NULL auto_increment,
  topics_ID mediumint(8) unsigned NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  data text default NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE user_profile (
  name varchar(50) NOT NULL,
  description varchar(100) NOT NULL,
  db_type varchar(10) NOT NULL,
  size tinyint(3) unsigned default 255,
  type varchar(10) default NULL,
  options text,
  default_value text default NULL,
  active tinyint(1) NOT NULL default '1',
  visible tinyint(1) NOT NULL default '1',
  mandatory tinyint(1) NOT NULL default '1',
  languages_NAME varchar(50) NOT NULL,
  PRIMARY KEY  (name)
) DEFAULT CHARSET=utf8;

CREATE TABLE questions_to_surveys (
  id mediumint(8) unsigned NOT NULL auto_increment,
  surveys_ID mediumint(8) unsigned default NULL,
  type varchar(40) default NULL,
  question mediumtext,
  answers mediumtext default NULL,
  created int(10) unsigned default NULL,
  info mediumtext default NULL,
  father_ID mediumint(8) unsigned default NULL,
  PRIMARY KEY  (id),
  KEY surveys_ID (surveys_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE surveys (
  id mediumint(8) unsigned NOT NULL auto_increment,
  survey_code varchar(150) default NULL,
  survey_name varchar(150) default NULL,
  survey_info mediumtext,
  author varchar(100),
  lang varchar(50) default NULL,  
  start_date int(10) unsigned default NULL,
  end_date int(10) unsigned default NULL,
  lessons_ID mediumint(8) unsigned NOT NULL,
  status tinyint(1) default '0',
  start_text mediumtext,
  end_text mediumtext,
  PRIMARY KEY  (id),
  KEY survey_code (survey_code)
) DEFAULT CHARSET=utf8; 

CREATE TABLE survey_questions_done (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100) NOT NULL,
  surveys_ID mediumint(8) unsigned NOT NULL,
  question_ID mediumint(8) unsigned NOT NULL,
  user_answers mediumtext NOT NULL,
  submited int(10) unsigned default NULL,
  PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

CREATE TABLE users_to_done_surveys (
  surveys_ID mediumint(8) unsigned default NULL,
  users_LOGIN varchar(100) default NULL,
  done tinyint(1) NOT NULL default '0',
  primary key (users_LOGIN, surveys_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE users_to_surveys (
  surveys_ID mediumint(8) unsigned NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  last_access int(10) unsigned default NULL, 
  last_post int(10) unsigned default NULL,
  KEY surveys_ID (surveys_ID,users_LOGIN),
  primary key (users_LOGIN, surveys_ID)
) DEFAULT CHARSET=utf8;

--
-- Table 'carts'
--
-- This table is used to store carts.
-- Fields:
-- id (primary key): A numerical identifier
-- timestamp: A 10-digit number representing the carts's last update time
-- session_id: The session id associated with this cart
-- contents: The contents of the cart 
CREATE TABLE carts (
  id mediumint(8) unsigned NOT NULL auto_increment,
  timestamp int(10) unsigned NOT NULL,
  session_id varchar(255) NOT NULL,
  contents text,
  PRIMARY KEY  (id)
)  DEFAULT CHARSET=utf8;


-- #cpp#ifndef COMMUNITY

--
-- Table 'coupons'
--
-- This table is used to store discount coupons.
-- Fields:
-- id (primary key): A numerical identifier
-- code (unique): A unique, human-defined code for the coupon
-- max_uses: The maximum number of times this coupon may be used
-- max_user_uses: The maximum number of times this coupon may be used by a single user
-- duration: The duration, in seconds, that the coupon will be valid
-- discount: The discount percentage of the coupon
-- description: An extended description of the coupon, that is send with the notification
-- active: Whether the coupon is active (1) or inactive (0)
-- from_timestamp: A 10-digit number representing the date when the coupon will become valid
CREATE TABLE coupons (
  id int(10) unsigned NOT NULL auto_increment,
  code varchar(150) NOT NULL,
  max_uses int(10) unsigned NOT NULL default 0,
  max_user_uses int(10) unsigned NOT NULL default 0,
  duration int(10) unsigned NOT NULL default 30,
  discount int(10) unsigned NOT NULL default 0,
  description text,
  active tinyint(1) unsigned NOT NULL default 1,
  from_timestamp int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE(code)
)  DEFAULT CHARSET=utf8;

--
-- Table 'users_to_coupons'
--
-- This table is used to store the relationship between users and coupons
-- Fields:
-- id (primary key): A numerical identifier
-- users_ID: The id of the user that used the coupon
-- coupons_ID: The id of the coupon used
-- payments_ID: The id of the corresponding entry in the payments table
-- products_list: A serialized array that describes the courses/lessons bought with this coupon
-- timestamp: A 10-digit number representing the date when the coupon was used
CREATE TABLE users_to_coupons (
  id int(10) unsigned NOT NULL auto_increment,
  users_ID int(10) unsigned NOT NULL,
  coupons_ID int(10) unsigned NOT NULL,
  payments_ID int (10) unsigned NOT NULL,
  products_list text,
  timestamp int(10) unsigned NOT NULL,
  PRIMARY KEY  (id)
)  DEFAULT CHARSET=utf8;


CREATE TABLE paypal_data (
  id int(10) unsigned NOT NULL auto_increment,
  mc_gross varchar(20) default NULL,
  settle_amount varchar(15) default NULL,
  address_status varchar(10) default NULL,
  payer_id varchar(20) default NULL,
  tax varchar(20) default NULL,
  address_street varchar(150) default NULL,
  payment_date varchar(30) default NULL,
  payment_status varchar(10) default NULL,
  charset varchar(10) default NULL,
  address_zip varchar(20) default NULL,
  first_name varchar(30) default NULL,
  mc_fee varchar(20) default NULL,
  address_country_code varchar(3) default NULL,
  exchange_rate varchar(20) default NULL,
  address_name varchar(100) default NULL,
  notify_version varchar(5) default NULL,
  settle_currency varchar(5) default NULL,
  custom varchar(60) default NULL,
  payer_status varchar(10) default NULL,
  business varchar(60) default NULL,
  address_country varchar(30) default NULL,
  address_city varchar(30) default NULL,
  quantity varchar(6) default NULL,
  verify_sign varchar(10) default NULL,
  payer_email varchar(120) default NULL,
  txn_id varchar(255) default NULL,
  payment_type varchar(30) default NULL,
  last_name varchar(120) default NULL,
  address_state varchar(30) default NULL,
  receiver_email varchar(120) default NULL,
  payment_fee varchar(10) default NULL,
  receiver_id varchar(60) default NULL,
  txn_type varchar(10) default NULL,
  item_name text,
  mc_currency varchar(3) default NULL,
  item_number text,
  residence_country varchar(3) default NULL,
  test_ipn varchar(3) default NULL,
  payment_gross varchar(20) default NULL,
  shipping varchar(20) default NULL,
  transactionID varchar(50) NOT NULL,
  status varchar(15) NOT NULL,
  timestamp int(10) NOT NULL,
  timestamp_finish int(10) NOT NULL,
  user varchar(255) NOT NULL,
  PRIMARY KEY  (id)
)  DEFAULT CHARSET=utf8;

--
-- Table 'payments'
--
-- This table represents a payment in the system. 
-- Fields:
-- id (primary key): A numerical identifier
-- users_LOGIN: The user that this payment is about, matching the 'login' field at the 'users' table
-- timestamp: A 10-digit number representing the event's date and time
-- method: The method of the payment, can be one of 'paypal', 'manual', 'balance'
-- amount: The amount of money that this payment is about
-- status: The status of the payment. Can be one of 'completed', 'pending'
-- txn_id: The id of the transaction, as sent by paypal (valid only for paypal payments)
-- comments: Any specific comments regarding this payment
CREATE TABLE payments (
  id mediumint(8) unsigned NOT NULL auto_increment,
  users_LOGIN varchar(100)  NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  method varchar(100) NOT NULL default 'manual',  
  amount float default '0',
  status varchar(100) NOT NULL default 'completed',
  txn_id varchar(50) default null,
  comments text,
  PRIMARY KEY  (id)
)  DEFAULT CHARSET=utf8;

CREATE TABLE facebook_connect (
  fb_uid varchar(20) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  fb_name varchar(150) NOT NULL,
  primary key (fb_uid, users_LOGIN)
) DEFAULT CHARSET=utf8;


CREATE TABLE advanced_user_reports (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar (255) NOT NULL,
  rules text,
  PRIMARY KEY (id)  
) DEFAULT CHARSET=utf8;

CREATE TABLE certificate_templates (
  id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  certificate_name varchar(100) NOT NULL,
  certificate_xml longtext NOT NULL,
  certificate_type varchar(45) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

-- #cpp#endif

-- #cpp#ifdef ENTERPRISE


CREATE TABLE module_hcd_employee_has_job_description (
  users_login varchar(100) NOT NULL, 
  job_description_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (users_login, job_description_ID),
  index(users_login),
  index(job_description_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_lesson_to_job_description (
  lessons_ID mediumint(8) unsigned NOT NULL,
  job_description_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (lessons_ID, job_description_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_lesson_to_branch (
  lessons_ID mediumint(8) unsigned NOT NULL,
  branches_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (lessons_ID, branches_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_course_to_job_description (
  courses_ID mediumint(8) unsigned NOT NULL,
  job_description_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (courses_ID, job_description_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_course_to_branch (
  courses_ID mediumint(8) unsigned NOT NULL,
  branches_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (courses_ID, branches_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_job_description_requires_skill (
  skill_ID mediumint(8) unsigned NOT NULL,
  job_description_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (skill_ID, job_description_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_branch (
  branch_ID mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL default 'New Branch',
  address varchar(255),
  city varchar(255),
  country varchar(255),
  telephone varchar(255),
  email varchar(255),
  father_branch_ID mediumint(8) unsigned default 0,
  themes_ID mediumint(8) unsigned default NULL,
  languages_NAME varchar(50) default NULL, 
  url varchar(255) default NULL, 
  PRIMARY KEY (branch_ID)
) DEFAULT CHARSET=utf8;


CREATE TABLE module_hcd_job_description (
  job_description_ID mediumint(8) unsigned NOT NULL auto_increment,
  employees_needed mediumint(8) unsigned default '1',
  description varchar(255) NOT NULL default 'New Job Description',
  job_role_description text, 
  required_training text,
  branch_ID mediumint(8) unsigned default '0',
  PRIMARY KEY (job_description_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_employee_works_at_branch (
  users_login varchar(100) NOT NULL,
  supervisor tinyint(1) default 0,
  assigned tinyint(1) default 0, 
  branch_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY users_login (users_login, supervisor, assigned, branch_ID) 
) DEFAULT CHARSET=utf8;


CREATE TABLE module_hcd_employees (
  users_login varchar(100) NOT NULL,
  wage mediumint(8) unsigned default 0,
  hired_on int(10) default NULL,
  left_on int(10) default NULL,
  address varchar(255) default NULL,
  city varchar(255) default NULL,
  country varchar(255) default NULL,
  father varchar(255) default NULL,
  homephone varchar(255) default NULL,
  mobilephone varchar(255) default NULL,
  sex varchar(255) default NULL,
  birthday varchar(255) default NULL,
  birthplace varchar(255) default NULL,
  birthcountry varchar(255) default NULL,
  mother_tongue varchar(255) default NULL,
  nationality varchar(255) default NULL,
  company_internal_phone varchar(255) default NULL,
  office varchar(255) default NULL,
  doy varchar(255) default NULL,
  police_id_number varchar(255) default NULL,
  driving_licence tinyint(1) default 0,
  work_permission_data varchar(255) default NULL,
  national_service_completed tinyint(1) default 0,
  employement_type varchar(255) default NULL,
  bank varchar(255) default NULL,
  bank_account varchar(255) default NULL,
  marital_status tinyint(1) default 0,
  way_of_working tinyint(1) default 0,  
  afm varchar(255) default NULL,
  candidate tinyint(1) default 0,
  transport tinyint(1) default 0,
  PRIMARY KEY (users_login)
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_events (
  event_ID mediumint(8) unsigned NOT NULL auto_increment,
  event_code mediumint(8) unsigned NOT NULL DEFAULT 0,
  users_login varchar(100) NOT NULL,
  author varchar(100) NOT NULL default '',
  specification text,
  timestamp int(10) NOT NULL,
  PRIMARY KEY (event_ID) 
) DEFAULT CHARSET=utf8;
-- #cpp#endif


-- #cpp#ifndef COMMUNITY
-- #cpp#ifndef STANDARD


--
-- Table 'curriculums'
--
-- id (primary key): A numerical identifier
-- name: The name of the curriculum
-- active: Whether this curriculum is active. Valid values are 0 (not active) and 1 (active)
-- description: A textual description of this entity
CREATE TABLE curriculums (
  id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  active tinyint(1) NOT NULL default '1',
  description text,
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8;

--
-- Table 'curriculums_to_courses'
--
-- id (primary key): A numerical identifier
-- curriculums_ID: The id of the curriculum
-- courses_ID: The id of the course
CREATE TABLE curriculums_to_courses (
  id mediumint(8) unsigned NOT NULL auto_increment,
  curriculums_ID mediumint(8) unsigned NOT NULL,
  courses_ID mediumint(8) unsigned NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(curriculums_ID, courses_ID)
) DEFAULT CHARSET=utf8;

--
-- Table 'curriculums_to_users'
--
-- id (primary key): A numerical identifier
-- curriculums_ID: The id of the curriculum
-- users_LOGIN: The login of the user
CREATE TABLE curriculums_to_users (
  id mediumint(8) unsigned NOT NULL auto_increment,
  curriculums_ID mediumint(8) unsigned NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE(curriculums_ID, users_LOGIN)
) DEFAULT CHARSET=utf8;


CREATE TABLE module_hcd_skills (
  skill_ID mediumint(8) unsigned NOT NULL auto_increment,
  description varchar(255) NOT NULL default '',
  categories_ID mediumint(8) NOT NULL,
  PRIMARY KEY(skill_ID) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_skill_categories (
  id mediumint(8) unsigned NOT NULL auto_increment,
  description varchar(255) NOT NULL default '',
  PRIMARY KEY(id) 
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_employee_has_skill (
  users_login varchar(100) NOT NULL,
  skill_ID mediumint(8) unsigned NOT NULL,
  specification varchar(255) default '',
  author_login varchar(100) NOT NULL,
  score int(11) NOT NULL default '100',
  PRIMARY KEY (users_login, skill_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_lesson_offers_skill (
  lesson_ID mediumint(8) unsigned NOT NULL,
  skill_ID mediumint(8) unsigned NOT NULL,
  specification varchar(255) default NULL, 
  PRIMARY KEY (lesson_ID, skill_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE module_hcd_course_offers_skill (
  courses_ID mediumint(8) unsigned NOT NULL,
  skill_ID mediumint(8) unsigned NOT NULL,
  specification varchar(255) default NULL,
  PRIMARY KEY (courses_ID, skill_ID)
) DEFAULT CHARSET=utf8;


CREATE TABLE users_to_skillgap_tests (
  tests_ID mediumint(8) unsigned NOT NULL default '0',
  users_LOGIN varchar(100) NOT NULL,
  solved tinyint(1) default '0',
  PRIMARY KEY  (users_LOGIN,tests_ID)
) DEFAULT CHARSET=utf8;

CREATE TABLE scorm_data_2004 (
  id int(11) NOT NULL AUTO_INCREMENT,
  content_ID int(11) NOT NULL DEFAULT '0',
  users_LOGIN varchar(100) DEFAULT NULL,
  `timestamp` varchar(255) DEFAULT NULL,
  lesson_location text,
  maxtimeallowed varchar(255) DEFAULT NULL,
  timelimitaction varchar(255) DEFAULT NULL,
  masteryscore varchar(255) DEFAULT NULL,
  datafromlms text,
  entry varchar(255) DEFAULT NULL,
  total_time varchar(255) DEFAULT NULL,
  comments varchar(255) DEFAULT NULL,
  comments_from_lms text,
  success_status varchar(255) DEFAULT NULL,
  score varchar(255) DEFAULT NULL,
  scorm_exit varchar(255) DEFAULT NULL,
  minscore varchar(255) DEFAULT NULL,
  maxscore varchar(255) DEFAULT NULL,
  score_scaled varchar(255) DEFAULT NULL,
  suspend_data text,
  completion_status varchar(255) DEFAULT NULL,
  progress_measure varchar(255) DEFAULT NULL,
  lesson_status varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_activity_progress_information (
  content_ID int(11) NOT NULL,
  users_LOGIN varchar(100) CHARACTER SET utf8 NOT NULL,
  activity_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  activity_attempt_count varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (content_ID,users_LOGIN)
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_activity_state_information (
  lessons_ID int(11) NOT NULL,
  content_ID int(11) NOT NULL,
  users_LOGIN varchar(100) CHARACTER SET utf8 NOT NULL,
  is_suspended varchar(255) CHARACTER SET utf8 DEFAULT '',
  is_active varchar(255) CHARACTER SET utf8 DEFAULT '',
  PRIMARY KEY (lessons_ID,content_ID,users_LOGIN)
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_adlseq_map_info (
  lessons_ID int(11) NOT NULL,
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  objective_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  target_objective_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  read_raw_score varchar(255) CHARACTER SET utf8 NOT NULL,
  read_min_score varchar(255) CHARACTER SET utf8 NOT NULL,
  read_max_score varchar(255) CHARACTER SET utf8 NOT NULL,
  read_completion_status varchar(255) CHARACTER SET utf8 NOT NULL,
  read_progress_measure varchar(255) CHARACTER SET utf8 NOT NULL,
  write_raw_score varchar(255) CHARACTER SET utf8 NOT NULL,
  write_min_score varchar(255) CHARACTER SET utf8 NOT NULL,
  write_max_score varchar(255) CHARACTER SET utf8 NOT NULL,
  write_completion_status varchar(255) CHARACTER SET utf8 NOT NULL,
  write_progress_measure varchar(255) CHARACTER SET utf8 NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_comments_from_learner (
  content_ID int(11) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  `data` text NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_comments_from_lms (
  content_ID int(11) NOT NULL,
  `data` text NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_completion_threshold (
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  min_progress_measure varchar(255) CHARACTER SET utf8 NOT NULL,
  completed_by_measure varchar(255) CHARACTER SET utf8 NOT NULL,
  progress_weight varchar(255) CHARACTER SET utf8 NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_constrained_choice (
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  prevent_activation varchar(255) CHARACTER SET utf8 NOT NULL,
  constrain_choice varchar(255) CHARACTER SET utf8 NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_content_to_organization (
  lessons_ID int(11) NOT NULL,
  content_ID int(11) NOT NULL,
  organization_content_ID varchar(255) NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_control_mode (
  content_ID int(11) NOT NULL,
  choice varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  choice_exit varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  flow varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  forward_only varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  use_current_attempt_objective_info varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  use_current_attempt_progress_info varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (content_ID)
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_delivery_controls (
  content_ID int(11) NOT NULL,
  tracked varchar(255) NOT NULL,
  completion_set_by_content varchar(255) NOT NULL,
  objective_set_by_content varchar(255) NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_global_state_information (
  lessons_ID int(11) NOT NULL,
  organization_content_ID varchar(255) NOT NULL,
  users_LOGIN varchar(100) DEFAULT NULL,
  current_activity varchar(11) DEFAULT '',
  suspended_activity varchar(11) DEFAULT ''
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_hide_lms_ui (
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  options varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT ''
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_interactions (
  content_ID int(11) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  `data` longtext NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_learner_preferences (
  content_ID int(11) NOT NULL,
  users_LOGIN varchar(100) NOT NULL,
  `data` longtext NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_limit_conditions (
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  attempt_limit varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  attempt_absolute_duration_limit varchar(255) CHARACTER SET ucs2 DEFAULT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_maps (
  id int(4) NOT NULL AUTO_INCREMENT,
  content_ID int(4) NOT NULL,
  target_ID varchar(255) DEFAULT NULL,
  read_shared_data varchar(255) DEFAULT NULL,
  write_shared_data varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_maps_info (
  lessons_ID int(4) NOT NULL,
  organization_content_ID varchar(255) NOT NULL,
  target_ID varchar(4000) NOT NULL,
  store mediumtext,
  users_LOGIN varchar(100) NOT NULL DEFAULT ''
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_map_info (
  id int(11) NOT NULL AUTO_INCREMENT,
  content_ID int(4) NOT NULL,
  objective_ID varchar(255) DEFAULT NULL,
  target_objective_ID varchar(255) DEFAULT NULL,
  read_satisfied_status varchar(255) DEFAULT NULL,
  read_normalized_measure varchar(255) DEFAULT NULL,
  write_satisfied_status varchar(255) DEFAULT NULL,
  write_normalized_measure varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_objectives (
  id int(11) NOT NULL AUTO_INCREMENT,
  content_ID int(11) DEFAULT NULL,
  is_primary tinyint(4) DEFAULT NULL,
  satisfied_by_measure varchar(255) DEFAULT NULL,
  objective_ID text,
  min_normalized_measure varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_objective_progress_information (
  content_ID int(4) NOT NULL,
  objective_ID text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  users_LOGIN varchar(100) CHARACTER SET utf8 NOT NULL default '',
  objective_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_satisfied_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_measure_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_normalized_measure varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  raw_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  min_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  max_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  activity_attempt_count varchar(4) NOT NULL DEFAULT '',
  not_attempted varchar(1) DEFAULT '',
  attempt_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_completion_amount_status varchar(255) NOT NULL DEFAULT '',
  attempt_completion_amount varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_completion_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  success_status varchar(255) CHARACTER SET utf8 DEFAULT '',
  attempt varchar(255) NOT NULL DEFAULT '',
  description varchar(255) CHARACTER SET utf8 DEFAULT '',
  reported_completion_status varchar(11) DEFAULT '',
  reported_satisfied_status varchar(11) DEFAULT '',
  reported_progress_measure varchar(11) DEFAULT ''
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_objective_progress_information_all (
  content_ID int(4) NOT NULL,
  objective_ID text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  
  users_LOGIN varchar(100) CHARACTER SET utf8 NOT NULL  default '',
  objective_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_satisfied_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_measure_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_normalized_measure varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  raw_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  min_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  max_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  activity_attempt_count varchar(4) NOT NULL DEFAULT '',
  not_attempted varchar(1) DEFAULT '',
  attempt_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_completion_amount_status varchar(255) NOT NULL DEFAULT '',
  attempt_completion_amount varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_completion_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  success_status varchar(255) CHARACTER SET utf8 DEFAULT '',
  attempt varchar(255) NOT NULL DEFAULT '',
  description varchar(255) CHARACTER SET utf8 DEFAULT '',
  reported_completion_status varchar(11) DEFAULT '',
  reported_satisfied_status varchar(11) DEFAULT '',
  reported_progress_measure varchar(11) DEFAULT ''
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_organizations (
  content_ID int(11) NOT NULL,
  lessons_ID varchar(255) NOT NULL,
  organization_ID varchar(255) NOT NULL,
  structure varchar(255) NOT NULL,
  objectives_global_to_system varchar(255) NOT NULL,
  shared_data_global_to_system varchar(255) NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_rollup_considerations (
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  required_for_satisfied varchar(255) CHARACTER SET utf8 NOT NULL,
  required_for_not_satisfied varchar(255) CHARACTER SET utf8 NOT NULL,
  required_for_completed varchar(255) CHARACTER SET utf8 NOT NULL,
  required_for_incomplete varchar(255) CHARACTER SET utf8 NOT NULL,
  measure_satisfaction_if_active varchar(255) CHARACTER SET utf8 NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_rollup_controls (
  content_ID varchar(255) CHARACTER SET utf8 NOT NULL,
  rollup_objective_satisfied varchar(255) CHARACTER SET utf8 NOT NULL,
  rollup_objective_measure_weight varchar(255) CHARACTER SET utf8 NOT NULL,
  rollup_progress_completion varchar(255) CHARACTER SET utf8 NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_rollup_rule (
  scorm_sequencing_rollup_rules_ID int(11) NOT NULL,
  operator varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  rule_condition varchar(255) CHARACTER SET utf8 NOT NULL
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_rollup_rules (
  id int(11) NOT NULL AUTO_INCREMENT,
  content_ID int(11) DEFAULT NULL,
  child_activity_set varchar(255) DEFAULT NULL,
  minimum_count varchar(255) DEFAULT NULL,
  minimum_percent varchar(255) DEFAULT NULL,
  condition_combination varchar(255) DEFAULT NULL,
  rule_action varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_rule (
  id int(11) NOT NULL AUTO_INCREMENT,
  scorm_sequencing_rules_ID int(11) NOT NULL,
  referenced_objective varchar(255) DEFAULT NULL,
  measure_threshold varchar(255) DEFAULT NULL,
  operator varchar(255) DEFAULT NULL,
  rule_condition varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_rules (
  id int(11) NOT NULL AUTO_INCREMENT,
  content_ID int(11) DEFAULT NULL,
  rule_type tinyint(1) DEFAULT NULL,
  condition_combination varchar(255) DEFAULT NULL,
  rule_action varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_shared_data (
  objective_ID varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  users_LOGIN varchar(50) CHARACTER SET utf8 NOT NULL,
  objective_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_satisfied_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_measure_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_normalized_measure varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  raw_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  min_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  max_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_progress_status varchar(255) NOT NULL DEFAULT '',
  attempt_completion_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_completion_amount_status varchar(255) NOT NULL DEFAULT '',
  attempt_completion_amount varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt varchar(255) NOT NULL DEFAULT '',
  activity_attempt_count varchar(4) NOT NULL DEFAULT '',
  PRIMARY KEY (objective_ID,users_LOGIN)
) DEFAULT CHARSET=utf8;



CREATE TABLE scorm_sequencing_shared_data_not_g (
  objective_ID varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  users_LOGIN varchar(50) CHARACTER SET utf8 NOT NULL,
  objective_progress_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_satisfied_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_measure_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  objective_normalized_measure varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  raw_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  min_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  max_score varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_progress_status varchar(255) NOT NULL DEFAULT '',
  attempt_completion_status varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt_completion_amount_status varchar(255) NOT NULL DEFAULT '',
  attempt_completion_amount varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  attempt varchar(255) NOT NULL DEFAULT '',
  activity_attempt_count varchar(4) NOT NULL DEFAULT '',
  PRIMARY KEY (objective_ID,users_LOGIN)
) DEFAULT CHARSET=utf8;

-- #cpp#endif
-- #cpp#endif