-- IGNORE-ERROR
ALTER TABLE ISSUER DROP CONSTRAINT FK_ISSUER_CRL1;
-- IGNORE-ERROR
ALTER TABLE CERT DROP CONSTRAINT FK_CERT_ISSUER1;
-- IGNORE-ERROR
ALTER TABLE CERT DROP CONSTRAINT FK_CERT_CRL1;

DROP TABLE IF EXISTS DBSCHEMA;
DROP TABLE IF EXISTS ISSUER;
DROP TABLE IF EXISTS CRL_INFO;
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)
)
COMMENT='database schema information';

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

CREATE TABLE ISSUER (
    ID SMALLINT NOT NULL,
    SUBJECT VARCHAR(350) NOT NULL,
    NBEFORE BIGINT NOT NULL COMMENT 'notBefore of certificate, seconds since January 1, 1970, 00:00:00 GMT',
    NAFTER BIGINT NOT NULL COMMENT 'notAfter of certificate, seconds since January 1, 1970, 00:00:00 GMT',
    S1C CHAR(28) NOT NULL COMMENT 'base64 enoded SHA1 sum of the certificate',
    REV_INFO VARCHAR(200) NULL COMMENT 'CA revocation information',
    CERT VARCHAR(6000) NOT NULL,
    CRL_ID INT NULL COMMENT 'CRL ID, only present for entry imported from CRL, and only if exactly one CRL is available for this CA',
    CONSTRAINT PK_ISSUER PRIMARY KEY (ID)
);

CREATE TABLE CRL_INFO (
    ID INT NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    INFO VARCHAR(1000) NOT NULL COMMENT 'CRL information',
    CONSTRAINT PK_CRL_INFO PRIMARY KEY (ID)
);

CREATE TABLE CERT (
    ID BIGINT NOT NULL,
    IID SMALLINT NOT NULL COMMENT 'issuer id',
    SN VARCHAR(40) NOT NULL COMMENT 'serial number',
    CRL_ID INT NULL COMMENT 'CRL ID, only present for entry imported from CRL',
    LUPDATE BIGINT NOT NULL COMMENT 'last update of the this database entry, seconds since January 1, 1970, 00:00:00 GMT',
    NBEFORE BIGINT NULL COMMENT 'notBefore of certificate, seconds since January 1, 1970, 00:00:00 GMT',
    NAFTER BIGINT NULL COMMENT 'notAfter of certificate, 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',
    HASH CHAR(86) NULL COMMENT 'base64 encoded hash value of the DER encoded certificate. Algorithm is defined by CERTHASH_ALGO in table DBSchema',
    SUBJECT VARCHAR(350) NULL COMMENT 'subject of the certificate',
    CONSTRAINT PK_CERT PRIMARY KEY (ID)
)
COMMENT='certificate information';

ALTER TABLE CERT ADD CONSTRAINT CONST_ISSUER_SN UNIQUE (IID, SN);

-- changeset xipki:2
ALTER TABLE CERT ADD CONSTRAINT FK_CERT_ISSUER1
    FOREIGN KEY (IID) REFERENCES ISSUER (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE CERT ADD CONSTRAINT FK_CERT_CRL1
    FOREIGN KEY (CRL_ID) REFERENCES CRL_INFO (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE ISSUER ADD CONSTRAINT FK_ISSUER_CRL1
    FOREIGN KEY (CRL_ID) REFERENCES CRL_INFO (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

