USE `vokesscp`;


##############################################################################################################################################################################################
# Spamassassin                                                                                                                                                                               #
##############################################################################################################################################################################################


# userprefs:
#     Einstellungen:
#           username=$GLOBAL:             Global settings
#           username=%<domain>:           Settings for a domain
#           username=<user>@<domain>:     Settings for a specified eMail address
#
# whitelist_from
# unwhitelist_from      override a (global) whitelist entry
# blacklist_from
# unblacklist_from      override a (global) blacklist entry
CREATE TABLE IF NOT EXISTS `vokesscp`.`spamassassin_userpreferences` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED DEFAULT NULL,
    `postfixDomainKey`                      SMALLINT(5) UNSIGNED DEFAULT NULL,
    `postfixAliasKey`                       SMALLINT(5) UNSIGNED DEFAULT NULL,
    `username`                              VARCHAR(250) BINARY NOT NULL,                         # eMail address, %<domain> or $GLOBAL
    `preference`                            VARCHAR(250) BINARY NOT NULL,                         # setting type (key)
    `value`                                 VARCHAR(250) BINARY NOT NULL DEFAULT '',              # setting value
    PRIMARY KEY `pidx_id` (`id`),
    CONSTRAINT `fk_spamassassinUserprefs_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_spamassassinUserprefs_postfixDomainKey` FOREIGN KEY (`postfixDomainKey`)
        REFERENCES `vokesscp`.`postfix_domains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_spamassassinUserprefs_postfixAliasKey` FOREIGN KEY (`postfixAliasKey`)
        REFERENCES `vokesscp`.`postfix_aliases` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='User preferences' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`spamassassin_autowhitelist` (
    `username`                              VARCHAR(250) BINARY NOT NULL DEFAULT '',              # Recpients email
    `email`                                 VARCHAR(250) BINARY NOT NULL DEFAULT '',              # Senders email
    `ip`                                    VARCHAR(39) BINARY NOT NULL DEFAULT '',               # IPv6: up to 39 chars are possible
    `messageCount`                          BIGINT(20) NOT NULL DEFAULT 0,
    `totalScore`                            FLOAT NOT NULL DEFAULT 0,
    `signedBy`                              VARCHAR(255) BINARY NOT NULL DEFAULT '',
    `lastHit`                               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY `pidx_username_email_signedBy_ip` (`username`, `email`, `signedBy`, `ip`)
) ENGINE=InnoDB COMMENT='AutoWhitelist (updated by SpamAssassin)' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE  IF NOT EXISTS `vokesscp`.`spamassassin_statistics` (
    `day`                                   DATE NOT NULL,
    `username`                              VARCHAR(100) BINARY NOT NULL DEFAULT '',
    `domain`                                VARCHAR(100) BINARY NOT NULL DEFAULT '',
    `spam`                                  BIGINT(20) DEFAULT 0,
    `ham`                                   BIGINT(20) DEFAULT 0,
    PRIMARY KEY `pidx_day_username_domain`  (`day`, `username`, `domain`)
) ENGINE=InnoDB COMMENT='SpamAssassin statistics' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`bayes_expire` (
    `id`                                    BIGINT(20) NOT NULL DEFAULT 0,
    `runtime`                               BIGINT(20) NOT NULL DEFAULT 0,
    INDEX `idx_id` (`id`)
) ENGINE=InnoDB COMMENT='Bayes expiration' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`bayes_global_vars` (
    `variable`                              VARCHAR(50) BINARY NOT NULL DEFAULT '',
    `value`                                 VARCHAR(250) BINARY NOT NULL DEFAULT '',
    PRIMARY KEY `pidx_variable` (`variable`)
) ENGINE=InnoDB COMMENT='Bayes global settings' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`bayes_seen` (
    `id`                                    BIGINT(20) NOT NULL DEFAULT 0,
    `messageId`                             VARCHAR(250) BINARY NOT NULL DEFAULT '',
    `flag`                                  CHAR(1) BINARY NOT NULL DEFAULT '',
    PRIMARY KEY `pidx_id_messageId` (`id`, `messageId`)
) ENGINE=InnoDB COMMENT='Bayes "seen"' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`bayes_token` (
    `id`                                    BIGINT(20) NOT NULL DEFAULT 0,
    `token`                                 CHAR(5) BINARY NOT NULL DEFAULT '',
    `spamCount`                             BIGINT(20) NOT NULL DEFAULT 0,
    `hamCount`                              BIGINT(20) NOT NULL DEFAULT 0,
    `accessTime`                            BIGINT(20) NOT NULL DEFAULT 0,
    PRIMARY KEY `pidx_id_token` (`id`, `token`),
    INDEX `idx_token` (`token`),
    INDEX `idx_id_accessTime` (`id`, `accessTime`)
) ENGINE=InnoDB COMMENT='Bayes tokens' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`bayes_vars` (
    `id`                                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username`                              VARCHAR(250) BINARY NOT NULL DEFAULT '',
    `spamCount`                             BIGINT(20) NOT NULL DEFAULT 0,
    `hamCount`                              BIGINT(20) NOT NULL DEFAULT 0,
    `tokenCount`                            BIGINT(20) NOT NULL DEFAULT 0,
    `lastExpire`                            BIGINT(20) NOT NULL DEFAULT 0,
    `lastAccessTimeDelta`                   BIGINT(20) NOT NULL DEFAULT 0,
    `lastExpireReduce`                      BIGINT(20) NOT NULL DEFAULT 0,
    `oldestTokenAge`                        BIGINT(20) NOT NULL DEFAULT 2147483647,
    `newestTokenAge`                        BIGINT(20) NOT NULL DEFAULT 0,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_username` (`username`)
) ENGINE=InnoDB COMMENT='Bayes vars' CHARACTER SET=utf8 COLLATE=utf8_bin;


GRANT SELECT ON `vokesscp`.`spamassassin_userpreferences`                                TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`spamassassin_autowhitelist`          TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`spamassassin_statistics`             TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`bayes_expire`                        TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`bayes_global_vars`                   TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`bayes_seen`                          TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`bayes_token`                         TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
GRANT INSERT, UPDATE, SELECT, DELETE ON `vokesscp`.`bayes_vars`                          TO 'vsscp_spamass'@'localhost' IDENTIFIED BY 'zsKrus3MQZfsrxTB9wdC';
FLUSH PRIVILEGES;

