DELIMITER //

USE defaultdb//

DROP FUNCTION IF EXISTS `initcap`//

CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30)
BEGIN
	SET @str='';
	SET @l_str='';
	WHILE x REGEXP ' ' DO
		SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
		SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
		SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
	END WHILE;
	RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END//



DROP PROCEDURE IF EXISTS `SendEmail`//

CREATE PROCEDURE `SendEmail` (source char(255), name char(255), reference_source char(255), refrence_id bigint(20), email_template_name char(255), job_id bigint(20), person_id bigint(20), resume_id bigint(20), tenant_person_profile_id bigint(20), from_person_id bigint(20), to_person_id bigint(20), job_application_id bigint(20), email_to_address char(255), email_cc_address char(255), email_subject char(255), email_content longtext)
BEGIN
	SET @emailTemplateId = '';
	SELECT id FROM email_templates WHERE name = email_template_name LIMIT 1 INTO @emailTemplateId;

	INSERT INTO queue_emails
			(type, source, status, name, reference_source, reference_id, email_template_id, queued_at, send_at, job_id, person_id, resume_id, tenant_person_profile_id, from_person_id, to_person_id, job_application_id, email_to_address, email_cc_address, email_subject)
		VALUES
			('Email', source, 'New', name, reference_source, reference_id, @emailTemplateId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, job_id, person_id, resume_id, tenant_person_profile_id, from_person_id, to_person_id, job_application_id, email_to_address, email_cc_address, email_subject);

END//



DROP TRIGGER IF EXISTS trg_bi_person//

CREATE TRIGGER trg_bi_person BEFORE INSERT ON person FOR EACH ROW
BEGIN
	IF NEW.uuid IS NULL THEN
		SET NEW.uuid = uuid();
	END IF;
    SET NEW.first_name = COALESCE(initcap(NEW.first_name), '-');
    SET NEW.last_name = COALESCE(initcap(NEW.last_name), '-');
    SET NEW.name = COALESCE(NEW.first_name || ' ' || NEW.last_name, '--');
	SET NEW.email = lower(NEW.email);
	SET NEW.domain = lower(SUBSTRING(NEW.email, LOCATE('@', NEW.email) + 1));
	SET NEW.profile_photo = CONCAT('https://avatars.dicebear.com/v2/avataaars/', NEW.uuid, '.svg?options[mood][]=happy');
	SET NEW.update_search_index = 1;
END//



DROP TRIGGER IF EXISTS trg_bu_person//

CREATE TRIGGER trg_bu_person BEFORE UPDATE ON person FOR EACH ROW
BEGIN
    SET NEW.first_name = COALESCE(initcap(NEW.first_name), '-');
    SET NEW.last_name = COALESCE(initcap(NEW.last_name), '-');
    SET NEW.name = COALESCE(NEW.first_name || ' ' || NEW.last_name, '--');
	SET NEW.email = lower(NEW.email);
	SET NEW.domain = lower(SUBSTRING(NEW.email, LOCATE('@', NEW.email) + 1));
END//





DROP TRIGGER IF EXISTS trg_bi_organization//

CREATE TRIGGER trg_bi_organization BEFORE INSERT ON organization FOR EACH ROW
BEGIN
	IF NEW.uuid IS NULL THEN
		SET NEW.uuid = uuid();
	END IF;
	SET NEW.profile_photo = CONCAT('https://avatars.dicebear.com/v2/bottts/', NEW.uuid, '.svg?options[mood][]=happy');
END//



DROP TRIGGER IF EXISTS trg_au_organization//

#CREATE TRIGGER trg_au_organization AFTER UPDATE ON organization FOR EACH ROW
#BEGIN
#	UPDATE jobs set update_search_index = 1 WHERE org_id = NEW.id;
#END//




DROP TRIGGER IF EXISTS trg_bi_jobs//

CREATE TRIGGER trg_bi_jobs BEFORE INSERT ON jobs FOR EACH ROW
BEGIN
	IF NEW.uuid IS NULL THEN
		SET NEW.uuid = uuid();
	END IF;
END//




DROP TRIGGER IF EXISTS trg_bu_jobs//

CREATE TRIGGER trg_bu_jobs BEFORE UPDATE ON jobs FOR EACH ROW
BEGIN
	IF (NEW.update_search_index IS NULL) THEN 
		SET NEW.update_search_index = 1;
	END IF;
END//



DROP TRIGGER IF EXISTS trg_bi_job_applications//

CREATE TRIGGER trg_bi_job_applications BEFORE INSERT ON job_applications FOR EACH ROW
BEGIN
	SET @sourcer_id = 0;
	SET @recruiter_id = 0;
	SET @hiring_manager_id = 0;

	SET NEW.status = 'Active';

	SELECT sourcer_id, recruiter_id, hiring_manager_id FROM jobs WHERE id = NEW.job_id LIMIT 1 INTO @sourcer_id, @recruiter_id, @hiring_manager_id;

	IF (@recruiter_id > 0) THEN
		SET NEW.recruiter_id = @recruiter_id;
	END IF;

	IF (@sourcer_id > 0) THEN
		SET NEW.sourcer_id = @sourcer_id;
	ELSE 
		SET NEW.sourcer_id = NEW.created_by;
	END IF;

	IF (@hiring_manager_id > 0) THEN
		SET NEW.hiring_manager_id = @hiring_manager_id;
	END IF;

END//


DROP TRIGGER IF EXISTS trg_ai_job_applications//

CREATE TRIGGER trg_ai_job_applications AFTER INSERT ON job_applications FOR EACH ROW
BEGIN
	DECLARE hiring_manager_id INTEGER DEFAULT 0;
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE hiring_managers CURSOR FOR SELECT p.id FROM jobs j, person p WHERE j.id = NEW.job_id AND j.org_id = p.org_id AND (p.type = 'Hiring Manager' OR p.type = 'Recruiter');
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

	SET @emailTemplateId = '';
	SET @from_person_id = 40; /* NEED TO FIX THE ID TO USE PERSON ID FOR GIGMINDS SUPPORT */

	/*
	IF (NEW.type != 'Candidate_Job_Match') THEN
		IF (NEW.tenant_person_profile_id IS NULL) THEN
			SELECT id FROM email_templates WHERE name = 'SEND-CANDIDATE-JOB-DETAILS' LIMIT 1 INTO @emailTemplateId;
			SELECT id FROM person WHERE user_id = NEW.created_by LIMIT 1 INTO @from_person_id;
			INSERT INTO queue_emails
				(type, source, status, name, reference_source, reference_id, sql_param_1, email_template_id, queued_at, send_at, tenant_id, job_application_id, job_id, person_id, tenant_person_profile_id, created_by, from_person_id, to_person_id)
			VALUES
				('Email', 'trg_ai_job_applications', 'New', 'SEND-CANDIDATE-JOB-DETAILS', 'job_applications', NEW.id, NEW.id, @emailTemplateId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NEW.tenant_id, NEW.id, NEW.job_id, NEW.person_id, NEW.tenant_person_profile_id, NEW.created_by, @from_person_id, NEW.person_id);
		ELSE
			OPEN hiring_managers;
			getMgrs: LOOP
				FETCH hiring_managers INTO hiring_manager_id;
				IF finished = 1 THEN
					leave getMgrs;
				END IF;

				SELECT id FROM email_templates WHERE name = 'SEND-EMPLOYER-CANDIDATE-DETAILS' LIMIT 1 INTO @emailTemplateId;
				SELECT id FROM person WHERE user_id = NEW.created_by LIMIT 1 INTO @from_person_id;
				INSERT INTO queue_emails
					(type, source, status, name, reference_source, reference_id, sql_param_1, email_template_id, queued_at, send_at, tenant_id, job_application_id, job_id, person_id, tenant_person_profile_id, created_by, from_person_id, to_person_id)
				VALUES
					('Email', 'trg_ai_job_applications', 'New', 'SEND-EMPLOYER-CANDIDATE-DETAILS', 'job_applications', NEW.id, NEW.id, @emailTemplateId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NEW.tenant_id, NEW.id, NEW.job_id, NEW.person_id, NEW.tenant_person_profile_id, NEW.created_by, @from_person_id, hiring_manager_id);
			END LOOP getMgrs;
			CLOSE hiring_managers;
		END IF;
	END IF;
	*/
	
END//



DROP TRIGGER IF EXISTS trg_bi_tenant_person_profiles//

CREATE TRIGGER trg_bi_tenant_person_profiles BEFORE INSERT ON tenant_person_profiles FOR EACH ROW
BEGIN
	IF NEW.uuid IS NULL THEN
		SET NEW.uuid = uuid();
	END IF;

	SET NEW.status = 'Active';
END//



DROP TRIGGER IF EXISTS trg_au_tenant_person_profiles//

CREATE TRIGGER trg_au_tenant_person_profiles AFTER UPDATE ON tenant_person_profiles FOR EACH ROW
BEGIN

	SELECT first_name, last_name, org_id, job_profile, total_experience_years, city, state_province, country
		FROM person 
		WHERE id = NEW.person_id 
		LIMIT 1 
		INTO @first_name, @last_name, @org_id, @job_title, @total_experience, @city, @state_province, @country;

	IF (NEW.first_name != @first_name AND NEW.first_name is not null) THEN
		SET @first_name = NEW.first_name;
	END IF;

	IF (NEW.last_name != @last_name AND NEW.last_name is not null) THEN
		SET @last_name = NEW.last_name;
	END IF;

	IF (NEW.org_id != @org_id AND NEW.org_id is not null) THEN
		SET @org_id = NEW.org_id;
	END IF;

	IF (NEW.job_title != @job_title AND NEW.job_title is not null) THEN
		SET @job_title = NEW.job_title;
	END IF;

	IF (NEW.total_experience_years != @total_experience AND NEW.total_experience_years is not null) THEN
		SET @total_experience = NEW.total_experience_years;
	END IF;

	IF (NEW.city != @city AND NEW.city is not null) THEN
		SET @city = NEW.city;
	END IF;

	IF (NEW.state_province != @state_province AND NEW.state_province is not null) THEN
		SET @state_province = NEW.state_province;
	END IF;

	IF (NEW.country != @country AND NEW.country is not null) THEN
		SET @country = NEW.country;
	END IF;

    UPDATE person 
	SET first_name = @first_name,
	    last_name = @last_name,
		org_id = @org_id,
		job_profile = @job_title,
		total_experience_years = @total_experience, 
		city = @city,
		state_province = @state_province,
	 	country = @country,
		update_search_index = 1
	WHERE id = NEW.person_id LIMIT 1;

END//



DROP TRIGGER IF EXISTS trg_bi_resumes//

CREATE TRIGGER trg_bi_resumes BEFORE INSERT ON resumes FOR EACH ROW
BEGIN
	IF NEW.uuid IS NULL THEN
		SET NEW.uuid = uuid();
	END IF;
END//


DROP TRIGGER IF EXISTS trg_bu_resumes//

CREATE TRIGGER trg_bu_resumes BEFORE UPDATE ON resumes FOR EACH ROW
BEGIN
	IF NEW.uuid IS NULL OR NEW.uuid = '' THEN
		SET NEW.uuid = uuid();
	END IF;
END//


DROP TRIGGER IF EXISTS trg_ai_resumes//

CREATE TRIGGER trg_ai_resumes AFTER INSERT ON resumes FOR EACH ROW
BEGIN

	SET @emailTemplateId = '';

	IF (NEW.from_email_address != 'jobs-listings@linkedin.com' OR NEW.from_email_address != 'jobapplies@monsterindia.com') THEN

		SELECT id FROM email_templates WHERE name = 'SEND-CANDIDATE-RESUME-CONFIRMATION' LIMIT 1 INTO @emailTemplateId;

		INSERT INTO queue_emails
			(type, source, status, name, reference_source, reference_id, sql_param_1, email_template_id, queued_at, send_at, resume_id)
		VALUES
			('Email', 'trg_ai_resumes', 'New', 'SEND-CANDIDATE-RESUME-CONFIRMATION', 'resumes', NEW.id, NEW.id, @emailTemplateId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NEW.id);

	END IF;

END//




DROP TRIGGER IF EXISTS trg_ai_notes//

CREATE TRIGGER trg_ai_notes AFTER INSERT ON notes FOR EACH ROW
BEGIN

	DECLARE email_address char(255);
    DECLARE finished INTEGER DEFAULT 0;
	DECLARE job_application_email_list CURSOR FOR select distinct email from vw_notify_users where job_id = NEW.job_id and tenant_id = NEW.tenant_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        
	SET @content = '';
	SET @subject = '';
    SET @emailFrom = '';
    SET @emailFromName = '';
    SET @candidateName = '';

	IF (NEW.type = 'Job_Application') THEN
		SELECT email, name FROM users WHERE id = NEW.created_by AND tenant_id = NEW.tenant_id LIMIT 1 INTO @emailFrom, @emailFromName;
        SELECT p.name from job_applications a left join person p on p.id = a.person_id where a.id = NEW.job_application_id and a.tenant_id = NEW.tenant_id LIMIT 1 INTO @candidateName; 
        
		SET @subject = 'Job Application Notes for ' || NEW.name || ' / ' || @candidateName;
        SET @content = 'Notes added on Job Application record by ' || @emailFromName || '<br/><br/>' || '<strong>Job Title:</strong>' || NEW.name || '<br/><strong>Candidate Name:</strong>' || @candidateName || '<br/><br/><a href="https://app.gigminds.com/my/applications/' || NEW.job_application_id || '">Please click here to view the Job Application</a><br/><br/><strong>Notes:</strong><br/>' || NEW.notes;
        
		OPEN job_application_email_list;
		getEmails: LOOP
			FETCH job_application_email_list INTO email_address;
			IF finished = 1 THEN
				leave getEmails;
			END IF;	
            
			# SET status = SendEmail(source, name, reference_source, refrence_id, email_template_name, job_id, person_id, resume_id, tenant_person_profile_id, from_person_id, to_person_id, job_application_id, email_to_address, email_cc_address, email_subject);
            # CALL SendEmail('trg_ai_notes', NEW.name, 'Notes', NEW.id, null, NEW.job_id, null, null, null, 'from_person_id', 'to_person_id', null, 'rberi@cloudely.com', null, subject, 'content');
			INSERT INTO queue_emails
				(type, source, status, name, reference_source, reference_id, sql_param_1, queued_at, send_at, tenant_id, job_id, created_by, from_email, to_email, subject, body)
			VALUES
				('Email', 'trg_ai_notes', 'New', 'SEND-NOTES-UPDATE', 'Notes', NEW.id, NEW.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NEW.tenant_id, NEW.job_id, NEW.created_by, @emailFrom, email_address, @subject, @content);


		END LOOP getEmails;
		CLOSE job_application_email_list;

	END IF;
		
END//




DROP TRIGGER IF EXISTS trg_ai_tasks//

CREATE TRIGGER trg_ai_tasks AFTER INSERT ON tasks FOR EACH ROW
BEGIN
        
	SET @content = '';
	SET @subject = '';
    SET @emailFrom = '';
    SET @emailFromName = '';
	SET @emailTo = '';
	SET @emailToName = '';
	SET @jobName = '';
	SET @candidateName = '';

	IF (NEW.type = 'Job_Application') THEN
		SELECT email, name FROM users WHERE id = NEW.created_by AND tenant_id = NEW.tenant_id LIMIT 1 INTO @emailFrom, @emailFromName;
        SELECT a.name, p.name from job_applications a LEFT JOIN person p on a.person_id = p.id  where a.id = NEW.job_application_id and a.tenant_id = NEW.tenant_id LIMIT 1 INTO @jobName, @candidateName;
		SELECT email, name FROM users WHERE id = NEW.user_id AND tenant_id = NEW.tenant_id LIMIT 1 INTO @emailTo, @emailToName;
        
		SET @subject = 'New Task assigned to you for : ' || @jobName || ' / ' || @candidateName;
        SET @content = 'New Tasked assigned to you by ' || @emailFromName || '<br/><br/>' || '<strong>Due Date: </strong>' || NEW.due_date || '<br/><strong>Job Application: </strong>' || @jobName || ' / ' || @candidateName || '<br/><strong>Subject: </strong>' || NEW.subject || '<br/><br/><strong>Details:</strong><br/>' || NEW.details  || '<br/><br/><a href="https://app.gigminds.com/my/applications/' || NEW.job_application_id || '">Please click here to view the Job Application</a>';
        
		INSERT INTO queue_emails
			(type, source, status, name, reference_source, reference_id, sql_param_1, queued_at, send_at, tenant_id, job_application_id, job_id, created_by, from_email, to_email, cc_email, subject, body)
		VALUES
			('Email', 'trg_ai_taskss', 'New', 'SEND-TASKS-UPDATE', 'Tasks', NEW.id, NEW.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NEW.tenant_id, NEW.job_application_id, NEW.job_id, NEW.created_by, @emailFrom, @emailTo, @emailFrom, @subject, @content);
	END IF;
		
END//





DROP TRIGGER IF EXISTS trg_ai_evaluations//

CREATE TRIGGER trg_ai_evaluations AFTER INSERT ON evaluations FOR EACH ROW
BEGIN

	DECLARE email_address char(255);
    DECLARE finished INTEGER DEFAULT 0;
	DECLARE evaulation_email_list CURSOR FOR select distinct email from vw_notify_users where (job_id = NEW.job_id or job_application_id = NEW.job_application_id) and tenant_id = NEW.tenant_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
        
	SET @content = '';
	SET @subject = '';
    SET @emailFrom = '';
    SET @emailFromName = '';

	SELECT email, name FROM users WHERE id = NEW.reviewed_by AND tenant_id = NEW.tenant_id LIMIT 1 INTO @emailFrom, @emailFromName;
	
	SET @subject = 'Evaluation Submission on ' || NEW.name;
	SET @content = 'Candidate Job Evaluation on ' || NEW.name || ' by ' || @emailFromName || '<br/><br/><a href="https://app.gigminds.com/my/applications/' || NEW.job_application_id || '">Please click here to view the Job Application</a><br/><br/>' || '<strong>Evaluation Notes:</strong><br/>';
	SET @content = @content || 'Type: ' || NEW.type || '<br/><br/>';

	SET @content = @content || 'Hiring Recomendation: <strong>' || NEW.hiring_recommendation || '</strong><br/><br/>';
	SET @content = @content || 'Key Strengths: ' || NEW.key_strengths || '<br/>';
	SET @content = @content || 'Key Concerns: ' || NEW.key_concerns || '<br/><br/>';
	
	SET @content = @content || 'Recommended Next Steps: ' || NEW.recommended_next_steps || '<br/><br/>';

	SET @content = @content || 'Technical: ' || NEW.technical_rating || 'stars. ' || NEW.technical_summary || '<br/>';
	SET @content = @content || 'Communication: ' || NEW.communication_rating || 'stars. ' || NEW.communication_summary || '<br/>';
	SET @content = @content || 'Behavioral: ' || NEW.behavioral_rating || 'stars. ' || NEW.behavioral_summary || '<br/>';
		
	OPEN evaulation_email_list;
	getEvalEmails: LOOP
		FETCH evaulation_email_list INTO email_address;
		IF finished = 1 THEN
			leave getEvalEmails;
		END IF;	
		
		#INSERT INTO queue_emails
		#	(type, source, status, name, reference_source, reference_id, sql_param_1, queued_at, send_at, tenant_id, job_id, created_by, from_email, to_email, subject, body)
		#VALUES
		#	('Email', 'trg_ai_evaluations', 'New', 'SEND-EVALUATIONS-UPDATE', 'Evaluations', NEW.id, NEW.id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NEW.tenant_id, NEW.job_id, NEW.created_by, @emailFrom, email_address, @subject, @content);


	END LOOP getEvalEmails;
	CLOSE evaulation_email_list;

		
END//





DELIMITER ;
