CREATE TABLE b_learn_exceptions_log (
  DATE_REGISTERED timestamp NOT NULL,
  CODE int NOT NULL,
  MESSAGE text NOT NULL,
  FFILE text NOT NULL,
  LINE int NOT NULL,
  BACKTRACE text NOT NULL
);

CREATE TABLE b_learn_publish_prohibition (
  COURSE_LESSON_ID int8 NOT NULL,
  PROHIBITED_LESSON_ID int8 NOT NULL,
  PRIMARY KEY (COURSE_LESSON_ID, PROHIBITED_LESSON_ID)
);

CREATE TABLE b_learn_rights (
  LESSON_ID int8 NOT NULL,
  SUBJECT_ID varchar(100) NOT NULL,
  TASK_ID int NOT NULL,
  PRIMARY KEY (LESSON_ID, SUBJECT_ID)
);

CREATE TABLE b_learn_rights_all (
  SUBJECT_ID varchar(100) NOT NULL,
  TASK_ID int NOT NULL,
  PRIMARY KEY (SUBJECT_ID)
);

CREATE TABLE b_learn_lesson_edges (
  SOURCE_NODE int NOT NULL,
  TARGET_NODE int NOT NULL,
  SORT int NOT NULL DEFAULT '500',
  PRIMARY KEY (SOURCE_NODE, TARGET_NODE)
);
CREATE INDEX ix_b_learn_lesson_edges_target_node ON b_learn_lesson_edges (target_node);

CREATE TABLE b_learn_course (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  CODE varchar(50),
  NAME varchar(255) NOT NULL DEFAULT 'name',
  SORT int NOT NULL DEFAULT '500',
  PREVIEW_PICTURE int,
  PREVIEW_TEXT text,
  PREVIEW_TEXT_TYPE char(4) NOT NULL DEFAULT 'text',
  DESCRIPTION text,
  DESCRIPTION_TYPE char(4) NOT NULL DEFAULT 'text',
  ACTIVE_FROM timestamp,
  ACTIVE_TO timestamp,
  RATING char(1),
  RATING_TYPE varchar(50),
  SCORM char(1) NOT NULL DEFAULT 'N',
  LINKED_LESSON_ID int DEFAULT NULL,
  JOURNAL_STATUS int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_course_linked_lesson_id ON b_learn_course (linked_lesson_id);

CREATE TABLE b_learn_course_site (
  COURSE_ID int8 NOT NULL,
  SITE_ID char(2) NOT NULL,
  PRIMARY KEY (COURSE_ID, SITE_ID)
);

CREATE TABLE b_learn_chapter (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  COURSE_ID int8 NOT NULL,
  CHAPTER_ID int,
  NAME varchar(255) NOT NULL,
  CODE varchar(50),
  SORT int NOT NULL DEFAULT '500',
  PREVIEW_PICTURE int,
  PREVIEW_TEXT text,
  PREVIEW_TEXT_TYPE char(4) NOT NULL DEFAULT 'text',
  DETAIL_PICTURE int,
  DETAIL_TEXT text,
  DETAIL_TEXT_TYPE char(4) NOT NULL DEFAULT 'text',
  JOURNAL_STATUS int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
);

CREATE TABLE b_learn_lesson (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  DATE_CREATE timestamp,
  CREATED_BY int,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  COURSE_ID int8 NOT NULL DEFAULT '0',
  CHAPTER_ID int8,
  NAME varchar(255) NOT NULL DEFAULT 'name',
  SORT int NOT NULL DEFAULT '500',
  PREVIEW_PICTURE int,
  KEYWORDS text,
  PREVIEW_TEXT text,
  PREVIEW_TEXT_TYPE char(4) NOT NULL DEFAULT 'text',
  DETAIL_PICTURE int,
  DETAIL_TEXT text,
  DETAIL_TEXT_TYPE char(4) NOT NULL DEFAULT 'text',
  LAUNCH text,
  CODE varchar(50) DEFAULT NULL,
  WAS_CHAPTER_ID int DEFAULT NULL,
  WAS_PARENT_CHAPTER_ID int DEFAULT NULL,
  WAS_PARENT_COURSE_ID int DEFAULT NULL,
  WAS_COURSE_ID int DEFAULT NULL,
  JOURNAL_STATUS int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
);

CREATE TABLE b_learn_question (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  LESSON_ID int8 NOT NULL,
  QUESTION_TYPE char(1) NOT NULL DEFAULT 'S',
  NAME varchar(255) NOT NULL,
  SORT int NOT NULL DEFAULT '500',
  DESCRIPTION text,
  DESCRIPTION_TYPE char(4) NOT NULL DEFAULT 'text',
  COMMENT_TEXT text,
  FILE_ID int,
  SELF char(1) NOT NULL DEFAULT 'N',
  POINT int NOT NULL DEFAULT '10',
  DIRECTION char(1) NOT NULL DEFAULT 'V',
  CORRECT_REQUIRED char(1) NOT NULL DEFAULT 'N',
  EMAIL_ANSWER char(1) NOT NULL DEFAULT 'N',
  INCORRECT_MESSAGE text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_question_lesson_id ON b_learn_question (lesson_id);

CREATE TABLE b_learn_answer (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  QUESTION_ID int8 NOT NULL,
  SORT int NOT NULL DEFAULT '10',
  ANSWER text NOT NULL,
  CORRECT char(1) NOT NULL,
  FEEDBACK text,
  MATCH_ANSWER text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_answer_question_id ON b_learn_answer (question_id);

CREATE TABLE b_learn_test (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  COURSE_ID int8 NOT NULL,
  TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  SORT int NOT NULL DEFAULT '500',
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  NAME varchar(255) NOT NULL,
  DESCRIPTION text,
  DESCRIPTION_TYPE char(4) NOT NULL DEFAULT 'text',
  ATTEMPT_LIMIT int NOT NULL DEFAULT '0',
  TIME_LIMIT int DEFAULT '0',
  COMPLETED_SCORE int,
  QUESTIONS_FROM char(1) NOT NULL DEFAULT 'A',
  QUESTIONS_FROM_ID int NOT NULL DEFAULT '0',
  QUESTIONS_AMOUNT int NOT NULL DEFAULT '0',
  RANDOM_QUESTIONS char(1) NOT NULL DEFAULT 'Y',
  RANDOM_ANSWERS char(1) NOT NULL DEFAULT 'Y',
  APPROVED char(1) NOT NULL DEFAULT 'Y',
  INCLUDE_SELF_TEST char(1) NOT NULL DEFAULT 'N',
  PASSAGE_TYPE char(1) NOT NULL DEFAULT '0',
  PREVIOUS_TEST_ID int,
  PREVIOUS_TEST_SCORE int DEFAULT 0,
  INCORRECT_CONTROL char(1) NOT NULL DEFAULT 'N',
  CURRENT_INDICATION int NOT NULL DEFAULT '0',
  FINAL_INDICATION int NOT NULL DEFAULT '0',
  MIN_TIME_BETWEEN_ATTEMPTS int NOT NULL DEFAULT '0',
  SHOW_ERRORS char(1) NOT NULL DEFAULT 'N',
  NEXT_QUESTION_ON_ERROR char(1) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_test_course_id ON b_learn_test (course_id);
CREATE INDEX ix_b_learn_test_previous_test_id ON b_learn_test (previous_test_id);

CREATE TABLE b_learn_attempt (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TEST_ID int NOT NULL,
  STUDENT_ID int NOT NULL,
  DATE_START timestamp NOT NULL,
  DATE_END timestamp,
  STATUS char(1) NOT NULL DEFAULT 'B',
  COMPLETED char(1) NOT NULL DEFAULT 'N',
  SCORE int DEFAULT 0,
  MAX_SCORE int DEFAULT 0,
  QUESTIONS int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_attempt_student_id_test_id ON b_learn_attempt (student_id, test_id);

CREATE TABLE b_learn_test_result (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ATTEMPT_ID int8 NOT NULL,
  QUESTION_ID int8 NOT NULL,
  RESPONSE text,
  POINT int NOT NULL DEFAULT 0,
  CORRECT char(1) NOT NULL DEFAULT 'N',
  ANSWERED char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_test_result_attempt_id_question_id ON b_learn_test_result (attempt_id, question_id);
CREATE INDEX ix_b_learn_test_result_question_id_answered_correct ON b_learn_test_result (question_id, answered, correct);

CREATE TABLE b_learn_gradebook (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  STUDENT_ID int NOT NULL,
  TEST_ID int NOT NULL,
  RESULT int,
  MAX_RESULT int,
  ATTEMPTS int NOT NULL DEFAULT '1',
  COMPLETED char(1) NOT NULL DEFAULT 'N',
  EXTRA_ATTEMPTS int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_learn_gradebook_student_id_test_id ON b_learn_gradebook (student_id, test_id);

CREATE TABLE b_learn_student (
  USER_ID int NOT NULL,
  TRANSCRIPT int NOT NULL,
  PUBLIC_PROFILE char(1) NOT NULL DEFAULT 'N',
  RESUME text,
  PRIMARY KEY (USER_ID)
);

CREATE TABLE b_learn_certification (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  STUDENT_ID int NOT NULL,
  COURSE_ID int8 NOT NULL,
  TIMESTAMP_X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  DATE_CREATE timestamp,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  SORT int NOT NULL DEFAULT '500',
  FROM_ONLINE char(1) NOT NULL DEFAULT 'Y',
  PUBLIC_PROFILE char(1) NOT NULL DEFAULT 'Y',
  SUMMARY int NOT NULL DEFAULT '0',
  MAX_SUMMARY int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_certification_student_id_course_id ON b_learn_certification (student_id, course_id);

CREATE TABLE b_learn_site_path (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  SITE_ID char(2) NOT NULL,
  PATH varchar(255) NOT NULL,
  TYPE char(1) DEFAULT NULL,
  PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX ux_b_learn_site_path_site_id_type ON b_learn_site_path (site_id, type);

CREATE TABLE b_learn_test_mark (
  ID int GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  TEST_ID int NOT NULL,
  SCORE int NOT NULL,
  MARK varchar(50) NOT NULL,
  DESCRIPTION text,
  PRIMARY KEY (ID)
);
CREATE INDEX ix_b_learn_test_mark_test_id ON b_learn_test_mark (test_id);

CREATE TABLE b_learn_groups (
  ID int8 GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACTIVE char(1) NOT NULL DEFAULT 'Y',
  TITLE varchar(255) NOT NULL DEFAULT ' ',
  CODE varchar(50),
  SORT int NOT NULL DEFAULT '500',
  ACTIVE_FROM timestamp,
  ACTIVE_TO timestamp,
  COURSE_LESSON_ID int NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE b_learn_groups_member (
  LEARNING_GROUP_ID int NOT NULL DEFAULT '0',
  USER_ID int NOT NULL DEFAULT '0',
  PRIMARY KEY (LEARNING_GROUP_ID, USER_ID)
);
CREATE INDEX ix_b_learn_groups_member_user_id ON b_learn_groups_member (user_id);

CREATE TABLE b_learn_groups_lesson (
  LEARNING_GROUP_ID int NOT NULL DEFAULT '0',
  LESSON_ID int NOT NULL DEFAULT '0',
  DELAY int NOT NULL DEFAULT '0',
  PRIMARY KEY (LEARNING_GROUP_ID, LESSON_ID)
);
CREATE INDEX ix_b_learn_groups_lesson_lesson_id ON b_learn_groups_lesson (lesson_id);