/* SCROTT - Core System Data Structures
 * ------------------------------------
 *   SQL Database Schema for backend
 */

CREATE DATABASE IF NOT EXISTS `db_scrott`;
USE `db_scrott`;

/* Description of SCROTT object permissions system
 * -----------------------------------------------
 * Every object has an owner, zero or more additional members, and then there is the general
 * public.
 *
 * OWNER  = user OR group
 * MEMBER = user
 *                                                 OWNER        MEMBERS           OTHERS
 * Permissions are:                            ------------------------------------------------
 *  1.  Access object                             GRANTED       GRANTED           MB #6
 *  2.  Modify object                             GRANTED        MB #0           DENIED
 *  3.  Modify members                            GRANTED        MB #1           DENIED
 *  4.  Modify permissions                        GRANTED       DENIED           DENIED
 *  5.  Access sub-objects                        GRANTED        MB #2            MB #7
 *  6.  Create sub-objects                        GRANTED        MB #3            MB #8
 *  7.  Modify sub-objects                        GRANTED        MB #4           DENIED
 *  8.  Modify sub-objects' members               GRANTED        MB #5           DENIED
 *  9.  Modify sub-objects' permissions           GRANTED       DENIED           DENIED
 *
 *                                012345678
 *                            EG: 001111000 = 120 --implies--> that members may access,create,and
 *                                                             fully modify sub-objects. All other
 *                                                             options are DENIED.  This mask value
 *                                                             of 120 is a recommended starting point.
 *
 * Permissions can be overridden down-stream, but permissions will cascade otherwise.
 */

DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
    `object` varchar(10) NOT NULL,
    `member` varchar(10) NOT NULL,

    PRIMARY KEY (`object`,`member`)
);

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
    `id`    int(10)     unsigned     NOT NULL AUTO_INCREMENT,
    `guid`  varchar(10)              NOT NULL,
    `owner` varchar(10)              NOT NULL,
    `perms` int(10)     unsigned     NOT NULL,
    `name`  varchar(50)              NOT NULL,
    `alias` varchar(50)          DEFAULT NULL,
    `key`   varchar(64)              NOT NULL,
    `salt`  varchar(64)              NOT NULL,

    PRIMARY KEY (`id`)
);






DROP TABLE IF EXISTS `group`;
CREATE TABLE `group` (
    `id`    int(10)     unsigned NOT NULL AUTO_INCREMENT,
    `guid`  varchar(10)          NOT NULL,
    `owner` varchar(10)          NOT NULL,
    `name`  varchar(50)          NOT NULL,
    /* TODO -- wip */