-- IGNORE-ERROR
ALTER TABLE ORDER2  DROP FOREIGN KEY FK_ORDER_ACCOUNT1;

DROP TABLE IF EXISTS DBSCHEMA;
DROP TABLE IF EXISTS ACCOUNT;
DROP TABLE IF EXISTS ORDER2;

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', '1');

CREATE TABLE ACCOUNT (
    ID BIGINT NOT NULL,
    LUPDATE BIGINT NOT NULL,
    STATUS SMALLINT NOT NULL,
    JWK_SHA256 CHAR(43) NOT NULL,
    DATA VARCHAR(2000) NOT NULL,
    CONSTRAINT PK_ACCOUNT PRIMARY KEY (ID)
);

CREATE TABLE ORDER2 (
    ID BIGINT NOT NULL,
    LUPDATE BIGINT NOT NULL,
    ACCOUNT_ID BIGINT NOT NULL,
    STATUS SMALLINT NOT NULL,
    EXPIRES BIGINT NOT NULL,
    CERT_NAFTER BIGINT NULL,
    CERT_SHA256 CHAR(43) NULL,
    AUTHZS VARCHAR(2000) NOT NULL,
    CERTREQ_META VARCHAR(200) NULL,
    CSR VARCHAR(2000) NULL,
    CERT VARCHAR(3000) NULL,
    CONSTRAINT PK_ORDER2 PRIMARY KEY (ID)
);

-- changeset xipki:2
ALTER TABLE ORDER2 ADD CONSTRAINT FK_ORDER_ACCOUNT1 FOREIGN KEY (ACCOUNT_ID) REFERENCES ACCOUNT (ID)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

