INSERT INTO ohrm_data_group (name, description, can_read, can_create, can_update, can_delete)
VALUES ('preboarding_references', 'Preboarding - Stage 04: References', 1, 0, 1, 0);

SET @data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'preboarding_references');
SET @new_hires_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'preboarding_new_hires');
SET @admin_user_role_type := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Admin');
SET @admin_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');
SET @preboarding_event_manager_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'PreBoardingEventManager');
SET @preboarding_new_hire_user_role_id := (SELECT `id` FROM `ohrm_user_role`  WHERE `name` = 'PreboardingNewHire');
SET @referee_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'PreboardingReferee');
SET @ess_id := (SELECT id FROM ohrm_user_role where name = 'ESS');
SET @ess_role_type := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'ESS');

SET @supervisor_id := (SELECT id FROM ohrm_user_role where name = 'Supervisor');
SET @hod_user_role := (SELECT id FROM ohrm_user_role WHERE `name` = 'HeadOfDepartment');
SET @hiring_manager_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HiringManager');
SET @supervisor_role_type := (SELECT `id` FROM `ohrm_user_role_type` WHERE `name` = 'Supervisor');

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),
    (@supervisor_id, @data_group_id, 1, 0, 1, 0, 0, NULL),
    (@hod_user_role, @data_group_id, 1, 0, 1, 0, 0, NULL),
    (@hiring_manager_user_role_id, @data_group_id, 1, 1, 1, 0, 0, NULL),
    (@preboarding_event_manager_user_role_id, @data_group_id, 1, 0, 1, 0, 0, NULL),
    (@preboarding_new_hire_user_role_id, @data_group_id, 1, 0, 1, 0, 1, NULL),
    (@referee_user_role_id, @new_hires_data_group_id, 1, 0, 1, 0, 0, NULL),
    (@referee_user_role_id, @data_group_id, 1, 0, 1, 0, 0, NULL),
    (@ess_id, @data_group_id, 1, 0, 1, 0, 1, NULL);

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),
    (@supervisor_role_type, @data_group_id, 1, 0, 1, 0),
    (@ess_role_type, @data_group_id, 1, 0, 1, 0);

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

INSERT INTO ohrm_dynamic_form (`name`, description, `action`, endpoint, meta_data, data_group_id, created_by,
                               updated_by, created_by_name, version)
VALUES ('Preboarding References', 'Preboarding References', 'POST',
        'preboarding-references', null, @data_group_id, @system_user_id, @system_user_id, 'System', 1);

SET @dynamic_form_id := (SELECT `id` FROM ohrm_dynamic_form WHERE name = 'Preboarding References');

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 ('Preboarding References', 'Preboarding References', 'POST', 'preboarding-references',
        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 = 'Preboarding References');

SET @metadata := CONCAT('{
  "version": {
    "schema-version": "1.0.0"
  },
  "fields": [
    {
      "uid": "title_004",
      "name": "title",
      "widget": "dropdown",
      "label": "Title",
      "attr": {
        "required": true,
        "choices": {
            "Mr": "Mr",
            "Mrs": "Mrs",
            "Ms": "Ms",
            "Dr": "Dr",
            "Prof":"Prof",
            "Sir": "Sir",
            "Lord": "Lord",
            "Miss": "Miss"
        }
      },
      "validations": [
        "#required"
      ]
    },
    {
      "uid": "first_name_004",
      "name": "first_name",
      "widget": "textbox",
      "label": "First Name",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#length:0,30"
      ]
    },
    {
      "uid": "surname_004",
      "name": "surname",
      "widget": "textbox",
      "label": "Surname",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#length:0,30"
      ]
    },
    {
      "uid": "work_email_004",
      "name": "email",
      "widget": "textbox",
      "label": "Work Email",
      "attr": {
        "autocapitalize": "none",
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#email",
        "#length:0,50"
      ]
    },
    {
      "uid": "phone_004",
      "name": "phone",
      "widget": "text",
      "label": "Phone",
      "validations": [
        "#telephone",
        "#length:0,25"
      ]
    },
    {
      "uid": "company_004",
      "name": "company",
      "widget": "textbox",
      "label": "Company/Academic Institute",
      "attr": {
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#length:0,250"
      ]
    },
    {
      "uid": "designation_004",
      "name": "designation",
      "widget": "textbox",
      "label": "Designation",
      "attr": {
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#length:0,250"
      ]
    },
    {
      "uid": "relationship_004",
      "name": "relationship",
      "widget": "dropdown",
      "label": "Relationship",
      "attr": {
        "required": true,
        "choices": {
                "Manager/Supervisor":  "Manager/Supervisor",
                "Work Colleague": "Work Colleague",
                "Professional Associate": "Professional Associate",
                "Professor/Lecturer": "Professor/Lecturer",
                "Research Partner": "Research Partner",
                "Other (Please Specify)": "Other (Please Specify)"
        }
      },
      "validations": [
        "#required"
      ]
    },
    {
      "uid": "specify_relationship_004",
      "name": "specify_relationship",
      "widget": "textbox",
      "label": "Specify the relationship",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#availableIf:relationship.id,#equals,Other (Please Specify)",
        "#length:0,250"
      ]
    },
    {
      "uid": "referee_category_004",
      "name": "referee_category",
      "widget": "dropdown",
      "label": "Category of the Reference",
      "attr": {
        "required": true,
        "choices": {
          "Employment Type": "Employment Type",
          "Character Type": "Character Type"
        }
      },
      "validations": [
        "#required",
        "#availableIf:$$task_status$$,#in,[Pending Initiation,Initiated,Submitted,Approved,Rejected]",
        "#select:[Employment Type,Character Type]"
      ]
    },
    {
      "uid": "new_hire_name_004",
      "name": "new_hire_name",
      "widget": "textbox",
      "label": "Name",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#length:0,250"
      ]
    },
    {
      "uid": "position_applied_for_004",
      "name": "position_applied_for",
      "widget": "textbox",
      "label": "Position Applied For",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#length:0,250"
      ]
    },
    {
      "uid": "what_job_role_were_they_employed_by_you_004",
      "name": "what_job_role_were_they_employed_by_you",
      "widget": "textbox",
      "label": "What job role were they employed by you?",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#length:0,250"
      ]
    },
    {
      "uid": "dates_of_employment_from_004",
      "name": "dates_of_employment_from",
      "widget": "datepicker",
      "label": "Dates of Employment (From)",
      "attr": {
        "required": true
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#date_format:yyyy-mm-dd",
        "#date",
        "#before_date:$dates_of_employment_to,Dates of employment (From) should be before dates of employment (To)"
      ]
    },
    {
      "uid": "dates_of_employment_to_004",
      "name": "dates_of_employment_to",
      "widget": "datepicker",
      "label": "Dates of Employment (To)",
      "attr": {
        "required": true
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#date_format:yyyy-mm-dd",
        "#date",
        "#after_date:$dates_of_employment_from,Dates of Employment (To) should be after Dates of Employment (From)"
      ]
    },
    {
      "uid": "reason_for_leaving_004",
      "name": "reason_for_leaving",
      "widget": "textbox",
      "label": "Reason for leaving",
      "attr": {
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#length:0,250",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "would_you_re_employ_004",
      "name": "would_you_re_employ",
      "widget": "radiogroup",
      "label": "Would you re-employ?",
      "attr": {
        "required": true,
        "choices": {
          "yes": "Yes",
          "no": "No"
        }
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#in:[yes,no]"
      ]
    },
    {
      "uid": "would_you_re_employ_reason_004",
      "name": "would_you_re_employ_reason",
      "widget": "textbox",
      "label": "If no, please state the reason",
      "validations": [
        "#length:0,2500",
        "#availableIf:would_you_re_employ,#equals,no",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "would_you_recommend_for_employment_004",
      "name": "would_you_recommend_for_employment",
      "widget": "radiogroup",
      "label": "Would you recommend for employment?",
      "attr": {
        "choices": {
          "yes": "Yes",
          "no": "No"
        }
      },
      "validations": [
        "#in:[yes,no]",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "would_you_recommend_for_employment_reason_004",
      "name": "would_you_recommend_for_employment_reason",
      "widget": "textbox",
      "label": "If not, please provide the reason",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#availableIf:would_you_recommend_for_employment,#equals,no"
      ]
    },
    {
      "uid": "sickness_absence_004",
      "name": "sickness_absence",
      "widget": "textbox",
      "label": "Sickness/Absence during last 12 months (please state total number of days & occurrences, and reason if known)",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "disciplinary_on_file_004",
      "name": "disciplinary_on_file",
      "widget": "radiogroup",
      "label": "Current or pending disciplinary on file?",
      "attr": {
        "choices": {
          "yes": "Yes",
          "no": "No"
        }
      },
      "validations": [
        "#in:[yes,no]",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "disciplinary_on_file_details_004",
      "name": "disciplinary_on_file_details",
      "widget": "textbox",
      "label": "If yes, please state and provide details",
      "validations": [
        "#length:0,2500",
        "#availableIf:disciplinary_on_file,#equals,yes",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "known_convictions_004",
      "name": "known_convictions",
      "widget": "radiogroup",
      "label": "Any known convictions?",
      "attr": {
        "choices": {
          "yes": "Yes",
          "no": "No"
        }
      },
      "validations": [
        "#in:[yes,no]",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "known_convictions_reason_004",
      "name": "known_convictions_reason",
      "widget": "textbox",
      "label": "If yes, please state reason if known",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#availableIf:known_convictions,#equals,yes"
      ]
    },
    {
      "uid": "demonstrated_strengths_004",
      "name": "demonstrated_strengths",
      "widget": "radiogroup",
      "label": "Did the candidate demonstrate key strengths in their role?",
      "attr": {
        "choices": {
          "yes": "Yes",
          "no": "No"
        }
      },
      "validations": [
        "#in:[yes,no]",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "demonstrated_strengths_reason_004",
      "name": "demonstrated_strengths_reason",
      "widget": "textbox",
      "label": "If yes, please specify the reason, if known",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#availableIf:demonstrated_strengths,#equals,yes"
      ]
    },
    {
      "uid": "suitability_for_employment_004",
      "name": "suitability_for_employment",
      "widget": "textbox",
      "label": "Please comment on the applicant’s suitability for employment:",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "print_name_ref_1_004",
      "name": "print_name_ref_1",
      "widget": "textbox",
      "label": "Print Name (BLOCK CAPITALS)",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#length:0,250"
      ]
    },
    {
      "uid": "date_ref_1_004",
      "name": "date_ref_1",
      "widget": "datepicker",
      "label": "Date",
      "attr": {
        "required": true
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#date_format:yyyy-mm-dd",
        "#date",
        "#valid_past_date"
      ]
    },
    {
      "uid": "company_name_ref_1_004",
      "name": "company_name_ref_1",
      "widget": "textbox",
      "label": "Company Name",
      "attr": {
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#length:0,250",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "job_title_004",
      "name": "job_title",
      "widget": "textbox",
      "label": "Your Job Title",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Employment Type",
        "#length:0,250"
      ]
    },
    {
      "uid": "was_direct_supervisor_004",
      "name": "was_direct_supervisor",
      "widget": "radiogroup",
      "label": "Were you the applicant’s direct supervisor?",
      "attr": {
        "choices": {
          "yes": "Yes",
          "no": "No"
        }
      },
      "validations": [
        "#in:[yes,no]",
        "#availableIf:referee_category.id,#equals,Employment Type"
      ]
    },
    {
      "uid": "capacity_known_by_you_004",
      "name": "capacity_known_by_you",
      "widget": "textbox",
      "label": "In what capacity are they known by you?",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#equals,Character Type",
        "#length:0,250"
      ]
    },
    {
      "uid": "known_for_how_long_004",
      "name": "known_for_how_long",
      "widget": "textbox",
      "label": "How long have you known the applicant?",
      "attr": {
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#length:0,250",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "pressure_handling_ability_004",
      "name": "pressure_handling_ability",
      "widget": "textbox",
      "label": "How does the applicant handle pressure or challenging situations?",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "reliability_trustworthiness_004",
      "name": "reliability_trustworthiness",
      "widget": "textbox",
      "label": "Can you describe the applicant’s reliability and trustworthiness?",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "other_comments_004",
      "name": "other_comments",
      "widget": "textbox",
      "label": "Any other comments",
      "validations": [
        "#length:0,2500",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "print_name_ref_2_004",
      "name": "print_name_ref_2",
      "widget": "textbox",
      "label": "Print Name (BLOCK CAPITALS) ",
      "attr": {
        "required": true,
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#required",
        "#length:0,250",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "date_ref_2_004",
      "name": "date_ref_2",
      "widget": "datepicker",
      "label": "Date ",
      "attr": {
        "required": true
      },
      "validations": [
        "#required",
        "#date_format:yyyy-mm-dd",
        "#date",
        "#valid_past_date",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "company_name_ref_2_004",
      "name": "company_name_ref_2",
      "widget": "textbox",
      "label": "Company Name (If applicable)",
      "attr": {
        "rows": "1",
        "style": "min-height: 45px"
      },
      "validations": [
        "#length:0,250",
        "#availableIf:referee_category.id,#equals,Character Type"
      ]
    },
    {
      "uid": "referee_consent",
      "name": "referee_consent",
      "widget": "checkbox",
      "label": "",
      "attr": {
        "required": true,
        "optionLabel": "I acknowledge that the organization will retain the information about myself and the details I have provided for future reference."
      },
      "validations": [
        "#required",
        "#availableIf:referee_category.id,#notEmpty"
      ]
    },
    {
      "uid": "referee_docs_004",
      "name": "referee_docs",
      "widget": "oxd-file-input",
      "label": "Upload documents",
      "attr": {
        "fieldName": "referee_docs",
        "hint": "Accepts up to ##attachementSize##MB"
      },
      "validations": [
        "#availableIf:referee_category.id,#notEmpty"
      ]
    }
  ],
  "views": {
    "form": {
      "uid": "form008",
      "action": "POST",
      "endpoint": "forms/8",
      "body": [
        {
          "uid": "form001_header",
          "tag": "div",
          "props": {
            "style":"display: flex;"
          },
          "slots": {
            "default": [
              {
                "if": "\\"##advancedTask.status##\\" === \\"Pending\\"",
                "uid": "form001_title",
                "tag": "oxd-text",
                "props": {
                  "type": "card-title"
                },
                "slots": {
                  "default": [
                    "Reference Details"
                  ]
                }
              },
              {
                "if": "\\"##advancedTask.status##\\" !== \\"Pending\\"",
                "uid": "form001_title",
                "tag": "oxd-text",
                "props": {
                  "type": "card-title"
                },
                "slots": {
                  "default": [
                    "Reference Details:",
                    " ##PreboardingReferences:first_name## ##PreboardingReferences:surname##"
                  ]
                }
              },
              {
                "uid":"form001_status",
                "tag":"oxd-status-chip",
                "props":{
                   "style":"margin-left: 10px;",
                   "status":"##advancedTask.status##"
                }
              }
            ]
          }
        },
        {
          "uid": "form008_br",
          "tag": "br"
        },
        {
          "uid": "form008_grid",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "uid": "form008_grid_item_1",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "title_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_2",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "first_name_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_3",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "surname_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_4",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "work_email_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_5",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "phone_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_6",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "company_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_7",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "designation_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_8",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "relationship_004"
                    }
                  ]
                }
              },
              {
                "if": "$relationship.id == \\"Other (Please Specify)\\"",
                "uid": "form008_grid_item_9",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "specify_relationship_004"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "uid": "form008_grid_ref_category_selector",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "uid": "form008_grid_item_9_5",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "referee_category_004"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "uid": "form008_divider",
          "tag": "oxd-divider",
          "props": {
            "style": "margin-bottom: 1rem;"
          }
        },
        {
          "if": "$referee_category.id === \\"Employment Type\\" || $referee_category.id === \\"Character Type\\"",
          "uid": "form001_title",
          "tag": "oxd-text",
          "props": {
            "type": "subtitle-1",
             "style": "margin-bottom: 1rem;"
          },
          "slots": {
            "default": [
              "New Hire Details"
            ]
          }
        },
        {
          "if": "$referee_category.id === \\"Employment Type\\" || $referee_category.id === \\"Character Type\\"",
          "uid": "form008_grid_common_fields_for_referees",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "uid": "form008_grid_item_10",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "new_hire_name_004"
                    }
                  ]
                }
              },
              {
                "uid": "form008_grid_item_11",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "position_applied_for_004"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "if": "$referee_category.id === \\"Employment Type\\"",
          "uid": "form008_grid_employment_type_ref",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_12",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "what_job_role_were_they_employed_by_you_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_13",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "dates_of_employment_from_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_40",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "dates_of_employment_to_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_14",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "reason_for_leaving_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_15",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "would_you_re_employ_004"
                    }
                  ]
                }
              },
              {
                "if": "$would_you_re_employ === \\"no\\"",
                "uid": "form008_grid_item_16",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "would_you_re_employ_reason_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_17",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "would_you_recommend_for_employment_004"
                    }
                  ]
                }
              },
              {
                "if": "$would_you_recommend_for_employment === \\"no\\"",
                "uid": "form008_grid_item_18",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "would_you_recommend_for_employment_reason_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_19",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "sickness_absence_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_20",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "disciplinary_on_file_004"
                    }
                  ]
                }
              },
              {
                "if": "$disciplinary_on_file === \\"yes\\"",
                "uid": "form008_grid_item_21",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "disciplinary_on_file_details_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_21",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "known_convictions_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "if": "$known_convictions === \\"yes\\"",
                "uid": "form008_grid_item_22",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "known_convictions_reason_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_23",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "demonstrated_strengths_004"
                    }
                  ]
                }
              },
              {
                "if": "$demonstrated_strengths === \\"yes\\"",
                "uid": "form008_grid_item_24",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "demonstrated_strengths_reason_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_25",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "suitability_for_employment_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_26",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "print_name_ref_1_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_27",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "date_ref_1_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_28",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "company_name_ref_1_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_29",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "job_title_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Employment Type\\"",
                "uid": "form008_grid_item_30",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "was_direct_supervisor_004"
                    }
                  ]
                }
              }
            ]
          }
        },
       {
          "if": "$referee_category.id === \\"Character Type\\"",
          "uid": "form008_grid_character_type_ref",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_31",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "capacity_known_by_you_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_32",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "known_for_how_long_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_33",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "pressure_handling_ability_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_34",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "reliability_trustworthiness_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_35",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "other_comments_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_36",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "print_name_ref_2_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_37",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "date_ref_2_004"
                    }
                  ]
                }
              },
              {
                "if": "$referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_38",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "company_name_ref_2_004"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "if": "$referee_category.id === \\"Employment Type\\" || $referee_category.id === \\"Character Type\\"",
          "uid": "form008_grid_referee_consent",
          "tag": "oxd-grid",
          "props": {
            "cols": 1,
            "style": "margin-left: -10px;margin-right: -10px;margin-top: 10px; margin-bottom: 2rem;"
          },
          "slots": {
            "default": [
              {
                "uid": "form008_grid_item_40",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "referee_consent"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "if": "$referee_category.id === \\"Employment Type\\" || $referee_category.id === \\"Character Type\\"",
          "uid": "form008_grid_referee_docs",
          "tag": "oxd-grid",
          "props": {
            "cols": 2,
            "style": "margin-left: -10px;margin-right: -10px;"
          },
          "slots": {
            "default": [
              {
                "if": "$referee_category.id === \\"Employment Type\\" || $referee_category.id === \\"Character Type\\"",
                "uid": "form008_grid_item_39",
                "tag": "oxd-grid-item",
                "slots": {
                  "default": [
                    {
                      "field": "referee_docs_004"
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "uid": "form008_buttons",
          "tag": "div",
          "props": {
            "class": "orangehrm-form-actions"
          },
          "slots": {
            "default": [
              {
                "uid": "form008_required_text",
                "tag": "oxd-required-text"
              },
              {
                "uid": "form008_button_reject",
                "tag": "oxd-button",
                "props": {
                  "type": "button",
                  "label": "Reject",
                  "display-type": "label-feedback-reject",
                  "style": "margin-left: 0.5rem;",
                  "tooltip": "Reject to request for a new reference"
                },
                "events": {
                  "onClick": "#onClickRejectButton"
                }
              },
              {
                "uid": "form008_button_approve",
                "tag": "oxd-button",
                "props": {
                  "type": "button",
                  "label": "Approve",
                  "style": "margin-left: 0.5rem;"
                },
                "events": {
                  "onClick": "#onClickApproveButton"
                }
              },
              {
                "uid": "form008_button_initiate",
                "tag": "oxd-button",
                "props": {
                  "type": "button",
                  "label": "Initiate",
                  "style": "margin-left: 0.5rem;"
                },
                "events": {
                  "onClick": "#onClickInitiateButton"
                }
              },
              {
                "uid": "form008_button_save",
                "tag": "oxd-button",
                "props": {
                  "type": "button",
                  "label": "Save",
                  "display-type": "secondary",
                  "style": "margin-left: 0.5rem;"
                },
                "events": {
                  "onClick": "#onClickSaveButton"
                }
              },
              {
                "uid": "form008_button_submit",
                "tag": "oxd-button",
                "props": {
                  "type": "button",
                  "label": "Submit",
                  "style": "margin-left: 0.5rem;"
                },
                "events": {
                  "onClick": "#onClickSubmitButton"
                }
              },
              {
                "uid": "form008_button_upload",
                "tag": "oxd-button",
                "props": {
                  "type": "button",
                  "label": "Submit",
                  "style": "margin-left: 0.5rem;",
                  "tooltip": "Submit to upload attachment"
                },
                "events": {
                  "onClick": "#onClickUploadButton"
                }
              }
            ]
          }
        }
      ],
      "model": {
        "title": null,
        "first_name": null,
        "surname": null,
        "email": null,
        "phone": null,
        "company": null,
        "designation": null,
        "relationship": null,
        "new_hire_name": null,
        "specify_relationship": null,
        "position_applied_for": null,
        "referee_category": null,
        "what_job_role_were_they_employed_by_you": null,
        "dates_of_employment_from": null,
        "dates_of_employment_to": null,
        "reason_for_leaving": null,
        "would_you_re_employ": null,
        "would_you_re_employ_reason": null,
        "would_you_recommend_for_employment": null,
        "would_you_recommend_for_employment_reason": null,
        "sickness_absence": null,
        "disciplinary_on_file": null,
        "disciplinary_on_file_details": null,
        "known_convictions": null,
        "known_convictions_reason": null,
        "demonstrated_strengths": null,
        "demonstrated_strengths_reason": null,
        "suitability_for_employment": null,
        "print_name_ref_1": null,
        "date_ref_1": null,
        "company_name_ref_1": null,
        "job_title": null,
        "was_direct_supervisor": null,
        "capacity_known_by_you": null,
        "known_for_how_long": null,
        "pressure_handling_ability": null,
        "reliability_trustworthiness": null,
        "other_comments": null,
        "print_name_ref_2": null,
        "date_ref_2": null,
        "company_name_ref_2": null,
        "referee_consent": null,
        "referee_docs": null
      },
      "computed": {},
      "methods": {
        "onClickSubmitButton": {
          "#validate": {},
          "#confirmation": {
            "title": "Submit Reference Details",
            "message": "Please confirm that all reference details are accurate and complete. Once submitted, changes cannot be made.",
            "messageAlt": "Are you sure you want to submit the form?",
            "confirmText": "Yes, Submit",
            "confirmDisplayType": "label-feedback-success",
            "style": "width: 500px; max-width:90vw;"
          },
          "#decodeB64Files": {
            "model": ["referee_docs"]
          },
          "#save": {
            "action": "Submit"
          },
          "#toast": {
            "error": "Failed to Submit",
            "success": "Successfully Submitted"
          }
        },
        "onClickUploadButton": {
          "#validate": {},
          "#confirmation": {
            "title": "Submit Reference Details",
            "message": "Please ensure the details provided are accurate.",
            "messageAlt": "The details cannot be edited after submission.",
            "confirmText": "Yes, Submit",
            "confirmDisplayType": "label-feedback-success"
          },
          "#decodeB64Files": {
            "model": ["referee_docs"]
          },
          "#save": {
            "action": "Upload"
          },
          "#toast": {
            "error": "Failed to Submit",
            "success": "Successfully Submitted"
          }
        },
        "onClickSaveButton": {
          "#validate": {},
          "#save": {
            "action": "Save"
          },
          "#toast": {
            "error": "Failed to Save",
            "success": "Successfully Saved"
          }
        },
        "onClickApproveButton": {
          "#validate": {},
          "#confirmation": {
            "title": "Approve Reference Details",
            "message": "By approving, you confirm that all reference details are accurate and satisfactory.",
            "confirmText": "Yes, Approve",
            "confirmDisplayType": "label-feedback-success"
          },
          "#decodeB64Files": {
            "model": ["referee_docs"]
          },
          "#save": {
            "action": "Approve"
          },
          "#toast": {
            "error": "Reference Details Approval Failed",
            "success": "Reference Details Have Been Approved"
          }
        },
        "onClickRejectButton": {
          "#validate": {},
          "#prompt": {
            "title": "Reject Reference Details",
            "confirmDisplayType": "label-feedback-reject",
            "confirmText": "Reject",
            "inputType": "textarea",
            "inputLabel": "Comment"
          },
          "#decodeB64Files": {
            "model": ["referee_docs"]
          },
          "#save": {
            "action": "Reject",
            "skipFormData": true
          },
          "#toast": {
            "error": "Reference Details Rejection Failed",
            "success": "Reference Details Have Been Rejected"
          }
        },
        "onClickInitiateButton": {
          "#validate": {},
          "#confirmation": {
            "title": "Initiate Reference Process",
            "message": "You are about to initiate the reference process for this reference. This will send an email with the portal URL to the reference.",
            "confirmText" : "Yes, Initiate",
            "confirmDisplayType": "label-feedback-success"
          },
          "#save": {
            "action": "Initiate"
          },
          "#toast": {
            "error": "Reference Initiation Failed",
            "success": "Successfully Initiated"
          }
        }
      }
    }
  }
}');

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;

-- END: Form for showing referee information submission instructions

INSERT INTO ohrm_data_field (`name`, `label`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES

('title', 'Title', @data_group_id, 1, 0, 1, 0),
('first_name', 'First Name', @data_group_id, 1, 0, 1, 0),
('surname', 'Surname', @data_group_id, 1, 0, 1, 0),
('email', 'Work Email', @data_group_id, 1, 0, 1, 0),
('phone', 'Phone', @data_group_id, 1, 0, 1, 0),
('company', 'Company', @data_group_id, 1, 0, 1, 0),
('designation', 'Designation', @data_group_id, 1, 0, 1, 0),
('relationship', 'Relationship', @data_group_id, 1, 0, 1, 0),
('specify_relationship', 'Specify Relationship', @data_group_id, 1, 0, 1, 0),
('new_hire_name', 'Name', @data_group_id, 1, 0, 1, 0),
('position_applied_for', 'Position Applied For', @data_group_id, 1, 0, 1, 0),
('what_job_role_were_they_employed_by_you', 'What job role were they employed by you?', @data_group_id, 1, 0, 1,
 0),
('dates_of_employment_from', 'Dates of Employment (From)', @data_group_id, 1, 0, 1, 0),
('dates_of_employment_to', 'Dates of Employment (To)', @data_group_id, 1, 0, 1, 0),
('reason_for_leaving', 'Reason for leaving', @data_group_id, 1, 0, 1, 0),
('would_you_re_employ', 'Would you re-employ?', @data_group_id, 1, 0, 1, 0),
('would_you_re_employ_reason', 'If no, please state the reason', @data_group_id, 1, 0, 1, 0),
('would_you_recommend_for_employment', 'Would you recommend for employment?', @data_group_id, 1, 0, 1, 0),
('would_you_recommend_for_employment_reason', 'If not, please provide the reason', @data_group_id, 1, 0, 1, 0),
('sickness_absence',
 'Sickness/Absence during last 12 months (please state total number of days & occurrences, and reason if known):',
 @data_group_id, 1, 0, 1, 0),
('disciplinary_on_file', 'Current or pending disciplinary on file?', @data_group_id, 1, 0, 1, 0),
('disciplinary_on_file_details', 'If yes, please state and provide details', @data_group_id, 1, 0, 1, 0),
('known_convictions', 'Any known convictions?', @data_group_id, 1, 0, 1, 0),
('known_convictions_reason', 'If yes, please state reason if known', @data_group_id, 1, 0, 1, 0),
('demonstrated_strengths',
 'Did the candidate demonstrate key strengths in their role?',
 @data_group_id, 1, 0, 1, 0),
('demonstrated_strengths_reason', 'If yes, please specify the reason, if known', @data_group_id, 1, 0, 1, 0),
('suitability_for_employment', 'Please comment on the applicant’s suitability for employment:', @data_group_id,
 1, 0, 1, 0),
('print_name_ref_1', 'Print Name (BLOCK CAPITALS) - Employment Type Referee', @data_group_id, 1, 0, 1, 0),
('date_ref_1', 'Date - Employment Type Referee', @data_group_id, 1, 0, 1, 0),
('company_name_ref_1', 'Company Name - Employment Type Referee', @data_group_id, 1, 0, 1, 0),
('job_title', 'Your Job Title', @data_group_id, 1, 0, 1, 0),
('was_direct_supervisor', 'Were you the applicant’s direct supervisor?', @data_group_id, 1, 0, 1, 0),
('capacity_known_by_you', 'In what capacity are they known by you?', @data_group_id, 1, 0, 1, 0),
('known_for_how_long', 'How long have you known the applicant?', @data_group_id, 1, 0, 1, 0),
('pressure_handling_ability', 'How does the applicant handle pressure or challenging situations?', @data_group_id, 1, 0, 1, 0),
('reliability_trustworthiness',
 'Can you describe the applicant’s reliability and trustworthiness?',
 @data_group_id, 1, 0, 1, 0),
('other_comments', 'Any other comments', @data_group_id, 1, 0, 1, 0),
('print_name_ref_2', 'Print Name (BLOCK CAPITALS) - Character Type Referee', @data_group_id, 1, 0, 1, 0),
('date_ref_2', 'Date - Character Type Referee', @data_group_id, 1, 0, 1, 0),
('company_name_ref_2', 'Company Name (If applicable) - Character Type Referee', @data_group_id, 1, 0, 1, 0),
('referee_category', 'Category of the Reference', @data_group_id, 1, 0, 1, 0),
('referee_docs', 'Upload documents', @data_group_id, 1, 0, 1, 0),
('referee_consent', 'Referee Consent', @data_group_id, 1, 0, 1, 0);

SET @referee_category := (SELECT `id` FROM ohrm_data_field WHERE `name` = 'referee_category' AND data_group_id = @data_group_id);
SET @referee_docs := (SELECT `id` FROM ohrm_data_field WHERE `name` = 'referee_docs' AND data_group_id = @data_group_id);

-- Data field permissions for Candidate - all the fields are hidden except the ones filled-in by the candidate
INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update, self)
SELECT @preboarding_new_hire_user_role_id, `id`, 0, 0, 1
FROM ohrm_data_field
WHERE data_group_id = @data_group_id
  AND name NOT IN ('title', 'first_name', 'surname', 'email', 'phone', 'company', 'designation', 'relationship', 'specify_relationship');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update, self)
SELECT @preboarding_new_hire_user_role_id, `id`, 1, 1, 1
FROM ohrm_data_field
WHERE data_group_id = @data_group_id
  AND name IN ('title', 'first_name', 'surname', 'email', 'phone', 'company', 'designation', 'relationship', 'specify_relationship');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update, self)
SELECT @ess_id, `id`, 0, 0, 1
FROM ohrm_data_field
WHERE data_group_id = @data_group_id
  AND name NOT IN ('title', 'first_name', 'surname', 'email', 'phone', 'company', 'designation', 'relationship', 'specify_relationship');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update, self)
SELECT @ess_id, `id`, 1, 1, 1
FROM ohrm_data_field
WHERE data_group_id = @data_group_id
  AND name IN ('title', 'first_name', 'surname', 'email', 'phone', 'company', 'designation', 'relationship', 'specify_relationship');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update, self)
SELECT @referee_user_role_id, `id`, 1, 0, 0
FROM ohrm_data_field
WHERE data_group_id = @data_group_id
  AND name IN ('title', 'first_name', 'surname', 'email', 'phone', 'company', 'designation', 'relationship', 'specify_relationship', 'new_hire_name', 'position_applied_for', 'referee_category');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update, self)
SELECT @referee_user_role_id, `id`, 0, 0, 0
FROM ohrm_data_field
WHERE data_group_id = @data_group_id
  AND name IN ('referee_docs');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update)
SELECT urdg.user_role_id, f.id, 1, 0
FROM `ohrm_user_role_data_group` urdg
         CROSS JOIN ohrm_data_field f
WHERE urdg.data_group_id = @data_group_id
  AND f.data_group_id = @data_group_id
  AND f.id NOT IN (@referee_category, @referee_docs)
  AND urdg.can_read = 1
  AND user_role_id NOT IN (@preboarding_new_hire_user_role_id, @ess_id, @referee_user_role_id);

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update)
SELECT user_role_id, @referee_category, 1, 1
FROM ohrm_user_role_data_group
WHERE data_group_id = @data_group_id
  AND can_read = 1 AND user_role_id NOT IN (@preboarding_new_hire_user_role_id, @ess_id, @referee_user_role_id);

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update)
SELECT user_role_id, @referee_docs, 1, 1
FROM ohrm_user_role_data_group
WHERE data_group_id = @data_group_id
  AND can_read = 1 AND user_role_id NOT IN (@preboarding_new_hire_user_role_id, @ess_id, @referee_user_role_id);

INSERT INTO ohrm_default_data_field_permission (`user_role_type_id`, `data_field_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
SELECT  `user_role_id`, `data_field_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`
FROM ohrm_user_role_data_field WHERE user_role_id IN (@admin_user_role_type,@supervisor_role_type,@ess_role_type)
                                 AND data_field_id IN (SELECT id FROM ohrm_data_field WHERE data_group_id = @data_group_id)
ORDER BY user_role_id, data_field_id ASC;

-- START: Form for showing referee information submission instructions
INSERT INTO ohrm_dynamic_form (`name`, description, `action`, endpoint, meta_data, data_group_id, created_by,
                               updated_by, created_by_name, version)
VALUES ('Reference Details - Instructions', 'Reference Details - Instructions', 'POST',
        'pre-boarding-reference-details-instructions', null, @data_group_id,
        @system_user_id, @system_user_id, 'System', 1);
SET @dynamic_form_id := (SELECT `id`
                         FROM ohrm_dynamic_form
                         WHERE name = 'Reference Details - Instructions');

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 ('Reference Details - Instructions', 'Reference Details - Instructions', 'POST',
        'pre-boarding-reference-details-instructions', 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 = 'Reference Details - Instructions');

SET @metadata := '{
  "version": {
    "schema-version": "1.0.0"
  },
  "fields": [],
  "views": {
    "form": {
      "uid": "form04",
      "action": "POST",
      "endpoint": "forms/4",
      "body": [
        {
          "uid": "form04_title",
          "tag": "oxd-text",
          "props": {
            "type": "card-title",
            "style": "margin-bottom: 30px;"
          },
          "slots": {
            "default": [
              "Instructions for Providing Reference Details"
            ]
          }
        },
        {
          "uid": "form04_title",
          "tag": "oxd-text",
          "props": {
          },
          "slots": {
            "default": [
              "Please provide reference details below. References should be from professional or academic backgrounds, such as previous employers, supervisors, or instructors. Avoid listing friends or family as references."
            ]
          }
        },
        {
          "uid": "form04_title2",
          "tag": "oxd-text",
          "props": {
            "style": "margin-top: 15px;"
          },
          "slots": {
            "default": [
              "Before submitting, ensure that you have informed your references about sharing their contact details with our company."
            ]
          }
        },
        {
          "uid": "form04_title3",
          "tag": "oxd-text",
          "props": {
            "style": "margin-top: 15px;"
          },
          "slots": {
            "default": [
              "Providing details for at least two references is ",
                {
                  "uid": "form04_title3_1",
                  "tag": "b",
                  "props": {
                  },
                  "slots": {
                    "default": [
                        "mandatory"
                    ]
                  }
                },
                "."
            ]
          }
        },
        {
          "uid": "form04_buttons",
          "tag": "div",
          "props": {
            "style": "display: flex; justify-content: space-between;"
          },
          "slots": {
            "default": []
          }
        }
      ],
      "model": {},
      "computed": {},
      "methods": {}
    }
  }
}';

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;

-- END: Form for showing referee information submission instructions