<?php

$sql = "
ALTER TABLE `redcap_surveys_scheduler_queue` CHANGE `reason_not_sent` `reason_not_sent` 
	ENUM('EMAIL ADDRESS NOT FOUND','PHONE NUMBER NOT FOUND','EMAIL ATTEMPT FAILED','UNKNOWN','SURVEY ALREADY COMPLETED','VOICE/SMS SETTING DISABLED','ERROR SENDING SMS','ERROR MAKING VOICE CALL','LINK HAD ALREADY EXPIRED','PARTICIPANT OPTED OUT') 
	CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'Explanation of why invitation did not send, if applicable';

CREATE TABLE `redcap_mycap_messages` (
`uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'UUID',
`project_code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
`type` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Announcement, standard',
`from` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Either a participant code or a redcap user',
`to` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Either a participant code or a redcap user',
`title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Optional title',
`body` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Body content',
`sent_date` datetime NOT NULL COMMENT 'Unix timestamp',
`received_date` datetime DEFAULT NULL COMMENT 'Unix timestamp',
`read_date` datetime DEFAULT NULL COMMENT 'Unix timestamp',
`processed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = No, 1 = Yes',
`processed_by` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'message processed by this REDCap user. FK to redcap_user_information.username',
PRIMARY KEY (`uuid`),
KEY `project_code_idx` (`project_code`),
KEY `received_date` (`received_date`),
KEY `sent_date` (`sent_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `redcap_mycap_participants` (
`code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Participant identifier. Alias for record_id. We never store record_id on the mobile app for security reasons.',
`project_code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'FK to redcap_mycap_projects.code',
`record` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'FK to ???',
`join_date` datetime DEFAULT NULL COMMENT 'Date participant joined the project',
`baseline_date` datetime DEFAULT NULL COMMENT 'Date of important event. Used for scheduling.',
`push_notification_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Stores push notification identifiers',
PRIMARY KEY (`code`),
UNIQUE KEY `code_unique` (`code`),
KEY `project_code` (`project_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `redcap_mycap_projectfiles` (
`project_code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'PFK to redcap_mycap_projects.code',
`doc_id` int(10) NOT NULL COMMENT 'PFK to redcap_edocs_metadata.doc_id',
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'File name',
`category` int(10) DEFAULT NULL COMMENT 'File categorization, if any. 1=PROMIS Form, 2=PROMIS Calibration, 3=Image, 4=Config Version',
PRIMARY KEY (`project_code`,`doc_id`),
KEY `doc_id_idx` (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `redcap_mycap_projects` (
`code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Project identifier. Alias for project_id. We never store project_id on the mobile app for security reasons.',
`project_id` int(10) NOT NULL COMMENT 'FK to redcap_projects.project_id',
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the project within the app',
`allow_new_participants` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Participants cannot join if FALSE',
`config` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'JSON representation of the config',
`status` int(1) NOT NULL DEFAULT '1' COMMENT '0=Deleted, 1=Active',
PRIMARY KEY (`code`),
UNIQUE KEY `code_unique` (`code`),
KEY `mcp_project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `redcap_mycap_syncissuefiles` (
`uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'PFK to redcap_mycap_syncissues.uuid',
`doc_id` int(10) NOT NULL COMMENT 'PFK to redcap_edocs_metadata.doc_id',
PRIMARY KEY (`doc_id`,`uuid`),
KEY `uuid_idx` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `redcap_mycap_syncissues` (
`uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'UUID generated by app. Maps to a field with annotation @MC-TASK-UUID',
`participant_code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'FK to a record with field annotation @MC-PARTICIPANT-CODE. FK is not enforced as someone may inadvertently delete a participant, but we still want to get results',
`project_code` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'FK to redcap_mycap_projects.code. Not enforced because someone may accidentally delete a project.',
`received_date` datetime NOT NULL COMMENT 'Date received by the server',
`payload` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Request payload in JSON format',
`instrument` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'FK to redcap_metadata.form_name. Relationship not enforced as we may receive results for tasks that were deleted in REDCap.',
`error_type` int(1) NOT NULL DEFAULT '0' COMMENT '1 = REDCap Save, 2 = Could not find participant, 3 = Could not find project, 4 = Other',
`error_message` varchar(4000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Error message that REDCap returned when attempting to save the result, or that MyCap identified',
`resolved` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = Unresolved, 1 = Resolved',
`resolved_by` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'issue resolved by this user. FK to redcap_user_information.username',
`resolved_comment` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Optional comment describing why issue was toggled as resolved',
PRIMARY KEY (`uuid`),
KEY `participant_code` (`participant_code`),
KEY `project_code` (`project_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `redcap_mycap_messages`
ADD FOREIGN KEY (`project_code`) REFERENCES `redcap_mycap_projects` (`code`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `redcap_mycap_participants`
ADD FOREIGN KEY (`code`) REFERENCES `redcap_mycap_projects` (`code`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `redcap_mycap_projectfiles`
ADD FOREIGN KEY (`doc_id`) REFERENCES `redcap_edocs_metadata` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (`project_code`) REFERENCES `redcap_mycap_projects` (`code`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `redcap_mycap_projects`
ADD FOREIGN KEY (`project_id`) REFERENCES `redcap_projects` (`project_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `redcap_mycap_syncissuefiles`
ADD FOREIGN KEY (`doc_id`) REFERENCES `redcap_edocs_metadata` (`doc_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD FOREIGN KEY (`uuid`) REFERENCES `redcap_mycap_syncissues` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `redcap_projects` ADD `pdf_hide_record_id` TINYINT(1) NOT NULL DEFAULT '0' AFTER `pdf_hide_secondary_field`;
";

// If db is using UTF8 instead of UTF8MB4, then remove MB4 from SQL
print SQLTableCheck::filterSqlCollation($sql);