CREATE TABLE users (

id SERIAL PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

email VARCHAR(100) NOT NULL UNIQUE,

password_hash VARCHAR(255) NOT NULL,

is_active BOOLEAN DEFAULT TRUE,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE products (

id SERIAL PRIMARY KEY,

sku VARCHAR(50) NOT NULL UNIQUE,

name VARCHAR(200) NOT NULL,

description TEXT,

price DECIMAL(10, 2) NOT NULL,

quantity INTEGER DEFAULT 0,

category_id INTEGER REFERENCES categories(id),

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

user_id INTEGER NOT NULL REFERENCES users(id),

status VARCHAR(20) DEFAULT 'pending',

total DECIMAL(10, 2) NOT NULL,

shipping_address TEXT,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE order_items (

id SERIAL PRIMARY KEY,

order_id INTEGER NOT NULL REFERENCES orders(id),

product_id INTEGER NOT NULL REFERENCES products(id),

quantity INTEGER NOT NULL,

unit_price DECIMAL(10, 2) NOT NULL

);

CREATE INDEX idx_orders_user_id ON orders(user_id);

CREATE INDEX idx_orders_status ON orders(status);

CREATE INDEX idx_products_category ON products(category_id);

CREATE VIEW order_summary AS

SELECT

o.id AS order_id,

u.username,

u.email,

o.status,

o.total,

o.created_at,

COUNT(oi.id) AS item_count

FROM orders o

JOIN users u ON o.user_id = u.id

LEFT JOIN order_items oi ON o.id = oi.order_id

GROUP BY o.id, u.username, u.email, o.status, o.total, o.created_at;

CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id INTEGER)

RETURNS DECIMAL AS $$

DECLARE

v_total DECIMAL(10, 2);

SELECT SUM(quantity * unit_price) INTO v_total

FROM order_items

WHERE order_id = p_order_id;

RETURN COALESCE(v_total, 0);

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_order_total()

RETURNS TRIGGER AS $$

UPDATE orders

SET total = calculate_order_total(NEW.order_id)

WHERE id = NEW.order_id;

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_order_total

AFTER INSERT OR UPDATE ON order_items

FOR EACH ROW EXECUTE FUNCTION update_order_total();