-- Create Products table with constraints
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY, -- Unique identifier for the product
    product_name VARCHAR(255) NOT NULL, -- Name of the product
    price INT NOT NULL, -- Price of the product as a whole number
    description TEXT, -- Description of the product
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0) -- Quantity of the product in stock, must be non-negative
);

-- Create Customers table with constraints
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Unique identifier for the customer
    first_name VARCHAR(100) NOT NULL, -- First name of the customer
    last_name VARCHAR(100) NOT NULL, -- Last name of the customer
    email VARCHAR(255) NOT NULL UNIQUE, -- Email address of the customer, must be unique
    address TEXT -- Address of the customer
);

-- Create Orders table with constraints
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY, -- Unique identifier for the order
    customer_id INT REFERENCES customers(customer_id), -- Reference to the customer who placed the order
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Date and time when the order was placed
    total_amount INT NOT NULL CHECK (total_amount >= 0), -- Total amount of the order as a whole number, must be non-negative
    status VARCHAR(50) DEFAULT 'Pending' CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Cancelled')) -- Status of the order, allowed values: 'Pending', 'Processing', 'Shipped', 'Cancelled'
);

-- Create OrderItems table with constraints
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY, -- Unique identifier for the order item
    order_id INT REFERENCES orders(order_id), -- Reference to the order to which this item belongs
    product_id INT REFERENCES products(product_id), -- Reference to the product in this order item
    quantity INT NOT NULL CHECK (quantity > 0), -- Quantity of the product in this order item, must be positive
    price_per_item INT NOT NULL CHECK (price_per_item >= 0), -- Price per unit of the product as a whole number, must be non-negative
    total_price INT NOT NULL CHECK (total_price >= 0), -- Total price of this order item as a whole number, must be non-negative
    FOREIGN KEY (product_id) REFERENCES products(product_id), -- Foreign key constraint for product_id column
    FOREIGN KEY (order_id) REFERENCES orders(order_id) -- Foreign key constraint for order_id column
);

-- Sample test case: Inserting data into tables
-- Insert products
INSERT INTO products (product_name, price, description, stock_quantity)
VALUES ('Laptop', 800, 'High-performance laptop', 50),
       ('Smartphone', 400, 'Latest smartphone model', 100),
       ('Headphones', 100, 'Noise-canceling headphones', 200);

-- Insert customers
INSERT INTO customers (first_name, last_name, email, address)
VALUES ('John', 'Doe', '[email protected]', '123 Main St, City, Country'),
       ('Alice', 'Smith', '[email protected]', '456 Elm St, City, Country');

-- Insert orders
INSERT INTO orders (customer_id, total_amount, status)
VALUES (1, 800, 'Shipped'),
       (2, 200, 'Processing');

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, price_per_item, total_price)
VALUES (1, 1, 1, 800, 800),
       (2, 2, 2, 100, 200);

-- Sample queries
-- Retrieve all products
SELECT * FROM products;

-- Retrieve customers with their orders
SELECT customers.customer_id, customers.first_name, customers.last_name, orders.order_id, orders.total_amount, orders.status
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

-- Retrieve order details with product information
SELECT orders.order_id, products.product_name, order_items.quantity, order_items.price_per_item, order_items.total_price
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
JOIN products ON order_items.product_id = products.product_id;

-- Calculate total sales for each product
SELECT products.product_id, products.product_name, SUM(order_items.quantity) AS total_quantity, SUM(order_items.total_price) AS total_sales
FROM products
LEFT JOIN order_items ON products.product_id = order_items.product_id
GROUP BY products.product_id, products.product_name;

-- Retrieve customers who made orders with total order amount
SELECT customers.customer_id, customers.first_name, customers.last_name, COUNT(orders.order_id) AS total_orders, SUM(orders.total_amount) AS total_spent
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.first_name, customers.last_name;

-- Update product price
UPDATE products
SET price = 850
WHERE product_id = 1;

-- Insert a new customer and order
-- Insert a new customer
INSERT INTO customers (first_name, last_name, email, address)
VALUES ('Jane', 'Doe', '[email protected]', '789 Elm St, City, Country');

-- Get the new customer's ID
-- Assuming the new customer's ID is 3

-- Insert a new order for the new customer
INSERT INTO orders (customer_id, total_amount, status)
VALUES (3, 500, 'Pending');

-- Get the new order's ID
-- Assuming the new order's ID is 3

-- Insert order items for the new order
INSERT INTO order_items (order_id, product_id, quantity, price_per_item, total_price)
VALUES (3, 2, 2, 250, 500);

-- Sample Queries for E-Commerce Database

-- Query 1: Retrieve all products with their details
SELECT * FROM products;

-- Query 2: Retrieve orders with customer information
SELECT orders.order_id, orders.order_date, orders.total_amount, orders.status, customers.first_name, customers.last_name, customers.email
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

-- Query 3: Retrieve total sales for each product
SELECT products.product_name, SUM(order_items.total_price) AS total_sales
FROM order_items
JOIN products ON order_items.product_id = products.product_id
GROUP BY products.product_name
ORDER BY total_sales DESC;

-- Query 4: Update product price by product_id
UPDATE products
SET price = 900
WHERE product_id = 1;

-- Query 5: Insert a new customer and their order
-- First, insert a new customer
INSERT INTO customers (first_name, last_name, email, address)
VALUES ('Emma', 'Johnson', '[email protected]', '789 Oak St, City, Country');

-- Get the ID of the newly inserted customer
-- Assuming the newly inserted customer has customer_id 3
-- Insert a new order for the new customer
INSERT INTO orders (customer_id, total_amount, status)
VALUES (3, 1200, 'Pending');

-- Insert items for the new order
INSERT INTO order_items (order_id, product_id, quantity, price_per_item, total_price)
VALUES (3, 2, 2, 400, 800),
       (3, 3, 1, 100, 100);

-- Query 6: Delete a product by product_id
DELETE FROM products
WHERE product_id = 3;

-- Query 7: Retrieve all orders for a specific customer (e.g., customer_id 1)
SELECT * FROM orders
WHERE customer_id = 1;

-- Query 8: Calculate total revenue
SELECT SUM(total_amount) AS total_revenue
FROM orders;

-- Query 9: Retrieve out-of-stock products
SELECT * FROM products
WHERE stock_quantity = 0;

-- Query 10: Retrieve the average price of products
SELECT AVG(price) AS average_price
FROM products;