
CREATE TABLE ohrm_claim_emp_group_emp_filter (
  `group_id`  INT,
  `filter_id` INT,
  `config`    TEXT,
  PRIMARY KEY (`group_id`, `filter_id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;



CREATE TABLE ohrm_claim_currency (
  `id`          INT AUTO_INCREMENT,
  `currency_id` VARCHAR(3),
  `active`      TINYINT DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;



CREATE TABLE ohrm_claim_request (
  `id`                 INT AUTO_INCREMENT,
  `request_id`         VARCHAR(255) NOT NULL,
  `emp_number`         INT,
  `claim_date`         DATE,
  `currency_id`        INT,
  `status`             VARCHAR(64),
  `created_date`       DATETIME,
  `submitted_date`     DATE,
  `created_by`         INT,
  `claiming_option_id` INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_request_attachment (
  `id`                 INT          AUTO_INCREMENT,
  `request_id`         INT(7) NOT NULL,
  `document_date`      DATE,
  `eattach_desc`       VARCHAR(200) DEFAULT NULL,
  `eattach_filename`   VARCHAR(255) DEFAULT NULL,
  `eattach_size`       INT(11)      DEFAULT '0',
  `eattach_attachment` MEDIUMBLOB,
  `eattach_type`       VARCHAR(200) DEFAULT NULL,
  `attached_by`        INT          DEFAULT NULL,
  `attached_by_name`   VARCHAR(200),
  `attached_time`      TIMESTAMP    DEFAULT now(),
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

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

CREATE TABLE ohrm_claim_currency_exchange_rate (
  `id`                  INT AUTO_INCREMENT,
  `local_currency_id`   INT,
  `foreign_currency_id` INT,
  `is_same_currency`    TINYINT        NOT NULL,
  `rate`                DECIMAL(20, 5) NOT NULL,
  `from_date`           DATE,
  `to_date`             DATE,
  `created_date`        DATETIME,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

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

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

CREATE TABLE ohrm_claim_expense (
  `id`              INT          AUTO_INCREMENT,
  `amount`          DECIMAL(20, 5)      NOT NULL,
  `date`            DATE,
  `status`          VARCHAR(64),
  `created_date`    DATETIME,
  `expense_type_id` INT,
  `request_id`      INT,
  `currency_id`     INT,
  `paid_by_id`      INT,
  `exchange_rate`   DECIMAL(20, 5)      NOT NULL,
  `custom`          TINYINT DEFAULT '0' NOT NULL,
  `remarks`         TEXT,
  `comment`         TEXT,
  `departure`       VARCHAR(500) DEFAULT NULL,
  `destination`     VARCHAR(500) DEFAULT NULL,
  `means`           VARCHAR(500) DEFAULT NULL,
  `check_in_date`     DATE,
  `check_out_date`    DATE,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_attachment (
  `id`                 INT          AUTO_INCREMENT,
  `expense_id`         INT(7) NOT NULL,
  `document_date`      DATE,
  `eattach_desc`       VARCHAR(200) DEFAULT NULL,
  `eattach_filename`   VARCHAR(255) DEFAULT NULL,
  `eattach_size`       INT(11)      DEFAULT '0',
  `eattach_attachment` MEDIUMBLOB,
  `eattach_type`       VARCHAR(200) DEFAULT NULL,
  `attached_by`        INT          DEFAULT NULL,
  `attached_by_name`   VARCHAR(200),
  `attached_time`      TIMESTAMP    DEFAULT now(),
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_type (
  `id`                           INT AUTO_INCREMENT,
  `name`                         VARCHAR(255)        NOT NULL,
  `deleted`                      TINYINT DEFAULT '0' NOT NULL,
  `predefined`                   TINYINT DEFAULT '0' NOT NULL,
  `active`                       TINYINT DEFAULT '0' NOT NULL,
  `restrictions_applicable`      TINYINT DEFAULT '0' NOT NULL,
  `multi_currency_applicable`    TINYINT DEFAULT '0' NOT NULL,
  `is_mandatory`                 TINYINT DEFAULT '0' NOT NULL,
  `is_eligibility_applicable`    TINYINT DEFAULT '0' NOT NULL,
  `eligibility`                  TEXT,
  `expense_type_group_id`        INT,
  `currency_id`                  INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


CREATE TABLE ohrm_claim_expense_type_currency (
  `id`              INT AUTO_INCREMENT,
  `expense_type_id` INT,
  `currency_id`     INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_type_eligibility (
  `id`                INT AUTO_INCREMENT,
  `expense_type_id`   INT,
  `employee_group_id` INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_type_group (
  `id`                    INT AUTO_INCREMENT,
  `name`                  VARCHAR(255)        NOT NULL,
  `deleted`               TINYINT 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_restriction (
  `expense_type_id`       INT,
  `restriction_filter_id` INT,
  `rule`                  TEXT,
  PRIMARY KEY (`expense_type_id`, `restriction_filter_id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_type_restriction_filter (
  `id`          INT AUTO_INCREMENT,
  `class`       VARCHAR(255),
  `eligibility` TEXT,
  `active`      TINYINT 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_estimate_request (
  `id`                     INT AUTO_INCREMENT,
  `estimate_id`            VARCHAR(255),
  `is_cash_advance_enable` TINYINT(1),
  `cash_in_advance`        DECIMAL(20, 5),
  `state`                  VARCHAR(255),
  `comment`                TEXT,
  `purpose`                TEXT,
  `emp_number`             INT,
  `created_by`             INT DEFAULT NULL,
  `local_currency_id`      INT,
  `submitted_date`         DATE,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

ALTER TABLE `ohrm_claim_estimate_request`
ADD created_date DATETIME;

ALTER TABLE `ohrm_claim_estimate_request`
CHANGE `created_date` `created_date`
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE TABLE ohrm_claim_estimate_request_history (
  `id`                  INT AUTO_INCREMENT,
  `estimate_request_id` INT,
  `action_description`  VARCHAR(255),
  `action_date`         DATETIME,
  `comment`             TEXT,
  `action_owner_id`     INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_estimates (
  `id`                    INT AUTO_INCREMENT,
  `expense_type_id`       INT,
  `paid_by_id`            INT,
  `exchange_rate_id`      INT,
  `estimate_request_id`   INT,
  `amount`                DECIMAL(20, 5)  NOT NULL,
  `date_estimate_added`   DATE,
  `now_of_days_travelled` INT DEFAULT '0' NOT NULL,
  `remarks`         TEXT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_expense_paid_by (
  `id`                      INT 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_claiming_option (
  `id`           INT AUTO_INCREMENT,
  `name`         VARCHAR(255),
  `label`        VARCHAR(255),
  `bridge_class` VARCHAR(255),
  `active`       INT DEFAULT '0' NOT NULL,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_estimation_claiming (
  `id`                    INT AUTO_INCREMENT,
  `claim_request_id`      INT,
  `estimation_request_id` INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_claim_project_claiming (
  `id`               INT AUTO_INCREMENT,
  `claim_request_id` INT,
  `project_id`       INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE `ohrm_claim_estimate_request_attachment` (
  `id`                 INT          AUTO_INCREMENT,
  `request_id`         INT(7) NOT NULL,
  `document_date`      DATE,
  `eattach_desc`       VARCHAR(200) DEFAULT NULL,
  `eattach_filename`   VARCHAR(255) DEFAULT NULL,
  `eattach_size`       INT(11)      DEFAULT '0',
  `eattach_attachment` MEDIUMBLOB,
  `eattach_type`       VARCHAR(200) DEFAULT NULL,
  `attached_by`        INT          DEFAULT NULL,
  `attached_by_name`   VARCHAR(200),
  `attached_time`      TIMESTAMP    DEFAULT now(),
  PRIMARY KEY (`id`)
)
  ENGINE = innodb
  DEFAULT CHARSET = utf8;


CREATE TABLE ohrm_claim_request_history (
  `id`               INT AUTO_INCREMENT,
  `claim_request_id` INT,
  `action`           TEXT,
  `comment`          TEXT,
  `date`             TIMESTAMP,
  `action_owner_id`  INT,
  PRIMARY KEY (`id`)
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


ALTER TABLE `ohrm_claim_request` ADD FOREIGN KEY (`emp_number`)
REFERENCES `hs_hr_employee` (`emp_number`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_request` ADD FOREIGN KEY (`created_by`)
REFERENCES `hs_hr_employee` (`emp_number`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_request` ADD FOREIGN KEY (`claiming_option_id`)
REFERENCES `ohrm_claim_claiming_option` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_request_attachment` ADD FOREIGN KEY (`request_id`)
REFERENCES `ohrm_claim_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_type` ADD FOREIGN KEY (`expense_type_group_id`)
REFERENCES `ohrm_claim_expense_type_group` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;



ALTER TABLE `ohrm_claim_expense_type` ADD FOREIGN KEY (`currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense` ADD FOREIGN KEY (`expense_type_id`)
REFERENCES `ohrm_claim_expense_type` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense` ADD FOREIGN KEY (`request_id`)
REFERENCES `ohrm_claim_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense` ADD FOREIGN KEY (`currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_attachment` ADD FOREIGN KEY (`expense_id`)
REFERENCES `ohrm_claim_expense` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_type_restriction` ADD FOREIGN KEY (`expense_type_id`)
REFERENCES `ohrm_claim_expense_type` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_type_restriction` ADD FOREIGN KEY (`restriction_filter_id`)
REFERENCES `ohrm_claim_expense_type_restriction_filter` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;



ALTER TABLE `ohrm_claim_emp_group_emp_filter` ADD FOREIGN KEY (`group_id`)
REFERENCES `ohrm_claim_employee_group` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_emp_group_emp_filter` ADD FOREIGN KEY (`filter_id`)
REFERENCES `ohrm_claim_employee_eligibility_criteria_filter` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;



ALTER TABLE `ohrm_claim_expense_type_eligibility` ADD FOREIGN KEY (`expense_type_id`)
REFERENCES `ohrm_claim_expense_type` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_type_eligibility` ADD FOREIGN KEY (`employee_group_id`)
REFERENCES `ohrm_claim_employee_group` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;



ALTER TABLE `ohrm_claim_currency` ADD FOREIGN KEY (`currency_id`)
REFERENCES `hs_hr_currency_type` (`currency_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_currency_exchange_rate` ADD FOREIGN KEY (`local_currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_currency_exchange_rate` ADD FOREIGN KEY (`foreign_currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_type_currency` ADD FOREIGN KEY (`expense_type_id`)
REFERENCES `ohrm_claim_expense_type` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_expense_type_currency` ADD FOREIGN KEY (`currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;


ALTER TABLE `ohrm_claim_estimate_request` ADD FOREIGN KEY (`emp_number`)
REFERENCES `hs_hr_employee` (`emp_number`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimate_request` ADD FOREIGN KEY (`created_by`)
REFERENCES `hs_hr_employee` (`emp_number`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimate_request` ADD FOREIGN KEY (`local_currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimate_request_history` ADD FOREIGN KEY (`estimate_request_id`)
REFERENCES `ohrm_claim_estimate_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimates` ADD FOREIGN KEY (`expense_type_id`)
REFERENCES `ohrm_claim_expense_type` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimates` ADD FOREIGN KEY (`paid_by_id`)
REFERENCES `ohrm_claim_expense_paid_by` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimates` ADD FOREIGN KEY (`exchange_rate_id`)
REFERENCES `ohrm_claim_currency_exchange_rate` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimates` ADD FOREIGN KEY (`estimate_request_id`)
REFERENCES `ohrm_claim_estimate_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_request` ADD FOREIGN KEY (`currency_id`)
REFERENCES `ohrm_claim_currency` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimation_claiming` ADD FOREIGN KEY (`claim_request_id`)
REFERENCES `ohrm_claim_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimation_claiming` ADD FOREIGN KEY (`estimation_request_id`)
REFERENCES `ohrm_claim_estimate_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_project_claiming` ADD FOREIGN KEY (`claim_request_id`)
REFERENCES `ohrm_claim_request` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_project_claiming` ADD FOREIGN KEY (`project_id`)
REFERENCES `ohrm_project` (`project_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `ohrm_claim_estimate_request_attachment` ADD CONSTRAINT FOREIGN KEY (`request_id`)
REFERENCES `ohrm_claim_estimate_request` (`id`)
  ON DELETE CASCADE;


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;
