$ psql -U username -d dbname -h hostname -p port

\i '~/Downloads/test.sql'

copy mytable from '~/Downloads/dummy.csv' with delimiter ',' csv;

-- Create the database
CREATE DATABASE dummy_db;

-- Connect to the database
\c dummy_db;

-- Create the users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending',
    CONSTRAINT valid_total_amount CHECK (total_amount >= 0)
);

-- Create a junction table for order items (many-to-many relationship)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT NOT NULL REFERENCES products(product_id) ON DELETE CASCADE,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    UNIQUE (order_id, product_id)
);

-- Sample Index (optional)
CREATE INDEX idx_users_username ON users (username);

-- Sample Views (optional)
CREATE VIEW user_orders AS
SELECT
    u.user_id,
    u.username,
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status
FROM
    users u
JOIN
    orders o ON u.user_id = o.user_id;

-- Insert dummy data into the users table
INSERT INTO users (username, email, password) VALUES
('john_doe', 'john@example.com', 'password123'),
('jane_smith', 'jane@example.com', 'securepassword'),
('mike_brown', 'mike@example.com', 'mypassword');

-- Insert dummy data into the products table
INSERT INTO products (name, description, price, stock_quantity) VALUES
('Laptop', 'High performance laptop with 16GB RAM and 512GB SSD', 1200.00, 50),
('Smartphone', 'Latest model smartphone with amazing features', 800.00, 100),
('Headphones', 'Wireless headphones with noise cancellation', 150.00, 200),
('Desk Chair', 'Ergonomic desk chair for comfortable seating', 300.00, 20);

-- Insert dummy data into the orders table
INSERT INTO orders (user_id, total_amount, status) VALUES
(1, 1500.00, 'Completed'),
(2, 950.00, 'Pending'),
(3, 300.00, 'Shipped');

-- Insert dummy data into the order_items table
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1200.00), -- John ordered 1 Laptop
(1, 3, 2, 150.00),  -- John ordered 2 Headphones
(2, 2, 1, 800.00),  -- Jane ordered 1 Smartphone
(2, 4, 1, 300.00),  -- Jane ordered 1 Desk Chair
(3, 4, 1, 300.00);  -- Mike ordered 1 Desk Chair