USE `vokesscp`;


##############################################################################################################################################################################################
# Yaa! Autoresponder                                                                                                                                                                         #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`yaa_autoresponders` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `postfixAliasKey`                       SMALLINT(5) UNSIGNED NOT NULL,
    `postfixAlias`                          VARCHAR(151) BINARY NOT NULL,                           # Full email address, e.g. user@example.com
    `senderName`                            VARCHAR(50) BINARY DEFAULT NULL,                        # Sender name, e.g. John Doe, just to be able to edit it
    `sender`                                VARCHAR(201) BINARY NOT NULL,                           # Full sender address, e.g. "John Doe" <user@example.com>, just for Yaa rewriting
    `localDomains`                          VARCHAR(250) BINARY NOT NULL,                           # Autoreply domain to listen for (configuration key: postfixAutoreplyDomain)
    `forward`                               VARCHAR(250) BINARY DEFAULT NULL,                       # Additionally forward the mail to this address
    `active`                                ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `startDate`                             DATETIME DEFAULT NULL,
    `endDate`                               DATETIME DEFAULT NULL,
    `waitingPeriodForSameSender`            SMALLINT(5) UNSIGNED NOT NULL DEFAULT 24,               # Hours to wait before replying to the same sender that already received the message
    `subject`                               VARCHAR(150) BINARY NOT NULL,
    `message`                               LONGTEXT BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    CONSTRAINT `fk_yaaAutoresponders_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_yaaAutoresponders_postfixAliasKey` FOREIGN KEY (`postfixAliasKey`)
        REFERENCES `vokesscp`.`postfix_aliases` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Yaa! autoresponders' CHARACTER SET=utf8 COLLATE=utf8_bin;


#DELIMITER |
#DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_UPDATE_configuration_autoreply_yaa`
#|
#CREATE
#    DEFINER = 'vsscp'@'localhost'
#    TRIGGER `vokesscp`.`AFTER_UPDATE_configuration_autoreply_yaa` AFTER UPDATE ON `vokesscp`.`configuration`
#    FOR EACH ROW BEGIN
#        IF NEW.`key` = 'postfixAutoreplyDomain' AND OLD.`value` != NEW.`value` THEN
#            UPDATE `vokesscp`.`yaa_autoresponders` SET `localDomains` = NEW.`value` WHERE `localDomains` = OLD.`value`;
#        END IF;
#    END;
#|
#DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`yaa_senderRecipientPairs` (
    `id`                                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `autoresponderKey`                      SMALLINT(5) UNSIGNED NOT NULL,
    `recipient`                             VARCHAR(250) BINARY NOT NULL,                           # Recipient of the response (originally the one who sent us an email)
    `lastHitDate`                           DATETIME NOT NULL,
    `sendDate`                              DATETIME DEFAULT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_autoresponderKey_recipient` (`autoresponderKey`, `recipient`),
    CONSTRAINT `fk_yaaSenderRecipientPairs_autoresponderKey` FOREIGN KEY (`autoresponderKey`)
        REFERENCES `vokesscp`.`yaa_autoresponders` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Response sender / recipient pairs' CHARACTER SET=utf8 COLLATE=utf8_bin;


GRANT SELECT ON `vokesscp`.`yaa_autoresponders`                                          TO 'vsscp_yaa'@'localhost' IDENTIFIED BY 'KmkJSQ9WDhFT5uxAJjXj';
GRANT SELECT, INSERT, UPDATE, DELETE ON `vokesscp`.`yaa_senderRecipientPairs`            TO 'vsscp_yaa'@'localhost' IDENTIFIED BY 'KmkJSQ9WDhFT5uxAJjXj';
FLUSH PRIVILEGES;

