SET @recruitment_module_id := (SELECT id FROM `ohrm_module` where name = 'recruitment');
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 NOT IN ('Initiate Onboarding Process', 'Welcome Candidate', 'Emergency Contact Record', 'Disclosure and Barring Service Details');

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,
                       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;


SET @emergency_contacts_value_getters := (
    WITH RECURSIVE seq AS (
        SELECT 0 AS n
        UNION ALL
        SELECT n + 1
        FROM seq
        WHERE n < 10
    ),
                   json_data AS (
                       SELECT
                           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, ']')), '$.widget')) AS widget
                       FROM ohrm_dynamic_form_version f
                                JOIN seq ON seq.n < JSON_LENGTH(meta_data, '$.fields')
                       WHERE f.endpoint = 'emergency-contact-record'
                   )

    SELECT
        CONCAT('{',GROUP_CONCAT(CONCAT('"', fieldName, '": ', JSON_OBJECT('display_name', fieldLabel, 'value_getter', fieldName, 'is_date', widget = 'datepicker'))), '}')
    FROM
        json_data
    WHERE
        fieldName IS NOT NULL AND widget != 'oxd-file-input'
    );

UPDATE ohrm_token SET `value_getters` = @emergency_contacts_value_getters, `parameters` = 'avoidDefaultEncode=>true,type=>table,hasMultiple=>true', `is_multi_attribute` = 1 WHERE name = 'EmergencyContacts:emergency_contact';

INSERT INTO `ohrm_restricted_module_token`(`token_id`, `restricted_module`)
SELECT id, @recruitment_module_id FROM ohrm_token WHERE id > @max_token_id AND name NOT LIKE 'ReferenceDetails:%';

