/*
SQLyog Community v12.0 (64 bit)
MySQL - 5.5.39-log : Database - mysqladmin
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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 */;
/*Table structure for table `comments` */

DROP TABLE IF EXISTS `comments`;

CREATE TABLE `comments` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `itemId` int(11) NOT NULL,
  `comment` text,
  `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `userId_idx` (`userId`),
  KEY `itemId_idx` (`itemId`),
  CONSTRAINT `itemId` FOREIGN KEY (`itemId`) REFERENCES `items` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `userId` FOREIGN KEY (`userId`) REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8;

/*Data for the table `comments` */

insert  into `comments`(`Id`,`userId`,`itemId`,`comment`,`updated`) values (1,51,1,'OMG, Everything is awesome!','2014-08-15 00:15:28'),(11,51,1,'Awesome is everything!','2014-08-15 00:19:35'),(21,61,1,'No way, I so love everything!','2014-08-15 00:20:49'),(31,61,1,'I agree, it&#39;s all awesome.','2014-08-15 00:21:09'),(41,61,1,'LOL, I remember the song. I can&#39;t get it out of my head.','2014-08-15 00:22:32'),(61,61,61,'These are so not awesome. I hate the itchy eyes and runny nose.','2014-08-15 20:26:18');

/*Table structure for table `items` */

DROP TABLE IF EXISTS `items`;

CREATE TABLE `items` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) NOT NULL,
  `ImageUrl` varchar(128) DEFAULT NULL,
  `createdBy` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `createdBy_idx` (`createdBy`),
  CONSTRAINT `createdBy` FOREIGN KEY (`createdBy`) REFERENCES `users` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8;

/*Data for the table `items` */

insert  into `items`(`ID`,`Name`,`Upvotes`,`Downvotes`,`Ratio`,`ImageUrl`,`createdBy`) values (1,'Everything',113,0,1.0000,'/images/lehi.jpg',61),(11,'Blue Skies',40,2,0.9524,'/images/blueskies.jpg',61),(21,'Bouncy Springs',48,2,0.9600,'/images/bouncysprings.jpg',61),(31,'Nobel Prize',85,7,0.9239,'/images/NobelPrize.jpg',61),(41,'Piece of String',4,10,0.2857,'/images/pieceofstring.jpg',61),(51,'Dogs and Fleas',10,42,0.1923,'/images/dogsfleas.jpg',61),(61,'Allergies',1,61,0.0161,'/images/allergies.jpg',61),(71,'Rocks',10,10,0.5000,'/images/rocks.jpg',61),(81,'Clocks',15,5,0.7500,'/images/clocks.jpg',61),(91,'Socks',5,5,0.5000,'/images/socks.jpg',61),(101,'General Conference',90,0,1.0000,'/images/conference.jpg',61),(111,'Scriptures',93,0,1.0000,'/images/scriptures.jpg',61),(151,'Kittens',121,0,1.0000,'http://placekitten.com/g/300/200',51);

/*Table structure for table `roles` */

DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `roles` */

insert  into `roles`(`Id`,`name`,`description`) values (1,'User','A Regular User'),(2,'Admin','An Admin User');

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `email` varchar(128) NOT NULL,
  `passwordhash` varchar(128) NOT NULL,
  `roleId` int(11) DEFAULT '0',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `username_UNIQUE` (`email`),
  KEY `roleId_idx` (`roleId`),
  CONSTRAINT `roleId` FOREIGN KEY (`roleId`) REFERENCES `roles` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=utf8;

/*Data for the table `users` */

insert  into `users`(`Id`,`firstname`,`lastname`,`email`,`passwordhash`,`roleId`) values (51,'Mike','Neville','admin@test.com','3fca4782a2c69ea87319e4592f3e6676cd308688',2),(61,'Jane','Doe','user@test.com','4f1254dba8bce30552a77023f65cf713f509343a',1);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
