ALTER TABLE course CHANGE code identifier char(255);
ALTER TABLE course ADD COLUMN provider bigint(20) UNSIGNED DEFAULT NULL;
ALTER TABLE course ADD COLUMN educationlevel bigint(20) UNSIGNED DEFAULT NULL;
ALTER TABLE course ADD COLUMN languageofinstruction bigint(20) UNSIGNED DEFAULT NULL;
ALTER TABLE course ADD COLUMN attendancemode enum('0','1','2','3','4','5','6') DEFAULT '0';
ALTER TABLE course ADD COLUMN objective TEXT;
ALTER TABLE course ADD COLUMN assessment TEXT;
ALTER TABLE course ADD COLUMN prerequisite TEXT;
ALTER TABLE course ADD COLUMN tag char(255);
ALTER TABLE course ADD COLUMN status enum('0','1','2') DEFAULT 0;
ALTER TABLE course ADD COLUMN image bigint(20) UNSIGNED DEFAULT NULL;
ALTER TABLE course ADD COLUMN trashed boolean NOT NULL DEFAULT 0;
ALTER TABLE course DROP COLUMN affiliation;
ALTER TABLE course DROP COLUMN cost;
ALTER TABLE course ADD FOREIGN KEY (provider) REFERENCES dictionary (id) ON DELETE SET NULL;
ALTER TABLE course ADD FOREIGN KEY (educationlevel) REFERENCES dictionary (id) ON DELETE SET NULL;
ALTER TABLE course ADD FOREIGN KEY (languageofinstruction) REFERENCES dictionary (id) ON DELETE SET NULL;

ALTER TABLE learning_resource MODIFY COLUMN folder bigint(20) UNSIGNED;
ALTER TABLE resource_folder MODIFY COLUMN description TEXT;
ALTER TABLE learning_resource MODIFY COLUMN description TEXT;
ALTER TABLE profile MODIFY COLUMN description TEXT;
ALTER TABLE learning_resource DROP COLUMN published;
ALTER TABLE learning_resource ADD COLUMN published int(11);

ALTER TABLE resource_folder ADD COLUMN created TIMESTAMP DEFAULT NOW();
ALTER TABLE resource_folder ADD COLUMN modified TIMESTAMP;

/* 20.09.2011 */
ALTER TABLE resource_folder DROP COLUMN description;

/* 28.09.2011 */
ALTER TABLE coursegroup ADD COLUMN workspace char(255);

/* 11.10.2011 */
ALTER TABLE courseblog_post ADD COLUMN type varchar(20) DEFAULT "CourseblogPost";
ALTER TABLE assignment ADD COLUMN subtype char(255) NOT NULL;
ALTER TABLE assignment MODIFY COLUMN type varchar(20) DEFAULT "Assignment";
ALTER TABLE learning_resource ADD COLUMN subtype char(100);
ALTER TABLE learning_resource MODIFY COLUMN type varchar(20) DEFAULT "LearningResource";
ALTER TABLE learning_outcome ADD COLUMN type varchar(20) DEFAULT "LearningOutcome";
UPDATE assignment SET type="Assignemnt", subtype="writing";
UPDATE learning_resource SET type="LearningResource", subtype="slideshare" WHERE type="slideshare";
UPDATE learning_resource SET type="LearningResource", subtype="link" WHERE type="link";
UPDATE learning_resource SET type="LearningResource", subtype="youtube" WHERE type="youtube";

/* 13.10.2011 */
ALTER TABLE assignment DROP COLUMN hidden;
ALTER TABLE assignment ADD COLUMN structure TEXT;
ALTER TABLE assignment ADD COLUMN late_submission boolean NOT NULL DEFAULT 0;
ALTER TABLE assignment ADD COLUMN submission_visibility boolean NOT NULL DEFAULT 0;
ALTER TABLE assignment ADD COLUMN maxpoints int(4) DEFAULT 0;

/* 10.11.2011 */
/* Please check that constraints are as follows: profile_ibfk_4, profile_ibfk_5 and profile_ibfk_6 */
ALTER TABLE profile DROP FOREIGN KEY profile_ibfk_4, DROP COLUMN curriculum_id;
ALTER TABLE profile DROP FOREIGN KEY profile_ibfk_5, DROP COLUMN university_id;
ALTER TABLE profile DROP FOREIGN KEY profile_ibfk_6, DROP COLUMN institute_id;
DROP TABLE curriculum;
DROP TABLE institute;
DROP TABLE university;

/* 23.12.2011 */
ALTER TABLE answer ADD COLUMN graded TIMESTAMP;
/* Need to set some default values, NULL is not allowed. */
UPDATE answer set graded = NOW();

/* 27.01.2012 */
ALTER TABLE profile ADD COLUMN blogurl varchar(255) DEFAULT '';
ALTER TABLE profile ADD COLUMN department bigint(20) UNSIGNED DEFAULT NULL;
ALTER TABLE profile ADD FOREIGN KEY (department) REFERENCES dictionary (id) ON DELETE SET NULL;

/* 28.02.2012 */
/* organization is not unique */
ALTER TABLE client DROP FOREIGN KEY client_ibfk_1, DROP INDEX organization, ADD FOREIGN KEY (organization) REFERENCES organization (id) ON DELETE SET NULL;

/* Version 0.5 released */

/* 10.05.2012 */
DROP TABLE friend;

/* 11.05.2012 */
ALTER TABLE profile ADD COLUMN user_id bigint(20) UNSIGNED NOT NULL;
UPDATE Profile p SET user_id=(SELECT id FROM User u WHERE u.email=p.email);
/* Make sure that user_id is populated with corrssponding identifiers from User table */
ALTER TABLE profile ADD FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;
/* Run these once you are sure that previous connection is working. */
/* Please check that constraints are as follows: profile_ibfk_1, profile_ibfk_2 and profile_ibfk_3 */
ALTER TABLE profile DROP FOREIGN KEY profile_ibfk_1, DROP COLUMN email;
ALTER TABLE profile DROP FOREIGN KEY profile_ibfk_2, DROP COLUMN firstname;
ALTER TABLE profile DROP FOREIGN KEY profile_ibfk_3, DROP COLUMN lastname;

/* 17.05.2012 */

/* INTRODUCING Table generated ID-s GenerationType.TABLE for Entities, reason this we can get new Entity after persist.
 * SQL based GenerationType.IDENTITY, requires FLUSH - two queries to DB, first insert and after insert SELECT
 */

CREATE TABLE id_gen ( ID_NAME VARCHAR(80), ID_VAL BIGINT UNSIGNED, CONSTRAINT pk_id_name PRIMARY KEY(ID_NAME) );

/* id_gen table has to be prepopulated with required data, JPA only calls SQL UPDATE on id_gen table */
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_ASSIGNMENT", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM assignment ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_ANSWER", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM answer ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_ACTIVITY", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM activity ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_CATEGORY", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM category ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_CLIENT", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM client ) );

INSERT INTO id_gen (id_name, id_val) VALUES("LAST_COURSE", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM course ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_COURSE_BLOGPOST_COMMENT", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM courseblog_comment ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_COURSE_BLOGPOST", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM courseblog_post ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_COURSE_GROUP", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM coursegroup ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_FACILITATOR", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM facilitator ) );

INSERT INTO id_gen (id_name, id_val) VALUES("LAST_IMAGE", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM image ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_LEARNER", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM learner ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_LEARNING_OUTCOME", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM learning_outcome ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_LEARNING_RESOURCE", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM learning_resource ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_ORGANIZATION", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM organization ) );

INSERT INTO id_gen (id_name, id_val) VALUES("LAST_PROFILE", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM profile ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_RESOURCE_FOLDER", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM resource_folder ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_TAG", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM tag ) );
INSERT INTO id_gen (id_name, id_val) VALUES("LAST_USER_ID", ( SELECT IF ( MAX(id) > 0, MAX(ID), 1 ) AS result FROM user ) );

/* Version 0.6 released */

/* 30.10.2012 by Raido Kuli */

ALTER TABLE learner MODIFY course BIGINT(20) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE learner MODIFY learner BIGINT(20) UNSIGNED DEFAULT 0 NOT NULL;

/* 23.01.2013 */
/* Added a table for Course Grades*/
CREATE TABLE IF NOT EXISTS grade (
    learner_id bigint(20) UNSIGNED NOT NULL,
    grade varchar(10) NOT NULL DEFAULT '',
    feedback varchar(255) NOT NULL DEFAULT '',
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    FOREIGN KEY (learner_id) REFERENCES learner (id) ON DELETE CASCADE,
    PRIMARY KEY (learner_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;