DROP DATABASE IF EXISTS `pulso`;
CREATE DATABASE IF NOT EXISTS `pulso` /*!40101 CHARACTER SET UTF8 */;
USE `pulso`;

-- DROP TABLE IF EXISTS `account_tbl`;
CREATE TABLE `account_tbl` (
    `account_id` SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `organization` VARCHAR(100) NOT NULL DEFAULT '',
    `address1` VARCHAR(100) NOT NULL DEFAULT '',
    `address2` VARCHAR(100) NOT NULL DEFAULT '',
    `city` VARCHAR(50) NOT NULL DEFAULT '',
    `state` VARCHAR(30) NOT NULL DEFAULT '',
    `zip` VARCHAR(15) NOT NULL DEFAULT '',
    `country` VARCHAR(33) NOT NULL DEFAULT '',
    `url` VARCHAR(255) NOT NULL DEFAULT '',
    `notes` TEXT NOT NULL,
    `status` ENUM('pending', 'active', 'deactivated') DEFAULT NULL,
    `stripe_customer_id` VARCHAR(25) NOT NULL DEFAULT '',
    `available_credit` FLOAT NOT NULL DEFAULT '0',
    `used_credit` FLOAT NOT NULL DEFAULT '0',
    `recharge_amount` FLOAT NOT NULL DEFAULT '0',
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    KEY `organization` (`organization`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Accounts are organizations who keep users, run surveys, and have unique settings and reports.';

-- DROP TABLE IF EXISTS `payment_tbl`;
CREATE TABLE `payment_tbl` (
    `payment_id` MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `payment_date` DATE NOT NULL DEFAULT '0000-00-00',
    `amount` FLOAT NOT NULL DEFAULT '0',
    `status` ENUM('pending','paid','refunded','failed') DEFAULT NULL,
    `type` ENUM('cc','check','cash','paypal','bank transfer') DEFAULT NULL,
    `stripe_charge_id` VARCHAR(25) NOT NULL DEFAULT '',
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    FOREIGN KEY (`account_id`) REFERENCES `account_tbl`(`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to store payment transactions for accounts.';

-- DROP TABLE IF EXISTS `user_tbl`;
CREATE TABLE `user_tbl` (
    `user_id` SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `username` VARCHAR(100) NOT NULL DEFAULT '',
    `userpass` VARCHAR(255) NOT NULL DEFAULT '',
    `userpass_hashtype` TINYINT UNSIGNED NOT NULL DEFAULT '0',
    `first_name` VARCHAR(50) NOT NULL DEFAULT '',
    `last_name` VARCHAR(50) NOT NULL DEFAULT '',
    `email` VARCHAR(255) NOT NULL DEFAULT '',
    `user_type` ENUM('primary') default NULL,
    `status` ENUM('invited', 'email pending', 'email confirmed') DEFAULT NULL,
    `login_abuse_exempt` ENUM('true') DEFAULT NULL,
    `blocked` ENUM('true') DEFAULT NULL,
    `blocked_reason` VARCHAR(255) NOT NULL DEFAULT '',
    `abuse_warning_level` TINYINT UNSIGNED NOT NULL DEFAULT '0',
    `seconds_online` INT UNSIGNED NOT NULL DEFAULT '0',
    `last_login_datetime` DATETIME DEFAULT NULL,
    `last_access_datetime` DATETIME DEFAULT NULL,
    `last_login_ip` VARCHAR(45) NOT NULL DEFAULT '0.0.0.0',
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    KEY `username` (`username`),
    KEY `userpass` (`userpass`),
    KEY `email` (`email`),
    KEY `last_login_datetime` (`last_login_datetime`),
    KEY `last_access_datetime` (`last_access_datetime`),
    FOREIGN KEY (`account_id`) REFERENCES `account_tbl`(`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Authentication table for users, attached to accounts.';

-- DROP TABLE IF EXISTS `survey_tbl`;
CREATE TABLE `survey_tbl` (
    `survey_id` MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `survey_name` VARCHAR(80) NOT NULL DEFAULT '',
    `begin_datetime` DATETIME DEFAULT NULL,
    `end_datetime` DATETIME DEFAULT NULL,
    `virtual_number` VARCHAR(20) NOT NULL DEFAULT '',
    `status` ENUM('queued','underway','closed') DEFAULT NULL,
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    KEY `survey_name` (`survey_name`),
    KEY `begin_datetime` (`begin_datetime`),
    KEY `end_datetime` (`end_datetime`),
    KEY `virtual_number` (`virtual_number`),
    KEY `status` (`status`),
    FOREIGN KEY (`account_id`) REFERENCES `account_tbl`(`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Primary survey table.';

-- DROP TABLE IF EXISTS `question_tbl`;
CREATE TABLE `question_tbl` (
    `question_id` MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `survey_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `rank` TINYINT UNSIGNED NOT NULL DEFAULT '0',
    `question_text` VARCHAR(160) NOT NULL DEFAULT '',
    `auto_continue` ENUM('true') DEFAULT NULL,
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    KEY `question_text` (`question_text`),
    FOREIGN KEY (`survey_id`) REFERENCES `survey_tbl`(`survey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Primary question table.';

-- DROP TABLE IF EXISTS `question_action_tbl`;
CREATE TABLE `question_action_tbl` (
    `question_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `question_option` VARCHAR(160) NOT NULL DEFAULT '', -- TODO: The response that matches this action ('a', 'b', … or longer? do we need varchar(160)?)
    `question_action` ENUM('continue','goto','end') DEFAULT NULL,
    `target_question_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`question_id`, `question_option`),
    FOREIGN KEY (`question_id`) REFERENCES `question_tbl`(`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Actions to perform for specified options of each answer.';

-- DROP TABLE IF EXISTS `participant_tbl`;
CREATE TABLE `participant_tbl` (
    `participant_id` MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `survey_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `phone` VARCHAR(20) NOT NULL DEFAULT '',
    `status` ENUM('unknown','active','inactive','opted out') DEFAULT NULL,
    `source` ENUM('web admin','imported','opt-in') DEFAULT NULL,
    `last_active_datetime` DATETIME DEFAULT NULL,
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Primary participant table.';

-- DROP TABLE IF EXISTS `participant_question_tbl`;
CREATE TABLE `participant_question_tbl` (
    `participant_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `question_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `sent_datetime` DATETIME DEFAULT NULL,
    FOREIGN KEY (`participant_id`) REFERENCES `participant_tbl`(`participant_id`),
    FOREIGN KEY (`question_id`) REFERENCES `question_tbl`(`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Maintains state of which questions have been sent to participants, to avoid sending duplicates.';

-- DROP TABLE IF EXISTS `participant_survey_tbl`;
CREATE TABLE `participant_survey_tbl` (
    `participant_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `survey_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `status` ENUM('pending','underway','completed') DEFAULT NULL,
    `current_question_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    FOREIGN KEY (`participant_id`) REFERENCES `participant_tbl`(`participant_id`),
    FOREIGN KEY (`survey_id`) REFERENCES `survey_tbl`(`survey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Maintains state between participants and surveys so we know which participants are members of which surveys, their progress through that survey, and the current question to send or receive an answer for.';

-- DROP TABLE IF EXISTS `response_tbl`;
CREATE TABLE `response_tbl` (
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `participant_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `question_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `response` VARCHAR(160) NOT NULL DEFAULT '', -- TODO: do we need varchar(160)?
    `response_parsed` VARCHAR(160) NOT NULL DEFAULT '',
    `added_datetime` DATETIME DEFAULT NULL,
    PRIMARY KEY `participant_id` (`participant_id`, `question_id`),
    KEY `response` (`response`),
    FOREIGN KEY (`participant_id`) REFERENCES `participant_tbl`(`participant_id`),
    FOREIGN KEY (`question_id`) REFERENCES `question_tbl`(`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table of received SMS survey responses.';

-- DROP TABLE IF EXISTS `response_concat_tbl`;
CREATE TABLE `response_concat_tbl` (
    `concat-ref` SMALLINT NOT NULL DEFAULT '0', -- On Wed, 26 Nov 2014 18:07:11 +0000, Nexmo Customer Care wrote: the maximum value is the string of the number that can be represented in 2 bytes:  so thats 5 chars  (0 - 65535)
    `concat-total` TINYINT UNSIGNED NOT NULL DEFAULT '0',
    `concat-part` TINYINT UNSIGNED NOT NULL DEFAULT '0',
    `text` VARCHAR(160) NOT NULL DEFAULT '',
    `added_datetime` DATETIME DEFAULT NULL,
    UNIQUE KEY `concat-ref` (`concat-ref`, `concat-part`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A place to temporarily store multipart SMS messages before they are combined and returned as a whole response.';

-- DROP TABLE IF EXISTS `trigger_tbl`;
CREATE TABLE `trigger_tbl` (
    `trigger_id` MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `account_id` SMALLINT UNSIGNED DEFAULT NULL,
    `virtual_number` VARCHAR(20) NOT NULL DEFAULT '',
    `keyword` VARCHAR(160) NOT NULL DEFAULT '',
    `action` ENUM('notify', 'auto-reply', 'results') DEFAULT NULL,
    `action_options` TEXT NOT NULL,
    `added_datetime` DATETIME DEFAULT NULL,
    `added_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    `modified_datetime` DATETIME DEFAULT NULL,
    `modified_by_user_id` SMALLINT UNSIGNED DEFAULT NULL,
    KEY keyword (keyword),
    KEY virtual_number (virtual_number)
) ENGINE=InnoDB COMMENT='Main table for trigger configurations.';

-- DROP TABLE IF EXISTS `trigger_log_tbl`;
CREATE TABLE `trigger_log_tbl` (
    `trigger_id` MEDIUMINT UNSIGNED DEFAULT NULL,
    `phone` VARCHAR(20) NOT NULL DEFAULT '',
    `response` VARCHAR(160) NOT NULL DEFAULT '',
    `result` TEXT NOT NULL,
    `added_datetime` DATETIME DEFAULT NULL,
    KEY `phone` (`phone`),
    FOREIGN KEY (`trigger_id`) REFERENCES `trigger_tbl`(`trigger_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Log of triggers that have been executed.';

# -------------------------------------------------------------------
# TESTING
# -------------------------------------------------------------------

# SELECT * FROM participant_question_tbl;
# SELECT * FROM participant_survey_tbl;
# SELECT * FROM response_tbl where question_id = '1';
# SELECT * FROM survey_tbl;
# SELECT * FROM question_tbl;

# Reset surveys to initial state:
-- DELETE FROM response_tbl WHERE participant_id = '1';DELETE FROM participant_question_tbl WHERE participant_id = '1';DELETE FROM participant_survey_tbl WHERE participant_id = '1';
-- DELETE FROM response_tbl;DELETE FROM participant_question_tbl;DELETE FROM participant_survey_tbl;



-- ALTER DATABASE pulso DEFAULT COLLATE utf8_general_ci;
-- ALTER TABLE participant_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ALTER TABLE survey_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ALTER TABLE question_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ALTER TABLE question_action_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ALTER TABLE participant_question_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ALTER TABLE participant_survey_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ALTER TABLE response_tbl CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;



-- INSERT INTO participant_question_tbl (
--     participant_id,
--     question_id,
--     sent_datetime
-- ) VALUES (
--     '1',
--     '1',
--     NOW()
-- )

-- CREATE TEMPORARY TABLE user_tbl_1 SELECT * FROM user_tbl WHERE user_id = 1;
-- UPDATE user_tbl_1 SET user_id = NULL;
-- INSERT INTO user_tbl SELECT * FROM user_tbl_1;
-- DROP TEMPORARY TABLE IF EXISTS user_tbl_1;
--
-- CREATE TEMPORARY TABLE account_tbl_1 SELECT * FROM account_tbl WHERE account_id = 1;
-- UPDATE account_tbl_1 SET account_id = NULL;
-- INSERT INTO account_tbl SELECT * FROM account_tbl_1;
-- DROP TEMPORARY TABLE IF EXISTS account_tbl_1;
--
--
-- UPDATE user_tbl SET account_id = '1', username = 'quinn' WHERE user_id = 1;
-- UPDATE participant_tbl SET account_id = '1' WHERE participant_id < 3;
-- UPDATE trigger_tbl SET account_id = '1' WHERE account_id = 0;
--
--
SELECT * FROM user_tbl;
SELECT * FROM account_tbl;
SELECT * FROM question_tbl;
SELECT * FROM question_tbl where question_id = 2\G
SELECT * FROM response_tbl;
SELECT * FROM participant_tbl;
SELECT * FROM participant_survey_tbl;
-- SELECT * FROM trigger_tbl;
SELECT * FROM survey_tbl;