USE `vokesscp`;


##############################################################################################################################################################################################
# ProFTPd                                                                                                                                                                                    #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`proftpd_virtualhosts` (
    `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_proftpdVirtualhosts_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_proftpdVirtualhosts_customerIpToAccountKey` FOREIGN KEY (`customerIpToAccountKey`)
        REFERENCES `vokesscp`.`customer_ips_to_accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_proftpdVirtualhosts_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_proftpdVirtualhosts_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Apache virtual hosts' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`proftpd_users` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `proftpdVirtualhostKey`                 SMALLINT(5) UNSIGNED NOT NULL,
    `userId`                                SMALLINT(5) UNSIGNED DEFAULT NULL,                    # Linux UID (from account!)
    `groupId`                               SMALLINT(5) UNSIGNED DEFAULT NULL,                    # Linux GID (from account!)
    `username`                              VARCHAR(60) BINARY NOT NULL,
    `password`                              CHAR(46) BINARY NOT NULL,
    `homeDirectory`                         TEXT BINARY NOT NULL,
    `shell`                                 VARCHAR(250) BINARY NOT NULL DEFAULT '/bin/false',
    `loginCounter`                          INT(10) UNSIGNED NOT NULL DEFAULT 0,
    `lastLoginDate`                         DATETIME DEFAULT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_username` (`username`),
    CONSTRAINT `fk_proftpdUsers_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_proftpdUsers_proftpdVirtualhostKey` FOREIGN KEY (`proftpdVirtualhostKey`)
        REFERENCES `vokesscp`.`proftpd_virtualhosts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='FTP users' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`proftpd_groups` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `proftpdVirtualhostKey`                 SMALLINT(5) UNSIGNED NOT NULL,
    `groupId`                               SMALLINT(5) UNSIGNED DEFAULT NULL,                    # Linux GID (from account!)
    `groupname`                             VARCHAR(60) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_groupname` (`groupname`),
    CONSTRAINT `fk_proftpdGroups_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_proftpdGroups_proftpdVirtualhostKey` FOREIGN KEY (`proftpdVirtualhostKey`)
        REFERENCES `vokesscp`.`proftpd_virtualhosts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='FTP groups' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`proftpd_users2groups` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `groupKey`                              SMALLINT(5) UNSIGNED NOT NULL,
    `userKey`                               SMALLINT(5) UNSIGNED NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_groupKey_userKey` (`groupKey`, `userKey`),
    CONSTRAINT `fk_proftpdUsers2Groups_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_proftpdUsers2Groups_groupKey` FOREIGN KEY (`groupKey`)
        REFERENCES `vokesscp`.`proftpd_groups` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_proftpdUsers2Groups_userKey` FOREIGN KEY (`userKey`)
        REFERENCES `vokesscp`.`proftpd_users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='FTP users to groups' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`proftpd_traffic` (
    `id`                                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED DEFAULT NULL,
    `proftpdVirtualhostKey`                 SMALLINT(5) UNSIGNED DEFAULT NULL,
    `username`                              VARCHAR(60) BINARY DEFAULT NULL,
    `date`                                  DATETIME NOT NULL,
    `direction`                             ENUM('in', 'out') NOT NULL DEFAULT 'in',
    `command`                               VARCHAR(25) NOT NULL DEFAULT '',
    `quantity`                              BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_accountKey_username_date_direction_command` (`accountKey`, `username`, `date`, `direction`, `command`),
    CONSTRAINT `fk_proftpdTraffic_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_proftpdTraffic_proftpdVirtualhostKey` FOREIGN KEY (`proftpdVirtualhostKey`)
        REFERENCES `vokesscp`.`proftpd_virtualhosts` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='FTP traffic' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP EVENT IF EXISTS `vokesscp`.`EVENT_proftpdTrafficPushToAccount`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    EVENT `vokesscp`.`EVENT_proftpdTrafficPushToAccount`
    ON SCHEDULE
        EVERY 1 DAY
        STARTS DATE_FORMAT(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 DAY), '%Y-%m-%d 00:09:00')
    DO
        BEGIN
            DECLARE `detailedDays` TINYINT(2) DEFAULT 3;
            
            # Copy daily traffic
            INSERT INTO `vokesscp`.`account_traffic` (`accountKey`, `accountId`, `date`, `ftpIn`, `ftpOut`) SELECT `accountKey`, IF(`accountKey` IS NULL, 0, `accountKey`),
                DATE_FORMAT(`date`, '%Y-%m-%d'), (SELECT SUM(`inner1`.`quantity`) FROM `vokesscp`.`proftpd_traffic` AS `inner1` WHERE
                DATE_FORMAT(`date`, '%Y-%m-%d') = DATE_FORMAT(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 DAY), '%Y-%m-%d') AND `direction` = 'in') AS `ftpTrafficIn`, (SELECT SUM(`inner2`.`quantity`) FROM
                `vokesscp`.`proftpd_traffic` AS `inner2` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = DATE_FORMAT(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 DAY), '%Y-%m-%d') AND `direction` = 'out') AS `ftpTrafficOut` FROM
                `vokesscp`.`proftpd_traffic` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = DATE_FORMAT(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 DAY), '%Y-%m-%d') ON DUPLICATE KEY UPDATE
                `ftpIn` = VALUES(`ftpIn`) + `ftpIn`, `ftpOut` = VALUES(`ftpOut`) + `ftpOut`;
                
            # Delete detailed traffic that is outdated
            SELECT `value` INTO `detailedDays` FROM `vokesscp`.`configuration` WHERE `key` = 'trafficStatisticFTPDetailDays';
            DELETE FROM `vokesscp`.`proftpd_traffic` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') < DATE_FORMAT(DATE_SUB(UTC_TIMESTAMP(), INTERVAL `detailedDays` DAY), '%Y-%m-%d');
        END
|
DELIMITER ;


GRANT SELECT ON `vokesscp`.`proftpd_users`                                   TO 'vsscp_proftpd'@'localhost' IDENTIFIED BY 'SNysW4HMvxyfDMBaD38H';
GRANT SELECT ON `vokesscp`.`proftpd_groups`                                  TO 'vsscp_proftpd'@'localhost' IDENTIFIED BY 'SNysW4HMvxyfDMBaD38H';
GRANT SELECT ON `vokesscp`.`proftpd_users2groups`                            TO 'vsscp_proftpd'@'localhost' IDENTIFIED BY 'SNysW4HMvxyfDMBaD38H';
GRANT INSERT ON `vokesscp`.`proftpd_traffic`                                 TO 'vsscp_proftpd'@'localhost' IDENTIFIED BY 'SNysW4HMvxyfDMBaD38H';

