bomstructure

-- MySQL dump 10.13  Distrib 5.5.37, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: bomcloud
-- ------------------------------------------------------
-- Server version	5.5.37-0ubuntu0.14.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account_opening`
--

use bomcloud;

DROP TABLE IF EXISTS `account_opening`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account_opening` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `request_date` datetime DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `middle_name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `salutation` varchar(255) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `date_of_birth` datetime DEFAULT NULL,
  `response_code` varchar(255) DEFAULT NULL,
  `response_message` varchar(255) DEFAULT NULL,
  `is_offline` tinyint(1) DEFAULT '0',
  `response_date` datetime DEFAULT NULL,
  `dsa_id` bigint(20) DEFAULT NULL,
  `device_id` bigint(20) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_account_opening_dsa_1` (`dsa_id`),
  KEY `ix_account_opening_device_2` (`device_id`),
  KEY `ix_account_opening_financialInstitution_3` (`financial_institution_id`),
  CONSTRAINT `fk_account_opening_device_2` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`),
  CONSTRAINT `fk_account_opening_dsa_1` FOREIGN KEY (`dsa_id`) REFERENCES `dsa` (`id`),
  CONSTRAINT `fk_account_opening_financialInstitution_3` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `audit_trail`
--

DROP TABLE IF EXISTS `audit_trail`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `audit_trail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `event` varchar(255) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  `additional` varchar(255) DEFAULT NULL,
  `affected_entity_id` bigint(20) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  `device_id` bigint(20) DEFAULT NULL,
  `ip_address` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_audit_trail_user_4` (`user_id`),
  KEY `ix_audit_trail_financialInstitution_5` (`financial_institution_id`),
  KEY `ix_audit_trail_device_6` (`device_id`),
  CONSTRAINT `fk_audit_trail_device_6` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`),
  CONSTRAINT `fk_audit_trail_financialInstitution_5` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`),
  CONSTRAINT `fk_audit_trail_user_4` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `branch`
--

DROP TABLE IF EXISTS `branch`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `branch` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_name` varchar(255) DEFAULT NULL,
  `branch_code` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) DEFAULT '0',
  `financial_institution_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_branch_financialInstitution_7` (`financial_institution_id`),
  CONSTRAINT `fk_branch_financialInstitution_7` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `config`
--

DROP TABLE IF EXISTS `config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `config_name` varchar(255) DEFAULT NULL,
  `config_value` int(11) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_config_financialInstitution_8` (`financial_institution_id`),
  CONSTRAINT `fk_config_financialInstitution_8` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `device`
--

DROP TABLE IF EXISTS `device`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `brand` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `os` varchar(255) DEFAULT NULL,
  `device_uid` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) DEFAULT '0',
  `financial_institution_id` bigint(20) DEFAULT NULL,
  `dsa_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_device_financialInstitution_9` (`financial_institution_id`),
  KEY `ix_device_dsa_10` (`dsa_id`),
  CONSTRAINT `fk_device_dsa_10` FOREIGN KEY (`dsa_id`) REFERENCES `dsa` (`id`),
  CONSTRAINT `fk_device_financialInstitution_9` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `device_history`
--

DROP TABLE IF EXISTS `device_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `device_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(255) DEFAULT NULL,
  `ip_address` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  `device_id` bigint(20) DEFAULT NULL,
  `event_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_device_history_user_11` (`user_id`),
  KEY `ix_device_history_financialInstitution_12` (`financial_institution_id`),
  KEY `ix_device_history_device_13` (`device_id`),
  CONSTRAINT `fk_device_history_device_13` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`),
  CONSTRAINT `fk_device_history_financialInstitution_12` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`),
  CONSTRAINT `fk_device_history_user_11` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `dsa`
--

DROP TABLE IF EXISTS `dsa`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dsa` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `till_account` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_dsa_user_14` (`user_id`),
  CONSTRAINT `fk_dsa_user_14` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `financial_institution`
--

DROP TABLE IF EXISTS `financial_institution`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `financial_institution` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) DEFAULT NULL,
  `ip_address` varchar(255) DEFAULT NULL,
  `switch_key` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `account_opening_end_point` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_financial_institution_code` (`code`),
  UNIQUE KEY `uq_financial_institution_ip_address` (`ip_address`),
  UNIQUE KEY `uq_financial_institution_switch_key` (`switch_key`),
  UNIQUE KEY `uq_financial_institution_name` (`name`),
  UNIQUE KEY `uq_financial_institution_account_opening_end_point` (`account_opening_end_point`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `holiday`
--

DROP TABLE IF EXISTS `holiday`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `holiday` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `is_enabled` tinyint(1) DEFAULT '0',
  `financial_institution_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_holiday_financialInstitution_15` (`financial_institution_id`),
  CONSTRAINT `fk_holiday_financialInstitution_15` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `holiday_history`
--

DROP TABLE IF EXISTS `holiday_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `holiday_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(255) DEFAULT NULL,
  `ip_address` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  `holiday_id` bigint(20) DEFAULT NULL,
  `event_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_holiday_history_user_16` (`user_id`),
  KEY `ix_holiday_history_financialInstitution_17` (`financial_institution_id`),
  KEY `ix_holiday_history_holiday_18` (`holiday_id`),
  CONSTRAINT `fk_holiday_history_financialInstitution_17` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`),
  CONSTRAINT `fk_holiday_history_holiday_18` FOREIGN KEY (`holiday_id`) REFERENCES `holiday` (`id`),
  CONSTRAINT `fk_holiday_history_user_16` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `igroup`
--

DROP TABLE IF EXISTS `igroup`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `igroup` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `license`
--

DROP TABLE IF EXISTS `license`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `license` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `license_name` varchar(255) DEFAULT NULL,
  `license_value` int(11) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_license_financialInstitution_19` (`financial_institution_id`),
  CONSTRAINT `fk_license_financialInstitution_19` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `location`
--

DROP TABLE IF EXISTS `location`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `location` (
  `coordinates` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`coordinates`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `privilege`
--

DROP TABLE IF EXISTS `privilege`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `privilege` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `transaction_request`
--

DROP TABLE IF EXISTS `transaction_request`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `transaction_request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tran_type` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `customer_account` varchar(255) DEFAULT NULL,
  `amount` bigint(20) DEFAULT NULL,
  `dsa_id` bigint(20) DEFAULT NULL,
  `device_id` bigint(20) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  `request_date` datetime DEFAULT NULL,
  `response_date` datetime DEFAULT NULL,
  `response_code` varchar(255) DEFAULT NULL,
  `response_message` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_transaction_request_dsa_20` (`dsa_id`),
  KEY `ix_transaction_request_device_21` (`device_id`),
  KEY `ix_transaction_request_financialInstitution_22` (`financial_institution_id`),
  CONSTRAINT `fk_transaction_request_device_21` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`),
  CONSTRAINT `fk_transaction_request_dsa_20` FOREIGN KEY (`dsa_id`) REFERENCES `dsa` (`id`),
  CONSTRAINT `fk_transaction_request_financialInstitution_22` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) DEFAULT '0',
  `group_id` bigint(20) DEFAULT NULL,
  `branch_id` bigint(20) DEFAULT NULL,
  `financial_institution_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_username` (`username`),
  UNIQUE KEY `uq_user_1` (`username`,`financial_institution_id`),
  KEY `ix_user_group_23` (`group_id`),
  KEY `ix_user_branch_24` (`branch_id`),
  KEY `ix_user_financialInstitution_25` (`financial_institution_id`),
  CONSTRAINT `fk_user_branch_24` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`),
  CONSTRAINT `fk_user_financialInstitution_25` FOREIGN KEY (`financial_institution_id`) REFERENCES `financial_institution` (`id`),
  CONSTRAINT `fk_user_group_23` FOREIGN KEY (`group_id`) REFERENCES `igroup` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_privilege`
--

DROP TABLE IF EXISTS `user_privilege`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_privilege` (
  `privilege_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`privilege_id`,`user_id`),
  KEY `fk_user_privilege_user_02` (`user_id`),
  CONSTRAINT `fk_user_privilege_privilege_01` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`),
  CONSTRAINT `fk_user_privilege_user_02` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `user_profile`
--

DROP TABLE IF EXISTS `user_profile`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_profile` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `middle_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `staff_number` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_user_profile_user_26` (`user_id`),
  CONSTRAINT `fk_user_profile_user_26` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-20 15:08:44