# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- schools
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `schools`;


CREATE TABLE `schools`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'School id',
	`name` VARCHAR(128) COMMENT 'The school name',
	PRIMARY KEY (`id`)
)Type=MyISAM COMMENT='School Table';

#-----------------------------------------------------------------------------
#-- school_info
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `school_info`;


CREATE TABLE `school_info`
(
	`id` INTEGER  NOT NULL COMMENT 'School info id',
	`zip_code` VARCHAR(6) COMMENT 'Post code',
	`region` VARCHAR(128) COMMENT 'School region',
	`city` VARCHAR(128) COMMENT 'School city',
	`address` VARCHAR(255) COMMENT 'School address',
	`phones` VARCHAR(255),
	`fax` VARCHAR(255),
	`email` VARCHAR(255),
	`OKPO_code` VARCHAR(32),
	`OKATO_code` VARCHAR(32),
	`OKOGU_code` VARCHAR(32),
	`head_id` INTEGER COMMENT 'Fkey to school head',
	PRIMARY KEY (`id`),
	INDEX `school_info_FI_1` (`head_id`),
	CONSTRAINT `school_info_FK_1`
		FOREIGN KEY (`head_id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	CONSTRAINT `school_info_FK_2`
		FOREIGN KEY (`id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Advanced information about school';

#-----------------------------------------------------------------------------
#-- users
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `users`;


CREATE TABLE `users`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'User id',
	`display_name` VARCHAR(255),
	`login` VARCHAR(40) COMMENT 'User login',
	`password_hash` VARCHAR(40) COMMENT 'User password hash',
	`school_id` INTEGER COMMENT 'Fkey to school',
	`user_role_key` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `users_FI_1` (`school_id`),
	CONSTRAINT `users_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='User base table';

#-----------------------------------------------------------------------------
#-- user_metadata
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `user_metadata`;


CREATE TABLE `user_metadata`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'User id',
	`first_name` VARCHAR(64)  NOT NULL COMMENT 'First name',
	`middle_name` VARCHAR(64)  NOT NULL COMMENT 'Middle name',
	`last_name` VARCHAR(64)  NOT NULL COMMENT 'Last name',
	`gender` VARCHAR(1)  NOT NULL COMMENT 'enum(\'male\', \'female\')',
	`date_of_birth` DATE COMMENT 'user\'s date of birth',
	`zip_code` VARCHAR(6) COMMENT 'Post code',
	`address` VARCHAR(255),
	`phones` VARCHAR(255),
	`email` VARCHAR(255),
	`passport_number` CHAR(6),
	`passport_serial_number` CHAR(4),
	`passport_emited_by` VARCHAR(64),
	`passport_emited_on` year,
	PRIMARY KEY (`id`),
	CONSTRAINT `user_metadata_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Common user information';

#-----------------------------------------------------------------------------
#-- parents
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `parents`;


CREATE TABLE `parents`
(
	`id` INTEGER  NOT NULL COMMENT 'Parent id',
	`place_of_employment` VARCHAR(255),
	`post` VARCHAR(64),
	`office_phone` VARCHAR(64),
	PRIMARY KEY (`id`),
	CONSTRAINT `parents_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Parents table extends users';

#-----------------------------------------------------------------------------
#-- students
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `students`;


CREATE TABLE `students`
(
	`id` INTEGER  NOT NULL COMMENT 'Student id',
	`birth_certificate` VARCHAR(32),
	`foreign_language_id` INTEGER,
	`case_number` INTEGER,
	PRIMARY KEY (`id`),
	CONSTRAINT `students_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `students_FI_2` (`foreign_language_id`),
	CONSTRAINT `students_FK_2`
		FOREIGN KEY (`foreign_language_id`)
		REFERENCES `foreign_languages` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Students table extends users';

#-----------------------------------------------------------------------------
#-- foreign_languages
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `foreign_languages`;


CREATE TABLE `foreign_languages`
(
	`id` INTEGER  NOT NULL COMMENT 'Language id',
	`language` VARCHAR(32),
	PRIMARY KEY (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- parents_students
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `parents_students`;


CREATE TABLE `parents_students`
(
	`parent_id` INTEGER  NOT NULL,
	`student_id` INTEGER  NOT NULL,
	PRIMARY KEY (`parent_id`,`student_id`),
	CONSTRAINT `parents_students_FK_1`
		FOREIGN KEY (`parent_id`)
		REFERENCES `parents` (`id`),
	INDEX `parents_students_FI_2` (`student_id`),
	CONSTRAINT `parents_students_FK_2`
		FOREIGN KEY (`student_id`)
		REFERENCES `students` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- teachers
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `teachers`;


CREATE TABLE `teachers`
(
	`id` INTEGER  NOT NULL COMMENT 'Teacher id',
	`number` INTEGER  NOT NULL,
	`awards` TEXT,
	PRIMARY KEY (`id`),
	CONSTRAINT `teachers_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Teacher table extends users';

#-----------------------------------------------------------------------------
#-- posts_teachers
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `posts_teachers`;


CREATE TABLE `posts_teachers`
(
	`teacher_id` INTEGER  NOT NULL,
	`post_id` INTEGER  NOT NULL,
	`qualifying_category_id` INTEGER,
	`qualifying_date` DATE,
	`qualifying_expiration_date` DATE,
	PRIMARY KEY (`teacher_id`,`post_id`),
	CONSTRAINT `posts_teachers_FK_1`
		FOREIGN KEY (`teacher_id`)
		REFERENCES `teachers` (`id`),
	INDEX `posts_teachers_FI_2` (`post_id`),
	CONSTRAINT `posts_teachers_FK_2`
		FOREIGN KEY (`post_id`)
		REFERENCES `posts` (`id`),
	INDEX `posts_teachers_FI_3` (`qualifying_category_id`),
	CONSTRAINT `posts_teachers_FK_3`
		FOREIGN KEY (`qualifying_category_id`)
		REFERENCES `qualifying_categories` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- posts
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `posts`;


CREATE TABLE `posts`
(
	`id` INTEGER  NOT NULL COMMENT 'Post id',
	`post` VARCHAR(255),
	PRIMARY KEY (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- qualifying_categories
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `qualifying_categories`;


CREATE TABLE `qualifying_categories`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Category id',
	`category` VARCHAR(64) COMMENT 'Category name',
	PRIMARY KEY (`id`)
)Type=MyISAM COMMENT='Categories';

#-----------------------------------------------------------------------------
#-- educations
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `educations`;


CREATE TABLE `educations`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Education id',
	`institute` VARCHAR(255) COMMENT 'Institute name',
	`education_date` DATE,
	`speciality` VARCHAR(255) COMMENT 'Speciality by diploma',
	`qualification` VARCHAR(255) COMMENT 'Qualification by diploma',
	`education_type_id` INTEGER COMMENT 'Type of education (high, secondary, school, special)',
	`diploma_number` VARCHAR(32),
	PRIMARY KEY (`id`),
	INDEX `educations_FI_1` (`education_type_id`),
	CONSTRAINT `educations_FK_1`
		FOREIGN KEY (`education_type_id`)
		REFERENCES `education_types` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Teacher educations';

#-----------------------------------------------------------------------------
#-- education_types
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `education_types`;


CREATE TABLE `education_types`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Education type id',
	`education_type` VARCHAR(64) COMMENT 'Education type',
	PRIMARY KEY (`id`)
)Type=MyISAM COMMENT='Education types';

#-----------------------------------------------------------------------------
#-- educations_teachers
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `educations_teachers`;


CREATE TABLE `educations_teachers`
(
	`education_id` INTEGER  NOT NULL,
	`teacher_id` INTEGER  NOT NULL,
	PRIMARY KEY (`education_id`,`teacher_id`),
	CONSTRAINT `educations_teachers_FK_1`
		FOREIGN KEY (`education_id`)
		REFERENCES `educations` (`id`),
	INDEX `educations_teachers_FI_2` (`teacher_id`),
	CONSTRAINT `educations_teachers_FK_2`
		FOREIGN KEY (`teacher_id`)
		REFERENCES `teachers` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- extension_courses
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `extension_courses`;


CREATE TABLE `extension_courses`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Course id',
	`name` VARCHAR(255) COMMENT 'Education type',
	`institute` VARCHAR(255) COMMENT 'Where was graded',
	`course_year` year,
	`hours` INTEGER COMMENT 'Course hours count',
	PRIMARY KEY (`id`)
)Type=MyISAM COMMENT='Teacher courses graded';

#-----------------------------------------------------------------------------
#-- extension_courses_teachers
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `extension_courses_teachers`;


CREATE TABLE `extension_courses_teachers`
(
	`course_id` INTEGER  NOT NULL,
	`teacher_id` INTEGER  NOT NULL,
	PRIMARY KEY (`course_id`,`teacher_id`),
	CONSTRAINT `extension_courses_teachers_FK_1`
		FOREIGN KEY (`course_id`)
		REFERENCES `extension_courses` (`id`),
	INDEX `extension_courses_teachers_FI_2` (`teacher_id`),
	CONSTRAINT `extension_courses_teachers_FK_2`
		FOREIGN KEY (`teacher_id`)
		REFERENCES `teachers` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- education_programs
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `education_programs`;


CREATE TABLE `education_programs`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Education program id',
	`name` VARCHAR(64) COMMENT 'Education program name',
	`school_id` INTEGER COMMENT 'Fkey to school',
	PRIMARY KEY (`id`),
	INDEX `education_programs_FI_1` (`school_id`),
	CONSTRAINT `education_programs_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Primary, secondary, high school';

#-----------------------------------------------------------------------------
#-- classes
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `classes`;


CREATE TABLE `classes`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Class id',
	`name` VARCHAR(64) COMMENT 'Class name, e.g. 1A, 1B, 1C, ..., 11A, 11B, 11C',
	`form_master_id` INTEGER,
	`education_program_id` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `classes_FI_1` (`form_master_id`),
	CONSTRAINT `classes_FK_1`
		FOREIGN KEY (`form_master_id`)
		REFERENCES `teachers` (`id`)
		ON DELETE SET NULL,
	INDEX `classes_FI_2` (`education_program_id`),
	CONSTRAINT `classes_FK_2`
		FOREIGN KEY (`education_program_id`)
		REFERENCES `education_programs` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Classes';

#-----------------------------------------------------------------------------
#-- classes_students
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `classes_students`;


CREATE TABLE `classes_students`
(
	`class_id` INTEGER  NOT NULL,
	`student_id` INTEGER  NOT NULL,
	PRIMARY KEY (`class_id`,`student_id`),
	CONSTRAINT `classes_students_FK_1`
		FOREIGN KEY (`class_id`)
		REFERENCES `classes` (`id`),
	INDEX `classes_students_FI_2` (`student_id`),
	CONSTRAINT `classes_students_FK_2`
		FOREIGN KEY (`student_id`)
		REFERENCES `students` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- courses
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `courses`;


CREATE TABLE `courses`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Course id',
	`name` VARCHAR(64) COMMENT 'Course name, e.g. physics, mathematics, history...',
	`short_name` VARCHAR(16) COMMENT 'Course name, e.g. ph, math, hist...',
	`num_of_lessons_per_week` FLOAT,
	`teacher_id` INTEGER,
	`class_id` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `courses_FI_1` (`teacher_id`),
	CONSTRAINT `courses_FK_1`
		FOREIGN KEY (`teacher_id`)
		REFERENCES `teachers` (`id`)
		ON DELETE SET NULL,
	INDEX `courses_FI_2` (`class_id`),
	CONSTRAINT `courses_FK_2`
		FOREIGN KEY (`class_id`)
		REFERENCES `classes` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='courses';

#-----------------------------------------------------------------------------
#-- courses_students
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `courses_students`;


CREATE TABLE `courses_students`
(
	`course_id` INTEGER  NOT NULL,
	`student_id` INTEGER  NOT NULL,
	PRIMARY KEY (`course_id`,`student_id`),
	CONSTRAINT `courses_students_FK_1`
		FOREIGN KEY (`course_id`)
		REFERENCES `courses` (`id`),
	INDEX `courses_students_FI_2` (`student_id`),
	CONSTRAINT `courses_students_FK_2`
		FOREIGN KEY (`student_id`)
		REFERENCES `students` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- courses_rooms
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `courses_rooms`;


CREATE TABLE `courses_rooms`
(
	`course_id` INTEGER  NOT NULL,
	`room_id` INTEGER  NOT NULL,
	PRIMARY KEY (`course_id`,`room_id`),
	CONSTRAINT `courses_rooms_FK_1`
		FOREIGN KEY (`course_id`)
		REFERENCES `courses` (`id`),
	INDEX `courses_rooms_FI_2` (`room_id`),
	CONSTRAINT `courses_rooms_FK_2`
		FOREIGN KEY (`room_id`)
		REFERENCES `rooms` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- rooms
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `rooms`;


CREATE TABLE `rooms`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Room id',
	`name` VARCHAR(64),
	`capacity` INTEGER,
	`school_id` INTEGER COMMENT 'Fkey to school',
	PRIMARY KEY (`id`),
	INDEX `rooms_FI_1` (`school_id`),
	CONSTRAINT `rooms_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Rooms at school';

#-----------------------------------------------------------------------------
#-- days
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `days`;


CREATE TABLE `days`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Day id',
	`name` VARCHAR(16) COMMENT 'e.g. Monday, tuesday, wednesday, thursday, friday',
	`school_id` INTEGER COMMENT 'Fkey to school',
	PRIMARY KEY (`id`),
	INDEX `days_FI_1` (`school_id`),
	CONSTRAINT `days_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Days of week';

#-----------------------------------------------------------------------------
#-- terms
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `terms`;


CREATE TABLE `terms`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Term id',
	`from` TIME COMMENT 'e.g. 08:00',
	`to` TIME COMMENT 'e.g. 08:40',
	`school_id` INTEGER COMMENT 'Fkey to school',
	PRIMARY KEY (`id`),
	INDEX `terms_FI_1` (`school_id`),
	CONSTRAINT `terms_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Lesson times';

#-----------------------------------------------------------------------------
#-- timetable
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `timetable`;


CREATE TABLE `timetable`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Timetable item id',
	`course_id` INTEGER  NOT NULL,
	`day_id` INTEGER  NOT NULL,
	`term_id` INTEGER  NOT NULL,
	`room_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `timetable_FI_1` (`course_id`),
	CONSTRAINT `timetable_FK_1`
		FOREIGN KEY (`course_id`)
		REFERENCES `courses` (`id`)
		ON DELETE CASCADE,
	INDEX `timetable_FI_2` (`day_id`),
	CONSTRAINT `timetable_FK_2`
		FOREIGN KEY (`day_id`)
		REFERENCES `days` (`id`)
		ON DELETE CASCADE,
	INDEX `timetable_FI_3` (`term_id`),
	CONSTRAINT `timetable_FK_3`
		FOREIGN KEY (`term_id`)
		REFERENCES `terms` (`id`)
		ON DELETE CASCADE,
	INDEX `timetable_FI_4` (`room_id`),
	CONSTRAINT `timetable_FK_4`
		FOREIGN KEY (`room_id`)
		REFERENCES `rooms` (`id`)
		ON DELETE CASCADE
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- lessons
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `lessons`;


CREATE TABLE `lessons`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Lesson id',
	`title` VARCHAR(255)  NOT NULL,
	`course_id` INTEGER  NOT NULL,
	`from` DATETIME  NOT NULL COMMENT 'e.g. 2008-10-27 08:00:00',
	`length` INTEGER  NOT NULL COMMENT 'e.g. 40 (min)',
	`room_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `lessons_FI_1` (`course_id`),
	CONSTRAINT `lessons_FK_1`
		FOREIGN KEY (`course_id`)
		REFERENCES `courses` (`id`)
		ON DELETE CASCADE,
	INDEX `lessons_FI_2` (`room_id`),
	CONSTRAINT `lessons_FK_2`
		FOREIGN KEY (`room_id`)
		REFERENCES `rooms` (`id`)
		ON DELETE CASCADE
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- marks
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `marks`;


CREATE TABLE `marks`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Mark id',
	`mark` INTEGER  NOT NULL COMMENT '1, 2, 3, 4, 5',
	`scale` INTEGER  NOT NULL COMMENT '1, 5, 10, 100',
	`lesson_id` INTEGER  NOT NULL,
	`student_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `marks_FI_1` (`lesson_id`),
	CONSTRAINT `marks_FK_1`
		FOREIGN KEY (`lesson_id`)
		REFERENCES `lessons` (`id`)
		ON DELETE CASCADE,
	INDEX `marks_FI_2` (`student_id`),
	CONSTRAINT `marks_FK_2`
		FOREIGN KEY (`student_id`)
		REFERENCES `students` (`id`)
		ON DELETE CASCADE
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- library_toc
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `library_toc`;


CREATE TABLE `library_toc`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Toc entry id',
	`lft` INTEGER default 0 NOT NULL,
	`rgt` INTEGER default 0 NOT NULL,
	`scope` INTEGER default 0 NOT NULL,
	`name` VARCHAR(255)  NOT NULL COMMENT 'e.g. Literature, chemistry...',
	`UDK` VARCHAR(32),
	`BBK` VARCHAR(32),
	`school_id` INTEGER COMMENT 'Fkey to school',
	PRIMARY KEY (`id`),
	KEY `lft`(`lft`),
	KEY `rgt`(`rgt`),
	KEY `scope`(`scope`),
	INDEX `library_toc_FI_1` (`school_id`),
	CONSTRAINT `library_toc_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- library_items
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `library_items`;


CREATE TABLE `library_items`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT COMMENT 'Item id',
	`title` VARCHAR(255)  NOT NULL,
	`author` VARCHAR(255),
	`author_type` VARCHAR(255)  NOT NULL COMMENT 'enum(\'author\',\'drawer\',\'collective\',\'editor\',\'other\',\'none\')',
	`description` TEXT,
	`ISBN` VARCHAR(32),
	`UDK` VARCHAR(32),
	`BBK` VARCHAR(32),
	`copies_count` INTEGER  NOT NULL,
	`readers_count` INTEGER,
	`school_id` INTEGER COMMENT 'Fkey to school',
	`item_type_key` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `library_items_FI_1` (`school_id`),
	CONSTRAINT `library_items_FK_1`
		FOREIGN KEY (`school_id`)
		REFERENCES `schools` (`id`)
		ON DELETE CASCADE
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- articles
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `articles`;


CREATE TABLE `articles`
(
	`id` INTEGER  NOT NULL COMMENT 'Article id',
	`first_page` INTEGER  NOT NULL,
	`last_page` INTEGER  NOT NULL,
	`magazine_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`,`magazine_id`),
	INDEX `articles_FI_1` (`magazine_id`),
	CONSTRAINT `articles_FK_1`
		FOREIGN KEY (`magazine_id`)
		REFERENCES `magazines` (`id`)
		ON DELETE CASCADE,
	CONSTRAINT `articles_FK_2`
		FOREIGN KEY (`id`)
		REFERENCES `library_items` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Articles table extends LibraryItems';

#-----------------------------------------------------------------------------
#-- magazines
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `magazines`;


CREATE TABLE `magazines`
(
	`id` INTEGER  NOT NULL COMMENT 'Magazine id',
	`publisher` VARCHAR(255),
	`magazine_number` INTEGER  NOT NULL,
	`magazine_year` year  NOT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `magazines_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `library_items` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Magazines table extends LibraryItems';

#-----------------------------------------------------------------------------
#-- books
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `books`;


CREATE TABLE `books`
(
	`id` INTEGER  NOT NULL COMMENT 'Book id',
	`publisher` VARCHAR(255),
	`year_published` year  NOT NULL,
	`price` FLOAT,
	PRIMARY KEY (`id`),
	CONSTRAINT `books_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `library_items` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Books table extends LibraryItems';

#-----------------------------------------------------------------------------
#-- inventory_nums
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `inventory_nums`;


CREATE TABLE `inventory_nums`
(
	`inventory_number` INTEGER  NOT NULL,
	`book_id` INTEGER  NOT NULL,
	PRIMARY KEY (`inventory_number`),
	INDEX `inventory_nums_FI_1` (`book_id`),
	CONSTRAINT `inventory_nums_FK_1`
		FOREIGN KEY (`book_id`)
		REFERENCES `books` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Inventory numbers for books';

#-----------------------------------------------------------------------------
#-- book_toc
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `book_toc`;


CREATE TABLE `book_toc`
(
	`id` INTEGER  NOT NULL COMMENT 'Book toc entry id',
	`book_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`,`book_id`),
	INDEX `book_toc_FI_1` (`book_id`),
	CONSTRAINT `book_toc_FK_1`
		FOREIGN KEY (`book_id`)
		REFERENCES `books` (`id`)
		ON DELETE CASCADE,
	CONSTRAINT `book_toc_FK_2`
		FOREIGN KEY (`id`)
		REFERENCES `library_items` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='BookToc table extends LibraryItems. Table of contents for books';

#-----------------------------------------------------------------------------
#-- documents
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `documents`;


CREATE TABLE `documents`
(
	`id` INTEGER  NOT NULL COMMENT 'Document id',
	`publisher` VARCHAR(255),
	PRIMARY KEY (`id`),
	CONSTRAINT `documents_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `library_items` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Documents table extends LibraryItems';

#-----------------------------------------------------------------------------
#-- urls
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `urls`;


CREATE TABLE `urls`
(
	`id` INTEGER  NOT NULL COMMENT 'Url id',
	`url` VARCHAR(255),
	PRIMARY KEY (`id`),
	CONSTRAINT `urls_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `library_items` (`id`)
		ON DELETE CASCADE
)Type=MyISAM COMMENT='Urls table extends LibraryItems';

#-----------------------------------------------------------------------------
#-- library_items_toc
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `library_items_toc`;


CREATE TABLE `library_items_toc`
(
	`toc_id` INTEGER  NOT NULL,
	`item_id` INTEGER  NOT NULL,
	PRIMARY KEY (`toc_id`,`item_id`),
	INDEX `library_items_toc_FI_1` (`item_id`),
	CONSTRAINT `library_items_toc_FK_1`
		FOREIGN KEY (`item_id`)
		REFERENCES `library_items` (`id`),
	CONSTRAINT `library_items_toc_FK_2`
		FOREIGN KEY (`toc_id`)
		REFERENCES `library_toc` (`id`)
)Type=MyISAM;

#-----------------------------------------------------------------------------
#-- library_items_users
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `library_items_users`;


CREATE TABLE `library_items_users`
(
	`user_id` INTEGER  NOT NULL,
	`item_id` INTEGER  NOT NULL,
	PRIMARY KEY (`user_id`,`item_id`),
	INDEX `library_items_users_FI_1` (`item_id`),
	CONSTRAINT `library_items_users_FK_1`
		FOREIGN KEY (`item_id`)
		REFERENCES `library_items` (`id`),
	CONSTRAINT `library_items_users_FK_2`
		FOREIGN KEY (`user_id`)
		REFERENCES `users` (`id`)
)Type=MyISAM COMMENT='Readers';

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;