-- -----------------------------------------------------------------------------
-- apache mod_authn_dbd
-- user authentication
-- -----------------------------------------------------------------------------
CREATE TABLE m00te00x00 (
    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(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 m00te00x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m00te00x00_ukc_00 UNIQUE      (user_code),
    CONSTRAINT m00te00x00_ukc_01 UNIQUE      (user_name),
    CONSTRAINT m00te00x00_ckc_00 CHECK       (is_active IN (0,1)),
    CONSTRAINT m00te00x00_ckc_01 CHECK       (is_locked IN (0,1)),
    CONSTRAINT m00te00x00_ckc_02 CHECK       (is_signin IN (0,1))

--  CONSTRAINT m00te00x00_fkc_00 FOREIGN KEY (enterer) REFERENCES m00te00x00 (user_code),
--  CONSTRAINT m00te00x00_fkc_01 FOREIGN KEY (amender) REFERENCES m00te00x00 (user_code)
);
COMMENT ON TABLE m00te00x00 IS 'apache mod_authn_dbd users';

-- -----------------------------------------------------------------------------
-- apache mod_authz_dbd
-- user authority
-- -----------------------------------------------------------------------------
CREATE TABLE m00ts00x00 (
    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(256)   NOT NULL,
    is_active    NUMBER(1)       NOT NULL,

    CONSTRAINT m00ts00x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m00ts00x00_ukc_00 UNIQUE      (group_code),
    CONSTRAINT m00ts00x00_ukc_01 UNIQUE      (group_name),
    CONSTRAINT m00ts00x00_ckc_00 CHECK       (is_active IN (0,1))

--  CONSTRAINT m00ts00x00_fkc_00 FOREIGN KEY (enterer) REFERENCES m00te00x00 (user_code),
--  CONSTRAINT m00ts00x00_fkc_01 FOREIGN KEY (amender) REFERENCES m00te00x00 (user_code)
);
COMMENT ON TABLE m00ts00x00 IS 'apache mod_authz_dbd groups';

-- -----------------------------------------------------------------------------
-- apache mod_authz_dbd
-- user authorization
-- -----------------------------------------------------------------------------
CREATE TABLE m00tj00x00 (
    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,
    group_code   VARCHAR2(2)     NOT NULL,
    is_active    NUMBER(1)       NOT NULL,

    CONSTRAINT m00tj00x00_pkc_00 PRIMARY KEY (id),
    CONSTRAINT m00tj00x00_ckc_00 CHECK       (is_active IN (0,1)),
    CONSTRAINT m00tj00x00_ukc_00 UNIQUE      (group_code, user_code)

--  CONSTRAINT m00tj00x00_fkc_00 FOREIGN KEY (enterer)    REFERENCES m00te00x00 (user_code),
--  CONSTRAINT m00tj00x00_fkc_01 FOREIGN KEY (amender)    REFERENCES m00te00x00 (user_code),
--  CONSTRAINT m00tj00x00_fkc_02 FOREIGN KEY (user_code)  REFERENCES m00te00x00 (user_code),
--  CONSTRAINT m00tj00x00_fkc_03 FOREIGN KEY (group_code) REFERENCES m00ts00x00 (group_code)
);
COMMENT ON TABLE m00tj00x00 IS 'apache mod_authz_dbd user_groups';