/* 
Pro hostingy -> ) ENGINE = TypUloziste (například. MyISAM nebo InnoDB) 
*/

-- ----------------------------
-- Databáze PORTAL
-- ----------------------------
DROP DATABASE IF EXISTS `portal`;
CREATE DATABASE `portal`;
USE `portal`;

-- ----------------------------
-- Tabulka USERS
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
	`user_id`  int(11)                                      NOT NULL AUTO_INCREMENT,
	`name` varchar(30) COLLATE utf8_czech_ci                NOT NULL,
	`pass` varchar(60) COLLATE utf8_czech_ci                NOT NULL,
	`role`     enum ('user', 'admin') COLLATE utf8_czech_ci NOT NULL DEFAULT 'user',
	PRIMARY KEY (`user_id`),
	UNIQUE KEY `name` (`name`) USING BTREE
) AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;

-- ----------------------------
-- Záznamy USERS
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'admin', '$2y$10$5y2TXn8WUFUL9H8sovHeaeOwibEcjARCf./BwZ/OP6ZX044Hr4Wce', 'admin');

-- ----------------------------
-- Tabulka EVENTS
-- ----------------------------
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
	`event_id`  int(11) NOT NULL AUTO_INCREMENT,
	`name`      varchar(50) COLLATE utf8_czech_ci DEFAULT NULL,
	`url`       varchar(50) COLLATE utf8_czech_ci DEFAULT NULL,
	`popis` 	text COLLATE utf8_czech_ci DEFAULT NULL,
	`otevreno`	tinyint COLLATE utf8_czech_ci DEFAULT 0,
	`created_at`	timestamp COLLATE utf8_czech_ci NOT NULL,
	PRIMARY KEY (`event_id`),
	UNIQUE KEY `url` (`url`)
) DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;

-- ----------------------------
-- Záznamy EVENTS
-- ----------------------------
INSERT INTO `events` VALUES ('1', 'Filmový festival', 'film-fest', '<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean fermentum risus id tortor. Ut enim ad min</p>
<p>Cras pede libero, dapibus nec, pretium sit amet, tempor quis. Pellentesque sapien. Maecenas aliquet accumsan leo.</p>', 1, now());
INSERT INTO `events` VALUES ('2', 'Workshop', 'workshop', '<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean fermentum risus id tortor. Ut enim ad min</p>
<p>Cras pede libero, dapibus nec, pretium sit amet, tempor quis. Pellentesque sapien. Maecenas aliquet accumsan leo.</p>', 1, now());

-- ----------------------------
-- Tabulka INVITATIONS
-- ----------------------------
DROP TABLE IF EXISTS `invitations`;
CREATE TABLE `invitations` (
	`invitation_id`  int(11) NOT NULL AUTO_INCREMENT,
	`name`      varchar(50) COLLATE utf8_czech_ci DEFAULT NULL,
	`body`      text COLLATE utf8_czech_ci DEFAULT NULL,
	`event_id` 	int(11) COLLATE utf8_czech_ci DEFAULT NULL,
	`created_at`	timestamp COLLATE utf8_czech_ci NOT NULL,
	`edited_at`	varchar(30) COLLATE utf8_czech_ci NOT NULL, 
	PRIMARY KEY (`invitation_id`),
	FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	UNIQUE KEY `event_id` (`event_id`) USING BTREE
) AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;

-- ----------------------------
-- Záznamy INVITATIONS
-- ----------------------------
INSERT INTO `invitations` VALUES ('1', 'film-fest', '<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean fermentum risus id tortor. Ut enim ad min</p>
<p>Cras pede libero, dapibus nec, pretium sit amet, tempor quis. Pellentesque sapien. Maecenas aliquet accumsan leo.</p>', 1, now(), now());
INSERT INTO `invitations` VALUES ('2', 'workshop', '<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean fermentum risus id tortor. Ut enim ad min</p>
<p>Cras pede libero, dapibus nec, pretium sit amet, tempor quis. Pellentesque sapien. Maecenas aliquet accumsan leo.</p>', 2, now(), now());

-- ----------------------------
-- Tabulka ADRESS
-- ----------------------------
DROP TABLE IF EXISTS `adress`;
CREATE TABLE `adress` (
	`adress_id`  int(11) NOT NULL AUTO_INCREMENT,
	`name`      varchar(50) COLLATE utf8_czech_ci DEFAULT NULL,
	`event_id` 	int(11) COLLATE utf8_czech_ci DEFAULT NULL,
	PRIMARY KEY (`adress_id`),
	FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	UNIQUE KEY `event_id` (`event_id`) USING BTREE
) AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;

-- ----------------------------
-- Záznamy ADRESS
-- ----------------------------
INSERT INTO `adress` VALUES ('1', 'Film-fest', 1);
INSERT INTO `adress` VALUES ('2', 'WorkShop', 2);

-- ----------------------------
-- Tabulka CONTACTS
-- ----------------------------
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
	`contact_id`  int(11) NOT NULL AUTO_INCREMENT,
	`mail`      varchar(80) COLLATE utf8_czech_ci DEFAULT NULL,
	`name`      varchar(80) COLLATE utf8_czech_ci DEFAULT NULL,
	`token`      varchar(80) COLLATE utf8_czech_ci DEFAULT NULL,
	`active`      tinyint COLLATE utf8_czech_ci DEFAULT 1,
	`created_at`	timestamp COLLATE utf8_czech_ci NOT NULL,
	`adress_id` 	int(11) COLLATE utf8_czech_ci DEFAULT NULL,
	`salt` 	varchar(30) COLLATE utf8_czech_ci DEFAULT NULL,
	PRIMARY KEY (`contact_id`),
	FOREIGN KEY (`adress_id`) REFERENCES `adress` (`adress_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	UNIQUE KEY `salt` (`salt`) USING BTREE
) AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;
