CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100) NOT NULL,
    CategoryID INT,
    QuantityInStock INT,
    Price DECIMAL(10, 2)
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY IDENTITY(1,1),
    CategoryName NVARCHAR(100) NOT NULL
);

CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY IDENTITY(1,1),
    SupplierName NVARCHAR(100) NOT NULL,
    ContactInfo NVARCHAR(255)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    OrderDate DATE NOT NULL,
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

CREATE TRIGGER UpdateStockAfterOrder
ON OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE Products
    SET QuantityInStock = QuantityInStock - i.Quantity
    FROM Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END;

CREATE FUNCTION dbo.CalculateOrderTotal(@OrderID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @Total DECIMAL(10, 2);
    SELECT @Total = SUM(od.Quantity * p.Price)
    FROM OrderDetails od
    INNER JOIN Products p ON od.ProductID = p.ProductID
    WHERE od.OrderID = @OrderID;
    
    RETURN @Total;
END;

CREATE PROCEDURE dbo.AddNewOrder
    @OrderDate DATE,
    @SupplierID INT,
    @OrderDetails dbo.OrderDetailsType READONLY -- فرض کنید نوع جدولی برای OrderDetails تعریف شده است
AS
BEGIN
    DECLARE @OrderID INT;
    
    INSERT INTO Orders (OrderDate, SupplierID)
    VALUES (@OrderDate, @SupplierID);
    
    SET @OrderID = SCOPE_IDENTITY();
    
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    SELECT @OrderID, ProductID, Quantity
    FROM @OrderDetails;
    
    RETURN @OrderID;
END;

CREATE VIEW OrderSummary AS
SELECT o.OrderID, o.OrderDate, s.SupplierName, p.ProductName, od.Quantity, (od.Quantity * p.Price) AS TotalPrice
FROM Orders o
INNER JOIN Suppliers s ON o.SupplierID = s.SupplierID
INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID;