-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: ./docs/db/changelog/db.changelog-master.xml
-- Ran at: 8/22/24, 2:09 PM
-- Against: null@offline:oracle
-- Liquibase version: 4.28.0
-- *********************************************************************

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::1::Jan Pesek
-- Create a new table audit_log
CREATE TABLE audit_log (audit_log_id VARCHAR2(36) NOT NULL, application_name VARCHAR2(256) NOT NULL, audit_level VARCHAR2(32) NOT NULL, audit_type VARCHAR2(256), timestamp_created TIMESTAMP DEFAULT sysdate, message CLOB NOT NULL, exception_message CLOB, stack_trace CLOB, param CLOB, calling_class VARCHAR2(256) NOT NULL, thread_name VARCHAR2(256) NOT NULL, version VARCHAR2(256), build_time TIMESTAMP, CONSTRAINT PK_AUDIT_LOG PRIMARY KEY (audit_log_id));

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::2::Jan Pesek
-- Create a new index on audit_log(timestamp_created)
CREATE INDEX audit_log_timestamp ON audit_log(timestamp_created);

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::3::Jan Pesek
-- Create a new index on audit_log(application_name)
CREATE INDEX audit_log_application ON audit_log(application_name);

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::4::Jan Pesek
-- Create a new index on audit_log(audit_level)
CREATE INDEX audit_log_level ON audit_log(audit_level);

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::5::Jan Pesek
-- Create a new index on audit_log(audit_type)
CREATE INDEX audit_log_type ON audit_log(audit_type);

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::6::Jan Pesek
-- Create a new table audit_param
CREATE TABLE audit_param (audit_log_id VARCHAR2(36) NOT NULL, param_key VARCHAR2(256), param_value VARCHAR2(4000), timestamp_created TIMESTAMP DEFAULT sysdate, CONSTRAINT PK_AUDIT_PARAM PRIMARY KEY (audit_log_id));

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::8::Jan Pesek
-- Create a new index on audit_param(timestamp_created)
CREATE INDEX audit_param_timestamp ON audit_param(timestamp_created);

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::9::Jan Pesek
-- Create a new index on audit_log(param_key)
CREATE INDEX audit_param_key ON audit_param(param_key);

-- Changeset liveness-check-proxy/0.1.x/20240412-audit.xml::10::Jan Pesek
-- Create a new index on audit_log(param_value)
CREATE INDEX audit_param_value ON audit_param(param_value);

-- Changeset liveness-check-proxy/0.1.x/20240617-enrollment-table.xml::1::Jan Pesek
-- Create a new table lcp_enrollment
CREATE TABLE lcp_enrollment (id VARCHAR2(36) NOT NULL, user_id VARCHAR2(255) NOT NULL, external_id VARCHAR2(255), timestamp_first_enrolled TIMESTAMP DEFAULT sysdate, timestamp_last_enrolled TIMESTAMP DEFAULT sysdate, CONSTRAINT PK_LCP_ENROLLMENT PRIMARY KEY (id));

-- Changeset liveness-check-proxy/0.1.x/20240821-security.xml::1::Jan Pesek
-- Create a new table lcp_user for spring security
CREATE TABLE lcp_user (username VARCHAR2(50) NOT NULL, "password" VARCHAR2(500) NOT NULL, enabled BOOLEAN NOT NULL, CONSTRAINT PK_LCP_USER PRIMARY KEY (username));

-- Changeset liveness-check-proxy/0.1.x/20240821-security.xml::2::Jan Pesek
-- Create a new table lcp_authority for spring security
CREATE TABLE lcp_authority (username VARCHAR2(50) NOT NULL, authority VARCHAR2(50) NOT NULL, CONSTRAINT lcp_user_username_fk FOREIGN KEY (username) REFERENCES lcp_user(username));

-- Changeset liveness-check-proxy/0.1.x/20240821-security.xml::3::Jan Pesek
-- Create a new unique index on lcp_authority(username, authority)
CREATE UNIQUE INDEX username_authority_idx ON lcp_authority(username, authority);

