/*
drop database psyhvel;
create database psyhvel;
*/
use psyhvel;

/* plugins fw */
CREATE TABLE IF NOT EXISTS psyhvel_plugins (
    pluginID char(255) UNIQUE NOT NULL PRIMARY KEY, 
    installed boolean, 
    version char(255)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_pages (
    pluginID char(255),
    pageID char(255),
    title char(255),
    grps char(255),
    roles char(255),
    validators char(255),
    FOREIGN KEY (pluginID) REFERENCES psyhvel_plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_types (
    pluginID char(255),
    typeID char(255),
    title char(255),
    icon char(255),
    icon_method char(255),
    task boolean,
    typeIdent char(255),
    FOREIGN KEY (pluginID) REFERENCES psyhvel_plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_menus (
    pluginID char(255),
    pageID char(255),
    title char(255),
    grp char(255),
    page char(255),
    menu char(255),
    roles char(255),
    valid char(255),
    FOREIGN KEY (pluginID) REFERENCES psyhvel_plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_users (
    id int(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    uname char(255) default NULL,
    pwd char(255) default NULL,
    roles char(6) default "000000",
    approved BOOLEAN
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_userinfo (
    id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userid int(5) default NULL UNIQUE KEY,
    firstname char(255) DEFAULT "",
    lastname char(255) DEFAULT "",
    email char(255) default NULL,
    language char(3) default 'EST',
    sex enum('boy','girl') default 'boy',
    homepage char(255) default NULL,
    FOREIGN KEY (userid) REFERENCES psyhvel_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_groups (
    groupID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    groupName char(255)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_grouprelations (
    relID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    groupID int(5),
    userID int(5),
    FOREIGN KEY (userID) REFERENCES psyhvel_users (id) ON DELETE CASCADE,
    FOREIGN KEY (groupID) REFERENCES psyhvel_groups (groupID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_folders (
    folderID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parentFolder int(5),
    userID int(5),
    title varchar(1500),
    FOREIGN KEY (userID) REFERENCES psyhvel_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_gamepacks (
    folderID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parentFolder int(5),
    userID int(5),
    title varchar(1500),
    description TEXT,
    FOREIGN KEY (userID) REFERENCES psyhvel_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_tests (
    testID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title char(255),
    userID int(5),
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    description TEXT,
    active int(1) DEFAULT 0,
    security ENUM('public', 'private') DEFAULT 'public',
    FOREIGN KEY (userID) REFERENCES psyhvel_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_testrelations (
    relID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    groupID int(5),
    testID int(5),
    FOREIGN KEY (testID) REFERENCES psyhvel_tests (testID) ON DELETE CASCADE,
    FOREIGN KEY (groupID) REFERENCES psyhvel_groups (groupID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_tests_for_groups (
    relID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    testID int(5),
    groupID int(5),
    userID int(5),
    security ENUM('public', 'private') DEFAULT 'public',
    FOREIGN KEY (testID) REFERENCES psyhvel_tests (testID),
    FOREIGN KEY (groupID) REFERENCES psyhvel_groups (groupID),
    FOREIGN KEY (userID) REFERENCES psyhvel_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_resources (
    resourceID int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    resourceUID char(22),
    deleted int(1) DEFAULT 0,
    type char(255),
    folderid int(5) DEFAULT NULL,
    title char(255),
    userID int(5),
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    description TEXT,
    keywords TEXT,
    state ENUM('draft', 'final') DEFAULT 'draft',
    security ENUM('public', 'private') DEFAULT 'public',
    FOREIGN KEY (userID) REFERENCES psyhvel_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_gamesheet (
    resourceID int(5),
    gamepack int(5),
    place int(3),
    message TEXT,
    games TEXT,
    data TEXT,
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (gamepack) REFERENCES psyhvel_gamepack (resourceID) ON DELETE CASCADE,
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_basket (
    resourceID int(5),
    data TEXT,
    messages TEXT,
    time int(5),
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_comboling (
    resourceID int(5),
    rows int(2) default 8,
    cols int(2) default 8,
    data TEXT,
    messages TEXT,
    time int(5),
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_battery (
    resourceID int(5),
    landscape TEXT,
    batteries TEXT,
    data TEXT,
    messages TEXT,
    time int(5),
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_laser (
    resourceID int(5),
    data TEXT,
    robo int(2) default 0,
    friend int(2) default 0,
    width int(2) default 0,
    height int(2) default 0,
    tools TEXT,
    messages TEXT,
    time int(5),
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_images (
    resourceID int(5),
    imageUID char(22),
    imageName char(255),
    size int(5),
    mime char(255),
    width int(1),
    height int(1),
    INDEX USING BTREE(resourceID),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_usedimages (
    resourceID int(5),
    imageUID char(22),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_usedgames (
    resourceID int(5),
    gameUID char(22),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS psyhvel_fti (
    resourceID INT PRIMARY KEY,
    content TEXT,
    FULLTEXT (content),
    FOREIGN KEY (resourceID) REFERENCES psyhvel_resources (resourceID) ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;