USE `vokesscp`;


##############################################################################################################################################################################################
# Apache                                                                                                                                                                                     #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`apache_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,
    `subdomain`                             VARCHAR(255) BINARY NOT NULL,
    `domain`                                VARCHAR(255) BINARY NOT NULL,
    `fullDomain`                            VARCHAR(255) BINARY NOT NULL,
    `type`                                  ENUM('virtualhost', 'redirect') NOT NULL DEFAULT 'virtualhost',
    `redirectTarget`                        VARCHAR(250) BINARY DEFAULT NULL,
    `redirectCode`                          ENUM('301', '302', '303') NOT NULL DEFAULT '301',
    `phpEnabled`                            ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `perlEnabled`                           ENUM('false', 'true') NOT NULL DEFAULT 'false',
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_sslEnabled_subdomainKey` (`sslEnabled`, `subdomainKey`),
    UNIQUE INDEX `uidx_sslEnabled_fullDomain` (`sslEnabled`, `fullDomain`),
    CONSTRAINT `fk_apacheVirtualhosts_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_apacheVirtualhosts_customerIpToAccountKey` FOREIGN KEY (`customerIpToAccountKey`)
        REFERENCES `vokesscp`.`customer_ips_to_accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_apacheVirtualhosts_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_apacheVirtualhosts_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;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_INSERT_apache_virtualhosts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_INSERT_apache_virtualhosts` BEFORE INSERT ON `vokesscp`.`apache_virtualhosts`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`apache_virtualhost_aliases` WHERE `accountKey` = NEW.`accountKey` AND `sslEnabled` = NEW.`sslEnabled` AND `subdomainKey` = NEW.`subdomainKey`;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_INSERT_apache_virtualhosts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_INSERT_apache_virtualhosts` AFTER INSERT ON `vokesscp`.`apache_virtualhosts`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_createVhost` (
            NEW.`accountKey`,
            NEW.`id`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_UPDATE_apache_virtualhosts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_UPDATE_apache_virtualhosts` BEFORE UPDATE ON `vokesscp`.`apache_virtualhosts`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.`accountKey` AND `sslEnabled` = OLD.`sslEnabled` AND `subdomainKey` = OLD.`subdomainKey`;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_UPDATE_apache_virtualhosts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_UPDATE_apache_virtualhosts` AFTER UPDATE ON `vokesscp`.`apache_virtualhosts`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_updateVhost` (
            OLD.`accountKey`,
            OLD.`id`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_DELETE_apache_virtualhosts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_DELETE_apache_virtualhosts` BEFORE DELETE ON `vokesscp`.`apache_virtualhosts`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`apache_virtualhost_aliases` WHERE `accountKey` = OLD.`accountKey` AND `apacheVirtualHostKey` = OLD.`id`;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_DELETE_apache_virtualhosts`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_DELETE_apache_virtualhosts` AFTER DELETE ON `vokesscp`.`apache_virtualhosts`
    FOR EACH ROW BEGIN
	    DECLARE `accountExists` ENUM('false', 'true') DEFAULT 'false';
        DECLARE `accountUsername` VARCHAR(25) BINARY;
        DECLARE `ipAddress` VARCHAR(39) BINARY;
        
        SELECT IF(COUNT(`id`) > 0, 'true', 'false') INTO `accountExists` FROM `vokesscp`.`accounts` WHERE `id` = OLD.`accountKey` AND `deletionInProgress` = 'false';
        
        IF `accountExists` = 'true' THEN
            SELECT `username` INTO `accountUsername` FROM `vokesscp`.`accounts` WHERE `id` = OLD.`accountKey`;
            SELECT
                `i`.`ip` INTO `ipAddress`
                FROM `vokesscp`.`ips` AS `i`
                INNER JOIN `vokesscp`.`ips_to_customers` AS `itc` ON `i`.`id` = `itc`.`ipKey`
                INNER JOIN `vokesscp`.`customer_ips_to_accounts` AS `cita` ON `itc`.`id` = `cita`.`ipToCustomerKey`
                WHERE `cita`.`id` = OLD.`customerIpToAccountKey`;
	        
            CALL `vokesscp`.`job_deleteVhost` (
                OLD.`accountKey`,
                `accountUsername`,
                OLD.`id`,
                OLD.`sslEnabled`,
                OLD.`fullDomain`,
                `ipAddress`
            );
        END IF;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`apache_virtualhost_aliases` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `sslEnabled`                            ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `apacheVirtualhostKey`                  SMALLINT(5) UNSIGNED NOT NULL,                        # Aliased VirtualHost
    `subdomainKey`                          SMALLINT(5) UNSIGNED NOT NULL,                        # Alias domain
    `subdomain`                             VARCHAR(255) BINARY NOT NULL,
    `domain`                                VARCHAR(255) BINARY NOT NULL,
    `fullDomain`                            VARCHAR(255) BINARY NOT NULL,                         # Alias domain
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_sslEnabled_subdomainKey` (`sslEnabled`, `subdomainKey`),
    UNIQUE INDEX `uidx_sslEnabled_fullDomain` (`sslEnabled`, `fullDomain`),
    CONSTRAINT `fk_apacheVirtualhostAliases_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_apacheVirtualhostAliases_apacheVirtualhostKey` FOREIGN KEY (`apacheVirtualhostKey`)
        REFERENCES `vokesscp`.`apache_virtualhosts` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_apacheVirtualhostAliases_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_apacheVirtualhostAliases_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Apache virtual host aliases' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_INSERT_apache_virtualhost_aliases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_INSERT_apache_virtualhost_aliases` BEFORE INSERT ON `vokesscp`.`apache_virtualhost_aliases`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`apache_virtualhosts` WHERE `accountKey` = NEW.`accountKey` AND `sslEnabled` = NEW.`sslEnabled` AND `subdomainKey` = NEW.`subdomainKey`;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_INSERT_apache_virtualhost_aliases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_INSERT_apache_virtualhost_aliases` AFTER INSERT ON `vokesscp`.`apache_virtualhost_aliases`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_updateVhost` (
            NEW.`accountKey`,
            NEW.`apacheVirtualhostKey`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`BEFORE_UPDATE_apache_virtualhost_aliases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`BEFORE_UPDATE_apache_virtualhost_aliases` BEFORE UPDATE ON `vokesscp`.`apache_virtualhost_aliases`
    FOR EACH ROW BEGIN
        DELETE FROM `vokesscp`.`apache_virtualhosts` WHERE `accountKey` = OLD.`accountKey` AND `sslEnabled` = OLD.`sslEnabled` AND `subdomainKey` = OLD.`subdomainKey`;
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_UPDATE_apache_virtualhost_aliases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_UPDATE_apache_virtualhost_aliases` AFTER UPDATE ON `vokesscp`.`apache_virtualhost_aliases`
    FOR EACH ROW BEGIN
        CALL `vokesscp`.`job_updateVhost` (
            OLD.`accountKey`,
            OLD.`apacheVirtualhostKey`
        );
    END;
|
DROP TRIGGER IF EXISTS `vokesscp`.`AFTER_DELETE_apache_virtualhost_aliases`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    TRIGGER `vokesscp`.`AFTER_DELETE_apache_virtualhost_aliases` AFTER DELETE ON `vokesscp`.`apache_virtualhost_aliases`
    FOR EACH ROW BEGIN
	    DECLARE `accountExists` ENUM('false', 'true') DEFAULT 'false';
	    
	    SELECT IF(COUNT(`id`) > 0, 'true', 'false') INTO `accountExists` FROM `vokesscp`.`accounts` WHERE `id` = OLD.`accountKey` AND `deletionInProgress` = 'false';
	    
	    IF `accountExists` = 'true' THEN
            CALL `vokesscp`.`job_updateVhost` (
                OLD.`accountKey`,
                OLD.`apacheVirtualhostKey`
            );
        END IF;
    END;
|
DELIMITER ;


CREATE TABLE IF NOT EXISTS `vokesscp`.`apache_traffic` (
    `id`                                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED DEFAULT NULL,
    `subdomainKey`                          SMALLINT(5) UNSIGNED DEFAULT NULL,
    `subdomain`                             VARCHAR(255) BINARY DEFAULT NULL,
    `domain`                                VARCHAR(255) BINARY DEFAULT NULL,
    `fullDomain`                            VARCHAR(255) BINARY DEFAULT NULL,
    `date`                                  DATETIME NOT NULL,
    `bytesIn`                               BIGINT(20) UNSIGNED NOT NULL,
    `bytesOut`                              BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE KEY `uidx_fullDomain_date` (`fullDomain`, `date`),
    CONSTRAINT `fk_apacheTraffic_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_apacheTraffic_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_apacheTraffic_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='Apache traffic' CHARACTER SET=utf8 COLLATE=utf8_bin;


DELIMITER |
DROP EVENT IF EXISTS `vokesscp`.`EVENT_apacheTraffic`
|
CREATE
    DEFINER = 'vsscp'@'localhost'
    EVENT `vokesscp`.`EVENT_apacheTraffic`
    ON SCHEDULE
        EVERY 1 DAY
        STARTS DATE_FORMAT(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 DAY), '%Y-%m-%d 00:06:00')
    DO
        BEGIN
            DECLARE `detailedDays` TINYINT(2) DEFAULT 3;
            
            # Copy daily traffic
            INSERT INTO `vokesscp`.`account_traffic` (`accountKey`, `accountId`, `date`, `webIn`, `webOut`) SELECT `accountKey`, IF(`accountKey` IS NULL, 0, `accountKey`),
                DATE_FORMAT(`date`, '%Y-%m-%d'), (SELECT SUM(`inner1`.`quantity`) FROM `vokesscp`.`apache_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 `webTrafficIn`, (SELECT SUM(`inner2`.`quantity`) FROM
                `vokesscp`.`apache_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 `webTrafficOut` FROM
                `vokesscp`.`apache_traffic` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = DATE_FORMAT(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 DAY), '%Y-%m-%d') ON DUPLICATE KEY UPDATE
                `webIn` = VALUES(`webIn`) + `webIn`, `webOut` = VALUES(`webOut`) + `webOut`;
                
            # Delete detailed traffic that is outdated
            SELECT `value` INTO `detailedDays` FROM `vokesscp`.`configuration` WHERE `key` = 'trafficStatisticApacheDetailDays';
            DELETE FROM `vokesscp`.`apache_traffic` WHERE DATE_FORMAT(`date`, '%Y-%m-%d') < DATE_FORMAT(DATE_SUB(UTC_TIMESTAMP(), INTERVAL `detailedDays` DAY), '%Y-%m-%d');
            END
|
DELIMITER ;


GRANT INSERT ON `vokesscp`.`apache_traffic` TO 'vsscp_apache'@'localhost' IDENTIFIED BY '3QJ2gWaeuUFCvHfwwyjR';

