CREATE DATABASE `pbs` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE pbs;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `pbs_group` (
  `groupID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(140) COLLATE utf8_unicode_ci,
  PRIMARY KEY (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `pbs_right` (
  `rightID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(140) COLLATE utf8_unicode_ci,
  PRIMARY KEY (`rightID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `pbs_role` (
  `roleID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(140) COLLATE utf8_unicode_ci,
  PRIMARY KEY (`roleID`),
  KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_role`
  ADD CONSTRAINT `pbs_role_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_rightroles` (
  `roleID` int(11) NOT NULL,
  `rightID` int(11) NOT NULL,
  KEY `roleID` (`roleID`),
  KEY `rightID` (`rightID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_rightroles`
  ADD CONSTRAINT `pbs_rightroles_ibfk_1` FOREIGN KEY (`roleID`) REFERENCES `pbs_role` (`roleID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_rightroles_ibfk_2` FOREIGN KEY (`rightID`) REFERENCES `pbs_right` (`rightID`) ON DELETE CASCADE;
  

CREATE TABLE IF NOT EXISTS `pbs_groupgroups` (
  `parentID` int(11) NOT NULL,
  `groupID` int(11) NOT NULL,
  KEY `parentID` (`parentID`),
  KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_groupgroups`
  ADD CONSTRAINT `pbs_groupgroups_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_groupgroups_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_person` (
  `personID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci,
  `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `firstname` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `street` varchar(30) COLLATE utf8_unicode_ci,
  `housenumber` varchar(30) COLLATE utf8_unicode_ci,
  `city` varchar(30) COLLATE utf8_unicode_ci,
  `postalcode` varchar(30) COLLATE utf8_unicode_ci,
  `logindate` varchar(14) COLLATE utf8_unicode_ci,
  `registerdate` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `login` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pbs_grouprequest` (
  `grouprequestID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `personID` int(11) NOT NULL,
  `time` varchar(14) NOT NULL,
  PRIMARY KEY (`grouprequestID`),
  KEY `groupID` (`groupID`),
  KEY `personID` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_grouprequest`
  ADD CONSTRAINT `pbs_grouprequest_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_grouprequest_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_membership` (
  `membershipID` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) NOT NULL,
  `roleID` int(11) NOT NULL,
  `personID` int(11) NOT NULL,
  PRIMARY KEY (`membershipID`),
  KEY `groupID` (`groupID`),
  KEY `roleID` (`roleID`),
  KEY `personID` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_membership`
  ADD CONSTRAINT `pbs_membership_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_membership_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_membership_ibfk_3` FOREIGN KEY (`roleID`) REFERENCES `pbs_role` (`roleID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_config` (
  `configID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `groupID` int(11) NOT NULL,
  `membershipID` int(11),
  `shellscript` text NOT NULL,
  `created` VARCHAR(14) NOT NULL,
  PRIMARY KEY (`configID`),
  KEY `groupID` (`groupID`),
  KEY `membershipID` (`membershipID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_config`
  ADD CONSTRAINT `pbs_config_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_config_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

  
CREATE TABLE IF NOT EXISTS `pbs_bootos` (
  `bootosID` int(11) NOT NULL AUTO_INCREMENT,
  `configID` int(11),
  `groupID` int(11) NOT NULL,
  `membershipID` int(11),
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `path_init` varchar(140) NOT NULL,
  `path_kernel` varchar(140) NOT NULL,
  `defaultkcl` varchar(140) NOT NULL,
  `created` VARCHAR(14) NOT NULL,
  `expires` VARCHAR(14),
  `public` int(11) NOT NULL,
  PRIMARY KEY (`bootosID`),
  KEY `configID` (`configID`),
  KEY `groupID` (`groupID`),
  KEY `membershipID` (`membershipID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_bootos`
  ADD CONSTRAINT `pbs_bootos_ibfk_1` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`),
  ADD CONSTRAINT `pbs_bootos_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`);

CREATE TABLE IF NOT EXISTS `pbs_bootmenu` (
  `bootmenuID` int(11) NOT NULL AUTO_INCREMENT,
  `membershipID` int(11),
  `groupID` int(11) NOT NULL,
  `title` varchar(30) NOT NULL,
  `created` varchar(14) NOT NULL,
  PRIMARY KEY (`bootmenuID`),
  KEY `groupID` (`groupID`),
  KEY `membershipID` (`membershipID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_bootmenu`
  ADD CONSTRAINT `pbs_bootmenu_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_bootmenu_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_bootmenuentries` (
  `bootmenuentriesID` int(11) NOT NULL AUTO_INCREMENT,
  `bootosID` int(11) NOT NULL,
  `bootmenuID` int(11) NOT NULL,
  `title` varchar(30) NOT NULL,
  `kcl` varchar(140),
  `configID` int(11),
  `order` int(11) NOT NULL,
  PRIMARY KEY (`bootmenuentriesID`),
  KEY `bootosID` (`bootosID`),
  KEY `bootmenuID` (`bootmenuID`),
  KEY `configID` (`configID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_bootmenuentries`
  ADD CONSTRAINT `pbs_bootmenuentries_ibfk_1` FOREIGN KEY (`bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_bootmenuentries_ibfk_2` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_bootmenuentries_ibfk_3` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`);

CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
  `bootisoID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,  
  `membershipID` int(11),
  `groupID` int(11) NOT NULL,
  `path` varchar(30) NOT NULL,    
  `serialnumber` int(11) NOT NULL,
  `created` VARCHAR(14) NOT NULL,
  `expires` VARCHAR(14),
  `public` int(11) NOT NULL,
  PRIMARY KEY (`bootisoID`),
  KEY `membershipID` (`membershipID`),
  KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_bootiso`
  ADD CONSTRAINT `pbs_bootiso_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`),
  ADD CONSTRAINT `pbs_bootiso_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_client` (
  `clientID` int(11) NOT NULL AUTO_INCREMENT,
  `macadress` varchar(17) NOT NULL,
  `hardwarehash` varchar(32),
  PRIMARY KEY (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `pbs_session` (
  `sessionID` int(11) NOT NULL AUTO_INCREMENT,
  `clientID` int(11) NOT NULL,
  `bootosID` int(11) NOT NULL,
  `time` varchar(14) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `ip6` varchar(45),
  PRIMARY KEY (`sessionID`),
  KEY `clientID` (`clientID`),
  KEY `bootosID` (`bootosID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `pbs_session` ADD `bootisoID` INT NOT NULL AFTER `bootosID`; 

ALTER TABLE `pbs_session`
  ADD CONSTRAINT `pbs_session_ibfk_1` FOREIGN KEY (`clientID`) REFERENCES `pbs_client` (`clientID`),
  ADD CONSTRAINT `pbs_session_ibfk_2` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`);
  ADD CONSTRAINT `pbs_session_ibfk_3` FOREIGN KEY (`bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`);

CREATE TABLE IF NOT EXISTS `pbs_pool` (
  `poolID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `location` varchar(30),
  PRIMARY KEY (`poolID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
  `poolID` int(11) NOT NULL,
  `clientID` int(11) NOT NULL,
  KEY `poolID` (`poolID`),
  KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `pbs_poolentries` ADD `poolentriesID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE `pbs_poolentries`
  ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_poolentries_ibfk_2` FOREIGN KEY (`clientID`) REFERENCES `pbs_client` (`clientID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_filter` (
  `filterID` int(11) NOT NULL AUTO_INCREMENT,
  `membershipID` int(11),
  `groupID` int(11) NOT NULL,
  `bootmenuID` int(11) NOT NULL,
  `title` varchar(30) NOT NULL,
  `description` varchar(140),  
  `created` varchar(14) NOT NULL,
  `priority` int(11) NOT NULL,
  PRIMARY KEY (`filterID`),
  KEY `membershipID` (`membershipID`),
  KEY `groupID` (`groupID`),
  KEY `bootmenuID` (`bootmenuID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_filter`
  ADD CONSTRAINT `pbs_filter_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`),
  ADD CONSTRAINT `pbs_filter_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_filter_ibfk_3` FOREIGN KEY (`bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_membershipfilters` (
  `membershipID` int(11) NOT NULL,
  `filterID` int(11) NOT NULL,
  KEY `membershipID` (`membershipID`),
  KEY `filterID` (`filterID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_membershipfilters`
  ADD CONSTRAINT `pbs_membershipfilters_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_membershipfilters_ibfk_2` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_poolfilters` (
  `poolID` int(11) NOT NULL,
  `filterID` int(11) NOT NULL,
  KEY `poolID` (`poolID`),
  KEY `filterID` (`filterID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_poolfilters`
  ADD CONSTRAINT `pbs_poolfilters_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_poolfilters_ibfk_2` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS `pbs_filtertype` (
  `filtertypeID` int(11) NOT NULL AUTO_INCREMENT,
  `filtertypename` varchar(30) NOT NULL,
  PRIMARY KEY (`filtertypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `pbs_filterentries` (
  `filterID` int(11) NOT NULL,
  `filtertypeID` int(11) NOT NULL,
  `filtervalue` varchar(140) NOT NULL,
  `filtervalue2` varchar(140) NOT NULL,
  KEY `filterID` (`filterID`),
  KEY `filtertypeID` (`filtertypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_filterentries` ADD `filterentriesID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; 
ALTER TABLE `pbs_filterentries`
  ADD CONSTRAINT `pbs_filterentries_ibfk_1` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE,
  ADD CONSTRAINT `pbs_filterentries_ibfk_2` FOREIGN KEY (`filtertypeID`) REFERENCES `pbs_filtertype` (`filtertypeID`) ON DELETE CASCADE;