Example of E-commice SQL in that specilises in the food industry

-- Drop existing database if it exists
DROP DATABASE IF EXISTS task_db;

-- Create a fresh database
CREATE DATABASE task_db;
USE task_db;

-- Role table
CREATE TABLE IF NOT EXISTS role (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(255)
);

-- Insert default roles
INSERT INTO role (name, description) VALUES
('admin', 'Full system access'),
('customer', 'Can browse and place orders'),
('staff', 'Can manage orders and products'),
('vendor', 'Supplier with limited access')
ON DUPLICATE KEY UPDATE name = name;

-- Users table (renamed from user)
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
role_id INT NOT NULL,
firstName VARCHAR(100) NOT NULL,
lastName VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
phone VARCHAR(20),
userPoints INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (role_id) REFERENCES role(id)
);

-- Producer table
CREATE TABLE IF NOT EXISTS producer (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Category table
CREATE TABLE IF NOT EXISTS category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);

-- Insert default categories
INSERT INTO category (name) VALUES
('Vegetables'),
('Fruits'),
('Dairy'),
('Honey & Preserves'),
('Meat & Poultry'),
('Drinks'),
('Bakery'),
('Herbs & Flowers')
ON DUPLICATE KEY UPDATE name = name;

-- Product table
CREATE TABLE IF NOT EXISTS product (
id INT AUTO_INCREMENT PRIMARY KEY,
producer_id INT NOT NULL,
category_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock_qty INT NOT NULL DEFAULT 0,
unit VARCHAR(50) NOT NULL DEFAULT 'item',
is_organic BOOLEAN DEFAULT FALSE,
is_available BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (producer_id) REFERENCES producer(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES category(id)
);

-- Address table
CREATE TABLE IF NOT EXISTS address (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
street VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
postcode VARCHAR(20) NOT NULL,
country VARCHAR(100) NOT NULL DEFAULT 'United Kingdom',
is_default BOOLEAN DEFAULT FALSE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Orders table (renamed from order)
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
address_id INT NOT NULL,
status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
total_price DECIMAL(10, 2) NOT NULL,
payment_status ENUM('unpaid', 'paid', 'refunded') DEFAULT 'unpaid',
payment_method VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (address_id) REFERENCES address(id)
);

-- Order item table
CREATE TABLE IF NOT EXISTS order_item (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES product(id)
);