CREATE TABLE IF NOT EXISTS user
(
    user_id        BIGINT       NOT NULL AUTO_INCREMENT,
    person_id      BIGINT       NOT NULL,
    name           VARCHAR(100) NOT NULL,
    password       TEXT         NOT NULL,
    is_admin       INT(1)       NOT NULL DEFAULT (0),
    m_code         VARCHAR(4)   NOT NULL COMMENT '{"text":3, "number":1}',
    owner_id       BIGINT       NOT NULL,
    data_state     VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time    TIMESTAMP             DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time    TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner (owner_id),
    FOREIGN KEY (create_user_id) REFERENCES user (user_id),
    FOREIGN KEY (update_user_id) REFERENCES user (user_id),
    CONSTRAINT unique_m_code UNIQUE (m_code, owner_id)
);

CREATE TABLE IF NOT EXISTS person
(
    person_id      BIGINT       NOT NULL AUTO_INCREMENT,
    vorname        VARCHAR(100) NOT NULL,
    nachname       VARCHAR(100) NOT NULL,
    m_code         VARCHAR(5)   NOT NULL COMMENT '{"text":4, "number":2}',
    owner_id       BIGINT       NOT NULL,
    data_state     VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time    TIMESTAMP             DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time    TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (person_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner (owner_id),
    FOREIGN KEY (create_user_id) REFERENCES user (user_id),
    FOREIGN KEY (update_user_id) REFERENCES user (user_id),
    CONSTRAINT unique_m_code UNIQUE (m_code, owner_id)
);

ALTER TABLE user
    ADD
        FOREIGN KEY (person_id) REFERENCES person (person_id);

CREATE TABLE IF NOT EXISTS rechte_gruppe
(
    gruppe_id      BIGINT       NOT NULL AUTO_INCREMENT,
    name           VARCHAR(100) NOT NULL,
    owner_id       BIGINT       NOT NULL,
    data_state     VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time    TIMESTAMP             DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time    TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (gruppe_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner (owner_id),
    FOREIGN KEY (create_user_id) REFERENCES user (user_id),
    FOREIGN KEY (update_user_id) REFERENCES user (user_id)
);

CREATE TABLE IF NOT EXISTS key_gruppe_user
(
    key_id    BIGINT NOT NULL AUTO_INCREMENT,
    gruppe_id BIGINT NOT NULL,
    user_id   BIGINT NOT NULL,
    PRIMARY KEY (key_id),
    FOREIGN KEY (gruppe_id) REFERENCES rechte_gruppe (gruppe_id),
    FOREIGN KEY (user_id) REFERENCES user (user_id)
);

CREATE TABLE IF NOT EXISTS key_gruppe_code
(
    key_id    BIGINT NOT NULL AUTO_INCREMENT,
    gruppe_id BIGINT NOT NULL,
    code_id   BIGINT NOT NULL,
    PRIMARY KEY (key_id),
    FOREIGN KEY (gruppe_id) REFERENCES rechte_gruppe (gruppe_id),
    FOREIGN KEY (code_id) REFERENCES PREFIXconfig.rechte_code (code_id)
);
