DROP VIEW IF EXISTS `ohrm_analytics_employee_personality`;
CREATE VIEW `ohrm_analytics_employee_personality` AS
SELECT e.emp_number,e.employee_id,  CONCAT_WS(' ',e.emp_firstname, e.emp_lastname) as emp_name,  epp.profile,epp.created_date FROM hs_hr_employee e
LEFT JOIN  `ohrm_employee_personality_profile` epp ON e.emp_number = epp.emp_number
WHERE e.purged_at IS NULL;

DROP VIEW IF EXISTS `ohrm_analytics_interview_sections`;
CREATE VIEW  `ohrm_analytics_interview_sections` AS
SELECT IF(vet.is_interview=1,qfs.id,CONCAT('test_',cve.id) ) AS section_id,IF(vet.is_interview=1,qg.alias,ve.event_title) AS section_name, IF(vet.is_interview=1,qfs.score,cve.final_score*5/ve.max_score) as score,IF(vet.is_interview=1,qfs.question_group_id,CONCAT('test_',ei.test_template_id) ) as question_group_id, cei.question_form_id,jcv.vacancy_id, jcv.candidate_id,
cve.vacancy_candidate_id as vacancy_candidate_id, cei.candidate_vacancy_event_id AS candidate_event_id, cve.event_id AS event_id,
cei.id AS candevent_inter_id, cet.id AS candevent_test_id  FROM ohrm_question_form_section qfs
LEFT JOIN ohrm_question_group qg ON qg.id = qfs.question_group_id
LEFT JOIN ohrm_candidate_event_interview cei ON cei.question_form_id = qfs.question_form_id
LEFT JOIN ohrm_candidate_event_test cet ON cet.question_form_id = qfs.question_form_id
RIGHT JOIN ohrm_candidate_vacancy_event cve ON cei.candidate_vacancy_event_id = cve.id
LEFT JOIN ohrm_vacancy_event ve ON ve.id = cve.event_id
LEFT JOIN `ohrm_vacancy_event_type` vet ON vet.id = ve.type_id
LEFT JOIN `ohrm_event_interview` ei ON ei.event_id = ve.id
INNER JOIN ohrm_job_candidate_vacancy jcv ON jcv.id = cve.vacancy_candidate_id
LEFT JOIN ohrm_job_candidate jc ON jc.id = jcv.candidate_id
WHERE ((vet.is_interview=1 AND qfs.score >'') OR (vet.is_test=1 AND cve.final_score >'')) AND (jc.purged_at IS NULL );
