# -----------------------------------------------------------------------
# SECURITY_PRINCIPAL
# -----------------------------------------------------------------------
drop table if exists SECURITY_PRINCIPAL;

CREATE TABLE SECURITY_PRINCIPAL
(
    PRINCIPAL_ID MEDIUMINT NOT NULL,
    CLASSNAME VARCHAR(254) NOT NULL,
    IS_MAPPING_ONLY INTEGER NOT NULL,
    IS_ENABLED INTEGER NOT NULL,
    FULL_PATH VARCHAR(254) NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    MODIFIED_DATE TIMESTAMP NOT NULL,
    PRIMARY KEY(PRINCIPAL_ID),
    UNIQUE (FULL_PATH));

# -----------------------------------------------------------------------
# SECURITY_PERMISSION
# -----------------------------------------------------------------------
drop table if exists SECURITY_PERMISSION;

CREATE TABLE SECURITY_PERMISSION
(
    PERMISSION_ID MEDIUMINT NOT NULL,
    CLASSNAME VARCHAR(254) NOT NULL,
    NAME VARCHAR(254) NOT NULL,
    ACTIONS VARCHAR(254) NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    MODIFIED_DATE TIMESTAMP NOT NULL,
    PRIMARY KEY(PERMISSION_ID));

# -----------------------------------------------------------------------
# PRINCIPAL_PERMISSION
# -----------------------------------------------------------------------
drop table if exists PRINCIPAL_PERMISSION;

CREATE TABLE PRINCIPAL_PERMISSION
(
    PRINCIPAL_ID MEDIUMINT NOT NULL,
    PERMISSION_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
    FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SECURITY_CREDENTIAL
# -----------------------------------------------------------------------
drop table if exists SECURITY_CREDENTIAL;

CREATE TABLE SECURITY_CREDENTIAL
(
    CREDENTIAL_ID MEDIUMINT NOT NULL,
    PRINCIPAL_ID MEDIUMINT NOT NULL,
    COLUMN_VALUE VARCHAR(254) NOT NULL,
    TYPE SMALLINT NOT NULL,
    CLASSNAME VARCHAR(254),
    UPDATE_REQUIRED INTEGER NOT NULL,
    IS_ENCODED INTEGER NOT NULL,
    IS_ENABLED INTEGER NOT NULL,
    AUTH_FAILURES SMALLINT NOT NULL,
    IS_EXPIRED INTEGER NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    MODIFIED_DATE TIMESTAMP NOT NULL,
    PREV_AUTH_DATE TIMESTAMP,
    LAST_AUTH_DATE TIMESTAMP,
    EXPIRATION_DATE DATETIME,
    PRIMARY KEY(CREDENTIAL_ID),
    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SSO_SITE
# -----------------------------------------------------------------------
drop table if exists SSO_SITE;

CREATE TABLE SSO_SITE
(
    SITE_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(254) NOT NULL,
    URL VARCHAR(254) NOT NULL,
    ALLOW_USER_SET INTEGER default 0,
    REQUIRES_CERTIFICATE INTEGER default 0,
    CHALLENGE_RESPONSE_AUTH INTEGER default 0,
    FORM_AUTH INTEGER default 0,
    FORM_USER_FIELD VARCHAR(128),
    FORM_PWD_FIELD VARCHAR(128),
    REALM VARCHAR(128),
    PRIMARY KEY(SITE_ID),
    UNIQUE (URL));

# -----------------------------------------------------------------------
# SSO_COOKIE
# -----------------------------------------------------------------------
drop table if exists SSO_COOKIE;

CREATE TABLE SSO_COOKIE
(
    COOKIE_ID MEDIUMINT NOT NULL,
    COOKIE VARCHAR(1024) NOT NULL,
    CREATE_DATE TIMESTAMP NOT NULL,
    PRIMARY KEY(COOKIE_ID));

# -----------------------------------------------------------------------
# SSO_SITE_TO_PRINCIPALS
# -----------------------------------------------------------------------
drop table if exists SSO_SITE_TO_PRINCIPALS;

CREATE TABLE SSO_SITE_TO_PRINCIPALS
(
    SITE_ID MEDIUMINT NOT NULL,
    PRINCIPAL_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(SITE_ID,PRINCIPAL_ID),
    FOREIGN KEY (SITE_ID) REFERENCES SSO_SITE (SITE_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SSO_PRINCIPAL_TO_REMOTE
# -----------------------------------------------------------------------
drop table if exists SSO_PRINCIPAL_TO_REMOTE;

CREATE TABLE SSO_PRINCIPAL_TO_REMOTE
(
    PRINCIPAL_ID MEDIUMINT NOT NULL,
    REMOTE_PRINCIPAL_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(PRINCIPAL_ID,REMOTE_PRINCIPAL_ID),
    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SSO_SITE_TO_REMOTE
# -----------------------------------------------------------------------
drop table if exists SSO_SITE_TO_REMOTE;

CREATE TABLE SSO_SITE_TO_REMOTE
(
    SITE_ID MEDIUMINT NOT NULL,
    PRINCIPAL_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(SITE_ID,PRINCIPAL_ID),
    FOREIGN KEY (SITE_ID) REFERENCES SSO_SITE (SITE_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SSO_COOKIE_TO_REMOTE
# -----------------------------------------------------------------------
drop table if exists SSO_COOKIE_TO_REMOTE;

CREATE TABLE SSO_COOKIE_TO_REMOTE
(
    COOKIE_ID MEDIUMINT NOT NULL,
    REMOTE_PRINCIPAL_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(COOKIE_ID,REMOTE_PRINCIPAL_ID),
    FOREIGN KEY (COOKIE_ID) REFERENCES SSO_COOKIE (COOKIE_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SECURITY_USER_ROLE
# -----------------------------------------------------------------------
drop table if exists SECURITY_USER_ROLE;

CREATE TABLE SECURITY_USER_ROLE
(
    USER_ID MEDIUMINT NOT NULL,
    ROLE_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(USER_ID,ROLE_ID),
    FOREIGN KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SECURITY_USER_GROUP
# -----------------------------------------------------------------------
drop table if exists SECURITY_USER_GROUP;

CREATE TABLE SECURITY_USER_GROUP
(
    USER_ID MEDIUMINT NOT NULL,
    GROUP_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(USER_ID,GROUP_ID),
    FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );

# -----------------------------------------------------------------------
# SECURITY_GROUP_ROLE
# -----------------------------------------------------------------------
drop table if exists SECURITY_GROUP_ROLE;

CREATE TABLE SECURITY_GROUP_ROLE
(
    GROUP_ID MEDIUMINT NOT NULL,
    ROLE_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(GROUP_ID,ROLE_ID),
    FOREIGN KEY (GROUP_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
        ON DELETE CASCADE 
  );