CREATE DATABASE IF NOT EXISTS hnd;
USE hnd;

--
-- Definition of table `address`
--

CREATE TABLE `address` (
  `addressId` bigint(16) NOT NULL AUTO_INCREMENT,
  `address1` varchar(500) DEFAULT NULL,
  `address2` varchar(500) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `stateProvince` varchar(250) DEFAULT NULL,
  `zipCode` varchar(30) DEFAULT NULL,
  `country` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`addressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `address`
--



--
-- Definition of table `businessaccount`
--

CREATE TABLE `businessaccount` (
  `businessAccountId` bigint(16) NOT NULL AUTO_INCREMENT,
  `companyName` varchar(250) DEFAULT NULL,
  `address1` varchar(250) DEFAULT NULL,
  `address2` varchar(250) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `stateProvince` varchar(250) DEFAULT NULL,
  `zipCode` varchar(15) DEFAULT NULL,
  `country` varchar(250) DEFAULT NULL,
  `licenseNumber` varchar(200) DEFAULT NULL,
  `imageFilename` varchar(100) DEFAULT NULL,
  `phoneNumber` TEXT DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  PRIMARY KEY (`businessAccountId`)
) ENGINE=InnoDB AUTO_INCREMENT=144 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `businessaccount`
--

INSERT INTO `businessaccount` (`businessAccountId`,`companyName`,`address1`,`address2`,`city`,`stateProvince`,`zipCode`,`country`,`licenseNumber`,`imageFilename`,`phoneNumber`,`createdOn`) VALUES 
 (142,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);




--
-- Definition of table `email`
--

CREATE TABLE `email` (
  `emailId` bigint(16) NOT NULL AUTO_INCREMENT,
  `sender` varchar(256) NOT NULL,
  `recipients` longtext NOT NULL,
  `subject` varchar(512) NOT NULL,
  `message` longtext NOT NULL,
  `created` datetime NOT NULL,
  `plainMessage` longtext,
  PRIMARY KEY (`emailId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `email`
--



--
-- Definition of table `emailqueue`
--

CREATE TABLE `emailqueue` (
  `emailQueueId` bigint(16) NOT NULL AUTO_INCREMENT,
  `emailId` bigint(16) NOT NULL,
  `status` varchar(128) DEFAULT NULL,
  `priority` varchar(128) DEFAULT NULL,
  `retry` int(4) DEFAULT NULL,
  `statusDescription` varchar(10000) DEFAULT NULL,
  `created` datetime NOT NULL,
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`emailQueueId`),
  KEY `idx_EmailQueue_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `emailqueue`
--



--
-- Definition of table `emailtemplate`
--

CREATE TABLE `emailtemplate` (
  `emailTemplateId` bigint(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `subject` varchar(512) NOT NULL,
  `message` longtext NOT NULL,
  `lang` varchar(5) NOT NULL,
  `plainMessage` longtext,
  PRIMARY KEY (`emailTemplateId`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `emailtemplate`
--

INSERT INTO `emailtemplate` (`emailTemplateId`,`name`,`subject`,`message`,`lang`,`plainMessage`) VALUES 
 (1,'user.password.reset','${profile[''site.name''].value}: Request to reset password','<#setting url_escaping_charset=''ISO-8859-1''>To reset your ${profile[''site.name''].value} password please click the link below. <a href="${path}/#/forgot-password/${user.email?url}/${user.resetPasswordToken}">The link</a> will expire within ${reset_hours_valid} <#if (reset_hours_valid>1)>hours<#else>hour</#if>.<br><a href="${path}/#/forgot-password/${user.email?url}/${user.resetPasswordToken}">${path}/#/forgot-password/${user.email?url}/${user.resetPasswordToken}</a>','en',''),
 (6,'user.password.create','${profile[''site.name''].value} - Verify Your Email','<#setting url_escaping_charset=''ISO-8859-1''>Thank you for signing up for ${profile[''site.name''].value}! Click here to get started:<br><a href="${path}/#/register/${user.email?url}/${user.resetPasswordToken}">${path}/#/register/${user.email?url}/${user.resetPasswordToken}</a>','en',''),
 (8,'user.invite','${profile[''site.name''].value} - Verify Your Email','<#setting url_escaping_charset=''ISO-8859-1''>Thank you for signing up for ${profile[''site.name''].value}! Please click the list to verify your email and set a password:<br><a href="${path}/#/register/${user.email?url}/${user.resetPasswordToken}">${path}/#/register/${user.email?url}/${user.resetPasswordToken}</a>','en','');


--
-- Definition of table `permission`
--

CREATE TABLE `permission` (
  `permissionId` bigint(16) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`permissionId`),
  UNIQUE KEY `code_UNIQUE` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `permission`
--

INSERT INTO `permission` (`permissionId`,`code`,`description`) VALUES 
 (1,'usr:*',NULL),
 (2,'usr:login',NULL),
 (3,'usr:self:edit',NULL),
 (4,'usr:edit',NULL),
 (5,'ba:*',NULL),
 (6,'ba:self:view',NULL),
 (7,'ba:self:edit',NULL),
 (13,'usr:create',NULL),
 (14,'usr:del',NULL),
 (15,'usr:view',NULL);



--
-- Definition of table `profile`
--

CREATE TABLE `profile` (
  `profileId` bigint(16) NOT NULL,
  `name` varchar(80) NOT NULL,
  `value` mediumtext,
  `category` varchar(15) DEFAULT NULL,
  `worksFrom` datetime DEFAULT NULL,
  `worksTill` datetime DEFAULT NULL,
  `description` varchar(200) DEFAULT NULL,
  `scope` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`profileId`),
  KEY `idx_Profile_works` (`worksFrom`,`worksTill`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `profile`
--

INSERT INTO `profile` (`profileId`,`name`,`value`,`category`,`worksFrom`,`worksTill`,`description`,`scope`) VALUES 
 (1,'user.password.hash.log.rounds','10','USER',NULL,NULL,'Password hash: 2**X iterations',0),
 (2,'admin.email','alexgul@swdfactory.com','SITE',NULL,NULL,'Admin\'s email',0),
 (3,'site.name','Handelion','SITE',NULL,NULL,'Site\'s name',1),
 (4,'site.language','en','SITE',NULL,NULL,'Site\'s default language',1),
 (5,'site.url','localhost:8180/Handelion','SITE',NULL,NULL,'Site URL',1),
 (6,'system.timer.email.schedule.expression.cron','{\"minute\":\"0/5\",\"hour\":\"*\"}','SYSTEM',NULL,NULL,'JSON Format: {\"second\":\"XXX\",\"minute\":\"XXX\",\"hour\":\"XXX\",\"dayOfWeek\":\"XXX\",\"dayOfMonth\":\"XXX\",\"month\":\"XXX\",\"year\":\"XXX\"} ',0),
 (7,'system.application.mode','STG','SYSTEM',NULL,NULL,'Application type: [DEVELOPMENT,QA,STG,PRODUCTION]',0),
 (8,'system.email.address','Handelion Support <alexgul@swdfactory.com>','SYSTEM',NULL,NULL,'System email address',0),
 (9,'accessKey','XXX','S3',NULL,NULL,'S3 access key',0),
 (10,'secretKey','YYY','S3',NULL,NULL,'S3 secret key',0),
 (11,'bucket','clinical-validator-dev','S3',NULL,NULL,'S3 bucket name',0),
 (12,'maximalFileSizeLimitation','10485760','S3',NULL,NULL,'S3 file size limit (bytes)',0),
 (13,'region','us-east-1','S3',NULL,NULL,'S3 region',0),
 (14,'user.reset.password.length','12','USER',NULL,NULL,'Reset password length',0),
 (15,'user.reset.password.chars.type','ASCII','USER',NULL,NULL,'Reset password characters type[ASCII, ALPHABETIC, ALPHANUMERIC, NUMERIC]',0),
 (16,'user.reset.password.expiration.hours','24','USER',NULL,NULL,'Reset password expiration in hours',0),
 (17,'user.reset.password.token.length','50','USER',NULL,NULL,'Reset password token length',0),
 (18,'user.reset.password.token.chars.type','ASCII','USER',NULL,NULL,'Reset password token characters type[ASCII, ALPHABETIC, ALPHANUMERIC, NUMERIC]',0),
 (19,'user.reset.password.token.expiration.hours','72','USER',NULL,NULL,'Reset password token expiration in hours',0),
 (20,'site.terms.of.use.url','https://handelion.com/software-end-user-license-agreement/','SITE',NULL,NULL,'Site terms of use url',1),
 (21,'site.privacy.policy','https://handelion.com/software-end-user-license-agreement/','SITE',NULL,NULL,'Site privacy policy url',1),
 (22,'site.logo.data','{\"url\":\"images/hnd_logo.png\",\"hrefUrl\":\"#/layout\",\"hrefTarget\":\"_self\"}','SITE',NULL,NULL,'Site logo data(JSON)',1),
 (23,'support.email','alexgul@swdfactory.com','SITE',NULL,NULL,'Support email',1),
 (24,'support.phone','(000) 1111-2222','SITE',NULL,NULL,'Support phone',1),
 (25,'system.mask.email','alexgul@swdfactory.com','SYSTEM',NULL,NULL,'System mask email (for dev mode)',0),
 (26,'session.inactivity.timeout','60','SITE',NULL,NULL,'Session inactivity timeout minutes',1),
 (27,'user.password.reset','user.password.reset','USER',NULL,NULL,'Link to user password reset template',1),
 (28,'system.email.address.errors','alexgul@swdfactory.com','SYSTEM',NULL,NULL,'Email addresses for specific errors such as weather not available error, simulation error',0),
 (29,'favicon.name','favicon','SITE',NULL,NULL,'Favicon for Site',1),
 (30,'global.css.class','hnd','SITE',NULL,NULL,'Global CSS Class for Site',1),
 (31,'site.title','Handelion','SITE',NULL,NULL,'Title of site',1),
 (32,'phone.mandatory','true','SITE',NULL,NULL,'Phone field is mandatory',1),
 (33,'user.password.create','user.password.create','USER',NULL,NULL,'User password confirm template',0),
 (34,'user.create.email','','USER',NULL,NULL,'User create confirmation email(rewrite registered email)',0),
 (35,'site.help.url','http://handelion.zendesk.com/','SITE',NULL,NULL,'Site Help link url',1),
 (36,'site.support.chat.type','zendesk','SITE',NULL,NULL,'Site Support chat service type',1),
 (37,'system.timer.data.cleaner.schedule.expression.cron','{\"minute\":\"0\",\"hour\":\"*/3\"}','SYSTEM',NULL,NULL,'JSON Format: {\"second\":\"XXX\",\"minute\":\"XXX\",\"hour\":\"XXX\",\"dayOfWeek\":\"XXX\",\"dayOfMonth\":\"XXX\",\"month\":\"XXX\",\"year\":\"XXX\"} ',0),
 (38,'data.cleaner.user.reg.not.confirmed.days.back','2','SYSTEM',NULL,NULL,'1=Delete all data older than today, 2=older than yesterday etc',0),
 (39,'login.failed.attempts.before.block','5','USER',NULL,NULL,'Max failed login attempts before blocking user',0);



--
-- Definition of table `role`
--

CREATE TABLE `role` (
  `roleId` bigint(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `isPublic` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`roleId`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `role`
--

INSERT INTO `role` (`roleId`,`name`,`description`,`isPublic`) VALUES 
 (1,'Admin','System Administrator',0),
 (2,'Investor','Investor',1),
 (3,'TBO','TBO',0),
 (4,'Fund','Fund/Stock',0);


--
-- Definition of table `rolepermission`
--

CREATE TABLE `rolepermission` (
  `rolePermissionId` bigint(16) NOT NULL AUTO_INCREMENT,
  `roleId` bigint(16) NOT NULL,
  `permissionId` bigint(16) NOT NULL,
  PRIMARY KEY (`rolePermissionId`),
  KEY `roleId` (`roleId`),
  KEY `permissionId` (`permissionId`),
  CONSTRAINT `RolePermission_ibfk_1` FOREIGN KEY (`roleId`) REFERENCES `role` (`roleId`),
  CONSTRAINT `RolePermission_ibfk_2` FOREIGN KEY (`permissionId`) REFERENCES `permission` (`permissionId`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `rolepermission`
--

INSERT INTO `rolepermission` (`rolePermissionId`,`roleId`,`permissionId`) VALUES 
 (1,1,1),
 (34,2,7),
 (35,2,6),
 (44,2,4),
 (45,2,2),
 (46,2,3),
 (47,2,15),
 (101,2,13),
 (102,3,2),
 (103,3,3);



--
-- Definition of table `timezone`
--

CREATE TABLE `timezone` (
  `timeZoneId` bigint(16) NOT NULL AUTO_INCREMENT,
  `displayName` varchar(200) NOT NULL,
  `gmtName` varchar(100) DEFAULT NULL,
  `utcOffset` int(11) NOT NULL,
  `norder` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`timeZoneId`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `timezone`
--

INSERT INTO `timezone` (`timeZoneId`,`displayName`,`gmtName`,`utcOffset`,`norder`) VALUES 
 (1,'Atlantic Time Zone','GMT-4',-4,1),
 (2,'Eastern Time Zone','GMT-5',-5,1),
 (3,'Central Time Zone','GMT-6',-6,1),
 (4,'Mountain Time Zone','GMT-7',-7,1),
 (5,'Pacific Time Zone','GMT-8',-8,1),
 (6,'Alaska Time Zone','GMT-9',-9,1),
 (7,'Hawaii-Aleutian Time Zone','GMT-10',-10,1),
 (8,'Samoa Time Zone','GMT-11',-11,1),
 (9,'Chamorro Time Zone','GMT+10',10,1);


--
-- Definition of table `useraccount`
--

CREATE TABLE `useraccount` (
  `userAccountId` bigint(16) NOT NULL AUTO_INCREMENT,
  `userName` varchar(250) NOT NULL,
  `firstName` TEXT DEFAULT NULL,
  `lastName` TEXT DEFAULT NULL,
  `displayName` varchar(100) DEFAULT NULL,
  `phoneNumber` TEXT DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `roleId` bigint(16),
  `businessAccountId` bigint(16) NOT NULL,
  `password` varchar(250) NOT NULL,
  `isEnabled` tinyint(1) NOT NULL DEFAULT '0',
  `isConfirmed` tinyint(1) NOT NULL DEFAULT '0',
  `timeZoneId` bigint(16) DEFAULT NULL,
  `passwordExpiration` datetime DEFAULT NULL,
  `resetPasswordToken` varchar(512) DEFAULT NULL,
  `resetPasswordTokenExpiration` datetime DEFAULT NULL,
  `regStatus` varchar(30) DEFAULT 'APPROVED',
  `regStatusChangedOn` datetime DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  `language` varchar(5) DEFAULT 'en',
  `loginAttempts` bigint(16) DEFAULT 0,
  PRIMARY KEY (`userAccountId`),
  UNIQUE KEY `userName_UNIQUE` (`userName`),
  KEY `roleId` (`roleId`),
  KEY `businessAccountId` (`businessAccountId`),
  CONSTRAINT `UserAccount_ibfk_1` FOREIGN KEY (`roleId`) REFERENCES `role` (`roleId`),
  CONSTRAINT `UserAccount_ibfk_2` FOREIGN KEY (`businessAccountId`) REFERENCES `businessaccount` (`businessAccountId`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `useraccount`
--

INSERT INTO `useraccount` (`userAccountId`,`userName`,`firstName`,`lastName`,`displayName`,`phoneNumber`,`email`,`roleId`,`businessAccountId`,`password`,`isEnabled`,`isConfirmed`,`timeZoneId`,`passwordExpiration`,`resetPasswordToken`,`resetPasswordTokenExpiration`,`regStatus`,`regStatusChangedOn`,`createdOn`,`lastLogin`,`language`,`loginAttempts`) VALUES 
 (129,'alexgul@swdfactory.com','C6AI9oWbMmhzwr82JlgzfA==','VE/F2pM94chvP5zWRZdF5w==','alexgul@swdfactory.com','aCNlHivQTwXafGE9KrxwMA==','alexgul@swdfactory.com',1,142,'$2a$10$ydoTtjSXwxYOJBwPKSLhSubbjk5dyJFtUrVOs8bpiqKzaueaA05nO',true,true,null,null,null,null,'APPROVED',null,null,{ts '2018-07-03 15:18:18'},'en',0);



--
-- Definition of table `useraccountstatistics`
--

CREATE TABLE `useraccountstatistics` (
  `userAccountStatisticsId` bigint(16) NOT NULL AUTO_INCREMENT,
  `userAccountId` bigint(16) NOT NULL,
  `businessAccountId` bigint(16) NOT NULL,
  `operation` varchar(30) NOT NULL,
  `counter` bigint(16) NOT NULL DEFAULT '1',
  `lastOperation` datetime NOT NULL,
  PRIMARY KEY (`userAccountStatisticsId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `audit` (
  `auditId` bigint(16) NOT NULL AUTO_INCREMENT,
  `entityId` bigint(16),
  `entityType` varchar(100) NOT NULL,
  `creatorId` bigint(16),
  `createdOn` datetime NOT NULL,
  `action` varchar(255) NOT NULL,
  PRIMARY KEY (`auditId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


CREATE TABLE `useraccountrole` (
  `userAccountRoleId` bigint(16) NOT NULL AUTO_INCREMENT,
  `userAccountId` bigint(16) NOT NULL,
  `roleId` bigint(16) NOT NULL,
  PRIMARY KEY (`userAccountRoleId`),
  KEY `userAccountId` (`userAccountId`),
  KEY `roleId` (`roleId`),
  CONSTRAINT `UserRole_ibfk_2` FOREIGN KEY (`userAccountId`) REFERENCES `useraccount` (`userAccountId`),
  CONSTRAINT `UserRole_ibfk_1` FOREIGN KEY (`roleId`) REFERENCES `role` (`roleId`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;

INSERT INTO `useraccountrole` (userAccountRoleId,userAccountId,roleId) VALUES (102,129,1);

CREATE TABLE `wallet` (
  `walletId` bigint(16) NOT NULL AUTO_INCREMENT,
  `businessAccountId` bigint(16) NOT NULL,
  `eAddress` TEXT NOT NULL,
  `key` TEXT NOT NULL,
  `createdOn` datetime DEFAULT NULL,
  PRIMARY KEY (`walletId`),
  CONSTRAINT `BusinessAccount_1` FOREIGN KEY (`businessAccountId`) REFERENCES `businessaccount` (`businessAccountId`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;

commit;