DROP TABLE IF EXISTS DBSCHEMA;
DROP TABLE IF EXISTS KEYSPEC;
DROP TABLE IF EXISTS KEYPOOL;

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

COMMENT ON TABLE DBSCHEMA IS 'database schema information';

INSERT INTO DBSCHEMA (NAME, VALUE2) VALUES ('VENDOR', 'XIPKI');
INSERT INTO DBSCHEMA (NAME, VALUE2) VALUES ('VERSION', '7');

CREATE TABLE KEYSPEC (
    ID NUMBER(5) NOT NULL,
    KEYSPEC VARCHAR2(100) NOT NULL,
    CONSTRAINT PK_KEYSPEC PRIMARY KEY (ID)
);

CREATE TABLE KEYPOOL (
    ID NUMBER(38, 0) NOT NULL,
    SHARD_ID NUMBER(5) NOT NULL,
    KID NUMBER(5) NOT NULL,
    ENC_ALG NUMBER(5) NOT NULL,
    ENC_META VARCHAR2(100),
    DATA VARCHAR2(3300) NOT NULL,
    CONSTRAINT PK_KEYPOOL PRIMARY KEY (ID)
);

COMMENT ON COLUMN KEYPOOL.SHARD_ID IS 'Shard id, match the shard id of the CA software instance';
COMMENT ON COLUMN KEYPOOL.KID IS 'KEYSPEC ID';
COMMENT ON COLUMN KEYPOOL.ENC_ALG IS 'Encryption algorithm: 1 for AES128/GCM, 2 for AES192/GCM, 3 for AES256/GCM';
COMMENT ON COLUMN KEYPOOL.ENC_META IS 'For ENC_ALG 1, 2, 3: base64(nonce)';
COMMENT ON COLUMN KEYPOOL.DATA IS 'base64(encrypted PrivateKeyInfo)';

