-- PART B
PRAGMA foreign_keys = ON;

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

CREATE TABLE Customers(
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);
PRAGMA table_info(Customer);

CREATE TABLE Products(
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    unit_price INTEGER CHECK (unit_price > 0)
);
PRAGMA table_info(Products);

CREATE TABLE Orders(
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'Available',
    FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)
);
PRAGMA table_info(Orders);
CREATE TABLE OrderItems(
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    qty INTEGER CHECK (qty > 0),
    UNIQUE (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
PRAGMA table_info(OrderItems);
ALTER TABLE Customers
ADD COLUMN contact_note TEXT;


INSERT INTO Customers(customer_id, customer_name)
VALUES (1, 'Isabel'),
(2, 'Jon'),
(3, 'Kira'),
(4, 'Leo');
SELECT * FROM Customers;

INSERT INTO Products(product_id, product_name, category, unit_price)
VALUES
(1, 'Bookmark', 'Paper', 20),
(2, 'Tote Bag', 'Merchandise', 160),
(3, 'Journal', 'Paper', 110);
SELECT * FROM Products;

INSERT INTO Orders(order_id, customer_id, order_date, status)
VALUES
(401, 1, '2026-10-15', 'Completed'),
(402, 2, '2026-10-16', 'Completed'),
(403, 3, '2026-10-17', 'Completed'),
(404, 1, '2026-10-18', 'Cancelled');
SELECT * FROM Orders;

INSERT INTO OrderItems(item_id, order_id, product_id, qty)
VALUES
(1, 401, 1, 5),
(2, 401, 2, 1),
(3, 402, 3, 2),
(4, 403, 2, 2),
(5, 403, 1, 4),
(6, 404, 3, 1);
SELECT * FROM OrderItems;

SELECT * FROM Orders WHERE order_id = 402;
UPDATE Orders 
SET status = 'Cancelled'
WHERE order_id;
SELECT * FROM Orders WHERE order_id = 402;
 
-- PART C
SELECT * FROM OrderItems WHERE item_id = 2;
DELETE FROM OrderItems WHERE item_id = 2;
SELECT * FROM OrderItems WHERE item_id = 2;

-- PART D
SELECT Orders.order_id, product_name, qty * unit_price AS revenue 
FROM Orders, Products, OrderItems
WHERE revenue 
ORDER BY revenue DESC, item_id ASC
LIMIT 3;


-- PART E

SELECT c.customer_name, o.order_id, p.product_name, i.qty,(i.qty * p.unit_price) AS line_sales
FROM Customers AS c
INNER JOIN Orders AS o 
ON c.customer_id = o.customer_id
INNER JOIN OrderItems AS i 
ON o.order_id = i.order_id
INNER JOIN Products AS p 
ON i.product_id = p.product_id
ORDER BY o.order_id ASC, i.item_id ASC;

SELECT c.customer_id, c.customer_name,
COUNT(DISTINCT o.order_id) AS completed_orders,
COALESCE(SUM(i.qty * p.unit_price), 0) AS revenue
FROM Customers AS c
LEFT JOIN Orders AS o 
ON c.customer_id = o.customer_id 
LEFT JOIN OrderItems AS i ON o.order_id = i.order_id
LEFT JOIN Products AS p ON i.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
ORDER BY revenue DESC, c.customer_id ASC;