-- IGNORE-ERROR
ALTER TABLE CRL  DROP FOREIGN KEY FK_CRL_CA1;
-- IGNORE-ERROR
ALTER TABLE CERT DROP FOREIGN KEY FK_CERT_CA1;
-- IGNORE-ERROR
ALTER TABLE CERT DROP FOREIGN KEY FK_CERT_REQUESTOR1;
-- IGNORE-ERROR
ALTER TABLE CERT DROP FOREIGN KEY FK_CERT_PROFILE1;

DROP TABLE IF EXISTS DBSCHEMA;
DROP TABLE IF EXISTS PROFILE;
DROP TABLE IF EXISTS REQUESTOR;
DROP TABLE IF EXISTS CA;
DROP TABLE IF EXISTS CRL;
DROP TABLE IF EXISTS CERT;

-- changeset xipki:1
CREATE TABLE DBSCHEMA (
    NAME VARCHAR(45) NOT NULL,
    VALUE2 VARCHAR(100) NOT NULL,
    CONSTRAINT PK_DBSCHEMA PRIMARY KEY (NAME)
);

INSERT INTO DBSCHEMA (NAME, VALUE2) VALUES ('VENDOR', 'XIPKI');
INSERT INTO DBSCHEMA (NAME, VALUE2) VALUES ('VERSION', '8');
INSERT INTO DBSCHEMA (NAME, VALUE2) VALUES ('X500NAME_MAXLEN', '350');

CREATE TABLE PROFILE (
    ID SMALLINT NOT NULL,
    NAME VARCHAR(45) NOT NULL COMMENT 'duplication is not permitted',
    CONSTRAINT PK_PROFILE PRIMARY KEY (ID)
);

CREATE TABLE REQUESTOR (
    ID SMALLINT NOT NULL,
    NAME VARCHAR(45) NOT NULL COMMENT 'duplication is not permitted',
    CONSTRAINT PK_REQUESTOR PRIMARY KEY (ID)
);

CREATE TABLE CA (
    ID SMALLINT NOT NULL,
    NAME VARCHAR(45) NOT NULL COMMENT 'duplication is not permitted',
    SUBJECT VARCHAR(350) NOT NULL,
    REV_INFO VARCHAR(200) NULL COMMENT 'CA revocation information',
    CERT VARCHAR(6000) NOT NULL,
    CONSTRAINT PK_CA PRIMARY KEY (ID)
);

-- changeset xipki:2
CREATE TABLE CRL (
    ID INT NOT NULL,
    CA_ID SMALLINT NOT NULL,
    CRL_SCOPE SMALLINT NOT NULL COMMENT 'CRL scope, reserved for future use',
    CRL_NO BIGINT NOT NULL,
    THISUPDATE BIGINT NOT NULL,
    NEXTUPDATE BIGINT NULL,
    DELTACRL SMALLINT NOT NULL,
    BASECRL_NO BIGINT NULL,
    SHA1 CHAR(28) NOT NULL COMMENT 'base64 encoded SHA1 fingerprint of the CRL',
    CRL LONGTEXT NOT NULL,
    CONSTRAINT PK_CRL PRIMARY KEY (ID)
);

ALTER TABLE CRL ADD CONSTRAINT CONST_CA_CRLNO UNIQUE (CA_ID, CRL_NO);

CREATE TABLE CERT (ID BIGINT NOT NULL,
    CA_ID SMALLINT NOT NULL COMMENT 'Issuer (CA) id',
    SN VARCHAR(40) NOT NULL COMMENT 'serial number',
    PID SMALLINT NOT NULL COMMENT 'certificate profile id',
    RID SMALLINT NOT NULL COMMENT 'requestor id',
    FP_S BIGINT NOT NULL COMMENT 'first 8 bytes of the SHA1 sum of the subject',
    FP_SAN BIGINT NULL COMMENT 'first 8 bytes of the SHA1 sum of the extension value of SubjectAltNames',
    FP_RS BIGINT NULL COMMENT 'first 8 bytes of the SHA1 sum of the requested subject',
    LUPDATE BIGINT NOT NULL COMMENT 'last update, seconds since January 1, 1970, 00:00:00 GMT',
    NBEFORE BIGINT NOT NULL COMMENT 'notBefore, seconds since January 1, 1970, 00:00:00 GMT',
    NAFTER BIGINT NOT NULL COMMENT 'notAfter, seconds since January 1, 1970, 00:00:00 GMT',
    REV SMALLINT NOT NULL COMMENT 'whether the certificate is revoked',
    RR SMALLINT NULL COMMENT 'revocation reason',
    RT BIGINT NULL COMMENT 'revocation time, seconds since January 1, 1970, 00:00:00 GMT',
    RIT BIGINT NULL COMMENT 'revocation invalidity time, seconds since January 1, 1970, 00:00:00 GMT',
    EE SMALLINT NOT NULL COMMENT 'whether it is an end entity cert',
    SUBJECT VARCHAR(350) NOT NULL,
    TID VARCHAR(43) NULL COMMENT 'base64 encoded transactionId, maximal 256 bit',
    CRL_SCOPE SMALLINT NOT NULL COMMENT 'CRL scope, reserved for future use',
    SHA1 CHAR(28) NOT NULL COMMENT 'base64 encoded SHA1 fingerprint of the certificate',
    REQ_SUBJECT VARCHAR(350) NULL,
    CERT VARCHAR(6000) NOT NULL COMMENT 'Base64 encoded certificate',
    PRIVATE_KEY VARCHAR(6000) NULL COMMENT 'Base64-encoded encrypted PKCS#8 private key',
    CONSTRAINT PK_CERT PRIMARY KEY (ID)
);

ALTER TABLE CERT ADD CONSTRAINT CONST_CA_SN UNIQUE (CA_ID, SN);
CREATE INDEX IDX_CA_FPS ON CERT(CA_ID, FP_S, FP_SAN);

-- changeset xipki:4
ALTER TABLE CRL ADD CONSTRAINT FK_CRL_CA1 FOREIGN KEY (CA_ID) REFERENCES CA (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE CERT ADD CONSTRAINT FK_CERT_CA1 FOREIGN KEY (CA_ID) REFERENCES CA (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE CERT ADD CONSTRAINT FK_CERT_REQUESTOR1 FOREIGN KEY (RID) REFERENCES REQUESTOR (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE CERT ADD CONSTRAINT FK_CERT_PROFILE1 FOREIGN KEY (PID) REFERENCES PROFILE (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

