-- CREATE TABLE "smd__events" ----------------------------------
CREATE TABLE `smd__events` (
	`eventID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`type` VarChar( 64 ) NOT NULL,
	`eventTime` Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`params` JSON NULL,
	`status` Enum( 'done', 'waiting' ) NOT NULL DEFAULT 'waiting',
	PRIMARY KEY ( `eventID` ),
	CONSTRAINT `unique_eventID` UNIQUE( `eventID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_eventTime" ------------------------------
CREATE INDEX `index_eventTime` ON `smd__events`( `eventTime` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_status1" --------------------------------
CREATE INDEX `index_status1` ON `smd__events`( `status` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_type8" ----------------------------------
CREATE INDEX `index_type8` ON `smd__events`( `type` );
-- -------------------------------------------------------------

-- CREATE TABLE "smd__user_has_events" -------------------------
CREATE TABLE `smd__user_has_events` (
	`userID` Int( 11 ) UNSIGNED NOT NULL,
	`eventID` Int( 11 ) UNSIGNED NOT NULL,
	`emailQueueID` Int( 11 ) UNSIGNED NULL,
	`processedOn` Timestamp NULL,
	`status` Enum( 'done', 'waiting' ) NOT NULL DEFAULT 'waiting',
	PRIMARY KEY ( `userID`, `eventID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_eventID" --------------------------------
CREATE INDEX `index_eventID` ON `smd__user_has_events`( `eventID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_userID" ---------------------------------
CREATE INDEX `index_userID` ON `smd__user_has_events`( `userID` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_processTime" ----------------------------
CREATE INDEX `index_processTime` ON `smd__user_has_events`( `processedOn` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_status11" -------------------------------
CREATE INDEX `index_status11` ON `smd__user_has_events`( `status` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_emailQueueID" ---------------------------
CREATE INDEX `index_emailQueueID` ON `smd__user_has_events`( `emailQueueID` );
-- -------------------------------------------------------------

-- CREATE LINK "smd__events_smd__user_has_events_eventID_eventID"
ALTER TABLE `smd__user_has_events`
	ADD CONSTRAINT `smd__events_smd__user_has_events_eventID_eventID` FOREIGN KEY ( `eventID` )
	REFERENCES `smd__events`( `eventID` )
	ON DELETE Cascade
	ON UPDATE Cascade;
-- -------------------------------------------------------------

-- CREATE TABLE "smd__email_queue" -----------------------------
CREATE TABLE `smd__email_queue` (
	`emailQueueID` Int( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL,
	`from` VarChar( 64 ) NULL,
	`fromName` VarChar( 64 ) NULL,
	`to` VarChar( 64 ) NOT NULL,
	`toName` VarChar( 64 ) NOT NULL,
	`subject` VarChar( 255 ) NOT NULL,
	`content` Text NOT NULL,
	`createDate` Timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`sent` Timestamp NULL,
	`attempt` Int( 11 ) UNSIGNED NULL,
	`maxAttempts` Int( 11 ) UNSIGNED NOT NULL DEFAULT 3,
	`lastAttempt` Timestamp NULL
	PRIMARY KEY ( `emailQueueID` ),
	CONSTRAINT `unique_emailQueueID` UNIQUE( `emailQueueID` ) )
ENGINE = InnoDB;
-- -------------------------------------------------------------

-- CREATE INDEX "index_timestamp" ------------------------------
CREATE INDEX `index_timestamp` ON `smd__email_queue`( `createDate` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_sent" -----------------------------------
CREATE INDEX `index_sent` ON `smd__email_queue`( `sent` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_attempt" --------------------------------
CREATE INDEX `index_attempt` ON `smd__email_queue`( `attempt` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_maxAttempts" ----------------------------
CREATE INDEX `index_maxAttempts` ON `smd__email_queue`( `maxAttempts` );
-- -------------------------------------------------------------

-- CREATE INDEX "index_lastAttempt" ----------------------------
CREATE INDEX `index_lastAttempt` ON `smd__email_queue`( `lastAttempt` );
-- -------------------------------------------------------------

-- CREATE LINK "smd__email_queue_smd__user_has_events_queueID_emailQueueID"
ALTER TABLE `smd__user_has_events`
	ADD CONSTRAINT `smd__email_queue_smd__user_has_events_queueID_emailQueueID` FOREIGN KEY ( `emailQueueID` )
	REFERENCES `smd__email_queue`( `emailQueueID` )
	ON DELETE Set NULL
	ON UPDATE Cascade;
-- -------------------------------------------------------------
