-- -----------------------------------------------------------------------------
-- https://docs.spring.io/spring-security/site/docs/5.0.x/reference/html/appendix-schema.html#user-schema
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- org.springframework.security.core.userdetails.User
-- spring security users
-- -----------------------------------------------------------------------------
CREATE TABLE m01te00x00 (
    entered        DATE          NOT NULL,
    enterer        VARCHAR2(4)   NOT NULL,
    amended        DATE,
    amender        VARCHAR2(4),

    id             VARCHAR2(4)   NOT NULL,
    user_code      VARCHAR2(4)   NOT NULL,
    user_name      VARCHAR2(32)  NOT NULL,
    user_mail      VARCHAR2(64)  NOT NULL,
    user_pass      VARCHAR2(64)  NOT NULL,
    full_name      VARCHAR2(64)  NOT NULL,
    is_active      NUMBER(1)     NOT NULL,
    is_locked      NUMBER(1)     NOT NULL,
    is_signin      NUMBER(1)     NOT NULL,
    user_expired   DATE,
    pass_expired   DATE,

    CONSTRAINT m01te00x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01te00x00_ukc_00 UNIQUE      (user_code),
    CONSTRAINT m01te00x00_ukc_01 UNIQUE      (user_name),
    CONSTRAINT m01te00x00_ckc_00 CHECK       (is_active IN (0,1)),
    CONSTRAINT m01te00x00_ckc_01 CHECK       (is_locked IN (0,1)),
    CONSTRAINT m01te00x00_ckc_02 CHECK       (is_signin IN (0,1))
);
COMMENT ON TABLE m01te00x00 IS 'spring security users';
-- -----------------------------------------------------------------------------
-- spring security authorities
-- -----------------------------------------------------------------------------
CREATE TABLE m01ts00x00 (
    entered     DATE             NOT NULL,
    enterer     VARCHAR2(4)      NOT NULL,
    amended     DATE,
    amender     VARCHAR2(4),

    id          VARCHAR2(4)      NOT NULL,
    role_code   VARCHAR2(4)      NOT NULL,
    role_name   VARCHAR2(32)     NOT NULL,
    role_note   VARCHAR2(64)     NOT NULL,
    is_active   NUMBER(1)        NOT NULL,

    CONSTRAINT m01ts00x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01ts00x00_ukc_00 UNIQUE      (role_code),
    CONSTRAINT m01ts00x00_ukc_01 UNIQUE      (role_name),
    CONSTRAINT m01ts00x00_ckc_00 CHECK       (active_flag IN (0,1))
);
COMMENT ON TABLE m01ts00x00 IS 'spring security authorities';
-- -----------------------------------------------------------------------------
-- org.springframework.security.core.GrantedAuthority
-- spring security user_authorities
-- -----------------------------------------------------------------------------
CREATE TABLE m01tj00x00 (
    entered     DATE             NOT NULL,
    enterer     VARCHAR2(4)      NOT NULL,
    amended     DATE,
    amender     VARCHAR2(4),

    id          VARCHAR2(8)      NOT NULL,
    user_code   VARCHAR2(4)      NOT NULL,
    role_code   VARCHAR2(4)      NOT NULL,
    is_active   NUMBER(1)        NOT NULL,

    CONSTRAINT m01tj00x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01tj00x00_ukc_00 UNIQUE      (user_code, role_code),
    CONSTRAINT m01tj00x00_fkc_00 FOREIGN KEY (user_code) REFERENCES m01te00x00 (user_code),
    CONSTRAINT m01tj00x00_fkc_01 FOREIGN KEY (role_code) REFERENCES m01ts00x00 (role_code)
);
COMMENT ON TABLE m01tj00x00 IS 'spring security user_authorities';


-- -----------------------------------------------------------------------------
-- https://docs.spring.io/spring-security/site/docs/5.0.x/reference/html/appendix-schema.html#group-authorities
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- spring security groups 
-- -----------------------------------------------------------------------------
CREATE TABLE m01ts01x00 (
    entered     DATE             NOT NULL,
    enterer     VARCHAR2(4)      NOT NULL,
    amended     DATE,
    amender     VARCHAR2(4),

    id          VARCHAR2(2)      NOT NULL,
    group_code  VARCHAR2(2)      NOT NULL,
    group_name  VARCHAR2(32)     NOT NULL,
    group_note  VARCHAR2(64)     NOT NULL,
    is_active   NUMBER(1)        NOT NULL,

    CONSTRAINT m01ts01x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01ts01x00_pkc_00 UNIQUE      (group_code),
    CONSTRAINT m01ts01x00_ukc_00 UNIQUE      (group_name),
    CONSTRAINT m01ts01x00_ckc_00 CHECK       (is_active IN (0,1))
);
COMMENT ON TABLE m01ts01x00 IS 'spring security groups';
-- -----------------------------------------------------------------------------
-- org.springframework.security.core.GrantedAuthority
-- spring security group_authorities 
-- -----------------------------------------------------------------------------
CREATE TABLE m01tj01x00 (
    entered      DATE            NOT NULL,
    enterer      VARCHAR2(4)     NOT NULL,
    amended      DATE,
    amender      VARCHAR2(4),

    id           VARCHAR2(8)     NOT NULL,
    group_code   VARCHAR2(4)     NOT NULL,
    role_code    VARCHAR2(4)     NOT NULL,

    CONSTRAINT m01tj01x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01tj01x00_ukc_00 UNIQUE      (group_code, role_code),
    CONSTRAINT m01tj01x00_fkc_01 FOREIGN KEY (role_code) REFERENCES m01ts00x00 (role_code),
    CONSTRAINT m01tj01x00_fkc_00 FOREIGN KEY (group_code) REFERENCES m01ts01x00 (group_code)
);
COMMENT ON TABLE m01tj01x00 IS 'spring security group_authorities';
-- -----------------------------------------------------------------------------
-- spring security group_members  
-- -----------------------------------------------------------------------------
CREATE TABLE m01tj02x00 (
    entered     DATE             NOT NULL,
    enterer     VARCHAR2(4)      NOT NULL,
    amended     DATE,
    amender     VARCHAR2(4),

    id          VARCHAR2(6)      NOT NULL,
    group_code  VARCHAR2(2)      NOT NULL,
    user_code   VARCHAR2(4)      NOT NULL,

    CONSTRAINT m01tj02x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01tj02x00_ukc_00 UNIQUE      (group_code, user_code),
    CONSTRAINT m01tj02x00_fkc_01 FOREIGN KEY (user_code) REFERENCES m01te00x00 (user_code),
    CONSTRAINT m01tj02x00_fkc_00 FOREIGN KEY (group_code) REFERENCES m01ts01x00 (group_code)
);
COMMENT ON TABLE m01tj02x00 IS 'spring security group_members ';


-- -----------------------------------------------------------------------------
-- https://docs.spring.io/spring-security/site/docs/5.0.x/reference/html/appendix-schema.html#persistent-login-remember-me-schema
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- org.springframework.security.web.authentication.rememberme.JdbcTokenRepositoryImpl
-- spring security persistent_logins
-- -----------------------------------------------------------------------------
CREATE TABLE m01te01x00 (
    entered     DATE,
    enterer     VARCHAR2(4),
    amended     DATE,
    amender     VARCHAR2(4),

    id          VARCHAR2(4)      NOT NULL,
    user_code   VARCHAR2(4)      NOT NULL,
--  user_name   VARCHAR2(64)     NOT NULL,
    series      VARCHAR2(64)     NOT NULL,
    token       VARCHAR2(64)     NOT NULL,
    last_used   DATE             NOT NULL,

    CONSTRAINT m01te01x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01te01x00_ukc_00 UNIQUE      (series),
    CONSTRAINT m01te01x00_fkc_00 FOREIGN KEY (user_code) REFERENCES m01te00x00 (user_code)
);

COMMENT ON TABLE m01te01x00 IS 'spring security persistent_logins';


-- -----------------------------------------------------------------------------
-- https://docs.spring.io/spring-security/site/docs/5.0.x/reference/html/appendix-schema.html#dbschema-acl
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- org.springframework.security.acls.jdbc.JdbcMutableAclService
-- spring security acl_sid
-- -----------------------------------------------------------------------------
CREATE TABLE m01tj03x00 (
    entered     DATE,
    enterer     VARCHAR2(4),
    amended     DATE,
    amender     VARCHAR2(4),

    id          NUMBER(38)       NOT NULL,
    principal   NUMBER(1)        NOT NULL,   -- {0: user, 1: role}
    sid         VARCHAR2(100)    NOT NULL,   -- {user|role}

    CONSTRAINT m01tj03x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01tj03x00_ukc_00 UNIQUE      (sid, principal),
    CONSTRAINT m01tj03x00_ckc_00 CHECK       (principal IN (0,1))
);
COMMENT ON TABLE m01tj03x00 IS 'spring security acl_sid';
CREATE SEQUENCE m01tj03x00_sqn_00 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER m01tj03x00_tgr_bef_ins BEFORE
    INSERT ON m01tj03x00
    FOR EACH ROW
BEGIN
    SELECT m01tj03x00_sqn_00.NEXTVAL INTO :new.id FROM dual;
END;
-- -----------------------------------------------------------------------------
-- org.springframework.security.acls.jdbc.JdbcMutableAclService
-- spring security acl_class
-- -----------------------------------------------------------------------------
CREATE TABLE m01ts02x00 (
    entered   DATE,
    enterer   VARCHAR2(4),
    amended   DATE,
    amender   VARCHAR2(4),

    id        NUMBER(38)         NOT NULL,
    class     VARCHAR2(100)      NOT NULL,

    CONSTRAINT m01ts02x00_ukc_00 UNIQUE      (class),
    CONSTRAINT m01ts02x00_pkc_00 PRIMARY KEY (id)
);
COMMENT ON TABLE m01ts02x00 IS 'spring security acl_class';
CREATE SEQUENCE m01ts02x00_sqn_00 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER m01ts02x00_tgr_bef_ins BEFORE
    INSERT ON m01ts02x00
    FOR EACH ROW
BEGIN
    SELECT m01ts02x00_sqn_00.NEXTVAL INTO :new.id FROM dual;
END;
-- -----------------------------------------------------------------------------
-- org.springframework.security.acls.jdbc.JdbcMutableAclService
-- spring security acl_object_identity
-- -----------------------------------------------------------------------------
CREATE TABLE m01te02x00 (
    entered              DATE,
    enterer              VARCHAR2(4),
    amended              DATE,
    amender              VARCHAR2(4),

    id                   NUMBER(38)          NOT NULL,
    object_id_class      NUMBER(38)          NOT NULL,
    object_id_identity   VARCHAR2(36)        NOT NULL,
    parent_object        NUMBER(38),
    owner_sid            NUMBER(38),
    entries_inheriting   NUMBER(1)           NOT NULL,

    CONSTRAINT m01te02x00_ckc_00 CHECK       (entries_inheriting IN (0,1)),
    CONSTRAINT m01te02x00_ukc_00 UNIQUE      (object_id_class, object_id_identity),
    CONSTRAINT m01te02x00_fkc_00 FOREIGN KEY (parent_object)   REFERENCES m01te02x00 (id),
    CONSTRAINT m01te02x00_fkc_01 FOREIGN KEY (object_id_class) REFERENCES m01ts02x00 (id),
    CONSTRAINT m01te02x00_fkc_02 FOREIGN KEY (owner_sid)       REFERENCES m01tj03x00 (id),
    CONSTRAINT m01te02x00_pkc_00 PRIMARY KEY (id)
);
COMMENT ON TABLE m01te02x00 IS 'spring security acl_object_identity';
CREATE SEQUENCE m01te02x00_sqn_00 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER m01te02x00_tgr_bef_ins BEFORE
    INSERT ON m01te02x00
    FOR EACH ROW
BEGIN
    SELECT m01te02x00_sqn_00.NEXTVAL INTO :new.id FROM dual;
END;
-- -----------------------------------------------------------------------------
-- org.springframework.security.acls.jdbc.JdbcMutableAclService
-- spring security acl_entry
-- -----------------------------------------------------------------------------
CREATE TABLE m01te03x00 (
    entered               DATE,
    enterer               VARCHAR2(4),
    amended               DATE,
    amender               VARCHAR2(4),

    id                    NUMBER(38)         NOT NULL,
    acl_object_identity   NUMBER(38)         NOT NULL,
    ace_order             INTEGER            NOT NULL,
    sid                   NUMBER(38)         NOT NULL,
    mask                  INTEGER            NOT NULL,
    granting              NUMBER(1)          NOT NULL,
    audit_success         NUMBER(1)          NOT NULL,
    audit_failure         NUMBER(1)          NOT NULL,

    CONSTRAINT m01te03x00_ckc_00 CHECK       (granting      IN (0,1)),
    CONSTRAINT m01te03x00_ckc_01 CHECK       (audit_success IN (0,1)),
    CONSTRAINT m01te03x00_ckc_02 CHECK       (audit_failure IN (0,1)),
    CONSTRAINT m01te03x00_ukc_00 UNIQUE      (acl_object_identity, ace_order),
    CONSTRAINT m01te03x00_fkc_00 FOREIGN KEY (acl_object_identity) REFERENCES m01te02x00 (id),
    CONSTRAINT m01te03x00_fkc_01 FOREIGN KEY (sid)                 REFERENCES m01tj03x00 (id),
    CONSTRAINT m01te03x00_pkc_00 PRIMARY KEY (id)
);
COMMENT ON TABLE m01te03x00 IS 'spring security acl_entry';
CREATE SEQUENCE m01te03x00_sqn_00 START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE OR REPLACE TRIGGER m01te03x00_tgr_bef_ins BEFORE
    INSERT ON m01te03x00
    FOR EACH ROW
BEGIN
    SELECT m01te03x00_sqn_00.NEXTVAL INTO :new.id FROM dual;
END;


-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- org.springframework.security.oauth2.provider.client.JdbcClientDetailsService
-- spring security oauth_client_details
-- -----------------------------------------------------------------------------
CREATE TABLE m01te04x00 (
    entered                   DATE,
    enterer                   VARCHAR2(4),
    amended                   DATE,
    amender                   VARCHAR2(4),

    client_id                 VARCHAR2(256)  NOT NULL,
    resource_ids              VARCHAR2(256),
    client_secret             VARCHAR2(256),
    scope                     VARCHAR2(256),
    authorized_grant_types    VARCHAR2(256),
    web_server_redirect_uri   VARCHAR2(256),
    authorities               VARCHAR2(256),
    access_token_validity     INTEGER,
    refresh_token_validity    INTEGER,
    additional_information    VARCHAR2(4000),
    auto_approve              VARCHAR2(256),

    CONSTRAINT m01te04x00_pkc_00 PRIMARY KEY (client_id)
);
COMMENT ON TABLE m01te04x00 IS 'spring security oauth_client_details';
-- -----------------------------------------------------------------------------
-- org.springframework.security.oauth2.client.token.JdbcClientTokenServices
-- spring security oauth_client_token
-- -----------------------------------------------------------------------------
CREATE TABLE m01te05x00 (
    entered             DATE                 NOT NULL,
    enterer             VARCHAR2(4)          NOT NULL,
    amended             DATE,
    amender             VARCHAR2(4),

    token_id            VARCHAR2(256),
    token               BLOB,
    authentication_id   VARCHAR2(256)        NOT NULL,
    user_name           VARCHAR2(256),
    client_id           VARCHAR2(256),

    CONSTRAINT m01te05x00_pkc_00 PRIMARY KEY (authentication_id)
);
COMMENT ON TABLE m01te05x00 IS 'spring security oauth_client_token';
-- -----------------------------------------------------------------------------
-- org.springframework.security.oauth2.provider.token.store.JdbcTokenStore
-- spring security oauth_access_token
-- -----------------------------------------------------------------------------
CREATE TABLE m01te06x00 (
    entered             DATE                 NOT NULL,
    enterer             VARCHAR2(4)          NOT NULL,
    amended             DATE,
    amender             VARCHAR2(4),

    token_id            VARCHAR2(256),
    token               BLOB,
    authentication_id   VARCHAR2(256)        NOT NULL,
    user_name           VARCHAR2(256),
    client_id           VARCHAR2(256),
    authentication      BLOB,
    refresh_token       VARCHAR2(256),

    CONSTRAINT m01te06x00_pkc_00 PRIMARY KEY (authentication_id)
);
COMMENT ON TABLE m01te06x00 IS 'spring security oauth_access_token';
-- -----------------------------------------------------------------------------
-- org.springframework.security.oauth2.provider.token.store.JdbcTokenStore
-- spring security oauth_refresh_token
-- -----------------------------------------------------------------------------
CREATE TABLE m01te07x00 (
    entered          DATE                    NOT NULL,
    enterer          VARCHAR2(4)             NOT NULL,
    amended          DATE,
    amender          VARCHAR2(4),

    token_id         VARCHAR2(256),
    token            BLOB,
    authentication   BLOB
);
COMMENT ON TABLE m01te07x00 IS 'spring security oauth_refresh_token';
-- -----------------------------------------------------------------------------
-- org.springframework.security.oauth2.provider.code.JdbcAuthorizationCodeServices
-- spring security oauth_code
-- -----------------------------------------------------------------------------
CREATE TABLE m01te08x00 (
    entered          DATE                    NOT NULL,
    enterer          VARCHAR2(4)             NOT NULL,
    amended          DATE,
    amender          VARCHAR2(4),

    code             VARCHAR2(256),
    authentication   BLOB
);
COMMENT ON TABLE m01te08x00 IS 'spring security oauth_code';
-- -----------------------------------------------------------------------------
-- org.springframework.security.oauth2.provider.approval.JdbcApprovalStore
-- spring security oauth_approvals
-- -----------------------------------------------------------------------------
CREATE TABLE m01te09x00 (
    entered              DATE                NOT NULL,
    enterer              VARCHAR2(4)         NOT NULL,
    amended              DATE,
    amender              VARCHAR2(4),

    user_id              VARCHAR2(256),
    client_id            VARCHAR2(256),
    scope                VARCHAR2(256),
    status               VARCHAR2(10),
    expires_date         TIMESTAMP,
    last_modified_date   TIMESTAMP
);
COMMENT ON TABLE m01te09x00 IS 'spring security oauth_approvals';