---

CREATE DATABASE IF NOT EXISTS `bicycle_store_db` 
    DEFAULT CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

USE `bicycle_store_db`;

DROP TABLE IF EXISTS `OrderProduct`;
DROP TABLE IF EXISTS `Order`;
DROP TABLE IF EXISTS `Product`;
DROP TABLE IF EXISTS `User`;
DROP TABLE IF EXISTS `Role`;
DROP TABLE IF EXISTS `OrderStatus`;
DROP TABLE IF EXISTS `PickupPoint`;
DROP TABLE IF EXISTS `Category`;
DROP TABLE IF EXISTS `Manufacturer`;
DROP TABLE IF EXISTS `Supplier`;

CREATE TABLE `Role` (
    `RoleID` INT AUTO_INCREMENT PRIMARY KEY,
    `RoleName` VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `User` (
    `UserID` INT AUTO_INCREMENT PRIMARY KEY,
    `UserSurname` VARCHAR(100) NOT NULL,
    `UserName` VARCHAR(100) NOT NULL,
    `UserPatronymic` VARCHAR(100) NULL,
    `UserLogin` VARCHAR(100) NOT NULL UNIQUE,
    `UserPassword` VARCHAR(255) NOT NULL,
    `RoleID` INT NOT NULL,
    CONSTRAINT `FK_User_Role` FOREIGN KEY (`RoleID`) REFERENCES `Role` (`RoleID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Category` (
    `CategoryID` INT AUTO_INCREMENT PRIMARY KEY,
    `CategoryName` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Manufacturer` (
    `ManufacturerID` INT AUTO_INCREMENT PRIMARY KEY,
    `ManufacturerName` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Supplier` (
    `SupplierID` INT AUTO_INCREMENT PRIMARY KEY,
    `SupplierName` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Product` (
    `ProductID` INT AUTO_INCREMENT PRIMARY KEY,
    `ProductArticle` VARCHAR(50) NULL UNIQUE,
    `ProductName` VARCHAR(200) NOT NULL,
    `ProductDescription` TEXT NULL,
    `CategoryID` INT NOT NULL,
    `ManufacturerID` INT NOT NULL,
    `SupplierID` INT NOT NULL,
    `ProductCost` DECIMAL(10, 2) NOT NULL CHECK (`ProductCost` >= 0),
    `ProductUnit` VARCHAR(20) NOT NULL DEFAULT 'шт.',
    `ProductDiscountAmount` DECIMAL(5, 2) NOT NULL DEFAULT 0.00 CHECK (`ProductDiscountAmount` >= 0 AND `ProductDiscountAmount` <= 100),
    `ProductQuantityInStock` INT NOT NULL CHECK (`ProductQuantityInStock` >= 0),
    `ProductPhotoPath` VARCHAR(255) NULL,
    CONSTRAINT `FK_Product_Category` FOREIGN KEY (`CategoryID`) REFERENCES `Category` (`CategoryID`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `FK_Product_Manufacturer` FOREIGN KEY (`ManufacturerID`) REFERENCES `Manufacturer` (`ManufacturerID`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `FK_Product_Supplier` FOREIGN KEY (`SupplierID`) REFERENCES `Supplier` (`SupplierID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `OrderStatus` (
    `StatusID` INT AUTO_INCREMENT PRIMARY KEY,
    `StatusName` VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `PickupPoint` (
    `PickupPointID` INT AUTO_INCREMENT PRIMARY KEY,
    `Address` VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Order` (
    `OrderID` INT AUTO_INCREMENT PRIMARY KEY,
    `OrderArticle` VARCHAR(50) NOT NULL UNIQUE,
    `OrderDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `OrderDeliveryDate` DATETIME NULL,
    `PickupPointID` INT NOT NULL,
    `StatusID` INT NOT NULL,
    `UserID` INT NULL,
    CONSTRAINT `FK_Order_PickupPoint` FOREIGN KEY (`PickupPointID`) REFERENCES `PickupPoint` (`PickupPointID`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `FK_Order_Status` FOREIGN KEY (`StatusID`) REFERENCES `OrderStatus` (`StatusID`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `FK_Order_User` FOREIGN KEY (`UserID`) REFERENCES `User` (`UserID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `OrderProduct` (
    `OrderID` INT NOT NULL,
    `ProductID` INT NOT NULL,
    `Count` INT NOT NULL DEFAULT 1 CHECK (`Count` > 0),
    PRIMARY KEY (`OrderID`, `ProductID`),
    CONSTRAINT `FK_OrderProduct_Order` FOREIGN KEY (`OrderID`) REFERENCES `Order` (`OrderID`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_OrderProduct_Product` FOREIGN KEY (`ProductID`) REFERENCES `Product` (`ProductID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;