--
-- Revoke all priveleges on helgadev for db users helgaGuest and helgaBasic.
-- These users probably should not have any privs on other databases but if
-- they do it's not the business of this script. We'll build up new
-- permissions for these users as we go along.
-- 
GRANT ALL PRIVILEGES ON * TO 'helgaGuest'@'%';
REVOKE ALL PRIVILEGES ON * FROM 'helgaGuest'@'%';
GRANT ALL PRIVILEGES ON * TO 'helgaBasic'@'%';
REVOKE ALL PRIVILEGES ON * FROM 'helgaBasic'@'%';
--
-- This should already be true, but make sure helgaAdvanced and helgaAdmin have 
-- full privs on the database.
--
GRANT ALL PRIVILEGES ON * TO 'helgaAdvanced'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON * TO 'helgaAdmin'@'%' WITH GRANT OPTION;

-- --------------------------------------------------------

--
-- Table structure for table `assets`
--

CREATE TABLE `assets` (
  `assetId` int(11) NOT NULL auto_increment,
  `assetName` varchar(255) NOT NULL,
  `typeId` int(11) NOT NULL,
  `parentId` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  PRIMARY KEY (`assetId`),
  UNIQUE KEY `parentId` (`parentId`,`position`)
);
GRANT SELECT ON `assets` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `assets` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `asset_types`
--

CREATE TABLE `asset_types` (
  `typeId` int(11) NOT NULL auto_increment,
  `typeName` varchar(255) NOT NULL,
  `inheritsId` int(11) NOT NULL,
  `forcedRoot` varchar(255) NULL,
  PRIMARY KEY (`typeId`),
  UNIQUE KEY `typeName` (`typeName`)
);
GRANT SELECT ON `asset_types` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `asset_types` TO 'helgaBasic'@'%';

--
-- Default asset types
--

INSERT INTO `asset_types` (`typeId`, `typeName`, `inheritsId`, `forcedRoot`) VALUES
(1, 'asset', -1, NULL),
(2, 'folder', 1, NULL),
(3, 'site', 2, NULL),
(4, 'project', 2, '/'),
(5, 'user', 1, '/users'),
(6, 'sequence', 2, NULL),
(7, 'shot', 2, NULL),
(8, 'file', 1, NULL),
(9, 'script', 8, NULL),
(10, 'machine', 1, '/machines'),
(11, 'model', 2, NULL),
(12, 'image', 8, NULL),
(13, 'video', 8, NULL),
(14, 'mayaAscii', 8, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `assigments`
--

CREATE TABLE `assignments` (
  `assignmentId` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `taskId` INT NOT NULL,
  `userId` INT NOT NULL,
  `progress` TINYINT NOT NULL DEFAULT '0'
);
GRANT SELECT, INSERT, UPDATE ON `assignments` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `asset_watch`
--

CREATE TABLE `asset_watch` (
  `watchId` int(11) NOT NULL auto_increment,
  `assetId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  PRIMARY KEY  (`watchId`)
);
GRANT SELECT ON `asset_watch` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `asset_watch` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `attachments`
--

CREATE TABLE `attachments` (
  `attachmentId` int(11) NOT NULL auto_increment,
  `logEntryId` int(11) NOT NULL,
  `filePath` varchar(255) NOT NULL,
  `setStart` int(11) NULL,
  `setEnd` int(11) NULL,
  PRIMARY KEY (`attachmentId`)
);
GRANT SELECT ON `attachments` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `attachments` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `attributes`
--

CREATE TABLE `attributes` (
  `attrId` int(11) NOT NULL auto_increment,
  `assetId` int(11) NOT NULL,
  `attrName` varchar(255) NOT NULL,
  `attrVal` text NULL,
  PRIMARY KEY  (`attrId`),
  UNIQUE KEY `assetId` (`assetId`,`attrName`)
);
GRANT SELECT ON `attributes` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `attributes` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `core_attrs`
--

CREATE TABLE `core_attrs` (
  `defId` int(11) NOT NULL auto_increment,
  `typeId` int(11) NOT NULL,
  `attrName` varchar(255) NOT NULL,
  `defaultValue` text NOT NULL,
  PRIMARY KEY  (`defId`)
);
GRANT SELECT ON `core_attrs` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `core_attrs` TO 'helgaBasic'@'%';

INSERT INTO `core_attrs` (`defId`, `typeId`, `attrName`, `defaultValue`) VALUES
(NULL, '1', 'createdBy', ''),
(NULL, '1', 'createdOn', ''),
(NULL, '1', 'currentPreview', ''),
(NULL, '1', 'description', ''),
(NULL, '1', 'fullName', ''),
(NULL, '1', 'notes', ''),
(NULL, '2', 'memberTypes', 'all'),
(NULL, '3', 'dbRevision', '1'),
(NULL, '3', 'helgaVersion', '1.0'),
(NULL, '3', 'siteName', ''),
(NULL, '4', 'fps', '24'),
(NULL, '4', 'xres', '640'),
(NULL, '4', 'yres', '360'),
(NULL, '4', 'projectWebRoot', '\\$::HELGADOCROOT/\\[hget project -name \\$assetName\\]'),
(NULL, '5', 'email', ''),
(NUll, '5', 'homeAsset', '/'),
(NUll, '5', 'layout', ''),
(NULL, '5', 'phone', ''),
(NULL, '5', 'url', ''),
(NULL, '7', 'startFrame', ''),
(NULL, '7', 'endFrame', ''),
(NULL, '8', 'filePath', ''),
(NULL, '8', 'setEnd', ''),
(NULL, '8', 'setStart', ''),
(NULL, '8', 'startFrame', ''),
(NULL, '8', 'endFrame', ''),
(NULL, '9', 'allowedTypes', ''),
(NULL, '9', 'machineNames', ''),
(NULL, '9', 'machineTags', ''),
(NULL, '9', 'version', '1'),
(NULL, '11', 'location', '');

-- --------------------------------------------------------

--
-- Table structure for table `log_entries`
--

CREATE TABLE `log_entries` (
  `entryId` int(11) unsigned NOT NULL auto_increment,
  `assetId` int(11) NOT NULL,
  `entryText` text,
  `timestamp` int(10) NOT NULL,
  `authorId` int(11) NOT NULL,
  PRIMARY KEY  (`entryId`)
);
GRANT SELECT ON `log_entries` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `log_entries` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `permId` int(11) NOT NULL auto_increment,
  `userId` int(11) NOT NULL,
  `assetId` int(11) NOT NULL,
  `permLevel` enum('none','basic','advanced','admin') NOT NULL,
  PRIMARY KEY  (`permId`)
);
GRANT SELECT ON `permissions` TO 'helgaGuest'@'%';
GRANT SELECT ON `permissions` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `previews`
--

CREATE TABLE `previews` (
   `previewId` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
   `assetId` INT NOT NULL ,
   `position` SMALLINT NOT NULL ,
   `filePath` VARCHAR( 255 ) NOT NULL
); 
GRANT SELECT ON `previews` TO 'helgaGuest'@'%';
GRANT SELECT,INSERT,UPDATE,DELETE ON `previews` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `tags`
--

CREATE TABLE `tags` (
  `tagId` int(11) NOT NULL auto_increment,
  `thingId` int(11) NOT NULL,
  `thingType` enum('asset','task','logEntry') NOT NULL,
  `tagText` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`tagId`)
);
GRANT SELECT ON `tags` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `tags` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `taskId` int(11) NOT NULL auto_increment,
  `assetId` int(11) NOT NULL,
  `taskName` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL default '',
  `status` enum('notstarted','inprogress','complete') NOT NULL default 'notstarted',
  `createdDate` int(10) NOT NULL,
  `startedDate` int(10) default NULL,
  `dueDate` int(10) default NULL,
  `completedDate` int(10) default NULL,
  `difficulty` tinyint(4) default NULL,
  `priority` tinyint(4) default NULL,
  PRIMARY KEY  (`taskId`)
);
GRANT SELECT ON `tasks` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `tasks` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `task_links`
--

CREATE TABLE `task_links` (
  `linkId` int(11) NOT NULL auto_increment,
  `sourceTaskId` int(11) NOT NULL,
  `destTaskId` int(11) NOT NULL,
  PRIMARY KEY  (`linkId`)
);
GRANT SELECT ON `task_links` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `task_links` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Table structure for table `task_names`
--

CREATE TABLE `task_names` (
  `nameId` int(11) NOT NULL auto_increment,
  `taskName` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`nameId`),
  UNIQUE KEY `taskName` (`taskName`)
);
GRANT SELECT ON `task_names` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `task_names` TO 'helgaBasic'@'%';

--
-- Initial data for table `task_names`
--

INSERT INTO `task_names` ( `nameId`, `taskName`, `description` ) VALUES
( 1, 'board', 'Boards' ),
( 2, 'lay', 'Layout' ),
( 3, 'anim', 'Animation' ),
( 4, 'fx', 'Effects' ),
( 5, 'light', 'Lighting' ),
( 6, 'render', 'Rendering' ),
( 7, 'pfix', 'Paint Fix' ),
( 8, 'comp', 'Compositing' ),
( 9, 'out', 'Film-out' ),
( 10, 'art', 'Art' ),
( 11, 'geom', 'Geometry' ),
( 12, 'artic', 'Articulation' ),
( 13, 'shad', 'Shading' ),
( 14, 'bless', 'Blessing' ),
( 15, 'afix', 'Anim Fix' ),
( 16, 'matte', 'Matte Painting' ),
( 17, 'lfix', 'Layout Fix' ),
( 18, 'occ', 'Occlusion' ),
( 19, 'bpaint', 'Background Paint' ),
( 20, 'flat', 'Flat Color' ),
( 21, 'multip', 'Multiplane' );

-- --------------------------------------------------------

--
-- Table structure for table `task_sets`
--

CREATE TABLE `task_sets` (
  `setId` int(11) NOT NULL auto_increment,
  `setName` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`setId`)
);
GRANT SELECT ON `task_sets` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `task_sets` TO 'helgaBasic'@'%';

--
-- Initial data for table `task_sets`
--

INSERT INTO `task_sets` ( `setId`, `setName` ) VALUES
( 1, 'standard_shot' ),
( 2, '3d_shot' ),
( 3, '2d_shot' ),
( 4, 'model' );

-- --------------------------------------------------------

--
-- Table structure for table `task_set_links`
--

CREATE TABLE `task_set_links` (
  `linkId` int(11) NOT NULL auto_increment,
  `setId` int(11) NOT NULL,
  `nameId` int(11) NOT NULL,
  PRIMARY KEY  (`linkId`)
);
GRANT SELECT ON `task_set_links` TO 'helgaGuest'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `task_set_links` TO 'helgaBasic'@'%';

--
-- Initial data for table `task_set_links`
--

INSERT INTO `task_set_links` ( `linkId`, `setId`, `nameId` ) VALUES
( NULL, 1, 1 ),
( NULL, 1, 2 ),
( NULL, 1, 3 ),
( NULL, 1, 5 ),
( NULL, 1, 6 ),
( NULL, 1, 8 ),
( NULL, 1, 9 ),
( NULL, 2, 1 ),
( NULL, 2, 2 ),
( NULL, 2, 3 ),
( NULL, 2, 4 ),
( NULL, 2, 14 ),
( NULL, 2, 6 ),
( NULL, 2, 18 ),
( NULL, 2, 16 ),
( NULL, 2, 8 ),
( NULL, 2, 7 ),
( NULL, 2, 15 ),
( NULL, 2, 9 ),
( NULL, 3, 1 ),
( NULL, 3, 2 ),
( NULL, 3, 3 ),
( NULL, 3, 19 ),
( NULL, 3, 20 ),
( NULL, 3, 5 ),
( NULL, 3, 21 ),
( NULL, 3, 4 ),
( NULL, 3, 8 ),
( NULL, 3, 9 ),
( NULL, 4, 10 ),
( NULL, 4, 11 ),
( NULL, 4, 12 ),
( NULL, 4, 13 );
GRANT SELECT ON `users` TO 'helgaBasic'@'%';

-- --------------------------------------------------------

--
-- Create the site asset and attrs for it
--

INSERT INTO `assets` ( `assetId`, `assetName`, `typeId`, `parentId`, `position` ) VALUES ( 1, 'site', 3, -1, 1 );

INSERT INTO `attributes` ( `attrId`, `assetId`, `attrName`, `attrVal` ) VALUES 
( NULL, 1, 'fullName', 'Helga Site' ),
( NULL, 1, 'description', 'Your Helga Site. The "root" asset.' ),
( NULL, 1, 'createdOn', '1227570861' ),
( NULL, 1, 'createdBy', 'bcipriano' ),
( NULL, 1, 'memberTypes', 'all' ),
( NULL, 1, 'helgaVersion', '1.0' ),
( NULL, 1, 'unique', 'true' ),
( NULL, 1, 'dbRevision', '1' ),
( NULL, 1, 'projectWebRoot', '$::HELGADOCROOT/global' ),
( NULL, 1, 'siteName', 'Hampshire College Dev' ),
( NULL, 1, 'xres', '640' ),
( NULL, 1, 'yres', '360' ),
( NULL, 1, 'displayResX', '640' ),
( NULL, 1, 'displayResY', '360' ),
( NULL, 1, 'thumbResX', '160' ),
( NULL, 1, 'thumbResY', '90' );

-- Create the users list

INSERT INTO `assets` ( `assetId`, `assetName`, `typeId`, `parentId`, `position` ) VALUES ( 2, 'users', 2, 1, 1 );

INSERT INTO `attributes` ( `attrId`, `assetId`, `attrName`, `attrVal` ) VALUES 
( NULL, 2, 'fullName', 'Users List' ),
( NULL, 2, 'description', 'The folder of all users in your site.' ),
( NULL, 2, 'createdOn', '1227570861' ),
( NULL, 2, 'createdBy', 'bcipriano' ),
( NULL, 2, 'memberTypes', 'user' );

-- Create the scripts list

INSERT INTO `assets` ( `assetId`, `assetName`, `typeId`, `parentId`, `position` ) VALUES ( 3, 'scripts', 2, 1, 2 );

INSERT INTO `attributes` ( `attrId`, `assetId`, `attrName`, `attrVal` ) VALUES 
( NULL, 3, 'fullName', 'Scripts List' ),
( NULL, 3, 'description', 'The folder of all your site-wide scripts.' ),
( NULL, 3, 'createdOn', '1227570861' ),
( NULL, 3, 'createdBy', 'bcipriano' ),
( NULL, 3, 'memberTypes', 'script' );

-- Create the machines list

INSERT INTO `assets` ( `assetId`, `assetName`, `typeId`, `parentId`, `position` ) VALUES ( 4, 'machines', 2, 1, 3 );

INSERT INTO `attributes` ( `attrId`, `assetId`, `attrName`, `attrVal` ) VALUES 
( NULL, 4, 'fullName', 'Machines List' ),
( NULL, 4, 'description', 'The folder of all machines in your site.' ),
( NULL, 4, 'createdOn', '1227570861' ),
( NULL, 4, 'createdBy', 'bcipriano' ),
( NULL, 4, 'memberTypes', 'machine' );

-- Create the guest and bcipriano users

INSERT INTO `assets` ( `assetId`, `assetName`, `typeId`, `parentId`, `position` ) VALUES
( 5, 'guest', 5, 2, 1 ),
( 6, 'bcipriano', 5, 2, 2 );

INSERT INTO `attributes` ( `attrId`, `assetId`, `attrName`, `attrVal` ) VALUES 
( NULL, 5, 'fullName', 'Guest User' ),
( NULL, 5, 'createdOn', '1227570861' ),
( NULL, 5, 'createdBy', 'bcipriano' ),
( NULL, 5, 'homeAsset', '/' ),
( NULL, 5, 'layout', '({tabs:[{name:"Welcome"}]})' ),
( NULL, 6, 'fullName', 'Brian Cipriano' ),
( NULL, 6, 'createdOn', '1227570861' ),
( NULL, 6, 'createdBy', 'bcipriano' ),
( NULL, 6, 'homeAsset', '/' ),
( NULL, 6, 'layout', '({tabs:[{name:"AssetManager"}]})' );

-- Give our users appropriate permissions

INSERT INTO `permissions` ( `permId`, `userId`, `assetId`, `permLevel` ) VALUES
( NULL, 5, 1, 'basic' ),
( NULL, 6, 1, 'admin' );
