INSERT INTO `ohrm_config`(`property`,`value`) VALUES
('analytics.recruitment.similar_achievers','on'),
('analytics.recruitment.predictive','on'),
('analytics.recruitment.personality_insights','off'),
('analytics.recruitment.personality_error_threshold','0.4'),
('personality_profile_cost','0.6'),
('analytics.personality_insights_warning','on'),
('analytics.app.host','http://analytics.orangehrmlive.com:3000'),
('analytics.app.uid','0004'),
('analytics.app.password','password');

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;

DROP VIEW IF EXISTS `ohrm_job_candidate_vacancy_status`;
CREATE VIEW `ohrm_job_candidate_vacancy_status` AS
SELECT cve.vacancy_candidate_id,ces.state_id,ces.active, vet.id as type_id, vet.type, vet.is_interview, vet.is_test
FROM ohrm_candidate_vacancy_event cve 
LEFT JOIN `ohrm_candidate_event_state` ces ON ces.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
WHERE ces.active =1
GROUP BY cve.id;

DROP VIEW IF EXISTS `ohrm_analytics_candidate_similarity`;
CREATE VIEW `ohrm_analytics_candidate_similarity` AS 
SELECT cve.id,cve.event_id,jc.id as candidate_id,jcv.id as vacancy_candidate_id, jc.candidate_picture,jcv.vacancy_id, IF(cvs.type_id IN (6,7),1,0) as is_hired, 
IF(cvs.type_id =8,1,0) as is_rejected,
IF(vet.is_interview>0,cvi.question_form_id,cvt.question_form_id) AS question_form_id,
IF(vet.is_interview>0,IF(it.id IS NULL,cve.final_score*100/100, cve.final_score*100/it.max_score),
IF(tt.id IS NULL,cve.final_score*100/100,cve.final_score*100/tt.max_score)) AS final_score, 
vet.is_interview, vet.is_test, ve.event_title, ve.max_score, CONCAT_WS(' ',jc.first_name, jc.last_name) as candidate_name, 
jc.status as candidate_status, IF(vet.is_interview>0,it.id,tt.id) AS template_id, 
IF(vet.is_interview>0,it.parent_id,tt.parent_id) AS template_parent_id  FROM ohrm_candidate_vacancy_event cve 
LEFT JOIN `ohrm_candidate_event_interview` cvi ON cvi.candidate_vacancy_event_id=cve.id
LEFT JOIN `ohrm_candidate_event_test` cvt ON cvt.candidate_vacancy_event_id = cve.id
LEFT JOIN `ohrm_job_candidate_vacancy` jcv ON cve.vacancy_candidate_id = jcv.id
LEFT JOIN `ohrm_job_candidate_vacancy_status` cvs ON cvs.vacancy_candidate_id=jcv.id
LEFT JOIN `ohrm_job_candidate` jc ON jc.id = jcv.candidate_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 ve.id=ei.event_id
LEFT JOIN `ohrm_interview_template` it ON ei.interview_template_id=it.id
LEFT JOIN `ohrm_test_template` tt ON ei.test_template_id = tt.id
WHERE vet.is_interview=1 OR vet.is_test=1 AND (cvi.question_form_id IS NOT NULL OR cvt.question_form_id IS NOT NULL) 
GROUP BY cve.id;



DROP VIEW IF EXISTS `ohrm_analytics_interview_questions`;
CREATE VIEW  `ohrm_analytics_interview_questions` AS
SELECT qfq.id qfq_id, qfq.question_id,cei.question_form_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_question qfq
INNER JOIN ohrm_question_form_section qfs ON qfq.question_form_section_id = qfs.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
LEFT JOIN ohrm_candidate_vacancy_event cve ON cei.candidate_vacancy_event_id = cve.id;

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
WHERE (vet.is_interview=1 AND qfs.score >'') OR (vet.is_test=1 AND cve.final_score >'');



-- CREATE TABLE `ohrm_analytics_model_job_title` (
--     `id` INT AUTO_INCREMENT, 
--     `model_id` INT NOT NULL, 
--     `job_title_id` INT NOT NULL, 
--     PRIMARY KEY(id),
--     FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE,
--     FOREIGN KEY (`model_id`) REFERENCES `ohrm_analytics_model`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE
-- ) engine=innodb default charset=utf8;
-- 
-- 
-- 
-- CREATE TABLE `ohrm_analytics_model_location` (
--     `id` INT AUTO_INCREMENT, 
--     `model_id` INT NOT NULL, 
--     `location_id` INT NOT NULL, 
--     PRIMARY KEY(id),
--     FOREIGN KEY (`location_id`) REFERENCES `ohrm_location`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE,
--     FOREIGN KEY (`model_id`) REFERENCES `ohrm_analytics_model`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE
-- ) engine=innodb default charset=utf8;


CREATE TABLE `ohrm_analytics_model_mapping` (
    `id` INT AUTO_INCREMENT, 
    `name` VARCHAR(100) NOT NULL,
    `model_id` INT NOT NULL, 
    `job_title_id` INT, 
    `location_id` INT,
    `enabled` TINYINT(1) DEFAULT 1 NOT NULL, 
     PRIMARY KEY(id),
     FOREIGN KEY (`model_id`) REFERENCES `ohrm_analytics_model`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE,
     FOREIGN KEY (`location_id`) REFERENCES `ohrm_location`(`id`)  ON DELETE SET NULL,
     FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title`(`id`)  ON DELETE SET NULL
) engine=innodb default charset=utf8;


CREATE TABLE `ohrm_analytics_model_vacancy` (
    `id` INT AUTO_INCREMENT, 
    `name` VARCHAR(100) NOT NULL, 
    `model_id` INT NOT NULL, 
    `vacancy_id` INT NOT NULL, 
    PRIMARY KEY(id),
    FOREIGN KEY (`vacancy_id`) REFERENCES `ohrm_job_vacancy`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`model_id`) REFERENCES `ohrm_analytics_model`(`id`)  ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb default charset=utf8;


CREATE TABLE `ohrm_candidate_analytics_model_vacancy` (
    `id` INT AUTO_INCREMENT, 
    `candidate_id` INT NOT NULL, 
    `analytics_model_vacancy_id` INT NOT NULL, 
    `score` FLOAT(18, 2),  
    `updated_date` TIMESTAMP, 
    PRIMARY KEY(id),
    FOREIGN KEY (`candidate_id`) REFERENCES `ohrm_job_candidate`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`analytics_model_vacancy_id`) REFERENCES `ohrm_analytics_model_vacancy`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb default charset=utf8;

-- SELECT sel.*, IF((sel.score - cand.final_score)>0,(sel.score - cand.final_score),(cand.final_score-sel.score)) AS margin FROM 
-- 
-- (select AVG(a2.final_score) AS score,count(a2.candidate_id) AS event_count, GROUP_CONCAT(simi_cand.event_id) AS cand_event_id, 
-- GROUP_CONCAT(simi_cand.cand_event) AS used_event_ids, a2.* FROM `ohrm_analytics_candidate_similarity` a2 
-- 
-- INNER JOIN 
-- (SELECT ve.id as event_id,a1.id as cand_event_id,a1.event_id as cand_event, 
-- count(aiq.question_id)/(SELECT count(question_id) FROM `ohrm_analytics_interview_questions` aiq2
-- LEFT JOIN `ohrm_analytics_candidate_similarity` a4 ON a4.question_form_id = aiq2.question_form_id
-- WHERE a4.event_id = ve.id AND candidate_id = :candidate_id) as ratio_relev_ques  FROM `ohrm_analytics_candidate_similarity` a1
-- LEFT JOIN `ohrm_analytics_interview_questions` aiq ON a1.question_form_id = aiq.question_form_id
-- JOIN `ohrm_vacancy_event` ve
-- WHERE aiq.question_id IN (
-- SELECT question_id FROM `ohrm_analytics_interview_questions` aiq2
-- LEFT JOIN `ohrm_analytics_candidate_similarity` a4 ON a4.question_form_id = aiq2.question_form_id
-- WHERE a4.event_id = ve.id AND candidate_id = :candidate_id
-- )
-- AND ve.id IN ($eventIdStr) GROUP BY ve.id, aiq.question_form_id) simi_cand ON a2.id = simi_cand.cand_event_id
-- WHERE simi_cand.ratio_relev_ques > 0.4 AND a2.candidate_id<>:candidate_id GROUP BY simi_cand.event_id,a2.candidate_id) AS sel ,
-- 
-- (SELECT AVG(a3.final_score) AS final_score FROM `ohrm_analytics_candidate_similarity` a3 
-- WHERE a3.candidate_id=:candidate_id AND a3.event_id IN ($eventIdStr) ) AS cand
-- 
-- WHERE sel.score > (cand.final_score-:margin) AND sel.score < (cand.final_score+:margin) AND sel.event_count=$eventCount
-- ORDER BY margin ASC LIMIT :result_limit


-- SELECT sel.*, IF((sel.avg_score - cand.avg_score)>0,(sel.avg_score - cand.avg_score),(cand.avg_score-sel.avg_score)) AS margin FROM 
-- (SELECT a1.*, AVG(ais.score) as avg_score, GROUP_CONCAT(CONCAT(ais.question_group_id,',',ais.section_name,',',ais.score) SEPARATOR ';') as section_scores,
-- count(DISTINCT ais.question_group_id)/(SELECT count(DISTINCT question_group_id) as ratio_relev_sec FROM `ohrm_analytics_interview_sections` ais2
-- LEFT JOIN `ohrm_analytics_candidate_similarity` a4 ON a4.question_form_id = ais2.question_form_id
-- WHERE a4.vacancy_id = 5 AND a4.candidate_id = 110) as ratio_relev_ques  FROM `ohrm_analytics_candidate_similarity` a1
-- LEFT JOIN `ohrm_analytics_interview_sections` ais ON a1.question_form_id = ais.question_form_id
-- 
-- WHERE ais.question_group_id IN (SELECT DISTINCT ais2.question_group_id FROM `ohrm_analytics_interview_sections` ais2
-- LEFT JOIN `ohrm_analytics_candidate_similarity` a4 ON a4.question_form_id = ais2.question_form_id
-- WHERE a4.candidate_id = 110 AND a4.vacancy_id = 5 AND ais2.score IS NOT NULL)
-- AND a1.candidate_id <>110 AND ais.score IS NOT NULL
-- GROUP BY a1.vacancy_candidate_id ) sel, 
-- 
-- (SELECT AVG(ais3.score) AS avg_score FROM `ohrm_analytics_interview_sections` ais3 
-- WHERE ais3.vacancy_id = 5 AND ais3.candidate_id=110 AND ais3.score IS NOT NULL ) cand
-- 
-- WHERE sel.avg_score > (cand.avg_score-50) AND sel.avg_score < (cand.avg_score+50) AND sel.ratio_relev_ques > 0.5
-- ORDER BY margin ASC LIMIT 100

-- SELECT sel.*, IF((sel.avg_score - cand.avg_score)>0,(sel.avg_score - cand.avg_score),(cand.avg_score-sel.avg_score)) AS margin FROM 
-- (SELECT a1.*, AVG(sel_ais.score*20) as avg_score, GROUP_CONCAT(DISTINCT CONCAT(sel_ais.question_group_id,',',sel_ais.section_name,',',sel_ais.score) SEPARATOR ';') as section_scores, 
-- count(DISTINCT sel_ais.question_group_id)/(SELECT count(DISTINCT question_group_id) as ratio_relev_sec
--  FROM `ohrm_analytics_interview_sections` ais2 
-- 
-- LEFT JOIN `ohrm_analytics_candidate_similarity` a4 ON a4.question_form_id = ais2.question_form_id WHERE a4.vacancy_id = 50 
-- AND a4.candidate_id = 198) as ratio_relev_ques 
-- FROM `ohrm_analytics_candidate_similarity` a1 LEFT JOIN  (SELECT ais.* FROM `ohrm_analytics_interview_sections` ais 
-- WHERE ais.question_group_id IN 
-- (SELECT DISTINCT ais2.question_group_id FROM `ohrm_analytics_interview_sections` ais2 LEFT JOIN `ohrm_analytics_candidate_similarity` a4 
-- ON a4.question_form_id = ais2.question_form_id WHERE a4.candidate_id = 198 AND a4.vacancy_id = 50
-- AND ais2.score IS NOT NULL)  AND ais.score IS NOT NULL group by ais.vacancy_candidate_id,ais.question_group_id) sel_ais ON a1.question_form_id = sel_ais.question_form_id 
-- WHERE a1.candidate_id <>198
-- GROUP BY a1.vacancy_candidate_id ) sel, 
-- 
-- 
-- (SELECT AVG(cand_scores.score*20) AS avg_score FROM (SELECT ais3.* FROM `ohrm_analytics_interview_sections` ais3 WHERE ais3.vacancy_id = 50 AND 
-- ais3.candidate_id=198 AND ais3.score IS NOT NULL GROUP BY ais3.question_group_id) cand_scores ) cand 
-- WHERE 
-- sel.avg_score > (cand.avg_score-10) AND sel.avg_score < (cand.avg_score+10) AND sel.ratio_relev_ques > 0.7
-- ORDER BY margin ASC LIMIT 10