-- -----------------------------------------------------------------------------
-- 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_ukc_00 UNIQUE      (group_code),
    CONSTRAINT m01ts01x00_ukc_01 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,
    is_active    NUMBER(1)       NOT NULL,

    CONSTRAINT m01tj01x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m01tj01x00_ckc_00 CHECK       (is_active IN (0,1)),
    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,
    user_code   VARCHAR2(4)      NOT NULL,
    group_code  VARCHAR2(2)      NOT NULL,
    is_active   NUMBER(1)        NOT NULL,

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