DROP TABLE IF EXISTS OrderItems;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;

CREATE TABLE Customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    FOREIGN KEY(customer_id)
        REFERENCES Customers(customer_id)
);

CREATE TABLE OrderItems (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product TEXT,
    qty INTEGER,
    unit_price INTEGER,
    FOREIGN KEY(order_id)
        REFERENCES Orders(order_id)
);

INSERT INTO Customers VALUES
(1, 'Ana'),
(2, 'Ben'),
(3, 'Cara'),
(4, 'Dan');

INSERT INTO Orders VALUES
(101, 1, '2026-10-01'),
(102, 1, '2026-10-02'),
(103, 2, '2026-10-02'),
(104, 3, '2026-10-03');

INSERT INTO OrderItems VALUES
(1, 101, 'Notebook', 2, 50),
(2, 101, 'Pen', 3, 10),
(3, 102, 'Folder', 4, 25),
(4, 103, 'Notebook', 1, 50),
(5, 103, 'Pen', 5, 10),
(6, 104, 'Folder', 2, 25);

SELECT i.product,
       SUM(i.qty) AS total_quantity,
       SUM(i.qty * i.unit_price) AS total_sales
FROM OrderItems AS i
GROUP BY i.product
HAVING SUM(i.qty * i.unit_price) >= 100
ORDER BY total_sales DESC, i.product;