---

CREATE TABLE Category (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(25) NOT NULL
);

CREATE TABLE Producer (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(25) NOT NULL
);

CREATE TABLE Provider (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(25) NOT NULL
);

CREATE TABLE Unit (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(5) NOT NULL
);

CREATE TABLE Role (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(25) NOT NULL
);

CREATE TABLE OrderStatus (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(20) NOT NULL
);

CREATE TABLE PickUpPoint (
    Id INT PRIMARY KEY IDENTITY,
    PostCode NVARCHAR(6),
    City NVARCHAR(30),
    Street NVARCHAR(30),
    Building NVARCHAR(6)
);

CREATE TABLE [User] (
    Id INT PRIMARY KEY IDENTITY,
    Surname NVARCHAR(30) NOT NULL,
    Name NVARCHAR(30) NOT NULL,
    Patronymic NVARCHAR(30),
    Login NVARCHAR(30) NOT NULL,
    Password NVARCHAR(30) NOT NULL,
    RoleId INT NOT NULL,

    FOREIGN KEY (RoleId) REFERENCES Role(Id)
);

CREATE TABLE Product (
    Id INT PRIMARY KEY IDENTITY,
    Article NVARCHAR(10) NOT NULL,
    Name NVARCHAR(30) NOT NULL,
    UnitId INT NOT NULL,
    Price MONEY NOT NULL,
    ProviderId INT NOT NULL,
    ProducerId INT NOT NULL,
    CategoryId INT NOT NULL,
    DiscountAmount DECIMAL(4,2),
    Description NVARCHAR(MAX),
    Photo NVARCHAR(MAX),

    FOREIGN KEY (UnitId) REFERENCES Unit(Id),
    FOREIGN KEY (ProviderId) REFERENCES Provider(Id),
    FOREIGN KEY (ProducerId) REFERENCES Producer(Id),
    FOREIGN KEY (CategoryId) REFERENCES Category(Id)
);

CREATE TABLE [Order] (
    Id INT PRIMARY KEY IDENTITY,
    CreationDate DATE NOT NULL,
    DeliveryDate DATE,
    PickUpPointId INT NOT NULL,
    UserId INT NOT NULL,
    ReceiptCode NVARCHAR(4),
    StatusId INT NOT NULL,

    FOREIGN KEY (PickUpPointId) REFERENCES PickUpPoint(Id),
    FOREIGN KEY (UserId) REFERENCES [User](Id),
    FOREIGN KEY (StatusId) REFERENCES OrderStatus(Id)
);

CREATE TABLE ProductInOrder (
    Id INT PRIMARY KEY IDENTITY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Amount INT NOT NULL,

    FOREIGN KEY (OrderId) REFERENCES [Order](Id),
    FOREIGN KEY (ProductId) REFERENCES Product(Id)
);