USE `vokesscp`;


##############################################################################################################################################################################################
# Postfix                                                                                                                                                                                    #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`postfix_hostnames` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `customerIpToAccountKey`                SMALLINT(5) UNSIGNED NOT NULL,
    `sslEnabled`                            ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `sslCertificate`                        LONGTEXT BINARY DEFAULT NULL,
    `sslKeyfile`                            LONGTEXT BINARY DEFAULT NULL,
    `subdomainKey`                          SMALLINT(5) UNSIGNED NOT NULL,
    `fullDomain`                            VARCHAR(255) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_sslEnabled_subdomainKey` (`sslEnabled`, `subdomainKey`),
    UNIQUE INDEX `uidx_sslEnabled_fullDomain` (`sslEnabled`, `fullDomain`),
    CONSTRAINT `fk_postfixHostnames_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_postfixHostnames_customerIpToAccountKey` FOREIGN KEY (`customerIpToAccountKey`)
        REFERENCES `vokesscp`.`customer_ips_to_accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_postfixHostnames_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_postfixHostnames_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Hostnames that Postfix is responsible for, e.g. mail.example.com' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`postfix_domains` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `subdomainKey`                          SMALLINT(5) UNSIGNED NOT NULL,
    `fullDomain`                            VARCHAR(255) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_subdomainKey` (`subdomainKey`),
    UNIQUE INDEX `uidx_fullDomain` (`fullDomain`),
    CONSTRAINT `fk_postfixDomains_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_postfixDomains_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_postfixDomains_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Mail domains that Postfix is responsible for, e.g. for <foobar>@example.com' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_DELETE_postfix_domains`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_DELETE_postfix_domains` BEFORE DELETE ON `vokesscp`.`postfix_domains`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`postfix_aliases` WHERE `accountKey` = OLD.`accountKey` AND `postfixDomainKey` = OLD.`id`;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`postfix_transports` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `domain`                                VARCHAR(255) BINARY NOT NULL,
    `destination`                           VARCHAR(250) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_domain` (`domain`)
) ENGINE=InnoDB COMMENT='Mail domains that are redirected to other delivery agents' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_UPDATE_configuration_autoreply_transports`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_UPDATE_configuration_autoreply_transports` AFTER UPDATE ON `vokesscp`.`configuration`
    FOR EACH ROW BEGIN
        DECLARE `domain` VARCHAR(255) BINARY;
        DECLARE `destination` VARCHAR(250) BINARY;
        
        IF NEW.`key` = 'postfixAutoreplyDomain' AND OLD.`value` != NEW.`value` THEN
            SELECT `value` INTO `destination` FROM `vokesscp`.`configuration` WHERE `key` = `postfixAutoreplyDestination`;
            
            UPDATE `vokesscp`.`postfix_transports` SET `domain` = NEW.`value` WHERE `domain` = OLD.`value` AND `destination` = `destination`;
        END IF;
        IF NEW.`key` = 'postfixAutoreplyDestination' AND OLD.`value` != NEW.`value` THEN
            SELECT `value` INTO `domain` FROM `vokesscp`.`configuration` WHERE `key` = `postfixAutoreplyDomain`;
            
            UPDATE `vokesscp`.`postfix_transports` SET `destination` = NEW.`value` WHERE `domain` = `domain` AND `destination` = OLD.`value`;
        END IF;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `postfix_accounts` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `postfixDomainKey`                      SMALLINT(5) UNSIGNED NOT NULL,
    `username`                              VARCHAR(25) BINARY NOT NULL,                            # Automatically suffixed by the domain specified by postfixDomainKey
    `password`                              VARCHAR(50) BINARY NOT NULL,
    `receiveMail`                           ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `sendMail`                              ENUM('false', 'true') NOT NULL DEFAULT 'false',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uidx_username` (`username`),
    CONSTRAINT `fk_postfixAccounts_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `accounts` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_postfixAccounts_postfixDomainKey` FOREIGN KEY (`postfixDomainKey`)
        REFERENCES `postfix_domains` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Postfix accounts (logins)' DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_INSERT_postfix_accounts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_INSERT_postfix_accounts` AFTER INSERT ON `vokesscp`.`postfix_accounts`
    FOR EACH ROW BEGIN
        DECLARE `accountBasePath` VARCHAR(100) BINARY;
        DECLARE `account` VARCHAR(25) BINARY;
        DECLARE `quota` MEDIUMINT(5) UNSIGNED;
        DECLARE `maildir` TEXT BINARY;

        IF NEW.`receiveMail` = 'true' THEN
            SELECT `value` INTO `accountBasePath` FROM `vokesscp`.`configuration` WHERE `key` = 'accountBasePath';
            SELECT `username`, `postfixMailboxQuota` INTO `account`, `quota` FROM `vokesscp`.`accounts` WHERE `id` = NEW.`accountKey`;
            SELECT CONCAT(NEW.`username`, '@', `fullDomain`) INTO `maildir` FROM `vokesscp`.`postfix_domains` WHERE `id` = NEW.`postfixDomainKey`;

            IF NEW.`receiveMail` THEN
                INSERT INTO `vokesscp`.`postfix_mailboxes` SET
                    `postfixAccountKey` = NEW.`id`,
                    `homedir` = CONCAT(`accountBasePath`, '/', `account`),
                    `maildir` = `maildir`,
                    `quota` = `quota`;
            END IF;
        END IF;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_UPDATE_postfix_accounts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_UPDATE_postfix_accounts` AFTER UPDATE ON `vokesscp`.`postfix_accounts`
    FOR EACH ROW BEGIN
        DECLARE `accountBasePath` VARCHAR(100) BINARY;
        DECLARE `account` VARCHAR(25) BINARY;
        DECLARE `quota` MEDIUMINT(5) UNSIGNED;
        DECLARE `maildir` TEXT BINARY;

        IF OLD.`receiveMail` = 'false' AND NEW.`receiveMail` = 'true' THEN
            SELECT `value` INTO `accountBasePath` FROM `vokesscp`.`configuration` WHERE `key` = 'accountBasePath';
            SELECT `username`, `postfixMailboxQuota` INTO `account`, `quota` FROM `vokesscp`.`accounts` WHERE `id` = NEW.`accountKey`;
            SELECT CONCAT(NEW.`username`, '@', `fullDomain`) INTO `maildir` FROM `vokesscp`.`postfix_domains` WHERE `id` = NEW.`postfixDomainKey`;

            INSERT INTO `vokesscp`.`postfix_mailboxes` SET
                `postfixAccountKey` = NEW.`id`,
                `homedir` = CONCAT(`accountBasePath`, '/', `account`),
                `maildir` = `maildir`,
                `quota` = `quota`;
        ELSEIF OLD.`receiveMail` = 'true' AND NEW.`receiveMail` = 'false' THEN
            DELETE FROM `vokesscp`.`postfix_mailboxes` WHERE `postfixAccountKey` = NEW.`id`;
        END IF;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_DELETE_postfix_accounts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_DELETE_postfix_accounts` AFTER DELETE ON `vokesscp`.`postfix_accounts`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`postfix_mailboxes` WHERE `postfixAccountKey` = OLD.`id`;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `postfix_mailboxes` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `postfixAccountKey`                     SMALLINT(5) UNSIGNED NOT NULL,
    `homedir`                               VARCHAR(126) BINARY NOT NULL,                           # Parent maildir for this mailbox, <accountBasePath>/<account>
                                                                                                    # No leading slash as virtual_mailbox_base and courier prepend it automatically!
    `maildir`                               TEXT BINARY NOT NULL,                                   # Maildir for this mailbox, <username@domain-from-postfix_accounts>
    `quota`                                 MEDIUMINT(5) UNSIGNED NOT NULL DEFAULT 25,              # Allowed space
    `size`                                  BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,                 # Acutally used space
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uidx_postfixAccountKey` (`postfixAccountKey`),
#    UNIQUE INDEX `uidx_homedir_maildir` (`homedir`, `maildir`),
    CONSTRAINT `fk_postfixMailboxes_postfixAccountKey` FOREIGN KEY (`postfixAccountKey`)
        REFERENCES `postfix_accounts` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='Postfix mailboxes' DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_INSERT_postfix_mailboxes`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_INSERT_postfix_mailboxes` AFTER INSERT ON `vokesscp`.`postfix_mailboxes`
    FOR EACH ROW BEGIN
        DECLARE `accountKey` SMALLINT(5) UNSIGNED;
        DECLARE `vmailLinuxUserId` SMALLINT(5) UNSIGNED;
        DECLARE `vmailLinuxGroupId` SMALLINT(5) UNSIGNED;
        
        SELECT `accountKey` INTO `accountKey` FROM `vokesscp`.`postfix_accounts` WHERE `id` = NEW.`postfixAccountKey`;
        SELECT CAST(`value` AS UNSIGNED INTEGER) INTO `vmailLinuxUserId` FROM `vokesscp`.`configuration` WHERE `key` = 'postfixVmailUserId';
        SELECT CAST(`value` AS UNSIGNED INTEGER) INTO `vmailLinuxGroupId` FROM `vokesscp`.`configuration` WHERE `key` = 'postfixVmailGroupId';

        CALL `vokesscp`.`job_createMailbox` (
            `accountKey`,
            NEW.`id`,
            NEW.`homedir`,
            NEW.`maildir`,
            `vmailLinuxUserId`,
            `vmailLinuxGroupId`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_DELETE_postfix_mailboxes`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_DELETE_postfix_mailboxes` BEFORE DELETE ON `vokesscp`.`postfix_mailboxes`
    FOR EACH ROW BEGIN
        DECLARE `mailbox` VARCHAR(75) BINARY;
        
        SELECT `username` INTO `mailbox` FROM `vokesscp`.`postfix_accounts` WHERE `id` = OLD.`postfixAccountKey`;
        
        DELETE FROM `vokesscp`.`postfix_alias_destinations` WHERE `type` = 'mailbox' AND `destination` = `mailbox`;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_DELETE_postfix_mailboxes`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_DELETE_postfix_mailboxes` AFTER DELETE ON `vokesscp`.`postfix_mailboxes`
    FOR EACH ROW BEGIN
        DECLARE `accountKey` SMALLINT(5) UNSIGNED;
	  DECLARE `accountExists` ENUM('false', 'true') DEFAULT 'false';
        
        SELECT `accountKey` INTO `accountKey` FROM `vokesscp`.`postfix_accounts` WHERE `id` = OLD.`postfixAccountKey`;
        SELECT IF(COUNT(`id`) > 0, 'true', 'false') INTO `accountExists` FROM `vokesscp`.`accounts` WHERE `id` = `accountKey` AND `deletionInProgress` = 'false';
        
        IF `accountExists` = 'true' THEN
            CALL `vokesscp`.`job_createMailbox` (
                `accountKey`,
                OLD.`id`,
                OLD.`homedir`,
                OLD.`maildir`
            );
        END IF;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`postfix_aliases` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `postfixDomainKey`                      SMALLINT(5) UNSIGNED NOT NULL,
    `alias`                                 VARCHAR(75) BINARY NOT NULL,
    `sendOnly`                              ENUM('false', 'true') NOT NULL DEFAULT 'false',
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_postfixDomainKey_alias` (`postfixDomainKey`, `alias`),
    CONSTRAINT `fk_postfixAliases_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_postfixAliases_postfixDomainKey` FOREIGN KEY (`postfixDomainKey`)
        REFERENCES `vokesscp`.`postfix_domains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Aliases for the mailboxes ("eMail addresses")' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_DELETE_postfix_aliases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_DELETE_postfix_aliases` BEFORE DELETE ON `vokesscp`.`postfix_aliases`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`postfix_alias_destinations` WHERE `postfixAliasKey` = OLD.`id`;
        DELETE FROM `vokesscp`.`yaa_autoresponders` WHERE `accountKey` = OLD.`accountKey` AND `postfixAliasKey` = OLD.`id`;
        DELETE FROM `vokesscp`.`spamassassin_userpreferences` WHERE `accountKey` = OLD.`accountKey` AND `postfixAliasKey` = OLD.`id`;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`postfix_alias_destinations` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `postfixAliasKey`                       SMALLINT(5) UNSIGNED NOT NULL,
    `type`                                  ENUM('mailbox', 'forwarder', 'autoresponder', 'file', 'pipe') NOT NULL DEFAULT 'mailbox',
    `destination`                           TEXT BINARY NOT NULL,
    `destinationHash`                       CHAR(40) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_postfixAliasKey_type_destinationHash` (`postfixAliasKey`, `type`, `destinationHash`),
    CONSTRAINT `fk_postfixAliasDestinations_postfixAliasKey` FOREIGN KEY (`postfixAliasKey`)
        REFERENCES `vokesscp`.`postfix_aliases` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Destinations for the aliases' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_INSERT_postfix_alias_destinations`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_INSERT_postfix_alias_destinations` BEFORE INSERT ON `vokesscp`.`postfix_alias_destinations`
    FOR EACH ROW BEGIN
        SET NEW.`destinationHash` = SHA1(NEW.`destination`);
    END;
|
DELIMITER ;


GRANT SELECT ON `vokesscp`.`postfix_domains`                     TO 'vsscp_postfix'@'localhost' IDENTIFIED BY 'ZZZFbF8gJtQLbrbrzm4B';
GRANT SELECT ON `vokesscp`.`postfix_transports`                  TO 'vsscp_postfix'@'localhost' IDENTIFIED BY 'ZZZFbF8gJtQLbrbrzm4B';
GRANT SELECT ON `vokesscp`.`postfix_mailboxes`                   TO 'vsscp_postfix'@'localhost' IDENTIFIED BY 'ZZZFbF8gJtQLbrbrzm4B';
GRANT SELECT ON `vokesscp`.`postfix_aliases`                     TO 'vsscp_postfix'@'localhost' IDENTIFIED BY 'ZZZFbF8gJtQLbrbrzm4B';
GRANT SELECT ON `vokesscp`.`postfix_alias_destinations`          TO 'vsscp_postfix'@'localhost' IDENTIFIED BY 'ZZZFbF8gJtQLbrbrzm4B';
FLUSH PRIVILEGES;

