-- ahti database

CREATE TABLE IF NOT EXISTS `patients` (
    `ID` INT(11) UNSIGNED AUTO_INCREMENT,
    `givenName` VARCHAR(191) NOT NULL,
    `lastName` VARCHAR(191) NOT NULL,
    `insertion` VARCHAR(20) DEFAULT NULL,
    `doctorID` INT(11) UNSIGNED NOT NULL,
    `verificationCode` VARCHAR(4) NULL UNIQUE,
    `verificationTimestamp` INT(11) UNSIGNED DEFAULT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `phoneVerificationTimestamp` INT(11) UNSIGNED DEFAULT NULL,
    `goalsGeneral` ENUM('RELIGION', 'FAMILY', 'CUSTOM') DEFAULT NULL,
    `goalsFreeForm` VARCHAR(255) DEFAULT NULL,
    `researchOnHold` TINYINT(1) DEFAULT 0,
    `monitoringPlan` ENUM('DAILY', 'WEEKLY') NOT NULL DEFAULT 'WEEKLY',
    `behavioralPlan` ENUM('YES', 'NO') NOT NULL DEFAULT 'NO',
    `language` VARCHAR(20) NOT NULL,
    `picture` VARCHAR(255) DEFAULT NULL,
    `dateOfBirth` DATE NOT NULL,
    `gender` ENUM('FEMALE', 'MALE') NOT NULL,
    `timezone` VARCHAR(255) NOT NULL,
    `note` TEXT DEFAULT NULL,
    `country` CHAR(3) NOT NULL,
    `lastCheckedTimestamp` INT(11) UNSIGNED DEFAULT NULL,
    `lastUpdatedTimestamp` INT(11) UNSIGNED DEFAULT NULL,
    `researchGroup` ENUM('1', '2', '3') AS (CASE WHEN monitoringPlan = 'DAILY' AND behavioralPlan = 'YES' THEN '1' WHEN monitoringPlan = 'WEEKLY' AND behavioralPlan = 'NO' THEN '2' WHEN monitoringPlan = 'WEEKLY' AND behavioralPlan = 'YES' THEN '3' ELSE NULL END ) PERSISTENT,
    PRIMARY KEY (`ID`),
    INDEX USING BTREE (`verificationCode`),
    INDEX USING BTREE (`doctorID`),
    INDEX USING BTREE (`lastName`),
    INDEX USING BTREE (`country`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
    `ID` INT(11) UNSIGNED AUTO_INCREMENT,
    `givenName` VARCHAR(191) NOT NULL,
    `insertion` VARCHAR(20) DEFAULT NULL,
    `lastName` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `password` VARCHAR(191) NOT NULL,
    `clinicID` INT(11) UNSIGNED,
    `language` VARCHAR(20) NOT NULL,
    `timezone` VARCHAR(191) NOT NULL,
    `country` CHAR(3) NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX USING BTREE (`lastName`),
    INDEX USING BTREE (`clinicID`),
    INDEX USING BTREE (`country`),
    UNIQUE INDEX USING BTREE (`email`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `blood_pressure_measurements` (
    `patientID` INT(11) UNSIGNED NOT NULL,
    `actualTimestamp` INT(11) UNSIGNED NOT NULL,
    `plannedTimestamp` INT(11) UNSIGNED NOT NULL,
    `diastolic` INT(3) UNSIGNED NULL,
    `systolic` INT(3) UNSIGNED NULL,
    `researchGroup` ENUM('1', '2', '3') DEFAULT NULL,
    `age` INT(3) UNSIGNED NOT NULL,
    `gender` ENUM('FEMALE', 'MALE') NOT NULL,
    `country` CHAR(3) NOT NULL,
    `clientOS` VARCHAR(50) NOT NULL,
    `monitoringPlan` ENUM('DAILY', 'WEEKLY') NOT NULL DEFAULT 'WEEKLY',
    `timezone` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`patientID`, `actualTimestamp`),
    INDEX USING BTREE (`researchGroup`),
    INDEX USING BTREE (`country`),
    INDEX USING BTREE (`monitoringPlan`),
    INDEX USING BTREE (`diastolic`, `systolic`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `medication_adherence` (
    `patientID` INT(11) UNSIGNED NOT NULL,
    `actualTimestamp` INT(11) UNSIGNED NOT NULL,
    `adherence` DECIMAL(5,2) UNSIGNED NOT NULL,
    `researchGroup` ENUM('1', '2', '3') DEFAULT NULL,
    `age` INT(3) UNSIGNED NOT NULL,
    `gender` ENUM('FEMALE', 'MALE') NOT NULL,
    `country` CHAR(3) NOT NULL,
    `clientOS` VARCHAR(50) NOT NULL,
    `monitoringPlan` ENUM('DAILY', 'WEEKLY') NOT NULL DEFAULT 'WEEKLY',
    `timezone` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`patientID`, `actualTimestamp`),
    INDEX USING BTREE (`researchGroup`),
    INDEX USING BTREE (`country`),
    INDEX USING BTREE (`monitoringPlan`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_sessions` (
    `sessionID` VARCHAR(191) NOT NULL,
    `expires` INT(11) UNSIGNED NOT NULL,
    `sessionData` TEXT NOT NULL,
    PRIMARY KEY (`sessionID`),
    INDEX USING BTREE (`expires`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `patient_medication` (
    `ID` INT(11) UNSIGNED AUTO_INCREMENT,
    `patientID` INT(11) UNSIGNED NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `dose` DOUBLE NOT NULL,
    `unit` VARCHAR(191) NOT NULL,
    `frequency` VARCHAR(191) NOT NULL,
    `startDate` DATE DEFAULT NULL,
    `stopDate` DATE DEFAULT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
