# Copyright 2001-2005 by VIKO team and contributors
# 
# This file is part of VIKO.
#
# VIKO is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# VIKO is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with VIKO; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

CREATE TABLE Users (
    user_id                 int AUTO_INCREMENT NOT NULL,
    username                varchar(64) NOT NULL    UNIQUE,
    password                varchar(41) NOT NULL,
    firstname               varchar(32) NOT NULL,
    lastname                varchar(32) NOT NULL,
    school_id               int NOT NULL,
    description             text NOT NULL,
    email                   varchar(64) NULL,
    level                   int default 0,
    PRIMARY KEY (user_id),
    FOREIGN KEY (school_id)
        REFERENCES Schools (school_id));

INSERT INTO Users (username,password,description,level) VALUES ('admin',Password('admin'),'administraator', '3');

CREATE TABLE Courses (
    course_id               int AUTO_INCREMENT NOT NULL,
    name                    varchar(128) NOT NULL,
    description             varchar(32) NOT NULL,
    teacher                 int NOT NULL,
    goal                    text NOT NULL,
    marks                   text NOT NULL,
    books                   text NOT NULL,
    time                    varchar(64) NOT NULL,
    classroom               varchar(32) NOT NULL,
    consult                 varchar(64) NOT NULL,
    lessons                 int NOT NULL,
    PRIMARY KEY (course_id),
    FOREIGN KEY (teacher)
        REFERENCES Users (user_id));

CREATE TABLE Courses_Users (
    course_id               int NOT NULL,
    user_id                 int NOT NULL,
    last_view               timestamp,
    total_views             int NOT NULL,
    total_messages          int NOT NULL,
    total_links             int NOT NULL,
    PRIMARY KEY (course_id, user_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id));

CREATE TABLE Files_Folders (
    folder_id               int AUTO_INCREMENT NOT NULL,
    folder_name             varchar(128) NOT NULL,
    course_id               int NOT NULL,
    PRIMARY KEY (folder_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id));

CREATE TABLE Files (
    file_id                 int AUTO_INCREMENT NOT NULL,
    title                   varchar(128) NOT NULL,
    description             varchar(255) NOT NULL,
    filename                varchar(128),
    filesize                int,
    filetype                varchar(32),
    course_id               int NOT NULL,
    user_id                 int NOT NULL,
    folder_id               int NOT NULL,
    addtime                 timestamp,
    PRIMARY KEY (file_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id),
    FOREIGN KEY (folder_id)
        REFERENCES Files_Folders (folder_id));

CREATE TABLE Schools (
    school_id               int AUTO_INCREMENT NOT NULL,
    school_name             varchar(128) NOT NULL,
    PRIMARY KEY (school_id));

CREATE TABLE Forms (
    school_id               int AUTO_INCREMENT NOT NULL,
    form_number             varchar(8) NOT NULL,
    PRIMARY KEY (school_id, form_number),
    FOREIGN KEY (school_id)
        REFERENCES Schools (school_id));

CREATE TABLE Lessons (
    course_id               int NOT NULL,
    lesson_num              int NOT NULL,
    date                    timestamp NOT NULL,
    topics                  text NOT NULL,
    hometask                text,
    term                    timestamp,
    marktype                enum('LESSON', 'PRELIM') DEFAULT 'LESSON' NOT NULL,
    PRIMARY KEY (course_id, lesson_num),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id));

CREATE TABLE Marks (
    course_id               int NOT NULL,
    lesson_num              int NOT NULL,
    user_id                 int NOT NULL,
    mark                    varchar(64) NOT NULL,
    PRIMARY KEY (course_id, lesson_num, user_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id),
    FOREIGN KEY (lesson_num)
        REFERENCES Lessons (lesson_num),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id));

CREATE TABLE Links_Folders (
    folder_id               int AUTO_INCREMENT NOT NULL,
    folder_name             varchar(128) NOT NULL,
    course_id               int NOT NULL,
    PRIMARY KEY (folder_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id));

CREATE TABLE Links (
    link_id                 int AUTO_INCREMENT NOT NULL,
    title                   varchar(128) NOT NULL,
    url                     varchar(255) NOT NULL,
    description             varchar(255) NOT NULL,
    addtime                 timestamp,
    user_id                 int NOT NULL,
    course_id               int NOT NULL,
    folder_id               int NOT NULL,
    PRIMARY KEY (link_id),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id),
    FOREIGN KEY (folder_id)
        REFERENCES Links_Folders (folder_id));

CREATE TABLE Topics (
    topic_id                int AUTO_INCREMENT NOT NULL,
    topic                   varchar(128) NOT NULL,
    user_id                 int NOT NULL,
    course_id               int NOT NULL,
    addtime                 timestamp,
    PRIMARY KEY (topic_id),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id));

CREATE TABLE Messages (
    message_id              int AUTO_INCREMENT NOT NULL,
    course_id               int NOT NULL,
    topic_id                int NOT NULL,
    user_id                 int NOT NULL,
    message                 text NOT NULL,
    addtime                 timestamp,
    PRIMARY KEY (message_id),
    FOREIGN KEY (topic_id)
        REFERENCES Topics (topic_id),
    FOREIGN KEY (course_id)
        REFERENCES Courses (course_id),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id));