create table `ohrm_config` (
	property varchar(100) not null default '',
	`value` varchar(2048) not null default '',
	primary key (property)
) engine=innodb default charset=utf8;

create table `ohrm_config_log` (
    `id` int not null auto_increment,
    `property` varchar(100) not null default '',
    `prev_value` varchar(2048) default null,
    `current_value` varchar(2048) not null default '',
    `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
    primary key (id)
) engine=innodb default charset=utf8;

create table `ohrm_secure_configuration` (
    `configuration_name` VARCHAR (255) not null,
    `configuration` TEXT not null,
    `updatable_from_frontend` TINYINT DEFAULT 0 not null,
    primary key (`configuration_name`)
) engine=innodb default charset=utf8;

CREATE TABLE ohrm_cost_centre (
  `id` BIGINT AUTO_INCREMENT,
  `cost_code` VARCHAR(20) NOT NULL,
  `name` VARCHAR(40) NOT NULL,
  `description` VARCHAR(255),
  `is_deleted` tinyint(1) default 0,
  PRIMARY KEY(id)
) ENGINE = INNODB default charset=utf8;

create table `ohrm_application_log` (
    `id` int not null auto_increment,
    `change_type` varchar(100) not null default 'INSTALL',
    `prev_build_name` varchar(512) default null,
    `prev_version` varchar(512) default null,
    `current_build_name` varchar(512) not null default '',
    `current_version` varchar(512) not null default '',
    `note` varchar(1024) default '',
    `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
    primary key (id)
) engine=innodb default charset=utf8;

create table `ohrm_employment_status` (
	`id` int not null auto_increment,
	`name` varchar(60) not null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_job_category` (
	`id` int not null auto_increment,
	`name` varchar(60) default null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `hs_hr_country` (
  `cou_code` char(2) not null default '',
  `name` varchar(80) not null default '',
  `cou_name` varchar(80) not null default '',
  `iso3` char(3) default null,
  `numcode` smallint(6) default null,
  primary key  (`cou_code`)
) engine=innodb default charset=utf8;

create table `hs_hr_currency_type` (
  `code` int(11) not null default '0',
  `currency_id` char(3) not null default '',
  `currency_name` varchar(70) not null default '',
  `decimals`int(1) null,
  `currency_symbol` varchar(5) null,
  primary key  (`currency_id`)
) engine=innodb default charset=utf8;

create table `ohrm_license` (
	`id` int not null auto_increment,
	`name` varchar(100) not null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `hs_hr_district` (
  `district_code` varchar(13) not null default '',
  `district_name` varchar(50) default null,
  `province_code` varchar(13) default null,
  primary key  (`district_code`)
) engine=innodb default charset=utf8;

create table `hs_hr_payperiod` (
  `payperiod_code` varchar(13) not null default '',
  `payperiod_name` varchar(100) default null,
  primary key  (`payperiod_code`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_language` (
  `emp_number` int(7) not null default 0,
  `lang_id` int not null,
  `fluency` smallint default '0',
  `competency` smallint default '0',
  `comments` varchar(100),
  primary key  (`emp_number`,`lang_id`,`fluency`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_us_tax` (
  `emp_number` int(7) not null default 0,
  `tax_federal_status` varchar(13) default null,
  `tax_federal_exceptions` int(2) default 0,
  `tax_state` varchar(13) default null,
  `tax_state_status` varchar(13) default null,
  `tax_state_exceptions` int(2) default 0,
  `tax_unemp_state` varchar(13) default null,
  `tax_work_state` varchar(13) default null,
  primary key  (`emp_number`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_attachment` (
  `emp_number` int(7) not null default 0,
  `eattach_id` int not null default '0',
  `eattach_desc` varchar(400) default null,
  `eattach_filename` varchar(255) default null,
  `eattach_size` int(11) default '0',
  `eattach_attachment` mediumblob,
  `eattach_type` varchar(200) default null,
  `screen` varchar(100) default '',
  `attached_by` int default null,
  `attached_by_name` varchar(200),
  `attached_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary key  (`emp_number`,`eattach_id`),
  key screen (`screen`)
) engine=innodb default charset=utf8;


create table `hs_hr_emp_children` (
  `emp_number` int(7) not null default 0,
  `ec_seqno` decimal(2,0) not null default '0',
  `ec_name` varchar(100) default '',
  `ec_date_of_birth` date null default null,
  primary key  (`emp_number`,`ec_seqno`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_dependents` (
  `emp_number` int(7) not null default 0,
  `ed_seqno` decimal(2,0) not null default '0',
  `ed_name` varchar(100) default '',
  `ed_relationship_type` ENUM('child', 'other'),
  `ed_relationship` varchar(100) default '',
  `ed_date_of_birth` date null default null,
  `ed_nation_code` int(6) null default null,
  primary key  (`emp_number`,`ed_seqno`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_emergency_contacts` (
  `emp_number` int(7) not null default 0,
  `eec_seqno` decimal(2,0) not null default '0',
  `eec_name` varchar(100) default '',
  `eec_relationship` varchar(100) default '',
  `eec_home_no` varchar(100) default '',
  `eec_mobile_no` varchar(100) default '',
  `eec_office_no` varchar(100) default '',
  primary key  (`emp_number`,`eec_seqno`)
) engine=innodb default charset=utf8;


create table `ohrm_emp_license` (
  `emp_number` int not null,
  `license_id` int not null,
  `license_no` varchar(50) default null,
  `license_issued_date` date null default null,
  `license_expiry_date` date null default null,
  primary key (`emp_number`,`license_id`)
) engine=innodb default charset=utf8;


create table `hs_hr_emp_member_detail` (
  `emp_number` int(7) not null default 0,
  `membship_code` int(6) not null default 0,
  `ememb_subscript_ownership` varchar(20) default null,
  `ememb_subscript_amount` decimal(15,2) default null,
  `ememb_subs_currency` varchar(20) default null,
  `ememb_commence_date` date default null,
  `ememb_renewal_date` date default null,
  primary key  (`emp_number`,`membship_code`)
) engine=innodb default charset=utf8;


create table `hs_hr_emp_passport` (
  `emp_number` int(7) not null default 0,
  `ep_seqno` decimal(2,0) not null default '0',
  `ep_passport_num` varchar(100) not null default '',
  `ep_passportissueddate` datetime default null,
  `ep_passportexpiredate` datetime default null,
  `ep_comments` varchar(255) default null,
  `ep_passport_type_flg` smallint(6) default null,
  `ep_i9_status` varchar(100) default '',
  `ep_i9_review_date` date null default null,
  `cou_code` varchar(6) default null,
  primary key  (`emp_number`,`ep_seqno`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_skill` (
  `id` INT  not null AUTO_INCREMENT,
  `emp_number` int(7) not null default 0,
  `skill_id` int not null,
  `years_of_exp` decimal(3,1) default null,
  `comments` varchar(100) not null default '',
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_picture` (
  `emp_number` int(7) not null default 0,
  `epic_picture` mediumblob,
  `epic_filename` varchar(100) default null,
  `epic_type` varchar(50) default null,
  `emp_small_picture` blob,
  `emp_big_picture` mediumblob,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  primary key  (`emp_number`)
) engine=innodb default charset=utf8;


create table `ohrm_emp_education` (
  `id` int not null auto_increment,
  `emp_number` int not null,
  `education_id` int not null,
  `institute` varchar(100) default null,
  `major` varchar(100) default null,
  `year` decimal(4,0) default null,
  `score` varchar(25) default null,
  `start_date` date default null,
  `end_date` date default null,
  primary key  (`id`)
) engine=innodb default charset=utf8;


create table `hs_hr_emp_reportto` (
  `erep_sup_emp_number` int(7) not null default 0,
  `erep_sub_emp_number` int(7) not null default 0,
  `erep_reporting_mode` int(7) not null default 0,
  primary key  (`erep_sup_emp_number`,`erep_sub_emp_number`, `erep_reporting_mode`)
) engine=innodb default charset=utf8;

create table `ohrm_emp_reporting_method` (
  `reporting_method_id` int(7) not null auto_increment,
  `reporting_method_name` varchar(100) not null,
  primary key  (`reporting_method_id`)
) engine=innodb default charset=utf8;

create table `hs_hr_emp_work_experience` (
  `emp_number` int(7) not null default 0,
  `eexp_seqno` decimal(10,0) not null default '0',
  `eexp_employer` varchar(100) default null,
  `eexp_jobtit` varchar(120) default null,
  `eexp_from_date` datetime default null,
  `eexp_to_date` datetime default null,
  `eexp_comments` varchar(200) default null,
  `eexp_internal` int(1) default null,
  `eexp_creditable` int(1) default null,
  primary key  (`emp_number`,`eexp_seqno`)
) engine=innodb default charset=utf8;


create table `hs_hr_employee` (
  `emp_number` int(7) not null auto_increment,
  `employee_id` varchar(50) default null unique,
  `emp_lastname` varchar(100) default '' not null,
  `emp_firstname` varchar(100) default '' not null,
  `emp_middle_name` varchar(100) default '' not null,
  `emp_nick_name` varchar(100) default '',
  `emp_smoker` smallint(6) default '0',
  `ethnic_race_code` varchar(13) default null,
  `emp_birthday` date null default null,
  `nation_code` int(4) default null,
  `emp_gender` smallint(6) default null,
  `emp_marital_status` varchar(20) default null,
  `emp_ssn_num` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
  `emp_sin_num` varchar(100) default '',
  `emp_other_id` varchar(100) default '',
  `emp_dri_lice_num` varchar(100) default '',
  `emp_dri_lice_exp_date` date null default null,
  `emp_military_service` varchar(100) default '',
  `emp_status` int(13) default null,
  `job_title_code` int(7) default null,
  `eeo_cat_code` int default null,
  `work_station` int(6) default null,
  `emp_street1` varchar(100) default '',
  `emp_street2` varchar(100) default '',
  `city_code` varchar(100) default '',
  `coun_code` varchar(100) default '',
  `provin_code` varchar(100) default '',
  `emp_zipcode` varchar(20) default null,
  `emp_hm_telephone` varchar(50) default null,
  `emp_mobile` varchar(50) default null,
  `emp_work_telephone` varchar(50) default null,
  `emp_work_email` varchar(50) default null,
  `sal_grd_code` varchar(13) default null,
  `joined_date` date null default null,
  `emp_oth_email` varchar(50) default null,
  `termination_id` int(4) default null,
  `custom1` varchar(250) default null,
  `custom2` varchar(250) default null,
  `custom3` varchar(250) default null,
  `custom4` varchar(250) default null,
  `custom5` varchar(250) default null,
  `custom6` varchar(250) default null,
  `custom7` varchar(250) default null,
  `custom8` varchar(250) default null,
  `custom9` varchar(250) default null,
  `custom10` varchar(250) default null,
  `deleted_at` DATETIME DEFAULT NULL,
  `added_date` date null default null,
  `eeo_job_category` int default null,
  `eeo_paygrade` int default null,
  `eeo_race_ent` int default null,
  `flsa_exempt`  int default null,
  `purged_at` timestamp null default null,
  `location_id` int(11) DEFAULT NULL,
  `work_schedule_id` int unsigned default null,
  primary key  (`emp_number`)
) engine=innodb default charset=utf8;


create table `ohrm_language` (
  `id` int not null auto_increment,
  `name` varchar(120) default null,
  primary key  (`id`)
) engine=innodb default charset=utf8;


create table `ohrm_location` (
  `id` int not null auto_increment,
  `name` varchar(110) not null,
  `country_code` varchar(3) not null,
  `time_zone` varchar(50) default null,
  `province` varchar(60) default null,
  `city` varchar(60) default null,
  `address` varchar(255) default null,
  `zip_code` varchar(35) default null,
  `phone` varchar(35) default null,
  `fax` varchar(35) default null,
  `notes` varchar(255) default null,
  `eeo_applicable` int default null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

CREATE  TABLE `ohrm_operational_country` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `country_code` CHAR(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

create table `hs_hr_module` (
  `mod_id` varchar(36) not null default '',
  `name` varchar(45) default null,
  `owner` varchar(45) default null,
  `owner_email` varchar(100) default null,
  `version` varchar(36) default null,
  `description` text,
  primary key  (`mod_id`)
) engine=innodb default charset=utf8;

create table `hs_hr_province` (
  `id` int(11) not null auto_increment,
  `province_name` varchar(40) not null default '',
  `province_code` char(2) not null default '',
  `cou_code` char(2) not null default 'us',
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_education` (
	`id` int not null auto_increment,
	`name` varchar(100) not null,
	primary key (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_skill` (
  `id` int not null auto_increment,
  `name` varchar(120) default null,
  `description` text default null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_pay_grade_currency` (
  `pay_grade_id` int not null ,
  `currency_id` varchar(6) not null default '',
  `min_salary` double default null,
  `max_salary` double default null,
  primary key  (`pay_grade_id`,`currency_id`)
) engine=innodb default charset=utf8;

create table `ohrm_pay_grade` (
  `id` int not null auto_increment,
  `name` varchar(60) default null unique,
  primary key  (`id`)
) engine=innodb default charset=utf8;

CREATE TABLE `ohrm_holiday` (
  `id` INT UNSIGNED AUTO_INCREMENT,
  `description` TEXT DEFAULT NULL,
  `date` DATE DEFAULT NULL,
  `recurring` TINYINT UNSIGNED DEFAULT 0,
  `length` INT UNSIGNED DEFAULT NULL,
  `operational_country_id` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

create table `hs_hr_mailnotifications` (
	`user_id` int(20) not null,
	`notification_type_id` int not null ,
	`status` int(2) not null,
    `email` varchar(100) default null,
	KEY `user_id` (`user_id`),
	KEY `notification_type_id` (`notification_type_id`)
) engine=innodb default charset=utf8;

create table `ohrm_customer` (
  `customer_id` int(11) not null auto_increment,
  `name` varchar(100) not null,
  `description` varchar(500) default null,
  `cost_centre_id` BIGINT,
  `is_deleted` tinyint(1) default 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 primary key  (`customer_id`),
  CONSTRAINT `fk_ohrm_customer_ohrm_cost_centre` FOREIGN KEY (`cost_centre_id`)
      REFERENCES `ohrm_cost_centre` (`id`) ON DELETE SET NULL
) engine=innodb default charset=utf8;

create table `ohrm_project` (
  `project_id` int(11) not null auto_increment,
  `customer_id` int(11) not null,
  `name` varchar(100) default null,
  `description` varchar(256) default null,
  `cost_centre_id` BIGINT,
  `is_deleted` tinyint(1) default 0,
  `status` TINYINT DEFAULT '1',
  `is_private` TINYINT(1) DEFAULT '0',
  primary key  (`project_id`,`customer_id`),  key `customer_id` (`customer_id`),
  CONSTRAINT `fk_ohrm_project_ohrm_cost_centre` FOREIGN KEY (`cost_centre_id`)
      REFERENCES `ohrm_cost_centre` (`id`) ON DELETE SET NULL
) engine=innodb default charset=utf8;

create table `ohrm_job_activity_type` (
  `id` int(11) not null auto_increment,
  `name` varchar(100) not null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_job_activity` (
  `activity_id` int(11) not null auto_increment,
  `project_id` int(11) null,
  `name` varchar(200) default null,
  `type_id` int(11) not null,
  `preset` tinyint(1) NOT NULL default 0,
  `is_deleted` tinyint(1) default 0,
  primary key  (`activity_id`),
  CONSTRAINT `fk_ohrm_job_activity_ohrm_project` FOREIGN KEY (`project_id`)
      REFERENCES `ohrm_project` (`project_id`) ON DELETE CASCADE ,
  CONSTRAINT `fk_ohrm_job_activity_ohrm_job_activity_type` FOREIGN KEY (`type_id`)
      REFERENCES `ohrm_job_activity_type` (`id`) ON DELETE RESTRICT
) engine=innodb default charset=utf8;

create table `ohrm_project_admin` (
  `project_id` int(11) not null,
  `emp_number` int(11) not null,
  primary key  (`project_id`,`emp_number`),
  key `emp_number` (`emp_number`)
) engine=innodb default charset=utf8;

create table `ohrm_project_member` (
  `project_id` int(11) not null,
  `emp_number` int(7) not null,
  primary key  (`project_id`,`emp_number`),
  CONSTRAINT `fk_ohrm_project_member_ohrm_project` FOREIGN KEY (`project_id`)
    REFERENCES `ohrm_project` (`project_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_project_member_hs_hr_employee` FOREIGN KEY (`emp_number`)
    REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb default charset=utf8;

create table `hs_hr_unique_id` (
  `id` int not null auto_increment,
  `last_id` int unsigned not null,
  `table_name` varchar(50) not null,
  `field_name` varchar(50) not null,
  primary key(`id`),
  unique key `table_field` (`table_name`, `field_name`)
) engine=innodb default charset=utf8;

create table ohrm_custom_field_screen (
    id int auto_increment,
    `name` varchar(255) unique not null,
    screen_id int,
    data_group_id int,
    display_order smallint default 0,
    `embed_in_standard_section` tinyint(1) default 0 not null,
    deleted tinyint(1) default 0,
    `menu_id` INT(11) DEFAULT NULL,
    primary key(id)
) engine = innodb default charset=utf8;

create table ohrm_custom_field_section (
    id int auto_increment,
    custom_field_screen_id int,
    display_name varchar(255),
    display_order smallint default 0,
    deleted tinyint(1) default 0,
    primary key(id)
) engine = innodb default charset=utf8;

create table `ohrm_custom_field` (
  `id` int not null auto_increment,
  `name` varchar(250) not null,
  `unique_name` varchar(1275) null,
  `type` int(11) not null,
  `custom_field_section_id` int not null,
  `extra_data` text default null,
  `display_order` smallint default 0 not null,
  `mandatory` tinyint(1) default 0 not null,
  `global` tinyint(1) default 1 not null,
  `deletable` tinyint(1) default 1 not null,
  `deleted` tinyint(1) default 0 not null,
  `extra_data_sorting_method` tinyint(1) default 1 null COMMENT '0 = Added Order, 1 = Alphabetical Order',
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table ohrm_custom_field_location (
  custom_field_id int not null,
  location_id int not null,
  primary key(custom_field_id, location_id),
  foreign key (custom_field_id) references ohrm_custom_field(`id`) on delete cascade,
  foreign key (location_id) references ohrm_location(`id`) on delete cascade
) engine=innodb default charset=utf8;

create table ohrm_custom_field_value (
  id int not null auto_increment,
  emp_number int not null,
  custom_field_id int not null,
  value_type tinyint default 1 not null,
  string_value text null,
  text_value text null,
  number_value double null,
  date_value date null,
  version BIGINT(20) DEFAULT NULL,
  sequence_number INT NOT NULL,
  primary key(id),
  foreign key (emp_number) references hs_hr_employee(emp_number) on delete cascade,
  foreign key (custom_field_id) references ohrm_custom_field(`id`) on delete cascade
) engine=innodb default charset=utf8;

create table ohrm_custom_field_value_history (
  id int not null,
  emp_number int not null,
  custom_field_id int not null,
  value_type tinyint default 1 not null,
  string_value text null,
  text_value text null,
  number_value double null,
  date_value date null,
  sequence_number INT NOT NULL,
  version BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`, `version`),
  CONSTRAINT `ohrm_custom_field_value_history_emp_number` foreign key (emp_number) references hs_hr_employee(emp_number) on delete cascade,
  CONSTRAINT `ohrm_custom_field_value_history_custom_field` foreign key (custom_field_id) references ohrm_custom_field(`id`) on delete cascade
) engine=innodb default charset=utf8;

create table ohrm_custom_field_attachment (
  id int not null auto_increment,
  custom_field_value_id int not null,
  file_name varchar(255) not null,
  file_size int not null,
  file_type varchar(255) not null,
  content longblob not null,
  version BIGINT(20) DEFAULT NULL,
  primary key(id),
  foreign key (custom_field_value_id) references ohrm_custom_field_value(`id`) on delete cascade
) engine=innodb default charset=utf8;

create table ohrm_custom_field_attachment_history (
  id int not null auto_increment,
  custom_field_value_id int not null,
  file_name varchar(255) not null,
  file_size int not null,
  file_type varchar(255) not null,
  content longblob not null,
  version BIGINT(20) DEFAULT NULL,
  primary key(id, version),
  CONSTRAINT `ohrm_custom_field_attachment_history_custom_field_value` foreign key (custom_field_value_id) references ohrm_custom_field_value(`id`) on delete cascade
) engine=innodb default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_attachment_history_for_custom_field_value_history` (
    `attachment_id` INT(13) NOT NULL,
    `attachment_version` BIGINT(20) NOT NULL,
    `custom_field_value_id` INT(11) NOT NULL,
    `custom_field_value_version` BIGINT(20) NOT NULL,
    PRIMARY KEY(`attachment_id`, `attachment_version`, `custom_field_value_id`, `custom_field_value_version`),
    CONSTRAINT `ohrm_attachment_history_for_cf_value_history_value` FOREIGN KEY (`custom_field_value_id`, `custom_field_value_version`) REFERENCES
    `ohrm_custom_field_value_history`(`id`,`version`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `ohrm_attachment_history_for_cf_value_history_attachment` FOREIGN KEY (`attachment_id`, `attachment_version`) REFERENCES
    `ohrm_custom_field_attachment_history`(`id`, `version`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

create table `hs_hr_pay_period` (
	`id` int not null ,
	`start_date` date not null ,
	`end_date` date not null ,
	`close_date` date not null ,
	`check_date` date not null ,
	`timesheet_aproval_due_date` date not null ,
	primary key (`id`)
) engine=innodb default charset=utf8;

create table `hs_hr_custom_export` (
  `export_id` int(11) not null,
  `name` varchar(250) not null,
  `fields` text default null,
  `headings` text default null,
  primary key  (`export_id`),
  key `emp_number` (`export_id`)
) engine=innodb default charset=utf8;

create table `hs_hr_custom_import` (
  `import_id` int(11) not null,
  `name` varchar(250) not null,
  `fields` text default null,
  `has_heading` tinyint(1) default 0,
  primary key  (`import_id`),
  key `emp_number` (`import_id`)
) engine=innodb default charset=utf8;

create table `ohrm_module` (
  `id` int not null auto_increment,
  `name` varchar(120) default null,
  `status` tinyint default 1,
  primary key  (`id`)
) engine=innodb default charset=utf8;

CREATE TABLE `ohrm_workflow` (
    `id` VARCHAR(255) not null,
    `name` VARCHAR(100) UNIQUE,
    `label` VARCHAR(100) DEFAULT NULL,
    `available_to` VARCHAR(15) DEFAULT NULL,
    `default_available_to` VARCHAR(15) DEFAULT NULL,
    `group_name` VARCHAR(100) DEFAULT NULL,
    `group_label` VARCHAR(100) DEFAULT NULL,
    `module_id` int not null,
    `order_no` INT DEFAULT 0,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_ohrm_workflow_module` FOREIGN KEY (`module_id`)
      REFERENCES `ohrm_module` (`id`) ON DELETE RESTRICT
) ENGINE = INNODB default charset=utf8;

create table `ohrm_workflow_state_machine`(
  `id` bigint(20) not null auto_increment,
  `workflow` varchar(255) not null,
  `state` varchar(255) not null,
  `role` varchar(255) not null,
  `action` varchar(255) not null,
  `resulting_state` varchar(255) not null,
  `roles_to_notify` text,
  `screens_to_show` VARCHAR(255) DEFAULT NULL,
  `priority` int not null default 0 COMMENT 'lowest priority 0',
  primary key (`id`)
) engine=innodb default charset=utf8;

CREATE TABLE `ohrm_workflow_screen` (
    `id` BIGINT(20) AUTO_INCREMENT,
    `workflow_name` VARCHAR(100) NOT NULL,
    `user_role_type_ids` VARCHAR(15),
    `module` VARCHAR(100) NOT NULL,
    `action` VARCHAR(100) NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB default charset=utf8;

CREATE TABLE   `ohrm_workflow_state_machine_dependancy` (
  `id` BIGINT AUTO_INCREMENT,
  `master_state_change_id` BIGINT NOT NULL,
  `slave_state_change_id` BIGINT NOT NULL,
  `trigger_verification` VARCHAR(100),

  PRIMARY KEY(`id`),
  FOREIGN KEY (`master_state_change_id`) REFERENCES `ohrm_workflow_state_machine`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`slave_state_change_id`) REFERENCES `ohrm_workflow_state_machine`(`id`) ON DELETE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table `ohrm_report_group` (
  `report_group_id` bigint(20) not null,
  `name` varchar(255) not null,
  `core_sql` mediumtext not null,
  primary key (`report_group_id`)
) engine=innodb default charset=utf8;

create table `ohrm_report` (
  `report_id` bigint(20) not null auto_increment,
  `name` varchar(255) not null,
  `report_group_id` bigint(20) not null,
  `use_filter_field` boolean not null,
  `type` varchar(255) default null,
  primary key (`report_id`),
  key `report_group_id` (`report_group_id`)
) engine=innodb default charset=utf8;

create table `ohrm_filter_field` (
  `filter_field_id` bigint(20) not null,
  `report_group_id` bigint(20) not null,
  `name` varchar(255) not null,
  `where_clause_part` mediumtext not null,
  `filter_field_widget` varchar(255),
  `condition_no` int(20) not null,
  `required` varchar(10) default null,
  primary key (`filter_field_id`),
  key `report_group_id` (`report_group_id`)
) engine=innodb default charset=utf8;

create table `ohrm_selected_filter_field` (
  `report_id` bigint(20) not null,
  `filter_field_id` bigint(20) not null,
  `filter_field_order` bigint(20) not null,
  `value1` varchar(255) default null,
  `value2` varchar(255) default null,
  `where_condition` varchar(255) default null,
  `type` varchar(255) not null,
  primary key (`report_id`,`filter_field_id`),
  key `report_id` (`report_id`),
  key `filter_field_id` (`filter_field_id`)
) engine=innodb default charset=utf8;

create table `ohrm_abstract_display_field` (
  `id` bigint(20) not null auto_increment,
  `report_group_id` bigint(20) not null,
  `name` varchar(255) not null,
  `label` varchar(255) not null,
  `field_alias` varchar(255),
  `is_sortable` varchar(10) not null,
  `sort_order` varchar(255),
  `sort_field` varchar(255),
  `element_type` varchar(255) not null,
  `element_property` varchar(1000) not null,
  `width` varchar(255) not null,
  `is_exportable` varchar(10),
  `text_alignment_style` varchar(20),
  `is_value_list` boolean not null default false,
  `display_field_group_id` int unsigned,
  `default_value` varchar(255) default null,
  `is_encrypted` boolean not null default false,
  `is_meta` boolean not null default false,
  `is_enabled` boolean not null default true,
  primary key (`id`),
  key `report_group_id` (`report_group_id`)
) engine=innodb default charset=utf8;

create table `ohrm_display_field` (
  `display_field_id` bigint(20) not null auto_increment,
  `report_group_id` bigint(20) not null,
  `name` varchar(255) not null,
  `label` varchar(255) not null,
  `field_alias` varchar(255),
  `is_sortable` varchar(10) not null,
  `sort_order` varchar(255),
  `sort_field` varchar(255),
  `element_type` varchar(255) not null,
  `element_property` varchar(1000) not null,
  `width` varchar(255) not null,
  `is_exportable` varchar(10),
  `text_alignment_style` varchar(20),
  `is_value_list` boolean not null default false,
  `display_field_group_id` int unsigned,
  `default_value` varchar(255) default null,
  `is_encrypted` boolean not null default false,
  `is_meta` boolean not null default false,
  `is_enabled` boolean not null default true,
  primary key (`display_field_id`),
  key `report_group_id` (`report_group_id`)
) engine=innodb default charset=utf8;

create table `ohrm_composite_display_field` (
  `composite_display_field_id` bigint(20) not null auto_increment,
  `report_group_id` bigint(20) not null,
  `name` varchar(1000) not null,
  `label` varchar(255) not null,
  `field_alias` varchar(255),
  `is_sortable` varchar(10) not null,
  `sort_order` varchar(255),
  `sort_field` varchar(255),
  `element_type` varchar(255) not null,
  `element_property` varchar(1000) not null,
  `width` varchar(255) not null,
  `is_exportable` varchar(10),
  `text_alignment_style` varchar(20),
  `is_value_list` boolean not null default false,
  `display_field_group_id` int unsigned,
  `default_value` varchar(255) default null,
  `is_encrypted` boolean not null default false,
  `is_meta` boolean not null default false,
  `is_enabled` boolean not null default true,
  primary key (`composite_display_field_id`),
  key `report_group_id` (`report_group_id`)
) engine=innodb default charset=utf8;

create table `ohrm_group_field` (
  `group_field_id` bigint(20) not null,
  `name` varchar(255) not null,
  `group_by_clause` mediumtext not null,
  `group_field_widget` varchar(255),
  primary key (`group_field_id`)
) engine=innodb default charset=utf8;

create table `ohrm_available_group_field` (
  `report_group_id` bigint(20) not null,
  `group_field_id` bigint(20) not null,
  primary key (`report_group_id`,`group_field_id`),
  key `report_group_id` (`report_group_id`),
  key `group_field_id` (`group_field_id`)
) engine=innodb default charset=utf8;

create table `ohrm_selected_display_field` (
  `id` bigint(20) not null auto_increment,
  `display_field_id` bigint(20) not null,
  `report_id` bigint(20) not null,
  primary key (`id`,`display_field_id`,`report_id`),
  key `display_field_id` (`display_field_id`),
  key `report_id` (`report_id`)
) engine=innodb default charset=utf8;

create table `ohrm_selected_composite_display_field` (
  `id` bigint(20) not null,
  `composite_display_field_id` bigint(20) not null,
  `report_id` bigint(20) not null,
  primary key (`id`,`composite_display_field_id`,`report_id`),
  key `composite_display_field_id` (`composite_display_field_id`),
  key `report_id` (`report_id`)
) engine=innodb default charset=utf8;

create table `ohrm_summary_display_field` (
  `summary_display_field_id` bigint(20) not null,
  `function` varchar(1000) not null,
  `label` varchar(255) not null,
  `field_alias` varchar(255),
  `is_sortable` varchar(10) not null,
  `sort_order` varchar(255),
  `sort_field` varchar(255),
  `element_type` varchar(255) not null,
  `element_property` varchar(1000) not null,
  `width` varchar(255) not null,
  `is_exportable` varchar(10),
  `text_alignment_style` varchar(20),
  `is_value_list` boolean not null default false,
  `display_field_group_id` int unsigned,
  `default_value` varchar(255) default null,
  primary key (`summary_display_field_id`)
) engine=innodb default charset=utf8;

create table `ohrm_selected_group_field` (
  `group_field_id` bigint(20) not null,
  `summary_display_field_id` bigint(20) not null,
  `report_id` bigint(20) not null,
  primary key (`group_field_id`,`summary_display_field_id`,`report_id`),
  key `group_field_id` (`group_field_id`),
  key `summary_display_field_id` (`summary_display_field_id`),
  key `report_id` (`report_id`)
) engine=innodb default charset=utf8;

create table `ohrm_display_field_group` (
  `id` int unsigned not null auto_increment,
  `report_group_id` bigint not null,
  `name` varchar(255) not null,
  `is_list` boolean not null default false,
  primary key (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_selected_display_field_group` (
  `id` int unsigned not null auto_increment,
  `report_id` bigint not null,
  `display_field_group_id` int unsigned not null,
  primary key (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_subunit` (
  `id` int(6) not null auto_increment,
  `name` varchar(100) not null unique,
  `unit_id` varchar(100) default null,
  `description` varchar(400),
  `cost_centre_id` BIGINT,
  `lft` smallint(6) unsigned default null,
  `rgt` smallint(6) unsigned default null,
  `level` smallint(6) unsigned default null,
  primary key (`id`),
  CONSTRAINT `fk_ohrm_subunit_ohrm_cost_centre` FOREIGN KEY (`cost_centre_id`)
      REFERENCES `ohrm_cost_centre` (`id`) ON DELETE SET NULL
) engine=innodb default charset=utf8;

create table `ohrm_organization_gen_info` (
  `id` int(4) not null auto_increment,
  `name` varchar(100) not null,
  `tax_id` varchar(30) default null,
  `registration_number` varchar(30) default null,
  `cost_centre_id` BIGINT,
  `phone` varchar(30) default null,
  `fax` varchar(30) default null,
  `email` varchar(50) default null,
  `country` varchar(30) default null,
  `province` varchar(30) default null,
  `city` varchar(30) default null,
  `zip_code` varchar(30) default null,
  `street1` varchar(100) default null,
  `street2` varchar(100) default null,
  `note` varchar(255) default null,
  `eeo_applicable` int(5) default null,
  `cost_center_enabled` tinyint(1) not null default 0,
  primary key (`id`),
  CONSTRAINT `fk_ohrm_organization_gen_info_ohrm_cost_centre` FOREIGN KEY (`cost_centre_id`)
      REFERENCES `ohrm_cost_centre` (`id`) ON DELETE SET NULL
) engine=innodb default charset=utf8;

create table `ohrm_job_title` (
  `id` int(13) not null auto_increment,
  `job_title` varchar(100) not null,
  `job_description` varchar(400) default null,
  `note` varchar(400) default null,
  `is_deleted` tinyint(1) default 0,
  primary key (`id`)
) engine=innodb default charset=utf8;



create table `ohrm_job_specification_attachment`(
	`id` int(13) not null auto_increment,
	`job_title_id` int(13) not null,
	`file_name` varchar(200) not null,
        `file_type` varchar(200) default null,
	`file_size` int(11) not null,
	`file_content` mediumblob,
	primary key (`id`)
)engine=innodb default charset=utf8;

create table `ohrm_emp_termination`(
	`id` int(4) not null auto_increment,
	`emp_number` int(4) default null,
  `reason_id` int(4) default null,
	`termination_date` date not null,
	`termination_time` time not null DEFAULT '23:55:00',
    `termination_time_zone_name` varchar(255) null,
    `termination_time_zone_prefix` varchar(20) null,
    `termination_performed_user_id` int default null,
	`reactivation_date` date default null,
    `reactivation_time` time default null,
    `reactivation_time_zone_name` varchar(255) null,
    `reactivation_time_zone_prefix` varchar(20) null,
    `reactivation_performed_user_id` int default null,
    `reactivated` int(1) default 0,
    `update_joined_date` int(1) default 0,
    `reactivation_failed` int(1) default 0,
  `note` text default null,
  `flaged` int(1) default 0,
	primary key (`id`),
    INDEX ohrm_emp_termination_termination_date (`termination_date`),
    INDEX ohrm_emp_termination_reactivation_date (`reactivation_date`)
)engine=innodb default charset=utf8;

create table `ohrm_emp_termination_reason`(
	`id` int(4) not null auto_increment,
    `name` varchar(100) default null,
	primary key (`id`)
)engine=innodb default charset=utf8;

create table `ohrm_user`(
        `id` int(10) not null auto_increment,
        `user_role_id` int(10) not null,
        `emp_number` int(13) DEFAULT NULL,
        `user_name` varchar(50) unique,
        `user_password` varchar(255) DEFAULT NULL,
        `deleted` tinyint(1) NOT NULL DEFAULT '0',
        `purged` tinyint(1) NOT NULL DEFAULT '0',
        `status` tinyint(1) NOT NULL DEFAULT '1',
        `date_entered` datetime null default null,
        `date_modified` datetime null default null,
        `modified_user_id` int(10) default null,
        `created_by` int(10) default null,
        `password_updated` datetime default null,
        `password_expiration` datetime default null,
        key `user_role_id` (`user_role_id`),
        key `emp_number` (`emp_number`),
        key `modified_user_id`(`modified_user_id`),
        key `created_by`(`created_by`),
        primary key (`id`)
)engine=innodb default charset=utf8;

create table `ohrm_user_role_type`(
    `id` int(3) not null,
    `name` varchar(100) not null,
    primary key (`id`)
)engine=innodb default charset=utf8;

create table `ohrm_user_role`(
	`id` int(10) not null auto_increment,
	`name` varchar(255) not null,
	`display_name` varchar(255) not null,
	`is_assignable` tinyint(1) default 0,
  `is_predefined` tinyint(1) default 0,
  `is_editable` tinyint(1) NOT NULL default 0,
  `type_id` int(3) default null,
  `deletable` tinyint(1) NOT NULL DEFAULT 1,
  unique key user_role_name (`name`),
	primary key (`id`)
)engine=innodb default charset=utf8;

create table ohrm_user_user_role (
    id int auto_increment,
    user_id int,
    user_role_id int,
    user_role_type_id int(3),
    rule_xml text,
    primary key(id)
) engine=innodb default charset=utf8;

create table `ohrm_user_role_type_data_group`(
    `user_role_type_id` int(3) not null,
    `data_group_id` int not null,
    `can_read` TINYINT default 1,
    `can_create` TINYINT default 1,
    `can_update` TINYINT default 1,
    `can_delete` TINYINT default 1,
    primary key (`user_role_type_id`, `data_group_id`)
)engine=innodb default charset=utf8;

create table `ohrm_user_selection_rule`(
	`id` int(10) not null auto_increment,
	`name` varchar(255) not null,
        `description` varchar(255) ,
	`implementation_class` varchar(255) not null,
        `rule_xml_data` text,
	primary key (`id`)
)engine=innodb default charset=utf8;

create table `ohrm_role_user_selection_rule`(
	`user_role_id` int(10) not null,
        `selection_rule_id` int(10) not null,
        `configurable_params` text,
	primary key (`user_role_id`,`selection_rule_id`)
)engine=innodb default charset=utf8;

create table `ohrm_work_schedule_type` (
	 `id` int unsigned not null auto_increment,
	 `name` varchar(255) unique not null,
	 `description` varchar(255) unique not null,
	 `class_name` varchar(255) not null,
	 primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_work_schedule` (
    `id` int unsigned not null auto_increment,
    `name` varchar(255) not null,
    `work_schedule_type_id` int unsigned not null,
    `is_default` tinyint(1) unsigned not null default 0,
    `definition` TEXT not null,
    `created_through` tinyint(1) unsigned not null default 0 COMMENT '1 = from leave work week, 2 = from employee work week',
    `deletable` tinyint(1) unsigned not null default 1,
    `editable` tinyint(1) unsigned not null default 1,
    `created_by` int(10) default null,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    primary key  (`id`),
    index ohrm_work_schedule_name (`name`),
    CONSTRAINT `ohrm_work_schedule_work_schedule_type` foreign key (work_schedule_type_id) references ohrm_work_schedule_type(id) on update cascade on delete restrict,
    CONSTRAINT `fk_ohrm_work_schedule_created_by` FOREIGN KEY (`created_by`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb default charset=utf8;

create table `ohrm_work_schedule_history` (
    `id` int unsigned not null,
    `from_date` date not null,
    `name` varchar(255) not null,
    `work_schedule_type_id` int unsigned not null,
    `definition` TEXT not null,
    `updated_by` int(10) default null,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    primary key  (`id`, `from_date`),
    CONSTRAINT `fk_ohrm_work_schedule_history_work_schedule_id` foreign key (id) references ohrm_work_schedule(id) on delete cascade,
    CONSTRAINT `fk_ohrm_work_schedule_history_work_schedule_type` foreign key (work_schedule_type_id) references ohrm_work_schedule_type(id) on update cascade on delete cascade,
    CONSTRAINT `fk_ohrm_work_schedule_history_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL
) engine=innodb default charset=utf8;

create table `ohrm_work_schedule_country_setting`
(
  `id`                     int unsigned not null auto_increment,
  `work_schedule_id`       int unsigned not null,
  `operational_country_id` int unsigned not null,
  primary key (`id`),
  CONSTRAINT `fk_ohrm_work_schedule_ohrm_operational_country` FOREIGN KEY (`operational_country_id`)
    REFERENCES `ohrm_operational_country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_work_schedule_country_setting_work_schedule` FOREIGN KEY (work_schedule_id)
    REFERENCES `ohrm_work_schedule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_workflow_action` (
    `id` int(7) AUTO_INCREMENT,
    `workflow_id` int(7),
    `workflow` VARCHAR(100),
    `action_entity` VARCHAR(100),
    `action_display_text` VARCHAR(100),
    `support_doc_creation` int(7),
    `module_name` VARCHAR(100),
    `action_name` VARCHAR(100),
    `action_type_text` VARCHAR(100),
    `parent_action` VARCHAR(100),
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

create table `ohrm_membership` (
  `id` int(6) not null auto_increment,
  `name` varchar(100) not null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_nationality` (
  `id` int(6) not null auto_increment,
  `name` varchar(100) not null,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_gender` (
  `id` SMALLINT (6) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (100) NOT NULL UNIQUE,
  PRIMARY KEY  (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

create table `ohrm_email_notification_type` (
    `id` int not null auto_increment,
    `name` varchar(100) not null,
primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_email_notification` (
  `id` int(6) not null auto_increment,
  `name` varchar(100) not null,
  `email_id` int(6) not null unique,
  `is_enable` int(6) not null,
  `parameters` varchar(256),
  `type_id` int not null default 1,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_email_subscriber` (
  `id` int(6) not null auto_increment,
  `notification_id` int(6) not null,
  `name` varchar(100) not null,
  `email` varchar(100) not null,
  `employee_id` int(7),
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_email` (
  `id` int(6) not null auto_increment,
  `name` varchar(100) not null unique,
  primary key  (`id`),
  unique key ohrm_email_name(`name`)
) engine=innodb default charset=utf8;

create table `ohrm_email_template` (
  `id` int(6) not null auto_increment,
  `email_id` int(6) not null,
  `locale` varchar(20),
  `performer_role` varchar(50) default null,
  `recipient_role` varchar(50) default null,
  `subject` varchar(255),
  `body` text,
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_email_processor` (
  `id` int(6) not null auto_increment,
  `email_id` int(6) not null,
  `class_name` varchar(100),
  primary key  (`id`)
) engine=innodb default charset=utf8;

create table ohrm_screen (
  `id` int not null auto_increment,
   `name` varchar(100) not null,
   `module_id` int not null,
   `action_url` varchar(255) not null,
   `route_name` varchar(255) default null,
   primary key (`id`)
) engine=innodb default charset=utf8;

create table ohrm_user_role_screen (
  id int not null auto_increment,
  user_role_id int not null,
  screen_id int not null,
  can_read tinyint(1) not null default '0',
  can_create tinyint(1) not null default '0',
  can_update tinyint(1) not null default '0',
  can_delete tinyint(1) not null default '0',
  primary key (`id`)
) engine=innodb default charset=utf8;

create table ohrm_default_permitted_screen (
  user_role_type_id int not null,
  screen_id int not null,
  primary key (`user_role_type_id`, `screen_id`)
) engine=innodb default charset=utf8;

create table `ohrm_menu_item` (
   `id` int not null auto_increment,
   `menu_title` varchar(255) not null,
   `menu_category` varchar(20) default null,
   `screen_id` int default null,
   `parent_id` int default null,
   `level` tinyint not null,
   `order_hint` int not null,
   `url_extras` varchar(255) default null,
   `image_name` VARCHAR(255) NULL,
   `uisref` VARCHAR(255),
   `status` tinyint not null default 1,
   `include_attachment_section` TINYINT( 4 ) DEFAULT NULL,
   `has_multiple_records` TINYINT( 4 ) NULL,
   primary key (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_upgrade_history` (
  `id` int(10) not null auto_increment,
  `start_version` varchar(30) DEFAULT NULL,
  `end_version` varchar(30) DEFAULT NULL,
  `start_increment` int(11) NOT NULL,
  `end_increment` int(11) NOT NULL,
  `upgraded_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

create table `ohrm_email_configuration` (
  `id` int(10) not null auto_increment,
  `mail_type` varchar(50) DEFAULT NULL,
  `sent_as` varchar(250) NOT NULL,
  `sender_name` varchar(100) DEFAULT NULL,
  `smtp_host` varchar(250) DEFAULT NULL,
  `smtp_port` int(10) DEFAULT NULL,
  `smtp_username` varchar(250) DEFAULT NULL,
  `smtp_password` varchar(250) DEFAULT NULL,
  `smtp_auth_type` varchar(50) DEFAULT NULL,
  `smtp_security_type` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE ohrm_data_group (
    `id` int AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL UNIQUE,
    description VARCHAR(255),
    `can_read` TINYINT, can_create TINYINT,
    `can_update` TINYINT,
    `can_delete` TINYINT,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_user_role_data_group (
    id int AUTO_INCREMENT,
    user_role_id int,
    data_group_id int,
    can_read TINYINT,
    can_create TINYINT,
    can_update TINYINT,
    can_delete TINYINT,
    self TINYINT,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

create table `ohrm_menu_item_data_group` (
  `id` int auto_increment,
  `menu_item_id` int(11),
  `data_group_id` int(11),
  primary key(`id`),
  index (`menu_item_id`),
  foreign key (`menu_item_id`) references `ohrm_menu_item` (`id`)
    on delete cascade on update cascade,
  index (`data_group_id`),
  foreign key (`data_group_id`) references `ohrm_data_group` (`id`)
    on delete cascade on update cascade
) engine = innodb default charset=utf8;

create table ohrm_home_page (
    `id` int(11) not null auto_increment,
    `user_role_id` int not null,
    `action` varchar(255),
    `enable_class` varchar(100) default null,
    `priority` int not null default 0 COMMENT 'lowest priority 0',
    primary key (`id`)
) engine=innodb default charset=utf8;

create table ohrm_module_default_page (
    `id` int(11) not null auto_increment,
    `module_id` int not null,
    `user_role_id` int not null,
    `action` varchar(255),
    `enable_class` varchar(100) default null,
    `priority` int not null default 0 COMMENT 'lowest priority 0',
    primary key (`id`)
) engine=innodb default charset=utf8;

CREATE TABLE ohrm_data_group_screen (
    `id` int AUTO_INCREMENT,
    `data_group_id` int not null,
    `screen_id` int not null,
    `permission` int,
    `self` tinyint default '0',
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_plugin (
    `id` int not null auto_increment,
    `name` varchar(100) not null UNIQUE,
    `version` varchar(32),
    `is_external` boolean not null default 0,
    primary key (`id`),
    key (`name`)
) engine=innodb default charset=utf8;

CREATE TABLE `ohrm_event_listener` (
    `id` INT AUTO_INCREMENT,
    `event` VARCHAR(128) NOT NULL,
    `listener_class` VARCHAR(255) NOT NULL,
    `enabled` TINYINT DEFAULT 0 NOT NULL,
    `position` INT NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_async_event`
(
    `id`             INT(4) AUTO_INCREMENT,
    `name`           VARCHAR(128)      NOT NULL,
    `display_name`   VARCHAR(255)      NULL,
    `group_name`     VARCHAR(255)      NULL,
    `listener_class` VARCHAR(255)      NULL,
    `enabled`        TINYINT DEFAULT 0 NOT NULL,
    `position`       INT               NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;


CREATE TABLE `ohrm_external_async_event_queue` (
  `id` int(4) not null auto_increment,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `enabled` int(1) NOT NULL,
  `is_delayed` int(1) NOT NULL,
  primary key (`id`)
) engine=innodb default charset=utf8;


CREATE TABLE `ohrm_external_async_event_tag` (
    `id` INT(4) AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `description` text NULL,
    `enabled` TINYINT DEFAULT 0 NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_external_data_queue_consumer` (
    `id` INT(4) AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `description` text NULL,
    `consumer_class` VARCHAR(255) NOT NULL,
    `enabled` TINYINT DEFAULT 0 NOT NULL,
    `queue_id` INT(4) DEFAULT 0 NOT NULL,
    `message_consume_limit` INT DEFAULT 25,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_ohrm_data_queue_consumer_async_event_queue` FOREIGN KEY (`queue_id`)
    REFERENCES `ohrm_external_async_event_queue` (`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_external_async_event_event_tag` (
    `id` INT(4) AUTO_INCREMENT,
    `event_id` INT(4) NOT NULL,
    `tag_id` INT(4) NOT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_ohrm_async_event_event_tag_event_id` FOREIGN KEY (`event_id`)
    REFERENCES `ohrm_async_event` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_async_event_event_tag_tag_id` FOREIGN KEY (`tag_id`)
    REFERENCES `ohrm_external_async_event_tag` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_external_async_event_tag_queue` (
    `id` int not null AUTO_INCREMENT,
    `queue_id` INT(4) NOT NULL ,
    `tag_id` INT(4) NOT NULL ,
    PRIMARY KEY(id),
    CONSTRAINT `fk_ohrm_async_event_tag_queue_id` FOREIGN KEY (`queue_id`)
    REFERENCES `ohrm_external_async_event_queue` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_async_event_event_tag_tag_tag_id` FOREIGN KEY (`tag_id`)
    REFERENCES `ohrm_external_async_event_tag` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

# CREATE TABLE `ohrm_pim_bulk_update_widget` (
#     `id` int not null AUTO_INCREMENT,
#     `widget_name` VARCHAR(255),
#     `position` int,
#     `enabled` TINYINT DEFAULT 0 NOT NULL,
#     `type` VARCHAR(255),
#     PRIMARY KEY(id)
# ) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_email_subscriber_user_role`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `notification_id` INT NOT NULL,
    `user_role_id` INT NOT NULL,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ohrm_user_config (
    `empid` BIGINT,
    `key` VARCHAR(100),
    `value` MEDIUMTEXT NOT NULL,
    PRIMARY KEY(empid, `key`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_notification_image` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `nt_id` INT NOT NULL,
    `image_content` longtext not null,
    `image_name` VARCHAR(100)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `ohrm_social_media_type` (
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `icon` BLOB,
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE ohrm_employee_social_media (
    `id` INT AUTO_INCREMENT,
    `emp_number` int(7) NOT NULL,
    `type_id` INT NOT NULL,
    `profile_name` VARCHAR(100),
    `allow_share` TINYINT( 2 ) NOT NULL DEFAULT '0',
    `profile_link` VARCHAR(1000),
    PRIMARY KEY(id),
    FOREIGN KEY (`type_id`) REFERENCES `ohrm_social_media_type`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee`(`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE  TABLE IF NOT EXISTS `ohrm_department_head` (
  `subunit_id` INT NOT NULL PRIMARY KEY,
  `employee_number` INT NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_my_shortcut` (
  `id` INT AUTO_INCREMENT,
  `shortcut_creator` INT,
  `name` VARCHAR(100),
  `state` VARCHAR(255),
  `params` MEDIUMTEXT,
  `status` TINYINT(1) DEFAULT '1' NOT NULL,
  PRIMARY KEY(`id`)) ENGINE = INNODB DEFAULT CHARSET=UTF8;

alter table `ohrm_email_subscriber_user_role`
   add foreign key (user_role_id) references ohrm_user_role(id) on delete cascade;

alter table `ohrm_email_subscriber_user_role`
   add foreign key (notification_id) references ohrm_email_notification(id) on delete cascade;

alter table ohrm_home_page
    add foreign key (user_role_id) references ohrm_user_role(id) on delete cascade;

alter table ohrm_module_default_page
    add foreign key (user_role_id) references ohrm_user_role(id) on delete cascade,
    add foreign key (module_id) references ohrm_module(id) on delete cascade;

alter table ohrm_menu_item
       add constraint foreign key (screen_id)
                             references ohrm_screen(id) on delete cascade;

alter table ohrm_user_role_data_group
       add constraint foreign key (user_role_id)
                             references ohrm_user_role(id) on delete cascade;

alter table ohrm_user_role_data_group
       add constraint foreign key (data_group_id)
                             references ohrm_data_group(id) on delete cascade;

ALTER TABLE `ohrm_email_notification`
    ADD FOREIGN KEY ( `email_id` )
        REFERENCES `ohrm_email` (`id`) ON DELETE CASCADE;

alter table ohrm_email_subscriber
       add constraint foreign key (notification_id)
                             references ohrm_email_notification(id) on delete cascade;

alter table ohrm_email_subscriber
       add constraint foreign key (employee_id)
                             references hs_hr_employee(emp_number) on delete set null;

alter table ohrm_email_template
    add foreign key (email_id)
        references ohrm_email(id) on delete cascade;

alter table ohrm_email_processor
    add foreign key (email_id)
        references ohrm_email(id) on delete cascade;

alter table ohrm_emp_termination
       add constraint foreign key (reason_id)
                             references ohrm_emp_termination_reason(id) on delete set null;

alter table ohrm_emp_termination
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

ALTER TABLE `ohrm_emp_termination`
    ADD CONSTRAINT `fk_termination_performer` FOREIGN KEY (termination_performed_user_id)
        REFERENCES ohrm_user(id) ON DELETE SET NULL;

ALTER TABLE `ohrm_emp_termination`
    ADD CONSTRAINT `fk_reactivation_performer` FOREIGN KEY (reactivation_performed_user_id)
        REFERENCES ohrm_user(id) ON DELETE SET NULL;

alter table ohrm_job_specification_attachment
       add constraint foreign key (job_title_id)
                             references ohrm_job_title(id) on delete cascade;

alter table ohrm_available_group_field
       add constraint foreign key (group_field_id)
                             references ohrm_group_field(group_field_id);

alter table ohrm_filter_field
       add constraint foreign key (report_group_id)
                             references ohrm_report_group(report_group_id) on delete cascade;

alter table ohrm_display_field
       add constraint foreign key (report_group_id)
                             references ohrm_report_group(report_group_id) on delete cascade;

alter table ohrm_display_field
       add constraint foreign key (display_field_group_id)
                             references ohrm_display_field_group(id) on delete set null;

alter table ohrm_composite_display_field
       add constraint foreign key (report_group_id)
                             references ohrm_report_group(report_group_id) on delete cascade;

alter table ohrm_composite_display_field
       add constraint foreign key (display_field_group_id)
                             references ohrm_display_field_group(id) on delete set null;

alter table ohrm_summary_display_field
       add constraint foreign key (display_field_group_id)
                             references ohrm_display_field_group(id) on delete set null;

alter table ohrm_selected_group_field
       add constraint foreign key (report_id)
                             references ohrm_report(report_id) on delete cascade;

alter table ohrm_selected_group_field
       add constraint foreign key (group_field_id)
                             references ohrm_group_field(group_field_id) on delete cascade;

alter table ohrm_selected_group_field
       add constraint foreign key (summary_display_field_id)
                             references ohrm_summary_display_field(summary_display_field_id);

alter table ohrm_selected_filter_field
       add constraint foreign key (report_id)
                             references ohrm_report(report_id) on delete cascade;

alter table ohrm_selected_filter_field
       add constraint foreign key (filter_field_id)
                             references ohrm_filter_field(filter_field_id) on delete cascade;

alter table ohrm_selected_display_field
       add constraint foreign key (report_id)
                             references ohrm_report(report_id) on delete cascade;

alter table ohrm_selected_display_field
       add constraint foreign key (display_field_id)
                             references ohrm_display_field(display_field_id) on delete cascade;

alter table ohrm_selected_composite_display_field
       add constraint foreign key (report_id)
                             references ohrm_report(report_id) on delete cascade;

alter table ohrm_selected_composite_display_field
       add constraint foreign key (composite_display_field_id)
                             references ohrm_composite_display_field(composite_display_field_id) on delete cascade;

alter table ohrm_report
       add constraint foreign key (report_group_id)
                             references ohrm_report_group(report_group_id) on delete cascade;

alter table ohrm_display_field_group
       add constraint foreign key (report_group_id)
                             references ohrm_report_group(report_group_id) on delete cascade;

alter table ohrm_selected_display_field_group
       add constraint foreign key (report_id)
                             references ohrm_report(report_id) on delete cascade;

alter table ohrm_selected_display_field_group
       add constraint foreign key (display_field_group_id)
                             references ohrm_display_field_group(id) on delete cascade;

alter table ohrm_pay_grade_currency
       add constraint `fk_ohrm_pay_grade_currency_currency_id` foreign key (currency_id)
                             references hs_hr_currency_type(currency_id) on delete cascade;

alter table ohrm_pay_grade_currency
       add constraint `fk_ohrm_pay_grade_currency_pay_grade_id` foreign key (pay_grade_id)
                             references ohrm_pay_grade(id) on delete cascade;

alter table ohrm_location
       add constraint foreign key (country_code)
                             references hs_hr_country(cou_code) on delete cascade;

alter table hs_hr_employee
       add constraint foreign key (work_station)
                             references ohrm_subunit(id) on delete set null on update cascade;

alter table hs_hr_employee
       add constraint foreign key (nation_code)
                             references ohrm_nationality(id) on delete set null;

ALTER TABLE `hs_hr_employee` ADD CONSTRAINT `hs_hr_employee_gender` FOREIGN KEY (`emp_gender`) REFERENCES `ohrm_gender` (`id`) ON DELETE SET NULL;

alter table hs_hr_employee
       add constraint foreign key (job_title_code)
                             references ohrm_job_title(id) on delete set null;

alter table hs_hr_employee
       add constraint foreign key (emp_status)
                             references ohrm_employment_status(id) on delete set null;

alter table hs_hr_employee
       add constraint foreign key (eeo_cat_code)
                             references ohrm_job_category(id) on delete set null;

alter table hs_hr_employee
       add constraint foreign key (termination_id)
                             references ohrm_emp_termination(id) on delete set null;

alter table hs_hr_employee
    add constraint `hs_hr_employee_ohrm_work_schedule` foreign key (work_schedule_id)
        references ohrm_work_schedule(id) on delete restrict on update cascade;

alter table hs_hr_emp_children
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_dependents
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_dependents
       add constraint foreign key (ed_nation_code)
                             references ohrm_nationality(id) on delete set null;

alter table hs_hr_emp_emergency_contacts
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_emp_license
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_emp_license
       add constraint foreign key (license_id)
                             references ohrm_license(id) on delete cascade;

alter table hs_hr_emp_skill
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_skill
       add constraint foreign key (skill_id)
                             references ohrm_skill(id) on delete cascade;

alter table hs_hr_emp_attachment
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_picture
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_emp_education
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table ohrm_emp_education
       add constraint foreign key (education_id)
                             references ohrm_education(id) on delete cascade;

alter table hs_hr_emp_work_experience
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_passport
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_member_detail
       add constraint foreign key (membship_code)
                             references ohrm_membership(id) on delete cascade;

alter table hs_hr_emp_member_detail
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_reportto
       add constraint foreign key (erep_sup_emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_reportto
       add constraint foreign key (erep_sub_emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_reportto
       add constraint foreign key (erep_reporting_mode)
                             references ohrm_emp_reporting_method(reporting_method_id) on delete cascade;

alter table hs_hr_emp_language
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_emp_language
       add constraint foreign key (lang_id)
                             references ohrm_language(id) on delete cascade;

alter table hs_hr_emp_us_tax
       add constraint foreign key (emp_number)
                             references hs_hr_employee(emp_number) on delete cascade;

alter table hs_hr_mailnotifications
       add constraint foreign key (user_id)
       						references ohrm_user(id) on delete cascade;

alter table `ohrm_project_admin`
  add constraint foreign key (`project_id`) references `ohrm_project` (`project_id`) on delete cascade,
  add constraint foreign key (`emp_number`) references `hs_hr_employee` (`emp_number`) on delete cascade;

alter table `ohrm_user`
    add constraint foreign key (`emp_number`)
        references hs_hr_employee(`emp_number`) on delete cascade;

alter table `ohrm_user`
    add constraint foreign key (`user_role_id`)
        references ohrm_user_role(`id`) on delete restrict;

ALTER TABLE `ohrm_operational_country`
ADD CONSTRAINT `fk_ohrm_operational_country_hs_hr_country`
    FOREIGN KEY (`country_code`)
    REFERENCES `hs_hr_country` (`cou_code`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

ALTER TABLE `ohrm_holiday`
ADD CONSTRAINT `fk_ohrm_holiday_ohrm_operational_country`
    FOREIGN KEY (`operational_country_id`)
    REFERENCES `ohrm_operational_country` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

alter table ohrm_screen
       add constraint foreign key (module_id)
                             references ohrm_module(id) on delete cascade;
alter table ohrm_user_role_screen
       add constraint foreign key (user_role_id)
                             references ohrm_user_role(id) on delete cascade;
alter table ohrm_user_role_screen
       add constraint foreign key (screen_id)
                             references ohrm_screen(id) on delete cascade;

alter table ohrm_data_group_screen
    add foreign key (data_group_id) references ohrm_data_group(id) on delete cascade;

alter table ohrm_data_group_screen
    add foreign key (screen_id) references ohrm_screen(id) on delete cascade;

alter table ohrm_user_role_type_data_group
    add foreign key (user_role_type_id) references ohrm_user_role_type(id) on delete cascade;

alter table ohrm_user_role_type_data_group
    add foreign key (data_group_id) references ohrm_data_group(id) on delete cascade;

alter table ohrm_user_role
    add foreign key (type_id) references ohrm_user_role_type(id) on delete cascade;

alter table ohrm_user_user_role
       add constraint foreign key (user_role_id)
            references ohrm_user_role(id) on delete cascade;
alter table ohrm_user_user_role
       add constraint foreign key (user_id)
            references ohrm_user(id) on delete cascade;

alter table ohrm_user_user_role
       add constraint foreign key (user_role_type_id)
            references ohrm_user_role_type(id) on delete cascade;

alter table ohrm_default_permitted_screen
    add foreign key (user_role_type_id) references ohrm_user_role_type(id) on delete cascade;

alter table ohrm_default_permitted_screen
    add foreign key (screen_id) references ohrm_screen(id) on delete cascade;

alter table ohrm_workflow_state_machine
    add foreign key (workflow) references ohrm_workflow(`id`) on delete cascade;

ALTER TABLE `ohrm_workflow_state_machine` ADD CONSTRAINT `fk_ohrm_workflow_state_machine_ohrm_user_role`
    FOREIGN KEY (`role`) REFERENCES `ohrm_user_role`(`name`)  ON DELETE CASCADE ON UPDATE CASCADE;

alter table ohrm_workflow_screen
    add foreign key (workflow_name) references ohrm_workflow(`name`) on delete cascade;

ALTER TABLE `hs_hr_employee` ADD CONSTRAINT `hs_hr_employee_location` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

CREATE VIEW  ohrm_application_change_log AS
(SELECT change_type, concat('build name has change from "', IFNULL(prev_build_name, ''), '" to "', current_build_name, '".\n build version is changed from "', IFNULL(prev_version, ''), '" to "', current_version, '"') as description, `timestamp` FROM ohrm_application_log)
UNION (SELECT 'CONFIG', concat('config value "', property,'" has changed from "', IFNULL(prev_value, ''), '" to "', current_value, '"') as description, `timestamp` FROM ohrm_config_log)
ORDER BY `timestamp`;

alter table ohrm_custom_field
    add foreign key (custom_field_section_id) references ohrm_custom_field_section(`id`) on delete cascade;

alter table ohrm_custom_field_screen
    add foreign key (screen_id) references ohrm_screen(`id`) on delete cascade,
    add foreign key (data_group_id) references ohrm_data_group(`id`) on delete cascade;

alter table ohrm_custom_field_section
    add foreign key (custom_field_screen_id) references ohrm_custom_field_screen(`id`) on delete cascade;

alter table ohrm_email_notification
    add foreign key (type_id) references ohrm_email_notification_type(`id`) on delete cascade;

alter table `ohrm_department_head`
   add foreign key (`subunit_id`) references `ohrm_subunit`(`id`) on delete cascade;

alter table `ohrm_department_head`
   add foreign key (`employee_number`) references `hs_hr_employee`(`emp_number`) on delete cascade;

CREATE TABLE ohrm_lang_past_tense (
  action VARCHAR(25) UNIQUE,
  past_tense VARCHAR(25),
  PRIMARY KEY(action)
) ENGINE = INNODB default charset=utf8;

ALTER TABLE `ohrm_custom_field_screen` ADD CONSTRAINT `ohrm_custom_field_screen_menu_id_fk_ohrm_menu_item_id` FOREIGN KEY (`menu_id`) REFERENCES `ohrm_menu_item`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE `ohrm_acknowledgement` (
  id INT NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(255) NOT NULL,
  entity_id INT(11) NOT NULL,
  employee_id INT(11) NOT NULL,
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_acknowledge TINYINT(2) NULL,
  CONSTRAINT unique_acknowledgement_record UNIQUE (entity_type,entity_id,employee_id),
  PRIMARY KEY(id)
) ENGINE = InnoDB default charset=utf8;

alter table `ohrm_acknowledgement` ADD CONSTRAINT `ohrm_acknowledgement_employee_id_fk_hs_hr_employee_emp_number`
  foreign key (`employee_id`) references `hs_hr_employee`(`emp_number`) on delete cascade;

CREATE TABLE IF NOT EXISTS ohrm_data_list_settings (
  id int(10) AUTO_INCREMENT,
  user_id int(10),
  data_list_id int(10) NOT NULL,
  report_id int(10),
  search_parameters MEDIUMTEXT,
  meta_data MEDIUMTEXT,
  PRIMARY KEY(id),
  CONSTRAINT `fk_ohrm_data_list_settings_ohrm_user` FOREIGN KEY (`user_id`)
    REFERENCES `ohrm_user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS ohrm_data_list (
  id int(10) AUTO_INCREMENT,
  name varchar(255) UNIQUE ,
  meta_data MEDIUMTEXT,
  PRIMARY KEY(id)
) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_token_section` (
    `id` int(11) AUTO_INCREMENT,
    `name` VARCHAR(255),
    `display_name` VARCHAR(255),
    `menu_id` int(11),
    `custom_field_section_id` int DEFAULT NULL,
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_token_section_menu_item` FOREIGN KEY (`menu_id`)  REFERENCES `ohrm_menu_item` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `fk_token_section_custom_field_section` FOREIGN KEY (`custom_field_section_id`) REFERENCES `ohrm_custom_field_section` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_token`(
    `id` INT AUTO_INCREMENT,
    `name` VARCHAR(1275) NOT NULL,
    `display_name`  VARCHAR(255) NOT NULL,
    `class_name` VARCHAR(255),
    `entity_name` VARCHAR(255),
    `value_getters` LONGTEXT,
    `data_group_id` INT,
    `is_date` INT(1) NOT NULL DEFAULT '0',
    `localize` INT(1) NOT NULL DEFAULT '0',
    `parameters` VARCHAR(255),
    `custom_field_id` INT(11),
    `token_section_id` INT(11),
    `is_multi_attribute` INT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY(`id`),
    CONSTRAINT `fk_token_token_section` FOREIGN KEY (`token_section_id`)  REFERENCES `ohrm_token_section` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (`data_group_id`)  REFERENCES `ohrm_data_group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_restricted_module_token`(
    `id` INT AUTO_INCREMENT,
    `token_id` INT,
    `restricted_module` INT,
    PRIMARY KEY(`id`),
    FOREIGN KEY (`token_id`)  REFERENCES `ohrm_token` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`restricted_module`)  REFERENCES `ohrm_module` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_zip_code` (
  `id` INT AUTO_INCREMENT,
  `code` VARCHAR(5) NOT NULL,
  `county` VARCHAR(255) DEFAULT NULL,
  `cou_code` VARCHAR(2) NOT NULL,
  PRIMARY KEY(`id`),
  FOREIGN KEY (`cou_code`)  REFERENCES `hs_hr_country` (`cou_code`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_naics_code` (
  `id` INT AUTO_INCREMENT,
  `code` VARCHAR(6) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_us_state` (
  `id` INT AUTO_INCREMENT,
  `code` VARCHAR(2) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE = INNODB default charset=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_time_zones` (
    `prefix` varchar(15) NOT NULL UNIQUE,
    `name` varchar(255) NOT NULL default '',
    `offset` decimal(4,2) NOT NULL DEFAULT 0,
    primary key  (`prefix`)
    ) engine=innodb default charset=utf8;

CREATE TABLE ohrm_cleanup_entities_config (
    id INT AUTO_INCREMENT,
    entity_name VARCHAR(50),
    configuration VARCHAR(400),
    delete_days_after INT,
    date_configuration_getter VARCHAR(50),
    delete_strategy VARCHAR(50),
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE SEQUENCE hs_hr_employee_employee_id START WITH 1 INCREMENT BY 1 CACHE 0 CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE SEQUENCE ohrm_custom_field_value_sequence_number START WITH 1 INCREMENT BY 1 CACHE 0 CHARACTER SET utf8 COLLATE utf8_general_ci;
