create table ohrm_dynamic_form
(
    `id`                int unsigned not null auto_increment,
    `name`              varchar(100) not null unique,
    `description`       varchar(500) default null,
    `endpoint`          varchar(250) not null unique,
    `action`            varchar(32)  default null,
    `meta_data`         json         default null,
    `data_group_id`     int          default null,
    `created_by`        int          default null,
    `updated_by`        int          default null,
    `created_by_name`   varchar(200) default null,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    `version` int default 1,
    CONSTRAINT `fk_ohrm_dynamic_form_created_by` FOREIGN KEY (`created_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_data_group_id` FOREIGN KEY (`data_group_id`) REFERENCES `ohrm_data_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    primary key (`id`)
)
    engine = innodb
    default charset = utf8;

create table ohrm_dynamic_form_version
(
    `id`                int unsigned not null auto_increment,
    `name`              varchar(100) not null,
    `description`       varchar(500) default null,
    `endpoint`          varchar(250) not null,
    `action`            varchar(32)  default null,
    `meta_data`         json         default null,
    `data_group_id`     int          default null,
    `created_by`        int          default null,
    `updated_by`        int          default null,
    `created_by_name`   varchar(200) default null,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    `dynamic_form_id` int unsigned not null,
    `version` int default 1,
    CONSTRAINT `fk_ohrm_dynamic_form_version_created_by` FOREIGN KEY (`created_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_version_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_version_data_group_id` FOREIGN KEY (`data_group_id`) REFERENCES `ohrm_data_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_version_dynamic_form_id` FOREIGN KEY (`dynamic_form_id`) REFERENCES `ohrm_dynamic_form` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    primary key (`id`)
) engine = innodb default charset = utf8;

create table ohrm_dynamic_form_record
(
    `id`                int unsigned not null auto_increment,
    `form_version_id` int unsigned not null,
    `form_values`       longtext     default null,
    `created_by`        int          default null,
    `updated_by`        int          default null,
    `created_by_name`   varchar(200) default null,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    primary key (`id`),
    CONSTRAINT `fk_ohrm_dynamic_form_record_form_id` FOREIGN KEY (`form_version_id`) REFERENCES `ohrm_dynamic_form_version` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_record_created_by` FOREIGN KEY (`created_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_dynamic_form_record_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
)
    engine = innodb
    default charset = utf8;

create table `ohrm_dynamic_form_attachment`
(
    `id`          int unsigned not null auto_increment,
    `file_name`   varchar(2000)                         default null,
    `file_size`   int(11)                               default '0',
    `file_type`   varchar(200)                          default null,
    `description` varchar(2000)                         default null,
    `attachment`  mediumblob,
    `created_at`  TIMESTAMP                             NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    primary key (`id`)
) engine = innodb
  default charset = utf8;

ALTER TABLE `ohrm_token_section`
    ADD CONSTRAINT `fk_token_section_dynamic_form` FOREIGN KEY (`dynamic_form_version_id`) REFERENCES `ohrm_dynamic_form_version` (`id`);
