/* GET USER MEDICINE ADHERENCE */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS calculateHealthScore;
COMMIT;

DELIMITER //

CREATE PROCEDURE `calculateHealthScore`(`p_userId` INT, `p_startOfPrevWeek` DATETIME, 
  `p_endOfPrevWeek` DATETIME, `p_startOfPrevMonth` DATETIME, `p_endOfPrevMonth` DATETIME)
BEGIN

DECLARE errno INT;
DECLARE iScore INT DEFAULT 0;
DECLARE iWeight INT DEFAULT 0;

DECLARE iMedicationAdherence INT DEFAULT NULL;
DECLARE iMedicationAdherenceScore INT DEFAULT NULL;
DECLARE vMedicineRisk VARCHAR(255) DEFAULT NULL;
DECLARE iMedicineRiskScore INT DEFAULT NULL;
DECLARE vBloodPressure VARCHAR(20) DEFAULT NULL;
DECLARE iBloodPressureScore INT DEFAULT NULL;
DECLARE dSugar DECIMAL(10,2) DEFAULT NULL;
DECLARE iSugarScore INT DEFAULT NULL;
DECLARE dBmi DECIMAL(10, 1) DEFAULT NULL;
DECLARE iBmiScore INT DEFAULT NULL;
DECLARE iBuddy TINYINT DEFAULT NULL;
DECLARE iBuddyScore INT DEFAULT NULL;
DECLARE iAge INT DEFAULT NULL;
DECLARE iAgeScore INT DEFAULT NULL;

DECLARE iMedTotal INT;
DECLARE iMedSkipped INT;
DECLARE iMedicineRiskCount INT;
DECLARE iSystolic INT;
DECLARE iDiastolic INT;
DECLARE iSystolicScore INT;
DECLARE iDiastolicScore INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
	GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: calcHealth', NOW(), errno; 

	RESIGNAL;
END;

/* [START] Adherence calculations */
  SELECT COUNT_VALID_REMINDERS(p_userId, p_startOfPrevWeek, p_endOfPrevWeek)
  INTO iMedTotal;

  IF iMedTotal <> 0 AND iMedTotal IS NOT NULL THEN 

    SELECT COUNT_SKIPPED_REMINDERS(p_userId, p_startOfPrevWeek, p_endOfPrevWeek)
    INTO iMedSkipped;

    SELECT ROUND(((iMedTotal - IFNULL(iMedSkipped, 0)) / iMedTotal) * 100) as 'adherence'
    INTO iMedicationAdherence;

    SELECT IF(iMedicationAdherence < 60, 0, IF(iMedicationAdherence <= 80, 50, 100))
    INTO iMedicationAdherenceScore;

    SELECT iScore + ROUND((iMedicationAdherenceScore * (GET_SCORE_WEIGHT('medicationAdherence')) / 100)),
      iWeight + GET_SCORE_WEIGHT('medicationAdherence')
    INTO iScore, iWeight;

  END IF;
/* [END] Adherence calculations */

/* [START] Medicine Risk calculations */
  SELECT LIST_MEDICINE_RISKS(p_userId, p_startOfPrevWeek)
  INTO vMedicineRisk;

  IF vMedicineRisk IS NOT NULL THEN 

    SELECT COUNT_MEDICINE_RISK_LEVEL_ONES(vMedicineRisk)
    INTO iMedicineRiskCount;

    SELECT IF(iMedicineRiskCount > 1, 0, IF(iMedicineRiskCount = 1, 50, 100))
    INTO iMedicineRiskScore;

  END IF;
/* [END] Medicine Risk calculations */

/* [START] Blood Pressure calculations */
  SELECT GET_AVERAGE_SYSTOLIC(p_userId, p_startOfPrevWeek, p_endOfPrevWeek), GET_AVERAGE_DIASTOLIC(p_userId, p_startOfPrevWeek, p_endOfPrevWeek)
  INTO iSystolic, iDiastolic;

  IF iSystolic <> 0 AND iSystolic IS NOT NULL THEN 

    SELECT IF(iSystolic > 180, 0, IF(iSystolic >= 130 OR iSystolic < 90, 50, 100)),
    IF(iDiastolic > 120, 0, IF(iDiastolic >= 80 OR iDiastolic < 60, 50, 100)),
    CONCAT(iSystolic, '/', iDiastolic)
    INTO iSystolicScore, iDiastolicScore, vBloodPressure;

    SELECT IF(iSystolicScore > iDiastolicScore, iDiastolicScore, iSystolicScore)
    INTO iBloodPressureScore;

    SELECT iScore + ROUND(iBloodPressureScore * (GET_SCORE_WEIGHT('bloodPressure') / 100)),
      iWeight + GET_SCORE_WEIGHT('bloodPressure')
    INTO iScore, iWeight;

  END IF;
/* [END] Blood Pressure calculations */

/* [START] Sugar calculations */
  SELECT GET_AVERAGE_SUGAR(p_userId, p_startOfPrevWeek, p_endOfPrevWeek)
  INTO dSugar;

  IF dSugar <> 0 AND dSugar IS NOT NULL THEN

    SELECT IF(dSugar > 10 OR dSugar < 3, 0, IF(dSugar >= 7 OR dSugar < 3.9, 50, 100))
    INTO iSugarScore;

    SELECT iScore + ROUND(iSugarScore * (GET_SCORE_WEIGHT('sugar') / 100)),
      iWeight + GET_SCORE_WEIGHT('sugar')
    INTO iScore, iWeight;

  END IF;
/* [END] Sugar calculations */

/* [START] BMI calulations */
  SELECT GET_AVERAGE_BMI(p_userId, p_startOfPrevMonth, p_endOfPrevMonth)
  INTO dBmi;

  IF dBmi <> 0 AND dBmi IS NOT NULL THEN 

    SELECT IF(dBmi > 30, 0, IF(dBmi > 25 OR dBmi < 18.5, 50, 100))
    INTO iBmiScore;

    SELECT iScore + ROUND(iBmiScore * (GET_SCORE_WEIGHT('bmi') / 100)), 
      iWeight + GET_SCORE_WEIGHT('bmi')
    INTO iScore, iWeight;

  END IF;
/* [END] BMI calulations */

/* [START] Buddy calculations */
  SELECT USER_HAS_BUDDIES(p_userId)
  INTO iBuddy;

  SELECT IF(iBuddy = 0 OR iBuddy IS NULL, 50, 100) 
  INTO iBuddyScore;

  SELECT iScore + ROUND((iBuddyScore * (GET_SCORE_WEIGHT('buddy')) / 100)), 
    iWeight + GET_SCORE_WEIGHT('buddy')
  INTO iScore, iWeight;
/* [END] Buddy calculations */

/* [START] Age calculations */
SELECT IF(`dateOfBirth` IS NOT NULL, TIMESTAMPDIFF(YEAR, `dateOfBirth`, CURDATE()), NULL)
FROM `Users`
WHERE `_id` = p_userId
INTO iAge;

IF iAge IS NOT NULL THEN

  SELECT IF(iAge >= 60, 0, IF(iAge >= 40, 50, 100))
  INTO iAgeScore;

  SELECT iScore + ROUND(iAge * (GET_SCORE_WEIGHT('age') / 100)), iWeight + GET_SCORE_WEIGHT('age')
  INTO iScore, iWeight;

END IF;
/* [END] Age calculations */

/* [START] Save weekly score */
  INSERT INTO `WeeklyScores` (`score`, `medicationAdherence`, `medicationAdherenceScore`, `medicineRisk`, 
    `medicineRiskScore`, `bloodPressure`, `bloodPressureScore`, `sugar`, `sugarScore`, `bmi`, `bmiScore`, 
    `buddy`, `buddyScore`, `age`, `ageScore`, `weekStart`, `userId`, `dependant`, `createdAt`, `updatedAt`) 
  SELECT ROUND(iScore/iWeight*100), iMedicationAdherence, iMedicationAdherenceScore, vMedicineRisk, 
    iMedicineRiskScore, vBloodPressure, iBloodPressureScore, dSugar, iSugarScore, dBmi, iBmiScore, iBuddy, 
    iBuddyScore, iAge, iAgeScore, p_startOfPrevWeek, p_userId, NULL, NOW(), NOW()
  ON DUPLICATE KEY 
  UPDATE `score` = ROUND(iScore/iWeight*100), 
    `medicationAdherence` = iMedicationAdherence, 
    `medicationAdherenceScore` = medicationAdherenceScore, 
    `medicineRisk` = vMedicineRisk, 
    `medicineRiskScore` = iMedicineRiskScore, 
    `bloodPressure` = vBloodPressure, 
    `bloodPressureScore` = iBloodPressureScore, 
    `sugar` = dSugar, 
    `sugarScore` = iSugarScore, 
    `bmi` = dBmi, 
    `bmiScore` = iBmiScore, 
    `buddy` = iBuddy, 
    `buddyScore` = iBuddyScore, 
    `age` = iAge, 
    `ageScore` = iAgeScore, 
    `updatedAt` = NOW();
/* [END] Save weekly score */

/* [START] Generate Payload */
SELECT * 
FROM `WeeklyScores`
WHERE `userId` = p_userId
AND DATEDIFF(`weekStart`, p_startOfPrevWeek) = 0;
/* [END] Generate Payload */

END
//

COMMIT //

DELIMITER ;

