-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema piads
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema piads
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS piads DEFAULT CHARACTER SET utf8mb4 ;
USE piads ;

-- -----------------------------------------------------
-- Table piads.acl_class
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.acl_class (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  class VARCHAR(100) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX uk_acl_class ON piads.acl_class (class ASC);


-- -----------------------------------------------------
-- Table piads.acl_sid
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.acl_sid (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  principal TINYINT(1) NOT NULL,
  sid VARCHAR(100) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX unique_acl_sid ON piads.acl_sid (sid ASC, principal ASC);


-- -----------------------------------------------------
-- Table piads.acl_object_identity
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.acl_object_identity (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  object_id_class BIGINT(20) UNSIGNED NOT NULL,
  object_id_identity VARCHAR(36) NOT NULL,
  parent_object BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  owner_sid BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  entries_inheriting TINYINT(1) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_acl_object_identity_class
    FOREIGN KEY (object_id_class)
    REFERENCES piads.acl_class (id),
  CONSTRAINT fk_acl_object_identity_owner
    FOREIGN KEY (owner_sid)
    REFERENCES piads.acl_sid (id),
  CONSTRAINT fk_acl_object_identity_parent
    FOREIGN KEY (parent_object)
    REFERENCES piads.acl_object_identity (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX uk_acl_object_identity ON piads.acl_object_identity (object_id_class ASC, object_id_identity ASC);

CREATE INDEX fk_acl_object_identity_parent ON piads.acl_object_identity (parent_object ASC);

CREATE INDEX fk_acl_object_identity_owner ON piads.acl_object_identity (owner_sid ASC);


-- -----------------------------------------------------
-- Table piads.acl_entry
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.acl_entry (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  acl_object_identity BIGINT(20) UNSIGNED NOT NULL,
  ace_order INT(11) NOT NULL,
  sid BIGINT(20) UNSIGNED NOT NULL,
  mask INT(10) UNSIGNED NOT NULL,
  granting TINYINT(1) NOT NULL,
  audit_success TINYINT(1) NOT NULL,
  audit_failure TINYINT(1) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_acl_entry_acl
    FOREIGN KEY (sid)
    REFERENCES piads.acl_sid (id),
  CONSTRAINT fk_acl_entry_object
    FOREIGN KEY (acl_object_identity)
    REFERENCES piads.acl_object_identity (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX unique_acl_entry ON piads.acl_entry (acl_object_identity ASC, ace_order ASC);

CREATE INDEX fk_acl_entry_acl ON piads.acl_entry (sid ASC);


-- -----------------------------------------------------
-- Table piads.users
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.users (
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  enabled TINYINT(1) NOT NULL,
  PRIMARY KEY (username))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


-- -----------------------------------------------------
-- Table piads.authorities
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.authorities (
  username VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  CONSTRAINT fk_authorities_users
    FOREIGN KEY (username)
    REFERENCES piads.users (username))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX ix_auth_username ON piads.authorities (username ASC, authority ASC);


-- -----------------------------------------------------
-- Table piads.groups
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.groups (
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  group_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


-- -----------------------------------------------------
-- Table piads.group_authorities
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.group_authorities (
  group_id BIGINT(20) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  CONSTRAINT fk_group_authorities_group
    FOREIGN KEY (group_id)
    REFERENCES piads.groups (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE INDEX fk_group_authorities_group ON piads.group_authorities (group_id ASC);


-- -----------------------------------------------------
-- Table piads.group_members
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.group_members (
  id BIGINT(20) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  group_id BIGINT(20) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_group_members_group
    FOREIGN KEY (group_id)
    REFERENCES piads.groups (id),
  CONSTRAINT fk_group_members_users1
    FOREIGN KEY (username)
    REFERENCES piads.users (username)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

CREATE INDEX fk_group_members_group ON piads.group_members (group_id ASC);

CREATE INDEX fk_group_members_users1_idx ON piads.group_members (username ASC);


-- -----------------------------------------------------
-- Table piads.persistent_logins
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS piads.persistent_logins (
  username VARCHAR(64) NOT NULL,
  series VARCHAR(64) NOT NULL,
  token VARCHAR(64) NOT NULL,
  last_used TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  PRIMARY KEY (series))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;