CREATE DATABASE  IF NOT EXISTS `TShirt` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `TShirt`;
--
-- Host: 127.0.0.1    Database: TShirt
-- ------------------------------------------------------


--
-- Table structure for table `OrderLines`
--

CREATE TABLE `OrderLines` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Orders_ID` int(11) NOT NULL,
  `SKU` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Orders_ID` (`Orders_ID`),
  CONSTRAINT `orderlines_ibfk_1` FOREIGN KEY (`Orders_ID`) REFERENCES `Orders` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `OrderLines`
--

INSERT INTO `OrderLines` VALUES (5,1,2004),(9,1,2005),(11,2,2003),(12,3,2010),(13,4,1007),(14,4,1008),(15,4,1009),(16,4,1010),(17,4,2010),(21,4,2010);

--
-- Table structure for table `Orders`
--

CREATE TABLE `Orders` (
  `ID` int(45) NOT NULL AUTO_INCREMENT,
  `User_ID` varchar(20) NOT NULL,
  `OrderDateandTime` datetime NOT NULL,
  `Total` decimal(5,2) NOT NULL,
  `ShippingAddress` varchar(255) NOT NULL DEFAULT '',
  `BillingAddress` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `User_ID` (`User_ID`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`User_ID`) REFERENCES `Users` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Orders`
--

INSERT INTO `Orders` VALUES (1,'dragmedaddy','2016-08-24 19:05:47',50.00,'656 Raphael Ct. Baltimore, MD, 55447','656 Raphael Ct. Baltimore, MD, 55447'),(2,'kappakeepo','2016-08-30 08:15:10',25.00,'78 Ali St. Bushwick, NY, 11237','78 Ali St. Bushwick, NY, 11237'),(3,'steamedhams','2017-01-30 10:25:37',35.00,'12 Knickerbocker Brooklyn, NY, 11206','12 Knickerbocker Brooklyn, NY, 11206'),(4,'kateisgr8','2017-02-15 09:10:30',170.00,'Cow Dr. Howell, MI, 48430','Cow Dr. Howell, MI, 48430'),(10,'kateisgr8','2016-12-30 21:49:05',0.00,'Cow Dr. Howell, MI, 48430','Cow Dr. Howell, MI, 48430');

--
-- Table structure for table `Shirts`
--

CREATE TABLE `Shirts` (
  `SKU` int(11) NOT NULL,
  `Name` varchar(20) DEFAULT NULL,
  `Price` decimal(5,2) DEFAULT NULL,
  `Qtyinstock` tinyint(2) DEFAULT NULL,
  `Color` varchar(20) DEFAULT NULL,
  `Style` varchar(20) DEFAULT NULL,
  `Size` char(2) DEFAULT NULL,
  `Sex` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`SKU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Shirts`
--

INSERT INTO `Shirts` VALUES (1006,'Detox',25.00,5,'red','Busted','XS','M'),(1007,'Alaska',25.00,5,'lavender','Diva','S','M'),(1008,'Tatiana',25.00,5,'gold','Hussy','S','M'),(1009,'Carmen',25.00,1,'white','Hussy','XL','W'),(1010,'KimChi',25.00,1,'pink','Diva','XL','M'),(1030,'Willam',35.00,2,'scarlet','Hussy','XS','W'),(2001,'Sharon',25.00,5,'purple','Hoochie','S','M'),(2002,'Trixie',25.00,5,'pink','Hunty','M','M'),(2003,'Katya',25.00,5,'purple','Hunty','S','W'),(2004,'Latrice',25.00,5,'purple','Fishy','L','N'),(2005,'PhiPhi',25.00,5,'turquoise','Hoochie','XL','M'),(2007,'Detox',25.00,10,'purple','Busted','XS','N'),(2008,'Detox',25.00,5,'gold','Busted','S','N'),(2009,'Carmen',25.00,4,'pink','Hussy','M','M'),(2010,'KimChi',35.00,3,'turquoise','Diva','L','W');

--
-- Table structure for table `Users`
--

CREATE TABLE `Users` (
  `ID` varchar(20) NOT NULL DEFAULT '',
  `FirstName` varchar(20) DEFAULT NULL,
  `LastName` varchar(30) DEFAULT NULL,
  `Email` varchar(30) DEFAULT NULL,
  `Password` varchar(16) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `Users`
--

INSERT INTO `Users` VALUES ('dragmedaddy','Eric','Zhang','ericzz@gmail.com','screwyou810','3016789696'),('ihavegreenhair','Seto','Kaiba','money@gmail.com','ihateyugi','5558675310'),('kappakeepo','John','Lemay','johnrocks@gmail.com','i<3k8lol','8102348901'),('kateisgr8','Kate','Lemay','klemay@gmail.com','dogs4luv','6033915088'),('monstercard','Yugi','Muto','grandpa@gmail.com','whatever','5558675308'),('steamedhams','David','Zhang','davidz@gmail.com','dentalplan22','3016785454'),('whatisinaname','Paul','Schnauzer','paulisdead@gmail.com','wearegoing2moon','5558675309');

--
-- Temporary view structure for view `menslowhigh`
--

/*!50001 DROP VIEW IF EXISTS `menslowhigh`*/;
/*!50001 CREATE VIEW `menslowhigh` AS SELECT 
 1 AS `SKU`,
 1 AS `Name`,
 1 AS `Price`,
 1 AS `Qtyinstock`,
 1 AS `Color`,
 1 AS `Style`,
 1 AS `Size`,
 1 AS `Sex`*/;

--
-- Temporary view structure for view `usersbyname`
--

/*!50001 DROP VIEW IF EXISTS `usersbyname`*/;
/*!50001 CREATE VIEW `usersbyname` AS SELECT 
 1 AS `ID`,
 1 AS `FirstName`,
 1 AS `LastName`,
 1 AS `Email`,
 1 AS `Password`,
 1 AS `Phone`*/;

--
-- Temporary view structure for view `womenslowhigh`
--

/*!50001 DROP VIEW IF EXISTS `womenslowhigh`*/;
/*!50001 CREATE VIEW `womenslowhigh` AS SELECT 
 1 AS `SKU`,
 1 AS `Name`,
 1 AS `Price`,
 1 AS `Qtyinstock`,
 1 AS `Color`,
 1 AS `Style`,
 1 AS `Size`,
 1 AS `Sex`*/;

--
-- Dumping events for database 'TShirt'
--

--
-- Dumping routines for database 'TShirt'
--
/*!50003 DROP FUNCTION IF EXISTS `CheckPassword` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `CheckPassword`(Username VARCHAR(20), Password VARCHAR(16)) RETURNS int(1)
    DETERMINISTIC
BEGIN
    DECLARE FOUND INT(1);
    
    SET FOUND = (SELECT COUNT(*) > 0 from Users where Users.`ID` = Username and Users.`Password` = Password);
    
    RETURN FOUND;

END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `NumberProducts` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `NumberProducts`(CATEGORY VARCHAR(2)) RETURNS int(10)
    DETERMINISTIC
BEGIN
    DECLARE ProductCount INT(10);
    
    SET ProductCount = (SELECT COUNT(*) from Shirts where Shirts.`Sex` = CATEGORY);
    
    RETURN ProductCount;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `AddOrder` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddOrder`(in User_ID varchar(20), ShippingAddress varchar(255), BillingAddress varchar(255))
BEGIN
 
 INSERT INTO `Orders` (`User_ID`, `OrderDateandTime`, `Total`,`ShippingAddress`,`BillingAddress`)
 VALUES (User_ID, NOW(), 0, ShippingAddress, BillingAddress);
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `NameandPrice` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `NameandPrice`(in CATEGORY varchar(20))
BEGIN 

Select Name, Price FROM Shirts Where Sex = CATEGORY;

End ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ProductDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ProductDetails`(in SKU int(11))
BEGIN 

Select Name, Price, Color, Style, Size, Sex from Shirts Where `Shirts`.`SKU` = SKU;

End ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RecordPurchases` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RecordPurchases`(in `Orders_ID` int(11), `SKU` int(11))
BEGIN
 
 INSERT INTO `OrderLines` (`Orders_ID`, `SKU`)
 VALUES (Orders_ID, SKU);
 
 UPDATE `Orders`
 SET Orders.Total=Orders.Total+(select `Price` from `Shirts` where Shirts.SKU=SKU) 
 WHERE ID = Orders_ID;
 
 
 END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RegisterUser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RegisterUser`(in ID varchar(20), in FirstName varchar(20), in LastName varchar(30), in Email varchar(30), in Password varchar(16), in Phone varchar(20))
BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
          ROLLBACK;
    END;

    START TRANSACTION;

  	INSERT INTO `Users` (`ID`,`FirstName`, `LastName`, `Email`, `Password`, `Phone`)
  	VALUES (ID, FirstName, LastName, Email, Password, Phone);

    COMMIT;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdatePassword` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdatePassword`(in `Password` varchar(16), in `ID` varchar(20))
BEGIN
    
START TRANSACTION;  

UPDATE `Users`

SET Users.Password = Password

WHERE Users.ID = ID;
 
COMMIT;
 
END ;;
DELIMITER ;

--
-- Final view structure for view `menslowhigh`
--

/*!50001 DROP VIEW IF EXISTS `menslowhigh`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `menslowhigh` AS select `shirts`.`SKU` AS `SKU`,`shirts`.`Name` AS `Name`,`shirts`.`Price` AS `Price`,`shirts`.`Qtyinstock` AS `Qtyinstock`,`shirts`.`Color` AS `Color`,`shirts`.`Style` AS `Style`,`shirts`.`Size` AS `Size`,`shirts`.`Sex` AS `Sex` from `shirts` where (`shirts`.`Sex` in ('M','N')) order by 'Price' */;

--
-- Final view structure for view `usersbyname`
--

/*!50001 DROP VIEW IF EXISTS `usersbyname`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `usersbyname` AS select `users`.`ID` AS `ID`,`users`.`FirstName` AS `FirstName`,`users`.`LastName` AS `LastName`,`users`.`Email` AS `Email`,`users`.`Password` AS `Password`,`users`.`Phone` AS `Phone` from `users` order by `users`.`LastName` desc,`users`.`FirstName` desc */;

--
-- Final view structure for view `womenslowhigh`
--

/*!50001 DROP VIEW IF EXISTS `womenslowhigh`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `womenslowhigh` AS select `shirts`.`SKU` AS `SKU`,`shirts`.`Name` AS `Name`,`shirts`.`Price` AS `Price`,`shirts`.`Qtyinstock` AS `Qtyinstock`,`shirts`.`Color` AS `Color`,`shirts`.`Style` AS `Style`,`shirts`.`Size` AS `Size`,`shirts`.`Sex` AS `Sex` from `shirts` where (`shirts`.`Sex` in ('W','N')) order by 'Price' */;


