INSERT INTO ohrm_data_group (name, description, can_read, can_create, can_update, can_delete)
VALUES ('recruitment_preboarding_job_offer_information', 'Recruitment (ATS) - Onboarding - Job Offer Information', 1, 0, 1, 0);

SET @data_group_id := (SELECT LAST_INSERT_ID());
SET @admin_user_role_id := (SELECT `id`
                            FROM `ohrm_user_role`
                            WHERE `name` = 'Admin');
SET @hiring_manager_user_role_id := (SELECT `id`
                                     FROM `ohrm_user_role`
                                     WHERE `name` = 'HiringManager');
SET @preboarding_event_manager_user_role_id := (SELECT `id`
                                     FROM `ohrm_user_role`
                                     WHERE `name` = 'PreboardingEventManager');

SET @candidate_user_role_id := (SELECT `id`
                                     FROM `ohrm_user_role`
                                     WHERE `name` = 'CandidateUser');

INSERT INTO ohrm_user_role_data_group (user_role_id, data_group_id, can_read, can_create, can_update, can_delete, self,
                                       restriction_id)
VALUES (@admin_user_role_id, @data_group_id, 1, 0, 1, 0, 0, NULL),
       (@hiring_manager_user_role_id, @data_group_id, 1, 0, 1, 0, 0, NULL),
       (@candidate_user_role_id, @data_group_id, 1, 0, 0, 0, 0, NULL),
       (@hiring_manager_user_role_id, @data_group_id, 1, 0, 1, 0, 0, NULL),
       (@preboarding_event_manager_user_role_id, @data_group_id, 1, 0, 1, 0, 0, NULL);

SET @admin_user_role_type := (SELECT `id`
                              FROM `ohrm_user_role_type`
                              WHERE `name` = 'Admin');
SET @system_user_id := (SELECT `id`
                        FROM `ohrm_user`
                        WHERE `user_name` = 'System');

INSERT INTO ohrm_user_role_type_data_group (user_role_type_id, data_group_id, can_read, can_create, can_update,
                                            can_delete)
VALUES (@admin_user_role_type, @data_group_id, 1, 0, 1, 0);

INSERT INTO ohrm_dynamic_form (`name`, description, `action`, endpoint, meta_data, data_group_id, created_by,
                               updated_by, created_by_name, version)
VALUES ('Job Offer Information', 'Offer Information', 'POST', 'preboarding-offer-information', null, @data_group_id,
        @system_user_id, @system_user_id, 'System', 1);
SET @dynamic_form_id := (SELECT `id`
                         FROM ohrm_dynamic_form
                         WHERE name = 'Job Offer Information');

INSERT INTO ohrm_dynamic_form_version (`name`, description, `action`, endpoint, meta_data, data_group_id, created_by,
                                       updated_by, created_by_name, version, dynamic_form_id)
VALUES ('Job Offer Information', 'Offer Information', 'POST', 'preboarding-offer-information', null, @data_group_id,
        @system_user_id, @system_user_id, 'System', 1, @dynamic_form_id);
SET @dynamic_form_version_id := (SELECT `id`
                                 FROM ohrm_dynamic_form_version
                                 WHERE name = 'Job Offer Information');

SET @job_hours_custom_field_id := (SELECT `id`
                                       FROM `ohrm_custom_field`
                                       WHERE `name` = 'Job Hours' AND deleted = 0);
SET @contract_type_custom_field_id := (SELECT `id`
                                       FROM `ohrm_custom_field`
                                       WHERE `name` = 'Contract Type' AND deleted = 0);
SET @holiday_entitlement_custom_field_id := (SELECT `id`
                                             FROM `ohrm_custom_field`
                                             WHERE `name` = 'Holiday Entitlement' AND deleted = 0);
SET @probation_and_notice_period_custom_field_id := (SELECT `id`
                                                     FROM `ohrm_custom_field`
                                                     WHERE `name` = 'Probation & Notice Period' AND deleted = 0);

SET @metadata := CONCAT('
{
  "version": {
    "schema-version": "1.0.0"
  },
  "fields": [
    {
      "uid": "job_title_1001",
      "name": "job_title",
      "label": "Job Title",
      "widget": "oxd-master-data-input",
      "attr": {
        "service": "JobTitle",
        "required": true
      },
      "validations": [
        "#required",
        "#masterDataSelect:JobTitle"
      ]
    },
    {
      "uid": "job_hours_1002",
      "name": "job_hours",
      "widget": "oxd-master-data-input",
      "label": "Job Hours",
      "attr": {
        "required": true,
        "service":"CustomFieldOptions",
        "customFieldId":"',@job_hours_custom_field_id,'"
      },
      "validations": [
        "#required"
      ]
    },
    {
      "uid": "location_1003",
      "name": "location",
      "label": "Location",
      "widget": "oxd-master-data-input",
      "attr": {
        "service": "Location",
        "required": true
      },
      "validations": [
        "#required",
        "#masterDataSelect:Location"
      ]
    },
    {
      "uid": "intended_join_date_1004",
      "name": "intended_join_date",
      "widget": "datepicker",
      "label": "Intended Join Date",
      "attr": {
        "required": true
      },
      "validations": [
        "#required",
        "#date_format:yyyy-mm-dd",
        "#date"
      ]
    },
    {
      "uid": "continuous_date_of_employment_1005",
      "name": "continuous_date_of_employment",
      "widget": "datepicker",
      "label": "Continuous Date of Employment",
      "attr": {
      },
      "validations": [
        "#date_format:yyyy-mm-dd",
        "#date"
      ]
    },
    {
      "uid": "contract_type_1006",
      "name": "contract_type",
      "label": "Contract Type",
      "widget":"oxd-master-data-input",
      "attr":{
        "required": true,
        "widgetType":"radiogroup",
        "service":"CustomFieldOptions",
        "customFieldId":"',@contract_type_custom_field_id,'"
      },
      "validations": [
        "#required"
      ]
    },
    {
      "uid": "employment_status_1007",
      "name": "employment_status",
      "label": "Employment Status",
      "widget": "oxd-master-data-input",
      "attr": {
        "required": true,
        "service": "EmploymentStatus"
      },
      "validations": [
        "#required",
        "#masterDataSelect:EmploymentStatus"
      ]
    },
    {
      "uid": "ft_annual_salary_1008",
      "name": "ft_annual_salary",
      "widget": "textbox",
      "label": "FT Annual Salary (£)",
      "attr": {
        "required": true,
        "salaryComponent": "FT Annual Salary",
        "jobTitleId": "$job_title.id",
        "locationId": "$location.id",
        "contractType": "$contract_type",
        "currencySymbol": "£",
        "format": "number",
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,999\\\\,999\\\\,999\\\\,999.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "actual_annual_salary_1009",
      "name": "actual_annual_salary",
      "widget": "textbox",
      "label": "Actual Annual Salary (%)",
      "attr": {
        "required": true,
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,1\\\\,000.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "car_allowance_1010",
      "name": "car_allowance",
      "widget": "textbox",
      "label": "Car Allowance (£)",
      "attr": {
        "required": true,
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,999\\\\,999\\\\,999\\\\,999.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "bonus_1011",
      "name": "bonus",
      "label": "Bonus (%)",
      "widget": "textbox",
      "attr": {
        "required": true,
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,1\\\\,000.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "standard_hourly_rate_1012",
      "name": "standard_hourly_rate",
      "label": "Standard Hourly Rate (£)",
      "widget": "textbox",
      "attr": {
        "required": true,
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,999\\\\,999\\\\,999\\\\,999.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "night_enhancement_1013",
      "name": "night_enhancement",
      "label": "Night Enhancement (£)",
      "widget": "textbox",
      "attr": {
        "required": true,
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,999\\\\,999\\\\,999\\\\,999.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "weekend_enhancement_1014",
      "name": "weekend_enhancement",
      "label": "Weekend Enhancement (£)",
      "widget": "textbox",
      "attr": {
        "required": true,
        "class": "text-right",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#validCurrency:0,999\\\\,999\\\\,999\\\\,999.00,2,.,\\\\,"
      ]
    },
    {
      "uid": "holiday_entitlement_1015",
      "name": "holiday_entitlement",
      "label": "Holiday Entitlement",
      "widget":"oxd-master-data-input",
      "attr":{
        "service":"CustomFieldOptions",
        "required": true,
        "customFieldId":"',@holiday_entitlement_custom_field_id,'"
      },
      "validations": [
        "#required"
      ]
    },
    {
      "uid": "probation_notice_period_1016",
      "name": "probation_notice_period",
      "label": "Probation & Notice Period",
      "widget":"oxd-master-data-input",
      "attr":{
        "service":"CustomFieldOptions",
        "customFieldId":"',@probation_and_notice_period_custom_field_id,'"
      },
      "validations": [
        "#alwaysValid"
      ]
    }
  ],
  "views": {
    "form": {
      "uid": "form001",
      "action": "POST",
      "endpoint": "forms/1",
      "body": [
        {
          "uid": "form001_title",
          "tag": "oxd-text",
          "props": {
            "type": "card-title"
          },
          "slots": {
            "default": [
              "Job Offer Information"
            ]
          }
        },
        {
          "uid": "form001_br",
          "tag": "br"
        },
        {
          "uid": "form001_grid",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "uid": "form001_grid_item_1",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "job_title_1001"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_2",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "job_hours_1002"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_3",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "location_1003"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_4",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "intended_join_date_1004"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_5",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "continuous_date_of_employment_1005"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_6",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "contract_type_1006"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_7",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "employment_status_1007"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Monthly\\"",
                "uid": "form001_grid_item_8",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "ft_annual_salary_1008"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Monthly\\"",
                "uid": "form001_grid_item_9",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "actual_annual_salary_1009"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Monthly\\"",
                "uid": "form001_grid_item_10",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "car_allowance_1010"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Monthly\\"",
                "uid": "form001_grid_item_11",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "bonus_1011"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Weekly\\"",
                "uid": "form001_grid_item_12",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "standard_hourly_rate_1012"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Weekly\\"",
                "uid": "form001_grid_item_13",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "night_enhancement_1013"
                    }
                  ]
                }
              },
              {
                "if": "$contract_type === \\"Weekly\\"",
                "uid": "form001_grid_item_14",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "weekend_enhancement_1014"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_15",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "holiday_entitlement_1015"
                    }
                  ]
                }
              },
              {
                "uid": "form001_grid_item_16",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "probation_notice_period_1016"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "uid": "form007_divider",
          "tag": "oxd-divider",
          "props": {
            "style": "margin-bottom: 1rem;"
          }
        },
        {
          "uid": "form001_buttons",
          "tag": "div",
          "props": {
            "style": "display: flex; justify-content: space-between;margin-top: 1rem"
          },
          "slots": {
              "default": [
                {
                  "uid": "form007_required_text",
                  "tag": "oxd-required-text"
                },
                {
                  "uid": "form001_button_submit",
                  "tag": "oxd-button",
                  "props": {
                    "type": "button",
                    "label": "Submit"
                  },
                  "events": {
                    "onClick": "#onClickSubmitButton"
                  }
                }
              ]
            }
        }
      ],
      "model": {
        "job_title": "",
        "job_hours": "",
        "location": "",
        "intended_join_date": "",
        "continuous_date_of_employment": "",
        "contract_type": "",
        "employment_status": "",
        "ft_annual_salary": "",
        "actual_annual_salary": "",
        "car_allowance": "",
        "bonus": "",
        "standard_hourly_rate": "",
        "night_enhancement" : "",
        "weekend_enhancement": "",
        "holiday_entitlement": "",
        "probation_notice_period": ""
      },
      "computed": {},
      "methods": {
        "onClickSubmitButton": {
          "#validate": {},
          "#confirmation": {
            "message": "Offer information will be submitted.",
            "confirmDisplayType": "label-feedback-success"
          },
          "#save": {
            "action": "Submit"
          },
          "#toast": {
            "error": "Failed to Submit",
            "success": "Successfully Submitted"
          }
        }
      }
    }
  }
}
');

UPDATE ohrm_dynamic_form
SET meta_data = @metadata
WHERE id = @dynamic_form_id;
UPDATE ohrm_dynamic_form_version
SET meta_data = @metadata
WHERE id = @dynamic_form_version_id;
