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