DROP TABLE IF EXISTS `OrderLines`;
DROP TABLE IF EXISTS `Orders`;
DROP TABLE IF EXISTS `People`;
DROP TABLE IF EXISTS `SpecificOrderLines`;
DROP TABLE IF EXISTS `SpecificOrders`;
DROP TABLE IF EXISTS `SpecificPeople`;
DROP TABLE IF EXISTS `TransactionLogs`;
DROP TABLE IF EXISTS `Note`;

CREATE TABLE `People` (
	`Id` VARCHAR(36) NOT NULL PRIMARY KEY, 
	`FullName` VARCHAR(255),
	`Age` BIGINT NOT NULL,
	`Height` INT NOT NULL,
	`Dob` DATETIME NULL
) ENGINE=INNODB;

CREATE TABLE `Orders` (
	`Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`PersonId` VARCHAR(36) NOT NULL,
	`PoNumber` VARCHAR(15) NOT NULL,
	`OrderStatus` INT NOT NULL,
	`CreatedOn` DATETIME NOT NULL,
	`CreatedBy` VARCHAR(255) NOT NULL,
	FOREIGN KEY `FK_Orders_People`(`PersonId`) REFERENCES `People`(`Id`)
) ENGINE=INNODB;

CREATE TABLE `OrderLines` (
	`Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`OrderId` INT NOT NULL,
	`Qty` SMALLINT NOT NULL,
	`Status` SMALLINT NOT NULL,
	`SellPrice` DECIMAL(10, 4) NOT NULL,
	FOREIGN KEY `FK_OrderLines_Orders`(`OrderId`) REFERENCES `Orders`(`Id`)
) ENGINE=INNODB;

CREATE TABLE `SpecificPeople` (
	`Id` VARCHAR(36) NOT NULL PRIMARY KEY, 
	`FullName` VARCHAR(255),
	`Age` BIGINT NOT NULL,
	`Height` INT NOT NULL,
	`Dob` DATETIME NULL
) ENGINE=INNODB;

CREATE TABLE `SpecificOrders` (
	`Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`PersonId` VARCHAR(36) NOT NULL,
	`PoNumber` VARCHAR(15) NOT NULL,
	`OrderStatus` INT NOT NULL,
	`CreatedOn` DATETIME NOT NULL,
	`CreatedBy` VARCHAR(255) NOT NULL,
	FOREIGN KEY `FK_SpecificOrders_SpecificPeople`(`PersonId`) REFERENCES `SpecificPeople`(`Id`)
) ENGINE=INNODB;

CREATE TABLE `SpecificOrderLines` (
	`Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`OrderId` INT NOT NULL,
	`Qty` SMALLINT NOT NULL,
	`Status` SMALLINT NOT NULL,
	`SellPrice` DECIMAL(10, 4) NOT NULL,
	FOREIGN KEY FK_SpecificOrderLines_SpecificOrders(OrderId) REFERENCES SpecificOrders(Id)
) ENGINE=INNODB;

CREATE TABLE `TransactionLogs` (
	`Description` VARCHAR(5000) NOT NULL,
	`CreatedOn` DATETIME NOT NULL
) ENGINE=INNODB;

CREATE TABLE `Note` (
	`Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`Text` VARCHAR(5000) NOT NULL,
	`CreatedOn` DATETIME NOT NULL
) ENGINE=INNODB;

-- Investigation Tables

DROP TABLE IF EXISTS `BugInvestigation_10R9LZYK`;

CREATE TABLE `BugInvestigation_10R9LZYK` (
	`Id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`TestColumn1` VARBINARY(32)
) ENGINE=INNODB;

-- Stored Procedures
DROP PROCEDURE IF EXISTS `SelectPeople`;
DROP PROCEDURE IF EXISTS `SelectPeopleWithParam`;
DROP PROCEDURE IF EXISTS `CountPeople`;
DROP PROCEDURE IF EXISTS `CountPeopleWithParam`;
DROP PROCEDURE IF EXISTS `UpdatePeople`;
DROP PROCEDURE IF EXISTS `UpdatePeopleWithParam`;


CREATE PROCEDURE `SelectPeople` ()
	SELECT * FROM `People`;

CREATE PROCEDURE `SelectPeopleWithParam` (age INT)
	SELECT * FROM `People` WHERE `People`.`Age` > age;

CREATE PROCEDURE `CountPeople` () 
	SELECT COUNT(*) FROM `People`;

CREATE PROCEDURE `CountPeopleWithParam` (age INT)
	SELECT COUNT(*) FROM `People` WHERE `People`.`Age` > age;

CREATE PROCEDURE `UpdatePeople` () 
	UPDATE `People` SET `FullName` = 'Updated'; 

CREATE PROCEDURE `UpdatePeopleWithParam` (age INT)
	UPDATE `People` SET `FullName` = 'Updated' WHERE `People`.`Age` > age;