Example

Public

• GET /api.php?action=health
• GET /api.php?action=products&[q=&category_id=&page=&limit=]
• GET /api.php?action=product&id=123

Auth

• POST /api.php?action=register {email, password, name}
• POST /api.php?action=login {email, password} → {token, expires_at}
• POST /api.php?action=logout (Bearer token)

Cart (Bearer token)

• GET /api.php?action=cart
• POST /api.php?action=cart_add {product_id, qty}
• POST /api.php?action=cart_update {product_id, qty} (qty<=0 removes)
• POST /api.php?action=cart_clear

Orders (Bearer token)

• POST /api.php?action=checkout {shipping_name, shipping_address}
• GET /api.php?action=orders
• GET /api.php?action=order&id=123

CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE shop;

-- Users & sessions CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, name VARCHAR(120) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB;

CREATE TABLE sessions ( token CHAR(64) PRIMARY KEY, user_id INT UNSIGNED NOT NULL, expires_at DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX (user_id), INDEX (expires_at) ) ENGINE=InnoDB;

-- Catalog CREATE TABLE categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL UNIQUE ) ENGINE=InnoDB;

CREATE TABLE products ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(190) NOT NULL, description TEXT NOT NULL, price_cents INT UNSIGNED NOT NULL, -- store price in minor units stock INT NOT NULL DEFAULT 0, image_url VARCHAR(255) NULL, category_id INT UNSIGNED NULL, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL, FULLTEXT KEY ft_title_desc (title, description) -- for simple search (InnoDB supports FULLTEXT) ) ENGINE=InnoDB;

-- Cart (one active cart per user) CREATE TABLE carts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB;

CREATE TABLE cart_items ( cart_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, qty INT NOT NULL CHECK (qty > 0), PRIMARY KEY (cart_id, product_id), FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ) ENGINE=InnoDB;

-- Orders CREATE TABLE orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, total_cents INT UNSIGNED NOT NULL, status ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING', shipping_name VARCHAR(120) NOT NULL, shipping_address TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX (user_id, created_at) ) ENGINE=InnoDB;

CREATE TABLE order_items ( order_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, title_snapshot VARCHAR(190) NOT NULL, price_cents INT UNSIGNED NOT NULL, qty INT NOT NULL, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB;

-- Seed (optional) INSERT INTO categories (name) VALUES ('T-Shirts'), ('Accessories'); INSERT INTO products (title, description, price_cents, stock, image_url, category_id) VALUES ('Black Tee', 'Soft cotton tee', 39000, 50, 'https://picsum.photos/seed/tee/600/400', 1), ('Cap', 'Adjustable cap', 25000, 80, 'https://picsum.photos/seed/cap/600/400', 2);