CREATE SCHEMA `Trade`;

CREATE TABLE `Trade`.`Role`(
	RoleID INT PRIMARY KEY,
    RoleName VARCHAR(100) NOT NULL
);

CREATE TABLE `Trade`.`User`(
	UserID INT PRIMARY KEY,
	UserSurname VARCHAR(100) NOT NULL,
	UserName VARCHAR(100) NOT NULL,
	UserPatronymic VARCHAR(100) NOT NULL,
	UserLogin TEXT NOT NULL,
	UserPassword TEXT NOT NULL,
	UserRole INT NOT NULL.
    CONSTRAINT `user_fk_1` FOREIGN KEY (`UserRole`) REFERENCES `Trade`.`Role` (`RoleID`)
);

CREATE TABLE `Trade`.`Order`
(
	OrderID INT PRIMARY KEY AUTO_INCREMENT,
	OrderStatus TEXT NOT NULL,
	OrderDeliveryDate DATETIME NOT NULL,
	OrderPickupPoint TEXT NOT NULL
);

CREATE TABLE `Trade`.`Product`(
	ProductArticleNumber VARCHAR(100) primary key,
	ProductName TEXT not null,
	ProductDescription TEXT not null,
	ProductCategory TEXT not null,
	ProductPhoto blob not null,
	ProductManufacturer TEXT not null,
	ProductCost decimal(19,4) not null,
	ProductDiscountAmount tinyint null,
	ProductQuantityInStock int not null,
	ProductStatus TEXT not null
);

CREATE TABLE `Trade`.`OrderProduct`(
	OrderID INT NOT NULL,
	ProductArticleNumber VARCHAR(100) NOT NULL,
    CONSTRAINT `orderproduct_fk_1` FOREIGN KEY (`OrderID`) REFERENCES `Trade`.`Order` (`OrderID`),
    CONSTRAINT `orderproduct_fk_2` FOREIGN KEY (`ProductArticleNumber`) REFERENCES `Trade`.`Product` (`ProductArticleNumber`)
);

ALTER TABLE `Trade`.`Product`
	CHANGE COLUMN `ProductStatus` `ProductStatus` TEXT NULL DEFAULT NULL,
    CHANGE COLUMN `ProductPhoto` `ProductPhoto` TEXT NULL,
	ADD COLUMN `ProductUnit` VARCHAR(50),
    ADD COLUMN `ProductMaxDiscountAmount` TINYINT,
    ADD COLUMN `ProductSupplier` VARCHAR(50),
    ADD COLUMN `ProductPerPack` INT NOT NULL,
    ADD COLUMN `ProductMinCount` INT NOT NULL;

CREATE TABLE `Trade`.`PickupPoint` (
  `pp_id` INT NOT NULL AUTO_INCREMENT,
  `pp_address` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`pp_id`));

ALTER TABLE `Trade`.`Order`
CHANGE COLUMN `OrderPickupPoint` `OrderPickupPoint` INT NOT NULL,
ADD COLUMN `OrderDate` DATETIME NOT NULL AFTER `OrderPickupPoint`,
ADD COLUMN `OrderClientFullName` TEXT NULL AFTER `OrderDate`,
ADD COLUMN `OrderReceiptCode` INT NOT NULL AFTER `OrderClientFullName`,
ADD CONSTRAINT `order_fk_1` FOREIGN KEY (`OrderPickupPoint`) REFERENCES `Trade`.`PickupPoint` (`pp_id`);