use tops;

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

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

CREATE TABLE IF NOT EXISTS prefix_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 prefix_plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_plugins (pluginID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_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 prefix_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

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

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

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

CREATE TABLE IF NOT EXISTS prefix_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 prefix_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_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 prefix_resources (resourceID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

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

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