/** 
 * /config/db.sql
 * @version 1.0
 * @desc SQL set up file
 * @author Fándly Gergő Zoltán
 * @copy 2017 Fándly Gergő Zoltán
 */

DROP TABLE IF EXISTS `users`, `login_history`, `login_bans`, `time_sequences`, `time_blocks`, `programs`, `registrations`, `registration_log`;

CREATE TABLE `users`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `name` varchar(65) NOT NULL default '',
    `class` varchar(10) NOT NULL default '',   /* format: ddC (ex: 05D) */
    `accesslevel` tinyint(1) UNSIGNED NOT NULL default 0,   /* 0:student; 1:head teacher; 2:manager; 3:administrator */
    `password` varchar(255) NOT NULL default '',
    `except_login` tinyint(1) UNSIGNED NOT NULL default 0,   /* 0:no change; 1:always allow login; 2:never allow login - only takes effect for students */
    `except_signup` tinyint(1) UNSIGNED NOT NULL default 0,   /* 0:no change; 1:always allow sign up; 2:never allow sign up - only takes effect for students */
    PRIMARY KEY (`id`)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `login_history`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `user` int(4) UNSIGNED NOT NULL default 1,   /* id of nouser */
    `date` timestamp NOT NULL default current_timestamp,
    `ip` varchar(45) NOT NULL default '0.0.0.0',
    `auth_token` varchar(65) NOT NULL default '',
    `user_agent` varchar(500) NOT NULL default '',
    `success` tinyint(1) NOT NULL default 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user`) REFERENCES users(`id`) ON DELETE CASCADE
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `login_bans`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `ip` varchar(45) NOT NULL default '0.0.0.0',
    `until` timestamp NOT NULL default current_timestamp,
    PRIMARY KEY (`id`)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `time_sequences`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `name` varchar(65) NOT NULL default '',   /* ex: monday, tuesday, 1st week, etc */
    `allow_signup` tinyint(1) UNSIGNED NOT NULL default 1,   /* 0:forbid; 1:allow */
    PRIMARY KEY (`id`)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `time_blocks`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `name` varchar(65) NOT NULL default '',   /* ex: 9-10, 8:00, etc */
    `sequence` int(4) UNSIGNED NOT NULL default 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`sequence`) REFERENCES time_sequences(`id`) ON DELETE CASCADE
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `programs`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `name` varchar(65) NOT NULL default '',
    `description` text NOT NULL default '',   /* as long, as wished! */
    `instructor` varchar(150) NOT NULL default '',
    `location` varchar(150) NOT NULL default '',
    `category` tinyint(1) UNSIGNED NOT NULL default 0,   /* 0:0th class; 1:1-2th class; 2:3-4th class; 3:5-6th class; 4:7-8th class; 5:9-10th class 6:11-12th class; 10:0-4th class; 11:5-8th class; 12:9-12th class; 20:0-12th class */
    `time_block` int(4) UNSIGNED NOT NULL default 0,
    `max_participants` int(4) UNSIGNED NOT NULL default 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`time_block`) REFERENCES time_blocks(`id`) ON DELETE NO ACTION
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `registrations`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `user` int(4) UNSIGNED NOT NULL default 0,
    `program` int(4) UNSIGNED NOT NULL default 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user`) REFERENCES users(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`program`) REFERENCES programs(`id`) ON DELETE CASCADE
) CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `registration_log`(
    `id` int(4) UNSIGNED NOT NULL auto_increment,
    `user` int(4) UNSIGNED NOT NULL default 0,
    `date` timestamp NOT NULL default current_timestamp,
    `action` tinyint(1) UNSIGNED NOT NULL default 0,   /* 0:unsibscribe; 1:subscribe; 10:admin deleted; 11:admin added */
    `program` int(4) UNSIGNED NOT NULL default 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user`) REFERENCES users(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`program`) REFERENCES programs(`id`) ON DELETE CASCADE
) CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO users (`id`, `name`) VALUES (1, 'nouser');