CREATE TABLE IF NOT EXISTS Binding (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255),
  peer_id CHAR(41),
  UNIQUE INDEX (peer_id),
  INDEX (email(20))
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS EmailUser (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255),
  passwd VARCHAR(256),
  is_staff BOOL NOT NULL,
  is_active BOOL NOT NULL,
  ctime BIGINT,
  reference_id VARCHAR(255),
  UNIQUE INDEX (email),
  UNIQUE INDEX (reference_id)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `Group` (
  `group_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `group_name` VARCHAR(255),
  `creator_name` VARCHAR(255),
  `timestamp` BIGINT,
  `type` VARCHAR(32),
  `parent_group_id` INTEGER
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS GroupDNPair (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  group_id INTEGER,
  dn VARCHAR(255)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS GroupStructure (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  group_id INTEGER,
  path VARCHAR(1024),
  UNIQUE INDEX(group_id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `GroupUser` (
  `id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `group_id` BIGINT,
  `user_name` VARCHAR(255),
  `is_staff` tinyint,
  UNIQUE INDEX (`group_id`, `user_name`),
  INDEX (`user_name`)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS LDAPConfig (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  cfg_group VARCHAR(255) NOT NULL,
  cfg_key VARCHAR(255) NOT NULL,
  value VARCHAR(255),
  property INTEGER
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS LDAPUsers (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  password varchar(255) NOT NULL,
  is_staff BOOL NOT NULL,
  is_active BOOL NOT NULL,
  extra_attrs TEXT,
  reference_id VARCHAR(255),
  UNIQUE INDEX(email),
  UNIQUE INDEX (reference_id)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS OrgGroup (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  org_id INTEGER,
  group_id INTEGER,
  INDEX (group_id),
  UNIQUE INDEX(org_id, group_id)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS OrgUser (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  org_id INTEGER,
  email VARCHAR(255),
  is_staff BOOL NOT NULL,
  INDEX (email),
  UNIQUE INDEX(org_id, email)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS Organization (
  org_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  org_name VARCHAR(255),
  url_prefix VARCHAR(255),
  creator VARCHAR(255),
  ctime BIGINT,
  UNIQUE INDEX (url_prefix)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS UserRole (
  id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255),
  role VARCHAR(255),
  is_manual_set INTEGER DEFAULT 0,
  UNIQUE INDEX (email)
) ENGINE=INNODB;