CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2),
    Stock INT
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    OrderDate DATE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
INSERT INTO Products VALUES (1, 'Laptop', 75000.00, 10);
INSERT INTO Products VALUES (2, 'Mouse', 500.00, 100);
INSERT INTO Products VALUES (3, 'Keyboard', 1000.00, 50);

INSERT INTO Orders VALUES (1, 1, 2, '2024-12-01');
INSERT INTO Orders VALUES (2, 2, 5, '2024-12-02');
INSERT INTO Orders VALUES (3, 3, 1, '2024-12-02');

CREATE INDEX idx_ProductName ON Products(ProductName);

CREATE INDEX idx_OrderDate ON Orders(OrderDate);

DELIMITER $$

CREATE PROCEDURE GetTotalRevenueByProduct(IN ProductName VARCHAR(50))
BEGIN
    SELECT p.ProductName, SUM(o.Quantity * p.Price) AS TotalRevenue
    FROM Orders o
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE p.ProductName= ProductName
    GROUP BY p.ProductName;
END $$

DELIMITER ;

CALL GetTotalRevenueByProduct('Laptop');