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

-- PoolCtrl Tabellen

CREATE TABLE IF NOT EXISTS `poolctrl_event` (
  `eventID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,  
  `start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP() ,
  `end` timestamp COLLATE utf8_unicode_ci NOT NULL,
  `participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0),
  `category` varchar(30) COLLATE utf8_unicode_ci,
  `pbs_poolID` int(11) NOT NULL,
  `pbs_personID` int(11) NOT NULL,
  `pbs_bootisoID` int(11) NOT NULL,
  `repeat` bool NOT NULL DEFAULT false,
  `immediate` bool NOT NULL DEFAULT false,
  `note` varchar(140) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`eventID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` (
  `reportID` int(11) NOT NULL AUTO_INCREMENT,
  `report` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success',
  `eventID` int(11) NOT NULL,
  PRIMARY KEY (`reportID`),
  FOREIGN KEY (`eventID`) references poolctrl_event(eventID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5;


-- PBS2 Tabellen
-- TO DO: Tabellen an dieser Stelle nicht per Hand erzeugen sondern von der PBS2-Datenbank kopieren, sodass diese auf dem neusten Stand sind

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,
  `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `loginpassword` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
  `suspend` tinyint(1) NOT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
  `bootisoID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,  
  `description` varchar(140),
  `membershipID` int(11),
  `groupID` int(11) NOT NULL,
  `prebootID` int(11) NOT NULL,    
  `serialnumber` varchar(30) NOT NULL UNIQUE,
  `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;

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` (
  `poolentriesID` INT NOT NULL AUTO_INCREMENT,
  `poolID` int(11) NOT NULL,
  `clientID` int(11) NOT NULL,
  PRIMARY KEY (`poolentriesID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

-- Constraints

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

ALTER TABLE `poolctrl_event` ADD CONSTRAINT `poolctrl_event_poolidC` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
							 ADD CONSTRAINT `poolctrl_event_personidC` FOREIGN KEY (`pbs_personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE,
							 ADD CONSTRAINT `poolctrl_event_boolisoidC` FOREIGN KEY (`pbs_bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`) ON DELETE CASCADE,
							 ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()),
							 ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);
