-- Set of custom Koolielu tables.

-- Categories (added by site administrators)
CREATE TABLE IF NOT EXISTS prefix_koolielu_categories (
  guid bigint(20) unsigned NOT NULL auto_increment,
  title varchar(255) NOT NULL,
  parent bigint(20) unsigned DEFAULT NULL,
  pos int(3),
  locked int(1) DEFAULT 0,
  PRIMARY KEY (guid),
  KEY title (title)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Moderation results (used to generate moderation statstics per user)
CREATE TABLE IF NOT EXISTS `prefix_koolielu_moderation` (
  `user_guid` bigint(20) unsigned  NOT NULL,
  `resolution` enum ('accept', 'decline') NOT NULL,
  `subtype` varchar(50) NOT NULL,
  `object_guid` bigint(20) unsigned DEFAULT NULL,
  `time` timestamp DEFAULT NOW(),
  KEY `user_guid` (`user_guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_curriculum (
    eid INT,
    cid INT,
    level INT,
    start INT,
    end INT,
    isSubject BOOLEAN,
    title VARCHAR(500),
    description TEXT,
    KEY cid (cid),
    KEY cid_eid (cid, eid),
    KEY start (start),
    KEY end (end),
    KEY level (level),
    KEY isSubject (isSubject)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS prefix_koolielu_waramu_queue (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    user_guid bigint UNSIGNED NOT NULL,
    xml text,
    data text,
    suitable_for_student int(1) DEFAULT 0,
    competition_resource int(1) DEFAULT 0,
    tiger_leap_supported int(1) DEFAULT 0,
    status int DEFAULT 0,
    moderator_guid BIGINT UNSIGNED,
    time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    moderation_time TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_waramu_resources (
    waramu_id char(255) PRIMARY KEY,
    user_guid bigint UNSIGNED,
    suitable_for_student int(1) DEFAULT 0,
    competition_resource int(1) DEFAULT 0,
    tiger_leap_supported int(1) DEFAULT 0,
    time timestamp DEFAULT 0,
    moderator_guid bigint UNSIGNED,
    koolielu_resource int(1) DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_waramu_comments (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    waramu_id char(255) NOT NULL,
    user_guid bigint UNSIGNED NOT NULL,
    time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    comment varchar(2000),
    is_public int(1) DEFAULT 1 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_frontpage_resource (
    ID char(255) PRIMARY KEY,
    subtype varchar(255) NOT NULL,
    user_guid bigint UNSIGNED NOT NULL,
    description text,
    subject int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_logos (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    address varchar(255) NOT NULL,
    placeid varchar(255) NOT NULL,
    position int(3),
    pageid varchar(255) NOT NULL,
    userid bigint(20) unsigned NOT NULL,
    url varchar(1000),
    alt varchar(1000)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_polls (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    questiontext varchar(500),
    time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    isActive int(1) DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_polls_variants (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    poll_id INT NOT NULL,
    variant varchar(255),
    pos int(3)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_polls_answers (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    user_guid bigint UNSIGNED,
    user_ip varchar(20),
    poll_id INT NOT NULL,
    answer_id INT NOT NULL,
    time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_resource_stats (
    waramu_id  char(255) PRIMARY KEY UNIQUE,
    read_stat INT DEFAULT 0,
    url_stat INT DEFAULT 0,
    favorite_stat INT DEFAULT 0,
    collection_stat INT DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_favorite_resources (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    waramu_id char(255) NOT NULL,
    userid bigint(20) unsigned NOT NULL,
    keywords TEXT
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_schools (
    school_id bigint(20) NOT NULL,
    note varchar(255),
    branch varchar(255),
    reg_nr varchar(255),
    school_name varchar(255),
    school_type varchar(255),
    school_subtype varchar(255),
    ownership varchar(255),
    county varchar(255),
    city varchar(255),
    location varchar(255),
    address varchar(255),
    postalindex varchar(255),
    tel varchar(255),
    fax varchar(255),
    email varchar(255),
    webpage varchar(255)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_quality_marks (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    image varchar(255) NOT NULL,
    user_guid bigint(20) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_resources_quality_marks (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    waramu_id char(255) NOT NULL,
    quality_mark_id INT NOT NULL,
    moderator_guid bigint UNSIGNED NOT NULL,
    time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_generated_certificates (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    creator bigint(20) NOT NULL,
    persona bigint(20) NOT NULL,
    materials text,
    time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_calendar_objects (
    guid bigint(20) PRIMARY KEY,
    creator bigint(20) NOT NULL,
    type varchar(255) NOT NULL,
    title varchar(255),
    description varchar(255),
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    time1_begin varchar(255) NOT NULL,
    time1_end varchar(255) NOT NULL,
    time2_begin varchar(255) DEFAULT 0 NOT NULL,
    time2_end varchar(255) DEFAULT 0 NOT NULL,
    approved INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_calendar_subject_refs (
    cal_object_id bigint(20),
    subject_id INT (6),
    FOREIGN KEY (cal_object_id) REFERENCES elggkoolielu_calendar_objects (guid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_curriculum_map (
    fromtax int,
    totax int,
    fromeid int,
    toeid int
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_koolielu_subcomments (
	id bigint(20) unsigned PRIMARY KEY AUTO_INCREMENT,
	entity_guid bigint(20) unsigned NOT NULL,
	comment_id bigint(20) unsigned NOT NULL,
	owner_guid bigint(20) unsigned NOT NULL,
	value text DEFAULT '',
	time_created int(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_subjectmap (
    id int,
    title varchar(500) COLLATE utf8_bin DEFAULT NULL,
    cid int,
    eid int
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS prefix_curriculum_map (
    fromtax int,
    totax int, 
    fromeid int, 
    toeid int
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

create table prefix_waramu_count_cache(query text, value int, created timestamp default now());
create table prefix_mms_keystore(guid int, mmskey varchar(255), created timestamp default now());
