CREATE TABLE IF NOT EXISTS PrWorlds (
 worldid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 worldname VARCHAR(32) NOT NULL UNIQUE COMMENT 'Name of world'
) COMMENT 'World table';

CREATE TABLE IF NOT EXISTS PrEntries (
 entryid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 name VARCHAR(32) NOT NULLCOMMENT 'Name of user/group',
 worldid INTEGER NOT NULL COMMENT 'ID of the world the user/group belongs to',
 TYPE TINYINT NOT NULL COMMENT 'Type denotes the entry type. 0 for a user, 1 for a group',
 CONSTRAINT NameWorld UNIQUE (name, worldid, TYPE),
 ENTRYINDEX,
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT 'User/Group table';

CREATE TABLE IF NOT EXISTS PrPermissions (
 permid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 permstring VARCHAR(64) NOT NULL COMMENT 'Permission node',
 entryid INTEGER NOT NULL COMMENT 'Entry whom this node belongs to',
 CONSTRAINT PrEntryPerm UNIQUE (entryid, permstring),
 FOREIGN KEY(entryid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT 'Table of permission nodes';

CREATE TABLE IF NOT EXISTS PrInheritance (
 uinheritid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 childid INTEGER NOT NULL COMMENT 'Child entry',
 parentid INTEGER NOT NULL COMMENT 'Parent entry',
 parentorder INTEGER NOT NULL COMMENT 'Denotes order of inheritance. Groups override other groups'' permissions/data below them in the results',
 CONSTRAINT PrParent UNIQUE (childid, parentid),
 CONSTRAINT PrOrderedInheritance UNIQUE (childid, parentorder),
 CONSTRAINT PrNoSelfInherit CHECK (childid <> parentid),
 FOREIGN KEY(childid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(parentid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT 'Table of parent-child relationships';

CREATE TABLE IF NOT EXISTS PrWorldBase (
 worldid INTEGER NOT NULL,
 defaultid INTEGER COMMENT 'Default group',
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(defaultid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT 'Table of the default groups in that world';

CREATE TABLE IF NOT EXISTS PrData (
 dataid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 entryid INTEGER NOT NULL COMMENT 'ID entry whom this data node belongs to',
 path VARCHAR(64) NOT NULL COMMENT 'Path to data node (e.g. "prefix", "build")',
 DATA VARCHAR(64) NOT NULL COMMENT 'Data node in string form (o.toString())',
 CONSTRAINT PrDataUnique UNIQUE (entryid, path),
 FOREIGN KEY(entryid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrTracks (
 trackid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 trackname VARCHAR(64) NOT NULL UNIQUE COMMENT 'Track name',
 worldid INTEGER NOT NULL COMMENT 'ID of world track belongs to',
 CONSTRAINT TracksUnique UNIQUE (trackid, worldid),
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrTrackGroups (
 trackgroupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 trackid INTEGER NOT NULL COMMENT 'ID of track',
 gid INTEGER NOT NULL COMMENT 'ID of group in track',
 groupOrder INTEGER NOT NULL COMMENT 'Denotes position of the group in the track',
 CONSTRAINT TrackGroupsUnique UNIQUE (trackid, gid),
 FOREIGN KEY(trackid) REFERENCES PrTracks(trackid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(gid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);