---

USE DemoExam;
GO

CREATE TABLE Users
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Login NVARCHAR(50) NOT NULL UNIQUE,
    Password NVARCHAR(50) NOT NULL,
    FullName NVARCHAR(100) NOT NULL,
    Role NVARCHAR(30) NOT NULL
);
GO

CREATE TABLE Categories
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL
);
GO

CREATE TABLE Manufacturers
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL
);
GO

CREATE TABLE Products
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    CategoryId INT NOT NULL,
    Description NVARCHAR(500),
    ManufacturerId INT NOT NULL,
    Supplier NVARCHAR(100),
    Price DECIMAL(10,2) NOT NULL,
    Unit NVARCHAR(20) NOT NULL,
    Quantity INT NOT NULL,
    Discount DECIMAL(5,2) NOT NULL,
    ImagePath NVARCHAR(255),

    FOREIGN KEY (CategoryId)
        REFERENCES Categories(Id),

    FOREIGN KEY (ManufacturerId)
        REFERENCES Manufacturers(Id)
);
GO

CREATE TABLE Orders
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Article NVARCHAR(50) NOT NULL,
    Status NVARCHAR(50) NOT NULL,
    PickupAddress NVARCHAR(200),
    OrderDate DATE,
    PickupDate DATE
);
GO

CREATE TABLE OrderItems
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,

    FOREIGN KEY (OrderId)
        REFERENCES Orders(Id),

    FOREIGN KEY (ProductId)
        REFERENCES Products(Id)
);
GO