---

-- РОЛИ
CREATE TABLE Roles (
    Role_ID INT NOT NULL PRIMARY KEY,
    RoleName NVARCHAR(50) NOT NULL UNIQUE
);

-- ПОЛЬЗОВАТЕЛИ
CREATE TABLE Users (
    User_ID INT NOT NULL PRIMARY KEY,
    LastName NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(50),
    Login NVARCHAR(50) NOT NULL UNIQUE,
    Password NVARCHAR(255) NOT NULL,
    Role_ID INT NOT NULL,
    FOREIGN KEY (Role_ID) REFERENCES Roles(Role_ID)
);

-- КАТЕГОРИИ
CREATE TABLE Categories (
    Category_ID INT NOT NULL PRIMARY KEY,
    CategoryName NVARCHAR(100) NOT NULL UNIQUE
);

-- ПРОИЗВОДИТЕЛИ
CREATE TABLE Manufacturers (
    Manufacturer_ID INT NOT NULL PRIMARY KEY,
    ManufacturerName NVARCHAR(100) NOT NULL UNIQUE
);

-- ПОСТАВЩИКИ
CREATE TABLE Suppliers (
    Supplier_ID INT NOT NULL PRIMARY KEY,
    SupplierName NVARCHAR(100) NOT NULL UNIQUE
);

-- ЕДИНИЦЫ
CREATE TABLE Units (
    Unit_ID INT NOT NULL PRIMARY KEY,
    UnitName NVARCHAR(20) NOT NULL UNIQUE
);

-- ТОВАРЫ
CREATE TABLE Products (
    Product_ID INT NOT NULL PRIMARY KEY,
    ProductName NVARCHAR(200) NOT NULL,
    Category_ID INT NOT NULL,
    Description NVARCHAR(MAX),
    Manufacturer_ID INT NOT NULL,
    Supplier_ID INT NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    Unit_ID INT NOT NULL,
    StockQuantity INT NOT NULL,
    DiscountPercent DECIMAL(5,2),
    PhotoPath NVARCHAR(255),

    FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID),
    FOREIGN KEY (Manufacturer_ID) REFERENCES Manufacturers(Manufacturer_ID),
    FOREIGN KEY (Supplier_ID) REFERENCES Suppliers(Supplier_ID),
    FOREIGN KEY (Unit_ID) REFERENCES Units(Unit_ID)
);

-- ПУНКТЫ ВЫДАЧИ
CREATE TABLE PickupPoints (
    PickupPoint_ID INT NOT NULL PRIMARY KEY,
    Address NVARCHAR(255) NOT NULL
);

-- СТАТУСЫ
CREATE TABLE OrderStatuses (
    Status_ID INT NOT NULL PRIMARY KEY,
    StatusName NVARCHAR(50) NOT NULL UNIQUE
);

-- ЗАКАЗЫ
CREATE TABLE Orders (
    Order_ID INT NOT NULL PRIMARY KEY,
    OrderArticle NVARCHAR(50),
    Status_ID INT NOT NULL,
    PickupPoint_ID INT NOT NULL,
    OrderDate DATE NOT NULL,
    DeliveryDate DATE,

    FOREIGN KEY (Status_ID) REFERENCES OrderStatuses(Status_ID),
    FOREIGN KEY (PickupPoint_ID) REFERENCES PickupPoints(PickupPoint_ID)
);

-- СОСТАВ ЗАКАЗА
CREATE TABLE OrderItems (
    OrderItem_ID INT NOT NULL PRIMARY KEY,
    Order_ID INT NOT NULL,
    Product_ID INT NOT NULL,
    Quantity INT NOT NULL,
    PriceAtOrder DECIMAL(10,2) NOT NULL,

    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID)
);