-- Export des vues de la base `jobplut618`
-- Date : 2026-04-16 21:51:22

CREATE OR REPLACE VIEW `appli_vw_cities` AS
SELECT `jobplut618`.`appli_ref_ctry_regs_cities`.`region` AS `region`, `jobplut618`.`appli_ref_ctry_regs_cities`.`city` AS `city`, `jobplut618`.`appli_ref_ctry_regs_cities`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_ctry_regs_cities`;

CREATE OR REPLACE VIEW `appli_vw_countries` AS
SELECT distinct `jobplut618`.`appli_ref_ctry_regs_cities`.`country` AS `country`, `jobplut618`.`appli_ref_ctry_regs_cities`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_ctry_regs_cities`
WHERE (length(`jobplut618`.`appli_ref_ctry_regs_cities`.`country`) > 2);

CREATE OR REPLACE VIEW `appli_vw_debug_company` AS
SELECT `r`.`first_name` AS `first_name`, `r`.`last_name` AS `last_name`, `r`.`email` AS `email`, `r`.`country_iso2` AS `country_iso2`, `r`.`lg_iso2` AS `lg_iso2`, `jt`.`jobtitle` AS `jobtitle`, `jt`.`jobtitle_en` AS `jobtitle_en`, `jt`.`jobtitle_family` AS `jobtitle_family`, `jt`.`description` AS `description`, left(`l`.`full_path`, 50) AS `left(l.full_path, 50)`
FROM ((`jobplut618`.`appli_recruiters` `r` left join `jobplut618`.`appli_jobtitle_recruiters` `jt` on(((`jt`.`identifier` = `r`.`email`) and (`jt`.`lg_iso2` = `r`.`lg_iso2`)))) left join `jobplut618`.`appli_logo_recruiters` `l` on((`l`.`identifier` = `r`.`email`)))
WHERE (`r`.`show_ad` = 0x01)
ORDER BY `r`.`last_name`;

CREATE OR REPLACE VIEW `appli_vw_debug_jobtitle_en` AS
SELECT `combined`.`jobtitle` AS `jobtitle`, group_concat((case when (`combined`.`source_table` = 'appli_ref_jobtitle') then `combined`.`id` end) separator ', ') AS `ids_jobtitle_table`, group_concat((case when (`combined`.`source_table` = 'appli_ref_fjsh_en') then `combined`.`id` end) separator ', ') AS `ids_jobtitle_fjsh`, `combined`.`lg_iso2` AS `lg_iso2`
FROM (SELECT `jobplut618`.`appli_ref_jobtitle`.`ID` AS `id`, `jobplut618`.`appli_ref_jobtitle`.`jobtitle` AS `jobtitle`, 'appli_ref_jobtitle' AS `source_table`, `jobplut618`.`appli_ref_jobtitle`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_jobtitle`
WHERE (`jobplut618`.`appli_ref_jobtitle`.`lg_iso2` = 'EN') union all SELECT `jobplut618`.`appli_ref_fjsh_en`.`id` AS `id`, `jobplut618`.`appli_ref_fjsh_en`.`jobtitle` AS `jobtitle`, 'appli_ref_fjsh_en' AS `source_table`, `jobplut618`.`appli_ref_fjsh_en`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_fjsh_en`
WHERE (`jobplut618`.`appli_ref_fjsh_en`.`lg_iso2` = 'EN')) `combined`
GROUP BY `combined`.`jobtitle`
ORDER BY `combined`.`jobtitle`;

CREATE OR REPLACE VIEW `appli_vw_debug_jobtitle_fr` AS
SELECT `combined`.`jobtitle` AS `jobtitle`, group_concat((case when (`combined`.`source_table` = 'appli_ref_jobtitle') then `combined`.`id` end) separator ', ') AS `ids_jobtitle_table`, group_concat((case when (`combined`.`source_table` = 'appli_ref_fjsh_fr') then `combined`.`id` end) separator ', ') AS `ids_jobtitle_fjsh`, `combined`.`lg_iso2` AS `lg_iso2`
FROM (SELECT `jobplut618`.`appli_ref_jobtitle`.`ID` AS `id`, `jobplut618`.`appli_ref_jobtitle`.`jobtitle` AS `jobtitle`, 'appli_ref_jobtitle' AS `source_table`, `jobplut618`.`appli_ref_jobtitle`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_jobtitle`
WHERE (`jobplut618`.`appli_ref_jobtitle`.`lg_iso2` = 'FR') union all SELECT `jobplut618`.`appli_ref_fjsh_fr`.`id` AS `id`, `jobplut618`.`appli_ref_fjsh_fr`.`jobtitle` AS `jobtitle`, 'appli_ref_fjsh_fr' AS `source_table`, `jobplut618`.`appli_ref_fjsh_fr`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_fjsh_fr`
WHERE (`jobplut618`.`appli_ref_fjsh_fr`.`lg_iso2` = 'FR')) `combined`
GROUP BY `combined`.`jobtitle`
ORDER BY `combined`.`jobtitle`;

CREATE OR REPLACE VIEW `appli_vw_hardskill_EN_FR` AS
with recursive `numbers` as (SELECT 1 AS `n` union all SELECT (`numbers`.`n` + 1) AS `n + 1`
FROM `numbers`
WHERE (`numbers`.`n` < 50)), `EN_base` as (SELECT `jobplut618`.`appli_ref_fjsh_en`.`id` AS `id`, `jobplut618`.`appli_ref_fjsh_en`.`hardskills` AS `hardskills`, (((char_length(`jobplut618`.`appli_ref_fjsh_en`.`hardskills`) - char_length(replace(`jobplut618`.`appli_ref_fjsh_en`.`hardskills`, ', ', ''))) / char_length(', ')) + 1) AS `skill_count`
FROM `jobplut618`.`appli_ref_fjsh_en`), `FR_base` as (SELECT `jobplut618`.`appli_ref_fjsh_fr`.`id` AS `id`, `jobplut618`.`appli_ref_fjsh_fr`.`hardskills` AS `hardskills`, (((char_length(`jobplut618`.`appli_ref_fjsh_fr`.`hardskills`) - char_length(replace(`jobplut618`.`appli_ref_fjsh_fr`.`hardskills`, ', ', ''))) / char_length(', ')) + 1) AS `skill_count`
FROM `jobplut618`.`appli_ref_fjsh_fr`), `Hardskills_EN_Split` as (SELECT `en`.`id` AS `id`, trim(substring_index(substring_index(`en`.`hardskills`, ', ', `n`.`n`), ', ', -(1))) AS `hardskill_EN`, `n`.`n` AS `skill_order`
FROM (`EN_base` `en` join `numbers` `n` on((`n`.`n` <= `en`.`skill_count`)))), `Hardskills_FR_Split` as (SELECT `fr`.`id` AS `id`, trim(substring_index(substring_index(`fr`.`hardskills`, ', ', `n`.`n`), ', ', -(1))) AS `hardskill_FR`, `n`.`n` AS `skill_order`
FROM (`FR_base` `fr` join `numbers` `n` on((`n`.`n` <= `fr`.`skill_count`)))) SELECT `e`.`hardskill_EN` AS `hardskill_EN`, `f`.`hardskill_FR` AS `hardskill_FR`
FROM (`Hardskills_EN_Split` `e` join `Hardskills_FR_Split` `f` on(((`e`.`id` = `f`.`id`) and (`e`.`skill_order` = `f`.`skill_order`))))
WHERE ((`e`.`hardskill_EN` is not null) and (`e`.`hardskill_EN` <> ''))
GROUP BY `e`.`hardskill_EN`, `f`.`hardskill_FR`
ORDER BY `e`.`hardskill_EN`;

CREATE OR REPLACE VIEW `appli_vw_hardskills` AS
with recursive `numbers` as (SELECT 1 AS `n` union all SELECT (`numbers`.`n` + 1) AS `n`
FROM `numbers`
WHERE (`numbers`.`n` < 10)), `hardskills_en` as (SELECT `rh`.`jobtitle` AS `jobtitle`, trim(substring_index(substring_index(`rh`.`hardskills`, ', ', `n`.`n`), ', ', -(1))) AS `hard_skill`, 'EN' AS `lg_iso2`
FROM (`jobplut618`.`appli_ref_fjsh_en` `rh` join `numbers` `n` on(((char_length(`rh`.`hardskills`) - char_length(replace(`rh`.`hardskills`, ', ', ''))) >= (`n`.`n` - 1))))), `hardskills_fr` as (SELECT `rh`.`jobtitle` AS `jobtitle`, trim(substring_index(substring_index(`rh`.`hardskills`, ', ', `n`.`n`), ', ', -(1))) AS `hard_skill`, 'FR' AS `lg_iso2`
FROM (`jobplut618`.`appli_ref_fjsh_fr` `rh` join `numbers` `n` on(((char_length(`rh`.`hardskills`) - char_length(replace(`rh`.`hardskills`, ', ', ''))) >= (`n`.`n` - 1))))) SELECT `hardskills_en`.`jobtitle` AS `jobtitle`, `hardskills_en`.`hard_skill` AS `hard_skill`, `hardskills_en`.`lg_iso2` AS `lg_iso2`
FROM `hardskills_en`
WHERE (`hardskills_en`.`hard_skill` is not null)
GROUP BY `hardskills_en`.`jobtitle`, `hardskills_en`.`hard_skill`, `hardskills_en`.`lg_iso2` union all SELECT `hardskills_fr`.`jobtitle` AS `jobtitle`, `hardskills_fr`.`hard_skill` AS `hard_skill`, `hardskills_fr`.`lg_iso2` AS `lg_iso2`
FROM `hardskills_fr`
WHERE (`hardskills_fr`.`hard_skill` is not null)
GROUP BY `hardskills_fr`.`jobtitle`, `hardskills_fr`.`hard_skill`, `hardskills_fr`.`lg_iso2`
ORDER BY `jobtitle`, `hard_skill`;

CREATE OR REPLACE VIEW `appli_vw_jobtitle_EN_FR` AS
SELECT (row_number() OVER (ORDER BY `t1`.`id` ) - 1) AS `sequence`, `t1`.`family_name` AS `family_name`, `t1`.`jobtitle` AS `jobtitle_FR`, `t2`.`jobtitle` AS `jobtitle_EN`
FROM (`jobplut618`.`appli_ref_fjsh_fr` `t1` join `jobplut618`.`appli_ref_fjsh_en` `t2` on((`t1`.`id` = `t2`.`id`)))
ORDER BY `sequence`;

CREATE OR REPLACE VIEW `appli_vw_jobtitle_en` AS
SELECT `t`.`id` AS `id`, `t`.`lg_iso2` AS `lg_iso2`, `t`.`family_name` AS `family_name`, `t`.`jobtitle` AS `jobtitle`, `t`.`softskills` AS `softskills`, `t`.`hardskills` AS `hardskills`, `t`.`created_at` AS `created_at`, `t`.`updated_at` AS `updated_at`
FROM `jobplut618`.`appli_ref_fjsh_en` `t`
WHERE (`t`.`lg_iso2` = 'EN')
ORDER BY `t`.`jobtitle`;

CREATE OR REPLACE VIEW `appli_vw_jobtitle_en_fr_new` AS
SELECT (row_number() OVER (ORDER BY `t1`.`id` ) - 1) AS `sequence`, `t1`.`family_name` AS `family_name`, `t1`.`jobtitle` AS `jobtitle_FR`, `t2`.`jobtitle` AS `jobtitle_EN`
FROM (`jobplut618`.`appli_ref_fjsh_fr` `t1` join `jobplut618`.`appli_ref_fjsh_en` `t2` on((`t1`.`id` = `t2`.`id`)))
ORDER BY `sequence`;

CREATE OR REPLACE VIEW `appli_vw_jobtitle_fr` AS
SELECT `t`.`id` AS `id`, `t`.`lg_iso2` AS `lg_iso2`, `t`.`family_name` AS `family_name`, `t`.`jobtitle` AS `jobtitle`, `t`.`softskills` AS `softskills`, `t`.`hardskills` AS `hardskills`, `t`.`created_at` AS `created_at`, `t`.`updated_at` AS `updated_at`
FROM `jobplut618`.`appli_ref_fjsh_fr` `t`
WHERE (`t`.`lg_iso2` = 'FR')
ORDER BY `t`.`jobtitle`;

CREATE OR REPLACE VIEW `appli_vw_jobtitle_nl` AS
SELECT `t`.`id` AS `id`, `t`.`lg_iso2` AS `lg_iso2`, `t`.`family_name` AS `family_name`, `t`.`jobtitle` AS `jobtitle`, `t`.`softskills` AS `softskills`, `t`.`hardskills` AS `hardskills`, `t`.`created_at` AS `created_at`, `t`.`updated_at` AS `updated_at`
FROM `jobplut618`.`appli_ref_fjsh_nl` `t`
WHERE (`t`.`lg_iso2` = 'NL')
ORDER BY `t`.`jobtitle`;

CREATE OR REPLACE VIEW `appli_vw_join_recruiters` AS
SELECT `jobt_rcr`.`jobtitle_en` AS `jobtitle`, `rc`.`jobtitle_family` AS `jobtitle_family`, (cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) AS `email`, `rc`.`last_name` AS `last_name`, `rc`.`first_name` AS `first_name`, `rc`.`show_ad` AS `show_ad`, `rc`.`path_document` AS `url_desc_jobtitle`, `lg`.`employment_language` AS `language`, `lg`.`mandatory` AS `mandatory_language`, `pay`.`salary` AS `salary`, `pay`.`currency` AS `currency`, `pay`.`period` AS `period`, `pay`.`hide` AS `hide_salary`, `pay`.`bonus` AS `bonus`, `educ`.`education` AS `educ`, `educ`.`mandatory` AS `mandatory_educ`, `arg`.`arrangement` AS `arrgmnt`, `arg`.`mandatory` AS `mandatory_arrgmnt`, `status`.`status` AS `status`, `status`.`mandatory` AS `mandatory_status`, `type`.`type` AS `type`, `type`.`mandatory` AS `mandatory_type`, `yr`.`nbr_years` AS `yrs`, `yr`.`mandatory` AS `mandatory_yrs`, `sector`.`sector` AS `sectorO`, `sector`.`mandatory` AS `mandatory_sector`, `hardskills`.`hard_skill` AS `HardSkills`, `softskills`.`soft_skill` AS `SoftSkills`, `ctryregs`.`ctry_regs_city` AS `ctry_regs_city`, `ctryregs`.`mandatory` AS `mandatory_location`, `workpermit`.`work_permit` AS `work_permit`, `workpermit`.`mandatory` AS `mandatory_workpermit`
FROM (((((((((((((`jobplut618`.`appli_recruiters` `rc` join `jobplut618`.`appli_jobtitle_recruiters` `jobt_rcr` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`jobt_rcr`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`jobt_rcr`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_language_recruiters` `lg` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`lg`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`lg`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_pay_recruiters` `pay` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`pay`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`pay`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_educations_recruiters` `educ` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`educ`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`educ`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_arrangements_recruiters` `arg` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`arg`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`arg`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_status_recruiters` `status` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`status`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`status`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_type_recruiters` `type` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`type`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`type`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_year_recruiters` `yr` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`yr`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`yr`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_industries_recruiters` `sector` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`sector`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`sector`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_hardskills_recruiters` `hardskills` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`hardskills`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`jobt_rcr`.`jobtitle_en` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`hardskills`.`jobtitle_EN` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_softskills_recruiters` `softskills` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`softskills`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`jobt_rcr`.`jobtitle_en` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`softskills`.`jobtitle_EN` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_ctry_regs_cities_recruiters` `ctryregs` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`ctryregs`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`ctryregs`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci))))) left join `jobplut618`.`appli_work_permit_recruiters` `workpermit` on((((cast(`rc`.`email` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`workpermit`.`identifier` as char(255) charset utf8mb4) collate utf8mb4_general_ci)) and ((cast(`rc`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci) = (cast(`workpermit`.`jobtitle` as char(255) charset utf8mb4) collate utf8mb4_general_ci)))))
WHERE ((`rc`.`jobtitle` is not null) and (`rc`.`jobtitle` <> ''))
GROUP BY `rc`.`email`, `jobt_rcr`.`jobtitle_en`, `rc`.`jobtitle_family`, `hardskills`.`hard_skill`, `softskills`.`soft_skill`;

CREATE OR REPLACE VIEW `appli_vw_join_seekers` AS
SELECT group_concat(distinct `jobtitle`.`jobtitle_en` separator ', ') AS `jobtitle`, group_concat(distinct `jobtitle`.`jobtitle_family` separator ', ') AS `jobtitle_family`, `sk`.`first_name` AS `first_name`, `sk`.`last_name` AS `last_name`, `sk`.`email` AS `email`, `sk`.`phone` AS `phone`, `sk`.`full_path_cv` AS `full_path_cv`, `sk`.`country_iso2` AS `ctry_lg`, `sk`.`lg_iso2` AS `sk_lg`, group_concat(distinct `lg`.`language` separator ', ') AS `language`, `pay`.`salary` AS `salary`, `pay`.`currency` AS `currency`, `pay`.`period` AS `period`, group_concat(distinct `educ`.`education` separator ', ') AS `educ`, group_concat(distinct `arg`.`arrangement` separator ', ') AS `arrgmnt`, `status`.`status` AS `status`, group_concat(distinct `type`.`type` separator ', ') AS `type`, group_concat(distinct `yr`.`nbr_years` separator ', ') AS `yrs`, group_concat(distinct `sector`.`sector` separator ', ') AS `sectorO`, group_concat(distinct `hardskills`.`sector` separator ', ') AS `HardSector`, group_concat(distinct `hardskills`.`hard_skill` separator ', ') AS `HardSkills`, group_concat(distinct `softskills`.`sector` separator ', ') AS `SoftSector`, group_concat(distinct `softskills`.`soft_skill` separator ', ') AS `SoftSkills`, group_concat(distinct `ctryregs`.`ctry_regs_city` separator ', ') AS `ctry_regs_city`, group_concat(distinct `workpermit`.`work_permit` separator ', ') AS `work_permit`, `ctry`.`country` AS `country`, `ctry`.`lg_iso2` AS `ctry_lg_iso2`, `sk`.`profile_picture_url` AS `profile_picture_url`
FROM ((((((((((((((`jobplut618`.`appli_seekers` `sk` left join `jobplut618`.`appli_countries_seekers` `ctry` on((`sk`.`email` = `ctry`.`identifier`))) left join `jobplut618`.`appli_language_seekers` `lg` on((`sk`.`email` = `lg`.`identifier`))) left join `jobplut618`.`appli_pay_seekers` `pay` on((`sk`.`email` = `pay`.`identifier`))) left join `jobplut618`.`appli_educations_seekers` `educ` on((`sk`.`email` = `educ`.`identifier`))) left join `jobplut618`.`appli_arrangements_seekers` `arg` on((`sk`.`email` = `arg`.`identifier`))) left join `jobplut618`.`appli_status_seekers` `status` on((`sk`.`email` = `status`.`identifier`))) left join `jobplut618`.`appli_year_seekers` `yr` on((`sk`.`email` = `yr`.`identifier`))) left join `jobplut618`.`appli_industries_seekers` `sector` on((`sk`.`email` = `sector`.`identifier`))) left join `jobplut618`.`appli_type_seekers` `type` on((`sk`.`email` = `type`.`identifier`))) left join `jobplut618`.`appli_jobtitle_seekers` `jobtitle` on((`sk`.`email` = `jobtitle`.`identifier`))) left join `jobplut618`.`appli_hardskills_seekers` `hardskills` on((`sk`.`email` = `hardskills`.`identifier`))) left join `jobplut618`.`appli_softskills_seekers` `softskills` on((`sk`.`email` = `softskills`.`identifier`))) left join `jobplut618`.`appli_ctry_regs_cities_seekers` `ctryregs` on((`sk`.`email` = `ctryregs`.`identifier`))) left join `jobplut618`.`appli_work_permit_seekers` `workpermit` on((`sk`.`email` = `workpermit`.`identifier`)))
GROUP BY `sk`.`email`, `sk`.`first_name`, `sk`.`last_name`, `sk`.`phone`, `sk`.`full_path_cv`, `sk`.`country_iso2`, `sk`.`lg_iso2`, `ctry`.`country`, `ctry`.`lg_iso2`, `pay`.`calc_amount`, `pay`.`currency`, `pay`.`period`, `status`.`status`
HAVING (`jobtitle` is not null);

CREATE OR REPLACE VIEW `appli_vw_join_seekers_old` AS
SELECT `ctry`.`country` AS `country`, `ctry`.`lg_iso2` AS `ctry_lg_iso2`, `sk`.`first_name` AS `first_name`, `sk`.`last_name` AS `last_name`, `sk`.`email` AS `email`, `sk`.`phone` AS `phone`, `sk`.`full_path_cv` AS `full_path_cv`, `sk`.`country_iso2` AS `ctry_lg`, `sk`.`lg_iso2` AS `sk_lg`, group_concat(distinct `lg`.`language` separator ', ') AS `language`, `pay`.`calc_amount` AS `salary`, `pay`.`currency` AS `currency`, `pay`.`period` AS `period`, group_concat(distinct `educ`.`education` separator ', ') AS `educ`, group_concat(distinct `arg`.`arrangement` separator ', ') AS `arrgmnt`, `status`.`status` AS `status`, group_concat(distinct `type`.`type` separator ', ') AS `type`, group_concat(distinct `yr`.`nbr_years` separator ', ') AS `yrs`, group_concat(distinct `sector`.`sector` separator ', ') AS `sectorO`, group_concat(distinct `jobtitle`.`jobtitle_en` separator ', ') AS `jobtitle`, group_concat(distinct `jobtitle`.`jobtitle_family` separator ', ') AS `jobtitle_family`, group_concat(distinct `hardskills`.`sector` separator ', ') AS `HardSector`, group_concat(distinct `hardskills`.`hard_skill` separator ', ') AS `HardSkills`, group_concat(distinct `softskills`.`sector` separator ', ') AS `SoftSector`, group_concat(distinct `softskills`.`soft_skill` separator ', ') AS `SoftSkills`, group_concat(distinct `ctryregs`.`ctry_regs_city` separator ', ') AS `ctry_regs_city`, group_concat(distinct `workpermit`.`work_permit` separator ', ') AS `work_permit`
FROM ((((((((((((((`jobplut618`.`appli_seekers` `sk` left join `jobplut618`.`appli_countries_seekers` `ctry` on((`sk`.`email` = `ctry`.`identifier`))) left join `jobplut618`.`appli_language_seekers` `lg` on((`sk`.`email` = `lg`.`identifier`))) left join `jobplut618`.`appli_pay_seekers` `pay` on((`sk`.`email` = `pay`.`identifier`))) left join `jobplut618`.`appli_educations_seekers` `educ` on((`sk`.`email` = `educ`.`identifier`))) left join `jobplut618`.`appli_arrangements_seekers` `arg` on((`sk`.`email` = `arg`.`identifier`))) left join `jobplut618`.`appli_status_seekers` `status` on((`sk`.`email` = `status`.`identifier`))) left join `jobplut618`.`appli_year_seekers` `yr` on((`sk`.`email` = `yr`.`identifier`))) left join `jobplut618`.`appli_industries_seekers` `sector` on((`sk`.`email` = `sector`.`identifier`))) left join `jobplut618`.`appli_type_seekers` `type` on((`sk`.`email` = `type`.`identifier`))) left join `jobplut618`.`appli_jobtitle_seekers` `jobtitle` on((`sk`.`email` = `jobtitle`.`identifier`))) left join `jobplut618`.`appli_hardskills_seekers` `hardskills` on((`sk`.`email` = `hardskills`.`identifier`))) left join `jobplut618`.`appli_softskills_seekers` `softskills` on((`sk`.`email` = `softskills`.`identifier`))) left join `jobplut618`.`appli_ctry_regs_cities_seekers` `ctryregs` on((`sk`.`email` = `ctryregs`.`identifier`))) left join `jobplut618`.`appli_work_permit_seekers` `workpermit` on((`sk`.`email` = `workpermit`.`identifier`)))
GROUP BY `sk`.`email`, `sk`.`first_name`, `sk`.`last_name`, `sk`.`phone`, `sk`.`full_path_cv`, `sk`.`country_iso2`, `sk`.`lg_iso2`, `ctry`.`country`, `ctry`.`lg_iso2`, `pay`.`calc_amount`, `pay`.`currency`, `pay`.`period`, `status`.`status`;

CREATE OR REPLACE VIEW `appli_vw_list_crc` AS
SELECT distinct `jobplut618`.`appli_ref_ctry_regs_cities`.`country` AS `value`, `jobplut618`.`appli_ref_ctry_regs_cities`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_ctry_regs_cities` union SELECT distinct `jobplut618`.`appli_ref_ctry_regs_cities`.`region` AS `value`, `jobplut618`.`appli_ref_ctry_regs_cities`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_ctry_regs_cities` union SELECT distinct `jobplut618`.`appli_ref_ctry_regs_cities`.`city` AS `value`, `jobplut618`.`appli_ref_ctry_regs_cities`.`lg_iso2` AS `lg_iso2`
FROM `jobplut618`.`appli_ref_ctry_regs_cities`;

CREATE OR REPLACE VIEW `appli_vw_matching` AS
SELECT `rc`.`status` AS `recru_status`, `rc`.`mandatory` AS `recru_mandatory`, `rc`.`lg_iso2` AS `recru_lg_iso2`, `rc`.`identifier` AS `recru_identifier`, `sk`.`status` AS `seeker_status`, `sk`.`lg_iso2` AS `seeker_lg_iso2`, `sk`.`identifier` AS `seeker_identifier`, 'STATUS' AS `type`
FROM (`jobplut618`.`appli_status_recruiters` `rc` left join `jobplut618`.`appli_status_seekers` `sk` on((`rc`.`status` = `sk`.`status`))) union SELECT `rca`.`arrangement` AS `recru_arrangement`, `rca`.`mandatory` AS `recru_mandatory`, `rca`.`lg_iso2` AS `recru_lg_iso2`, `rca`.`identifier` AS `recru_identifier`, `ska`.`arrangement` AS `seeker_arrangement`, `ska`.`lg_iso2` AS `seeker_lg_iso2`, `ska`.`identifier` AS `seeker_identifier`, 'ARRANGEMENTS' AS `type`
FROM (`jobplut618`.`appli_arrangements_recruiters` `rca` left join `jobplut618`.`appli_arrangements_seekers` `ska` on((`rca`.`arrangement` = `ska`.`arrangement`))) union SELECT `rce`.`education` AS `recru_education`, `rce`.`mandatory` AS `recru_mandatory`, `rce`.`lg_iso2` AS `recru_lg_iso2`, `rce`.`identifier` AS `recru_identifier`, `ske`.`education` AS `seeker_education`, `ske`.`lg_iso2` AS `seeker_lg_iso2`, `ske`.`identifier` AS `seeker_identifier`, 'EDUCATIONS' AS `type`
FROM (`jobplut618`.`appli_educations_recruiters` `rce` left join `jobplut618`.`appli_educations_seekers` `ske` on((`rce`.`education` = `ske`.`education`))) union SELECT `rci`.`sector` AS `recru_industry`, `rci`.`mandatory` AS `recru_mandatory`, `rci`.`lg_iso2` AS `recru_lg_iso2`, `rci`.`identifier` AS `recru_identifier`, `ski`.`sector` AS `seeker_industry`, `ski`.`lg_iso2` AS `seeker_lg_iso2`, `ski`.`identifier` AS `seeker_identifier`, 'INDUSTRY' AS `type`
FROM (`jobplut618`.`appli_industries_recruiters` `rci` left join `jobplut618`.`appli_industries_seekers` `ski` on((`rci`.`sector` = `ski`.`sector`))) union SELECT `rcp`.`work_permit` AS `recru_permit`, `rcp`.`mandatory` AS `recru_mandatory`, `rcp`.`lg_iso2` AS `recru_lg_iso2`, `rcp`.`identifier` AS `recru_identifier`, `skp`.`work_permit` AS `seeker_permit`, `skp`.`lg_iso2` AS `seeker_lg_iso2`, `skp`.`identifier` AS `seeker_identifier`, 'PERMIT' AS `type`
FROM (`jobplut618`.`appli_work_permit_recruiters` `rcp` left join `jobplut618`.`appli_work_permit_seekers` `skp` on((`rcp`.`work_permit` = `skp`.`work_permit`))) union SELECT `rcy`.`nbr_years` AS `recru_years`, `rcy`.`mandatory` AS `recru_mandatory`, `rcy`.`lg_iso2` AS `recru_lg_iso2`, `rcy`.`identifier` AS `recru_identifier`, `sky`.`nbr_years` AS `seeker_years`, `sky`.`lg_iso2` AS `seeker_lg_iso2`, `sky`.`identifier` AS `seeker_identifier`, 'YEARS' AS `type`
FROM (`jobplut618`.`appli_year_recruiters` `rcy` left join `jobplut618`.`appli_year_seekers` `sky` on((`rcy`.`nbr_years` = `sky`.`nbr_years`))) union SELECT `rct`.`type` AS `recru_type`, `rct`.`mandatory` AS `recru_mandatory`, `rct`.`lg_iso2` AS `recru_lg_iso2`, `rct`.`identifier` AS `recru_identifier`, `skt`.`type` AS `seeker_type`, `skt`.`lg_iso2` AS `seeker_lg_iso2`, `skt`.`identifier` AS `seeker_identifier`, 'TYPE' AS `type`
FROM (`jobplut618`.`appli_type_recruiters` `rct` left join `jobplut618`.`appli_type_seekers` `skt` on((`rct`.`type` = `skt`.`type`)));

CREATE OR REPLACE VIEW `appli_vw_pat` AS
SELECT `t`.`jobtitle` AS `jobtitle`, `t`.`identifier` AS `identifier`, `t`.`mode` AS `mode`, `o`.`created_at` AS `created_at`
FROM (`jobplut618`.`appli_ref_jobtitle_transit` `t` left join `jobplut618`.`appli_profiles` `o` on((`t`.`identifier` = `o`.`email`)))
WHERE ((`o`.`created_at` > '2025-12-02') and (`t`.`logical_deleted` = 0))
GROUP BY `t`.`identifier`, `t`.`mode`, `t`.`jobtitle`;

CREATE OR REPLACE VIEW `appli_vw_recru_ind_hard_soft` AS
SELECT `i`.`sector` AS `industries`, `i`.`identifier` AS `identifier`, `i`.`jobtitle` AS `jobtitle`, `i`.`created_at` AS `created_at`, group_concat(distinct `h`.`hard_skill` separator ', ') AS `hardskills`, group_concat(distinct `s`.`soft_skill` separator ', ') AS `softskills`
FROM ((`jobplut618`.`appli_industries_recruiters` `i` left join `jobplut618`.`appli_hardskills_recruiters` `h` on((`i`.`identifier` = `h`.`identifier`))) left join `jobplut618`.`appli_softskills_recruiters` `s` on((`i`.`identifier` = `s`.`identifier`)))
WHERE (1 = 1)
GROUP BY `i`.`sector`, `i`.`identifier`, `i`.`jobtitle`, `i`.`created_at`;

CREATE OR REPLACE VIEW `appli_vw_regions` AS
SELECT distinct `jobplut618`.`appli_ref_ctry_regs_cities`.`region` AS `region`, `jobplut618`.`appli_ref_ctry_regs_cities`.`lg_iso2` AS `lg_iso2`, `jobplut618`.`appli_ref_ctry_regs_cities`.`country` AS `country`
FROM `jobplut618`.`appli_ref_ctry_regs_cities`;

CREATE OR REPLACE VIEW `appli_vw_seek_ind_hard_soft` AS
SELECT `i`.`sector` AS `industries`, `i`.`identifier` AS `identifier`, `j`.`jobtitle` AS `jobtitle`, `i`.`created_at` AS `created_at`, group_concat(distinct `h`.`hard_skill` separator ', ') AS `hardskills`, group_concat(distinct `s`.`soft_skill` separator ', ') AS `softskills`
FROM (((`jobplut618`.`appli_industries_seekers` `i` left join `jobplut618`.`appli_hardskills_seekers` `h` on((`i`.`identifier` = `h`.`identifier`))) left join `jobplut618`.`appli_softskills_seekers` `s` on((`i`.`identifier` = `s`.`identifier`))) left join `jobplut618`.`appli_jobtitle_seekers` `j` on((`i`.`identifier` = `j`.`identifier`)))
WHERE (1 = 1)
GROUP BY `i`.`sector`, `i`.`identifier`, `j`.`jobtitle`, `i`.`created_at`;

CREATE OR REPLACE VIEW `appli_vw_softskill_EN_FR` AS
with recursive `numbers` as (SELECT 1 AS `n` union all SELECT (`numbers`.`n` + 1) AS `n + 1`
FROM `numbers`
WHERE (`numbers`.`n` < 10)), `Softskills_EN_Split` as (SELECT `t1`.`id` AS `id`, trim(substring_index(substring_index(`t1`.`softskills`, ', ', `numbers`.`n`), ', ', -(1))) AS `softskill_EN`, `numbers`.`n` AS `skill_order`
FROM (`jobplut618`.`appli_ref_fjsh_en` `t1` join `numbers` on(((char_length(`t1`.`softskills`) - char_length(replace(`t1`.`softskills`, ', ', ''))) >= ((`numbers`.`n` - 1) * char_length(', ')))))), `Softskills_FR_Split` as (SELECT `t2`.`id` AS `id`, trim(substring_index(substring_index(`t2`.`softskills`, ', ', `numbers`.`n`), ', ', -(1))) AS `softskill_FR`, `numbers`.`n` AS `skill_order`
FROM (`jobplut618`.`appli_ref_fjsh_fr` `t2` join `numbers` on(((char_length(`t2`.`softskills`) - char_length(replace(`t2`.`softskills`, ', ', ''))) >= ((`numbers`.`n` - 1) * char_length(', ')))))) SELECT `e`.`softskill_EN` AS `softskill_EN`, `f`.`softskill_FR` AS `softskill_FR`
FROM (`Softskills_EN_Split` `e` join `Softskills_FR_Split` `f` on(((`e`.`id` = `f`.`id`) and (`e`.`skill_order` = `f`.`skill_order`))))
WHERE ((`e`.`softskill_EN` is not null) and (`e`.`softskill_EN` <> ''))
GROUP BY `e`.`softskill_EN`, `f`.`softskill_FR`
ORDER BY `e`.`softskill_EN`;

CREATE OR REPLACE VIEW `appli_vw_softskills` AS
with recursive `numbers` as (SELECT 1 AS `n` union all SELECT (`numbers`.`n` + 1) AS `n`
FROM `numbers`
WHERE (`numbers`.`n` < 10)), `softskills_en` as (SELECT `rs`.`jobtitle` AS `jobtitle`, trim(substring_index(substring_index(`rs`.`softskills`, ', ', `n`.`n`), ', ', -(1))) AS `soft_skill`, 'EN' AS `lg_iso2`
FROM (`jobplut618`.`appli_ref_fjsh_en` `rs` join `numbers` `n` on(((char_length(`rs`.`softskills`) - char_length(replace(`rs`.`softskills`, ', ', ''))) >= (`n`.`n` - 1))))), `softskills_fr` as (SELECT `rs`.`jobtitle` AS `jobtitle`, trim(substring_index(substring_index(`rs`.`softskills`, ', ', `n`.`n`), ', ', -(1))) AS `soft_skill`, 'FR' AS `lg_iso2`
FROM (`jobplut618`.`appli_ref_fjsh_fr` `rs` join `numbers` `n` on(((char_length(`rs`.`softskills`) - char_length(replace(`rs`.`softskills`, ', ', ''))) >= (`n`.`n` - 1))))) SELECT `softskills_en`.`jobtitle` AS `jobtitle`, `softskills_en`.`soft_skill` AS `soft_skill`, `softskills_en`.`lg_iso2` AS `lg_iso2`
FROM `softskills_en`
WHERE (`softskills_en`.`soft_skill` is not null)
GROUP BY `softskills_en`.`jobtitle`, `softskills_en`.`soft_skill`, `softskills_en`.`lg_iso2` union all SELECT `softskills_fr`.`jobtitle` AS `jobtitle`, `softskills_fr`.`soft_skill` AS `soft_skill`, `softskills_fr`.`lg_iso2` AS `lg_iso2`
FROM `softskills_fr`
WHERE (`softskills_fr`.`soft_skill` is not null)
GROUP BY `softskills_fr`.`jobtitle`, `softskills_fr`.`soft_skill`, `softskills_fr`.`lg_iso2`
ORDER BY `jobtitle`, `soft_skill`;

CREATE OR REPLACE VIEW `appli_vw_wor3404_users` AS
SELECT `u`.`ID` AS `user_id`, `u`.`user_login` AS `user_login`, `u`.`user_email` AS `email`, `fn`.`meta_value` AS `first_name`, `ln`.`meta_value` AS `last_name`, `lang`.`meta_value` AS `language`, `mode`.`meta_value` AS `mode`
FROM ((((`jobplut618`.`wor3404_users` `u` left join `jobplut618`.`wor3404_usermeta` `fn` on(((`fn`.`user_id` = `u`.`ID`) and (`fn`.`meta_key` = 'first_name')))) left join `jobplut618`.`wor3404_usermeta` `ln` on(((`ln`.`user_id` = `u`.`ID`) and (`ln`.`meta_key` = 'last_name')))) left join `jobplut618`.`wor3404_usermeta` `lang` on(((`lang`.`user_id` = `u`.`ID`) and (`lang`.`meta_key` = 'language')))) left join `jobplut618`.`wor3404_usermeta` `mode` on(((`mode`.`user_id` = `u`.`ID`) and (`mode`.`meta_key` = 'mode'))));

