
CREATE TABLE ohrm_claim_employee_eligibility_criteria_filter (
  `id`              INT(4) AUTO_INCREMENT,
  `name`            VARCHAR(255),
  `widget_class`    VARCHAR(255),
  `validator_class` VARCHAR(255),
  `processor_class` VARCHAR(255),
  `active`          TINYINT(1) DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_employee_group (
  `id`       INT(4) AUTO_INCREMENT,
  `name`     VARCHAR(255),
  `priority` INT(4),
  `deleted`  TINYINT(1) DEFAULT '0' NOT NULL,
  `config`   TEXT,
  `type`     TEXT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_emp_group_emp_filter (
  `group_id`  INT(4),
  `filter_id` INT(4),
  `config`    TEXT,
  PRIMARY KEY (`group_id`, `filter_id`),
  CONSTRAINT `fk_ohrm_claim_emp_group_emp_filter_ohrm_claim_employee_group` FOREIGN KEY (`group_id`)
    REFERENCES `ohrm_claim_employee_group` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_emp_group_emp_filter_employee_eligibility_criteria_filter` FOREIGN KEY (`filter_id`)
    REFERENCES `ohrm_claim_employee_eligibility_criteria_filter` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;



CREATE TABLE ohrm_claim_currency (
  `id`          INT(4) AUTO_INCREMENT,
  `currency_id` VARCHAR(3),
  `active`      TINYINT(1) DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_currency_hs_hr_currency_type` FOREIGN KEY (`currency_id`)
    REFERENCES `hs_hr_currency_type` (`currency_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_claiming_option (
  `id`           INT(4) AUTO_INCREMENT,
  `name`         VARCHAR(255),
  `label`        VARCHAR(255),
  `bridge_class` VARCHAR(255),
  `active`       TINYINT(1) DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;



CREATE TABLE ohrm_claim_request (
  `id`                 INT(4) AUTO_INCREMENT,
  `request_id`         VARCHAR(255) NOT NULL,
  `emp_number`         INT(4),
  `claim_date`         DATE,
  `currency_id`        INT(4),
  `status`             VARCHAR(64),
  `created_date`       DATETIME,
  `submitted_date`     DATE,
  `created_by`         INT(4),
  `claiming_option_id` INT(4),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_request_hs_hr_employee_emp_number` FOREIGN KEY (`emp_number`)
    REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_request_hs_hr_employee_created_by` FOREIGN KEY (`created_by`)
    REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_request_ohrm_claim_claiming_option` FOREIGN KEY (`claiming_option_id`)
    REFERENCES `ohrm_claim_claiming_option` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_request_ohrm_claim_currency` FOREIGN KEY (`currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_request_attachment (
  `id`                 INT          AUTO_INCREMENT,
  `request_id`         INT(4) NOT NULL,
  `document_date`      DATE,
  `eattach_desc`       VARCHAR(200) DEFAULT NULL,
  `eattach_filename`   VARCHAR(255) DEFAULT NULL,
  `eattach_size`       INT(4)      DEFAULT '0',
  `eattach_attachment` MEDIUMBLOB,
  `eattach_type`       VARCHAR(200) DEFAULT NULL,
  `attached_by`        INT(4)       DEFAULT NULL,
  `attached_by_name`   VARCHAR(200),
  `attached_time`      TIMESTAMP NOT NULL DEFAULT now(),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_request_attachment_ohrm_claim_request` FOREIGN KEY (`request_id`)
    REFERENCES `ohrm_claim_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_request_restriction_filter (
  `id`          INT(4) AUTO_INCREMENT,
  `class`       VARCHAR(255),
  `eligibility` DATE,
  `active`      TINYINT(1) DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_currency_exchange_rate (
  `id`                  INT(4) AUTO_INCREMENT,
  `local_currency_id`   INT(4),
  `foreign_currency_id` INT(4),
  `is_same_currency`    TINYINT        NOT NULL,
  `rate`                DECIMAL(20, 6) NOT NULL,
  `from_date`           DATE,
  `to_date`             DATE,
  `created_date`        DATETIME,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_currency_exchange_rate_ohrm_claim_currency_local` FOREIGN KEY (`local_currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_currency_exchange_rate_ohrm_claim_currency_foreign` FOREIGN KEY (`foreign_currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_type_group (
  `id`                    INT(4) AUTO_INCREMENT,
  `name`                  VARCHAR(255)        NOT NULL,
  `deleted`               TINYINT(1) DEFAULT '0' NOT NULL,
  `form_class`            VARCHAR(255),
  `estimation_form_class` VARCHAR(255)        NOT NULL,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_type (
  `id`                           INT(4) AUTO_INCREMENT,
  `name`                         VARCHAR(255)        NOT NULL,
  `deleted`                      TINYINT(1) DEFAULT '0' NOT NULL,
  `predefined`                   TINYINT(1) DEFAULT '0' NOT NULL,
  `active`                       TINYINT(1) DEFAULT '0' NOT NULL,
  `restrictions_applicable`      TINYINT(1) DEFAULT '0' NOT NULL,
  `multi_currency_applicable`    TINYINT(1) DEFAULT '0' NOT NULL,
  `is_mandatory`                 TINYINT(1) DEFAULT '0' NOT NULL,
  `is_eligibility_applicable`    TINYINT(1) DEFAULT '0' NOT NULL,
  `eligibility`                  TEXT,
  `expense_type_group_id`        INT(4),
  `currency_id`                  INT(4),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_expense_type_ohrm_claim_expense_type_group` FOREIGN KEY (`expense_type_group_id`)
    REFERENCES `ohrm_claim_expense_type_group` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_expense_type_ohrm_claim_currency` FOREIGN KEY (`currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_paid_by (
  `id`                      INT(4) AUTO_INCREMENT,
  `name_of_expense_paid_by` VARCHAR(255),
  `is_used_calculate_sum`   TINYINT(1),
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense (
  `id`              INT(4)          AUTO_INCREMENT,
  `amount`          DECIMAL(20, 5)      NOT NULL,
  `date`            DATE,
  `status`          VARCHAR(64),
  `created_date`    DATETIME,
  `expense_type_id` INT(4),
  `request_id`      INT(4),
  `currency_id`     INT(4),
  `paid_by_id`      INT(4),
  `exchange_rate`   DECIMAL(20, 6)      NOT NULL,
  `custom`          TINYINT(1) DEFAULT '0' NOT NULL,
  `remarks`         TEXT,
  `comment`         TEXT,
  `departure`       VARCHAR(500) DEFAULT NULL,
  `destination`     VARCHAR(500) DEFAULT NULL,
  `means`           VARCHAR(255) DEFAULT NULL,
  `check_in_date`     DATE,
  `check_out_date`    DATE,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_expense_ohrm_claim_expense_type` FOREIGN KEY (`expense_type_id`)
    REFERENCES `ohrm_claim_expense_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_expense_ohrm_claim_request` FOREIGN KEY (`request_id`)
    REFERENCES `ohrm_claim_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_expense_ohrm_claim_currency` FOREIGN KEY (`currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_expense_ohrm_claim_expense_paid_by` FOREIGN KEY (`paid_by_id`)
    REFERENCES `ohrm_claim_expense_paid_by` (`id`)
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_attachment (
  `id`                 INT          AUTO_INCREMENT,
  `expense_id`         INT(4) NOT NULL,
  `document_date`      DATE,
  `eattach_desc`       VARCHAR(200) DEFAULT NULL,
  `eattach_filename`   VARCHAR(255) DEFAULT NULL,
  `eattach_size`       INT(4)      DEFAULT '0',
  `eattach_attachment` MEDIUMBLOB,
  `eattach_type`       VARCHAR(200) DEFAULT NULL,
  `attached_by`        INT(4)          DEFAULT NULL,
  `attached_by_name`   VARCHAR(200),
  `attached_time`      TIMESTAMP NOT NULL DEFAULT now(),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_expense_attachment_ohrm_claim_expense` FOREIGN KEY (`expense_id`)
    REFERENCES `ohrm_claim_expense` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_type_currency (
  `id`              INT(4) AUTO_INCREMENT,
  `expense_type_id` INT(4),
  `currency_id`     INT(4),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_expense_type_currency_ohrm_claim_expense_type` FOREIGN KEY (`expense_type_id`)
    REFERENCES `ohrm_claim_expense_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_expense_type_currency_ohrm_claim_currency` FOREIGN KEY (`currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_type_eligibility (
  `id`                INT(4) AUTO_INCREMENT,
  `expense_type_id`   INT(4),
  `employee_group_id` INT(4),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_expense_type_eligibility_ohrm_claim_expense_type` FOREIGN KEY (`expense_type_id`)
    REFERENCES `ohrm_claim_expense_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_expense_type_eligibility_ohrm_claim_employee_group` FOREIGN KEY (`employee_group_id`)
    REFERENCES `ohrm_claim_employee_group` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_type_restriction_filter (
  `id`          INT(4) AUTO_INCREMENT,
  `class`       VARCHAR(255),
  `eligibility` TEXT,
  `active`      TINYINT(1) DEFAULT '0' NOT NULL,
  `widget`      VARCHAR(255),
  `validator`   VARCHAR(255),
  `description` TEXT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_type_restriction (
  `expense_type_id`       INT(4),
  `restriction_filter_id` INT(4),
  `rule`                  TEXT,
  PRIMARY KEY (`expense_type_id`, `restriction_filter_id`),
  CONSTRAINT `fk_ohrm_claim_expense_type_restriction_ohrm_claim_expense_type` FOREIGN KEY (`expense_type_id`)
    REFERENCES `ohrm_claim_expense_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_expense_type_restriction_expense_type_restriction_filter` FOREIGN KEY (`restriction_filter_id`)
    REFERENCES `ohrm_claim_expense_type_restriction_filter` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_estimate_request (
  `id`                     INT(4) AUTO_INCREMENT,
  `estimate_id`            VARCHAR(255),
  `is_cash_advance_enable` TINYINT(1),
  `cash_in_advance`        DECIMAL(20, 5),
  `state`                  VARCHAR(255),
  `comment`                TEXT,
  `purpose`                VARCHAR(255),
  `emp_number`             INT(4),
  `created_by`             INT(4) DEFAULT NULL,
  `local_currency_id`      INT(4),
  `submitted_date`         DATE,
  `created_date`           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_estimate_request_hs_hr_employee_emp_number` FOREIGN KEY (`emp_number`)
    REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_estimate_request_hs_hr_employee_created_by` FOREIGN KEY (`created_by`)
    REFERENCES `hs_hr_employee` (`emp_number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_estimate_request_ohrm_claim_currency` FOREIGN KEY (`local_currency_id`)
    REFERENCES `ohrm_claim_currency` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_estimate_request_history (
  `id`                  INT(4) AUTO_INCREMENT,
  `estimate_request_id` INT(4),
  `action_description`  VARCHAR(255),
  `action_date`         DATETIME,
  `comment`             TEXT,
  `action_owner_id`     INT,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_claim_estimate_request_history_claim_estimate_request` FOREIGN KEY (`estimate_request_id`)
    REFERENCES `ohrm_claim_estimate_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_claim_estimate_request_history_ohrm_user` FOREIGN KEY (`action_owner_id`)
    REFERENCES `ohrm_user` (`id`)
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_estimates (
  `id`                    INT(4) AUTO_INCREMENT,
  `expense_type_id`       INT(4),
  `paid_by_id`            INT(4),
  `exchange_rate_id`      INT(4),
  `estimate_request_id`   INT(4),
  `amount`                DECIMAL(20, 5)  NOT NULL,
  `date_estimate_added`   DATE,
  `now_of_days_travelled` INT(4) DEFAULT '0' NOT NULL,
  `remarks`         TEXT,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_estimates_ohrm_claim_expense_type` FOREIGN KEY (`expense_type_id`)
    REFERENCES `ohrm_claim_expense_type` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_estimates_ohrm_claim_expense_paid_by` FOREIGN KEY (`paid_by_id`)
    REFERENCES `ohrm_claim_expense_paid_by` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_estimates_ohrm_claim_currency_exchange_rate` FOREIGN KEY (`exchange_rate_id`)
    REFERENCES `ohrm_claim_currency_exchange_rate` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_estimates_ohrm_claim_estimate_request` FOREIGN KEY (`estimate_request_id`)
    REFERENCES `ohrm_claim_estimate_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_estimation_claiming (
  `id`                    INT(4) AUTO_INCREMENT,
  `claim_request_id`      INT(4),
  `estimation_request_id` INT(4),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_estimation_claiming_ohrm_claim_request` FOREIGN KEY (`claim_request_id`)
    REFERENCES `ohrm_claim_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_estimation_claiming_ohrm_claim_estimate_request` FOREIGN KEY (`estimation_request_id`)
    REFERENCES `ohrm_claim_estimate_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_project_claiming (
  `id`               INT(4) AUTO_INCREMENT,
  `claim_request_id` INT(4),
  `project_id`       INT(4),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_project_claiming_ohrm_claim_request` FOREIGN KEY (`claim_request_id`)
    REFERENCES `ohrm_claim_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_project_claiming_ohrm_project` FOREIGN KEY (`project_id`)
    REFERENCES `ohrm_project` (`project_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE `ohrm_claim_estimate_request_attachment` (
  `id`                 INT          AUTO_INCREMENT,
  `request_id`         INT(4) NOT NULL,
  `document_date`      DATE,
  `eattach_desc`       VARCHAR(200) DEFAULT NULL,
  `eattach_filename`   VARCHAR(255) DEFAULT NULL,
  `eattach_size`       INT(4)      DEFAULT '0',
  `eattach_attachment` MEDIUMBLOB,
  `eattach_type`       VARCHAR(200) DEFAULT NULL,
  `attached_by`        INT(4)          DEFAULT NULL,
  `attached_by_name`   VARCHAR(200),
  `attached_time`      TIMESTAMP NOT NULL DEFAULT now(),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_claim_estimate_request_attachment_claim_estimate_request` FOREIGN KEY (`request_id`)
    REFERENCES `ohrm_claim_estimate_request` (`id`)
    ON DELETE CASCADE
)
  ENGINE = innodb
  DEFAULT CHARSET = utf8;

CREATE TABLE ohrm_claim_request_history (
  `id`               INT(4) AUTO_INCREMENT,
  `claim_request_id` INT(4),
  `action`           TEXT,
  `comment`          TEXT,
  `date`             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `action_owner_id`  INT,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_claim_request_history_ohrm_claim_request` FOREIGN KEY (`claim_request_id`)
    REFERENCES `ohrm_claim_request` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_claim_request_history_ohrm_user` FOREIGN KEY (`action_owner_id`)
    REFERENCES `ohrm_user` (`id`)
    ON UPDATE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_travel_information (
    id INTEGER AUTO_INCREMENT,
    request_id INTEGER,
    main_destination VARCHAR(255),
    travel_period_from DATE,
    travel_period_to DATE,
    departure_time VARCHAR(5),
    destination_address VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT `fk_travel_information_estimate_request` FOREIGN KEY (`request_id`) REFERENCES `ohrm_claim_estimate_request` (`id`) ON DELETE CASCADE
)
DEFAULT CHARACTER SET UTF8
ENGINE = INNODB;
