SQL example

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

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    first_name    VARCHAR(100) NOT NULL,
    last_name     VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role          ENUM('customer', 'producer', 'admin') DEFAULT 'customer',
    bio           TEXT,
    logo_url      VARCHAR(500),
    loyalty_points INT DEFAULT 0,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    producer_id INT NOT NULL,
    category_id INT,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       DECIMAL(10,2) NOT NULL,
    stock       INT DEFAULT 0,
    allergies   VARCHAR(255) DEFAULT NULL,
    image_url   VARCHAR(500),
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (producer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    order_type ENUM('collection', 'delivery') DEFAULT 'collection',
    total      DECIMAL(10,2) NOT NULL,
    status     ENUM('pending', 'processing', 'complete', 'cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_items (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    price      DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE reviews (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    product_id  INT,
    producer_id INT,
    rating      TINYINT DEFAULT 5,
    body        TEXT NOT NULL,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)     REFERENCES users(id)     ON DELETE CASCADE,
    FOREIGN KEY (product_id)  REFERENCES products(id)  ON DELETE SET NULL,
    FOREIGN KEY (producer_id) REFERENCES users(id)     ON DELETE SET NULL
);

INSERT INTO users (first_name, last_name, email, password_hash, role) VALUES
('Admin', 'User', '[e-mail]',
'$2b$12$FIdawChxq0wrbWkYIPTtBer8bCiHS59fO/Is2bD5sM5aLtsmNIjde', 'admin');

INSERT INTO users (first_name, last_name, email, password_hash, role, bio) VALUES
('Green Valley', 'Farm',    '[e-mail]', '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'producer', 'A family-run farm producing seasonal vegetables and fruits.'),
('Sunrise',      'Orchards','[e-mail]',     '$2b$12$FIdawChxq0wrbWkYIPTtBer8bCiHS59fO/Is2bD5sM5aLtsmNIjde', 'producer', 'Award-winning apple and pear growers with over 30 years of experience.'),
('Meadow',       'Dairy',   '[e-mail]',      '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'producer', 'Fresh milk, cheese, and butter from our free-range herd.'),
('Forest Honey', 'Co.',     '[e-mail]',       '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'producer', 'Pure raw honey harvested from wildflower meadows and woodland areas.'),
('River',        'Bakehouse','[e-mail]',  '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'producer', 'Artisan sourdough breads and pastries baked fresh each morning.'),
('Hilltop',      'Herbs',   '[e-mail]',       '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'producer', 'Certified organic herbs and herbal teas grown without pesticides.');

INSERT INTO users (first_name, last_name, email, password_hash, role) VALUES
('Sarah', 'Johnson',  '[e-mail]', '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'customer'),
('Tom',   'Williams', '[e-mail]',   '$2b$12$.CDLs9MA4KU5VNNfss8W9.xaXYtwarubmDR0eKQ.XGi2DAiemXwKa', 'customer');

INSERT INTO categories (name) VALUES
('Vegetables'), ('Fruit'), ('Dairy'), ('Honey'), ('Bakery'), ('Herbs');

INSERT INTO products (producer_id, category_id, name, description, price, stock, image_url) VALUES
(2, 1, 'Seasonal Veg Box',       'Freshly picked seasonal vegetables, harvested that morning.',       12.99, 50,  'https://images.unsplash.com/photo-1540420773420-3366772f4999?w=400&q=80'),
(2, 1, 'Heritage Tomatoes',      'Mixed colourful heritage tomatoes, full of flavour.',                3.49, 80,  'https://images.unsplash.com/photo-1592924357228-91a4daadcfea?w=400&q=80'),
(2, 1, 'New Potatoes (1kg)',     'Freshly dug new potatoes — perfect boiled with mint.',               2.50, 120, 'https://images.unsplash.com/photo-1518977676601-b53f82aba655?w=400&q=80'),
(3, 2, 'Cox Apples (6 pack)',    'Classic British Cox apples — crisp, sweet, and aromatic.',           2.99, 200, 'https://images.unsplash.com/photo-1568702846914-96b305d2aaeb?w=400&q=80'),
(3, 2, 'Conference Pears (4 pack)', 'Perfectly ripe conference pears, great for eating or cooking.',  2.75, 150, 'https://images.unsplash.com/photo-1514756331096-242fdeb70d4a?w=400&q=80'),
(3, 2, 'Mixed Berry Punnet',     'Strawberries, raspberries, and blueberries from our berry fields.', 4.50, 60,  'https://images.unsplash.com/photo-1610832958506-aa56368176cf?w=400&q=80'),
(4, 3, 'Fresh Whole Milk (2L)',  'Unhomogenised full-fat milk from our grass-fed herd.',               1.80, 300, 'https://images.unsplash.com/photo-1550583724-b2692b85b150?w=400&q=80'),
(4, 3, 'Mature Cheddar (200g)', 'Hand-pressed cheddar matured for 12 months.',                        5.50, 90,  'https://images.unsplash.com/photo-1486297678162-eb2a19b0a32d?w=400&q=80'),
(4, 3, 'Salted Butter (250g)',  'Churned from fresh cream with a pinch of sea salt.',                  3.20, 110, 'https://images.unsplash.com/photo-1589985270826-4b7bb135bc9d?w=400&q=80'),
(5, 4, 'Wildflower Honey (340g)', 'Raw unfiltered honey bursting with floral notes.',                 7.99, 70,  'https://images.unsplash.com/photo-1587049352846-4a222e784d38?w=400&q=80'),
(5, 4, 'Creamed Honey (250g)',  'Smooth, spreadable honey — perfect on toast.',                        6.50, 55,  'https://images.unsplash.com/photo-1601039641847-7857b994d704?w=400&q=80'),
(6, 5, 'Sourdough Loaf',        'Long-fermented sourdough with a crispy crust and chewy crumb.',      4.20, 40,  'https://images.unsplash.com/photo-1509440159596-0249088772ff?w=400&q=80'),
(6, 5, 'Croissants (4 pack)',   'Buttery all-butter croissants baked fresh each morning.',             4.80, 35,  'https://images.unsplash.com/photo-1555507036-ab1f4038808a?w=400&q=80'),
(7, 6, 'Organic Herb Bundle',   'Rosemary, thyme, sage, and mint — freshly cut and bundled.',         3.00, 90,  'https://images.unsplash.com/photo-1466637574441-749b8f19452f?w=400&q=80'),
(7, 6, 'Chamomile Tea (30 bags)', 'Dried chamomile flowers from our pesticide-free meadows.',         5.25, 65,  'https://images.unsplash.com/photo-1564890369478-c89ca6d9cde9?w=400&q=80');

INSERT INTO reviews (user_id, product_id, rating, body) VALUES
(8, 1,  5, 'The veg box is absolutely incredible — so fresh and great value!'),
(9, 4,  5, 'Best apples I have ever tasted. Will definitely order again.'),
(8, 7,  4, 'The milk is divine. Delivery was quick too.');