SET NAMES 'utf8' COLLATE 'utf8_bin';
USE `vokesscp`;


##############################################################################################################################################################################################
# Lets Encrypt SSL                                                                                                                                                                           #
##############################################################################################################################################################################################


CREATE TABLE IF NOT EXISTS `vokesscp`.`ssl_accounts` (
    `id`                                    SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountKey`                            SMALLINT(5) UNSIGNED NOT NULL,
    `termsOfServiceAgreed`                  ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `letsEncryptAccountId`                  VARCHAR(25) BINARY DEFAULT NULL,
    `emailAddress`                          VARCHAR(250) BINARY NOT NULL,
    `privateKey`                            TEXT BINARY DEFAULT NULL,
    `privateKeyType`                        ENUM('RSA-256', 'RSA-512', 'RSA-1024', 'RSA-2048', 'RSA-4096', 'EC-P-256', 'EC-P-384', 'EC-P521') NOT NULL DEFAULT 'RSA-2048',
    `active`                                ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `creationInProgress`                    ENUM('false', 'true') NOT NULL DEFAULT 'false',
    `updateInProgress`                      ENUM('false', 'true') NOT NULL DEFAULT 'false',
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_accountKey` (`accountKey`),
    UNIQUE INDEX `uidx_emailAddress` (`emailAddress`),
    CONSTRAINT `fk_sslAccounts_accountKey` FOREIGN KEY (`accountKey`)
        REFERENCES `vokesscp`.`accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Let\'s Encrypt accounts' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`ssl_certificates` (
    `id`                                    MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `sslAccountKey`                         SMALLINT(5) UNSIGNED NOT NULL,
    `sslCertificatePath`                    LONGTEXT BINARY DEFAULT NULL,                       # Path to the certificate file (should be in the user account)
    `sslKeyfilePath`                        LONGTEXT BINARY DEFAULT NULL,                       # Path to the key file (should be in the user account)
    PRIMARY KEY `pidx_id` (`id`),
    CONSTRAINT `fk_sslAccountCertificates_sslAccountKey` FOREIGN KEY (`sslAccountKey`)
        REFERENCES `vokesscp`.`ssl_accounts` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Let\'s Encrypt ssl certificates' CHARACTER SET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `vokesscp`.`ssl_certificate_domains` (
    `id`                                    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `sslCertificateKey`                     MEDIUMINT(8) UNSIGNED NOT NULL,
    `subdomainKey`                          SMALLINT(5) UNSIGNED NOT NULL,
    `fullDomain`                            VARCHAR(255) BINARY NOT NULL,
    PRIMARY KEY `pidx_id` (`id`),
    UNIQUE INDEX `uidx_sslCertificateKey_subdomainKey` (`sslCertificateKey`, `subdomainKey`),
    UNIQUE INDEX `uidx_sslCertificateKey_fullDomain` (`sslCertificateKey`, `fullDomain`),
    CONSTRAINT `fk_sslAccountCertificateDomains_sslCertificateKey` FOREIGN KEY (`sslCertificateKey`)
        REFERENCES `vokesscp`.`ssl_certificates` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_sslAccountCertificateDomains_subdomainKey` FOREIGN KEY (`subdomainKey`)
        REFERENCES `vokesscp`.`subdomains` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_sslAccountCertificateDomains_fullDomain` FOREIGN KEY (`fullDomain`)
        REFERENCES `vokesscp`.`subdomains` (`fullDomain`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Let\'s Encrypt ssl certificate domain assignments' CHARACTER SET=utf8 COLLATE=utf8_bin;



# ssl_domains
# ssl_certificates
# ssl_accounts
# ssl_keys

