PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
    category TEXT PRIMARY KEY NOT NULL
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    category TEXT NOT NULL REFERENCES categories(category),
    product TEXT NOT NULL,
    qty INTEGER NOT NULL CHECK(qty > 0),
    unit_price REAL NOT NULL CHECK(unit_price >= 0),
    status TEXT NOT NULL CHECK(status IN ('Completed','Cancelled'))
);

INSERT INTO categories VALUES
('Stationery'), ('Accessories'), ('Snacks');

INSERT INTO sales VALUES
(1,'Stationery','Notebook',5,40,'Completed'),
(2,'Stationery','Pen',10,10,'Completed'),
(3,'Accessories','USB',2,300,'Completed'),
(4,'Snacks','Biscuit',8,25,'Completed'),
(5,'Stationery','Notebook',3,40,'Cancelled'),
(6,'Accessories','Mouse',1,450,'Completed'),
(7,'Snacks','Juice',6,30,'Completed'),
(8,'Stationery','Pen',15,10,'Completed'),
(9,'Accessories','USB',1,300,'Cancelled'),
(10,'Snacks','Biscuit',4,25,'Completed'),
(11,'Stationery','Folder',4,20,'Completed'),
(12,'Accessories','Mouse',2,450,'Completed'),
(13,'Stationery','Folder',5,20,'Completed'),
(14,'Snacks','Juice',10,30,'Completed'),
(15,'Accessories','USB',1,300,'Completed');


-- SETUP VALIDATION QUERIES


SELECT name FROM sqlite_master
WHERE type = 'table' AND name IN ('categories','sales')
ORDER BY name;

SELECT COUNT(*) AS category_records FROM categories;

SELECT COUNT(*) AS sales_records FROM sales;


-- A1:

SELECT sale_id, product, qty
FROM sales
WHERE status = 'Completed'
  AND category = 'Stationery'
  AND qty >= 4
ORDER BY sale_id ASC;

-- A2:

SELECT sale_id, product, qty * unit_price AS revenue
FROM sales
WHERE status = 'Completed'
ORDER BY revenue DESC, sale_id ASC
LIMIT 3;

-- A3:

SELECT COUNT(*) AS line_count, 
       SUM(qty) AS units,
       SUM(qty * unit_price) AS revenue,
       ROUND(AVG(qty * unit_price), 2) AS avg_line_revenue,
       MIN(qty * unit_price) AS lowest_line_revenue,
       MAX(qty * unit_price) AS highest_line_revenue
FROM sales 
WHERE status = 'Completed';

SELECT category, 
       COUNT(*) AS line_count,
       SUM(qty) AS units, 
       SUM(qty * unit_price) AS revenue
FROM sales 
WHERE status = 'Completed'
GROUP BY category 
ORDER BY category ASC;

SELECT category, 
       SUM(qty * unit_price) AS revenue,
       ROUND(AVG(qty * unit_price), 2) AS avg_line_revenue
FROM sales 
WHERE status = 'Completed'
GROUP BY category 
HAVING SUM(qty * unit_price) >= 700
ORDER BY revenue DESC, category ASC;