-- phpMyAdmin SQL Dump
-- version 3.2.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 21, 2011 at 11:15 AM
-- Server version: 5.1.44
-- PHP Version: 5.3.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `erp_dev`
--

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

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

CREATE TABLE `attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `file_id` int(11) NOT NULL COMMENT 'file that is being attached',
  `type` enum('comment','message','project-todo') NOT NULL COMMENT 'type of thread that the attachment is for',
  `associate_id` bigint(20) NOT NULL COMMENT 'id of the item that the file is being attached to',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='file attachments for messages, comments, etc' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `attachments`
--


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

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `user_id` int(11) NOT NULL COMMENT 'user who posted the comment',
  `type` enum('message','event','todo') NOT NULL COMMENT 'type of thread that the comment is posted to',
  `associate_id` int(11) NOT NULL COMMENT 'id of the item that the comment is associated to',
  `content` text COMMENT 'content of the comment',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='comments for different threads' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `comments`
--


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

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `type` enum('project-milestone','project-event','group-event-public','group-event-private','user-specific') NOT NULL COMMENT 'type of event, whether it is a project milestone, etc',
  `associate_id` int(11) NOT NULL COMMENT 'id of the thread that the event is associated with',
  `title` varchar(256) NOT NULL COMMENT 'title of the event',
  `description` text COMMENT 'description of the event',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'last update of the event',
  `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when the event start',
  `end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when the event ends',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='different events for the site' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `events`
--


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

--
-- Table structure for table `files`
--

CREATE TABLE `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `root_group_id` int(11) NOT NULL COMMENT 'root group that this file belongs to',
  `user_id` int(11) NOT NULL COMMENT 'user who uploaded this file',
  `path` varchar(256) NOT NULL COMMENT 'path of the directory structure',
  `name` varchar(256) NOT NULL COMMENT 'name of the file',
  `location` varchar(256) NOT NULL COMMENT 'location of the file',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'last time the file was updated',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='files saved on the site' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `files`
--


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

--
-- Table structure for table `folders`
--

CREATE TABLE `folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `root_group_id` int(11) NOT NULL COMMENT 'root group that this folder belongs to',
  `user_id` int(11) NOT NULL COMMENT 'user who created this folder',
  `path` varchar(256) NOT NULL COMMENT 'path of the directory structure',
  `name` varchar(256) NOT NULL COMMENT 'name of the folder',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when the folder was last updated',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='folder for the file management system' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `folders`
--


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

--
-- Table structure for table `groups`
--

CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the group',
  `type` enum('company','department','group') NOT NULL DEFAULT 'group' COMMENT 'type of group',
  `path` varchar(128) NOT NULL DEFAULT '/' COMMENT 'path of parent groups',
  `name` varchar(128) NOT NULL COMMENT 'name of the group',
  `description` text COMMENT 'description of the group',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when the group was last updated',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'when the group was created',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='general groups of users' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `groups`
--


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

--
-- Table structure for table `group_domains`
--

CREATE TABLE `group_domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL COMMENT 'group id',
  `domain` varchar(128) NOT NULL COMMENT 'domain name',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`),
  UNIQUE KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='domain or subdomains for companies' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `group_domains`
--


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

--
-- Table structure for table `group_roles`
--

CREATE TABLE `group_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the role',
  `group_id` int(11) NOT NULL COMMENT 'group id',
  `name` varchar(128) NOT NULL COMMENT 'name of the role',
  `description` text COMMENT 'description of the role',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='different roles within organizations' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `group_roles`
--


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

--
-- Table structure for table `group_role_auths`
--

CREATE TABLE `group_role_auths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `auth_action` varchar(128) NOT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='group roles action auths table' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `group_role_auths`
--


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

--
-- Table structure for table `group_users`
--

CREATE TABLE `group_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `group_id` int(11) NOT NULL COMMENT 'group id',
  `user_id` int(11) NOT NULL COMMENT 'user id',
  `role_id` int(11) NOT NULL COMMENT 'position id',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='group user relations table' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `group_users`
--


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

--
-- Table structure for table `product_attributes`
--

CREATE TABLE `product_attributes` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT COMMENT 'attribute id',
  `attr_column_name` varchar(8) NOT NULL COMMENT 'attribute column name as reflected in product attribute table',
  `name` varchar(128) NOT NULL COMMENT 'name of the attribute',
  `description` text COMMENT 'a description of the attribute',
  PRIMARY KEY (`id`),
  KEY `attr_column_name` (`attr_column_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='all product attributes' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `product_attributes`
--

INSERT INTO `product_attributes` VALUES(0, 'attr_000', 'default', NULL);

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

--
-- Table structure for table `product_attributes_01`
--

CREATE TABLE `product_attributes_01` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'product attribute id',
  `product_type_id` mediumint(9) NOT NULL COMMENT 'product type that this set of attribute belongs to',
  `attr_001` smallint(6) NOT NULL DEFAULT '0',
  `attr_002` smallint(6) NOT NULL DEFAULT '0',
  `attr_003` smallint(6) NOT NULL DEFAULT '0',
  `attr_004` smallint(6) NOT NULL DEFAULT '0',
  `attr_005` smallint(6) NOT NULL DEFAULT '0',
  `attr_006` smallint(6) NOT NULL DEFAULT '0',
  `attr_007` smallint(6) NOT NULL DEFAULT '0',
  `attr_008` smallint(6) NOT NULL DEFAULT '0',
  `attr_009` smallint(6) NOT NULL DEFAULT '0',
  `attr_010` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `product_type_id` (`product_type_id`),
  KEY `attr_001` (`attr_001`),
  KEY `attr_002` (`attr_002`),
  KEY `attr_003` (`attr_003`),
  KEY `attr_004` (`attr_004`),
  KEY `attr_005` (`attr_005`),
  KEY `attr_006` (`attr_006`),
  KEY `attr_007` (`attr_007`),
  KEY `attr_008` (`attr_008`),
  KEY `attr_009` (`attr_009`),
  KEY `attr_010` (`attr_010`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a set of attribute values for a product type' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `product_attributes_01`
--


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

--
-- Table structure for table `product_attributes_02`
--

CREATE TABLE `product_attributes_02` (
  `product_attribute_id` int(11) NOT NULL COMMENT 'product attribute id',
  `attr_011` smallint(6) NOT NULL DEFAULT '0',
  `attr_012` smallint(6) NOT NULL DEFAULT '0',
  `attr_013` smallint(6) NOT NULL DEFAULT '0',
  `attr_014` smallint(6) NOT NULL DEFAULT '0',
  `attr_015` smallint(6) NOT NULL DEFAULT '0',
  `attr_016` smallint(6) NOT NULL DEFAULT '0',
  `attr_017` smallint(6) NOT NULL DEFAULT '0',
  `attr_018` smallint(6) NOT NULL DEFAULT '0',
  `attr_019` smallint(6) NOT NULL DEFAULT '0',
  `attr_020` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_attribute_id`),
  KEY `attr_011` (`attr_011`),
  KEY `attr_012` (`attr_012`),
  KEY `attr_013` (`attr_013`),
  KEY `attr_014` (`attr_014`),
  KEY `attr_015` (`attr_015`),
  KEY `attr_016` (`attr_016`),
  KEY `attr_017` (`attr_017`),
  KEY `attr_018` (`attr_018`),
  KEY `attr_019` (`attr_019`),
  KEY `attr_020` (`attr_020`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a set of attribute values for a product type';

--
-- Dumping data for table `product_attributes_02`
--


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

--
-- Table structure for table `product_attributes_03`
--

CREATE TABLE `product_attributes_03` (
  `product_attribute_id` int(11) NOT NULL COMMENT 'product attribute id',
  `attr_021` smallint(6) NOT NULL DEFAULT '0',
  `attr_022` smallint(6) NOT NULL DEFAULT '0',
  `attr_023` smallint(6) NOT NULL DEFAULT '0',
  `attr_024` smallint(6) NOT NULL DEFAULT '0',
  `attr_025` smallint(6) NOT NULL DEFAULT '0',
  `attr_026` smallint(6) NOT NULL DEFAULT '0',
  `attr_027` smallint(6) NOT NULL DEFAULT '0',
  `attr_028` smallint(6) NOT NULL DEFAULT '0',
  `attr_029` smallint(6) NOT NULL DEFAULT '0',
  `attr_030` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_attribute_id`),
  KEY `attr_021` (`attr_021`),
  KEY `attr_022` (`attr_022`),
  KEY `attr_023` (`attr_023`),
  KEY `attr_024` (`attr_024`),
  KEY `attr_025` (`attr_025`),
  KEY `attr_026` (`attr_026`),
  KEY `attr_027` (`attr_027`),
  KEY `attr_028` (`attr_028`),
  KEY `attr_029` (`attr_029`),
  KEY `attr_030` (`attr_030`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a set of attribute values for a product type';

--
-- Dumping data for table `product_attributes_03`
--


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

--
-- Table structure for table `product_attribute_values`
--

CREATE TABLE `product_attribute_values` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `attr_id` tinyint(4) NOT NULL COMMENT 'attribute id',
  `value` varchar(128) NOT NULL COMMENT 'attribute value',
  `order` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'order in which the values should be presented',
  PRIMARY KEY (`id`),
  KEY `attr_id` (`attr_id`,`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='all attribute values' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `product_attribute_values`
--

INSERT INTO `product_attribute_values` VALUES(0, 0, 'default', 1);

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

--
-- Table structure for table `product_items`
--

CREATE TABLE `product_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'product item id',
  `product_attribute_id` int(11) NOT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'unassigned',
  `location` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `product_attribute_id` (`product_attribute_id`),
  KEY `status` (`status`),
  KEY `location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `product_items`
--


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

--
-- Table structure for table `product_types`
--

CREATE TABLE `product_types` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT 'product type id',
  `snap` varchar(64) NOT NULL DEFAULT 'default' COMMENT 'product image',
  `snap_160` varchar(64) NOT NULL DEFAULT 'default',
  `name` varchar(128) NOT NULL COMMENT 'product name',
  `description` text NOT NULL COMMENT 'product description',
  `date_added` datetime DEFAULT NULL,
  `user_id` int(11) NOT NULL COMMENT 'user who added this product',
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='information on a particular product' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `product_types`
--


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

--
-- Table structure for table `product_type_attribute_values`
--

CREATE TABLE `product_type_attribute_values` (
  `product_type_id` mediumint(9) NOT NULL,
  `attribute_id` tinyint(4) NOT NULL,
  `attribute_value_id` smallint(6) NOT NULL,
  `attribute_value` varchar(128) NOT NULL,
  KEY `product_type_id` (`product_type_id`),
  KEY `attribute_id` (`attribute_id`),
  KEY `attribute_value_id` (`attribute_value_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product_type_attribute_values`
--


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

--
-- Table structure for table `projects`
--

CREATE TABLE `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'project id',
  `path` varchar(128) NOT NULL DEFAULT '/' COMMENT 'path of the project since a project can be another''s to do item',
  `root_group_id` int(11) NOT NULL COMMENT 'the company that this project belongs to',
  `name` varchar(256) NOT NULL COMMENT 'name of the project',
  `description` text COMMENT 'description of the project',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'when the project was created',
  PRIMARY KEY (`id`),
  KEY `path` (`path`),
  KEY `root_group_id` (`root_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='projects for the project management section' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `projects`
--


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

--
-- Table structure for table `project_participants`
--

CREATE TABLE `project_participants` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the row',
  `project_id` int(11) NOT NULL COMMENT 'project id',
  `type` enum('group','user') NOT NULL DEFAULT 'user' COMMENT 'type of participants, could be a group or a single user',
  `group_id` int(11) NOT NULL DEFAULT '0' COMMENT 'group id if the participant is a group',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT 'user id if the participant is a user',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `group_id` (`group_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='participants of a project' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `project_participants`
--


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

--
-- Table structure for table `project_todos`
--

CREATE TABLE `project_todos` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'to do id',
  `project_id` int(11) NOT NULL COMMENT 'project that this todo belongs to',
  `todo_list_id` int(11) NOT NULL DEFAULT '0' COMMENT 'todo list that this todo belongs to',
  `type` enum('individual','project') NOT NULL DEFAULT 'individual' COMMENT 'type of todo, could be another project',
  `assigned_id` int(11) NOT NULL COMMENT 'where this todo is assigned to, another project or a single user',
  `subject` varchar(256) NOT NULL COMMENT 'subject of the todo',
  `description` text NOT NULL COMMENT 'content description of the todo',
  `due_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when the todo is due',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'when the todo was last updated',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'when the todo was assigned',
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `assigned_project_id` (`assigned_id`),
  KEY `todo_list_id` (`todo_list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='project todos ' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `project_todos`
--


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

--
-- Table structure for table `project_todo_lists`
--

CREATE TABLE `project_todo_lists` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'todo list id',
  `project_id` int(11) NOT NULL COMMENT 'project that this list belongs to',
  `name` varchar(256) NOT NULL COMMENT 'name of the list',
  `description` text NOT NULL COMMENT 'description of the list',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'when the list was created',
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='project todo lists' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `project_todo_lists`
--


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

--
-- Table structure for table `stock`
--

CREATE TABLE `stock` (
  `product_type_id` mediumint(9) NOT NULL,
  `product_attribute_id` int(11) NOT NULL,
  `number_in_stock` mediumint(9) NOT NULL DEFAULT '0',
  `warehouse_id` smallint(6) NOT NULL,
  KEY `product_type_id` (`product_type_id`),
  KEY `product_attribute_id` (`product_attribute_id`),
  KEY `warehouse_id` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='current stock holdings';

--
-- Dumping data for table `stock`
--


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

--
-- Table structure for table `stock_in_logs`
--

CREATE TABLE `stock_in_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'log id',
  `warehouse_id` smallint(6) NOT NULL COMMENT 'warehouse id where this product is going into stock',
  `product_attribute_id` int(11) NOT NULL COMMENT 'type of product going into stock',
  `number_of_items` smallint(6) NOT NULL COMMENT 'number of items going into stock',
  `type` varchar(16) NOT NULL,
  `user_id` int(11) NOT NULL COMMENT 'user who entered the product into stock',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `warehouse_id` (`warehouse_id`),
  KEY `product_attribute_id` (`product_attribute_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record items going into stock' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `stock_in_logs`
--


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

--
-- Table structure for table `stock_out_logs`
--

CREATE TABLE `stock_out_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'log id',
  `warehouse_id` smallint(6) NOT NULL COMMENT 'warehouse id where this product is going into stock',
  `product_attribute_id` int(11) NOT NULL COMMENT 'type of product going out of stock',
  `number_of_items` smallint(6) NOT NULL COMMENT 'number of items going out of stock',
  `type` varchar(16) NOT NULL,
  `user_id` int(11) NOT NULL COMMENT 'user who entered the product out of stock',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `warehouse_id` (`warehouse_id`),
  KEY `product_attribute_id` (`product_attribute_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record items going into stock' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `stock_out_logs`
--


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

--
-- Table structure for table `thread_subscribers`
--

CREATE TABLE `thread_subscribers` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id row',
  `type` enum('message','event','project-todo') NOT NULL COMMENT 'what type of thread being subscribed to',
  `associate_id` int(11) NOT NULL COMMENT 'id of the thread being subscribed to',
  `user_id` int(11) NOT NULL COMMENT 'user who subscribed to the thread',
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'time stamp',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='subscribers to message, event, etc' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `thread_subscribers`
--


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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(9) NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `email` varchar(128) NOT NULL,
  `username` varchar(32) NOT NULL COMMENT 'usernanme of the user',
  `password` varchar(32) NOT NULL COMMENT 'password of the user stored in md5 hash',
  `snap` varchar(64) NOT NULL COMMENT 'thumbnail of the user',
  `time_added` datetime NOT NULL COMMENT 'when the user entered the company',
  `last_signin` datetime NOT NULL COMMENT 'when the user last signed on',
  `status` enum('active','left','suspended') NOT NULL DEFAULT 'active' COMMENT 'current status of the user, active, suspended, etc',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_2` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `users`
--


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

--
-- Table structure for table `user_auths`
--

CREATE TABLE `user_auths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL COMMENT 'group which this action is allowed for',
  `auth_action` varchar(32) NOT NULL,
  `auth_type` enum('allow','restrict') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_type_id` (`user_id`,`auth_action`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='action authorized for a user type' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user_auths`
--


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

--
-- Table structure for table `user_types`
--

CREATE TABLE `user_types` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT COMMENT 'id of this user type',
  `name` varchar(128) NOT NULL COMMENT 'title of this type of user',
  `description` text COMMENT 'description of this type of user',
  `time_added` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='different types of users for this company' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user_types`
--


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

--
-- Table structure for table `warehouses`
--

CREATE TABLE `warehouses` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'warehouse id',
  `name` varchar(128) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='a list of all warehouse locations' AUTO_INCREMENT=1 ;

--
-- Dumping data for table `warehouses`
--


--
-- Constraints for dumped tables
--

--
-- Constraints for table `product_attributes_01`
--
ALTER TABLE `product_attributes_01`
  ADD CONSTRAINT `product_attributes_01_ibfk_1` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_12` FOREIGN KEY (`attr_001`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_13` FOREIGN KEY (`attr_002`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_14` FOREIGN KEY (`attr_003`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_15` FOREIGN KEY (`attr_004`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_16` FOREIGN KEY (`attr_005`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_17` FOREIGN KEY (`attr_006`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_18` FOREIGN KEY (`attr_007`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_19` FOREIGN KEY (`attr_008`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_20` FOREIGN KEY (`attr_009`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_01_ibfk_21` FOREIGN KEY (`attr_010`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `product_attributes_02`
--
ALTER TABLE `product_attributes_02`
  ADD CONSTRAINT `product_attributes_02_ibfk_1` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attributes_01` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_12` FOREIGN KEY (`attr_011`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_13` FOREIGN KEY (`attr_012`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_14` FOREIGN KEY (`attr_013`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_15` FOREIGN KEY (`attr_014`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_16` FOREIGN KEY (`attr_015`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_17` FOREIGN KEY (`attr_016`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_18` FOREIGN KEY (`attr_017`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_19` FOREIGN KEY (`attr_018`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_20` FOREIGN KEY (`attr_019`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_02_ibfk_21` FOREIGN KEY (`attr_020`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `product_attributes_03`
--
ALTER TABLE `product_attributes_03`
  ADD CONSTRAINT `product_attributes_03_ibfk_1` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attributes_01` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_12` FOREIGN KEY (`attr_021`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_13` FOREIGN KEY (`attr_022`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_14` FOREIGN KEY (`attr_023`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_15` FOREIGN KEY (`attr_024`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_16` FOREIGN KEY (`attr_025`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_17` FOREIGN KEY (`attr_026`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_18` FOREIGN KEY (`attr_027`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_19` FOREIGN KEY (`attr_028`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_20` FOREIGN KEY (`attr_029`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_attributes_03_ibfk_21` FOREIGN KEY (`attr_030`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `product_attribute_values`
--
ALTER TABLE `product_attribute_values`
  ADD CONSTRAINT `product_attribute_values_ibfk_1` FOREIGN KEY (`attr_id`) REFERENCES `product_attributes` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `product_items`
--
ALTER TABLE `product_items`
  ADD CONSTRAINT `product_items_ibfk_1` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attributes_01` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `product_type_attribute_values`
--
ALTER TABLE `product_type_attribute_values`
  ADD CONSTRAINT `product_type_attribute_values_ibfk_1` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_type_attribute_values_ibfk_4` FOREIGN KEY (`attribute_id`) REFERENCES `product_attributes` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `product_type_attribute_values_ibfk_5` FOREIGN KEY (`attribute_value_id`) REFERENCES `product_attribute_values` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `stock`
--
ALTER TABLE `stock`
  ADD CONSTRAINT `stock_ibfk_1` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `stock_ibfk_2` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attributes_01` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `stock_ibfk_3` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `stock_in_logs`
--
ALTER TABLE `stock_in_logs`
  ADD CONSTRAINT `stock_in_logs_ibfk_1` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `stock_in_logs_ibfk_2` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attributes_01` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `stock_out_logs`
--
ALTER TABLE `stock_out_logs`
  ADD CONSTRAINT `stock_out_logs_ibfk_1` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `stock_out_logs_ibfk_2` FOREIGN KEY (`product_attribute_id`) REFERENCES `product_attributes_01` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `user_auths`
--
ALTER TABLE `user_auths`
  ADD CONSTRAINT `user_auths_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE;