
--------------------------------------------------
-- 01/15/2020
-- Author: Tony Chen 
-- Following changes are made to integrate with 
-- Phoenix Bioinformatics. 
--------------------------------------------------

-- Create a new application record for CIPRES Portal REST API app. 
INSERT INTO `applications` (`NAME`, `APP_ID`, `LONGNAME`, `AUTH_TYPE`, `ACTIVE`, `DATE_CREATED`) VALUES 
    ('PORTALREST', 'portal_rest', 'CIPRES PORTAL REST', 'DIRECT', b'1', NOW())
;

-- Properties for PORTALREST app. 
INSERT INTO `application_preferences` (`NAME`, `PREFERENCE`, `VALUE`) VALUES 
    ('PORTALREST', 'aes.algorithm', 'AES/CBC/PKCS5Padding'),
    ('PORTALREST', 'aes.key.size', '256'),
    ('PORTALREST', 'hmac.algorithm', 'HmacSHA512'),
    ('PORTALREST', 'pbkdf2.algorithm', 'PBKDF2WithHmacSHA512'), 
    ('PORTALREST', 'pbkdf2.iterations', '200000')
;
-- --------------------------------------------------

-- Clients/applications authorized to access API endpoints. 
CREATE TABLE `client_api_credentials`
(
    ID                              BIGINT AUTO_INCREMENT, -- PK of the table. 
    CLIENT_NAME                     VARCHAR(255) NOT NULL, 
    CLIENT_IDENTIFIER               VARCHAR(255) NOT NULL, -- Java UUID string. 
    CLIENT_SECRET                   VARCHAR(255) NOT NULL, -- Base64-encoded string. 
    CLIENT_SALT                     VARCHAR(255) NOT NULL, -- Base64-encoded string.
    SIGNING_KEY                     VARCHAR(4096) NOT NULL, -- Key to sign tokens, Base64-encoded string. 
    CREATED_TIME                    DATETIME NOT NULL,
    END_TIME                        DATETIME NULL, 
    ACTIVE_YN                       VARCHAR(1) NOT NULL DEFAULT 'Y', 
    NOTE                            VARCHAR(2000) NULL, 

    CONSTRAINT pk_client_api_credentials PRIMARY KEY (ID),
    CONSTRAINT uq_client_api_credentials_01 UNIQUE (CLIENT_NAME),
    CONSTRAINT uq_client_api_credentials_02 UNIQUE (CLIENT_IDENTIFIER),
    CONSTRAINT ck_client_api_credentials_01 CHECK (ACTIVE_YN = 'Y' OR ACTIVE_YN = 'N')
);

-- Tokens for clients/applications authorized to access API endpoints.
CREATE TABLE `client_api_tokens`
(
    ID                              BIGINT AUTO_INCREMENT,
    CLIENT_ID                       BIGINT NOT NULL, -- FK to client_api_credentials.ID 
    ACCESS_TOKEN                    VARCHAR(2048) NOT NULL,
    CREATED_TIME                    DATETIME NOT NULL,
    NOT_BEFORE                      DATETIME NOT NULL,
    EXPIRES_TIME                    DATETIME NOT NULL, 
    REVOKED_YN                      VARCHAR(1) NOT NULL DEFAULT 'N', 
    NOTE                            VARCHAR(2000) NULL, 

    CONSTRAINT pk_client_api_tokens PRIMARY KEY (ID),

    CONSTRAINT fk_client_api_tokens_01 FOREIGN KEY (CLIENT_ID) 
        REFERENCES `client_api_credentials` (`ID`) 
        ON DELETE NO ACTION     ON UPDATE NO ACTION,

    CONSTRAINT ck_client_api_tokens_01 CHECK (REVOKED_YN = 'Y' OR REVOKED_YN = 'N')
);
-- --------------------------------------------------

CREATE TABLE `version`
(
    `ID`                            BIGINT AUTO_INCREMENT,
    `VERSION`                       VARCHAR(255) NOT NULL,
    `LAST_MODIFIED_TIME`            DATETIME NOT NULL DEFAULT NOW(),

    CONSTRAINT pk_version_01 PRIMARY KEY (`ID`),
    CONSTRAINT uq_version_01 UNIQUE (`VERSION`) 
);
-- --------------------------------------------------

INSERT INTO `version` (`VERSION`, `LAST_MODIFIED_TIME`) VALUES 
    ('2020-01-15', NOW())
;
-- --------------------------------------------------
