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='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `webApplication` ;
SHOW WARNINGS;
USE `webApplication`;

-- -----------------------------------------------------
-- Table `webApplication`.`aclPrivileges`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`aclPrivileges` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `description` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `webApplication`.`aclResources`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`aclResources` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `description` VARCHAR(255) NOT NULL ,
  `availablePrivileges` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 29
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `webApplication`.`groups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`groups` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `description` CHAR(32) NULL DEFAULT NULL ,
  `styleDefinition` VARCHAR(255) NULL DEFAULT NULL ,
  `createdByFK` INT(10) UNSIGNED NOT NULL ,
  `createdTimestamp` DATETIME NOT NULL ,
  `modifiedByFK` INT(10) UNSIGNED NOT NULL ,
  `modifiedTimestamp` DATETIME NOT NULL ,
  `status` TINYINT(4) NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `FK_groups_1`
    FOREIGN KEY (`createdByFK` )
    REFERENCES `webApplication`.`users` (`id` ),
  CONSTRAINT `FK_groups_2`
    FOREIGN KEY (`modifiedByFK` )
    REFERENCES `webApplication`.`users` (`id` ))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX createdByFK ON `webApplication`.`groups` (`createdByFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX modifiedByFK ON `webApplication`.`groups` (`modifiedByFK` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `webApplication`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `groupFK` INT(10) UNSIGNED NOT NULL ,
  `username` CHAR(16) NOT NULL ,
  `password` CHAR(64) NOT NULL ,
  `firstName` VARCHAR(32) NOT NULL ,
  `middleInitial` CHAR(1) NULL DEFAULT NULL ,
  `lastName` VARCHAR(32) NOT NULL ,
  `emailAddress` VARCHAR(64) NULL DEFAULT NULL ,
  `phoneNumber` CHAR(16) NULL DEFAULT NULL ,
  `createdByFK` INT(10) UNSIGNED NOT NULL ,
  `createdTimestamp` DATETIME NOT NULL ,
  `modifiedByFK` INT(10) UNSIGNED NOT NULL ,
  `modifiedTimestamp` DATETIME NOT NULL ,
  `status` TINYINT(4) NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `FK_users_1`
    FOREIGN KEY (`groupFK` )
    REFERENCES `webApplication`.`groups` (`id` ),
  CONSTRAINT `FK_users_2`
    FOREIGN KEY (`createdByFK` )
    REFERENCES `webApplication`.`users` (`id` ),
  CONSTRAINT `FK_users_3`
    FOREIGN KEY (`modifiedByFK` )
    REFERENCES `webApplication`.`users` (`id` ))
ENGINE = InnoDB
AUTO_INCREMENT = 1007
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE UNIQUE INDEX i_username USING BTREE ON `webApplication`.`users` (`username` ASC) ;

SHOW WARNINGS;
CREATE INDEX groupFK ON `webApplication`.`users` (`groupFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX createdByFK ON `webApplication`.`users` (`createdByFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX modifiedByFK ON `webApplication`.`users` (`modifiedByFK` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `webApplication`.`aclGroups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`aclGroups` (
  `groupFK` INT(10) UNSIGNED NOT NULL ,
  `aclResourceFK` INT(10) UNSIGNED NOT NULL ,
  `aclPrivilegeFK` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`groupFK`, `aclResourceFK`, `aclPrivilegeFK`) ,
  CONSTRAINT `FK_aclGroups_1`
    FOREIGN KEY (`aclPrivilegeFK` )
    REFERENCES `webApplication`.`aclPrivileges` (`id` ),
  CONSTRAINT `FK_aclGroups_2`
    FOREIGN KEY (`aclResourceFK` )
    REFERENCES `webApplication`.`aclResources` (`id` ),
  CONSTRAINT `FK_aclGroups_3`
    FOREIGN KEY (`groupFK` )
    REFERENCES `webApplication`.`groups` (`id` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX aclPrivilegeFK ON `webApplication`.`aclGroups` (`aclPrivilegeFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX aclResourceFK ON `webApplication`.`aclGroups` (`aclResourceFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX groupFK ON `webApplication`.`aclGroups` (`groupFK` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `webApplication`.`aclUsers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`aclUsers` (
  `userFK` INT(10) UNSIGNED NOT NULL ,
  `aclResourceFK` INT(10) UNSIGNED NOT NULL ,
  `aclPrivilegeFK` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`userFK`, `aclResourceFK`, `aclPrivilegeFK`) ,
  CONSTRAINT `FK_aclUsers_1`
    FOREIGN KEY (`aclPrivilegeFK` )
    REFERENCES `webApplication`.`aclPrivileges` (`id` ),
  CONSTRAINT `FK_aclUsers_2`
    FOREIGN KEY (`aclResourceFK` )
    REFERENCES `webApplication`.`aclResources` (`id` ),
  CONSTRAINT `FK_aclUsers_3`
    FOREIGN KEY (`userFK` )
    REFERENCES `webApplication`.`users` (`id` ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;
CREATE INDEX aclPrivilegeFK ON `webApplication`.`aclUsers` (`aclPrivilegeFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX aclResourceFK ON `webApplication`.`aclUsers` (`aclResourceFK` ASC) ;

SHOW WARNINGS;
CREATE INDEX userFK ON `webApplication`.`aclUsers` (`userFK` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `webApplication`.`log`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `webApplication`.`log` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `message` TEXT NOT NULL ,
  `timestamp` DATETIME NOT NULL ,
  `priority` VARCHAR(45) NOT NULL ,
  `priorityName` VARCHAR(45) NOT NULL ,
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `line` INT(10) UNSIGNED NOT NULL ,
  `code` INT(11) NOT NULL ,
  `file` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 1041
DEFAULT CHARACTER SET = latin1;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Placeholder table for view `webApplication`.`acl`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `webApplication`.`acl` (`id` INT);
SHOW WARNINGS;

-- -----------------------------------------------------
-- View `webApplication`.`acl`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `webApplication`.`acl`;
SHOW WARNINGS;
CREATE OR REPLACE  OR REPLACE VIEW `acl` AS 
SELECT 
    NULL AS `userFK`,`aclGroups`.`groupFK` AS `groupFK`
    ,`aclGroups`.`aclResourceFK` AS `aclResourceFK`
    ,`aclGroups`.`aclPrivilegeFK` AS `aclPrivilegeFK` 
FROM `aclGroups` 
UNION 
SELECT 
    `aclUsers`.`userFK` AS `userFK`
    ,NULL AS `groupFK`
    ,`aclUsers`.`aclResourceFK` AS `aclResourceFK`
    ,`aclUsers`.`aclPrivilegeFK` AS `aclPrivilegeFK` 
FROM `aclUsers`;
SHOW WARNINGS;


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