SET FOREIGN_KEY_CHECKS=0;



-- ----------------------------
-- Table structure for applications
-- ----------------------------
CREATE TABLE `applications` (
  `appid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `exec` varchar(255) NOT NULL,
  `guest` tinyint(1) NOT NULL DEFAULT '0',
  `state` tinyint(1) NOT NULL DEFAULT '0',
  `last_action` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `session` varchar(255) NOT NULL DEFAULT '',
  `view` tinyint(1) NOT NULL DEFAULT '1',
  `reservation` tinyint(1) NOT NULL DEFAULT '0',
  `res_limits` varchar(255) NOT NULL DEFAULT '10,1,7,14',
  PRIMARY KEY (`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DELIMITER ;;
CREATE TRIGGER `ctr_req_cleanup` AFTER UPDATE ON `applications` FOR EACH ROW IF NEW.state = '0' THEN
DELETE FROM control_requests WHERE appid=NEW.appid;
END IF;;
DELIMITER ;

-- ----------------------------
-- Table structure for control_requests
-- ----------------------------
CREATE TABLE `control_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sessid` varchar(255) CHARACTER SET utf8 NOT NULL,
  `appid` int(10) unsigned NOT NULL,
  `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
  PRIMARY KEY (`id`),
  KEY `appid` (`appid`),
  KEY `sessid` (`sessid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for reservations
-- ----------------------------
CREATE TABLE `reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `appid` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `appid` (`appid`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for sessions
-- ----------------------------
CREATE TABLE `sessions` (
  `sessid` varchar(255) CHARACTER SET utf8 NOT NULL,
  `last_action` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` varchar(15) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `uid` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`sessid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for user_data
-- ----------------------------
CREATE TABLE `user_data` (
  `uid` int(10) unsigned NOT NULL,
  `first_name` varchar(128) CHARACTER SET utf8mb4 DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `year` smallint(1) DEFAULT NULL,
  `study` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`uid`),
  CONSTRAINT `uid` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for users
-- ----------------------------
CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) CHARACTER SET utf8 NOT NULL,
  `password` varchar(255) NOT NULL DEFAULT '',
  `created` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `last_login` timestamp NULL DEFAULT '1970-01-01 01:00:01',
  `attempts` tinyint(1) unsigned DEFAULT '0',
  `last_attempt` timestamp NULL DEFAULT '1970-01-01 01:00:01',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



SET GLOBAL event_scheduler = ON;

DROP EVENT IF EXISTS cleanup_sessions;
CREATE EVENT cleanup_sessions
ON SCHEDULE EVERY 30 MINUTE
DO
DELETE FROM sessions WHERE last_action < DATE_SUB(NOW(), INTERVAL 20 MINUTE)


DROP EVENT IF EXISTS cleanup_reservations;
CREATE EVENT cleanup_reservations
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM reservations WHERE end < DATE(NOW())


DROP PROCEDURE IF EXISTS ControlReqs_ResetAutInc;
CREATE PROCEDURE ControlReqs_ResetAutInc()
BEGIN

SET @var = (SELECT COUNT(*) FROM control_requests);
IF @var = '0' THEN
ALTER TABLE control_requests AUTO_INCREMENT = 1;
END IF;

END;



DROP PROCEDURE IF EXISTS Reservations_ResetAutInc;
CREATE PROCEDURE Reservations_ResetAutInc()
BEGIN

SET @var = (SELECT COUNT(*) FROM reservations);
IF @var = '0' THEN
ALTER TABLE reservations AUTO_INCREMENT = 1;
END IF;

END;

DROP EVENT IF EXISTS reset_autoinc;
CREATE EVENT reset_autoinc
ON SCHEDULE EVERY 120 MINUTE
DO
BEGIN
CALL Reservations_ResetAutInc();
CALL ControlReqs_ResetAutInc();
END;


