#
# ptreal.sql Database Schema
#   actualizada por LML (19 Abril 2006)
#   actualizada por LML (19 Abril 2006)
#
# Schema para a criação da base de dados 
# aquando de uma nova instalação da ptreal.
#

#
# TuDO
#
#

# 
# Table structure for table `Company`
# 

CREATE TABLE `company` (
  `id` int(10) NOT NULL auto_increment,
  `module` int(10) NOT NULL default '0',
  `name` varchar(100) default '',
  `phone1` varchar(30) default '',
  `phone2` varchar(30) default '',
  `fax` varchar(30) default '',
  `address1` varchar(50) default '',
  `address2` varchar(50) default '',
  `city` varchar(30) default '',
  `state` varchar(30) default '',
  `zip` varchar(11) default '',
  `primaryUrl` varchar(255) default '',
  `owner` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  `type` int(3) NOT NULL default '0',
  `email` varchar(255) default NULL,
  `custom` longtext,
  PRIMARY KEY  (`id`),
  KEY `idx_cpy1` (`owner`)
) TYPE=MyISAM;


INSERT INTO `company` (`id`, `module`, `name`, `phone1`, `phone2`, `fax`, `address1`, `address2`, `city`, `state`, `zip`, `primaryUrl`, `owner`, `description`, `type`, `email`, `custom`) VALUES (1, 0, 'Trivus', '', '', '', '', '', '', '', '', '', 1, '', 5, NULL, NULL);


CREATE TABLE `contact` (
  `id` int(11) NOT NULL auto_increment,
  `firstName` varchar(30) default NULL,
  `lastName` varchar(30) default NULL,
  `orderBy` varchar(30) NOT NULL default '',
  `title` varchar(50) default NULL,
  `birthday` date default NULL,
  `job` varchar(255) default NULL,
  `company` varchar(100) NOT NULL default '',
  `department` tinytext,
  `type` varchar(20) default NULL,
  `email` varchar(255) default NULL,
  `email2` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `phone` varchar(30) default NULL,
  `phone2` varchar(30) default NULL,
  `fax` varchar(30) default NULL,
  `mobile` varchar(30) default NULL,
  `address1` varchar(60) default NULL,
  `address2` varchar(60) default NULL,
  `city` varchar(30) default NULL,
  `state` varchar(30) default NULL,
  `zip` varchar(11) default NULL,
  `country` varchar(30) default NULL,
  `jabber` varchar(255) default NULL,
  `icq` varchar(20) default NULL,
  `msn` varchar(255) default NULL,
  `yahoo` varchar(255) default NULL,
  `aol` varchar(30) default NULL,
  `notes` text,
  `icon` varchar(20) default 'obj/contact',
  `owner` int(10) unsigned default '0',
  `private` tinyint(3) unsigned default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_oby` (`orderBy`),
  KEY `idx_co` (`company`)
) ENGINE=MyISAM;


CREATE TABLE `permissions` (
  `id` int(11) NOT NULL auto_increment,
  `permission_user` int(11) NOT NULL default '0',
  `permission_grant_on` varchar(12) NOT NULL default '',
  `permission_item` int(11) NOT NULL default '0',
  `permission_value` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_pgrant_on` (`permission_grant_on`,`permission_item`,`permission_user`),
  KEY `idx_puser` (`permission_user`),
  KEY `idx_pvalue` (`permission_value`)
) TYPE=MyISAM;

# 
# Estrutura da tabela `user`
# 

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `contactId` int(11) NOT NULL default '0',
  `username` varchar(255) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `parent` int(11) NOT NULL default '0',
  `type` tinyint(3) NOT NULL default '0',
  `companyId` int(11) default '0',
  `owner` int(11) NOT NULL default '0',
  `signature` text,
  PRIMARY KEY  (`id`),
  KEY `idx_uid` (`username`),
  KEY `idx_pwd` (`password`),
  KEY `idx_user_parent` (`parent`)
) TYPE=MyISAM;


CREATE TABLE `user_preferences` (
  `pref_user` varchar(12) NOT NULL default '',
  `pref_name` varchar(72) NOT NULL default '',
  `pref_value` varchar(32) NOT NULL default '',
  KEY `pref_user` (`pref_user`,`pref_name`)
) TYPE=MyISAM;


#INSERT INTO `user` (`id`, `contactId`, `username`, `password`, `parent`, `type`, `companyId`, `owner`, `signature`) VALUES (1, 1, 'admin',MD5('passwd'), 0, 1, 0, 0, '');
#INSERT INTO `contact` (`id`, `firstName`, `lastName`, `orderBy`, `title`, `birthday`, `job`, `company`, `department`, `type`, `email`, `email2`, `url`, `phone`, `phone2`, `fax`, `mobile`, `address1`, `address2`, `city`, `state`, `zip`, `country`, `jabber`, `icq`, `msn`, `yahoo`, `aol`, `notes`, `icon`, `owner`, `private`) VALUES (1, 'Luciano', 'Letra', '', NULL, NULL, NULL, '', NULL, NULL, 'admin@localhost', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'obj/contact', 0, 0);


INSERT INTO `permissions` VALUES (1,1,"all",-1, -1);

INSERT INTO `user_preferences` VALUES("0", "LOCALE", "pt");
INSERT INTO `user_preferences` VALUES("0", "SHDATEFORMAT", "%d/%m/%Y");
INSERT INTO `user_preferences` VALUES("0", "TIMEFORMAT", "%I:%M %p");
INSERT INTO `user_preferences` VALUES("0", "UISTYLE", "default");


CREATE TABLE `user_roles` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `role_id` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


#
# Table structure for table 'modules'
#
#DROP TABLE module;
CREATE TABLE `module` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `directory` varchar(64) NOT NULL default '',
  `version` varchar(10) NOT NULL default '',
  `setup` varchar(64) NOT NULL default '',
  `type` varchar(64) NOT NULL default '',
  `active` int(1) unsigned NOT NULL default '0',
  `uiActive` int(1) unsigned NOT NULL default '0',
  `uiName` varchar(20) NOT NULL default '',
  `icon` varchar(64) NOT NULL default '',
  `ordered` tinyint(3) NOT NULL default '0',
  `position` varchar(5) default 'left',
  `description` varchar(255) NOT NULL default '',
  `frame` tinyint(1) default '0',
  PRIMARY KEY  (`id`,`directory`)
) TYPE=MyISAM;


INSERT INTO `module` VALUES("1", "User Administration", "user", "1.0.0", "", "core", 1,"0", "User Admin", "helix-setup-users.png", "1", "","",0);
INSERT INTO `module` VALUES("2", "System Administration", "system", "1.0.0", "", "core", 1,"0", "System Admin", "48_my_computer.png", "2", "", "",0);
INSERT INTO `module` VALUES(5, 'Help', 'help', '1.00', 'Setup.php', 'core', 0, 0, 'Help', '', 5, '', '',0);


#Added user access log
CREATE TABLE `user_access_log` (
`user_access_log_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_id` INT( 10 ) UNSIGNED NOT NULL ,
`user_ip` VARCHAR( 15 ) NOT NULL ,
`date_time_in` DATETIME DEFAULT '0000-00-00 00:00:00',
`date_time_out` DATETIME DEFAULT '0000-00-00 00:00:00',
`date_time_last_action` DATETIME DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ( `user_access_log_id` )
) TYPE = MyISAM;



CREATE TABLE `config` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `value` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `nameConfig` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=47 ;



INSERT INTO `config` VALUES ('', 'local', 'pt', 'text');
INSERT INTO `config` VALUES ('', 'style', 'default', 'text');
INSERT INTO `config` VALUES ('', 'empresa', 'Trivus', 'text');
INSERT INTO `config` VALUES ('', 'title', 'PTREAL', 'text');
INSERT INTO `config` VALUES ('', 'site', 'trivus-si.com', 'text');
INSERT INTO `config` VALUES ('', 'email_prefix', '[trivus-si]', 'text');
INSERT INTO `config` VALUES ('', 'admin_username', 'admin', 'text');
INSERT INTO `config` VALUES ('', 'username_min_len', '4', 'text');
INSERT INTO `config` VALUES ('', 'password_min_len', '4', 'text');
INSERT INTO `config` VALUES ('', 'log_changes', 'false', 'checkbox');
INSERT INTO `config` VALUES ('', 'locale_warn', 'false', 'checkbox');
INSERT INTO `config` VALUES ('', 'numero_menus', '4', 'text');
INSERT INTO `config` VALUES ('', 'logo', 'logo.jpg', 'text');
INSERT INTO `config` VALUES ('', 'numero_submenus', '4', 'text');
INSERT INTO `config` VALUES ('', 'default_m', 'destaques', 'text');
INSERT INTO `config` VALUES ('', 'default_a', 'addedit', 'text');
INSERT INTO `config` VALUES ('', 'default_ma', 'system', 'text');
INSERT INTO `config` VALUES ('', 'default_aa', 'index', 'text');

-- 
-- Table structure for table `syskeys`
-- 

CREATE TABLE `syskeys` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(48) NOT NULL default '',
  `label` varchar(255) NOT NULL default '',
  `type` int(1) unsigned NOT NULL default '0',
  `sep1` char(2) default '\n',
  `sep2` char(2) NOT NULL default '|',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `idx_syskey_name` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `syskeys`
-- 

INSERT INTO `syskeys` (`id`, `name`, `label`, `type`, `sep1`, `sep2`) VALUES (1, 'SelectList', 'Enter values for list', 0, '\n', '|');
INSERT INTO `syskeys` (`id`, `name`, `label`, `type`, `sep1`, `sep2`) VALUES (2, 'CustomField', 'Serialized array in the following format:\r\n<KEY>|<SERIALIZED ARRAY>\r\n\r\nSerialized Array:\r\n[type] => text | checkbox | select | textarea | label\r\n[name] => <Field''s name>\r\n[options] => <html capture options>\r\n[selects] => <options for select and checkbox>', 0, '\n', '|');
INSERT INTO `syskeys` (`id`, `name`, `label`, `type`, `sep1`, `sep2`) VALUES (3, 'ColorSelection', 'Hex color values for type=>color association.', 0, '\n', '|');

-- 
-- Table structure for table `sysvals`
-- 

CREATE TABLE `sysvals` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `keyId` int(10) unsigned NOT NULL default '0',
  `title` varchar(48) NOT NULL default '',
  `value` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;

-- 
-- Dumping data for table `sysvals`
-- 

INSERT INTO `sysvals` (`id`, `keyId`, `title`, `value`) VALUES (1, 1, 'FileType', '0|Unknown\n1|Document\n2|Application');