# ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# http://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: localhost (MySQL 5.7.21)
# Database: chess
# Generation Time: 2018-08-26 11:34:04 +0000
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


# Dump of table avatars
# ------------------------------------------------------------

DROP TABLE IF EXISTS `avatars`;

CREATE TABLE `avatars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `avatars` WRITE;
/*!40000 ALTER TABLE `avatars` DISABLE KEYS */;

INSERT INTO `avatars` (`id`, `url`)
VALUES
    (1,'bear.svg'),
    (2,'bird.svg'),
    (3,'boar.svg'),
    (4,'cat.svg'),
    (5,'chicken (1).svg'),
    (6,'chicken.svg'),
    (7,'cow.svg'),
    (8,'deer.svg'),
    (9,'dog.svg'),
    (10,'fox.svg'),
    (11,'frog.svg'),
    (12,'giraffe.svg'),
    (13,'gorilla.svg'),
    (14,'hamster.svg'),
    (15,'hen.svg'),
    (16,'koala.svg'),
    (17,'lion.svg'),
    (18,'monkey.svg'),
    (19,'mouse.svg'),
    (20,'owl.svg'),
    (21,'panda.svg'),
    (22,'pig.svg'),
    (23,'rabbit.svg'),
    (24,'tiger.svg'),
    (25,'wolf.svg'),
    (26,'zebra.svg');

/*!40000 ALTER TABLE `avatars` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table games
# ------------------------------------------------------------

DROP TABLE IF EXISTS `games`;

CREATE TABLE `games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `whitePlayerId` int(11) DEFAULT NULL,
  `blackPlayerId` int(11) DEFAULT NULL,
  `result` enum('b','w','d') DEFAULT NULL,
  `fen` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `whiteUserId` (`whitePlayerId`),
  KEY `blackUserId` (`blackPlayerId`),
  CONSTRAINT `games_ibfk_1` FOREIGN KEY (`whitePlayerId`) REFERENCES `users` (`id`),
  CONSTRAINT `games_ibfk_2` FOREIGN KEY (`blackPlayerId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `games` WRITE;
/*!40000 ALTER TABLE `games` DISABLE KEYS */;

INSERT INTO `games` (`id`, `whitePlayerId`, `blackPlayerId`, `result`, `fen`)
VALUES
    (10,30,29,'b','rnb1kbnr/pppp1ppp/8/4p3/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
    (11,29,30,'b','rnb1kbnr/pppp1ppp/8/4p3/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
    (12,29,30,NULL,NULL),
    (13,29,30,'b','rnb1kbnr/ppp2ppp/4p3/8/3p2Pq/3P1P2/PPPBP2P/RN1QKBNR w KQkq - 1 5'),
    (14,32,29,'b','rnb1kbnr/pp3ppp/2p1p3/3p4/3P2Pq/5P2/PPPBP2P/RN1QKBNR w KQkq - 1 5'),
    (15,29,32,'b','rnb1kbnr/pppp1ppp/4p3/8/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
    (16,32,29,NULL,NULL),
    (17,34,29,'b','rnb1kbnr/pppp1ppp/4p3/8/6Pq/5P2/PPPPP2P/RNBQKBNR w KQkq - 1 3'),
    (18,34,29,NULL,NULL);

/*!40000 ALTER TABLE `games` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table games_stats
# ------------------------------------------------------------

DROP VIEW IF EXISTS `games_stats`;

CREATE TABLE `games_stats` (
   `id` INT(11) NOT NULL DEFAULT '0',
   `result` ENUM('b','w','d') NULL DEFAULT NULL,
   `whitePlayerId` INT(11) NOT NULL DEFAULT '0',
   `blackPlayerId` INT(11) NOT NULL DEFAULT '0',
   `whitePlayerEmail` VARCHAR(64) NOT NULL,
   `blackPlayerEmail` VARCHAR(64) NOT NULL
) ENGINE=MyISAM;



# Dump of table users
# ------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(64) NOT NULL,
  `salt` varchar(256) NOT NULL,
  `hashedPassword` char(64) NOT NULL DEFAULT '',
  `roles` varchar(256) DEFAULT 'user',
  `avatarUrl` varchar(256) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;

INSERT INTO `users` (`id`, `email`, `salt`, `hashedPassword`, `roles`, `avatarUrl`)
VALUES
    (29,'ivan@asd.asd','2zdXaEXPN80RF0MVkbf/SANShTCCHi9Q4sYeG1svZ1xauhWGEv0L+MvmXQ8etJa8fEaJU237ysbXVnfgxER/cjfD/27gkkQC7OXJbDvgzllHpwQ0FqHf6f3WzyLN7P81u+VyUf3K0f5pJZHeTFrp9Fm4ct8ppHcdJxWi96z0pcI=','0058f68df8f84f72aa8462ff0a28cef742102c5d035745b4df7b6533589339e1','user admin','deer.svg'),
    (30,'asdd@asd.asd','RApCtig0CPTCubdN/CawEcTrUVTDZNvHIOSxjhHp+AGS/S1h9O87Qg3ub9LCClcaVou8jFKO10rFRX4clzgaMA53hoGNS1PN1UZBcOJhtsfP+Btel9NloTe1jPYRSJKQn5HikVRGUU7dROlNPXQwyQFTbuL3DVoO0auvI1Dr5VE=','df42744c429fcc68b49c3391dcb5049701c4b487311aa4cce37a2daaad01e1e0','user admin','bird.svg'),
    (32,'asd@asd.asd','9VY6nNEQF6zax53HB7fmY7khR876+bH+H4Xu7xYWXZdOXMAo6JZJ4rkKAhmOld2Qz+3v2KnZ+Lccf4QgZ+K18IZOLkH6AzdxWY9FwmPm6GpM0ei7W+F0EfZbBnNil46mo6qTP5AiHJjRMxd2O33GmtdOzEPVwfTETjEKVadI2ec=','133e9d8ae6be910b21af142ad3825661157ebc195acd863aa29c15404cfcf3fb','user admin','cow.svg'),
    (33,'asddddd@asd.asd','Lpu/wlr75CEYc4q6DeP4eL/kWsmF9+bVkZ0Imsww8VoULpd+y1XwpktVmeF43m32nVlRRGYfxmQ8hGeuIFT0gmwxfECN4EdUfe6AWULx1iiK5DXnPTx87Wp48nIuQvUpp2bN3KdhLpJfFrH7gdiZewHVHif90DiQW4KGFio5O0g=','cb81f9d3c68b9a1915b824955562a09eabe8fd9c5ddbd367c4357f22c0fc163d','user admin','cat.svg'),
    (34,'asddddd@asssd.asd','izrc4N31sNA6t6lOoCpDiCwhoMKuVVm5nuk3sJFx7uXPWELqrOSFin8yp8dsEOB8igyk/FEQBw9D42+40bpdOedCljFIPgvEA0s4/QaNnqPnK2j8XfZMIqjKs5jBJTLsMTs6/Grl0Ms1Pp8mFq/WNXWRmaU+sTLS1htZEDKR/6Q=','19505c5bd0858f1ffed8f476407f4c07e8c6a06f35178e8e2d42a16b4afa631b','user','hamster.svg');

/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `set_avatar` BEFORE INSERT ON `users` FOR EACH ROW SET NEW.avatarUrl = (SELECT a.url FROM avatars a
ORDER BY RAND() LIMIT 1) */;;
/*!50003 SET SESSION SQL_MODE="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `delete_games` BEFORE DELETE ON `users` FOR EACH ROW DELETE FROM games WHERE games.whitePlayerId = OLD.id OR games.blackPlayerId = OLD.id */;;
DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;


# Dump of table users_stats
# ------------------------------------------------------------

DROP VIEW IF EXISTS `users_stats`;

CREATE TABLE `users_stats` (
   `id` INT(11) NOT NULL DEFAULT '0',
   `email` VARCHAR(64) NOT NULL,
   `salt` VARCHAR(256) NOT NULL,
   `roles` VARCHAR(256) NULL DEFAULT 'user',
   `hashedPassword` CHAR(64) NOT NULL DEFAULT '',
   `avatarUrl` VARCHAR(256) NULL DEFAULT ''
) ENGINE=MyISAM;





# Replace placeholder table for games_stats with correct view syntax
# ------------------------------------------------------------

DROP TABLE `games_stats`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `games_stats`
AS SELECT
   `g`.`id` AS `id`,
   `g`.`result` AS `result`,
   `uw`.`id` AS `whitePlayerId`,
   `ub`.`id` AS `blackPlayerId`,
   `uw`.`email` AS `whitePlayerEmail`,
   `ub`.`email` AS `blackPlayerEmail`
FROM ((`games` `g` join `users` `uw` on((`uw`.`id` = `g`.`whitePlayerId`))) join `users` `ub` on((`ub`.`id` = `g`.`blackPlayerId`)));


# Replace placeholder table for users_stats with correct view syntax
# ------------------------------------------------------------

DROP TABLE `users_stats`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `users_stats`
AS SELECT
   `u`.`id` AS `id`,
   `u`.`email` AS `email`,
   `u`.`salt` AS `salt`,
   `u`.`roles` AS `roles`,
   `u`.`hashedPassword` AS `hashedPassword`,
   `u`.`avatarUrl` AS `avatarUrl`
FROM `users` `u`;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;