SET @module_id := (SELECT id FROM `ohrm_module` where name = 'preboarding');
SET @pro_module_id := (SELECT id FROM `ohrm_module` where name = 'onboardingPro');
SET @max_token_id := (SELECT MAX(id) FROM `ohrm_token`);

INSERT INTO `ohrm_token_section` (name, display_name, dynamic_form_version_id)
SELECT REPLACE(name, ' ', '') as name1, name as display_name, id
FROM `ohrm_dynamic_form_version`
WHERE name IN ('Preboarding Self Introduction',
               'Preboarding References',
               'Preboarding Personal Details',
               'Preboarding Contact Details',
               'Preboarding Emergency Contacts',
               'Preboarding Dependent Information',
               'Preboarding Immigration Information',
               'Preboarding Work Experience',
               'Preboarding Education',
               'Preboarding Skills',
               'Preboarding Language',
               'Preboarding License',
               'Preboarding Memberships',
               'Preboarding Forms and Attachments');

CREATE TEMPORARY TABLE temp_json_data AS
WITH RECURSIVE seq AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1
    FROM seq
    WHERE n < 100
),
               json_data AS (
                   SELECT
                       s.id,
                       REPLACE(s.name, '/', '') as sectionName,
                       s.display_name as sectionLabel,
                       f.data_group_id,
                       JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(meta_data, CONCAT('$.fields[', seq.n, ']')), '$.name')) AS fieldName,
                       JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(meta_data, CONCAT('$.fields[', seq.n, ']')), '$.label')) AS fieldLabel,
                       JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(meta_data, CONCAT('$.fields[', seq.n, ']')), '$.attr.optionLabel')) AS optionLabel,
                       JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(meta_data, CONCAT('$.fields[', seq.n, ']')), '$.widget')) AS widget
                   FROM ohrm_dynamic_form_version f
                            LEFT JOIN ohrm_token_section s ON  (s.dynamic_form_version_id = f.id)
                            JOIN seq ON seq.n < JSON_LENGTH(meta_data, '$.fields')
                   WHERE s.dynamic_form_version_id IS NOT NULL
               )

SELECT
    CONCAT(sectionName, ':', fieldName) as name,
    CONCAT(sectionLabel, ' - ', IF(fieldLabel ='', optionLabel, fieldLabel)) as display_name,
    'DynamicFormTokenValueGenerator' as class_name,
    data_group_id,
    CASE
        WHEN widget = 'datepicker' THEN 1
        ELSE 0
        END as is_date,
    id as token_section_id
FROM
    json_data
WHERE
    fieldName IS NOT NULL AND widget != 'oxd-file-input';

INSERT INTO `ohrm_token` (`name`, `display_name`, `class_name`, `data_group_id`, `is_date`, `token_section_id`)
SELECT * FROM temp_json_data;


INSERT INTO `ohrm_restricted_module_token`(`token_id`, `restricted_module`)
SELECT id, @module_id FROM ohrm_token
WHERE id > @max_token_id
AND token_section_id IN (SELECT id FROM ohrm_token_section WHERE display_name = 'Preboarding Self Introduction');

INSERT INTO `ohrm_restricted_module_token`(`token_id`, `restricted_module`)
SELECT id, @pro_module_id FROM ohrm_token
WHERE id > @max_token_id
AND token_section_id NOT IN (SELECT id FROM ohrm_token_section WHERE display_name = 'Preboarding Self Introduction');