---

CREATE TABLE Roles (
Role_ID INT IDENTITY(1,1) PRIMARY KEY,
RoleName VARCHAR(50) NOT NULL UNIQUE,
);

CREATE TABLE Categories (
Category_ID INT IDENTITY(1,1) PRIMARY KEY,
CategoryName VARCHAR(100) NOT NULL UNIQUE,
);

CREATE TABLE Manufacturers (
Manufacturer_ID INT IDENTITY(1,1) PRIMARY KEY,
ManufacturerName VARCHAR(100) NOT NULL UNIQUE,
);

CREATE TABLE Suppliers (
Supplier_ID INT IDENTITY(1,1) PRIMARY KEY,
SupplierName VARCHAR(100) NOT NULL UNIQUE,
);

CREATE TABLE Users (
User_ID INT IDENTITY(1,1) PRIMARY KEY,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
MiddleName VARCHAR(50)  NULL,
Login VARCHAR(50) NOT NULL UNIQUE,
Password VARCHAR(255) NOT NULL,
Role_ID INT NOT NULL,
FOREIGN KEY (Role_ID) REFERENCES Roles(Role_ID)
);

CREATE TABLE Products (
Product_ID INT IDENTITY(1,1) PRIMARY KEY,
Arcticle VARCHAR(50) NOT NULL UNIQUE,
ProductName VARCHAR(200) NOT NULL,
Price DECIMAL(10, 2) NOT NULL CHECK (Price >=0),
Unit VARCHAR(20) NOT NULL,
StockQuantity INT NOT NULL DEFAULT 0 CHECK (StockQuantity >=0),
DiscountPrice DECIMAL(5, 2) DEFAULT 0 CHECK (DiscountPrice >=0 AND DiscountPrice <=100),
Description VARCHAR(MAX) NOT NULL,
PhotoPath VARCHAR(255) NULL,
Category_ID INT NOT NULL,
Supplier_ID INT NOT NULL,
Manufacturer_ID INT NOT NULL,
FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID),
FOREIGN KEY (Supplier_ID) REFERENCES Suppliers(Supplier_ID),
FOREIGN KEY (Manufacturer_ID) REFERENCES Manufacturers(Manufacturer_ID),
);

CREATE TABLE PickupPoints (
PickupPoint_ID INT IDENTITY(1,1) PRIMARY KEY,
PostalCode VARCHAR(20) NOT NULL,
City VARCHAR(50) NOT NULL,
Street VARCHAR(100) NOT NULL,
HouseNumber VARCHAR(20) NULL
);

CREATE TABLE OrderStatuses (
Status_ID INT IDENTITY(1,1) PRIMARY KEY,
StatusName VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Orders (
Order_ID INT IDENTITY(1,1) PRIMARY KEY,
OrderNumber INT NOT NULL UNIQUE,
OrderDate DATETIME NOT NULL,
DeliveryDate DATETIME NULL,
PickupCode VARCHAR(10) NULL,
User_ID INT NULL,
PickupPoint_ID INT NOT NULL,
Status_ID INT NOT NULL,
FOREIGN KEY (Status_ID) REFERENCES OrderStatuses(Status_ID),
FOREIGN KEY (PickupPoint_ID) REFERENCES PickupPoints(PickupPoint_ID),
FOREIGN KEY (User_ID) REFERENCES Users(User_ID),
CONSTRAINT CHK_DeliveryDate CHECK (DeliveryDate IS NULL OR DeliveryDate >= OrderDate)
);

CREATE TABLE OrderItems (
OrderItem_ID INT IDENTITY(1,1) PRIMARY KEY,
Quantity INT NOT NULL CHECK (Quantity > 0),
PriceAtOrder DECIMAL(10, 2) NOT NULL CHECK (PriceAtOrder >= 0),
Order_ID INT NOT NULL,
Product_ID INT NOT NULL,
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID),
);