/*
 * db_tables.php - SQL script to generate all the TODCM tables
 *
 * @author $Author: dtong $
 * @version $Id: db_init.sql,v 1.12 2011/06/07 08:46:58 dtong Exp $
 * @copyright Copyright (c) 2011, Tiller Software Co., Ltd.
*/

set foreign_key_checks=0;
drop table IF EXISTS btnsort;
drop table IF EXISTS category_department;
drop table IF EXISTS category;
drop table IF EXISTS app_config;
drop table IF EXISTS unit_search_cache2;
drop table IF EXISTS memory_table_config;
drop table IF EXISTS unit_search_cache1;
drop table IF EXISTS template_date;
drop table IF EXISTS survey_extra_data;
drop table IF EXISTS template_htmldata;
drop table IF EXISTS survey_course;
drop table IF EXISTS survey_goal;
drop table IF EXISTS survey_result;
drop table IF EXISTS survey_template;
drop table IF EXISTS survey;
drop table IF EXISTS resource_url;
drop table IF EXISTS school_type;
drop table IF EXISTS grade_type;
drop table IF EXISTS team_members;
drop table IF EXISTS entry_point;
drop table IF EXISTS linkunit;
drop table IF EXISTS goal;
drop table IF EXISTS course_lt_link;
drop table IF EXISTS target_text_hist;
drop table IF EXISTS res_sort;
drop table IF EXISTS resource_select;
drop table IF EXISTS user_role;
drop table IF EXISTS resource_link;
drop table IF EXISTS upload;
drop table IF EXISTS benchmark;
drop table IF EXISTS standard;
drop table IF EXISTS display_grade;
drop table IF EXISTS unit;
drop table IF EXISTS user_course;
drop table IF EXISTS resource;
drop table IF EXISTS course_grade;
drop table IF EXISTS unit_target;
drop table IF EXISTS unit_selection;
drop table IF EXISTS course_description;
drop table IF EXISTS selection_category;
drop table IF EXISTS selection_text;
drop table IF EXISTS grade;
drop table IF EXISTS target_text;
drop table IF EXISTS strand;
drop table IF EXISTS course_sis;
drop table IF EXISTS user;
drop table IF EXISTS school;
drop table IF EXISTS access;
drop table IF EXISTS auth;
drop table IF EXISTS department;
drop table IF EXISTS config;
set foreign_key_checks=1;


Create table config (
	id Int UNSIGNED NOT NULL,
	system_version Varchar(30),
 Primary Key (id)) ENGINE = MyISAM;

Create table department (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	enabled Tinyint DEFAULT 1,
	shortname Varchar(30),
	name Varchar(2000) NOT NULL,
	description Text,
 Primary Key (id)) ENGINE = MyISAM;

Create table auth (
	id Int UNSIGNED NOT NULL,
	type Varchar(30) NOT NULL,
 Primary Key (id)) ENGINE = MyISAM;

Create table access (
	id Int UNSIGNED NOT NULL,
	name Varchar(30),
 Primary Key (id)) ENGINE = MyISAM;

Create table school (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	shortname Varchar(20),
	name Varchar(1000),
 Primary Key (id)) ENGINE = MyISAM;

Create table user (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	auth_id Int UNSIGNED NOT NULL,
	department_id Int UNSIGNED,
	accessid Int UNSIGNED NOT NULL,
	username Varchar(50) NOT NULL,
	enabled Bool DEFAULT 1,
	autogen Bool DEFAULT 1,
	lastlogin Datetime,
	lastfailedlogin Datetime,
	failedlogincount Int DEFAULT 0,
	hide_unit_private_resource Tinyint UNSIGNED NOT NULL DEFAULT 1,
	hide_unit_admin_note Tinyint UNSIGNED NOT NULL DEFAULT 0,
	password Varchar(70),
	email Varchar(200),
	sis_teacher_number Varchar(32),
	firstname Varchar(150),
	middlename Varchar(150),
	lastname Varchar(150),
	displayname Varchar(500),
	title Varchar(1000),
	successips Varchar(500),
	failedips Varchar(500),
	UNIQUE (id),
	UNIQUE (username),
 Primary Key (id)) ENGINE = MyISAM;

Create table course_sis (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	mingradeid Int,
	maxgradeid Int,
	departmentid Int UNSIGNED NOT NULL,
	# version Int DEFAULT 1,
	hidden Tinyint DEFAULT 0,
	enabled Bool DEFAULT 1,
	autogen Bool DEFAULT 1,
	tree_sort Int DEFAULT 100,
	createdate Datetime NOT NULL,
	modifydate Datetime,
	sis_course_number Varchar(20),
	sis_course_dbid Varchar(20),
	shortname Varchar(30),
	name Varchar(3000),
	url_key Varchar(20),
	department_display Varchar(150),
	description Text,
 Primary Key (id)) ENGINE = MyISAM;

Create table strand (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	schoolid Int UNSIGNED,
	departmentid Int UNSIGNED,
	type Int,
	enabled Tinyint DEFAULT 1,
	importid Varchar(20),
	importid_int Bigint UNSIGNED,
	data Text,
 Primary Key (id)) ENGINE = MyISAM;

Create table target_text (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	benchmarkid Int UNSIGNED NOT NULL,
	courseid Int UNSIGNED,
	unitid Int UNSIGNED,
	type Int,
	ranking Tinyint UNSIGNED,
	select1 Int UNSIGNED,
	select2 Int UNSIGNED,
	select3 Int,
	importid Varchar(20),
	data Text,
 Primary Key (id)) ENGINE = InnoDB;

Create table grade (
	id Int NOT NULL AUTO_INCREMENT,
	schoolid Int UNSIGNED NOT NULL,
	name Varchar(300),
	sort Int,
 Primary Key (id)) ENGINE = MyISAM;

Create table selection_text (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	departmentid Int UNSIGNED NOT NULL,
	categoryid Int UNSIGNED,
	type Int UNSIGNED NOT NULL,
	text1 Text,
 Primary Key (id)) ENGINE = MyISAM;

Create table selection_category (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	departmentid Int UNSIGNED,
	type Int UNSIGNED NOT NULL,
	sort Int DEFAULT 1,
	enabled Tinyint DEFAULT 1,
	text Text,
	# pagetitle Varchar(500),
 Primary Key (id)) ENGINE = MyISAM;

Create table course_description (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	courseid Int UNSIGNED NOT NULL,
	lasteditorid Int UNSIGNED NOT NULL,
	creatorid Int UNSIGNED NOT NULL,
	createdate Datetime,
	modifydate Datetime,
	isedit Bool,
	prerequisites Text,
	coursedescription Text,
	resourcelist Text,
	summerreadings Text,
	# teachername Text,
	# subjectarea Varchar(200),
 Primary Key (id)) ENGINE = MyISAM;

Create table unit_selection (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	unitid Int UNSIGNED NOT NULL,
	catid Int UNSIGNED,
	textid Int UNSIGNED,
	type Int NOT NULL,
	select1 Int UNSIGNED,
	select2 Int UNSIGNED,
	select3 Int UNSIGNED,
	select4 Int UNSIGNED,
	select5 Int UNSIGNED,
 Primary Key (id)) ENGINE = InnoDB;

Create table unit_target (
	unitid Int UNSIGNED NOT NULL,
	targetid Int UNSIGNED NOT NULL,
	benchmarkid Int UNSIGNED,
	target_type Int NOT NULL,
	select1 Int,
	select2 Int,
	select3 Int,
	data Text,
 Primary Key (unitid,targetid)) ENGINE = InnoDB;

Create table course_grade (
	courseid Int UNSIGNED NOT NULL,
	gradeid Int NOT NULL,
 Primary Key (courseid,gradeid)) ENGINE = MyISAM;

Create table resource (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	origid Int UNSIGNED,
	courseid Int UNSIGNED NOT NULL,
	unitid Int UNSIGNED NOT NULL,
	creatorid Int UNSIGNED NOT NULL,
	modifierid Int UNSIGNED,
	createdate Datetime,
	modifydate Datetime,
	type Int UNSIGNED NOT NULL,
	ispublic Bool DEFAULT 1,
	date1 Datetime,
	text1 Text,
 Primary Key (id)) ENGINE = InnoDB;

Create table user_course (
	userid Int UNSIGNED NOT NULL,
	courseid Int UNSIGNED NOT NULL,
	iseditor Bool DEFAULT 0,
	is_from_sis Bool DEFAULT 0,
 Primary Key (userid,courseid)) ENGINE = MyISAM;

Create table unit (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	origunitid Int UNSIGNED,
	editunitid Int UNSIGNED,
	currentunitid Int UNSIGNED,
	groupid Int UNSIGNED,
	parentid Int UNSIGNED,
	course_intid Int UNSIGNED,
	editorid Int UNSIGNED,
	lasteditorid Int UNSIGNED NOT NULL,
	creatorid Int UNSIGNED NOT NULL,
	courseid Int UNSIGNED NOT NULL,
	type Int UNSIGNED NOT NULL,
	status Tinyint UNSIGNED NOT NULL,
	hidden Tinyint DEFAULT 0,
	startdate Datetime,
	enddate Datetime,
	createdate Datetime,
	editdate Datetime,
	modifydate Datetime,
	archivedate Datetime,
	edit_accessdate Datetime,
	version Int,
	template Varchar(32) NOT NULL DEFAULT 'ubd',
	title Varchar(2000) NOT NULL,
	description Text,
 Primary Key (id)) ENGINE = InnoDB;

Create table display_grade (
	gradeid Int NOT NULL,
	courseid Int UNSIGNED NOT NULL,
 Primary Key (gradeid,courseid)) ENGINE = MyISAM;

Create table standard (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	strandid Int UNSIGNED NOT NULL,
	type Int,
	enabled Tinyint DEFAULT 1,
	importid Varchar(20),
	importid_int Bigint UNSIGNED,
	data Text,
 Primary Key (id)) ENGINE = MyISAM;

Create table benchmark (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	courseid Int UNSIGNED,
	mingradeid Int,
	maxgradeid Int,
	standardid Int UNSIGNED NOT NULL,
	type Int,
	enabled Tinyint DEFAULT 1,
	importid Varchar(20),
	importid_int Bigint UNSIGNED,
	data Text,
 Primary Key (id)) ENGINE = MyISAM;

Create table upload (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	resourceid Int UNSIGNED NOT NULL,
	size Int UNSIGNED,
	filename Varchar(150) NOT NULL,
 Primary Key (id)) ENGINE = InnoDB;

Create table resource_link (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	resourceid Int UNSIGNED NOT NULL,
	pid1 Int UNSIGNED NOT NULL,
	pid2 Int UNSIGNED,
	type Int UNSIGNED NOT NULL,
 Primary Key (id)) ENGINE = InnoDB;

Create table user_role (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	userid Int UNSIGNED NOT NULL,
	departmentid Int UNSIGNED,
	schoolid Int UNSIGNED,
	type Int UNSIGNED NOT NULL,
	grouping Varchar(30),
 Primary Key (id)) ENGINE = MyISAM;

Create table resource_select (
	resourceid Int UNSIGNED NOT NULL,
	type Int NOT NULL,
	choice Int NOT NULL,
 Primary Key (resourceid,type,choice)) ENGINE = InnoDB;

Create table res_sort (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	fk1 Int UNSIGNED,
	fk2 Int UNSIGNED,
	fk3 Int UNSIGNED,
	type Int UNSIGNED,
	sortkey Int,
 Primary Key (id)) ENGINE = MyISAM;

Create table target_text_hist (
	id Int UNSIGNED NOT NULL,
	benchmarkid Int UNSIGNED NOT NULL,
	courseid Int UNSIGNED,
	unitid Int UNSIGNED,
	type Int,
	ranking Tinyint UNSIGNED,
	select1 Int UNSIGNED,
	select2 Int UNSIGNED,
	select3 Int,
	importid Varchar(20),
	data Text,
	hist_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	hist_date Datetime NOT NULL,
	hist_type Tinyint UNSIGNED NOT NULL,
	hist_mod_select1 Int UNSIGNED,
	hist_mod_select2 Int UNSIGNED,
	hist_mod_select3 Int UNSIGNED,
	hist_mod_data Text,
 Primary Key (hist_id)) ENGINE = MyISAM;

Create table course_lt_link (
	id Int NOT NULL AUTO_INCREMENT,
	courseid Int UNSIGNED NOT NULL,
	lt_courseid Int UNSIGNED,
	lt_departmentid Int UNSIGNED,
 Primary Key (id)) ENGINE = MyISAM;

Create table goal (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	creatorid Int UNSIGNED NOT NULL,
	createdate Datetime,
	modifydate Datetime,
	enabled Tinyint DEFAULT 1,
	title Varchar(1024),
 Primary Key (id)) ENGINE = InnoDB;

Create table linkunit (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	unitid Int UNSIGNED NOT NULL,
	extid Int UNSIGNED NOT NULL,
	type Int UNSIGNED NOT NULL,
 Primary Key (id)) ENGINE = InnoDB;

Create table entry_point (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	creatorid Int UNSIGNED NOT NULL,
	goalid Int UNSIGNED NOT NULL,
	type Int NOT NULL,
	createdate Datetime,
	modifydate Datetime,
	completedate Datetime,
	title Varchar(1024),
 Primary Key (id)) ENGINE = InnoDB;

Create table team_members (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	userid Int UNSIGNED NOT NULL,
	goalid Int UNSIGNED NOT NULL,
	iseditor Tinyint DEFAULT 0,
 Primary Key (id)) ENGINE = InnoDB;

Create table grade_type (
	id Int UNSIGNED NOT NULL,
	schoolid Int UNSIGNED NOT NULL,
	type Int,
	sort Int,
	name Varchar(300),
 Primary Key (id)) ENGINE = MyISAM;

Create table school_type (
	id Int UNSIGNED NOT NULL,
	type Int UNSIGNED,
	shortname Varchar(20),
	name Varchar(1000),
 Primary Key (id)) ENGINE = MyISAM;

Create table resource_url (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	resourceid Int UNSIGNED NOT NULL,
	url Varchar(500),
 Primary Key (id)) ENGINE = InnoDB;

Create table survey (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	creatorid Int UNSIGNED NOT NULL,
	ispublic Bool DEFAULT 1,
	islogin Bool DEFAULT 1,
	disabled Bool DEFAULT 0,
	createdate Datetime,
	modifydate Datetime,
	startdate Datetime,
	enddate Datetime,
	parity Varchar(5) NOT NULL,
	title Varchar(1000),
 Primary Key (id)) ENGINE = InnoDB;

Create table survey_template (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	surveyid Int UNSIGNED NOT NULL,
	type Int NOT NULL,
	template Varchar(30) NOT NULL,
	title Varchar(1000),
 Primary Key (id)) ENGINE = InnoDB;

Create table survey_result (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	templateid Int UNSIGNED NOT NULL,
	userid Int UNSIGNED,
	createdate Datetime,
	modifydate Datetime,
	completed Bool DEFAULT 0,
	ext_username Varchar(50),
	ext_firstname Varchar(150),
	ext_lastname Varchar(150),
	ext_email Varchar(150),
	ip Varchar(46),
 Primary Key (id)) ENGINE = MyISAM;

Create table survey_goal (
	goalid Int UNSIGNED NOT NULL,
	surveyid Int UNSIGNED NOT NULL,
 Primary Key (goalid,surveyid)) ENGINE = InnoDB;

Create table survey_course (
	courseid Int UNSIGNED NOT NULL,
	surveyid Int UNSIGNED NOT NULL,
 Primary Key (courseid,surveyid)) ENGINE = InnoDB;

Create table template_htmldata (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	extid Int UNSIGNED NOT NULL,
	type Int UNSIGNED,
	question_index Int,
	weight Int,
	radio_select Tinyint UNSIGNED,
 Primary Key (id)) ENGINE = MyISAM;

Create table survey_extra_data (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	surveyid Int UNSIGNED NOT NULL,
	data_type Int UNSIGNED,
	data Int,
 Primary Key (id)) ENGINE = MyISAM;

Create table template_date (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	extid Int UNSIGNED NOT NULL,
	type Int NOT NULL,
	date Datetime NOT NULL,
 Primary Key (id)) ENGINE = MyISAM;

Create table unit_search_cache1 (
	id Int UNSIGNED NOT NULL,
	groupid Int UNSIGNED,
	parentid Int UNSIGNED,
	courseid Int UNSIGNED,
	type Int UNSIGNED,
	compare_unif_id Int UNSIGNED,
	compare_unifint_id Int UNSIGNED,
	departmentid Int UNSIGNED,
 Primary Key (id)) ENGINE = MEMORY;

Create table memory_table_config (
	name Varchar(30) NOT NULL,
	update_time Datetime NOT NULL,
	number Tinyint UNSIGNED NOT NULL,
	dirty Bool DEFAULT 0,
 Primary Key (name)) ENGINE = MEMORY;

Create table unit_search_cache2 (
	id Int UNSIGNED NOT NULL,
	groupid Int UNSIGNED,
	parentid Int UNSIGNED,
	courseid Int UNSIGNED,
	type Int UNSIGNED,
	compare_unif_id Int UNSIGNED,
	compare_unifint_id Int UNSIGNED,
	departmentid Int UNSIGNED,
 Primary Key (id)) ENGINE = MEMORY;

Create table app_config (
	item Varchar(50) NOT NULL,
	system Smallint UNSIGNED,
	value Varchar(5000),
 Primary Key (item)) ENGINE = MyISAM;

Create table category (
	id Int NOT NULL AUTO_INCREMENT,
	mingradeid Int NOT NULL,
	maxgradeid Int NOT NULL,
	enabled Tinyint DEFAULT 1,
	sort Int DEFAULT 0,
	name Varchar(100) NOT NULL,
 Primary Key (id)) ENGINE = MyISAM;

Create table category_department (
	departmentid Int UNSIGNED NOT NULL,
	categoryid Int NOT NULL,
 Primary Key (departmentid,categoryid)) ENGINE = MyISAM;

Create table btnsort (
	id Int UNSIGNED NOT NULL AUTO_INCREMENT,
	coreid Int UNSIGNED NOT NULL,
	linkid Int UNSIGNED NOT NULL,
	type Int UNSIGNED NOT NULL,
	sortkey Int NOT NULL,
	segmentid Int UNSIGNED NOT NULL DEFAULT 0,
	realtype Int UNSIGNED NOT NULL,
	filehash Bigint UNSIGNED NOT NULL,
 Primary Key (id)) ENGINE = MyISAM;


Create Index ind_username ON user (username);
Create Index ind_type ON strand (type);
Create Index ind_importid_int ON strand (importid_int);
Create Index ind_type ON target_text (type);
Create Index ind_unitid ON target_text (unitid);
Create Index ind_benchmarkid ON target_text (benchmarkid);
Create Index ind_courseid ON target_text (courseid);
Create Index ind_type ON selection_category (type);
Create Index ind_type ON unit_selection (type);
Create Index ind_catid ON unit_selection (catid);
Create Index ind_target_type ON unit_target (target_type);
Create Index ind_benchmarkid ON unit_target (benchmarkid);
Create Index ind_type ON resource (type);
Create Index ind_unitid ON resource (unitid);
Create Index ind_courseid ON resource (courseid);
Create Index ind_creatorid ON resource (creatorid);
Create Index ind_groupid ON unit (groupid);
Create Index ind_type ON unit (type);
Create Index ind_status ON unit (status);
Create Index ind_version ON unit (version);
Create Index ind_courseid ON unit (courseid);
Create Index ind_parentid ON unit (parentid);
Create Index ind_hidden ON unit (hidden);
Create Index ind_type ON standard (type);
Create Index ind_importid_int ON standard (importid_int);
Create Index ind_type ON benchmark (type);
Create Index ind_courseid ON benchmark (courseid);
Create Index ind_importid_int ON benchmark (importid_int);
Create Index ind_pid1 ON resource_link (pid1);
Create Index ind_pid2 ON resource_link (pid2);
Create Index ind_type ON resource_link (type);
Create Index ind_type ON user_role (type);
Create Index ind_departmentid ON user_role (departmentid);
Create Index ind_schoolid ON user_role (schoolid);
Create Index ind_userid ON user_role (userid);
Create Index ind_fk1 ON res_sort (fk1);
Create Index ind_fk2 ON res_sort (fk2);
Create Index ind_fk3 ON res_sort (fk3);
Create Index ind_type ON res_sort (type);
Create Index ind_type ON target_text_hist (type);
Create Index ind_unitid ON target_text_hist (unitid);
Create Index ind_benchmarkid ON target_text_hist (benchmarkid);
Create Index ind_courseid ON target_text_hist (courseid);
Create Index ind_hist_date ON target_text_hist (hist_date);
Create Index ind_hist_type ON target_text_hist (hist_type);
Create Index ind_creatorid ON goal (creatorid);
Create Index ind_type ON entry_point (type);
Create Index ind_creatorid ON entry_point (creatorid);
Create Index ind_userid ON team_members (userid);
Create Index ind_creatorid ON survey (creatorid);
Create Index ind_type ON template_htmldata (type);
Create Index ind_extid ON template_htmldata (extid);
Create Index ind_extid ON template_date (extid);
Create Index ind_type ON template_date (type);
Create Index ind_groupid ON unit_search_cache1 (groupid);
Create Index ind_type ON unit_search_cache1 (type);
Create Index ind_courseid ON unit_search_cache1 (courseid);
Create Index ind_parentid ON unit_search_cache1 (parentid);
Create Index ind_compare_unif_id ON unit_search_cache1 (compare_unif_id);
Create Index ind_compare_unifint_id ON unit_search_cache1 (compare_unifint_id);
Create Index ind_departmentid ON unit_search_cache1 (departmentid);
Create Index ind_groupid ON unit_search_cache2 (groupid);
Create Index ind_type ON unit_search_cache2 (type);
Create Index ind_courseid ON unit_search_cache2 (courseid);
Create Index ind_parentid ON unit_search_cache2 (parentid);
Create Index ind_compare_unif_id ON unit_search_cache2 (compare_unif_id);
Create Index ind_compare_unifint_id ON unit_search_cache2 (compare_unifint_id);
Create Index ind_departmentid ON unit_search_cache2 (departmentid);
Create Index ind_system ON app_config (system);
Create Index ind_coreid_filehash ON btnsort (coreid,filehash);
Create Index ind_coreid_type ON btnsort (coreid,type);
Create Index ind_type ON btnsort (type);
Create Index ind_linkid ON btnsort (linkid);

Alter table user add Index IX_department_user (department_id);
Alter table user add Foreign Key (department_id) references department (id) on delete cascade on update cascade;
Alter table course_sis add Foreign Key (departmentid) references department (id) on delete  restrict on update  restrict;
Alter table selection_category add Foreign Key (departmentid) references department (id) on delete  restrict on update  restrict;
Alter table strand add Foreign Key (departmentid) references department (id) on delete  restrict on update  restrict;
Alter table selection_text add Foreign Key (departmentid) references department (id) on delete  restrict on update  restrict;
Alter table user add Foreign Key (auth_id) references auth (id) on delete cascade on update cascade;
Alter table user add Foreign Key (accessid) references access (id) on delete  restrict on update  restrict;
Alter table grade add Foreign Key (schoolid) references school (id) on delete  restrict on update  restrict;
Alter table strand add Foreign Key (schoolid) references school (id) on delete  restrict on update  restrict;
Alter table course_description add Foreign Key (creatorid) references user (id) on delete cascade on update cascade;
Alter table unit add Foreign Key (creatorid) references user (id) on delete  restrict on update  restrict;
Alter table unit add Foreign Key (lasteditorid) references user (id) on delete  restrict on update  restrict;
Alter table resource add Foreign Key (creatorid) references user (id) on delete cascade on update cascade;
Alter table user_course add Foreign Key (userid) references user (id) on delete  restrict on update  restrict;
Alter table course_description add Foreign Key (lasteditorid) references user (id) on delete  restrict on update  restrict;
Alter table user_role add Foreign Key (userid) references user (id) on delete  restrict on update  restrict;
Alter table unit add Foreign Key (editorid) references user (id) on delete  restrict on update  restrict;
Alter table goal add Foreign Key (creatorid) references user (id) on delete  restrict on update  restrict;
Alter table entry_point add Foreign Key (creatorid) references user (id) on delete  restrict on update  restrict;
Alter table team_members add Foreign Key (userid) references user (id) on delete  restrict on update  restrict;
Alter table survey add Foreign Key (creatorid) references user (id) on delete  restrict on update  restrict;
Alter table category_department add Foreign Key (departmentid) references user (id) on delete  restrict on update  restrict;
Alter table resource add Foreign Key (modifierid) references user (id) on delete cascade on update cascade;
Alter table course_description add Foreign Key (courseid) references course_sis (id) on delete cascade on update cascade;
Alter table unit add Foreign Key (courseid) references course_sis (id) on delete cascade on update cascade;
Alter table course_grade add Foreign Key (courseid) references course_sis (id) on delete cascade on update cascade;
Alter table user_course add Foreign Key (courseid) references course_sis (id) on delete  restrict on update  restrict;
Alter table display_grade add Foreign Key (courseid) references course_sis (id) on delete  restrict on update  restrict;
Alter table unit add Foreign Key (course_intid) references course_sis (id) on delete  restrict on update  restrict;
Alter table target_text add Foreign Key (courseid) references course_sis (id) on delete  restrict on update  restrict;
Alter table survey_course add Foreign Key (courseid) references course_sis (id) on delete  restrict on update  restrict;
Alter table standard add Foreign Key (strandid) references strand (id) on delete  restrict on update  restrict;
Alter table unit_target add Foreign Key (targetid) references target_text (id) on delete  restrict on update  restrict;
Alter table course_grade add Foreign Key (gradeid) references grade (id) on delete cascade on update cascade;
Alter table course_sis add Foreign Key (mingradeid) references grade (id) on delete  restrict on update  restrict;
Alter table course_sis add Foreign Key (maxgradeid) references grade (id) on delete  restrict on update  restrict;
Alter table display_grade add Foreign Key (gradeid) references grade (id) on delete  restrict on update  restrict;
Alter table benchmark add Foreign Key (mingradeid) references grade (id) on delete  restrict on update  restrict;
Alter table benchmark add Foreign Key (maxgradeid) references grade (id) on delete  restrict on update  restrict;
Alter table category add Foreign Key (mingradeid) references grade (id) on delete  restrict on update  restrict;
Alter table category add Foreign Key (maxgradeid) references grade (id) on delete  restrict on update  restrict;
Alter table unit_selection add Foreign Key (textid) references selection_text (id) on delete  restrict on update  restrict;
Alter table selection_text add Foreign Key (categoryid) references selection_category (id) on delete  restrict on update  restrict;
Alter table unit_selection add Foreign Key (catid) references selection_category (id) on delete  restrict on update  restrict;
Alter table upload add Foreign Key (resourceid) references resource (id) on delete  restrict on update  restrict;
Alter table resource_link add Foreign Key (resourceid) references resource (id) on delete  restrict on update  restrict;
Alter table resource_select add Foreign Key (resourceid) references resource (id) on delete cascade on update cascade;
Alter table resource_url add Foreign Key (resourceid) references resource (id) on delete  restrict on update  restrict;
Alter table unit_selection add Foreign Key (unitid) references unit (id) on delete  restrict on update  restrict;
Alter table unit_target add Foreign Key (unitid) references unit (id) on delete  restrict on update  restrict;
Alter table linkunit add Foreign Key (unitid) references unit (id) on delete  restrict on update  restrict;
Alter table benchmark add Foreign Key (standardid) references standard (id) on delete  restrict on update  restrict;
Alter table target_text add Foreign Key (benchmarkid) references benchmark (id) on delete  restrict on update  restrict;
Alter table unit_target add Foreign Key (benchmarkid) references benchmark (id) on delete  restrict on update  restrict;
Alter table entry_point add Foreign Key (goalid) references goal (id) on delete  restrict on update  restrict;
Alter table team_members add Foreign Key (goalid) references goal (id) on delete  restrict on update  restrict;
Alter table survey_goal add Foreign Key (goalid) references goal (id) on delete  restrict on update  restrict;
Alter table grade_type add Foreign Key (schoolid) references school_type (id) on delete  restrict on update  restrict;
Alter table survey_template add Foreign Key (surveyid) references survey (id) on delete  restrict on update  restrict;
Alter table survey_goal add Foreign Key (surveyid) references survey (id) on delete  restrict on update  restrict;
Alter table survey_course add Foreign Key (surveyid) references survey (id) on delete  restrict on update  restrict;
Alter table survey_extra_data add Foreign Key (surveyid) references survey (id) on delete  restrict on update  restrict;
Alter table survey_result add Foreign Key (templateid) references survey_template (id) on delete  restrict on update  restrict;
Alter table category_department add Foreign Key (categoryid) references category (id) on delete  restrict on update  restrict;


insert into config(id,system_version) values (1,'1.1.0.3');

# Table user used to start with 100 and unit and resource start at 1
# Legacy installations use the old values
ALTER TABLE user AUTO_INCREMENT = 1001;
ALTER TABLE unit AUTO_INCREMENT = 1001;
ALTER TABLE resource AUTO_INCREMENT = 1001;

CREATE TABLE IF NOT EXISTS  `ci_sessions` (
session_id varchar(40) DEFAULT '0' NOT NULL,
ip_address varchar(16) DEFAULT '0' NOT NULL,
user_agent varchar(50) NOT NULL,
last_activity int(10) unsigned DEFAULT 0 NOT NULL,
user_data blob,
PRIMARY KEY (session_id)
) ENGINE = MyISAM;

insert into auth (id, type) values (1, 'local');
insert into auth (id, type) values (2, 'ldap');

insert into access (id, name) values (1, 'admin');
#insert into access (id, name) values (2, 'admin_read');
#insert into access (id, name) values (3, 'editor');
insert into access (id, name) values (4, 'teacher');
insert into access (id, name) values (5, 'guest (Not supported yet)');
#insert into access (id, name) values (6, 'ict');


insert into user
(id, auth_id, department_id, accessid, enabled, username, password, email, sis_teacher_number, firstname, middlename, lastname, displayname,autogen)
values
(1, 1, NULL, 1, 1, 'admin', '1cad899ab5ba34889b2acb49a98cb8b4', '', NULL, 'Admin', '', 'User', 'Admin User',0),
(2, 1, NULL, 5, 1, 'guest', 'NO_LOGIN', '', NULL, 'Guest', '', 'User', 'Guest User',0);

insert into school (id,shortname,name) values
	(1,'ES', 'Elementary School'),
	(2,'MS', 'Middle School'),
	(3,'HS', 'High School');

insert into grade (id, schoolid, name, sort) values
	(1,1,'Grade 1',1),
	(2,1, 'Grade 2',2),
	(3,1, 'Grade 3',3),
	(4,1, 'Grade 4',4),
	(5,1, 'Grade 5',5),
	(6,2, 'Grade 6',6),
	(7,2, 'Grade 7',7),
	(8,2, 'Grade 8',8),
	(9,3, 'Grade 9',9),
	(10,3, 'Grade 10',10),
	(11,3, 'Grade 11',11),
	(12,3, 'Grade 12',12),
	(13,1,'Kindergarten',0),
	(14, 1, 'EC4',-1),
	(15, 1, 'EC3',-2);
	
insert into department (id, name) values
	(1,'English'),
	(2,'World Languages'),
	(3,'Mathematics'),
	(4,'Science'),
	(5,'Social Studies'),
	(6,'Arts'),
	(7,'Computer Science'),
	(8,'Physical Education');
